private void Button_Click_1(object sender, RoutedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); SandboxDataSetTableAdapters.WasteTrackerDBTableAdapter da = new SandboxDataSetTableAdapters.WasteTrackerDBTableAdapter(); DateTime?Date = dateDatePicker.SelectedDate; SqlConnection conn = ConnectionHelper.GetConn(); conn.Open(); //iterate over datagrid, update values foreach (DataRow dr in ds.WasteTrackerDB.Rows) { string sqlString = "UPDATE WasteTrackerDB SET LeftOver = @LeftOver, Ordered = @Ordered WHERE MenuItem = @MenuItem AND Date = @Date"; Cmd = new SqlCommand(sqlString, conn); Cmd.Parameters.AddWithValue("@MenuItem", dr[2]); Cmd.Parameters.AddWithValue("@LeftOver", dr[3]); Cmd.Parameters.AddWithValue("@Ordered", dr[8]); Cmd.Parameters.AddWithValue("@Date", Date); Cmd.ExecuteNonQuery(); } BIMessageBox.Show("Leftover values have been updated"); conn.Close(); }
private void wasteTrackerStationsComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); //get index of combobox selected item 0 based int item = wasteTrackerStationsComboBox.SelectedIndex; SandboxDataSetTableAdapters.MenuItemsTableAdapter mida = new SandboxDataSetTableAdapters.MenuItemsTableAdapter(); //fill datagrid with dataset of menu items that match station selection mida.FillByStation(ds.MenuItems, item); }
public PagePrintSheets() { InitializeComponent(); SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); //Load combobox of stations CollectionViewSource stViewSource = ((CollectionViewSource)(FindResource("wasteTrackerStationsViewSource"))); SandboxDataSetTableAdapters.WasteTrackerStationsTableAdapter sbda = new SandboxDataSetTableAdapters.WasteTrackerStationsTableAdapter(); sbda.Fill(ds.WasteTrackerStations); }
private void Button_Click(object sender, RoutedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); //create new excel application Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); //create workbook and worksheet Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet); Worksheet ws = (Worksheet)xla.ActiveSheet; //start at row 4 row 1 is the Station, row 2 is date, row 3 is header int i = 4; //create header ws.Range["A1"].Cells.Value = wasteTrackerStationsComboBox.SelectedValue.ToString(); ws.Range["A2"].Cells.Value = "Report Generated on: " + DateTime.Now; //startDateDatePicker.SelectedDate.ToString(); ws.Range["A3"].Cells.ColumnWidth = 24; ws.Range["A3"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["A3"].Value = "Menu Item"; ws.Range["B3"].Cells.ColumnWidth = 24; ws.Range["B3"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["B3"].Value = "Date"; ws.Range["C3"].Cells.ColumnWidth = 24; ws.Range["C3"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["C3"].Value = "% of Par Left Over"; ws.Range["D3"].Cells.ColumnWidth = 24; ws.Range["D3"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["D3"].Value = "% of Prod Left Over"; ws.Range["E3"].Cells.ColumnWidth = 48; ws.Range["E3"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["E3"].Cells.WrapText = true; ws.Range["E3"].Value = "Notes"; //iterate through datagrid and put into excel doc foreach (DataRow dr in ds.WasteTrackerDB.Rows) { ws.Range["A" + i].Value = dr[2]; ws.Range["B" + i].Value = dr[6]; ws.Range["C" + i].Value = dr[9]; ws.Range["D" + i].Value = dr[10]; ws.Range["E" + i].Value = dr[12]; i++; } xla.Visible = true; }
public void FillData(DateTime?start, DateTime?end, int item) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); SandboxDataSetTableAdapters.WasteTrackerDBTableAdapter da = new SandboxDataSetTableAdapters.WasteTrackerDBTableAdapter(); string startdate = start.ToString(); string enddate = end.ToString(); if (startdate == null) { BIMessageBox.Show("Please Select a Date"); return; } else { da.FillByDateRange(ds.WasteTrackerDB, item, startdate, enddate); } }
public PageViewReports() { InitializeComponent(); try { //Load database SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); //Load combobox of stations CollectionViewSource stViewSource = ((CollectionViewSource)(FindResource("wasteTrackerStationsViewSource"))); SandboxDataSetTableAdapters.WasteTrackerStationsTableAdapter sbda = new SandboxDataSetTableAdapters.WasteTrackerStationsTableAdapter(); sbda.Fill(ds.WasteTrackerStations); } catch (Exception ex) { BIMessageBox.Show("Oops there was a problem, please contact Business Intelligence \n" + ex); } }
private void Button_Click(object sender, RoutedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); try { SqlConnection conn = ConnectionHelper.GetConn(); conn.Open(); //iterate over datagrid, update values foreach (DataRow dr in ds.MenuItems.Rows) { string sqlString = "UPDATE MenuItems SET Par = @Par, UoM = @UoM, IsActive = @IsActive WHERE MenuItem = @MenuItem AND @StationId = StationId"; Cmd = new SqlCommand(sqlString, conn); Cmd.Parameters.AddWithValue("@StationId", dr[1]); Cmd.Parameters.AddWithValue("@MenuItem", dr[2]); Cmd.Parameters.AddWithValue("@Par", dr[3]); Cmd.Parameters.AddWithValue("@UoM", dr[4]); Cmd.Parameters.AddWithValue("@IsActive", dr[5]); //get par cell for data validation string _par = dr[3].ToString(); decimal par; par = decimal.Parse(_par); if (par < 0) { BIMessageBox.Show("Please a positive number for the Par Amount."); return; } else { Cmd.ExecuteNonQuery(); } } conn.Close(); BIMessageBox.Show("Par level has been updated."); } catch (Exception ex) { BIMessageBox.Show("Oops there was a problem, please contact Business Intelligence \n" + ex); } }
private void Button_Click(object sender, RoutedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); SqlConnection conn = ConnectionHelper.GetConn(); conn.Open(); foreach (DataRow dr in ds.MenuItems.Rows) { string sqlString = "UPDATE MenuItems SET IsActive = @IsActive WHERE MenuItem = @MenuItem"; Cmd = new SqlCommand(sqlString, conn); Cmd.Parameters.AddWithValue("@MenuItem", dr[2]); Cmd.Parameters.AddWithValue("@IsActive", dr[5]); Cmd.ExecuteNonQuery(); } BIMessageBox.Show("Item is Active Again"); conn.Close(); }
private void Button_Click_PrintStation(object sender, RoutedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); CollectionViewSource stViewSource = ((CollectionViewSource)(FindResource("wasteTrackerStationsViewSource"))); #region Setup Excel Sheet //create new excel application Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); //create workbook and worksheet Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet); Worksheet ws = (Worksheet)xla.ActiveSheet; ws.PageSetup.FitToPagesWide = 1; ws.PageSetup.Orientation = XlPageOrientation.xlLandscape; //start at row 3 row 1 is the Station, row 3 is header int i = 3; //create header ws.Range["A1"].Cells.Value = wasteTrackerStationsComboBox.SelectedValue.ToString(); ws.Range["A2"].Cells.ColumnWidth = 8; ws.Range["A2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["A2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["A2"].Cells.Rows.RowHeight = 30; ws.Range["A2"].Value = "Station Id"; ws.Range["B2"].Cells.ColumnWidth = 26; ws.Range["B2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["B2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["B2"].Value = "Menu Item"; ws.Range["C2"].Cells.ColumnWidth = 4; ws.Range["C2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["C2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["C2"].Value = "UoM"; ws.Range["D2"].Cells.ColumnWidth = 6.2; ws.Range["D2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["D2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["D2"].Value = "Fri"; ws.Range["E2"].Cells.ColumnWidth = 6.2; ws.Range["E2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["E2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["E2"].Value = "Mon"; ws.Range["F2"].Cells.ColumnWidth = 6.2; ws.Range["F2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["F2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["F2"].Value = "Tue"; ws.Range["G2"].Cells.ColumnWidth = 6.2; ws.Range["G2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["G2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["G2"].Value = "Wed"; ws.Range["H2"].Cells.ColumnWidth = 6.2; ws.Range["H2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["H2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["H2"].Value = "Thu"; ws.Range["I2"].Cells.ColumnWidth = 40; ws.Range["I2"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ws.Range["I2"].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["I2"].Value = "Comments"; foreach (DataRow dr in ds.MenuItems.Rows) { ws.Range["A" + i].Value = dr[1]; ws.Range["A" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["A" + i].Cells.Rows.RowHeight = 30; ws.Range["B" + i].Value = dr[2]; ws.Range["B" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["C" + i].Value = dr[3]; ws.Range["C" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["D" + i].Value = ""; ws.Range["D" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["E" + i].Value = ""; ws.Range["E" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["F" + i].Value = ""; ws.Range["F" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["G" + i].Value = ""; ws.Range["G" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["H" + i].Value = ""; ws.Range["H" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; ws.Range["I" + i].Value = ""; ws.Range["I" + i].Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; i++; } #endregion xla.Visible = true; }
private void Button_Click(object sender, RoutedEventArgs e) { SandboxDataSet ds = ((SandboxDataSet)(FindResource("sandboxDataSet"))); SandboxDataSetTableAdapters.WasteTrackerDBTableAdapter da = new SandboxDataSetTableAdapters.WasteTrackerDBTableAdapter(); DateTime?Date = dateDatePicker.SelectedDate; //if there is a date selected if (Date != null) { try { //use SQL command to insert into DB SqlConnection conn = ConnectionHelper.GetConn(); conn.Open(); decimal LeftOver; decimal Ordered; //iterate over each row of DataGrid, get values of each cell, and insert into DB foreach (DataRow dr in ds.WasteTrackerDB.Rows) { string sqlString = "INSERT INTO WasteTrackerDB VALUES (@StationId, @MenuItem, @LeftOver, @Par, @UoM, @Date, @IsActive, @Ordered, @Notes)"; Cmd = new SqlCommand(sqlString, conn); Cmd.Parameters.AddWithValue("@StationId", dr[1]); Cmd.Parameters.AddWithValue("@MenuItem", dr[2]); Cmd.Parameters.AddWithValue("@LeftOver", dr[3]); Cmd.Parameters.AddWithValue("@Par", dr[4]); Cmd.Parameters.AddWithValue("@UoM", dr[5]); Cmd.Parameters.AddWithValue("@Date", Date); Cmd.Parameters.AddWithValue("@IsActive", 1); Cmd.Parameters.AddWithValue("@Ordered", dr[8]); Cmd.Parameters.AddWithValue("@Notes", dr[12]); string leftOver = dr[3].ToString(); string ordered = dr[8].ToString(); if (leftOver == "" || ordered == "") { BIMessageBox.Show("Please enter a valid number between 0 and 99999999"); return; } else { LeftOver = decimal.Parse(leftOver); Ordered = decimal.Parse(ordered); if (LeftOver < 0 || Ordered < 0 || LeftOver == -1 || Ordered == -1) { BIMessageBox.Show("Please enter a valid number between 0 and 9999999999"); return; } else { Cmd.ExecuteNonQuery(); } } } BIMessageBox.Show("Leftover values have been added"); conn.Close(); } catch (Exception ex) { BIMessageBox.Show("Oops there was a problem, please contact Business Intelligence \n" + ex); } } //oops there was no date selected else { BIMessageBox.Show("Please Enter a Date"); return; } }