{
######################################################################
 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