HCSINT-52462

DROP TABLE IF EXISTS tmp_management;
CREATE TEMPORARY TABLE tmp_management AS
SELECT
    *
FROM
    dblink('hcshmdb_d', 'SELECT
                            guid,
                            fias_house_code,
                            management_organization_guid,
                            document_guid,
                            status,
                            management_period_from,
                            management_period_to,
                            last_editing_date
                        FROM
                            hmsm.hm_house_management_periods
                        WHERE
                            status IN (''INCLUDED'', ''EXCLUDED'')') AS
                        h(
                            guid character varying(36),
                            fias_house_code character varying(36),
                            management_organization_guid character varying(36),
                            document_guid character varying(36),
                            status character varying(10),
                            management_period_from date,
                            management_period_to date,
                            last_editing_date timestamp without time zone
                        );

DROP TABLE IF EXISTS tmp_voting;
CREATE TEMPORARY TABLE tmp_voting AS
SELECT
    vt.guid AS voting_guid,
    regions.formatted_name AS region_formatted_name,
    vta.region_code AS fias_region_code,
    vta.house_code AS fias_house_code,
    vta.formatted_address AS formatted_address,
    vv.voting_type,
    CASE vv.voting_type
        WHEN 'ANNUALLY' THEN 'Ежегодное'
        WHEN 'EXTRAORDINARY' THEN 'Внеочередное'
        ELSE 'Неизвестно'
    END AS voting_type_name,
    vv.voting_form,
    CASE vv.voting_form
        WHEN 'MEETING' THEN 'Очное'
        WHEN 'ABSENT_VOTING' THEN 'Заочное (опросным путем)'
        WHEN 'MEETING_ABSENT_VOTING' THEN 'Очно-заочное'
        WHEN 'ONLINE_VOTING' THEN 'Заочное (с использованием системы)'
        ELSE 'Неизвестно'
    END AS voting_form_name,
    CASE vv.voting_form
        WHEN 'ONLINE_VOTING' THEN TRUE
        ELSE FALSE
    END AS use_hcs,
    vv.meeting_eligibility,
    CASE meeting_eligibility
        WHEN 'COMPETENT' THEN 'Правомочно'
        ELSE 'Неправомочно'
    END AS meeting_eligibility_name,
    vv.meeting_type,
    CASE vv.meeting_type
        WHEN 'HOMEOWNERS' THEN 'Собрание членов ТСЖ'
        WHEN 'COOPERATIVE' THEN 'Собрание членов кооператива'
        WHEN 'OWNERS' THEN 'Общее собрание собственников'
        ELSE 'Неизвестно'
    END AS meeting_type_name,
    vv.protocol_date,
    vv.protocol_code,
    vv.meeting_place,
    tmp_management.management_organization_guid,
    ROW_NUMBER() OVER (PARTITION BY vt.guid, vta.house_code ORDER BY tmp_management.last_editing_date DESC) AS row_num
FROM
    vtsm.vt_voting vt
    INNER JOIN vtsm.vt_voting_version vv ON vv.guid = vt.last_signed_protocol_guid
    INNER JOIN vtsm.vt_voting_address vta ON vta.voting_version_guid = vv.guid
    LEFT JOIN tmp_management ON tmp_management.fias_house_code = vta.house_code
                                AND vv.protocol_date BETWEEN COALESCE(tmp_management.management_period_from, '1900-01-01'::date)
                                AND COALESCE(tmp_management.management_period_to, '5000-01-01'::date)
    INNER JOIN (SELECT
                    aoguid,
                    formatted_name
                FROM
                    dblink('hcsnsidb_d', 'SELECT aoguid, (formalname ||'' ''|| shortname) FROM NSISM.NSI_ADDROBJ WHERE aolevel=1 AND is_actual; ') AS regions(aoguid VARCHAR(36), formatted_name VARCHAR(255))
                ) regions ON regions.aoguid = vta.region_code
WHERE
    vt.voting_status IN ('FINISHED')
    AND EXTRACT(YEAR FROM vv.protocol_date) = 2021;

DELETE FROM tmp_voting WHERE row_num>1;

DROP TABLE IF EXISTS tmp_used_orgs;
CREATE TEMPORARY TABLE tmp_used_orgs AS
SELECT
    DISTINCT management_organization_guid
FROM
    tmp_voting;

DROP TABLE IF EXISTS tmp_used_orgs_with_names;
CREATE TEMPORARY TABLE tmp_used_orgs_with_names AS
SELECT
    tmp_used_orgs.management_organization_guid,
    ppa_org.org_name,
    ppa_org.ogrn
FROM
    tmp_used_orgs
    INNER JOIN dblink('hcsppadb_d', 'SELECT guid, COALESCE(organization_short_name, organization_full_name), ogrn FROM ppasm.ppa_organizations') AS ppa_org(guid varchar(36), org_name varchar(4000), ogrn varchar(15)) ON ppa_org.guid = tmp_used_orgs.management_organization_guid;

DROP TABLE IF EXISTS tmp_result;
CREATE TEMPORARY TABLE tmp_result AS
SELECT
    MAX(fias_region_code) AS fias_region_code,
    MAX(region_formatted_name) AS region_formatted_name,
    MAX(protocol_code) AS protocol_number,
    MAX(protocol_date) AS protocol_date,
    STRING_AGG(formatted_address, ', ') AS formatted_address,
    STRING_AGG(fias_house_code, ', ') AS fias_house_code,
    MAX(tmp_used_orgs_with_names.org_name) AS org_name,
    MAX(meeting_place) AS meeting_place,
    MAX(voting_form_name) AS voting_form_name,
    MAX(voting_type_name) AS voting_type_name,
    MAX(meeting_type_name) AS meeting_type_name,
    MAX(meeting_eligibility_name) AS meeting_eligibility_name,
    BOOL_AND(use_hcs) AS use_hcs
FROM
    tmp_voting
    LEFT JOIN tmp_used_orgs_with_names ON tmp_used_orgs_with_names.management_organization_guid = tmp_voting.management_organization_guid
GROUP BY
    tmp_voting.voting_guid;

SELECT * FROM tmp_result;