{
-------------------------------------------------------------------------------
PROGRAMA             :  CCPRRP016
OBJETIVO             :  Cuentas por Cobrar a mas de x dias X Vendedor 
PROGRAMADOR          :  Tadeo A. Ferreras 
FECHA REALIZACION    :  Abril 6, 1993 
-------------------------------------------------------------------------------
}
GLOBALS "ccprgb000.4gl"

        
    DEFINE fechaAV,fecha_corte DATE 
    DEFINE ck_dev INTEGER
    
    
    DEFINE cheque_dv CHAR(11),
           sbalance1,sbalance DECIMAL(12,2)

   
    DEFINE doccli16 RECORD
           tipo_cliente   SMALLINT,
           sec_cliente    SMALLINT,
           nombre         CHAR(30),
           aplica_a       INTEGER,
           pendiente      DECIMAL(12,2),
           fecha_factura  DATE,
           tipo_doc       CHAR(2),
           sec_vend       SMALLINT
    END RECORD



    DEFINE tot_gen2 RECORD
        total0   DECIMAL(10,2),
        total1   DECIMAL(10,2),
        total2   DECIMAL(10,2),
        total3   DECIMAL(10,2),
        total4   DECIMAL(10,2),
        total5   DECIMAL(10,2)
    END RECORD
    
 MAIN
 DEFER INTERRUPT
 CALL ARG_VAL(1) RETURNING usuarios
 CALL ARG_VAL(2) RETURNING clave
 CALL ARG_VAL(3) RETURNING impresor
 CALL ARG_VAL(7) RETURNING strconect
 
 CALL STARTLOG("CCRP16.TXT") 

 IF strconect = 'marmotech' THEN   
    CONNECT to strconect USER 'conecta' USING 'conecta'
 ELSE
  CONNECT to strconect USER  usuarios USING clave
 
 END IF   
 SELECT a.* INTO p_companias.* FROM companias a
   CALL defecto(usuarios,clave,impresor) RETURNING imprime,negrilla_on,negrillas_of,
                                                       doble_on,doble_off,comp_on,comp_off,
                                                       doce,normal,archivo,copia

 CALL ccprrp016()
END MAIN

FUNCTION ccprrp016()
DEFINE salir16 CHAR(1)

   #WHENEVER ERROR CONTINUE

    OPTIONS
       FORM LINE 8,
       ERROR LINE 23,
       COMMENT LINE 21

    OPEN WINDOW ccfmrp016 WITH FORM "ccfmrp016"
    
###### Captura de los valores que intervendran en la condicion de busqueda.
  LET pflag="N"
  INPUT BY NAME tipo_venta,fecha_corte
    ON KEY(F2)
        LET pflag='I'
        
        AFTER FIELD tipo_venta
              IF tipo_venta is null THEN
                 LET numero_msg = 16
                 CALL msg(numero_msg)
                 NEXT FIELD tipo_venta 
              END IF

              IF tipo_venta = "1" OR tipo_venta = "2" THEN
                 DISPLAY BY NAME tipo_venta
              ELSE
                 LET numero_msg = 7 
                 CALL msg(numero_msg)
                 NEXT FIELD tipo_venta 
              END IF
              
        BEFORE FIELD fecha_corte
               LET fecha_corte = today

        AFTER FIELD fecha_corte 
              IF fecha_corte is null THEN  
                 LET numero_msg = 16
                 CALL msg(numero_msg)
                 NEXT FIELD fecha_corte 
              END IF
    
        AFTER INPUT 

##### Creando la facilidad para poder cancelar el proceso mediante la tecla
##### DELETE o SUPR.

              IF int_flag THEN
                 LET numero_msg = 2
                 CALL msg(numero_msg)
                 LET int_flag = false
                 RETURN
              END IF

              IF tipo_venta is null THEN
                 LET numero_msg = 16
                 CALL msg(numero_msg)
                 NEXT FIELD tipo_venta 
              END IF

              IF tipo_venta = "1" OR tipo_venta = "2" THEN
                 DISPLAY BY NAME tipo_venta
              ELSE
                 LET numero_msg = 7 
                 CALL msg(numero_msg)
                 NEXT FIELD tipo_venta 
              END IF
              
              IF fecha_corte is null THEN  
                 LET numero_msg = 16
                 CALL msg(numero_msg)
                 NEXT FIELD fecha_corte 
              END IF
    
       EXIT INPUT
   END INPUT

##### Crea criterio de busqueda

 ##### Variables que intervienen en la condicion de busqueda, teniendo estas
 ##### la ventaja de que se pueden dejar en blanco en caso de que desee todos
 ##### los registro con ese campo.

  CONSTRUCT BY NAME criterio ON c.sec_vend,a.tipo_cliente,a.sec_cliente
   AFTER CONSTRUCT  
  IF int_flag THEN
     LET numero_msg = 2
     CALL msg(numero_msg)
     LET int_flag = FALSE
     RETURN
  END IF
 END CONSTRUCT
 DISPLAY BY NAME pflag 
# Busca pendiente de las facturas cuyas fechas de vencimiento son menores 
# a la fecha de corte  
         LET selec3 = 
             "SELECT a.tipo_cliente,a.sec_cliente,b.nombre,a.aplica_a, ",
             "       SUM(a.valor+a.monto_desc) ",
             "FROM cctb00001 a,vetb00004 b,vetb00028 c ",
             "WHERE a.fecha_orig <= ? AND ",criterio CLIPPED,
             "  AND a.tipo_cliente=b.tipo_cliente AND a.sec_cliente=b.sec_cliente AND ",
             "  a.tipo_cliente = c.tipo_cliente AND ",
             "  a.sec_cliente = c.sec_cliente AND ",
             "      a.sec_cliente=a.sec_cliente AND a.tipo_doc !='PC' AND a.num_doc IS NOT NULL ",
             "  AND a.status_t IS NULL ",
             "GROUP BY a.tipo_cliente,a.sec_cliente,b.nombre,a.aplica_a   HAVING SUM(a.valor + a.monto_desc) <> 0 ORDER BY a.tipo_cliente,a.sec_cliente,a.aplica_a"

      
       DISPLAY "<< Buscando Informacion ... Espere Por Favor. >>" AT 19,14
           ATTRIBUTE (REVERSE,BOLD)

   
     
       CALL seleccionarSalida() RETURNING r_output
       CALL configureoutput(r_output) RETURNING handler
       
       START REPORT reporte016 TO XML HANDLER HANDLER
       
       LET progreso = 20    
       DISPLAY progreso TO progreso 
       CALL ui.Interface.refresh()

   PREPARE busca_pendiente FROM selec3 
   DECLARE ft_pendiente CURSOR FOR busca_pendiente      
   OPEN ft_pendiente USING fecha_corte

   FOREACH  ft_pendiente INTO doccli16.*


    LET doccli16.sec_vend = NULL
    SELECT UNIQUE a.sec_vend INTO doccli16.sec_vend
    FROM vetb00028 a
    WHERE a.tipo_cliente = doccli16.tipo_cliente and
          a.sec_cliente = doccli16.sec_cliente AND a.status_t IS NULL

    IF doccli16.sec_vend IS NULL THEN
       LET doccli16.sec_vend = 0
    END IF


    SELECT MAX( a.tipo_doc),CONVERT(CHAR(10),MAX(a.fecha_ven),103)
    INTO doccli16.tipo_doc,doccli16.fecha_factura
    FROM cctb00001 a
    WHERE a.status_t IS NULL AND a.num_doc = a.aplica_a AND 
          a.num_doc=doccli16.aplica_a AND a.tipo_doc not in ("PG","NC","PC","OC","DV") and
          a.tipo_cliente=doccli16.tipo_cliente AND 
          a.sec_cliente=doccli16.sec_cliente
    IF doccli16.fecha_factura IS NULL THEN
    
    SELECT CONVERT(CHAR(10),MAX(a.fecha_orig),103)
    INTO doccli16.fecha_factura
    FROM cctb00001 a
    WHERE a.status_t IS NULL AND 
          a.aplica_a=doccli16.aplica_a AND a.tipo_doc  in ("PG","NC","PC","OC","DV","ND") and
          a.tipo_cliente=doccli16.tipo_cliente AND 
          a.sec_cliente=doccli16.sec_cliente
    END IF
    
    IF doccli16.tipo_doc = "AV" THEN
       SELECT CONVERT(CHAR(10),a.fecha_orig,103) INTO fechaAV
       FROM cctb00001 a
       WHERE a.num_doc = doccli16.aplica_a AND 
             a.tipo_cliente=doccli16.tipo_cliente 
         AND a.sec_cliente=doccli16.sec_cliente AND 
             a.num_doc = a.aplica_a AND a.tipo_Doc = 'AV' AND a.status_t IS NULL

       SELECT SUM(a.valor+a.monto_desc)*-1 INTO doccli16.pendiente     
       FROM cctb00001 a
       WHERE a.fecha_orig<=fecha_corte AND 
             a.tipo_cliente=doccli16.tipo_cliente 
         AND a.sec_cliente=doccli16.sec_cliente AND a.status_t IS NULL AND 
             a.num_doc = doccli16.aplica_a AND a.fecha_orig >= fechaav
         LET doccli16.fecha_factura=fechaAV    
    END IF
    
# BUSCA BALANCE DEL CLIENTE PARA IMPRIMIRLO
    SELECT SUM(a.valor+a.monto_desc) INTO sbalance
    FROM cctb00001 a
    WHERE a.tipo_cliente = doccli16.tipo_cliente AND
          a.sec_cliente = doccli16.sec_cliente AND
          a.fecha_orig <= fecha_corte AND
          a.tipo_doc NOT IN ("AV","PC") AND
          a.status_t IS NULL

    SELECT SUM(a.valor+a.monto_desc) INTO sbalance1
    FROM cctb00001 a
    WHERE a.tipo_cliente = doccli16.tipo_cliente AND
          a.sec_cliente = doccli16.sec_cliente AND
          a.fecha_orig <= fecha_corte AND
          a.tipo_doc IN ("AV") AND
          a.status_t IS NULL
    IF sbalance IS NULL THEN
       LET sbalance = 0
    END IF
    IF sbalance1 IS NULL THEN
       LET sbalance1 = 0
    END IF
    LET sbalance = sbalance - sbalance1

    IF sbalance != 0 THEN
    IF doccli16.pendiente <> 0 THEN
       LET progreso = progreso + 2
         IF progreso > 95 THEN
            LET progreso = 95
         END IF
         CURRENT WINDOW IS ccfmrp016
        DISPLAY progreso TO progreso
        CALL ui.Interface.refresh()
       OUTPUT TO REPORT reporte016(doccli16.*)
    END IF
    END IF
 END FOREACH
 FINISH REPORT reporte016 
 LET progreso = 100
 DISPLAY progreso TO progreso
 CALL ui.Interface.refresh()
 
 CLOSE WINDOW ccfmrp016
END FUNCTION

 ###### Funcion que genera el reporte

 REPORT reporte016(x)
 DEFINE x RECORD
        tipo_cliente  SMALLINT,
        sec_cliente   SMALLINT,
        nombre        CHAR(30),
        aplica_a      INTEGER,
        pendiente     DECIMAL(12,2),
        fecha_factura DATE,
        tipo_doc      CHAR(2),
        sec_vend      SMALLINT
 END RECORD


    DEFINE nom1,apell1 CHAR(25)
    DEFINE doble_on CHAR(2)
    DEFINE doble_off CHAR(2)
    DEFINE negrillas_on CHAR(6)
    DEFINE negrillas_off CHAR(6)
    DEFINE comp_on CHAR(2)
    DEFINE comp_off CHAR(2) 
    DEFINE doce CHAR(2)
    DEFINE normal CHAR(2) 
    DEFINE hora CHAR(5)
    DEFINE fecha_factura DATE 
    DEFINE de1a30, de31a45, de46a60, masde60, total_saldo DECIMAL(12,2) 
    DEFINE tcte,t30, t45, t60, tm60, tsaldo DECIMAL(12,2) 
    DEFINE ttcte,tt30, tt45, tt60, ttm60, ttsaldo DECIMAL(12,2) 
    DEFINE tttcte,ttt30, ttt45, ttt60, tttm60, tttsaldo DECIMAL(12,2) 
    DEFINE dias INTEGER 

    OUTPUT
     TOP MARGIN 0
     LEFT MARGIN 0
     BOTTOM MARGIN 2
# ORDER BY x.sec_vend,x.sec_cliente,x.fecha_factura 

    FORMAT
      PAGE HEADER
        IF r_output = "MA" THEN
           LET doble_on =  ASCII 14
           LET doble_off = ASCII 20
           LET negrillas_on = ASCII 27, ASCII 69
           LET negrillas_off = ASCII 27, ASCII 70
           LET comp_on = ASCII 15
           LET comp_off = ASCII 18
           LET doce = ASCII 27, ASCII 77  
           LET normal = ASCII 27, ASCII 80
         END IF  
           LET hora = time

           LET lj = (150 - LENGTH(p_companias.nombre CLIPPED))/2
           PRINT COLUMN   1, comp_on
           PRINT COLUMN   1, "ccprrp016",
                 COLUMN  lj, p_companias.nombre CLIPPED,
                 COLUMN 134, "Pag. ",pageno using "###"
           PRINT COLUMN  60, "Sistema de Cuentas por Cobrar",
                 COLUMN 134, today using "dd/mm/yyyy"

           PRINT COLUMN  48, "Partidas Pendientes de Cobro Por Vendedor al " , 
                             fecha_corte using "dd/mm/yyyy",
                 COLUMN 134, hora

### Dependiendo del tipo de venta imprimira el simbolo de la moneda de pago

           IF tipo_venta = "1" THEN
             PRINT COLUMN 64, "Valores en RD$"
           ELSE
             PRINT COLUMN 64, "Valores en US$"
           END IF

          SKIP 1 LINES

          PRINT COLUMN 1, "--------------------------------------------------",
                          "--------------------------------------------------",
                          "---------------------------------"
                          

           PRINT COLUMN  53, "Fecha"  
           PRINT COLUMN   2, "C l i e n t e",
                 COLUMN  45, "Factura",
                 COLUMN  53, "Vence", 
                 COLUMN  66, "Corriente",
                 COLUMN  82, "De 1 a 30", 
                 COLUMN  98, "De 31 a 60", 
                 COLUMN 114, "De 61 a 90", 
                 COLUMN 129, "Mas de 91", 
                 COLUMN 142, "Total"   

          PRINT COLUMN 1, "--------------------------------------------------",
                          "--------------------------------------------------",
                          "---------------------------------"
                          
      

      BEFORE GROUP OF x.sec_vend

##### Variables que acumulan totales por condicion de pago
        LET ttcte = 0
        LET tt30 = 0
        LET tt45 = 0
        LET tt60 = 0
        LET ttm60 = 0
        LET ttsaldo = 0

##### Seleciona datos referentes al vendedor en la maestra de empleado
        SELECT UNIQUE nom1_emp,apell1_emp INTO nom1,apell1 FROM adtb00003
        WHERE num_emp = x.sec_vend

        IF status = notfound THEN
           LET status = 0
           LET nom1 = "Vendedor no Existe"
        END IF

        SKIP 1 LINE
          
          PRINT COLUMN 1,x.sec_vend USING "&&&&"," ",
                         nom1 clipped," ",apell1 
          
      BEFORE GROUP OF x.sec_cliente 
        LET existe = "N" 
        LET descrip1 = NULL 
        LET tcte = 0
        LET t30 = 0
        LET t45 = 0
        LET t60 = 0
        LET tm60 = 0
        LET tsaldo = 0
        LET idx = 0

        PRINT COLUMN   1, x.tipo_cliente using "&&", "-",
                          x.sec_cliente using "&&&&&&", " ", 
                          x.nombre  

      ON EVERY ROW
         IF ttt30 IS NULL THEN
            LET ttt30 = 0
         END IF

         IF ttt45 IS NULL THEN
            LET ttt45 = 0
         END IF
         IF tttcte IS NULL THEN
            LET tttcte = 0
         END IF
         IF ttt60 IS NULL THEN
            LET ttt60 = 0
         END IF

         IF tttm60 IS NULL THEN
            LET tttm60 = 0
         END IF

         IF tttsaldo IS NULL THEN
            LET tttsaldo = 0
         END IF

        LET ck_dev = null
        SELECT MAX(a.valor_cheque) INTO ck_dev FROM cctb00001 a
        WHERE a.tipo_cliente=x.tipo_cliente AND a.sec_cliente=x.sec_cliente AND
              a.aplica_a = x.aplica_a AND a.tipo_doc = "ND"

         IF ck_dev IS NOT NULL OR ck_dev <> 0 THEN
            LET cheque_dv = "CD-",ck_dev USING "<<<<<<<"
         ELSE
            LET cheque_dv = NULL 
         END IF

         IF x.fecha_factura IS NULL THEN
            LET x.fecha_factura = 0
         END IF

  #### Calcula la diferencia de dias de pago a determinada factura para acu-
  #### mularlo en la variable correspondiente por tipo de dia.

         LET dias = fecha_corte - x.fecha_factura
         
  #### Imprime y acumula los valores correspondientes a pago realizados en
  #### menos de 31 dias.

               PRINT COLUMN  33, cheque_dv CLIPPED,
                     COLUMN  44, x.tipo_doc,"-",x.aplica_a using "&&&&&&"," ",
                     COLUMN  54, x.fecha_factura using "dd/mm/yyyy";

         IF dias < 0 THEN
            LET tcte = tcte + x.pendiente
            PRINT COLUMN  56, x.pendiente using "(((,(((,(((.&&)",
                  COLUMN 135, x.pendiente using "(((,(((,(((.&&)" 

         END IF

         IF dias >= 0 AND dias < 30 AND x.pendiente <> 0 THEN
            LET t30 = t30 + x.pendiente 
            PRINT COLUMN  72, x.pendiente using "(((,(((,(((.&&)",
                  COLUMN 135, x.pendiente using "(((,(((,(((.&&)" 
         END IF

  #### Imprime y acumula los valores correspondientes a pago realizados en
  #### mas de 30 y menos de 45 dias.

         IF dias >= 30 AND dias < 60 AND x.pendiente <> 0 THEN
            LET t45 = t45 + x.pendiente 
            PRINT COLUMN  88, x.pendiente using "(((,(((,(((.&&)",
                  COLUMN 135, x.pendiente using "(((,(((,(((.&&)" 
         END IF

  #### Imprime y acumula los valores correspondientes a pago realizados en
  #### mas de 46 y menos de 60 dias.

         IF dias >= 60 AND dias < 90 AND x.pendiente <> 0 THEN
            LET t60 = t60 + x.pendiente 
            PRINT COLUMN 103, x.pendiente using "(((,(((,(((.&&)",
                  COLUMN 135, x.pendiente using "(((,(((,(((.&&)" 
         END IF

  #### Imprime y acumula los valores correspondientes a pago realizados en
  #### mas de 60 dias.
         IF dias >= 90 AND x.pendiente <> 0 THEN
            LET tm60 = tm60 + x.pendiente 
            PRINT COLUMN 119, x.pendiente using "(((,(((,(((.&&)",
                  COLUMN 135, x.pendiente using "(((,(((,(((.&&)" 
         END IF

      AFTER GROUP OF x.sec_cliente 
  ######## IMPRIME totales acumulado por cliente.

            LET tsaldo = 0
            LET tsaldo = tcte + t30 + t45 +t60 + tm60

            SKIP 1 LINE 
   
            PRINT COLUMN  41, "Total Cliente :", 
                  COLUMN  56, tcte USING "(((,(((,(((.&&)",
                  COLUMN  72, t30 using "(((,(((,(((.&&)",
                  COLUMN  88, t45 using "(((,(((,(((.&&)",
                  COLUMN 103, t60 using "(((,(((,(((.&&)",
                  COLUMN 119, tm60 using "(((,(((,(((.&&)",
                  COLUMN 133, tsaldo using "(((,(((,(((.&&)"

            PRINT COLUMN  56, "===============",
                  COLUMN  72, "===============",
                  COLUMN  88, "===============",
                  COLUMN 103, "===============",
                  COLUMN 119, "===============",
                  COLUMN 133, "===============" 

            SKIP 1 LINE 
            LET ttcte = ttcte + tcte
            LET tt30 = tt30 + t30 
            LET tt45 = tt45 + t45 
            LET tt60 = tt60 + t60 
            LET ttm60 = ttm60 + tm60
            LET ttsaldo = ttsaldo + tsaldo

            LET tttcte = tttcte + ttcte
            LET ttt30 = ttt30 + t30 
            LET ttt45 = ttt45 + t45 
            LET ttt60 = ttt60 + t60 
            LET tttm60 = tttm60 + tm60
            LET tttsaldo = tttsaldo + tsaldo

            LET tcte = 0
            LET t30  = 0
            LET t45 = 0
            LET t60 = 0
            LET tm60 = 0 
            LET tsaldo = 0 

     AFTER GROUP OF x.sec_vend

  ######## IMPRIME totales acumulado por vendedor.

         PRINT COLUMN   1, negrillas_on
         PRINT COLUMN  18, "Total Vendedor-->", 
               COLUMN  56, ttcte USING "((,(((,(((.&&)",
               COLUMN  72, tt30 using "((,(((,(((.&&)",
               COLUMN  88, tt45 using "((,(((,(((.&&)",
               COLUMN 103, tt60 using "((,(((,(((.&&)",
               COLUMN 119, ttm60 using "((,(((,(((.&&)",
               COLUMN 133, ttsaldo using "(((,(((,(((.&&)"

         PRINT COLUMN  56, "===============",
               COLUMN  72, "===============",
               COLUMN  88, "===============",
               COLUMN 103, "===============",
               COLUMN 119, "===============",
               COLUMN 133, "==============="

            LET tt30  = 0
            LET tt45 = 0
            LET tt60 = 0
            LET ttm60 = 0 
            LET ttsaldo = 0 
      SKIP TO TOP OF PAGE  
      ON LAST ROW  

  ######## IMPRIME totales acumulado en general.

         PRINT COLUMN   1, negrillas_on 
         PRINT COLUMN  18, "Total General -->", 
               COLUMN  56, tttcte USING "(((,(((,(((.&&)",
               COLUMN  72, ttt30 using "(((,(((,(((.&&)",
               COLUMN  88, ttt45 using "(((,(((,(((.&&)",
               COLUMN 103, ttt60 using "(((,(((,(((.&&)",
               COLUMN 119, tttm60 using "(((,(((,(((.&&)",
               COLUMN 133, tttsaldo using "((((,(((,(((.&&)"

               LET tot_gen2.total0 = tttcte
               LET tot_gen2.total1 = ttt30
               LET tot_gen2.total2 = ttt45
               LET tot_gen2.total3 = ttt60
               LET tot_gen2.total4 = tttm60
               LET tot_gen2.total5 = tttsaldo

         PRINT COLUMN  56, "===============",
               COLUMN  72, "===============",
               COLUMN  88, "===============",
               COLUMN 103, "===============",
               COLUMN 119, "===============",
               COLUMN 133, "==============="


               LET tttcte = 0
               LET ttt30 = 0
               LET ttt45 = 0
               LET ttt60 = 0
               LET tttm60 = 0
               LET tttsaldo = 0

               LET tt30 = 0
               LET tt45 = 0
               LET tt60 = 0
               LET ttm60 = 0
               LET ttsaldo = 0

               LET t30 = 0
               LET t45 = 0
               LET t60 = 0
               LET tm60 = 0
               LET tsaldo = 0

     END REPORT