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