2018-05-28

2018 年 5 月 28 日2018 年 5 月 29 日 by 沧海之树 In 工作

--设置正常退费状态

select ReturnMoneyStatus from CustomerConsumptionDetails where ID in (select CertainProjectID from FinanceTotal where DocType='退项目单据' )
update CustomerConsumptionDetails set ReturnMoneyStatus=2 where ID in (select CertainProjectID from FinanceTotal where DocType='退项目单据' )

--设置纠纷退费状态，这个状态在查询顾客信息时显示红色。

select * from CustomerConsumptionDetails where ID in (select CertainProjectID from FinanceTotal where DocType='纠纷退费单据' )
update CustomerConsumptionDetails set ReturnMoneyStatus=2,IsDispute=1 where ID in (select CertainProjectID from FinanceTotal where DocType='纠纷退费单据' )

以下是调试过程，无需执行

======================

exec sp_executesql N'SELECT [t0].[Date] AS [ComeDate], [t0].[CustomerID], [t1].[Name] AS [CustomerName], [t0].[ID] AS [Id], [t2].[MemberCardNum], [t3].[Name] AS [RegisterBy], [t5].[Name] AS [RegisterDept],
(CASE
WHEN [t1].[SceneEmployee] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t6].[Name])
ELSE NULL
END) AS [SceneName], [t1].[Sex]
FROM [dbo].[CustomerComeLog] AS [t0]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t1] ON [t1].[ID] = [t0].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t2] ON [t2].[CustomerID] = [t1].[ID]
INNER JOIN [dbo].[Employee] AS [t3] ON [t3].[ID] = [t0].[HandleBy]
INNER JOIN [dbo].[PositionInfo] AS [t4] ON [t4].[ID] = [t3].[PositionID]
INNER JOIN [dbo].[Department] AS [t5] ON [t5].[ID] = [t4].[DepartmentID]
LEFT OUTER JOIN [dbo].[Employee] AS [t6] ON [t6].[ID] = [t1].[SceneEmployee]
WHERE ([t0].[HandleBy] = @p0) AND ([t1].[Name] LIKE @p1) AND (NOT ([t1].[Remove] = 1)) AND (1 = 1)',N'@p0 varchar(8000),@p1 nvarchar(4000),@p2 int,@p3 varchar(8000),@p4 varchar(8000),@p5 int',@p0='00018',@p1=N'%张%',@p2=0,@p3='%%',@p4='%%',@p5=1

select * from CustomerComeLog

select * from ReturnMoneyForCertainDoc

exec sp_executesql N'SELECT [t0].[Amount], [t0].[ApprovalEmployeeID], [t0].[ID], [t0].[CanExecute], [t0].[ChargeStatus], [t0].[ExcutionNumber], [t0].[ExcutePercent], [t0].[ExecuteStatus], [t0].[HandselItemModeID], [t0].[HasReserveDoctor], [t0].[IsDispute], [t0].[ItemID], [t0].[ItemTypeID], [t0].[KindOfSicknessID], [t0].[NeedInHospital], [t0].[PackageID], [t0].[PayedAmount], [t0].[PlanExcuteDate], [t0].[Price], [t0].[Quantity], [t0].[Quantity2], [t0].[Remark], [t0].[ReserveDoctorID], [t0].[ReturnMoneyStatus], [t0].[ReturnVisited], [t0].[Sconto], [t0].[Status], [t0].[Version]
FROM [dbo].[CustomerConsumptionDetails] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 bigint',@p0=1558
exec sp_executesql N'SELECT [t0].[Amount], [t0].[ApprovalEmployeeID], [t0].[ID], [t0].[CanExecute], [t0].[ChargeStatus], [t0].[ExcutionNumber], [t0].[ExcutePercent], [t0].[ExecuteStatus], [t0].[HandselItemModeID], [t0].[HasReserveDoctor], [t0].[IsDispute], [t0].[ItemID], [t0].[ItemTypeID], [t0].[KindOfSicknessID], [t0].[NeedInHospital], [t0].[PackageID], [t0].[PayedAmount], [t0].[PlanExcuteDate], [t0].[Price], [t0].[Quantity], [t0].[Quantity2], [t0].[Remark], [t0].[ReserveDoctorID], [t0].[ReturnMoneyStatus], [t0].[ReturnVisited], [t0].[Sconto], [t0].[Status], [t0].[Version]
FROM [dbo].[CustomerConsumptionDetails] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 bigint',@p0=1556
exec sp_executesql N'SELECT [t0].[Aliases], [t0].[ID], [t0].[CategoryID], [t0].[ChangePrice], [t0].[ConsultingItemCategoryID], [t0].[CustomCode], [t0].[ExcuteDepartmentID], [t0].[GoodsCategoryID], [t0].[IncomeCategoryID], [t0].[IntervalOfOneTreatment], [t0].[IntervalOfTreatment], [t0].[IsConsume], [t0].[IsOperation], [t0].[IsPlace], [t0].[IsTreatment], [t0].[TopCategoryID], [t0].[LimitInsteadMoney], [t0].[LimitIntegration], [t0].[LimitSconto], [t0].[MaxPrice], [t0].[MinPrice], [t0].[MustExcute], [t0].[Name], [t0].[NationalCode], [t0].[NeedInHospital], [t0].[PriceOfOneTime], [t0].[Remark], [t0].[SimpleCode], [t0].[Standard], [t0].[StockQuantity], [t0].[Success], [t0].[TimesOfOneTreatment], [t0].[TimesOfTreatment], [t0].[Unit], [t0].[UnitPrice], [t0].[UseInMedicine], [t0].[Valid]
FROM [dbo].[ItemInfo] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 varchar(8000)',@p0='00257'

exec sp_executesql N'SELECT [t0].[Aliases], [t0].[ID], [t0].[CategoryID], [t0].[ChangePrice], [t0].[ConsultingItemCategoryID], [t0].[CustomCode], [t0].[ExcuteDepartmentID], [t0].[GoodsCategoryID], [t0].[IncomeCategoryID], [t0].[IntervalOfOneTreatment], [t0].[IntervalOfTreatment], [t0].[IsConsume], [t0].[IsOperation], [t0].[IsPlace], [t0].[IsTreatment], [t0].[TopCategoryID], [t0].[LimitInsteadMoney], [t0].[LimitIntegration], [t0].[LimitSconto], [t0].[MaxPrice], [t0].[MinPrice], [t0].[MustExcute], [t0].[Name], [t0].[NationalCode], [t0].[NeedInHospital], [t0].[PriceOfOneTime], [t0].[Remark], [t0].[SimpleCode], [t0].[Standard], [t0].[StockQuantity], [t0].[Success], [t0].[TimesOfOneTreatment], [t0].[TimesOfTreatment], [t0].[Unit], [t0].[UnitPrice], [t0].[UseInMedicine], [t0].[Valid]
FROM [dbo].[ItemInfo] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 varchar(8000)',@p0='00258'

exec sp_executesql N'SELECT [t0].[ID], [t0].[DisplayOnConsulting], [t0].[TopCategoryID], [t0].[Name], [t0].[ParentID], [t0].[SimpleCode]
FROM [dbo].[ItemCategory] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 varchar(8000)',@p0='00002'

select * from CustomerConsumptionDetails where ReturnMoneyStatus=2

select * CustomerBaseInfo whr

exec sp_executesql N'SELECT [t0].[AllTotal], [t0].[ArrearsAmount], [t0].[BigCustomerEmployee], [t0].[BigCustomerEmployeeName], [t0].[Came], [t0].[CardNameID], [t0].[CashAmount], [t0].[CashOfMoneyCardAmount], [t0].[CertainProjectID], [t0].[ChargeBy], [t0].[ChargeByName], [t0].[ChargeOrReturn], [t0].[CustomerID], [t0].[CustomerStatus], [t0].[CustomerStatusID], [t0].[Date], [t0].[DocCode], [t0].[DocHandleBy], [t0].[DocHandleByName], [t0].[DocHandleDeptID], [t0].[DocHandleDeptName], [t0].[DocInfoCode], [t0].[DocType], [t0].[SceneEmployee], [t0].[TMKEmployee], [t0].[TempEmployee], [t0].[ID], [t0].[IncomeTypeID], [t0].[GoodsID], [t0].[HandselOfMoneyCardAmount], [t0].[InsteadMoneyAmount], [t0].[IntegrationAmount], [t0].[ItemID], [t0].[LeadersID], [t0].[MedicineID], [t0].[MHDetailsID], [t0].[MoneyCardPercent], [t0].[MoneyCardRealAmount], [t0].[PrePaymentAmount], [t0].[Quantity], [t0].[Quantity2], [t0].[ReturnCertainID], [t0].[ReturnMHID], [t0].[SceneEmployeeName], [t0].[TempEmployeeName], [t0].[TMKDeptID], [t0].[TMKDeptName], [t0].[TMKEmployeeName], [t0].[UnitPrice], [t0].[YearCardAmount]
FROM [dbo].[FinanceTotal] AS [t0]
WHERE [t0].[CustomerID] = @p0
ORDER BY [t0].[Date] DESC',N'@p0 varchar(8000)',@p0='ZBJZ11Z2H0'

select * from CustomerConsumptionDetails where ReturnMoneyStatus=2;

select * from CustomerConsumptionInfo where Status=5;

select * from YUN_HIS20180521.dbo.H_FrontSK where sSFLB = '退费';
select * from YUN_HIS20180521.dbo.H_FrontSK where sSFLB = '纠纷退费';
select * from ReturnMoneyForCertainDoc;
18051506
select distinct(sSFLB) from YUN_HIS20180521.dbo.H_FrontSK

select * from CustomerBaseInfo;

select * from MemberCardHandle where MemberCardNum='18051506'

select * from CustomerBaseInfo where ID='ZBJZ11Z2H0'

select * from CustomerConsumptionDetails where Amount <0;

select * from FinanceTotal

exec sp_executesql N'SELECT [t0].[AllTotal], [t0].[ArrearsAmount], [t0].[BigCustomerEmployee], [t0].[BigCustomerEmployeeName], [t0].[Came], [t0].[CardNameID], [t0].[CashAmount], [t0].[CashOfMoneyCardAmount], [t0].[CertainProjectID], [t0].[ChargeBy], [t0].[ChargeByName], [t0].[ChargeOrReturn], [t0].[CustomerID], [t0].[CustomerStatus], [t0].[CustomerStatusID], [t0].[Date], [t0].[DocCode], [t0].[DocHandleBy], [t0].[DocHandleByName], [t0].[DocHandleDeptID], [t0].[DocHandleDeptName], [t0].[DocInfoCode], [t0].[DocType], [t0].[SceneEmployee], [t0].[TMKEmployee], [t0].[TempEmployee], [t0].[ID], [t0].[IncomeTypeID], [t0].[GoodsID], [t0].[HandselOfMoneyCardAmount], [t0].[InsteadMoneyAmount], [t0].[IntegrationAmount], [t0].[ItemID], [t0].[LeadersID], [t0].[MedicineID], [t0].[MHDetailsID], [t0].[MoneyCardPercent], [t0].[MoneyCardRealAmount], [t0].[PrePaymentAmount], [t0].[Quantity], [t0].[Quantity2], [t0].[ReturnCertainID], [t0].[ReturnMHID], [t0].[SceneEmployeeName], [t0].[TempEmployeeName], [t0].[TMKDeptID], [t0].[TMKDeptName], [t0].[TMKEmployeeName], [t0].[UnitPrice], [t0].[YearCardAmount]
FROM [dbo].[FinanceTotal] AS [t0]
WHERE [t0].[CustomerID] = @p0 and
AllTotal<0
ORDER BY [t0].[Date] DESC',N'@p0 varchar(8000)',@p0='ZBJZ11Z2H0'

select * from CustomerConsumptionDetails where ID in
(select DocCode from FinanceTotal where AllTotal<0)

select * from FinanceTotal where DocType='退项目单据' or DocType='纠纷退费单据'

select * from CustomerConsumptionDetails where ExcutionNumber in (select DocInfoCode from FinanceTotal where DocType='退项目单据' or DocType='纠纷退费单据')

select * from FinanceTotal where DocType='退项目单据' and CustomerID='ZBJZ11Z2H0'

select * from CustomerConsumptionDetails where ExcutionNumber in (select DocInfoCode from FinanceTotal where DocType='退项目单据' and CustomerID='ZBJZ11Z2H0')

select * from CustomerConsumptionDetails where ID in (select CertainProjectID from FinanceTotal where DocType='退项目单据' and CustomerID='ZBJZ11Z2H0')

select * from CustomerConsumptionDetails where ExcutionNumber in (select DocInfoCode from FinanceTotal where DocType='退项目单据' )

select * from CustomerConsumptionDetails where ID in (select CertainProjectID from FinanceTotal where DocType='退项目单据' )
select * from FinanceTotal where DocType='退项目单据'

select * from FinanceTotal where DocInfoCode='MZJPA743LH94'

select * from CustomerBaseInfo where ID='KAGN7ORHXP'

select * from FinanceTotal where DocInfoCode='FTN3EDRCHZH6'

select * from CustomerBaseInfo where ID='N8EYNTQNWK'

exec sp_executesql N'SELECT
(CASE
WHEN [t0].[AnesthetistID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t3].[Name])
ELSE NULL
END) AS [AnesthetistName],
(CASE
WHEN [t0].[FirstAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t4].[Name])
ELSE NULL
END) AS [AssistantName],
(CASE
WHEN [t0].[TwoAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t5].[Name])
ELSE NULL
END) AS [AssistantTwoName], [t7].[Name] AS [DeptName],
(CASE
WHEN [t0].[DoctorID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t8].[Name])
ELSE NULL
END) AS [DoctorName], [t0].[Date] AS [ExcuteDate], [t0].[ExcuteQuantity], [t0].[Excutetimes] AS [ExcuteTimes], [t6].[Name] AS [ItemName], [t6].[Standard] AS [ItemStandard], [t1].[Amount], [t1].[ApprovalEmployeeID], [t1].[ID], [t1].[CanExecute], [t1].[ChargeStatus], [t1].[ExcutionNumber], [t1].[ExcutePercent], [t1].[ExecuteStatus], [t1].[HandselItemModeID], [t1].[HasReserveDoctor], [t1].[IsDispute], [t1].[ItemID], [t1].[ItemTypeID], [t1].[KindOfSicknessID], [t1].[NeedInHospital], [t1].[PackageID], [t1].[PayedAmount], [t1].[PlanExcuteDate], [t1].[Price], [t1].[Quantity], [t1].[Quantity2], [t1].[Remark], [t1].[ReserveDoctorID], [t1].[ReturnMoneyStatus], [t1].[ReturnVisited], [t1].[Sconto], [t1].[Status], [t1].[Version], [t0].[ExcuteRecord] AS [Remark2]
FROM [dbo].[ExcuteInfo] AS [t0]
INNER JOIN [dbo].[CustomerConsumptionDetails] AS [t1] ON [t1].[ID] = [t0].[CertainProjectID]
INNER JOIN [dbo].[CustomerConsumptionInfo] AS [t2] ON [t2].[ExcutionNumber] = [t1].[ExcutionNumber]
LEFT OUTER JOIN [dbo].[Employee] AS [t3] ON [t3].[ID] = [t0].[AnesthetistID]
LEFT OUTER JOIN [dbo].[Employee] AS [t4] ON [t4].[ID] = [t0].[FirstAssistantID]
LEFT OUTER JOIN [dbo].[Employee] AS [t5] ON [t5].[ID] = [t0].[TwoAssistantID]
INNER JOIN [dbo].[ItemInfo] AS [t6] ON [t6].[ID] = [t1].[ItemID]
INNER JOIN [dbo].[Department] AS [t7] ON [t7].[ID] = [t6].[ExcuteDepartmentID]
LEFT OUTER JOIN [dbo].[Employee] AS [t8] ON [t8].[ID] = [t0].[DoctorID]
WHERE ([t2].[CustomerID] = @p0) AND (NOT ([t0].[Cancel] = 1))',N'@p0 varchar(8000)',@p0='KAGN7ORHXP'

select * from ExcuteInfo

select * from CustomerConsumptionDetails