public static List <DividerItem> GetDivider(int week, int year)
{
try
{
List <DividerItem> Items = new List <DividerItem>();
using (SqlConnection npdConnection = new SqlConnection(Static.Secrets.NpdConnectionString))
{
string sql = $@"SELECT z.zfinIndex, d.* FROM tbDivider d
LEFT JOIN tbZfin z ON z.zfinId = d.ProductId
WHERE Week = {week} AND Year = {year}";
SqlCommand command = new SqlCommand(sql, npdConnection);
if (npdConnection.State == ConnectionState.Closed || npdConnection.State == ConnectionState.Broken)
{
npdConnection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
string zfinIndex = reader["zfinIndex"].ToString();
DividerItem d;
LocationAmount la = new LocationAmount();
la.L = reader["L"].ToString();
la.Amount = Convert.ToInt32(reader["Amount"].ToString());
if (Items.Any(i => i.ZfinIndex == zfinIndex))
{
//there's already this product row in collection, append it
d = Items.FirstOrDefault(i => i.ZfinIndex == zfinIndex);
d.Locations.Add(la);
}
else
{
//there is not this product yet, let's add it
d = new DividerItem();
d.ZfinIndex = reader["zfinIndex"].ToString();
d.Locations = new List <LocationAmount>();
d.Locations.Add(la);
Items.Add(d);
}
}
}
return(Items);
}
}
catch (Exception ex)
{
throw;
}
}
public static List <DividerItem> GetDefaultDestinations()
{
try
{
List <DividerItem> Items = new List <DividerItem>();
using (SqlConnection npdConnection = new SqlConnection(Static.Secrets.NpdConnectionString))
{
string sql = $@"SELECT z.zfinIndex, cs.location
FROM tbZfin z LEFT JOIN tbCustomerString cs ON cs.custStringId = z.custString
WHERE z.custString IS NOT NULL AND prodStatus = 'PR'";
SqlCommand command = new SqlCommand(sql, npdConnection);
if (npdConnection.State == ConnectionState.Closed || npdConnection.State == ConnectionState.Broken)
{
npdConnection.Open();
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
DividerItem d = new DividerItem();
d.ZfinIndex = reader["zfinIndex"].ToString();
d.Locations = new List <LocationAmount>();
LocationAmount la = new LocationAmount();
la.L = reader["location"].ToString().Trim();
la.Amount = 0;
d.Locations.Add(la);
Items.Add(d);
}
}
return(Items);
}
}
catch (Exception ex)
{
throw;
}
}
public static List <Location> GetProductionPlanByCountry(string query = null)
{
try
{
List <Location> Locations = new List <Location>();
List <ProductionPlanItem> Items = GetProductionPlan(query);
if (Items.Any())
{
List <DividerKeeper> Dividers = new List <DividerKeeper>();
List <DividerItem> DefaultDestinations = Utilities.GetDefaultDestinations();
DateTime start = Items.Min(i => i.START_DATE);
DateTime stop = Items.Max(i => i.STOP_DATE);
TimeSpan span = stop - start;
string ProductIds = string.Join(",", Items.Select(i => i.PRODUCT_ID).ToList().Distinct());
ProductMachineEfficiencyKeeper EfficiencyKeeper = new ProductMachineEfficiencyKeeper();
EfficiencyKeeper.Items = Utilities.GetProductMachineEfficiencies(ProductIds);
List <Session> Sessions = new List <Session>();
List <ShipmentGroup> shipmentGroups = Utilities.GetShipmentGroups();
foreach (ProductionPlanItem i in Items)
{
//for each operation
//get how to allocate it
//What week is this?
//info from session dates rather than from operation dates
if (!Sessions.Any(s => s.SCHEDULING_ID == i.SCHEDULING_ID))
{
//we need to create the session and calculate its week/year
Session s = new Session();
s.SCHEDULING_ID = i.SCHEDULING_ID;
s.BEGIN_DATE = i.BEGIN_DATE;
s.END_DATE = i.END_DATE;
s.CalcualatePeriod();
Sessions.Add(s);
}
i.WEEK = Sessions.FirstOrDefault(s => s.SCHEDULING_ID == i.SCHEDULING_ID).Week;
i.YEAR = Sessions.FirstOrDefault(s => s.SCHEDULING_ID == i.SCHEDULING_ID).Year;
if (!Dividers.Any(d => d.Week == i.WEEK && d.Year == i.YEAR))
{
//create it
DividerKeeper div = new DividerKeeper();
div.Week = i.WEEK;
div.Year = i.YEAR;
div.Items = Utilities.GetDivider(i.WEEK, i.YEAR);
Dividers.Add(div);
}
DividerKeeper currDiv = Dividers.FirstOrDefault(d => d.Week == i.WEEK && d.Year == i.YEAR);
double palletCount = i.QUANTITY / i.PAL; //number of pieces on pallet
if (currDiv.Items.Any(x => x.ZfinIndex == i.PRODUCT_NR))
{
}
else
{
//this product hasn't been found in divider for week X
//check futher weeks, maybe it's in week X+1
currDiv = null;
foreach (DividerKeeper dk in Dividers.Where(d => (d.Week > i.WEEK && d.Year == i.YEAR) || (d.Week <i.WEEK && d.Year> i.YEAR)))
{
if (dk.Items.Any(y => y.ZfinIndex == i.PRODUCT_NR))
{
//it's in divider for next week
currDiv = dk;
}
}
}
if (currDiv != null)
{
foreach (LocationAmount la in currDiv.Items.FirstOrDefault(x => x.ZfinIndex == i.PRODUCT_NR).Locations)
{
if (i.QUANTITY > 0)
{
//if there's nothing left to allocate in this operation, go to next operation
if (la.Amount > 0)
{
//there's still quantity to allocate
if (!Locations.Any(l => l.L.Trim() == la.L.Trim()))
{
//we don't have this location started yet
Location loc = new Location();
loc.L = la.L.Trim();
if (shipmentGroups.Any(s => s.Members.Any(m => m.L.Trim() == loc.L)))
{
loc.ShipmentGroupName = shipmentGroups.FirstOrDefault(s => s.Members.Any(m => m.L.Trim() == loc.L)).Name;
}
Locations.Add(loc);
}
Location currLoc = Locations.FirstOrDefault(l => l.L.Trim() == la.L.Trim());
ProductionPlanItem p = new ProductionPlanItem();
p = i.CloneJson();
if (la.Amount >= i.QUANTITY)
{
//this L needs more than operation quantity or all of it
//take only operation quantity then
la.Amount -= i.QUANTITY; //decrease the amount of allocation that remains to this L
i.QUANTITY = 0;
i.PAL = 0;
}
else
{
//there will remain some quantity for other L
p.QUANTITY -= la.Amount;
p.PAL = la.Amount / palletCount;
la.Amount = 0;
i.QUANTITY -= p.QUANTITY;
i.PAL -= p.QUANTITY / palletCount;
//as we don't consume the whole operation,
//we must adjust the REMAINING & CONSUMED parts (stop date, quantity, etc)
long?minutesTaken = EfficiencyKeeper.Amount2Minutes(i.MACHINE_ID, i.PRODUCT_ID, p.QUANTITY);
if (minutesTaken != null)
{
//we have the efficiency set in MES
p.STOP_DATE = p.START_DATE.AddMinutes((double)minutesTaken);
i.START_DATE = p.STOP_DATE; //stop date of this part is beginning of next part
}
}
p.LOCATION = la.L;
p.DIVIDER_WEEK = currDiv.Week;
p.DIVIDER_YEAR = currDiv.Year;
currLoc.Parts.Add(p); // add this part to operations for this location
}
}
}
}
else
{
//It's not divider-based product
//take default allocation
Location currLoc;
if (DefaultDestinations.Any(d => d.ZfinIndex == i.PRODUCT_NR))
{
LocationAmount la = DefaultDestinations.Where(d => d.ZfinIndex == i.PRODUCT_NR).FirstOrDefault().Locations.FirstOrDefault();
//default destination found
if (!Locations.Any(l => l.L.Trim() == la.L.Trim()))
{
//we don't have this location started yet
Location loc = new Location();
loc.L = la.L.Trim();
if (shipmentGroups.Any(s => s.Members.Any(m => m.L.Trim() == loc.L)))
{
loc.ShipmentGroupName = shipmentGroups.FirstOrDefault(s => s.Members.Any(m => m.L.Trim() == loc.L)).Name;
}
Locations.Add(loc);
}
currLoc = Locations.FirstOrDefault(l => l.L.Trim() == la.L.Trim());
}
else
{
//default destination doesn't exist
//add it to unknow collection
if (!Locations.Any(l => l.L.Trim() == "LXXX"))
{
//we don't have this location started yet
Location loc = new Location();
loc.L = "LXXX";
Locations.Add(loc);
}
currLoc = Locations.FirstOrDefault(l => l.L.Trim() == "LXXX");
}
ProductionPlanItem p = new ProductionPlanItem();
p = i.CloneJson();
i.QUANTITY = 0;
i.PAL = 0;
p.LOCATION = currLoc.L;
p.DIVIDER_WEEK = 0; // not divider-based
p.DIVIDER_YEAR = 0; // not divider-based
currLoc.Parts.Add(p); // add this part to operations for this location
}
}
}
foreach (Location l in Locations)
{
l.Compose();
}
return(Locations);
}
catch (Exception ex)
{
Logger.Error("GetProductionPlanByDestinations: Błąd. Szczegóły: {Message}", ex.ToString());
return(null);
}
}