509 lines
16 KiB
Plaintext
509 lines
16 KiB
Plaintext
{
|
|
|
|
PROGRAMA: CGPRCS025
|
|
OBJETIVO: VISUALIZAR LOS GASTOS DE FORMA PANORAMICA ANUAL
|
|
FECHA DESARROLLO: ENERO 23,2023
|
|
DESARROLLADO POR: ING. JUAN SOTO
|
|
}
|
|
|
|
GLOBALS "cgprgb000.4gl"
|
|
DEFINE
|
|
data DYNAMIC ARRAY OF RECORD
|
|
|
|
descripcion VARCHAR(100),
|
|
mes1,
|
|
mes2,
|
|
mes3,
|
|
mes4,
|
|
mes5,
|
|
mes6,
|
|
mes7,
|
|
mes8,
|
|
mes9,
|
|
mes10,
|
|
mes11,
|
|
mes12,
|
|
total
|
|
DEC(18, 2),
|
|
departamento INT,
|
|
cuenta_no VARCHAR(15),
|
|
parentID, childid VARCHAR(20)
|
|
END RECORD,
|
|
pano,
|
|
nodo,
|
|
hijo,
|
|
secuencia,
|
|
progreso,
|
|
cuentadepto,
|
|
nopregdepto,
|
|
noregacum,
|
|
cuentaacum
|
|
INT,
|
|
usuario_c VARCHAR(100),
|
|
i INT
|
|
MAIN
|
|
DEFER INTERRUPT
|
|
|
|
--CALL STARTLOG("CGPRMT021.TXT")
|
|
CALL ARG_VAL(1) RETURNING usuarios
|
|
CALL ARG_VAL(2) RETURNING clave
|
|
|
|
CONNECT TO "smarmotech" AS "MSSQL" USER usuarios USING clave
|
|
CALL cgprcs025()
|
|
END MAIN
|
|
FUNCTION cgprcs025()
|
|
DEFINE fechas RECORD
|
|
fecha1, fecha2 DATE
|
|
END RECORD
|
|
DEFINE v_col STRING
|
|
DEFINE
|
|
v_mes SMALLINT,
|
|
kdepartamento INT
|
|
|
|
CALL ui.Interface.loadStyles("formularios")
|
|
OPEN WINDOW w1 WITH FORM 'cgfmcs026'
|
|
|
|
DIALOG ATTRIBUTE(UNBUFFERED)
|
|
INPUT BY NAME pano
|
|
BEFORE INPUT
|
|
DISPLAY 'VISUALIZACION GASTOS DEPARTAMENTOS PANORAMICO ANUAL'
|
|
TO encabezado
|
|
SELECT RTRIM(a.nom1_emp) || ' ' || a.apell1_emp
|
|
INTO usuario_c
|
|
FROM adtb00003 a, seg0001 b
|
|
WHERE b.usuario = usuarios AND a.num_emp = b.num_emp
|
|
|
|
DISPLAY BY NAME usuario_c
|
|
AFTER FIELD pano
|
|
IF pano IS NULL THEN
|
|
CALL msg(16)
|
|
NEXT FIELD pano
|
|
END IF
|
|
|
|
LET noregacum = 1
|
|
|
|
LET progreso = 0
|
|
DISPLAY progreso TO progreso
|
|
CALL ui.Interface.refresh()
|
|
END INPUT
|
|
DISPLAY ARRAY data TO sdata.*
|
|
BEFORE DISPLAY
|
|
CALL buscaNodo0()
|
|
ON ACTION gasranfech ATTRIBUTE(TEXT = "Registro", IMAGE = 'record')
|
|
# LET v_col = DIALOG.getCurrentItem()
|
|
LET v_mes = seleccionarMes(arr_curr())
|
|
DISPLAY "data1: ", v_mes
|
|
IF v_mes = 0 THEN
|
|
CALL msg(1024)
|
|
CONTINUE DIALOG
|
|
END IF
|
|
IF data[arr_curr()].cuenta_no IS NULL THEN
|
|
CALL msg(1025)
|
|
CONTINUE DIALOG
|
|
END IF
|
|
DISPLAY "data: ",
|
|
pano,
|
|
" ",
|
|
v_mes,
|
|
" ",
|
|
data[arr_curr()].cuenta_no,
|
|
" DEP: ",
|
|
data[arr_curr()].departamento
|
|
-- 4) Rango de fechas del mes/a?o desde prdtable
|
|
SELECT a.fecha_inicio, a.fecha_corte
|
|
INTO fechas.*
|
|
FROM prdtable a
|
|
WHERE a.ano = pano AND a.mes = v_mes
|
|
|
|
IF STATUS = NOTFOUND THEN
|
|
CALL msg(45)
|
|
CONTINUE DIALOG
|
|
END IF -- 5) Detalle (te recomiendo pasar tambi?n v_mes por claridad)
|
|
FOR i = 1 TO arr_curr()
|
|
IF data[i].departamento IS NOT NULL THEN
|
|
LET kdepartamento = data[i].departamento
|
|
END IF
|
|
DISPLAY "i: ", i, " ", kdepartamento
|
|
END FOR
|
|
CALL detalleGastos(
|
|
'NO TIENE',
|
|
data[arr_curr()].cuenta_no,
|
|
kdepartamento,
|
|
'gasranfech',
|
|
fechas.*)
|
|
ON ACTION CANCEL
|
|
EXIT PROGRAM
|
|
ON ACTION continuar
|
|
ATTRIBUTE(TEXT = "Cont. Consulta", IMAGE = 'refresh')
|
|
CALL data.clear()
|
|
NEXT FIELD pano
|
|
END DISPLAY
|
|
|
|
END DIALOG
|
|
|
|
END FUNCTION
|
|
|
|
FUNCTION buscaNodo0()
|
|
|
|
DECLARE busca_noDeptos CURSOR FOR
|
|
SELECT COUNT(DISTINCT a.cuenta_no), d.departamento_visualiza
|
|
FROM cgtb00004 a, cgtb00001 b, prdtable c, adtb00056 d
|
|
WHERE (a.cuenta_no = b.cuenta_no AND a.cuenta_no LIKE '7%')
|
|
AND (a.fecha BETWEEN c.fecha_inicio AND c.fecha_corte)
|
|
AND (a.ref = a.ref AND a.tipo <> 3)
|
|
AND (a.departamento = d.departamento_Visualiza)
|
|
AND a.ref NOT LIKE 'ED.99%' AND a.cuenta_no <> '7600'
|
|
AND d.usuario = usuarios
|
|
AND c.ano = pano
|
|
AND a.status_t IS NULL
|
|
GROUP BY d.departamento_visualiza
|
|
IF cuentaacum IS NULL THEN
|
|
LET cuentaacum = 0
|
|
END IF
|
|
FOREACH busca_noDeptos INTO cuentadepto
|
|
DISPLAY "cuenta acum ",
|
|
cuentaacum,
|
|
" ano ",
|
|
pano,
|
|
" usuario ",
|
|
usuarios,
|
|
" depto ",
|
|
cuentadepto
|
|
LET cuentaacum = cuentaacum + cuentadepto
|
|
END FOREACH
|
|
LET secuencia = 1
|
|
DECLARE nodo0 CURSOR FOR
|
|
SELECT a.nom_dpto, a.departamento
|
|
FROM adtb00001 a, adtb00056 b
|
|
WHERE a.departamento = b.departamento_visualiza
|
|
AND b.usuario = usuarios
|
|
|
|
LET idx = 1
|
|
LET nodo = NULL
|
|
LET hijo = 1
|
|
FOREACH nodo0 INTO data[idx].descripcion, data[idx].departamento
|
|
LET data[idx].parentID = nodo
|
|
LET data[idx].childid = secuencia USING "<<<"
|
|
# DISPLAY data[idx].descripcion," parent ",data[idx].parentID," child ",data[idx].childid
|
|
CALL balanceTotalMes(data[idx].departamento)
|
|
CALL llenahijos(data[idx].departamento)
|
|
|
|
LET idx = idx + 1
|
|
END FOREACH
|
|
FOR idx = 1 TO data.getLength()
|
|
|
|
IF data[idx].descripcion IS NULL THEN
|
|
CALL data.deleteElement(idx)
|
|
END IF
|
|
END FOR
|
|
{
|
|
LET data[1].descripcion ='TECNOLOGIA'
|
|
LET data[1].parentID = null
|
|
LET data[1].childid='1'
|
|
|
|
LET data[2].descripcion ='GASTOS1'
|
|
LET data[2].parentID = 1
|
|
LET data[2].childid='1.1'
|
|
LET data[2].mes1 = 1000
|
|
|
|
LET data[3].descripcion ='GASTOS2'
|
|
LET data[3].parentID = 1
|
|
LET data[3].childid='1.2'
|
|
LET data[3].mes1 = 2000
|
|
|
|
|
|
LET data[4].descripcion ='CORTES A MEDIDAS'
|
|
LET data[4].parentID = null
|
|
LET data[4].childid='2'
|
|
|
|
|
|
LET data[5].descripcion ='GASTOS3'
|
|
LET data[5].parentID = 2
|
|
LET data[5].childid='2'
|
|
LET data[5].mes1 = 4000
|
|
}
|
|
|
|
END FUNCTION
|
|
FUNCTION llenahijos(pdepartamento)
|
|
DEFINE pdepartamento, noreg INT
|
|
|
|
DECLARE buscahijos CURSOR FOR
|
|
SELECT DISTINCT '(' || RTRIM(b.cuenta_no) || ')' || b.descripcion,
|
|
b.cuenta_no
|
|
FROM cgtb00004 a, cgtb00001 b, prdtable c
|
|
WHERE (a.cuenta_no = b.cuenta_no AND a.cuenta_no LIKE '7%')
|
|
AND (a.fecha BETWEEN c.fecha_inicio AND c.fecha_corte)
|
|
AND (a.departamento = pdepartamento)
|
|
AND a.ref NOT LIKE 'ED.99%' AND a.cuenta_no <> '7600'
|
|
AND (a.ref = a.ref AND a.tipo <> 3)
|
|
AND c.ano = pano
|
|
AND a.status_t IS NULL
|
|
|
|
LET idx = idx + 1
|
|
IF noreg IS NULL THEN
|
|
LET noreg = 0
|
|
END IF
|
|
FOREACH buscahijos INTO data[idx].descripcion, data[idx].cuenta_no
|
|
|
|
LET data[idx].parentID = secuencia
|
|
LET data[idx].childid =
|
|
data[idx].parentID CLIPPED, ".", hijo USING "<<<"
|
|
|
|
CALL balances(pdepartamento, data[idx].cuenta_no)
|
|
|
|
LET progreso = (noregacum / cuentaacum) * 100
|
|
DISPLAY "regis ",
|
|
noregacum,
|
|
" cuenta ",
|
|
cuentaacum,
|
|
" progreso ",
|
|
progreso
|
|
DISPLAY "noreg ", noreg, " cuenta ", data[idx].descripcion
|
|
DISPLAY progreso TO progreso
|
|
CALL ui.Interface.refresh()
|
|
|
|
LET noregacum = noregacum + 1
|
|
LET noreg = noreg + 1
|
|
LET idx = idx + 1
|
|
LET hijo = hijo + 1
|
|
END FOREACH
|
|
LET secuencia = secuencia + 1
|
|
|
|
END FUNCTION
|
|
FUNCTION balances(xdepartamento, xcuenta_no)
|
|
|
|
DEFINE
|
|
xdepartamento INT,
|
|
xcuenta_no VARCHAR(14),
|
|
balance DEC(12, 2)
|
|
|
|
FOR i = 1 TO 12
|
|
#GASTOS RANGO DE FECHAS:
|
|
SELECT a.fecha_inicio, a.fecha_corte
|
|
INTO fecha1, fecha2
|
|
FROM prdtable a
|
|
WHERE a.ano = pano AND a.mes = i
|
|
|
|
LET balance = 0
|
|
SELECT ISNULL(SUM(a.debito - a.credito), 0)
|
|
INTO balance
|
|
FROM cgtb00004 a
|
|
WHERE (a.cuenta_no = xcuenta_no AND a.cuenta_no LIKE '7%')
|
|
AND (a.fecha BETWEEN fecha1 AND fecha2)
|
|
AND (a.departamento = xdepartamento)
|
|
AND (a.ref = a.ref AND a.tipo <> 3)
|
|
AND a.ref NOT LIKE 'ED.99%' AND a.cuenta_no <> '7600'
|
|
AND a.status_t IS NULL
|
|
IF i = 1 THEN
|
|
LET data[idx].mes1 = balance
|
|
# DISPLAY "BALANCE ",BALANCE," DEP ",xdepartamento," CUENTA ",xcuenta_no," fechas ",fecha1," ",fecha2
|
|
END IF
|
|
|
|
IF i = 2 THEN
|
|
LET data[idx].mes2 = balance
|
|
END IF
|
|
IF i = 3 THEN
|
|
LET data[idx].mes3 = balance
|
|
END IF
|
|
IF i = 4 THEN
|
|
LET data[idx].mes4 = balance
|
|
END IF
|
|
IF i = 5 THEN
|
|
LET data[idx].mes5 = balance
|
|
END IF
|
|
IF i = 6 THEN
|
|
LET data[idx].mes6 = balance
|
|
END IF
|
|
IF i = 7 THEN
|
|
LET data[idx].mes7 = balance
|
|
END IF
|
|
IF i = 8 THEN
|
|
LET data[idx].mes8 = balance
|
|
END IF
|
|
IF i = 9 THEN
|
|
LET data[idx].mes9 = balance
|
|
END IF
|
|
IF i = 10 THEN
|
|
LET data[idx].mes10 = balance
|
|
END IF
|
|
IF i = 11 THEN
|
|
LET data[idx].mes11 = balance
|
|
END IF
|
|
IF i = 12 THEN
|
|
LET data[idx].mes12 = balance
|
|
END IF
|
|
END FOR
|
|
END FUNCTION
|
|
FUNCTION balanceTotalMes(hdepartamento)
|
|
DEFINE
|
|
balancem, totalG DEC(12, 2),
|
|
hdepartamento INT
|
|
LET totalG = 0
|
|
FOR i = 1 TO 12
|
|
|
|
SELECT a.fecha_inicio, a.fecha_corte
|
|
INTO fecha1, fecha2
|
|
FROM prdtable a
|
|
WHERE a.ano = pano AND a.mes = i
|
|
|
|
SELECT ISNULL(SUM(a.debito - a.credito), 0)
|
|
INTO balanceM
|
|
FROM cgtb00004 a
|
|
WHERE (a.cuenta_no LIKE '7%')
|
|
AND (a.fecha BETWEEN fecha1 AND fecha2)
|
|
AND (a.departamento = hdepartamento)
|
|
AND (a.ref = a.ref AND a.tipo <> 3)
|
|
AND a.ref NOT LIKE 'ED.99%' AND a.cuenta_no <> '7600'
|
|
AND a.status_t IS NULL
|
|
|
|
IF i = 1 THEN
|
|
LET data[idx].mes1 = balanceM
|
|
|
|
# DISPLAY "BALANCE ",BALANCE," DEP ",xdepartamento," CUENTA ",xcuenta_no," fechas ",fecha1," ",fecha2
|
|
END IF
|
|
|
|
IF i = 2 THEN
|
|
LET data[idx].mes2 = balanceM
|
|
|
|
END IF
|
|
IF i = 3 THEN
|
|
LET data[idx].mes3 = balanceM
|
|
|
|
END IF
|
|
IF i = 4 THEN
|
|
LET data[idx].mes4 = balanceM
|
|
END IF
|
|
IF i = 5 THEN
|
|
LET data[idx].mes5 = balanceM
|
|
END IF
|
|
IF i = 6 THEN
|
|
LET data[idx].mes6 = balanceM
|
|
END IF
|
|
IF i = 7 THEN
|
|
LET data[idx].mes7 = balanceM
|
|
END IF
|
|
IF i = 8 THEN
|
|
LET data[idx].mes8 = balanceM
|
|
END IF
|
|
IF i = 9 THEN
|
|
LET data[idx].mes9 = balanceM
|
|
END IF
|
|
IF i = 10 THEN
|
|
LET data[idx].mes10 = balanceM
|
|
END IF
|
|
IF i = 11 THEN
|
|
LET data[idx].mes11 = balanceM
|
|
END IF
|
|
IF i = 12 THEN
|
|
LET data[idx].mes12 = balanceM
|
|
END IF
|
|
LET totalG = totalG + balanceM
|
|
LET data[idx].total = totalG
|
|
END FOR
|
|
END FUNCTION
|
|
|
|
|
|
FUNCTION seleccionarMes(p_idx)
|
|
DEFINE p_idx INTEGER
|
|
DEFINE v_mes SMALLINT
|
|
DEFINE i SMALLINT
|
|
DEFINE l_nombre ARRAY[12] OF STRING
|
|
|
|
LET v_mes = 0
|
|
LET l_nombre[1] = "Enero"
|
|
LET l_nombre[2] = "Febrero"
|
|
LET l_nombre[3] = "Marzo"
|
|
LET l_nombre[4] = "Abril"
|
|
LET l_nombre[5] = "Mayo"
|
|
LET l_nombre[6] = "Junio"
|
|
LET l_nombre[7] = "Julio"
|
|
LET l_nombre[8] = "Agosto"
|
|
LET l_nombre[9] = "Septiembre"
|
|
LET l_nombre[10] = "Octubre"
|
|
LET l_nombre[11] = "Noviembre"
|
|
LET l_nombre[12] = "Diciembre"
|
|
|
|
MENU "Seleccione el mes a consultar"
|
|
BEFORE MENU
|
|
-- Oculta los meses sin valor en la fila seleccionada
|
|
IF data[p_idx].mes1 IS NULL OR data[p_idx].mes1 == 0 THEN
|
|
HIDE OPTION "m1"
|
|
END IF
|
|
IF data[p_idx].mes2 IS NULL OR data[p_idx].mes2 == 0 THEN
|
|
HIDE OPTION "m2"
|
|
END IF
|
|
IF data[p_idx].mes3 IS NULL OR data[p_idx].mes3 == 0 THEN
|
|
HIDE OPTION "m3"
|
|
END IF
|
|
IF data[p_idx].mes4 IS NULL OR data[p_idx].mes4 == 0 THEN
|
|
HIDE OPTION "m4"
|
|
END IF
|
|
IF data[p_idx].mes5 IS NULL OR data[p_idx].mes5 == 0 THEN
|
|
HIDE OPTION "m5"
|
|
END IF
|
|
IF data[p_idx].mes6 IS NULL OR data[p_idx].mes6 == 0 THEN
|
|
HIDE OPTION "m6"
|
|
END IF
|
|
IF data[p_idx].mes7 IS NULL OR data[p_idx].mes7 == 0 THEN
|
|
HIDE OPTION "m7"
|
|
END IF
|
|
IF data[p_idx].mes8 IS NULL OR data[p_idx].mes8 == 0 THEN
|
|
HIDE OPTION "m8"
|
|
END IF
|
|
IF data[p_idx].mes9 IS NULL OR data[p_idx].mes9 == 0 THEN
|
|
HIDE OPTION "m9"
|
|
END IF
|
|
IF data[p_idx].mes10 IS NULL OR data[p_idx].mes10 == 0 THEN
|
|
HIDE OPTION "m10"
|
|
END IF
|
|
IF data[p_idx].mes11 IS NULL OR data[p_idx].mes11 == 0 THEN
|
|
HIDE OPTION "m11"
|
|
END IF
|
|
IF data[p_idx].mes12 IS NULL OR data[p_idx].mes12 == 0 THEN
|
|
HIDE OPTION "m12"
|
|
END IF
|
|
|
|
ON ACTION m1 ATTRIBUTE(TEXT = l_nombre[1])
|
|
LET v_mes = 1
|
|
EXIT MENU
|
|
ON ACTION m2 ATTRIBUTE(TEXT = l_nombre[2])
|
|
LET v_mes = 2
|
|
EXIT MENU
|
|
ON ACTION m3 ATTRIBUTE(TEXT = l_nombre[3])
|
|
LET v_mes = 3
|
|
EXIT MENU
|
|
ON ACTION m4 ATTRIBUTE(TEXT = l_nombre[4])
|
|
LET v_mes = 4
|
|
EXIT MENU
|
|
ON ACTION m5 ATTRIBUTE(TEXT = l_nombre[5])
|
|
LET v_mes = 5
|
|
EXIT MENU
|
|
ON ACTION m6 ATTRIBUTE(TEXT = l_nombre[6])
|
|
LET v_mes = 6
|
|
EXIT MENU
|
|
ON ACTION m7 ATTRIBUTE(TEXT = l_nombre[7])
|
|
LET v_mes = 7
|
|
EXIT MENU
|
|
ON ACTION m8 ATTRIBUTE(TEXT = l_nombre[8])
|
|
LET v_mes = 8
|
|
EXIT MENU
|
|
ON ACTION m9 ATTRIBUTE(TEXT = l_nombre[9])
|
|
LET v_mes = 9
|
|
EXIT MENU
|
|
ON ACTION m10 ATTRIBUTE(TEXT = l_nombre[10])
|
|
LET v_mes = 10
|
|
EXIT MENU
|
|
ON ACTION m11 ATTRIBUTE(TEXT = l_nombre[11])
|
|
LET v_mes = 11
|
|
EXIT MENU
|
|
ON ACTION m12 ATTRIBUTE(TEXT = l_nombre[12])
|
|
LET v_mes = 12
|
|
EXIT MENU
|
|
COMMAND "Cancelar"
|
|
LET v_mes = 0
|
|
EXIT MENU
|
|
END MENU
|
|
|
|
RETURN v_mes
|
|
END FUNCTION
|