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