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 } }