public static Assureur GetAssByName(string name)
{
try
{
Assureur ass;
using (var context = new CompteResultatEntities())
{
var elements = context.Assureurs.Where(a => a.Name == name);
if (elements.Any())
{
ass = elements.First();
}
else
{
ass = null;
}
}
return(ass);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int GetYearsToCalcForId(int id)
{
try
{
int yearsToCalc;
using (var context = new CompteResultatEntities())
{
var elements = context.ReportTemplate.Where(c => c.Id == id).Select(c => c.YearsToCalc);
if (elements.Any())
{
if (elements.First().HasValue)
{
yearsToCalc = elements.First().Value;
}
else
{
yearsToCalc = 1;
}
}
else
{
yearsToCalc = 1;
}
}
return(yearsToCalc);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static C.eReportTemplateTypes GetTemplateTypeForId(int id)
{
try
{
C.eReportTemplateTypes type = C.eReportTemplateTypes.SANTE;
using (var context = new CompteResultatEntities())
{
var elements = context.ReportTemplate.Where(c => c.Id == id).Select(c => c.Type);
if (elements.Any())
{
type = (C.eReportTemplateTypes)Enum.Parse(typeof(C.eReportTemplateTypes), elements.First());
}
else
{
throw new Exception("GetTemplateTypeForId :: No template type was found for the report template with the following ID: " + id.ToString());
}
}
return(type);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int TryAddCompanyToContract(string contrName, string comp)
{
try
{
using (var context = new CompteResultatEntities())
{
Contract myContr;
int compId = C.cINVALIDID;
//get the Contract
var elements = context.Contracts.Where(c => (c.ContractId == contrName));
if (elements.Any())
{
//there should only be 1 contract with that name
myContr = elements.First();
if (elements.Count() > 1)
{
log.Error("There are more than 1 contracts with the same name in the Contract Table: " + contrName);
}
//try to find the company
var foundComp = myContr.Companies.Where(c => c.Name == comp);
if (!foundComp.Any())
{
//we did not find the company - so we need to add it to the contract
var elemComp = context.Companies.Where(c => c.Name == comp);
if (elemComp.Any())
{
Company myComp = elemComp.First();
myContr.Companies.Add(myComp);
compId = myComp.Id;
context.SaveChanges();
}
else
{
//we did not find the COMPANY => raise error
throw new Exception("The Company with the name: '" + comp + "' was not found in the Company Table!");
}
}
}
else
{
//we did not find the Contract => raise error
throw new Exception("The Contract with the name: '" + contrName + "' was not found in the Contract Table!");
}
return(compId);
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void InsertIDPairIntoIMTable(IMContrCompIDPair idPair)
{
try
{
using (var context = new CompteResultatEntities())
{
string sql = "INSERT INTO dbo.IMContrComp (IdContract, IdCompany) VALUES(@idContr, @idComp)";
List <SqlParameter> parameterList = new List <SqlParameter>();
parameterList.Add(new SqlParameter("@idContr", idPair.IdContract));
parameterList.Add(new SqlParameter("@idComp", idPair.IdCompany));
SqlParameter[] parameters = parameterList.ToArray();
context.Database.ExecuteSqlCommand(sql, parameters);
}
}
catch (Exception ex)
{
//###
if (!ex.Message.Contains("Violation of PRIMARY KEY constraint"))
{
log.Error(ex.Message);
throw ex;
}
}
}
public static int GetIdForCRNameAndParentComp(string crName, string parentCompId)
{
int crId = C.cINVALIDID;
try
{
using (var context = new CompteResultatEntities())
{
var elements = context.CompteResults.Where(c => (c.Name == crName && c.CompanyIds == parentCompId)).Select(C => C.Id);
if (elements.Any())
{
crId = elements.First();
}
else
{
crId = C.cINVALIDID;
}
}
return(crId);
}
catch (Exception ex)
{
log.Error("GetIdForCRNameAndParentComp :: " + ex.Message);
throw ex;
}
}
public static List <CotisatSante> GetCotisationsForContracts(List <string> assurList, List <string> parentCompanyList, List <string> companyList,
List <string> contrIds, string college, DateTime debutPeriod, DateTime finPeriod)
{
try
{
List <CotisatSante> cotisat;
using (var context = new CompteResultatEntities())
{
cotisat = context.CotisatSantes.Where(cot => assurList.Contains(cot.AssureurName) && parentCompanyList.Contains(cot.Company) &&
companyList.Contains(cot.Subsid) && contrIds.Contains(cot.ContractId) &&
cot.DebPrime >= debutPeriod && cot.FinPrime <= finPeriod).ToList();
//cotisat = context.CotisatSantes.Where(cot => contrIds.Contains(cot.ContractId)
// && cot.DebPrime >= debutPeriod && cot.FinPrime <= finPeriod).ToList();
//old: here we also search for college
//cotisat = context.CotisatSantes.Where(cot => contrIds.Contains(cot.ContractId) && cot.CodeCol == college
// && cot.DebPrime >= debutPeriod && cot.FinPrime <= finPeriod).ToList();
//cotisat2 = context.CotisatSantes.Where(cot => contrIds.Contains(cot.ContractId)).
// Select(cot => new { cot.DebPrime, cot.FinPrime, cot.ContractId, cot.CodeCol, cot.Year, cot.Cotisation }).ToList();
}
return(cotisat);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <DemoSanteWithOptionInfo> GetDemoDataWithOptionInfoTrue(int importId)
{
try
{
List <DemoSanteWithOptionInfo> demoDatawithOption;
using (var context = new CompteResultatEntities())
{
demoDatawithOption = context.Demographies
.Where(d => d.ImportId == importId && d.WithOption.Trim().ToUpper() == "TRUE")
.Select(d => new DemoSanteWithOptionInfo
{
ContractId = d.ContractId,
//WithOption = d.WithOption.HasValue ? d.WithOption.Value : false
WithOption = "TRUE"
}).ToList();
}
return(demoDatawithOption);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static string GetAssNamesForContract(string contractName)
{
try
{
string assName;
using (var context = new CompteResultatEntities())
{
var elements = context.C_TempOtherFields.Where(t => t.ContractId == contractName).Select(t => t.Assureur);
if (elements.Any())
{
assName = elements.Distinct().First();
}
else
{
assName = C.cINVALIDSTRING;
}
}
return(assName);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <GroupesGarantiesSante> GetUniqueGroupsAndGarantiesForAssureur(string assurName)
{
try
{
List <GroupesGarantiesSante> ggs;
using (var context = new CompteResultatEntities())
{
ggs = context.GroupGarantySantes.Where(c => c.AssureurName == assurName)
.GroupBy(p => new { p.AssureurName, p.GroupName, p.GarantyName })
.Select(g => new GroupesGarantiesSante {
AssureurName = g.Key.AssureurName,
GroupName = g.Key.GroupName,
GarantyName = g.Key.GarantyName
})
.OrderBy(gn => gn.GroupName)
.ThenBy(gan => gan.GarantyName)
.ToList();
}
//if (ggs == null || ggs.Count == 0)
// throw new Exception("The 'GroupGarantySante' entity does not contain any data!");
return(ggs);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <SinistrePrev> GetSinistresForContracts(List <string> assurList, List <string> parentCompanyList, List <string> companyList,
List <string> contrIds, string college, DateTime debutPeriod, DateTime finPeriod, DateTime dateArret)
{
try
{
List <SinistrePrev> sinistres;
using (var context = new CompteResultatEntities())
{
sinistres = context.SinistrePrevs.Where(sin => assurList.Contains(sin.AssureurName) && parentCompanyList.Contains(sin.Company) &&
companyList.Contains(sin.Subsid) && contrIds.Contains(sin.ContractId) &&
sin.DateSinistre >= debutPeriod && sin.DateSinistre <= finPeriod && sin.DateRecep <= dateArret).ToList();
//sinistres = context.SinistrePrevs.Where(sin => contrIds.Contains(sin.ContractId)
// && sin.DateSinistre >= debutPeriod && sin.DateSinistre <= finPeriod && sin.DateRecep <= dateArret).ToList();
//List<SinistrePrev> sinistres2 = context.SinistrePrevs.Where(sin => sin.DateSinistre >= debutPeriod && sin.DateSinistre <= finPeriod && sin.DateRecep <= dateArret).ToList();
}
return(sinistres);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <GroupesGarantiesSante> GetUniqueAssureurAndGroups(string assurName)
{
try
{
List <GroupesGarantiesSante> ggs;
using (var context = new CompteResultatEntities())
{
ggs = context.GroupGarantySantes.Where(c => c.AssureurName == assurName)
.GroupBy(p => new { p.AssureurName, p.GroupName })
.Select(g => new GroupesGarantiesSante
{
AssureurName = g.Key.AssureurName,
GroupName = g.Key.GroupName
})
.OrderBy(gn => gn.GroupName)
.ToList();
}
return(ggs);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int AddContractToAss(Contract contr, int assId)
{
try
{
using (var context = new CompteResultatEntities())
{
Assureur myAss;
var elements = context.Assureurs.Where(a => a.Id == assId);
if (elements.Any())
{
myAss = elements.First();
myAss.Contracts.Add(contr);
context.SaveChanges();
return(contr.Id);
}
else
{
return(C.cINVALIDID);
}
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int GetAssIdForAssName(string assName)
{
try
{
int assurId;
using (var context = new CompteResultatEntities())
{
var elements = context.Assureurs.Where(a => a.Name == assName).Select(a => a.Id);
if (elements.Any())
{
assurId = elements.First();
}
else
{
assurId = C.cINVALIDID;
}
}
return(assurId);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <Import> GetImports(string sortExpression)
{
try
{
List <Import> imports;
using (var context = new CompteResultatEntities())
{
if (sortExpression == "Name")
{
imports = context.Imports.OrderBy(i => i.Name).ToList();
}
else if (sortExpression == "UserName")
{
imports = context.Imports.OrderBy(i => i.UserName).ToList();
}
else
{
imports = context.Imports.OrderByDescending(i => i.Date).ToList();
}
}
return(imports);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <string> GetAllSubsidForContract(string contrName)
{
try
{
List <string> compNames;
using (var context = new CompteResultatEntities())
{
var elements = context.C_TempOtherFields.Where(t => t.ContractId == contrName).Select(t => t.Subsid);
if (elements.Any())
{
compNames = elements.Distinct().ToList();
}
else
{
compNames = null;
}
}
return(compNames);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void Delete(int Id)
{
try
{
CompteResult myCR = null;
using (var context = new CompteResultatEntities())
{
var elements = context.CompteResults.Where(c => c.Id == Id);
if (elements.Any())
{
myCR = elements.First();
context.CompteResults.Attach(myCR);
context.CompteResults.Remove(myCR);
context.SaveChanges();
}
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static string GetParentCompanyNameForSubsid(string subsid)
{
try
{
string compName;
using (var context = new CompteResultatEntities())
{
var elements = context.C_TempOtherFields.Where(t => t.Subsid == subsid).Select(t => t.Company);
if (elements.Any())
{
compName = elements.First();
}
else
{
compName = C.cINVALIDSTRING;
}
}
return(compName);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <ExcelGlobalCotisatData> GetCotisatGlobalSubsidData(List <int> years, List <string> subsidList)
{
try
{
List <ExcelGlobalCotisatData> cotisat = new List <ExcelGlobalCotisatData>();
using (var context = new CompteResultatEntities())
{
cotisat = context.CotisatSantes
.Where(d => years.Contains((d.Year.HasValue ? d.Year.Value : 0)) && subsidList.Contains(d.Subsid))
.GroupBy(p => new { p.AssureurName, p.Company, p.Subsid, AnnSurv = p.Year })
.Select(g => new ExcelGlobalCotisatData
{
Assureur = g.Key.AssureurName,
Company = g.Key.Company,
Subsid = g.Key.Subsid,
YearSurv = g.Key.AnnSurv.HasValue ? g.Key.AnnSurv.Value : 0,
Cotisat = g.Sum(i => i.Cotisation),
CotisatBrute = g.Sum(i => i.CotisationBrute)
})
.OrderBy(ga => ga.YearSurv).ThenBy(gb => gb.Company).ThenBy(gc => gc.Subsid)
.ToList();
}
return(cotisat);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <Company> GetParentCompaniesForAssureurIdORIG(int assurId)
{
try
{
List <Company> companies;
Assureur assur = Assureur.GetAssById(assurId);
using (var context = new CompteResultatEntities())
{
List <int> listOfContractIds = assur.Contracts.Select(c => c.Id).ToList();
companies = context.Companies.Where(c => (c.ParentId == null &&
listOfContractIds.Intersect(c.Contracts.Select(cont => cont.Id).ToList()).Any())).OrderBy(c => c.Name).ToList();
}
if (companies == null)
{
throw new Exception("The 'Company' entity does not contain any data!");
}
return(companies);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void Update(CRPlanning newPl)
{
try
{
if (newPl == null)
{
throw new Exception("The 'CRPlanning' entity does not contain any data!");
}
using (var context = new CompteResultatEntities())
{
//CRPlanning oldPl = context.CRPlannings.Where(c => c.Id == newPl.Id).First();
var elements = context.CRPlannings.Where(c => c.Id == newPl.Id);
if (elements.Any())
{
CRPlanning oldPl = elements.First();
oldPl.DateArret = newPl.DateArret;
oldPl.DatePlanification = newPl.DatePlanification;
oldPl.DateTraitement = newPl.DateTraitement;
oldPl.DebutPeriode = newPl.DebutPeriode;
oldPl.FinPeriode = newPl.FinPeriode;
context.SaveChanges();
}
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static bool SubsidCompIdPairExists(string subsid, int compId)
{
try
{
bool exists;
using (var context = new CompteResultatEntities())
{
var elements = context.Companies.Where(c => c.ParentId == compId && c.Name == subsid);
if (elements.Any())
{
exists = true;
}
else
{
exists = false;
}
}
return(exists);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int TryAddAssToContract(string contrName, string ass)
{
try
{
using (var context = new CompteResultatEntities())
{
Contract myContr;
int assId = C.cINVALIDID;
//get the Contract
var elements = context.Contracts.Where(c => (c.ContractId == contrName));
if (elements.Any())
{
//there should only be 1 contract with that name
myContr = elements.First();
if (elements.Count() > 1)
{
log.Error("There are more than 1 contracts with the same name in the Contract Table: " + contrName);
}
//try to find the assureur
var foundAss = myContr.Assureurs.Where(a => a.Name == ass);
if (!foundAss.Any())
{
//we did not find the company - so we need to add it to the contract
var elemAss = context.Assureurs.Where(a => a.Name == ass);
if (elemAss.Any())
{
Assureur myAss = elemAss.First();
myContr.Assureurs.Add(myAss);
assId = myAss.Id;
context.SaveChanges();
}
else
{
//we did not find the Assureur => raise error
throw new Exception("The Assureur with the name: '" + ass + "' was not found in the Assureur Table!");
}
}
}
else
{
//we did not find the Contract => raise error
throw new Exception("The Contract with the name: '" + contrName + "' was not found in the Contract Table!");
}
return(assId);
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int?GetParentIdForSubsid(int subsidId)
{
try
{
int?id;
using (var context = new CompteResultatEntities())
{
var elements = context.Companies.Where(c => c.Id == subsidId).Select(c => c.ParentId);
if (elements.Any())
{
id = elements.First();
}
else
{
id = C.cINVALIDID;
}
}
return(id);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static int GetContrIdForContrName(string contractName)
{
try
{
int contrId;
using (var context = new CompteResultatEntities())
{
var elements = context.Contracts.Where(c => c.ContractId == contractName).Select(c => c.Id);
if (elements.Any())
{
contrId = elements.First();
}
else
{
contrId = C.cINVALIDID;
}
}
return(contrId);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void UpdateCompany(Company newCompany)
{
try
{
if (newCompany == null)
{
throw new Exception("The 'Company' entity does not contain any data!");
}
using (var context = new CompteResultatEntities())
{
Company oldCompany = context.Companies.Where(c => c.Id == newCompany.Id).First();
oldCompany.Name = newCompany.Name;
oldCompany.Telephone = newCompany.Telephone;
oldCompany.Logo = newCompany.Logo;
oldCompany.ContactName = newCompany.ContactName;
oldCompany.Email = newCompany.Email;
oldCompany.Address = newCompany.Address;
context.SaveChanges();
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static bool GetWithOptionFlag(int id)
{
try
{
bool withOption;
using (var context = new CompteResultatEntities())
{
var elements = context.ReportTemplate.Where(c => c.Id == id).Select(c => c.WithOption);
if (elements.Any())
{
if (elements.First().HasValue)
{
withOption = elements.First().Value;
}
else
{
withOption = false;
}
}
else
{
withOption = false;
}
}
return(withOption);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static List <CotisatPrev> GetCotisationsForContracts(List <string> assurList, List <string> parentCompanyList, List <string> companyList,
List <string> contrIds, string college, DateTime debutPeriod, DateTime finPeriod)
{
try
{
List <CotisatPrev> cotisat;
using (var context = new CompteResultatEntities())
{
cotisat = context.CotisatPrevs.Where(cot => assurList.Contains(cot.AssureurName) && parentCompanyList.Contains(cot.Company) &&
companyList.Contains(cot.Subsid) && contrIds.Contains(cot.ContractId) &&
cot.DebPrime >= debutPeriod && cot.FinPrime <= finPeriod).ToList();
//cotisat = context.CotisatPrevs.Where(cot => contrIds.Contains(cot.ContractId)
// && cot.DebPrime >= debutPeriod && cot.FinPrime <= finPeriod).ToList();
}
return(cotisat);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static string GetTemplateFileNameForId(int id)
{
try
{
string name;
using (var context = new CompteResultatEntities())
{
var elements = context.ReportTemplate.Where(c => c.Id == id).Select(c => c.FileName);
if (elements.Any())
{
name = elements.First();
}
else
{
name = C.cINVALIDSTRING;
}
}
return(name);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static Assureur GetAssById(int id)
{
try
{
Assureur ass;
using (var context = new CompteResultatEntities())
{
var elements = context.Assureurs.Include("Contracts").Where(a => a.Id == id);
if (elements.Any())
{
ass = elements.First();
}
else
{
ass = null;
}
}
return(ass);
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
