public IQueryable <ComboBoxResult> GetCustomProducts(int Id, string term)
{
var MaterialPlan = new MaterialPlanHeaderService(_unitOfWork).Find(Id);
var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(MaterialPlan.DocTypeId, MaterialPlan.DivisionId, MaterialPlan.SiteId);
string[] ProductTypes = null;
if (!string.IsNullOrEmpty(settings.filterProductTypes))
{
ProductTypes = settings.filterProductTypes.Split(",".ToCharArray());
}
else
{
ProductTypes = new string[] { "NA" };
}
string[] Products = null;
if (!string.IsNullOrEmpty(settings.filterProducts))
{
Products = settings.filterProducts.Split(",".ToCharArray());
}
else
{
Products = new string[] { "NA" };
}
string[] ProductGroups = null;
if (!string.IsNullOrEmpty(settings.filterProductGroups))
{
ProductGroups = settings.filterProductGroups.Split(",".ToCharArray());
}
else
{
ProductGroups = new string[] { "NA" };
}
return(from p in db.Product
where (string.IsNullOrEmpty(settings.filterProductTypes) ? 1 == 1 : ProductTypes.Contains(p.ProductGroup.ProductTypeId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProducts) ? 1 == 1 : Products.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(settings.filterProductGroups) ? 1 == 1 : ProductGroups.Contains(p.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(term) ? 1 == 1 : p.ProductName.ToLower().Contains(term.ToLower()))
orderby p.ProductName
select new ComboBoxResult
{
id = p.ProductId.ToString(),
text = p.ProductName,
});
}
public IEnumerable <SaleOrderLineListViewModel> GetSaleOrdersForDocumentType(int HeaderId, string term)
{
//return (from p in db.SaleOrderHeader
// where DocTypeIds.Contains(p.DocTypeId.ToString())
// orderby p.DocDate descending, p.DocNo descending
// select new SaleOrderLineListViewModel
// {
// DocNo = p.DocNo,
// SaleOrderHeaderId = p.SaleOrderHeaderId
// }
// );
var Header = new MaterialPlanHeaderService(_unitOfWork).Find(HeaderId);
var Settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
SqlParameter SqlParameterDocType = new SqlParameter("@PlanningDocumentType", Header.DocTypeId);
SqlParameter SqlParameterSite = new SqlParameter("@Site", Header.SiteId);
SqlParameter SqlParameterDivision = new SqlParameter("@Division", Header.DivisionId);
SqlParameter SqlParameterBuyer = new SqlParameter("@BuyerId", Header.BuyerId.HasValue ? Header.BuyerId : (object)DBNull.Value);
string ProcName = Settings.PendingProdOrderList;
if (string.IsNullOrEmpty(ProcName))
{
throw new Exception("Pending ProdOrders not configured");
}
IEnumerable <PendingSaleOrderFromProc> CalculationLineList = db.Database.SqlQuery <PendingSaleOrderFromProc>("" + ProcName + " @PlanningDocumentType, @Site, @Division, @BuyerId", SqlParameterDocType, SqlParameterSite, SqlParameterDivision, SqlParameterBuyer).ToList();
var list = (from p in CalculationLineList
where p.SaleOrderNo.ToLower().Contains(term.ToLower())
group new { p } by p.SaleOrderHeaderId into g
select new SaleOrderLineListViewModel
{
DocNo = g.Max(m => m.p.SaleOrderNo),
SaleOrderHeaderId = g.Key
}
);
return(list.ToList());
}
public IEnumerable <MaterialPlanForSaleOrderViewModel> GetSaleOrdersForFilters(MaterialPlanForLineFilterViewModel 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.SaleOrderHeaderId))
{
SaleOrderIdArr = svm.SaleOrderHeaderId.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[] 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[] BuyerIdArr = null;
if (!string.IsNullOrEmpty(svm.BuyerId))
{
BuyerIdArr = svm.BuyerId.Split(",".ToCharArray());
}
else
{
BuyerIdArr = new string[] { "NA" };
}
var Header = new MaterialPlanHeaderService(_unitOfWork).Find(svm.MaterialPlanHeaderId);
var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
string ProcName = settings.PendingProdOrderList;
if (string.IsNullOrEmpty(ProcName))
{
throw new Exception("Pending ProdOrders Not Configured");
}
SqlParameter SqlParameterDocType = new SqlParameter("@PlanningDocumentType", svm.DocTypeId);
SqlParameter SqlParameterSite = new SqlParameter("@Site", Header.SiteId);
SqlParameter SqlParameterDivision = new SqlParameter("@Division", Header.DivisionId);
SqlParameter SqlParameterBuyer = new SqlParameter("@BuyerId", (Header.BuyerId.HasValue ? Header.BuyerId : (object)DBNull.Value));
IEnumerable <PendingSaleOrderFromProc> PendingSaleOrders = db.Database.SqlQuery <PendingSaleOrderFromProc>(" " + ProcName + " @PlanningDocumentType, @Site, @Division, @BuyerId", SqlParameterDocType, SqlParameterSite, SqlParameterDivision, SqlParameterBuyer).ToList();
//var ProductIds=(PendingSaleOrders.Select(m=>m.ProductId)).ToArray();
//var ProductGroupIds = (from p in db.Product.Where(m => ProductIdArr.Contains(m.ProductId.ToString()))
// select p.ProductGroupId).ToArray();
var resu = (from p in PendingSaleOrders
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.SaleOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.SaleOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension1Id) ? 1 == 1 : Dimension1IdArr.Contains(p.Dimension1Id.ToString())) &&
(string.IsNullOrEmpty(svm.Dimension2Id) ? 1 == 1 : Dimension2IdArr.Contains(p.Dimension2Id.ToString())) &&
(string.IsNullOrEmpty(svm.BuyerId) ? 1 == 1 : BuyerIdArr.Contains(p.BuyerId.ToString())) &&
p.BalanceQty > 0
orderby p.Sr
select new MaterialPlanForSaleOrderViewModel
{
BalanceQtyForPlan = p.BalanceQty,
Qty = p.BalanceQty,
SaleOrderDocNo = p.SaleOrderNo,
ProductId = p.ProductId,
ProductName = p.ProductName,
Dimension1Id = p.Dimension1Id,
Dimension1Name = p.Dimension1Name,
Dimension2Id = p.Dimension2Id,
Dimension2Name = p.Dimension2Name,
MaterialPlanHeaderId = svm.MaterialPlanHeaderId,
SaleOrderLineId = p.SaleOrderLineId,
Specification = p.Specification,
UnitName = p.UnitName,
BomDetailExists = p.BomDetailExists,
}).ToList();
return(resu);
}
public IEnumerable <MaterialPlanForSaleOrderViewModel> GetProdOrdersForFilters(MaterialPlanLineForProductionFilterViewModel 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.ProdOrderHeaderId))
{
SaleOrderIdArr = svm.ProdOrderHeaderId.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 Header = new MaterialPlanHeaderService(_unitOfWork).Find(svm.MaterialPlanHeaderId);
string ProcName = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId).PendingProdOrderList;
SqlParameter SqlParameterProductId = new SqlParameter("@MaterialPlanHeaderId", svm.MaterialPlanHeaderId);
IEnumerable <ProdOrderBalanceViewModel> StockAvailableForPacking = db.Database.SqlQuery <ProdOrderBalanceViewModel>("" + ProcName + " @MaterialPlanHeaderId", SqlParameterProductId).ToList();
var ProductIds = StockAvailableForPacking.Select(m => m.ProductId).ToArray();
var ProdOrderLineIds = StockAvailableForPacking.Select(m => m.ProdOrderLineId).ToArray();
var temp1 = from p in StockAvailableForPacking
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.ProdOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.ProdOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) &&
p.BalanceQty > 0 && (svm.DocDate.HasValue ? p.ProdOrderDate.Date <= svm.DocDate.Value.Date : 1 == 1)
select new MaterialPlanForSaleOrderViewModel
{
BalanceQtyForPlan = p.BalanceQty,
Qty = p.BalanceQty,
ProdOrderDocNo = p.ProdOrderNo,
ProductId = p.ProductId,
ProductName = p.ProductName,
MaterialPlanHeaderId = svm.MaterialPlanHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
UnitName = p.UnitName,
ProcessId = p.ProcessId,
ProcessName = p.ProcessName,
BomDetailExists = p.IsBomExist,
};
var temp2 = (from p in StockAvailableForPacking
where (string.IsNullOrEmpty(svm.ProductId) ? 1 == 1 : ProductIdArr.Contains(p.ProductId.ToString())) &&
(string.IsNullOrEmpty(svm.ProdOrderHeaderId) ? 1 == 1 : SaleOrderIdArr.Contains(p.ProdOrderHeaderId.ToString())) &&
(string.IsNullOrEmpty(svm.ProductGroupId) ? 1 == 1 : ProductGroupIdArr.Contains(p.ProductGroupId.ToString())) &&
p.BalanceQty > 0 && (svm.DocDate.HasValue ? p.ProdOrderDate.Date <= svm.DocDate.Value.Date : 1 == 1)
select new MaterialPlanForSaleOrderViewModel
{
BalanceQtyForPlan = p.BalanceQty,
Qty = p.BalanceQty,
ProdOrderDocNo = p.ProdOrderNo,
ProductId = p.ProductId,
ProductName = p.ProductName,
MaterialPlanHeaderId = svm.MaterialPlanHeaderId,
ProdOrderLineId = p.ProdOrderLineId,
Dimension1Id = p.Dimension1Id,
Dimension2Id = p.Dimension2Id,
Dimension1Name = p.Dimension1Name,
Dimension2Name = p.Dimension2Name,
UnitName = p.UnitName,
ProcessId = p.ProcessId,
ProcessName = p.ProcessName,
BomDetailExists = p.IsBomExist,
}).ToList();
var DocTypeIds = StockAvailableForPacking.Select(m => m.DocTypeId).ToArray();
return(temp2);
}
