/// <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>
/// Parse the content of the selected .csv file in the objects of the classes associated with the database
/// </summary>
public void ParseFile()
{
using (StreamReader StreamReader = new StreamReader(StrFilePath, Encoding.Default))
{
string Line, BrandName = "", FamilyName = "", SubfamilyName = "";
string[] SplitedLine;
bool Existence;
int BrandReference = 1, FamilyReference = 1, SubfamilyReference = 1, NumberBrands, NumberFamilies, NumberSubfamilies;
StreamReader.ReadLine(); // Read the first line of the file
// Read all other lines in the file
while ((Line = StreamReader.ReadLine()) != null)
{
SplitedLine = Line.Split(';'); // Item characteristic
Existence = false;
foreach (Brand Brand in Brands)
{
if (Brand.GetName() == SplitedLine[2])
{
BrandReference = Brand.GetBrandReference();
BrandName = Brand.GetName();
Existence = true;
break;
}
}
// Add the brand if it does not already exist
if (!Existence)
{
Brands.Add(new Brand(Brands.Count + 1, SplitedLine[2]));
BrandReference = Brands.Count;
}
Existence = false;
foreach (Family Family in Families)
{
if (Family.GetName() == SplitedLine[3])
{
FamilyReference = Family.GetFamilyReference();
FamilyName = Family.GetName();
Existence = true;
break;
}
}
// Add the family if it does not already exist
if (!Existence)
{
Families.Add(new Family(Families.Count + 1, SplitedLine[3]));
FamilyReference = Families.Count;
}
Existence = false;
foreach (Subfamily Subfamily in Subfamilies)
{
if (Subfamily.GetName() == SplitedLine[4])
{
SubfamilyReference = Subfamily.GetSubfamilyReference();
SubfamilyName = Subfamily.GetName();
Existence = true;
break;
}
}
// Add the subfamily if it does not already exist
if (!Existence)
{
Subfamilies.Add(new Subfamily(Subfamilies.Count + 1, FamilyReference, SplitedLine[4]));
SubfamilyReference = Subfamilies.Count;
}
Existence = false;
foreach (Item Item in Items)
{
// Replacing the current item characteristics with these if the item reference already exists
if (Item.GetItemReference() == SplitedLine[1])
{
Anomalies.Add(Item.GetItemReference());
Item.SetDescription(SplitedLine[0]);
Item.SetBrandReference(BrandReference);
Item.SetSubfamilyReference(SubfamilyReference);
Item.SetPrice(float.Parse(SplitedLine[5]));
if (BrandName != SplitedLine[2])
{
NumberBrands = 0;
foreach (Item BrandItem in Items)
{
if (BrandItem.GetBrandReference() == BrandReference)
{
NumberBrands++;
}
}
if (NumberBrands == 0)
{
foreach (Brand Brand in Brands)
{
if (Brand.GetBrandReference() == BrandReference)
{
Brands.Remove(Brand);
break;
}
}
}
}
if (SubfamilyName != SplitedLine[4])
{
NumberSubfamilies = 0;
foreach (Item SubfamilyItem in Items)
{
if (SubfamilyItem.GetSubfamilyReference() == SubfamilyReference)
{
NumberSubfamilies++;
}
}
if (NumberSubfamilies == 0)
{
foreach (Subfamily Subfamily in Subfamilies)
{
if (Subfamily.GetSubfamilyReference() == SubfamilyReference)
{
Subfamilies.Remove(Subfamily);
break;
}
}
}
}
if (FamilyName != SplitedLine[3])
{
NumberFamilies = 0;
foreach (Subfamily Subfamily in Subfamilies)
{
if (Subfamily.GetFamilyReference() == FamilyReference)
{
NumberFamilies++;
}
}
if (NumberFamilies == 0)
{
foreach (Family Family in Families)
{
if (Family.GetFamilyReference() == FamilyReference)
{
Families.Remove(Family);
break;
}
}
}
}
Existence = true;
break;
}
}
// Adding the item if the item reference does not yet exist
if (!Existence)
{
Items.Add(new Item(SplitedLine[1], SplitedLine[0], SubfamilyReference, BrandReference, float.Parse(SplitedLine[5])));
}
}
}
}
/// <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();
}
}