receipts_ext

CREATE OR REPLACE VIEW billing.receipts_ext
AS SELECT me.id,
    me.payment_date,
    me.number,
    me.comments,
    me.sum,
    me.status,
    me.payer,
    me.proposed_agr,
    me.proposed_bill,
    me.process_result,
    me.payer_ok,
    me.sum - sum(COALESCE(pplus.values_sum, 0::numeric)) AS remaining,
    b.id AS bill_id,
    b.bill_number,
    b.vat,
    round(COALESCE(pplus.rur_sum, 0::numeric), 2) AS value,
    round(COALESCE(pplus.rur_sum * (100::numeric + b.vat) / 100::numeric, 0::numeric), 2) AS value_vat,
    status.name AS status_name,
    client.data,
    client.id AS client_id,
    status.code AS status_code,
    proposed_agr.number AS agr_number,
    COALESCE(pplus.bills_per_rcpt, 0::bigint) AS bills_per_rcpt
   FROM receipts me
     LEFT JOIN (agreements proposed_agr
     JOIN clients client ON client.id = proposed_agr.client) ON proposed_agr.id = me.proposed_agr
     LEFT JOIN ( SELECT p.receipt,
            count(DISTINCT p.bill) AS bills_per_rcpt,
            p.value AS values_sum,
            sum(sr.value_rur) AS rur_sum
           FROM payments p
             JOIN services sr ON sr.bill = p.bill
             JOIN bills b_1 ON b_1.id = p.bill
          GROUP BY p.receipt, p.value) pplus ON pplus.receipt = me.id
     LEFT JOIN bills b ON b.id = me.proposed_bill
     JOIN aux.receipt_states status ON status.id = me.status
  GROUP BY me.id, me.payment_date, me.number, me.comments, me.sum, me.status, me.payer, me.proposed_agr, me.proposed_bill, me.process_result, me.payer_ok, b.id, b.bill_number, b.vat, status.name, pplus.rur_sum, client.data, client.id, status.code, proposed_agr.number, pplus.values_sum, pplus.bills_per_rcpt
  ORDER BY me.status;