/// <summary>
/// Loader of industries
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void FunctionHandler(ILambdaContext context)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
Logger.Init(TRANSFORMER_NAME);
Logger.Log("Started");
// todo: switch to S3
var filename = "C:\\temp\\industry.csv";
File.WriteAllText(filename, File.ReadAllText(filename).Replace(',', '.'));
var str = File.ReadAllText(filename);
str = str.Replace(',', '.');
File.WriteAllText(filename, str);
Logger.Log("Configuring DB context.");
SilverContext dbContext = new SilverContext();
dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
dbContext.ChangeTracker.LazyLoadingEnabled = true;
using (var fileReader = new StreamReader(filename))
{
Logger.Log("Configuring CSV reader");
var csvReader = new CsvReader(fileReader, CultureInfo.InvariantCulture);
csvReader.Configuration.Delimiter = ";";
csvReader.Configuration.MissingFieldFound = null;
csvReader.Configuration.RegisterClassMap <IndustryMap>();
csvReader.Configuration.RegisterClassMap <IndustryCountryMap>();
while (csvReader.Read())
{
var industry = csvReader.GetRecord <Industry>();
var industryCountry = csvReader.GetRecord <IndustryCountry>();
industryCountry.Industry = industry;
industry.IndustryCountries.Add(industryCountry);
dbContext.Add(industryCountry);
dbContext.Add(industry);
}
}
dbContext.SaveChanges();
dbContext.Dispose();
sw.Stop();
Logger.Log($"Finished successfully in {sw.ElapsedMilliseconds} ms");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
}
public APIGatewayProxyResponse GetIndustries(APIGatewayProxyRequest request, ILambdaContext context)
{
try
{
context.Logger.LogLine("Get Request\n");
if (!ValidateRequest(request))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.Forbidden,
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
SilverContext silverContext = new SilverContext();
var response = new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.OK,
Body = JsonConvert.SerializeObject(silverContext.Industries.ToList()),
Headers = new Dictionary <string, string> {
{ "Content-Type", "application/json" }
}
};
return(response);
}
catch (Exception ex)
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = $"Bad query: {ex}",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
}
public APIGatewayProxyResponse SaveCompany(APIGatewayProxyRequest request, ILambdaContext context)
{
try
{
context.Logger.LogLine("Get Request\n");
if (!ValidateRequest(request))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.Forbidden,
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
if (string.IsNullOrEmpty(request.Body))
{
throw new ArgumentNullException("request.Body");
}
Company source = JsonConvert.DeserializeObject <Company>(request.Body);
Company targetCompany;
SilverContext silverContext = new SilverContext();
if (!source.Id.HasValue || source.Id == 0)
{
targetCompany = new Company();
silverContext.Companies.Add(targetCompany);
}
else
{
targetCompany = silverContext.Companies.Where(x => x.Id == source.Id)
.Include(x => x.CompanyCountries)
.Include(x => x.CompanyExtendedData)
.Include(x => x.CompanyQuestionnaires)
.Include(x => x.CompanyIndustries)
.Include(x => x.CompanyNames)
.FirstOrDefault();
if (targetCompany == null)
{
throw new ArgumentOutOfRangeException("company");
}
}
targetCompany.Lei = source.Lei;
targetCompany.Status = source.Status;
targetCompany.LegalName = source.LegalName;
targetCompany.NumEmployees = source.NumEmployees;
// countries
var srcIds = source.CompanyCountries
.Where(x => x.CompanyCountryId.HasValue)
.Select(x => x.CompanyCountryId.Value)
.ToHashSet();
var toRemove = new List <CompanyCountry>();
foreach (var companyCountry in targetCompany.CompanyCountries)
{
if (!srcIds.Contains(companyCountry.CompanyCountryId.Value))
{
toRemove.Add(companyCountry);
}
}
foreach (var item in toRemove)
{
targetCompany.CompanyCountries.Remove(item);
silverContext.Remove(item);
}
foreach (var companyCountry in source.CompanyCountries)
{
CompanyCountry targetEntity;
if (!companyCountry.CompanyCountryId.HasValue)
{
targetEntity = new CompanyCountry()
{
Company = targetCompany
};
targetCompany.CompanyCountries.Add(targetEntity);
silverContext.CompanyCountries.Add(targetEntity);
}
else
{
targetEntity = targetCompany.CompanyCountries.First(x => x.CompanyCountryId == companyCountry.CompanyCountryId);
}
targetEntity.CountryId = companyCountry.CountryId;
targetEntity.IsPrimary = companyCountry.IsPrimary;
targetEntity.LegalJurisdiction = companyCountry.LegalJurisdiction;
targetEntity.Ticker = companyCountry.Ticker;
}
// extended data
if (source.CompanyExtendedData.Count > 0)
{
var sourceExtendedData = source.CompanyExtendedData.First();
CompanyExtendedData targetExtendedData;
if (targetCompany.CompanyExtendedData.Any())
{
targetExtendedData = targetCompany.CompanyExtendedData.First();
}
else
{
targetExtendedData = new CompanyExtendedData();
targetCompany.CompanyExtendedData.Add(targetExtendedData);
silverContext.Add(targetExtendedData);
}
targetExtendedData.BelowNationalAvgIncome = sourceExtendedData.BelowNationalAvgIncome;
targetExtendedData.Company = targetCompany;
targetExtendedData.DisabledEmployees = sourceExtendedData.DisabledEmployees;
targetExtendedData.HierarchyLevel = sourceExtendedData.HierarchyLevel;
targetExtendedData.RetentionRate = sourceExtendedData.RetentionRate;
targetExtendedData.MedianSalary = sourceExtendedData.MedianSalary;
}
else
{
targetCompany.CompanyExtendedData.Clear();
}
// questions
if (source.CompanyQuestionnaires.Count > 0)
{
srcIds = source.CompanyQuestionnaires
.Where(x => x.Id.HasValue)
.Select(x => x.Id.Value)
.ToHashSet();
var questionsToRemove = new List <CompanyQuestion>();
foreach (var companyQuestion in targetCompany.CompanyQuestionnaires)
{
if (!srcIds.Contains(companyQuestion.Id.Value))
{
questionsToRemove.Add(companyQuestion);
}
}
foreach (var item in questionsToRemove)
{
targetCompany.CompanyQuestionnaires.Remove(item);
silverContext.Remove(item);
}
foreach (var companyQuestion in source.CompanyQuestionnaires)
{
CompanyQuestion targetEntity;
if (!companyQuestion.Id.HasValue)
{
targetEntity = new CompanyQuestion()
{
Company = targetCompany
};
targetCompany.CompanyQuestionnaires.Add(targetEntity);
silverContext.CompanyQuestionnaires.Add(targetEntity);
}
else
{
targetEntity = targetCompany.CompanyQuestionnaires.First(x => x.Id == companyQuestion.Id);
}
targetEntity.Question = companyQuestion.Question;
targetEntity.Answer = companyQuestion.Answer;
}
}
else
{
targetCompany.CompanyQuestionnaires.Clear();
}
// names
if (source.CompanyNames.Count > 0)
{
srcIds = source.CompanyNames
.Where(x => x.Id.HasValue)
.Select(x => x.Id.Value)
.ToHashSet();
var namesToRemove = new List <CompanyName>();
foreach (var companyName in targetCompany.CompanyNames)
{
if (!srcIds.Contains(companyName.Id.Value))
{
namesToRemove.Add(companyName);
}
}
foreach (var item in namesToRemove)
{
targetCompany.CompanyNames.Remove(item);
silverContext.Remove(item);
}
foreach (var companyName in source.CompanyNames)
{
CompanyName targetEntity;
if (!companyName.Id.HasValue)
{
targetEntity = new CompanyName()
{
Company = targetCompany
};
targetCompany.CompanyNames.Add(targetEntity);
silverContext.CompanyNames.Add(targetEntity);
}
else
{
targetEntity = targetCompany.CompanyNames.First(x => x.Id == companyName.Id);
}
targetEntity.Name = companyName.Name;
targetEntity.Type = companyName.Type;
}
}
else
{
targetCompany.CompanyNames.Clear();
}
// industries
if (source.CompanyIndustries.Count > 0)
{
srcIds = source.CompanyIndustries
.Where(x => x.Id.HasValue)
.Select(x => x.Id.Value)
.ToHashSet();
var industriesToRemove = new List <CompanyIndustry>();
foreach (var companyName in targetCompany.CompanyIndustries)
{
if (!srcIds.Contains(companyName.Id.Value))
{
industriesToRemove.Add(companyName);
}
}
foreach (var item in industriesToRemove)
{
targetCompany.CompanyIndustries.Remove(item);
silverContext.Remove(item);
}
foreach (var companyIndustry in source.CompanyIndustries)
{
CompanyIndustry targetEntity;
if (!companyIndustry.Id.HasValue)
{
targetEntity = new CompanyIndustry()
{
Company = targetCompany
};
targetCompany.CompanyIndustries.Add(targetEntity);
silverContext.CompanyIndustries.Add(targetEntity);
}
else
{
targetEntity = targetCompany.CompanyIndustries.First(x => x.Id == companyIndustry.Id);
}
targetEntity.IndustryId = companyIndustry.IndustryId;
targetEntity.PrimarySecondary = companyIndustry.PrimarySecondary;
}
}
else
{
targetCompany.CompanyIndustries.Clear();
}
// TODO: roles and people
silverContext.SaveChanges();
var response = new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.OK,
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
};
return(response);
}
catch (Exception ex)
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = $"Bad query: {ex}",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
}
public APIGatewayProxyResponse DeleteCompany(APIGatewayProxyRequest request, ILambdaContext context)
{
try
{
context.Logger.LogLine("Get Request\n");
if (!ValidateRequest(request))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.Forbidden,
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
int id;
if (request.QueryStringParameters == null ||
!request.QueryStringParameters.ContainsKey(ID))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = "Expected query string parameters",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
if (!int.TryParse(request.QueryStringParameters[ID], out id))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = "Wrong number format",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
SilverContext silverContext = new SilverContext();
var company = silverContext.Companies.Where(x => x.Id == id).FirstOrDefault();
if (company == null)
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = "Item is not found",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
silverContext.Companies.Remove(company);
silverContext.SaveChanges();
var response = new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.OK,
Headers = new Dictionary <string, string> {
{ "Content-Type", "application/json" }
}
};
return(response);
}
catch (Exception ex)
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = $"Bad query: {ex}",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
}
/// <summary>
/// A Lambda function to get a list of companies to HTTP Get methods from API Gateway
/// </summary>
/// <param name="request"></param>
/// <returns>The API Gateway response.</returns>
public APIGatewayProxyResponse GetCompanies(APIGatewayProxyRequest request, ILambdaContext context)
{
try
{
context.Logger.LogLine("Get Request\n");
if (!ValidateRequest(request))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.Forbidden,
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
int pageSize;
int pageIndex;
if (request.QueryStringParameters == null)
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = "Expected query string parameters",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
if (!int.TryParse(request.QueryStringParameters[PAGE_SIZE], out pageSize) ||
!int.TryParse(request.QueryStringParameters[PAGE_INDEX], out pageIndex))
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = "Wrong number format",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
SilverContext silverContext = new SilverContext();
var companiesQuery = silverContext.Companies.AsQueryable <Company>();
string prefix = null;
if (request.QueryStringParameters.ContainsKey(SEARCH_PREFIX) &&
!string.IsNullOrEmpty(prefix = request.QueryStringParameters[SEARCH_PREFIX]))
{
companiesQuery = companiesQuery.Where(x => x.LegalName.StartsWith(prefix));
}
var companies = companiesQuery
.Include(x => x.Roles).ThenInclude(x => x.Person)
.Include(x => x.CompanyNames)
.Include(x => x.CompanyExtendedData)
.Include(x => x.CompanyQuestionnaires)
.OrderBy(x => x.LegalName).Skip(pageIndex * pageSize).Take(pageSize).ToArray();
var response = new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.OK,
Body = JsonConvert.SerializeObject(companies),
Headers = new Dictionary <string, string> {
{ "Content-Type", "application/json" }
}
};
return(response);
}
catch (Exception ex)
{
return(new APIGatewayProxyResponse
{
StatusCode = (int)HttpStatusCode.BadRequest,
Body = $"Bad query: {ex}",
Headers = new Dictionary <string, string> {
{ "Content-Type", "text/plain" }
}
});
}
}
/// <summary>
/// Loader of CEOs and board members
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void FunctionHandler(ILambdaContext context)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
Logger.Init(TRANSFORMER_NAME, "{2}");
Logger.Log("Started");
// todo: switch to S3
var filename = "C:\\temp\\Companies.csv";
Logger.Log("Configuring DB context.");
var factory = LoggerFactory.Create(builder =>
{
builder.AddProvider(new TraceLoggerProvider());
});
var dbContext = new SilverContext(factory);
dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
dbContext.ChangeTracker.LazyLoadingEnabled = true;
var countries = dbContext.Countries.Select(x => new { x.Name, x.Id }).ToDictionary(x => x.Name, x => x.Id);
countries["UAE"] = countries["United Arab Emirates"];
countries["Russia"] = countries["Russian Federation"];
countries["England"] = countries["United Kingdom"];
countries["Guernsey"] = countries["United Kingdom"];
countries["Great Britain"] = countries["United Kingdom"];
countries["Hong Kong"] = countries["China"];
countries["Palestine"] = countries["State of Palestine"];
foreach (var key in countries.Keys)
{
Logger.Log(key);
}
var matchesRaw = dbContext.CompanyMatches.AsNoTracking()
.Where(x => x.CompanyId != null)
.ToList();
var matches = matchesRaw
.GroupBy(x => x.Name)
.Where(x => x.Count() == 1)
.SelectMany(x => x)
.ToDictionary(x => x.Name, x => x.CompanyId);
List <Tuple <Role, Person> > rolesToAdd = new List <Tuple <Role, Person> >();
HashSet <string> companies = new HashSet <string>();
// preload relevant countries
using (var fileReader = new StreamReader(filename))
{
Logger.Log("Configuring CSV reader");
var csvReader = new CsvReader(fileReader, CultureInfo.InvariantCulture);
csvReader.Configuration.Delimiter = ";";
csvReader.Configuration.MissingFieldFound = null;
csvReader.Configuration.RegisterClassMap <PersonMap>();
csvReader.Configuration.RegisterClassMap <RoleMap>();
bool doOnce = false;
while (csvReader.Read())
{
if (!doOnce)
{
var role = csvReader.GetRecord <Role>();
doOnce = true;
}
companies.Add(csvReader.GetField(0));
}
}
var companyIds = companies.Where(x => matches.ContainsKey(x)).Select(x => matches[x]).Where(x => x != -1).ToList();
Dictionary <int, Company> companiesLookup =
dbContext.Companies.AsQueryable().Include(x => x.Roles).Where(x => companyIds.Contains(x.Id))
.ToDictionary(x => x.Id.Value, x => x);
using (var fileReader = new StreamReader(filename))
{
Logger.Log("Configuring CSV reader");
var csvReader = new CsvReader(fileReader, CultureInfo.InvariantCulture);
csvReader.Configuration.Delimiter = ";";
csvReader.Configuration.MissingFieldFound = null;
csvReader.Configuration.RegisterClassMap <PersonMap>();
csvReader.Configuration.RegisterClassMap <RoleMap>();
string prevCompanyName = "";
Company targetCompany = null;
Person previousPerson = null;
while (csvReader.Read())
{
var role = csvReader.GetRecord <Role>();
var person = csvReader.GetRecord <Person>();
if (person.Disability == "0")
{
person.Disability = null;
}
if (person.EduInstitute == "0")
{
person.EduInstitute = null;
}
if (person.EduSubject == "0")
{
person.EduSubject = null;
}
if (person.HighEdu == "0")
{
person.HighEdu = null;
}
if (person.Married == "0")
{
person.Married = null;
}
if (person.Race == "0")
{
person.Race = null;
}
if (person.Religion == "0")
{
person.Religion = null;
}
if (person.Sexuality == "0")
{
person.Sexuality = null;
}
//if (person.Urban == "0") person.Urban = null;
var companyName = csvReader.GetField(0); // company name
if (companyName != prevCompanyName)
{
if (matches.ContainsKey(companyName))
{
var companyId = matches[companyName];
prevCompanyName = companyName;
if (companyId == -1 || !companiesLookup.ContainsKey(companyId.Value))
{
targetCompany = null;
continue;
}
targetCompany = companiesLookup[companyId.Value];
if (targetCompany == null)
{
throw new InvalidOperationException($"Company is not found for ID {companyId}");
}
}
else
{
continue;
}
}
if (targetCompany == null)
{
continue;
}
if (previousPerson == null || person.Name != previousPerson.Name)
{
previousPerson = person;
dbContext.Add(person);
Role targetRole;
if ((targetRole = targetCompany.Roles.FirstOrDefault(x => x.RoleType == role.RoleType && x.Title == role.Title)) == null)
{
role.Company = targetCompany;
rolesToAdd.Add(Tuple.Create(role, person));
}
var primaryNation = csvReader.GetField("Primary Nation").Trim();
primaryNation = primaryNation.Substring(0, 1).ToUpperInvariant() + primaryNation.Substring(1);
if (!string.IsNullOrEmpty(primaryNation) && primaryNation != "0")
{
if (countries.ContainsKey(primaryNation))
{
var pc = new PersonCountry()
{
CountryId = countries[primaryNation],
Person = person
};
person.PersonCountries.Add(pc);
dbContext.Add(pc);
}
else
{
Logger.Log($"No country found - {primaryNation}");
}
}
var secondaryNation = csvReader.GetField("Secondary Nation").Trim();
secondaryNation = secondaryNation.Substring(0, 1).ToUpperInvariant() + secondaryNation.Substring(1);
if (!string.IsNullOrEmpty(secondaryNation) && secondaryNation != "0" && secondaryNation != "Hong Kong")
{
if (countries.ContainsKey(secondaryNation))
{
var pc = new PersonCountry()
{
CountryId = countries[secondaryNation],
Person = person
};
person.PersonCountries.Add(pc);
dbContext.Add(pc);
}
else
{
Logger.Log($"No country found - {secondaryNation}");
}
}
}
}
}
dbContext.SaveChanges();
foreach (var tuple in rolesToAdd)
{
tuple.Item1.PersonId = tuple.Item2.Id;
dbContext.Add(tuple.Item1);
}
dbContext.SaveChanges();
dbContext.Dispose();
sw.Stop();
Logger.Log($"Finished successfully in {sw.ElapsedMilliseconds} ms");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
}
/// <summary>
/// A simple function that takes a string and does a ToUpper
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void FunctionHandler(ILambdaContext context)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
Logger.Init(TRANSFORMER_NAME);
Logger.Log("Started");
// todo: switch to S3
var filename = "C:\\temp\\country.csv";
Logger.Log($"Creating DB contexts.");
var silverContext = new SilverContext();
var leadContext = new LeadContext();
Dictionary <string, Country> countries = silverContext.Countries.ToDictionary(x => x.Code, x => x);
if (countries.Count == 0)
{
throw new InvalidOperationException("Countries are not loaded");
}
List <string> leis;
leis = leadContext.GleifEntity.Select(x => x.Lei)
//.Take(1000)
.ToList();
//using (StreamWriter sw = new StreamWriter("C:\\temp\\leis.dat"))
//{
// foreach (var s in leis)
// {
// sw.WriteLine(s);
// }
//}
//leis = new List<string>(1700000);
//using (StreamReader sr = new StreamReader("C:\\temp\\leis.dat"))
//{
// while(!sr.EndOfStream)
// {
// leis.Add(sr.ReadLine());
// }
//}
var chunks = new List <List <string> >();
int size = 10000;
for (int i = 0; i < leis.Count; i += size)
{
chunks.Add(leis.GetRange(i, Math.Min(size, leis.Count - i)));
}
chunks
.AsParallel().WithDegreeOfParallelism(24).ForAll(
//.Skip(20).ToList()
//.ForEach(
x => LoadChunk(x, chunks.IndexOf(x), countries));
sw.Stop();
Logger.Log($"Finished successfully in {sw.ElapsedMilliseconds} ms");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
}
public void LoadChunk(List <string> chunk, int index, Dictionary <string, Country> countries)
{
try
{
Logger.Log($"Loading chunk {index}");
var leadContext = new LeadContext();
//var factory = LoggerFactory.Create(builder =>
//{
// builder.AddProvider(new TraceLoggerProvider());
//});
var silverContext = new SilverContext();
silverContext.ChangeTracker.LazyLoadingEnabled = true;
silverContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
silverContext.ChangeTracker.AutoDetectChangesEnabled = false;
List <Company> companiesToSave = new List <Company>();
List <CompanyName> namesToSave = new List <CompanyName>();
foreach (var entity in leadContext.GleifEntity.AsNoTracking().Where(x => chunk.Contains(x.Lei)).Include(x => x.GleifAddresses).Include(x => x.GleifEntityNames))
{
//var targetEntity = silverContext.Company.Where(x => x.Lei == entity.Lei).FirstOrDefault();
//if (targetEntity == null)
//{
if (!countries.ContainsKey(entity.LegalJurisdiction.Split('-')[0]))
{
// we have no country data for it
continue;
}
var targetEntity = new Company();
companiesToSave.Add(targetEntity);
//}
var names = entity.GleifEntityNames;
var legalName = names.Where(x => x.Type == "LEGAL").First(); //legal name should exist
var otherNames = names.Where(x => x.Type != "LEGAL" && x.Type != "PREVIOUS_LEGAL_NAME" && !string.IsNullOrEmpty(x.Name)).ToList();
targetEntity.LegalName = legalName.Name;
foreach (var name in otherNames)
{
var otherName = new CompanyName()
{
Company = targetEntity,
Name = name.Name,
Type = name.Type,
};
targetEntity.CompanyNames.Add(otherName);
namesToSave.Add(otherName);
}
targetEntity.Status = entity.Status;
targetEntity.Lei = entity.Lei;
var legalAddress = entity.GleifAddresses.Where(x => x.Type == "LEGAL").First();
if (targetEntity.Legal == null)
{
targetEntity.Legal = new Address();
silverContext.Add(targetEntity.Legal);
//targetEntity.Legal.CompanyLegal.Add(targetEntity);
}
Address targetLegalAddress = targetEntity.Legal;
targetLegalAddress.City = legalAddress.City;
if (countries.ContainsKey(legalAddress.Country))
{
targetLegalAddress.Country = countries[legalAddress.Country];
targetLegalAddress.CountryId = targetLegalAddress.Country.Id;
}
targetLegalAddress.AddressNumber = legalAddress.Addressnumber;
targetLegalAddress.PostalCode = legalAddress.Postalcode;
targetLegalAddress.Region = legalAddress.Region;
targetLegalAddress.AddressLine = legalAddress.Firstaddressline
+ (string.IsNullOrEmpty(legalAddress.Additionaladdressline1) ? string.Empty : " " + legalAddress.Additionaladdressline1)
+ (string.IsNullOrEmpty(legalAddress.Additionaladdressline2) ? string.Empty : " " + legalAddress.Additionaladdressline2)
+ (string.IsNullOrEmpty(legalAddress.Additionaladdressline3) ? string.Empty : " " + legalAddress.Additionaladdressline3);
targetLegalAddress.AddressLine = targetLegalAddress.AddressLine ?? string.Empty;
var hqAddress = entity.GleifAddresses.Where(x => x.Type == "HQ").First();
if (targetEntity.Hq == null)
{
targetEntity.Hq = new Address();
silverContext.Add(targetEntity.Hq);
//targetEntity.Hq.CompanyHq.Add(targetEntity);
}
Address targetHqAddress = targetEntity.Hq;
targetHqAddress.City = hqAddress.City;
if (countries.ContainsKey(hqAddress.Country))
{
targetHqAddress.Country = countries[hqAddress.Country];
targetHqAddress.CountryId = targetHqAddress.Country.Id;
}
targetHqAddress.AddressNumber = hqAddress.Addressnumber;
targetHqAddress.PostalCode = hqAddress.Postalcode;
targetHqAddress.Region = hqAddress.Region;
targetHqAddress.AddressLine = hqAddress.Firstaddressline
+ (string.IsNullOrEmpty(hqAddress.Additionaladdressline1) ? string.Empty : " " + hqAddress.Additionaladdressline1)
+ (string.IsNullOrEmpty(hqAddress.Additionaladdressline2) ? string.Empty : " " + hqAddress.Additionaladdressline2)
+ (string.IsNullOrEmpty(hqAddress.Additionaladdressline3) ? string.Empty : " " + hqAddress.Additionaladdressline3);
targetHqAddress.AddressLine = targetHqAddress.AddressLine ?? string.Empty;
targetEntity.CompanyCountries.Add(
new CompanyCountry()
{
IsPrimary = true,
LegalJurisdiction = true,
Company = targetEntity,
CountryId = countries[entity.LegalJurisdiction.Split('-')[0]].Id
});
}
silverContext.SaveChanges();
foreach (var company in companiesToSave)
{
silverContext.Add(company);
}
foreach (var name in namesToSave)
{
silverContext.Add(name);
}
silverContext.SaveChanges();
silverContext.Dispose();
leadContext.Dispose();
Logger.Log($"Chunk {index} loaded");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
throw;
}
}
/// <summary>
/// Automatcher between given company names and companies
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void FunctionHandler(ILambdaContext context)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
Logger.Init(TRANSFORMER_NAME, "{2}");
Logger.Log("Started");
// todo: switch to S3
var filename = "C:\\temp\\Companies.csv";
Logger.Log("Configuring DB context.");
/*var factory = LoggerFactory.Create(builder =>
* {
* builder.AddProvider(new TraceLoggerProvider());
* });*/
var dbContext = new SilverContext(/*factory*/);
dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
dbContext.ChangeTracker.LazyLoadingEnabled = true;
dbContext.Database.ExecuteSqlRaw("TRUNCATE TABLE company_match");
countries = dbContext.Countries.AsNoTracking().ToList();
var names = dbContext.CompanyNames.AsNoTracking()
.Include(x => x.Company).ThenInclude(x => x.CompanyCountries.Where(x => x.LegalJurisdiction)).ThenInclude(x => x.Country)
.Select(x => new { x.Name, x.Type, x.Id, LegalJurisdiction = x.Company.CompanyCountries }).ToList(); // note that ID is internal to our DB, it's different from LEI
Action <string, ICollection <CompanyCountry>, int, List <Tuple <string, string> >, Dictionary <string, int> > add = (name, code, id, namesList, namesDictionary) =>
{
var standard = Standardize(name);
if (!string.IsNullOrEmpty(standard) && standard.Length > 1)
{
if (!namesDictionary.ContainsKey(standard))
{
namesList.Add(Tuple.Create(standard, code.First().Country.Code));
namesDictionary[standard] = id;
}
else
{
Logger.Log("Found duplicate entity name: " + name);
}
}
};
foreach (var n in names)
{
if (n.Type == "TRADING_OR_OPERATING_NAME")
{
add(n.Name, n.LegalJurisdiction, n.Id.Value, akaNames, akaNameToId);
}
else if (n.Type == "AUTO_ASCII_TRANSLITERATED_LEGAL_NAME" ||
n.Type == "PREFERRED_ASCII_TRANSLITERATED_LEGAL_NAME" ||
n.Type == "ALTERNATIVE_LANGUAGE_LEGAL_NAME")
{
add(n.Name, n.LegalJurisdiction, n.Id.Value, transliteratedNames, transliteratedNameToId);
}
}
var companies = dbContext.Companies.AsNoTracking()
.Include(x => x.CompanyCountries.Where(x => x.LegalJurisdiction)).ThenInclude(x => x.Country)
.Select(x => new { x.LegalName, LegalJurisdiction = x.CompanyCountries, x.Id }).ToList();
foreach (var n in companies)
{
add(n.LegalName, n.LegalJurisdiction, n.Id.Value, legalNames, legalNameToId);
}
// The following code is used to dump all relevant DB data except countries - it saves time to reload ready data structures from hard drive
//BinaryFormatter bf = new BinaryFormatter();
//using (var fs = new FileStream("C:\\temp\\legal_names.txt", FileMode.Create, FileAccess.Write))
//{
// bf.Serialize(fs, legalNames);
//}
//using (var fs = new FileStream("C:\\temp\\legal_names.txt", FileMode.Open, FileAccess.Read))
//{
// legalNames = (List<Tuple<string, string>>)bf.Deserialize(fs);
//}
//using (var fs = new FileStream("C:\\temp\\legal_dict.txt", FileMode.Create, FileAccess.Write))
//{
// bf.Serialize(fs, legalNameToId);
//}
//using (var fs = new FileStream("C:\\temp\\legal_dict.txt", FileMode.Open, FileAccess.Read))
//{
// legalNameToId = (Dictionary<string, int>)bf.Deserialize(fs);
//}
//using (var fs = new FileStream("C:\\temp\\transliterated_names.txt", FileMode.Create, FileAccess.Write))
//{
// bf.Serialize(fs, transliteratedNames);
//}
//using (var fs = new FileStream("C:\\temp\\transliterated_names.txt", FileMode.Open, FileAccess.Read))
//{
// transliteratedNames = (List<Tuple<string, string>>)bf.Deserialize(fs);
//}
//using (var fs = new FileStream("C:\\temp\\transliterated_dict.txt", FileMode.Create, FileAccess.Write))
//{
// bf.Serialize(fs, transliteratedNameToId);
//}
//using (var fs = new FileStream("C:\\temp\\transliterated_dict.txt", FileMode.Open, FileAccess.Read))
//{
// transliteratedNameToId = (Dictionary<string, int>)bf.Deserialize(fs);
//}
//using (var fs = new FileStream("C:\\temp\\other_names.txt", FileMode.Create, FileAccess.Write))
//{
// bf.Serialize(fs, akaNames);
//}
//using (var fs = new FileStream("C:\\temp\\other_names.txt", FileMode.Open, FileAccess.Read))
//{
// akaNames = (List<Tuple<string, string>>)bf.Deserialize(fs);
//}
//using (var fs = new FileStream("C:\\temp\\other_dict.txt", FileMode.Create, FileAccess.Write))
//{
// bf.Serialize(fs, akaNameToId);
//}
//using (var fs = new FileStream("C:\\temp\\other_dict.txt", FileMode.Open, FileAccess.Read))
//{
// akaNameToId = (Dictionary<string, int>)bf.Deserialize(fs);
//}
using (var fileReader = new StreamReader(filename))
{
Logger.Log("Configuring CSV reader");
var csvReader = new CsvReader(fileReader, CultureInfo.InvariantCulture);
csvReader.Configuration.Delimiter = ";";
csvReader.Configuration.MissingFieldFound = null;
string prevCompanyName = "";
Company targetCompany = null;
Person previousPerson = null;
csvReader.Read();
while (csvReader.Read())
{
var companyName = csvReader.GetField(0); // company name
if (companyName != prevCompanyName)
{
prevCompanyName = companyName;
var standName = Standardize(companyName);
var countryName = csvReader.GetField(4); // company HQ
if (countryName == "Russia")
{
countryName = "Russian Federation";
}
var countryCode = countries.Where(x => x.Name == countryName).Select(x => x.Code).FirstOrDefault();
if (countryCode == null)
{
throw new InvalidOperationException($"No country is found for name \"{countryName}\"");
}
var companyIds = MatchCompanyName(companyName, countryCode);
if (companyIds.Count > 0)
{
foreach (var id in companyIds)
{
dbContext.CompanyMatches.Add(
new CompanyMatch()
{
CompanyId = id,
Name = companyName
});
}
}
else
{
dbContext.CompanyMatches.Add(
new CompanyMatch()
{
CompanyId = null,
Name = companyName
});
}
}
}
}
dbContext.SaveChanges();
dbContext.Dispose();
sw.Stop();
Logger.Log($"Finished successfully in {sw.ElapsedMilliseconds} ms");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
}
private void LoadCountries(SilverContext silverContext, GoldContext goldContext)
{
Logger.Log("Loading countries");
foreach (var country in silverContext.Countries.AsQueryable()
.Include(x => x.CountryAges)
.Include(x => x.CountryDemographics)
.Include(x => x.CountryDisabilities)
.Include(x => x.CountryEconomies)
.Include(x => x.CountryEdus)
.Include(x => x.CountryGenders)
.Include(x => x.CountryPoliticals)
.Include(x => x.CountryRaces)
.Include(x => x.CountryReligions)
.Include(x => x.CountrySexes)
.Include(x => x.CountryUrbans))
{
var newCountry = new Country()
{
Code = country.Code,
Name = country.Name,
};
if (country.CountryAges.Count > 0)
{
var countryAge = country.CountryAges.First();
newCountry.CountryAges.Add(
new DB.Gold.Entities.CountryData.CountryAge()
{
Country = newCountry,
Avg18 = countryAge.Avg18,
Dis19 = countryAge.Dis19,
Dis39 = countryAge.Dis39,
Dis59 = countryAge.Dis59,
Dis70 = countryAge.Dis70,
Disx = countryAge.Disx,
Ministers = countryAge.Ministers,
Parliament = countryAge.Parliament,
});
goldContext.Add(newCountry.CountryAges.First());
}
if (country.CountryDemographics.Count > 0)
{
var countryDemographics = country.CountryDemographics.First();
newCountry.CountryDemographics.Add(
new DB.Gold.Entities.CountryData.CountryDemographic()
{
Country = newCountry,
ImmigrantPercent = countryDemographics.ImmigrantPercent,
ImmigrantPop = countryDemographics.ImmigrantPop,
Population = countryDemographics.Population,
});
goldContext.Add(newCountry.CountryDemographics.First());
}
if (country.CountryDisabilities.Count > 0)
{
var countryDisability = country.CountryDisabilities.First();
newCountry.CountryDisabilities.Add(
new DB.Gold.Entities.CountryData.CountryDisability()
{
Country = newCountry,
Disabled = countryDisability.Disabled,
DiscriminationLaw = countryDisability.DiscriminationLaw,
HealthFundingGdp = countryDisability.HealthFundingGdp,
HealthFundingType = countryDisability.HealthFundingType,
Overweight = countryDisability.Overweight,
});
goldContext.Add(newCountry.CountryDisabilities.First());
}
if (country.CountryEconomies.Count > 0)
{
var countryEconomy = country.CountryEconomies.First();
newCountry.CountryEconomies.Add(
new DB.Gold.Entities.CountryData.CountryEconomy()
{
Country = newCountry,
AvgIncome = countryEconomy.AvgIncome,
EqualityLevel = countryEconomy.EqualityLevel,
FemaleUnemploy = countryEconomy.FemaleUnemploy,
Gdp = countryEconomy.Gdp,
GdpPerCapita = countryEconomy.GdpPerCapita,
GdpWorld = countryEconomy.GdpWorld,
LabourForce = countryEconomy.LabourForce,
LabourForcePercent = countryEconomy.LabourForcePercent,
MaleUnemploy = countryEconomy.MaleUnemploy,
Poor = countryEconomy.Poor,
});
goldContext.Add(newCountry.CountryEconomies.First());
}
if (country.CountryEdus.Count > 0)
{
var countryEdu = country.CountryEdus.First();
newCountry.CountryEdus.Add(
new DB.Gold.Entities.CountryData.CountryEdu()
{
Country = newCountry,
ActualEducation = countryEdu.ActualEducation,
BachelorFemale = countryEdu.BachelorFemale,
BachelorMale = countryEdu.BachelorMale,
BachelorMf = countryEdu.BachelorMf,
ElementaryFemale = countryEdu.ElementaryFemale,
ElementaryMale = countryEdu.ElementaryMale,
ElementaryMf = countryEdu.ElementaryMf,
ExpectedEducation = countryEdu.ExpectedEducation,
HighSchoolFemale = countryEdu.HighSchoolFemale,
HighSchoolMale = countryEdu.HighSchoolMale,
HighSchoolMf = countryEdu.HighSchoolMf,
MasterFemale = countryEdu.MasterFemale,
MasterMale = countryEdu.MasterMale,
MasterMf = countryEdu.MasterMf,
PublicFundFund = countryEdu.PublicFundFund,
PublicFundingGdp = countryEdu.PublicFundingGdp,
TotalMf = countryEdu.TotalMf,
});
goldContext.Add(newCountry.CountryEdus.First());
}
if (country.CountryGenders.Count > 0)
{
var countryGender = country.CountryGenders.First();
newCountry.CountryGenders.Add(
new DB.Gold.Entities.CountryData.CountryGender()
{
Country = newCountry,
FemaleMinisterShare = countryGender.FemaleMinisterShare,
FemaleParliamentShare = countryGender.FemaleParliamentShare,
FemalePop = countryGender.FemalePop,
FemalePromotionPolicy = countryGender.FemalePromotionPolicy,
FemaleWorkForce = countryGender.FemaleWorkForce,
FemaleWorkForcePercent = countryGender.FemaleWorkForcePercent,
FemaleWorkForcePercentPop = countryGender.FemaleWorkForcePercentPop,
GenderEduGap = countryGender.GenderEduGap,
GenderHealthGap = countryGender.GenderHealthGap,
GenderPolGap = countryGender.GenderPolGap,
GenderWorkGap = countryGender.GenderWorkGap,
IncomeGap = countryGender.IncomeGap,
LifeFemale = countryGender.LifeFemale,
LifeMale = countryGender.LifeMale,
MalePop = countryGender.MalePop,
MaterintyLeave = countryGender.MaterintyLeave,
PaternityLeave = countryGender.PaternityLeave,
WomenEdu = countryGender.WomenEdu,
WomenViolence = countryGender.WomenViolence,
});
goldContext.Add(newCountry.CountryGenders.First());
}
if (country.CountryPoliticals.Count > 0)
{
var countryPolitical = country.CountryPoliticals.First();
newCountry.CountryPoliticals.Add(
new DB.Gold.Entities.CountryData.CountryPolitical()
{
Country = newCountry,
Corruption = countryPolitical.Corruption,
Democracy = countryPolitical.Democracy,
FreedomSpeech = countryPolitical.FreedomSpeech,
});
goldContext.Add(newCountry.CountryPoliticals.First());
}
if (country.CountryRaces.Count > 0)
{
var countryRace = country.CountryRaces.First();
newCountry.CountryRaces.Add(
new DB.Gold.Entities.CountryData.CountryRace()
{
Country = newCountry,
Arab = countryRace.Arab,
Asian = countryRace.Asian,
Black = countryRace.Black,
Caucasian = countryRace.Caucasian,
DiscriminationLaw = countryRace.DiscriminationLaw,
Hispanic = countryRace.Hispanic,
Indegineous = countryRace.Indegineous,
});
goldContext.Add(newCountry.CountryRaces.First());
}
if (country.CountryReligions.Count > 0)
{
var countryReligion = country.CountryReligions.First();
newCountry.CountryReligions.Add(
new DB.Gold.Entities.CountryData.CountryReligion()
{
Country = newCountry,
Buddishm = countryReligion.Buddishm,
Christian = countryReligion.Christian,
Freedom = countryReligion.Freedom,
Hindu = countryReligion.Hindu,
Judaism = countryReligion.Judaism,
Muslim = countryReligion.Muslim,
Statereligion = countryReligion.Statereligion,
});
goldContext.Add(newCountry.CountryReligions.First());
}
if (country.CountrySexes.Count > 0)
{
var countrySex = country.CountrySexes.First();
newCountry.CountrySexes.Add(
new DB.Gold.Entities.CountryData.CountrySex()
{
Country = newCountry,
HomosexualPop = countrySex.HomosexualPop,
HomosexualTolerance = countrySex.HomosexualTolerance,
SameAdopt = countrySex.SameAdopt,
SameMarriage = countrySex.SameMarriage,
});
goldContext.Add(newCountry.CountrySexes.First());
}
if (country.CountryUrbans.Count > 0)
{
var countryUrban = country.CountryUrbans.First();
newCountry.CountryUrbans.Add(
new DB.Gold.Entities.CountryData.CountryUrban()
{
Country = newCountry,
CitiesPop = countryUrban.CitiesPop,
});
goldContext.Add(newCountry.CountryUrbans.First());
}
goldContext.Add(newCountry);
}
goldContext.SaveChanges();
var oldCountries = silverContext.Countries.Select(x => new { x.Name, x.Id }).ToDictionary(x => x.Id, x => x.Name);
var newCountries = goldContext.Country.Select(x => new { x.Name, x.Id }).ToDictionary(x => x.Name, x => x.Id);
foreach (var id in oldCountries.Keys)
{
countryMap[id] = newCountries[oldCountries[id]];
}
Logger.Log("Countries loaded");
}
/// <summary>
/// An uploader from Silver to Gold
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void FunctionHandler(ILambdaContext context)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
Logger.Init(UPDATER_NAME);
Logger.Log("Started");
// 1) Clean up Gold
var goldContext = new GoldContext();
goldContext.ChangeTracker.AutoDetectChangesEnabled = false;
goldContext.ChangeTracker.LazyLoadingEnabled = true;
goldContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
Logger.Log("Cleaning up Gold");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE company_name CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE company_operation CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE role CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE person_country CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE person CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE industry_country CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE company_country CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE company_questionnaire CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE company CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE address CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE industry CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_age CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_demographics CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_disability CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_economy CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_edu CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_gender CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_political CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_race CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_religion CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_sex CASCADE");
goldContext.Database.ExecuteSqlRaw("TRUNCATE TABLE country_urban CASCADE");
Logger.Log("Gold cleaned up");
// 2) Load industry and country data
var silverContext = new SilverContext();
silverContext.ChangeTracker.AutoDetectChangesEnabled = false;
silverContext.ChangeTracker.LazyLoadingEnabled = true;
silverContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
LoadCountries(silverContext, goldContext);
LoadIndustries(silverContext, goldContext);
// 3) Loading companies and people
LoadCompanies();
sw.Stop();
Logger.Log($"Finished successfully in {sw.ElapsedMilliseconds} ms");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
}
private void LoadIndustries(SilverContext silverContext, GoldContext goldContext)
{
Logger.Log("Loading industries");
foreach (var industry in silverContext.Industries.AsQueryable()
.Include(x => x.IndustryCountries))
{
var newIndustry = new Industry()
{
Name = industry.Name,
};
if (industry.IndustryCountries.Count > 0)
{
var industryCountry = industry.IndustryCountries.First();
newIndustry.IndustryCountries.Add(
new IndustryCountry()
{
Industry = newIndustry,
AvgPay = industryCountry.AvgPay,
DiPledge = industryCountry.DiPledge,
DisabilitiesPledge = industryCountry.DisabilitiesPledge,
EducationSpend = industryCountry.EducationSpend,
FlexibleHoursPledge = industryCountry.FlexibleHoursPledge,
HarassmentPledge = industryCountry.HarassmentPledge,
IndustryDiversity = industryCountry.IndustryDiversity,
LgbtPledge = industryCountry.LgbtPledge,
MaterintyLeavePledge = industryCountry.MaterintyLeavePledge,
NumEmployees = industryCountry.NumEmployees,
PaternityLeavePledge = industryCountry.PaternityLeavePledge,
RententionRate = industryCountry.RententionRate,
WomenEmployeedPercent = industryCountry.WomenEmployeedPercent,
});
goldContext.Add(newIndustry.IndustryCountries.First());
}
goldContext.Add(newIndustry);
}
foreach (var industryCountry in silverContext.IndustryCountries.AsQueryable()
.Where(x => x.Industry == null))
{
goldContext.Add(new IndustryCountry()
{
AvgPay = industryCountry.AvgPay,
DiPledge = industryCountry.DiPledge,
DisabilitiesPledge = industryCountry.DisabilitiesPledge,
EducationSpend = industryCountry.EducationSpend,
FlexibleHoursPledge = industryCountry.FlexibleHoursPledge,
HarassmentPledge = industryCountry.HarassmentPledge,
IndustryDiversity = industryCountry.IndustryDiversity,
LgbtPledge = industryCountry.LgbtPledge,
MaterintyLeavePledge = industryCountry.MaterintyLeavePledge,
NumEmployees = industryCountry.NumEmployees,
PaternityLeavePledge = industryCountry.PaternityLeavePledge,
RententionRate = industryCountry.RententionRate,
WomenEmployeedPercent = industryCountry.WomenEmployeedPercent,
});
}
goldContext.SaveChanges();
Logger.Log("Industries loaded");
}
private void LoadCompanies()
{
Semaphore s = new Semaphore(0, THREAD_COUNT);
ThreadStart loadCompaniesChunk = () =>
{
try
{
int page = 0;
while (true)
{
page = Interlocked.Increment(ref currentPage);
var silverContext = new SilverContext();
silverContext.ChangeTracker.AutoDetectChangesEnabled = false;
silverContext.ChangeTracker.LazyLoadingEnabled = true;
silverContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var goldContext = new GoldContext();
goldContext.ChangeTracker.AutoDetectChangesEnabled = false;
goldContext.ChangeTracker.LazyLoadingEnabled = true;
goldContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
Logger.Log($"Statrting to load page {page} of companies");
var companies = silverContext.Companies.AsNoTracking()
.Include(x => x.Hq)
.Include(x => x.Legal)
.Include(x => x.CompanyNames)
.Include(x => x.CompanyQuestionnaires)
//.Include(x => x.CompanyOperation)
.Include(x => x.Roles).ThenInclude(x => x.Person).ThenInclude(x => x.PersonCountries)
.Skip(currentPage * PAGE_SIZE).Take(PAGE_SIZE).ToList();
if (companies.Count == 0)
{
break;
}
var companiesToSave = new List <Company>();
var namesToSave = new List <CompanyName>();
var rolesToSave = new List <Role>();
var personsToSave = new List <Person>();
var countriesToSave = new List <PersonCountry>();
foreach (var company in companies)
{
var newCompany = new Company()
{
LegalJurisdiction = company.LegalJurisdiction,
LegalName = company.LegalName,
Lei = company.Lei,
NumEmployees = company.NumEmployees,
Status = company.Status,
};
var hqAddress = new Address()
{
AddressLine = company.Hq.AddressLine,
AddressNumber = company.Hq.AddressNumber,
City = company.Hq.City,
PostalCode = company.Hq.PostalCode,
Region = company.Hq.Region,
};
//hqAddress.CompanyHq.Add(newCompany);
newCompany.Hq = hqAddress;
goldContext.Add(hqAddress);
var legalAddress = new Address()
{
AddressLine = company.Legal.AddressLine,
AddressNumber = company.Legal.AddressNumber,
City = company.Legal.City,
PostalCode = company.Legal.PostalCode,
Region = company.Legal.Region,
};
//legalAddress.CompanyLegal.Add(newCompany);
newCompany.Legal = legalAddress;
goldContext.Add(legalAddress);
foreach (var companyName in company.CompanyNames)
{
var newCompanyName = new CompanyName()
{
Company = newCompany,
Name = companyName.Name,
Type = companyName.Type,
};
newCompany.CompanyNames.Add(newCompanyName);
namesToSave.Add(newCompanyName);
//goldContext.Add(newCompanyName);
}
// todo: add when becomes relevant
/*foreach (var companyOperation in company.CompanyOperation)
* {
* var newCompanyOperation = new CompanyOperation()
* {
* Company = newCompany,
* Name = companyOperation.,
* };
* newCompany.CompanyOperation.Add(newCompanyOperation);
* GoldContext.Add(newCompanyOperation);
* }*/
foreach (var companyQuestion in company.CompanyQuestionnaires)
{
var newCompanyQuestion = new CompanyQuestion()
{
Company = newCompany,
Question = (DB.Gold.Enums.CompanyQuestion)(int) companyQuestion.Question,
Answer = companyQuestion.Answer
};
newCompany.CompanyQuestionnaires.Add(newCompanyQuestion);
goldContext.Add(newCompanyQuestion);
}
foreach (var role in company.Roles)
{
var newRole = new Role()
{
Company = newCompany,
BaseSalary = role.BaseSalary,
IncentiveOptions = role.IncentiveOptions,
RoleType = role.RoleType,
Title = role.Title,
};
var person = role.Person;
var newPerson = new Person()
{
BirthYear = person.BirthYear,
EduInstitute = person.EduInstitute,
EduSubject = person.EduSubject,
Gender = person.Gender,
HighEdu = person.HighEdu,
Married = person.Married,
Name = person.Name,
Picture = person.Picture,
Race = person.Race,
Religion = person.Religion,
Sexuality = person.Sexuality,
BaseSalary = person.BaseSalary,
OtherIncentive = person.OtherIncentive,
};
newPerson.Roles.Add(newRole);
newRole.Person = newPerson;
personsToSave.Add(newPerson);
//goldContext.Add(newPerson);
newCompany.Roles.Add(newRole);
rolesToSave.Add(newRole);
//goldContext.Add(role);
foreach (var nation in person.PersonCountries)
{
countriesToSave.Add(
new PersonCountry()
{
Person = newPerson,
CountryId = countryMap[nation.CountryId.Value]
}
);
}
}
companiesToSave.Add(newCompany);
}
goldContext.SaveChanges();
foreach (var c in companiesToSave)
{
goldContext.Add(c);
}
foreach (var cn in namesToSave)
{
goldContext.Add(cn);
}
foreach (var r in rolesToSave)
{
goldContext.Add(r);
}
foreach (var p in personsToSave)
{
goldContext.Add(p);
}
goldContext.SaveChanges();
foreach (var c in countriesToSave)
{
goldContext.Add(c);
}
goldContext.SaveChanges();
goldContext.Dispose();
}
Logger.Log($"No data for page {page}, finishing loading thread");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
finally
{
s.Release();
}
};
for (int i = 0; i < THREAD_COUNT; i++)
{
new Thread(loadCompaniesChunk).Start();
}
for (int i = 0; i < THREAD_COUNT; i++)
{
s.WaitOne();
}
}
/// <summary>
/// A simple function that takes a string and does a ToUpper
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public void FunctionHandler(ILambdaContext context)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
Logger.Init(TRANSFORMER_NAME);
Logger.Log("Started");
// todo: switch to S3
var filename = "C:\\temp\\country.csv";
File.WriteAllText(filename, File.ReadAllText(filename).Replace(',', '.'));
var str = File.ReadAllText(filename);
str = str.Replace(',', '.');
File.WriteAllText(filename, str);
Logger.Log("Configuring DB context.");
SilverContext dbContext = new SilverContext();
dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
dbContext.ChangeTracker.LazyLoadingEnabled = true;
using (var fileReader = new StreamReader(filename))
{
Logger.Log("Configuring CSV reader");
var csvReader = new CsvReader(fileReader, CultureInfo.InvariantCulture);
csvReader.Configuration.Delimiter = ";";
csvReader.Configuration.MissingFieldFound = null;
csvReader.Configuration.RegisterClassMap <CountryAgeMap>();
csvReader.Configuration.RegisterClassMap <CountryDemographicsMap>();
csvReader.Configuration.RegisterClassMap <CountryDisabilityMap>();
csvReader.Configuration.RegisterClassMap <CountryEconomyMap>();
csvReader.Configuration.RegisterClassMap <CountryEduMap>();
csvReader.Configuration.RegisterClassMap <CountryGenderMap>();
csvReader.Configuration.RegisterClassMap <CountryMap>();
csvReader.Configuration.RegisterClassMap <CountryPoliticalMap>();
csvReader.Configuration.RegisterClassMap <CountryRaceMap>();
csvReader.Configuration.RegisterClassMap <CountryReligionMap>();
csvReader.Configuration.RegisterClassMap <CountrySexMap>();
csvReader.Configuration.RegisterClassMap <CountryUrbanMap>();
while (csvReader.Read())
{
var country = csvReader.GetRecord <Country>();
var countryAge = csvReader.GetRecord <CountryAge>();
countryAge.Country = country;
country.CountryAges.Add(countryAge);
dbContext.Add(countryAge);
var countryDemographic = csvReader.GetRecord <CountryDemographic>();
countryDemographic.Country = country;
country.CountryDemographics.Add(countryDemographic);
dbContext.Add(countryDemographic);
var countryDisability = csvReader.GetRecord <CountryDisability>();
countryDisability.Country = country;
country.CountryDisabilities.Add(countryDisability);
dbContext.Add(countryDisability);
var countryEconomy = csvReader.GetRecord <CountryEconomy>();
countryEconomy.Country = country;
country.CountryEconomies.Add(countryEconomy);
dbContext.Add(countryEconomy);
var countryEdu = csvReader.GetRecord <CountryEdu>();
countryEdu.Country = country;
country.CountryEdus.Add(countryEdu);
dbContext.Add(countryEdu);
var countryGender = csvReader.GetRecord <CountryGender>();
countryGender.Country = country;
country.CountryGenders.Add(countryGender);
dbContext.Add(countryGender);
var countryPolitical = csvReader.GetRecord <CountryPolitical>();
countryPolitical.Country = country;
country.CountryPoliticals.Add(countryPolitical);
dbContext.Add(countryPolitical);
var countryRace = csvReader.GetRecord <CountryRace>();
countryRace.Country = country;
country.CountryRaces.Add(countryRace);
dbContext.Add(countryRace);
var countryReligion = csvReader.GetRecord <CountryReligion>();
countryReligion.Country = country;
country.CountryReligions.Add(countryReligion);
dbContext.Add(countryReligion);
var countrySex = csvReader.GetRecord <CountrySex>();
countrySex.Country = country;
country.CountrySexes.Add(countrySex);
dbContext.Add(countrySex);
var countryUrban = csvReader.GetRecord <CountryUrban>();
countryUrban.Country = country;
country.CountryUrbans.Add(countryUrban);
dbContext.Add(countryUrban);
dbContext.Add(country);
}
}
dbContext.SaveChanges();
dbContext.Dispose();
sw.Stop();
Logger.Log($"Finished successfully in {sw.ElapsedMilliseconds} ms");
}
catch (Exception ex)
{
Logger.Log($"ERROR: Unhandled exception: {ex.ToString()}");
}
}
