273 lines
8.5 KiB
Plaintext
273 lines
8.5 KiB
Plaintext
{
|
|
######################################################################
|
|
Programa : CPPRRP018
|
|
Proceso : Generacion File DGII
|
|
Sistema : Cuenta Por Pagar
|
|
Autor : JUAN SOTO
|
|
Fecha : SEPTIEMBRE 3, 2008
|
|
#######################################################################
|
|
}
|
|
GLOBALS
|
|
"cpprgb000.4gl"
|
|
DEFINE celda,rval,p_cmd,arch_pc,xval,xcol,file_name,
|
|
prog,file_excel,fila_excel STRING,
|
|
p_res int,rcol VARCHAR(10),
|
|
xi SMALLINT,
|
|
total_facturado DEC(12,2)
|
|
MAIN
|
|
DEFER INTERRUPT
|
|
CALL STARTLOG("cp18.txt")
|
|
CALL ARG_VAL(1) RETURNING usuarios
|
|
CALL ARG_VAL(2) RETURNING clave
|
|
CALL ARG_VAL(3) RETURNING impresor
|
|
|
|
CONNECT to "marmotech" user usuarios USING clave
|
|
SELECT a.* INTO p_companias.* FROM companias a
|
|
CALL cpprrp018()
|
|
END MAIN
|
|
FUNCTION cpprrp018()
|
|
DEFINE fecha1,fecha2 date,
|
|
pcodsp,pcodspsec INT,
|
|
periodo CHAR(6),
|
|
cels,pnumdoc,xcuenta_no CHAR(10),
|
|
kmes,kano,numero_c,pcod_sp,xcodigo_isr,pcodigofiscal,pcel,prow SMALLINT,
|
|
archivo1,filename CHAR(200),
|
|
facturado,pitbis,pvalor,retencion,itbisretenido,xvalor DEC(12,2),
|
|
documento,prnc,pcedula CHAR(11),
|
|
pdescripcion CHAR(30),
|
|
strfiscal,xtipo_cuenta,xselectivo_consumo CHAR(40),
|
|
mess CHAR(200),
|
|
pncf CHAR(19),
|
|
pfecha DATE,
|
|
periodo1 CHAR(6),
|
|
idioma_office CHAR(20)
|
|
|
|
|
|
CLEAR SCREEN
|
|
OPTIONS
|
|
FORM LINE 8
|
|
|
|
OPEN FORM cpfmrp018 FROM "cpfmrp018"
|
|
|
|
DISPLAY "cpprrp018" AT 4,3 ATTRIBUTE(blue)
|
|
DISPLAY "GENERA FILE 606 PARA LA DGII" AT 6,17
|
|
DISPLAY FORM cpfmrp018
|
|
LET int_flag = FALSE
|
|
|
|
INPUT BY NAME fecha1,fecha2,pcod_sp,idioma_office
|
|
|
|
IF int_flag THEN
|
|
LET numero_msg = 2
|
|
CALL msg(numero_msg)
|
|
LET int_flag = FALSE
|
|
RETURN
|
|
END IF
|
|
LET kmes = MONTH(fecha2)
|
|
LET kano = YEAR(fecha2)
|
|
LET periodo = kano USING "&&&&",kmes USING "&&"
|
|
|
|
LET prog = "EXCEL"
|
|
IF idioma_office = "E" THEN
|
|
LET fila_excel = "F"
|
|
ELSE
|
|
LET fila_excel = "R"
|
|
END IF
|
|
CALL ui.Interface.frontCall("standard","getenv", ["USDGII606"], [file_name] )
|
|
|
|
|
|
#LET arch_pc='"',file,'"' -- le agrega las comillas por si hay blancos (solo se necesita para shellexec)
|
|
LET arch_pc=FILE_name 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,arch_pc], [p_res] )
|
|
CALL chqDdeError(p_res,"DDEConnect "||arch_pc)
|
|
|
|
CALL ui.Interface.refresh()
|
|
|
|
SELECT count(*),SUM(b.valor-a.monto_itbi) INTO NUMERO_C,facturado FROM cptb00004 a,cptb00001 b
|
|
WHERE (a.cod_sp = b.cod_sp) AND
|
|
(a.cod_sp = pcod_sp) AND
|
|
(a.cod_sp_Sec = b.cod_sp_sec AND a.ncf=a.ncf AND a.num_doc = a.num_doc) AND
|
|
(a.num_doc = b.num_doc) AND b.tipo_doc <> 'CK' AND
|
|
b.fecha_orig BETWEEN fecha1 AND fecha2 AND b.status_t IS NULL
|
|
|
|
LET rcol = "C3"
|
|
LET xi = 5
|
|
CALL pru_new_dde(periodo,rcol,xi)
|
|
{CANTIDAD REGISTORS}
|
|
|
|
LET rcol = "C3"
|
|
LET xi = 6
|
|
CALL pru_new_dde(numero_c,rcol,xi)
|
|
|
|
CALL fgl_winmessage("Hoja de Excel Formulario 606",
|
|
"Debe Ejecutar el boton INICIO en la hoja de excel","Info")
|
|
|
|
DECLARE busca_datos CURSOR FOR
|
|
SELECT a.rnc,b.codigo_fiscal,c.descripcion,b.ncf,Convert(varchar(10),d.fecha_orig,103),AVG(b.monto_itbi),AVG(d.valor)-AVG(b.monto_itbi),
|
|
b.num_doc,a.cod_sp,a.cod_sp_sec,f.tipo_cuenta,f.selectivo_consumo,
|
|
SUM(g.debito+g.credito),x1.codigo_isr
|
|
FROM cotb00001 a,cptb00004 b,cgtb00074 c,cptb00001 d,cgtb00001 f
|
|
LEFT OUTER JOIN cgtb00077 x1 ON f.codigo_isr = x1.codigo_isr,
|
|
cptb00003 g
|
|
WHERE a.cod_sp = pcod_sp AND
|
|
a.cod_sp = b.cod_sp AND
|
|
a.cod_sp_Sec = b.cod_sp_sec AND
|
|
a.cod_sp = d.cod_sp AND
|
|
a.cod_sp_sec = d.cod_sp_sec AND
|
|
b.codigo_fiscal = c.codigo_fiscal AND
|
|
b.num_doc = d.num_doc AND d.tipo_doc = 'FT' AND
|
|
g.cuenta_no = f.cuenta_no AND (F.tipo_cuenta in ('BIENES','SERVICIOS') OR f.selectivo_consumo='SI') AND
|
|
g.cod_sp = d.cod_sp AND g.cod_sp_sec = d.cod_sp_sec AND
|
|
g.factura =d.num_doc AND
|
|
g.cod_sp = b.cod_sp AND g.cod_sp_sec = b.cod_sp_sec and
|
|
d.fecha_orig BETWEEN fecha1 AND fecha2 AND d.status_t IS NULL
|
|
GROUP BY a.rnc,b.codigo_fiscal,c.descripcion,b.ncf,d.fecha_orig,b.num_doc,
|
|
a.cod_sp,a.cod_sp_sec,f.tipo_cuenta,f.selectivo_consumo,x1.codigo_isr
|
|
ORDER BY a.rnc
|
|
|
|
LET prow = 12
|
|
LET pcel = 2
|
|
LET total_facturado = 0
|
|
FOREACH busca_datos INTO prnc,pcodigofiscal,pdescripcion,pncf,pfecha,pitbis,pvalor,
|
|
pnumdoc,pcodsp,pcodspsec,xtipo_cuenta,xselectivo_consumo,
|
|
xvalor,xcodigo_isr,xcuenta_no
|
|
LET rcol = "C2"
|
|
LET xi = prow
|
|
CALL pru_new_dde(prnc,rcol,xi)
|
|
|
|
LET strfiscal = pcodigofiscal USING "&&","-",pdescripcion CLIPPED
|
|
|
|
LET rcol = "C4"
|
|
LET xi = prow
|
|
CALL pru_new_dde(strfiscal,rcol,xi)
|
|
|
|
LET rcol = "C5"
|
|
LET xi = prow
|
|
CALL pru_new_dde(pncf,rcol,xi)
|
|
|
|
LET periodo1 = YEAR(pfecha) USING "&&&&",MONTH(pfecha) USING "&&"
|
|
|
|
LET rcol = "C7"
|
|
LET xi = prow
|
|
CALL pru_new_dde(periodo1,rcol,xi)
|
|
|
|
LET rcol = "C8"
|
|
LET xi = prow
|
|
CALL pru_new_dde(DAY(pfecha),rcol,xi)
|
|
|
|
LET rcol = "C9"
|
|
LET xi = prow
|
|
CALL pru_new_dde(periodo1,rcol,xi)
|
|
|
|
LET rcol = "C11"
|
|
LET xi = prow
|
|
|
|
IF xtipo_cuenta = "SERVICIOS" THEN
|
|
CALL pru_new_dde(xvalor,rcol,xi)
|
|
ELSE
|
|
CALL pru_new_dde(0,rcol,xi)
|
|
END IF
|
|
LET rcol = "C12"
|
|
LET xi = prow
|
|
|
|
IF xtipo_cuenta = "BIENES" THEN
|
|
CALL pru_new_dde(xvalor,rcol,xi)
|
|
ELSE
|
|
CALL pru_new_dde(0,rcol,xi)
|
|
END IF
|
|
IF pvalor IS NULL THEN
|
|
LET pvalor = 0
|
|
END IF
|
|
|
|
LET rcol = "C13"
|
|
LET xi = prow
|
|
CALL pru_new_dde(pvalor,rcol,xi)
|
|
LET total_facturado = total_facturado + pvalor
|
|
|
|
LET rcol = "C14"
|
|
LET xi = prow
|
|
CALL pru_new_dde(pitbis,rcol,xi)
|
|
|
|
IF xcodigo_isr IS NOT NULL THEN
|
|
LET retencion = 0
|
|
SELECT SUM(a.credito) INTO retencion
|
|
FROM cptb00003 a
|
|
WHERE a.cod_sp = pcodsp AND
|
|
a.cod_sp_Sec = pcodspsec AND
|
|
a.factura = pnumdoc and
|
|
a.cuenta_no = xcuenta_no
|
|
|
|
LET itbisretenido = 0
|
|
SELECT SUM(a.credito) INTO itbisretenido
|
|
FROM cptb00003 a
|
|
WHERE a.cod_sp = pcodsp AND
|
|
a.cod_sp_Sec = pcodspsec AND
|
|
a.factura = pnumdoc and
|
|
a.cuenta_no = xcuenta_no
|
|
|
|
|
|
LET rcol = "C15"
|
|
LET xi = prow
|
|
CALL pru_new_dde(itbisretenido,rcol,xi)
|
|
|
|
LET rcol = "C16"
|
|
LET xi = prow
|
|
CALL pru_new_dde(retencion,rcol,xi)
|
|
|
|
END IF
|
|
LET prow = prow + 1
|
|
|
|
END FOREACH
|
|
|
|
LET rcol = "C3"
|
|
LET xi = 7
|
|
CALL pru_new_dde(total_facturado,rcol,xi)
|
|
|
|
|
|
END FUNCTION
|
|
|
|
FUNCTION pru_new_dde(xval,xcol,i)
|
|
|
|
DEFINE celda,rval,xval,xcol STRING
|
|
DEFINE i INTEGER
|
|
|
|
|
|
|
|
|
|
LET celda = fila_excel,i USING "<<<<&",xcol CLIPPED
|
|
LET rval = xval
|
|
CALL ui.Interface.frontCall("WINDDE","DDEPoke", [prog,arch_pc,celda,rval], [p_res] )
|
|
CALL chqDdeError(p_res,"DDEPoke "||celda)
|
|
|
|
|
|
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
|