Reporte movimientos de cuentas x cobrar SAE Firebird

Anteriormente vimos el reporte más simple, solo un catálogo de clientes. Qué pasaría si quisiéramos ver los movimientos a de nuestras cuentas x cobrar, es decir, una vez generada la remisión o factura, todos los movimientos que afecten a esa factura. El reporte tendrá un rango de fechas para que el usuario pueda consultar exactamente el periodo que a el le importe y adicionalmente tenemos 4 empresas por lo que el reporte debe ser capaz de consultar cualquier empresa que le dicte el usuario.

Para esto será imprescindible haber leído el artículo Mi primer reporte en Excel http://www.cyberadmin.com.mx/portal/2-uncategorised/2-mi-primer-reporte-en-excel-explotando-base-firebird , Adicionalmente, debido a que el reporte soporta hasta 4 empresas, deberás crear tu al menos 2 ODBC´s, cada una apuntando a una empresa de SAE.

Seguir todos los pasos que se indican en mi primer reporte en Excel, solo que en la función deberán poner en el contenido lo siguiente

 

    Dim Var1 As String
    Dim Var2 As String
    Dim Var3 As String
    Dim CodEmpresa As Long
    Dim Empresa10 As String
    Var1 = Sheets(1).Cells(1, 2).Value
    Var2 = Sheets(1).Cells(2, 2).Value
    Var3 = Sheets(1).Cells(3, 2).Value
   
    If Len(Var1) <> 8 Or Len(Var2) <> 8 Then
        MsgBox "La fecha debe tener format AAAAMMDD"
        Exit Sub
    End If
   
    If Var3 <> "1" And Var3 <> "6" And Var3 <> "7" And Var3 <> "3" Then
        MsgBox "Debes seleccionar una empresa válida"
        Exit Sub
    End If
   
    Empresa10 = Format(Var3, "00")
   
    Var1 = Left(Var1, 4) & "-" & Left(Right(Var1, 4), 2) & "-" & Right(Var1, 2)
    Var2 = Left(Var2, 4) & "-" & Left(Right(Var2, 4), 2) & "-" & Right(Var2, 2)
    
    Sheets(2).Select
    Cells.Select
    On Error Resume Next
    Selection.ListObject.QueryTable.Delete
    On Error GoTo 0
    Selection.ClearContents
       

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("ODBC;DSN=SAE" & Empresa10), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array("select cc.descr, c.nombre as Cliente, dc.cve_clie, dc.no_factura, dc.docto, dc.importe, dc.fecha_apli, dc.fechaelab, ", _
    "coalesce(oc.str_obs, '') as observaciones,  dc.strcvevend ", _
    "from cuen_det" & Empresa10 & " dc ", _
    "inner join clie" & Empresa10 & " c on dc.cve_clie = c.clave ", _
    "inner join conc" & Empresa10 & " cc on dc.num_cpto = cc.num_cpto ", _
    "left join ocuen" & Empresa10 & " oc on oc.cve_obs = dc.cve_obs ", _
    "where fecha_apli between '" & Var1 & "' and '" & Var2 & "'")

.Refresh BackgroundQuery:=False
    End With

Así mismo, deberán poner las celdas y el botón que ya tenían de la hoja 1 como se muestra

Ustedes deberán reemplazar los códigos de empresas y nombres para que sus usuarios puedan trabajar sin problemas

Veremos el código nuevo paso a paso

  

Declaramos 3 variables Var1, 2 y 3 donde guardaremos en Var1 la fecha inicial, Var2 la Fecha final y Var3 el código de empresa, crearemos la variable CodEmpresa para garantizar que sea un entero y Empresa10 tendrá el código de la empresa a 2 decimales que ocuparemos para conectarnos a nuestro ODBC y a los nombres de tablas.

    Dim Var1 As String
    Dim Var2 As String
    Dim Var3 As String
    Dim CodEmpresa As Long
    Dim Empresa10 As String

 

Como se puso anteriormente, obtenemos las variables de las celdas correspondientes.
   

    Var1 = Sheets(1).Cells(1, 2).Value
    Var2 = Sheets(1).Cells(2, 2).Value
    Var3 = Sheets(1).Cells(3, 2).Value
   
Validamos que la fecha de inicio y de fin sea de 8 caracteres como se pide en el Excel.
    If Len(Var1) <> 8 Or Len(Var2) <> 8 Then
        MsgBox "La fecha debe tener format AAAAMMDD"
        Exit Sub
    End If
   
Validamos que los códigos de las empresas sean los que tenemos en nuestro sistema IMPORTANTE: Aquí ustedes deberá reemplazar por los códigos de empresas que tengan.
    If Var3 <> "1" And Var3 <> "6" And Var3 <> "7" And Var3 <> "3" Then
        MsgBox "Debes seleccionar una empresa válida"
        Exit Sub
    End If
   

Obtenemos el código de empresa a 2 decimales

   Empresa10 = Format(Var3, "00")
   

Obtenemos la fecha inicial y final en el formato que soporta Firebird, es decir, 4 dígitos para el año + un guion medio + 2 dígitos para el mes + otro guion medio y finalmente 2 dígitos para el día


    Var1 = Left(Var1, 4) & "-" & Left(Right(Var1, 4), 2) & "-" & Right(Var1, 2)
    Var2 = Left(Var2, 4) & "-" & Left(Right(Var2, 4), 2) & "-" & Right(Var2, 2)

 

La parte donde se hace el query también tuvo modificaciones; veamoslas.

Al conectarse a la ODBC, estoy poniendo el DSN con un prefijo SAE y luego le concateno el código de la empresa a 2 posiciones, es por eso que cuando creemos las ODBC´s, es necesario ser ordenado con SAE01, SAE02, SAE03, etc. Así mismo, al momento de hacer la liga de las tablas y para asegurar que funcione con cualquier empresa que pongamos, le quitamos el número de la empresa que normalmente usaríamos y en vez de eso le concatenamos el código de la empresa a 2 posiciones, si el usuario pone empresa 1, cuando consulte clientes será CLIE01, si el usuario pone la empresa 6, la tabla consultada será CLIE06. Finalmente, en la consulta agregamos la condicionante que puso el usuario en las fechas inicial y final; en mi caso utilicé fecha_apli, pero también puede utilizarse fecha_elab o fecha_venc.

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array("ODBC;DSN=SAE" & Empresa10), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array("select cc.descr, c.nombre as Cliente, dc.cve_clie, dc.no_factura, dc.docto, dc.importe, dc.fecha_apli, dc.fechaelab, ", _
    "coalesce(oc.str_obs, '') as observaciones,  dc.strcvevend ", _
    "from cuen_det" & Empresa10 & " dc ", _
    "inner join clie" & Empresa10 & " c on dc.cve_clie = c.clave ", _
    "inner join conc" & Empresa10 & " cc on dc.num_cpto = cc.num_cpto ", _
    "left join ocuen" & Empresa10 & " oc on oc.cve_obs = dc.cve_obs ", _
    "where fecha_apli between '" & Var1 & "' and '" & Var2 & "'")

.Refresh BackgroundQuery:=False
    End With

 

El resto es historia. Si se perdieron en algo, pueden descargar el archivo con macros desde http://www.cyberadmin.com.mx/portal/achivos/foro/reportes/Movimientos de cuentas x cobrar.xlsm

Pueden agregar tantas condiciones como ustedes quieran, filtrar por clientes, por montos, tipo de movimientos, etc. Nos leemos luego.

 

 

 

 

 

Comentarios   

0 #9 EDER VELAZQUEZ 21-08-2019 16:38
Buena día, estoy intentando hacer una consulta de pedidos, requiero que me traiga las partidas de un pedido en especifico, y que el reporte tenga la siguiente información

Clave
Cantidad
Descripción
Linea


Dado que yo posteriormente de obtener esa información requerimos realizar subtotales de cantidades y saltos de pagina cada que termine de listarme todos los productos de una linea,

Esto ya lo logre con una macros pero requiero exportar infomación antes, lo cual es uy tedioso y lo que no puedo es obtener la información directamente de la BD de SAE, que me facilitaria el obtener la infoación solicitada
0 #8 Super User 16-01-2018 15:06
Cito a ALBERTO SANCHEZ:
Hola, Muy bueno tu aporte.

Espero puedas aclararme una duda, si quiero hacer una tabla con datos de dos conusltas, como seria el codigo?

Hola Alberto.

En SQL, la sintaxis sería como

select * from tabla1 t1 inner join tabla2 t2 on t1.id = t2.id
where t1.fecha >= '20180101'
into tabla3

Es decir, fusiona el contenido de 2 tablas y las mete en tabla3, sin embargo firebird no soporta esta sintaxis. Lo que puedes hacer es utilizar la sintaxis create table para que una vez creada tu tercera tabla, utilices una sintaxis como la que sigue

insert into tabla3
select * from tabla1 t1 inner join tabla2 t2 on t1.id = t2.id
where t1.fecha >= '20180101'
0 #7 ALBERTO SANCHEZ 13-01-2018 08:20
Hola, Muy bueno tu aporte.

Espero puedas aclararme una duda, si quiero hacer una tabla con datos de dos conusltas, como seria el codigo?
0 #6 Super User 09-06-2017 19:59
Cito a Abraham LM:
Hola, que tal, tengo la necesidad urgente de tener una herramienta en excel que me permita conocer la antiguedad de saldos de clientes de SAE 6.0 con opción para elegir la fecha de corte, esto ya que el reporte QR2 que entrega ASPEL a guardarlo con formato de excel es un desastre y lo ocupo para un análisis posterior en excel, podrías cotizármelo e indicarme tiempos de entrega, de antemano gracias


Hola, Arriba te pongo el ejemplo del reporte y todo lo que necesitas para hacerlo de forma autosuficiente. Si aún con esto necesitas ayuda, yo te lo cotizo en $1000 y en un dia tienes el funcionamiento
0 #5 Abraham LM 09-06-2017 19:22
Hola, que tal, tengo la necesidad urgente de tener una herramienta en excel que me permita conocer la antiguedad de saldos de clientes de SAE 6.0 con opción para elegir la fecha de corte, esto ya que el reporte QR2 que entrega ASPEL a guardarlo con formato de excel es un desastre y lo ocupo para un análisis posterior en excel, podrías cotizármelo e indicarme tiempos de entrega, de antemano gracias
0 #4 Super User 18-05-2017 22:17
Alberto, a primera vista no veo nada mal de performance en tu query pues buscas solamente productos dados de baja del almacén 1 (y solo si está dado de baja). Tendrías que tener demasiados registros o un performance del servidor bastante bajo para que esto sucediera. Lo primero lo puedes atacar con una migración a SQL Server y tunnear la base de daatos lo cual es un proceso sencillo si sigues los concejos de optimización que ya trae incorporada la base de datos.

Ahora bien, veo que tu query tiene errores de lógica los cuales te enumero.

1. SELECT EXTRACT(MONTH FROM CURRENT_DATE-2) y SELECT EXTRACT(MONTH FROM CURRENT_DATE-1) te dan el mes del dia de ayer y de antier respectivamente y no precisamente el mes pasado y antepasdo
2. Suponiendo que solucionas el problema número 1, estás pensando que el año actual sería siempre igual que el año del mes anterior (Lo cual no es válido en enero)
3. Las piezas facturadas no están validando las facturas canceladas asi como las notas de crédito solicitadas.

Cito a Alberto:
Esta seria el query:

SELECT
V1.CAMPLIB2 AS MARCA, V.LIN_PROD AS LINEA, V.CVE_ART AS CLAVE, V.DESCR AS DESCRIPCION, M.STOCK_MIN AS MINIMO, M.STOCK_MAX AS MAXIMO, V.ULT_COSTO AS ULTIMO_COSTO,
V.COSTO_PROM AS COSTO_PROM, V.VTAS_ANL_C AS VENTA_ANUAL, M.EXIST AS EXISTENCIA, V.FCH_ULTCOM AS FECHA_ULT_COMP, V.FCH_ULTVTA AS FECHA_ULT_VTA,
V.COMP_X_REC AS PENDxREC, V.PEND_SURT AS PEND_SURT, V1.CAMPLIB1 AS CLASE, V1.CAMPLIB5 AS PRECIO_PROVEEDOR,
(select SUM(PF.CANT) from FACTF01 F LEFT OUTER JOIN PAR_FACTF01 PF ON F.CVE_DOC=PF.CVE_DOC
WHERE EXTRACT(MONTH FROM F.FECHA_DOC)=(SELECT EXTRACT(MONTH FROM CURRENT_DATE-2) AS FECHA FROM RDB$DATABASE)
AND EXTRACT(YEAR FROM F.FECHA_DOC)=(SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND PF.CVE_ART=V.CVE_ART) AS TERCER_MES_ANTERIOR,
(select SUM(PF.CANT) from FACTF01 F LEFT OUTER JOIN PAR_FACTF01 PF ON F.CVE_DOC=PF.CVE_DOC
WHERE EXTRACT(MONTH FROM F.FECHA_DOC)=(SELECT EXTRACT(MONTH FROM CURRENT_DATE-1) AS FECHA FROM RDB$DATABASE)
AND EXTRACT(YEAR FROM F.FECHA_DOC)=(SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND PF.CVE_ART=V.CVE_ART) AS MES_ANTERIOR,
(select SUM(PF.CANT) from FACTF01 F LEFT OUTER JOIN PAR_FACTF01 PF ON F.CVE_DOC=PF.CVE_DOC
WHERE EXTRACT(MONTH FROM F.FECHA_DOC)=(SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND EXTRACT(YEAR FROM F.FECHA_DOC)=(SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND PF.CVE_ART=V.CVE_ART) AS MES_ACTUAL
FROM INVE01 V
LEFT OUTER JOIN INVE_CLIB01 V1 ON V.CVE_ART = V1.CVE_PROD
LEFT OUTER JOIN MULT01 M ON V.CVE_ART=M.CVE_ART
WHERE V.STATUS 'B' AND M.STATUS 'B' AND M.CVE_ALM='1' ORDER BY V1.CAMPLIB2 DESC
0 #3 Alberto 18-05-2017 17:23
Esta seria el query:

SELECT
V1.CAMPLIB2 AS MARCA, V.LIN_PROD AS LINEA, V.CVE_ART AS CLAVE, V.DESCR AS DESCRIPCION, M.STOCK_MIN AS MINIMO, M.STOCK_MAX AS MAXIMO, V.ULT_COSTO AS ULTIMO_COSTO,
V.COSTO_PROM AS COSTO_PROM, V.VTAS_ANL_C AS VENTA_ANUAL, M.EXIST AS EXISTENCIA, V.FCH_ULTCOM AS FECHA_ULT_COMP, V.FCH_ULTVTA AS FECHA_ULT_VTA,
V.COMP_X_REC AS PENDxREC, V.PEND_SURT AS PEND_SURT, V1.CAMPLIB1 AS CLASE, V1.CAMPLIB5 AS PRECIO_PROVEEDOR,
(select SUM(PF.CANT) from FACTF01 F LEFT OUTER JOIN PAR_FACTF01 PF ON F.CVE_DOC=PF.CVE_DOC
WHERE EXTRACT(MONTH FROM F.FECHA_DOC)=(SELECT EXTRACT(MONTH FROM CURRENT_DATE-2) AS FECHA FROM RDB$DATABASE)
AND EXTRACT(YEAR FROM F.FECHA_DOC)=(SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND PF.CVE_ART=V.CVE_ART) AS TERCER_MES_ANTERIOR,
(select SUM(PF.CANT) from FACTF01 F LEFT OUTER JOIN PAR_FACTF01 PF ON F.CVE_DOC=PF.CVE_DOC
WHERE EXTRACT(MONTH FROM F.FECHA_DOC)=(SELECT EXTRACT(MONTH FROM CURRENT_DATE-1) AS FECHA FROM RDB$DATABASE)
AND EXTRACT(YEAR FROM F.FECHA_DOC)=(SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND PF.CVE_ART=V.CVE_ART) AS MES_ANTERIOR,
(select SUM(PF.CANT) from FACTF01 F LEFT OUTER JOIN PAR_FACTF01 PF ON F.CVE_DOC=PF.CVE_DOC
WHERE EXTRACT(MONTH FROM F.FECHA_DOC)=(SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND EXTRACT(YEAR FROM F.FECHA_DOC)=(SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS FECHA FROM RDB$DATABASE)
AND PF.CVE_ART=V.CVE_ART) AS MES_ACTUAL
FROM INVE01 V
LEFT OUTER JOIN INVE_CLIB01 V1 ON V.CVE_ART = V1.CVE_PROD
LEFT OUTER JOIN MULT01 M ON V.CVE_ART=M.CVE_ART
WHERE V.STATUS 'B' AND M.STATUS 'B' AND M.CVE_ALM='1' ORDER BY V1.CAMPLIB2 DESC
0 #2 Super User 18-05-2017 15:56
Cito a Alberto:
Buenas tardes:

duda, tengo una vista de firebird que me esta tardando casi 5 hrs en descargar 145,000 registros, cuando tengo otra vista de 145,000 que si descarga en 3 min.

Alguna recomendación?


Hola Alberto.

Las consultas a una base de datos dependen de un sinnumero de variables. Si haces referencia a más de una tabla, si están indizados, la cantidad de registros, etc.

Sin la consulta que haces es imposible darte más información!!
0 #1 Alberto 18-05-2017 00:52
Buenas tardes:

duda, tengo una vista de firebird que me esta tardando casi 5 hrs en descargar 145,000 registros, cuando tengo otra vista de 145,000 que si descarga en 3 min.

Alguna recomendación?

No tienes derecho suficientes para publicar contenido