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<id_prod, vente_tot> lvente_tot = new Dictionary<id_prod, vente_tot>(prodEqC);
        Dictionary<decimal, vente_tva> lvente_tva = new Dictionary<decimal, vente_tva>();
        Dictionary<string, vente_tot> lvente_fact = new Dictionary<string, vente_tot>();
        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 = "";
        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", "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, vente_tot> lvente_fact = new Dictionary<string, vente_tot>();
                lvente_fact.Add("A - DIESEL", new vente_tot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });
                lvente_fact.Add("B - AUTRES CARBURANTS", new vente_tot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });

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

                try
                {


                    foreach (DataSet1.tb_venteRow myrow in tb_vente.Where(p => p.station_vt == id_sta))
                    {
                        vente_tot 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é
                        {
                            sort = pdt.type_prod == "Carburant" ? (myrow.prod_vt.ToUpper().Contains("DIESEL") ? "A - DIESEL" : "B - AUTRES CARBURANTS") : "C - PRODUITS DIVERS";
                            lib_produit = pdt.lib_prod;
                        }
                        else
                            sort = "C - PRODUITS DIVERS";
                        id_prod my_id = new id_prod { 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 vente_tot { 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, vente_tot> 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;
                    lig = 44;

                    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 notepad = Process.Start(notepadStartInfo);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Abandon !!!! \r\n " + ex.Message);
            }
            this.Cursor = Cursors.Default;
        }


        private string makeItCLean(string strExcel, string data)
        {
            string ret = data;
            if (strExcel.Length > 0)
                if (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)
        {

            //makeChange();
            Dictionary<string, vente_tot> lvente_fact = new Dictionary<string, vente_tot>();
            lvente_fact.Add("A - DIESEL", new vente_tot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });
            lvente_fact.Add("B - AUTRES CARBURANTS", new vente_tot { 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))
                {
                    vente_tot myTot = null;
                    vente_tot 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é
                    {
                        sort = pdt.type_prod == "Carburant" ? (myrow.prod_vt.ToUpper().Contains("DIESEL") ? "A - DIESEL" : "B - AUTRES CARBURANTS") : "C - PRODUITS DIVERS";
                        lib_produit = pdt.lib_prod;
                    }
                    else
                        sort = "C - PRODUITS DIVERS";
                    id_prod my_id = new id_prod { 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 vente_tot { vente = null, total_ttc = 0, total_qtt = 0, total_ht = 0 });
                    if (!lvente_tot.ContainsKey(my_id))
                        lvente_tot.Add(my_id, new vente_tot { 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;

                    vente_tva myTva = null;
                    if (myrow.tva_vt != 0)
                    {
                        if (!lvente_tva.ContainsKey(myrow.tva_vt))
                            lvente_tva.Add(myrow.tva_vt, new vente_tva { 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;
                int lig = 21;
                decimal totgal = 0;

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

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

                lig = 44;
                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 qtt = 0;
            float puttc = 0;
            float tva = 0;
            float ttc = 0;
            float ht = 0;
            bool ok = true;
            ok = float.TryParse(vt_ttc.Text, out ttc);
            if (ok) ok = float.TryParse(vt_pu.Text, out puttc);
            if (ok) ok = float.TryParse(vt_tva.Text, out tva);
            if (ok) ok = float.TryParse(vt_ht.Text, out ht);
            if (ok) ok = float.TryParse(vt_ttc.Text, out ttc);
            qtt = ttc / puttc;
            ht = (qtt * puttc) / (1 + (tva / 100));
            float dif = ttc - ht;
            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; }


    }
}