SHARE
TWEET

Untitled

a guest Jun 20th, 2019 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS tmp_conflict_data;
  2. CREATE TEMPORARY TABLE tmp_conflict_data AS
  3. SELECT
  4.     ARRAY_AGG(DISTINCT org_house_agr.or_organization_root_guid)::uuid[] AS or_organization_root_guid_arr,
  5.     ARRAY_AGG(COALESCE(ppa.entity_name, ppa.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa.ogrn,'-') || ', КПП: ' || COALESCE(ppa.kpp,'-'))::varchar[] AS organization_name_arr,
  6.     org_house_agr.fias_house_code::uuid,
  7.     1::integer AS confl_type,
  8.     NULL::varchar(50) AS information_type_code
  9. FROM
  10.     corg.org_pf_336_offence_pd_tech_org_house_agr org_house_agr
  11.     JOIN sppa.ppa_organizations ppa ON ppa.organization_root_guid = org_house_agr.or_organization_root_guid::varchar AND ppa.entity_is_actual
  12. WHERE
  13.     org_house_agr.with_agreement = FALSE
  14.     AND ARRAY['8'::varchar] && org_house_agr.org_role_code_arr
  15.     AND org_house_agr.report_month = '2019-05-01'::date
  16.     AND COALESCE(org_house_agr.percent_fill,100) < 100 -- Фиксируем конфликт только для домов  у которых есть нарушение по ТХ (временно, до уточнения в Ланите)
  17. GROUP BY
  18.     org_house_agr.fias_house_code
  19. HAVING
  20.     COUNT(DISTINCT org_house_agr.or_organization_root_guid) > 1;
  21.  
  22. INSERT INTO tmp_conflict_data
  23. SELECT
  24.     null::uuid[] AS or_organization_root_guid_arr,
  25.     null::varchar[] AS organization_name_arr,
  26.     h.fias_house_code::uuid,
  27.     2::integer AS confl_type,
  28.     NULL::varchar(50) AS information_type_code
  29. FROM
  30.     corg.org_pf_336_offence_pd_tech_house_adr h
  31.     LEFT JOIN shm.hm_house_management_periods mng_per ON mng_per.fias_house_code = h.fias_house_code::varchar
  32.                                                 AND mng_per.status IN ('INCLUDED','EXCLUDED')
  33.                                                 AND '2019-05-31'::date BETWEEN COALESCE(mng_per.management_period_from, '1900-01-01')::date AND COALESCE(mng_per.management_period_to, '2900-01-01')::date
  34.                                                 AND mng_per.is_management_agreement_exists = FALSE -- -- Признак наличия ДУ (для ТСЖ)  (для ТСЖ/ЖСК/ЖК/Иных кооперативов: должна отсутствовать информация по дому, что «Управление многоквартирным домом осуществляется управляющей организацией по договору управления»
  35. WHERE
  36.     h.report_month = '2019-05-01'::date
  37.     AND h.hm_house_guid IS NOT NULL
  38.     AND h.house_type = '1'
  39.     AND h.house_management_type_code IS NULL -- в РАО отсутствует в доме значение поля «Способ управления»
  40.     AND mng_per.guid IS NULL;  
  41.  
  42. DROP TABLE IF EXISTS tmp_ppa_deleg_acc_org;
  43. CREATE TEMPORARY TABLE tmp_ppa_deleg_acc_org AS
  44. SELECT
  45.     acc.or_organization_root_guid_base,
  46.     adr.fias_house_code
  47. FROM
  48.     corg.org_pf_336_offence_pd_tech_acc acc
  49.     JOIN corg.org_pf_336_offence_pd_tech_house_adr adr ON adr.hm_house_guid = acc.house_guid
  50. WHERE
  51.     acc.report_month = '2019-05-01'::date
  52.     AND NOT ARRAY['8'::varchar] && acc.org_role_code_arr
  53. GROUP BY
  54.     acc.or_organization_root_guid_base,
  55.     adr.fias_house_code;
  56. ANALYZE tmp_ppa_deleg_acc_org; 
  57.  
  58. DROP TABLE IF EXISTS tmp_ppa_deleg;
  59. CREATE TEMPORARY TABLE tmp_ppa_deleg AS
  60. SELECT
  61.     ppa_from.organization_root_guid::uuid AS or_organization_root_guid_from,
  62.     ppa_to.organization_root_guid::uuid AS or_organization_root_guid_to,
  63.     it.information_type_code AS information_type_code,
  64.     MAX(COALESCE(ppa_from.entity_name, ppa_from.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa_from.ogrn,'-') || ', КПП: ' || COALESCE(ppa_from.kpp,'-')) AS ppa_from_info,
  65.     MAX(COALESCE(ppa_to.entity_name, ppa_to.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa_to.ogrn,'-') || ', КПП: ' || COALESCE(ppa_to.kpp,'-')) AS ppa_to_info,
  66.    
  67.     BOOL_OR(ppa_to.entity_is_actual) AS org_to_actual
  68. FROM
  69.     sppa.ppa_access_right_requests req
  70.     JOIN sppa.ppa_delegated_access_rights r ON r.request_guid = req.guid AND r.entity_is_actual AND req.status = 'ACCEPTED' AND req.revoke_reason IS NULL
  71.     JOIN sppa.ppa_delegated_information_types it on it.access_right_guid = r.guid AND it.information_type_code IN ('35','3') -- Информация о состоянии расчетов потребителей за жилое помещение и коммунальные услуги nsism.nsi_information_types
  72.     JOIN sppa.ppa_organizations ppa_from ON ppa_from.guid = req.organization_from AND ppa_from.entity_is_actual
  73.     LEFT JOIN sppa.ppa_information_systems ppa_is ON ppa_is.guid = req.information_system_guid
  74.     JOIN sppa.ppa_organizations ppa_to ON ppa_to.guid = COALESCE(ppa_is.organization_guid, req.organization_to)
  75. WHERE
  76.     '2019-05-31'::date BETWEEN COALESCE(req.start, '1900-01-01')::date AND COALESCE(req.finish, '5000-01-01')::date -- Дата начала / окончания делегирования
  77. GROUP BY
  78.     ppa_from.organization_root_guid,
  79.     ppa_to.organization_root_guid,
  80.     it.information_type_code;
  81.  
  82. DROP TABLE IF EXISTS tmp_ppa_deleg_conflict;
  83. CREATE TEMPORARY TABLE tmp_ppa_deleg_conflict AS
  84. SELECT
  85.     d.or_organization_root_guid_from AS or_organization_root_guid_from,
  86.     d.information_type_code AS information_type_code
  87. FROM tmp_ppa_deleg d
  88. GROUP BY d.or_organization_root_guid_from, d.information_type_code
  89. HAVING COUNT(DISTINCT d.or_organization_root_guid_to) > 1;
  90.  
  91. INSERT INTO tmp_conflict_data
  92. SELECT
  93.     ARRAY_AGG(DISTINCT d.or_organization_root_guid_to) AS or_organization_root_guid_arr,
  94.     ARRAY_AGG(DISTINCT d.ppa_to_info) AS organization_name_arr,
  95.     acc.fias_house_code AS fias_house_code,
  96.     3 AS confl_type,
  97.     d.information_type_code AS information_type_code
  98. FROM
  99.     tmp_ppa_deleg d
  100.     JOIN tmp_ppa_deleg_conflict conf ON conf.or_organization_root_guid_from = d.or_organization_root_guid_from AND d.information_type_code = conf.information_type_code
  101.     JOIN tmp_ppa_deleg_acc_org acc ON d.or_organization_root_guid_from = acc.or_organization_root_guid_base
  102. GROUP BY d.or_organization_root_guid_from, d.information_type_code, acc.fias_house_code
  103. HAVING COUNT(DISTINCT d.or_organization_root_guid_to) > 1; 
  104.  
  105. INSERT INTO tmp_conflict_data
  106. SELECT DISTINCT
  107.     d.or_organization_root_guid_from || ARRAY_AGG(DISTINCT d.or_organization_root_guid_to) AS or_organization_root_guid_arr,
  108.     ARRAY_AGG(DISTINCT d.ppa_to_info) AS organization_name_arr,
  109.     acc.fias_house_code AS fias_house_code,
  110.     4 AS confl_type,
  111.     d.information_type_code AS information_type_code
  112. FROM
  113.     tmp_ppa_deleg d
  114.     JOIN tmp_ppa_deleg_acc_org acc ON d.or_organization_root_guid_from = acc.or_organization_root_guid_base
  115. WHERE d.org_to_actual = FALSE
  116. GROUP BY d.or_organization_root_guid_from, d.information_type_code, d.or_organization_root_guid_to, acc.fias_house_code;
  117.  
  118. INSERT INTO tmp_conflict_data
  119. SELECT
  120.     ARRAY_AGG(DISTINCT d.or_organization_root_guid_to) AS or_organization_root_guid_arr,
  121.     ARRAY_AGG(DISTINCT d.ppa_to_info) AS organization_name_arr,
  122.     acc.fias_house_code AS fias_house_code,
  123.     5 AS confl_type,
  124.     d.information_type_code AS information_type_code
  125. FROM
  126.     tmp_ppa_deleg d
  127.     JOIN tmp_ppa_deleg_acc_org acc ON d.or_organization_root_guid_from = acc.or_organization_root_guid_base
  128. WHERE EXISTS (SELECT 1
  129.                 FROM tmp_ppa_deleg d1
  130.                 WHERE d1.or_organization_root_guid_from = d.or_organization_root_guid_to
  131.                 AND d1.information_type_code = d.information_type_code
  132.                 AND d1.or_organization_root_guid_from <> d1.or_organization_root_guid_to -- Не учитываем делегирование самому себе
  133.                 LIMIT 1)
  134. GROUP BY d.information_type_code, d.or_organization_root_guid_to, acc.fias_house_code;
  135.  
  136. INSERT INTO tmp_conflict_data
  137. SELECT
  138. ARRAY_AGG(organization_root_guid)::uuid[] AS or_organization_root_guid_arr,
  139. ARRAY_AGG(COALESCE(ppa.entity_name, ppa.organization_short_name) || ', ОГРН/ОГРНИП: ' || COALESCE(ppa.ogrn,'-') || ', КПП: ' || COALESCE(ppa.kpp,'-'))::varchar[] AS organization_name_arr,
  140. tmp_risu.fias_house_code::uuid,
  141. 6::integer AS confl_type,
  142.     NULL::varchar(50) AS information_type_code
  143. FROM sppa.ppa_organizations ppa
  144. JOIN
  145.     (  
  146.     SELECT
  147.         fias_house_code::uuid,
  148.         management_period_from,
  149.         management_period_to,
  150.         ppa_organization_guid AS ppa_organization_guid -- Если по дому и договору в РИСУ есть 2 организации - возьмем сперва ту, которая имеет призанк обособленного подразделения
  151.     FROM
  152.         shm.hm_house_management_periods
  153.     WHERE
  154.         entity_is_actual = TRUE
  155.         AND status in ('INCLUDED','EXCLUDED')
  156.         AND '2019-05-01'::date BETWEEN COALESCE(date_trunc('month', management_period_from), '1900-01-01')::date AND COALESCE(management_period_to, '2900-01-01')::date
  157.         AND is_management_agreement_exists = FALSE -- -- Признак наличия ДУ (для ТСЖ)  (для ТСЖ/ЖСК/ЖК/Иных кооперативов: должна отсутствовать информация по дому, что «Управление многоквартирным домом осуществляется управляющей организацией по договору управления»
  158.     ) tmp_risu ON tmp_risu.ppa_organization_guid = ppa.guid AND ppa.entity_is_actual
  159. GROUP BY tmp_risu.fias_house_code
  160. HAVING
  161.     COUNT(DISTINCT ppa.organization_root_guid) > 1 -- Несколько РАЗНЫХ организаций управляли домом в одном периоде
  162.     AND MAX(tmp_risu.management_period_from) <= MIN(tmp_risu.management_period_to); --периоды пересекаются
  163.  
  164.  
  165. SELECT
  166.     h.region_code,
  167.     confl.or_organization_root_guid_arr,
  168.     confl.fias_house_code,
  169.     confl.confl_type,
  170.     confl.information_type_code
  171. FROM
  172.     tmp_conflict_data confl
  173.     JOIN corg.org_pf_336_offence_pd_tech_house_adr h ON h.fias_house_code = confl.fias_house_code
  174.     JOIN cdim.dim_admterritory adm_r ON adm_r.root_guid = h.dim_admterritory_root_guid::character varying
  175. WHERE
  176.     h.hm_house_guid IS NOT NULL
  177.     AND h.report_month = '2019-05-01'::date
  178.     AND h.region_code IS NOT NULL
  179.     AND CASE WHEN (h.region_code IN ('77','78','92') AND '2019-05-01'::date >= '2019-07-01'::date) OR (h.region_code NOT IN ('77','78','92')) THEN TRUE ELSE FALSE END
  180. GROUP BY
  181.     h.region_code,
  182.     confl.or_organization_root_guid_arr,
  183.     confl.fias_house_code,
  184.     confl.confl_type,
  185.     confl.information_type_code
  186. HAVING COUNT(*) > 1
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top