

if exists(select 1 where OBJECT_ID('tempdb..#voucheritems') is not null) drop table #voucheritems
if exists(select 1 where OBJECT_ID('tempdb..#VoucherItems_Prn') is not null) drop table #VoucherItems_Prn
if exists(select 1 where OBJECT_ID('tempdb..#voucheritemsbak') is not null) drop table #voucheritemsbak
if exists(select 1 where OBJECT_ID('tempdb..#VoucherItems_Prnbak') is not null) drop table #VoucherItems_Prnbak
if exists(select 1 where OBJECT_ID('tempdb..#vouchertemplatesbak') is not null) drop table #vouchertemplatesbak
if exists(select 1 where OBJECT_ID('tempdb..#vouchers') is not null) drop table #vouchers

select distinct CardNum ,CardSection ,FieldName  into #voucheritems from @u8db..voucheritems 
select distinct CardNum ,CardSection ,FieldName  into #VoucherItems_Prn from @u8db..VoucherItems_Prn  

select vt_id as tmpid,*  into #voucheritemsbak from @u8db..voucheritems 
select vt_id as tmpid,*  into #VoucherItems_Prnbak from @u8db..VoucherItems_Prn 
select vt_id as tmpid,*  into #vouchertemplatesbak from @u8db..vouchertemplates

select distinct cardnumber into  #vouchers
from (
select cardnumber  from @u8db..vouchers
union 
select VT_CardNumber  from @u8db..VoucherTemplates
) a

truncate table @u8db..voucheritems
truncate table @u8db..VoucherItems_Prn
truncate table @u8db..VoucherTemplates

insert into @u8db..voucheritems(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,ShowIt,LinkIt,LinkTbl,LinkField,TableName,FieldName,FieldType,ReferType,ReferTable,CardSection,CardFormula1,CardFormula2,CanModify,MaxLength,MaxShowLen,NumPoint,IsNull,CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,NeedSum,CalcField,AliasNum,isSelfDef,COX,COY,Width,Height,TabIndex,DefaultValue,IsFixedLenght,FormatData,FontState,PrintCaption,PrintUpcase,PrintInterval,DataSource,EnterType,DataRule,ValidityCheck,ReserveSegTitlePos,BuildArchives,ReferReturnField,cmemo,nBorder,pubufts)

select a.VT_ID,a.CardNum,a.CardItemNum,a.CardItemName,a.CanNotSelect,a.ShowIt,a.LinkIt,a.LinkTbl,a.LinkField,a.TableName,a.FieldName,a.FieldType,a.ReferType,a.ReferTable,a.CardSection,a.CardFormula1,a.CardFormula2,a.CanModify,a.MaxLength,a.MaxShowLen,a.NumPoint,a.IsNull,a.CanDelete,a.UserCheck,a.UserPrompt,a.FormatChar,a.IsMain,a.NeedSum,a.CalcField,a.AliasNum,a.isSelfDef,a.COX,a.COY,a.Width,a.Height,a.TabIndex,a.DefaultValue,a.IsFixedLenght,a.FormatData,a.FontState,a.PrintCaption,a.PrintUpcase,a.PrintInterval,a.DataSource,a.EnterType,a.DataRule,a.ValidityCheck,a.ReserveSegTitlePos,a.BuildArchives,a.ReferReturnField,a.cmemo,a.nBorder,Null pubufts
	from @t6db..voucheritems a inner join  #voucheritems b on a.CardNum=b.CardNum and a.CardSection =b.CardSection and a.FieldName =b.FieldName 

insert into @u8db..VoucherItems_Prn(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,ShowIt,LinkIt,LinkTbl,LinkField,TableName,FieldName,FieldType,ReferType,ReferTable,CardSection,CardFormula1,CardFormula2,CanModify,MaxLength,MaxShowLen,NumPoint,IsNull,CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,NeedSum,CalcField,AliasNum,isSelfDef,COX,COY,Width,Height,TabIndex,DefaultValue,IsFixedLenght,FormatData,FontState,PrintCaption,PrintUpcase,PrintInterval,DataSource,EnterType,DataRule,ValidityCheck,ReserveSegTitlePos,BuildArchives,ReferReturnField,cmemo,nBorder,pubufts)

select a.VT_ID,a.CardNum,a.CardItemNum,a.CardItemName,a.CanNotSelect,a.ShowIt,a.LinkIt,a.LinkTbl,a.LinkField,a.TableName,a.FieldName,a.FieldType,a.ReferType,a.ReferTable,a.CardSection,a.CardFormula1,a.CardFormula2,a.CanModify,a.MaxLength,a.MaxShowLen,a.NumPoint,a.IsNull,a.CanDelete,a.UserCheck,a.UserPrompt,a.FormatChar,a.IsMain,a.NeedSum,a.CalcField,a.AliasNum,a.isSelfDef,a.COX,a.COY,a.Width,a.Height,a.TabIndex,a.DefaultValue,a.IsFixedLenght,a.FormatData,a.FontState,a.PrintCaption,a.PrintUpcase,a.PrintInterval,a.DataSource,a.EnterType,a.DataRule,a.ValidityCheck,a.ReserveSegTitlePos,a.BuildArchives,a.ReferReturnField,a.cmemo,a.nBorder,Null pubufts
	from @t6db..VoucherItems_Prn a inner join  #VoucherItems_Prn b on a.CardNum=b.CardNum and a.CardSection =b.CardSection and a.FieldName =b.FieldName 

insert into @u8db..VoucherTemplates(VT_ID,VT_Name,VT_CardNumber,VT_TemplateMode,VT_Width,VT_Height,VT_BodyTop,VT_BodyLeft,VT_BodyWidth,VT_BodyHeight,VT_SelfDef1,VT_SelfDef2,VT_SelfDef3,VT_Memo,VT_Lock,VT_TitleName,VT_TitleTop,VT_TitleLeft,VT_TitleFontState,VT_PageHeader,VT_BodyFixedCols,VT_BodyMaxRows,VT_GridStyle,VT_WorkAreaColor,VT_FiexdColor,VT_TotalColor,VT_ControlStyle,VT_GridStyleHead,VT_GridStyleBody,VT_GridStyleTotal,VT_GridPrnRows,VT_GridPrnRowHeight,VT_Header,VT_Foot,VT_footHeaderFont,VT_PrintTemplID,VT_AutoCalc,VT_PageSubTotal,VT_PageTotal,VT_PrintGrid,nAutoCalcWidth,nPrintSeril,nPrintGridLine,varPrintSetting,pubufts)
	select a.VT_ID,a.VT_Name,a.VT_CardNumber,a.VT_TemplateMode,a.VT_Width,a.VT_Height,a.VT_BodyTop,a.VT_BodyLeft,a.VT_BodyWidth,a.VT_BodyHeight,a.VT_SelfDef1,a.VT_SelfDef2,a.VT_SelfDef3,a.VT_Memo,a.VT_Lock,a.VT_TitleName,a.VT_TitleTop,a.VT_TitleLeft,a.VT_TitleFontState,a.VT_PageHeader,a.VT_BodyFixedCols,a.VT_BodyMaxRows,a.VT_GridStyle,a.VT_WorkAreaColor,a.VT_FiexdColor,a.VT_TotalColor,a.VT_ControlStyle,a.VT_GridStyleHead,a.VT_GridStyleBody,a.VT_GridStyleTotal,a.VT_GridPrnRows,a.VT_GridPrnRowHeight,a.VT_Header,a.VT_Foot,a.VT_footHeaderFont,a.VT_PrintTemplID,a.VT_AutoCalc,a.VT_PageSubTotal,a.VT_PageTotal,a.VT_PrintGrid,a.nAutoCalcWidth,a.nPrintSeril,a.nPrintGridLine,a.varPrintSetting,Null pubufts
	from @t6db..VoucherTemplates a inner join #vouchers b on a.VT_CardNumber=b.CardNumber

update a set a.DEF_ID =b.DEF_ID ,a.DEF_ID_PRN =b.DEF_ID_PRN 
	 from @u8db..vouchers a inner join @t6db..vouchers b on a.CardNumber =b.CardNumber

--需要将T6 错误删除的数据恢复到模板库
if exists(select 1 where OBJECT_ID('tempdb..#VoucherTemplates2') is not null) drop table #VoucherTemplates2
if exists(select 1 where OBJECT_ID('tempdb..#VoucherTemplates3') is not null) drop table #VoucherTemplates3
select distinct  a.VT_CardNumber into #VoucherTemplates2  from #vouchertemplatesbak a left join @u8db..vouchertemplates b
		on a.VT_CardNumber=b.VT_CardNumber where b.VT_CardNumber is null
select identity(int,1,1) as tmpid2 ,tmpid,vt_id,VT_CardNumber
	into #VoucherTemplates3
 from #vouchertemplatesbak where VT_CardNumber in (select VT_CardNumber
			from #VoucherTemplates2 )
update #VoucherTemplates3 set tmpid=tmpid2+(select max(VT_ID) from @u8db..VoucherTemplates)
update a set a.tmpid =b.tmpid from  #voucheritemsbak a inner join #VoucherTemplates3 b on a.vt_id=b.vt_id
update a set a.tmpid =b.tmpid from  #VoucherItems_Prnbak a inner join #VoucherTemplates3 b on a.vt_id=b.vt_id
update a set a.tmpid =b.tmpid from  #vouchertemplatesbak a inner join #VoucherTemplates3 b on a.vt_id=b.vt_id


insert into @u8db..voucheritems(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,ShowIt,LinkIt,LinkTbl,LinkField,TableName,FieldName,FieldType,ReferType,ReferTable,CardSection,CardFormula1,CardFormula2,CanModify,MaxLength,MaxShowLen,NumPoint,IsNull,CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,NeedSum,CalcField,AliasNum,isSelfDef,COX,COY,Width,Height,TabIndex,DefaultValue,IsFixedLenght,FormatData,FontState,PrintCaption,PrintUpcase,PrintInterval,DataSource,EnterType,DataRule,ValidityCheck,ReserveSegTitlePos,BuildArchives,ReferReturnField,cmemo,nBorder,pubufts)

select a.tmpid,a.CardNum,a.CardItemNum,a.CardItemName,a.CanNotSelect,a.ShowIt,a.LinkIt,a.LinkTbl,a.LinkField,a.TableName,a.FieldName,a.FieldType,a.ReferType,a.ReferTable,a.CardSection,a.CardFormula1,a.CardFormula2,a.CanModify,a.MaxLength,a.MaxShowLen,a.NumPoint,a.IsNull,a.CanDelete,a.UserCheck,a.UserPrompt,a.FormatChar,a.IsMain,a.NeedSum,a.CalcField,a.AliasNum,a.isSelfDef,a.COX,a.COY,a.Width,a.Height,a.TabIndex,a.DefaultValue,a.IsFixedLenght,a.FormatData,a.FontState,a.PrintCaption,a.PrintUpcase,a.PrintInterval,a.DataSource,a.EnterType,a.DataRule,a.ValidityCheck,a.ReserveSegTitlePos,a.BuildArchives,a.ReferReturnField,a.cmemo,a.nBorder,Null pubufts
	from #voucheritemsbak a where cardnum in (select VT_CardNumber from  #VoucherTemplates2)

insert into @u8db..VoucherItems_Prn(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,ShowIt,LinkIt,LinkTbl,LinkField,TableName,FieldName,FieldType,ReferType,ReferTable,CardSection,CardFormula1,CardFormula2,CanModify,MaxLength,MaxShowLen,NumPoint,IsNull,CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,NeedSum,CalcField,AliasNum,isSelfDef,COX,COY,Width,Height,TabIndex,DefaultValue,IsFixedLenght,FormatData,FontState,PrintCaption,PrintUpcase,PrintInterval,DataSource,EnterType,DataRule,ValidityCheck,ReserveSegTitlePos,BuildArchives,ReferReturnField,cmemo,nBorder,pubufts)
	select a.tmpid,a.CardNum,a.CardItemNum,a.CardItemName,a.CanNotSelect,a.ShowIt,a.LinkIt,a.LinkTbl,a.LinkField,a.TableName,a.FieldName,a.FieldType,a.ReferType,a.ReferTable,a.CardSection,a.CardFormula1,a.CardFormula2,a.CanModify,a.MaxLength,a.MaxShowLen,a.NumPoint,a.IsNull,a.CanDelete,a.UserCheck,a.UserPrompt,a.FormatChar,a.IsMain,a.NeedSum,a.CalcField,a.AliasNum,a.isSelfDef,a.COX,a.COY,a.Width,a.Height,a.TabIndex,a.DefaultValue,a.IsFixedLenght,a.FormatData,a.FontState,a.PrintCaption,a.PrintUpcase,a.PrintInterval,a.DataSource,a.EnterType,a.DataRule,a.ValidityCheck,a.ReserveSegTitlePos,a.BuildArchives,a.ReferReturnField,a.cmemo,a.nBorder,Null pubufts
	from #VoucherItems_Prnbak a where cardnum in (select VT_CardNumber from  #VoucherTemplates2)
	
insert into @u8db..VoucherTemplates(VT_ID,VT_Name,VT_CardNumber,VT_TemplateMode,VT_Width,VT_Height,VT_BodyTop,VT_BodyLeft,VT_BodyWidth,VT_BodyHeight,VT_SelfDef1,VT_SelfDef2,VT_SelfDef3,VT_Memo,VT_Lock,VT_TitleName,VT_TitleTop,VT_TitleLeft,VT_TitleFontState,VT_PageHeader,VT_BodyFixedCols,VT_BodyMaxRows,VT_GridStyle,VT_WorkAreaColor,VT_FiexdColor,VT_TotalColor,VT_ControlStyle,VT_GridStyleHead,VT_GridStyleBody,VT_GridStyleTotal,VT_GridPrnRows,VT_GridPrnRowHeight,VT_Header,VT_Foot,VT_footHeaderFont,VT_PrintTemplID,VT_AutoCalc,VT_PageSubTotal,VT_PageTotal,VT_PrintGrid,nAutoCalcWidth,nPrintSeril,nPrintGridLine,varPrintSetting,pubufts)
	select a.tmpid,a.VT_Name,a.VT_CardNumber,a.VT_TemplateMode,a.VT_Width,a.VT_Height,a.VT_BodyTop,a.VT_BodyLeft,a.VT_BodyWidth,a.VT_BodyHeight,a.VT_SelfDef1,a.VT_SelfDef2,a.VT_SelfDef3,a.VT_Memo,a.VT_Lock,a.VT_TitleName,a.VT_TitleTop,a.VT_TitleLeft,a.VT_TitleFontState,a.VT_PageHeader,a.VT_BodyFixedCols,a.VT_BodyMaxRows,a.VT_GridStyle,a.VT_WorkAreaColor,a.VT_FiexdColor,a.VT_TotalColor,a.VT_ControlStyle,a.VT_GridStyleHead,a.VT_GridStyleBody,a.VT_GridStyleTotal,a.VT_GridPrnRows,a.VT_GridPrnRowHeight,a.VT_Header,a.VT_Foot,a.VT_footHeaderFont,a.VT_PrintTemplID,a.VT_AutoCalc,a.VT_PageSubTotal,a.VT_PageTotal,a.VT_PrintGrid,a.nAutoCalcWidth,a.nPrintSeril,a.nPrintGridLine,a.varPrintSetting,Null pubufts
	from #vouchertemplatesbak a 
	where VT_CardNumber in (select VT_CardNumber from  #VoucherTemplates2)

update a set a.DEF_ID =b.tmpid
	 from @u8db..vouchers a inner join #VoucherTemplates3 b on a.CardNumber =b.VT_CardNumber
		and a.DEF_ID =b.vt_id
	 where CardNumber in (select VT_CardNumber
			from #VoucherTemplates2 )
update a set a.DEF_ID_PRN =b.tmpid
	 from @u8db..vouchers a inner join #VoucherTemplates3 b on a.CardNumber =b.VT_CardNumber
		and a.DEF_ID_PRN =b.vt_id
	 where CardNumber in (select VT_CardNumber
			from #VoucherTemplates2 )

--处理单据前缀
if exists(select 1 where OBJECT_ID('tempdb..#t6VoucherNumber') is not null) drop table #t6VoucherNumber

select AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule
into #t6VoucherNumber
from @t6db..VoucherNumber
where CardNumber in (select cardnumber from @u8db..VoucherNumber)
delete from @u8db..VoucherNumber where CardNumber in(select cardnumber from #t6VoucherNumber)
insert into @u8db..VoucherNumber (AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule)
select AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule
	from #t6VoucherNumber


if exists(select 1 where OBJECT_ID('tempdb..#t6VoucherNumber') is not null) drop table #t6VoucherNumber
if exists(select 1 where OBJECT_ID('tempdb..#voucheritems') is not null) drop table #voucheritems
if exists(select 1 where OBJECT_ID('tempdb..#VoucherItems_Prn') is not null) drop table #VoucherItems_Prn
if exists(select 1 where OBJECT_ID('tempdb..#voucheritemsbak') is not null) drop table #voucheritemsbak
if exists(select 1 where OBJECT_ID('tempdb..#VoucherItems_Prnbak') is not null) drop table #VoucherItems_Prnbak
if exists(select 1 where OBJECT_ID('tempdb..#vouchertemplatesbak') is not null) drop table #vouchertemplatesbak
if exists(select 1 where OBJECT_ID('tempdb..#VoucherTemplates2') is not null) drop table #VoucherTemplates2
if exists(select 1 where OBJECT_ID('tempdb..#VoucherTemplates3') is not null) drop table #VoucherTemplates3
if exists(select 1 where OBJECT_ID('tempdb..#vouchers') is not null) drop table #vouchers
update a set a.cvalue =b.cvalue 
	from @u8db..accinformation a inner join @t6db..accinformation b on a.csysid =b.csysid and a.cname =b.cname 
GO

--删除重复数据
IF OBJECT_ID('TEMPDB..#printEx') IS NOT NULL DROP TABLE #printEx
GO
SELECT * INTO #printEx FROM @t6db..printEx WHERE 1=2
GO
ALTER TABLE #printEx ADD iID int IDENTITY(1,1)
GO
INSERT INTO #printEx(cSystemName,cSystemSubNum,nTableNum,cTableName,cTableCode,cPaperType,bOrientation,iTitleRes,cResPathHead,cResPathTail,iSelfDefWidth,iselfDefHeight,iLeftMargin,iRightMargin,iBottomMargin,iTopMargin)
SELECT cSystemName,cSystemSubNum,nTableNum,cTableName,cTableCode,cPaperType,bOrientation,iTitleRes,cResPathHead,cResPathTail,iSelfDefWidth,iselfDefHeight,iLeftMargin,iRightMargin,iBottomMargin,iTopMargin 
FROM @t6db..printEx a WHERE EXISTS(SELECT cSystemName, nTableNum FROM @t6db..printEx 
	WHERE a.cSystemName=cSystemName AND a.nTableNum=nTableNum GROUP BY cSystemName, nTableNum having COUNT(*)>1)
DELETE FROM #printEx WHERE iID NOT IN(SELECT MIN(iID) FROM #printEx GROUP BY cSystemName, nTableNum)
DELETE @t6db..printEx FROM @t6db..printEx  a 
WHERE EXISTS(SELECT cSystemName, nTableNum FROM @t6db..printEx WHERE a.cSystemName=cSystemName AND a.nTableNum=nTableNum GROUP BY cSystemName, nTableNum having COUNT(*)>1)
INSERT INTO @t6db..printEx(cSystemName,cSystemSubNum,nTableNum,cTableName,cTableCode,cPaperType,bOrientation,iTitleRes,cResPathHead,cResPathTail,iSelfDefWidth,iselfDefHeight,iLeftMargin,iRightMargin,iBottomMargin,iTopMargin)
SELECT cSystemName,cSystemSubNum,nTableNum,cTableName,cTableCode,cPaperType,bOrientation,iTitleRes,cResPathHead,cResPathTail,iSelfDefWidth,iselfDefHeight,iLeftMargin,iRightMargin,iBottomMargin,iTopMargin 
FROM #printEx
GO
DROP TABLE #printEx
GO
IF OBJECT_ID('TEMPDB..#VoucherNumber') IS NOT NULL DROP TABLE #VoucherNumber
GO
SELECT * INTO #VoucherNumber FROM @t6db..VoucherNumber WHERE 1=2
GO
ALTER TABLE #VoucherNumber ADD iID int IDENTITY(1,1)
GO
INSERT INTO #VoucherNumber(AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule)
SELECT AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule 
FROM @t6db..VoucherNumber WHERE CardNumber IN (SELECT CardNumber FROM @t6db..VoucherNumber GROUP BY CardNumber having COUNT(*)>1)
DELETE FROM #VoucherNumber WHERE iID NOT IN(SELECT MIN(iID) FROM #VoucherNumber GROUP BY CardNumber)

DELETE @t6db..VoucherNumber FROM @t6db..VoucherNumber  a 
WHERE CardNumber IN (SELECT CardNumber FROM @t6db..VoucherNumber GROUP BY CardNumber having COUNT(*)>1)
INSERT INTO @t6db..VoucherNumber(AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule)
SELECT AppName,CardNumber,CardName,iSize,dStartDate,bAllowHandWork,bRepeatReDo,bRdFlag,iStartNumber,iIsRdVoucher,Prefix1,Prefix1Len,Prefix1Rule,Prefix2,Prefix2Len,Prefix2Rule,Prefix3,Prefix3Len,Prefix3Rule,Glide,GlideLen,GlideRule 
FROM #VoucherNumber
GO
DROP TABLE #VoucherNumber
GO
