/// <summary>
/// Add and update the data in the selected .csv file to the Database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnAjout_Click(object sender, EventArgs e)
{
PbIntegration.Visible = true;
PbIntegration.Value = 0;
// Creation of queries
InsertBrands = "INSERT INTO Marques (Nom) VALUES ";
InsertFamilies = "INSERT INTO Familles (Nom) VALUES ";
InsertSubfamilies = "INSERT INTO SousFamilles (RefFamille, Nom) VALUES ";
InsertItems = "INSERT INTO Articles VALUES ";
Message = "";
Brands.Clear();
Families.Clear();
Subfamilies.Clear();
Items.Clear();
Anomalies.Clear();
SQLiteDataReader SQLiteDataReader;
int NumberItemsUpdated = 0;
ParseFile();
try
{
SQLiteConnection.Open();
SQLiteCommand SQLiteCommand = SQLiteConnection.CreateCommand();
SQLiteDataReader = new SQLiteCommand("SELECT Nom FROM Marques", SQLiteConnection).ExecuteReader();
while (SQLiteDataReader.Read())
{
foreach (Brand Brand in Brands)
{
if (Brand.GetName() == SQLiteDataReader["Nom"].ToString())
{
Brands.Remove(Brand);
break;
}
}
}
SQLiteDataReader.Close();
SQLiteDataReader = new SQLiteCommand("SELECT RefFamille, Nom FROM SousFamilles", SQLiteConnection).ExecuteReader();
while (SQLiteDataReader.Read())
{
foreach (Subfamily Subfamily in Subfamilies)
{
if (Subfamily.GetFamilyReference() == int.Parse(SQLiteDataReader["RefFamille"].ToString()) && Subfamily.GetName() == SQLiteDataReader["Nom"].ToString())
{
Subfamilies.Remove(Subfamily);
break;
}
}
}
SQLiteDataReader.Close();
SQLiteDataReader = new SQLiteCommand("SELECT Nom FROM Familles", SQLiteConnection).ExecuteReader();
while (SQLiteDataReader.Read())
{
foreach (Family Family in Families)
{
if (Family.GetName() == SQLiteDataReader["Nom"].ToString())
{
Families.Remove(Family);
break;
}
}
}
SQLiteDataReader.Close();
// Updating of existing elements
SQLiteDataReader = new SQLiteCommand("SELECT RefArticle, Description, RefSousFamille, RefMarque, PrixHT, Quantite FROM Articles", SQLiteConnection).ExecuteReader();
while (SQLiteDataReader.Read())
{
foreach (Item Item in Items)
{
if (Item.GetItemReference() == SQLiteDataReader["RefArticle"].ToString() && Item.GetDescription() == SQLiteDataReader["Description"].ToString() && Item.GetSubfamilyReference() == int.Parse(SQLiteDataReader["RefSousFamille"].ToString()) && Item.GetBrandReference() == int.Parse(SQLiteDataReader["RefMarque"].ToString()) && Item.GetPrice() == float.Parse(SQLiteDataReader["PrixHT"].ToString()))
{
SQLiteCommand.CommandText = "UPDATE Articles SET Quantite = " + (int.Parse(SQLiteDataReader["Quantite"].ToString()) + 1) + " WHERE RefArticle = '" + Item.GetItemReference() + "';";
SQLiteCommand.ExecuteNonQuery();
NumberItemsUpdated++;
Items.Remove(Item);
break;
}
else if (Item.GetItemReference() == SQLiteDataReader["RefArticle"].ToString())
{
Anomalies.Add(Item.GetItemReference());
SQLiteCommand.CommandText = "UPDATE Articles SET Description = '" + Item.GetDescription() + "', RefSousFamille = " + Item.GetSubfamilyReference() + ", RefMarque = " + Item.GetBrandReference() + ", PrixHT = " + Item.GetPrice().ToString().Replace(',', '.') + ", Quantite = " + (int.Parse(SQLiteDataReader["Quantite"].ToString()) + 1) + " WHERE RefArticle = '" + Item.GetItemReference() + "';";
SQLiteCommand.ExecuteNonQuery();
NumberItemsUpdated++;
Items.Remove(Item);
break;
}
}
}
SQLiteDataReader.Close();
foreach (Brand Brand in Brands)
{
InsertBrands = string.Concat(InsertBrands, "('", Brand.GetName(), "'),");
}
InsertBrands = RefactorSQL(InsertBrands);
foreach (Family Family in Families)
{
InsertFamilies = string.Concat(InsertFamilies, "('", Family.GetName(), "'),");
}
InsertFamilies = RefactorSQL(InsertFamilies);
foreach (Subfamily Subfamily in Subfamilies)
{
InsertSubfamilies = string.Concat(InsertSubfamilies, "('", Subfamily.GetFamilyReference(), "', '", Subfamily.GetName(), "'),");
}
InsertSubfamilies = RefactorSQL(InsertSubfamilies);
foreach (Item Item in Items)
{
InsertItems = string.Concat(InsertItems, "('", Item.GetItemReference(), "', '", Item.GetDescription(), "', '", Item.GetSubfamilyReference(), "', '", Item.GetBrandReference(), "', '", Item.GetPrice().ToString().Replace(',', '.'), "', 0),");
}
InsertItems = RefactorSQL(InsertItems);
if (Items.Count == 0)
{
PbIntegration.Maximum = 1;
}
PbIntegration.PerformStep();
if (Items.Count != 0)
{
PbIntegration.Maximum = 5;
// Insertion of elements that do not exist in the database
SQLiteCommand.CommandText = InsertBrands;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
SQLiteCommand.CommandText = InsertFamilies;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
SQLiteCommand.CommandText = InsertSubfamilies;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
SQLiteCommand.CommandText = InsertItems;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
}
switch (Anomalies.Count)
{
case 0:
Message = "";
break;
case 1:
Message = Environment.NewLine + "1 anomalie a été détectée sur l'article de référence suivante qui a été indiqué plusieurs fois avec une ou plusieurs caractéristique(s) différente(s) : " + Environment.NewLine + Anomalies.First() + Environment.NewLine + "Les caractéristiques de la dernière ligne contenant cette référence ont donc été prises en compte.";
break;
default:
Message = Environment.NewLine + Anomalies.Count + " anomalies ont été détectées sur les articles de références suivantes qui ont été indiqués plusieurs fois avec une ou plusieurs caractéristique(s) différente(s) : " + Environment.NewLine;
foreach (string Anomaly in Anomalies)
{
Message = string.Concat(Message, Anomaly, Environment.NewLine);
}
Message += "Les caractéristiques des dernières lignes contenant respectivement ces références ont donc été prises en compte.";
break;
}
MessageBox.Show(Items.Count + " article(s) ajouté(s) et " + NumberItemsUpdated + " mis à jour." + Message);
PbIntegration.Value = 0;
}
catch (SQLiteException SQLiteException)
{
MessageBox.Show(SQLiteException.Message);
}
finally
{
SQLiteConnection.Close();
}
}
/// <summary>
/// Clear the .SQLite Database then add the data in the selected .csv file to the Database.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnEcrasement_Click(object sender, EventArgs e)
{
PbIntegration.Visible = true;
PbIntegration.Value = 0;
// Creation of queries
InsertBrands = "INSERT INTO Marques (Nom) VALUES ";
InsertFamilies = "INSERT INTO Familles (Nom) VALUES ";
InsertSubfamilies = "INSERT INTO SousFamilles (RefFamille, Nom) VALUES ";
InsertItems = "INSERT INTO Articles VALUES ";
Message = "";
Brands.Clear();
Families.Clear();
Subfamilies.Clear();
Items.Clear();
Anomalies.Clear();
ParseFile();
if (Items.Count == 0)
{
PbIntegration.Maximum = 1;
}
foreach (Brand Brand in Brands)
{
InsertBrands = string.Concat(InsertBrands, "('", Brand.GetName(), "'),");
}
InsertBrands = RefactorSQL(InsertBrands);
foreach (Family Family in Families)
{
InsertFamilies = string.Concat(InsertFamilies, "('", Family.GetName(), "'),");
}
InsertFamilies = RefactorSQL(InsertFamilies);
foreach (Subfamily Subfamily in Subfamilies)
{
InsertSubfamilies = string.Concat(InsertSubfamilies, "('", Subfamily.GetFamilyReference(), "', '", Subfamily.GetName(), "'),");
}
InsertSubfamilies = RefactorSQL(InsertSubfamilies);
foreach (Item Item in Items)
{
InsertItems = string.Concat(InsertItems, "('", Item.GetItemReference(), "', '", Item.GetDescription(), "', '", Item.GetSubfamilyReference(), "', '", Item.GetBrandReference(), "', '", Item.GetPrice().ToString().Replace(',', '.'), "', 0),");
}
InsertItems = RefactorSQL(InsertItems);
try
{
SQLiteConnection.Open();
SQLiteCommand SQLiteCommand = SQLiteConnection.CreateCommand();
// Element insertion
SQLiteCommand.CommandText = "DELETE FROM Articles;";
SQLiteCommand.ExecuteNonQuery();
SQLiteCommand.CommandText = "DELETE FROM Familles;";
SQLiteCommand.ExecuteNonQuery();
SQLiteCommand.CommandText = "DELETE FROM Marques;";
SQLiteCommand.ExecuteNonQuery();
SQLiteCommand.CommandText = "DELETE FROM SousFamilles;";
SQLiteCommand.ExecuteNonQuery();
SQLiteCommand.CommandText = "DELETE FROM sqlite_sequence;";
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
if (Items.Count != 0)
{
PbIntegration.Maximum = 5;
SQLiteCommand.CommandText = InsertBrands;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
SQLiteCommand.CommandText = InsertFamilies;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
SQLiteCommand.CommandText = InsertSubfamilies;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
SQLiteCommand.CommandText = InsertItems;
SQLiteCommand.ExecuteNonQuery();
PbIntegration.PerformStep();
}
// Anomaly management
switch (Anomalies.Count)
{
case 0:
Message = "";
break;
case 1:
Message = Environment.NewLine + "1 anomalie a été détectée sur l'article de référence suivante qui a été indiqué plusieurs fois avec une ou plusieurs caractéristique(s) différente(s) : " + Environment.NewLine + Anomalies.First() + Environment.NewLine + "Les caractéristiques de la dernière ligne contenant cette référence ont donc été prises en compte.";
break;
default:
Message = Environment.NewLine + Anomalies.Count + " anomalies ont été détectées sur les articles de références suivantes qui ont été indiqués plusieurs fois avec une ou plusieurs caractéristique(s) différente(s) : " + Environment.NewLine;
foreach (string Anomaly in Anomalies)
{
Message = string.Concat(Message, Anomaly, Environment.NewLine);
}
Message += "Les caractéristiques des dernières lignes contenant respectivement ces références ont donc été prises en compte.";
break;
}
MessageBox.Show(Items.Count + " article(s) ajouté(s)." + Message);
PbIntegration.Value = 0;
}
catch (SQLiteException SQLiteException)
{
MessageBox.Show(SQLiteException.Message);
}
finally
{
SQLiteConnection.Close();
}
}