Fuel_ASo/Fuel_ASO/f_impvehi.cs

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


namespace Fuel01
{
    public partial class f_impvehi : Form
    {
        DataSet1.tb_vehiDataTable tb_vehi = new DataSet1.tb_vehiDataTable();
        private bool hasChanged = false;
        string mode = "";
        List<produit> lproduit = new List<produit>();
        List<produit> lcarbu = new List<produit>();
        List<famille> lfamille = new List<famille>();

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

        private void vehi_Load(object sender, EventArgs e)
        {

            Program.subfolder = Program.folder + @"\" + Program.key_ope;

            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();


        }

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

        #region affichage
        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;

        }

        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;
        }
        #endregion

        #region Boutons


        private void bt_Mod_Click(object sender, EventArgs e)
        {
            turn_bt(true);
            turn_txt(true);
        }

        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_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;

        }

        private void Bt_Abort_Click(object sender, EventArgs e)
        {
            tb_vehi.RejectChanges();
            turn_txt(false);
            turn_bt(false);
        }
        #endregion

        #region fonctions

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

        private DataSet1.tb_vehiRow findOnekey(int bandeau, string cmp, string typevehi)
        {
            DataSet1.tb_vehiRow onevevi = tb_vehi.FirstOrDefault(v => v.num_vehi == bandeau);
            //while (tb_vehi.FindBykey_vehi(key) != null)
            //{
            //    //MessageBox.Show(key);
            //    key = rand1.Next(99999).ToString();
            //}
            //return key;
            return onevevi;
        }
        #endregion

        private void f_vehi_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (hasChanged) saveData();
            DataTable tb_vehi_change = tb_vehi.GetChanges();
            tb_vehi_change = null;
            if (tb_vehi_change != null) MessageBox.Show("Veuillez Valider ou Abandonner", "Modifications en cours");
            e.Cancel = (tb_vehi_change != null);
        }

        private void saveData()
        {
            if (File.Exists(Program.subfolder + @"\vehicule.json"))
                File.Copy(Program.subfolder + @"\vehicule.json", Program.subfolder + @"\vehicule.back.json", true);
            
            DbUtil.SaveToJson<DataSet1.tb_vehiDataTable>(Program.subfolder + @"\statvehiculeion.json", tb_vehi);
        }

        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 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 bt_ok_Click(object sender, EventArgs e)
        {
            if (checkZero.Checked)
                tb_vehi.Clear();
            if (mode == "tdf")
                import_tdf();
            else
                if (mode == "parc")
                import_parc();
            else
                import_PN(); ;
        }

        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", "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 = "";
                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)
                    {
                        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;
                                ligneVide = false;
                                break;
                            case 3:
                                famille = b;
                                ligneVide = false;
                                break;
                            case 4:
                                pilote = b;
                                ligneVide = false;
                                break;
                            case 6:
                                model = b;
                                break;
                            case 7:
                                carbu = b;
                                ligneVide = false;
                                break;
                            case 8:
                                immat = b;
                                ligneVide = false;
                                break;
                            case 9:
                                categ = b;
                                ligneVide = false;
                                break;
                        }

                    }
                }

                if (bandeau != "")
                {
                    vide = 0;
                    DataSet1.tb_vehiRow myNewRow = null;
                    string key = "999999";
                    bool newrow = false;
                    try
                    {
                        if (!checkZero.Checked)
                        {
                            myNewRow = findOnekey(Convert.ToInt32(bandeau), "", categ);
                            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;

            }
            if (xlApp != null)
                xlApp.Quit();

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

        }


        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", "Ouverture de " + textNomFile.Text + " impossible", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            //uint[] indexCol = { 1, 3, 4, 5, 6, 7, 10, 11 };
            uint[] indexCol = { 2, 4, 5, 6, 7, 8, 11, 12 };
            string categ = "A";
            for (uint row = 2; row < 800; 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)
                    {
                        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 2:
                                model = b;
                                ligneVide = false;
                                break;
                            case 4:
                                immat = immat + b + " ";
                                break;
                            case 5:
                                ligneVide = false;
                                immat = immat + b + " ";
                                break;
                            case 6:
                                ligneVide = false;
                                immat = immat + b + " ";
                                break;
                            case 7:
                                bandeau = b;
                                ligneVide = false;
                                break;
                            case 8:
                                cmp = b;
                                ligneVide = false;

                                break;
                            case 11:
                                famille = b;
                                ligneVide = false;
                                break;
                            case 12:
                                pilote = b;
                                ligneVide = false;
                                break;
                        }

                    }
                }

                if (ligneVide)
                {
                    try
                    {
                        string m = (string)(xlWorkSheet.Cells[row, 4].Value);
                        if (m != null)
                            if (m.Contains("MOTOS"))
                                categ = "M";
                    }
                    catch (Exception ex) {; }
                }
                if (bandeau != "")
                {
                    vide = 0;
                    DataSet1.tb_vehiRow myNewRow = null;
                    string key = "999999";
                    bool newrow = false;
                    try
                    {
                        if (!checkZero.Checked)
                        {
                            myNewRow = findOnekey(Convert.ToInt32(bandeau), cmp, categ);
                            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;

            }
            if (xlApp != null)
                xlApp.Quit();

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

        }


        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", "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";

                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)
                    {
                        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;
                        while (b.Contains("  "))
                            b = b.Replace("  ", " ");
                        switch (col)
                        {
                            case 1:
                                bandeau = b;
                                ligneVide = false;
                                break;
                            case 2:
                                famille = b;
                                ligneVide = false;
                                break;
                            case 3:
                                pilote = b;
                                ligneVide = false;
                                break;
                            case 4:
                                pilote = pilote + " " + b;
                                ligneVide = false;
                                break;
                            case 5:
                                carbu = b;
                                ligneVide = false;
                                break;
                            case 6:
                                model = b;
                                ligneVide = false;
                                break;
                            case 7:
                                immat = immat + b + " ";
                                break;
                            case 8:
                                ligneVide = false;
                                immat = immat + b + " ";
                                break;
                            case 9:
                                ligneVide = false;
                                immat = immat + b + " ";
                                break;
                            case 15:
                                ligneVide = false;
                                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(Convert.ToInt32(bandeau), cmp, categ);
                            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"]);
                        switch (carbu)
                        {
                            case "D":
                                carbu = "DIESEL";
                                break;
                            case "E":
                                carbu = "SP98";
                                break;
                            default:
                                carbu = "?";
                                break;
                        }
                        myNewRow["carbu_vehi"] = carbu == "?" ? (categ == "M" ? "SP98" : "DIESEL") : 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();
                    }
                    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;

            }
            if (xlApp != null)
                xlApp.Quit();

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

        }

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

    }
}