Skip to content

Instantly share code, notes, and snippets.

@kovenko

kovenko/12889 сф

Last active June 3, 2020 19:55
Show Gist options
  • Save kovenko/477e48f2cf02b3aba2d3d2906d2bfa3a to your computer and use it in GitHub Desktop.
Save kovenko/477e48f2cf02b3aba2d3d2906d2bfa3a to your computer and use it in GitHub Desktop.
Download ZIP
12889 сф
Raw
12889 сф
module/Common/Main/src/Main/Service/HousesPassportUpdater/UpdateableTargets/AgfAfter2012Houses.php:72
/Version20161006143035_6899_update_ajf_houses.php:1
resettlement_status_id
SELECT * FROM refs WHERE parent_id = 594
blank, resettlement_await, owner_wanted, empty, in_court, resettled
SELECT
h.id AS house_id,
h.date_created,
hr.id AS house_revision_id,
hqrs.id AS snapshot_id
FROM houses h
JOIN house_revision hr ON hr.houses_id = h.id AND hr.is_current = 1
LEFT JOIN house_quarters_residents_snapshots hqrs ON hqrs.house_revision_id = hr.id AND hqrs.is_main
WHERE h.deleted_at IS NULL AND h.id = 9317262
SELECT
-- первые 2 поля обновляют таблицу UPDATE emrg_housing_docs_aftr_2012_house_rev_rel rel_u
hr.id AS house_revision_id,
hqrs.id AS snapshot_id,
rel_f.id AS rel_u_id,
doc.id AS doc_id,
(SELECT id FROM emrg_housing_documents_aftr_2012 doc2 WHERE doc2.geo_tag_id = doc.geo_tag_id ORDER BY doc2.created_at DESC LIMIT 1) AS doc2_id,
doc.geo_tag_id AS doc_geo_tag_id,
h.id AS house_id
FROM emrg_housing_docs_aftr_2012_house_rev_rel rel_f
JOIN emrg_housing_docs_aftr_2012_mun_areas mo ON rel_f.municipal_area_id = mo.id
JOIN emrg_housing_documents_aftr_2012 doc ON mo.document_id = doc.id
JOIN houses h ON rel_f.house_id = h.id
JOIN house_revision hr ON h.id = hr.houses_id AND hr.is_current = 1
LEFT JOIN epd_house_passports hp ON hp.house_revision_id = rel_f.house_revision_id
LEFT JOIN refs refs_house_type ON refs_house_type.id = hp.house_type_id
LEFT JOIN house_quarters_residents_snapshots hqrs ON hr.id = hqrs.house_revision_id AND hqrs.is_main
WHERE
h.id = 9317262
AND rel_f.house_revision_id <> hr.id
AND rel_f.removed_at IS NULL
AND mo.deleted_at IS NULL
AND (refs_house_type.code <> '0505' OR refs_house_type.code IS NULL)
AND doc.id =
CASE
WHEN :currentAgfDocumentId :: INT IS NOT NULL
THEN :currentAgfDocumentId :: INT
ELSE (SELECT id FROM emrg_housing_documents_aftr_2012 doc2 WHERE doc2.geo_tag_id = doc.geo_tag_id ORDER BY doc2.created_at DESC LIMIT 1)
END
--:monthlyReportMonitoring = 27
--:reportingPeriodId = 0
--:fromDate = '2020-01-01'
--:toDate = '2021-01-01'
--:geoTagId = 2220005
WITH cte_report_periods AS (
SELECT
array_agg(id) AS ids,
min(date_start) AS date_start,
max(date_end) AS date_end
FROM reporting_periods rp
WHERE rp.type = :monthlyReportMonitoring AND (
(:reportingPeriodId <> 0 AND rp.id = :reportingPeriodId) OR
(
:reportingPeriodId = 0 AND rp.date_start >= '2020-04-01' :: DATE AND
(rp.date_start BETWEEN coalesce(:fromDate :: DATE, '-infinity') AND coalesce(:toDate :: DATE, 'infinity')) AND
(rp.date_end BETWEEN coalesce(:fromDate :: DATE, '-infinity') AND coalesce(:toDate :: DATE, 'infinity'))
)
)
), cte_program_objects AS (
SELECT DISTINCT ON (vpd.id)
rpo.id AS regional_program_object_id,
sf.id AS sf_group,
sf.name AS sf_name,
mo.id AS mo_group,
mo.name AS mo_name,
vpd.documents_name AS documents_name
FROM mski_monitoring.monitoring m
JOIN mski_monitoring.regional_program_objects rpo ON rpo.monitoring_id = m.id AND rpo.deleted_at IS NULL
JOIN public.reporting_periods rp ON rp.id = m.reporting_period_id
JOIN geo_tags sf ON sf.id = m.geo_tag_id
JOIN geo_tags mo ON mo.id = rpo.geo_tag_id
JOIN mski_monitoring.regional_program_objects_volga rpov ON rpov.id = rpo.id
JOIN volga_river_recovery.volga_program_documents vpd ON vpd.id = rpov.program_document_id AND vpd.deleted_at IS NULL
JOIN mski_monitoring.volga_contracts vc ON vc.program_document_id = vpd.id
JOIN mski_monitoring.contracts c ON c.id = vc.id AND c.deleted_at IS NULL
JOIN mski_monitoring.payment_documents pd ON pd.contracts_id = c.id AND pd.deleted_at IS NULL
WHERE m.type = 'vrr' AND m.geo_tag_id = :geoTagId
AND pd.payment_date BETWEEN (SELECT date_start FROM cte_report_periods) AND (SELECT date_end FROM cte_report_periods)
ORDER BY vpd.id, rp.date_start DESC
)
SELECT
ROW_NUMBER() OVER (
PARTITION BY cpo.mo_name
ORDER BY cpo.mo_name, cpo.documents_name
) AS row_num,
1 AS parent_group,
cpo.sf_group AS sf_group,
cpo.sf_name AS sf_name,
cpo.mo_group AS mo_group,
cpo.mo_name AS mo_name,
cpo.documents_name AS documents_name,
cery.smr_plan_total / 1000.0 AS c_smr_4,
cery.smr_fact_total / 1000.0 AS c_smr_5,
cery.smr_fact_advance_payment / 1000.0 AS c_smr_6,
cery.smr_federal_budget_plan_total / 1000.0 AS c_smr_7,
cery.smr_federal_budget_fact_total / 1000.0 AS c_smr_8,
cery.smr_federal_budget_fact_advance_payment / 1000.0 AS c_smr_9,
cery.smr_consolidated_budget_plan_total / 1000.0 AS c_smr_10,
cery.smr_consolidated_budget_fact_total / 1000.0 AS c_smr_11,
cery.smr_consolidated_budget_fact_advance_payment / 1000.0 AS c_smr_12,
cery.smr_extrabudgetary_budget_plan_total / 1000.0 AS c_smr_13,
cery.smr_extrabudgetary_budget_fact_total / 1000.0 AS c_smr_14,
cery.smr_plan_total / 1000.0 AS c_smr_15,
cery.smr_fact_total / 1000.0 AS c_smr_16,
cery.smr_fact_advance_payment / 1000.0 AS c_smr_17,
cery.smr_federal_budget_plan_total / 1000.0 AS c_smr_18,
cery.smr_federal_budget_fact_total / 1000.0 AS c_smr_19,
cery.smr_federal_budget_fact_advance_payment / 1000.0 AS c_smr_20,
cery.smr_consolidated_budget_plan_total / 1000.0 AS c_smr_21,
cery.smr_consolidated_budget_fact_total / 1000.0 AS c_smr_22,
cery.smr_consolidated_budget_fact_advance_payment / 1000.0 AS c_smr_23,
cery.pd_plan_total / 1000.0 AS c_pd_4,
cery.pd_fact_total / 1000.0 AS c_pd_5,
cery.pd_fact_advance_payment / 1000.0 AS c_pd_6,
cery.pd_federal_budget_plan_total / 1000.0 AS c_pd_7,
cery.pd_federal_budget_fact_total / 1000.0 AS c_pd_8,
cery.pd_federal_budget_fact_advance_payment / 1000.0 AS c_pd_9,
cery.pd_consolidated_budget_plan_total / 1000.0 AS c_pd_10,
cery.pd_consolidated_budget_fact_total / 1000.0 AS c_pd_11,
cery.pd_consolidated_budget_fact_advance_payment / 1000.0 AS c_pd_12,
cery.pd_extrabudgetary_budget_plan_total / 1000.0 AS c_pd_13,
cery.pd_extrabudgetary_budget_fact_total / 1000.0 AS c_pd_14,
cery.pd_plan_total / 1000.0 AS c_pd_15,
cery.pd_fact_total / 1000.0 AS c_pd_16,
cery.pd_fact_advance_payment / 1000.0 AS c_pd_17,
cery.pd_federal_budget_plan_total / 1000.0 AS c_pd_18,
cery.pd_federal_budget_fact_total / 1000.0 AS c_pd_19,
cery.pd_federal_budget_fact_advance_payment / 1000.0 AS c_pd_20,
cery.pd_consolidated_budget_plan_total / 1000.0 AS c_pd_21,
cery.pd_consolidated_budget_fact_total / 1000.0 AS c_pd_22,
cery.pd_consolidated_budget_fact_advance_payment / 1000.0 AS c_pd_23
FROM cte_program_objects cpo
LEFT JOIN mski_monitoring.cash_expense_on_reporting_year cery ON cery.regional_program_object_id = cpo.regional_program_object_id
ORDER BY cpo.mo_name, cpo.documents_name
старая версия
--:monthlyReportMonitoring = 27
--:reportingPeriodId = 0
--:fromDate = '2000-01-01'
--:toDate = '2021-01-01'
--:geoTagId = 2220005
WITH cte_report_periods AS (
SELECT
array_agg(id) AS ids,
min(date_start) AS date_start,
max(date_end) AS date_end
FROM reporting_periods rp
WHERE rp.type = :monthlyReportMonitoring AND (
(:reportingPeriodId <> 0 AND rp.id = :reportingPeriodId) OR
(
:reportingPeriodId = 0 AND rp.date_start >= '2020-04-01' :: DATE AND
(rp.date_start BETWEEN coalesce(:fromDate :: DATE, '-infinity') AND coalesce(:toDate :: DATE, 'infinity')) AND
(rp.date_end BETWEEN coalesce(:fromDate :: DATE, '-infinity') AND coalesce(:toDate :: DATE, 'infinity'))
)
)
), cte_last_monitoring AS (
SELECT
m.id, m.regional_program_id, m.geo_tag_id, m.reporting_period_id
FROM volga_river_recovery.regional_programs rp
JOIN mski_monitoring.monitoring m ON m.type = 'vrr' AND m.regional_program_id = rp.id AND m.geo_tag_id = rp.geo_tag_id
JOIN cte_report_periods t ON m.reporting_period_id = ANY (t.ids)
GROUP BY m.regional_program_id, m.geo_tag_id, m.id, t.date_start
ORDER BY t.date_start DESC
LIMIT 1
), cte_data AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY sf.name, mo.name
ORDER BY sf.name, mo.name, vpd.documents_name
) AS row_num,
1 AS parent_group,
sf.id AS sf_group,
sf.name AS sf_name,
mo.id AS mo_group,
mo.name AS mo_name,
vpd.documents_name AS documents_name,
cery.smr_plan_total / 1000.0 AS c_smr_4,
cery.smr_fact_total / 1000.0 AS c_smr_5,
cery.smr_fact_advance_payment / 1000.0 AS c_smr_6,
cery.smr_federal_budget_plan_total / 1000.0 AS c_smr_7,
cery.smr_federal_budget_fact_total / 1000.0 AS c_smr_8,
cery.smr_federal_budget_fact_advance_payment / 1000.0 AS c_smr_9,
cery.smr_consolidated_budget_plan_total / 1000.0 AS c_smr_10,
cery.smr_consolidated_budget_fact_total / 1000.0 AS c_smr_11,
cery.smr_consolidated_budget_fact_advance_payment / 1000.0 AS c_smr_12,
cery.smr_extrabudgetary_budget_plan_total / 1000.0 AS c_smr_13,
cery.smr_extrabudgetary_budget_fact_total / 1000.0 AS c_smr_14,
cery.smr_plan_total / 1000.0 AS c_smr_15,
cery.smr_fact_total / 1000.0 AS c_smr_16,
cery.smr_fact_advance_payment / 1000.0 AS c_smr_17,
cery.smr_federal_budget_plan_total / 1000.0 AS c_smr_18,
cery.smr_federal_budget_fact_total / 1000.0 AS c_smr_19,
cery.smr_federal_budget_fact_advance_payment / 1000.0 AS c_smr_20,
cery.smr_consolidated_budget_plan_total / 1000.0 AS c_smr_21,
cery.smr_consolidated_budget_fact_total / 1000.0 AS c_smr_22,
cery.smr_consolidated_budget_fact_advance_payment / 1000.0 AS c_smr_23,
cery.pd_plan_total / 1000.0 AS c_pd_4,
cery.pd_fact_total / 1000.0 AS c_pd_5,
cery.pd_fact_advance_payment / 1000.0 AS c_pd_6,
cery.pd_federal_budget_plan_total / 1000.0 AS c_pd_7,
cery.pd_federal_budget_fact_total / 1000.0 AS c_pd_8,
cery.pd_federal_budget_fact_advance_payment / 1000.0 AS c_pd_9,
cery.pd_consolidated_budget_plan_total / 1000.0 AS c_pd_10,
cery.pd_consolidated_budget_fact_total / 1000.0 AS c_pd_11,
cery.pd_consolidated_budget_fact_advance_payment / 1000.0 AS c_pd_12,
cery.pd_extrabudgetary_budget_plan_total / 1000.0 AS c_pd_13,
cery.pd_extrabudgetary_budget_fact_total / 1000.0 AS c_pd_14,
cery.pd_plan_total / 1000.0 AS c_pd_15,
cery.pd_fact_total / 1000.0 AS c_pd_16,
cery.pd_fact_advance_payment / 1000.0 AS c_pd_17,
cery.pd_federal_budget_plan_total / 1000.0 AS c_pd_18,
cery.pd_federal_budget_fact_total / 1000.0 AS c_pd_19,
cery.pd_federal_budget_fact_advance_payment / 1000.0 AS c_pd_20,
cery.pd_consolidated_budget_plan_total / 1000.0 AS c_pd_21,
cery.pd_consolidated_budget_fact_total / 1000.0 AS c_pd_22,
cery.pd_consolidated_budget_fact_advance_payment / 1000.0 AS c_pd_23
FROM volga_river_recovery.regional_programs rp
JOIN cte_last_monitoring m ON m.regional_program_id = rp.id AND m.geo_tag_id = rp.geo_tag_id
JOIN mski_monitoring.regional_program_objects rpo ON rpo.monitoring_id = m.id AND rpo.deleted_at IS NULL
JOIN mski_monitoring.regional_program_objects_volga rpov ON rpov.id = rpo.id
JOIN volga_river_recovery.volga_program_documents vpd ON vpd.id = rpov.program_document_id AND vpd.deleted_at IS NULL
JOIN geo_tags sf ON sf.id = rp.geo_tag_id
JOIN geo_tags mo ON mo.id = rpo.geo_tag_id
LEFT JOIN mski_monitoring.cash_expense_on_reporting_year cery ON cery.regional_program_object_id = rpo.id
LEFT JOIN cte_report_periods t ON m.reporting_period_id = ANY (t.ids)
LEFT JOIN mski_monitoring.payment_documents pd ON pd.reporting_period_id = m.reporting_period_id AND pd.deleted_at IS NULL
AND pd.mo_geo_tag_id = rpo.geo_tag_id AND (pd.payment_date BETWEEN t.date_start AND t.date_end)
WHERE rp.deleted_at IS NULL AND rp.geo_tag_id IN (:geoTagId)
ORDER BY sf.name, mo.name, vpd.documents_name
)
SELECT * FROM cte_data
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment