public static List<DateSouscriptionModel> GetDateSouscriptionByClient(Guid idclient)
{
using (UpsilabEntities db = new UpsilabEntities())
{
return db.ExecuteStoreQuery<DateSouscriptionModel>("exec agreg.GetDateDeSouscriptionCompteByClient @IdClient", new SqlParameter("@IdClient", idclient)).ToList<DateSouscriptionModel>();
}
}
public static List<AgregSuiviCommercialModel> GetListClientsSuiviCommercial(Guid pSelectedUserId, bool? isCustomer, Upsilab.Data.Model.User pCurrentUser, FirmInstitution pFirmInstitutionParent, List<FirmInstitution> pLstFirmInstitutions)
{
using (UpsilabEntities db = new UpsilabEntities())
{
string sIdFirms = string.Empty;
List<string> idfirms = pLstFirmInstitutions.Select(f => f.idFirmInstitution.ToString()).ToList();
if (idfirms != null && idfirms.Count() > 0)
{
sIdFirms = string.Join(",", idfirms.ToArray());
}
List<AgregSuiviCommercialModel> resultat = db.ExecuteStoreQuery<AgregSuiviCommercialModel>("exec GetListClientsSuiviCommercial @idUser, @isSingleUserDB ,@isCustomer ,@lstIdFirm", new SqlParameter("@idUser", pSelectedUserId), new SqlParameter("@isSingleUserDB", pFirmInstitutionParent.SingleUsersDB), new SqlParameter("@isCustomer", isCustomer), new SqlParameter("@lstIdFirm", sIdFirms)).ToList<AgregSuiviCommercialModel>(); ;
return resultat;
}
}
public static string getNiveaudeRisque(int idcompte,Guid idClient)
{
using (UpsilabEntities context = new UpsilabEntities())
{
try
{
var result = context.ExecuteStoreQuery<string>("select agreg.frontalweb_quantalyscalcrisque(@idcompte,@idClient)", new SqlParameter("idcompte", idcompte), new SqlParameter("idClient", idClient));
return result.FirstOrDefault();
}
catch(Exception ex)
{
Upsilab.Business.Log.Log.AppendException(ex);
return string.Empty;
}
}
}
public static List<AgregChart> GetOPCVMByCompteClient(int idcompte)
{
List<AgregChart> listeChart = new List<AgregChart>();
using (UpsilabEntities db = new UpsilabEntities())
{
/*listeChart = (from support in db.ag_upsideo_support
join ctsupport in db.ag_upsideo_cptrelsupports.Include("ag_upsideo_support").Include("ag_upsideo_compte") on support.idfrontsupport equals ctsupport.idfrontsupport
join cgp in db.ag_upsideo_cgp on support.idcrmcabinet equals cgp.idcrmcabinet
join cpt in db.ag_upsideo_compte on ctsupport.Idfrontcompte equals cpt.Idfrontcompte
where support != null
&& cgp.idcgp == idCgp
&& support.deleted == "n" && cpt.deleted == "n"
&& cpt.Idfrontcompte == idcompte
select ctsupport).ToList()
.GroupBy(s => s.ag_upsideo_support.nomsupport)
.Select(s => new
{
nomsupport = s.Key,
//total = s.Count()
total=s.Sum(x=>x.ag_upsideo_compte.totalcompte.Value)
}).ToDictionary(d => d.nomsupport, d => d.total);*/
listeChart = db.ExecuteStoreQuery<AgregChart>("exec GetChartOPCVM @idcompte,''", new SqlParameter("@idcompte", idcompte)).ToList<AgregChart>();
}
return listeChart;
}
/// <summary>
/// Repartition par class d'actif par compte d'un client
/// </summary>
/// <param name="idCgp"></param>
/// <param name="idclient"></param>
/// <param name="idcompte"></param>
/// <returns></returns>
public static List<AgregChart> GetActifByCompteClient(Guid idCgp, Guid idclient, int idcompte)
{
List<AgregChart> listeChart = new List<AgregChart>();
using (UpsilabEntities db = new UpsilabEntities())
{
listeChart = db.ExecuteStoreQuery<AgregChart>("exec GetChartActif @idcompte,''", new SqlParameter("@idcompte", idcompte)).ToList<AgregChart>();
}
return listeChart;
}
public static List<AgregChart> GetEvalValoByCompte(int idCompte)
{
List<AgregChart> listrepartition = new List<AgregChart>();
using (UpsilabEntities db = new UpsilabEntities())
{
listrepartition = db.ExecuteStoreQuery<AgregChart>("exec GetChartEncourCompte @Idfrontcompte", new SqlParameter("@Idfrontcompte", idCompte)).ToList<AgregChart>();
}
return listrepartition;
}
public ActionResult GetPerfs(int id, int type)
{
try
{
double perfValue = 0;
int calcultype = 2;//todo
using (UpsilabEntities db = new UpsilabEntities())
{
switch (calcultype)
{
case 0:
case 1:
case 2:
case 3:
List<double> resultat = db.ExecuteStoreQuery<double>("exec GraphePerformance @idcompte,@IsSouscription,0,1", new SqlParameter("@idcompte", id), new SqlParameter("@IsSouscription", type)).ToList<double>();
perfValue = resultat.FirstOrDefault();
break;
default:
break;
}
}
string message = string.Format("Performance = {0}", perfValue);
if (perfValue > 0)
{
message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr2.png"), perfValue);
}
else if (perfValue == 0)
{
//message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr1.png"), perfValue);
message = "--";
}
else
{
message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr3.png"), perfValue);
}
return Content(message);
}
catch (Exception ex)
{
//// TODO Erreur de division par zero dans la fonction XIRR
//if (ex is SqlException)
//{
// //string message = string.Format("{1:0.00} % <img src='{0}' width='16' height='16' border='0' class='tooltip tooltipstered' />", @Url.Content("~/Content/images/picto-arr1.png"), 0);
// string message = "--";
// return Content(message);
//}
Upsilab.Business.Log.Log.AppendException(ex);
string message = "--";
return Content(message);
}
}
public static object GetProfilCalculer(Guid idCustomer)
{
using (UpsilabEntities db = new UpsilabEntities())
{
db.CommandTimeout = 180;
var resultat = db.ExecuteStoreQuery<string>("exec GetProfilCalculer @idCustomer", new SqlParameter("@idCustomer", idCustomer)).FirstOrDefault<string>();
return resultat;
}
}
public static List<OPCVMModel> GetOpcvm(Guid idCgp)
{
List<OPCVMModel> ListeOPCVM = new List<OPCVMModel>();
using (UpsilabEntities db = new UpsilabEntities())
{
ListeOPCVM = db.ExecuteStoreQuery<OPCVMModel>("exec GetOpcvm @idcgp", new SqlParameter("@idcgp", idCgp)).ToList<OPCVMModel>();
}
return ListeOPCVM;
}
public static List<AgregSupport> GetTop10OPCVM(Guid idCgp)
{
List<AgregSupport> ListeOPCVM = new List<AgregSupport>();
using (UpsilabEntities db = new UpsilabEntities())
{
ListeOPCVM = db.ExecuteStoreQuery<AgregSupport>("exec GetTop10opcvm @idcgp", new SqlParameter("@idcgp", idCgp)).ToList<AgregSupport>();
}
return ListeOPCVM;
}
public static List<AgregChart> GetCompteHistoByIdCgp(Guid idCgp)
{
List<AgregChart> listrepartition = new List<AgregChart>();
using (UpsilabEntities db = new UpsilabEntities())
{
listrepartition = db.ExecuteStoreQuery<AgregChart>("exec GetChartEncourCgp @idcgp", new SqlParameter("@idcgp", idCgp)).ToList<AgregChart>();
}
return listrepartition;
}
public static List<SqlAgregCompteDetail> GetCompteDetail(int idCompte)
{
List<SqlAgregCompteDetail> resultat = new List<SqlAgregCompteDetail>();
using (UpsilabEntities context = new UpsilabEntities())
{
resultat = context.ExecuteStoreQuery<SqlAgregCompteDetail>("exec GetCompteDetail @idcompte", new SqlParameter("@idcompte", idCompte)).ToList<SqlAgregCompteDetail>();
}
return resultat;
}
public static List<SqlAgregCompteDetail> GetCompteDetailContratAgrege(Guid idClient)
{
List<SqlAgregCompteDetail> resultat = new List<SqlAgregCompteDetail>();
using (UpsilabEntities context = new UpsilabEntities())
{
resultat = context.ExecuteStoreQuery<SqlAgregCompteDetail>("exec GetCompteContratAgrege @idClient", new SqlParameter("@idClient", idClient)).ToList<SqlAgregCompteDetail>();
resultat = resultat.Where(x => x.Qteferme != 0).GroupBy(x => x.Nomsupport).Select(x => x.First()).ToList();
}
return resultat;
}
public static List<SqlAgregCompteModel> GetComptesClient(Guid idClient)
{
List<SqlAgregCompteModel> resultat = new List<SqlAgregCompteModel>();
using (UpsilabEntities context = new UpsilabEntities())
{
context.CommandTimeout = 180;
resultat = context.ExecuteStoreQuery<SqlAgregCompteModel>("exec GetComptesClient @idClient", new SqlParameter("@idClient", idClient)).ToList<SqlAgregCompteModel>();
}
return resultat;
}
public static List<AgregClientModel> GetListAgregClientByCodeisin(Guid idCgp, string codeisin, bool? isCustomer, Upsilab.Data.Model.User pCurrentUser, FirmInstitution pFirmInstitutionParent, List<FirmInstitution> pLstFirmInstitutions)
{
using (UpsilabEntities db = new UpsilabEntities())
{
string sIdFirms = string.Empty;
List<string> idfirms = pLstFirmInstitutions.Select(f => f.idFirmInstitution.ToString()).ToList();
if (idfirms != null && idfirms.Count() > 0)
{
sIdFirms = string.Join(",", idfirms.ToArray());
//sIdFirms = string.Format("'{0}'",sIdFirms);
}
db.CommandTimeout = 180;
List<AgregClientModel> resultat = db.ExecuteStoreQuery<AgregClientModel>("exec GetListAgregClientByCodeisin @idUser,@codeisin, @isSingleUserDB ,@isCustomer ,@lstIdFirm", new SqlParameter("@idUser", idCgp), new SqlParameter("@codeisin", codeisin), new SqlParameter("@isSingleUserDB", pFirmInstitutionParent.SingleUsersDB), new SqlParameter("@isCustomer", isCustomer), new SqlParameter("@lstIdFirm", sIdFirms)).ToList<AgregClientModel>();
return resultat;
}
}
public static object GetTotalClient(Guid idCustomer, string typeTotal)
{
using (UpsilabEntities db = new UpsilabEntities())
{
db.CommandTimeout = 180;
var resultat = db.ExecuteStoreQuery<decimal>("exec GetTotalClient @idCustomer ,@typeTotal", new SqlParameter("@idCustomer", idCustomer), new SqlParameter("@typeTotal", typeTotal)).FirstOrDefault<decimal>();
return resultat;
}
}
public static List<AgregChart> GetActifByFiltre(string listeIdCompte)
{
List<AgregChart> listeChart = new List<AgregChart>();
using (UpsilabEntities db = new UpsilabEntities())
{
listeChart = db.ExecuteStoreQuery<AgregChart>("exec GetChartActifFiltrer @idcompte", new SqlParameter("@idcompte", listeIdCompte)).ToList<AgregChart>();
}
return listeChart;
}
public static List<AgregChart> GetOPCVMByClient(Guid idclient)
{
List<AgregChart> listeChart = new List<AgregChart>();
using (UpsilabEntities db = new UpsilabEntities())
{
listeChart = db.ExecuteStoreQuery<AgregChart>("exec GetChartOPCVM 0,@idclient", new SqlParameter("@idclient", idclient.ToString())).ToList<AgregChart>();
}
return listeChart;
}
public ActionResult getPerformance(int montanttype, int calcultype, int temp, int idCompte)
{
try
{
string perfValue = "0";
string chart = "";
List<AgregGraphePerformanceModel> listeChart = new List<AgregGraphePerformanceModel>();
var log = string.Empty;
string xmlData = "";
using (UpsilabEntities db = new UpsilabEntities())
{
switch (calcultype)
{
case 0:
List<double> listeresultwcf = new List<double>();
listeresultwcf = db.ExecuteStoreQuery<double>("SELECT agreg.frontalweb_fctcalcperfwcf(@idfrontcompte)", new SqlParameter("@idfrontcompte", idCompte)).ToList<double>();
perfValue = listeresultwcf.FirstOrDefault().ToString();
break;
case 1:
case 2:
log += string.Format("exec GraphePerformanceXIRR @idcompte = {0},@isMontantBrut = {1},@typeCalcul = {2},@interval = {3}", idCompte, montanttype, calcultype, temp);
listeChart = db.ExecuteStoreQuery<AgregGraphePerformanceModel>("exec GraphePerformanceXIRR @idcompte,@isMontantBrut,@typeCalcul,@interval", new SqlParameter("@idcompte", idCompte), new SqlParameter("@isMontantBrut", montanttype), new SqlParameter("@typeCalcul", calcultype), new SqlParameter("@interval", temp)).ToList<AgregGraphePerformanceModel>();
xmlData = ChartBL.GetChart2DPerformance(listeChart, false);
chart = FusionCharts.RenderChart(Url.Content("~/Content/FusionCharts/ZoomLine.swf"), "", xmlData, "performanceChartId", "1000", "400", false, true, true, "FFFFFF", "noscale", "EN");
break;
case 3:
log += string.Format("exec GraphePerformanceDIETZ @idcompte = {0},@isMontantBrut = {1},@interval = {2}", idCompte, montanttype, temp);
listeChart = db.ExecuteStoreQuery<AgregGraphePerformanceModel>("exec GraphePerformanceDIETZ @idcompte,@isMontantBrut,@interval", new SqlParameter("@idcompte", idCompte), new SqlParameter("@isMontantBrut", montanttype), new SqlParameter("@interval", temp)).ToList<AgregGraphePerformanceModel>();
xmlData = ChartBL.GetChart2DPerformance(listeChart,false);
chart = FusionCharts.RenderChart(Url.Content("~/Content/FusionCharts/ZoomLine.swf"), "", xmlData, "performanceChartId", "1000", "400", false, true, true, "FFFFFF", "noscale", "EN");
break;
default:
break;
}
}
//
if (listeChart.Count > 0)
{
foreach (var datachart in listeChart)
{
log += string.Format("Resultat Perf{0} - Temp{1}", datachart.perf, datachart.temp);
}
}
else
{
log += "Result empty";
}
//
//var model = new { chart = chart, log = log };
//return PartialView("ChartPerformancePartial", model);
return PartialView("ChartPerformancePartial", chart);
}
catch (Exception ex)
{
Upsilab.Business.Log.Log.AppendException(ex);
return Content("Error");
}
}
