-- View: public.vw_utility_invoices

-- DROP VIEW public.vw_utility_invoices;

CREATE OR REPLACE VIEW public.vw_utility_invoices AS
 WITH invoice_lines_agg AS (
         SELECT vil."intInvoiceId",
            sum(vil."dblQuantity") AS "dblTotalQuantity",
            sum(vil."dblLineTotal") AS "dblTotalLineAmount",
            max(vil."intContractLineId") AS "intContractLineId"
           FROM vw_utility_invoice_lines vil
          GROUP BY vil."intInvoiceId"
        ), payments_agg AS (
         SELECT am."intOriginId" AS "intInvoiceId",
            sum(am."dblAmount") AS "dblPaidAmountInBaseCurrency",
            sum(am."dblAmountSecondCurrency") AS "dblPaidAmountInSecondCurrency",
            count(*) AS "intCountPayments"
           FROM vw_utility_account_move am
          WHERE 1 = 1 AND am."intReconciliation" > 0 AND am."strAccountJournalCode"::text = 'CASH'::text AND am."strOrigin" = 'account.invoice'::text
          GROUP BY am."intOriginId"
        )
 SELECT ai.id AS "intInvoiceId",
    ai.company AS "intCompanyId",
    company_party.name AS "strCompanyName",
    cur.name AS "strCompanyCurrency",
    COALESCE(ai.number, ''::character varying) AS "strInvoiceNumber",
    ai.invoice_date AS "dtmInvoiceDate",
    invoice_party.name AS "strPartyName",
    ai.type AS "strInvoiceType",
    aipt.name AS "strPaymentTerm",
    ai.accounting_date AS "dtmAccountingDate",
    ai.currency AS "intCurrencyId",
    cc.name AS "strCurrencyCode",
    ai.state AS "strInvoiceState",
    COALESCE(ai.move, 0) AS "intMoveId",
    ai.account AS "intAccountId",
    aa.name AS "strAccountName",
    ila."dblTotalQuantity" AS "dblInvoiceQuantity",
    ila."dblTotalLineAmount" AS "dblInvoiceAmount",
    ila."intContractLineId",
    pa."dblPaidAmountInBaseCurrency",
    pa."dblPaidAmountInSecondCurrency",
        CASE
            WHEN cur.name::text = cc.name::text THEN COALESCE(pa."dblPaidAmountInBaseCurrency", 0::numeric)
            ELSE COALESCE(pa."dblPaidAmountInSecondCurrency", 0::numeric)
        END AS "dblPaidAmount",
    ila."dblTotalLineAmount" -
        CASE
            WHEN cur.name::text = cc.name::text THEN COALESCE(pa."dblPaidAmountInBaseCurrency", 0::numeric)
            ELSE COALESCE(pa."dblPaidAmountInSecondCurrency", 0::numeric)
        END AS "dblOpenedAmount"
   FROM account_invoice ai
     JOIN invoice_lines_agg ila ON ila."intInvoiceId" = ai.id
     JOIN currency_currency cc ON cc.id = ai.currency
     JOIN party_party invoice_party ON invoice_party.id = ai.party
     JOIN company_company comp ON comp.id = ai.company
     JOIN currency_currency cur ON comp.currency = cur.id
     JOIN party_party company_party ON company_party.id = comp.party
     JOIN account_invoice_payment_term aipt ON aipt.id = ai.payment_term
     LEFT JOIN account_account aa ON aa.id = ai.account
     LEFT JOIN payments_agg pa ON pa."intInvoiceId" = ai.id;

ALTER TABLE public.vw_utility_invoices
    OWNER TO postgres;