using System.IO;
using System.Linq;
using System.Web.Mvc;
using System.Data;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Web;
using MALDFGASSURANCE.Models;
using System.Threading;
using System;

namespace MALDFGASSURANCE.Areas.ADHESION.Controllers
{
    public class ExcelUploadController : Controller
    {
        private MALDFGEntities db = new MALDFGEntities();
        public ActionResult Index()
        {
          

            return View(db.SIMULATIONFIXEE.ToList());
        }
        public ActionResult ImportExcel()
        {


            return View();
        }
        [ActionName("Importexcel")]
        [HttpPost]
        public ActionResult Importexcel1()
        {

            DataTable dt = null;
            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
                string query = null;
                string connString = "";




                string[] validFileTypes = { ".xls", ".xlsx", ".csv" };

                string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);
                if (!Directory.Exists(path1))
                {
                    Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));
                }
                if (validFileTypes.Contains(extension))
                {
                    if (System.IO.File.Exists(path1))
                    {
                        System.IO.File.Delete(path1);
                    }
                    Request.Files["FileUpload1"].SaveAs(path1);
                    if (extension == ".csv")
                    {
                        dt = Utility.ConvertCSVtoDataTable(path1);
                        ViewBag.Data = dt;
                    }
                    //Connection String to Excel Workbook  
                    else if (extension.Trim() == ".xls")
                    {
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                      dt = Utility.ConvertXSLXtoDataTable(path1, connString);
                        ViewBag.Data = dt;
                    }
                    else if (extension.Trim() == ".xlsx")
                    {
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        dt = Utility.ConvertXSLXtoDataTable(path1, connString);
                        ViewBag.Data = dt;
                    }

                }
                else
                {
                    ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format";

                }

                Session["dataExcel"] = dt;
            }

            return View();

        }

        public ActionResult error()
        {
            return View("error");
        }

        public void ControleSimmulateurCotisation()
        {
            EST_FIXEE FIXEE = new EST_FIXEE();
            ADHERENTSController ADHERENTS = new ADHERENTSController();
            PERIODESController PERIODES = new PERIODESController();
            SIMULATIONFIXEESController SIMULATIONFIXEES = new SIMULATIONFIXEESController();
            
                 bool anomaliRetrouver = false;
                foreach (var item in db.SIMULATIONFIXEE.ToList())
               {
                SIMULATIONFIXEE sIMULATION = new SIMULATIONFIXEE();

                sIMULATION.id = item.id;
                sIMULATION.IDUTILISATEUR = item.IDUTILISATEUR;
                sIMULATION.MONTANT = item.MONTANT;
                sIMULATION.REFERENCE_ADHERENT = item.REFERENCE_ADHERENT;
                sIMULATION.Code ="Verifier";
                sIMULATION.ETAT =1;
                sIMULATION.REFERENCE_ADHERENT= item.REFERENCE_ADHERENT;
                sIMULATION.IDPERIODE = item.IDPERIODE;


                if (string.IsNullOrEmpty( item.REFERENCE_ADHERENT))
                {
                    sIMULATION.Commentaire += "Matricule est null " + Environment.NewLine;
                    anomaliRetrouver = true;
                }
                else
                {
                    if (ADHERENTS.InfosMatriculeAdherent(item.REFERENCE_ADHERENT) == null)
                    {
                        sIMULATION.Commentaire += "Matricule Inexistant " + Environment.NewLine;
                        anomaliRetrouver = true;

                    }
                }

                if (string.IsNullOrEmpty(item.MONTANT))
                {
                    sIMULATION.Commentaire += "Montant est null" + Environment.NewLine;
                    anomaliRetrouver = true;
                }
                else
                {
                    int montant = 0;
                    if (!int.TryParse(item.MONTANT, out montant))
                    {
                        sIMULATION.Commentaire += " Format du Montant incorret" + Environment.NewLine;
                        anomaliRetrouver = true;

                    }
                }
                if (string.IsNullOrEmpty(item.IDPERIODE))
                {
                    sIMULATION.Commentaire += "La Periode est null" + Environment.NewLine;
                    anomaliRetrouver = true;
                }
                else
                {
                    if (PERIODES.InfosPeriodeAdherent(item.IDPERIODE) == null)
                    {
                        sIMULATION.Commentaire += " La periode inexistant" + Environment.NewLine;
                        anomaliRetrouver = true;

                    }
                }


                sIMULATION.STATUS = string.IsNullOrEmpty(sIMULATION.Commentaire) ?"OK":"KO";

                SIMULATIONFIXEES.Edit(sIMULATION);


            }
            if (!anomaliRetrouver)
            {
                EST_FIXEE eST_FIXEE = new EST_FIXEE();
                EST_FIXEEController eST_FIXEEController = new EST_FIXEEController();
              
                foreach (var item in db.SIMULATIONFIXEE.ToList())
                {
                    eST_FIXEE.REFERENCE_ADHERENT = ADHERENTS.InfosMatriculeAdherent(item.REFERENCE_ADHERENT.Trim());
                    eST_FIXEE.IDPERIODE = PERIODES.InfosPeriodeAdherent(item.IDPERIODE.Trim());
                    int Montant = 0;
                    int.TryParse( item.MONTANT.Trim(), out Montant);
                    eST_FIXEE.MONTANT = Montant;
                    eST_FIXEE.REVENU = Montant;
                    eST_FIXEE.ETAT = 1;
                    eST_FIXEE.IDUTILISATEUR = "ADMIN01";
                    eST_FIXEE.DATECREATION = DateTime.Now;
                   if(!eST_FIXEEController.Ajouter(eST_FIXEE))
                    {
                        error();
                    }

                }
            }

        }


            public ActionResult Create()
        {

            try
            {
                EST_FIXEEController FIXEEController = new EST_FIXEEController();

                if (Session["dataExcel"] != null)
                {
                    DataTable dt = Session["dataExcel"] as DataTable;
                    int code = 1;
                    if (dt.Columns.Count > 0)
                    {
                        SIMULATIONFIXEESController SIMULATIONFIXEES = new SIMULATIONFIXEESController();
                        SIMULATIONFIXEES.DeleteAll();
                        foreach (DataRow dr in (dt as System.Data.DataTable).Rows)

                        {

                            
                            SIMULATIONFIXEE sIMULATION = new SIMULATIONFIXEE();

                            foreach (DataColumn column in (dt as System.Data.DataTable).Columns)
                            {
                                if (column.ColumnName.Equals("Matricule"))
                                {

                                    sIMULATION.REFERENCE_ADHERENT = dr[column].ToString();
                                }
                                if (column.ColumnName.Equals("COTISATION MENSUEL"))
                                {
                                    sIMULATION.MONTANT = dr[column].ToString();
                                }
                                if (column.ColumnName.Equals("PERIODE"))
                                {
                                    sIMULATION.IDPERIODE = dr[column].ToString().Substring(0,10).Trim();

                                }
                                
                            }
                            code = code + 1;
                            sIMULATION.IDUTILISATEUR = User.Identity.Name;
                            sIMULATION.id = code;
                            SIMULATIONFIXEES.Create(sIMULATION);
                        }
                    }
                }
                Thread myThread;

                // Instanciation du thread, on spécifie dans le 
                // délégué ThreadStart le nom de la méthode qui
                // sera exécutée lorsque l'on appelle la méthode
                // Start() de notre thread.
                myThread = new Thread(new ThreadStart(ControleSimmulateurCotisation));

                // Lancement du thread
                myThread.Start();


                return RedirectToAction("index", "SIMULATIONFIXEES");

            }
            catch (System.Exception)
            {

                throw;

                return View("error");
            }
        }
    }

}