Fuel_ASo/Fuel_ASO/f_facture.cs

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

namespace Fuel01
{
    public partial class f_facture : Form
    {

        static ProdEqualityComparer prodEqC = new ProdEqualityComparer();
        Dictionary<IdProd, VenteTot> lvente_tot = new Dictionary<IdProd, VenteTot>(prodEqC);
        Dictionary<decimal, VenteTva> lvente_tva = new Dictionary<decimal, VenteTva>();
        DataSet1.tb_venteDataTable tb_vente = new DataSet1.tb_venteDataTable();
        DataSet1.tb_stationDataTable tb_station = new DataSet1.tb_stationDataTable();

        DataSet1.tb_stationRow current_sta = null;
        string Id_sta { get; set; }

        List<Produit> lproduit = new List<Produit>();


        public f_facture(string _id_sta)
        {
            Id_sta = _id_sta;
            InitializeComponent();

            if (File.Exists(Program.subfolder + @"\station.json"))
                tb_station = DbUtil.LoadFromJson<DataSet1.tb_stationDataTable>(Program.subfolder + @"\station.json", tb_station);

            if (File.Exists(Program.subfolder + @"\vente.json"))
                tb_vente = DbUtil.LoadFromJson<DataSet1.tb_venteDataTable>(Program.subfolder + @"\vente.json", tb_vente);

            tb_vente.AcceptChanges();

        }

        private void f_facture_Load(object sender, EventArgs e)
        {
            tbventeBindingSource.DataSource = tb_vente;
            tbventeBindingSource.Filter = " station_vt='" + Id_sta + "'";

            current_sta = tb_station.FindBykey_sta(Id_sta);

            init_list_produit();
        }

        private void bt_fact_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            makeChange();
            lvente_tot.Clear();
            lvente_tva.Clear();

            string fileName = "";
            Excel.Application xlApp = null;
            try
            {
                if (!File.Exists(Program.subfolder + @"\Facture_Carbu.xlsx"))
                {
                    MessageBox.Show("Fichier de base " + Program.subfolder + @"\Facture_Carbu.xlsx non trouvé!!!", "Erreur");
                    return;
                }
                fileName = Program.subfolder + @"\Facture_" + Id_sta + ".xlsx";
                File.Copy(Program.subfolder + @"\Facture_Carbu.xlsx", fileName, true);


                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                try
                {
                    xlApp = new Excel.Application();
                    xlWorkBook = xlApp.Workbooks.Open(fileName, 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 " + fileName + " impossible", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    if (xlApp != null)
                        xlApp.Quit();
                    this.Cursor = Cursors.Default;
                    return;
                }

                xlWorkSheet.Cells[2, 2].Value = makeItCLean(xlWorkSheet.Cells[2, 2].Value, current_sta["nom_sta"].ToString());
                xlWorkSheet.Cells[3, 2].Value = makeItCLean(xlWorkSheet.Cells[3, 2].Value, current_sta["adr1_sta"].ToString());
                xlWorkSheet.Cells[4, 2].Value = makeItCLean(xlWorkSheet.Cells[4, 2].Value, current_sta["adr2_sta"].ToString());
                xlWorkSheet.Cells[5, 2].Value = makeItCLean(xlWorkSheet.Cells[5, 2].Value, current_sta["cp_sta"].ToString() + "-" + current_sta["ville_sta"].ToString());
                xlWorkSheet.Cells[6, 2].Value = makeItCLean(xlWorkSheet.Cells[6, 2].Value, current_sta["pays_sta"].ToString());
                xlWorkSheet.Cells[8, 2].Value = makeItCLean(xlWorkSheet.Cells[8, 2].Value, current_sta["tel_sta"].ToString());
                xlWorkSheet.Cells[9, 2].Value = makeItCLean(xlWorkSheet.Cells[9, 2].Value, current_sta["tva_sta"].ToString());

                xlWorkSheet.Cells[10, 4].Value = makeItCLean(xlWorkSheet.Cells[10, 4].Value, current_sta["ville_sta"].ToString());
                xlWorkSheet.Cells[11, 4].Value = makeItCLean(xlWorkSheet.Cells[11, 4].Value, DateTime.Now.ToString("Le : dd/MM/yyyy"));

                xlWorkSheet.Cells[14, 2].Value = makeItCLean(xlWorkSheet.Cells[14, 2].Value, Program.nom_ope + " du " + current_sta.date_sta.ToString("dd/MM/yy"));

                xlWorkSheet.Cells[48, 2].Value = makeItCLean(xlWorkSheet.Cells[48, 2].Value, current_sta["divers_sta"].ToString());


                Dictionary<string, VenteTot> lvente_fact = new Dictionary<string, VenteTot>();
                lvente_fact.Add("A - DIESEL", new VenteTot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });
                lvente_fact.Add("B - AUTRES CARBURANTS", new VenteTot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });

                lvente_tot.Clear();

                try
                {


                    foreach (DataSet1.tb_venteRow myrow in tb_vente.Where(p => p.station_vt == Id_sta))
                    {
                        VenteTot myFact = null;
                        Produit pdt = lproduit.Find(xx => xx.key_prod == myrow.prod_vt);
                        string sort = "Z";
                        if (pdt != null) // Produit référencé
                        {
                            var isDIesel = myrow.prod_vt.ToUpper().Contains("DIESEL");
                            if (isDIesel)
                            {
                                sort = pdt.type_prod == "Carburant" ? ("A - DIESEL") : "C - PRODUITS DIVERS";
                            }
                            else
                            {
                                sort = pdt.type_prod == "Carburant" ? ("B - AUTRES CARBURANTS") : "C - PRODUITS DIVERS";
                            }
                        }
                        else
                        {
                            sort = "C - PRODUITS DIVERS";
                        }

                        if (!lvente_fact.ContainsKey(sort))
                            lvente_fact.Add(sort, new VenteTot { vente = myrow, total_ttc = 0, total_qtt = 0, total_ht = 0 });

                        myFact = lvente_fact[sort];
                        myFact.total_qtt += myrow.qtt_vt;
                        myFact.total_ht += myrow.ht_vt;
                        myFact.total_ttc += myrow.ttc_vt;

                    }


                    int lig = 21;
                    decimal totgal = 0;
                    foreach (KeyValuePair<string, VenteTot> myVente in lvente_fact)
                    {

                        Console.WriteLine(string.Format(" Type :{0}  ", myVente.Key));
                        if (myVente.Value.total_ht > 0)
                        {
                            xlWorkSheet.Cells[lig, 2].Value = myVente.Key.Replace("A - ", "").Replace("B - ", "").Replace("C - ", "").Replace("D - ", "");
                            xlWorkSheet.Cells[lig, 6].Value = myVente.Value.total_ttc;

                            totgal += myVente.Value.total_ttc;
                            lig += 1;
                        }
                    }

                    xlWorkSheet.Cells[41, 6].Value = totgal;
                    xlWorkBook.Save();
                    xlWorkBook.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Erreur à la création de " + fileName);
                }


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


                ProcessStartInfo notepadStartInfo = new ProcessStartInfo(fileName);
                Process.Start(notepadStartInfo);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Abandon !!!! \r\n " + ex.Message);
            }
            this.Cursor = Cursors.Default;
        }


        private static string makeItCLean(string strExcel, string data)
        {
            string ret = data;
            if (strExcel.Length > 0 && strExcel.Substring(0, 1) == "#")
            {
                ret = strExcel.Substring(1) + data;
            }
            return ret;
        }
        private void f_facture_FormClosing(object sender, FormClosingEventArgs e)
        {
            e.Cancel = makeChange();
        }


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

        private void bt_simul_Click(object sender, EventArgs e)
        {
            Dictionary<string, VenteTot> lvente_fact = new Dictionary<string, VenteTot>();
            lvente_fact.Add("A - DIESEL", new VenteTot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });
            lvente_fact.Add("B - AUTRES CARBURANTS", new VenteTot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });

            List<Simul> lsimul = new List<Simul>();
            lvente_tot.Clear();
            lvente_tva.Clear();

            try
            {

                foreach (DataSet1.tb_venteRow myrow in tb_vente.Where(p => p.station_vt == Id_sta))
                {
                    VenteTot myTot = null;
                    VenteTot myFact = null;
                    Produit pdt = lproduit.Find(xx => xx.key_prod == myrow.prod_vt);
                    string lib_produit = "";
                    string sort = "Z";
                    if (pdt != null) // Produit référencé
                    {
                        var isDIesel = myrow.prod_vt.ToUpper().Contains("DIESEL");
                        if (isDIesel)
                        {
                            sort = pdt.type_prod == "Carburant" ? ("A - DIESEL") : "C - PRODUITS DIVERS";
                        }
                        else
                        {
                            sort = pdt.type_prod == "Carburant" ? ("B - AUTRES CARBURANTS") : "C - PRODUITS DIVERS";
                        }
                        lib_produit = pdt.lib_prod;
                    }
                    else
                        sort = "C - PRODUITS DIVERS";
                    IdProd my_id = new IdProd { code_pro = myrow.prod_vt, ttc_pro = myrow.pu_vt, tva_pro = myrow.tva_vt, lib_pro = lib_produit, type_pro = sort };

                    if (!lvente_fact.ContainsKey(sort))
                        lvente_fact.Add(sort, new VenteTot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });
                    if (!lvente_tot.ContainsKey(my_id))
                        lvente_tot.Add(my_id, new VenteTot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });

                    myTot = lvente_tot[my_id];
                    myTot.total_qtt += myrow.qtt_vt;
                    myTot.total_ht += myrow.ht_vt;
                    myTot.total_ttc += myrow.ttc_vt;

                    myFact = lvente_fact[sort];
                    myFact.total_qtt += myrow.qtt_vt;
                    myFact.total_ht += myrow.ht_vt;
                    myFact.total_ttc += myrow.ttc_vt;

                    VenteTva myTva = null;
                    if (myrow.tva_vt != 0)
                    {
                        if (!lvente_tva.ContainsKey(myrow.tva_vt))
                            lvente_tva.Add(myrow.tva_vt, new VenteTva { taux_tva = myrow.tva_vt, base_tva = 0, mnt_ttc = 0, mnt_tva = 0 });

                        myTva = lvente_tva[myrow.tva_vt];
                        myTva.base_tva += myrow.ht_vt;
                        myTva.mnt_ttc += myrow.ttc_vt;
                        myTva.mnt_tva = myTva.mnt_ttc - myTva.base_tva;
                        Console.WriteLine(string.Format(" taux:{0} base:{1}  tva:{2}  ttc:{3} ", myrow.tva_vt, myTva.base_tva, myTva.mnt_tva, myTva.mnt_ttc));
                    }

                }


                int nbprod = 0;
                decimal totgal = 0;

                foreach (KeyValuePair<string, VenteTot> myVente in lvente_fact)
                {

                    Console.WriteLine(string.Format(" Type :{0}  ", myVente.Key));
                    Simul sm = new Simul();
                    sm.libel = myVente.Key;
                    sm.qtt = 0;
                    sm.pu = 0;
                    sm.prix = myVente.Value.total_ttc;
                    totgal += sm.prix;
                    nbprod += 1;
                    lsimul.Add(sm);
                }
                lsimul.Add(new Simul { libel = "Total", prix = totgal, qtt = null, pu = null });

                dg_simul.DataSource = lsimul;

                dg_simul.Columns[0].Width = 300;

                DataGridViewCellStyle N2 = new DataGridViewCellStyle();
                N2.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleRight;
                N2.Format = "N2";
                N2.NullValue = "";

                DataGridViewCellStyle N3 = new DataGridViewCellStyle();
                N3.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleRight;
                N3.Format = "N3";
                N3.NullValue = "";

                dg_simul.Columns[1].DefaultCellStyle = N2;
                dg_simul.Columns[2].DefaultCellStyle = N3;
                dg_simul.Columns[3].DefaultCellStyle = N2;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Erreur au calcul de la facture");
            }
        }

        private bool makeChange()
        {
            this.vt_qtt.DataBindings["Text"].BindingManagerBase.EndCurrentEdit();

            bool ret = false;
            DataTable tb_vente_change = tb_vente.GetChanges();

            while (tb_vente_change != null)
            {
                if (MessageBox.Show("Voulez-vous Valider Oui ou Non les modifications apportées ?", "Modifications en cours!!!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation) == DialogResult.Yes)
                {
                    tb_vente.AcceptChanges();
                    tb_vente.WriteXml(Program.subfolder + @"\vente.xml", XmlWriteMode.WriteSchema);
                    tb_vente_change = tb_vente.GetChanges();
                }
                else
                {
                    tb_vente.RejectChanges();
                    tb_vente_change = tb_vente.GetChanges();
                }
            }
            ret = (tb_vente_change != null);
            return ret;
        }

        private void vt_qtt_TextChanged(object sender, EventArgs e)
        {
            //this.vt_ttc.DataBindings["Text"].BindingManagerBase.EndCurrentEdit();
            if (vt_ttc.Text == "")
            {
                vt_ttc.Text = "0";
                vt_qtt.SelectAll();
            }
            float ttc = 0;
            try
            {
                ttc = (float)Convert.ToDouble(this.vt_ttc.Text);
            }
            catch (Exception ex)
            {
                return; 
            }
            float qtt = 0;
            float puttc = (float)Convert.ToDouble(this.vt_pu.Text);
            float tva = 0;

            float ht = 0;

            qtt = ttc / puttc;
            ht = (qtt * puttc) / (1 + (tva / 100));

            vt_qtt.Text = String.Format("{0:0.00}", qtt);
            vt_ht.Text = String.Format("{0:0.00}", ht);

            vt_ttc.Refresh();
            vt_ht.Refresh();

            //this.vt_qtt.DataBindings["Text"].BindingManagerBase.EndCurrentEdit();
            //this.vt_ht.DataBindings["Text"].BindingManagerBase.EndCurrentEdit();
        }

        private void vt_qtt_Enter(object sender, EventArgs e)
        {
            this.vt_ttc.TextChanged += new System.EventHandler(this.vt_qtt_TextChanged);

        }

        private void vt_qtt_Leave(object sender, EventArgs e)
        {
            if (vt_qtt.Text == "") vt_qtt.Text = "0";
            this.vt_ttc.TextChanged -= new System.EventHandler(this.vt_qtt_TextChanged);

        }
    }

    public class Simul
    {
        public string libel { get; set; }
        public decimal? qtt { get; set; }
        public decimal? pu { get; set; }
        public decimal prix { get; set; }


    }
}