2018-06-06

2018 年 6 月 6 日2018 年 6 月 6 日 by 沧海之树 In 工作

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
--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
select @cusid=CustomerID from FinanceTotal where DocInfoCode=@executionnumber
PRINT '2-1'
BEGIN
PRINT '====================='
PRINT @cusid
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
--写入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
--写入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
--CustomerRegisterType
INSERT INTO bh.dbo.CustomerRegisterType(CustomerID, RegisterType) select CustomerID, RegisterType from bh20180420.dbo.CustomerRegisterType b where b.CustomerID=@cusid
--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
--StatusOfCustomer
INSERT INTO bh.dbo.StatusOfCustomer(CustomerID,Status) select CustomerID,Status from bh20180420.dbo.StatusOfCustomer b where b.CustomerID=@cusid
--SuccessOfCustomer
INSERT INTO bh.dbo.SuccessOfCustomer(CustomerID,Success) select CustomerID,Success from bh20180420.dbo.SuccessOfCustomer b where b.CustomerID=@cusid
--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
--MemberCardInfo
INSERT INTO bh.dbo.MemberCardInfo(CardNo,CustomerID,MemberCardNum) select CardNo,CustomerID,MemberCardNum from bh20180420.dbo.MemberCardInfo b where b.CustomerID=@cusid
--==============================================新建顾客信息 end

--==============================================分诊信息 start
--CustomerForDepartment
INSERT INTO bh.dbo.CustomerForDepartment(CustomerID,DepartmentID) select CustomerID,DepartmentID from bh20180420.dbo.CustomerForDepartment b where b.CustomerID=@cusid
--ConsultingInfo
INSERT INTO bh.dbo.ConsultingInfo(CustomerID,ExcutionNumber,Remark) select CustomerID,ExcutionNumber,Remark from bh20180420.dbo.ConsultingInfo b where b.CustomerID=@cusid
--CustomerForGuideMedical
INSERT INTO bh.dbo.CustomerForGuideMedical(ConsultingProjectID,CustomerID,GMDate,GuideMedicalEmployeeID) select ConsultingProjectID,CustomerID,GMDate,GuideMedicalEmployeeID b from bh20180420.dbo.CustomerForGuideMedical where b.CustomerID=@cusid
--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
--CustomerForScene
INSERT INTO bh.dbo.CustomerForScene(CustomerID,SceneDate,SceneEmployeeID) select CustomerID,SceneDate,SceneEmployeeID from bh20180420.dbo.CustomerForScene b where b.CustomerID=@cusid
--==============================================分诊信息 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
--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=@cusid
--CertainDocStatusLog
INSERT INTO bh.dbo.CertainDocStatusLog(ChageDate,ExcutionNumber,NewStatusID,Remark) select ChageDate,ExcutionNumber,NewStatusID,Remark from bh20180420.dbo.CertainDocStatusLog b where b.ExcutionNumber=@executionnumber
--==============================================下确诊单 end

--==============================================收费 end

--==============================================收费 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