/*
изменение объектов пятого уровня классификации (update klstreet)
улицы городов, населенных пунктов, гаражно-строительных кооперативы, ...
*/

/* #count
select count(1)
		 from tmp.gpp_kladr_street tt
		   inner join klstreet k on k.kladr_code = tt.code
		   inner join dbo.klsocr ks on k.klsocr_id=ks.klsocr_id
		   inner join dbo.klsocr ks1 on tt.socr=ks1.klsocr_nick and ks1.klarealevel_id=5
		where 
		((upper(tt.name) != upper(k.klstreet_name))
			 or (tt.ocatd != k.kladr_ocatd)
			 or (tt.index != k.kladr_index)
				 or (tt.gninmb != k.kladr_gninmb)
				   or (tt.uno != k.kladr_uno) or upper(ks.klsocr_nick)!=upper(tt.socr)
		) 
*/

do $$
declare
    adr_klad record;
		mCurs refcursor;
    p_error_code varchar = null;
    p_error_message varchar = null;
begin	
	raise notice 'Обновим данные по улицам % ', ' klstreet';
	/*
	while (
			select count(1)
					 from tmp.gpp_kladr_street tt
						   inner join klstreet k on k.kladr_code = tt.code and k.kladr_actual=0 
						   inner join dbo.klsocr ks on k.klsocr_id=ks.klsocr_id and ks.klcountry_id=643
						   inner join dbo.klsocr ks1 on upper(tt.socr)=upper(ks1.klsocr_nick) and ks1.klarealevel_id=5 and ks1.klcountry_id=643
					where 
					((upper(trim(tt.name)) != upper(trim(k.klstreet_name)))
						 or (tt.ocatd != k.kladr_ocatd)
						 --or (tt.index != k.kladr_index)
							 or (tt.gninmb != k.kladr_gninmb)
							   or (tt.uno != k.kladr_uno) or upper(ks.klsocr_nick)!=upper(tt.socr)
					) 
			)>0
		LOOP
		*/	
			for adr_klad in		
				select 
				   k.klstreet_id, k.klarea_id, ks1.klsocr_id,
				   trim(upper(name)) as klstreet_name,
				   trim(upper(socr)) as klsocr_nick,
				   code::varchar(20) as klstreet_code,
				   case when trim(index::varchar(10))='' then null 
								else k.kladr_index
								end as klstreet_index,
					 gninmb::varchar(20) as klstreet_gninmb,
					 case when trim(uno::varchar(10))='' then null 
								else trim(uno::varchar(10))
								end as klstreet_uno,
					 ocatd::varchar(20) as klstreet_ocatd
				from tmp.gpp_kladr_street tt
						   inner join klstreet k on k.kladr_code = tt.code and k.kladr_actual=0 
						   inner join dbo.klsocr ks on k.klsocr_id=ks.klsocr_id and ks.klcountry_id=643
						   inner join dbo.klsocr ks1 on upper(tt.socr)=upper(ks1.klsocr_nick) and ks1.klarealevel_id=5 and ks1.klcountry_id=643
				where 
						((upper(trim(tt.name)) != upper(trim(k.klstreet_name)))
							 or (tt.ocatd != k.kladr_ocatd)
							 or (tt.index != k.kladr_index)
								 or (tt.gninmb != k.kladr_gninmb)
								   or (tt.uno != k.kladr_uno) or upper(ks.klsocr_nick)!=upper(tt.socr)
						) --limit 10000
				LOOP
					select fnc.error_code, fnc.error_message 
					into p_error_code, p_error_message
					from dbo.xp_klstreet_upd(
						klstreet_id := adr_klad.klstreet_id,
						klarea_id := adr_klad.klarea_id,
						klsocr_id := adr_klad.klsocr_id,
						klstreet_name := adr_klad.klstreet_name,
						klsocr_nick := adr_klad.klsocr_nick,
						klstreet_code := adr_klad.klstreet_code,
						klstreet_index := adr_klad.klstreet_index,
						klstreet_gninmb := adr_klad.klstreet_gninmb,
						klstreet_uno := adr_klad.klstreet_uno,
						klstreet_ocatd := adr_klad.klstreet_ocatd
					)fnc;					
				
			--	end loop;
			--raise notice 'Обновлено 10000 % ', ' klstreet';
			--commit;
			--exit;
		end LOOP;
		raise notice 'Обновлено % ', ' klstreet';
end $$
