public ActionResult InsertOperation(ag_upsideo_operation _operation)
        {
            int idCompte = 0;
            SessionManager.Get<int>(agupsideocompteBL.AgregCompteIdSessionKey, out idCompte);
            Guid idAgregCGP = SessionManager.GetUserSession().idAgregCGP.Value;
            ag_upsideo_cgp cgpagreg = AgregCgpBL.GetCgpById(idAgregCGP);
            try
            {
                int idoperation = 0;
                idoperation = _operation.idop;
                ag_upsideo_operation newoperation;
                ag_upsideo_cptrelsupports cptsupport;
                ag_upsideo_cptrelsupports_histo cptsupporthisto;

                ag_upsideo_compte compte;
                ag_upsideo_support support;
                bool isValorisation = false;
                string action = "historique";
                if (Request.Form["Isvalorisation"] != null && Convert.ToInt32(Request.Form["Isvalorisation"]) == 1)
                {
                    isValorisation = true;
                    action = "valoriser";
                }
                //il faut en 1er créer le support selectionné depuis le réferentiel dans la table support
                //ensuite prendre le support depuis la table support
                
                if (_operation.idfrontsupport == 0 && Request.Form["idrefsupport"] != null && Convert.ToInt32(Request.Form["idrefsupport"]) != 0)
                {
                    using (UpsilabEntities db = new UpsilabEntities())
                    {
                        int idrefsupport = Convert.ToInt32(Request.Form["idrefsupport"]);
                        ag_upsideo_referentiel refer = db.ag_upsideo_referentiel.Where(r=>r.idrefsupport == idrefsupport).FirstOrDefault();
                        if (refer != null)
                        {                            
                            ag_upsideo_support newsup = new ag_upsideo_support()
                            {
                                idrefsupport = refer.idrefsupport,
                                idcrmcabinet = cgpagreg.idcrmcabinet,
                                idsg = 0,
                                idtypesupport = refer.idtypesupport,
                                nomsupport = refer.nomsupport,
                                datevl = refer.datevl,
                                vl = refer.vl,
                                codeisin = refer.codeisin,
                                devise = refer.devise,
                                niveaurisque = refer.niveaurisque,
                                categorisation = refer.categorisation,
                                Vleuro = refer.Vleuro,
                                actions = refer.actions,
                                obligations = refer.obligations,
                                monetaire = refer.monetaire,
                                immobilier = refer.immobilier,
                                autres = refer.autres,
                                Performance12mois = refer.Performance12mois,
                                Performance3112 = refer.Performance3112,
                                Performance60mois = refer.Performance60mois,
                                Volatilite12mois = refer.Volatilite12mois,
                                Volatilite60mois = refer.Volatilite60mois,
                                deleted = "n",
                                fiche_prospectus = refer.fiche_prospectus,
                                fiche_dici = refer.fiche_dici
                            };
                            db.ag_upsideo_support.AddObject(newsup);
                            db.SaveChanges();
                            //set new support
                            _operation.idfrontsupport = newsup.idfrontsupport;
                        }
                    }
                }
                using (UpsilabEntities db = new UpsilabEntities())
                {
                    newoperation = db.ag_upsideo_operation.Where(m => m.idop == idoperation).FirstOrDefault();
                    if (idoperation == 0)
                    {
                        newoperation = new ag_upsideo_operation()
                        {
                            Idfrontcompte = idCompte,                            
                            libelleoperation = _operation.libelleoperation,
                            qte = _operation.qte,
                            vl = _operation.vl,
                            frais = _operation.frais, 
                            idfrontsupport = _operation.idfrontsupport,
                            action = action,
                            dateope = _operation.dateope,
                            deleted = "n"
                        };
                        //insert
                        db.ag_upsideo_operation.AddObject(newoperation);
                    }
                    else
                    {
                        newoperation.dateope = _operation.dateope;
                        newoperation.libelleoperation = _operation.libelleoperation;
                        newoperation.qte = _operation.qte;
                        newoperation.vl = _operation.vl;
                        newoperation.frais = _operation.frais;
                        newoperation.idfrontsupport = _operation.idfrontsupport;
                        newoperation.action = action;                        
                    }
                    //obtenir le compte
                    compte = db.ag_upsideo_compte.Where(c => c.Idfrontcompte == idCompte && (c.deleted == null || (c.deleted != null && c.deleted == "n"))).FirstOrDefault();
                    //gerer la table relation 
                    if(_operation.idfrontsupport != 0 && isValorisation)
                    {
                        double qteavantoperation = 0;
                        double qteoperation = 0;
                        double vlderniereoperation = 0;
                        double vloperation = 0;
                        bool isNewRelation = false;
                        
                        support = db.ag_upsideo_support.Where(s => s.idfrontsupport == _operation.idfrontsupport).FirstOrDefault();
                        cptsupport = db.ag_upsideo_cptrelsupports.Include("ag_upsideo_support").Where(c => c.Idfrontcompte == _operation.Idfrontcompte && c.idfrontsupport == _operation.idfrontsupport).FirstOrDefault();
                        cptsupporthisto = db.ag_upsideo_cptrelsupports_histo.Where(c => c.idfrontcompte == _operation.Idfrontcompte && c.idfrontsupport == _operation.idfrontsupport).OrderByDescending(o=>o.datehisto).FirstOrDefault();
                        if (cptsupport != null)
                        {
                            //update
                            //maj pru
                            if (cptsupporthisto != null)
                            {
                                qteavantoperation = cptsupport.qte.HasValue ? cptsupport.qte.Value : 0;
                                qteoperation = _operation.qte.HasValue ? _operation.qte.Value : 0;
                                vlderniereoperation = cptsupporthisto.vl.HasValue ? cptsupporthisto.vl.Value : 0;
                                //vloperation = (cptsupport.ag_upsideo_support != null && cptsupport.ag_upsideo_support.vl.HasValue) ? cptsupport.ag_upsideo_support.vl.Value : 0;
                                vloperation = _operation.vl.HasValue ? _operation.vl.Value : 0;
                                
                                if (_operation.libelleoperation.ToLower().Contains("vente"))
                                {                                    
                                    cptsupport.pru = (qteavantoperation == qteoperation) ? 0 :((qteavantoperation * vlderniereoperation) - (qteoperation * vloperation)) / (qteavantoperation - qteoperation);

                                }
                                else
                                {
                                    cptsupport.pru = ((qteavantoperation * vlderniereoperation) + (qteoperation * vloperation)) / (qteavantoperation + qteoperation);
                                }
                            }
                            //maj qté
                            if (_operation.libelleoperation.ToLower().Contains("vente") && _operation.qte.HasValue && cptsupport.qte.HasValue)
                            {
                                cptsupport.qte = (cptsupport.qte.HasValue ? cptsupport.qte.Value : 0) - (_operation.qte.HasValue ? _operation.qte.Value : 0);
                            }
                            else if (_operation.qte.HasValue && cptsupport.qte.HasValue)
                            {
                                cptsupport.qte = cptsupport.qte.Value + _operation.qte.Value;
                            }
                            cptsupport.datemaj = DateTime.Now;
                            
                        }
                        else
                        {
                            //ajout
                            isNewRelation = true;
                            //ajout pru
                            double pru = 0;                            
                            //vloperation = support.vl.HasValue ? support.vl.Value : 0;
                            vloperation = _operation.vl.HasValue ? _operation.vl.Value : 0;
                            if (!_operation.libelleoperation.ToLower().Contains("vente"))
                            {
                                pru = vloperation;
                            }
                            
                            //ajout qté
                            double qte = 0;
                            if (!_operation.libelleoperation.ToLower().Contains("vente") && _operation.qte.HasValue)
                            {
                                qte = _operation.qte.Value;
                            }                            

                            cptsupport = new ag_upsideo_cptrelsupports()
                            {
                                Idfrontcompte = _operation.Idfrontcompte,
                                idfrontsupport = _operation.idfrontsupport,
                                qte = qte,
                                pru = pru,
                                datemaj = DateTime.Now
                            };
                            db.ag_upsideo_cptrelsupports.AddObject(cptsupport);
                        }
                        //update pru liquidité
                        double montanthorsfrais = (_operation.qte.HasValue ? _operation.qte.Value : 0) * (_operation.vl.HasValue ? _operation.vl.Value : 0);
                        double liquidite= 0;
                        if (_operation.libelleoperation.ToLower().Contains("vente"))
                        {
                            liquidite = montanthorsfrais * (1 - (_operation.frais.HasValue ? _operation.frais.Value : 0) / 100);//txtmontanthorsfrais * (1 - txtfrais / 100);                                                       
                        }
                        else
                        {
                            liquidite = - montanthorsfrais * (1 + (_operation.frais.HasValue ? _operation.frais.Value : 0) / 100);//txtmontanthorsfrais * (1 + txtfrais / 100);                                                        
                        }

                        //voir si le cabinet a un fond fictif (nomsupport = Liquidités)                        
                        ag_upsideo_support supportfictif = db.ag_upsideo_support.Where(s => s.idcrmcabinet == cgpagreg.idcrmcabinet && s.nomsupport == "Liquidités" && (s.deleted == null || (s.deleted != null && s.deleted == "n"))).FirstOrDefault();
                        //si support fictif existe 
                        if (supportfictif != null)
                        {
                            ag_upsideo_cptrelsupports relationfictif = db.ag_upsideo_cptrelsupports.Where(s => s.Idfrontcompte == compte.Idfrontcompte && s.idfrontsupport == supportfictif.idfrontsupport).FirstOrDefault();
                            if(relationfictif != null)
                            {
                                relationfictif.pru = relationfictif.pru + liquidite;
                                relationfictif.qte = 1;
                            }
                        }
                        
                        //fin maj pru fictif
                        
                        
                        
                        //add histo ag_upsideo_cptrelsupports_histo
                        double mtfondeuro = 0;
                        List<ag_upsideo_cptrelsupports> cptsupportListe = new List<ag_upsideo_cptrelsupports>();
                        cptsupportListe = db.ag_upsideo_cptrelsupports.Include("ag_upsideo_support").Where(c => c.Idfrontcompte == _operation.Idfrontcompte).ToList();
                        foreach (ag_upsideo_cptrelsupports cptsupportnow in cptsupportListe)
                        {
                            if (cptsupportnow.ag_upsideo_support.vl.HasValue
                            && cptsupportnow.ag_upsideo_support.vl.Value > 0
                            && cptsupportnow.qte.HasValue
                            && cptsupportnow.qte.Value > 1)
                            {
                                mtfondeuro = cptsupportnow.qte.Value * cptsupportnow.ag_upsideo_support.vl.Value;
                            }
                            else
                            {
                                mtfondeuro = cptsupportnow.pru.HasValue ? cptsupportnow.pru.Value : 0;
                            }
                            //regarde si déjà dans l'histo pour la date du jour
                            ag_upsideo_cptrelsupports_histo cptsupporthistonow = db.ag_upsideo_cptrelsupports_histo.Where(h => h.idfrontcompte == idCompte
                                                                                            && h.idfrontsupport == cptsupportnow.idfrontsupport
                                                                                            && h.datehisto.Value.Year == DateTime.Now.Year
                                                                                            && h.datehisto.Value.Month == DateTime.Now.Month
                                                                                            && h.datehisto.Value.Day == DateTime.Now.Day).FirstOrDefault();
                            if (cptsupporthistonow != null)
                            {
                                cptsupporthistonow.datemaj = DateTime.Now;
                                cptsupporthistonow.qte = cptsupportnow.qte;
                                cptsupporthistonow.pru = cptsupportnow.pru;
                                cptsupporthistonow.vl = cptsupportnow.ag_upsideo_support.vl;
                                cptsupporthistonow.mtfondeuro = mtfondeuro;
                            }
                            else
                            {
                                cptsupporthistonow = new ag_upsideo_cptrelsupports_histo()
                                {
                                    datehisto = DateTime.Now,
                                    datemaj = DateTime.Now,
                                    idfrontcompte = cptsupportnow.Idfrontcompte,
                                    idfrontsupport = cptsupportnow.idfrontsupport,
                                    qte = cptsupportnow.qte,
                                    pru = cptsupportnow.pru,
                                    vl = cptsupportnow.ag_upsideo_support.vl,
                                    mtfondeuro = mtfondeuro
                                };
                                db.ag_upsideo_cptrelsupports_histo.AddObject(cptsupporthistonow);
                            }

                        }
                        //si un nouveau relation (pas encore dans la table relation)
                        if (isNewRelation)
                        {
                            if (support.vl.HasValue
                            && support.vl.Value > 0
                            && cptsupport.qte.HasValue
                            && cptsupport.qte.Value > 1)
                            {
                                mtfondeuro = cptsupport.qte.Value * cptsupport.ag_upsideo_support.vl.Value;
                            }
                            else
                            {
                                mtfondeuro = cptsupport.pru.HasValue ? cptsupport.pru.Value : 0;
                            }

                            ag_upsideo_cptrelsupports_histo cptsupporthistofornewrelation = new ag_upsideo_cptrelsupports_histo()
                            {
                                datehisto = DateTime.Now,
                                datemaj = DateTime.Now,
                                idfrontcompte = cptsupport.Idfrontcompte,
                                idfrontsupport = cptsupport.idfrontsupport,
                                qte = cptsupport.qte,
                                pru = cptsupport.pru,
                                vl = support.vl,
                                mtfondeuro = mtfondeuro
                            };
                            db.ag_upsideo_cptrelsupports_histo.AddObject(cptsupporthistofornewrelation);
                        }
                        //fin histo ag_upsideo_cptrelsupports_histo

                    }
                    //save
                    db.SaveChanges();
                    //après chaque opération , lancer la proc frontalweb_majtotalcompte (pour valorisé compte)                    
                    if (_operation.idfrontsupport != 0 && isValorisation)
                    {                        
                        Guid idClient = Guid.Empty;
                        SessionManager.Get<Guid>(agupsideocompteBL.AgregClientIdSessionKey, out idClient);
                        db.CommandTimeout = 180;
                        db.ExecuteStoreCommand("exec agreg.frontalweb_majtotalcompte @idcrmcabinet,@idfrontcompte", new SqlParameter("@idcrmcabinet", cgpagreg.idcrmcabinet), new SqlParameter("@idfrontcompte", idCompte));
                        //save
                        db.SaveChanges();    
                    }                                   
                    
                }

                if (_operation.idfrontsupport != 0 && isValorisation)
                {
                    using (UpsilabEntities db = new UpsilabEntities())
                    {
                        //compte aprés maj
                        ag_upsideo_compte compteahistoriser = db.ag_upsideo_compte.Where(c => c.Idfrontcompte == idCompte && (c.deleted == null || (c.deleted != null && c.deleted == "n"))).FirstOrDefault();

                        //historisé dans ag_upsideo_compte_histo
                        ag_upsideo_compte_histo cpthisto = db.ag_upsideo_compte_histo.Where(h => h.idfrontcompte == idCompte
                                                                                            && h.datehisto.Year == DateTime.Now.Year
                                                                                            && h.datehisto.Month == DateTime.Now.Month
                                                                                            && h.datehisto.Day == DateTime.Now.Day).FirstOrDefault();
                        if (cpthisto != null)
                        {
                            cpthisto.datehisto = DateTime.Now;
                            cpthisto.totalcompte = compteahistoriser.totalcompte.Value;
                        }
                        else
                        {
                            ag_upsideo_compte_histo cpthistonew = new ag_upsideo_compte_histo()
                            {
                                datehisto = DateTime.Now,
                                idfrontcompte = compteahistoriser.Idfrontcompte,
                                devise = string.IsNullOrEmpty(compteahistoriser.devise) ? "EUR" : compteahistoriser.devise,
                                totalcompte = compteahistoriser.totalcompte.HasValue ? compteahistoriser.totalcompte.Value : 0
                            };
                            //insert
                            db.ag_upsideo_compte_histo.AddObject(cpthistonew);
                        }
                        //save
                        db.SaveChanges();
                        //fin historisation
                    }
                }


            }
            catch (Exception ex)
            {
                ViewBag.Style = "style=color:red";
                ViewBag.Message = "Erreur: " + ex.Message;
                Upsilab.Business.Log.Log.AppendException(ex);
            }

            return RedirectToAction("Index", new { id = idCompte });
           
        }
        public ActionResult OperationDetail(string id)
        {
            //get opération
            ag_upsideo_operation model = null;
            try
            {
                int idCompte = 0;
                SessionManager.Get<int>(agupsideocompteBL.AgregCompteIdSessionKey, out idCompte);
                //teste si compte n'existe pas
                if (idCompte == 0)
                {
                    throw new Exception("Il faut passer par un compte pour manipuler une opération.");
                }
                //set idop 
                int idop = 0;
                if (!string.IsNullOrEmpty(id))
                {
                    idop = Convert.ToInt32(id);
                    SessionManager.Set<int>(agupsideocompteBL.AgregOperationIdSessionKey, idop);
                }
                else
                {
                    SessionManager.Get<int>(agupsideocompteBL.AgregOperationIdSessionKey, out idop);
                }

                using (UpsilabEntities db = new UpsilabEntities())
                {
                    model = db.ag_upsideo_operation.Include("ag_upsideo_compte").Include("ag_upsideo_compte.ag_upsideo_client").Where(o => o.idop == idop && o.Idfrontcompte == idCompte).FirstOrDefault();

                    if (model == null)
                    {
                        model = new ag_upsideo_operation();
                        model.ag_upsideo_compte = db.ag_upsideo_compte.Include("ag_upsideo_client").Where(c => c.Idfrontcompte == idCompte).FirstOrDefault();

                    }
                }
                List<ag_upsideo_support> listesupport = new List<ag_upsideo_support>();
                SessionManager.Get<List<ag_upsideo_support>>(agupsideocompteBL.AgregListeSupportIdSessionKey, out listesupport);
                ViewBag.listesupport = listesupport;
            }
            catch
            {
            }
            return View(model); 
        }