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