API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 13th, 2019
90
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 19.54 KB | None | 0 0
raw download clone embed print report
  1. with
  2. rp as (
  3. select id,CASE WHEN month = 1 THEN 13 ELSE month END as "month" from reporting_periods where reporting_periods.id = (select max(id) as "id" from reporting_periods where type = '15')
  4. ),
  5. prev_month as (select
  6. geo_tag_id as "prev_geo_tag_id",
  7.  
  8. charge_payment_owner_start_period as "prev_charge_payment_owner_start_period",
  9. collected_payment_owner_start_period as "prev_collected_payment_owner_start_period",
  10. cost_of_completed_work as "prev_cost_of_completed_work",
  11. completed_work_houses_count as "prev_completed_work_houses_count",
  12. costs_of_work_in_current_year as "prev_costs_of_work_in_current_year",
  13. completed_work_current_period_costs as "prev_completed_work_current_period_costs",
  14. completed_work_current_period_houses_count as "prev_completed_work_current_period_houses_count",
  15. completed_work_current_period_houses_area as "prev_completed_work_current_period_houses_area",
  16. completed_work_current_period_residents_count as "prev_completed_work_current_period_residents_count",
  17. completed_work_current_period_services_count as "prev_completed_work_current_period_services_count",
  18. completed_installation_work_houses_count as "prev_completed_installation_work_houses_count",
  19. completed_installation_work_houses_area as "prev_completed_installation_work_houses_area",
  20. completed_installation_work_residents_count as "prev_completed_installation_work_residents_count"
  21. from overhaul_documents_kr1_2017_871red where reporting_period_id=(select max(id) as "id" from reporting_periods where type = '15' and id != (select max(id) as "id" from reporting_periods where type = '15'))
  22. ),
  23. countstat as (
  24. select overhauldocument_id as id,count(*) from overhaul_documents_status_changes_relations
  25. join status_changes on status_changes.id = overhaul_documents_status_changes_relations.statuschange_id and status_changes.to_status_id =390
  26. where is_successfully is true
  27. group by overhauldocument_id
  28. ),
  29. timeline as ( -- информация за предыдущий отчетный период КР-2
  30. select
  31. geo_tag_id,
  32. CASE WHEN geo_tag_id = '2208163' THEN 4 WHEN geo_tag_id = '2215422' THEN 6 WHEN geo_tag_id = '2216073' THEN 0 WHEN geo_tag_id = '2220005' THEN 0 WHEN geo_tag_id = '2220463' THEN 0 WHEN geo_tag_id = '2222089' THEN 0 WHEN geo_tag_id = '2224825' THEN 0 WHEN geo_tag_id = '2227349' THEN 0 WHEN geo_tag_id = '2228920' THEN 0 WHEN geo_tag_id = '2236864' THEN 0 WHEN geo_tag_id = '2280999' THEN 0 WHEN geo_tag_id = '2276347' THEN 0 WHEN geo_tag_id = '2399515' THEN 0 WHEN geo_tag_id = '2361205' THEN 7 WHEN geo_tag_id = '2333436' THEN 6 WHEN geo_tag_id = '2243734' THEN 0 WHEN geo_tag_id = '2246043' THEN 5 WHEN geo_tag_id = '2347352' THEN 0 WHEN geo_tag_id = '2247652' THEN -1 WHEN geo_tag_id = '2258331' THEN 0 WHEN geo_tag_id = '2261569' THEN 9 WHEN geo_tag_id = '2352939' THEN 0 WHEN geo_tag_id = '2261688' THEN 4 WHEN geo_tag_id = '2262823' THEN 0 WHEN geo_tag_id = '2267309' THEN 0 WHEN geo_tag_id = '2209858' THEN 0 WHEN geo_tag_id = '2211647' THEN 4 WHEN geo_tag_id = '2272233' THEN 2 WHEN geo_tag_id = '2273507' THEN 0 WHEN geo_tag_id = '2276351' THEN 0 WHEN geo_tag_id = '2279312' THEN 0 WHEN geo_tag_id = '2280928' THEN 8 WHEN geo_tag_id = '2281126' THEN 0 WHEN geo_tag_id = '2287468' THEN 0 WHEN geo_tag_id = '2361342' THEN 0 WHEN geo_tag_id = '2238753' THEN 0 WHEN geo_tag_id = '2287629' THEN 0 WHEN geo_tag_id = '2290273' THEN 4 WHEN geo_tag_id = '2291899' THEN 3 WHEN geo_tag_id = '2293481' THEN 2 WHEN geo_tag_id = '2295251' THEN 0 WHEN geo_tag_id = '2298217' THEN 0 WHEN geo_tag_id = '2299744' THEN 2 WHEN geo_tag_id = '2213474' THEN 7 WHEN geo_tag_id = '2303372' THEN 0 WHEN geo_tag_id = '2340164' THEN 0 WHEN geo_tag_id = '2347543' THEN 4 WHEN geo_tag_id = '2340399' THEN 2 WHEN geo_tag_id = '2345009' THEN 5 WHEN geo_tag_id = '2345675' THEN 0 WHEN geo_tag_id = '2247643' THEN 0 WHEN geo_tag_id = '2347799' THEN 0 WHEN geo_tag_id = '2348078' THEN 0 WHEN geo_tag_id = '2348894' THEN 0 WHEN geo_tag_id = '2399489' THEN 0 WHEN geo_tag_id = '2349746' THEN 0 WHEN geo_tag_id = '2351376' THEN 0 WHEN geo_tag_id = '2360536' THEN 6 WHEN geo_tag_id = '2352709' THEN 0 WHEN geo_tag_id = '2353101' THEN 0 WHEN geo_tag_id = '2356269' THEN 4 WHEN geo_tag_id = '2358459' THEN 4 WHEN geo_tag_id = '2310204' THEN 0 WHEN geo_tag_id = '2312245' THEN 0 WHEN geo_tag_id = '2270853' THEN 1 WHEN geo_tag_id = '2315056' THEN 1 WHEN geo_tag_id = '2316924' THEN 8 WHEN geo_tag_id = '2317157' THEN 2 WHEN geo_tag_id = '2319070' THEN 0 WHEN geo_tag_id = '2214158' THEN 0 WHEN geo_tag_id = '2323682' THEN 0 WHEN geo_tag_id = '2248754' THEN 0 WHEN geo_tag_id = '2325436' THEN 4 WHEN geo_tag_id = '2326046' THEN 0 WHEN geo_tag_id = '2329523' THEN 2 WHEN geo_tag_id = '2356486' THEN 1 WHEN geo_tag_id = '2331106' THEN 1 WHEN geo_tag_id = '2214950' THEN 7 WHEN geo_tag_id = '2330826' THEN 2 WHEN geo_tag_id = '2332121' THEN 2 WHEN geo_tag_id = '2358750' THEN 0 WHEN geo_tag_id = '2358768' THEN 0 WHEN geo_tag_id = '2334262' THEN 9 WHEN geo_tag_id = '2331019' THEN 2 WHEN geo_tag_id = '2334335' THEN 0 ELSE null END as "time"
  33. from overhaul_documents_kr2_2017_871red where overhaul_documents_kr2_2017_871red.reporting_period_id = 255
  34. )
  35.  
  36. select
  37. geo_tags.name as "Субъект РФ",
  38. timeline.time,
  39. countstat.count as "Возвр",
  40. statuses.name::text as "Статус",
  41.  
  42. /*
  43. kr2.minimum_payment_size_min||'|'||
  44. kr2.minimum_payment_size_max||'|'||
  45.  
  46. round(charge_payment_owner_start_period*1000/kr2.quarters_area/8::numeric,2) ||'|'||
  47.  
  48. round((charge_payment_owner_start_period*1000/kr2.quarters_area/8)/((kr2.minimum_payment_size_min+kr2.minimum_payment_size_max)/2)*100::numeric,2)||'|'||
  49. round(kr2.mkd_area_on_operator_account/kr2.cp_mkd_square_on_reporting_period*100::numeric,2),*/
  50.  
  51. round(completed_work_current_period_services_count/(service_count_plan+service_count_plan_end)::numeric*100,0)||'|'||
  52. round(completed_work_current_period_costs/cost_of_completed_work*100,0)||'|'||
  53. round(completed_work_current_period_houses_count/completed_work_houses_count::numeric*100,0)||'|'||
  54. round(completed_work_current_period_houses_area/(mkd_area_plan+mkd_area_plan_end)*100,0)||'|'||
  55. round(completed_work_current_period_residents_count/(ppl_count_plan+ppl_count_plan_end)*100,0)
  56. as "done",
  57.  
  58. CASE WHEN completed_work_current_period_services_count < completed_work_current_period_houses_count
  59. THEN (completed_work_current_period_services_count)||'<'||(completed_work_current_period_houses_count)
  60. ELSE '' END as "!mkd_СМР<",
  61.  
  62. CASE WHEN charge_payment_owner_start_period =0 or charge_payment_owner_start_period is null THEN '!n/a'
  63. WHEN (charge_payment_owner_start_period::numeric-prev_charge_payment_owner_start_period::numeric)>(prev_charge_payment_owner_start_period::numeric/(rp.month-1)::numeric)*1.5 THEN '+50%'
  64. WHEN (charge_payment_owner_start_period::numeric-prev_charge_payment_owner_start_period::numeric)>(prev_charge_payment_owner_start_period::numeric/(rp.month-1)::numeric)*1.8 THEN '!+80%'
  65. WHEN (charge_payment_owner_start_period::numeric-prev_charge_payment_owner_start_period::numeric)<(prev_charge_payment_owner_start_period::numeric/(rp.month-1)::numeric)*0.2 THEN '!-80%'
  66. WHEN (charge_payment_owner_start_period::numeric-prev_charge_payment_owner_start_period::numeric)<(prev_charge_payment_owner_start_period::numeric/(rp.month-1)::numeric)*0.5 THEN '-50%'
  67. ELSE '' END as "гр.1",
  68.  
  69. CASE WHEN collected_payment_owner_start_period =0 or collected_payment_owner_start_period is null THEN '!n/a'
  70. WHEN (collected_payment_owner_start_period::numeric-prev_collected_payment_owner_start_period::numeric)>(prev_collected_payment_owner_start_period::numeric/(rp.month-1)::numeric)*1.5 THEN '+50%'
  71. WHEN (collected_payment_owner_start_period::numeric-prev_collected_payment_owner_start_period::numeric)>(prev_collected_payment_owner_start_period::numeric/(rp.month-1)::numeric)*1.8 THEN '!+80%'
  72. WHEN (collected_payment_owner_start_period::numeric-prev_collected_payment_owner_start_period::numeric)<(prev_collected_payment_owner_start_period::numeric/(rp.month-1)::numeric)*0.2 THEN '!-80%'
  73. WHEN (collected_payment_owner_start_period::numeric-prev_collected_payment_owner_start_period::numeric)<(prev_collected_payment_owner_start_period::numeric/(rp.month-1)::numeric)*0.5 THEN '-50%'
  74. ELSE '' END as "гр.2",
  75.  
  76. CASE WHEN charge_payment_owner_start_period =0 or charge_payment_owner_start_period is null or collected_payment_owner_start_period =0 or collected_payment_owner_start_period is null THEN '!n/a'
  77. WHEN charge_payment_owner_start_period::numeric-collected_payment_owner_start_period::numeric<0 THEN '!min'
  78. ELSE '' END as "гр.2/1",
  79.  
  80. CASE WHEN round(prev_cost_of_completed_work,1) = round(cost_of_completed_work,1) THEN null
  81. ELSE round(prev_cost_of_completed_work,1)||'->'||round(cost_of_completed_work,1) END as "гр.3",
  82.  
  83. CASE WHEN prev_completed_work_houses_count = completed_work_houses_count THEN null
  84. ELSE prev_completed_work_houses_count||' -> '||completed_work_houses_count END as "гр.4",
  85.  
  86. CASE WHEN cost_of_completed_work is null or completed_work_houses_count is null or completed_work_houses_count=0 THEN 'n/a'
  87. WHEN cost_of_completed_work/completed_work_houses_count>8 or cost_of_completed_work/completed_work_houses_count<1 THEN '! '||(round(cost_of_completed_work/completed_work_houses_count*1000,0))::text||' !'
  88. ELSE (round(cost_of_completed_work/completed_work_houses_count*1000,0))::text END as "гр.3/4",
  89.  
  90. CASE WHEN round(prev_costs_of_work_in_current_year,1) = round(costs_of_work_in_current_year,1) THEN null
  91. ELSE round(prev_costs_of_work_in_current_year,1)||' -> '||round(costs_of_work_in_current_year,1) END||CASE WHEN prev_costs_of_work_in_current_year>costs_of_work_in_current_year THEN '['||round((costs_of_work_in_current_year/prev_costs_of_work_in_current_year-1)*100,0)||']' ELSE '' END as "гр.5",
  92.  
  93. CASE WHEN completed_work_current_period_services_count is null or completed_work_current_period_costs is null THEN '!n/a'
  94. WHEN completed_work_current_period_services_count=0 and completed_work_current_period_costs=0 THEN '0'
  95. WHEN completed_work_current_period_services_count=0 or completed_work_current_period_costs=0 THEN '0!=0'
  96. WHEN completed_work_current_period_services_count=prev_completed_work_current_period_services_count or completed_work_current_period_costs=prev_completed_work_current_period_costs THEN
  97. CASE WHEN completed_work_current_period_services_count=prev_completed_work_current_period_services_count and completed_work_current_period_costs=prev_completed_work_current_period_costs THEN '' ELSE '6!=6 or 7!=7'
  98. END
  99. WHEN completed_work_current_period_costs::numeric/completed_work_current_period_services_count::numeric*1000>9000 THEN '!9000+'
  100. WHEN completed_work_current_period_costs::numeric/completed_work_current_period_services_count::numeric*1000<50 THEN '!50-'
  101. WHEN completed_work_current_period_costs::numeric/completed_work_current_period_services_count::numeric*1000>5000 THEN '5000+'
  102. WHEN completed_work_current_period_costs::numeric/completed_work_current_period_services_count::numeric*1000<100 THEN '100-'
  103. ELSE '' END as "гр.7/6",
  104.  
  105. CASE WHEN completed_work_current_period_services_count=0 or completed_work_current_period_costs=0 THEN null
  106. ELSE round(completed_work_current_period_costs::numeric/completed_work_current_period_services_count::numeric*1000,0) END as "7/6",
  107.  
  108.  
  109. CASE WHEN completed_work_current_period_houses_count-prev_completed_work_current_period_houses_count = completed_installation_work_houses_count-prev_completed_work_current_period_houses_count THEN
  110. CASE WHEN completed_work_current_period_houses_area-prev_completed_work_current_period_houses_area != completed_installation_work_houses_area-prev_completed_installation_work_houses_area THEN 'sq'
  111. WHEN completed_work_current_period_residents_count-prev_completed_work_current_period_residents_count != completed_installation_work_residents_count-prev_completed_installation_work_residents_count THEN 'ppl'
  112. else '' END
  113. else '' END as "гр.8,9,10",
  114.  
  115.  
  116. CASE WHEN completed_work_current_period_houses_count=0
  117. or completed_work_current_period_houses_area=0
  118. or completed_work_current_period_residents_count=0
  119. THEN CASE WHEN completed_work_current_period_houses_count=0
  120. and completed_work_current_period_houses_area=0
  121. and completed_work_current_period_residents_count=0 THEN '' ELSE '!8(9,10)!=0' END
  122. ELSE '' END as "8,9,10",
  123.  
  124. CASE WHEN completed_work_current_period_houses_area is null or completed_work_current_period_houses_count is null THEN '!n/a'
  125. WHEN completed_work_current_period_houses_area::numeric=0 or completed_work_current_period_houses_count::numeric=0 THEN ''
  126. WHEN completed_work_current_period_houses_area::numeric/completed_work_current_period_houses_count::numeric*1000<500 THEN '500-'
  127. WHEN completed_work_current_period_houses_area::numeric/completed_work_current_period_houses_count::numeric*1000<300 THEN '!300-'
  128. WHEN completed_work_current_period_houses_area::numeric/completed_work_current_period_houses_count::numeric*1000>8000 THEN '8000+'
  129. WHEN completed_work_current_period_houses_area::numeric/completed_work_current_period_houses_count::numeric*1000>10000 THEN '!10000+'
  130. ELSE '' END as "гр.9/8",
  131.  
  132. CASE WHEN completed_work_current_period_residents_count is null or completed_work_current_period_houses_count is null THEN '!n/a'
  133. WHEN completed_work_current_period_residents_count::numeric=0 or completed_work_current_period_houses_count::numeric=0 THEN ''
  134. WHEN completed_work_current_period_residents_count::numeric/completed_work_current_period_houses_count::numeric*1000<11 THEN '11-'
  135. WHEN completed_work_current_period_residents_count::numeric/completed_work_current_period_houses_count::numeric*1000<8 THEN '!8-'
  136. WHEN completed_work_current_period_residents_count::numeric/completed_work_current_period_houses_count::numeric*1000>200 THEN '200+'
  137. WHEN completed_work_current_period_residents_count::numeric/completed_work_current_period_houses_count::numeric*1000>500 THEN '!500+'
  138. ELSE '' END as "гр.10/8",
  139.  
  140. CASE WHEN completed_installation_work_houses_count=0
  141. or completed_installation_work_houses_area=0
  142. or completed_installation_work_residents_count=0
  143. THEN CASE WHEN completed_installation_work_houses_count=0
  144. and completed_installation_work_houses_area=0
  145. and completed_installation_work_residents_count=0 THEN '' ELSE '!9(10,11)!=0' END
  146. ELSE '' END as "11,12,13",
  147.  
  148. CASE WHEN completed_installation_work_houses_area is null or completed_installation_work_houses_count is null THEN '!n/a'
  149. WHEN completed_installation_work_houses_area::numeric=0 or completed_installation_work_houses_count::numeric=0 THEN ''
  150. WHEN completed_installation_work_houses_area::numeric/completed_installation_work_houses_count::numeric*1000<500 THEN '500-'
  151. WHEN completed_installation_work_houses_area::numeric/completed_installation_work_houses_count::numeric*1000<300 THEN '!300-'
  152. WHEN completed_installation_work_houses_area::numeric/completed_installation_work_houses_count::numeric*1000>8000 THEN '8000+'
  153. WHEN completed_installation_work_houses_area::numeric/completed_installation_work_houses_count::numeric*1000>10000 THEN '!10000+'
  154. ELSE '' END as "гр.12/11",
  155.  
  156. CASE WHEN completed_installation_work_residents_count is null or completed_installation_work_houses_count is null THEN '!n/a'
  157. WHEN completed_installation_work_residents_count::numeric=0 or completed_installation_work_houses_count::numeric=0 THEN ''
  158. WHEN completed_installation_work_residents_count::numeric/completed_installation_work_houses_count::numeric*1000<11 THEN '11-'
  159. WHEN completed_installation_work_residents_count::numeric/completed_installation_work_houses_count::numeric*1000<8 THEN '!8-'
  160. WHEN completed_installation_work_residents_count::numeric/completed_installation_work_houses_count::numeric*1000>200 THEN '200+'
  161. WHEN completed_installation_work_residents_count::numeric/completed_installation_work_houses_count::numeric*1000>500 THEN '!500+'
  162. ELSE '' END as "гр.13/11",
  163.  
  164.  
  165. CASE WHEN completed_work_current_period_houses_count-completed_installation_work_houses_count=0
  166. or completed_work_current_period_houses_area-completed_installation_work_houses_area=0
  167. or completed_work_current_period_residents_count-completed_installation_work_residents_count=0
  168. THEN CASE WHEN completed_work_current_period_houses_count-completed_installation_work_houses_count=0
  169. and completed_work_current_period_houses_area-completed_installation_work_houses_area=0
  170. and completed_work_current_period_residents_count-completed_installation_work_residents_count=0 THEN '' ELSE '!5(6,7)!=9(10,11)' END
  171. ELSE '' END as "8,9,10,11,12,13",
  172.  
  173.  
  174. round(prev_charge_payment_owner_start_period::numeric/(rp.month-1)::numeric,2)::text||'['||(round(charge_payment_owner_start_period::numeric-prev_charge_payment_owner_start_period::numeric,2))::text||']' as "1|среднее в году",
  175. round(charge_payment_owner_start_period,2) as "1|текущ.мес",
  176.  
  177. round(prev_collected_payment_owner_start_period::numeric/(rp.month-1)::numeric,2)::text||'['||(round(collected_payment_owner_start_period::numeric-prev_collected_payment_owner_start_period::numeric,2))::text||']' as "2|среднее в году",
  178. round(collected_payment_owner_start_period,2) as "2|текущ.мес.",
  179.  
  180. round(prev_cost_of_completed_work,2) as "3|пред.мес",
  181. round(cost_of_completed_work,2) as "3|текущ.мес",
  182.  
  183. prev_completed_work_houses_count as "4|пред.мес",
  184. completed_work_houses_count as "4|текущ.мес",
  185.  
  186. round(prev_costs_of_work_in_current_year,2) as "5|пред.мес",
  187. round(costs_of_work_in_current_year,2) as "5|текущ.мес",
  188.  
  189. prev_completed_work_current_period_services_count as "6|пред.мес",
  190. completed_work_current_period_services_count as "6|текущ.мес",
  191.  
  192. round(prev_completed_work_current_period_costs,2) as "7|пред.мес",
  193. round(completed_work_current_period_costs,2) as "7|текущ.мес",
  194.  
  195. prev_completed_work_current_period_houses_count as "8|пред.мес",
  196. completed_work_current_period_houses_count as "8|текущ.мес",
  197.  
  198. prev_completed_work_current_period_houses_area as "9|пред.мес",
  199. completed_work_current_period_houses_area as "9|текущ.мес",
  200.  
  201. prev_completed_work_current_period_residents_count as "10|пред.мес",
  202. completed_work_current_period_residents_count as "10|текущ.мес",
  203.  
  204. prev_completed_installation_work_houses_count as "11|пред.мес",
  205. completed_installation_work_houses_count as "11|текущ.мес",
  206.  
  207. prev_completed_installation_work_houses_area as "12|пред.мес",
  208. completed_installation_work_houses_area as "12|текущ.мес",
  209.  
  210. prev_completed_installation_work_residents_count as "13|пред.мес",
  211. completed_installation_work_residents_count as "13|текущ.мес"
  212.  
  213.  
  214.  
  215. from
  216. overhaul_documents_kr1_2017_871red as kr1
  217. join prev_month on prev_month.prev_geo_tag_id = kr1.geo_tag_id
  218. join statuses on statuses.id = kr1.status_id
  219. join geo_tags on geo_tags.id = kr1.geo_tag_id
  220. join rp on rp.id = kr1.reporting_period_id
  221. join overhaul_documents_kr2_2017_871red kr2 on (kr1.geo_tag_id = kr2.geo_tag_id and kr2.reporting_period_id = (select max(id) as "id" from reporting_periods where type = '16'))
  222. left join countstat on countstat.id = kr1.id
  223. join timeline on timeline.geo_tag_id = kr1.geo_tag_id
  224. where --statuses.name not in ('Проверено Фондом') and
  225. kr1.reporting_period_id=(select max(id) as "id" from reporting_periods where type = '15') --and id !=231
  226. order by statuses.name,geo_tags.name --,time desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!