public async Task <IActionResult> GenerateCategorizeReport(CATEGORIZEFILTER filter) { if (filter != null) { if (filter.CompanyCode != "404") { var model = await qneReportService.GenerateCategorizeReport(filter); if (model != null) { return(Ok(model)); } else { return(BadRequest()); } } } return(BadRequest()); }
public async Task <CATEGORIZE_FINAL> GenerateCategorizeReport(CATEGORIZEFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { CATEGORIZE_FINAL FINAL_RESULT = new CATEGORIZE_FINAL(); string category = filter.category; string whereClause = category == "%%" ? "A.StockId IS NOT NULL " : category == "1" ? "SWINE = '1' AND POULTRY = '0' AND COMMON = '0'" : category == "2" ? "SWINE = '0' AND POULTRY = '1' AND COMMON = '0'" : category == "3" ? "SWINE = '0' AND POULTRY = '0' AND COMMON = '1'" : "A.StockId IS NOT NULL"; //Console.WriteLine(whereClause); var p = new DynamicParameters(); p.Add("@SalesPerson", filter.salesPersonCode); p.Add("@dateFrom", filter.DateFrom); p.Add("@dateTo", filter.DateTo); string queryStringCredit = ""; string queryStringNonCredit = ""; if (filter.WithVat) { queryStringCredit = @"SELECT G.StaffCode AS SALESPERSONCODE,B.CNDate AS DELIVERYORDERDATE,B.CNCode AS CREDITNOTECODE, B.DEBTORNAME AS DEBTOR,d.StockName AS DESCRIPTION, CONVERT(BIT, (CASE WHEN SWINE IS NULL OR SWINE = 0 THEN 0 ELSE 1 END)) AS SWINE, CONVERT(BIT, (CASE WHEN COMMON IS NULL OR COMMON = 0 THEN 0 ELSE 1 END)) AS COMMON, CONVERT(BIT, (CASE WHEN POULTRY IS NULL OR POULTRY = 0 THEN 0 ELSE 1 END)) AS POULTRY, A.QTY,F.UOMCode AS UOM,A.UNITPRICE,A.AMOUNT-A.TaxAmount AS AMOUNT FROM SalesCNDetails A LEFT JOIN SalesCN B ON A.SalesCNId = B.Id LEFT JOIN StockTransactions C ON c.DocumentId = a.Id AND c.DocType = 'SalesCN' AND B.CNCode = C.DocumentCode LEFT JOIN Stocks D ON D.Id = A.StockId LEFT JOIN CATEGORIZED_PRODUCTS E ON E.STOCKCODE = D.StockCode LEFT JOIN UOMs F ON F.Id = A.UOMId LEFT JOIN SalesPersons G ON G.id = B.SalesPersonId WHERE " + whereClause + " AND G.StaffCode LIKE @SalesPerson AND B.CNDate BETWEEN @dateFrom AND @dateTo ORDER BY B.CNDate"; queryStringNonCredit = @"SELECT F.StaffCode AS SALESPERSONCODE,B.SALESINVOICEDATE,B.SALESINVOICECODE,B.DEBTORNAME AS DEBTOR,A.DESCRIPTION, CONVERT(BIT, (CASE WHEN SWINE IS NULL OR SWINE = 0 THEN 0 ELSE 1 END)) AS SWINE, CONVERT(BIT, (CASE WHEN COMMON IS NULL OR COMMON = 0 THEN 0 ELSE 1 END)) AS COMMON, CONVERT(BIT, (CASE WHEN POULTRY IS NULL OR POULTRY = 0 THEN 0 ELSE 1 END)) AS POULTRY, A.QTY,E.UOMCode AS UOM,A.UNITPRICE,A.AMOUNT-A.TaxAmount AS AMOUNT FROM SalesInvoiceDetails A LEFT JOIN SalesInvoices B ON A.SalesInvoiceId = B.Id LEFT JOIN Stocks AS D ON D.Id = A.StockId LEFT JOIN CATEGORIZED_PRODUCTS C ON C.STOCKCODE = D.StockCode LEFT JOIN UOMs AS E ON E.Id = A.UOMId LEFT JOIN SalesPersons AS F ON B.SalesPersonId = F.Id WHERE " + whereClause + " AND F.StaffCode LIKE @SalesPerson" + " AND B.SALESINVOICEDATE BETWEEN @dateFrom AND @dateTo ORDER BY B.SALESINVOICEDATE ASC"; } else { queryStringCredit = @"SELECT G.StaffCode AS SALESPERSONCODE,B.CNDate AS DELIVERYORDERDATE,B.CNCode AS CREDITNOTECODE, B.DEBTORNAME AS DEBTOR,d.StockName AS DESCRIPTION, CONVERT(BIT, (CASE WHEN SWINE IS NULL OR SWINE = 0 THEN 0 ELSE 1 END)) AS SWINE, CONVERT(BIT, (CASE WHEN COMMON IS NULL OR COMMON = 0 THEN 0 ELSE 1 END)) AS COMMON, CONVERT(BIT, (CASE WHEN POULTRY IS NULL OR POULTRY = 0 THEN 0 ELSE 1 END)) AS POULTRY, A.QTY,F.UOMCode AS UOM,A.UNITPRICE,A.AMOUNT FROM SalesCNDetails A LEFT JOIN SalesCN B ON A.SalesCNId = B.Id LEFT JOIN StockTransactions C ON c.DocumentId = a.Id AND c.DocType = 'SalesCN' AND B.CNCode = C.DocumentCode LEFT JOIN Stocks D ON D.Id = A.StockId LEFT JOIN CATEGORIZED_PRODUCTS E ON E.STOCKCODE = D.StockCode LEFT JOIN UOMs F ON F.Id = A.UOMId LEFT JOIN SalesPersons G ON G.id = B.SalesPersonId WHERE " + whereClause + " AND G.StaffCode LIKE @SalesPerson AND B.CNDate BETWEEN @dateFrom AND @dateTo ORDER BY B.CNDate"; queryStringNonCredit = @"SELECT F.StaffCode AS SALESPERSONCODE,B.SALESINVOICEDATE,B.SALESINVOICECODE,B.DEBTORNAME AS DEBTOR,A.DESCRIPTION, CONVERT(BIT, (CASE WHEN SWINE IS NULL OR SWINE = 0 THEN 0 ELSE 1 END)) AS SWINE, CONVERT(BIT, (CASE WHEN COMMON IS NULL OR COMMON = 0 THEN 0 ELSE 1 END)) AS COMMON, CONVERT(BIT, (CASE WHEN POULTRY IS NULL OR POULTRY = 0 THEN 0 ELSE 1 END)) AS POULTRY, A.QTY,E.UOMCode AS UOM,A.UNITPRICE,A.AMOUNT FROM SalesInvoiceDetails A LEFT JOIN SalesInvoices B ON A.SalesInvoiceId = B.Id LEFT JOIN Stocks AS D ON D.Id = A.StockId LEFT JOIN CATEGORIZED_PRODUCTS C ON C.STOCKCODE = D.StockCode LEFT JOIN UOMs AS E ON E.Id = A.UOMId LEFT JOIN SalesPersons AS F ON B.SalesPersonId = F.Id WHERE " + whereClause + " AND F.StaffCode LIKE @SalesPerson" + " AND B.SALESINVOICEDATE BETWEEN @dateFrom AND @dateTo ORDER BY B.SALESINVOICEDATE ASC"; } FINAL_RESULT.CATEGORIZERESULTCREDITS = await con.QueryAsync <CATEGORIZERESULT>(queryStringCredit, p, commandType : CommandType.Text); FINAL_RESULT.CATEGORIZERESULTS = await con.QueryAsync <CATEGORIZERESULT>(queryStringNonCredit, p, commandType : CommandType.Text); var SUMMARY_AGENT = FINAL_RESULT.CATEGORIZERESULTS.GroupBy(X => X.SALESPERSONCODE).Select(g => g.First()).ToList(); List <CATEGORIZE_SUMMARY_AGENT> list = new List <CATEGORIZE_SUMMARY_AGENT>(); foreach (var item in SUMMARY_AGENT) { list.Add(new CATEGORIZE_SUMMARY_AGENT { SALESPERSONCODE = item.SALESPERSONCODE }); } FINAL_RESULT.SUMMARY_FOR_AGENTS = list; return(FINAL_RESULT); } }