API tools faq
paste
Advertisement
aliGo

Untitled

aliGo
Mar 27th, 2023
21
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 52.80 KB | None | 0 0
raw download clone embed print report
  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
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!