{ --------------------------------------------------------------------------- PROGRAMA : NOPRMT026 OBJETIVO : PROCESO PARA EL PAGO DE LA TESORERIA DE LA SEGURIDAD SOCIAL PROGRAMADOR : Ing. JUAN SOTO FECHA REALIZACION : MAYO 31, 2005 --------------------------------------------------------------------------- } GLOBALS DEFINE rcol,arch_pc,fila_excel,prog STRING, p_res INT,cuenta,xi,progreso SMALLINT, selec CHAR(1000), ciarnc CHAR(11), otras_remuneraciones DEC(12,2), tipo_reporte VARCHAR(3) DEFINE usuarios,clave CHAR(80), file STRING, id1 base.StringTokenizer, fecha_n char(8), fecha_inicial,fecha_final DATE, kano,kmes, cnt,numero_msg SMALLINT, periodo CHAR(6), valor_boni DEC(12,2), idioma_office VARCHAR END GLOBALS MAIN DEFER INTERRUPT CALL ARG_VAL(1) RETURNING usuarios CALL ARG_VAL(2) RETURNING clave CALL startlog("NORP26.txt") CONNECT to "smarmotech" USER usuarios USING clave SELECT a.rnc INTO ciarnc FROM companias a LET id1= base.StringTokenizer.create(ciarnc,"-") LET ciarnc = id1.nextToken() LET ciarnc = ciarnc CLIPPED,id1.nextToken() LET ciarnc = ciarnc CLIPPED,id1.nextToken() LET ciarnc = ciarnc CLIPPED,id1.nextToken() CALL pago_tss() END MAIN FUNCTION pago_tss() DEFINE ptss RECORD ficha INTEGER, nom1 CHAR(30), apell1 CHAR(30), apell2 CHAR(30), sexo VARCHAR, cedula CHAR(11), fecha_n DATE, valor_tss DEC(12,2), departamento SMALLINT, valor_isr DEC(12,2) END RECORD OPTIONS FORM LINE 9, ERROR LINE 24 OPEN FORM nofmmt026 FROM "nofmmt026" DISPLAY FORM nofmmt026 INPUT BY NAME kano,kmes,tipo_reporte,idioma_office AFTER FIELD kmes LET selec = "SELECT CONVERT(CHAR(10),MIN(a.fecha_del),103) ,CONVERT(CHAR(10),MAX(a.fecha_al),103) ", " FROM notb00010 a ", " WHERE a.ano ='", kano,"' AND a.mes ='", kmes, "'" PREPARE busca_fecha FROM selec EXECUTE busca_fecha INTO fecha_inicial,fecha_final DISPLAY BY NAME fecha_inicial,fecha_final AFTER INPUT IF int_flag THEN LET numero_msg = 2 CALL msg(numero_msg) LET int_flag = FALSE RETURN END IF LET periodo = kmes USING "&&",kano USING "&&&&" LET prog = "EXCEL" IF idioma_office = "E" THEN LET fila_excel = "F" ELSE LET fila_excel = "R" END IF IF tipo_reporte = "BON" THEN CALL bonificacion() EXIT PROGRAM END IF END INPUT CALL ui.Interface.frontCall("standard","getenv", ["USTSS"], [file] ) #LET arch_pc='"',file,'"' -- le agrega las comillas por si hay blancos (solo se necesita para shellexec) LET arch_pc=FILE CLIPPED --PARA OFFICE 2010 en adelante no es necesario colocar las comillas. CALL ui.Interface.frontCall("standard","shellexec", [arch_pc], [p_res] ) CALL chqDdeError(p_res,"shellexec "||arch_pc) CALL ui.Interface.frontCall("WINDDE","DDEConnect", [prog,file], [p_res] ) CALL chqDdeError(p_res,"DDEConnect "||file) CALL ui.Interface.refresh() LET rcol = "C4" LET xi = 8 CALL pru_new_dde(periodo,rcol,xi) LET progreso = 5 DISPLAY progreso TO progreso CALL ui.Interface.refresh() LET selec = " SELECT a.num_emp,b.nom1_emp,b.apell1_emp,b.apell2_emp,d.sexo,b.cedula,convert(char(10),b.fech_nac,103),", " SUM(a.valor) ", " FROM adtb00003 b, notb00008 a,notb00010 c,adtb00021 d ", " WHERE b.num_emp = a.num_emp AND c.mes ='",kmes,"' AND ", " c.ano ='",kano,"' AND b.num_emp = d.num_emp and ", " a.num_nomi = c.num_nomi and ", " a.tipo_emp = c.tipo_emp and ", " a.cod_mov in (1,40,36,5,24) AND ", " a.num_emp = d.num_emp AND ", " convert(char(10), a.fecha,101) between '",fecha_inicial USING "mm/dd/yyyy", "' and '",fecha_final using "mm/dd/yyyy","' and b.nomina = 'Q' ", " GROUP BY a.num_emp,b.nom1_emp,b.apell1_emp,b.apell2_emp,d.sexo,b.cedula,b.fech_nac ", " ORDER BY b.apell1_emp " LET cnt = 14 PREPARE comando FROM selec DECLARE busca CURSOR FOR comando LET cuenta = 0 FOREACH busca INTO ptss.* LET rcol = "C2" LET xi = cnt CALL pru_new_dde("001",rcol,xi) LET rcol = "C3" LET xi = cnt CALL pru_new_dde("C",rcol,xi) LET rcol = "C4" LET xi = cnt CALL pru_new_dde(ptss.cedula,rcol,xi) LET rcol = "C5" LET xi = cnt CALL pru_new_dde(ptss.nom1,rcol,xi) LET rcol = "C6" LET xi = cnt CALL pru_new_dde(ptss.apell1,rcol,xi) LET rcol = "C7" LET xi = cnt CALL pru_new_dde(ptss.apell2,rcol,xi) LET rcol = "C8" LET xi = cnt CALL pru_new_dde(ptss.sexo,rcol,xi) LET fecha_n = ptss.fecha_n USING "ddmmyyyy" LET rcol = "C9" LET xi = cnt CALL pru_new_dde(fecha_n,rcol,xi) LET rcol = "C10" LET xi = cnt CALL pru_new_dde(ptss.valor_tss,rcol,xi) # BUSCA VALOR IMPUESTO ISR LET ptss.valor_isr = 0 SELECT SUM(a.valor) INTO ptss.valor_isr FROM notb00008 a WHERE a.num_emp = ptss.ficha AND a.fecha BETWEEN fecha_inicial AND fecha_final AND a.clase_mov IN ("E","I") and a.cod_mov not in (32,33,2,3,4,37,35) LET rcol = "C12" LET xi = cnt CALL pru_new_dde(ptss.valor_isr,rcol,xi) # BONIFICACION LET valor_boni = 0 SELECT SUM(a.valor) INTO valor_boni FROM notb00008 a WHERE a.num_emp = ptss.ficha AND a.fecha BETWEEN fecha_inicial AND fecha_final AND a.clase_mov IN ("E","I") and a.cod_mov in (33,2,3,4,37,35) LET rcol = "C13" LET xi = cnt CALL pru_new_dde(valor_boni,rcol,xi) LET rcol = "C14" LET xi = cnt CALL pru_new_dde(ciarnc,rcol,xi) #REGALIA PASCUAL LET valor_boni = 0 SELECT SUM(a.valor) INTO valor_boni FROM notb00008 a WHERE a.num_emp = ptss.ficha AND a.fecha BETWEEN fecha_inicial AND fecha_final AND a.clase_mov IN ("E","I") and a.cod_mov in (32) LET rcol = "C16" LET xi = cnt CALL pru_new_dde(valor_boni,rcol,xi) LET rcol = "C18" LET xi = cnt CALL pru_new_dde(ptss.valor_tss,rcol,xi) LET cnt = cnt + 1 IF cuenta > 3 THEN LET cuenta = 0 LET progreso =progreso + 1 DISPLAY progreso TO progreso CALL ui.Interface.refresh() END IF LET cuenta = cuenta + 1 END FOREACH CALL ui.Interface.frontCall("WINDDE","DDEFinish", [prog,file], [p_res] ) CALL chqDdeError(p_res,"DDEFinish") CALL ui.Interface.frontCall("WINDDE","DDEFinishAll", [], [p_res] ) CALL chqDdeError(p_res,"DDEFinishAll") END FUNCTION FUNCTION pru_new_dde(xval,xcol,i) DEFINE celda,rval,p_cmd,arch_pc,xval,xcol STRING DEFINE i INTEGER LET celda = fila_excel,i USING "<<<<&",xcol CLIPPED LET rval = xval # CALL fgl_winmessage("PROGRAMA",prog,"stop") # CALL fgl_winmessage("FILE",file,"stop") # CALL fgl_winmessage("CELDA",celda,"stop") # CALL fgl_winmessage("rVAL",rval,"stop") # CALL fgl_winmessage("P_RES",p_res,"stop") CALL ui.Interface.frontCall("WINDDE","DDEPoke", [prog,file,celda,rval], [p_res] ) CALL chqDdeError(p_res,"DDEPoke "||celda) { MESSAGE "Salvando la planilla ",file CLIPPED," !" CALL ui.Interface.refresh() LET p_cmd="[save()]" CALL ui.Interface.frontCall("WINDDE","DDEExecute", [prog,file,p_cmd], [p_res] ) IF p_res THEN CALL fgl_winmessage("Planilla salvada", "Nombre = "||file, "information") ELSE CALL chqDdeError(p_res,"DDEExecute save") END IF CAL ui.Interface.frontCall("WINDDE","DDEFinish", [prog,file], [p_res] ) CALL chqDdeError(p_res,"DDEFinish") CALL ui.Interface.frontCall("WINDDE","DDEFinishAll", [], [p_res] ) CALL chqDdeError(p_res,"DDEFinishAll") } END FUNCTION FUNCTION chqDdeError(p_res,p_msg) DEFINE p_res INTEGER, p_msg STRING, m_err STRING, m_log STRING IF p_res THEN RETURN END IF CALL ui.Interface.frontCall("WINDDE","DDEError",[],[m_err]) CALL ui.Interface.frontCall("WINDDE","DDEFinishAll", [], [p_res] ) LET m_log="DDE Error: ",m_err CLIPPED," (",p_msg,")" # CALL errorlog(m_log) LET m_log="Se ha producido un Error en '",p_msg CLIPPED,"'. Para mas detalle revise el LOG de errores." CALL fgl_winmessage("DDE Error", m_log, "stop") EXIT PROGRAM END FUNCTION FUNCTION bonificacion() DEFINE tssbon RECORD tipo_Doc CHAR(1), cedula CHAR(11), nombres CHAR(50), primer_apellido CHAR(30), segundo_apellido CHAR(30), sexo CHAR(2), fecha_nacimiento CHAR(10), monto DECIMAL(12,2) END RECORD CALL ui.Interface.frontCall("standard","getenv", ["USTSSBON"], [file] ) #LET arch_pc='"',file,'"' -- le agrega las comillas por si hay blancos (solo se necesita para shellexec) LET arch_pc=FILE CLIPPED --PARA OFFICE 2010 en adelante no es necesario colocar las comillas. CALL ui.Interface.frontCall("standard","shellexec", [arch_pc], [p_res] ) CALL chqDdeError(p_res,"shellexec "||arch_pc) CALL ui.Interface.frontCall("WINDDE","DDEConnect", [prog,file], [p_res] ) CALL chqDdeError(p_res,"DDEConnect "||file) CALL ui.Interface.refresh() LET rcol = "C4" LET xi = 9 CALL pru_new_dde(periodo,rcol,xi) LET progreso = 5 DISPLAY progreso TO progreso CALL ui.Interface.refresh() LET selec = " SELECT 'C',b.cedula,RTRIM(b.nom1_emp)+' '+isnull(b.nom2_emp,' '),b.apell1_emp,b.apell2_emp,d.sexo,convert(char(10),b.fech_nac,103),", " a.valor,a.departamento ", " FROM adtb00003 b, notb00008 a,notb00010 c,adtb00021 d ", " WHERE b.num_emp = a.num_emp AND c.mes ='",kmes,"' AND ", " c.ano ='",kano,"' AND b.num_emp = d.num_emp and ", " a.num_nomi = c.num_nomi and ", " a.tipo_emp = c.tipo_emp and ", " a.cod_mov in (33) AND ", " convert(char(10), a.fecha,101) between '",fecha_inicial USING "mm/dd/yyyy", "' and '",fecha_final using "mm/dd/yyyy","' and b.nomina = 'Q' ", " ORDER BY a.departamento " LET cnt = 15 PREPARE comando1 FROM selec DECLARE busca1 CURSOR FOR comando1 LET cuenta = 0 FOREACH busca1 INTO tssbon.* LET rcol = "C2" LET xi = cnt CALL pru_new_dde(tssbon.tipo_Doc,rcol,xi) LET rcol = "C3" LET xi = cnt CALL pru_new_dde(tssbon.cedula,rcol,xi) LET rcol = "C4" LET xi = cnt CALL pru_new_dde(tssbon.nombres,rcol,xi) LET rcol = "C5" LET xi = cnt CALL pru_new_dde(tssbon.primer_apellido,rcol,xi) LET rcol = "C6" LET xi = cnt CALL pru_new_dde(tssbon.segundo_apellido,rcol,xi) LET rcol = "C7" LET xi = cnt CALL pru_new_dde(tssbon.sexo,rcol,xi) LET rcol = "C8" LET xi = cnt CALL pru_new_dde(tssbon.fecha_nacimiento ,rcol,xi) LET rcol = "C9" LET xi = cnt CALL pru_new_dde(tssbon.monto,rcol,xi) LET cnt = cnt +1 END FOREACH END FUNCTION