FUNCTION RETURNS COUNT OF ALL REQUIRING REACTION DOCUMENTS BY DOCTYPE ID

all_doc_type_counters.sql

create function all_doc_type_counters(mailbox_id_in text, profile_id_in text, structure_id_in text) returns json
    language plpgsql
as
$$
DECLARE owner_reaction_doctype_id   text[];
DECLARE partner_reaction_doctype_id text[];
DECLARE departments                 text;
DECLARE result_json      json;
DECLARE visibility_zone  integer;

-- DECLARE DOC TYPES
DECLARE invoice_v1   text := 'ceaed8e2-b141-415d-8d5d-172584acc413';
DECLARE invoice_v2   text := '5dc512c3-bc0b-419f-9254-0fc00e2569ef';
DECLARE contract_doc text := 'f0cd7256-71fe-465a-b26e-0840ca052c48';
DECLARE act          text := '42616ee4-1b6e-4015-b9c6-922a459bc170';
DECLARE attorney     text := '945dde7f-b55e-4666-990c-749751a3c726';
DECLARE waybill      text := 'e6dc135c-33f8-4f49-b6f4-b3302db26b0a';

-- DECLARE POSITIONS

DECLARE invoice_positions_owner_reaction int;
DECLARE contract_positions_owner_reaction int;
DECLARE act_positions_owner_reaction int;
DECLARE attorney_positions_owner_reaction int;
DECLARE waybill_positions_owner_reaction int;
DECLARE total_owner_reaction int;

DECLARE invoice_positions_partner_reaction int;
DECLARE contract_positions_partner_reaction int;
DECLARE act_positions_partner_reaction int;
DECLARE attorney_positions_partner_reaction int;
DECLARE waybill_positions_partner_reaction int;
DECLARE total_partner_reaction int;

BEGIN
    visibility_zone := profile_to_structures_visibility_zone from oauth_m24.profiles_to_structure where profile_id = profile_id_in and structure_id = structure_id_in;
    IF (visibility_zone = 1)
    THEN
        owner_reaction_doctype_id := ARRAY(SELECT cast(doctype_id as text) FROM main_documents_grid
        where transaction_operation IN ('RecievedBySeller', 'ReceivedByRecipient', 'ReceivedByConfidant', 'ReceivedByAgent')
        AND transaction_owner_mailbox_id = mailbox_id_in
        AND departament_id = structure_id_in
        AND ((created_at > CURRENT_TIMESTAMP - INTERVAL '10 DAY' AND (transaction_operation = 'ReceivedByRecipient' OR transaction_operation = 'ReceivedByAgent') AND (doctype_id <>  invoice_v1 AND doctype_id <> invoice_v2))
        OR (doctype_id <>  invoice_v1 AND doctype_id <> invoice_v2)));

        partner_reaction_doctype_id := ARRAY(SELECT cast(doctype_id as text) FROM main_documents_grid
        where transaction_operation IN ('SentToConfidant', 'SentByOwner', 'PendingBuyerAction')
        AND transaction_owner_mailbox_id = mailbox_id_in
        AND departament_id = structure_id_in
        AND ((created_at > CURRENT_TIMESTAMP - INTERVAL '10 DAY' AND (transaction_operation = 'SentByOwner') AND (doctype_id = invoice_v1 OR doctype_id = invoice_v2))
        OR (doctype_id <> invoice_v1 AND doctype_id <> invoice_v2)));

    ELSE
        departments := ARRAY(WITH RECURSIVE strs AS (
            SELECT * FROM oauth_m24.structures WHERE parent_structure_id = structure_id_in
          UNION
            SELECT t.*
            FROM oauth_m24.structures t
            JOIN strs rt ON rt.structure_id = t.parent_structure_id
         )
        SELECT structure_id::text FROM strs);

        owner_reaction_doctype_id := ARRAY(SELECT cast(doctype_id as text) FROM main_documents_grid
        where transaction_operation IN ('RecievedBySeller', 'ReceivedByRecipient', 'ReceivedByConfidant', 'ReceivedByAgent')
        AND transaction_owner_mailbox_id = mailbox_id_in
        AND (departament_id IN (departments) OR departament_id = structure_id_in)
        AND ((created_at > CURRENT_TIMESTAMP - INTERVAL '10 DAY' AND (transaction_operation = 'ReceivedByRecipient' OR transaction_operation = 'ReceivedByAgent') AND (doctype_id <>  invoice_v1 AND doctype_id <> invoice_v2))
        OR (doctype_id <>  invoice_v1 AND doctype_id <> invoice_v2)));

        partner_reaction_doctype_id := ARRAY(SELECT cast(doctype_id as text) FROM main_documents_grid
        where transaction_operation IN ('SentToConfidant', 'SentByOwner', 'PendingBuyerAction')
        AND transaction_owner_mailbox_id = mailbox_id_in
        AND (departament_id IN (departments) OR departament_id = structure_id_in)
        AND ((created_at > CURRENT_TIMESTAMP - INTERVAL '10 DAY' AND (transaction_operation = 'SentByOwner') AND (doctype_id = invoice_v1 OR doctype_id = invoice_v2))
        OR (doctype_id <> invoice_v1 AND doctype_id <> invoice_v2)));

    END IF;

    invoice_positions_owner_reaction  = cardinality(array_positions(owner_reaction_doctype_id, invoice_v1)) + cardinality(array_positions(owner_reaction_doctype_id, invoice_v2));
    contract_positions_owner_reaction = cardinality(array_positions(owner_reaction_doctype_id, contract_doc));
    act_positions_owner_reaction      = cardinality(array_positions(owner_reaction_doctype_id, act));
    attorney_positions_owner_reaction = cardinality(array_positions(owner_reaction_doctype_id, attorney));
    waybill_positions_owner_reaction  = cardinality(array_positions(owner_reaction_doctype_id, waybill));
    total_owner_reaction = cardinality(owner_reaction_doctype_id);

    invoice_positions_partner_reaction  = cardinality(array_positions(partner_reaction_doctype_id, invoice_v1)) + cardinality(array_positions(partner_reaction_doctype_id, invoice_v2));
    contract_positions_partner_reaction = cardinality(array_positions(partner_reaction_doctype_id, contract_doc));
    act_positions_partner_reaction      = cardinality(array_positions(partner_reaction_doctype_id, act));
    attorney_positions_partner_reaction = cardinality(array_positions(partner_reaction_doctype_id, attorney));
    waybill_positions_partner_reaction  = cardinality(array_positions(partner_reaction_doctype_id, waybill));
    total_partner_reaction = cardinality(partner_reaction_doctype_id);

    -- формируем ответ для пользователя
    result_json :=
    json_build_object(
        'invoice', json_build_object(
            'owner_reaction', invoice_positions_owner_reaction,
            'partner_reaction', invoice_positions_partner_reaction
         ),
        'contract_doc', json_build_object(
            'owner_reaction', contract_positions_owner_reaction,
            'partner_reaction', contract_positions_partner_reaction
         ),
        'act', json_build_object(
            'owner_reaction', act_positions_owner_reaction,
            'partner_reaction', act_positions_partner_reaction
         ),
        'attorney', json_build_object(
            'owner_reaction', attorney_positions_owner_reaction,
            'partner_reaction', attorney_positions_partner_reaction
         ),
        'waybill', json_build_object(
            'owner_reaction', waybill_positions_owner_reaction,
            'partner_reaction', waybill_positions_partner_reaction
         ),
        'total_owner_reaction', total_owner_reaction,
        'total_partner_reaction', total_partner_reaction

     );
    return result_json;
END
$$;