SELECT C.ProtocolCustomerSN AS CompanySN , C.CompanyName AS CompanyName , ISNULL(CheckingAccount, 0) + ISNULL(CAB.FreeCharge, 0) AS TotalAccount , -- ISNULL(V4.CheckingAccount, 0) AS CheckingAccount , -- CAB.MarginCharge AS MarginCharge , -- ISNULL(CAB.FreeCharge, 0) AS FreezeAccount , -- 0 AS ETicketAccount , 0 AS CouponAccount , --ISNULL(V1.ETicketAccount, 0) AS ETicketAccount, -- --ISNULL(V2.CouponAccount, 0) AS CouponAccount, --, ISNULL(CPB.Point, 0) AS PointAccount , -- 0 AS PreAuthAccount , -- ISNULL(-V3.RentConsumeReceivable, 0) AS RentConsumeReceivable , -- ISNULL(-V3.PeccancyReceivable, 0) AS PeccancyReceivable , -- ISNULL(-V3.AccidentReceivable, 0) AS AccidentReceivable , -- ISNULL(-V3.LiquidatedDamagesReceivable, 0) AS LiquidatedDamagesReceivable , -- ISNULL(-V3.ValueAddedServiceReceivable, 0) AS ValueAddedServiceReceivable , -- ( ISNULL(-V3.RentConsumeReceivable, 0) + ISNULL(-V3.PeccancyReceivable, 0) + ISNULL(-V3.AccidentReceivable, 0) + ISNULL(-V3.LiquidatedDamagesReceivable, 0) + ISNULL(-V3.ValueAddedServiceReceivable, 0) ) AS TotalReceivable , CAB.PCustomerID AS PCustomerID , E.EmployID AS ServiceOperatorID , E1.EmployID AS ClientManagerID , E.EmployName AS ServiceOperatorName , E1.EmployName AS ClientManagerName , ISNULL(T.RentingCharge, 0) AS RentingCharge , -- PreAuthCharge = ISNULL(PCAS.PreAuthCharge, 0) , UseCharge = ISNULL(PCAS.UseCharge, 0) FROM TB_PCustomerAccountBalance CAB LEFT OUTER JOIN dbo.TB_PCustomerPointBalance CPB ON CPB.PCustomerID = CAB.PCustomerID LEFT OUTER JOIN ( SELECT SUM(CASE WHEN V_ReceivableCharge.ItemID = 1 THEN ISNULL(ReceivableCharge, 0) ELSE 0 END) + SUM(CASE WHEN V_Adjust.ItemID = 1 THEN ISNULL(AdjustCharge, 0) ELSE 0 END) AS RentConsumeReceivable , -- SUM(CASE WHEN V_ReceivableCharge.ItemID = 2 THEN ReceivableCharge ELSE 0 END) + SUM(CASE WHEN V_Adjust.ItemID = 2 THEN ISNULL(AdjustCharge, 0) ELSE 0 END) AS PeccancyReceivable , -- SUM(CASE WHEN V_ReceivableCharge.ItemID = 3 THEN ReceivableCharge ELSE 0 END) + SUM(CASE WHEN V_Adjust.ItemID = 3 THEN ISNULL(AdjustCharge, 0) ELSE 0 END) AS AccidentReceivable , -- SUM(CASE WHEN V_ReceivableCharge.ItemID = 4 THEN ReceivableCharge ELSE 0 END) + SUM(CASE WHEN V_Adjust.ItemID = 4 THEN ISNULL(AdjustCharge, 0) ELSE 0 END) AS LiquidatedDamagesReceivable , -- SUM(CASE WHEN V_ReceivableCharge.ItemID = 6 THEN ReceivableCharge ELSE 0 END) + SUM(CASE WHEN V_Adjust.ItemID = 6 THEN ISNULL(AdjustCharge, 0) ELSE 0 END) AS ValueAddedServiceReceivable , -- V_ReceivableCharge.PCustomerID FROM ( SELECT VT.ItemID , VT.PCustomerID , SUM(VT.ReceivableCharge) AS ReceivableCharge FROM ( SELECT ( CASE BillTypeID WHEN 1 THEN 1 WHEN 27 THEN 1 WHEN 38 THEN 1 WHEN 11 THEN 1 WHEN 3 THEN 2 WHEN 5 THEN 3 WHEN 4 THEN 4 ELSE 6 END ) AS ItemID , CA.ReceivableCharge - ISNULL(woaCharge, 0) AS ReceivableCharge , PCustomerID FROM TB_PCustomerAccount CA LEFT OUTER JOIN ( SELECT SUM(woaCharge) AS woaCharge , woaBillID , woaBillType FROM TB_WriteOffPCustomerAccountsLog GROUP BY woaBillID , woaBillType HAVING SUM(woaCharge) > 0 ) V1 ON V1.woaBillID = CA.BillID AND V1.woaBillType = CA.BillTypeID WHERE TypeID = 1 AND CA.ReceivableCharge - ISNULL(woaCharge, 0) > 0 AND CA.ReceivableCharge >= 0 -- AND CA.InAccountStatusID = 1 AND CA.BillTypeID <> 45 ) VT GROUP BY VT.ItemID , VT.PCustomerID ) V_ReceivableCharge LEFT OUTER JOIN ( SELECT ( CASE AB.BillTypeID WHEN 1 THEN 1 WHEN 27 THEN 1 WHEN 38 THEN 1 WHEN 3 THEN 2 WHEN 5 THEN 3 WHEN 4 THEN 4 ELSE 6 END ) AS ItemID , CA.PCustomerID , SUM(AdjustCharge) - SUM(ISNULL(V1.woaCharge, 0)) + SUM(ISNULL(V2.woaCharge, 0)) AS AdjustCharge FROM TB_PCustomerAccount CA INNER JOIN dbo.TB_AdjustBill AB ON CA.BillID = AB.BillID AND CA.BillTypeID = 45 LEFT OUTER JOIN ( SELECT SUM(woaCharge) AS woaCharge , woaBillID , woaBillType FROM TB_WriteOffPCustomerAccountsLog GROUP BY woaBillID , woaBillType HAVING SUM(woaCharge) > 0 ) V1 ON CA.BillID = V1.woaBillID AND CA.BillTypeID = V1.woaBillType LEFT OUTER JOIN ( SELECT SUM(woaCharge) AS woaCharge , woaGatheringID , woaGatheringType FROM TB_WriteOffPCustomerAccountsLog GROUP BY woaGatheringID , woaGatheringType HAVING SUM(woaCharge) > 0 ) V2 ON CA.BillID = V2.woaGatheringID AND CA.BillTypeID = V2.woaGatheringType WHERE AdjustCharge > 0 GROUP BY CA.PCustomerID , ( CASE AB.BillTypeID WHEN 1 THEN 1 WHEN 27 THEN 1 WHEN 38 THEN 1 WHEN 3 THEN 2 WHEN 5 THEN 3 WHEN 4 THEN 4 ELSE 6 END ) ) V_Adjust ON V_ReceivableCharge.PCustomerID = V_Adjust.PCustomerID AND V_ReceivableCharge.ItemID = V_Adjust.ItemID GROUP BY V_ReceivableCharge.PCustomerID ) V3 ON V3.PCustomerID = CAB.PCustomerID LEFT OUTER JOIN ( SELECT A.PCustomerID , ( SUM(GatheringCharge - ISNULL(V2.UnWriteOffCharge, 0) + CASE WHEN ( BillTypeID = 11 OR BillTypeID = 45 ) AND ReceivableCharge < 0 THEN -ReceivableCharge ELSE 0 END) - ISNULL(CAB.FreeCharge, 0) ) AS CheckingAccount FROM TB_PCustomerAccount A LEFT OUTER JOIN ( SELECT SUM(woaCharge) AS WriteOffCharge , woaBillID , woaBillType FROM TB_WriteOffPCustomerAccountsLog GROUP BY woaBillID , woaBillType ) V1 ON A.BillID = woaBillID AND A.BillTypeID = woaBillType LEFT OUTER JOIN ( SELECT SUM(woaCharge) AS UnWriteOffCharge , woaGatheringID , woaGatheringType FROM TB_WriteOffPCustomerAccountsLog GROUP BY woaGatheringID , woaGatheringType ) V2 ON A.BillID = woaGatheringID AND A.BillTypeID = woaGatheringType LEFT OUTER JOIN TB_PCustomerAccountBalance CAB ON CAB.PCustomerID = A.PCustomerID WHERE A.InAccountStatusID = 1 GROUP BY A.PCustomerID , CAB.FreeCharge HAVING ( SUM(GatheringCharge - ISNULL(V2.UnWriteOffCharge, 0) + CASE WHEN ( BillTypeID = 11 OR BillTypeID = 45 ) AND ReceivableCharge < 0 THEN -ReceivableCharge ELSE 0 END) - ISNULL(CAB.FreeCharge, 0) ) > 0 ) V4 ON V4.PCustomerID = CAB.PCustomerID LEFT OUTER JOIN TB_ProtocolCustomer C ON C.ProtocolCustomerID = CAB.PCustomerID LEFT OUTER JOIN TB_EmployeeInfo E ON E.EmployID = C.ServiceOperatorID LEFT OUTER JOIN TB_EmployeeInfo E1 ON E1.EmployID = C.OperatorID LEFT OUTER JOIN ( SELECT ProtocolCustomerID , ISNULL(SUM(ISNULL(ConsumeCharge, 0)), 0) AS RentingCharge FROM ( -- SELECT ARF.ProtocolCustomerID , ConsumeCharge = ISNULL(SUM(P.RealCharge), 0) FROM dbo.TB_AutoRentFlow ARF INNER JOIN dbo.TB_Precontract P ON ARF.FlowID = P.RentID WHERE ARF.RentChargePayTypeID = 2 AND ARF.RentStatus IN ( 1, 2, 3 ) AND ARF.FlowID NOT IN ( SELECT BillID FROM TB_PCustomerAccount WHERE BillTypeID = 1 AND InAccountStatusID = 1 ) GROUP BY ARF.ProtocolCustomerID UNION ALL -- SELECT CRB.crbPCustomerID AS ProtocolCustomerID , ConsumeCharge = ISNULL(SUM(CRB.crbReceivableAmount), 0) FROM dbo.TB_ChauffeurRentBill CRB WHERE CRB.crbPCustomerPayWayID = 2 AND CRB.crbRentStatusID IN ( 1, 2, 3 ) AND CRB.crbBillID NOT IN ( SELECT BillID FROM TB_PCustomerAccount WHERE BillTypeID = 27 AND InAccountStatusID = 1 ) GROUP BY CRB.crbPCustomerID UNION ALL -- SELECT LRB.lrbProtocolCustomerID AS ProtocolCustomerID , ConsumeCharge = ISNULL(SUM(LRUCP.lrpcMonthTotalCharge), 0) FROM dbo.TB_LongRentBill LRB LEFT OUTER JOIN dbo.TB_LongRentUseCarProcess LRUCP ON LRUCP.lrpcLongRentBillID = LRB.lrbLongRentBillID WHERE lrpcBalanceStatus = 0 AND lrbProtocolCustomerID > 0 GROUP BY LRB.lrbProtocolCustomerID ) T1 GROUP BY ProtocolCustomerID ) T ON T.ProtocolCustomerID = CAB.PCustomerID LEFT OUTER JOIN ( SELECT PCustomerID , UseCharge = SUM(ISNULL(BC.BalanceGetCharge, 0) - ISNULL(FC.FreezeCharge, 0)) , PreAuthCharge = SUM(ISNULL(SC.Amount, 0)) FROM dbo.TB_PCustomerAssigner PCA LEFT OUTER JOIN ( SELECT CustomerID , BalanceGetCharge = ISNULL(SUM(ISNULL(GatheringCharge, 0)) - SUM(ISNULL(ReceivableCharge, 0)), 0) FROM [dbo].[TB_CustomerAccount] WHERE InAccountStatusID = 1 GROUP BY CustomerID ) BC ON PCA.CustomerID = BC.CustomerID LEFT OUTER JOIN ( SELECT CustomerID , FreezeCharge = ISNULL(SUM(ISNULL(FreezeCharge, 0)), 0) FROM [dbo].[TB_CustomerAccountFreeze] WHERE StatusID = 1 GROUP BY CustomerID ) FC ON PCA.CustomerID = FC.CustomerID LEFT OUTER JOIN ( SELECT CustomerID , Amount = SUM(ISNULL(B.Amount, 0)) FROM dbo.TB_SCardTradeBill A LEFT OUTER JOIN dbo.TB_SCardTradeBillItem B ON A.BillID = B.BillID AND CurrentTradeTypeID = B.TradeTypeID WHERE CurrentTradeTypeID = 3 AND B.AuditTag = 1 AND DATEDIFF(dd, B.TradeTime, GETDATE()) <= 28 GROUP BY CustomerID ) SC ON PCA.CustomerID = SC.CustomerID WHERE ISNULL(BC.BalanceGetCharge, 0) - ISNULL(FC.FreezeCharge, 0) < 0 GROUP BY PCustomerID ) PCAS ON PCAS.PCustomerID = CAB.PCustomerID