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,
}
);
}
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);
}
}
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);
}
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,
}
);
}
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);
}
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());
}
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));
}