Example #1
0
        /// <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();
            }
        }
Example #2
0
        /// <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])));
                    }
                }
            }
        }
Example #3
0
        /// <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();
            }
        }