public async Task <IEnumerable <BATCHNOBALANCEDETAILSRESULT> > GenerateBatchNoBalDetails(BATCHNOBALANCEDETAILSFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { string sql = @"SELECT A.*,B.StockName FROM dbo.FN_BatchNoBalanceDetails(@DateFrom,@DateTo,@UnsalablePeriod,@IncludeZeroBalance) AS A LEFT JOIN Stocks AS B ON A.StockId=B.Id WHERE A.StockGroupCode LIKE @STOCKGROUP AND A.LocationCode LIKE @LOCATIONCODE"; 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(); int IncludeZeroBal = filter.IncludeZeroBalance == true ? 1 : 0; p.Add("@STOCKGROUP", filter.StockGroup); p.Add("@LOCATIONCODE", filter.Location); p.Add("@DateFrom", fromDate); p.Add("@DateTo", toDate); p.Add("@UnsalablePeriod", 6); p.Add("@IncludeZeroBalance", IncludeZeroBal); var data = await con.QueryAsync <BATCHNOBALANCEDETAILSRESULT>(sql, p); return(data); } }
public async Task <int> InsertUpdateFGMASTERFILE(FGMASTERFILEFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.companyCode))) { string sql = ""; var p = new DynamicParameters(); p.Add("@STCODE", filter.StockCode); if (filter.CATID != null || filter.CATID > 0) { switch (filter.Category) { case 'S': sql = "UPDATE CATEGORIZED_PRODUCTS SET SWINE = @SWINE WHERE STOCKCODE = @STCODE"; p.Add("@SWINE", filter.IsChecked); break; case 'C': sql = "UPDATE CATEGORIZED_PRODUCTS SET COMMON = @COMMON WHERE STOCKCODE = @STCODE"; p.Add("@COMMON", filter.IsChecked); break; case 'P': sql = "UPDATE CATEGORIZED_PRODUCTS SET POULTRY = @POULTRY WHERE STOCKCODE = @STCODE"; p.Add("@POULTRY", filter.IsChecked); break; } return(await con.ExecuteAsync(sql, p, commandType : CommandType.Text)); } else { sql = @"INSERT INTO CATEGORIZED_PRODUCTS (STOCKCODE,SWINE,POULTRY,COMMON) VALUES (@STCODE,@SWINE,@POULTRY,@COMMON)"; switch (filter.Category) { case 'S': p.Add("@SWINE", "1"); p.Add("@POULTRY", "0"); p.Add("@COMMON", "0"); break; case 'C': p.Add("@SWINE", "0"); p.Add("@POULTRY", "0"); p.Add("@COMMON", "1"); break; case 'P': p.Add("@SWINE", "0"); p.Add("@POULTRY", "1"); p.Add("@COMMON", "0"); break; } } return(await con.ExecuteAsync(sql, p, commandType : CommandType.Text)); //sql = @"UPDATE CATEGORIZED_PRODUCTS SET {0}=@Status WHERE STOCKCODE = @StockCode // IF @@ROWCOUNT=0 // INSERT INTO CATEGORIZED_PRODUCTS (STOCKCODE) VALUES(@StockCode);"; } }
public async Task <IEnumerable <STOCKLEDGERWITHBATCHRESULT> > GenerateStockLedgerWBatch(STOCKLEDGERWITHBATCHFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { string sql = @"SELECT A.*,B.BatchNoExpiryDate,B.BatchNoManufacturingDate FROM dbo.FN_StockLedgerWithBatch(@DateFrom,@DateTo,@IncludeZero,@IncludeInactive,@IncludeStockTransfer,@StockCodes,@Location) AS A JOIN StockBatchNumbers AS B ON A.StockBatchNumberId = B.Id WHERE A.StockBatchNumberId LIKE @BatchId AND A.GroupCode LIKE @GroupCode"; var p = new DynamicParameters(); p.Add("@DateFrom", filter.DateFrom); p.Add("@DateTo", filter.DateTo); p.Add("@IncludeZero", filter.IncludeZero); p.Add("@IncludeInactive", filter.IncludeInactive); p.Add("@IncludeStockTransfer", filter.IncludeStockTransfer); p.Add("@StockCodes", filter.StockCodes); p.Add("@BatchId", filter.BatchId); p.Add("@Location", filter.LocationCode); p.Add("@GroupCode", filter.StockGroup); var model = await con.QueryAsync <STOCKLEDGERWITHBATCHRESULT>(sql, p); return(model); //if(filter.BatchId != "%%") //{ // model.Where(x => x.StockBatchNumberId == Guid.Parse(filter.BatchId)); //} //return model.ToList(); } }
public async Task <IEnumerable <stockbatchnumbers> > GetBatchNumByStockId(string companyCode, Guid StockId) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(companyCode))) { const string sql = @"SELECT Id,StockId,BatchNo FROM StockBatchNumbers WHERE StockId = @StockId"; return(await con.QueryAsync <stockbatchnumbers>(sql, new { StockId }, commandType : CommandType.Text)); } }
public async Task <IEnumerable <StockIssue> > GetStockIssuesAsync(string companyCode) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(companyCode))) { const string sql = @"SELECT Id,StockOutCode FROM StockOuts ORDER BY StockOutCode DESC"; return(await con.QueryAsync <StockIssue>(sql, commandType : CommandType.Text)); } }
public async Task <IEnumerable <DebtorCategory> > GetDebtorCategory(string companyCode) { var sqlcon = QNEConnectionString.ChooseConnection(companyCode); using (IDbConnection con = new SqlConnection(sqlcon)) { var sql = "SELECT Id,Description FROM DebtorCategory ORDER BY Description"; var model = await con.QueryAsync <DebtorCategory>(sql, commandType : CommandType.Text); return(model); } }
public async Task <IEnumerable <SalesPersons> > GetSalesPersons(string companyCode) { var sqlcon = QNEConnectionString.ChooseConnection(companyCode); using (IDbConnection con = new SqlConnection(sqlcon)) { var sql = "SELECT Id,Name,StaffCode FROM SalesPersons ORDER BY StaffCode"; var model = await con.QueryAsync <SalesPersons>(sql, commandType : CommandType.Text); return(model); } }
public async Task <IEnumerable <StockLocations> > GetStockLocations(string companyCode) { var sqlcon = QNEConnectionString.ChooseConnection(companyCode); using (IDbConnection con = new SqlConnection(sqlcon)) { var sql = "SELECT * FROM StockLocations"; var model = await con.QueryAsync <StockLocations>(sql, commandType : CommandType.Text); return(model); } }
public async Task <IEnumerable <NEAREXPIRYRESULT> > GenerateNearExpiry(NEAREXPIRYFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { string sql = "SELECT * FROM dbo.FN_NearExpiryItems(@AsofDate,@StockCodes,@Location)"; var p = new DynamicParameters(); p.Add("@AsofDate", filter.AsOfDate); p.Add("@StockCodes", filter.StockCodes); p.Add("@Location", filter.LocationCode); var res = await con.QueryAsync <NEAREXPIRYRESULT>(sql, p); return(res); } }
public async Task <FGMASTERFILEMODEL> GetFGByCode(string companyCode, string stockCode) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(companyCode))) { var sql = @"SELECT B.Id AS CATID,A.StockCode,A.StockName, CONVERT(BIT, (CASE WHEN B.SWINE IS NULL OR B.SWINE = 0 THEN 0 ELSE 1 END)) AS SWINE, CONVERT(BIT, (CASE WHEN B.COMMON IS NULL OR B.COMMON = 0 THEN 0 ELSE 1 END)) AS COMMON, CONVERT(BIT, (CASE WHEN B.POULTRY IS NULL OR B.POULTRY = 0 THEN 0 ELSE 1 END)) AS POULTRY FROM Stocks AS A LEFT JOIN CATEGORIZED_PRODUCTS AS B ON A.StockCode = B.STOCKCODE WHERE A.STOCKCODE = @STCODE"; DynamicParameters p = new DynamicParameters(); p.Add("@STCODE", stockCode); var model = await con.QuerySingleAsync <FGMASTERFILEMODEL>(sql, p, commandType : CommandType.Text); return(model); } }
public async Task <IEnumerable <STOCKISSUEDETAILSRESULT> > GenerateStockissuesDetail(STOCKISSUEFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { string sql = @"select B.StockCode,B.StockName,D.BatchNo,A.Qty,C.UOMCode,A.Price,A.Amount from StockOutDetails AS A LEFT JOIN Stocks AS B ON A.StockId = B.Id LEFT JOIN UOMs AS C ON C.StockId = A.StockId LEFT JOIN StockBatchNumbers AS D ON A.StockBatchNumberId = D.Id WHERE A.StockOutId = @StockIssueId"; var res = await con.QueryAsync <STOCKISSUEDETAILSRESULT>(sql, new { filter.StockIssueId }); return(res); } }
public async Task <IEnumerable <FGMASTERFILEMODEL> > GetFGMasterFile(string companyCode) { var sqlcon = QNEConnectionString.ChooseConnection(companyCode); using (IDbConnection con = new SqlConnection(sqlcon)) { var sql = @"SELECT B.Id AS CATID,A.StockCode,A.StockName, CONVERT(BIT, (CASE WHEN B.SWINE IS NULL OR B.SWINE = 0 THEN 0 ELSE 1 END)) AS SWINE, CONVERT(BIT, (CASE WHEN B.COMMON IS NULL OR B.COMMON = 0 THEN 0 ELSE 1 END)) AS COMMON, CONVERT(BIT, (CASE WHEN B.POULTRY IS NULL OR B.POULTRY = 0 THEN 0 ELSE 1 END)) AS POULTRY FROM Stocks AS A LEFT JOIN CATEGORIZED_PRODUCTS AS B ON A.StockCode = B.STOCKCODE WHERE A.StockCode LIKE 'FG%'"; var model = await con.QueryAsync <FGMASTERFILEMODEL>(sql, commandType : CommandType.Text); return(model); } }
public async Task <IEnumerable <STOCKLEDGERINQUIRYRESULT> > GenerateStockLedgerInq(STOCKLEDGERINQUIRYFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { string sql = "SELECT * FROM dbo.FN_StockLedgerInquiry(@DateFrom,@DateTo,@IncludeGST,@IncludeZero,@IncludeInactive,@StockCodeFrom,@StockCodeTo,@Location,@IncludeStockTransfer)"; var p = new DynamicParameters(); p.Add("@DateFrom", filter.DateFrom); p.Add("@DateTo", filter.DateTo); p.Add("@IncludeGST", filter.IncludeGST); p.Add("@IncludeZero", filter.IncludeZero); p.Add("@IncludeInactive", filter.IncludeInactive); p.Add("@StockCodeFrom", filter.StockCodeFrom); p.Add("@StockCodeTo", filter.StockCodeTo); p.Add("@Location", filter.LocationCode); p.Add("@IncludeStockTransfer", filter.IncludeStockTransfer); var res = await con.QueryAsync <STOCKLEDGERINQUIRYRESULT>(sql, p); return(res); } }
public async Task <IEnumerable <SALESANALYSISRESULT> > GenerateSalesAnalysis(SALESANALYSISFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.CompanyCode))) { string sql = "select * from dbo.FN_SalesAnalysis(@DateFrom,@DateTo,@IncludeGST,@IncludeDN,@includeCN,@includeCS,@stockControlOnly)"; 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("@DateFrom", fromDate); p.Add("@DateTo", toDate); p.Add("@IncludeGST", filter.IncludeGST); p.Add("@IncludeDN", filter.IncludeDN); p.Add("@includeCN", filter.includeCN); p.Add("@includeCS", filter.includeCS); p.Add("@stockControlOnly", filter.stockControlOnly); var res = await con.QueryAsync <SALESANALYSISRESULT>(sql, p); return(res); } }
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); } }
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); } }