CREATE OR REPLACE FUNCTION dbo.xp_personrebuildstate(person_id bigint, statetable character varying DEFAULT 'PersonState'::character varying, personevnclass_id bigint DEFAULT NULL::bigint, debug integer DEFAULT NULL::integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
-- обновление активного состояния человека
declare 
	CRLF varchar(2);
	p_PersonEvnClass_SysNick varchar(128); 
	SqlText varchar ;
 	OuterSqlText varchar;
 	PersonFilter varchar(50);
 	SubObj varchar(128);
 	LinkField varchar(128);
 	Fields varchar;
 	OuterFields varchar;
 	p_schema_name varchar(128);
 	ClassCode BIGINT;
	ObjCur record;
begin

        drop table if exists pg_temp.columns00;
        create temporary table pg_temp.columns00 on commit drop as select table_schema, table_name, column_name from information_schema.columns;
        create index on pg_temp.columns00(table_name, column_name);

	CRLF := chr(13)||chr(10);
-- set schema_name = (select top 1 schema_name(schema_id) from sys.tables where name = StateTable order by schema_id)
-- считаем что все таблицы в dbo
	p_schema_name := 'dbo';
	PersonFilter := StateTable||'.Person_id = ' || cast(Person_id as varchar(20));
	/*
	-- для конкретного человека удаляем и вставляем запись
	if PersonEvnClass_id is null
	begin
		set SqlText= 'delete from ' || schema_name || '.' || StateTable || ' with (rowlock) where ' || PersonFilter || CRLF ||
		'insert into ' || schema_name || '.' || StateTable || ' with (rowlock) (Person_id) select ' || cast(Person_id as varchar(20))
	end
	*/
	-- если указан определенный класс события, то просто зануляем эти поля
	if PersonEvnClass_id is not null
	then
		select p.PersonEvnClass_SysNick into p_PersonEvnClass_SysNick 
		from dbo.PersonEvnClass P where P.PersonEvnClass_id = xp_PersonRebuildState.PersonEvnClass_id;
	
		SubObj := COALESCE((select p.PersonEvnSubClass_SysNick from dbo.PersonEvnSubClass p where p.PersonEvnClass_id = xp_PersonRebuildState.PersonEvnClass_id),'');
		--RAISE  notice 'SubObj=% p_PersonEvnClass_SysNick=% p_schema_name=%',SubObj,p_PersonEvnClass_SysNick,p_schema_name;
		LinkField := COALESCE((select c_fk.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as fk 
								JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc on rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME 
								JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as pk on pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME 
								JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c_fk on c_fk.CONSTRAINT_NAME = fk.CONSTRAINT_NAME 
								JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c_pk on c_pk.CONSTRAINT_NAME = pk.CONSTRAINT_NAME and c_pk.ORDINAL_POSITION = c_fk.ORDINAL_POSITION 
								where fk.CONSTRAINT_TYPE = 'FOREIGN KEY' and pk.TABLE_NAME=lower(p_PersonEvnClass_SysNick) 
								and fk.TABLE_NAME=lower(SubObj) and fk.table_schema=lower( p_schema_name ) 
								and pk.table_schema='dbo'),'');
	
		Fields := '';
		select 
		string_agg(distinct c1.column_name || ' = null',','||CRLF 	)||CRLF
		into Fields
		from  pg_temp.columns00 c1 
		inner join  pg_temp.columns00 c2  on c1.table_schema = c2.table_schema and c2.table_name =lower ('v_' || p_PersonEvnClass_SysNick) and (c1.column_name = c2.column_name or c1.column_name = LinkField)
		where c1.table_schema = lower( p_schema_name) and c1.table_name = lower(StateTable ) and c1.column_name not in ('person_id', 'server_id') and c1.column_name not like 'pmuser%';

	
		/*if Fields<>'' then
			Fields := left(Fields,length(Fields)-3) || CRLF;
		end if;*/
	
		SqlText:= 'update ' || StateTable || ' set ' || CRLF || Fields || 'where ' || PersonFilter;
		
		if COALESCE(Debug,0) = 1 then
			RAISE  notice '%', SqlText;
		else 
			RAISE  notice '%', SqlText;
			execute(SqlText);
		end if;
	end if;
	
	-- готовим запрос для нахождения активных состояний для каждого события
	SqlText:= 'update ' || p_schema_name || '.' || StateTable || ' set' || CRLF;
	OuterSqlText:= '';
	--select PersonEvnClass_id, PersonEvnClass_SysNick from dbo.PersonEvnClass 
	--where PersonEvnClass_IsState = 2 and (PersonEvnClass_id = PersonEvnClass_id or PersonEvnClass_id is null)
	--=================================================================
	
	for  ObjCur  in (select 
		P.PersonEvnClass_id, 
		P.PersonEvnClass_SysNick,
		coalesce(sb.PersonEvnSubClass_SysNick,'') AS SubObj,
		--coalesce(LF.column_name,'') AS LinkField ,
		P.PersonEvnClass_Code	
		FROM dbo.PersonEvnClass P 
		LEFT JOIN LATERAL(
			select PersonEvnSubClass_SysNick from dbo.PersonEvnSubClass pesc 
			inner join dbo.PersonEvnClass pec  on pesc.PersonEvnClass_id = pec.PersonEvnClass_id where pec.PersonEvnClass_SysNick = P.PersonEvnClass_SysNick
		)SB on true
		/*LEFT JOIN LATERAL(
			select a.attname as column_name
			from pg_attribute af, pg_attribute a,
			(select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey,conname
			from (select conrelid,confrelid,conkey,confkey,
			generate_series(1,array_upper(conkey,1)) as i,conname
			from pg_constraint where contype = 'f') ss) ss2
			where af.attnum = confkey and af.attrelid = confrelid and
			a.attnum = conkey and a.attrelid = conrelid
			AND confrelid::regclass = COALESCE(lower(sb.PersonEvnSubClass_SysNick),'')::regclass and conrelid::regclass=lower(P.PersonEvnClass_SysNick)::regclass
		)LF on true*/
		where P.PersonEvnClass_IsState = 2 
		and (P.PersonEvnClass_id = xp_PersonRebuildState.PersonEvnClass_id or xp_PersonRebuildState.PersonEvnClass_id is null))
	loop
		PersonEvnClass_id:=ObjCur.PersonEvnClass_id;
	    p_PersonEvnClass_SysNick:=ObjCur.PersonEvnClass_SysNick; 
	    SubObj:=ObjCur.SubObj;
	    ClassCode:=ObjCur.PersonEvnClass_Code;
	    if COALESCE(Debug,0) = 1 then
	   		 RAISE  notice 'SubObj=% p_PersonEvnClass_SysNick=% p_schema_name=%',SubObj,p_PersonEvnClass_SysNick,p_schema_name;
	   	end if;
	    LinkField := COALESCE((select c_fk.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as fk 
								JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc on rc.CONSTRAINT_NAME = fk.CONSTRAINT_NAME 
								JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as pk on pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME 
								JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c_fk on c_fk.CONSTRAINT_NAME = fk.CONSTRAINT_NAME 
								JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c_pk on c_pk.CONSTRAINT_NAME = pk.CONSTRAINT_NAME and c_pk.ORDINAL_POSITION = c_fk.ORDINAL_POSITION 
								where fk.CONSTRAINT_TYPE = 'FOREIGN KEY' and pk.TABLE_NAME=lower(p_PersonEvnClass_SysNick) 
								and fk.TABLE_NAME=COALESCE(lower(SubObj),'') and fk.table_schema=lower( p_schema_name ) 
								and pk.table_schema='dbo'),'');
		--EXIT WHEN NOT FOUND;
			--set SubObj = COALESCE((select PersonEvnSubClass_SysNick from dbo.PersonEvnSubClass pesc 
			--inner join dbo.PersonEvnClass pec  on pesc.PersonEvnClass_id = pec.PersonEvnClass_id where PersonEvnClass_SysNick = PersonEvnClass_SysNick),'')
		
			--set LinkField = COALESCE((select c1.column_name from sys.foreign_key_columns kc1 
			--	inner join dbo.v_ColumnsLite c1  on kc1.parent_object_id = c1.table_id and kc1.parent_column_id = c1.column_order
			--	inner join dbo.v_ColumnsLite c2  on kc1.referenced_object_id = c2.table_id and kc1.referenced_column_id = c2.column_order
			--	where c1.schema_name = 'dbo' and c2.schema_name = schema_name
			--		and c1.table_name = PersonEvnClass_SysNick and c1.column_name<>'Server_id' and c2.table_name = SubObj),'')
		
			OuterFields := '';
		---!!!НАШЕ 24.07.2014
		--if (select PersonEvnClass_Code from PersonEvnClass  where PersonEvnClass_SysNick = PersonEvnClass_SysNick) in (1,2,3)
			--RAISE  notice 'ClassCode=% p_PersonEvnClass_SysNick=% LinkField=%',ClassCode,p_PersonEvnClass_SysNick,LinkField;
			if (ClassCode) in (1,2,3)
			THEN
				   select 
				     string_agg( distinct c1.column_name || ' = ' || p_PersonEvnClass_SysNick || '.' || 
				    	(case when c1.column_name = '' then '' || '_id' else c1.column_name end )|| ',' 
				    	||(case when c1.column_name like '%_id' then '' else  ' ' || c1.column_name || 'R = ' 
				    		|| 	(case when ''||c1.column_name = '' then '' || '_id' else 'upper(replace ('|| p_PersonEvnClass_SysNick || '.' || c1.column_name||',''ё'',''е''))' end) 
					    || ',' end) ||	' ',CRLF ),
				     string_agg( distinct  case when c1.column_name = lower(LinkField) then SubObj || '_id' else c1.column_name end  ,','||CRLF)
					--Fields = Fields || c1.column_name || ' = null,' || CRLF
					into Fields,OuterFields
					from pg_temp.columns00 c1 
					inner join pg_temp.columns00 c2  on c1.table_schema = c2.table_schema and c2.table_name in (lower('v_' || p_PersonEvnClass_SysNick) )
						and (c1.column_name = c2.column_name or c1.column_name = lower(LinkField))
					where c1.table_schema = p_schema_name 
					and c1.table_name = lower(StateTable) 
					and c1.column_name not in ('person_id', 'server_id')
					and c1.column_name not like 'pmuser%';
			else
			---!!!НАШЕ 24.07.2014
					select 
					 string_agg( distinct  c1.column_name || ' = ' ||p_PersonEvnClass_SysNick || '.' || (
					 	case when c1.column_name = lower(LinkField) then SubObj || '_id' else c1.column_name end) || ',' , CRLF),
					 string_agg( case when c1.column_name = LinkField then SubObj || '_id' else c1.column_name end , ', '|| CRLF)
					into Fields,OuterFields	 
					from pg_temp.columns00 c1 
					inner join pg_temp.columns00 c2  on c1.table_schema = c2.table_schema and c2.table_name in (lower('v_' || p_PersonEvnClass_SysNick) )
						and (c1.column_name = c2.column_name or c1.column_name = lower(LinkField))
					where c1.table_schema = p_schema_name 
					and c1.table_name = lower(StateTable) 
					and c1.column_name not in ('person_id', 'server_id')
					and c1.column_name not like 'pmuser%'
				    AND c2.table_name||'.'||c2.column_name NOT IN ('v_persondocument.klcountry_id','v_personuaddress.klcountry_id','v_personpaddress.klcountry_id');
			end if;
			Fields := '-- ' || p_PersonEvnClass_SysNick || CRLF||Fields;
			if OuterFields<>'' and p_PersonEvnClass_SysNick <> 'PersonPolis' then
				OuterFields := '-- ' || p_PersonEvnClass_SysNick || CRLF ||
					'LEFT JOIN LATERAL (select  ' || OuterFields || CRLF || 'from dbo.v_' || p_PersonEvnClass_SysNick || '   where Person_id = ps.Person_id' ||
					CRLF || 'order by ' || p_PersonEvnClass_SysNick || '_insDT desc, ' || p_PersonEvnClass_SysNick || '_TimeStamp desc limit 1) ' || p_PersonEvnClass_SysNick || ' on true ';
			end if;
		
			-- фикс для полиса, последний выданный полис главнее
			if OuterFields<>'' and p_PersonEvnClass_SysNick = 'PersonPolis'	then
				OuterFields := '-- ' || p_PersonEvnClass_SysNick || CRLF ||
					'LEFT JOIN LATERAL (select  ' || OuterFields || CRLF || 'from dbo.v_' || p_PersonEvnClass_SysNick || '  where Person_id = ps.Person_id' || 
					CRLF || 'order by ' || p_PersonEvnClass_SysNick || '_insDT desc, BDZ_id desc, ' || p_PersonEvnClass_SysNick || '_TimeStamp desc limit 1) ' ||p_PersonEvnClass_SysNick|| ' on true ';
			end if;
			--RAISE  notice 'Fields=%', Fields;
			--RAISE  notice 'OuterSqlText=%', OuterSqlText;
			SqlText:= concat(SqlText, Fields);
			OuterSqlText:= concat(OuterSqlText, OuterFields || CRLF);
	end loop;	
	--close ObjCur;

	OuterSqlText:= OuterSqlText|| '-- PersonEvnMin' || CRLF ||'LEFT JOIN LATERAL  (select Person_insDT, pmUser_insID from Person  where Person_id = ps.Person_id) PersonEvnMin on true' || CRLF;
	
	OuterSqlText:= OuterSqlText|| '-- PersonEvnMax' || CRLF || 'LEFT JOIN LATERAL  (select  Server_id, PersonEvn_id, PersonEvn_insDT, pmUser_insID from v_PersonEvn  where Person_id = ps.Person_id ' || CRLF ||
		'order by PersonEvn_insDT desc, PersonEvn_TimeStamp desc, personevn_id desc  limit 1) PersonEvnMax on true' || CRLF;
	
	SqlText:= SqlText|| 'Server_id = PersonEvnMax.Server_id,' || CRLF ||
		'PersonEvn_id = PersonEvnMax.PersonEvn_id,' || CRLF ||
		'pmUser_insID = PersonEvnMin.pmUser_insID,' || CRLF ||
		'pmUser_updID = PersonEvnMax.pmUser_insID,' || CRLF ||
		StateTable || '_insDT = PersonEvnMin.Person_insDT,' || CRLF ||
		StateTable || '_updDT = PersonEvnMax.PersonEvn_insDT' || CRLF ||
		'from ' || StateTable || ' ps' || CRLF || 
		OuterSqlText|| 
		'where ' || PersonFilter || ' and '||StateTable||'.person_id=ps.person_id' ;
	
	if COALESCE(Debug,0) = 1 then
		RAISE  notice '%', SqlText;
	else 
		execute(SqlText);
	end if;
	if exists(select 1 from pg_temp.columns00  where table_name = lower(StateTable) and table_schema = lower(p_schema_name) and column_name = 'lpu_id')
	then
		-- пробиваем Lpu_id по основному активному прикреплению
		SqlText:= CRLF || 
		'update ' || StateTable || '  set' || CRLF || 
		'Lpu_id = PersonCard.Lpu_id' || CRLF || 
		'from ' || StateTable || ' ps ' || CRLF ||
		'LEFT JOIN LATERAL (select ' || CRLF ||
		'PersonCard.Lpu_id' || CRLF ||
		'from v_PersonCard PersonCard ' || CRLF ||
		'where PersonCard.Person_id = ps.Person_id' || CRLF ||
		'and PersonCard.LpuAttachType_id = 1 and PersonCard.PersonCard_endDate is null' || CRLF ||
		'order by PersonCard_begDate desc' || CRLF ||
		'limit 1) PersonCard on true' || CRLF ||
		'where ' || PersonFilter|| ' and '||StateTable||'.person_id=ps.person_id';
	
		if COALESCE(Debug,0) = 1 then
			RAISE  notice '%', SqlText;
		else 
			execute(SqlText);
		end if;
	
	end	if;
end;
$function$
