public IEnumerable <ComboBoxList> GetPendingProductsForSaleDelivery(int sid, string term)//DocTypeId
        {
            var SaleDelivery = new SaleDeliveryOrderHeaderService(_unitOfWork).Find(sid);

            // var settings = new JobInvoiceSettingsService(_unitOfWork).GetJobInvoiceSettingsForDocument(JobInvoice.DocTypeId, JobInvoice.DivisionId, JobInvoice.SiteId);

            //string[] ContraSites = null;
            //if (!string.IsNullOrEmpty(settings.filterContraSites)) { ContraSites = settings.filterContraSites.Split(",".ToCharArray()); }
            //else { ContraSites = new string[] { "NA" }; }

            //string[] ContraDivisions = null;
            //if (!string.IsNullOrEmpty(settings.filterContraDivisions)) { ContraDivisions = settings.filterContraDivisions.Split(",".ToCharArray()); }
            //else { ContraDivisions = new string[] { "NA" }; }

            return((from p in db.ViewSaleOrderBalanceForCancellation
                    join t in db.Product on p.ProductId equals t.ProductId into ProdTable
                    from ProTab in ProdTable.DefaultIfEmpty()
                    where p.BalanceQty > 0 && ProTab.ProductName.ToLower().Contains(term.ToLower()) && SaleDelivery.BuyerId == p.BuyerId
                    group new { p, ProTab } by p.ProductId into g
                    orderby g.Key descending
                    select new ComboBoxList
            {
                Id = g.Key,
                PropFirst = g.Max(m => m.ProTab.ProductName)
            }
                    ).Take(20));
        }
        public IQueryable <LedgerHeaderViewModel> GetLedgerHeaderList(int id, string Uname)//Document Category Id
        {
            int           SiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];
            int           DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
            List <string> UserRoles  = (List <string>)System.Web.HttpContext.Current.Session["Roles"];

            return(from p in _ledgerHeaderRepository.Instance
                   join t in _unitOfWork.Repository <LedgerLine>().Instance on p.LedgerHeaderId equals t.LedgerHeaderId into ProdTable
                   from ProTab in ProdTable.DefaultIfEmpty()
                   where p.DocTypeId == id && p.SiteId == SiteId && p.DivisionId == DivisionId
                   group new { p, ProTab } by p.LedgerHeaderId into g
                   orderby g.Key descending
                   select new LedgerHeaderViewModel
            {
                CreditDays = g.Max(m => m.p.CreditDays),
                DivisionId = g.Max(m => m.p.DivisionId),
                DocDate = g.Max(m => m.p.DocDate),
                DocNo = g.Max(m => m.p.DocNo),
                DocTypeName = g.Max(m => m.p.DocType.DocumentTypeName),
                LedgerAccountName = g.Max(m => m.p.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.p.LedgerAccount.LedgerAccountSuffix) + "}",
                Narration = g.Max(m => m.p.Narration),
                ModifiedBy = g.Max(m => m.p.ModifiedBy),
                Remark = g.Max(m => m.p.Remark),
                SiteName = g.Max(m => m.p.Site.SiteName),
                Status = g.Max(m => m.p.Status),
                LedgerHeaderId = g.Max(m => m.p.LedgerHeaderId),
                AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.ProTab.LedgerAccount.LedgerAccountSuffix) + "}",
                ReviewCount = g.Max(m => m.p.ReviewCount),
                ReviewBy = g.Max(m => m.p.ReviewBy),
                Reviewed = (SqlFunctions.CharIndex(Uname, g.Max(m => m.p.ReviewBy)) > 0),
            });
        }
        public IQueryable <LedgerHeaderViewModel> GetLedgerHeaderList(int id, string Uname)//Document Category Id
        {
            int           SiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];
            int           DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
            List <string> UserRoles  = (List <string>)System.Web.HttpContext.Current.Session["Roles"];

            //return (from p in db.LedgerHeader
            //        where p.DocTypeId == id && p.SiteId == SiteId && p.DivisionId == DivisionId && (p.Status == 0 ? (p.ModifiedBy == Uname || UserRoles.Contains("Admin")) : 1 == 1)
            //        orderby p.DocDate descending, p.DocNo descending
            //        select new LedgerHeaderViewModel
            //        {
            //            CreditDays = p.CreditDays,
            //            DivisionId = p.DivisionId,
            //            DocDate = p.DocDate,
            //            DocNo=p.DocNo,
            //            DocTypeName = p.DocType.DocumentTypeName,
            //            LedgerAccountName = p.LedgerAccount.LedgerAccountName,
            //            Narration = p.Narration,
            //            ModifiedBy=p.ModifiedBy,
            //            Remark = p.Remark,
            //            SiteName = p.Site.SiteName,
            //            Status = p.Status,
            //            LedgerHeaderId=p.LedgerHeaderId,

            //        });



            return(from p in db.LedgerHeader
                   join t in db.LedgerLine on p.LedgerHeaderId equals t.LedgerHeaderId into ProdTable
                   from ProTab in ProdTable.DefaultIfEmpty()
                   where p.DocTypeId == id && p.SiteId == SiteId && p.DivisionId == DivisionId
                   group new { p, ProTab } by p.LedgerHeaderId into g
                   orderby g.Key descending
                   select new LedgerHeaderViewModel
            {
                CreditDays = g.Max(m => m.p.CreditDays),
                DivisionId = g.Max(m => m.p.DivisionId),
                DocDate = g.Max(m => m.p.DocDate),
                DocNo = g.Max(m => m.p.DocNo),
                DocTypeName = g.Max(m => m.p.DocType.DocumentTypeName),
                //LedgerAccountName = g.Max(m => m.p.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.p.LedgerAccount.LedgerAccountSuffix) + "}",
                LedgerAccountName = g.Max(m => m.p.LedgerAccount.LedgerAccountName + ", " + (m.p.LedgerAccount.PersonId != null ? m.p.LedgerAccount.Person.Suffix + " [" + m.p.LedgerAccount.Person.Code + "]" : m.p.LedgerAccount.LedgerAccountSuffix)),
                Narration = g.Max(m => m.p.Narration),
                ModifiedBy = g.Max(m => m.p.ModifiedBy),
                Remark = g.Max(m => m.p.Remark),
                SiteName = g.Max(m => m.p.Site.SiteName),
                Status = g.Max(m => m.p.Status),
                LedgerHeaderId = g.Max(m => m.p.LedgerHeaderId),
                //AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName) + " {" + g.Max(m => m.ProTab.LedgerAccount.LedgerAccountSuffix) + "}",
                //AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName + "," + m.ProTab.LedgerAccount.LedgerAccountSuffix),
                AccountName = g.Max(m => m.ProTab.LedgerAccount.LedgerAccountName + ", " + (m.ProTab.LedgerAccount.PersonId != null ? m.ProTab.LedgerAccount.Person.Suffix + " [" + m.ProTab.LedgerAccount.Person.Code + "]" : m.ProTab.LedgerAccount.LedgerAccountSuffix)),
                ReviewCount = g.Max(m => m.p.ReviewCount),
                ReviewBy = g.Max(m => m.p.ReviewBy),
                Reviewed = (SqlFunctions.CharIndex(Uname, g.Max(m => m.p.ReviewBy)) > 0),
                TotalAmount = g.Sum(m => m.ProTab.Amount),
            });
        }
        public IEnumerable <ComboBoxList> GetPendingJobOrders(string DocTypes, string term, int JobOrderCancelHeaderId)//DocTypeId
        {
            JobOrderCancelHeader header = new JobOrderCancelHeaderService(_unitOfWork).Find(JobOrderCancelHeaderId);

            return(from p in db.ViewJobOrderBalance
                   join t in db.JobOrderHeader on p.JobOrderHeaderId equals t.JobOrderHeaderId into ProdTable
                   from ProTab in ProdTable.DefaultIfEmpty()
                   where p.BalanceQty > 0 && ProTab.DocNo.ToLower().Contains(term.ToLower()) &&
                   p.SiteId == header.SiteId && p.DivisionId == header.DivisionId && p.JobWorkerId == header.JobWorkerId
                   group new { p, ProTab } by p.JobOrderHeaderId into g
                   select new ComboBoxList
            {
                Id = g.Key,
                PropFirst = g.Max(m => m.ProTab.DocNo),
            }
                   );
        }
Example #5
0
        public IQueryable <ComboBoxResult> GetPendingProductsForSaleDelivery(int id, string term)//DocTypeId
        {
            var SaleDelivery = new SaleDeliveryHeaderService(_unitOfWork).Find(id);

            var settings = new SaleDeliverySettingService(_unitOfWork).GetSaleDeliverySettingForDocument(SaleDelivery.DocTypeId, SaleDelivery.DivisionId, SaleDelivery.SiteId);

            string[] ContraSites = null;
            if (!string.IsNullOrEmpty(settings.filterContraSites))
            {
                ContraSites = settings.filterContraSites.Split(",".ToCharArray());
            }
            else
            {
                ContraSites = new string[] { "NA" };
            }

            string[] ContraDivisions = null;
            if (!string.IsNullOrEmpty(settings.filterContraDivisions))
            {
                ContraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
            }
            else
            {
                ContraDivisions = new string[] { "NA" };
            }

            return(from p in db.ViewSaleInvoiceBalanceForDelivery
                   join t in db.Product on p.ProductId equals t.ProductId into ProdTable
                   from ProTab in ProdTable.DefaultIfEmpty()
                   where p.BalanceQty > 0 && p.SaleToBuyerId == SaleDelivery.SaleToBuyerId &&
                   (string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == SaleDelivery.SiteId : ContraSites.Contains(p.SiteId.ToString())) &&
                   (string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == SaleDelivery.DivisionId : ContraDivisions.Contains(p.DivisionId.ToString())) &&
                   (string.IsNullOrEmpty(term) ? 1 == 1 : ProTab.ProductName.ToLower().Contains(term.ToLower()))
                   group new { p, ProTab } by p.ProductId into g
                   orderby g.Key descending
                   select new ComboBoxResult
            {
                id = g.Key.ToString(),
                text = g.Max(m => m.ProTab.ProductName)
            }
                   );
        }
        public IQueryable <ComboBoxResult> GetPendingProductsForJobOrderInspectionRequestCancel(string term, int JobOrderInspectionRequestCancelHeaderId)//DocTypeId
        {
            JobOrderInspectionRequestCancelHeader header = new JobOrderInspectionRequestCancelHeaderService(db).Find(JobOrderInspectionRequestCancelHeaderId);

            var settings = new JobOrderInspectionRequestSettingsService(db).GetJobOrderInspectionRequestSettingsForDocument(header.DocTypeId, header.DivisionId, header.SiteId);

            string[] contraDocTypes = null;
            if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
            {
                contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
            }
            else
            {
                contraDocTypes = new string[] { "NA" };
            }

            string[] contraSites = null;
            if (!string.IsNullOrEmpty(settings.filterContraSites))
            {
                contraSites = settings.filterContraSites.Split(",".ToCharArray());
            }
            else
            {
                contraSites = new string[] { "NA" };
            }

            string[] contraDivisions = null;
            if (!string.IsNullOrEmpty(settings.filterContraDivisions))
            {
                contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
            }
            else
            {
                contraDivisions = new string[] { "NA" };
            }

            var Query = (from p in db.ViewJobOrderInspectionRequestBalance
                         join jir in db.JobOrderInspectionRequestHeader on p.JobOrderInspectionRequestHeaderId equals jir.JobOrderInspectionRequestHeaderId
                         join t in db.Product on p.ProductId equals t.ProductId into ProdTable
                         from ProTab in ProdTable.DefaultIfEmpty()
                         where p.BalanceQty > 0 &&
                         p.SiteId == header.SiteId && p.DivisionId == header.DivisionId && p.JobWorkerId == header.JobWorkerId && jir.ProcessId == header.ProcessId
                         select new
            {
                ProductId = p.ProductId,
                ProductName = ProTab.ProductName,
                DocTypeId = jir.DocTypeId,
                SiteId = jir.SiteId,
                DivisionId = jir.DivisionId,
            }
                         );

            if (!string.IsNullOrEmpty(term))
            {
                Query = Query.Where(m => m.ProductName.ToLower().Contains(term.ToLower()));
            }
            if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
            {
                Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
            }
            if (!string.IsNullOrEmpty(settings.filterContraSites))
            {
                Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
            }
            else
            {
                Query = Query.Where(m => m.SiteId == header.SiteId);
            }
            if (!string.IsNullOrEmpty(settings.filterContraDivisions))
            {
                Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
            }
            else
            {
                Query = Query.Where(m => m.DivisionId == header.DivisionId);
            }

            return(from p in Query
                   group p by p.ProductId into g
                   orderby g.Max(m => m.ProductName)
                   select new ComboBoxResult
            {
                text = g.Max(m => m.ProductName),
                id = g.Key.ToString(),
            });
        }
        public IQueryable <ComboBoxResult> GetPendingProductsForProdOrderCancel(int filter, string term)//DocTypeId
        {
            var HEader = new ProdOrderCancelHeaderService(_unitOfWork).Find(filter);

            var settings = new ProdOrderSettingsService(_unitOfWork).GetProdOrderSettingsForDocument(HEader.DocTypeId, HEader.DivisionId, HEader.SiteId);

            string[] contraDocTypes = null;
            if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
            {
                contraDocTypes = settings.filterContraDocTypes.Split(",".ToCharArray());
            }
            else
            {
                contraDocTypes = new string[] { "NA" };
            }

            string[] contraSites = null;
            if (!string.IsNullOrEmpty(settings.filterContraSites))
            {
                contraSites = settings.filterContraSites.Split(",".ToCharArray());
            }
            else
            {
                contraSites = new string[] { "NA" };
            }

            string[] contraDivisions = null;
            if (!string.IsNullOrEmpty(settings.filterContraDivisions))
            {
                contraDivisions = settings.filterContraDivisions.Split(",".ToCharArray());
            }
            else
            {
                contraDivisions = new string[] { "NA" };
            }

            string[] filterProducts = null;
            if (!string.IsNullOrEmpty(settings.filterProducts))
            {
                filterProducts = settings.filterProducts.Split(",".ToCharArray());
            }
            else
            {
                filterProducts = new string[] { "NA" };
            }

            string[] filterProductTypes = null;
            if (!string.IsNullOrEmpty(settings.filterProductTypes))
            {
                filterProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
            }
            else
            {
                filterProductTypes = new string[] { "NA" };
            }

            string[] filterProductGroups = null;
            if (!string.IsNullOrEmpty(settings.filterProductGroups))
            {
                filterProductGroups = settings.filterProductGroups.Split(",".ToCharArray());
            }
            else
            {
                filterProductGroups = new string[] { "NA" };
            }

            int CurrentSiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];
            int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];


            var Query = from p in db.ViewProdOrderBalance
                        join t in db.ProdOrderHeader on p.ProdOrderHeaderId equals t.ProdOrderHeaderId into ProdTable
                        from ProTab in ProdTable.DefaultIfEmpty()
                        join Prod in db.Product on p.ProductId equals Prod.ProductId
                        where p.BalanceQty > 0
                        select new
            {
                ProductName    = Prod.ProductName,
                DocTypeId      = p.DocTypeId,
                ProductId      = p.ProductId,
                ProductGroupId = Prod.ProductGroupId,
                ProductTypeId  = Prod.ProductGroup.ProductTypeId,
                SiteId         = ProTab.SiteId,
                DivisionId     = ProTab.DivisionId,
            };

            if (!string.IsNullOrEmpty(term))
            {
                Query = Query.Where(m => m.ProductName.ToLower().Contains(term.ToLower()));
            }

            if (!string.IsNullOrEmpty(settings.filterContraDocTypes))
            {
                Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
            }

            if (!string.IsNullOrEmpty(settings.filterProducts))
            {
                Query = Query.Where(m => filterProducts.Contains(m.ProductId.ToString()));
            }

            if (!string.IsNullOrEmpty(settings.filterProductGroups))
            {
                Query = Query.Where(m => filterProductGroups.Contains(m.ProductGroupId.ToString()));
            }

            if (!string.IsNullOrEmpty(settings.filterProductTypes))
            {
                Query = Query.Where(m => filterProductTypes.Contains(m.ProductTypeId.ToString()));
            }

            if (!string.IsNullOrEmpty(settings.filterContraSites))
            {
                Query = Query.Where(m => contraSites.Contains(m.SiteId.ToString()));
            }
            else
            {
                Query = Query.Where(m => m.SiteId == CurrentSiteId);
            }
            if (!string.IsNullOrEmpty(settings.filterContraDivisions))
            {
                Query = Query.Where(m => contraDivisions.Contains(m.DivisionId.ToString()));
            }
            else
            {
                Query = Query.Where(m => m.DivisionId == CurrentDivisionId);
            }


            return(from p in Query
                   group p by p.ProductId into g
                   orderby g.Max(m => m.ProductName)
                   select new ComboBoxResult
            {
                id = g.Key.ToString(),
                text = g.Max(m => m.ProductName),
            });
        }