private void frmInventoryControl_Load(object sender, EventArgs e)
{
LoadLanguagePack();
dtpDate.Value = DateTime.Now;
cbAllocationMethod.SelectedIndex = 0;
using (var db = new Session6Entities())
{
cbAssetNumberEMNumber.Items.AddRange((from p in db.EmergencyMaintenances
where p.EMStartDate.HasValue && !p.EMEndDate.HasValue
select p.Asset.AssetName + " (" + p.ID + ")").ToArray());
cbWarehouse.DataSource = (from p in db.Warehouses
orderby p.Name
select new IDStringPair()
{
ID = p.ID, Value = p.Name
}).ToList();
cbWarehouse.DisplayMember = "Value";
cbWarehouse.ValueMember = "ID";
UpdatePartsCombobox();
}
}
void UpdatePartsCombobox()
{
if (cbWarehouse.SelectedValue == null || !(cbWarehouse.SelectedValue is long))
{
return;
}
/*parts in stock at the warehouse before the date picked
* Assumption here is to just list all those with TransactionTypeID=1 aka PurchaseOrders
*/
using (var db = new Session6Entities())
{
var WarehouseID = Convert.ToInt64(cbWarehouse.SelectedValue);
cbPartName.DataSource = (from p in db.OrderItems
where p.Order.TransactionTypeID == 1 && p.Order.DestinationWarehouseID == WarehouseID
group p by new { p.Part.Name, p.PartID } into q
select new IDStringPair()
{
ID = q.Key.PartID,
Value = q.Key.Name,
Params = q.Sum(p => p.Amount)
}).ToList();
cbPartName.DisplayMember = "Value";
cbPartName.ValueMember = "ID";
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
//This creates the order and assigns it to the selected EM
var strEM_ID = cbAssetNumberEMNumber.SelectedItem.ToString();
strEM_ID = strEM_ID.Substring(strEM_ID.LastIndexOf("(") + 1);
var EM_ID = Convert.ToInt64(strEM_ID.Substring(0, strEM_ID.Length - 1));
var WarehouseID = Convert.ToInt64(cbWarehouse.SelectedValue);
using (var db = new Session6Entities())
{
var theOrder = new Order()
{
Date = dtpDate.Value,
EmergencyMaintenancesID = EM_ID,
SourceWarehouseID = WarehouseID,
Time = new TimeSpan(DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second),
TransactionTypeID = 3
};
db.Orders.Add(theOrder);
db.SaveChanges();
//After saving, theOrder.ID will be populated
foreach (var r in AssignedParts)
{
db.OrderItems.Add(new OrderItem()
{
Amount = r.Amount,
BatchNumber = r.BatchNumber,
OrderID = theOrder.ID,
PartID = r.PartID,
UnitPrice = r.UnitPrice
});
}
db.SaveChanges();
}
MessageBox.Show("Changes saved", "Operation Success", MessageBoxButtons.OK);
}
private void btnAllocate_Click(object sender, EventArgs e)
{
var Max = (decimal)txtAmount.Tag;
var Req = Convert.ToDecimal(txtAmount.Text);
if (Req > Max)
{
MessageBox.Show($"Amount to transfer cannot be more than {txtAmount.Tag}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
using (var db = new Session6Entities())
{
var WarehouseID = Convert.ToInt64(cbWarehouse.SelectedValue);
var PartID = (cbPartName.SelectedItem as IDStringPair).ID;
var rec = (from p in db.OrderItems
where p.PartID == PartID && p.Order.TransactionTypeID == 1 && p.Order.DestinationWarehouseID == WarehouseID
select new { OrderItem = p, Order = p.Order });
switch (cbAllocationMethod.SelectedItem.ToString())
{
case "FIFO (First in First Out)":
rec = (from q in rec
orderby q.Order.Date
select q);
break;
case "LIFO(Last in First Out)":
rec = (from q in rec
orderby q.Order.Date descending
select q);
break;
case "Minimum First":
rec = (from q in rec
orderby q.OrderItem.UnitPrice
select q);
break;
}
//As i cannot tell how many is needed, take x records where x = amount needed (assuming each record has only 1)
//This is an assumption as there isnt an optimal method of tracking current inventory based on the system design
var DS = (from p in rec
select new dAllocatedParts()
{
PartName = p.OrderItem.Part.Name,
BatchNumber = p.OrderItem.BatchNumber,
UnitPrice = p.OrderItem.UnitPrice.Value,
Amount = p.OrderItem.Amount,
PartID = p.OrderItem.PartID
}).Take(Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(txtAmount.Text))));
var lAllocatedParts = new List <dAllocatedParts>();
decimal CurrentAmount = 0;
foreach (var r in DS)
{
CurrentAmount += r.Amount;
if (CurrentAmount > Req)
{
r.Amount -= (CurrentAmount - Req);
lAllocatedParts.Add(r);
break;
}
else if (CurrentAmount == Req)
{
lAllocatedParts.Add(r);
break;
}
else
{
lAllocatedParts.Add(r);
}
}
dgvAllocatedParts.DataSource = lAllocatedParts;
btnAssignToEM.Enabled = lAllocatedParts.Count() > 0;
}
}
}
private void frmInventoryDashboard_Load(object sender, EventArgs e)
{
cbLanguages.Items.AddRange(GlobalVariables.Language_GetAvailable.ToArray());
cbLanguages.SelectedItem = GlobalVariables.Language_Current;
LoadLanguagePack();
using (var db = new Session6Entities())
{
#region EM Spending by Department
//Trigger the SQL query to change it to list for c# processing
var DS_EM_Spending_By_Department1 = (from p in db.Orders
where p.EmergencyMaintenance.EMStartDate != null & p.EmergencyMaintenance.EMEndDate != null
select new
{
DeptName = p.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name,
//DeptID = p.EmergencyMaintenance.Asset.DepartmentLocation.Department.ID,
theDate = p.Date,
Spending = p.OrderItems.Sum(q => q.Amount * q.UnitPrice)
}).ToList();
//Tostring function only works when it is a list and not within SQL
var DS_EM_Spending_By_Department = (from p in DS_EM_Spending_By_Department1
select new
{
p.DeptName,
p.Spending,
theDate = p.theDate.ToString("yyyy-MM")
}
).ToList();
//Eventually, Department / Year-Month
/*
* Grab the last 10 Year-Month columns
* NOTE DateColumns is used by ALL the DGV
*/
var DateColumns = (from p in DS_EM_Spending_By_Department
orderby p.theDate descending
select p.theDate).Distinct();
if (DateColumns.Count() > 10)
{
DateColumns = DateColumns.Take(10);
}
var theDepartments = (from p in DS_EM_Spending_By_Department
orderby p.DeptName
select p.DeptName).Distinct();
/* Ideally the binding will be done using a custom built sql script which maps to a data table
* but this is messy, so go old school and build a data table using linq and code
*/
var DTEMSpendingByDepartment = new DataTable();
//Map first column in DT to first column in DGV
DTEMSpendingByDepartment.Columns.Add("DeptName");
//Add the first column to grid
dgvEMSpendingByDepartment.Columns.Clear();
dgvEMSpendingByDepartment.Columns.Add(new DataGridViewTextBoxColumn()
{
DataPropertyName = "DeptName",
HeaderText = "Department / Month"
});
//Define the entire table along with the grid
foreach (var DC in DateColumns)
{
DTEMSpendingByDepartment.Columns.Add(DC);
var DCCol = new DataGridViewTextBoxColumn()
{
DataPropertyName = DC,
HeaderText = DC,
};
DCCol.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvEMSpendingByDepartment.Columns.Add(DCCol);
}
foreach (var Dept in theDepartments)
{
var dr = DTEMSpendingByDepartment.NewRow();
dr["DeptName"] = Dept;
foreach (var DC in DateColumns)
{
dr[DC] = (from p in DS_EM_Spending_By_Department
where p.DeptName == Dept && p.theDate == DC
select p.Spending).Sum().Value.ToString("0");
}
DTEMSpendingByDepartment.Rows.Add(dr);
}
dgvEMSpendingByDepartment.DataSource = DTEMSpendingByDepartment;
//need to annotate Red highest spending, green lowest spending
#endregion
#region Department Spending Ratio Chart
//uses same data as DS_EM_Spending_By_Department
var DS_DepartmentSpendingRatio = (from p in DS_EM_Spending_By_Department
group p by p.DeptName into q
select new
{
DeptName = q.Key,
Total = q.Sum(p => p.Spending)
});
chDepartmentSpendingRatio.Series[0].Points.Clear();
foreach (var r in DS_DepartmentSpendingRatio)
{
//Pie chart only has Y values
var idx = chDepartmentSpendingRatio.Series[0].Points.AddY(r.Total);
chDepartmentSpendingRatio.Series[0].Points[idx].Label = r.DeptName;
}
#endregion
#region Monthly Department Spending
//uses same data as DS_EM_Spending_By_Department
var DS_MonthlyDepartmentSpendingRatio1 = (from p in DS_EM_Spending_By_Department1
select new
{
p.DeptName,
theDate = p.theDate.ToString("yyyy-MM"),
p.Spending
});
var DS_MonthlyDepartmentSpendingRatio = (from p in DS_MonthlyDepartmentSpendingRatio1
group p by new { p.theDate, p.DeptName } into q
orderby q.Key.theDate, q.Key.DeptName
select new
{
q.Key.DeptName,
q.Key.theDate,
Total = q.Sum(p => p.Spending)
});
chMonthlyDepartmentSpending.Series.Clear();
foreach (var DeptName in DS_MonthlyDepartmentSpendingRatio.Select(p => p.DeptName).Distinct())
{
//Add each deptname as a series
chMonthlyDepartmentSpending.Series.Add(new System.Windows.Forms.DataVisualization.Charting.Series(DeptName)
{
});
}
var curDate = "";
var DateIdx = 0;
foreach (var r in DS_MonthlyDepartmentSpendingRatio)
{
if (curDate != r.theDate)
{
curDate = r.theDate;
DateIdx++;
}
var idx = chMonthlyDepartmentSpending.Series[r.DeptName].Points.AddXY(DateIdx, r.Total);
chMonthlyDepartmentSpending.Series[r.DeptName].Points[idx].AxisLabel = r.theDate;
chMonthlyDepartmentSpending.Series[r.DeptName].Points[idx].Label = r.Total.Value.ToString("#,##0");
}
#endregion
#region Monthly Report for Most-used Parts
//Highest cost and most number
var DS_MonthlyReport_MostUsed_Parts1 = (from p in db.OrderItems
where p.Order.EmergencyMaintenance.EMStartDate != null & p.Order.EmergencyMaintenance.EMEndDate != null
select new
{
theDate = p.Order.Date,
p.Amount,
TotalCost = p.UnitPrice * p.Amount,
PartName = p.Part.Name
}).ToList();
//Tostring function only works when it is a list and not within SQL
var DS_MonthlyReport_MostUsed_Parts = (from p in DS_MonthlyReport_MostUsed_Parts1
select new
{
p.PartName,
p.Amount,
p.TotalCost,
theDate = p.theDate.ToString("yyyy-MM")
}
).ToList();
var DTMonthlyReportForMostUsedParts = new DataTable();
//Map first column in DT to first column in DGV
DTMonthlyReportForMostUsedParts.Columns.Add("Notes");
//Add the first column to grid
dgvMonthlyReportForMostUsedParts.Columns.Clear();
dgvMonthlyReportForMostUsedParts.Columns.Add(new DataGridViewTextBoxColumn()
{
DataPropertyName = "Notes",
HeaderText = "Notes / Month"
});
//Define the entire table along with the grid
foreach (var DC in DateColumns)
{
DTMonthlyReportForMostUsedParts.Columns.Add(DC);
dgvMonthlyReportForMostUsedParts.Columns.Add(new DataGridViewTextBoxColumn()
{
DataPropertyName = DC,
HeaderText = DC,
});
}
//Now populate the Data table
var DRMonthlyReportForMostUsedParts_HighestCost = DTMonthlyReportForMostUsedParts.NewRow();
DRMonthlyReportForMostUsedParts_HighestCost["Notes"] = "Highest Cost";
var DS_MonthlyReport_MostUsed_Parts_HighestCost = (from p in DS_MonthlyReport_MostUsed_Parts
group p by new { p.theDate, p.PartName } into q
select new {
q.Key.PartName,
q.Key.theDate,
TotalCost = q.Sum(p => p.TotalCost)
}
);
foreach (var DC in DateColumns)
{
var MaxCost = (from p in DS_MonthlyReport_MostUsed_Parts_HighestCost
where p.theDate == DC
orderby p.TotalCost descending
select p.TotalCost).FirstOrDefault();
DRMonthlyReportForMostUsedParts_HighestCost[DC] = String.Join(",", (from p in DS_MonthlyReport_MostUsed_Parts_HighestCost
where p.theDate == DC && p.TotalCost == MaxCost
orderby p.PartName descending
select p.PartName));
}
var DRMonthlyReportForMostUsedParts_MostNumber = DTMonthlyReportForMostUsedParts.NewRow();
DRMonthlyReportForMostUsedParts_MostNumber["Notes"] = "Most Number";
var DS_MonthlyReport_MostUsed_Parts_MostNumber = (from p in DS_MonthlyReport_MostUsed_Parts
group p by new { p.theDate, p.PartName } into q
select new
{
q.Key.PartName,
q.Key.theDate,
TotalQuantity = q.Sum(p => p.Amount)
}
);
foreach (var DC in DateColumns)
{
var MaxParts = (from p in DS_MonthlyReport_MostUsed_Parts_MostNumber
where p.theDate == DC
orderby p.TotalQuantity descending
select p.TotalQuantity).FirstOrDefault();
DRMonthlyReportForMostUsedParts_MostNumber[DC] = String.Join(",", (from p in DS_MonthlyReport_MostUsed_Parts_MostNumber
where p.theDate == DC && p.TotalQuantity == MaxParts
orderby p.PartName descending
select p.PartName));
}
DTMonthlyReportForMostUsedParts.Rows.Add(DRMonthlyReportForMostUsedParts_HighestCost);
DTMonthlyReportForMostUsedParts.Rows.Add(DRMonthlyReportForMostUsedParts_MostNumber);
dgvMonthlyReportForMostUsedParts.DataSource = DTMonthlyReportForMostUsedParts;
#endregion
#region Monthly Report of Costly assets
//for each department, highest spend per item
var DS_MonthlyReportOfCostlyAssets1 = (from p in db.OrderItems
where p.Order.EmergencyMaintenance.EMStartDate != null & p.Order.EmergencyMaintenance.EMEndDate != null
select new
{
theDate = p.Order.Date,
DeptName = p.Order.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name,
TotalCost = p.UnitPrice * p.Amount,
PartName = p.Part.Name
}).ToList();
var DS_MonthlyReportOfCostlyAssets = (from p in DS_MonthlyReportOfCostlyAssets1
select new
{
p.DeptName,
p.TotalCost,
p.PartName,
theDate = p.theDate.ToString("yyyy-MM")
});
var DT_MonthlyReportOfCostlyAssets = new DataTable();
DT_MonthlyReportOfCostlyAssets.Columns.Add("Notes");
//Add the first column to grid
dgvMonthlyReportOfCostlyAssets.Columns.Clear();
dgvMonthlyReportOfCostlyAssets.Columns.Add(new DataGridViewTextBoxColumn()
{
DataPropertyName = "Notes",
HeaderText = "Asset Name / Month"
});
//Define the entire table along with the grid
foreach (var DC in DateColumns)
{
DT_MonthlyReportOfCostlyAssets.Columns.Add(DC);
dgvMonthlyReportOfCostlyAssets.Columns.Add(new DataGridViewTextBoxColumn()
{
DataPropertyName = DC,
HeaderText = DC,
});
}
//the manager wants to identify which assets have been costing the company the most each month
var DS_MonthlyReportOfCostlyAssets_HighestCost = (from p in DS_MonthlyReportOfCostlyAssets
group p by new { p.theDate, p.PartName, p.DeptName } into q
select new
{
q.Key.PartName,
q.Key.theDate,
q.Key.DeptName,
Total = q.Sum(p => p.TotalCost),
}
);
var DR_MonthlyReportOfCostlyAssets_PartName = DT_MonthlyReportOfCostlyAssets.NewRow();
DR_MonthlyReportOfCostlyAssets_PartName["Notes"] = "Asset";
var DR_MonthlyReportOfCostlyAssets_DeptName = DT_MonthlyReportOfCostlyAssets.NewRow();
DR_MonthlyReportOfCostlyAssets_DeptName["Notes"] = "Department";
foreach (var DC in DateColumns)
{
var HighestCost = (from p in DS_MonthlyReportOfCostlyAssets_HighestCost
where p.theDate == DC
orderby p.Total
select p.Total).FirstOrDefault();
DR_MonthlyReportOfCostlyAssets_PartName[DC] = String.Join(",", (from p in DS_MonthlyReportOfCostlyAssets_HighestCost
where p.Total == HighestCost
select p.PartName));
DR_MonthlyReportOfCostlyAssets_DeptName[DC] = String.Join(",", (from p in DS_MonthlyReportOfCostlyAssets_HighestCost
where p.Total == HighestCost
select p.DeptName));
}
DT_MonthlyReportOfCostlyAssets.Rows.Add(DR_MonthlyReportOfCostlyAssets_PartName);
DT_MonthlyReportOfCostlyAssets.Rows.Add(DR_MonthlyReportOfCostlyAssets_DeptName);
dgvMonthlyReportOfCostlyAssets.DataSource = DT_MonthlyReportOfCostlyAssets;
#endregion
}
}