Beispiel #1
0
        public IQueryable <ComboBoxResult> GetJobOrderHelpListForProduct(int filter, string term)
        {
            int CurrentSiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];
            int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];


            var list = (from p in _unitOfWork.Repository <ViewJobOrderBalance>().Instance
                        join t in _unitOfWork.Repository <JobOrderHeader>().Instance on p.JobOrderHeaderId equals t.JobOrderHeaderId
                        join t2 in _unitOfWork.Repository <JobOrderLine>().Instance on p.JobOrderLineId equals t2.JobOrderLineId
                        join pt in _unitOfWork.Repository <Product>().Instance on p.ProductId equals pt.ProductId into ProductTable
                        from ProductTab in ProductTable.DefaultIfEmpty()
                        join D1 in _unitOfWork.Repository <Dimension1>().Instance on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in _unitOfWork.Repository <Dimension2>().Instance on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        where p.BalanceQty > 0 &&
                        (string.IsNullOrEmpty(term) ? 1 == 1 : p.JobOrderNo.ToLower().Contains(term.ToLower()) ||
                         string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower()) ||
                         string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower())) &&
                        p.SiteId == CurrentSiteId &&
                        p.DivisionId == CurrentDivisionId
                        orderby t.DocDate, t.DocNo
                        select new ComboBoxResult
            {
                text = t.DocType.DocumentTypeName + "-" + p.JobOrderNo,
                id = p.JobOrderLineId.ToString(),
                TextProp1 = "Product: " + ProductTab.ProductName.ToString(),
                TextProp2 = "Qty: " + p.BalanceQty.ToString(),
                AProp1 = Dimension1Tab.Dimension1Name,
                AProp2 = Dimension2Tab.Dimension2Name
            });

            return(list);
        }
        public JobOrderRateAmendmentLineViewModel GetJobOrderRateAmendmentLine(int id)
        {
            var temp = (from p in db.JobOrderRateAmendmentLine
                        join t in db.JobOrderLine on p.JobOrderLineId equals t.JobOrderLineId into table
                        from tab in table.DefaultIfEmpty()
                        join t5 in db.JobOrderHeader on tab.JobOrderHeaderId equals t5.JobOrderHeaderId into table5
                        from tab5 in table5.DefaultIfEmpty()
                        join D1 in db.Dimension1 on tab.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on tab.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        join D3 in db.Dimension3 on tab.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                        from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                        join D4 in db.Dimension4 on tab.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                        from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                        join t3 in db.Product on tab.ProductId equals t3.ProductId into table3
                        from tab3 in table3.DefaultIfEmpty()
                        join t4 in db.JobOrderAmendmentHeader on p.JobOrderAmendmentHeaderId equals t4.JobOrderAmendmentHeaderId into table4
                        from tab4 in table4.DefaultIfEmpty()
                        join t6 in db.Persons on tab4.JobWorkerId equals t6.PersonID into table6
                        from tab6 in table6.DefaultIfEmpty()
                        join t7 in db.ViewJobOrderBalanceForInvoice on p.JobOrderLineId equals t7.JobOrderLineId into table7
                        from tab7 in table7.DefaultIfEmpty()
                        orderby p.JobOrderRateAmendmentLineId
                        where p.JobOrderRateAmendmentLineId == id
                        select new JobOrderRateAmendmentLineViewModel
            {
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                LotNo = tab.LotNo,
                ProductId = tab.ProductId,
                ProductName = tab3.ProductName,
                JobOrderAmendmentHeaderDocNo = tab4.DocNo,
                JobOrderAmendmentHeaderId = p.JobOrderAmendmentHeaderId,
                JobOrderRateAmendmentLineId = p.JobOrderRateAmendmentLineId,
                JobOrderDocNo = tab5.DocNo,
                JobOrderLineId = tab.JobOrderLineId,
                Qty = p.Qty,
                Specification = tab.Specification,
                JobWorkerId = p.JobWorkerId,
                JobWorkerName = tab6.Name,
                UnitId = tab3.UnitId,
                UnitName = tab3.Unit.UnitName,
                DealUnitName = tab.DealUnit.UnitName,
                UnitConversionMultiplier = tab.UnitConversionMultiplier,
                DealQty = (p.Qty * tab.UnitConversionMultiplier),
                JobOrderRate = p.JobOrderRate,
                LockReason = p.LockReason,
                AmendedRate = p.AmendedRate,
                Rate = p.Rate,
                Amount = p.Amount,
                Remark = p.Remark,
            }

                        ).FirstOrDefault();

            return(temp);
        }
 public ProdOrderLineBalance GetLineDetail(int id)
 {
     return((from p in db.ViewProdOrderBalance
             join P in db.Product on p.ProductId equals P.ProductId
             join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
             from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
             join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
             from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
             join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
             from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
             join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
             from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
             where p.ProdOrderLineId == id
             select new ProdOrderLineBalance
     {
         Dimension1Name = Dimension1Tab.Dimension1Name,
         Dimension2Name = Dimension2Tab.Dimension2Name,
         Dimension3Name = Dimension3Tab.Dimension3Name,
         Dimension4Name = Dimension4Tab.Dimension4Name,
         ProductId = p.ProductId,
         ProductName = P.ProductName,
         ProdOrderLineId = p.ProdOrderLineId,
         ProdOrderDocNo = p.ProdOrderNo,
         BalanceQty = p.BalanceQty,
     }).FirstOrDefault());
 }
        public JobOrderInspectionRequestCancelLineViewModel GetJobOrderInspectionRequestCancelLine(int id)
        {
            var temp = (from p in db.JobOrderInspectionRequestCancelLine
                        join t in db.JobOrderInspectionRequestLine on p.JobOrderInspectionRequestLineId equals t.JobOrderInspectionRequestLineId into table
                        from tab in table.DefaultIfEmpty()
                        join Jol in db.JobOrderLine on tab.JobOrderLineId equals Jol.JobOrderLineId
                        join t5 in db.JobOrderInspectionRequestHeader on tab.JobOrderInspectionRequestHeaderId equals t5.JobOrderInspectionRequestHeaderId into table5
                        from tab5 in table5.DefaultIfEmpty()
                        join D1 in db.Dimension1 on Jol.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on Jol.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        join D3 in db.Dimension3 on Jol.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                        from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                        join D4 in db.Dimension4 on Jol.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                        from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                        join t3 in db.Product on Jol.ProductId equals t3.ProductId into table3
                        from tab3 in table3.DefaultIfEmpty()
                        join t4 in db.JobOrderInspectionRequestCancelHeader on p.JobOrderInspectionRequestCancelHeaderId equals t4.JobOrderInspectionRequestCancelHeaderId into table4
                        from tab4 in table4.DefaultIfEmpty()
                        join t6 in db.Persons on tab4.JobWorkerId equals t6.PersonID into table6
                        from tab6 in table6.DefaultIfEmpty()
                        join t7 in db.ViewJobOrderInspectionRequestBalance on p.JobOrderInspectionRequestLineId equals t7.JobOrderInspectionRequestLineId into table7
                        from tab7 in table7.DefaultIfEmpty()
                        orderby p.JobOrderInspectionRequestCancelLineId
                        where p.JobOrderInspectionRequestCancelLineId == id
                        select new JobOrderInspectionRequestCancelLineViewModel
            {
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                DueDate = Jol.DueDate,
                LotNo = Jol.LotNo,
                ProductId = Jol.ProductId,
                ProductName = tab3.ProductName,
                JobOrderInspectionRequestCancelHeaderDocNo = tab4.DocNo,
                JobOrderInspectionRequestCancelHeaderId = p.JobOrderInspectionRequestCancelHeaderId,
                JobOrderInspectionRequestCancelLineId = p.JobOrderInspectionRequestCancelLineId,
                JobOrderInspectionRequestDocNo = tab5.DocNo,
                JobOrderInspectionRequestLineId = tab.JobOrderInspectionRequestLineId,
                BalanceQty = p.Qty + tab7.BalanceQty,
                Qty = p.Qty,
                Remark = p.Remark,
                Specification = Jol.Specification,
                JobWorkerId = tab4.JobWorkerId,
                JobWorkerName = tab6.Name,
                UnitId = tab3.UnitId,
                ProductUidId = p.ProductUidId,
                ProductUidName = p.ProductUid.ProductUidName,
                LockReason = p.LockReason,
            }

                        ).FirstOrDefault();

            return(temp);
        }
 public IEnumerable <JobOrderCancelLineViewModel> GetJobOrderCancelLineForHeader(int id)
 {
     return(from p in db.JobOrderCancelLine
            join t in db.JobOrderLine on p.JobOrderLineId equals t.JobOrderLineId into table
            from tab in table.DefaultIfEmpty()
            join t5 in db.JobOrderHeader on tab.JobOrderHeaderId equals t5.JobOrderHeaderId
            join D1 in db.Dimension1 on tab.Dimension1Id equals D1.Dimension1Id into Dimension1Table
            from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
            join D2 in db.Dimension2 on tab.Dimension2Id equals D2.Dimension2Id into Dimension2Table
            from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
            join D3 in db.Dimension3 on tab.Dimension3Id equals D3.Dimension3Id into Dimension3Table
            from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
            join D4 in db.Dimension4 on tab.Dimension4Id equals D4.Dimension4Id into Dimension4Table
            from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
            join t3 in db.Product on tab.ProductId equals t3.ProductId into table3
            from tab3 in table3.DefaultIfEmpty()
            join t4 in db.JobOrderCancelHeader on p.JobOrderCancelHeaderId equals t4.JobOrderCancelHeaderId
            join t6 in db.Persons on t4.JobWorkerId equals t6.PersonID into table6
            from tab6 in table6.DefaultIfEmpty()
            where p.JobOrderCancelHeaderId == id
            orderby p.Sr
            select new JobOrderCancelLineViewModel
     {
         Dimension1Name = Dimension1Tab.Dimension1Name,
         Dimension2Name = Dimension2Tab.Dimension2Name,
         Dimension3Name = Dimension3Tab.Dimension3Name,
         Dimension4Name = Dimension4Tab.Dimension4Name,
         DueDate = tab.DueDate,
         LotNo = tab.LotNo,
         ProductId = tab.ProductId,
         ProductName = tab3.ProductName,
         JobOrderCancelHeaderDocNo = t4.DocNo,
         JobOrderCancelHeaderId = p.JobOrderCancelHeaderId,
         JobOrderCancelLineId = p.JobOrderCancelLineId,
         JobOrderDocNo = t5.DocNo,
         JobOrderLineId = tab.JobOrderLineId,
         Qty = p.Qty,
         Remark = p.Remark,
         ProductUidId = p.ProductUidId,
         Specification = tab.Specification,
         JobWorkerId = t4.JobWorkerId,
         JobWorkerName = tab6.Name,
         UnitId = tab3.UnitId,
         unitDecimalPlaces = tab3.Unit.DecimalPlaces,
         StockId = p.StockId,
         StockProcessId = p.StockProcessId,
         ProductUidName = p.ProductUid.ProductUidName,
     }
            );
 }
Beispiel #6
0
        public JobInvoiceLineViewModel GetLineDetailFromUId(string UID, int HeaderId)
        {
            var Header = db.JobInvoiceAmendmentHeader.Find(HeaderId);

            return((from p in db.ViewJobInvoiceBalanceForRateAmendment
                    join ji in db.JobInvoiceLine on p.JobInvoiceLineId equals ji.JobInvoiceLineId
                    join jih in db.JobInvoiceHeader on ji.JobInvoiceHeaderId equals jih.JobInvoiceHeaderId
                    join JR in db.JobReceiveLine on ji.JobReceiveLineId equals JR.JobReceiveLineId
                    join JO in db.JobOrderLine on JR.JobOrderLineId equals JO.JobOrderLineId
                    join t in db.ProductUid on JR.ProductUidId equals t.ProductUIDId into uidtable
                    from uidtab in uidtable.DefaultIfEmpty()
                    join t2 in db.Product on JO.ProductId equals t2.ProductId
                    join D1 in db.Dimension1 on JO.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                    from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                    join D2 in db.Dimension2 on JO.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                    from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                    join D3 in db.Dimension3 on JO.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                    from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                    join D4 in db.Dimension4 on JO.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                    from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                    where uidtab.ProductUidName == UID && p.ProcessId == Header.ProcessId && jih.SiteId == Header.SiteId && jih.DivisionId == Header.DivisionId
                    select new JobInvoiceLineViewModel
            {
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                LotNo = JR.LotNo,
                Qty = (ji.Qty),
                Specification = JO.Specification,
                UnitId = t2.UnitId,
                DealUnitId = ji.DealUnitId,
                DealQty = ji.DealQty,
                UnitConversionMultiplier = ji.UnitConversionMultiplier,
                UnitName = t2.Unit.UnitName,
                DealUnitName = ji.DealUnit.UnitName,
                JobWorkerId = ji.JobWorkerId,
                JobWorkerName = ji.JobWorker.Person.Name,
                Rate = p.Rate,
                ProductId = JO.ProductId,
                ProductName = JO.Product.ProductName,
                InvoiceDocNo = jih.DocNo,
                JobInvoiceLineId = ji.JobInvoiceLineId,
            }
                    ).FirstOrDefault());
        }
        public IEnumerable <JobOrderHeaderListViewModel> GetPendingJobOrdersWithPatternMatch(int JobWorkerId, string term, int Limiter)//Product Id
        {
            var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
            var SiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];


            var tem = (from p in db.ViewJobOrderBalance
                       join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                       from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                       join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                       from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                       join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                       from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                       join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                       from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                       join t3 in db.Product on p.ProductId equals t3.ProductId
                       join Jol in db.JobOrderLine on p.JobOrderLineId equals Jol.JobOrderLineId into JobOrderLineTable from JobOrderLineTab in JobOrderLineTable.DefaultIfEmpty()
                       where p.BalanceQty > 0 && p.JobWorkerId == JobWorkerId &&
                       p.DivisionId == DivisionId && p.SiteId == SiteId &&
                       ((string.IsNullOrEmpty(term) ? 1 == 1 : p.JobOrderNo.ToLower().Contains(term.ToLower())) ||
                        (string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower())) ||
                        (string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower())) ||
                        (string.IsNullOrEmpty(term) ? 1 == 1 : Dimension3Tab.Dimension3Name.ToLower().Contains(term.ToLower())) ||
                        (string.IsNullOrEmpty(term) ? 1 == 1 : Dimension4Tab.Dimension4Name.ToLower().Contains(term.ToLower())) ||
                        (string.IsNullOrEmpty(term) ? 1 == 1 : t3.ProductName.ToLower().Contains(term.ToLower())))
                       orderby p.JobOrderNo
                       select new JobOrderHeaderListViewModel
            {
                DocNo = p.JobOrderNo,
                JobOrderLineId = p.JobOrderLineId,
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                ProductName = t3.ProductName,
                ProductUidId = JobOrderLineTab.ProductUidId,
                ProductUidName = JobOrderLineTab.ProductUid.ProductUidName,
                LotNo = JobOrderLineTab.LotNo,
                BalanceQty = p.BalanceQty,
            }).Take(Limiter);

            return(tem);
        }
        public JobOrderInspectionRequestLineViewModel GetInspectionRequestLineDetail(int id)
        {
            var temp = (from p in db.ViewJobOrderInspectionRequestBalance
                        join t1 in db.JobOrderInspectionRequestLine on p.JobOrderInspectionRequestLineId equals t1.JobOrderInspectionRequestLineId
                        join t2 in db.Product on p.ProductId equals t2.ProductId
                        join Jol in db.JobOrderLine on t1.JobOrderLineId equals Jol.JobOrderLineId
                        join D1 in db.Dimension1 on Jol.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on Jol.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        join D3 in db.Dimension3 on Jol.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                        from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                        join D4 in db.Dimension4 on Jol.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                        from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                        where p.JobOrderInspectionRequestLineId == id
                        select new JobOrderInspectionRequestLineViewModel
            {
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                LotNo = Jol.LotNo,
                Qty = p.BalanceQty,
                Specification = Jol.Specification,
                UnitId = t2.UnitId,
                DealUnitId = Jol.DealUnitId,
                JobOrderInspectionRequestLineId = t1.JobOrderInspectionRequestLineId,
                DealQty = p.BalanceQty * Jol.UnitConversionMultiplier,
                UnitConversionMultiplier = Jol.UnitConversionMultiplier,
                UnitName = t2.Unit.UnitName,
                DealUnitName = Jol.DealUnit.UnitName,
                ProductId = p.ProductId,
                ProductName = Jol.Product.ProductName,
                unitDecimalPlaces = t2.Unit.DecimalPlaces,
                DealunitDecimalPlaces = Jol.DealUnit.DecimalPlaces,
            }
                        ).FirstOrDefault();


            return(temp);
        }
        public JsonResult GetSaleOrderDetailJson(int SaleOrderLineId)
        {
            var temp = (from L in db.ViewSaleOrderBalance
                        join Dl in db.SaleOrderLine on L.SaleOrderLineId equals Dl.SaleOrderLineId into SaleOrderLineTable
                        from SaleOrderLineTab in SaleOrderLineTable.DefaultIfEmpty()
                        join P in db.Product on L.ProductId equals P.ProductId into ProductTable
                        from ProductTab in ProductTable.DefaultIfEmpty()
                        join U in db.Units on ProductTab.UnitId equals U.UnitId into UnitTable
                        from UnitTab in UnitTable.DefaultIfEmpty()
                        join D1 in db.Dimension1 on L.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on L.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        where L.SaleOrderLineId == SaleOrderLineId
                        select new
            {
                SaleOrderHeaderDocNo = L.SaleOrderNo,
                UnitId = UnitTab.UnitId,
                UnitName = UnitTab.UnitName,
                DealUnitId = SaleOrderLineTab.DealUnitId,
                Specification = SaleOrderLineTab.Specification,
                UnitConversionMultiplier = SaleOrderLineTab.UnitConversionMultiplier,
                ProductId = L.ProductId,
                Dimension1Id = L.Dimension1Id,
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Id = L.Dimension2Id,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Rate = L.Rate,
                BalanceQty = L.BalanceQty
            }).FirstOrDefault();

            if (temp != null)
            {
                return(Json(temp));
            }
            else
            {
                return(null);
            }
        }
Beispiel #10
0
        public IQueryable <ComboBoxResult> GetSaleEnquiryHelpListForProduct(int PersonId, string term)
        {
            int CurrentSiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];
            int CurrentDivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];


            var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
                        join t in db.SaleEnquiryHeader on p.SaleEnquiryHeaderId equals t.SaleEnquiryHeaderId
                        join t2 in db.SaleEnquiryLine on p.SaleEnquiryLineId equals t2.SaleEnquiryLineId
                        join pt in db.Product on p.ProductId equals pt.ProductId into ProductTable
                        from ProductTab in ProductTable.DefaultIfEmpty()
                        join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                        from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                        join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                        from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                        where p.BuyerId == PersonId &&
                        ((string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower())) ||
                         (string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower()))) &&
                        p.SiteId == CurrentSiteId &&
                        p.DivisionId == CurrentDivisionId
                        orderby t.DocDate, t.DocNo
                        select new ComboBoxResult
            {
                text = ProductTab.ProductName,
                id = p.SaleEnquiryLineId.ToString(),
                TextProp1 = "Enquiry No: " + p.SaleEnquiryNo.ToString(),
                TextProp2 = "BalQty: " + p.BalanceQty.ToString(),
                AProp1 = Dimension1Tab.Dimension1Name + (string.IsNullOrEmpty(Dimension1Tab.Dimension1Name) ? "" : ",") + Dimension2Tab.Dimension2Name,
                AProp2 = Dimension3Tab.Dimension3Name + (string.IsNullOrEmpty(Dimension3Tab.Dimension3Name) ? "" : ",") + Dimension4Tab.Dimension4Name,
            });

            return(list);
        }
Beispiel #11
0
 public IEnumerable <JobInvoiceRateAmendmentLineViewModel> GetJobInvoiceRateAmendmentLineForHeader(int id)
 {
     return(from p in db.JobInvoiceRateAmendmentLine
            join t in db.JobInvoiceLine on p.JobInvoiceLineId equals t.JobInvoiceLineId into table
            from tab in table.DefaultIfEmpty()
            join JR in db.JobReceiveLine on tab.JobReceiveLineId equals JR.JobReceiveLineId
            join JO in db.JobOrderLine on JR.JobOrderLineId equals JO.JobOrderLineId
            join uid in db.ProductUid on JR.ProductUidId equals uid.ProductUIDId into uidtable
            from uidtab in uidtable.DefaultIfEmpty()
            join t5 in db.JobInvoiceHeader on tab.JobInvoiceHeaderId equals t5.JobInvoiceHeaderId
            join D1 in db.Dimension1 on JO.Dimension1Id equals D1.Dimension1Id into Dimension1Table
            from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
            join D2 in db.Dimension2 on JO.Dimension2Id equals D2.Dimension2Id into Dimension2Table
            from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
            join D3 in db.Dimension3 on JO.Dimension3Id equals D3.Dimension3Id into Dimension3Table
            from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
            join D4 in db.Dimension4 on JO.Dimension4Id equals D4.Dimension4Id into Dimension4Table
            from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
            join t3 in db.Product on JO.ProductId equals t3.ProductId into table3
            from tab3 in table3.DefaultIfEmpty()
            join t4 in db.JobInvoiceAmendmentHeader on p.JobInvoiceAmendmentHeaderId equals t4.JobInvoiceAmendmentHeaderId
            join t6 in db.Persons on tab.JobWorkerId equals t6.PersonID into table6
            from tab6 in table6.DefaultIfEmpty()
            where p.JobInvoiceAmendmentHeaderId == id
            orderby p.Sr
            select new JobInvoiceRateAmendmentLineViewModel
     {
         Dimension1Name = Dimension1Tab.Dimension1Name,
         Dimension2Name = Dimension2Tab.Dimension2Name,
         Dimension3Name = Dimension3Tab.Dimension3Name,
         Dimension4Name = Dimension4Tab.Dimension4Name,
         LotNo = JO.LotNo,
         ProductId = JO.ProductId,
         ProductName = tab3.ProductName,
         JobInvoiceAmendmentHeaderDocNo = t4.DocNo,
         JobInvoiceAmendmentHeaderId = p.JobInvoiceAmendmentHeaderId,
         JobInvoiceRateAmendmentLineId = p.JobInvoiceRateAmendmentLineId,
         JobInvoiceDocNo = t5.DocNo,
         JobInvoiceDocDate = t5.DocDate,
         JobInvoiceLineId = tab.JobInvoiceLineId,
         Qty = p.Qty,
         DealQty = p.Qty * tab.UnitConversionMultiplier,
         DealUnitName = tab.DealUnit.UnitName,
         Specification = JO.Specification,
         JobWorkerId = p.JobWorkerId,
         JobWorkerName = tab6.Name,
         UnitId = tab3.UnitId,
         UnitName = tab3.Unit.UnitName,
         unitDecimalPlaces = tab3.Unit.DecimalPlaces,
         DealunitDecimalPlaces = tab.DealUnit.DecimalPlaces,
         Remark = p.Remark,
         Rate = p.Rate,
         Amount = p.Amount,
         AmendedRate = p.AmendedRate,
         JobInvoiceRate = p.JobInvoiceRate,
         ProductUidName = uidtab.ProductUidName,
         InvoiceDocTypeId = t5.DocTypeId,
         InvoiceHeaderId = t5.JobInvoiceHeaderId,
     }
            );
 }
Beispiel #12
0
        public IEnumerable <SaleEnquiryHeaderListViewModel> GetPendingSaleEnquiriesWithPatternMatch(int Id, string term, int Limiter)
        {
            var SaleQuotationHeader = new SaleQuotationHeaderService(_unitOfWork).Find(Id);

            var settings = new SaleQuotationSettingsService(_unitOfWork).GetSaleQuotationSettingsForDocument(SaleQuotationHeader.DocTypeId, SaleQuotationHeader.DivisionId, SaleQuotationHeader.SiteId);

            string settingProductTypes    = "";
            string settingProductDivision = "";
            string settingProductCategory = "";

            if (!string.IsNullOrEmpty(settings.filterProductTypes))
            {
                settingProductTypes = "|" + settings.filterProductTypes.Replace(",", "|,|") + "|";
            }
            if (!string.IsNullOrEmpty(settings.FilterProductDivision))
            {
                settingProductDivision = "|" + settings.FilterProductDivision.Replace(",", "|,|") + "|";
            }


            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[] ProductTypes = null;
            if (!string.IsNullOrEmpty(settings.filterProductTypes))
            {
                ProductTypes = settingProductTypes.Split(",".ToCharArray());
            }
            else
            {
                ProductTypes = new string[] { "NA" };
            }

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



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


            var list = (from p in db.ViewSaleEnquiryBalanceForQuotation
                        join Pt in db.Product on p.ProductId equals Pt.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
                        join Fp in db.FinishedProduct on p.ProductId equals Fp.ProductId into FinishedProductTable from FinishedProductTab in FinishedProductTable.DefaultIfEmpty()
                        join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                        from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                        join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                        from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                        where (
                            string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleEnquiryNo.ToLower().Contains(term.ToLower()) ||
                            string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower()) ||
                            string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower()) ||
                            string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower()) ||
                            string.IsNullOrEmpty(term) ? 1 == 1 : Dimension3Tab.Dimension3Name.ToLower().Contains(term.ToLower()) ||
                            string.IsNullOrEmpty(term) ? 1 == 1 : Dimension4Tab.Dimension4Name.ToLower().Contains(term.ToLower())
                            ) && p.BalanceQty > 0 &&
                        (string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(p.DocTypeId.ToString())) &&
                        (string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
                        (string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
                        (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains("|" + ProductTab.ProductGroup.ProductTypeId.ToString() + "|")) &&
                        (string.IsNullOrEmpty(settings.FilterProductDivision) ? 1 == 1 : ProductDivision.Contains("|" + ProductTab.DivisionId.ToString() + "|"))
                        orderby p.SaleEnquiryNo
                        select new SaleEnquiryHeaderListViewModel
            {
                DocNo = p.SaleEnquiryNo,
                SaleEnquiryLineId = p.SaleEnquiryLineId,
                ProductName = ProductTab.ProductName,
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
            }).Take(Limiter);

            return(list);
        }
Beispiel #13
0
        public IQueryable <ComboBoxResult> GetSaleInvoiceHelpListForProduct(int filter, string term)
        {
            var SaleDeliveryHeader = new SaleDeliveryHeaderService(_unitOfWork).Find(filter);

            var settings = new SaleDeliverySettingService(_unitOfWork).GetSaleDeliverySettingForDocument(SaleDeliveryHeader.DocTypeId, SaleDeliveryHeader.DivisionId, SaleDeliveryHeader.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" };
            }

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

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


            var list = (from p in db.ViewSaleInvoiceBalanceForDelivery
                        join t in db.SaleInvoiceHeader on p.SaleInvoiceHeaderId equals t.SaleInvoiceHeaderId
                        join t2 in db.SaleInvoiceLine on p.SaleInvoiceLineId equals t2.SaleInvoiceLineId
                        join pt in db.Product on p.ProductId equals pt.ProductId into ProductTable
                        from ProductTab in ProductTable.DefaultIfEmpty()
                        join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                        from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        where p.SaleToBuyerId == SaleDeliveryHeader.SaleToBuyerId &&
                        ((string.IsNullOrEmpty(term) ? 1 == 1 : p.SaleInvoiceNo.ToLower().Contains(term.ToLower())) ||
                         (string.IsNullOrEmpty(term) ? 1 == 1 : ProductTab.ProductName.ToLower().Contains(term.ToLower())) ||
                         (string.IsNullOrEmpty(term) ? 1 == 1 : Dimension1Tab.Dimension1Name.ToLower().Contains(term.ToLower())) ||
                         (string.IsNullOrEmpty(term) ? 1 == 1 : Dimension2Tab.Dimension2Name.ToLower().Contains(term.ToLower()))) &&
                        (string.IsNullOrEmpty(settings.filterContraSites) ? p.SiteId == CurrentSiteId : contraSites.Contains(p.SiteId.ToString())) &&
                        (string.IsNullOrEmpty(settings.filterContraDivisions) ? p.DivisionId == CurrentDivisionId : contraDivisions.Contains(p.DivisionId.ToString())) &&
                        (string.IsNullOrEmpty(settings.filterContraDocTypes) ? 1 == 1 : contraDocTypes.Contains(t.DocTypeId.ToString())) &&
                        p.BalanceQty > 0
                        orderby t.DocDate, t.DocNo
                        select new ComboBoxResult
            {
                text = ProductTab.ProductName,
                id = p.SaleInvoiceLineId.ToString(),
                TextProp1 = "Order No: " + p.SaleInvoiceNo.ToString(),
                TextProp2 = "BalQty: " + p.BalanceQty.ToString(),
                AProp1 = Dimension1Tab.Dimension1Name,
                AProp2 = Dimension2Tab.Dimension2Name
            });

            return(list);
        }
        // here we simulate SQL search, sorting and paging operations
        private List <ProductRateIndex> FilterProductData(ref int recordFiltered, ref int recordTotal, int start, int length, string search, int sortColumn, string sortDirection, FilterArgs Fvm)
        {
            bool Pending = false;
            bool Sample  = false;

            if (string.IsNullOrEmpty(Fvm.WizardType) || Fvm.WizardType == "Pending")
            {
                Pending = true;
            }



            List <ProductRateIndex>       list  = new List <ProductRateIndex>();
            IQueryable <ProductRateIndex> _data = (from Pp in db.ProductProcess
                                                   join Rh in db.RateListHeader on Pp.ProcessId equals Rh.ProcessId into RateListHeaderTable
                                                   from RateListHeaderTab in RateListHeaderTable.DefaultIfEmpty()
                                                   join P in db.Product on Pp.ProductId equals P.ProductId into ProductTable
                                                   from ProductTab in ProductTable.DefaultIfEmpty()
                                                   join D1 in db.Dimension1 on Pp.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                                                   from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                                                   join D2 in db.Dimension2 on Pp.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                                                   from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                                                   where RateListHeaderTab.RateListHeaderId == Fvm.RateListHeaderId && (Pending ? Pp.ProductRateGroupId == null : 1 == 1) &&
                                                   RateListHeaderTab.DivisionId == ProductTab.DivisionId && ProductTab.IsActive == true
                                                   group new { Pp, ProductTab, Dimension1Tab, Dimension2Tab } by new { ProductTab.ProductId, Dimension1Tab.Dimension1Id, Dimension2Tab.Dimension2Id } into g
                                                   select new ProductRateIndex
            {
                ProductId = g.Key.ProductId,
                ProductName = g.Max(m => m.ProductTab.ProductName),
                Dimension1Id = g.Key.Dimension1Id,
                Dimension1Name = g.Max(m => m.Dimension1Tab.Dimension1Name),
                Dimension2Id = g.Key.Dimension2Id,
                Dimension2Name = g.Max(m => m.Dimension2Tab.Dimension2Name),
                ImageFileName = g.Max(m => m.ProductTab.ImageFileName),
                ImageFolderName = g.Max(m => m.ProductTab.ImageFolderName),
                ProductRateGroupId = g.Max(m => m.Pp.ProductRateGroupId),
            });


            recordTotal = _data.Count();

            if (string.IsNullOrEmpty(search))
            {
            }
            else
            {
                // simulate search
                _data = from m in _data
                        where (m.ProductName).ToLower().Contains(search.ToLower()) || (m.Dimension1Name).ToLower().Contains(search.ToLower()) ||
                        (m.Dimension2Name).ToLower().Contains(search.ToLower())
                        select m;
            }

            _data = _data.OrderBy(m => m.ProductName);


            recordFiltered = _data.Count();

            // get just one page of data
            list = _data.Select(m => new ProductRateIndex
            {
                ProductId          = m.ProductId,
                ProductName        = m.ProductName,
                Dimension1Id       = m.Dimension1Id,
                Dimension1Name     = m.Dimension1Name,
                Dimension2Id       = m.Dimension2Id,
                Dimension2Name     = m.Dimension2Name,
                ImageFileName      = m.ImageFileName,
                ImageFolderName    = m.ImageFolderName,
                ProductRateGroupId = m.ProductRateGroupId,
            })
                   .Skip(start).Take((start == 0) ? 90 : length).ToList();

            return(list);
        }
        public IEnumerable <SaleOrderCancelLineViewModel> GetSaleOrderLineForMultiSelect(SaleOrderCancelFilterViewModel svm)
        {
            string[] ProductIdArr = null;
            if (!string.IsNullOrEmpty(svm.ProductId))
            {
                ProductIdArr = svm.ProductId.Split(",".ToCharArray());
            }
            else
            {
                ProductIdArr = new string[] { "NA" };
            }

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

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

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

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

            var temp = (from p in db.ViewSaleOrderBalanceForCancellation
                        join product in db.Product on p.ProductId equals product.ProductId into table2
                        from tab2 in table2.DefaultIfEmpty()
                        join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                        join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                        from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                        where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
                        (svm.BuyerId == 0? 1 == 1 : p.BuyerId == svm.BuyerId) &&
                        (string.IsNullOrEmpty(svm.SaleOrderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
                        (string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(tab2.ProductGroupId.ToString())) &&
                        (string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dimension1IdArr.Contains(p.Dimension1Id.ToString())) &&
                        (string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dimension2IdArr.Contains(p.Dimension2Id.ToString())) &&
                        p.BalanceQty > 0
                        select new SaleOrderCancelLineViewModel
            {
                BalanceQty = p.BalanceQty,
                Qty = p.BalanceQty,
                DocNo = p.SaleOrderNo,
                ProductName = tab2.ProductName,
                ProductId = p.ProductId,
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension1Id = p.Dimension1Id,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension2Id = p.Dimension2Id,
                SaleOrderCancelHeaderId = svm.SaleOrderCancelHeaderId,
                SaleOrderLineId = p.SaleOrderLineId
            });

            return(temp);
        }
        public IEnumerable <JobOrderInspectionRequestLineViewModel> GetJobOrderLineForMultiSelect(JobOrderInspectionRequestFilterViewModel svm)
        {
            string[] ProductIdArr = null;
            if (!string.IsNullOrEmpty(svm.ProductId))
            {
                ProductIdArr = svm.ProductId.Split(",".ToCharArray());
            }
            else
            {
                ProductIdArr = new string[] { "NA" };
            }

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

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

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

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

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

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

            var Header = db.JobOrderInspectionRequestHeader.Find(svm.JobOrderInspectionRequestHeaderId);

            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.ViewJobOrderBalanceForInspectionRequest
                         join t in db.JobOrderLine on p.JobOrderLineId equals t.JobOrderLineId into table
                         from tab in table.DefaultIfEmpty()
                         join product in db.Product on p.ProductId equals product.ProductId into table2
                         from tab2 in table2.DefaultIfEmpty()
                         join jo in db.JobOrderHeader on p.JobOrderHeaderId equals jo.JobOrderHeaderId
                         join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                         from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                         join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                         from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                         join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                         from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                         join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                         from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                         join uni in db.Units on tab2.UnitId equals uni.UnitId into unittable
                         from unittab in unittable.DefaultIfEmpty()
                         join dealunit in db.Units on tab.DealUnitId equals dealunit.UnitId into dealunittable
                         from dealunittab in dealunittable.DefaultIfEmpty()
                         where p.BalanceQty > 0 && p.JobWorkerId == svm.JobWorkerId && jo.ProcessId == Header.ProcessId
                         orderby p.OrderDate, p.JobOrderNo, tab.Sr
                         select new
            {
                BalanceQty = p.BalanceQty,
                Qty = p.BalanceQty,
                JobOrderDocNo = p.JobOrderNo,
                ProductName = tab2.ProductName,
                ProductId = p.ProductId,
                JobOrderInspectionRequestHeaderId = svm.JobOrderInspectionRequestHeaderId,
                JobOrderLineId = p.JobOrderLineId,
                Dimension1Id = Dimension1Tab.Dimension1Id,
                Dimension2Id = Dimension2Tab.Dimension2Id,
                Dimension3Id = Dimension3Tab.Dimension3Id,
                Dimension4Id = Dimension4Tab.Dimension4Id,
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                Specification = tab.Specification,
                UnitId = unittab.UnitId,
                UnitName = unittab.UnitName,
                unitDecimalPlaces = unittab.DecimalPlaces,
                DealunitDecimalPlaces = dealunittab.DecimalPlaces,
                ProductUidName = (tab.ProductUidHeaderId == null ? tab.ProductUid.ProductUidName : ""),
                ProductUidId = tab.ProductUidId,
                JobOrderHeaderId = p.JobOrderHeaderId,
                ProdOrderLineId = tab.ProdOrderLineId,
                ProductGroupId = tab2.ProductGroupId,
                SiteId = jo.SiteId,
                DivisionId = jo.DivisionId,
                DocTypeId = jo.DocTypeId,
            }
                         );

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

            if (!string.IsNullOrEmpty(svm.JobOrderId))
            {
                Query = Query.Where(m => SaleOrderIdArr.Contains(m.JobOrderHeaderId.ToString()));
            }

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

            if (!string.IsNullOrEmpty(svm.Dimension1Id))
            {
                Query = Query.Where(m => Dime1IdArr.Contains(m.Dimension1Id.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension2Id))
            {
                Query = Query.Where(m => Dime2IdArr.Contains(m.Dimension2Id.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension3Id))
            {
                Query = Query.Where(m => Dime1IdArr.Contains(m.Dimension3Id.ToString()));
            }

            if (!string.IsNullOrEmpty(svm.Dimension4Id))
            {
                Query = Query.Where(m => Dime2IdArr.Contains(m.Dimension4Id.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);
            }

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


            return((from p in Query
                    select new JobOrderInspectionRequestLineViewModel
            {
                BalanceQty = p.BalanceQty,
                Qty = p.Qty,
                JobOrderDocNo = p.JobOrderDocNo,
                ProductName = p.ProductName,
                ProductId = p.ProductId,
                JobOrderInspectionRequestHeaderId = p.JobOrderInspectionRequestHeaderId,
                JobOrderLineId = p.JobOrderLineId,
                Dimension1Id = p.Dimension1Id,
                Dimension2Id = p.Dimension2Id,
                Dimension3Id = p.Dimension3Id,
                Dimension4Id = p.Dimension4Id,
                Dimension1Name = p.Dimension1Name,
                Dimension2Name = p.Dimension2Name,
                Dimension3Name = p.Dimension3Name,
                Dimension4Name = p.Dimension4Name,
                Specification = p.Specification,
                UnitId = p.UnitId,
                UnitName = p.UnitName,
                unitDecimalPlaces = p.unitDecimalPlaces,
                DealunitDecimalPlaces = p.DealunitDecimalPlaces,
                ProductUidName = p.ProductUidName,
                ProductUidId = p.ProductUidId,
                JobOrderHeaderId = p.JobOrderHeaderId,
                ProdOrderLineId = p.ProdOrderLineId,
            }).ToList());
        }
Beispiel #17
0
        public JsonResult GetSummary(IEnumerable <MaterialPlanForSaleOrderViewModel> selectedRec, int DocTypeId)
        {
            int SiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];
            int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];

            //MaterialPlanSettings Setting = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(DocTypeId, DivisionId, SiteId);
            var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(DocTypeId, DivisionId, SiteId);

            var ProductIds = selectedRec.Select(m => m.ProductId).ToArray();
            List <MaterialPlanLineViewModel> Line = new List <MaterialPlanLineViewModel>();

            if (selectedRec != null)
            {
                System.Web.HttpContext.Current.Session["BuyerId"] = selectedRec.FirstOrDefault().BuyerId;
            }

            System.Web.HttpContext.Current.Session["SODyeingPlan"] = selectedRec;

            if (settings.SqlProcConsumptionSummary != null)
            {
                var prodorderlinelist = selectedRec.Where(m => m.Qty > 0).Select(m => new { m.SaleOrderLineId, m.Qty });

                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("SaleOrderLineId");
                dataTable.Columns.Add("ProductId");
                dataTable.Columns.Add("Qty");


                foreach (var item in prodorderlinelist)
                {
                    var dr = dataTable.NewRow();
                    dr["SaleOrderLineId"] = item.SaleOrderLineId;
                    dr["ProductId"]       = null;
                    dr["Qty"]             = item.Qty;
                    dataTable.Rows.Add(dr);
                }
                DataSet ds = new DataSet();
                using (SqlConnection sqlConnection = new SqlConnection((string)System.Web.HttpContext.Current.Session["DefaultConnectionString"]))
                {
                    sqlConnection.Open();
                    using (SqlCommand cmd = new SqlCommand(settings.SqlProcConsumptionSummary))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection  = sqlConnection;
                        cmd.Parameters.AddWithValue("@T", dataTable);
                        cmd.Parameters.AddWithValue("@MaterialPlanHeaderId", 0);
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(ds);
                        }
                    }
                }

                DataTable dt2 = ds.Tables[0];

                foreach (DataRow dr in dt2.Rows)
                {
                    MaterialPlanLineViewModel line = new MaterialPlanLineViewModel();

                    line.ProductId    = dr["ProductId"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["ProductId"].ToString());
                    line.ProcessId    = dr["ProcessId"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["ProcessId"].ToString());
                    line.Dimension1Id = dr["Dimension1Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension1Id"]);
                    line.Dimension2Id = dr["Dimension2Id"] == System.DBNull.Value ? null : (int?)Convert.ToInt32(dr["Dimension2Id"]);


                    line.ProductName       = dr["ProductName"] == System.DBNull.Value ? null : dr["ProductName"].ToString();
                    line.Specification     = dr["Specification"] == System.DBNull.Value ? null : dr["Specification"].ToString();
                    line.UnitName          = dr["UnitName"] == System.DBNull.Value ? null : dr["UnitName"].ToString();
                    line.unitDecimalPlaces = dr["DecimalPlaces"] == System.DBNull.Value ? 0 : Convert.ToInt32(dr["DecimalPlaces"].ToString());
                    line.RequiredQty       = dr["Qty"] == System.DBNull.Value ? 0 : Convert.ToDecimal(dr["Qty"].ToString());
                    line.Dimension1Name    = dr["Dimension1Name"] == System.DBNull.Value ? null : dr["Dimension1Name"].ToString();
                    line.Dimension2Name    = dr["Dimension2Name"] == System.DBNull.Value ? null : dr["Dimension2Name"].ToString();
                    line.ProcessName       = dr["ProcessName"] == System.DBNull.Value ? null : dr["ProcessName"].ToString();


                    line.ExcessStockQty       = 0;
                    line.MaterialPlanHeaderId = 0;


                    line.ProdPlanQty  = (dr["PurchProd"].ToString() == "Purchase") ? 0 : Convert.ToDecimal(dr["Qty"].ToString());
                    line.PurchPlanQty = (dr["PurchProd"].ToString() == "Purchase") ? Convert.ToDecimal(dr["Qty"].ToString()) : 0;

                    line.GeneratedFor = MaterialPlanConstants.SaleOrder;

                    Line.Add(line);
                }
            }
            else
            {
                //var summary = (from p in db.Product.Where(p => ProductIds.Contains(p.ProductId)).AsEnumerable()
                //               join t in selectedRec on p.ProductId equals t.ProductId
                //               where t.Qty > 0
                //               group t by new { t.ProductId, p.ProductName, t.Specification } into g
                //               join p1 in db.Product.Where(p => ProductIds.Contains(p.ProductId)).AsEnumerable() on g.Key.ProductId equals p1.ProductId
                //               join u1 in db.Units on p1.UnitId equals u1.UnitId
                //               select new
                //               {
                //                   id = g.Key.ProductId,
                //                   QtySum = g.Sum(m => m.Qty),
                //                   //GroupedItems = g,
                //                   name = g.Key.ProductName,
                //                   unitname = u1.UnitName,
                //                   Specification = g.Key.Specification,
                //                   Fractionunits = u1.DecimalPlaces
                //               }).ToList();


                var summary = (from t in selectedRec
                               join p in db.Product on t.ProductId equals p.ProductId into ProductTable from ProductTab in ProductTable.DefaultIfEmpty()
                               join d1 in db.Dimension1 on t.Dimension1Id equals d1.Dimension1Id into Dimension1Table from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                               join d2 in db.Dimension2 on t.Dimension2Id equals d2.Dimension2Id into Dimension2Table
                               from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                               where t.Qty > 0
                               group t by new { t.ProductId, t.Dimension1Id, t.Dimension2Id, t.Specification } into g
                               join p1 in db.Product.Where(p => ProductIds.Contains(p.ProductId)).AsEnumerable() on g.Key.ProductId equals p1.ProductId
                               join u1 in db.Units on p1.UnitId equals u1.UnitId
                               select new
                {
                    id = g.Key.ProductId,
                    Dimension1Id = g.Key.Dimension1Id,
                    Dimension2Id = g.Key.Dimension2Id,
                    QtySum = g.Sum(m => m.Qty),
                    //GroupedItems = g,
                    name = g.Max(m => m.ProductName),
                    unitname = u1.UnitName,
                    Dimension1Name = g.Max(m => m.Dimension1Name),
                    Dimension2Name = g.Max(m => m.Dimension2Name),
                    Specification = g.Key.Specification,
                    Fractionunits = u1.DecimalPlaces
                }).ToList();

                int j = 0;
                foreach (var item in summary)
                {
                    MaterialPlanLineViewModel planline = new MaterialPlanLineViewModel();
                    planline.ProductName          = item.name;
                    planline.Dimension1Name       = item.Dimension1Name;
                    planline.Dimension2Name       = item.Dimension2Name;
                    planline.Dimension1Id         = item.Dimension1Id;
                    planline.Dimension2Id         = item.Dimension2Id;
                    planline.RequiredQty          = item.QtySum;
                    planline.ExcessStockQty       = 0;
                    planline.Specification        = item.Specification;
                    planline.MaterialPlanHeaderId = 0;
                    planline.ProductId            = item.id;
                    planline.ProdPlanQty          = item.QtySum;
                    planline.UnitName             = item.unitname;
                    planline.unitDecimalPlaces    = item.Fractionunits;
                    planline.GeneratedFor         = MaterialPlanConstants.SaleOrder;
                    Line.Add(planline);

                    j++;
                }
            }



            MaterialPlanSummaryViewModel Summary = new MaterialPlanSummaryViewModel();

            //Summary.MaterialPlanSettings = Setting;
            Summary.MaterialPlanSettings = Mapper.Map <MaterialPlanSettings, MaterialPlanSettingsViewModel>(settings);

            var data = Line.OrderBy(m => m.ProductName).ThenBy(m => m.Dimension1Name).ThenBy(m => m.Dimension2Name)
                       .Select((m, i) => new
            {
                Dimension1Id            = m.Dimension1Id,
                Dimension1Name          = m.Dimension1Name,
                Dimension2Id            = m.Dimension2Id,
                Dimension2Name          = m.Dimension2Name,
                DueDate                 = m.DueDate,
                MaterialPlanHeaderDocNo = m.MaterialPlanHeaderDocNo,
                MaterialPlanHeaderId    = m.MaterialPlanHeaderId,
                MaterialPlanLineId      = m.MaterialPlanLineId,
                ProcessId               = m.ProcessId,
                ProcessName             = m.ProcessName,
                ProdPlanQty             = m.ProdPlanQty,
                ProductId               = m.ProductId,
                ProductName             = m.ProductName,
                PurchPlanQty            = m.PurchPlanQty,
                Remark            = m.Remark,
                RequiredQty       = m.RequiredQty,
                Specification     = m.Specification,
                StockPlanQty      = m.StockPlanQty,
                unitDecimalPlaces = m.unitDecimalPlaces,
                UnitId            = m.UnitId,
                UnitName          = m.UnitName,
                Id = i
            })
                       .ToList();

            return(Json(new { success = true, data = data }, JsonRequestBehavior.AllowGet));
        }
        public IEnumerable <JobOrderHeaderListViewModel> GetPendingJobOrdersForAC(int HeaderId, string term, int Limiter)//Product Id
        {
            var DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
            var SiteId     = (int)System.Web.HttpContext.Current.Session["SiteId"];

            var InspectionRequestHeader = db.JobOrderInspectionRequestHeader.Find(HeaderId);

            var settings = new JobOrderInspectionRequestSettingsService(db).GetJobOrderInspectionRequestSettingsForDocument(InspectionRequestHeader.DocTypeId, InspectionRequestHeader.DivisionId, InspectionRequestHeader.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.ViewJobOrderBalanceForInspectionRequest
                         join D1 in db.Dimension1 on p.Dimension1Id equals D1.Dimension1Id into Dimension1Table
                         from Dimension1Tab in Dimension1Table.DefaultIfEmpty()
                         join D2 in db.Dimension2 on p.Dimension2Id equals D2.Dimension2Id into Dimension2Table
                         from Dimension2Tab in Dimension2Table.DefaultIfEmpty()
                         join D3 in db.Dimension3 on p.Dimension3Id equals D3.Dimension3Id into Dimension3Table
                         from Dimension3Tab in Dimension3Table.DefaultIfEmpty()
                         join D4 in db.Dimension4 on p.Dimension4Id equals D4.Dimension4Id into Dimension4Table
                         from Dimension4Tab in Dimension4Table.DefaultIfEmpty()
                         join t3 in db.Product on p.ProductId equals t3.ProductId
                         join t4 in db.JobOrderHeader on p.JobOrderHeaderId equals t4.JobOrderHeaderId
                         where p.BalanceQty > 0 && p.JobWorkerId == InspectionRequestHeader.JobWorkerId && t4.ProcessId == InspectionRequestHeader.ProcessId
                         //&& ((string.IsNullOrEmpty(term) ? 1 == 1 : p.JobOrderNo.ToLower().Contains(term.ToLower()))
                         //|| (string.IsNullOrEmpty(term) ? 1 == 1 : dim1.Dimension1Name.ToLower().Contains(term.ToLower()))
                         //|| (string.IsNullOrEmpty(term) ? 1 == 1 : dim2.Dimension2Name.ToLower().Contains(term.ToLower()))
                         //|| (string.IsNullOrEmpty(term) ? 1 == 1 : t3.ProductName.ToLower().Contains(term.ToLower())))
                         orderby p.JobOrderNo
                         select new
            {
                DocNo = p.JobOrderNo,
                JobOrderLineId = p.JobOrderLineId,
                Dimension1Name = Dimension1Tab.Dimension1Name,
                Dimension2Name = Dimension2Tab.Dimension2Name,
                Dimension3Name = Dimension3Tab.Dimension3Name,
                Dimension4Name = Dimension4Tab.Dimension4Name,
                ProductName = t3.ProductName,
                BalanceQty = p.BalanceQty,
                SiteId = t4.SiteId,
                DivisionId = t4.DivisionId,
                DocTypeId = t4.DocTypeId,
            });

            if (!string.IsNullOrEmpty(term))
            {
                Query = Query.Where(m => m.DocNo.Contains(term.ToLower()) || m.Dimension1Name.Contains(term.ToLower()) ||
                                    m.Dimension2Name.Contains(term.ToLower()) ||
                                    m.Dimension3Name.Contains(term.ToLower()) ||
                                    m.Dimension4Name.Contains(term.ToLower()) ||
                                    m.ProductName.ToLower().Contains(term.ToLower()));
            }

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

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


            return((from p in Query
                    select new JobOrderHeaderListViewModel
            {
                DocNo = p.DocNo,
                JobOrderLineId = p.JobOrderLineId,
                Dimension1Name = p.Dimension1Name,
                Dimension2Name = p.Dimension2Name,
                Dimension3Name = p.Dimension3Name,
                Dimension4Name = p.Dimension4Name,
                ProductName = p.ProductName,
                BalanceQty = p.BalanceQty,
            }).Take(Limiter));
        }