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