private void LoadFields()
{
dateBox.Value = DateTime.Now;
using (var context = new Session6Entities())
{
var getAsset = (from x in context.Assets
join y in context.EmergencyMaintenances on x.ID equals y.AssetID
where y.EMStartDate != null && y.EMEndDate == null
select new { Asset = x.AssetName, EM = y.ID });
List <string> assets = new List <string>();
foreach (var item in getAsset)
{
assets.Add(item.Asset + " (" + item.EM + ")");
}
assetBox.Items.AddRange(assets.ToArray());
assetBox.SelectedItem = assets.First();
var getWarehouse = (from x in context.Warehouses
select x.Name).Distinct().ToList();
warehouseBox.Items.AddRange(getWarehouse.ToArray());
warehouseBox.SelectedItem = getWarehouse.First();
List <string> vs = new List <string>()
{
"LIFO", "FIFO", "Minimum Price"
};
aMethodBox.Items.AddRange(vs.ToArray());
aMethodBox.SelectedItem = vs[2];
}
}
private void partBox_Click(object sender, EventArgs e)
{
using (var context = new Session6Entities())
{
if (warehouseBox.SelectedItem != null)
{
partBox.Items.Clear();
var warehouseName = warehouseBox.SelectedItem.ToString();
var getWarehouseID = (from x in context.Warehouses
where x.Name.Equals(warehouseName)
select x.ID).FirstOrDefault();
var getParts = (from x in context.Orders
where x.SourceWarehouseID == getWarehouseID
join y in context.OrderItems on x.ID equals y.OrderID
where y.Amount != 0
select y.Part.Name).Distinct();
List <string> partList = new List <string>();
foreach (var parts in getParts)
{
partList.Add(parts);
}
partBox.Items.AddRange(partList.ToArray());
}
}
}
private void submitBtn_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow item in assignedList.Rows)
{
if (item == null)
{
MessageBox.Show("A part needs to be allocated to submit changes to Database!",
"No changes made!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
using (var context = new Session6Entities())
{
var part = item.Cells[0].Value.ToString();
var getPartID = (from x in context.Parts
where x.Name == part
select x.ID).FirstOrDefault();
if (item.Cells[1].Value.ToString() != "")
{
context.OrderItems.Add(new OrderItem()
{
PartID = getPartID,
BatchNumber = item.Cells[1].Value.ToString(),
UnitPrice = Convert.ToDecimal(item.Cells[2].Value),
Amount = Convert.ToDecimal(item.Cells[3].Value),
OrderID = Convert.ToInt64(item.Cells[4].Value)
});
}
else
{
context.OrderItems.Add(new OrderItem()
{
PartID = getPartID,
UnitPrice = Convert.ToDecimal(item.Cells[2].Value),
Amount = Convert.ToDecimal(item.Cells[3].Value),
OrderID = Convert.ToInt64(item.Cells[4].Value)
});
}
var orderID = Convert.ToInt64(item.Cells[4].Value);
var updateOrders = (from x in context.Orders
where x.ID == orderID
select new { x }).First();
updateOrders.x.Date = dateBox.Value;
context.SaveChanges();
this.Close();
}
}
}
}
private void allocateBtn_Click(object sender, EventArgs e)
{
allocatedList.ColumnCount = 5;
allocatedList.Columns[0].Name = "Name";
allocatedList.Columns[1].Name = "Batch Number";
allocatedList.Columns[2].Name = "Unit Price";
allocatedList.Columns[3].Name = "Amount";
allocatedList.Columns[4].Name = "ID";
allocatedList.Columns[4].Visible = false;
allocatedList.Rows.Clear();
using (var context = new Session6Entities())
{
var amount = Decimal.Parse(amtBox.Text);
var selectedPart = partBox.SelectedItem.ToString();
if (aMethodBox.SelectedItem.ToString() == "LIFO")
{
var getPart = (from x in context.OrderItems
where x.Part.Name == selectedPart
orderby x.Order.Date descending
where x.Amount <= amount
select new { Name = x.Part.Name, BatchNumber = x.BatchNumber, unitPrice = x.UnitPrice, Amount = x.Amount, ID = x.OrderID });
decimal amtToAdd = 0;
while (amtToAdd <= amount)
{
foreach (var item in getPart)
{
List <string> rows = new List <string>()
{
item.Name, item.BatchNumber, item.unitPrice.ToString(), item.Amount.ToString(), item.ID.ToString()
};
amtToAdd += item.Amount;
if (amtToAdd > amount)
{
break;
}
allocatedList.Rows.Add(rows.ToArray());
}
}
}
else if (aMethodBox.SelectedItem.ToString() == "FIFO")
{
var getPart = (from x in context.OrderItems
where x.Part.Name == selectedPart
orderby x.Order.Date ascending
where x.Amount <= amount
select new { Name = x.Part.Name, BatchNumber = x.BatchNumber, unitPrice = x.UnitPrice, Amount = x.Amount, ID = x.OrderID });
decimal amtToAdd = 0;
while (amtToAdd <= amount)
{
foreach (var item in getPart)
{
List <string> rows = new List <string>()
{
item.Name, item.BatchNumber, item.unitPrice.ToString(), item.Amount.ToString(), item.ID.ToString()
};
amtToAdd += item.Amount;
if (amtToAdd > amount)
{
break;
}
allocatedList.Rows.Add(rows.ToArray());
}
}
}
else
{
var getPart = (from x in context.OrderItems
where x.Part.Name == selectedPart
orderby x.UnitPrice * x.Amount ascending
where x.Amount <= amount
select new { Name = x.Part.Name, BatchNumber = x.BatchNumber, unitPrice = x.UnitPrice, Amount = x.Amount, ID = x.OrderID });
decimal amtToAdd = 0;
while (amtToAdd <= amount)
{
foreach (var item in getPart)
{
List <string> rows = new List <string>()
{
item.Name, item.BatchNumber, item.unitPrice.ToString(), item.Amount.ToString(), item.ID.ToString()
};
amtToAdd += item.Amount;
if (amtToAdd > amount)
{
break;
}
allocatedList.Rows.Add(rows.ToArray());
}
}
}
}
}
private void GridRefresh()
{
spendList.ColumnCount = 1;
spendList.Columns[0].Name = "Department / Month";
mostUsedList.ColumnCount = 1;
mostUsedList.Columns[0].Name = "Notes / Month";
costlyAssetList.ColumnCount = 1;
costlyAssetList.Columns[0].Name = "Asset Name / Month";
spendList.Rows.Clear();
mostUsedList.Rows.Clear();
costlyAssetList.Rows.Clear();
using (var context = new Session6Entities())
{
var getDepartment = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
select x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name).ToList();
var getDepartmentSpendingGrouping = (from x in context.Orders
where x.EmergencyMaintenance.EMEndDate != null && x.EmergencyMaintenance.EMStartDate != null
orderby x.Date descending
select new { intDate = x.Date }).ToList();
var getDistinctDates = (from x in getDepartmentSpendingGrouping
select new { date = x.intDate.ToString("yyyy-MM") }).ToList().Select(x => x.date).Distinct().Take(10);
#region 1st DGV Loading
foreach (var date in getDistinctDates)
{
spendList.Columns.Add(date, date);
mostUsedList.Columns.Add(date, date);
costlyAssetList.Columns.Add(date, date);
}
foreach (var department in getDepartment.Select(x => x).Distinct())
{
List <string> row = new List <string>();
row.Add(department);
Console.WriteLine(department);
foreach (var date in getDistinctDates)
{
var getDetailsOfSpending = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
where x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name == department
select x).ToList();
var getDetailsOfSpending1 = (from x in getDetailsOfSpending
where x.Date.ToString("yyyy-MM") == date
select x.OrderItems.Sum(p => p.Amount * p.UnitPrice)).Sum().Value.ToString("0");
row.Add(getDetailsOfSpending1);
}
spendList.Rows.Add(row.ToArray());
}
#endregion
#region PieChart Load
pieChart.Series["s1"].Points.Clear();
var getSpendingsOfDepartment = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
group x by x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name into q
select new { spendings = q.Sum(y => y.OrderItems.Sum(z => z.UnitPrice * z.Amount)), DeptName = q.Key });
foreach (var r in getSpendingsOfDepartment)
{
var index = pieChart.Series["s1"].Points.AddY(r.spendings);
pieChart.Series["s1"].Points[index].Label = r.DeptName;
}
#endregion
#region Bar graph load
barChart.Series.Clear();
var initialQuery1 = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
select x).ToList();
foreach (var departments in getDepartment.Select(x => x).Distinct())
{
barChart.Series.Add(new Series(departments));
barChart.ChartAreas["ChartArea1"].AxisX.LabelStyle.Interval = 1;
foreach (var dates in getDistinctDates)
{
var getPoints = (from x in initialQuery1
where x.Date.ToString("yyyy-MM") == dates && x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name == departments
select x.OrderItems.Sum(z => z.UnitPrice * z.Amount).Value).Sum();
if (getPoints == 0)
{
barChart.Series[departments].Points.AddXY(dates, 0);
}
else
{
barChart.Series[departments].Points.AddXY(dates, getPoints);
}
}
}
#endregion
#region 2nd DGV Loading
List <string> noteList = new List <string>()
{
"Highest Cost", "Most Number"
};
foreach (var notes in noteList)
{
List <string> rows = new List <string>();
rows.Add(notes);
foreach (var dates in getDistinctDates)
{
var getParts = (from x in context.Parts
select x.Name).ToList();
if (notes == "Highest Cost")
{
List <decimal> comparison = new List <decimal>();
List <string> comparisonName = new List <string>();
foreach (var parts in getParts)
{
var initialQuery = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
select x).ToList();
var getCost = (from x in initialQuery
where x.Date.ToString("yyyy-MM") == dates
select x.OrderItems.Where(y => y.Part.Name == parts).Sum(p => p.Amount * p.UnitPrice)).Sum();
comparison.Add(Convert.ToDecimal(getCost));
comparisonName.Add(parts);
}
var getHighest = comparison.Max();
int index = comparison.FindIndex(x => x == getHighest);
var getPartName = comparisonName[index];
rows.Add(getPartName);
}
else
{
List <decimal> NumberOfTransaction = new List <decimal>();
List <string> comparisonName = new List <string>();
foreach (var parts in getParts)
{
var initialQuery = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
select x).ToList();
var getCount = (from x in initialQuery
where x.Date.ToString("yyyy-MM") == dates
where x.TransactionTypeID == 3
select x.OrderItems.Where(y => y.Part.Name == parts).Sum(y => y.Amount)).Sum();
NumberOfTransaction.Add(getCount);
comparisonName.Add(parts);
}
var getHighest = NumberOfTransaction.Max();
int index = NumberOfTransaction.FindIndex(x => x == getHighest);
var getPartName = comparisonName[index];
rows.Add(getPartName);
}
}
mostUsedList.Rows.Add(rows.ToArray());
}
#endregion
#region 3rd DGV Loading
List <string> main = new List <string>()
{
"Asset", "Department"
};
foreach (var label in main)
{
List <string> row = new List <string>();
row.Add(label);
foreach (var dates in getDistinctDates)
{
if (label == "Asset")
{
List <string> Assets = new List <string>();
List <decimal> CostTotal = new List <decimal>();
var getAsset = (from x in context.Assets
select x.AssetName);
foreach (var Asset in getAsset)
{
var initialQuery = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
select x).ToList();
var getCostly = (from x in initialQuery
where x.Date.ToString("yyyy-MM") == dates && x.EmergencyMaintenance.Asset.AssetName == Asset
select x.OrderItems.Sum(y => y.Amount * y.UnitPrice)).Sum().Value;
Assets.Add(Asset);
CostTotal.Add(getCostly);
}
var getHighest = CostTotal.Max();
int index = CostTotal.FindIndex(x => x == getHighest);
var getAssetName = Assets[index];
row.Add(getAssetName);
}
else
{
List <string> Department = new List <string>();
List <decimal> CostTotal = new List <decimal>();
var getAsset = (from x in context.Assets
select x.AssetName);
foreach (var Asset in getAsset)
{
var initialQuery = (from x in context.Orders
where x.EmergencyMaintenance.EMStartDate != null && x.EmergencyMaintenance.EMEndDate != null
select x).ToList();
var getCostly = (from x in initialQuery
where x.Date.ToString("yyyy-MM") == dates && x.EmergencyMaintenance.Asset.AssetName == Asset
select x.OrderItems.Sum(y => y.Amount * y.UnitPrice)).Sum().Value;
var getDepartmentOfAsset = (from x in initialQuery
where x.Date.ToString("yyyy-MM") == dates && x.EmergencyMaintenance.Asset.AssetName == Asset
select x.EmergencyMaintenance.Asset.DepartmentLocation.Department.Name).FirstOrDefault();
Department.Add(getDepartmentOfAsset);
CostTotal.Add(getCostly);
}
var getHighest = CostTotal.Max();
int index = CostTotal.FindIndex(x => x == getHighest);
var getDepartmentName = Department[index];
row.Add(getDepartmentName);
}
}
costlyAssetList.Rows.Add(row.ToArray());
}
#endregion
#region Coloring 1st DGV
List <decimal> remove0 = new List <decimal>()
{
0
};
foreach (DataGridViewColumn columns in spendList.Columns)
{
List <decimal> listToCompare = new List <decimal>();
foreach (DataGridViewRow rows in spendList.Rows)
{
if (columns.Index != 0)
{
listToCompare.Add(Convert.ToDecimal(spendList.Rows[rows.Index].Cells[columns.Index].Value));
}
}
var refineQuery = (from x in listToCompare
select x).Except(new List <decimal>()
{
0
}).ToList();
Console.WriteLine(listToCompare);
var getCount = refineQuery.Count();
if (getCount != 0)
{
var getMax = refineQuery.Max();
var getLowest = refineQuery.Min();
Console.WriteLine(getMax);
Console.WriteLine(getLowest);
foreach (DataGridViewRow item in spendList.Rows)
{
if (Convert.ToDecimal(item.Cells[columns.Index].Value) == getMax)
{
item.Cells[columns.Index].Style.ForeColor = Color.Green;
if (Convert.ToDecimal(item.Cells[columns.Index].Value) == getLowest)
{
item.Cells[columns.Index].Style.ForeColor = Color.Red;
}
}
else if (Convert.ToDecimal(item.Cells[columns.Index].Value) == getLowest)
{
item.Cells[columns.Index].Style.ForeColor = Color.Red;
if (Convert.ToDecimal(item.Cells[columns.Index].Value) == getMax)
{
item.Cells[columns.Index].Style.ForeColor = Color.Red;
}
}
}
}
}
#endregion
}
}