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; } }