Advertisement
aliGo

Untitled

Mar 27th, 2023
20
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 52.80 KB | None | 0 0
  1. -- anid value -> inValue
  2.  
  3. drop function if exists expert.build_acts_lists_grid_query(boolean, integer, expert.act_list_status[],
  4. character varying[], character varying[],
  5. character varying[], uuid, character varying[], bigint[],
  6. varchar, timestamp with time zone, timestamp with time zone,
  7. timestamp with time zone, timestamp with time zone, date,
  8. date, date, date, date, date, character varying[], uuid[],
  9. uuid[], character varying[], character varying[], text, text,
  10. text, date, date, expert.act_status[], integer, numeric,
  11. numeric, numeric, numeric, numeric, numeric, date, date,
  12. text, text, integer, integer);
  13.  
  14. drop function if exists expert.get_act_lists(integer, expert.act_list_status[], character varying[],
  15. character varying[], character varying[], uuid, character varying[],
  16. bigint[], varchar, timestamp with time zone, timestamp with time zone,
  17. timestamp with time zone, timestamp with time zone, date, date, date, date,
  18. date, date, character varying[], uuid[], text, text, integer, integer);
  19.  
  20.  
  21. create or replace function expert.get_act_lists(aregion_code integer, astatus expert.act_list_status[]
  22. , aold_id character varying[], amedical_organization_codes character varying[], alist_number character varying[]
  23. , anid uuid[], auser_id character varying[], aassigned_expert_id bigint[], alist_name character varying
  24. , create_date_begin timestamp with time zone, create_date_end timestamp with time zone, upload_date_begin timestamp with time zone
  25. , upload_date_end timestamp with time zone, sign_date_begin date, sign_date_end date, send_date_begin date, send_date_end date
  26. , print_date_begin date, print_date_end date, amed_work_type_name character varying[], azpmd_id uuid[]
  27. , order_column text, order_direction text, alimit integer, aoffset integer)
  28. returns TABLE(id uuid, list_number character varying, list_name character varying, date_create timestamp with time zone
  29. , region_code text, medical_organization_name text, medical_organization_code text, user_id character varying
  30. , date_upload timestamp with time zone, zpmd_id uuid, has_cover_letter_date boolean, date_print date
  31. , date_send date, date_sign date, old_id text, theme_category_id uuid, med_work_type_id bigint
  32. , med_work_type_name character varying, assigned_expert_id bigint, status expert.act_list_status
  33. , region expert.region)
  34. parallel safe
  35. language plpgsql
  36. as
  37. $$
  38. declare
  39. _order_by text = coalesce(case when regexp_split_to_array(order_column, ',') <@ array['region_code','medical_organization_code'
  40. ,'id','list_number','status', 'old_id','user_id','list_name','date_create','date_upload','date_sign','date_send','date_print', 'med_work_type_name']
  41. then ' order by ' || order_column || ' ' || order_direction || ' , id '|| format('limit %L offset %L', alimit, aoffset) end, '');
  42. _where text = concat_ws(' and '
  43. , case when cardinality(anid) > 0 then format('al.id = any(%L)', anid) end
  44. , case when cardinality(alist_number) > 0 then format('al.list_number = any(%L)', alist_number) end
  45. , case when cardinality(astatus) > 0 then format('al.status = any(%L)', astatus) end
  46. , case when cardinality(aold_id) > 0 then format('al.old_id = any(%L)', aold_id) end
  47. , case when cardinality(auser_id) > 0 then format('al.user_id = any(%L)', auser_id) end
  48. , case when cardinality(azpmd_id) > 0 then format('al.zpmd_id = any(%L)', azpmd_id) end
  49. , case when cardinality(aassigned_expert_id) > 0 then format('al.assigned_expert_id = any(%L)',aassigned_expert_id) end
  50. , case when alist_name is not null then format('al.list_name like ''%%%s%%''',alist_name) end
  51. , case when create_date_begin is not null then format('al.date_create >= %L',create_date_begin) end
  52. , case when create_date_end is not null then format('al.date_create <= %L', create_date_end) end
  53. , case when upload_date_begin is not null then format('al.date_upload >= %L', upload_date_begin) end
  54. , case when upload_date_end is not null then format('al.date_upload <= %L', upload_date_end) end
  55. , case when sign_date_begin is not null then format('al.date_sign >= %L', sign_date_begin) end
  56. , case when sign_date_end is not null then format('al.date_sign <= %L', sign_date_end) end
  57. , case when send_date_begin is not null then format('al.date_send >= %L', send_date_begin) end
  58. , case when send_date_end is not null then format('al.date_send <= %L', send_date_end) end
  59. , case when print_date_begin is not null then format('al.date_print >= %L', print_date_begin) end
  60. , case when print_date_end is not null then format('al.date_print <= %L', print_date_end) end
  61. , case when cardinality(amed_work_type_name) > 0 then format('v006d.name_type_usl = any(%L)', amed_work_type_name) end);
  62. _sql text = 'select al.id,
  63. al.list_number,
  64. al.list_name,
  65. al.date_create,
  66. case
  67. when al.region = ''TULA''::expert.region then mo_tula.short_name::text
  68. else f003d.nam_mok::text
  69. end
  70. as medical_organization_name,
  71. case
  72. when al.region = ''TULA''::expert.region then mo_tula.mo_code::text
  73. else f003d.mcode::text
  74. end as medical_organization_code,
  75. f003d.mcode::text as medical_organization_code,
  76. al.user_id,
  77. al.date_upload,
  78. al.zpmd_id,
  79. al.has_cover_letter_date,
  80. al.date_print,
  81. al.date_send,
  82. al.date_sign,
  83. al.old_id::text as old_id,
  84. al.theme_category_id,
  85. al.med_work_type_id,
  86. v006d.name_type_usl as med_work_type_name,
  87. al.assigned_expert_id,
  88. al.status,
  89. al.region
  90. from expert.acts_list al
  91. join directory.f003 f003d on f003d.mcode = al.medical_organization_code '|| concat_ws(' and '
  92. ,case when aregion_code is not null
  93. then format(' and f003d.region_code = %s', aregion_code) end
  94. , case when cardinality(amedical_organization_codes) > 0
  95. then format('f003d.mcode = any(''%s'')', amedical_organization_codes) end) ||
  96. 'join directory.v006 v006d on v006d.code_type_usl = al.med_work_type_id' ||
  97. 'left join directory.mo_tula mo_tula on mo_tula.mo_code = al.medical_organization_code '|| concat_ws(' and '
  98. ,case when aregion_code is not null
  99. then format(' and mo_tula.region_code = %s', aregion_code) end
  100. , case when cardinality(amedical_organization_codes) > 0
  101. then format('mo_tula.mo_code = any(''%s'')', amedical_organization_codes) end);
  102. begin
  103. return query execute _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by;
  104. --return _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by; -- debug mode
  105. end
  106. $$;
  107.  
  108. create or replace function expert.build_acts_lists_grid_query(is_count_query boolean, aregion_code integer
  109. , astatus expert.act_list_status[], aold_id character varying[], amedical_organization_codes character varying[]
  110. , alist_number character varying[], anid uuid[], auser_id character varying[], aassigned_expert_id bigint[]
  111. , alist_name character varying, create_date_begin timestamp with time zone, create_date_end timestamp with time zone
  112. , upload_date_begin timestamp with time zone, upload_date_end timestamp with time zone, sign_date_begin date
  113. , sign_date_end date, send_date_begin date, send_date_end date, print_date_begin date, print_date_end date
  114. , amed_work_type_name character varying[], azpmd_id uuid[], apodrid uuid[], athemename character varying[], acategoryname character varying[]
  115. , aexpertisepurpose text, aexpertisethemetype text, aexpertisetype text, acoverletterdatebegin date, acoverletterdateend date
  116. , aactstatus expert.act_status[], acountacts integer, aretentionsum numeric, aretentionsummax numeric, aretentionsummin numeric
  117. , atotalsumfine numeric, atotalsumfinemax numeric, atotalsumfinemin numeric, aexpertisedatebegin date, aexpertisedateend date
  118. , order_column text, order_direction text, alimit integer, aoffset integer) returns text
  119. parallel safe
  120. language plpgsql
  121. as
  122. $$
  123. declare
  124. _having_filters text = concat_ws(' and '
  125. , case when acountActs is not null then format('count(distinct act.id) = %L', acountActs) end
  126. , case when aretentionSum is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) = %L', aretentionSum) end
  127. , case when aretentionSumMax is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) < %L', aretentionSumMax) end
  128. , case when aretentionSumMin is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) > %L', aretentionSumMin) end
  129. , case when atotalSumFine is not null then format('sum(sanction.sum_fine) = %L', atotalSumFine) end
  130. , case when atotalSumFineMax is not null then format('sum(sanction.sum_fine) < %L', atotalSumFineMax) end
  131. , case when atotalSumFineMin is not null then format('sum(sanction.sum_fine) > %L', atotalSumFineMin) end
  132. , case when aexpertiseDateBegin is not null then format('max(re.expertise_date) >= %L', aexpertiseDateBegin) end
  133. , case when aexpertiseDateEnd is not null then format('max(re.expertise_date) <= %L', aexpertiseDateEnd) end);
  134.  
  135. _where_filters text = concat_ws(' and '
  136. , case when cardinality(athemeName) > 0 then format('theme.name = any(%L)', athemeName) end
  137. , case when cardinality(acategoryName) > 0 then format('category.name = any(%L)', acategoryName) end
  138. , case when aexpertisePurpose is not null then format('ecd.expertise_purpose = %L', aexpertisePurpose) end
  139. , case when aexpertiseThemeType is not null then format('ecd.expertise_theme_type = %L', aexpertiseThemeType) end
  140. , case when aexpertiseType is not null then format('ecd.expertise_type = %L', aexpertiseType) end
  141. , case when acoverLetterDateBegin is not null then format('cv.date >= %L', acoverLetterDateBegin) end
  142. , case when acoverLetterDateEnd is not null then format('cv.date <= %L', acoverLetterDateEnd) end
  143. , case when cardinality(aactStatus) > 0 then format('act.status = any(%L)', aactStatus) end
  144. , case when cardinality(apodrId) > 0 then format('zpmd.podr_id = any(%L)', apodrId) end);
  145.  
  146. has_filters bool = _where_filters <> '' or _having_filters <> '';
  147.  
  148. _select text = 'select al.id,
  149. al.list_number,
  150. al.list_name,
  151. al.medical_organization_name,
  152. al.med_work_type_name,
  153. ecd.expertise_purpose::text as expertise_purpose,
  154. ecd.expertise_type::text as expertise_type,
  155. ecd.expertise_theme_type::text as expertise_theme_type,
  156. al.status as status,
  157. count(distinct act.id) as count_acts,
  158. concat_ws('' '', users.last_name, users.first_name) as expert_last_name,
  159. al.date_create,
  160. al.region_code,
  161. al.user_id,
  162. max(theme.name::text) as theme_name,
  163. max(category.name::text) as category_name,
  164. al.date_upload,
  165. al.medical_organization_code,
  166. sum(coalesce(sanction.sum_rem, 0.0)) as retention_sum,
  167. al.zpmd_id,
  168. theme_category.id as list_theme_category_id,
  169. zpmd.theme_category_id as zpmd_theme_category_id,
  170. al.has_cover_letter_date,
  171. cv.number as cover_letter_number,
  172. cv.date as cover_letter_date,
  173. max(re.expertise_date) as expertise_date,
  174. al.date_print,
  175. al.date_send,
  176. al.date_sign,
  177. concat_ws('' ''::text, exp.family_name, exp.first_name, exp.patronymic) as assigned_expert,
  178. al.assigned_expert_id,
  179. sum(sanction.sum_fine) as total_sum_fine,
  180. zpmd.podr_id,
  181. max(coalesce(podr_omsk.nam_mop, podr_tula.name)::text) as podr_name,
  182. max(coalesce(podr_omsk.lpu_1::text, podr_tula.podr_id) ::text) as podr_code,
  183. al.old_id as old_id';
  184.  
  185. _inner_joins text = '
  186. join expert.act act on act.act_list_id = al.id
  187. join expert.theme_category theme_category on al.theme_category_id = theme_category.id
  188. join expert.category on category.id = theme_category.category_id
  189. join expert.expertise_class_decomposed ecd
  190. on ecd.expertise_class = theme_category.expertise_class
  191. join zpmd.zpmd on zpmd.id = al.zpmd_id';
  192.  
  193. _left_joins text = '
  194. left join expert.theme on theme_category.theme_id = theme.id
  195. left join expert.document cv on al.id = cv.act_list_id and cv.type = ''COVER_LETTER''::expert.document_type
  196. left join expert.expertise_result re
  197. on act.id = re.act_id and re.status <> ''DELETED''::expert.expertise_result_status
  198. left join expert.expertise_sanction sanction on re.id = sanction.expertise_result_id and
  199. (sanction.sum_fine is not null or sanction.sum_rem is not null)
  200. left join expert.users users on users.id = al.user_id
  201. left join directory.expert exp on exp.id = al.assigned_expert_id
  202. left join directory.podr_omsk podr_omsk on al.region = ''OMSK'' and podr_omsk.id = zpmd.podr_id
  203. left join directory.t_podr_tula podr_tula on al.region = ''TULA'' and podr_tula.id = zpmd.podr_id';
  204.  
  205. _from text = format('
  206. from expert.get_act_lists(%L, %L, %L, %L, %L, %L , %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L
  207. , %L, %L, %L, %L, %L, %L ) al '
  208. , aregion_code, astatus, aold_id, amedical_organization_codes, alist_number, anid , auser_id
  209. , aassigned_expert_id, alist_name, create_date_begin, create_date_end, upload_date_begin, upload_date_end
  210. , sign_date_begin, sign_date_end, send_date_begin, send_date_end, print_date_begin, print_date_end
  211. , amed_work_type_name, azpmd_id, order_column, order_direction
  212. , case when not is_count_query and not has_filters then alimit end
  213. , case when not is_count_query and not has_filters then aoffset end) ||
  214. _inner_joins ||
  215. case when is_count_query and not has_filters then '' else _left_joins end;
  216.  
  217. _where text = case when _where_filters <> '' then '
  218. where '|| _where_filters || ' ' else '' end;
  219.  
  220. _group_by text = '
  221. group by al.id,
  222. al.list_number,
  223. al.list_name,
  224. al.date_create,
  225. al.region_code,
  226. al.medical_organization_name,
  227. al.medical_organization_code,
  228. al.user_id,
  229. al.date_upload,
  230. al.zpmd_id,
  231. al.has_cover_letter_date,
  232. al.date_print,
  233. al.date_send,
  234. al.date_sign,
  235. al.old_id,
  236. al.theme_category_id,
  237. al.med_work_type_name,
  238. al.assigned_expert_id,
  239. al.status,
  240. al.region,
  241. (ecd.expertise_purpose::text),(ecd.expertise_type::text), (ecd.expertise_theme_type::text),
  242. concat_ws('' '', users.last_name, users.first_name), zpmd.theme_category_id,
  243. theme_category.id, cv.number, cv.date, exp.id, zpmd.podr_id';
  244.  
  245. _order_by text = ' order by ' || order_column || ' ' || order_direction || ' , id';
  246.  
  247. _limit text = case when not is_count_query and has_filters then format(' limit %L offset %L', alimit, aoffset) else '' end;
  248.  
  249. _list_query text = _select ||
  250. _from ||
  251. _where ||
  252. _group_by ||
  253. (case when _having_filters <> '' then ' having ' || _having_filters else '' end) ||
  254. _order_by || _limit;
  255.  
  256. _count_query text = 'select count(distinct al.id) ' ||
  257. case when _having_filters = '' then '' else '
  258. from ( select al.id ' end||
  259. _from ||
  260. _where ||
  261. case when _having_filters = '' then '' else _group_by || ' having '|| _having_filters || ') as al' end;
  262. begin
  263. return
  264. case when is_count_query
  265. then _count_query
  266. else _list_query
  267. end;
  268. end
  269. $$;-- anid value -> inValue
  270.  
  271. drop function if exists expert.build_acts_lists_grid_query(boolean, integer, expert.act_list_status[],
  272. character varying[], character varying[],
  273. character varying[], uuid, character varying[], bigint[],
  274. varchar, timestamp with time zone, timestamp with time zone,
  275. timestamp with time zone, timestamp with time zone, date,
  276. date, date, date, date, date, character varying[], uuid[],
  277. uuid[], character varying[], character varying[], text, text,
  278. text, date, date, expert.act_status[], integer, numeric,
  279. numeric, numeric, numeric, numeric, numeric, date, date,
  280. text, text, integer, integer);
  281.  
  282. drop function if exists expert.get_act_lists(integer, expert.act_list_status[], character varying[],
  283. character varying[], character varying[], uuid, character varying[],
  284. bigint[], varchar, timestamp with time zone, timestamp with time zone,
  285. timestamp with time zone, timestamp with time zone, date, date, date, date,
  286. date, date, character varying[], uuid[], text, text, integer, integer);
  287.  
  288.  
  289. create or replace function expert.get_act_lists(aregion_code integer, astatus expert.act_list_status[]
  290. , aold_id character varying[], amedical_organization_codes character varying[], alist_number character varying[]
  291. , anid uuid[], auser_id character varying[], aassigned_expert_id bigint[], alist_name character varying
  292. , create_date_begin timestamp with time zone, create_date_end timestamp with time zone, upload_date_begin timestamp with time zone
  293. , upload_date_end timestamp with time zone, sign_date_begin date, sign_date_end date, send_date_begin date, send_date_end date
  294. , print_date_begin date, print_date_end date, amed_work_type_name character varying[], azpmd_id uuid[]
  295. , order_column text, order_direction text, alimit integer, aoffset integer)
  296. returns TABLE(id uuid, list_number character varying, list_name character varying, date_create timestamp with time zone
  297. , region_code text, medical_organization_name text, medical_organization_code text, user_id character varying
  298. , date_upload timestamp with time zone, zpmd_id uuid, has_cover_letter_date boolean, date_print date
  299. , date_send date, date_sign date, old_id text, theme_category_id uuid, med_work_type_id bigint
  300. , med_work_type_name character varying, assigned_expert_id bigint, status expert.act_list_status
  301. , region expert.region)
  302. parallel safe
  303. language plpgsql
  304. as
  305. $$
  306. declare
  307. _order_by text = coalesce(case when regexp_split_to_array(order_column, ',') <@ array['region_code','medical_organization_code'
  308. ,'id','list_number','status', 'old_id','user_id','list_name','date_create','date_upload','date_sign','date_send','date_print', 'med_work_type_name']
  309. then ' order by ' || order_column || ' ' || order_direction || ' , id '|| format('limit %L offset %L', alimit, aoffset) end, '');
  310. _where text = concat_ws(' and '
  311. , case when cardinality(anid) > 0 then format('al.id = any(%L)', anid) end
  312. , case when cardinality(alist_number) > 0 then format('al.list_number = any(%L)', alist_number) end
  313. , case when cardinality(astatus) > 0 then format('al.status = any(%L)', astatus) end
  314. , case when cardinality(aold_id) > 0 then format('al.old_id = any(%L)', aold_id) end
  315. , case when cardinality(auser_id) > 0 then format('al.user_id = any(%L)', auser_id) end
  316. , case when cardinality(azpmd_id) > 0 then format('al.zpmd_id = any(%L)', azpmd_id) end
  317. , case when cardinality(aassigned_expert_id) > 0 then format('al.assigned_expert_id = any(%L)',aassigned_expert_id) end
  318. , case when alist_name is not null then format('al.list_name like ''%%%s%%''',alist_name) end
  319. , case when create_date_begin is not null then format('al.date_create >= %L',create_date_begin) end
  320. , case when create_date_end is not null then format('al.date_create <= %L', create_date_end) end
  321. , case when upload_date_begin is not null then format('al.date_upload >= %L', upload_date_begin) end
  322. , case when upload_date_end is not null then format('al.date_upload <= %L', upload_date_end) end
  323. , case when sign_date_begin is not null then format('al.date_sign >= %L', sign_date_begin) end
  324. , case when sign_date_end is not null then format('al.date_sign <= %L', sign_date_end) end
  325. , case when send_date_begin is not null then format('al.date_send >= %L', send_date_begin) end
  326. , case when send_date_end is not null then format('al.date_send <= %L', send_date_end) end
  327. , case when print_date_begin is not null then format('al.date_print >= %L', print_date_begin) end
  328. , case when print_date_end is not null then format('al.date_print <= %L', print_date_end) end
  329. , case when cardinality(amed_work_type_name) > 0 then format('v006d.name_type_usl = any(%L)', amed_work_type_name) end);
  330. _sql text = 'select al.id,
  331. al.list_number,
  332. al.list_name,
  333. al.date_create,
  334. f003d.region_code::text as region_code,
  335. f003d.nam_mok::text as medical_organization_name,
  336. f003d.mcode::text as medical_organization_code,
  337. al.user_id,
  338. al.date_upload,
  339. al.zpmd_id,
  340. al.has_cover_letter_date,
  341. al.date_print,
  342. al.date_send,
  343. al.date_sign,
  344. al.old_id::text as old_id,
  345. al.theme_category_id,
  346. al.med_work_type_id,
  347. v006d.name_type_usl as med_work_type_name,
  348. al.assigned_expert_id,
  349. al.status,
  350. al.region
  351. from expert.acts_list al
  352. join directory.f003 f003d on f003d.mcode = al.medical_organization_code '|| concat_ws(' and '
  353. ,case when aregion_code is not null
  354. then format(' and f003d.region_code = %s', aregion_code) end
  355. , case when cardinality(amedical_organization_codes) > 0
  356. then format('f003d.mcode = any(''%s'')', amedical_organization_codes) end) || '
  357. join directory.v006 v006d on v006d.code_type_usl = al.med_work_type_id';
  358. begin
  359. return query execute _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by;
  360. --return _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by; -- debug mode
  361. end
  362. $$;
  363.  
  364. create or replace function expert.build_acts_lists_grid_query(is_count_query boolean, aregion_code integer
  365. , astatus expert.act_list_status[], aold_id character varying[], amedical_organization_codes character varying[]
  366. , alist_number character varying[], anid uuid[], auser_id character varying[], aassigned_expert_id bigint[]
  367. , alist_name character varying, create_date_begin timestamp with time zone, create_date_end timestamp with time zone
  368. , upload_date_begin timestamp with time zone, upload_date_end timestamp with time zone, sign_date_begin date
  369. , sign_date_end date, send_date_begin date, send_date_end date, print_date_begin date, print_date_end date
  370. , amed_work_type_name character varying[], azpmd_id uuid[], apodrid uuid[], athemename character varying[], acategoryname character varying[]
  371. , aexpertisepurpose text, aexpertisethemetype text, aexpertisetype text, acoverletterdatebegin date, acoverletterdateend date
  372. , aactstatus expert.act_status[], acountacts integer, aretentionsum numeric, aretentionsummax numeric, aretentionsummin numeric
  373. , atotalsumfine numeric, atotalsumfinemax numeric, atotalsumfinemin numeric, aexpertisedatebegin date, aexpertisedateend date
  374. , order_column text, order_direction text, alimit integer, aoffset integer) returns text
  375. parallel safe
  376. language plpgsql
  377. as
  378. $$
  379. declare
  380. _having_filters text = concat_ws(' and '
  381. , case when acountActs is not null then format('count(distinct act.id) = %L', acountActs) end
  382. , case when aretentionSum is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) = %L', aretentionSum) end
  383. , case when aretentionSumMax is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) < %L', aretentionSumMax) end
  384. , case when aretentionSumMin is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) > %L', aretentionSumMin) end
  385. , case when atotalSumFine is not null then format('sum(sanction.sum_fine) = %L', atotalSumFine) end
  386. , case when atotalSumFineMax is not null then format('sum(sanction.sum_fine) < %L', atotalSumFineMax) end
  387. , case when atotalSumFineMin is not null then format('sum(sanction.sum_fine) > %L', atotalSumFineMin) end
  388. , case when aexpertiseDateBegin is not null then format('max(re.expertise_date) >= %L', aexpertiseDateBegin) end
  389. , case when aexpertiseDateEnd is not null then format('max(re.expertise_date) <= %L', aexpertiseDateEnd) end);
  390.  
  391. _where_filters text = concat_ws(' and '
  392. , case when cardinality(athemeName) > 0 then format('theme.name = any(%L)', athemeName) end
  393. , case when cardinality(acategoryName) > 0 then format('category.name = any(%L)', acategoryName) end
  394. , case when aexpertisePurpose is not null then format('ecd.expertise_purpose = %L', aexpertisePurpose) end
  395. , case when aexpertiseThemeType is not null then format('ecd.expertise_theme_type = %L', aexpertiseThemeType) end
  396. , case when aexpertiseType is not null then format('ecd.expertise_type = %L', aexpertiseType) end
  397. , case when acoverLetterDateBegin is not null then format('cv.date >= %L', acoverLetterDateBegin) end
  398. , case when acoverLetterDateEnd is not null then format('cv.date <= %L', acoverLetterDateEnd) end
  399. , case when cardinality(aactStatus) > 0 then format('act.status = any(%L)', aactStatus) end
  400. , case when cardinality(apodrId) > 0 then format('zpmd.podr_id = any(%L)', apodrId) end);
  401.  
  402. has_filters bool = _where_filters <> '' or _having_filters <> '';
  403.  
  404. _select text = 'select al.id,
  405. al.list_number,
  406. al.list_name,
  407. al.medical_organization_name,
  408. al.med_work_type_name,
  409. ecd.expertise_purpose::text as expertise_purpose,
  410. ecd.expertise_type::text as expertise_type,
  411. ecd.expertise_theme_type::text as expertise_theme_type,
  412. al.status as status,
  413. count(distinct act.id) as count_acts,
  414. concat_ws('' '', users.last_name, users.first_name) as expert_last_name,
  415. al.date_create,
  416. al.region_code,
  417. al.user_id,
  418. max(theme.name::text) as theme_name,
  419. max(category.name::text) as category_name,
  420. al.date_upload,
  421. al.medical_organization_code,
  422. sum(coalesce(sanction.sum_rem, 0.0)) as retention_sum,
  423. al.zpmd_id,
  424. theme_category.id as list_theme_category_id,
  425. zpmd.theme_category_id as zpmd_theme_category_id,
  426. al.has_cover_letter_date,
  427. cv.number as cover_letter_number,
  428. cv.date as cover_letter_date,
  429. max(re.expertise_date) as expertise_date,
  430. al.date_print,
  431. al.date_send,
  432. al.date_sign,
  433. concat_ws('' ''::text, exp.family_name, exp.first_name, exp.patronymic) as assigned_expert,
  434. al.assigned_expert_id,
  435. sum(sanction.sum_fine) as total_sum_fine,
  436. zpmd.podr_id,
  437. max(coalesce(podr_omsk.nam_mop, podr_tula.name)::text) as podr_name,
  438. max(coalesce(podr_omsk.lpu_1::text, podr_tula.podr_id) ::text) as podr_code,
  439. al.old_id as old_id';
  440.  
  441. _inner_joins text = '
  442. join expert.act act on act.act_list_id = al.id
  443. join expert.theme_category theme_category on al.theme_category_id = theme_category.id
  444. join expert.category on category.id = theme_category.category_id
  445. join expert.expertise_class_decomposed ecd
  446. on ecd.expertise_class = theme_category.expertise_class
  447. join zpmd.zpmd on zpmd.id = al.zpmd_id';
  448.  
  449. _left_joins text = '
  450. left join expert.theme on theme_category.theme_id = theme.id
  451. left join expert.document cv on al.id = cv.act_list_id and cv.type = ''COVER_LETTER''::expert.document_type
  452. left join expert.expertise_result re
  453. on act.id = re.act_id and re.status <> ''DELETED''::expert.expertise_result_status
  454. left join expert.expertise_sanction sanction on re.id = sanction.expertise_result_id and
  455. (sanction.sum_fine is not null or sanction.sum_rem is not null)
  456. left join expert.users users on users.id = al.user_id
  457. left join directory.expert exp on exp.id = al.assigned_expert_id
  458. left join directory.podr_omsk podr_omsk on al.region = ''OMSK'' and podr_omsk.id = zpmd.podr_id
  459. left join directory.t_podr_tula podr_tula on al.region = ''TULA'' and podr_tula.id = zpmd.podr_id';
  460.  
  461. _from text = format('
  462. from expert.get_act_lists(%L, %L, %L, %L, %L, %L , %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L
  463. , %L, %L, %L, %L, %L, %L ) al '
  464. , aregion_code, astatus, aold_id, amedical_organization_codes, alist_number, anid , auser_id
  465. , aassigned_expert_id, alist_name, create_date_begin, create_date_end, upload_date_begin, upload_date_end
  466. , sign_date_begin, sign_date_end, send_date_begin, send_date_end, print_date_begin, print_date_end
  467. , amed_work_type_name, azpmd_id, order_column, order_direction
  468. , case when not is_count_query and not has_filters then alimit end
  469. , case when not is_count_query and not has_filters then aoffset end) ||
  470. _inner_joins ||
  471. case when is_count_query and not has_filters then '' else _left_joins end;
  472.  
  473. _where text = case when _where_filters <> '' then '
  474. where '|| _where_filters || ' ' else '' end;
  475.  
  476. _group_by text = '
  477. group by al.id,
  478. al.list_number,
  479. al.list_name,
  480. al.date_create,
  481. al.region_code,
  482. al.medical_organization_name,
  483. al.medical_organization_code,
  484. al.user_id,
  485. al.date_upload,
  486. al.zpmd_id,
  487. al.has_cover_letter_date,
  488. al.date_print,
  489. al.date_send,
  490. al.date_sign,
  491. al.old_id,
  492. al.theme_category_id,
  493. al.med_work_type_name,
  494. al.assigned_expert_id,
  495. al.status,
  496. al.region,
  497. (ecd.expertise_purpose::text),(ecd.expertise_type::text), (ecd.expertise_theme_type::text),
  498. concat_ws('' '', users.last_name, users.first_name), zpmd.theme_category_id,
  499. theme_category.id, cv.number, cv.date, exp.id, zpmd.podr_id';
  500.  
  501. _order_by text = ' order by ' || order_column || ' ' || order_direction || ' , id';
  502.  
  503. _limit text = case when not is_count_query and has_filters then format(' limit %L offset %L', alimit, aoffset) else '' end;
  504.  
  505. _list_query text = _select ||
  506. _from ||
  507. _where ||
  508. _group_by ||
  509. (case when _having_filters <> '' then ' having ' || _having_filters else '' end) ||
  510. _order_by || _limit;
  511.  
  512. _count_query text = 'select count(distinct al.id) ' ||
  513. case when _having_filters = '' then '' else '
  514. from ( select al.id ' end||
  515. _from ||
  516. _where ||
  517. case when _having_filters = '' then '' else _group_by || ' having '|| _having_filters || ') as al' end;
  518. begin
  519. return
  520. case when is_count_query
  521. then _count_query
  522. else _list_query
  523. end;
  524. end
  525. $$;-- anid value -> inValue
  526.  
  527. drop function if exists expert.build_acts_lists_grid_query(boolean, integer, expert.act_list_status[],
  528. character varying[], character varying[],
  529. character varying[], uuid, character varying[], bigint[],
  530. varchar, timestamp with time zone, timestamp with time zone,
  531. timestamp with time zone, timestamp with time zone, date,
  532. date, date, date, date, date, character varying[], uuid[],
  533. uuid[], character varying[], character varying[], text, text,
  534. text, date, date, expert.act_status[], integer, numeric,
  535. numeric, numeric, numeric, numeric, numeric, date, date,
  536. text, text, integer, integer);
  537.  
  538. drop function if exists expert.get_act_lists(integer, expert.act_list_status[], character varying[],
  539. character varying[], character varying[], uuid, character varying[],
  540. bigint[], varchar, timestamp with time zone, timestamp with time zone,
  541. timestamp with time zone, timestamp with time zone, date, date, date, date,
  542. date, date, character varying[], uuid[], text, text, integer, integer);
  543.  
  544.  
  545. create or replace function expert.get_act_lists(aregion_code integer, astatus expert.act_list_status[]
  546. , aold_id character varying[], amedical_organization_codes character varying[], alist_number character varying[]
  547. , anid uuid[], auser_id character varying[], aassigned_expert_id bigint[], alist_name character varying
  548. , create_date_begin timestamp with time zone, create_date_end timestamp with time zone, upload_date_begin timestamp with time zone
  549. , upload_date_end timestamp with time zone, sign_date_begin date, sign_date_end date, send_date_begin date, send_date_end date
  550. , print_date_begin date, print_date_end date, amed_work_type_name character varying[], azpmd_id uuid[]
  551. , order_column text, order_direction text, alimit integer, aoffset integer)
  552. returns TABLE(id uuid, list_number character varying, list_name character varying, date_create timestamp with time zone
  553. , region_code text, medical_organization_name text, medical_organization_code text, user_id character varying
  554. , date_upload timestamp with time zone, zpmd_id uuid, has_cover_letter_date boolean, date_print date
  555. , date_send date, date_sign date, old_id text, theme_category_id uuid, med_work_type_id bigint
  556. , med_work_type_name character varying, assigned_expert_id bigint, status expert.act_list_status
  557. , region expert.region)
  558. parallel safe
  559. language plpgsql
  560. as
  561. $$
  562. declare
  563. _order_by text = coalesce(case when regexp_split_to_array(order_column, ',') <@ array['region_code','medical_organization_code'
  564. ,'id','list_number','status', 'old_id','user_id','list_name','date_create','date_upload','date_sign','date_send','date_print', 'med_work_type_name']
  565. then ' order by ' || order_column || ' ' || order_direction || ' , id '|| format('limit %L offset %L', alimit, aoffset) end, '');
  566. _where text = concat_ws(' and '
  567. , case when cardinality(anid) > 0 then format('al.id = any(%L)', anid) end
  568. , case when cardinality(alist_number) > 0 then format('al.list_number = any(%L)', alist_number) end
  569. , case when cardinality(astatus) > 0 then format('al.status = any(%L)', astatus) end
  570. , case when cardinality(aold_id) > 0 then format('al.old_id = any(%L)', aold_id) end
  571. , case when cardinality(auser_id) > 0 then format('al.user_id = any(%L)', auser_id) end
  572. , case when cardinality(azpmd_id) > 0 then format('al.zpmd_id = any(%L)', azpmd_id) end
  573. , case when cardinality(aassigned_expert_id) > 0 then format('al.assigned_expert_id = any(%L)',aassigned_expert_id) end
  574. , case when alist_name is not null then format('al.list_name like ''%%%s%%''',alist_name) end
  575. , case when create_date_begin is not null then format('al.date_create >= %L',create_date_begin) end
  576. , case when create_date_end is not null then format('al.date_create <= %L', create_date_end) end
  577. , case when upload_date_begin is not null then format('al.date_upload >= %L', upload_date_begin) end
  578. , case when upload_date_end is not null then format('al.date_upload <= %L', upload_date_end) end
  579. , case when sign_date_begin is not null then format('al.date_sign >= %L', sign_date_begin) end
  580. , case when sign_date_end is not null then format('al.date_sign <= %L', sign_date_end) end
  581. , case when send_date_begin is not null then format('al.date_send >= %L', send_date_begin) end
  582. , case when send_date_end is not null then format('al.date_send <= %L', send_date_end) end
  583. , case when print_date_begin is not null then format('al.date_print >= %L', print_date_begin) end
  584. , case when print_date_end is not null then format('al.date_print <= %L', print_date_end) end
  585. , case when cardinality(amed_work_type_name) > 0 then format('v006d.name_type_usl = any(%L)', amed_work_type_name) end);
  586. _sql text = 'select al.id,
  587. al.list_number,
  588. al.list_name,
  589. al.date_create,
  590. f003d.region_code::text as region_code,
  591. case
  592. when al.region = ''TULA''::expert.region then mo_tula.short_name::text
  593. else f003d.nam_mok::text
  594. end as medical_organization_name,
  595. case
  596. when al.region = ''TULA''::expert.region then mo_tula.mo_code::text
  597. else f003d.mcode::text
  598. end as medical_organization_code,
  599. al.user_id,
  600. al.date_upload,
  601. al.zpmd_id,
  602. al.has_cover_letter_date,
  603. al.date_print,
  604. al.date_send,
  605. al.date_sign,
  606. al.old_id::text as old_id,
  607. al.theme_category_id,
  608. al.med_work_type_id,
  609. v006d.name_type_usl as med_work_type_name,
  610. al.assigned_expert_id,
  611. al.status,
  612. al.region
  613. from expert.acts_list al
  614. join directory.f003 f003d on f003d.mcode = al.medical_organization_code '|| concat_ws(' and '
  615. ,case when aregion_code is not null
  616. then format(' and f003d.region_code = %s', aregion_code) end
  617. , case when cardinality(amedical_organization_codes) > 0
  618. then format('f003d.mcode = any(''%s'')', amedical_organization_codes) end) || '
  619. join directory.v006 v006d on v006d.code_type_usl = al.med_work_type_id ' || '
  620. left join directory.mo_tula mo_tula on mo_tula.mo_code = al.medical_organization_code '|| concat_ws(' and '
  621. ,case when aregion_code is not null
  622. then format(' and %s = 71 ', aregion_code) end
  623. , case when cardinality(amedical_organization_codes) > 0
  624. then format('mo_tula.mo_code = any(''%s'')', amedical_organization_codes) end);
  625. begin
  626. return query execute _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by;
  627. --return _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by; -- debug mode
  628. end
  629. $$;
  630.  
  631. create or replace function expert.build_acts_lists_grid_query(is_count_query boolean, aregion_code integer
  632. , astatus expert.act_list_status[], aold_id character varying[], amedical_organization_codes character varying[]
  633. , alist_number character varying[], anid uuid[], auser_id character varying[], aassigned_expert_id bigint[]
  634. , alist_name character varying, create_date_begin timestamp with time zone, create_date_end timestamp with time zone
  635. , upload_date_begin timestamp with time zone, upload_date_end timestamp with time zone, sign_date_begin date
  636. , sign_date_end date, send_date_begin date, send_date_end date, print_date_begin date, print_date_end date
  637. , amed_work_type_name character varying[], azpmd_id uuid[], apodrid uuid[], athemename character varying[], acategoryname character varying[]
  638. , aexpertisepurpose text, aexpertisethemetype text, aexpertisetype text, acoverletterdatebegin date, acoverletterdateend date
  639. , aactstatus expert.act_status[], acountacts integer, aretentionsum numeric, aretentionsummax numeric, aretentionsummin numeric
  640. , atotalsumfine numeric, atotalsumfinemax numeric, atotalsumfinemin numeric, aexpertisedatebegin date, aexpertisedateend date
  641. , order_column text, order_direction text, alimit integer, aoffset integer) returns text
  642. parallel safe
  643. language plpgsql
  644. as
  645. $$
  646. declare
  647. _having_filters text = concat_ws(' and '
  648. , case when acountActs is not null then format('count(distinct act.id) = %L', acountActs) end
  649. , case when aretentionSum is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) = %L', aretentionSum) end
  650. , case when aretentionSumMax is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) < %L', aretentionSumMax) end
  651. , case when aretentionSumMin is not null then format('sum(coalesce(sanction.sum_rem, 0.0)) > %L', aretentionSumMin) end
  652. , case when atotalSumFine is not null then format('sum(sanction.sum_fine) = %L', atotalSumFine) end
  653. , case when atotalSumFineMax is not null then format('sum(sanction.sum_fine) < %L', atotalSumFineMax) end
  654. , case when atotalSumFineMin is not null then format('sum(sanction.sum_fine) > %L', atotalSumFineMin) end
  655. , case when aexpertiseDateBegin is not null then format('max(re.expertise_date) >= %L', aexpertiseDateBegin) end
  656. , case when aexpertiseDateEnd is not null then format('max(re.expertise_date) <= %L', aexpertiseDateEnd) end);
  657.  
  658. _where_filters text = concat_ws(' and '
  659. , case when cardinality(athemeName) > 0 then format('theme.name = any(%L)', athemeName) end
  660. , case when cardinality(acategoryName) > 0 then format('category.name = any(%L)', acategoryName) end
  661. , case when aexpertisePurpose is not null then format('ecd.expertise_purpose = %L', aexpertisePurpose) end
  662. , case when aexpertiseThemeType is not null then format('ecd.expertise_theme_type = %L', aexpertiseThemeType) end
  663. , case when aexpertiseType is not null then format('ecd.expertise_type = %L', aexpertiseType) end
  664. , case when acoverLetterDateBegin is not null then format('cv.date >= %L', acoverLetterDateBegin) end
  665. , case when acoverLetterDateEnd is not null then format('cv.date <= %L', acoverLetterDateEnd) end
  666. , case when cardinality(aactStatus) > 0 then format('act.status = any(%L)', aactStatus) end
  667. , case when cardinality(apodrId) > 0 then format('zpmd.podr_id = any(%L)', apodrId) end);
  668.  
  669. has_filters bool = _where_filters <> '' or _having_filters <> '';
  670.  
  671. _select text = 'select al.id,
  672. al.list_number,
  673. al.list_name,
  674. al.medical_organization_name,
  675. al.med_work_type_name,
  676. ecd.expertise_purpose::text as expertise_purpose,
  677. ecd.expertise_type::text as expertise_type,
  678. ecd.expertise_theme_type::text as expertise_theme_type,
  679. al.status as status,
  680. count(distinct act.id) as count_acts,
  681. concat_ws('' '', users.last_name, users.first_name) as expert_last_name,
  682. al.date_create,
  683. al.region_code,
  684. al.user_id,
  685. max(theme.name::text) as theme_name,
  686. max(category.name::text) as category_name,
  687. al.date_upload,
  688. al.medical_organization_code,
  689. sum(coalesce(sanction.sum_rem, 0.0)) as retention_sum,
  690. al.zpmd_id,
  691. theme_category.id as list_theme_category_id,
  692. zpmd.theme_category_id as zpmd_theme_category_id,
  693. al.has_cover_letter_date,
  694. cv.number as cover_letter_number,
  695. cv.date as cover_letter_date,
  696. max(re.expertise_date) as expertise_date,
  697. al.date_print,
  698. al.date_send,
  699. al.date_sign,
  700. concat_ws('' ''::text, exp.family_name, exp.first_name, exp.patronymic) as assigned_expert,
  701. al.assigned_expert_id,
  702. sum(sanction.sum_fine) as total_sum_fine,
  703. zpmd.podr_id,
  704. max(coalesce(podr_omsk.nam_mop, podr_tula.name)::text) as podr_name,
  705. max(coalesce(podr_omsk.lpu_1::text, podr_tula.podr_id) ::text) as podr_code,
  706. al.old_id as old_id';
  707.  
  708. _inner_joins text = '
  709. join expert.act act on act.act_list_id = al.id
  710. join expert.theme_category theme_category on al.theme_category_id = theme_category.id
  711. join expert.category on category.id = theme_category.category_id
  712. join expert.expertise_class_decomposed ecd
  713. on ecd.expertise_class = theme_category.expertise_class
  714. join zpmd.zpmd on zpmd.id = al.zpmd_id';
  715.  
  716. _left_joins text = '
  717. left join expert.theme on theme_category.theme_id = theme.id
  718. left join expert.document cv on al.id = cv.act_list_id and cv.type = ''COVER_LETTER''::expert.document_type
  719. left join expert.expertise_result re
  720. on act.id = re.act_id and re.status <> ''DELETED''::expert.expertise_result_status
  721. left join expert.expertise_sanction sanction on re.id = sanction.expertise_result_id and
  722. (sanction.sum_fine is not null or sanction.sum_rem is not null)
  723. left join expert.users users on users.id = al.user_id
  724. left join directory.expert exp on exp.id = al.assigned_expert_id
  725. left join directory.podr_omsk podr_omsk on al.region = ''OMSK'' and podr_omsk.id = zpmd.podr_id
  726. left join directory.t_podr_tula podr_tula on al.region = ''TULA'' and podr_tula.id = zpmd.podr_id';
  727.  
  728. _from text = format('
  729. from expert.get_act_lists(%L, %L, %L, %L, %L, %L , %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L, %L
  730. , %L, %L, %L, %L, %L, %L ) al '
  731. , aregion_code, astatus, aold_id, amedical_organization_codes, alist_number, anid , auser_id
  732. , aassigned_expert_id, alist_name, create_date_begin, create_date_end, upload_date_begin, upload_date_end
  733. , sign_date_begin, sign_date_end, send_date_begin, send_date_end, print_date_begin, print_date_end
  734. , amed_work_type_name, azpmd_id, order_column, order_direction
  735. , case when not is_count_query and not has_filters then alimit end
  736. , case when not is_count_query and not has_filters then aoffset end) ||
  737. _inner_joins ||
  738. case when is_count_query and not has_filters then '' else _left_joins end;
  739.  
  740. _where text = case when _where_filters <> '' then '
  741. where '|| _where_filters || ' ' else '' end;
  742.  
  743. _group_by text = '
  744. group by al.id,
  745. al.list_number,
  746. al.list_name,
  747. al.date_create,
  748. al.region_code,
  749. al.medical_organization_name,
  750. al.medical_organization_code,
  751. al.user_id,
  752. al.date_upload,
  753. al.zpmd_id,
  754. al.has_cover_letter_date,
  755. al.date_print,
  756. al.date_send,
  757. al.date_sign,
  758. al.old_id,
  759. al.theme_category_id,
  760. al.med_work_type_name,
  761. al.assigned_expert_id,
  762. al.status,
  763. al.region,
  764. (ecd.expertise_purpose::text),(ecd.expertise_type::text), (ecd.expertise_theme_type::text),
  765. concat_ws('' '', users.last_name, users.first_name), zpmd.theme_category_id,
  766. theme_category.id, cv.number, cv.date, exp.id, zpmd.podr_id';
  767.  
  768. _order_by text = ' order by ' || order_column || ' ' || order_direction || ' , id';
  769.  
  770. _limit text = case when not is_count_query and has_filters then format(' limit %L offset %L', alimit, aoffset) else '' end;
  771.  
  772. _list_query text = _select ||
  773. _from ||
  774. _where ||
  775. _group_by ||
  776. (case when _having_filters <> '' then ' having ' || _having_filters else '' end) ||
  777. _order_by || _limit;
  778.  
  779. _count_query text = 'select count(distinct al.id) ' ||
  780. case when _having_filters = '' then '' else '
  781. from ( select al.id ' end||
  782. _from ||
  783. _where ||
  784. case when _having_filters = '' then '' else _group_by || ' having '|| _having_filters || ') as al' end;
  785. begin
  786. return
  787. case when is_count_query
  788. then _count_query
  789. else _list_query
  790. end;
  791. end
  792. $$;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement