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 IQueryable <ComboBoxResult> GetCustomPerson(int Id, string term)
{
int DocTypeId = Id;
int SiteId = (int)System.Web.HttpContext.Current.Session["SiteId"];
int DivisionId = (int)System.Web.HttpContext.Current.Session["DivisionId"];
var settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(DocTypeId, DivisionId, SiteId);
string[] PersonRoles = null;
if (!string.IsNullOrEmpty(settings.filterPersonRoles))
{
PersonRoles = settings.filterPersonRoles.Split(",".ToCharArray());
}
else
{
PersonRoles = new string[] { "NA" };
}
string DivIdStr = "|" + DivisionId.ToString() + "|";
string SiteIdStr = "|" + SiteId.ToString() + "|";
int ProcessId = new ProcessService(_unitOfWork).Find(ProcessConstants.Sales).ProcessId;
var list = (from p in db.Persons
join bus in db.BusinessEntity on p.PersonID equals bus.PersonID into BusinessEntityTable
from BusinessEntityTab in BusinessEntityTable.DefaultIfEmpty()
join pp in db.PersonProcess on p.PersonID equals pp.PersonId into PersonProcessTable
from PersonProcessTab in PersonProcessTable.DefaultIfEmpty()
join pr in db.PersonRole on p.PersonID equals pr.PersonId into PersonRoleTable
from PersonRoleTab in PersonRoleTable.DefaultIfEmpty()
where PersonProcessTab.ProcessId == ProcessId &&
(string.IsNullOrEmpty(term) ? 1 == 1 : (p.Name.ToLower().Contains(term.ToLower()) || p.Code.ToLower().Contains(term.ToLower()))) &&
(string.IsNullOrEmpty(settings.filterPersonRoles) ? 1 == 1 : PersonRoles.Contains(PersonRoleTab.RoleDocTypeId.ToString())) &&
BusinessEntityTab.DivisionIds.IndexOf(DivIdStr) != -1 &&
BusinessEntityTab.SiteIds.IndexOf(SiteIdStr) != -1 &&
(p.IsActive == null ? 1 == 1 : p.IsActive == true)
group new { p } by new { p.PersonID } into Result
orderby Result.Max(m => m.p.Name)
select new ComboBoxResult
{
id = Result.Key.PersonID.ToString(),
text = Result.Max(m => m.p.Name + ", " + m.p.Suffix + " [" + m.p.Code + "]"),
}
);
return(list);
}
public IQueryable <ComboBoxResult> GetPendingPlanningHelpList(int Id, string term)
{
var Header = new MaterialPlanCancelHeaderService(_unitOfWork).Find(Id);
var Settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(Header.DocTypeId, Header.DivisionId, Header.SiteId);
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 = db.ViewMaterialPlanBalance.AsQueryable();
if (!string.IsNullOrEmpty(term))
{
list = list.Where(m => m.MaterialPlanNo.ToLower().Contains(term.ToLower()));
}
if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
{
list = list.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
var Jh = list.Join(db.MaterialPlanHeader,
m => m.MaterialPlanHeaderId,
t => t.MaterialPlanHeaderId,
(v, h) => new { v, h }).Where(m => m.h.BuyerId == Header.BuyerId).Select(m => m.v);
var Result = Jh.GroupBy(m => m.MaterialPlanHeaderId,
(k, e) => new ComboBoxResult
{
id = k.ToString(),
text = e.Max(m => m.MaterialPlanNo + " | " + m.DocType.DocumentTypeName),
}).OrderBy(m => m.text);
return(Result);
}
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 <MaterialPlanCancelLineViewModel> GetOrderPlanForFilters(MaterialPlanCancelFilterViewModel vm)
{
var Header = new MaterialPlanCancelHeaderService(_unitOfWork).Find(vm.MaterialPlanCancelHeaderId);
var Settings = new MaterialPlanSettingsService(_unitOfWork).GetMaterialPlanSettingsForDocument(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[] ProductIdArr = null;
if (!string.IsNullOrEmpty(vm.ProductId))
{
ProductIdArr = vm.ProductId.Split(",".ToCharArray());
}
else
{
ProductIdArr = new string[] { "NA" };
}
string[] SaleOrderIdArr = null;
if (!string.IsNullOrEmpty(vm.MaterialPlanHeaderId))
{
SaleOrderIdArr = vm.MaterialPlanHeaderId.Split(",".ToCharArray());
}
else
{
SaleOrderIdArr = new string[] { "NA" };
}
string[] ProductGroupIdArr = null;
if (!string.IsNullOrEmpty(vm.ProductGroupId))
{
ProductGroupIdArr = vm.ProductGroupId.Split(",".ToCharArray());
}
else
{
ProductGroupIdArr = new string[] { "NA" };
}
string[] Dimension1IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension1Id))
{
Dimension1IdArr = vm.Dimension1Id.Split(",".ToCharArray());
}
else
{
Dimension1IdArr = new string[] { "NA" };
}
string[] Dimension2IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension2Id))
{
Dimension2IdArr = vm.Dimension2Id.Split(",".ToCharArray());
}
else
{
Dimension2IdArr = new string[] { "NA" };
}
string[] Dimension3IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension3Id))
{
Dimension3IdArr = vm.Dimension3Id.Split(",".ToCharArray());
}
else
{
Dimension3IdArr = new string[] { "NA" };
}
string[] Dimension4IdArr = null;
if (!string.IsNullOrEmpty(vm.Dimension4Id))
{
Dimension4IdArr = vm.Dimension4Id.Split(",".ToCharArray());
}
else
{
Dimension4IdArr = new string[] { "NA" };
}
string[] ProcessIdArr = null;
if (!string.IsNullOrEmpty(vm.ProcessId))
{
ProcessIdArr = vm.ProcessId.Split(",".ToCharArray());
}
else
{
ProcessIdArr = new string[] { "NA" };
}
var Query = db.ViewMaterialPlanBalance.AsQueryable()
.Join(db.MaterialPlanLine,
m => m.MaterialPlanLineId,
om => om.MaterialPlanLineId,
(m, p) => new { m, p })
.Join(db.Product, m => m.m.ProductId, t => t.ProductId,
(m, j) => new
{
MaterialPlanLineId = m.m.MaterialPlanLineId,
BalanceQty = m.m.BalanceQty,
MaterialPlanHeaderId = m.m.MaterialPlanHeaderId,
MaterialPlanNo = m.m.MaterialPlanNo,
ProductId = m.m.ProductId,
MaterialPlanDate = m.m.MaterialPlanDate,
DocTypeId = m.m.DocTypeId,
DocTypeName = m.m.DocType.DocumentTypeName,
ProductGroupId = j.ProductGroupId,
Dimension1Name = m.p.Dimension1.Dimension1Name,
Dimenstion2Name = m.p.Dimension2.Dimension2Name,
ProcessName = m.p.Process.ProcessName,
ProductName = j.ProductName,
Specification = m.p.Specification,
unitDecimalPlaces = j.Unit.DecimalPlaces,
UnitId = j.UnitId,
MaterialPlanDocNo = m.m.MaterialPlanNo,
UnitName = j.Unit.UnitName,
BuyerId = m.p.MaterialPlanHeader.BuyerId,
Dimension1Id = m.p.Dimension1Id,
Dimension2Id = m.p.Dimension2Id,
Dimension3Id = m.p.Dimension3Id,
Dimension4Id = m.p.Dimension4Id,
ProcessId = m.p.ProcessId,
});
if (!string.IsNullOrEmpty(Settings.filterContraDocTypes))
{
Query = Query.Where(m => contraDocTypes.Contains(m.DocTypeId.ToString()));
}
if (!string.IsNullOrEmpty(vm.ProductId))
{
Query = Query.Where(m => ProductIdArr.Contains(m.ProductId.ToString()));
}
if (!string.IsNullOrEmpty(vm.MaterialPlanHeaderId))
{
Query = Query.Where(m => SaleOrderIdArr.Contains(m.MaterialPlanHeaderId.ToString()));
}
if (!string.IsNullOrEmpty(vm.ProductGroupId))
{
Query = Query.Where(m => ProductGroupIdArr.Contains(m.ProductGroupId.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension1Id))
{
Query = Query.Where(m => Dimension1IdArr.Contains(m.Dimension1Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension2Id))
{
Query = Query.Where(m => Dimension2IdArr.Contains(m.Dimension2Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension3Id))
{
Query = Query.Where(m => Dimension3IdArr.Contains(m.Dimension3Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.Dimension4Id))
{
Query = Query.Where(m => Dimension4IdArr.Contains(m.Dimension4Id.ToString()));
}
if (!string.IsNullOrEmpty(vm.ProcessId))
{
Query = Query.Where(m => ProcessIdArr.Contains(m.ProcessId.ToString()));
}
var Result = Query.Where(m => m.BuyerId == Header.BuyerId).Select(m => new MaterialPlanCancelLineViewModel
{
BalanceQty = m.BalanceQty,
Dimension1Name = m.Dimension1Name,
Dimension2Name = m.Dimenstion2Name,
MaterialPlanCancelHeaderDocNo = Header.DocNo,
MaterialPlanDocNo = m.MaterialPlanDocNo,
MaterialPlanCancelHeaderId = Header.MaterialPlanCancelHeaderId,
MaterialPlanLineId = m.MaterialPlanLineId,
ProcessName = m.ProcessName,
ProductName = m.ProductName,
Qty = m.BalanceQty,
Specification = m.Specification,
unitDecimalPlaces = m.unitDecimalPlaces,
UnitId = m.UnitId,
UnitName = m.UnitName
});
return(Result);
}
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);
}