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