Untitled

a
 avatar
unknown
sql
2 years ago
2.3 kB
6
Indexable
SELECT rca.name as IssueCategory,
  case 
  when dateFrom is null and dateTo is null then
  (SELECT count(i.*) FROM vw_dash_laporan_issue_proyek i
  	WHERE i.issue_category = rca.name)
  else
  (SELECT count(i.*) FROM vw_dash_laporan_issue_proyek i
  	WHERE i.issue_category = rca.name AND i.created_at::text BETWEEN $1 AND $2)
  end::integer as totalProject,

 case 
  when dateFrom is null and dateTo is null then
  COALESCE((select jsonb_agg(
	  jsonb_build_object(
        'issue_name', i.issue_name,
        'code_proyek', i.code_proyek,
		'name_proyek', i.name_proyek,
		'issue_category', i.issue_category,
		'issue_status', i.issue_status,
		'impacts', i.impacts,
		'response_plans', i.response_plans,
		'created_at', i.created_at,
		'updated_at', i.updated_at
    )
  ) res FROM vw_dash_laporan_issue_proyek i 
			WHERE (lower(i.issue_name) like '%' || lower($3) || '%' 
				   or lower(i.code_proyek) like '%' || lower($3) || '%'
				  or lower(i.name_proyek) like '%' || lower($3) || '%'
				   or lower(i.issue_category) like '%' || lower($3) || '%'
				   or lower(i.issue_status) like '%' || lower($3) || '%'
				   or lower(i.impacts) like '%' || lower($3) || '%'
				   or lower(i.response_plans) like '%' || lower($3) || '%') 
			AND i.issue_category = rca.name), '{}'::jsonb) 
  else
  	COALESCE((select jsonb_agg(
	  jsonb_build_object(
        'issue_name', i.issue_name,
        'code_proyek', i.code_proyek,
		'name_proyek', i.name_proyek,
		'issue_category', i.issue_category,
		'issue_status', i.issue_status,
		'impacts', i.impacts,
		'response_plans', i.response_plans,
		'created_at', i.created_at,
		'updated_at', i.updated_at
    )
  ) res FROM vw_dash_laporan_issue_proyek i 
			WHERE (lower(i.issue_name) like '%' || lower($3) || '%' 
				   or lower(i.code_proyek) like '%' || lower($3) || '%'
				  or lower(i.name_proyek) like '%' || lower($3) || '%'
				   or lower(i.issue_category) like '%' || lower($3) || '%'
				   or lower(i.issue_status) like '%' || lower($3) || '%'
				   or lower(i.impacts) like '%' || lower($3) || '%'
				   or lower(i.response_plans) like '%' || lower($3) || '%') 
			AND i.issue_category = rca.name 
			AND i.created_at::text BETWEEN $1 AND $2 ), '{}'::jsonb)
  end as dataProject
  FROM risk_categories rca
Editor is loading...