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 ARTrackingListModel getARTrackingByCustomerID(int Month, int Year, string filterCustomerID = "")
{
ARTrackingListModel objSingle = new ARTrackingListModel();
#region "Get AR Tracking List"
List<ARTrackingListModel> GetDataList = getARTrackingList(Month, Year, filterCustomerID).ToList();
#endregion
#region "Assign result"
if (GetDataList.Where(x => x.Over90 != 0 || x.Over120 != 0).Count() > 0)
{
return GetDataList.FirstOrDefault();
}
else
{
#region "This assign is for null value return from AR Tracking List. Default all Cust Info and AR figures are zero when no credit of this Customer"
#region "Get Cust Info"
IQueryable<F01030> CustMain = objF01030.GetQuery();
if (filterCustomerID != "")
CustMain = CustMain.Where(c => c.CustomerID.Equals(filterCustomerID));
#endregion
if (CustMain.Count() == 0)
return objSingle;
var objResultJoin = (from c in CustMain.AsEnumerable()
select new
{
c.CustomerID,
c.CustomerName,
c.CCatCode04,
c.PInst,
c.CreditLimit,
c.PaymentABC,
c.PTerm,
c.CreditMessage,
c.HoldCode,
c.CurrCode
}).FirstOrDefault();
#region"Assign to Result Object"
objSingle.Link = objResultJoin.CustomerID;
objSingle.CustomerID = objResultJoin.CustomerID;
objSingle.CustomerName = objResultJoin.CustomerName;
objSingle.CCatCode04 = objResultJoin.CCatCode04;
var pabc = objF98001.GetAll(u => u.SystemID.Equals("03") && u.UDCType.Equals("PBC") && u.Code.Equals(objResultJoin.PaymentABC)).FirstOrDefault();
if (pabc != null)
objSingle.PaymentABC = pabc.Desc1;
else
objSingle.PaymentABC = "";
var sm = objF98001.GetAll(u => u.SystemID.Equals("03") && u.UDCType.Equals("C04") && u.Code.Equals(objResultJoin.CCatCode04)).FirstOrDefault();
if (sm != null)
objSingle.SMName = sm.Desc1;
else
objSingle.SMName = "";
objSingle.PInst = objResultJoin.PInst;
objSingle.CreditLimit = objResultJoin.CreditLimit;
objSingle.PTerm = objResultJoin.PTerm;
objSingle.HoldCode = objResultJoin.HoldCode;
objSingle.CurrCode = objResultJoin.CurrCode;
objSingle.Over90 = 0;
objSingle.Over120 = 0;
if (GetDataList.Where(x => x.Over90 == 0 & x.Over120 == 0).Count() > 0)
{
objSingle.TotOutstanding = GetDataList.FirstOrDefault().TotOutstanding;
objSingle.EarliestInvDate = GetDataList.FirstOrDefault().EarliestInvDate;
objSingle.LastReceiptDate = GetDataList.FirstOrDefault().LastReceiptDate;
objSingle.LastReceiptAmt = GetDataList.FirstOrDefault().LastReceiptAmt;
}
else
{
objSingle.TotOutstanding = 0;
objSingle.EarliestInvDate = null;
objSingle.LastReceiptDate = null;
objSingle.LastReceiptAmt = 0;
}
return objSingle;
#endregion
#endregion
}
#endregion
}
public IList<ARTrackingListModel> getARTrackingList(int Month, int Year, string filterCustomerID = "")
{
#region "Declaration"
objF01030 = new F01030Repository();
objF98001 = new F98001Repository();
objF98090 = new F98090Repository();
objGLAcc = new GLAccRepository();
objAR = new ARManager();
IList<ARTrackingListModel> objResult = new List<ARTrackingListModel>();
DateTime FilterGLDate = new DateTime(Year, Month, DateTime.DaysInMonth(Year, Month));
//DateTime FilterGLDate = new DateTime(Year, Month, DateTime.DaysInMonth(Year, Month)).AddDays(1);
#endregion
#region "Get AR Aging Detail Result"
IEnumerable<ARAgingDetailModel> objARDetail = objAR.getARAgingDetail(FilterGLDate, filterCustomerID)
.Where(x => x.OutBalance != 0 && x.OutBalanceLocal != 0);
#endregion
#region "Minus CR Values"
var ARCrTranType = (from argl in objF98090.GetAll()
join glacc in objGLAcc.GetAll()
on argl.COA equals glacc.GLAID
where argl.ProgramModule.Equals("AR") && argl.COAType.Equals("ARMNU") && argl.DRCR.Equals("CR")
select new
{
argl.TransType
}).Distinct();
List<ARAgingDetailModel> updatedARAging = new List<ARAgingDetailModel>();
foreach (ARAgingDetailModel data in objARDetail)
{
if (ARCrTranType.Any(t2 => t2.TransType == data.TransType))
{
ARAgingDetailModel agm = new ARAgingDetailModel();
agm = data;
agm.AmountC *= -1;
agm.AmountLocal *= -1;
agm.OutBalance *= -1;
agm.OutBalanceLocal *= -1;
updatedARAging.Add(agm);
}
else
{
updatedARAging.Add(data);
}
}
#endregion
#region "Get Distinct Customer List from ARAgingDetail info"
var objARCustList = (from ardetail in updatedARAging select new { CustomerID = ardetail.CustomerID }).Distinct();
#endregion
#region "Get Customer Info"
IQueryable<F01030> CustMain = objF01030.GetQuery();
if (filterCustomerID != "")
CustMain = CustMain.Where(c => c.CustomerID.Equals(filterCustomerID));
#endregion
#region "Get UDC Info"
//var entUDCSM = objF98001.GetAll(u => u.SystemID.Equals("03") && u.UDCType.Equals("C04"));
//var entUDCPTerm = objF98001.GetAll(u => u.SystemID.Equals("03") && u.UDCType.Equals("PBC"));
#endregion
#region "Get FinalResult Info by Joining All Infos"
var objResultJoin = from c in CustMain.AsEnumerable()
join ARDetail in objARCustList.AsEnumerable() on c.CustomerID equals ARDetail.CustomerID
select new
{
c.CustomerID,
c.CustomerName,
c.CCatCode04,
c.PInst,
c.CreditLimit,
c.PaymentABC,
c.PTerm,
c.CreditMessage,
c.HoldCode,
c.CurrCode
};
#endregion
#region "Assign to result object from result"
foreach (var data in objResultJoin)
{
ARTrackingListModel objSingle = new ARTrackingListModel();
objSingle.Link = data.CustomerID;
objSingle.CustomerID = data.CustomerID;
objSingle.CustomerName = data.CustomerName;
objSingle.CCatCode04 = data.CCatCode04;
var pabc=objF98001.GetAll(u => u.SystemID.Equals("03") && u.UDCType.Equals("PBC") && u.Code.Equals(data.PaymentABC)).FirstOrDefault();
if (pabc!=null)
objSingle.PaymentABC = pabc.Desc1;
else
objSingle.PaymentABC = "";
var sm=objF98001.GetAll(u => u.SystemID.Equals("03") && u.UDCType.Equals("C04") && u.Code.Equals(data.CCatCode04)).FirstOrDefault();
if (sm!=null)
objSingle.SMName = sm.Desc1;
else
objSingle.SMName = "";
objSingle.PInst = data.PInst;
objSingle.CreditLimit = data.CreditLimit;
objSingle.PTerm = data.PTerm;
objSingle.HoldCode = data.HoldCode;
objSingle.CurrCode = data.CurrCode;
objSingle.CreditRating = data.CreditMessage;
var tmpAR = updatedARAging.Where(x => x.CustomerID.Equals(data.CustomerID)).OrderBy(x => x.InvoiceDate).Distinct();
foreach (var ar in tmpAR)
{
if (ar.ARAgingDay > 90)
{
objSingle.TotOutstanding += Math.Round(ar.OutBalanceLocal, 2);
if (ar.ARAgingDay > 120)
{
objSingle.Over120 += Math.Round(ar.OutBalanceLocal, 2);
}
else
{
objSingle.Over90 += Math.Round(ar.OutBalanceLocal, 2);
}
}
else
{
objSingle.TotOutstanding += Math.Round(ar.OutBalanceLocal, 2);
}
}
if (objSingle.Over90 < 0)
objSingle.Over90 = 0;
if (objSingle.Over120 < 0)
objSingle.Over120 = 0;
objSingle.EarliestInvDate = updatedARAging.Where(x => x.CustomerID.Equals(data.CustomerID)).Min(x => x.InvoiceDate);
objSingle.LastReceiptDate = updatedARAging.Where(x => x.CustomerID.Equals(data.CustomerID)).FirstOrDefault().LastReceiptDate;
objSingle.LastReceiptAmt = updatedARAging.Where(x => x.CustomerID.Equals(data.CustomerID)).FirstOrDefault().LastReceiptAmt;
objResult.Add(objSingle);
}
#endregion
return objResult;
}