internal/database/migrations/004_create_functions.up.sql - main - iezhelev/cppsh_micro

internal/database/migrations/004_create_functions.up.sql
169 строк14 KB
iezhelev
query update
06 июн 2025, 15:44
06 июн 2025, 15:44ce549ba
CREATE OR REPLACE FUNCTION public.get_dayplan_data(
    p_timefrom TIMESTAMP DEFAULT null,
    p_topicid TEXT DEFAULT '',
    p_userid INTEGER DEFAULT 6173
)
RETURNS TABLE (
    fields JSONB
)
LANGUAGE SQL
AS $$
        WITH params AS (
                SELECT 
                p_timefrom AS timefrom,
                p_topicid AS topicid,
                p_userid AS userid
        ),
	pers as (
		select vp.personalid, podrid from workspace_user wu  
		left outer join rel_personal_workspace_users pwu on pwu.workspace_userid=wu.workspace_userid 
		inner join personal vp on pwu.personalid = vp.personalid
		where userid = (select userid from params)
	) 
	,act_pers as (
	select distinct pw.pa_worksid, pa.plannedactivityid, pa.obj_osn_infid, obj_infid, obor_szbid, personalid, userid,
	activity_shid, pa.activity_normid, citypeid, ciid
	from plannedactivity_persons pp
	inner join plannedactivity pa on pp.plannedactivityid = pa.plannedactivityid
	inner join pa_works pw on pa.pa_worksid = pw.pa_worksid 
	where pw.workflowstepname in ('dayPlan', 'MRMProcessing', 'processing','canceled','close','done') 
	and pp.personalid = (select personalid from pers)
	and (date(coalesce(pa.userdatastart,pa.resultdaydata,pa.plannedstarttime)) between date(CURRENT_DATE - INTERVAL '13 DAY') and date(CURRENT_DATE  + INTERVAL '10 DAY'))
	--limit 10
) --select * from act_pers;
        , usr as (
		SELECT podrid
		FROM ipuser su 
		LEFT OUTER JOIN workspace_user wu ON wu.userid=su.userid 
		LEFT OUTER JOIN rel_personal_workspace_users pwu ON pwu.workspace_userid=wu.workspace_userid 
		INNER JOIN personal vp ON pwu.personalid = vp.personalid 
		WHERE su.userid = (SELECT userId FROM params)
	), assignee as (
	        select pa_worksid, string_agg(distinct per.full_name_str, ', ') assig from act_pers acts
		inner join plannedactivity_persons pap on pap.plannedactivityid = acts.plannedactivityid
	        inner join personal per on pap.personalid = per.personalid
	        group by pa_worksid
	), ciprms as (
                select distinct ciparamid from planned_activity_params where 
                plannedactivityid in (select plannedactivityid from act_pers)	
	), upd_acts as (
	 	select ap.* from act_pers ap
		inner join pa_works pw on ap.pa_worksid = pw.pa_worksid
	 	where 1=1 -- ap.pa_worksid in (select distinct pa_worksid from act_pers) 
		--and (coalesce(on_mrm,0)=0 or (select timefrom from params) is null) 
		and ((select timefrom from params) is null or synctime > (select timefrom from params))
	)
	, messages as ( 
	select 'plannedactivity' as table, plannedactivityid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime "time" 
	from (select activity_normid,activity_shid,ciid,citypeid,confirm_done_work,createdbyid,extract(epoch from createdtime) * 1000 as createdtime,extract(epoch from deadline_fixed) * 1000 as deadline_fixed,description,factduration,extract(epoch from factendtime) * 1000 as factendtime,factlaborintensity,extract(epoch from factstarttime) * 1000 as factstarttime,mat_approved,mat_required,mat_used,obj_infid,obj_osn_infid,obor_szbid,off_approved,off_required,pa_worksid,plannedactivityid,plannedduration,extract(epoch from plannedendtime) * 1000 as plannedendtime,plannedlaborintensity,extract(epoch from plannedstarttime) * 1000 as plannedstarttime,podrid,predid,priority,shortname,templateid,trns_approved,trns_required,updatedbyid,extract(epoch from updatedtime) * 1000 as updatedtime,wnd_approved,wnd_required,workflowid,workflowstepid,workflowstepname,displayname--,obor_szbid_name
		from plannedactivity where plannedactivityid in (select plannedactivityid from act_pers) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'pa_works', pa_worksid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime 
	from (select activity_normid,activity_shid_string,createdbyid,extract(epoch from createdtime) * 1000 as createdtime,extract(epoch from deadline) * 1000 as deadline,extract(epoch from deadline_fixed) * 1000 as deadline_fixed,discription_work,displayname_static,dorid,extract(epoch from factendtime) * 1000 as factendtime,extract(epoch from factstarttime) * 1000 as factstarttime,incidentid,obj_osn_infid,on_mrm,pw.pa_worksid,extract(epoch from plannedstarttime) * 1000 as plannedstarttime,podrid,predid,priority,shortname,templateid,updatedbyid,extract(epoch from updatedtime) * 1000 as updatedtime,workflowid,workflowstepid,workflowstepname, assig as pa_works_persons_for_list_view,plannedlaborintensity,fact_chel_chas_sum,displayname,prioritet_new
		from pa_works pw left outer join assignee sg on pw.pa_worksid = sg.pa_worksid where pw.pa_worksid in (select distinct pa_worksid from act_pers) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'planned_activity_params', planned_activity_paramsid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime
	from (select norm_measure , value_color , createdbyid , ciparamid, citypeparamsid , norm , minimumnorm , metermethod , measureunitid , maximumnorm , isactive , incidentid  , displayname , date_of_measure , current_type , ciparamid , extract(epoch from createdtime) * 1000 as createdtime , updatedbyid , shortname , plannedactivityid , extract(epoch from updatedtime) * 1000 as updatedtime , planned_activity_paramsid , value , workflowid , workflowstepid , workflowstepname , indicator , synctime 
                from planned_activity_params where plannedactivityid in (select plannedactivityid from act_pers) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'citypeparams', ciparamsid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime 
	from (select calculationtype , calculation , value_format , value , extract(epoch from updatedtime) * 1000 as updatedtime, updatedbyid , unitofmeasure , sortorder , skipvisualization , shortname , paramname , param_type , normlistid , isactive , fieldtype , displayname , defaultvalue , extract(epoch from createdtime) * 1000 as createdtime, createdbyid , citype , ciparamsid , synctime 
                from citypeparams where ciparamsid in (select ciparamid from ciprms) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'ciparam', ciparamid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime 
	from (select shortname, unitofmeasure , up_ustr_citype_string , up_ustr_citype_string2 , up_ustr_id_string , updatedbyid , extract(epoch from updatedtime) * 1000 as updatedtime, value , unit_of_measure , sortorder , paramname , citype_shortname , param_type , skipvisualization , value_format , healthicon , param_order , ci , ciparam , ciparamid , citype , COALESCE(concat_name,'') as concat_name , createdbyid , extract(epoch from createdtime) * 1000 as createdtime , description , displayname , filtr_priz , healthindicator , incident , isactive , maximumnorm , minimumnorm , norm , pa_id_link , service_level , synctime 
                from ciparam where ci in (select ciid from act_pers) and ciparamid in (select ciparamid from ciprms) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
        select 'citypicalfault', citypicalfaultid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime 
	        from (select shortname, citype, citypicalfaultid, dangerous, deadline, deadlineunits, displayname, require_mat, roles,  extract(epoch from createdtime) * 1000 as createdtime, createdbyid, extract(epoch from updatedtime) * 1000 as updatedtime, updatedbyid from citypicalfault where citype in (select citypeid from act_pers) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'workflowstep', workflowstepid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime
	from (select createdbyid, extract(epoch from createdtime) * 1000 as createdtime, displayname, internalname, isactive, shortname, sortorder, steptype, updatedbyid, extract(epoch from updatedtime) * 1000 as updatedtime, workflowid, workflowstepid
                from ipworkflowstep where workflowid = 231 and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'comment', commentid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime 
	from (select FLOOR(extract(epoch from updatedtime) * 1000) as updatedtime,isactive,shortname,FLOOR(extract(epoch from createdtime) * 1000) as createdtime,entitytypeid,updatedbyid,commentid,createdbyid,workspaceid,entityid,subject,comment,parentcommentid,source from comment 
	where exists (select plannedactivityid from act_pers where 
			entitytypeid = 3692 and entityid = plannedactivityid or  
			entitytypeid = 4314 and entityid = obj_osn_infid or
			entitytypeid = 4444 and entityid = obor_szbid or
			entitytypeid = 4387 and entityid = obj_infid 
			) and ((select timefrom from params) is null or synctime > (select timefrom from params))) t
	union all
	select 'ipattachment', attachmentid as id, jsonb_strip_nulls(to_jsonb(t)) fields, updatedtime 
		from (select FLOOR(extract(epoch from att.updatedtime) * 1000) as updatedtime, att.isactive, att.shortname, FLOOR(extract(epoch from att.createdtime) * 1000) as createdtime,entityid,entitytypeid,att.updatedbyid,att.attachmentid,att.createdbyid,att.workspaceid,displayname 
		from ipattachment att inner join ipentityattachment ett on att.shortname = ett.shortname
		where exists (select plannedactivityid from act_pers where 
			entitytypeid = 3692 and entityid = plannedactivityid or  
			entitytypeid = 4314 and entityid = obj_osn_infid or
			entitytypeid = 4444 and entityid = obor_szbid or
			entitytypeid = 4387 and entityid = obj_infid 
			) and ((select timefrom from params) is null or att.synctime > (select timefrom from params))) t
	union all
	select 'reference', id, jsonb_strip_nulls(to_jsonb(t)) fields, 0 
	from (
		select row_number() over (order by (select 1)) as id, * from (
			select 'citype' as reference_name, citypeid as refid, '' as name, '' as sname, shortname, displayname, null as payload from citype where citypeid in (select citypeid from upd_acts)
			union all
			select 'edizm' as reference_name, edizmid as refid, name, sname, shortname, displayname, null from edizm where edizmid in (select measureunitid from planned_activity_params where plannedactivityid in (select plannedactivityid from upd_acts))
			union all
			select 'obj_osn_inf' as reference_name, obj_osn_infid as refid, vname, name, shortname, displayname, null from obj_osn_inf where obj_osn_infid in (select obj_osn_infid from upd_acts)
			union all
			select 'obj_inf' as reference_name, obj_infid as refid, vname, name, shortname, displayname, null from obj_inf where obj_infid in (select obj_infid from upd_acts)
			union all
			select 'obor_szb' as reference_name, obor_szbid as refid, oz.full_name, oz.obor_oboz, oz.shortname, oz.displayname, '{"latitude":'||latitude||',"longitude":'||longitude||'}'  from obor_szb oz 
                                inner join obj_inf oi on oi.obj_infid = oz.obj_infid
                                where obor_szbid in (select obor_szbid from upd_acts)
			union all
			select 'activity_norm' as reference_name, activity_normid as refid, fullname, description, shortname, displayname, null from activity_norm where activity_normid in (select activity_normid from upd_acts) /*and ((select timefrom from params) is null or synctime > (select timefrom from params))*/
                        union all
			select 'activity_sh' as reference_name, activity_shid as refid, cypher as fullname, cypher_name, shortname, displayname, null from activity_sh where activity_shid in (select activity_shid from upd_acts) /*and ((select timefrom from params) is null or synctime > (select timefrom from params))*/
			union all
			select 'user', su.userid, su.fullname, su.title, su.shortname, null, null
				from ipuser su 
                                left outer join workspace_user wu on wu.userid=su.userid 
                                left outer join rel_personal_workspace_users pwu on pwu.workspace_userid=wu.workspace_userid 
                                inner join personal vp on pwu.personalid = vp.personalid 
                                where vp.podrid = (select podrid from usr) and ((select timefrom from params) is null)
	           	) tt
		) t
	) 
	--select COALESCE(array_to_json(array_agg(m)), '[]'::json) msg from (select *, 'INSERT' as action from messages) m;
	select coalesce(jsonb_strip_nulls(to_jsonb(m)), '{}') msg from (select *, 'INSERT' as action from messages) m

$$;

CREATE OR REPLACE FUNCTION public.get_history_items_for_entity(
	p_entity_id INTEGER,
	p_entity_type_id INTEGER,
	p_items_count INTEGER,
	p_time_before TIMESTAMP
)
RETURNS TABLE (
    fields JSONB
)
LANGUAGE SQL
AS $$
	with acts as (
		select plannedactivityid from plannedactivity where 
			p_entity_type_id = 3692 and p_entity_id = plannedactivityid or  
			p_entity_type_id = 4314 and p_entity_id = obj_osn_infid or
			p_entity_type_id = 4444 and p_entity_id = obor_szbid or
			p_entity_type_id = 4387 and p_entity_id = obj_infid 
	)
	, messages as (
	select 'planned_activity_params' as table, planned_activity_paramsid as id, jsonb_strip_nulls(to_jsonb(t)) fields, extract(epoch from updatedtime) * 1000 tm
	from (select * from planned_activity_params where plannedactivityid in (select plannedactivityid from acts) and (p_time_before is null or updatedtime < p_time_before)) t
	union all
	select 'comment', commentid as id, jsonb_strip_nulls(to_jsonb(t)) fields, createdtime 
		from (select FLOOR(extract(epoch from updatedtime) * 1000) as updatedtime,isactive,shortname,FLOOR(extract(epoch from createdtime) * 1000) as createdtime,entitytypeid,updatedbyid,commentid,createdbyid,workspaceid,entityid,subject,comment,parentcommentid,source from comment 
		where p_entity_type_id = entitytypeid and p_entity_id = entityid and (p_time_before is null or createdtime < p_time_before)) t
	union all
	select 'ipattachment', attachmentid as id, jsonb_strip_nulls(to_jsonb(t)) fields, createdtime 
		from (select FLOOR(extract(epoch from att.updatedtime) * 1000) as updatedtime, att.isactive, att.shortname, FLOOR(extract(epoch from att.createdtime) * 1000) as createdtime,entityid,entitytypeid,att.updatedbyid,att.attachmentid,att.createdbyid,att.workspaceid,displayname 
		from ipattachment att inner join ipentityattachment ett on att.shortname = ett.shortname
		where  p_entity_type_id = entitytypeid and p_entity_id = entityid and (p_time_before is null or att.createdtime < p_time_before)) t
	)
	select coalesce(jsonb_strip_nulls(to_jsonb(m)), '{}') msg from (select *, 'INSERT' as action from messages order by tm limit 20) m
 
$$;