392 lines
12 KiB
Plaintext
392 lines
12 KiB
Plaintext
{
|
|
---------------------------------------------------------------------------
|
|
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 |