2018-06-07
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DataTransferForCheck')
BEGIN
PRINT '======================================='
PRINT 'Dropping Procedure DataTransferForCheck'
PRINT '======================================='
DROP Procedure DataTransferForCheck
END
GO
PRINT '======================================='
PRINT 'Creating Procedure DataTransferForCheck'
PRINT '======================================='
GO
CREATE PROCEDURE DataTransferForCheck
/* Param List 参数列表*/
@StartDate datetime='2018-01-01',
@EndDate datetime='2100-01-01'
AS
begin
BEGIN TRANSACTION
PRINT '1'
DECLARE @errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0
DECLARE @executionnumber sysname
--select @executionnumber=ExecutionNumber from InvoiceMaked where IfTransfered=0 and ExecuteDate>@StartDate and ExecuteDate<@EndDate
DECLARE Execution_Cursor CURSOR FOR
select ExecutionNumber from InvoiceMaked where IfTransfered=0 and ExecuteDate>@StartDate and ExecuteDate<@EndDate and ExecutionNumber='HJTK917F2W29AZJFIYX5'
--select ExecutionNumber from InvoiceMaked where IfTransfered=0 and ExecuteDate>'2018-01-01' and ExecuteDate<'2100-01-01'
OPEN Execution_Cursor
FETCH NEXT FROM Execution_Cursor into @executionnumber
--0 FETCH 语句成功
--1 FETCH 语句失败或此行不在结果集中
--2 被提取的行不存在
--PRINT @executionnumber
PRINT '2'
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '====================='
PRINT @executionnumber
PRINT '====================='
DECLARE @cusid sysname
DECLARE @certainid sysname
DECLARE @financetotalid sysname
select @cusid=CustomerID,@certainid=CertainProjectID,@financetotalid=ID from FinanceTotal where DocInfoCode=@executionnumber
PRINT '2-1'
BEGIN
PRINT '====================='
PRINT '@cusid'+@cusid
PRINT '@certainid'+@certainid
PRINT '@financetotalid'+@financetotalid
PRINT '====================='
--==============================================新建顾客信息 start
--写入CustomerBaseInfo
insert into bh.dbo.CustomerBaseInfo(Address, Age, Amount, Balance, BirthDay, ChannelEmployee,
CIdentity, ComeTimes, CustomerServiceEmployee, DoctorEmployee, EducationID, Email, FirstPhone,
Forzen, HasMedical, HasSickHistory, ID, IntegrationBalance, IsCome, IsMember, LastestOperateEmployee,
MSN, Name, NetChannelID, OtherInfo, PresentationInsideID, PresentationMode, PresentationOutsideID,
PresentationPeopleID, ProfessionID, QQ, RankID, RegionID, RegisterDate, Remark, Remove, SceneEmployee,
SecondPhone, Sex, Status, Success, SuccessTimes, TempEmployee, ThirdPhone, TMKEmployee, UnderstandWayID, VIP, ZipCode)
select Address, Age, Amount, Balance, BirthDay, ChannelEmployee, CIdentity, ComeTimes, CustomerServiceEmployee,
DoctorEmployee, EducationID, Email, FirstPhone, Forzen, HasMedical, HasSickHistory, ID, IntegrationBalance, IsCome,
IsMember, LastestOperateEmployee, MSN, Name, NetChannelID, OtherInfo, PresentationInsideID, PresentationMode, PresentationOutsideID,
PresentationPeopleID, ProfessionID, QQ, RankID, RegionID, RegisterDate, Remark, Remove, SceneEmployee, SecondPhone, Sex, Status, Success,
SuccessTimes, TempEmployee, ThirdPhone, TMKEmployee, UnderstandWayID, VIP, ZipCode from bh20180420.dbo.CustomerBaseInfo b where b.ID=@cusid
PRINT '=============CustomerBaseInfo finished!================='
--写入CustomerPhoneNumber
INSERT INTO bh.dbo.CustomerPhoneNumber(Belong, CallStatus, CustomerID, Handleby, LastestUpdateTime, PhoneNumber, RegisterDate, SmsStatus)
select Belong, CallStatus, CustomerID, Handleby, LastestUpdateTime, PhoneNumber, RegisterDate, SmsStatus from bh20180420.dbo.CustomerPhoneNumber b where b.CustomerID=@cusid
PRINT '=============CustomerPhoneNumber finished!================='
--写入CustomerComeLog
INSERT INTO bh.dbo.CustomerComeLog(CountSettingsID, CountSettingsSubCategoryID, CustomerID, Date, HandleBy, OriginalCSID, OriginalCSSubCategoryID)
select CountSettingsID, CountSettingsSubCategoryID, CustomerID, Date, HandleBy, OriginalCSID, OriginalCSSubCategoryID from bh20180420.dbo.CustomerComeLog b where b.CustomerID=@cusid
PRINT '=============CustomerComeLog finished!================='
--CustomerRegisterType
INSERT INTO bh.dbo.CustomerRegisterType(CustomerID, RegisterType) select CustomerID, RegisterType from bh20180420.dbo.CustomerRegisterType b where b.CustomerID=@cusid
PRINT '=============CustomerRegisterType finished!================='
--AccountsOfCustomer
INSERT INTO bh.dbo.AccountsOfCustomer(Amount,Balance,CustomerID,IntegrationBalance,Invoice) select Amount,Balance,CustomerID,IntegrationBalance,Invoice from bh20180420.dbo.AccountsOfCustomer b where b.CustomerID=@cusid
PRINT '=============AccountsOfCustomer finished!================='
--StatusOfCustomer
INSERT INTO bh.dbo.StatusOfCustomer(CustomerID,Status) select CustomerID,Status from bh20180420.dbo.StatusOfCustomer b where b.CustomerID=@cusid
PRINT '=============StatusOfCustomer finished!================='
--SuccessOfCustomer
INSERT INTO bh.dbo.SuccessOfCustomer(CustomerID,Success) select CustomerID,Success from bh20180420.dbo.SuccessOfCustomer b where b.CustomerID=@cusid
PRINT '=============SuccessOfCustomer finished!================='
--CustomerLevelDetails
INSERT INTO bh.dbo.CustomerLevelDetails(Balance,CashAmount,CustomerID,LevelID,Manual,ReturnCashAmount) select Balance,CashAmount,CustomerID,LevelID,Manual,ReturnCashAmount from bh20180420.dbo.CustomerLevelDetails b where b.CustomerID=@cusid
PRINT '=============CustomerLevelDetails finished!================='
--MemberCardInfo
INSERT INTO bh.dbo.MemberCardInfo(CardNo,CustomerID,MemberCardNum) select CardNo,CustomerID,MemberCardNum from bh20180420.dbo.MemberCardInfo b where b.CustomerID=@cusid
PRINT '=============MemberCardInfo finished!================='
--==============================================新建顾客信息 end
--==============================================分诊信息 start
--CustomerForDepartment
INSERT INTO bh.dbo.CustomerForDepartment(CustomerID,DepartmentID) select CustomerID,DepartmentID from bh20180420.dbo.CustomerForDepartment b where b.CustomerID=@cusid
PRINT '=============CustomerForDepartment finished!================='
--ConsultingInfo
INSERT INTO bh.dbo.ConsultingInfo(CustomerID,ExcutionNumber,Remark) select CustomerID,ExcutionNumber,Remark from bh20180420.dbo.ConsultingInfo b where b.CustomerID=@cusid
PRINT '=============ConsultingInfo finished!================='
--CustomerForGuideMedical
INSERT INTO bh.dbo.CustomerForGuideMedical(ConsultingProjectID,CustomerID,GMDate,GuideMedicalEmployeeID) select ConsultingProjectID,CustomerID,GMDate,GuideMedicalEmployeeID from bh20180420.dbo.CustomerForGuideMedical b where b.CustomerID=@cusid
PRINT '=============CustomerForGuideMedical finished!================='
--GuideLog
INSERT INTO bh.dbo.GuideLog(CustomerID,GuideDate,GuidedBy,SceneEmployee,TempEmployee) select CustomerID,GuideDate,GuidedBy,SceneEmployee,TempEmployee from bh20180420.dbo.GuideLog b where b.CustomerID=@cusid
PRINT '=============GuideLog finished!================='
--CustomerForScene
INSERT INTO bh.dbo.CustomerForScene(CustomerID,SceneDate,SceneEmployeeID) select CustomerID,SceneDate,SceneEmployeeID from bh20180420.dbo.CustomerForScene b where b.CustomerID=@cusid
PRINT '=============CustomerForScene finished!================='
--==============================================分诊信息 end
--==============================================下确诊单 start
--CustomerConsumptionInfo
INSERT INTO bh.dbo.CustomerConsumptionInfo(Cancellation,ChargeDate,ChargedBy,ConsumptionTotal,CustomerID,Date,ExcutionNumber,HandledBy,HandledDeptID,IsSecondary,RegisterBy,RegisterDept,Remark,Status) select Cancellation,ChargeDate,ChargedBy,ConsumptionTotal,CustomerID,Date,ExcutionNumber,HandledBy,HandledDeptID,IsSecondary,RegisterBy,RegisterDept,Remark,Status from bh20180420.dbo.CustomerConsumptionInfo b where b.CustomerID=@cusid
PRINT '=============CustomerConsumptionInfo finished!================='
--CustomerConsumptionDetails
INSERT INTO bh.dbo.CustomerConsumptionDetails(Amount,ApprovalEmployeeID,CanExecute,ChargeStatus,ExcutePercent,ExcutionNumber,ExecuteStatus,HandselItemModeID,HasReserveDoctor,IsDispute,ItemID,ItemTypeID,KindOfSicknessID,NeedInHospital,PackageID,PayedAmount,PlanExcuteDate,Price,Quantity,Quantity2,Remark,ReserveDoctorID,ReturnMoneyStatus,ReturnVisited,Sconto,Status) select Amount,ApprovalEmployeeID,CanExecute,ChargeStatus,ExcutePercent,ExcutionNumber,ExecuteStatus,HandselItemModeID,HasReserveDoctor,IsDispute,ItemID,ItemTypeID,KindOfSicknessID,NeedInHospital,PackageID,PayedAmount,PlanExcuteDate,Price,Quantity,Quantity2,Remark,ReserveDoctorID,ReturnMoneyStatus,ReturnVisited,Sconto,Status from bh20180420.dbo.CustomerConsumptionDetails b where b.ExcutionNumber=@executionnumber
PRINT '=============CustomerConsumptionDetails finished!================='
--CertainDocStatusLog
INSERT INTO bh.dbo.CertainDocStatusLog(ChageDate,ExcutionNumber,NewStatusID,Remark) select ChageDate,ExcutionNumber,NewStatusID,Remark from bh20180420.dbo.CertainDocStatusLog b where b.ExcutionNumber=@executionnumber
PRINT '=============CertainDocStatusLog finished!================='
--==============================================下确诊单 end
--==============================================建会员卡 end
INSERT INTO bh.dbo.MemberCardHandle(CardNo,Checked,CustomerID,Date,HandleBy,Manual,MemberCardNum,NewRankID,Valid) select CardNo,Checked,CustomerID,Date,HandleBy,Manual,MemberCardNum,NewRankID,Valid from bh20180420.dbo.MemberCardHandle b where b.CustomerID=@cusid
PRINT '=============MemberCardHandle finished!================='
--==============================================建会员卡 start
--==============================================收费 end
INSERT INTO ChargeForCertainDocInfo(Date,ExcutionNumber,HandleBy,Remark,SN,Status) select Date,ExcutionNumber,HandleBy,Remark,SN,Status from bh20180420.dbo.ChargeForCertainDocInfo b where b.ExcutionNumber=@executionnumber
PRINT '=============ChargeForCertainDocInfo finished!================='
INSERT INTO ChargeForCertainDocDetails(Amount,ArrearsPaymentAmount,ArrearsPaymentDate,ArrearsPaymentHandleby,CardNumber,CertainProjectID,Date,ExcutionNumber,Handleby,IsHandselMoney,IsPayed,PayModeID) select Amount,ArrearsPaymentAmount,ArrearsPaymentDate,ArrearsPaymentHandleby,CardNumber,CertainProjectID,Date,ExcutionNumber,Handleby,IsHandselMoney,IsPayed,PayModeID from bh20180420.dbo.ChargeForCertainDocDetails b where b.ExcutionNumber=@executionnumber
PRINT '=============ChargeForCertainDocDetails finished!================='
INSERT INTO FinanceTotal(AllTotal,ArrearsAmount,BigCustomerEmployee,BigCustomerEmployeeName,Came,CardNameID,CashAmount,CashOfMoneyCardAmount,CertainProjectID,ChargeBy,ChargeByName,ChargeOrReturn,CustomerID,CustomerStatus,CustomerStatusID,Date,DocCode,DocHandleBy,DocHandleByName,DocHandleDeptID,DocHandleDeptName,DocInfoCode,DocType,GoodsID,HandselOfMoneyCardAmount,IncomeTypeID,InsteadMoneyAmount,IntegrationAmount,ItemID,LeadersID,MedicineID,MHDetailsID,MoneyCardPercent,MoneyCardRealAmount,PrePaymentAmount,Quantity,Quantity2,ReturnCertainID,ReturnMHID,SceneEmployee,SceneEmployeeName,TempEmployee,TempEmployeeName,TMKDeptID,TMKDeptName,TMKEmployee,TMKEmployeeName,UnitPrice,YearCardAmount) select AllTotal,ArrearsAmount,BigCustomerEmployee,BigCustomerEmployeeName,Came,CardNameID,CashAmount,CashOfMoneyCardAmount,CertainProjectID,ChargeBy,ChargeByName,ChargeOrReturn,CustomerID,CustomerStatus,CustomerStatusID,Date,DocCode,DocHandleBy,DocHandleByName,DocHandleDeptID,DocHandleDeptName,DocInfoCode,DocType,GoodsID,HandselOfMoneyCardAmount,IncomeTypeID,InsteadMoneyAmount,IntegrationAmount,ItemID,LeadersID,MedicineID,MHDetailsID,MoneyCardPercent,MoneyCardRealAmount,PrePaymentAmount,Quantity,Quantity2,ReturnCertainID,ReturnMHID,SceneEmployee,SceneEmployeeName,TempEmployee,TempEmployeeName,TMKDeptID,TMKDeptName,TMKEmployee,TMKEmployeeName,UnitPrice,YearCardAmount from bh20180420.dbo.FinanceTotal b where b.DocInfoCode=@executionnumber
PRINT '=============FinanceTotal finished!================='
INSERT INTO FinanceTotalDetails(Amount,CardNumber,FinanceTotalID,IsHandselMoney,PayModeID) select Amount,CardNumber,FinanceTotalID,IsHandselMoney,PayModeID from bh20180420.dbo.FinanceTotalDetails b where b.FinanceTotalID=@financetotalid
PRINT '=============FinanceTotalDetails finished!================='
INSERT INTO CertainDocDetailsTotalRecord(CertainProjectID,Date,DocType,ExecuteArrearsAmount,ExecuteCashAmount,ExecuteInsteadMoneyAmount,ExecuteIntegrationAmount,ExecuteMoneyCardCashAmount,ExecuteMoneyCardHandselAmount,ExecutePrepaymentAmount,ExecuteTotalOther,ExecuteTotalPR,ExecuteYearCardAmount,ItemID,PayArrearsAmount,PayCashAmount,PayInsteadMoneyAmount,PayIntegrationAmount,PayMoneyCardCashAmount,PayMoneyCardHandselAmount,PayPrepaymentAmount,PayTotalOther,PayTotalPR,PayYearCardAmount,SceneEmployee) select CertainProjectID,Date,DocType,ExecuteArrearsAmount,ExecuteCashAmount,ExecuteInsteadMoneyAmount,ExecuteIntegrationAmount,ExecuteMoneyCardCashAmount,ExecuteMoneyCardHandselAmount,ExecutePrepaymentAmount,ExecuteTotalOther,ExecuteTotalPR,ExecuteYearCardAmount,ItemID,PayArrearsAmount,PayCashAmount,PayInsteadMoneyAmount,PayIntegrationAmount,PayMoneyCardCashAmount,PayMoneyCardHandselAmount,PayPrepaymentAmount,PayTotalOther,PayTotalPR,PayYearCardAmount,SceneEmployee from bh20180420.dbo.CertainDocDetailsTotalRecord b where b.CertainProjectID=@certainid
PRINT '=============CertainDocDetailsTotalRecord finished!================='
INSERT INTO UnExcuteTotal(ArrearsAmount,CashAmount,CertainProjectID,InsteadMoneyAmount,IntegrationAmount,MoneyCardCashAmount,MoneyCardHandselAmount,MoneyCardPercent,MoneyCardRealAmount,MoneyCardTotalAmount,PrepaymentAmount,TotalOther,TotalPR,TotalRealMoney,YearCardAmount,YearCardPercent,YearCardRealAmount) select ArrearsAmount,CashAmount,CertainProjectID,InsteadMoneyAmount,IntegrationAmount,MoneyCardCashAmount,MoneyCardHandselAmount,MoneyCardPercent,MoneyCardRealAmount,MoneyCardTotalAmount,PrepaymentAmount,TotalOther,TotalPR,TotalRealMoney,YearCardAmount,YearCardPercent,YearCardRealAmount from bh20180420.dbo.UnExcuteTotal b where b.CertainProjectID=@certainid
PRINT '=============UnExcuteTotal finished!================='
INSERT INTO UnExcuteBalanceTotal(ArrearsAmount,CashAmount,CertainProjectID,InsteadMoneyAmount,IntegrationAmount,MoneyCardCashAmount,MoneyCardHandselAmount,MoneyCardPercent,MoneyCardRealAmount,MoneyCardTotalAmount,PrepaymentAmount,TotalOther,TotalPR,TotalRealMoney,YearCardAmount,YearCardPercent,YearCardRealAmount) select ArrearsAmount,CashAmount,CertainProjectID,InsteadMoneyAmount,IntegrationAmount,MoneyCardCashAmount,MoneyCardHandselAmount,MoneyCardPercent,MoneyCardRealAmount,MoneyCardTotalAmount,PrepaymentAmount,TotalOther,TotalPR,TotalRealMoney,YearCardAmount,YearCardPercent,YearCardRealAmount from bh20180420.dbo.UnExcuteBalanceTotal b where b.CertainProjectID=@certainid
PRINT '=============UnExcuteBalanceTotal finished!================='
INSERT INTO ItemExcutedTotal(ArrearsAmount,CashAmount,CertainProjectID,InsteadMoneyAmount,IntegrationAmount,MoneyCardCashAmount,MoneyCardHandselAmount,MoneyCardPercent,MoneyCardRealAmount,MoneyCardTotalAmount,PrepaymentAmount,TotalOther,TotalPR,TotalRealMoney,YearCardAmount,YearCardPercent,YearCardRealAmount) select ArrearsAmount,CashAmount,CertainProjectID,InsteadMoneyAmount,IntegrationAmount,MoneyCardCashAmount,MoneyCardHandselAmount,MoneyCardPercent,MoneyCardRealAmount,MoneyCardTotalAmount,PrepaymentAmount,TotalOther,TotalPR,TotalRealMoney,YearCardAmount,YearCardPercent,YearCardRealAmount from bh20180420.dbo.ItemExcutedTotal b where b.CertainProjectID=@certainid
PRINT '=============ItemExcutedTotal finished!================='
--==============================================收费 start
END
FETCH NEXT FROM Execution_Cursor into @executionnumber
END
PRINT '3'
--PRINT @executionnumber
CLOSE Execution_Cursor
DEALLOCATE Execution_Cursor
PRINT '4'
IF @errorSun<>0
BEGIN
--PRINT '有错误，回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
--PRINT '成功，提交'
COMMIT TRANSACTION--事务提交语句
END
end
GO