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); } }