-- Backup of adempiere.v_orders_summary
-- taken 2026-10-27T20:04:22.506Z from localhost:5432/idempiere (owner: adempiere)
-- columns (86): ad_client_id, ad_org_id, c_order_id, created, createdby, updated, updatedby, isactive, issotrx, docstatus, documentno, description, dateordered, grandtotal, priorityrule, externalorderid, shopify_order_id, xentral_order_id, shopware6_order_id, amazon_order_id, plentyone_order_id, jtl_order_id, temu_order_id, ebay_order_id, c_bpartner_id, c_bpartner_location_id, bill_bpartner_id, bill_location_id, m_warehouse_id, c_doctype_id, c_doctypetarget_id, c_currency_id, c_ordersource_id, partner_name, order_source_name, order_source_description, marketplace_key, marketplace_name, currency_iso, warehouse_name, doctype_name, has_description, ship_location_id, ship_name, ship_name2, ship_address1, ship_address2, ship_city, ship_postal, ship_country, ship_countrycode, bill_name, bill_name2, bill_address1, bill_address2, bill_city, bill_postal, bill_country, bill_countrycode, billing_differs_from_shipping, shipment_id, shipment_documentno, shipment_docstatus, trackingno, iscommissioned, iscommissionedconfirmed, shipping_date, shipment_weight, shipper_id, shipper_name, shipper_cfg, is_attachment_required, is_print_dina4, paket_type_name, totalqty, totalpositions, totalshippedqty, shipment_count, shipments_summary, has_shipment, has_invoice, invoices_summary, fulfillment_fee_total, fee_processed, days_since_created, created_to_ordered_age
-- Restore:  node reference/sql/restore-view.cjs <this file>

CREATE OR REPLACE VIEW adempiere.v_orders_summary AS
SELECT ord.ad_client_id,
    ord.ad_org_id,
    ord.c_order_id,
    ord.created,
    ord.createdby,
    ord.updated,
    ord.updatedby,
    ord.isactive,
    ord.issotrx,
    ord.docstatus,
    ord.documentno,
    ord.description,
    ord.dateordered,
    ord.grandtotal,
    ord.priorityrule,
    ord.externalorderid,
    ord.shopify_order_id,
    ord.xentral_order_id,
    ord.shopware6_order_id,
    ord.amazon_order_id,
    ord.plentyone_order_id,
    ord.jtl_order_id,
    ord.temu_order_id,
    ord.ebay_order_id,
    ord.c_bpartner_id,
    ord.c_bpartner_location_id,
    ord.bill_bpartner_id,
    ord.bill_location_id,
    ord.m_warehouse_id,
    ord.c_doctype_id,
    ord.c_doctypetarget_id,
    ord.c_currency_id,
    ord.c_ordersource_id,
    bp.name AS partner_name,
    osrc.name AS order_source_name,
    osrc.description AS order_source_description,
    osrc.marketplace AS marketplace_key,
    arl.name AS marketplace_name,
    cur.iso_code AS currency_iso,
    wh.name AS warehouse_name,
    dt.name AS doctype_name,
        CASE
            WHEN ord.description IS NOT NULL AND length(TRIM(BOTH FROM ord.description)) > 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS has_description,
    loc.c_location_id AS ship_location_id,
    bpl.name AS ship_name,
    bpl.name2 AS ship_name2,
    loc.address1 AS ship_address1,
    loc.address2 AS ship_address2,
    loc.city AS ship_city,
    loc.postal AS ship_postal,
    cou.name AS ship_country,
    cou.countrycode AS ship_countrycode,
    bplbill.name AS bill_name,
    bplbill.name2 AS bill_name2,
    locbill.address1 AS bill_address1,
    locbill.address2 AS bill_address2,
    locbill.city AS bill_city,
    locbill.postal AS bill_postal,
    coubill.name AS bill_country,
    coubill.countrycode AS bill_countrycode,
        CASE
            WHEN COALESCE(bpl.name, ''::character varying)::text <> COALESCE(bplbill.name, ''::character varying)::text OR COALESCE(bpl.name2, ''::character varying)::text <> COALESCE(bplbill.name2, ''::character varying)::text OR COALESCE(loc.address1, ''::character varying)::text <> COALESCE(locbill.address1, ''::character varying)::text OR COALESCE(loc.address2, ''::character varying)::text <> COALESCE(locbill.address2, ''::character varying)::text OR COALESCE(loc.city, ''::character varying)::text <> COALESCE(locbill.city, ''::character varying)::text OR COALESCE(loc.postal, ''::character varying)::text <> COALESCE(locbill.postal, ''::character varying)::text OR COALESCE(cou.name, ''::character varying)::text <> COALESCE(coubill.name, ''::character varying)::text THEN 'Y'::text
            ELSE 'N'::text
        END AS billing_differs_from_shipping,
    ls.m_inout_id AS shipment_id,
    ls.shipment_documentno,
    ls.shipment_docstatus,
    ls.trackingno,
    ls.iscommissioned,
    ls.iscommissionedconfirmed,
    ls.shipping_date,
    ls.shipment_weight,
    COALESCE(ls.m_shipper_id, ord.m_shipper_id) AS shipper_id,
    COALESCE(ls.shipper_name, ord_sh.name) AS shipper_name,
    COALESCE(ls.shipper_cfg, ord_shcfg.name) AS shipper_cfg,
    COALESCE(ls.is_attachment_required, ord_sh.isattachmentrequired) AS is_attachment_required,
    COALESCE(ls.is_print_dina4, ord_sh.isprintdina4) AS is_print_dina4,
    ls.paket_type_name,
    COALESCE(ol_agg.total_qty, 0::numeric) AS totalqty,
    COALESCE(ol_agg.total_positions, 0::bigint) AS totalpositions,
    COALESCE(ino_agg.total_shipped_qty, 0::numeric) AS totalshippedqty,
    COALESCE(ino_agg.shipment_count, 0::bigint) AS shipment_count,
    ino_agg.shipments_summary,
        CASE
            WHEN COALESCE(ino_agg.shipment_count, 0::bigint) > 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS has_shipment,
        CASE
            WHEN COALESCE(inv_agg.invoice_count, 0::bigint) > 0 THEN 'Y'::text
            ELSE 'N'::text
        END AS has_invoice,
    inv_agg.invoices_summary,
    COALESCE(ff_agg.fulfillment_fee_total, 0::numeric) AS fulfillment_fee_total,
    COALESCE(ff_agg.fee_processed, 'N'::text) AS fee_processed,
    CURRENT_DATE - ord.created::date AS days_since_created,
        CASE
            WHEN abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) < 3600::numeric THEN round(abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) / 60::numeric)::integer || '
      minutes'::text
            WHEN abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) < 86400::numeric THEN round(abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) / 3600::numeric)::integer || '
  hours'::text
            ELSE round(abs(EXTRACT(epoch FROM ord.dateordered - ord.created)) / 86400::numeric)::integer || ' days'::text
        END AS created_to_ordered_age
   FROM c_order ord
     LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = ord.c_bpartner_id
     LEFT JOIN c_bpartner_location bpl ON bpl.c_bpartner_location_id = ord.c_bpartner_location_id
     LEFT JOIN c_location loc ON loc.c_location_id = bpl.c_location_id
     LEFT JOIN c_country cou ON cou.c_country_id = loc.c_country_id
     LEFT JOIN c_bpartner_location bplbill ON bplbill.c_bpartner_location_id = ord.bill_location_id
     LEFT JOIN c_location locbill ON locbill.c_location_id = bplbill.c_location_id
     LEFT JOIN c_country coubill ON coubill.c_country_id = locbill.c_country_id
     LEFT JOIN c_ordersource osrc ON osrc.c_ordersource_id = ord.c_ordersource_id
     LEFT JOIN ad_ref_list arl ON arl.ad_reference_id = 1000003::numeric AND arl.value::text = osrc.marketplace::text
     LEFT JOIN c_currency cur ON cur.c_currency_id = ord.c_currency_id
     LEFT JOIN m_warehouse wh ON wh.m_warehouse_id = ord.m_warehouse_id
     LEFT JOIN c_doctype dt ON dt.c_doctype_id = ord.c_doctype_id
     LEFT JOIN m_shipper ord_sh ON ord_sh.m_shipper_id = ord.m_shipper_id
     LEFT JOIN m_shippercfg ord_shcfg ON ord_shcfg.m_shippercfg_id = ord_sh.m_shippercfg_id
     LEFT JOIN ( SELECT sub.c_order_id,
            sub.m_inout_id,
            sub.shipment_documentno,
            sub.shipment_docstatus,
            sub.trackingno,
            sub.iscommissioned,
            sub.iscommissionedconfirmed,
            sub.shipping_date,
            sub.shipment_weight,
            sub.m_shipper_id,
            sub.shipper_name,
            sub.shipper_cfg,
            sub.is_attachment_required,
            sub.is_print_dina4,
            sub.paket_type_name
           FROM ( SELECT ino.c_order_id,
                    ino.m_inout_id,
                    ino.documentno AS shipment_documentno,
                    ino.docstatus AS shipment_docstatus,
                    ino.trackingno,
                    ino.iscommissioned,
                    ino.iscommissionedconfirmed,
                    ino.shipping_date,
                    ino.weight AS shipment_weight,
                    sh.m_shipper_id,
                    sh.name AS shipper_name,
                    shcfg.name AS shipper_cfg,
                    sh.isattachmentrequired AS is_attachment_required,
                    sh.isprintdina4 AS is_print_dina4,
                    pt.name AS paket_type_name,
                    row_number() OVER (PARTITION BY ino.c_order_id ORDER BY ino.m_inout_id DESC) AS rn
                   FROM m_inout ino
                     LEFT JOIN m_shipper sh ON sh.m_shipper_id = ino.m_shipper_id
                     LEFT JOIN m_shippercfg shcfg ON shcfg.m_shippercfg_id = sh.m_shippercfg_id
                     LEFT JOIN m_product pt ON pt.m_product_id = ino.m_paket_type_id) sub
          WHERE sub.rn = 1) ls ON ls.c_order_id = ord.c_order_id
     LEFT JOIN ( SELECT ol.c_order_id,
            sum(ol.qtyentered) AS total_qty,
            count(ol.c_orderline_id) AS total_positions
           FROM c_orderline ol
          GROUP BY ol.c_order_id) ol_agg ON ol_agg.c_order_id = ord.c_order_id
     LEFT JOIN ( SELECT ino.c_order_id,
            sum(miol.movementqty) AS total_shipped_qty,
            count(DISTINCT ino.m_inout_id) AS shipment_count,
            string_agg(DISTINCT (ino.m_inout_id::text || ':'::text) || ino.documentno::text, ' | '::text) AS shipments_summary
           FROM m_inout ino
             LEFT JOIN m_inoutline miol ON miol.m_inout_id = ino.m_inout_id
          GROUP BY ino.c_order_id) ino_agg ON ino_agg.c_order_id = ord.c_order_id
     LEFT JOIN ( SELECT inv.c_order_id,
            count(DISTINCT inv.c_invoice_id) AS invoice_count,
            string_agg(DISTINCT (inv.c_invoice_id::text || ':'::text) || inv.documentno::text, ' | '::text) AS invoices_summary
           FROM c_invoice inv
          GROUP BY inv.c_order_id) inv_agg ON inv_agg.c_order_id = ord.c_order_id
     LEFT JOIN ( SELECT ffl.c_order_id,
            sum(ffl.linetotalamt) AS fulfillment_fee_total,
                CASE
                    WHEN bool_and(ffl.a_processed = 'Y'::bpchar) THEN 'Y'::text
                    ELSE 'N'::text
                END AS fee_processed
           FROM cust_fulfillmentfeeline ffl
          GROUP BY ffl.c_order_id) ff_agg ON ff_agg.c_order_id = ord.c_order_id;

ALTER VIEW adempiere.v_orders_summary OWNER TO "adempiere";
GRANT DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE ON adempiere.v_orders_summary TO "adempiere";