queries.sql

// Query cliente
SELECT dateinvoice, rfc_re, SUM(subtotal_calc) AS subtotal
  FROM (SELECT *, 
      CASE 
        WHEN estatus = 'Cancelado' THEN (SELECT CASE 
          WHEN EXISTS (SELECT 1 FROM polizas WHERE factura_id = a.id) THEN subtotaltc 
          ELSE 0 
          END) 
        ELSE (SELECT CASE 
          WHEN tipo_de_comprobante = 'egreso' AND tipo <> 'nomina' THEN subtotaltc*-1 
          ELSE subtotaltc 
          END) 
        END AS subtotal_calc 
      FROM (SELECT DATE (fecha_expedicion) AS dateinvoice, 
        CASE 
          WHEN is_nomina = TRUE THEN 'nomina' 
          WHEN rfc_em = '#{rfc}' THEN 'sales' 
          WHEN rfc_re = '#{rfc}' THEN 'purchase' 
          ELSE 'desconocido' 
          END AS tipo, 
        CASE 
          WHEN TRIM(moneda) = 'MXN' THEN (subtotal - descuento) 
          ELSE (subtotal - descuento)*COALESCE(tc.precio,1) 
          END AS subtotaltc, subtotal AS subtotalorig, 
          f.id, f.estatus, f.tipo_de_comprobante, f.rfc_re, f.rfc_em 
        FROM facturas f LEFT JOIN tipo_cambios tc ON f.tipo_cambio_id = tc.id 
        WHERE 1 = 1 AND rfc_em = '#{rfc}' AND is_nomina = FALSE 
        AND DATE (fecha_expedicion) BETWEEN '#{date_start}' AND '#{date_end}') a) b 
        GROUP BY dateinvoice, tipo, rfc_re
        
  // Query producto
  SELECT dateinvoice, tipo, producto, SUM(subtotal_calc) AS subtotal 
      FROM (SELECT *, 
        CASE 
          WHEN estatus = 'Cancelado' THEN (SELECT CASE 
            WHEN EXISTS (SELECT 1 FROM polizas WHERE factura_id = a.id) THEN subtotaltc 
            ELSE 0 
            END) 
          ELSE (SELECT CASE 
            WHEN tipo_de_comprobante = 'egreso' AND tipo <> 'nomina' THEN subtotaltc*-1 
            ELSE subtotaltc 
            END) 
          END AS subtotal_calc 
      FROM (SELECT DATE (fecha_expedicion) AS dateinvoice, 
        CASE 
          WHEN is_nomina = TRUE THEN 'nomina' 
          WHEN rfc_em = '#{rfc}' THEN 'sales' 
          WHEN rfc_re = '#{rfc}' THEN 'purchase' 
          ELSE 'desconocido' 
        END AS tipo, 
      CASE 
          WHEN TRIM(moneda) = 'MXN' THEN (importe) 
          ELSE (importe)*COALESCE(tc.precio,1) 
          END AS subtotaltc, subtotal AS subtotalorig, f.id, f.estatus, 
          f.tipo_de_comprobante, f.rfc_re, f.rfc_em, c.producto 
      FROM facturas f LEFT JOIN conceptos c ON f.id = c.factura_id 
      LEFT JOIN tipo_cambios tc ON f.tipo_cambio_id = tc.id WHERE 1 = 1 
      AND (rfc_em = '#{rfc}' OR rfc_re = '#{rfc}') 
      AND DATE (fecha_expedicion) BETWEEN '#{start_date}' AND '#{end_date}') a) b 
      GROUP BY dateinvoice, tipo, producto
      
  // Query provider
  SELECT dateinvoice, rfc_em, SUM(subtotal_calc) AS subtotal 
    FROM (SELECT *, 
        CASE 
          WHEN estatus = 'Cancelado' THEN (SELECT CASE 
            WHEN EXISTS (SELECT 1 FROM polizas WHERE factura_id = a.id) THEN subtotaltc 
            ELSE 0 
            END) 
          ELSE (SELECT CASE 
            WHEN tipo_de_comprobante = 'egreso' AND tipo <> 'nomina' THEN subtotaltc*-1 
            ELSE subtotaltc 
            END) 
          END AS subtotal_calc 
    FROM (SELECT DATE (fecha_expedicion) AS dateinvoice, 
      CASE 
        WHEN is_nomina = TRUE THEN 'nomina' 
        WHEN rfc_em = '#{rfc}' THEN 'sales' 
        WHEN rfc_re = '#{rfc}' THEN 'purchase' 
        ELSE 'desconocido' 
        END AS tipo, 
          CASE 
            WHEN TRIM(moneda) = 'MXN' THEN (subtotal - descuento) 
            ELSE (subtotal - descuento)*COALESCE(tc.precio,1) 
            END AS subtotaltc, subtotal AS subtotalorig, 
            f.id, f.estatus, f.tipo_de_comprobante, f.rfc_re, f.rfc_em 
    FROM facturas f LEFT JOIN tipo_cambios tc ON f.tipo_cambio_id = tc.id 
    WHERE 1 = 1 AND rfc_re = '#{rfc}' 
    AND DATE (fecha_expedicion) BETWEEN '#{date_start}' AND '#{date_end}') a) b 
    GROUP BY dateinvoice, tipo, rfc_em ORDER BY subtotal
    
  // Query by type
  SELECT dateinvoice, tipo, SUM(subtotal_calc) AS subtotal 
    FROM (SELECT *,
              CASE
                WHEN estatus = 'Cancelado' THEN (SELECT CASE
                     WHEN EXISTS (SELECT 1 FROM polizas WHERE factura_id = a.id) THEN subtotalt
                     ELSE 
                     END)
               ELSE (SELECT CASE
                       WHEN tipo_de_comprobante = 'egreso' AND tipo <> 'nomina' THEN subtotaltc*-1
                       ELSE subtotaltc 
                       END)
             END AS subtotal_calc
       FROM (SELECT DATE (fecha_expedicion) AS dateinvoice,
                 CASE
                   WHEN is_nomina = TRUE THEN 'nomina'
                   WHEN rfc_em = '#{rfc}' THEN 'sales'
                   WHEN rfc_re = '#{rfc}' THEN 'purchase'
                     ELSE 'desconocido'
                   END AS tipo,
                   CASE
                     WHEN trim(moneda) = 'MXN' THEN (subtotal - descuento)
                     ELSE (subtotal - descuento)*COALESCE(tc.precio,1)
                   END AS subtotaltc,
                   subtotal as subtotalorig,
                   f.id,
                   f.estatus,
                   f.tipo_de_comprobante
           FROM facturas f
             LEFT JOIN tipo_cambios tc ON f.tipo_cambio_id = tc.id
           WHERE 1 = 1
           AND (rfc_em = '#{rfc}' OR rfc_re = '#{rfc}')
           AND DATE (fecha_expedicion) BETWEEN '#{date_start}' AND '#{date_end}' ) a) b
 GROUP BY dateinvoice, tipo