# Query

$from = 2020-01-01;
$to = 2020-09-01;

    SELECT
      (moves.date_delivered AT TIME ZONE 'UTC-6') :: timestamp :: date fecha,
      (so.create_date AT TIME ZONE 'UTC-6') :: timestamp :: date       fechaso,
      c4.name                                                          familia,
      CASE
      WHEN rp2.name ISNULL
        THEN team1.name
      ELSE team2.name
      END                                                              equipo,
      CASE
      WHEN rp2.name ISNULL
        THEN rp.name
      ELSE rp2.name
      END                                                              cliente,
      CASE
      WHEN rp.name ISNULL
        THEN rp2.name
      ELSE rp.name
      END                                                              enviado,
      CASE
      WHEN rp.name ISNULL
        THEN rp2.id
      ELSE rp.id
      END                                                              id_enviado,
      CASE
      WHEN rp.name ISNULL
        THEN country_sat.name
      ELSE rp.city
      END                                                              geo_municipio,
      CASE
      WHEN rp.name ISNULL
        THEN states2.name
      ELSE states.name
      END                                                              geo_estado,
      CASE
      WHEN rp.name ISNULL
        THEN country2.name
      ELSE country.name
      END                                                              geo_country,
      moves.remision                                                   albaran,
      moves.origin,
      so.client_order_ref                                              pedidocliente,
      so.client_order_ref_2                                            referenciacliente,
      coalesce(invoice.move_name, 'por facturar')                      factura,
      (invoice.date at time zone 'UTC-6') :: timestamp :: date         fechafactura,
      pt.default_code                                                  clave,
      pt.name                                                          descripcion,
      uom.name                                                         udm,
      pt.package_type                                                  empaque,
      to_char(moves.qty_done, 'FM999999999.00') :: FLOAT               cantidad,
      to_char(moves.qty_done * pt.udv, 'FM999999999.00') :: FLOAT      litros
    FROM
      (SELECT
         (picking.date_done AT TIME ZONE 'UTC-6') :: timestamp :: date date_delivered,
         picking.name                                                  remision,
         origin,
         qty_done,
         product_id,
         picking.partner_id,
         picking.invoice_related_id
       FROM
         stock_pack_operation spo
         INNER JOIN stock_picking picking on spo.picking_id = picking.id
         INNER JOIN stock_picking_type ptype ON picking.picking_type_id = ptype.id
       WHERE
         picking.state IN ('done', 'documentado')
         AND invoice_control NOT IN ('')
         AND picking.picking_from_sale
         AND ptype.code = 'outgoing'
         AND picking.name NOT IN
             (
               SELECT origin
               FROM stock_picking
                 INNER JOIN stock_location ON stock_picking.location_id = stock_location.id
                 INNER JOIN stock_picking_type ON stock_picking.picking_type_id = stock_picking_type.id
               WHERE stock_location.usage = 'customer'
                     AND stock_picking_type.code = 'incoming'
                     AND stock_picking.state NOT IN ('cancel')
             )
       ORDER BY
         date_delivered,
         remision) moves
      INNER JOIN sale_order so ON moves.origin = so.name
      INNER JOIN product_product pp ON moves.product_id = pp.id
      INNER JOIN product_template pt ON pp.product_tmpl_id = pt.id
      INNER JOIN product_uom uom ON pt.uom_id = uom.id
      INNER JOIN product_category c1 ON pt.categ_id = c1.id
      INNER JOIN product_category c2 on c1.parent_id = c2.id
      INNER JOIN product_category c3 on c2.parent_id = c3.id
      INNER JOIN product_category c4 on c3.parent_id = c4.id
      INNER JOIN res_partner rp ON moves.partner_id = rp.id
      LEFT JOIN res_partner rp2 ON rp.parent_id = rp2.id
      LEFT JOIN res_country_state states ON rp.state_id = states.id
      LEFT JOIN res_country_state states2 ON rp2.state_id = states2.id
      LEFT JOIN res_country country ON states.country_id = country.id
      LEFT JOIN res_country country2 ON states2.country_id = country2.id
      LEFT JOIN res_country_township_sat_code country_sat ON rp2.township_sat_id = country_sat.id
      LEFT JOIN crm_team team1 ON rp.team_id = team1.id
      LEFT JOIN crm_team team2 ON rp2.team_id = team2.id
      LEFT JOIN account_invoice invoice ON moves.invoice_related_id = invoice.id
    WHERE
      c4.name = 'ADBLUE'
     AND (moves.date_delivered AT TIME ZONE 'UTC-6')::TIMESTAMP::DATE >= '$from'
     AND (moves.date_delivered AT TIME ZONE 'UTC-6')::TIMESTAMP::DATE <= '$to'
    ORDER BY cliente, fecha, albaran, clave