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
