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