private void btnSubmit_Click(object sender, EventArgs e)
{
if (cbAssetName.SelectedItem == null)
{
MessageBox.Show("Please select your Asset!!", "Submit", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
var response = MessageBox.Show("Are you sure you want to submit?", "Submit", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (response == DialogResult.Yes)
{
using (var context = new Session6Entities())
{
var getWarehouseID = (from x in context.Warehouses
where x.Name == cbWarehouse.SelectedItem.ToString()
select x.ID).First();
var getEMID = (from x in context.EmergencyMaintenances
where x.Asset.AssetName + " (" + x.ID + ")" == cbAssetName.SelectedItem.ToString()
select x.ID).First();
var getNewOrderID = (from x in context.Orders
orderby x.ID descending
select x.ID).First() + 1;
context.Orders.Add(new Order()
{
ID = getNewOrderID,
Date = dtpDate.Value,
DestinationWarehouseID = null,
EmergencyMaintenancesID = getEMID,
SourceWarehouseID = getWarehouseID,
SupplierID = null,
TransactionTypeID = 3,
Time = DateTime.Now.TimeOfDay
});
context.SaveChanges();
foreach (DataGridViewRow item in dgvAssignedParts.Rows)
{
context.OrderItems.Add(new OrderItem()
{
PartID = Convert.ToInt64(dgvAssignedParts.Rows[item.Index].Cells[0].Value),
BatchNumber = dgvAssignedParts.Rows[item.Index].Cells[2].Value.ToString(),
Amount = Convert.ToDecimal(dgvAssignedParts.Rows[item.Index].Cells[4].Value),
UnitPrice = Convert.ToDecimal(dgvAssignedParts.Rows[item.Index].Cells[3].Value),
OrderID = getNewOrderID
});
}
context.SaveChanges();
}
this.Close();
}
}
}
private void InventoryControl_Load(object sender, EventArgs e)
{
using (var context = new Session6Entities())
{
#region Populating Combo Box for Asset Name
var getAssetName = (from x in context.EmergencyMaintenances
where x.EMEndDate == null
select new { AssetName = x.Asset.AssetName, EMNumber = x.ID });
foreach (var item in getAssetName)
{
cbAssetName.Items.Add($"{item.AssetName} ({item.EMNumber})");
}
#endregion
#region Populating Combo Box for Warehouses
var getWarehouses = (from x in context.Warehouses
select x.Name).Distinct().ToArray();
cbWarehouse.Items.AddRange(getWarehouses);
#endregion
#region Populating Combo Box for Part
var getParts = (from x in context.Parts
select x.Name).Distinct().ToArray();
cbPartName.Items.AddRange(getParts);
#endregion
}
var allocationMethodList = new List <string>()
{
"FIFO", "LIFO", "Minimum First"
};
cbAllocationMethod.Items.AddRange(allocationMethodList.ToArray());
#region Loading Columns for Allocated Parts DGV
dgvAllocatedParts.ColumnCount = 5;
dgvAllocatedParts.Columns[0].Name = "Part ID";
dgvAllocatedParts.Columns[1].Name = "Part Name";
dgvAllocatedParts.Columns[2].Name = "Batch Number";
dgvAllocatedParts.Columns[3].Name = "Unit Price";
dgvAllocatedParts.Columns[4].Name = "Amount";
dgvAllocatedParts.Columns[0].Visible = false;
#endregion
#region Adding Column for Link Cell for Assigned EM DGV
var linkColumn = new DataGridViewLinkColumn
{
Text = "Remove",
Name = "Actions",
UseColumnTextForLinkValue = true
};
dgvAssignedParts.Columns.Add(linkColumn);
#endregion
}
private void btnAllocate_Click(object sender, EventArgs e)
{
dgvAllocatedParts.Rows.Clear();
var amt = nudAmount.Value;
if (cbAllocationMethod.SelectedItem == null)
{
MessageBox.Show("Please select your allocation method!", "Allocate", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (cbAllocationMethod.SelectedItem.ToString() == "FIFO")
{
var listToAdd = new List <OrderItem>();
using (var context = new Session6Entities())
{
var getRelatedParts = (from x in context.OrderItems
where x.Part.Name == cbPartName.SelectedItem.ToString() && x.Order.Warehouse.Name == cbWarehouse.SelectedItem.ToString()
orderby x.Order.Date
select x);
foreach (var item in getRelatedParts)
{
if (amt == 0)
{
break;
}
if (item.Amount > amt)
{
continue;
}
else
{
listToAdd.Add(item);
amt -= item.Amount;
}
}
foreach (var item in listToAdd)
{
var row = new List <string>()
{
item.PartID.ToString(), item.Part.Name, item.BatchNumber, item.UnitPrice.ToString(), item.Amount.ToString()
};
dgvAllocatedParts.Rows.Add(row.ToArray());
}
}
}
else if (cbAllocationMethod.SelectedItem.ToString() == "LIFO")
{
var listToAdd = new List <OrderItem>();
using (var context = new Session6Entities())
{
var getRelatedParts = (from x in context.OrderItems
where x.Part.Name == cbPartName.SelectedItem.ToString() && x.Order.Warehouse.Name == cbWarehouse.SelectedItem.ToString()
orderby x.Order.Date descending
select x);
foreach (var item in getRelatedParts)
{
if (amt == 0)
{
break;
}
if (item.Amount > amt)
{
continue;
}
else
{
listToAdd.Add(item);
amt -= item.Amount;
}
}
foreach (var item in listToAdd)
{
var row = new List <string>()
{
item.PartID.ToString(), item.Part.Name, item.BatchNumber, item.UnitPrice.ToString(), item.Amount.ToString()
};
dgvAllocatedParts.Rows.Add(row.ToArray());
}
}
}
else if (cbAllocationMethod.SelectedItem.ToString() == "Minimum First")
{
var listToAdd = new List <OrderItem>();
using (var context = new Session6Entities())
{
var getRelatedParts = (from x in context.OrderItems
where x.Part.Name == cbPartName.SelectedItem.ToString() && x.Order.Warehouse.Name == cbWarehouse.SelectedItem.ToString()
orderby x.UnitPrice *x.Amount
select x);
foreach (var item in getRelatedParts)
{
if (amt == 0)
{
break;
}
if (item.Amount > amt)
{
continue;
}
else
{
listToAdd.Add(item);
amt -= item.Amount;
}
}
foreach (var item in listToAdd)
{
var row = new List <string>()
{
item.PartID.ToString(), item.Part.Name, item.BatchNumber, item.UnitPrice.ToString(), item.Amount.ToString()
};
dgvAllocatedParts.Rows.Add(row.ToArray());
}
}
}
}
private void LoadGrid()
{
#region Clearing all rows and columns of all data bounded views
dgvSpending.Rows.Clear();
dgvSpending.Columns.Clear();
dgvMostUsed.Rows.Clear();
dgvMostUsed.Columns.Clear();
dgvCostlyAssets.Rows.Clear();
dgvCostlyAssets.Columns.Clear();
cDepartmentSpendingRatio.Series.Clear();
cMonthlyDepartmentSpending.Series.Clear();
#endregion
dgvSpending.Columns.Add("Department/Month", "Department/Month");
dgvMostUsed.Columns.Add("Notes/Month", "Notes/Month");
dgvCostlyAssets.Columns.Add("Asset Name/Month", "Asset Name/Month");
using (var context = new Session6Entities())
{
var getDepartments = (from x in context.EmergencyMaintenances
where x.EMStartDate != null && x.EMEndDate != null
select x.Asset.DepartmentLocation.Department.Name).Distinct();
var getDistinctDatesInitial = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
orderby x.Date descending
select x.Date).ToList();
var getDistinctDates = (from x in getDistinctDatesInitial
select x.ToString("yyyy/MM")).Distinct().Take(10);
foreach (var distinctDates in getDistinctDates)
{
dgvSpending.Columns.Add(distinctDates, distinctDates);
dgvMostUsed.Columns.Add(distinctDates, distinctDates);
dgvCostlyAssets.Columns.Add(distinctDates, distinctDates);
}
#region Populating 1st DGV
foreach (var departments in getDepartments)
{
var row = new List <string>();
row.Add(departments);
foreach (var distinctDates in getDistinctDates)
{
var getList = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
where x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name == departments
select x).ToList();
var getSpendings = (from x in getList
where x.Date.ToString("yyyy/MM") == distinctDates
select x.OrderItems.Sum(z => z.Amount * z.UnitPrice)).Sum().Value.ToString("0");
row.Add(getSpendings);
}
dgvSpending.Rows.Add(row.ToArray());
}
foreach (DataGridViewColumn column in dgvSpending.Columns)
{
if (dgvSpending.Columns[column.Index].Index == 0)
{
continue;
}
else
{
var dict = new Dictionary <int, decimal>();
foreach (DataGridViewRow row in dgvSpending.Rows)
{
dict.Add(row.Index, Convert.ToDecimal(dgvSpending.Rows[row.Index].Cells[column.Index].Value));
}
var getMax = (from x in dict
orderby x.Value descending
where x.Value != 0
select x.Key).First();
var getMin = (from x in dict
orderby x.Value
where x.Value != 0
select x.Key).First();
if (getMin == getMax)
{
dgvSpending.Rows[getMin].Cells[column.Index].Style.ForeColor = Color.Red;
}
else
{
dgvSpending.Rows[getMin].Cells[column.Index].Style.ForeColor = Color.Red;
dgvSpending.Rows[getMax].Cells[column.Index].Style.ForeColor = Color.Green;
}
}
}
#endregion
#region Populating 2nd DGV
var getDistinctParts = (from x in context.Parts
select x.Name).Distinct();
var highestCostRow = new List <string>()
{
"Highest Cost"
};
var mostNumberRow = new List <string>()
{
"Most Number"
};
foreach (var distinctDates in getDistinctDates)
{
var highestCostDict = new Dictionary <string, decimal>();
var mostNumberDict = new Dictionary <string, decimal>();
foreach (var item in getDistinctParts)
{
var initialQuery = (from x in context.Orders
where x.EmergencyMaintenance.EMEndDate != null && x.EmergencyMaintenance.EMStartDate != null
select x).ToList();
var getSpendings = (from x in initialQuery
where x.Date.ToString("yyyy/MM") == distinctDates
select x.OrderItems.Where(y => y.Part.Name == item).Sum(y => y.Amount * y.UnitPrice)).Sum();
highestCostDict.Add(item, Convert.ToDecimal(getSpendings));
var getAmount = (from x in initialQuery
where x.Date.ToString("yyyy/MM") == distinctDates
select x.OrderItems.Where(y => y.Part.Name == item).Sum(y => y.Amount)).Sum();
mostNumberDict.Add(item, getAmount);
}
var getHighestCost = (from x in highestCostDict
orderby x.Value descending
where x.Value != 0
select x.Key).First();
highestCostRow.Add(getHighestCost);
var getMostNumber = (from x in mostNumberDict
orderby x.Value descending
where x.Value != 0
select x.Key).First();
mostNumberRow.Add(getMostNumber);
}
dgvMostUsed.Rows.Add(highestCostRow.ToArray());
dgvMostUsed.Rows.Add(mostNumberRow.ToArray());
#endregion
#region Populating 3rd DGV
var assetRow = new List <string>()
{
"Asset"
};
var departmentRow = new List <string>()
{
"Department"
};
var getDistinctAssets = (from x in context.Assets
select x.AssetName).Distinct();
foreach (var distinctDates in getDistinctDates)
{
var assetSpendings = new Dictionary <string, decimal>();
var assetDepartment = new Dictionary <string, string>();
foreach (var item in getDistinctAssets)
{
var initialQuery = (from x in context.Orders
where x.EmergencyMaintenance.EMEndDate != null && x.EmergencyMaintenance.EMStartDate != null
select x).ToList();
var getAssetSpendings = (from x in initialQuery
where x.Date.ToString("yyyy/MM") == distinctDates && x.EmergencyMaintenance.Asset.AssetName == item
select x.OrderItems.Sum(y => y.Amount * y.UnitPrice)).Sum();
var getAssetDepartment = (from x in context.Assets
where x.AssetName == item
select x.DepartmentLocation.Department.Name).FirstOrDefault();
assetSpendings.Add(item, Convert.ToDecimal(getAssetSpendings));
assetDepartment.Add(item, getAssetDepartment);
}
var getHighestSpendingAsset = (from x in assetSpendings
where x.Value != 0
orderby x.Value descending
select x.Value).FirstOrDefault();
var checkNumberofAssets = (from x in assetSpendings
where x.Value == getHighestSpendingAsset
select x.Key).ToList();
if (checkNumberofAssets.Count == 1)
{
assetRow.Add(checkNumberofAssets.First());
var getDepartment = (from x in assetDepartment
where x.Key == checkNumberofAssets.First()
select x.Value).First();
departmentRow.Add(getDepartment);
}
else
{
var toAddAsset = string.Empty;
var toAddDepartment = string.Empty;
foreach (var item in checkNumberofAssets)
{
if (toAddAsset == string.Empty)
{
toAddAsset = item;
var getDepartment = (from x in assetDepartment
where x.Key == item
select x.Value).First();
toAddDepartment = getDepartment;
}
else
{
toAddAsset += $", {item}";
var getDepartment = (from x in assetDepartment
where x.Key == item
select x.Value).First();
toAddDepartment += $", {getDepartment}";
}
}
assetRow.Add(toAddAsset);
departmentRow.Add(toAddDepartment);
}
}
dgvCostlyAssets.Rows.Add(assetRow.ToArray());
dgvCostlyAssets.Rows.Add(departmentRow.ToArray());
#endregion
#region Loading Piechart
cDepartmentSpendingRatio.Series.Add("Series0");
cDepartmentSpendingRatio.Series[0].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Pie;
foreach (var departments in getDepartments)
{
var getList = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
where x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name == departments
select x).ToList();
var getSpendings = (from x in getList
select x.OrderItems.Sum(z => z.Amount * z.UnitPrice)).Sum();
var y = cDepartmentSpendingRatio.Series[0].Points.AddY(getSpendings);
cDepartmentSpendingRatio.Series[0].Points[y].AxisLabel = departments;
}
#endregion
#region Loading Barchart
cMonthlyDepartmentSpending.ChartAreas[0].AxisX.LabelStyle.Interval = 1;
foreach (var departments in getDepartments)
{
cMonthlyDepartmentSpending.Series.Add(departments);
foreach (var distinctDates in getDistinctDates)
{
var getList = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
where x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name == departments
select x).ToList();
var getSpendings = (from x in getList
where x.Date.ToString("yyyy/MM") == distinctDates
select x.OrderItems.Sum(z => z.Amount * z.UnitPrice)).Sum();
cMonthlyDepartmentSpending.Series[departments].Points.AddXY(distinctDates, getSpendings);
}
}
#endregion
}
}