<% '-- fcs로 데이터 이관 sql="" sql=sql&" SET NOCOUNT ON; "&vbCrLf sql=sql&" DECLARE @pn_cd varchar(30) "&vbCrLf sql=sql&" DECLARE @pu_type varchar(5) "&vbCrLf sql=sql&" DECLARE @fcs_type varchar(5) "&vbCrLf sql=sql&" DECLARE @fcs_area varchar(50) "&vbCrLf sql=sql&" DECLARE @ss int "&vbCrLf sql=sql&" DECLARE @ee int "&vbCrLf sql=sql&" DECLARE @ckday varchar(10) "&vbCrLf sql=sql&" set @pn_cd='"&pn_cd&"' "&vbCrLf sql=sql&" set @pu_type='"&pu_prjcode_type&"' "&vbCrLf sql=sql&" set @fcs_type='"&pu_fcs_type&"' "&vbCrLf sql=sql&" set @fcs_area='"&pu_prjcode_area&"' "&vbCrLf sql=sql&" set @ss='"&fcs_type__ss&"' "&vbCrLf sql=sql&" set @ee='"&fcs_type__ee&"' "&vbCrLf sql=sql&" set @ckday='"&ckday&"' "&vbCrLf '-- (1) 등록된 검증률이 없다면 등록 sql=sql&" if not EXISTS(select id from [project2].[dbo].[T_QC_CONFIG] where p_sn=@pn_cd and s_area=@fcs_area and use_yn='Y') "&vbCrLf sql=sql&" Begin "&vbCrLf '-- 검증률 등록 (Default 룰 insert ) sql=sql&" insert into [project2].[dbo].[T_QC_CONFIG] "&vbCrLf sql=sql&" (p_sn,s_area,qc_rate,error_rate,qc_date,no_call_time,no_call_limit,use_yn,sIdStart,sIdEnd,input_dt,input_user) "&vbCrLf sql=sql&" values "&vbCrLf sql=sql&" (@pn_cd,@fcs_area,'30','50','5','2','6','Y','0','0','"&date()&"','"&Session("User_Number")&"') "&vbCrLf sql=sql&" end "&vbCrLf '-- (2) 등록된 지역별 설문지 범위 정보가 있는지 체크 sql=sql&" if not EXISTS(select id from [dbo].[T_AREA_SID] where p_sn=@pn_cd and s_area=@fcs_area and s_type=@fcs_type and del_yn='N' ) "&vbCrLf '-- 등록된 내용이 없다면 insert sql=sql&" Begin "&vbCrLf sql=sql&" insert into [project2].[dbo].[T_AREA_SID] "&vbCrLf sql=sql&" (p_sn,s_area,s_type,sIdStart,sIdEnd,del_yn,input_date) "&vbCrLf sql=sql&" values "&vbCrLf sql=sql&" (@pn_cd,@fcs_area,@fcs_type,@ss,@ee,'N','"&date()&"') "&vbCrLf sql=sql&" end "&vbCrLf sql=sql&" else "&vbCrLf '-- 등록된 내용이 있다면 update sql=sql&" Begin "&vbCrLf sql=sql&" update [project2].[dbo].[T_AREA_SID] set "&vbCrLf sql=sql&" sIdStart=@ss "&vbCrLf sql=sql&" ,sIdEnd=@ee "&vbCrLf sql=sql&" where p_sn=@pn_cd "&vbCrLf sql=sql&" and s_area=@fcs_area "&vbCrLf sql=sql&" and s_type=@fcs_type "&vbCrLf sql=sql&" and del_yn='N' "&vbCrLf sql=sql&" end "&vbCrLf '-- (3) 검증문항 설정 '################################################# '-- 이전 등록된 내용이 있다면 삭제(del_) 처리함 ----- sql=sql&" update [project2].[dbo].[T_RES_SQ] set "&vbCrLf sql=sql&" p_sn='del_'+p_sn "&vbCrLf sql=sql&" ,p_child_sn='del_'+p_child_sn "&vbCrLf sql=sql&" where p_child_sn=@pn_cd "&vbCrLf&vbCrLf '################################################# '-- 검증문항 등록 sql=sql&" DECLARE @검증문항 varchar(30) "&vbCrLf sql=sql&" DECLARE @SQL varchar(max) "&vbCrLf sql=sql&" DECLARE @SQL1 varchar(max) "&vbCrLf sql=sql&" DECLARE @SQL2 varchar(max) "&vbCrLf sql=sql&" DECLARE @SQL1_1 varchar(max) "&vbCrLf sql=sql&" DECLARE @SQL2_1 varchar(max) "&vbCrLf sql=sql&" DECLARE @tcnt int "&vbCrLf sql=sql&" set @tcnt=0 "&vbCrLf sql=sql&" set @SQL='' "&vbCrLf sql=sql&" set @SQL1='' "&vbCrLf sql=sql&" set @SQL2='' "&vbCrLf sql=sql&" set @SQL1_1='' "&vbCrLf sql=sql&" set @SQL2_1='' "&vbCrLf sql=sql&" DECLARE cur_step1 CURSOR FOR "&vbCrLf sql=sql&" select pu_que from [dbo].[T_RES_SQ_QUE_LAB] "&vbCrLf sql=sql&" where ting='1' "&vbCrLf sql=sql&" and pu_child_sn=@pn_cd "&vbCrLf sql=sql&" and pu_type=@pu_type "&vbCrLf '-- fcs지역 sql=sql&" and fcs_area=@fcs_area "&vbCrLf sql=sql&" and pu_que_type='0' "&vbCrLf sql=sql&" order by id asc "&vbCrLf sql=sql&" OPEN cur_step1 "&vbCrLf sql=sql&" FETCH NEXT FROM cur_step1 INTO @검증문항 "&vbCrLf sql=sql&" WHILE @@FETCH_STATUS = 0 "&vbCrLf sql=sql&" BEGIN "&vbCrLf sql=sql&" set @tcnt=@tcnt+1 "&vbCrLf sql=sql&" set @SQL1=@SQL1+'sq'+convert(varchar(5),@tcnt)+',' "&vbCrLf sql=sql&" set @SQL2=@SQL2+''''+@검증문항+''',' "&vbCrLf '-- SQ사용시 sql=sql&" set @SQL2=@SQL2+'''SQ_'+@검증문항+''',' "&vbCrLf sql=sql&" set @SQL1_1=@SQL1_1+'as'+convert(varchar(5),@tcnt)+',' "&vbCrLf sql=sql&" set @SQL2_1=@SQL2_1+' ['+@검증문항+'] as [as'+convert(varchar(5),@tcnt)+'], ' "&vbCrLf sql=sql&" FETCH NEXT FROM cur_step1 INTO @검증문항 "&vbCrLf sql=sql&" END "&vbCrLf sql=sql&" CLOSE cur_step1 "&vbCrLf sql=sql&" DEALLOCATE cur_step1 "&vbCrLf sql=sql&" set @SQL=' insert into [project2].[dbo].[T_RES_SQ] (p_sn,p_child_sn,'+@SQL1+' s_area,s_type,duplication_date) values (substring('''+@pn_cd+''',1,7),'''+@pn_cd+''','+@SQL2+' '''+@fcs_area+''','''+@fcs_type+''',''"&date()&"'') ' "&vbCrLf sql=sql&" exec(@SQL) "&vbCrLf '-- (4) FCS 데이터 등록 sql=sql&" DECLARE @r_name varchar(50) "&vbCrLf sql=sql&" DECLARE @r_tel varchar(50) "&vbCrLf sql=sql&" DECLARE @r_phone varchar(50) "&vbCrLf sql=sql&" set @r_name='' "&vbCrLf sql=sql&" set @r_tel='' "&vbCrLf sql=sql&" set @r_phone='' "&vbCrLf '----------------------------------------------------------------------------------- ' 응답자 이름 sql=sql&" select top 1 @r_name=pu_que from [dbo].[T_RES_SQ_QUE_LAB] with(nolock) "&vbCrLf sql=sql&" where ting='1' "&vbCrLf sql=sql&" and pu_child_sn='"&pn_cd&"' "&vbCrLf sql=sql&" and pu_type='"&pu_prjcode_type&"' "&vbCrLf '-- fcs 지역 sql=sql&" and fcs_area='"&pu_prjcode_area&"' "&vbCrLf sql=sql&" and pu_que_type in ('2','3') "&vbCrLf sql=sql&" and len(tuser_name)>0 "&vbCrLf '응답자 전화번호 sql=sql&" select top 1 @r_tel=replace(replace(pu_que,'-',''),' ','') from [dbo].[T_RES_SQ_QUE_LAB] with(nolock) "&vbCrLf sql=sql&" where ting='1' "&vbCrLf sql=sql&" and pu_child_sn='"&pn_cd&"' "&vbCrLf sql=sql&" and pu_type='"&pu_prjcode_type&"' "&vbCrLf '-- fcs 지역 sql=sql&" and fcs_area='"&pu_prjcode_area&"' "&vbCrLf sql=sql&" and pu_que_type in ('2','3') "&vbCrLf sql=sql&" and len(tphone_2)>0 "&vbCrLf ' 응답자 휴대폰번호 sql=sql&" select top 1 @r_phone=replace(replace(pu_que,'-',''),' ','') from [dbo].[T_RES_SQ_QUE_LAB] with(nolock) "&vbCrLf sql=sql&" where ting='1' "&vbCrLf sql=sql&" and pu_child_sn='"&pn_cd&"' "&vbCrLf sql=sql&" and pu_type='"&pu_prjcode_type&"' "&vbCrLf '-- fcs 지역 sql=sql&" and fcs_area='"&pu_prjcode_area&"' "&vbCrLf sql=sql&" and pu_que_type in ('2','3') "&vbCrLf sql=sql&" and len(tphone_1)>0 "&vbCrLf '----------------------------------------------------------------------------------- '-- (4) FCS 데이터 등록 insert sql=sql&" set @SQL=' insert into [project2].[dbo].[res] ' "&vbCrLf sql=sql&" set @SQL=@SQL+' (chasu, r_name, r_tel, r_phone, r_date, p_sn, m_name, m_no, r_sv, DelGubun, v_warning_cnt, s_id, '+@SQL1_1+' input_dt, s_type, s_area ) ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' select '"&vbCrLf sql=sql&" set @SQL=@SQL+' ''1'' as chasu, '"&vbCrLf '----------------------------------------------------------------- sql=sql&" if @r_name<>'' "&vbCrLf sql=sql&" begin "&vbCrLf sql=sql&" set @SQL=@SQL+' A.'+@r_name+' as r_name, ' "&vbCrLf sql=sql&" end "&vbCrLf sql=sql&" else "&vbCrLf sql=sql&" begin "&vbCrLf sql=sql&" set @SQL=@SQL+' '''' as r_name, ' "&vbCrLf sql=sql&" end "&vbCrLf '----------------------------------------------------------------- sql=sql&" if @r_tel<>'' "&vbCrLf sql=sql&" begin "&vbCrLf sql=sql&" set @SQL=@SQL+' A.'+@r_tel+' as r_tel, '"&vbCrLf sql=sql&" end "&vbCrLf sql=sql&" else "&vbCrLf sql=sql&" begin "&vbCrLf sql=sql&" set @SQL=@SQL+' '''' as r_tel, '"&vbCrLf sql=sql&" end "&vbCrLf '----------------------------------------------------------------- sql=sql&" if @r_phone<>'' "&vbCrLf sql=sql&" begin "&vbCrLf sql=sql&" set @SQL=@SQL+' A.'+@r_phone+' as r_phone, ' "&vbCrLf sql=sql&" end "&vbCrLf sql=sql&" else "&vbCrLf sql=sql&" begin "&vbCrLf sql=sql&" set @SQL=@SQL+' '''' as r_phone, ' "&vbCrLf sql=sql&" end "&vbCrLf '----------------------------------------------------------------- ' sql=sql&" set @SQL=@SQL+' ''"&ckday&"'' as r_date, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' convert(varchar(10),getdate(),120) as r_date, ' "&vbCrLf '-- 등록일자 sql=sql&" set @SQL=@SQL+' '''+@pn_cd+''' as p_sn, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' B.m_name as m_name, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' B.new_id as m_no, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ''"&Session("User_Number")&"'' as r_sv, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ''N'' as DelGubun, ' "&vbCrLf '-- 사용(N) sql=sql&" set @SQL=@SQL+' ''0'' as v_warning_cnt, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' A.ffid as s_id, '+@SQL2_1+' getdate() as input_dt, ''"&pu_fcs_type&"'' as s_type, ''"&pu_prjcode_area&"'' as s_area from ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ( ' "&vbCrLf sql=sql&" set @SQL=@SQL+' select ' "&vbCrLf sql=sql&" set @SQL=@SQL+' (isnull(r_error,''0'')+isnull(m_error,''0'')+isnull(s_error,''0'')+isnull(q_error,''0'')) as t, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' case when left(ffusercode,2)=''1-'' then ffusercode else ''1-''+ffusercode end as ff, ' "&vbCrLf sql=sql&" set @SQL=@SQL+' * ' "&vbCrLf sql=sql&" set @SQL=@SQL+' from "&co_table&" ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ) A ' "&vbCrLf sql=sql&" set @SQL=@SQL+' left join ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ( ' "&vbCrLf sql=sql&" set @SQL=@SQL+' select new_id, m_name, left(m_jumin,6) as m_jumin from [project2].[dbo].[man] with(nolock) ' "&vbCrLf sql=sql&" set @SQL=@SQL+' where m_activity=''활동'' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' and left(new_id,2)=''1-'' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ) B ' "&vbCrLf sql=sql&" set @SQL=@SQL+' on A.ff=B.new_id ' "&vbCrLf sql=sql&" set @SQL=@SQL+' left join ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ( ' "&vbCrLf sql=sql&" set @SQL=@SQL+' select s_id, s_type, s_area from project2.dbo.res ' "&vbCrLf sql=sql&" set @SQL=@SQL+' where p_sn = '''+@pn_cd+''' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' and DelGubun=''N'' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ) C ' "&vbCrLf sql=sql&" set @SQL=@SQL+' on A.ffid = C.s_id ' "&vbCrLf sql=sql&" set @SQL=@SQL+' where A.t=0 ' "&vbCrLf sql=sql&" set @SQL=@SQL+' and C.s_id is null ' "&vbCrLf sql=sql&" set @SQL=@SQL+' order by A.ffid asc ' "&vbCrLf sql=sql&" exec(@SQL) "&vbCrLf response.Write sql '-- 고유키(num) 처리 sql=sql&" set @SQL=' update [project2].[dbo].[RES] set ' "&vbCrLf sql=sql&" set @SQL=@SQL+' r_sn=num ' "&vbCrLf sql=sql&" set @SQL=@SQL+' where p_sn='''+@pn_cd+''' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' and s_area='''+@fcs_area+''' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' and DelGubun=''N'' ' "&vbCrLf '-- 사용(N) sql=sql&" set @SQL=@SQL+' and cast(chasu as int)=1 ' "&vbCrLf sql=sql&" exec(@SQL) "&vbCrLf '-- 프로젝트별 고유값 (rp_sn) 처리 sql=sql&" DECLARE @rp_sn bigint "&vbCrLf sql=sql&" select @rp_sn=isnull(max(rp_sn),0) from [project2].[dbo].[RES] "&vbCrLf sql=sql&" where p_sn='"&pn_cd&"' "&vbCrLf sql=sql&" and cast(chasu as int)=1 "&vbCrLf sql=sql&" update [project2].[dbo].[RES] set "&vbCrLf sql=sql&" rp_sn=B.tno "&vbCrLf sql=sql&" from [project2].[dbo].[RES] as A "&vbCrLf sql=sql&" inner join "&vbCrLf sql=sql&" ( "&vbCrLf sql=sql&" select ROW_NUMBER() OVER(ORDER BY num ASC)+@rp_sn AS tno, "&vbCrLf sql=sql&" p_sn,s_area,s_id from [project2].[dbo].[RES] with(nolock) "&vbCrLf sql=sql&" where p_sn='"&pn_cd&"' "&vbCrLf sql=sql&" and s_area='"&pu_prjcode_area&"' "&vbCrLf sql=sql&" and DelGubun='N' "&vbCrLf '-- 사용(N) sql=sql&" and cast(chasu as int)=1 "&vbCrLf sql=sql&" and rp_sn is null "&vbCrLf sql=sql&" ) B "&vbCrLf sql=sql&" on A.p_sn=B.p_sn "&vbCrLf sql=sql&" and A.s_area=B.s_area "&vbCrLf sql=sql&" and A.s_id=B.s_id "&vbCrLf sql=sql&" where A.p_sn='"&pn_cd&"' "&vbCrLf sql=sql&" and A.s_area='"&pu_prjcode_area&"' "&vbCrLf sql=sql&" and A.DelGubun='N' "&vbCrLf '-- 사용(N) sql=sql&" and cast(A.chasu as int)=1 "&vbCrLf sql=sql&" and A.rp_sn is null "&vbCrLf '-- '-- 이전 리스트가 있는경우 : 이전리스트를 DelGubun='Y' 처리(프로젝트 내에서 s_id 로 중복체크하여 처리) ※ [dbo].[T_INSPECTION_RES] 진행된 리스트(페이퍼) 아이디는 q_error 로 처리되어 넘어오지 않음 '-- sql=sql&" set @SQL=' update [project2].[dbo].[RES] set ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' DelGubun=''Y'' ' "&vbCrLf '-- 미사용 데이터로 처리함 '-- sql=sql&" set @SQL=@SQL+' where p_sn='''+@pn_cd+''' ' "&vbCrLf '-- '-- 지역조건 제외 sql=sql&" set @SQL=@SQL+' and s_area='''+@fcs_area+''' ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' and DelGubun=''N'' ' "&vbCrLf '-- 사용(N) '-- sql=sql&" set @SQL=@SQL+' and cast(chasu as int)=1 ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' and s_id in ( ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' select s_id from [project2].[dbo].[RES] ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' where p_sn='''+@pn_cd+''' ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' and DelGubun=''N'' ' "&vbCrLf '-- 사용(N) '-- sql=sql&" set @SQL=@SQL+' and s_area='''+@fcs_area+''' ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' and cast(chasu as int)=1 ' "&vbCrLf '-- sql=sql&" set @SQL=@SQL+' ) ' "&vbCrLf '-- sql=sql&" exec(@SQL) "&vbCrLf '-- 업로드 데이터 차수가 있는경우 처리함(검증율[dbo].[T_QC_CONFIG] 이 지역별 이므로 차수계산시 지역을 포함 함) 'sql=sql&" set @SQL=' update [project2].[dbo].[RES] set ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' chasu=A2.new_chasu ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' from [project2].[dbo].[RES] as A1 ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' inner join ' "&vbCrLf ''-------------------------------------------------------------------------------------------- 'sql=sql&" set @SQL=@SQL+' ( ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' select C.p_sn, C.m_no, C.s_area, C.new_chasu from ( ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' select ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' case when B.qc_stat is null then ''1'' else cast(A.chasu as int)+1 end as new_chasu, ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' A.chasu,A.r_sn, A.p_sn, A.m_no, A.s_type, A.s_area, ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' B.pass_stat,B.qc_stat from ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' ( ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' select * from [project2].[dbo].[RES] with(nolock) ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' where p_sn='''+@pn_cd+''' ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' and s_area='''+@fcs_area+''' ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' and cast(chasu as int)<999 ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' ) A ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' left join ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' ( ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' select r_sn, m_no, s_id , pass_stat, qc_stat from [project2].[dbo].[T_INSPECTION_RES] with(nolock) ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' where num in ( ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' select max(num) from [project2].[dbo].[T_INSPECTION_RES] with(nolock) ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' where p_sn='''+@pn_cd+''' ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' and s_area='''+@fcs_area+''' ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' group by p_sn, s_area, m_no ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' ) ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' ) B ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' on A.r_sn=B.r_sn ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' where B.qc_stat is not null ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' ) C ' "&vbCrLf ' sql=sql&" set @SQL=@SQL+' group by C.p_sn, C.m_no, C.s_area, C.new_chasu ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' ) A2 ' "&vbCrLf ''-------------------------------------------------------------------------------------------- 'sql=sql&" set @SQL=@SQL+' on A1.p_sn=A2.p_sn ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' and A1.s_area=A2.s_area ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' and A1.m_no=A2.m_no ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' where A1.p_sn='''+@pn_cd+''' ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' and A1.DelGubun=''N'' ' "&vbCrLf '-- 사용(N) 'sql=sql&" set @SQL=@SQL+' and A1.s_area='''+@fcs_area+''' ' "&vbCrLf 'sql=sql&" set @SQL=@SQL+' and cast(A1.chasu as int)=999 ' "&vbCrLf 'sql=sql&" exec(@SQL) "&vbCrLf '-- 추가차수가 없는경우 1차로 처리함 sql=sql&" update [project2].[dbo].[RES] set "&vbCrLf sql=sql&" chasu='1' "&vbCrLf sql=sql&" where p_sn=@pn_cd "&vbCrLf sql=sql&" and s_area=@fcs_area "&vbCrLf sql=sql&" and DelGubun='N' "&vbCrLf sql=sql&" and cast(chasu as int)=1 "&vbCrLf '-- 테스트시 사용 sql ↓------------------------------ '-- 임시테이블 삭제 '-- sql=sql&" drop table "&co_table&" " '-- '-- sql=sql&" SET NOCOUNT OFF; "&vbCrLf '-- rsp.Open sql, adoConn, 1 '-- 테스트시 사용 sql ↑------------------------------ '########## 펀칭DB 처리 진행 ##################################### '-- (5) 펀칭디비 - 불량데이터 검증에러(12) 처리 sql=sql&" set @SQL='' "&vbCrLf sql=sql&" set @SQL=@SQL+' update [punching].[dbo].[tb_response] set ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ting=''12'' ' "&vbCrLf '-- 검증에러(12) 처리 sql=sql&" set @SQL=@SQL+' from [punching].[dbo].[tb_response] as A1 ' "&vbCrLf sql=sql&" set @SQL=@SQL+' inner join ' "&vbCrLf sql=sql&" set @SQL=@SQL+' ( select ffid from "&co_table&" where (isnull(r_error,''0'')+isnull(m_error,''0'')+isnull(s_error,''0'')+isnull(q_error,''0''))>0) A2 ' "&vbCrLf sql=sql&" set @SQL=@SQL+' on A1.ttelnum=A2.ffid ' "&vbCrLf sql=sql&" set @SQL=@SQL+' where A1.ting=''19'' ' "&vbCrLf '-- 검증이관(19) sql=sql&" set @SQL=@SQL+' and A1.tprocode=''"&pn_cd&"'' ' "&vbCrLf sql=sql&" set @SQL=@SQL+' and char(A1.ttype+65)=''"&pu_prjcode_type&"'' ' "&vbCrLf sql=sql&" exec(@SQL) "&vbCrLf '-- 펀칭 데이터 삭제전 백업 sql=sql&" insert into [project2].[dbo].[punching_data_log] "&vbCrLf sql=sql&" (tid, pc_prjcode, pc_prjcode_sub1, pc_prjcode_sub2, ffusercode, ffid, ffque, ffans, ffinsert, fcs_user, fcs_insert, ff_ing) "&vbCrLf sql=sql&" select "&vbCrLf sql=sql&" tid, pc_prjcode, pc_prjcode_sub1, pc_prjcode_sub2, ffusercode, ffid, ffque, ffans, ffinsert, "&vbCrLf sql=sql&" '"&Session("User_Number")&"' as fcs_user, getdate() as fcs_insert, ff_ing "&vbCrLf sql=sql&" from [project2].[dbo].[punching_data] "&vbCrLf sql=sql&" where pc_prjcode=@pn_cd "&vbCrLf sql=sql&" and pc_prjcode_sub1=@pu_type "&vbCrLf '-- 펀칭 데이터 삭제처리 sql=sql&" delete from [project2].[dbo].[punching_data] " sql=sql&" where pc_prjcode=@pn_cd " sql=sql&" and pc_prjcode_sub1=@pu_type " '-- 임시테이블 삭제 sql=sql&" drop table "&co_table&" " sql=sql&" SET NOCOUNT OFF; "&vbCrLf xprint = "1" if xprint="1" then response.write sql&"

" '-- SQL 실행 '--response.End rsp.Open sql, adoConn, 1 %>