예제 #1
0
        public CustCatCodesVM getCustCatCodesInfoByID(string _ID)
        {
            objF01030 = new F01030Repository();

            Mapper.CreateMap<F01030, CustCatCodesVM>();

            F01030 objEntity = objF01030.GetSingle(x => x.CustomerID == _ID);

            CustCatCodesVM objResult = new CustCatCodesVM();

            objResult = Mapper.Map(objEntity, objResult);

            return objResult;
        }
예제 #2
0
        public IList<SalesDetailInfoModel> getAllSalesInfo(int page, int pagesize)
        {
            IList<SalesDetailInfoModel> resultObj = new List<SalesDetailInfoModel>();

            SH = new F42020Repository();
            SD = new F42021Repository();
            Cust = new F01030Repository();
            CustAddr = new F01034Repository();

            var objSH = SH.GetAll();
            var objSD = SD.GetAll();
            var objCust = Cust.GetAll();
            var objCustAddr = CustAddr.GetAll();

            var objResultJoin = from sh in objSH
                                join sd in objSD on new { DocType = sh.DocType, DocNo = sh.DocNo } equals new { DocType = sd.DocType, DocNo = sd.DocNo }
                                join cust in objCust on sh.CustomerID equals cust.CustomerID
                                join custAddr in objCustAddr on cust.CustomerID equals custAddr.CustomerID
                                select new
                                {
                                    sh.DocType,
                                    sh.DocNo,
                                    sd.LineNum,
                                    sh.CustomerID,
                                    cust.CustomerName,
                                    custAddr.TaxID,
                                    custAddr.Phone,
                                    custAddr.Email
                                };

            foreach (var data in objResultJoin)
            {
                SalesDetailInfoModel objSingle = new SalesDetailInfoModel();

                objSingle.DocType = data.DocType;
                objSingle.DocNo = data.DocNo;
                objSingle.LineNum = data.LineNum;
                objSingle.CustomerID = data.CustomerID;
                objSingle.CustomerName = data.CustomerName;
                objSingle.TaxID = data.TaxID;
                objSingle.Phone = data.Phone;
                objSingle.Email = data.Email;

                resultObj.Add(objSingle);
            }

            return resultObj.OrderBy(a => a.DocType).ThenBy(a => a.DocNo).ThenBy(a => a.LineNum).Skip(page * pagesize).Take(pagesize).ToList();
        }
예제 #3
0
        public F01030Model GetDataByCustomerID(string customerID)
        {
            Mapper.CreateMap<F01030, F01030Model>();
            objF01030 = new F01030Repository();

            F01030 objEntity = objF01030.GetSingle(c => c.CustomerID.Equals(customerID));
            F01030Model resultModel = new F01030Model();

            resultModel = Mapper.Map(objEntity, resultModel);

            return resultModel;
        }
예제 #4
0
        public IList<F01030Model> GetData()
        {
            Mapper.CreateMap<F01030, F01030Model>();
            objF01030 = new F01030Repository();

            IList<F01030> objEntity = objF01030.GetAll().ToList();
            IList<F01030Model> resultModel = new List<F01030Model>();

            resultModel = Mapper.Map(objEntity, resultModel);

            return resultModel;
        }
예제 #5
0
        public IList<CustShippingVM> getCustShippingInfoByID(string _ID)
        {
            objF01031 = new F01031Repository();
            objF01030 = new F01030Repository();

            IList<CustShippingVM> objResult = new List<CustShippingVM>();

            IList<F01031> objEntity= objF01031.GetAll(x=>x.CustomerID.Equals(_ID)).ToList();

            foreach (F01031 item in objEntity)
            {
                CustShippingVM data = new CustShippingVM();

                data.ShipToID = item.ShipToID;
                data.DeliveryZone = item.DeliveryZone;
                data.ShipToName = objF01030.GetSingle(x => x.CustomerID.Equals(data.ShipToID)).CustomerName;

                objResult.Add(data);
            }

            return objResult;
        }
        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;
        }
예제 #7
0
        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;
        }
예제 #8
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;
        }