Fuel_ASo/Fuel_ASO/f_impvehi.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;

namespace Fuel01
{
    public partial class F_impvehi : Form
    {
        private readonly string mode;
        private bool hasChanged = false;
        private List<Produit> lcarbu = new List<Produit>();
        private List<Famille> lfamille = new List<Famille>();
        private List<Produit> lproduit = new List<Produit>();
        private DataSet1.tb_vehiDataTable tb_vehi = new DataSet1.tb_vehiDataTable();

        public F_impvehi(string _mode)
        {
            InitializeComponent();
            mode = _mode;
        }

        private void Bt_ok_Click(object sender, EventArgs e)
        {
            if (checkZero.Checked)
            {
                tb_vehi.Clear();
            }
            if (mode == "tdf")
            {
                Import_tdf();
            }
            
        }

        private void Bt_openFile_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.DialogResult res = openFileParc.ShowDialog();
            if (res == System.Windows.Forms.DialogResult.OK)
            {
                textNomFile.Text = openFileParc.FileName;
            }
            bt_ok.Enabled = (res == System.Windows.Forms.DialogResult.OK);
        }

        private void Combo_energy_TextUpdate(object sender, EventArgs e)
        {
            Produit cur_Prod = lproduit.Find(p => p.key_prod == combo_energy.Text);
            if (cur_Prod != null)
                StatusLabel1.Text = cur_Prod.lib_prod;
            else
                StatusLabel1.Text = "...";
        }

        private void F_vehi_FormClosing(object sender, FormClosingEventArgs e)
        {

            DataTable tb_vehi_change = tb_vehi.GetChanges();

            if (tb_vehi_change != null || hasChanged)
            {
                var result = MessageBox.Show("Voulez-vous valider les madifications apportées ?", "Modifications en cours",
                    MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

                if (result == DialogResult.Yes) 
                {
                    SaveData();
                }
                if (result == DialogResult.Cancel)
                {
                    e.Cancel = true;
                    return;
                }
            }

            e.Cancel = false;
        }





        private void Import_tdf()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            try
            {
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(textNomFile.Text, misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Vérifier que le fichier n'est pas ouvert et est un fichier Excel correct\r\n{ex.Message}", "Ouverture de " + textNomFile.Text + " impossible", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            uint[] indexCol = { 1, 2, 6, 8 };
            string categ = "A";
            int indiceVide = 0;

            for (uint row = 2; row < 800 && indiceVide<10; row++)
            {
                string model = "";
                string immat = "";
                string bandeau = "";
                string cmp = "";
                string famille = "";
                string pilote = "";
                bool ligneVide = true;
                

                int vide = 0;
                for (int colNdx = 0; colNdx < indexCol.Length; colNdx++)
                {
                    uint col = indexCol[colNdx];
                    var varex = xlWorkSheet.Cells[row, col].Value;
                    if (varex != null)
                    {
                        ligneVide = false;
                        string b = "";
                        string mytype = varex.GetType().ToString();
                        switch (mytype)
                        {
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Double":
                                b = ((Int32)varex).ToString();
                                break;

                            default:
                                b = (string)varex;
                                break;
                        }

                        int p;
                        if (col == 12 && (p = b.IndexOf('\n')) != -1)
                            b = b.Substring(0, p);
                        b = b.Replace('\n', ' ');
                        while (b.Contains("  "))
                            b = b.Replace("  ", " ");
                        switch (col)
                        {
                            case 1:
                                bandeau = b;
                                break;
                            case 2:
                                model = b;
                                break;

                            case 6:
                                famille = b;
                                break;
                            case 8:
                                immat = b;
                                break;
                        }
                    }
                }

                if (ligneVide)
                {
                    try
                    {
                        string m = (string)(xlWorkSheet.Cells[row, 4].Value);
                        if (m != null && m.Contains("MOTOS"))
                        {
                            categ = "M";
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                    indiceVide++;
                }

                if (bandeau != "")
                {
                    vide = 0;
                    DataSet1.tb_vehiRow myNewRow = null;
                    string key = "999999";
                    bool newrow = false;
                    try
                    {
                        if (!checkZero.Checked)
                        {
                            myNewRow = FindOnekey(bandeau);
                            if (myNewRow != null)
                            {
                                key = myNewRow["key_vehi"].ToString();
                            }
                        }
                        if (myNewRow == null)
                        {
                            key = FindNewkey();
                            myNewRow = tb_vehi.Newtb_vehiRow();
                            newrow = true;
                        }
                        myNewRow["key_vehi"] = key;
                        myNewRow["ope_vehi"] = Program.key_ope;
                        myNewRow["num_vehi"] = bandeau;
                        myNewRow["cmpnum_vehi"] = cmp;
                        myNewRow["immat_vehi"] = immat;
                        myNewRow["type_vehi"] = model;
                        myNewRow["famille_vehi"] = famille;
                        myNewRow["categ_vehi"] = categ;
                        myNewRow["cond_vehi"] = pilote;
                        myNewRow["sort1_vehi"] = string.Format("{0}{1:00000}{2}", myNewRow["categ_vehi"], myNewRow["num_vehi"], myNewRow["cmpnum_vehi"]);
                        myNewRow["carbu_vehi"] = categ == "M" ? "SP98" : "DIESEL";
                        if (newrow)
                            tb_vehi.Addtb_vehiRow(myNewRow);
                        StatusLabel1.Text = string.Format("{0}{1:00000}{2}-{3}-{4}", myNewRow["categ_vehi"], myNewRow["num_vehi"], myNewRow["cmpnum_vehi"], myNewRow["cond_vehi"], myNewRow["immat_vehi"]);
                        Application.DoEvents();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(string.Format("{3}\r\nPb. à insertion de {0} {1} {2}", bandeau, pilote, model, ex.Message));
                    }
                }
                else
                    vide++;
                if (vide > 10)
                    break;
            }

            xlWorkBook?.Close();
            xlApp?.Quit();

            StatusLabel1.Text = "F I N I .................";
            MessageBox.Show($"Importation de {textNomFile.Text} terminée.", "Importation",MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        private void Init_list_famille()
        {
            XDocument xRoot = XDocument.Load(Program.folder + @"\param\famille.xml");
            var data = from item in xRoot.Descendants("tb_famille")
                       orderby item.Element("key_fam").Value
                       select new Famille()
                       {
                           key_fam = item.Element("key_fam").Value
                       };
            lfamille = data.ToList();
            combo_famille.Items.Clear();
            combo_famille.DisplayMember = "key_fam";
            foreach (Famille f in lfamille)
                combo_famille.Items.Add(f);
        }

        private void Init_list_produit()
        {
            XDocument xRoot = XDocument.Load(Program.folder + @"\param\produit.xml");
            var data = from item in xRoot.Descendants("tb_prod")
                       orderby item.Element("type_prod").Value, item.Element("lib_prod").Value
                       select new Produit()
                       {
                           key_prod = item.Element("key_prod").Value,
                           lib_prod = item.Element("lib_prod").Value,
                           type_prod = item.Element("type_prod").Value
                       };
            lproduit = data.ToList();
            lcarbu = lproduit.Where(p => p.type_prod == "Carburant").ToList();
            combo_energy.Items.Clear();
            combo_energy.DisplayMember = "key_prod";
            foreach (Produit p in lcarbu)
                combo_energy.Items.Add(p);
        }

        private void SaveData()
        {
            if (File.Exists(Program.subfolder + @"\vehicule.json"))
                File.Copy(Program.subfolder + @"\vehicule.json", Program.subfolder + @"\vehicule.back.json", true);

            if (DbUtil.SaveToJson<DataSet1.tb_vehiDataTable>(Program.subfolder + @"\vehicule.json", tb_vehi))
            {
                tb_vehi.AcceptChanges();
                hasChanged = false;
            }
        }

        private void Tb_allValid_Click(object sender, EventArgs e)
        {
            hasChanged = true;
            this.Close();
        }

        private void Vehi_Load(object sender, EventArgs e)
        {
            if (!Directory.Exists(Program.subfolder))
                Directory.CreateDirectory(Program.subfolder);
            else
            {
                if (File.Exists(Program.subfolder + @"\vehicule.json"))
                    tb_vehi = DbUtil.LoadFromJson<DataSet1.tb_vehiDataTable>(Program.subfolder + @"\vehicule.json", tb_vehi);
            }
            tb_vehi.AcceptChanges();
            tbvehiculeBindingSource.DataSource = tb_vehi;

            Turn_txt(false);
            Turn_bt(false);
            if (Program.key_ope != null)
                this.Text = "Importation Véhicules " + Program.nom_ope;

            if (File.Exists(Program.folder + @"\param\famille.xml"))
                Init_list_famille();
            if (File.Exists(Program.folder + @"\param\produit.xml"))
                Init_list_produit();
        }
        #region affichage

        private void Turn_bt(bool act)
        {
            Bt_Abort.Enabled = act;
            bt_Valid.Enabled = act;
            bt_Mod.Enabled = !act;
            bt_Aj.Enabled = !act;
            bt_Sup.Enabled = !act;
            dg_vehi.Enabled = !act;
        }

        private void Turn_txt(bool act)
        {
            text_immat_vehi.Enabled = act;
            text_num_vehi.Enabled = act;
            text_cmpnum_vehi.Enabled = act;
            text_type_vehi.Enabled = act;
            text_kmdep_vehi.Enabled = act;
            text_kmarr_vehi.Enabled = act;
            text_cond_vehi.Enabled = act;
            combo_energy.Enabled = act;
            combo_famille.Enabled = act;
            text_sort_vehi.Enabled = act;
        }
        #endregion affichage

        #region Boutons

        private void Bt_Abort_Click(object sender, EventArgs e)
        {
            tb_vehi.RejectChanges();
            Turn_txt(false);
            Turn_bt(false);
        }

        private void Bt_Aj_Click(object sender, EventArgs e)
        {
            Turn_txt(true);
            Turn_bt(true);
            string key = FindNewkey();
            DataSet1.tb_vehiRow myNewRow = tb_vehi.Newtb_vehiRow();
            myNewRow["key_vehi"] = key;
            myNewRow["ope_vehi"] = Program.nom_ope;
            tb_vehi.Addtb_vehiRow(myNewRow);
            tbvehiculeBindingSource.MoveFirst();
            DataRowView myRow = tbvehiculeBindingSource.Current as DataRowView;
            while (myRow.Row["key_vehi"].ToString() != key)
            {
                tbvehiculeBindingSource.MoveNext();
                myRow = tbvehiculeBindingSource.Current as DataRowView;
            }
        }

        private void Bt_Mod_Click(object sender, EventArgs e)
        {
            Turn_bt(true);
            Turn_txt(true);
        }
        private void Bt_Sup_Click(object sender, EventArgs e)
        {
            DataRowView myRow = tbvehiculeBindingSource.Current as DataRowView;
            if (myRow == null) return;
            string msg = string.Format("Vehicule {0}\nEtes-vous certain de vouloir supprimer \n{1}", myRow.Row["num_vehi"].ToString(), myRow.Row["immat_vehi"].ToString());
            string caption = "Suppression définitive";
            if (MessageBox.Show(msg, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
            {
                DataSet1.tb_vehiRow vehiRow = tb_vehi.FindBykey_vehi(myRow.Row["key_vehi"].ToString());
                if (vehiRow != null)
                    tb_vehi.Removetb_vehiRow(vehiRow);
                hasChanged = true;
            }
        }

        private void Bt_Valid_Click(object sender, EventArgs e)
        {
            tb_vehi.AcceptChanges();
            Turn_txt(false);
            Turn_bt(false);
            hasChanged = true;
        }
        #endregion Boutons

        #region fonctions

        private string FindNewkey()
        {
            Random rand1 = new Random();
            string key = rand1.Next(99999).ToString();
            while (tb_vehi.FindBykey_vehi(key) != null)
            {
                key = rand1.Next(99999).ToString();
            }
            return key;
        }

        private DataSet1.tb_vehiRow FindOnekey(string bandeau)
        {
            if (Int32.TryParse(bandeau, out int bandoNum))
            {
                DataSet1.tb_vehiRow onevevi = tb_vehi.FirstOrDefault(v => v.num_vehi == bandoNum);
                return onevevi;
            }
            else
            {
                return null;
            }

        }
        #endregion fonctions
    }
}

/*
         private void Import_parc()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            try
            {
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(textNomFile.Text, misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Vérifier que le fichier n'est pas ouvert et est un fichier Excel correct {ex.Message}", "Ouverture de " + textNomFile.Text + " impossible", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            uint[] indexCol = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 15 };
            for (uint row = 3; row < 800; row++)
            {
                string model = "";
                string immat = "";
                string bandeau = "";
                string famille = "";
                string pilote = "";
                string carbu = "D";
                string cmp = "";
                string categ = "A";

                int vide = 0;
                for (int colNdx = 0; colNdx < indexCol.Length; colNdx++)
                {
                    uint col = indexCol[colNdx];
                    var varex = xlWorkSheet.Cells[row, col].Value;
                    if (varex != null)
                    {
                        string b = "";
                        string mytype = varex.GetType().ToString();
                        switch (mytype)
                        {
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Double":
                                b = ((Int32)varex).ToString();
                                break;

                            default:
                                b = (string)varex;
                                break;
                        }

                        while (b.Contains("  "))
                            b = b.Replace("  ", " ");
                        switch (col)
                        {
                            case 1:
                                bandeau = b;
                                break;

                            case 2:
                                famille = b;
                                break;

                            case 3:
                                pilote = b;
                                break;

                            case 4:
                                pilote = new StringBuilder(pilote).Append(" ").Append(b).ToString();
                                break;

                            case 5:
                                carbu = b;
                                break;

                            case 6:
                                model = b;
                                break;

                            case 7:
                                immat = new StringBuilder(immat).Append(b).Append(" ").ToString();
                                break;

                            case 8:
                                immat = new StringBuilder(immat).Append(b).Append(" ").ToString();
                                break;

                            case 9:
                                immat = new StringBuilder(immat).Append(b).Append(" ").ToString();
                                break;

                            case 15:
                                categ = b.ToUpper() == "M" ? "M" : "A";
                                break;
                        }
                    }
                }

                if (bandeau != "")
                {
                    vide = 0;
                    DataSet1.tb_vehiRow myNewRow = null;
                    string key = "999999";
                    bool newrow = false;
                    try
                    {
                        if (!checkZero.Checked)
                        {

                            myNewRow = FindOnekey(bandeau);
                            if (myNewRow == null)
                            {
                                continue;
                            }
                            else
                            {
                                key = myNewRow["key_vehi"].ToString();
                            }
                        }
                        if (myNewRow == null)
                        {
                            key = FindNewkey();
                            myNewRow = tb_vehi.Newtb_vehiRow();
                            newrow = true;
                        }
                        myNewRow["key_vehi"] = key;
                        myNewRow["ope_vehi"] = Program.key_ope;
                        myNewRow["num_vehi"] = bandeau;
                        myNewRow["cmpnum_vehi"] = cmp;
                        myNewRow["immat_vehi"] = immat;
                        myNewRow["type_vehi"] = model;
                        myNewRow["famille_vehi"] = famille;
                        myNewRow["categ_vehi"] = categ;
                        myNewRow["cond_vehi"] = pilote;
                        myNewRow["sort1_vehi"] = string.Format("{0}{1:00000}{2}", myNewRow["categ_vehi"], myNewRow["num_vehi"], myNewRow["cmpnum_vehi"]);
                        switch (carbu)
                        {
                            case "D":
                                carbu = "DIESEL";
                                break;

                            case "E":
                                carbu = "SP98";
                                break;

                            default:
                                carbu = "?";
                                break;
                        }

                        if (carbu == "?")
                        {
                            if (categ == "M")
                            {
                                myNewRow["carbu_vehi"] = "SP98";
                            }
                            else
                            {
                                myNewRow["carbu_vehi"] = "DIESEL";
                            }
                        }
                        else
                        {
                            myNewRow["carbu_vehi"] = carbu;
                        }

                        if (newrow)
                            tb_vehi.Addtb_vehiRow(myNewRow);
                        StatusLabel1.Text = string.Format("{0}{1:00000}{2}-{3}-{4}", myNewRow["categ_vehi"], myNewRow["num_vehi"], myNewRow["cmpnum_vehi"], myNewRow["cond_vehi"], myNewRow["immat_vehi"]);
                        Application.DoEvents();
                        Thread.Sleep(5);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(string.Format("{3}\r\nPb. à insertion de {0} {1} {2}", bandeau, pilote, model, ex.Message));
                    }
                }
                else
                { vide++; }
                if (vide > 10)
                {
                    break;
                }
            }

            xlApp?.Quit();

            StatusLabel1.Text = "F I N I .................";
        }

        private void Import_PN()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            try
            {
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(textNomFile.Text, misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Vérifier que le fichier n'est pas ouvert et est un fichier Excel correct \r\n {ex.Message}", "Ouverture de " + textNomFile.Text + " impossible", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            uint[] indexCol = { 1, 3, 4, 6, 7, 8, 9 };
            string categ = "A";
            for (uint row = 2; row < 200; row++)
            {
                string model = "";
                string immat = "";
                string bandeau = "";
                string carbu = "";
                string famille = "";
                string pilote = "";

                int vide = 0;
                for (int colNdx = 0; colNdx < indexCol.Length; colNdx++)
                {
                    uint col = indexCol[colNdx];
                    var varex = xlWorkSheet.Cells[row, col].Value;
                    if (varex != null)
                    {
                        string b = "";
                        string mytype = varex.GetType().ToString();
                        switch (mytype)
                        {
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Double":
                                b = ((Int32)varex).ToString();
                                break;

                            default:
                                b = (string)varex;
                                break;
                        }

                        b = b.Replace('\n', ' ');
                        while (b.Contains("  "))
                            b = b.Replace("  ", " ");
                        switch (col)
                        {
                            case 1:
                                bandeau = b;
                                break;

                            case 3:
                                famille = b;
                                break;

                            case 4:
                                pilote = b;
                                break;

                            case 6:
                                model = b;
                                break;

                            case 7:
                                carbu = b;
                                break;

                            case 8:
                                immat = b;
                                break;

                            case 9:
                                categ = b;
                                break;
                        }
                    }
                }

                if (bandeau != "")
                {
                    vide = 0;
                    DataSet1.tb_vehiRow myNewRow = null;
                    string key = "999999";
                    bool newrow = false;
                    try
                    {
                        if (!checkZero.Checked)
                        {
                            myNewRow = FindOnekey(bandeau);
                            if (myNewRow != null)
                            {
                                key = myNewRow["key_vehi"].ToString();
                            }
                        }
                        if (myNewRow == null)
                        {
                            key = FindNewkey();
                            myNewRow = tb_vehi.Newtb_vehiRow();
                            newrow = true;
                        }
                        myNewRow["key_vehi"] = key;
                        myNewRow["ope_vehi"] = Program.key_ope;
                        myNewRow["num_vehi"] = bandeau;
                        myNewRow["cmpnum_vehi"] = "";
                        myNewRow["immat_vehi"] = immat;
                        myNewRow["type_vehi"] = model;
                        myNewRow["famille_vehi"] = famille;
                        myNewRow["categ_vehi"] = categ;
                        myNewRow["cond_vehi"] = pilote;
                        myNewRow["sort1_vehi"] = string.Format("{0}{1:00000}{2}", myNewRow["categ_vehi"], myNewRow["num_vehi"], myNewRow["cmpnum_vehi"]);
                        myNewRow["carbu_vehi"] = carbu == "E" ? "SP98" : "DIESEL";
                        if (newrow)
                            tb_vehi.Addtb_vehiRow(myNewRow);
                        StatusLabel1.Text = string.Format("{0}{1:00000}{2}-{3}-{4}", myNewRow["categ_vehi"], myNewRow["num_vehi"], myNewRow["cmpnum_vehi"], myNewRow["cond_vehi"], myNewRow["immat_vehi"]);
                        Application.DoEvents();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(string.Format("{3}\r\nPb. à insertion de {0} {1} {2}", bandeau, pilote, model, ex.Message));
                    }
                }
                else
                    vide++;
                if (vide > 10)
                    break;
            }

            xlApp?.Quit();

            StatusLabel1.Text = "F I N I .................";
        }
 */