public async Task <IActionResult> GenerateCustomerStatement(CUSTOMERSTATEMENTFILTER filter)
{
if (filter != null)
{
if (filter.CompanyCode != "404")
{
var model = await qneReportService.GenerateCustomerStatement(filter);
if (model != null)
{
return(Ok(model));
}
else
{
return(BadRequest());
}
}
}
return(BadRequest());
}
public async Task <List <CUSTOMERSTATEMENTRESULT> > GenerateCustomerStatement(CUSTOMERSTATEMENTFILTER filter)
{
using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode)))
{
string sql = @"SELECT
DR.DeliveryOrderDate,
SI.OurDONO,
INV.InvoiceCode,
INV.TotalAmountLocal,
(SELECT SUM(ARMatched.Amount) FROM ARMatched WHERE ARMatched.PayForCode = INV.InvoiceCode)
AS PAIDAMOUNT,
DEBTOR.CompanyCode,
DEBTOR.CompanyName AS DEBTORNAME,
Agent.Name,
Areas.AreaCode AS DEBTORARECODE,
Areas.Description AS DEBTORAREADESCRIPTION,
CATEGORY.CategoryCode AS DEBTORCATEGORYCODE,
INV.InvoiceDate AS INVOICEDATE,
Terms.Description as TERMS,
DEBTOR.Address1,
DEBTOR.Address2,
DEBTOR.Address3,
DEBTOR.Address4
FROM Invoices AS INV
INNER JOIN Debtors as DEBTOR
ON INV.DebtorId = DEBTOR.Id
LEFT JOIN DebtorCategory AS CATEGORY
ON DEBTOR.CategoryId = CATEGORY.Id
LEFT JOIN Areas
ON DEBTOR.AreaId = Areas.Id
LEFT JOIN SalesPersons AS Agent
ON INV.SalesPersonId = Agent.Id
LEFT JOIN Terms AS Terms
ON INV.TermId = Terms.Id
LEFT JOIN SalesInvoices as SI
ON SI.SalesInvoiceCode = INV.InvoiceCode
LEFT JOIN DeliveryOrders as DR
ON DR.DeliveryOrderCode = SI.OurDONO
WHERE INV.DebtorId LIKE @DEBTORCODE
AND INV.SalesPersonId LIKE @SALESPERSONCODE
AND DEBTOR.AreaId LIKE @AREACODE
AND DEBTOR.CategoryId LIKE @DEBTORCATID
AND INV.InvoiceDate BETWEEN @DATEFROM AND @DATETO
AND INV.IsCancelled <> 1
AND ((SELECT ISNULL(SUM(ARMATCHED.Amount),0) FROM ARMATCHED
WHERE ARMatched.PayForCode = INV.InvoiceCode) < INV.TotalAmountLocal
OR(SELECT
SUM(ARMatched.Amount)
FROM ARMatched
JOIN Receipts
ON ARMatched.ARCode = Receipts.ReceiptCode
WHERE ARMatched.PayForCode = INV.InvoiceCode AND Receipts.IsPostDatedCheque <> 1)
> 0
);";
var fromDate = filter.DateFrom == null?Convert.ToDateTime("1900-01-01") : filter.DateFrom;
var toDate = filter.DateTo == null ? DateTime.Now : filter.DateTo;
var p = new DynamicParameters();
p.Add("@DEBTORCODE", filter.DebtortId);
p.Add("@SALESPERSONCODE", filter.SalesPersonId);
p.Add("@AREACODE", filter.AreaId);
p.Add("@DEBTORCATID", filter.DebtorCategoryId);
p.Add("@DATEFROM", fromDate);
p.Add("@DATETO", toDate);
var data = await con.QueryAsync <CUSTOMERSTATEMENTRESULT>(sql, p);
string PDC_MATCHED_QUERY = @"SELECT ARR.TotalAmountLocal,
ARR.ChequeDate,
ARR.ReferenceNo
FROM ARMATCHED AS ARM
LEFT JOIN RECEIPTS ARR
ON ARM.ARCode = ARR.ReceiptCode
WHERE ARM.PayForCode = @INVOICECODE AND ARR.IsPostDatedCheque = '1'";
var CUSTOMERSTATEMENTASLIST = data.ToList();
for (int i = 0; i < CUSTOMERSTATEMENTASLIST.Count; i++)
{
var inv = CUSTOMERSTATEMENTASLIST[i];
string INVOICECODE = CUSTOMERSTATEMENTASLIST[i].InvoiceCode;
var param = new DynamicParameters();
param.Add("@INVOICECODE", INVOICECODE);
var reader = con.ExecuteReader(PDC_MATCHED_QUERY, param);
while (reader.Read())
{
inv.matchedPDCs.Add(new MATCHED_PDC()
{
DETAILS = reader["ReferenceNo"].ToString().Trim(),
PDC_AMOUNT = !String.IsNullOrEmpty(reader["TotalAmountLocal"].ToString().Trim()) ? Convert.ToDouble(reader["TotalAmountLocal"].ToString().Trim()) : 0,
DUEDATE = !String.IsNullOrEmpty(reader["ChequeDate"].ToString().Trim()) ? Convert.ToDateTime(reader["ChequeDate"].ToString().Trim()) : (DateTime?)null
});
}
if (inv.matchedPDCs.Count == 0)
{
DateTime invoiceDate = Convert.ToDateTime(inv.INVOICEDATE);
DateTime dateToday = Convert.ToDateTime(DateTime.Now.ToShortDateString());
var days = Convert.ToInt32(dateToday.Subtract(invoiceDate).TotalDays);
inv.AGING = days;
}
else
{
foreach (var m in inv.matchedPDCs)
{
DateTime invoiceDate = Convert.ToDateTime(inv.INVOICEDATE);
DateTime matchedDate = Convert.ToDateTime(m.DUEDATE);
var days = Convert.ToInt32(matchedDate.Subtract(invoiceDate).TotalDays);
m.AGING = days;
// matchedAmountTotal += m.MATCHEDAMOUNT;
}
}
}
CUSTOMERSTATEMENTASLIST = CUSTOMERSTATEMENTASLIST.Where(i => (i.PAIDAMOUNT < i.TotalAmountLocal) || (i.matchedPDCs.Count > 0)).ToList();
return(CUSTOMERSTATEMENTASLIST);
}
}
