SELECT		c.Name,
		ib.ImportBatchID AS BatchID,
		CONVERT(VARCHAR(10), ib.UpdatedOn, 103) AS LoadedOn,
		COUNT(d.DebtID) AS Reg_Count,
		SUM(d.DebtValue) AS Reg_Value,
		SUM(COALESCE(Peso.Adjustments, 0)) AS Adjustments,
		SUM(COALESCE(Yeti.OBC_Num, 0)) AS OBC_Num,
		SUM(COALESCE(Yeti.ICC_Num, 0)) AS ICC_Num,
		SUM(COALESCE(Yeti.ItemsMailed, 0)) AS ItemsMailed,
		SUM(COALESCE(Yak.ComRate,
		SUM(COALESCE(h.Girobank, 0)) AS Girobank,
		SUM(COALESCE(h.StandingOrder, 0)) AS StandingOrder,
		SUM(COALESCE(h.CreditCard, 0)) AS CreditCard,
		SUM(COALESCE(h.DirectDebit, 0)) AS DirectDebit,
		SUM(COALESCE(h.Cheque, 0)) AS Cheque,
		SUM(COALESCE(h.PostalOrder, 0)) AS PostalOrder,
		SUM(COALESCE(h.Cash, 0)) AS Cash,
		SUM(COALESCE(h.BankersDraft, 0)) AS BankersDraft
FROM		dbo.Client AS c WITH (NOLOCK)
INNER JOIN	dbo.Debt AS d WITH (NOLOCK) ON d.ClientID = c.ClientID
INNER JOIN	dbo.ImportBatchItem AS ib WITH (NOLOCK) ON ib.ItemID = d.DebtID
LEFT JOIN	(
			SELECT		bi.ImportBatchID,
					SUM(CONVERT(DECIMAL(10,2),	CASE 
										WHEN dp.ReceivedByID = 1 THEN dp.Amount * (tF.Rate /100)
										WHEN dp.ReceivedByID = 2 THEN dp.Amount * (tD.Rate /100)
										ELSE dp.Amount * ((tF.Rate + tFe.Rate) / 100)
									END) AS ComRate
			FROM		dbo.DebtPayment AS dp
			INNER JOIN	dbo.Debt AS d ON d.DebtID = dp.DebtID
			INNER JOIN	dbo.ImportBatchItem AS bi ON bi.ItemID = dp.DebtID
			LEFT JOIN	dbo.mTrackerFeeChange tF ON tF.ClientID = d.ClientID
						AND tF.ContractID = d.ContractID
						AND tF.RateType = dp.ReceivedByID
						AND dp.PaymentOn >= tF.StartDate
						AND dp.PaymentOn <= COALESCE(tF.EndDate, dp.PaymentOn)
			LEFT JOIN	dbo.mTrackerDirectChange AS tD ON tD.ClientID = d.ClientID 
						AND tD.ContractID = d.ContractID
						AND tD.RateType = dp.ReceivedByID
						AND dp.PaymentOn >= tD.StartDate
						AND dp.PaymentOn <= COALESCE(tD.EndDate, dp.PaymentOn)
			LEFT JOIN	dbo.mTrackerFieldChange AS tFe ON tFe.ClientID = d.ClientID
						AND tFe.ContractID = d.ContractID
						AND tFe.RateType = dp.ReceivedByID
						AND dp.PaymentOn >= tFe.StartDate
						AND dp.PaymentOn <= COALESCE(tFe.EndDate, dp.PaymentOn)
			GROUP BY	bi.ImportBatchID
		) AS Yak ON Yak.ImportBatchID = ib.ImportBatchID
LEFT JOIN	(
			SELECT		DebtID,
					SUM(CASE WHEN Note LIKE 'OBC:%' THEN 1 ELSE 0 END) AS OBC_Num,
					SUM(CASE WHEN Note LIKE 'ICC:%' THEN 1 ELSE 0 END) AS ICC_Num,
					SUM(CASE WHEN Note LIKE '%Letter%' AND UserName = 'Letter Server' THEN 1 ELSE 0 END) AS ItemsMailed
			FROM		dbo.DebtHistory WITH (NOLOCK)
			GROUP BY	DebtID
		) AS Yeti ON Yeti.DebtID = d.DebtID
LEFT JOIN	(
			SELECT		DebtID,
					SUM(Amount) AS Adjustments
			FROM		dbo.DebtAdjustment WITH (NOLOCK)
			GROUP BY	DebtID
		) AS Peso ON Peso.DebtID = d.DebtID
LEFT JOIN	(
			SELECT		Debt.DebtID,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 1 THEN Debt.OutstandingValue ELSE 0 END) AS Girobank,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 2 THEN Debt.OutstandingValue ELSE 0 END) AS StandingOrder,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 3 THEN Debt.OutstandingValue ELSE 0 END) AS CreditCard,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 4 THEN Debt.OutstandingValue ELSE 0 END) AS DirectDebit,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 5 THEN Debt.OutstandingValue ELSE 0 END) AS Cheque,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 6 THEN Debt.OutstandingValue ELSE 0 END) AS PostalOrder,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 7 THEN Debt.OutstandingValue ELSE 0 END) AS Cash,
					SUM(CASE WHEN Debt.AgreementPaymentMethodID = 8 THEN Debt.OutstandingValue ELSE 0 END) AS BankersDraft
			FROM		Debt WITH (NOLOCK)
			INNER JOIN	DebtStatus WITH (NOLOCK) ON DebtStatus.DebtStatusID = Debt.Status
			WHERE		DebtStatus.Description LIKE '%Arrangement%'
					AND Debt.OutstandingValue > 0
			GROUP BY	Debt.DebtID
		) AS h ON h.DebtID = d.DebtID
WHERE		c.ClientID = @ClientID
		OR @ClientID IS NULL
GROUP BY	c.Name,
		ib.ImportBatchID,
		CONVERT(VARCHAR(10), ib.UpdatedOn,103),
		DATENAME(MONTH, ib.UpdatedOn) + ' ' + DATENAME(Year, ib.UpdatedOn)
ORDER BY	ib.ImportBatchID