public ActionResult GetAdjBills(string CustCode, string MRAgainst, decimal Amount, string isEdit) { var Bills = default(dynamic); string sql = ""; string MrAg = LoadDropDown.GetMrAgainstType(MRAgainst); if (MrAg == null) { MrAg = MRAgainst; //temporary Ashik } if (MrAg == "Sales") { if (isEdit == "1") { Bills = (from mr in _moneyReceiptService.All().ToList() join mre in _moneyReceiptExtService.All().ToList() on mr.MRId equals mre.MRId where mr.CustCode == CustCode select new { SaleNo = mre.SaleNo, SaleDate = mr.MRDate, BillAmount = mre.Amount, IsPaid = true }).ToList(); } else if (isEdit == "ChqEdit") { sql = string.Format("select sm.IsPaid, sm.NetAmount as Billamount, sm.SaleNo, sm.SaleDate , (sm.NetAmount - isnull(sum(cre.BillAmount),0)) as Billamt from SalesMain sm left join ChequeReceiptExt cre on cre.BillNo = sm.SaleNo where sm.CustCode = " + CustCode + " group by sm.IsPaid, cre.BillNo, sm.NetAmount, sm.SaleNo, sm.SaleDate order by sm.SaleNo "); //sql = string.Format("select sm.IsPaid, sm.NetAmount as Billamount, sm.SaleNo, sm.SaleDate , (sm.NetAmount - isnull(sum(cre.BillAmount),0)) as Billamt from SalesMain sm left join ChequeReceiptExt cre on cre.BillNo = sm.SaleNo where sm.CustCode = " + CustCode + " and sm.Ispaid = 'true' and Billamt = 0 group by sm.IsPaid, cre.BillNo, sm.NetAmount, sm.SaleNo, sm.SaleDate, cre.BillAmount order by sm.SaleNo "); IEnumerable <billShow> BillLst; using (AcclineERPContext dbContext = new AcclineERPContext()) { BillLst = dbContext.Database.SqlQuery <billShow>(sql).ToList(); Bills = BillLst; } } else { //decimal MrExtAmt = 0; //Bills = (from sm in _salesMainService.All().ToList() // join mre in _moneyReceiptExtService.All().ToList() on sm.SaleNo equals mre.SaleNo into SmMre // from leftJ in SmMre.DefaultIfEmpty() // where sm.CustCode == CustCode && sm.IsPaid == false // //group leftJ by leftJ.SaleNo into G // select new // { // SaleNo = sm.SaleNo, // SaleDate = sm.SaleDate, // BillAmount = sm.NetAmount - (MrExtAmt = (leftJ == null) ? 0 : leftJ.Amount) // }).GroupBy(x => x.SaleNo).ToList(); sql = string.Format("select sm.SaleNo, sm.SaleDate, (sm.NetAmount - (select isnull(sum(mre.Amount),0) as Billamount from MoneyReceiptExt mre where SaleNo = sm.SaleNo)) as Billamount from SalesMain sm where sm.CustCode = " + CustCode + " and sm.IsPaid = 'false' group by sm.NetAmount, sm.SaleNo, sm.SaleDate order by sm.SaleNo "); IEnumerable <billShow> BillLst; using (AcclineERPContext dbContext = new AcclineERPContext()) { BillLst = dbContext.Database.SqlQuery <billShow>(sql).ToList(); Bills = BillLst; } } } //else if (true) //{ //} return(Json(new { data = Bills }, JsonRequestBehavior.AllowGet)); }