public Material GetMaterial(string name)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Materials.FirstOrDefault(c => c.Name == name.ToUpper()));
}
}
//public IEnumerable<Item> GetUniqueItemsAndUnquieSKU(List<Item> items)
//{
// using (var context = new ManufacturingDataContext(_connectionString))
// {
// var uniqueItems = new List<Item>();
// foreach (var item in items)
// {
// var testItem = context.Items.FirstOrDefault(i => (i.DepartmentId == item.DepartmentId && i.MaterialId == item.MaterialId && i.BodyStyleId == item.BodyStyleId
// && i.ColorId == item.ColorId && i.SleeveId == item.SleeveId && i.SizeId == item.SizeId) || i.SKU == item.SKU);
// if (testItem == null)
// {
// uniqueItems.Add(item);
// }
// if (!context.Items.Any(i => i.DepartmentId == item.DepartmentId && i.FabricId == item.FabricId && i.BodyStyle == item.BodyStyle
// && i.Color == item.Color && i.Sleeve == item.Sleeve && i.Size == item.Size) || !context.Items.Any(i => i.SKU == item.SKU))
// {
// uniqueItems.Add(item);
// }
// }
// return uniqueItems;
// }
//}
//public void AddItems(IEnumerable<Item> items)
//{
// using (var context = new ManufacturingDataContext(_connectionString))
// {
// context.Items.InsertAllOnSubmit(items);
// context.SubmitChanges();
// }
//}
public Item GetItem(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Items.FirstOrDefault(i => i.Id == id));
}
}
public Item GetItem(string sku)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Items.FirstOrDefault(i => i.SKU == sku.ToUpper()));
}
}
public IEnumerable <ItemWithQuantity> GetItemsWithQuantitys(bool newway)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
var loadoptions = new DataLoadOptions();
loadoptions.LoadWith <CuttingInstructionItem>(c => c.Item);
context.LoadOptions = loadoptions;
var seasons = context.PlannedProductions.Where(pp => !pp.Archived).OrderByDescending(s => s.CreatedOn).Take(2).SelectMany(p => p.CuttingInstructions.SelectMany(pd => pd.CuttingInstructionDetails.SelectMany(pdi => pdi.CuttingInstructionItems)));
//var random = context.CuttingInstructions.Where(ci => !ci.Completed && ci.PlannedProductionId == null).SelectMany(c => c.CuttingInstructionDetails.SelectMany(ci => ci.CuttingInstructionItems));
var random = context.CuttingInstructions.Where(i => (i.CuttingInstructionDetails
.Count() > 0 ? i.CuttingInstructionDetails
.Sum(co => co.CuttingInstructionItems.Sum(d => d.Quantity)) : 0)
!= (i.ReceivingItemsTransactions
.Count() > 0 ? i.ReceivingItemsTransactions.Sum(d => d.Quantity) : 0) && i.PlannedProductionId == null).SelectMany(c => c.CuttingInstructionDetails.SelectMany(ci => ci.CuttingInstructionItems));
return(seasons.Concat(random).GroupBy(i => i.ItemId)
.ToList()
.Select(ite =>
{
var it = ite.FirstOrDefault();
return new ItemWithQuantity
{
Item = it.Item,
LastCuttingInstructionDatePretty = ite.OrderByDescending(p => p.CuttingInstructionDetail.CuttingInstruction.Production.Date).Select(p => p.CuttingInstructionDetail.CuttingInstruction.Production.Date).FirstOrDefault().ToShortDateString(),
Quantitys = new ItemQuantity
{
AmountOrdered = ite.Sum(p => p.Quantity),
AmountReceived = it.Item.ReceivingItemsTransactions.Where(i => i.ItemId == it.Id).Count() > 0 ? it.Item.ReceivingItemsTransactions.Where(i => i.ItemId == it.Id).Sum(p => p.Quantity) : 0
}
};
}).ToList());
}
}
public IEnumerable <CuttingInstructionItem> GetItemsForBarcodes(int productionId)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.CuttingInstructionItems.Where(p => p.CuttingInstructionDetail.CuttingInstruction.ProductionId == productionId).ToList());
}
}
//UPDATE HISTORY
public IEnumerable <UpdateHistory> GetUpdateHistories()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.UpdateHistories.ToList());
}
}
//public IEnumerable<ItemWithQuantity> GetItemsInProduction()
//{
// using (var context = new ManufacturingDataContext(_connectionString))
// {
// var loadoptions = new DataLoadOptions();
// loadoptions.LoadWith<CuttingInstructionItem>(c => c.Item);
// context.LoadOptions = loadoptions;
// return context.CuttingInstructionItems.GroupBy(i => i.ItemId).Where(ite =>
// ite.Sum(p => p.Quantity) - (ite.FirstOrDefault().Item.ReceivingItemsTransactions.Where(i => i.ItemId == ite.FirstOrDefault().ItemId).Count() > 0 ? ite.FirstOrDefault().Item.ReceivingItemsTransactions.Where(i => i.ItemId == ite.FirstOrDefault().ItemId).Sum(p => p.Quantity) : 0) > 0
// )
// .ToList()
// .Select(ite =>
// {
// var it = ite.FirstOrDefault();
// return new ItemWithQuantity
// {
// Item = it.Item,
// LastCuttingInstructionDate = ite.OrderByDescending(p => p.CuttingInstructionDetail.CuttingInstruction.Production.Date).Select(p => p.CuttingInstructionDetail.CuttingInstruction.Production.Date).FirstOrDefault(),
// LastCuttingInstructionDatePretty = ite.OrderByDescending(p => p.CuttingInstructionDetail.CuttingInstruction.Production.Date).Select(p => p.CuttingInstructionDetail.CuttingInstruction.Production.Date).FirstOrDefault().ToShortDateString(),
// Quantitys = new ItemQuantity
// {
// AmountOrdered = ite.Sum(p => p.Quantity),
// AmountReceived = it.Item.ReceivingItemsTransactions.Where(i => i.ItemId == it.ItemId).Count() > 0 ? it.Item.ReceivingItemsTransactions.Where(i => i.ItemId == it.ItemId).Sum(p => p.Quantity) : 0
// }
// };
// }).ToList();
// }
//}
public IEnumerable <ItemsWithDetailsResult> GetItemsInProduction()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.ItemsWithDetails().ToList());
}
}
public ReceivingItemsTransaction GetReceivingItemsTransaction(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.ReceivingItemsTransactions.FirstOrDefault(r => r.Id == id));
}
}
public IEnumerable <Department> GetDepartments()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Departments.ToList());
}
}
public Department GetDepartment(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Departments.FirstOrDefault(c => c.Id == id));
}
}
public IEnumerable <CuttingInstruction> GetNotCompleteInstructionForLots()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.CuttingInstructions.Where(ct => !ct.Completed).ToList());
}
}
public ProductionCatergory GetProductionCatergory(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.ProductionCatergories.FirstOrDefault(pc => pc.Id == id));
}
}
public IEnumerable <ProductionCatergory> GetProductionCatergories()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.ProductionCatergories.ToList());
}
}
public IEnumerable <Material> GetMaterials()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Materials.ToList());
}
}
public IEnumerable <Sleeve> GetSleeves()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Sleeves.ToList());
}
}
public PlannedProduction GetPlannedProduction(PlannedProduction plannedProduction)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.PlannedProductions.Where(x => !x.Deleted).FirstOrDefault(p => p.ProductionCatergoryId == plannedProduction.ProductionCatergoryId && p.ProductionCatYear == plannedProduction.ProductionCatYear));
}
}
public void UpdateCID(int Id, int quantity)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
context.ExecuteCommand("UPDATE CuttingInstructionItems SET Quantity = {0} WHERE Id = {1}", quantity, Id);
}
}
public PlannedProductionDetail GetPlannedProductionDetail(int plannedProductionDetailId)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.PlannedProductionDetails.Where(x => !x.Deleted).FirstOrDefault(p => p.Id == plannedProductionDetailId));
}
}
public IEnumerable <BodyStyle> GetBodyStyle()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.BodyStyles.ToList());
}
}
public BodyStyle GetBodyStyle(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.BodyStyles.FirstOrDefault(c => c.Id == id));
}
}
public int CurrentSeason()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Settings.FirstOrDefault().CurrentSeason);
}
}
public Sleeve GetSleeve(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Sleeves.FirstOrDefault(c => c.Id == id));
}
}
public IEnumerable <ItemsForBarcodes2Result> GetItemsForBarcodes()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.ItemsForBarcodes2().ToList());
}
}
public IEnumerable <Production> GetNonClosedProductions()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
var loadOptions = new DataLoadOptions();
//loadOptions.LoadWith<CuttingInstruction>(p => p.CuttingInstructionItems);
loadOptions.LoadWith <CuttingInstruction>(p => p.ReceivingItemsTransactions);
loadOptions.LoadWith <Production>(p => p.CuttingInstructions);
context.LoadOptions = loadOptions;
var prods = new List <Production>();
foreach (var productin in context.Productions.Where(p => !p.CuttingInstructions.All(i => i.Completed == true)))
{
if (productin.CuttingInstructions.Count() > 0)
{
var CuttingInstructionItemsSum = productin.CuttingInstructions.Where(c => c.CuttingInstructionDetails.Count() > 0).Sum(c => c.CuttingInstructionDetails.Sum(cd => cd.CuttingInstructionItems.Sum(d => d.Quantity)));
var recivedItemSum = productin.CuttingInstructions.Where(c => c.ReceivingItemsTransactions.Count() > 0).Sum(c => c.ReceivingItemsTransactions.Sum(cd => cd.Quantity));
if (CuttingInstructionItemsSum != recivedItemSum)
{
prods.Add(productin);
}
}
}
return(prods.ToList());
}
}
public SeasonItemWithActivity GetSeasonItemWithActivity(int?ppId, int itemId, int?months = null)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
var season = context.PlannedProductions.Where(p => !p.Deleted).FirstOrDefault(pp => pp.Id == ppId);
var item = context.Items.FirstOrDefault(i => i.Id == itemId);
var ordered = item.CuttingInstructionItems.Where(ci => months != null ? ci.CuttingInstructionDetail.CuttingInstruction.Production.Date > DateTime.Now.AddMonths(-(int)months) : true)
.Where(ci => ci.CuttingInstructionDetail.CuttingInstruction.PlannedProductionId == ppId)
.Select(ci =>
{
return(new ItemActivity
{
Id = ci.Id,
Type = ActivityType.Ordered,
Date = ci.CuttingInstructionDetail.CuttingInstruction.Production.Date,
DatePretty = ci.CuttingInstructionDetail.CuttingInstruction.Production.Date.ToShortDateString(),
Quantity = ci.Quantity,
CuttingInstructionId = ci.CuttingInstructionDetail.CuttingInstructionId,
});
});
var recived = item.ReceivingItemsTransactions.Where(ci => months != null ? ci.Date > DateTime.Now.AddMonths(-(int)months) : true)
.Where(ci => ci.CuttingInstruction.PlannedProductionId == ppId)
.Select(ci =>
{
return(new ItemActivity
{
Id = ci.Id,
Type = ActivityType.Received,
Date = ci.Date,
DatePretty = ci.Date.ToShortDateString(),
Quantity = ci.Quantity,
CuttingInstructionId = ci.CuttingInstruction.Id,
});
});
return(new SeasonItemWithActivity
{
ItemWithActivity = new ItemWithActivity
{
Item = item,
Activities = ordered.Concat(recived).OrderByDescending(a => a.Date).ToList()
},
Season = season.NotNull() ? new Season
{
PlannedProductionId = season.Id,
Notes = season.Notes,
Name = $"{season.ProductionCatergory.Name} {season.ProductionCatYear}"
} : null,
TotalQuantitys = new ItemQuantity
{
PlannedAmount = season.NotNull() ? season.PlannedProductionDetails.Where(p => !p.Deleted).FirstOrDefault(p => p.ItemId == itemId) != null?
season.PlannedProductionDetails.FirstOrDefault(p => p.ItemId == itemId).Quantity
: 0 : 0,
AmountReceived = recived.Sum(r => r.Quantity),
AmountOrdered = ordered.Sum(r => r.Quantity)
}
});
}
}
public void MarkCuttingTicketAsCompleate(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
context.ExecuteCommand("UPDATE CuttingInstructions SET Completed = 1 WHERE Id = {0}", id);
}
}
public IEnumerable <Item> GetItemsInCuttingInstruction(bool isInCuttingTicket)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(isInCuttingTicket ? context.CuttingInstructionItems.Select(i => i.Item).Distinct().ToList() : GetItemsInCuttingInstruction());
}
}
public CuttingInstructionItem GetInstructionDetail(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.CuttingInstructionItems.FirstOrDefault(d => d.Id == id));
}
}
public IEnumerable <Item> GetItems()
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Items.ToList());
}
}
public Material GetMaterial(int id)
{
using (var context = new ManufacturingDataContext(_connectionString))
{
return(context.Materials.FirstOrDefault(c => c.Id == id));
}
}
