doc counters function

doc_counters.sql

create or replace function doc_counters(
   mailbox_id_in text,
   profile_id_in text,
   structure_id_in text
)
RETURNS json
AS
$$
DECLARE owner_reaction integer;
DECLARE partner_reaction integer;
DECLARE invoice_v1 text := 'ceaed8e2-b141-415d-8d5d-172584acc413';
DECLARE invoice_v2 text := '5dc512c3-bc0b-419f-9254-0fc00e2569ef';
DECLARE result_record counters_row;
DECLARE result_json json;
DECLARE visibility_zone integer;
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 := count(*) 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 := count(*) 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
        owner_reaction := count(*) FROM main_documents_grid
        where transaction_operation IN ('RecievedBySeller', 'ReceivedByRecipient', 'ReceivedByConfidant', 'ReceivedByAgent')
        AND transaction_owner_mailbox_id = mailbox_id_in
        AND (departament_id IN (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 FROM strs) 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 := count(*) FROM main_documents_grid
        where transaction_operation IN ('SentToConfidant', 'SentByOwner', 'PendingBuyerAction')
        AND transaction_owner_mailbox_id = mailbox_id_in
          AND (departament_id IN (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 FROM strs) 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;
    result_record.requiring_reaction := owner_reaction;
    result_record.requiring_partner_reaction := partner_reaction;
    result_json := to_json(result_record);
    return result_json;
END $$ LANGUAGE plpgsql;