· 5 years ago · Mar 03, 2020, 08:50 AM
1-- FUNCTION: platform.find_seed_build_query(json)
2
3-- DROP FUNCTION platform.find_seed_build_query(json);
4
5CREATE OR REPLACE FUNCTION platform.find_seed_build_query(
6 filters json)
7 RETURNS TABLE(main_sql text, count_sql text, working_list_sql text)
8 LANGUAGE 'plpgsql'
9
10 COST 100
11 VOLATILE
12 ROWS 1000
13AS $BODY$
14
15DECLARE
16 abbrev_arr text[];
17 table_arr text[];
18 config json;
19 column_config json;
20 r_rec RECORD;
21 config_abbrev_rec RECORD;
22 cond_values text;
23 column_config_rec RECORD;
24 main_table text;
25 main_table_alias text;
26 main_table_primary_key text;
27 metadata_column text;
28 metadata_condition text;
29 crosstab_column text;
30 metadata_column_arr integer[];
31 from_joined_table text;
32 select_column text;
33 from_table text;
34 condition_text text;
35 operator text;
36 order_by text;
37 order_by_values text;
38 final_order_by text;
39 main_order_by text;
40 group_by text;
41 userId int;
42 seedListTable text;
43 seedListTableExist boolean;
44 includeSeedlistOption boolean;
45 working_list_condition text;
46
47BEGIN
48 -- get config for search parameters
49 select (config_value->>'values')::json into config from platform.config where
50 config.abbrev='FIND_SEED_FILTERS';
51
52 -- get config for data browser columns
53 select (config_value->>'values')::json into column_config from platform.config where
54 config.abbrev='FIND_SEED_DATA_BROWSER_COLUMNS';
55
56 -- get main table
57 select (config_value->>'main_table')::text into main_table from platform.config where
58 config.abbrev='FIND_SEED_DATA_BROWSER_COLUMNS';
59
60 -- get main table alias
61 select (config_value->>'main_table_alias')::text into main_table_alias from platform.config where
62 config.abbrev='FIND_SEED_DATA_BROWSER_COLUMNS';
63
64 -- get main table primary key
65 select (config_value->>'main_table_primary_key')::text into main_table_primary_key from platform.config where
66 config.abbrev='FIND_SEED_DATA_BROWSER_COLUMNS';
67
68 -- get the user ID
69 select values into userId from json_to_recordset(filters) as x(abbrev text, values text, operator text, filter_type text)
70 where abbrev='userId';
71
72 -- get the value if to include records that already in the working list
73 select values into includeSeedlistOption from json_to_recordset(filters) as x(abbrev text, values text, operator text, filter_type text)
74 where abbrev='includeSeedlist';
75
76 seedListTable:='';
77 from_joined_table:='';
78 main_order_by:='';
79
80 order_by:='ss.product_id';
81 final_order_by:=' ORDER BY '||order_by;
82
83 select_column := 'SELECT
84 '||main_table_alias||'.'|| main_table_primary_key ;
85
86 from_table:=',master.program prog, master.product pr';
87
88 condition_text:= ' WHERE
89 '|| main_table_alias || '.is_void=false and
90 pr.id=ss.product_id and
91 prog.id=ss.program_id';
92
93 working_list_condition:= ' WHERE
94 '|| main_table_alias || '.is_void=false and
95 pr.id=ss.product_id and
96 prog.id=ss.program_id';
97
98 metadata_column:='';
99 metadata_condition:='';
100 crosstab_column:='id integer ';
101
102 -- mandatory tables
103 table_arr:=array_append(table_arr,'master.program_prog');
104 table_arr:=array_append(table_arr,'master.product_pr');
105 table_arr:=array_append(table_arr,'operational.seed_storage_'||main_table_alias);
106
107 -- working list temp table
108 execute 'select exists (
109 select 1
110 from information_schema.tables
111 where table_schema = ''temporary_data''
112 and table_name = ''temp_findseed_'||userId||'''
113 )' into seedListTableExist;
114
115 -- checks if the temporary table for working list is existing
116 if(seedListTableExist) THEN
117
118 from_joined_table:=from_joined_table||' left join temporary_data.temp_findseed_'||userId||'
119 as workingList on workingList.seed_storage_id=ss.id';
120 raise notice 'seed list table: %',from_joined_table;
121
122 select_column:=select_column||'
123 ,(case when workingList.seed_storage_id is null then ''excludeRow'' else ''includeRow''
124 end) as includeSeedlistOption';
125
126 if(includeSeedlistOption='false') THEN
127 condition_text:=condition_text||' and workingList.seed_storage_id is null';
128 END IF;
129 else
130 -- if table does not exist, includeSeedlistOption column will be set to excludeRow
131 -- this column is added as html class in the row
132 select_column:=select_column||',''excludeRow'' as includeSeedlistOption';
133
134 END IF;
135
136 raise notice '-- build the query using the search query parameters';
137
138 FOR r_rec in select * from json_to_recordset(filters) as x(abbrev text, values text, operator text, filter_type text)
139 LOOP
140 raise notice '%',r_rec;
141 execute '
142 Select * from
143 json_to_recordset($$'||config||'$$)
144 as x(
145 disabled text,
146 required text,
147 input_type text,
148 field_label text,
149 input_field text,
150 order_number text,
151 target_table text,
152 target_table_alias text,
153 default_value text,
154 target_column text,
155 allowed_values text,
156 basic_parameter text,
157 variable_abbrev text,
158 reference_column text,
159 field_description text,
160 primary_attribute text,
161 secondary_attribute text,
162 secondary_target_table text,
163 secondary_target_table_alias text,
164 secondary_target_column text,
165 filter_column text) where variable_abbrev=$$'||r_rec.abbrev||'$$'
166 into config_abbrev_rec;
167
168 raise notice '%',config_abbrev_rec.variable_abbrev;
169
170 if(config_abbrev_rec.variable_abbrev is not null and r_rec.filter_type='search'
171 and exists(select 1 from master.variable where abbrev=r_rec.abbrev)) then
172 -- use case: the target column is in the find seeds data filters config
173 raise notice '-- FILTER by search query parameters';
174 raise notice 'SEARCH ---- target: % target column: % ref column: % 2nd target table: %',
175 config_abbrev_rec.target_table, config_abbrev_rec.target_column,
176 config_abbrev_rec.reference_column, config_abbrev_rec.secondary_target_table;
177
178 if r_rec.operator='ilike any' then -- used for not exact matching/column filtering. assumes all cols are text
179
180 select '::text '||r_rec.operator||' (ARRAY['||string_agg('$$%'||replace(value,'"','')||'%$$', ',')||'])'
181 into cond_values from (select value::text from json_array_elements(r_rec.values::json))a ;
182
183 else -- used for exact match(operator is 'in')
184 -- by default, columns are cast to text
185
186 select ' '||r_rec.operator||' ('||string_agg('$$'||regexp_replace(value,'["|'']','','g')||'$$', ',')||')'
187 into cond_values from (select value::text from json_array_elements(r_rec.values::json))a ;
188
189 end if;
190 -- search param has 2nd target table
191 IF (config_abbrev_rec.secondary_target_table <> '' )THEN
192 raise notice '-- search param has 2nd target table';
193 raise notice 'table arr: %',table_arr;
194
195 IF (config_abbrev_rec.secondary_target_table||'_'||config_abbrev_rec.secondary_target_table_alias=any(table_arr) ) THEN
196 -- table is already in the array (for building the FROM query), prevent multiple declaring of tables
197 ELSE
198 -- add the 2ndary target table to the array
199 table_arr:=array_append(table_arr,config_abbrev_rec.secondary_target_table||'_'||config_abbrev_rec.secondary_target_table_alias);
200
201 from_joined_table :=from_joined_table || '
202 left join '||config_abbrev_rec.secondary_target_table ||' as ' || config_abbrev_rec.secondary_target_table_alias ||
203 ' on '||main_table_alias||'.'||config_abbrev_rec.reference_column||' = '||
204 config_abbrev_rec.secondary_target_table_alias||'.id ';
205
206 END IF;
207
208 IF config_abbrev_rec.target_table||'_'||config_abbrev_rec.target_table_alias=any(table_arr) THEN
209 -- checks if the target table or the primary table is in the array
210 ELSE
211 table_arr:=array_append(table_arr,config_abbrev_rec.target_table||'_'||
212 config_abbrev_rec.target_table_alias);
213
214 from_joined_table :=from_joined_table || '
215 left join '||config_abbrev_rec.target_table || ' as '||config_abbrev_rec.target_table_alias||
216 ' on '||config_abbrev_rec.target_table_alias||'.'||config_abbrev_rec.filter_column||' = '||
217 config_abbrev_rec.secondary_target_table_alias||'.'||config_abbrev_rec.secondary_target_column;
218
219 END IF;
220
221 condition_text:=condition_text||' and
222 '||config_abbrev_rec.target_table_alias||'.'||
223 config_abbrev_rec.filter_column||'::text '||cond_values;
224
225 raise notice 'tables: %',from_joined_table;
226 raise notice 'condition_text: %',condition_text;
227
228
229 ELSIF config_abbrev_rec.secondary_target_table = '' THEN
230 raise notice 'NO 2ndary table ';
231 -- no 2ndary target table, examples are the columns of operational.seed_storage,
232 -- operational.study columns such as source study name, source study
233 IF config_abbrev_rec.target_table ='operational.seed_storage' THEN
234 config_abbrev_rec.target_table_alias := main_table_alias;
235 END IF;
236
237 if(config_abbrev_rec.target_table||'_'||config_abbrev_rec.target_table_alias=any(table_arr) ) THEN
238
239 ELSE
240 table_arr:=array_append(table_arr,config_abbrev_rec.target_table||'_'||
241 config_abbrev_rec.target_table_alias);
242
243 from_joined_table :=from_joined_table || '
244 left join '||config_abbrev_rec.target_table ||
245 ' as ' || config_abbrev_rec.target_table_alias || ' on '||main_table_alias||'.'||
246 config_abbrev_rec.reference_column||' = '||config_abbrev_rec.target_table_alias||'.id ';
247 END IF;
248
249 IF r_rec.abbrev='VOLUME' THEN
250 select replace(value::text,'"','') into cond_values from json_array_elements(r_rec.values::json);
251 condition_text:=condition_text||' and
252 '||config_abbrev_rec.target_table_alias||'.'||
253 config_abbrev_rec.filter_column||''||cond_values;
254
255 ELSIF(r_rec.abbrev='FACILITY') THEN
256
257 select ' '||r_rec.operator||' ('||string_agg(''||replace(value,'"','')||'', ',')||')'
258 into cond_values from (select value::text from json_array_elements(r_rec.values::json))a ;
259
260 condition_text:=condition_text||'
261 and '||
262 '(with recursive fac(id,name,facility_type,level,parent_id,depth) as (
263 select
264 a.id, a.name,a.facility_type, a.level, a.parent_id, 1::int as depth
265 from
266 master.facility a
267 where
268 a.id= ss.facility_id
269 union all
270 select
271 b.id, b.name, b.facility_type, b.level, b.parent_id, fac.depth+1 as depth
272 from
273 master.facility b,fac
274 where
275 b.id = fac.parent_id
276 )
277 select id from fac order by depth desc limit 1)'
278 ||cond_values;
279
280 ELSIF r_rec.abbrev in ('PLOTNO','ENTNO','REP') then -- expected input value: [" 1 -5 ","11","25"]
281 raise notice 'RANGE input: %',r_rec.abbrev;
282
283 execute '
284 select ''(''||string_agg(value, '' or '')||'')''
285 from
286 (
287 select
288 (case
289 when value[2] is null then
290 '''||config_abbrev_rec.target_table_alias||'.'||
291 config_abbrev_rec.filter_column||' =''||value[1]
292
293 when value[2]::int=value[1]::int then
294 '''||config_abbrev_rec.target_table_alias||'.'||
295 config_abbrev_rec.filter_column||' =''||value[1]
296
297 when value[2]::int > value[1]::int then
298 ''('||config_abbrev_rec.target_table_alias||'.'||
299 config_abbrev_rec.filter_column||' >=''||value[1]||'' and '||config_abbrev_rec.target_table_alias||'.'||
300 config_abbrev_rec.filter_column||' <=''||value[2]||'')''
301 else
302 ''('||config_abbrev_rec.target_table_alias||'.'||
303 config_abbrev_rec.filter_column||' >=''||value[2]||'' and '||config_abbrev_rec.target_table_alias||'.'||
304 config_abbrev_rec.filter_column||' <=''||value[1]||'')''
305 end) as value
306 from (
307 select
308 regexp_matches(
309 regexp_replace(jsonval::text,''[\"*|\s*]'','''',''g''),
310 ''^(-?\d+(?:[.]\d+)?)-?(-?\d+(?:[.]\d+)?)?$'',
311 ''g'')
312 as value
313 from
314 json_array_elements('''||r_rec.values::json||''') as jsonval
315 )a
316 )range_values ' into cond_values;
317
318 condition_text:=condition_text||' and '||cond_values;
319
320 ELSE
321 condition_text:=condition_text||' and
322 '||config_abbrev_rec.target_table_alias||'.'||
323 config_abbrev_rec.filter_column||' '||cond_values;
324
325 END IF;
326
327 END IF;
328
329 ELSIF exists(select 1 from master.variable where abbrev=r_rec.abbrev) THEN
330 -- use case: column is not specified in the find seeds data filter config
331 -- use case: column filtering and input list search
332 raise notice 'COLUMN FILTERS/INPUT LIST ----';
333
334 IF r_rec.operator='ilike any' then -- used for column filtering
335
336 select replace(REGEXP_REPLACE(value,'\\''+', '''', 'g'),'"','') into cond_values from
337 (select value::text from json_array_elements(r_rec.values::json))a ;
338
339 select r_rec.operator||' (ARRAY['||string_agg('E''%'||replace(value,'"','')||'%''', ',')||'])'
340 into cond_values from (select value::text from json_array_elements(r_rec.values::json))a ;
341
342 else -- used for exact match(operator is 'in')
343 -- by default columns are cast to text
344
345 select r_rec.operator|| ' ('||string_agg(''''||replace(value,'"','')||'''', ',')||')',
346 ''''||string_agg(replace(value,'"',''), ',')||''''
347 into cond_values ,order_by_values
348 from (select value::text from json_array_elements(r_rec.values::json))a ;
349
350 end if;
351
352 execute '
353 Select * from
354 json_to_recordset($$'||column_config||'$$)
355 as x(
356 hidden text,
357 field_label text,
358 field_description text,
359 required text,
360 disabled text,
361 order_number text,
362 variable_abbrev text,
363 primary_attribute text,
364 secondary_attribute text,
365 target_table text,
366 target_table_alias text,
367 secondary_target_table text,
368 secondary_target_table_alias text,
369 target_column text,
370 secondary_target_column text,
371 reference_column text
372 )
373 where variable_abbrev=$$'||r_rec.abbrev||'$$ '
374 into column_config_rec;
375
376 raise notice ' var: %',column_config_rec.variable_abbrev;
377 raise notice ' var: %',column_config_rec.target_table;
378
379 IF (column_config_rec.secondary_target_table <> '' )THEN
380
381 IF column_config_rec.target_table||'_'||column_config_rec.target_table_alias=any(table_arr) THEN
382
383 ELSE
384 table_arr:=array_append(table_arr,column_config_rec.target_table||'_'||
385 column_config_rec.target_table_alias);
386
387 from_joined_table :=from_joined_table || '
388 left join '||
389 column_config_rec.target_table || ' '||column_config_rec.target_table_alias||' on '||
390 main_table_alias||'.'||column_config_rec.reference_column||' = '||
391 column_config_rec.target_table_alias||'.id';
392
393 END IF;
394
395 IF (column_config_rec.secondary_target_table||'_'||column_config_rec.secondary_target_table_alias=any(table_arr)) THEN
396
397 ELSE
398 table_arr:=array_append(table_arr,column_config_rec.secondary_target_table||'_'||column_config_rec.secondary_target_table_alias);
399
400 from_joined_table :=from_joined_table || '
401 left join '||column_config_rec.secondary_target_table || ' as '|| column_config_rec.secondary_target_table_alias||
402 ' on '||column_config_rec.target_table_alias||'.'||column_config_rec.target_column||' = '||
403 column_config_rec.secondary_target_table_alias||'.id ';
404 END IF;
405
406 condition_text:=condition_text||'
407 and '||
408 column_config_rec.secondary_target_table_alias||'.'||
409 column_config_rec.secondary_target_column||'::text '||cond_values;
410
411 ELSIF column_config_rec.secondary_target_table = '' THEN
412
413 IF column_config_rec.target_table||'_'||column_config_rec.target_table_alias=any(table_arr) THEN
414 IF(column_config_rec.variable_abbrev='DESIGNATION') THEN
415 -- from find seeds inputlist search by designation,exact match
416 select
417 string_agg(''''||replace(value,'"','')||'''', ','),
418 ''''||string_agg(replace(value,'"',''), ',')||''''
419
420 into cond_values ,order_by_values
421 from
422 (select z_admin.normalize_text(desig_index.value)as value
423 from (
424 select replace(value,'"','')as value from (
425 select value::text from json_array_elements(r_rec.values::json)
426 )a
427 ) as desig_index
428 )a ;
429
430 if r_rec.operator='ilike any' then -- used for column filtering
431
432 cond_values:= r_rec.operator||' (ARRAY['||cond_values||'])';
433
434 else-- used for exact match(operator is 'in')
435 -- by default columns are cast to text
436 cond_values:= r_rec.operator||' ('||cond_values||')';
437 end if;
438
439 condition_text:=condition_text||'
440 and pr.system_product_name '||cond_values;
441
442 order_by:='
443 array_position((SELECT
444 array_agg(id)
445 from (
446 select id
447 FROM master.product
448 JOIN UNNEST(''{' ||BTRIM(order_by_values, '''')||'}''::text[]) WITH ORDINALITY t(system_product_name, ord) USING (system_product_name)
449 WHERE
450 system_product_name '||cond_values||'
451 ORDER BY t.ord
452 )a),ss.product_id)';
453 final_order_by:= ' position (pr.system_product_name in '||order_by_values||')';
454 group_by:= 'pr.system_product_name';
455
456 ELSE
457 condition_text:=condition_text||'
458 and '||column_config_rec.target_table_alias||'.'||
459 column_config_rec.target_column||'::text '||cond_values;
460 END IF;
461
462 ELSE
463
464 table_arr:=array_append(table_arr,column_config_rec.target_table||'_'||
465 column_config_rec.target_table_alias);
466
467 IF column_config_rec.target_table='operational.seed_storage_metadata' THEN
468 -- special case for seed_storage_metadata , append to column as parameter in crosstab
469
470 metadata_column_arr:=array_append(metadata_column_arr,(select id from master.variable where
471 abbrev=column_config_rec.variable_abbrev));
472
473 ELSIF column_config_rec.target_table <> 'operational.seed_storage' THEN
474 --default is using left join to get values from other table
475
476 IF(r_rec.abbrev='FACILITY') THEN
477 condition_text:=condition_text||'
478 and '||
479 '(with recursive fac(id,name,facility_type,level,parent_id,depth) as (
480 select
481 a.id, a.name,a.facility_type, a.level, a.parent_id, 1::int as depth
482 from
483 master.facility a
484 where
485 a.id= ss.facility_id
486 union all
487 select
488 b.id, b.name, b.facility_type, b.level, b.parent_id, fac.depth+1 as depth
489 from
490 master.facility b,fac
491 where
492 b.id = fac.parent_id
493 )
494 select name::text from fac order by depth desc limit 1
495 )'
496 ||cond_values;
497
498 ELSE
499 condition_text:=condition_text||'
500 and '||column_config_rec.target_table_alias||'.'||
501 column_config_rec.target_column||'::text '||cond_values;
502 END IF;
503
504 from_joined_table :=from_joined_table || '
505 left join '||column_config_rec.target_table || ' as ' ||
506 column_config_rec.target_table_alias ||
507 ' on '||main_table_alias||'.'||column_config_rec.reference_column||' = '||
508 column_config_rec.target_table_alias||'.id ';
509
510 ELSE -- operational.seed storage columns
511
512 IF(r_rec.abbrev='GID') THEN
513
514 IF r_rec.operator='ilike any' then -- used for column filtering
515
516 condition_text:=condition_text||'
517 and '||main_table_alias||'.'||
518 column_config_rec.target_column||'::text '||cond_values;
519
520 else
521 -- used for exact match(operator is 'in')
522 -- by default columns are cast to text
523 -- from find seeds inputlist search by gid
524
525 condition_text:=condition_text||'
526 and '||main_table_alias||'.'||
527 column_config_rec.target_column||' '||cond_values;
528
529 order_by:= 'position (ss.product_id::text in (
530 select string_agg(product_id::text, '','' order by position (
531 gid::text in '|| order_by_values ||'))
532 from master.product_gid where gid
533 '||cond_values||'))';
534 final_order_by:=' order by position (
535 ss.gid::text in '|| order_by_values ||')';
536 main_order_by:=' order by row_number,rank_number';
537 end if;
538 ELSIF(r_rec.abbrev='LABEL') THEN
539
540 IF r_rec.operator='ilike any' then -- used for column filtering
541
542 condition_text:=condition_text||'
543 and '||main_table_alias||'.'||
544 column_config_rec.target_column||'::text '||cond_values;
545
546 else
547 -- used for exact match(operator is 'in')
548 -- by default columns are cast to text
549 -- from find seeds inputlist search by gid
550
551 condition_text:=condition_text||'
552 and '||main_table_alias||'.'||
553 column_config_rec.target_column||' '||cond_values;
554
555 final_order_by:= ' ORDER BY position (ss.label::text in '||order_by_values||')';
556 group_by:= 'ss.label';
557 end if;
558 ELSE
559 IF r_rec.operator='ilike any' then -- used for column filtering
560
561 condition_text:=condition_text||'
562 and '||main_table_alias||'.'||
563 column_config_rec.target_column||'::text '||cond_values;
564
565 else -- used for exact match(operator is 'in')
566 -- by default columns are cast to text
567
568 condition_text:=condition_text||'
569 and '||main_table_alias||'.'||
570 column_config_rec.target_column||' '||cond_values;
571 END IF;
572
573 END IF;
574
575 END IF;
576
577 END IF;
578
579 ELSIF (r_rec.abbrev='SYNONYM') THEN
580
581 -- from find seeds inputlist search by designation, return synonyms
582 select
583 string_agg(''''||replace(value,'"','')||'''', ','),
584 ''''||string_agg(replace(value,'"',''), ',')||''''
585
586 into cond_values ,order_by_values
587 from
588 (select z_admin.normalize_text(desig_index.value)as value
589 from (
590 select replace(value,'"','')as value from (
591 select value::text from json_array_elements(r_rec.values::json)
592 )a
593 ) as desig_index
594 )a ;
595
596 if r_rec.operator='ilike any' then -- used for column filtering
597
598 cond_values:= r_rec.operator||' (ARRAY['||cond_values||'])';
599
600 else-- used for exact match(operator is 'in')
601 -- by default columns are cast to text
602 cond_values:= r_rec.operator||' ('||cond_values||')';
603 end if;
604
605 condition_text:=condition_text||'
606 and pr.id in (
607 select px.id
608 from
609 master.product_name pn,
610 master.product px
611 where
612 pn.system_product_name '||cond_values||'
613 and pn.product_id=px.id
614 and pn.is_void=false
615 and px.is_void=false
616 )';
617
618 order_by:='
619 array_position((SELECT
620 array_agg(id)
621 from (
622 select px.id
623 FROM
624 master.product px,
625 master.product_name pn
626 JOIN UNNEST(''{' ||BTRIM(order_by_values, '''')||'}''::text[])
627 WITH ORDINALITY t(system_product_name, ord) USING (system_product_name)
628 WHERE
629 pn.system_product_name '||cond_values||'
630 and pn.product_id=px.id
631 and pn.is_void=false
632 and px.is_void=false
633 ORDER BY t.ord
634 )a),ss.product_id)
635 ';
636 final_order_by:= order_by;
637
638 group_by:= 'synonym';
639
640 END IF;
641 END IF;
642 END LOOP;
643 -- Retain order as inputted in INPUT List
644 IF(group_by='ss.label') then
645 order_by:='
646 array_position((SELECT
647 array_agg(product_id)
648 from (
649 select ss.product_id
650 FROM '||
651 main_table|| ' ' || main_table_alias ||
652 from_joined_table||
653 from_table ||
654 condition_text||
655 final_order_by||'
656 )a),ss.product_id)';
657 main_order_by:=' order by row_number,rank_number';
658
659 ELSIF(group_by='pr.system_product_name') then
660
661 final_order_by:= ' ORDER BY '||final_order_by;
662 main_order_by:=' order by row_number,rank_number';--||final_order_by;
663
664 ELSIF(group_by='synonym') then
665
666 final_order_by:= ' ORDER BY '||final_order_by;
667 main_order_by:=' order by row_number,rank_number';--||final_order_by;
668
669 END IF;
670
671 raise notice '-- build columns based on config dataset for data browser columns';
672
673 FOR column_config_rec in
674 execute '
675 Select * from
676 json_to_recordset($$'||column_config||'$$)
677 as x(
678 hidden text,
679 field_label text,
680 field_description text,
681 order_number text,
682 variable_abbrev text,
683 additional_columns text,
684 target_table text,
685 target_table_alias text,
686 secondary_target_table text,
687 secondary_target_table_alias text,
688 target_column text,
689 secondary_target_column text,
690 reference_column text
691 ) order by order_number::int'
692
693 LOOP
694 raise notice '%',column_config_rec.variable_abbrev;
695
696 crosstab_column:=crosstab_column||' ,
697 '|| column_config_rec.variable_abbrev|| ' text';
698
699 IF(column_config_rec.additional_columns<>'') THEN
700 select_column:= select_column|| ',
701 '||column_config_rec.additional_columns;
702 END IF;
703
704 IF(column_config_rec.target_table_alias<>'' and column_config_rec.target_table_alias is not null and
705 column_config_rec.target_table<>'operational.seed_storage_metadata') then
706
707 IF column_config_rec.secondary_target_table is not null and
708 column_config_rec.secondary_target_table <> '' THEN
709
710 select_column:= select_column|| ',
711 '||column_config_rec.secondary_target_table_alias||'.'||
712 column_config_rec.secondary_target_column || ' as '|| column_config_rec.variable_abbrev;
713 ELSE
714 IF column_config_rec.variable_abbrev='FACILITY' THEN
715 select_column:= select_column|| ',
716 '||
717 '(with recursive fac(id,name,facility_type,level,parent_id,depth) as (
718 select
719 a.id, a.name,a.facility_type, a.level, a.parent_id, 1::int as depth
720 from
721 master.facility a
722 where
723 a.id= ss.facility_id
724
725 union all
726 select
727 b.id, b.name, b.facility_type, b.level, b.parent_id, fac.depth+1 as depth
728 from master.facility b,fac
729 where
730 b.id = fac.parent_id
731 )
732 select name from fac order by depth desc limit 1)'
733 ||
734 ' as '|| column_config_rec.variable_abbrev;
735 ELSE
736 select_column:= select_column|| ',
737 '||column_config_rec.target_table_alias||'.'||
738 column_config_rec.target_column || ' as '|| column_config_rec.variable_abbrev;
739 END IF;
740 END IF;
741
742 elsif column_config_rec.target_table<>'operational.seed_storage_metadata' THEN
743
744 select_column:= select_column|| ',
745 '||column_config_rec.target_table||'.'||
746 column_config_rec.target_column || ' as '|| column_config_rec.variable_abbrev;
747
748 end if;
749
750 raise notice 'table: %',table_arr;
751
752 IF (column_config_rec.secondary_target_table <> '' )THEN
753
754 IF column_config_rec.target_table||'_'||column_config_rec.target_table_alias=any(table_arr) THEN
755
756 ELSE
757 -- target table will be added as joined table
758 table_arr:=array_append(table_arr,column_config_rec.target_table||'_'||
759 column_config_rec.target_table_alias);
760
761 from_joined_table :=from_joined_table || '
762 left join '||column_config_rec.target_table || ' as ' ||
763 column_config_rec.target_table_alias ||' on '||main_table_alias||'.'||
764 column_config_rec.reference_column||' = '||column_config_rec.target_table_alias||'.id ';
765
766 END IF;
767
768 IF (column_config_rec.secondary_target_table|| '_'||column_config_rec.secondary_target_table_alias=any(table_arr)) THEN
769
770 ELSE
771 table_arr:=array_append(table_arr,column_config_rec.secondary_target_table|| '_'||column_config_rec.secondary_target_table_alias);
772
773 from_joined_table :=from_joined_table || '
774 left join '||column_config_rec.secondary_target_table || ' as '||column_config_rec.secondary_target_table_alias ||
775 ' on '||column_config_rec.target_table_alias||'.'||column_config_rec.target_column||' = '||
776 column_config_rec.secondary_target_table_alias||'.id ';
777 END IF;
778
779 -- secondary table will be added as joined table
780 raise notice '-- secondary table will be added as joined table';
781
782 ELSIF column_config_rec.secondary_target_table = '' THEN
783 IF column_config_rec.target_table||'_'||column_config_rec.target_table_alias=any(table_arr) THEN
784 ELSE
785 table_arr:=array_append(table_arr,column_config_rec.target_table||'_'||
786 column_config_rec.target_table_alias);
787
788 IF column_config_rec.target_table='operational.seed_storage_metadata' THEN
789 -- special case for seed_storage_metadata , append to column as parameter in crosstab
790 metadata_column_arr:=array_append(metadata_column_arr,(select id from master.variable
791 where abbrev=column_config_rec.variable_abbrev));
792
793 ELSIF column_config_rec.target_table <>'operational.seed_storage' THEN
794 --default is using left join to get values from other table
795
796 from_joined_table :=from_joined_table || '
797 left join '||column_config_rec.target_table || ' as ' ||
798 column_config_rec.target_table_alias ||
799 ' on '||main_table_alias||'.'||column_config_rec.reference_column||' = '||
800 column_config_rec.target_table_alias||'.id ';
801
802 END IF;
803 END IF;
804 END IF;
805
806 END LOOP;
807 raise notice 'select column: %',select_column;
808 raise notice 'main_table: %',main_table;
809 raise notice 'main_table_alias: %',main_table_alias;
810 raise notice 'from_joined_table: %',from_joined_table;
811 raise notice 'condition_text: %',condition_text;
812 raise notice 'final_order_by: %',final_order_by;
813 main_sql:='
814 select
815 *,
816 (case when row_number::int%2=0 then 0 else 1 end) as product_group,
817 max(rank_number) over (partition by product_id ORDER BY row_number) as product_count
818 from (
819 '||select_column||',
820 dense_rank () OVER (ORDER BY '||order_by||') as row_number,
821 row_number () OVER (partition by pr.id '||final_order_by||') as rank_number
822 FROM '||
823 main_table|| ' ' || main_table_alias ||
824 from_joined_table||
825 from_table ||
826 condition_text||
827 ')a' || main_order_by;
828
829 count_sql:='select count(*)
830 FROM '|| main_table|| ' ' || main_table_alias ||
831 from_joined_table|| from_table ||
832 condition_text;
833
834 working_list_sql:=select_column||'
835 FROM '|| main_table|| ' ' || main_table_alias ||
836 from_joined_table|| from_table || working_list_condition;
837
838 RETURN NEXT;
839
840END;
841$BODY$;
842
843ALTER FUNCTION platform.find_seed_build_query(json)
844 OWNER TO postgres;