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;
        }
예제 #2
0
        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;
        }