private ARAgingDetailModel DataAssignForARAgingDetail(dynamic data, DateTime filterDate,int resultNo)
{
#region "Declare object"
decimal _totRecAmtC = 0;
decimal _totRecAmtLocal = 0;
decimal _totCNAmtC = 0;
decimal _totCNAmtLocal = 0;
decimal _totDNAmtC = 0;
decimal _totDNAmtLocal = 0;
ARAgingDetailModel result = new ARAgingDetailModel();
#endregion
#region "Object validation null, if yes set value 0"
if (data.cn_r != null)
{
_totCNAmtC = data.cn_r.TotCNAmtC;
_totCNAmtLocal = data.cn_r.TotCNAmtLocal;
}
//==========================================================================
//Debit Note nee only for result 1 and Receipt is main based for result 2
//==========================================================================
if (resultNo==1)
{
if (data.rec_r != null)
{
_totRecAmtC = data.rec_r.TotReceiptAmtC;
_totRecAmtLocal = data.rec_r.TotReceiptAmtLocal;
}
if (data.dn_r != null)
{
_totDNAmtC =data.dn_r.TotDNAmtC;
_totDNAmtLocal = data.dn_r.TotDNAmtLocal;
}
}
#endregion
#region "Assign result data"
result.CustomerID = data.cl.CustomerID;
result.CustomerName = data.cust_r.CustomerName;
result.DocType = data.cl.DocType;
result.DocNo = data.cl.DocNo;
result.CreditLimit = data.cust_r.CreditLimit;
result.PTerm = data.cust_r.PTerm;
result.TransType = data.cl.TransType;
result.CurrCode = data.cl.CurrCode;
result.ExchangeRate = data.cl.ExchangeRate;
if (resultNo == 1)
{
result.ARAgingDay = Int32.Parse(filterDate.Subtract(data.cl.InvoiceDate).Days.ToString());
result.InvoiceDate = (DateTime)data.cl.InvoiceDate;
result.DueDate = (DateTime)data.cl.DueDate;
result.AmountC += (data.cl.OpenAmtC + data.cl.ReceiptAmtC + data.cl.WriteOffAmtC);
result.AmountLocal += data.cl.OpenAmtLocal + data.cl.ReceiptAmtLocal + data.cl.WriteOffAmtLocal;
result.OutBalance += (data.cl.OpenAmtC + data.cl.ReceiptAmtC + data.cl.WriteOffAmtC)
- _totRecAmtC - _totCNAmtC -_totDNAmtC;
result.OutBalanceLocal += (data.cl.OpenAmtLocal + data.cl.ReceiptAmtLocal + data.cl.WriteOffAmtLocal)
- _totRecAmtLocal - _totCNAmtLocal - _totDNAmtLocal;
}
else if (resultNo==2)
{
result.ARAgingDay = Int32.Parse(filterDate.Subtract(data.gl.GLDate).Days.ToString());
result.InvoiceDate = (DateTime)data.gl.GLDate;
result.DueDate = (DateTime)data.gl.GLDate;
result.AmountC = data.cl.ReceiptAmt + data.cl.BankCharges;
result.AmountLocal = data.cl.ReceiptAmtLocal + data.cl.BankCharges;
result.OutBalance = (data.cl.ReceiptAmt + data.cl.BankCharges) - _totCNAmtC;
result.OutBalanceLocal = (data.cl.ReceiptAmtLocal + data.cl.BankCharges) - _totCNAmtLocal;
}
if (data.lrec_r != null)
{
result.LastReceiptDate = data.lrec_r.LastReceiptDate;
result.LastReceiptAmt = data.lrec_r.TotLastReceiptAmtLocal;
}
else {
result.LastReceiptDate = null;
result.LastReceiptAmt = 0;
}
result.GLPostFlag = data.gl.GLPostFlag;
#endregion
return result;
}
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;
}
public IList<ARDetailInfoModel> getARDetailInfo(DateTime filterDate, string CustomerID)
{
#region "Declaration"
objF01030 = new F01030Repository();
objF98001 = new F98001Repository();
objF98090 = new F98090Repository();
objGLAcc = new GLAccRepository();
objAR = new ARManager();
#endregion
#region "Get AR Aging Detail Result"
IEnumerable<ARAgingDetailModel> objARDetail = objAR.getARAgingDetail(filterDate, CustomerID)
.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"Assign data"
List<ARDetailInfoModel> objPreResult= new List<ARDetailInfoModel>();
//var qLoop = updatedARAging.Where(x=>x.ARAgingDay>90).AsEnumerable();
var qLoop = updatedARAging.AsEnumerable();
foreach (var item in qLoop.ToList())
{
ARDetailInfoModel objSingle = new ARDetailInfoModel();
objSingle.CurrCode = item.CurrCode;
objSingle.EarliestInvoiceDate = item.InvoiceDate;
objSingle.AmountC_Total = item.OutBalance;
if (item.ARAgingDay > 120)
{
objSingle.AmountC_120 = item.OutBalance;
objSingle.AmountC_90 = 0;
}
else if (item.ARAgingDay > 90)
{
objSingle.AmountC_90 = item.OutBalance;
objSingle.AmountC_120 = 0;
}
objPreResult.Add(objSingle);
}
var temp = from r in objPreResult
group r by r.CurrCode into gr
select new
{
CurrCode=gr.Key,
AmountC_90 = gr.Sum(x => x.AmountC_90),
AmountC_120 = gr.Sum(x => x.AmountC_120),
AmountC_Total = gr.Sum(x => x.AmountC_Total),
EarliestInvDate = gr.Min(x=>x.EarliestInvoiceDate)
};
List<ARDetailInfoModel> objResult = new List<ARDetailInfoModel>();
foreach (var item in temp)
{
ARDetailInfoModel objSingle = new ARDetailInfoModel();
objSingle.CurrCode = item.CurrCode;
objSingle.EarliestInvoiceDate = item.EarliestInvDate;
objSingle.AmountC_90 = item.AmountC_90;
objSingle.AmountC_120 = item.AmountC_120;
objSingle.AmountC_Total = item.AmountC_Total;
objResult.Add(objSingle);
}
#endregion
return objResult;
}