-- ==========================================
-- 작업일자: YYYY-MM-DD [쿼리 작성 및 실행 날짜를 기록합니다.]
-- 작 성 자: [실행자 이름을 기록합니다.]
-- 작업목적: [주기적으로 실행하여 아이체크로 이상데이타 확인하며 필요시 실무자와 확인 후 수정을 별도 진행합니다.]
-- 요청자/티켓번호: [별도 요청은 없으며, 이상자료에 대한 분석에 대한 조치는 실무자와 협의 후 수정하거나 수정하지 않고 확인만 진행합니다.]
-- 데이타베이스 : MS-SQL
-- ==========================================

-- 실행예산의 소요예산과 구매청구서 금액 + 직접비 금액이 다른 것 조회하기


select
	a.*
	, a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0) x_amt
	, (b.soyo_amt+b.input_amt)
	, a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0) - (b.soyo_amt+b.input_amt)
from
	HPOIMS.dbo.ys_pbudget a
join
(
	select 
		*
	from
	(
		select
			a.pjt_no, a.main, a.sub, a.bud_seq
			, sum(a.bud_amt) bud_amt
			, sum(a.bud_x_amt) bud_x_amt
			, sum(a.bud_e_amt) bud_e_amt
			, sum(a.soyo_amt) soyo_amt
			, sum(a.input_amt) input_amt
		from
		(
			SELECT 
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ISNULL(a.closing_yn,'N')
				, bud_amt = round(ISNULL(a.amt*a.base_rate,0),0)
				, bud_x_amt = round(ISNULL(a.x_amt*a.base_rate,0),0) 
				, bud_e_amt = round(ISNULL(a.e_amt*a.base_rate,0),0)
				, soyo_amt = 0
				, input_amt = 0      
			FROM 
				HPOIMS.dbo.ys_pbudget a
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
-- 				, soyo_amt = SUM(ISNULL(a.amt,0)*ISNULL(b.base_rate,1) + CASE WHEN b.fob IS NULL THEN 0 ELSE FLOOR(ISNULL(a.amt,0)*ISNULL(b.fob,1)*b.base_rate) END)
				, soyo_amt = SUM(ISNULL(a.w_amt,0))
				, input_amt = 0
			FROM 
				HPOIMS.dbo.gm_por_detail a
			left outer join
				HPOIMS.dbo.ys_project_rate b
			on
				a.pjt_no = b.pjt_no and a.curr = b.curr
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
				, soyo_amt = 0
-- 				, input_amt = SUM(ISNULL(a.amt,0)*ISNULL(b.base_rate,1) + CASE WHEN b.fob IS NULL THEN 0 ELSE FLOOR(ISNULL(a.amt,0)*ISNULL(b.fob,1)*b.base_rate) END)
				, input_amt = SUM(ISNULL(a.amt,0))
			FROM 
				HPOIMS.dbo.ys_direct_cost a
			left outer join
				HPOIMS.dbo.ys_project_rate b
			on
				a.pjt_no = b.pjt_no and a.curr = b.curr
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
		) a
		GROUP BY 
			a.pjt_no, a.main, a.sub, a.bud_seq
	) a
-- 	where
-- 		(a.bud_x_amt*a.base_rate) <> (a.soyo_amt+a.input_amt)
		
) b
on
	a.pjt_no = b.pjt_no
	and a.main = b.main
	and a.sub = b.sub
	and a.bud_seq = b.bud_seq
 	and a.pjt_no like '%' 
 	and a.main <> 'AB00'
  	and a.curr = 'WON'
	and ROUND(isnull(a.x_amt,0)*a.base_rate,0) <> (b.soyo_amt+b.input_amt)

-- 	and a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0)
-- 		<> 
-- 		(b.soyo_amt+b.input_amt)
--	and abs((a.x_amt*a.base_rate) - (b.soyo_amt+b.input_amt)) > 1 
-- 	and a.item_name = 'ManDay 지원비'

-- 외자인 경우 체크
select
	a.*
	, round(a.x_amt*a.base_rate,-1) + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0) x_amt
	, (b.soyo_amt+b.input_amt)
	, a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0) - (b.soyo_amt+b.input_amt)
from
	HPOIMS.dbo.ys_pbudget a
join
(
	select 
		*
	from
	(
		select
			a.pjt_no, a.main, a.sub, a.bud_seq
			, sum(a.bud_amt) bud_amt
			, sum(a.bud_x_amt) bud_x_amt
			, sum(a.bud_e_amt) bud_e_amt
			, sum(a.soyo_amt) soyo_amt
			, sum(a.input_amt) input_amt
		from
		(
			SELECT 
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ISNULL(a.closing_yn,'N')
				, bud_amt = round(ISNULL(a.amt*a.base_rate,0),0)
				, bud_x_amt = round(ISNULL(a.x_amt*a.base_rate,0),0) 
				, bud_e_amt = round(ISNULL(a.e_amt*a.base_rate,0),0)
				, soyo_amt = 0
				, input_amt = 0      
			FROM 
				HPOIMS.dbo.ys_pbudget a
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
-- 				, soyo_amt = SUM(ISNULL(a.amt,0)*ISNULL(b.base_rate,1) + CASE WHEN b.fob IS NULL THEN 0 ELSE FLOOR(ISNULL(a.amt,0)*ISNULL(b.fob,1)*b.base_rate) END)
				, soyo_amt = SUM(ISNULL(a.w_amt,0))
				, input_amt = 0
			FROM 
				HPOIMS.dbo.gm_por_detail a
			left outer join
				HPOIMS.dbo.ys_project_rate b
			on
				a.pjt_no = b.pjt_no and a.curr = b.curr
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
				, soyo_amt = 0
-- 				, input_amt = SUM(ISNULL(a.amt,0)*ISNULL(b.base_rate,1) + CASE WHEN b.fob IS NULL THEN 0 ELSE FLOOR(ISNULL(a.amt,0)*ISNULL(b.fob,1)*b.base_rate) END)
				, input_amt = SUM(ISNULL(a.amt,0))
			FROM 
				HPOIMS.dbo.ys_direct_cost a
			left outer join
				HPOIMS.dbo.ys_project_rate b
			on
				a.pjt_no = b.pjt_no and a.curr = b.curr
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
		) a
		GROUP BY 
			a.pjt_no, a.main, a.sub, a.bud_seq
	) a
-- 	where
-- 		(a.bud_x_amt*a.base_rate) <> (a.soyo_amt+a.input_amt)
		
) b
on
	a.pjt_no = b.pjt_no
	and a.main = b.main
	and a.sub = b.sub
	and a.bud_seq = b.bud_seq
 	and a.pjt_no like 'V%' 
 	and a.main <> 'AB00'
--  	and a.curr = 'WON'
-- 	and a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0)
-- 		<> 
-- 		(b.soyo_amt+b.input_amt)
	and  round(a.x_amt*a.base_rate, -1) + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0) <> (b.soyo_amt+b.input_amt)
	and abs(a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0) - (b.soyo_amt+b.input_amt)) > 1 
-- 	and a.item_name = 'ManDay 지원비'

/*

V120T006	9C  	011	1
V120T507	9D  	05U	1
V120T602	AB27	041	1
V120T703	AB29	AC1	1
V120T709	AB25	E11	1
V120T709	AB27	011	1
V271FD12	9D  	06C	1
V665MI16	9T  	071	1
V665MI16	AB29	AB1	1
V672MI16	9T  	071	1



V120T902	9Y  	010	2	ABSORBER SYSTEM
V120T902	9Y  	021	2	GAS COOLER AND REHEATER
V120T902	9Y  	021	6	GAS COOLER AND REHEATER(외자)
V160T707	9T  	022	3	그레이트
V160T707	AB27	021	2	DWG. FEE
V160T802	9N  	000	2	BOILER EQUIPMENT
V160T915	9S  	018	2	OTHERS
V170T707	AB25	351	2	하상여과설비 본체소모품




-- 실행예산 소요예산 = 구매청구서 금액 + 직접비 금액 비교하여 다른 것 업데이트 하기
begin tran

update
	HPOIMS.dbo.ys_pbudget
set
	x_amt = (b.soyo_amt+b.input_amt)
from
	HPOIMS.dbo.ys_pbudget a
join
(
	select 
		*
	from
	(
		select
			a.pjt_no, a.main, a.sub, a.bud_seq
			, sum(a.bud_amt) bud_amt
			, sum(a.bud_x_amt) bud_x_amt
			, sum(a.bud_e_amt) bud_e_amt
			, sum(a.soyo_amt) soyo_amt
			, sum(a.input_amt	) input_amt
		from
		(
			SELECT 
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ISNULL(a.closing_yn,'N')
				, bud_amt = ISNULL(a.amt,0)
				, bud_x_amt = ISNULL(a.x_amt,0)
				, bud_e_amt = ISNULL(a.e_amt,0)
				, soyo_amt = 0
				, input_amt = 0      
			FROM 
				HPOIMS.dbo.ys_pbudget a
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
				, soyo_amt = SUM(ISNULL(a.w_amt,0))
				, input_amt = 0
			FROM 
				HPOIMS.dbo.gm_por_detail a
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
				, soyo_amt = 0
				, input_amt = SUM(ISNULL(a.amt,0))
			FROM 
				HPOIMS.dbo.ys_direct_cost a
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
		) a
		GROUP BY 
			a.pjt_no, a.main, a.sub, a.bud_seq
	) a
	where
		a.bud_x_amt <> (a.soyo_amt+a.input_amt)
) b
on
	a.pjt_no = b.pjt_no
	and a.main = b.main
	and a.sub = b.sub
	and a.bud_seq = b.bud_seq
 	and a.pjt_no like '%' 
 	and a.main <> 'AB00'
	and a.curr = 'WON'
	and ROUND(isnull(a.x_amt,0)*a.base_rate,0) <> (b.soyo_amt+b.input_amt)

commit tran

rollback tran


V120T806	9B  	041	1	511445	481283.37	0	89999990	0
V120T806	9B  	041	2	534820	534759.36	0	100000000	0
V170T707	AB25	350	1	686467320	530807119.00	0	0	520840395
V170T804	AB25	350	3	194000000	64880860.00	0	28500000	13177012


begin tran

update
	HPOIMS.dbo.ys_pbudget
set
	x_amt = (b.soyo_amt+b.input_amt)
from
	HPOIMS.dbo.ys_pbudget a
join
(
	select 
		*
	from
	(
		select
			a.pjt_no, a.main, a.sub, a.bud_seq
			, sum(a.bud_amt) bud_amt
			, sum(a.bud_x_amt) bud_x_amt
			, sum(a.bud_e_amt) bud_e_amt
			, sum(a.soyo_amt) soyo_amt
			, sum(a.input_amt	) input_amt
		from
		(
			SELECT 
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ISNULL(a.closing_yn,'N')
				, bud_amt = ISNULL(a.amt,0)
				, bud_x_amt = ISNULL(a.x_amt,0)
				, bud_e_amt = ISNULL(a.e_amt,0)
				, soyo_amt = 0
				, input_amt = 0      
			FROM 
				HPOIMS.dbo.ys_pbudget a
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
				, soyo_amt = SUM(ISNULL(a.w_amt,0))
				, input_amt = 0
			FROM 
				HPOIMS.dbo.gm_por_detail a
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
	
			UNION ALL
			SELECT
				  pjt_no = a.pjt_no
				, main = a.main
				, sub = a.sub
				, bud_seq = a.bud_seq
				, closing_yn = ''
				, bud_amt = 0
				, bud_x_amt = 0
				, bud_e_amt = 0
				, soyo_amt = 0
				, input_amt = SUM(ISNULL(a.amt,0))
			FROM 
				HPOIMS.dbo.ys_direct_cost a
			GROUP BY 
				a.pjt_no, a.main, a.sub, a.bud_seq
		) a
		GROUP BY 
			a.pjt_no, a.main, a.sub, a.bud_seq
	) a
-- 	where
-- 		a.bud_x_amt <> (a.soyo_amt+a.input_amt)
) b
on

	a.pjt_no = b.pjt_no
	and a.main = b.main
	and a.sub = b.sub
	and a.bud_seq = b.bud_seq
 	and a.pjt_no like '%' 
 	and a.main <> 'AB00'
	and a.curr = 'WON'
	and ROUND(isnull(a.x_amt,0)*a.base_rate,0) <> (b.soyo_amt+b.input_amt)

--  	and a.pjt_no like 'B%' 
	and ROUND(a.x_amt*a.base_rate,0) <> (b.soyo_amt+b.input_amt)

-- 	and a.x_amt*a.base_rate + round(((a.x_amt*a.base_rate) * isnull((select fob from HPOIMS.dbo.ys_project_rate where pjt_no = a.pjt_no and curr = a.curr),0)),0)
-- 		<> 
-- 		(b.soyo_amt+b.input_amt)

rollback tran
*/



-- 경상비 통제금액과 부서예산항목의 통제금액이 다른 것들 조회하기
select
	*
from
(
	select 
		a.dept_cd, a.year, a.month, a.main_cd, a.sub_cd, isnull(a.budget_use,0) budget_use, isnull(b.amt,0) amt
	from
		HPOIMS.dbo.ks_budget a
	left outer join
	(
		select 
			dept_cd, year, month, main_cd, sub_cd, sum(amt) amt
		from 
			HPOIMS.dbo.ks_indirect_cost
		where	
			ctr_yn = 'Y'
		group by
			dept_cd, year, month, main_cd, sub_cd
	) b
	on
		a.dept_cd = b.dept_cd
		and a.year = b.year
		and a.month = b.month
		and a.main_cd = b.main_cd
		and a.sub_cd = b.sub_cd
) a
where
	a.budget_use <> a.amt
	and a.year >= '2024'


/*
-- 경상비 통제금액과 부서예산항목의 통제금액이 다른 것들 수정하기 
update
	HPOIMS.dbo.ks_budget
set
	budget_use = b.amt
from
	HPOIMS.dbo.ks_budget a
join
(
	select 
		a.*
	from
	(
		select 
			a.dept_cd, a.year, a.month, a.main_cd, a.sub_cd, isnull(a.budget_use,0) budget_use , isnull(b.amt,0) amt
		from
			HPOIMS.dbo.ks_budget a
		left outer join
		(
			select 
				dept_cd, year, month, main_cd, sub_cd, sum(amt) amt
			from 
				HPOIMS.dbo.ks_indirect_cost
			where	
				ctr_yn = 'Y'
			group by
				dept_cd, year, month, main_cd, sub_cd
		) b
		on
			a.dept_cd = b.dept_cd
			and a.year = b.year
			and a.month = b.month
			and a.main_cd = b.main_cd
			and a.sub_cd = b.sub_cd
	) a
	where
		a.budget_use <> a.amt
) b
on
	a.dept_cd = b.dept_cd
	and a.year = b.year
	and a.month = b.month
	and a.main_cd = b.main_cd
	and a.sub_cd = b.sub_cd
	and a.year >= '2024'

*/

-- 직접비 예산에 있으나 전표와 일치 하지 않는 것
select 
	a.*, b.*
from 
	HPOIMS.dbo.ys_direct_cost a
left outer join
	ACC.dbo.t23b10 b
on
	b.slpdate>'20220101' and a.pjt_no = b.ys_pjtno and a.main = b.ys_main and a.sub = b.ys_sub and a.bud_seq = b.ys_bud_seq and a.ctr_seq = b.ys_ctr_seq 
where
	a.bill_date > '20220101'
  	and a.ctr_yn = 'N'
	and a.direct_desc not like '%국민연금%'
	and a.direct_desc not like '%고용보험%'
	and a.direct_desc not like '%건강보험%'
	and a.direct_desc not like '%급여%'
	and a.direct_desc not like '%중식대%'
	and b.area is null
order by
	a.pjt_no, a.ctr_seq


/*

select 
	a.pjt_no, a.main, a.sub, a.bud_seq, isnull(a.x_amt,0)  , b.amt
from 
	HPOIMS.dbo.ys_pbudget a
join
(
	select a.pjt_no, a.main, a.sub, a.bud_seq, sum(amt) amt from HPOIMS.dbo.ys_direct_cost a where a.pjt_no like 'H%' or a.pjt_no like 'X%' group by a.pjt_no, a.main, a.sub, a.bud_seq
)b
on
	a.pjt_no = b.pjt_no and a.main = b.main and a.sub = b.sub and a.bud_seq = b.bud_seq
where 
	(a.pjt_no like 'H%' or a.pjt_no like 'X%')
	and isnull(a.x_amt,0) <> b.amt


update
	a
set
	x_amt = b.amt
from 
	HPOIMS.dbo.ys_pbudget a
join
(
	select a.pjt_no, a.main, a.sub, a.bud_seq, sum(amt) amt from HPOIMS.dbo.ys_direct_cost a where a.pjt_no like 'H%' or a.pjt_no like 'X%' group by a.pjt_no, a.main, a.sub, a.bud_seq
)b
on
	a.pjt_no = b.pjt_no and a.main = b.main and a.sub = b.sub and a.bud_seq = b.bud_seq
where 
	(a.pjt_no like 'H%' or a.pjt_no like 'X%')
	and isnull(a.x_amt,0) <> b.amt

*/

-- 경상비 예산과 전표가 일치 하지 않는 것
select 
	a.*, b.*
from 
	HPOIMS.dbo.ks_indirect_cost a
left outer join
	ACC.dbo.t23b10 b
on
	b.slpdate>'20190101' and a.dept_cd = b.ys_dept_cd and a.main_cd = b.ys_main and a.sub_cd = b.ys_sub and a.ctr_seq = b.ys_ctr_seq 
where
	a.billdate > '20190101'
  	and a.ctr_yn = 'N'
	and a.indirect_desc not like '%국민연금%'
	and a.indirect_desc not like '%고용보험%'
	and a.indirect_desc not like '%건강보험%'
	and a.indirect_desc not like '%급여%'
	and a.indirect_desc not like '%중식대%'
	and b.area is null
order by
	a.dept_cd, a.ctr_seq


-- >> 경상비에서 부서코드 없이 입력되어 있는 자료
select '경상비에서 부서코드 없이', * from HPOIMS.dbo.ks_indirect_cost where dept_cd = '' and year > '2019' 


select 
	'수주가와 수주상세금액 차이', a.pjt_no, a.suju_amt, b.w_amt
from 
	HPOIMS.dbo.ys_project a
join
(
	select 
		pjt_no, sum(w_amt) w_amt
	from 
		HPOIMS.dbo.ys_project_rev
	where 
		pjt_no like 'V%' -- and approval_yn = 'Y'
	group by
		pjt_no
) b
on
	a.pjt_no = b.pjt_no
where 
	a.pjt_no like 'V%'
	and a.suju_amt <> b.w_amt

/*

update
	a
set
	suju_amt = b.w_amt
from 
	HPOIMS.dbo.ys_project a
join
(
	select 
		pjt_no, sum(w_amt) w_amt
	from 
		HPOIMS.dbo.ys_project_rev
	where 
		pjt_no like 'V%' -- and approval_yn = 'Y'
	group by
		pjt_no
) b
on
	a.pjt_no = b.pjt_no
where 
	a.pjt_no like 'V%'
	and a.suju_amt <> b.w_amt

*/


-- 실행예산과 PM추가예상 금액 다른 것
select
	'실행예산과 PM추가예상 금액 다른 것', a.*, c.*
from
(
	select 
		a.pjt_no, a.main, a.sub, a.bud_seq, sum(a.amt) amt, sum(a.amt*a.base_rate + a.amt*isnull(b.fob,0)) w_amt
	from 	
		HPOIMS.dbo.ys_ebudget a
	left outer join
		HPOIMS.dbo.ys_project_rate b
	on
		a.pjt_no = b.pjt_no and a.curr = b.curr
	group by
		a.pjt_no, a.main, a.sub, a.bud_seq
) a
right outer join
	HPOIMS.dbo.ys_pbudget c
on
	a.pjt_no = c.pjt_no and a.main = c.main and a.sub = c.sub and a.bud_seq = c.bud_seq
where
	isnull(a.amt,0) <> isnull(c.e_amt,0)
/*
update
	a
set
	e_amt = isnull(b.amt,0)
from
	HPOIMS.dbo.ys_pbudget a
left outer join
(
	select 
		a.pjt_no, a.main, a.sub, a.bud_seq, sum(a.amt) amt, sum(a.amt*a.base_rate + a.amt*isnull(b.fob,0)) w_amt
	from 	
		HPOIMS.dbo.ys_ebudget a
	left outer join
		HPOIMS.dbo.ys_project_rate b
	on
		a.pjt_no = b.pjt_no and a.curr = b.curr
	group by
		a.pjt_no, a.main, a.sub, a.bud_seq
) b
on
	a.pjt_no = b.pjt_no and a.main = b.main and a.sub = b.sub and a.bud_seq = b.bud_seq
where 
	isnull(a.e_amt,0) <> isnull(b.amt,0)
*/

-- 구매 청구수량과 구매 발주 수량이 다른 것
select
	'구매 청구수량과 구매 발주 수량이 다른 것', a.por_no, a.por_seq, a.pjt_no, a.main, a.sub, a.bud_seq, a.item_name, a.qty, a.w_amt, b.qty, b.w_amt, (select emp from HPOIMS.dbo.gm_poim where poim = c.poim) emp_id
from
	HPOIMS.dbo.gm_por_detail a
join
(
	select
		por_no, por_seq, sum(qty) qty, sum(w_amt) w_amt
	from
		HPOIMS.dbo.gm_po_detail 
	where 
		isnull(u_stat, '') <> 'D'
	group by
		por_no, por_seq
) b
on
	a.por_no = b.por_no 
	and a.por_seq = b.por_seq
join
(
	select distinct b.por_no, b.por_seq, a.poim from HPOIMS.dbo.gm_po a join HPOIMS.dbo.gm_po_detail b on a.po_no = b.po_no
) c
on
	a.por_no = c.por_no 
	and a.por_seq = c.por_seq
where
	a.pjt_no like 'V%'
	and a.pjt_no in (select pjt_no from HPOIMS.dbo.ys_project where closing_yn = 'N' and pjt_no like 'V%')
	and a.qty < b.qty
-- and a.qty <> b.qty
order by
	a.por_no

-- 실행예산의 추가변경금액과 추가변경예산의 금액이 다른 것
select
	'추가변경예산 차이발생', b.*, a.item_name, a.amt, a.m_amt, a.c_amt, a.curr, a.base_rate
from
	HPOIMS.dbo.ys_pbudget a
left outer join
(
	select 
		a.pjt_no, a.main, a.sub, a.bud_seq, sum(a.amt) amt
	from 	
		HPOIMS.dbo.ys_cbudget a
	group by
		a.pjt_no, a.main, a.sub, a.bud_seq
) b
on
	a.pjt_no = b.pjt_no and a.main = b.main and a.sub = b.sub and a.bud_seq = b.bud_seq
where 
	isnull(a.c_amt,0) <> isnull(b.amt,0)
	and a.pjt_no like 'V%'
--	and a.curr = 'WON'
/*
begin tran

update
	a
set
	c_amt = isnull(b.amt,0)
from
	HPOIMS.dbo.ys_pbudget a
left outer join
(
	select 
		a.pjt_no, a.main, a.sub, a.bud_seq, sum(a.amt) amt
	from 	
		HPOIMS.dbo.ys_cbudget a
	group by
		a.pjt_no, a.main, a.sub, a.bud_seq
) b
on
	a.pjt_no = b.pjt_no and a.main = b.main and a.sub = b.sub and a.bud_seq = b.bud_seq
where 
	isnull(a.c_amt,0) <> isnull(b.amt,0)
	and a.pjt_no like 'V%'
--	and a.curr = 'WON'

commit
*/


-- 실행예산에서 실행금액 <> 초기편성예산 + 추가변경예산
select
	 '실행금액 <> 초기편성예산 + 추가변경예산', a.*
from
	HPOIMS.dbo.ys_pbudget a
where 
	isnull(a.m_amt,0) + isnull(a.c_amt,0) <> isnull(a.amt,0)
	and a.pjt_no like 'V%'

/*
update
	 a
set
	amt = isnull(a.m_amt,0) + isnull(a.c_amt,0)
from
	HPOIMS.dbo.ys_pbudget a
where 
	isnull(a.m_amt,0) + isnull(a.c_amt,0) <> isnull(a.amt,0)
	and a.pjt_no like 'V%'
*/


-- 실행금액을 초과한 소요예산
select
	 '실행금액을 초과한 소요예산' 
	, a.pjt_no, pjt_name = (select pjt_name from HPOIMS.dbo.ys_project where a.pjt_no = pjt_no)
	, a.main, a.sub, a.bud_seq, a.item_name, a.amt, a.x_amt, a.curr, a.base_rate
from
	HPOIMS.dbo.ys_pbudget a 
join
	HPOIMS.dbo.ys_project b
on
	a.pjt_no = b.pjt_no
	and b.closing_yn = 'N'
where 
	a.pjt_no like 'V%'
	and isnull(abs(a.amt),0) < isnull(abs(a.x_amt),0)
	and a.item_name not like '%원가안분%'

/*


*/

-- 구매발주서에 프로젝트가 없는 것
select '구매발주서에 프로젝트가 없는 것', * from HPOIMS.dbo.gm_po where pjt_no = ''


-- 미지급금(공익) 체크하기
select 
	'미지급금(공익)' gubun, a.apvdate, a.apvnum, a.apvseq, sum(a.amt) jigub_amt, max(b.apvcramt), (max(b.apvcramt) - sum(a.amt))
from 
	ACC.dbo.t23a11 a 
join
	ACC.dbo.t23a10 b
on
	b.area = '9' and a.apvdate = b.apvdate and a.apvnum = b.apvnum and a.apvseq = b.apvseq
group by 
	a.apvdate, a.apvnum, a.apvseq
having
	(max(b.apvcramt) - sum(a.amt)) < 0
/*
select * from ACC.dbo.t23a11 where apvdate = '20180101' and apvnum = '000001' and apvseq = '000002'
*/

-- 매출부가세 검토하기
select 
	'매출부가세' gubun, sum(apvcramt) - sum(apvdramt) - ISNULL((select item31 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)), 0)
	, sum(apvcramt) - sum(apvdramt)
from 
	ACC.dbo.t23a10 
where 
	area = '9' and apvdate >= '20220101' and acntcode like '21016%' and magam_yn = 'Y'						
having 
	sum(apvcramt) - sum(apvdramt) - (select item31 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)) <> 0

-- 매입부가세 검토하기
select 
	'매입부가세' gubun, sum(apvdramt) - sum(apvcramt)  - ISNULL((select item32 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)), 0)
	, sum(apvdramt) - sum(apvcramt)
from 
	ACC.dbo.t23a10 
where 
	area = '9' and apvdate >= '20220101' and acntcode like '11311%' and magam_yn = 'Y'
having 
	sum(apvdramt) - sum(apvcramt)  - (select item32 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)) <> 0

-- 매출 검토하기
--select 
--	'매출' gubun, isnull(sum(billamt),0) - ISNULL((select item26 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)), 0)
--from 
--	ACC.dbo.t23e10 where billdate >= '20190101' and approve_yn = 'O'  and gbn in ('1', '2') and magam_yn = 'Y' 	-- 공사기성, 위탁기성만 포함(원가안분, 수주분담, 기타 미수금은 미포함 한다.)
--having 
--	isnull(sum(billamt),0) - (select item26 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)) <> 0

-- 수금 검토하기
--select 
--	'수금' gubun, isnull(sum(a.billamt),0)  - ISNULL((select item27 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)), 0)
--from 
--	ACC.dbo.t23e13 a 
--join
--	ACC.dbo.t23e10 b
--on
--	a.billdate = b.billdate
--	and a.seq = b.seq
--where 
--	a.collectdate >= '20190101'
--	and a.billdate >= '20190101'
--	and b.gbn in ('1', '2') -- 공사기성, 위탁기성만 포함(원가안분, 수주분담, 기타 미수금은 미포함 한다.)
--having 
--	isnull(sum(a.billamt),0)  - (select item27 from ACC.dbo.t23a13 where magam_dt = convert(char(8), getdate(), 112)) <> 0


-- 수주통보서 최종납기일 없는 것
select 
	'수주통보서 최종납기일 없는 것' gbn, a.pjt_no, a.delv_date, b.delv_date 
from 
	HPOIMS.dbo.ys_project a 
join 
	(
		select 
			pjt_no, delv_date 
		from 
			HPOIMS.dbo.ys_project_rev 
		where pjt_no+cast(rev_no as varchar(10)) in (select pjt_no + cast(max(rev_no) as varchar(10)) from HPOIMS.dbo.ys_project_rev where approval_yn = 'Y' group by pjt_no)
	) b 
on 
	a.pjt_no = b.pjt_no 
where 
	a.delv_date = '1900-01-01' 


-- 인트라넷 결재상태와 미소 결재상태 비교하기

-- 인트라넷 결재상태 확인해 볼 것
SELECT * FROM OPENQUERY ([MYSQL], 'SELECT DocSN, FORMNUM, RT_SanctionState FROM sanctiondoc_tbl WHERE formnum like ''HLF-2%'' and RT_SanctionState = '''' ') mysql 

SELECT * FROM OPENQUERY ([MYSQL], 'SELECT * FROM sanctiondoc_tbl WHERE DocSN LIKE ''2026-%'' and RT_SanctionState LIKE ''%FINISH%'' AND PG_Date IS NULL AND FormNum LIKE ''HLF-2%'' ') mysql 

SELECT * FROM OPENQUERY ([MYSQL], 'SELECT DocSN, FORMNUM, RT_SanctionState FROM sanctiondoc_tbl WHERE formnum like ''HLF-1%'' and rg_date > ''2019-01-01''and RT_SanctionState = '''' ') mysql 

SELECT * FROM OPENQUERY ([MYSQL], 'SELECT DocSN, FORMNUM, RT_SanctionState FROM sanctiondoc_tbl WHERE LENGTH(DocSN) < 13 ') mysql 

-- SELECT * FROM OPENQUERY ([MYSQL], 'SELECT DocSN, FORMNUM, RT_SanctionState FROM sanctiondoc_tbl WHERE formnum like ''HLF-%'' and RT_SanctionState = '''' ') mysql 


-- 매출 지출 계획 관리 지워졌을 경우 복원하기
-- insert
-- 	HPOIMS.dbo.ys_moneyplan_day
-- (
-- 	plandate, seq, pjt_no, venname, content, amt1, v_amt1, amt2, v_amt2, amt3, v_amt3, payhow, bigo, input_id, input_dt, update_id, update_dt
-- )
-- select 
-- 	plandate, (select max(seq)+1 from HPOIMS.dbo.ys_moneyplan_day where plandate = HPOIMS.dbo.ys_moneyplan_h.plandate)+row_number() over (order by plandate) seq, 
-- 	pjt_no, venname, content, amt1, v_amt1, amt2, v_amt2, amt3, v_amt3, payhow, bigo, input_id, input_dt, update_id, update_dt
-- from 
-- 	HPOIMS.dbo.ys_moneyplan_h 
-- where 
-- 	pjt_no = 'V171T501' and actdate = '20180626' and acttime = '14:14:41' and io_knd = 'IN'

-- POR 감액하기
--select * from HPOIMS.dbo.gm_por_detail where por_no = 'P1809014'
--begin tran
--update HPOIMS.dbo.gm_por_detail set unitp = unitp-11290000, amt = amt-11290000, w_unitp = w_unitp-11290000, w_amt = w_amt-11290000, t_amt = t_amt-11290000 where por_no = 'P1809014'
--commit tran 
--rollback tran


-- 인사정보의 이메일이 서로 다른 경우 일치시키기 
select
 a.emp_id, a.emp_nm, b.E_MAIL, a.emp_email
from
(select * from HRM.dbo.hr_insam where holoff_gb = '01' -- and isnull(rtrim(emp_email),'') = ''
) a
join
(select * from HRM.dbo.HR_PERS_MASTER where isnull(rtrim(E_MAIL),'') <> '') b
on
a.emp_id = b.EMP_NO
where 
	isnull(rtrim(a.emp_email),'') <> isnull(rtrim(b.E_MAIL),'')

--	and rtrim(a.emp_email) is null
--	and a.emp_id like 'HF%'

/*
update
a
set
 emp_email = b.E_MAIL
from
(select * from HRM.dbo.hr_insam where holoff_gb = '01' -- and isnull(rtrim(emp_email),'') = ''
) a
join
(select * from HRM.dbo.HR_PERS_MASTER where isnull(rtrim(E_MAIL),'') <> '') b
on
a.emp_id = b.EMP_NO
where
	isnull(rtrim(a.emp_email),'') <> isnull(rtrim(b.E_MAIL),'')
	and rtrim(a.emp_email) is null
	-- and a.emp_id like 'HM01775%'

begin tran
update
b
set
 E_MAIL = a.emp_email
from
(select * from HRM.dbo.hr_insam where holoff_gb = '01' -- and isnull(rtrim(emp_email),'') = ''
) a
join
(select * from HRM.dbo.HR_PERS_MASTER where isnull(rtrim(E_MAIL),'') <> '') b
on
a.emp_id = b.EMP_NO
where
	isnull(rtrim(a.emp_email),'') <> isnull(rtrim(b.E_MAIL),'')
	and a.emp_id <> 'HM00550'
commit
*/


-- 진행중인 프로젝트 중 PM 및 현장소장 미지정 프로젝트 
select a.pjt_no, a.pjt_name, max(a.pm) pm, max(a.현장소장) 현장소장, max(a.cost_dept) 원가부서
from 
(
select 
	distinct a.pjt_no, a.pjt_name
	, (case when b.mbr_gb = 'A' then b.pjt_mbr else null end) as pm
	, (case when b.mbr_gb = 'I' then b.pjt_mbr else null end) as 현장소장
	, cost_dept
from 
	HPOIMS.dbo.ys_project a 
left outer join 
(SELECT * FROM HPOIMS.dbo.pm_member WHERE pjt_no in (select pjt_no from HPOIMS.dbo.ys_project where pjt_no like 'V%' and closing_yn = 'N')) b 
on a.pjt_no = b.pjt_no
where a.pjt_no like 'V%' and a.closing_yn = 'N'
) a
group by a.pjt_no, pjt_name	-- 64 rows 
having max(a.pm) is null or max(a.현장소장) is null or max(a.cost_dept) is null -- 55 rows --> 48 rows(24.05.17)


-- 기성지불처리된 것이나 기성신청에 지불처리중인 건 체크하기 
SELECT a.*, b.slpdate, b.slpnum, b.acc_gbn FROM HPOIMS.dbo.gs_require a left outer join HPOIMS.dbo.gs_payment b on a.cont_no = b.cont_no and a.year = b.year and a.month = b.month and a.gs_gbn = b.gs_gbn where a.pay_yn = 'N' and b.slpdate is not null
-- begin tran 
-- update a set pay_yn = 'Y' FROM HPOIMS.dbo.gs_require a left outer join HPOIMS.dbo.gs_payment b on a.cont_no = b.cont_no and a.year = b.year and a.month = b.month and a.gs_gbn = b.gs_gbn where a.pay_yn = 'N' and b.slpdate is not null
-- commit
-- sp_who2

-- 기성지불의 전표정보와 회계전표번호 차이 체크하기 
select 
	'기성지불의 전표와 회계전표 차이', a.cont_no, a.year, a.month, a.gs_gbn, a.gs_seq, a.gs_amt, a.sk_amt, a.sj_amt, a.slpdate, a.dtcode, a.slpnum, b.slpnum , b.slpamt 
from 
	HPOIMS.dbo.gs_payment a 
left outer join 
	ACC.dbo.t23b10 b 
on 
	a.slpdate = b.slpdate and a.dtcode = b.dtcode 
where 
	a.year = '2026' and a.slpnum <> isnull(b.slpnum,0) and (a.gs_amt = b.slpamt or a.sk_amt = b.slpamt)


-- 구매발주서의 계약정보와 계약정보의 계약상세의 발주번호 조회하여 불일치 자료 검토하기
select 
	'구매발주서와 계약정보의 발주번호 불일치 자료', a.*
from
(
	select 
		a.po_no, a.cont_no, b.cont_seq, b.new_po_no, b.mfg_amt, b.set_amt 
	from 
		HPOIMS.dbo.gm_po a 
	left outer join 
		HPOIMS.dbo.gy_contract_detail b 
	on 
		a.cont_no = b.cont_no and a.po_no = b.new_po_no
	where
		a.po_date > '2024-01-01'
) a
where 
	a.cont_seq is null