/// <summary> /// Updates the datagrid with the given filters /// </summary> /// <param name="filterParameters"></param> private void UpdateDataGrid(LotFilterParameters filterParameters) { List <Lot> lots = Program.manager.GetFilteredLots(filterParameters); lotDataGrid.Rows.Clear(); Recette recette = new Recette(); for (int i = 0; i < lots.Count(); i++) { recette = Program.manager.GetRecetteByID(lots[i].RecetteID); string status = ""; switch (lots[i].StatusID) { case 1: status = "Terminé"; break; case 2: status = "En Production"; break; case 3: status = "En attente"; break; case 4: status = "Ouvert"; break; } string[] row = { lots[i].Nom, lots[i].DateCreation.ToString(), lots[i].DateButoir.ToString(), lots[i].Quantite.ToString(), lots[i].QuantiteAtteinte.ToString(), recette.Nom, status }; lotDataGrid.Rows.Add(row); } }
/// <summary> /// On load of the page /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Tracabilite_Load(object sender, EventArgs e) { SetupDataGrid(); // Loads the lots with no filters at the pages first load LotFilterParameters lotFilterParameters = new LotFilterParameters(); lotFilterParameters.UseDateFilter = false; lotFilterParameters.StateFilter = StateFilterOptions.all; UpdateDataGrid(lotFilterParameters); }
private void update_Click(object sender, EventArgs e) { LotFilterParameters lotFilterParameters = new LotFilterParameters(); // Date filter lotFilterParameters.UseDateFilter = useDateFilters.Checked; lotFilterParameters.Start = timePickerDebut.Value; lotFilterParameters.End = timePickerFin.Value; if (radioDateButoir.Checked) { lotFilterParameters.DateFilterOptions = DateFilterOptions.dateButoir; } else { lotFilterParameters.DateFilterOptions = DateFilterOptions.dateCreation; } // State filter if (radioTermine.Checked) { lotFilterParameters.StateFilter = StateFilterOptions.finished; } else if (radioProduction.Checked) { lotFilterParameters.StateFilter = StateFilterOptions.inProduction; } else if (radioTous.Checked) { lotFilterParameters.StateFilter = StateFilterOptions.all; } else if (radioAttente.Checked) { lotFilterParameters.StateFilter = StateFilterOptions.waiting; } else if (radioOuvert.Checked) { lotFilterParameters.StateFilter = StateFilterOptions.open; } UpdateDataGrid(lotFilterParameters); }
/// <summary> /// Get all lots with the given filters /// </summary> /// <param name="filterParameters">(see struct) all parameters to apply on the query</param> public List <Lot> GetFilteredLots(LotFilterParameters filterParameters) { OpenConnection(); string SQLString = "SELECT * FROM lot WHERE "; // String used to compose the SQL command string dateStart = ""; string dateEnd = ""; // If date filters are used, add them to the command if (filterParameters.UseDateFilter) { string column = ""; if (filterParameters.DateFilterOptions == DateFilterOptions.dateButoir) { column = "Lot_DateButoir"; } else { column = "Lot_DateCreation"; } SQLString += column + " > STR_TO_DATE(@DateStart, '%Y-%m-%d %H:%i:%s') AND " + column + " < STR_TO_DATE(@DateEnd, '%Y-%m-%d %H:%i:%s') AND "; dateStart = filterParameters.Start.ToString("yyyy-MM-dd HH:mm:ss"); dateEnd = filterParameters.End.ToString("yyyy-MM-dd HH:mm:ss"); } // Add the state filters to the command switch (filterParameters.StateFilter) { case StateFilterOptions.all: SQLString += "1 = 1"; break; case StateFilterOptions.finished: SQLString += "Stu_ID = 1"; break; case StateFilterOptions.inProduction: SQLString += "Stu_ID = 2"; break; case StateFilterOptions.waiting: SQLString += "Stu_ID = 3"; break; case StateFilterOptions.open: SQLString += "Stu_ID = 4"; break; } MySqlCommand cmd = Conn.CreateCommand(); cmd.CommandText = SQLString; Debug.WriteLine(SQLString); Debug.WriteLine(dateStart + " " + dateEnd); // If date filters are used, add the parameters with values if (filterParameters.UseDateFilter) { cmd.Parameters.AddWithValue("@DateStart", dateStart); cmd.Parameters.AddWithValue("@DateEnd", dateEnd); } Debug.WriteLine(cmd.CommandText); cmd.Prepare(); MySqlDataReader reader = cmd.ExecuteReader(); List <Lot> lots = new List <Lot>(); Lot lot = new Lot(); while (reader.Read()) { lot.ID = int.Parse(reader["Lot_Numero"].ToString()); lot.Nom = reader["Lot_Nom"].ToString(); lot.DateCreation = DateTime.Parse(reader["Lot_DateCreation"].ToString()); DateTime.TryParse(reader["Lot_DateButoir"].ToString(), out lot.DateButoir); // This one is by TryParse() because it can be empty lot.Quantite = int.Parse(reader["Lot_Quantite"].ToString()); lot.QuantiteAtteinte = int.Parse(reader["Lot_QuantiteAtteinte"].ToString()); lot.RecetteID = int.Parse(reader["Rct_Numero"].ToString()); lot.StatusID = int.Parse(reader["Stu_ID"].ToString()); lots.Add(lot); } CloseConnection(); return(lots); }