2018-05-02
update ItemInfo set Success=1
exec sp_executesql N'SELECT [t2].[ChargeDate] AS [ChargeDate], [t0].[ID] AS [Cpid], [t4].[MemberCardNum] AS [CustomerMemberCardNum], [t3].[Name] AS [CustomerName], [t0].[ExecuteStatus] AS [cs],
(CASE
WHEN (CONVERT(Int,[t0].[HandselItemModeID])) IS NOT NULL THEN [t5].[Mode]
ELSE CONVERT(NVarChar(20),@p6)
END) AS [HandselMode], [t1].[Name] AS [ItemName], [t1].[Standard] AS [ItemStandard], [t6].[ItemTypeName] AS [ItemType], [t1].[Unit] AS [ItemUnit], [t0].[Quantity], [t0].[Quantity2] AS [Quantity1], [t0].[Remark], [t0].[Version]
FROM [dbo].[CustomerConsumptionDetails] AS [t0]
INNER JOIN [dbo].[ItemInfo] AS [t1] ON [t1].[ID] = [t0].[ItemID]
INNER JOIN [dbo].[CustomerConsumptionInfo] AS [t2] ON [t2].[ExcutionNumber] = [t0].[ExcutionNumber]
INNER JOIN [dbo].[CustomerBaseInfo] AS [t3] ON [t3].[ID] = [t2].[CustomerID]
LEFT OUTER JOIN [dbo].[MemberCardInfo] AS [t4] ON [t4].[CustomerID] = [t3].[ID]
LEFT OUTER JOIN [dbo].[HandselItemMode] AS [t5] ON [t5].[ID] = [t0].[HandselItemModeID]
INNER JOIN [dbo].[ItemTypeInfo] AS [t6] ON [t6].[ID] = [t0].[ItemTypeID]
WHERE ([t0].[CanExecute] = 1) AND ([t0].[ChargeStatus] = @p0) AND (([t1].[MustExcute]) = 1) AND ((
(CASE
WHEN @p1 = 1 THEN
(CASE
WHEN ([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2) THEN 1
WHEN NOT (([t4].[MemberCardNum] IS NOT NULL) AND ([t4].[MemberCardNum] LIKE @p2)) THEN 0
ELSE NULL
END)
WHEN [t4].[MemberCardNum] IS NOT NULL THEN CONVERT(Int,
(CASE
WHEN [t4].[MemberCardNum] LIKE @p3 THEN 1
WHEN NOT ([t4].[MemberCardNum] LIKE @p3) THEN 0
ELSE NULL
END))
ELSE @p4
END)) = 1) AND ([t3].[Name] LIKE @p5) AND ([t2].[ChargeDate] IS NOT NULL)',N'@p0 int,@p1 int,@p2 varchar(8000),@p3 varchar(8000),@p4 int,@p5 nvarchar(4000),@p6 nvarchar(4000)',@p0=1,@p1=0,@p2='%%',@p3='%%',@p4=1,@p5=N'%%',@p6=N''
exec sp_executesql N'SELECT
(CASE
WHEN [t0].[AnesthetistID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t1].[Name])
ELSE NULL
END) AS [AnesthetistName],
(CASE
WHEN [t0].[FirstAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t2].[Name])
ELSE NULL
END) AS [AssistantName],
(CASE
WHEN [t0].[TwoAssistantID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t3].[Name])
ELSE NULL
END) AS [AssistantTwoName], [t6].[Name] AS [DeptName],
(CASE
WHEN [t0].[DoctorID] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t7].[Name])
ELSE NULL
END) AS [DoctorName], [t0].[Date] AS [ExcuteDate], [t0].[ExcuteQuantity], [t0].[Excutetimes] AS [ExcuteTimes], [t5].[Name] AS [ItemName], [t5].[Standard] AS [ItemStandard], [t4].[Amount], [t4].[ApprovalEmployeeID], [t4].[ID], [t4].[CanExecute], [t4].[ChargeStatus], [t4].[ExcutionNumber], [t4].[ExcutePercent], [t4].[ExecuteStatus], [t4].[HandselItemModeID], [t4].[HasReserveDoctor], [t4].[IsDispute], [t4].[ItemID], [t4].[ItemTypeID], [t4].[KindOfSicknessID], [t4].[NeedInHospital], [t4].[PackageID], [t4].[PayedAmount], [t4].[PlanExcuteDate], [t4].[Price], [t4].[Quantity], [t4].[Quantity2], [t4].[Remark], [t4].[ReserveDoctorID], [t4].[ReturnMoneyStatus], [t4].[ReturnVisited], [t4].[Sconto], [t4].[Status], [t4].[Version], [t0].[ExcuteRecord] AS [Remark2]
FROM [dbo].[ExcuteInfo] AS [t0]
LEFT OUTER JOIN [dbo].[Employee] AS [t1] ON [t1].[ID] = [t0].[AnesthetistID]
LEFT OUTER JOIN [dbo].[Employee] AS [t2] ON [t2].[ID] = [t0].[FirstAssistantID]
LEFT OUTER JOIN [dbo].[Employee] AS [t3] ON [t3].[ID] = [t0].[TwoAssistantID]
INNER JOIN [dbo].[CustomerConsumptionDetails] AS [t4] ON [t4].[ID] = [t0].[CertainProjectID]
INNER JOIN [dbo].[ItemInfo] AS [t5] ON [t5].[ID] = [t4].[ItemID]
INNER JOIN [dbo].[Department] AS [t6] ON [t6].[ID] = [t5].[ExcuteDepartmentID]
LEFT OUTER JOIN [dbo].[Employee] AS [t7] ON [t7].[ID] = [t0].[DoctorID]
WHERE ([t0].[CertainProjectID] = @p0) AND (NOT ([t0].[Cancel] = 1))',N'@p0 bigint',@p0=133664
select* from CustomerConsumptionDetails where ID=133664
select * from ExcuteInfo where CertainProjectID=133664
select * from ItemForConsultingCategory
SELECT [t0].[ContainsCategories], [t0].[ContainsConsultingCategories], [t0].[ID], [t0].[DeptID], [t0].[IsOther], [t0].[Name], [t0].[Weights]
FROM [dbo].[CountSettings] AS [t0]
select * from bh20180420.dbo.CountSettings
select * from bh20180502.dbo.CountSettings
--update bh20180502.dbo.CountSettings set (Name,ContainsCategories,ContainsConsultingCategories,Weights,DeptID,IsOther)
--update bh20180502.dbo.CountSettings set Name=d.Name, ContainsCategories=d.ContainsCategories from bh20180502.dbo.CountSettings u, bh20180420.dbo.CountSettings d where u.ID=d.ID
SELECT [t0].[ContainsCategories], [t0].[ContainsConsultingCategories], [t0].[CountSettingsID], [t0].[ID], [t0].[Name]
FROM [dbo].[CountSettingsForSubCategory] AS [t0]
--update bh20180502.dbo.CountSettingsForSubCategory set CountSettingsID=d.CountSettingsID, ContainsCategories=d.ContainsCategories,ContainsConsultingCategories=d.ContainsConsultingCategories from bh20180502.dbo.CountSettingsForSubCategory u, bh20180420.dbo.CountSettingsForSubCategory d where u.ID=d.ID
select * from bh20180420.dbo.[CountSettingsForSubCategory]
select * from bh20180502.dbo.[CountSettingsForSubCategory]
exec sp_executesql N'SELECT [t0].[ContainsCategories], [t0].[ContainsConsultingCategories], [t0].[ID], [t0].[DeptID], [t0].[IsOther], [t0].[Name], [t0].[Weights]
FROM [dbo].[CountSettings] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 varchar(8000)',@p0='003'
exec sp_executesql N'SELECT [t0].[ID], [t0].[DisplayOnConsulting], [t0].[TopCategoryID], [t0].[Name], [t0].[ParentID], [t0].[SimpleCode]
FROM [dbo].[ItemCategory] AS [t0]
WHERE [t0].[ParentID] = @p0',N'@p0 varchar(8000)',@p0='00024'
select * from bh20180502.dbo.[ItemCategory];
select * from bh20180420.dbo.[ItemCategory];
select * from bh20180420.dbo.[ItemForConsultingCategory];
select * from bh20180502.dbo.[ItemForConsultingCategory];
--update bh20180502.dbo.ItemForConsultingCategory set Name=d.Name, ParentID=d.ParentID,SimpleCode=d.SimpleCode,BelongItemCategory=d.BelongItemCategory,TopCategoryID=d.TopCategoryID from bh20180502.dbo.ItemForConsultingCategory u, bh20180420.dbo.ItemForConsultingCategory d where u.ID=d.ID
--insert into bh20180502.[dbo].[ItemForConsultingCategory] select * from bh20180420.[dbo].[ItemForConsultingCategory] where bh20180420.[dbo].[ItemForConsultingCategory].ID>64
with a
as
(
select sum(AllTotal) total, CustomerID from FinanceTotal
where DocType not like '退%'
group by CustomerID
),
b as
(
select sum(AllTotal) rtn, CustomerID from FinanceTotal
where DocType like '退%'
group by CustomerID
)
insert into CustomerLevelDetails
select a.CustomerID, total, ABS(isnull(rtn,0)) rtn, total+isnull(rtn, 0) Balance,
case when (total+isnull(rtn, 0)) <50000 then 0
when (total+isnull(rtn, 0)) >=50000 and (total+isnull(rtn, 0)) <150000 then 1
when (total+isnull(rtn, 0)) >=150000 and (total+isnull(rtn, 0)) <300000 then 2
when (total+isnull(rtn, 0)) >=300000 and (total+isnull(rtn, 0)) <500000 then 3
when (total+isnull(rtn, 0)) >=500000 then 4
end level, 0 manual
from a
left join b on a.CustomerID=b.CustomerID
select * from CustomerCashLevel;
insert into bh20180502.[dbo].CustomerCashLevel select * from bh20180420.[dbo].CustomerCashLevel
SELECT is_broker_enabled FROM sys.databases WHERE name = 'bh20180502'
ALTER DATABASE bh20180502 SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE bh20180502 SET ENABLE_BROKER;
update CustomerAppendInfo set Rating=5 where CustomerID in
( select Now from bh20180502.dbo.CustomerBaseInfoID a inner join YUN_HIS20180420.dbo.H_TMK b where a.Original=b.FID and sBJ in ('A','皮肤重点','整形重点','重点') )
select * from bh20180502.dbo.CustomerBaseInfoID
select distinct(sBJ) from H_TMK;
select * from H_TMK where sBJ in ('皮肤重点','整形重点','重点');
select * from bh20180502.dbo.CustomerAppendInfo
update bh20180502.dbo.CustomerAppendInfo set Rating=5 where CustomerID in (
select Now from bh20180502.dbo.CustomerBaseInfoID a
inner join YUN_HIS20180420.dbo.H_TMK b on a.Original=b.FID
where b.sBJ in ('皮肤重点','整形重点','重点') )
update bh20180502.dbo.CustomerAppendInfo set Rating=3 where CustomerID in (
select Now from bh20180502.dbo.CustomerBaseInfoID a
inner join YUN_HIS20180420.dbo.H_TMK b on a.Original=b.FID
where b.sBJ in ('次重点','一般') )
select * from bh20180502.dbo.CustomerBaseInfoID a
inner join YUN_HIS20180420.dbo.H_TMK b on a.Original=b.FID
where b.sBJ in ('皮肤重点','整形重点','重点')
with t
as
(
select FID,
mXF as allAmount,
mJFTotal as remainPoint,
AccTotalJF as syzzj,
round(AccTotalMoney-AccMoney, 2) as ysk,
a.mQK as mqk,
b.Now as currID
from H_TMK a, bh20180502.dbo.CustomerBaseInfoID b where a.FID=b.Original
)
update AccountsOfCustomer set IntegrationBalance=t.remainPoint, Balance=t.ysk, Amount=t.allAmount,ValueAdded=t.syzzj
from AccountsOfCustomer inner join t on AccountsOfCustomer.CustomerID=t.currID
with t
as
(
select FID,
mXF as allAmount,
mJFTotal as remainPoint,
AccTotalJF as syzzj,
round(AccTotalMoney-AccMoney, 2) as ysk,
a.mQK as mqk,
b.Now as currID
from H_TMK a, bh20180502.dbo.CustomerBaseInfoID b where a.FID=b.Original
)
insert into ArrearsOfAccount(CustomerID,Balance) select t.currID,t.mqk from t where t.mqk>0
with t
as
(
select FID,
mJFTotal as jf,
b.Now as currID
from H_TMK a, bh20180502.dbo.CustomerBaseInfoID b where a.FID=b.Original
)
update AccountsOfCustomer set IntegrationBalance=t.jf
from AccountsOfCustomer inner join t on AccountsOfCustomer.CustomerID=t.currID