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 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 List <string> GetParentCompanyNamesForAssureurId(int assurId)
{
try
{
List <Company> parentComps;
List <string> companies = new List <string>();
Assureur assur = Assureur.GetAssById(assurId);
using (var context = new CompteResultatEntities())
{
List <int> listOfContractIds = assur.Contracts.Select(c => c.Id).ToList();
//use eager loading to get contract data related to each company
parentComps = context.Companies
.Where(c => (c.ParentId == null))
.Include(c => c.Contracts)
.OrderBy(c => c.Name)
.ToList();
foreach (Company c in parentComps)
{
bool hasCommonContracts = listOfContractIds.Intersect(c.Contracts.Select(cont => cont.Id).ToList()).Any();
if (hasCommonContracts)
{
companies.Add(c.Name);
}
}
//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 int Insert(Assureur ass)
{
try
{
using (var context = new CompteResultatEntities())
{
context.Assureurs.Add(ass);
context.SaveChanges();
return(ass.Id);
}
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}
public static void NOTNEEDEDUpdateCorrespondingTablesAfterImport()
{
try
{
using (var context = new CompteResultatEntities())
{
//Verify Assureur's
List <string> existingAss = Assureur.GetUniqueAssNames();
List <string> newAss = C_TempOtherFields.GetUniqueAssNames();
List <string> assToBeAdded = newAss.Where(i => !existingAss.Contains(i)).ToList();
foreach (string ass in assToBeAdded)
{
Assureur assur = new Assureur {
Name = ass
};
context.Assureurs.Add(assur);
int assId = assur.Id;
//int assId = Assureur.Insert(new Assureur { Name = ass });
}
//Verify ContractId's
List <string> existingContr = Contract.GetUniqueContractIds();
List <string> newContr = C_TempOtherFields.GetUniqueContractIds();
List <string> contractsToBeAdded = newContr.Where(i => !existingContr.Contains(i)).ToList();
foreach (string contr in contractsToBeAdded)
{
//get the AssureurName for the given contract from TempTable
string assName = C_TempOtherFields.GetAssNamesForContract(contr);
if (assName == C.cINVALIDSTRING)
{
throw new Exception("No insurance company was found in the '_TempOtherFields' table for the following contract: " + contr);
}
//with the AssureurName, get the AssId from the Assureur Table
int assId = Assureur.GetAssIdForAssName(assName);
if (assId == C.cINVALIDID)
{
throw new Exception("No insurance company ID was found in the 'Assureur' table for the following insurance company: " + assName);
}
context.Contracts.Add(new Contract {
ContractId = contr
});
//int contrId = Contract.Insert(new Contract { ContractId = contr, AssureurId = assId });
}
//save all changes
context.SaveChanges();
} //end of: using (var context...
}
catch (Exception ex)
{
log.Error(ex.Message);
throw ex;
}
}