public IList<IncreaseCreditLimitModel> getInfoIncreaseCrLimit(int month, int year,List<string> CustomerIDList)
{
IList<IncreaseCreditLimitModel> result = new List<IncreaseCreditLimitModel>();
objF42071 = new F42071Repository();
objF03021 = new F03021Repository();
objF03020 = new F03020Repository();
decimal TotSales = 0;
decimal TotRec = 0;
foreach (string custID in CustomerIDList)
{
ARTrackingListModel ARData = new ARTrackingListModel();
ARData = getARTrackingByCustomerID(month, year, custID);
IQueryable<F03020> objARRec_H = objF03020.GetQuery();
var Totdata = from sh in objF42071.GetAll().Where(x => x.CustomerID.Equals(custID) && x.PeriodYear == DateTime.Today.Year)
group sh by new { sh.CustomerID } into g
select new
{
customerID = g.Key.CustomerID,
TotSales = g.Sum(x => x.TotCost01 + x.TotCost02 + x.TotCost03 + x.TotCost04 + x.TotCost05 + x.TotCost06
+ x.TotCost07 + x.TotCost08 + x.TotCost09 + x.TotCost10 + x.TotCost11 + x.TotCost12)
};
//objARRec_H = objARRec_H.Where(x => x.AdvReceipt.Equals("N"));
objARRec_H = objARRec_H.Where(x => x.CustomerID.Equals(custID));
var ARReceipt = from h in objARRec_H.AsEnumerable()
where h.CustomerID.Equals(custID) && h.ReceiptDate.Year == DateTime.Today.Year
group h by new { h.CustomerID } into g
select new
{
CustomerID = g.Key.CustomerID,
TotReceiptAmtC = g.Sum(x => x.ReceiptAmt) + g.Sum(x => x.WriteOffAmt),
TotReceiptAmtLocal = g.Sum(x => x.ReceiptAmtLocal) + g.Sum(x => x.WriteOffAmtLocal)
};
if (Totdata.Count() > 0)
TotSales = Totdata.FirstOrDefault().TotSales;
else
TotSales = 0;
if (ARReceipt.Count() > 0)
TotRec = ARReceipt.FirstOrDefault().TotReceiptAmtLocal;
else
TotRec = 0;
IncreaseCreditLimitModel ICL = new IncreaseCreditLimitModel();
ICL.CustomerID = ARData.CustomerID;
ICL.CustomerName = ARData.CustomerName;
ICL.CreditLimit = ARData.CreditLimit;
ICL.CreditTerm = ARData.PaymentABC;
ICL.TotalOutstanding = ARData.TotOutstanding;
ICL.Over90 = ARData.Over90;
ICL.Over120 = ARData.Over120;
ICL.PTerm = ARData.PTerm;
ICL.TotalSales = TotSales;
ICL.TotalReceipt = TotRec;
ICL.LastReceiptDate = ARData.LastReceiptDate;
result.Add(ICL);
}
return result;
}
public IList<ARAgingDetailModel> getARAgingDetail(DateTime filterDate, string filterCustomerID)
{
#region "Declaration"
List<ARAgingDetailModel> entResult = new List<ARAgingDetailModel>();
#endregion
#region "Instance Repository"
objF01030 = new F01030Repository();
objF03010 = new F03010Repository();
objF03090 = new F03090Repository();
objF03020 = new F03020Repository();
objF03021 = new F03021Repository();
objF03030 = new F03030Repository();
objF03031 = new F03031Repository();
objF03032 = new F03032Repository();
objF98090 = new F98090Repository();
objGLAcc = new GLAccRepository();
#endregion
#region "First Result"
#region "Get Customer Ledger"
IQueryable<F03010> CustLedMain = objF03010.GetQuery();
if (filterCustomerID != "")
{
CustLedMain = CustLedMain.Where(x=>x.CustomerID.Equals(filterCustomerID));
}
var CustLed = (from cl in CustLedMain
//where cl.DocType.Equals("TB") && cl.DocNo.Equals("13071398")
select new
{
cl.DocType,
cl.DocNo,
cl.CustomerID,
cl.InvoiceDate,
cl.DueDate,
cl.TransType,
cl.CurrCode,
cl.ExchangeRate,
cl.OpenAmtC,
cl.OpenAmtLocal,
cl.ReceiptAmtC,
cl.ReceiptAmtLocal,
cl.WriteOffAmtC,
cl.WriteOffAmtLocal
}).AsEnumerable();
#endregion
#region "Get General Ledger"
var GL = (from gl in objF03090.GetAll(x => x.GLDate <= filterDate && x.GLPostFlag.Equals("Y"))
select new
{
gl.DocType,
gl.DocNo,
gl.GLPostFlag,
gl.GLDate
}).AsEnumerable();
//IQueryable<F03090> GL = from gl in objF03090.GetQuery()
// select gl;
//GL = GL.Where(x => x.GLDate < filterDate && x.GLPostFlag.Equals("Y"));
#endregion
#region "Get Customer Data"
IQueryable<F01030> CustMain = objF01030.GetQuery();
if (filterCustomerID != "")
{
CustMain = CustMain.Where(c => c.CustomerID.Equals(filterCustomerID));
}
var Cust = (from c in CustMain
select new
{
c.CustomerID,
c.CustomerName,
c.CCatCode04,
c.CreditLimit,
c.PTerm
}).AsQueryable();
#endregion
#region "Get AR Receipt, AdvReceipt='N'"
IQueryable<F03020> objARRec_H = objF03020.GetQuery();
objARRec_H = objARRec_H.Where(x => x.AdvReceipt.Equals("N"));
if (filterCustomerID != "")
{
objARRec_H=objARRec_H.Where(x => x.CustomerID.Equals(filterCustomerID));
}
var ARReceipt = from d in objF03021.GetAll()
join h in objARRec_H.AsEnumerable()
on new { DocType = d.DocType, DocNo = d.DocNo } equals new { DocType = h.DocType, DocNo = h.DocNo }
join gl in GL
on new { DocType = d.InvType, DocNo = d.InvNo } equals new { DocType = gl.DocType, DocNo = gl.DocNo }
where h.ReceiptDate<=filterDate
group d by new { d.InvType, d.InvNo } into g
select new
{
InvType = g.Key.InvType,
InvNo = g.Key.InvNo,
TotReceiptAmtC = g.Sum(x => x.ReceiptAmtC) + g.Sum(x => x.WriteOffAmtC),
TotReceiptAmtLocal = g.Sum(x => x.ReceiptAmtLocal) + g.Sum(x=>x.WriteOffAmtLocal)
};
#endregion
#region "Get AR Credit Note"
IQueryable<F03030> objARApply_H = objF03030.GetQuery();
if (filterCustomerID != "")
{
objARApply_H = objARApply_H.Where(x => x.CustomerID.Equals(filterCustomerID));
}
var ARCreditNote = from d in objF03031.GetAll()
join h in objARApply_H.AsEnumerable()
on new { DocType = d.DocType, DocNo = d.DocNo } equals new { DocType = h.DocType, DocNo = h.DocNo }
join gl in GL
on new { DocType = d.DocType, DocNo = d.DocNo } equals new { DocType = gl.DocType, DocNo = gl.DocNo }
group d by new { d.InvoiceType, d.InvoiceNo } into g
select new
{
InvType = g.Key.InvoiceType,
InvNo = g.Key.InvoiceNo,
TotCNAmtC = g.Sum(x => x.ApplyAmtC),
TotCNAmtLocal = g.Sum(x => x.ApplyAmtLocal)
};
#endregion
#region "Get AR Debit Note"
var ARDebitNote = from d in objF03032.GetAll()
join h in objARApply_H.AsEnumerable()
on new { DocType = d.DocType, DocNo = d.DocNo } equals new { DocType = h.DocType, DocNo = h.DocNo }
join gl in GL
on new { DocType = d.DocType, DocNo = d.DocNo } equals new { DocType = gl.DocType, DocNo = gl.DocNo }
group d by new { d.InvoiceType, d.InvoiceNo } into g
select new
{
InvType = g.Key.InvoiceType,
InvNo = g.Key.InvoiceNo,
TotDNAmtC = g.Sum(x => x.ApplyAmtC+x.WriteOffAmtLocal),
TotDNAmtLocal = g.Sum(x => x.ApplyAmtLocal+x.WriteOffAmtLocal)
};
#endregion
#region "Get Last Receipt"
var initLastReceipt = from h in objARRec_H.AsEnumerable()
group h by new { h.CustomerID } into g
select new
{
CustomerID = g.Key.CustomerID,
LastReceiptDate = g.Max(x => x.ReceiptDate)
};
var preLastReceipt = (from h in objARRec_H.AsEnumerable()
join j in initLastReceipt on new { CustomerID = h.CustomerID, LRDate = h.ReceiptDate } equals new { CustomerID=j.CustomerID,LRDate=j.LastReceiptDate }
select new
{
CustomerID = h.CustomerID,
LastReceiptDate = j.LastReceiptDate,
TotLastReceiptAmtC = h.ReceiptAmt,
TotLastReceiptAmtLocal = h.ReceiptAmtLocal
}).ToList();
var LastReceipt = from r in preLastReceipt.AsEnumerable()
group r by new { r.CustomerID, r.LastReceiptDate } into g
select new
{
CustomerID = g.Key.CustomerID,
LastReceiptDate = g.Key.LastReceiptDate,
TotLastReceiptAmtC = g.Sum(x => x.TotLastReceiptAmtC),
TotLastReceiptAmtLocal = g.Sum(x => x.TotLastReceiptAmtLocal)
};
#endregion
#region "Get Join all to first result"
var entCustLedger = from cl in CustLed
join gl in GL
on new { DocType = cl.DocType, DocNo = cl.DocNo } equals new { DocType = gl.DocType, DocNo = gl.DocNo }
join cust in Cust
on cl.CustomerID equals cust.CustomerID into c_result
join LRec in LastReceipt
on cl.CustomerID equals LRec.CustomerID into lrec_result
join ARRec in ARReceipt
on new { InvType = cl.DocType, InvNo = cl.DocNo } equals new { InvType = ARRec.InvType, InvNo = ARRec.InvNo } into rec_result
join ARCN in ARCreditNote
on new { InvType = cl.DocType, InvNo = cl.DocNo } equals new { InvType = ARCN.InvType, InvNo = ARCN.InvNo } into cn_result
join ARDN in ARDebitNote
on new { InvType = cl.DocType, InvNo = cl.DocNo } equals new { InvType = ARDN.InvType, InvNo = ARDN.InvNo } into dn_result
from cust_r in c_result.DefaultIfEmpty()
from lrec_r in lrec_result.DefaultIfEmpty()
from rec_r in rec_result.DefaultIfEmpty()
from cn_r in cn_result.DefaultIfEmpty()
from dn_r in dn_result.DefaultIfEmpty()
select new
{
cl,
gl,
cust_r,
lrec_r,
rec_r,
cn_r,
dn_r
};
#endregion
#endregion
#region "Second result It will Union to first result"
#region "Get AR Receipt AdvReceipt='Y' "
IQueryable<F03020> ARReceipt_2_Main = objF03020.GetQuery();
ARReceipt_2_Main = ARReceipt_2_Main.Where(x => x.AdvReceipt.Equals("Y"));
if (filterCustomerID != "")
{
ARReceipt_2_Main = ARReceipt_2_Main.Where(x => x.CustomerID.Equals(filterCustomerID));
}
var ARReceipt_2 = (from Rec in ARReceipt_2_Main.AsEnumerable()
select new
{
Rec.CustomerID,
Rec.DocType, Rec.DocNo,
Rec.TransType,
Rec.CurrCode,
Rec.ExchangeRate,
Rec.ReceiptAmt, Rec.BankCharges,
Rec.ReceiptAmtLocal,Rec.BankChargesLocal,
Rec.WriteOffAmt,Rec.WriteOffAmtLocal
}).AsEnumerable();
#endregion
#region "Get Join all to Second result"
//==========================================================================
// alias "cl" use for ARReceipt_2 because to consistant for assignation data
//==========================================================================
var entARReceipt = from cl in ARReceipt_2
join gl in GL
on new { DocType = cl.DocType, DocNo = cl.DocNo } equals new { DocType = gl.DocType, DocNo = gl.DocNo }
join cust in Cust
on cl.CustomerID equals cust.CustomerID into c_result
join LRec in LastReceipt
on cl.CustomerID equals LRec.CustomerID into lrec_result
join ARCN in ARCreditNote
on new { InvType = cl.DocType, InvNo = cl.DocNo } equals new { InvType = ARCN.InvType, InvNo = ARCN.InvNo } into cn_result
from cust_r in c_result.DefaultIfEmpty()
from lrec_r in lrec_result.DefaultIfEmpty()
from cn_r in cn_result.DefaultIfEmpty()
select new
{
cl,
gl,
cust_r,
lrec_r,
cn_r
};
#endregion
#endregion
#region "Get return list Fetching by results"
//==========================================================================
//For Result 1
//==========================================================================
foreach (var data in entCustLedger.ToList())
{
entResult.Add(DataAssignForARAgingDetail(data,filterDate, 1));
}
//==========================================================================
//Union Result 2
//==========================================================================
foreach (var data in entARReceipt.ToList())
{
entResult.Add(DataAssignForARAgingDetail(data, filterDate, 2));
}
#endregion
return entResult;
}
