140 lines
4.2 KiB
Plaintext
140 lines
4.2 KiB
Plaintext
{
|
|
|
|
PROGRAMA: EMAILCOBROS
|
|
OBJETIVO: GENERAR REPORTE PARA EL ENVIO DE CORREO
|
|
PROGRAMADOR: ING. JUAN SOTO
|
|
FECHA : 3/11/2015
|
|
|
|
|
|
}
|
|
GLOBALS
|
|
DEFINE usuarios,clave STRING
|
|
|
|
END GLOBALS
|
|
MAIN
|
|
DEFER INTERRUPT
|
|
CALL ARG_VAL(1) RETURNING usuarios
|
|
CALL ARG_VAL(2) RETURNING clave
|
|
|
|
CALL STARTLOG("emailcorreo.TXT")
|
|
CONNECT to "decomarmol" AS "MSSQL" USER usuarios USING clave
|
|
|
|
CALL emailcorreo()
|
|
END MAIN
|
|
|
|
FUNCTION emailcorreo()
|
|
DEFINE HANDLER om.SaxDocumentHandler,
|
|
archivo STRING,
|
|
xmes CHAR(20),
|
|
mes_act,ano_act,ano_ant,xnumero_mes smallint,
|
|
xtipoCobro varchar(20),
|
|
xVendedor,nombre_empresa varchar(80),
|
|
Xvalor_orig, XvalorRd,xvalorRd_ant DEC(12,2),
|
|
Xsimbolo varchar(10),
|
|
fecha1,fecha2,fecha3,fecha4 DATE
|
|
|
|
SELECT a.nombre INTO nombre_empresa FROM companias a WHERE cod_comp = 1
|
|
DISPLAY "empresa ",nombre_empresa
|
|
|
|
LET ano_act = YEAR(TODAY)
|
|
LET ano_ant = YEAR(TODAY)-1
|
|
|
|
LET mes_act = MONTH(TODAY)
|
|
LET xnumero_mes = mes_act
|
|
SELECT descrip INTO xmes FROM mestable WHERE mes=mes_act
|
|
|
|
IF mes_act = 1 THEN
|
|
|
|
SELECT a.fecha_inicio,a.fecha_corte INTO fecha3,fecha4 FROM prdtable a
|
|
WHERE a.ano = ano_ant AND a.mes = 12
|
|
ELSE
|
|
SELECT a.fecha_inicio,a.fecha_corte INTO fecha3,fecha4 FROM prdtable a
|
|
WHERE a.ano = ano_act AND a.mes = mes_act -1
|
|
|
|
END IF
|
|
SELECT a.fecha_inicio,a.fecha_corte INTO fecha1,fecha2 FROM prdtable a
|
|
WHERE a.ano = ano_act AND a.mes = mes_act
|
|
|
|
LET archivo = "emailcobros.4rp"
|
|
|
|
DECLARE informacion CURSOR FOR
|
|
SELECT tipoCobro ,ISNULL(RTRIM(primerNombre),1 ) + " "+ISNULL(RTRIM(PrimerApellido)," "),
|
|
SUM(Valor),SUM(ValorRD),'0',simbolo
|
|
from vwcobros where fecha_orig BETWEEN fecha1 AND fecha2
|
|
|
|
GROUP BY tipoCobro,primerNombre,PrimerApellido,simbolo
|
|
|
|
UNION
|
|
SELECT tipoCobro ,ISNULL(RTRIM(primerNombre),1 ) + " "+ISNULL(RTRIM(PrimerApellido)," "),
|
|
'0','0',SUM(ValorRD),simbolo
|
|
from vwcobros where fecha_orig BETWEEN fecha3 AND fecha4
|
|
|
|
GROUP BY tipoCobro,primerNombre,PrimerApellido,simbolo
|
|
|
|
|
|
IF fgl_report_loadCurrentSettings(archivo) THEN -- load the .4rp file
|
|
|
|
CALL fgl_report_selectDevice("PDF")
|
|
CALL fgl_report_selectPreview(false)
|
|
|
|
CALL fgl_report_setOutputFileName("emailCobros.pdf")
|
|
|
|
LET handler = fgl_report_commitCurrentSettings() -- commit changes
|
|
START REPORT r_email TO XML HANDLER HANDLER
|
|
ELSE
|
|
EXIT PROGRAM
|
|
END IF
|
|
FOREACH informacion INTO xtipoCobro, xVendedor,
|
|
Xvalor_orig, XvalorRd ,xvalorRd_ant,
|
|
Xsimbolo,xnumero_mes
|
|
|
|
OUTPUT TO REPORT r_email(xmes,xnumero_mes,xtipoCobro, xVendedor,
|
|
Xvalor_orig, XvalorRd ,xValorRD_ant,
|
|
Xsimbolo,nombre_empresa)
|
|
END FOREACH
|
|
FINISH REPORT r_email
|
|
END FUNCTION
|
|
REPORT r_email(ymes,ynumero_mes,ytipoCobro, yVendedor,
|
|
yvalor_orig, yvalorRd , yvalorRd_ant,
|
|
ysimbolo,ynombre_empresa)
|
|
DEFINE xano CHAR(4),
|
|
ymes CHAR(20),
|
|
ynumero_mes smallint,
|
|
ytipoCobro varchar(20),
|
|
yVendedor,ynombre_empresa varchar(80),
|
|
yvalor_orig, yvalorRd,total1,total2,total_general1,total_General2,yvalorRd_ant,total3,total_general3 DEC(12,2),
|
|
ysimbolo varchar(10)
|
|
|
|
|
|
ORDER BY ynumero_mes,ytipoCobro,yvendedor
|
|
FORMAT
|
|
FIRST PAGE HEADER
|
|
LET xano = YEAR(today)
|
|
LET total_general1=0
|
|
LET total_General2=0
|
|
LET total_general3=0
|
|
|
|
BEFORE GROUP OF ytipoCobro
|
|
LET total1=0
|
|
LET total2=0
|
|
LET total3=0
|
|
|
|
AFTER GROUP OF yVendedor
|
|
LET yvalor_orig = GROUP SUM(yvalor_orig)
|
|
LET yvalorRD = GROUP SUM(yvalorRD)
|
|
LET yvalorRd_ant = GROUP SUM(yvalorRd_ant)
|
|
|
|
LET total1= total1 + yvalorRd
|
|
LET total2=total2 + yvalor_orig
|
|
LET total3=total3+ yvalorRd_ant
|
|
|
|
LET total_general1 = total_general1 + yvalorRd
|
|
LET total_general2 = total_general2 + yvalor_orig
|
|
LET total_general3 = total_general3 + yvalorRd_ant
|
|
|
|
PRINTX ymes ,ytipoCobro, yVendedor,
|
|
yvalor_orig, yvalorRd ,
|
|
ysimbolo,ynombre_empresa,xano,total1,total2,
|
|
total_general1,total_general2,yValorRd_ant,total_general3,total3
|
|
|
|
END REPORT |