public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Company>("Id");
var fields = context.GetSelectedFields();
if (fields.ContainsKey("name"))
{
query.Select($"{alias}.Name");
}
if (fields.ContainsKey("emails"))
{
var companyEmailAlias = $"{alias}CompanyEmail";
var emailAlias = $"{alias}Email";
query
.LeftJoin($"CompanyEmail {companyEmailAlias} ON {alias}.Id = {companyEmailAlias}.PersonId")
.LeftJoin($"Email {emailAlias} ON {companyEmailAlias}.EmailId = {emailAlias}.Id");
query = emailQueryBuilder.Build(query, fields["emails"], emailAlias);
}
if (fields.ContainsKey("phones"))
{
var companyPhoneAlias = $"{alias}CompanyPhone";
var phoneAlias = $"{alias}Phone";
query
.LeftJoin($"CompanyPhone {companyPhoneAlias} ON {alias}.Id = {companyPhoneAlias}.PersonId")
.LeftJoin($"Phone {phoneAlias} ON {companyPhoneAlias}.PhoneId = {phoneAlias}.Id");
query = phoneQueryBuilder.Build(query, fields["phones"], phoneAlias);
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "firstName": query.Select($"{alias}.FirstName"); break;
case "lastName": query.Select($"{alias}.LastName"); break;
case "emails":
{
var emailAlias = $"{alias}Email";
query.LeftJoin($"Email {emailAlias} ON {alias}.Id = {emailAlias}.PersonId");
query = emailQueryBuilder.Build(query, kvp.Value, emailAlias);
}
break;
case "phones":
{
var phoneAlias = $"{alias}Phone";
query.LeftJoin($"Phone {phoneAlias} ON {alias}.Id = {phoneAlias}.PersonId");
query = phoneQueryBuilder.Build(query, kvp.Value, phoneAlias);
}
break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
var fields = context.GetSelectedFields();
if (fields.ContainsKey("firstName"))
{
query.Select($"{alias}.FirstName");
}
if (fields.ContainsKey("lastName"))
{
query.Select($"{alias}.LastName");
}
if (fields.ContainsKey("companies"))
{
var personCompanies = $"{alias}PersonCompany";
var companyAlias = $"{alias}Company";
query
.LeftJoin($"PersonCompany {personCompanies} ON {alias}.Id = {personCompanies}.PersonId")
.LeftJoin($"Company {companyAlias} ON {personCompanies}.CompanyId = {companyAlias}.Id");
query = emailQueryBuilder.Build(query, fields["companies"], companyAlias);
}
if (fields.ContainsKey("emails"))
{
var personEmailAlias = $"{alias}PersonEmail";
var emailAlias = $"{alias}Email";
query
.LeftJoin($"PersonEmail {personEmailAlias} ON {alias}.Id = {personEmailAlias}.PersonId")
.LeftJoin($"Email {emailAlias} ON {personEmailAlias}.EmailId = {emailAlias}.Id");
query = emailQueryBuilder.Build(query, fields["emails"], emailAlias);
}
if (fields.ContainsKey("phones"))
{
var personPhoneAlias = $"{alias}PersonPhone";
var phoneAlias = $"{alias}Phone";
query
.LeftJoin($"PersonPhone {personPhoneAlias} ON {alias}.Id = {personPhoneAlias}.PersonId")
.LeftJoin($"Phone {phoneAlias} ON {personPhoneAlias}.PhoneId = {phoneAlias}.Id");
query = phoneQueryBuilder.Build(query, fields["phones"], phoneAlias);
}
if (fields.ContainsKey("supervisor"))
{
var supervisorAlias = $"{alias}Supervisor";
query.LeftJoin($"Person {supervisorAlias} ON {alias}.SupervisorId = {supervisorAlias}.Id");
query = Build(query, fields["supervisor"], supervisorAlias);
}
if (fields.ContainsKey("careerCounselor"))
{
var careerCounselorAlias = $"{alias}CareerCounselor";
query.LeftJoin($"Person {careerCounselorAlias} ON {alias}.CareerCounselorId = {careerCounselorAlias}.Id");
query = Build(query, fields["careerCounselor"], careerCounselorAlias);
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Person>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "firstName": query.Select($"{alias}.FirstName"); break;
case "lastName": query.Select($"{alias}.LastName"); break;
case "emails":
{
var emailAlias = $"{alias}Email";
query.LeftJoin($"Email {emailAlias} ON {alias}.Id = {emailAlias}.PersonId");
query = emailQueryBuilder.Build(query, kvp.Value, emailAlias);
}
break;
case "phones":
{
var phoneAlias = $"{alias}Phone";
query.LeftJoin($"Phone {phoneAlias} ON {alias}.Id = {phoneAlias}.PersonId");
query = phoneQueryBuilder.Build(query, kvp.Value, phoneAlias);
}
break;
}
}
if (fields.ContainsKey("supervisor"))
{
var supervisorAlias = $"{alias}Supervisor";
query.LeftJoin($"Person {supervisorAlias} ON {alias}.SupervisorId = {supervisorAlias}.Id");
query = Build(query, fields["supervisor"], supervisorAlias);
}
if (fields.ContainsKey("careerCounselor"))
{
var careerCounselorAlias = $"{alias}CareerCounselor";
query.LeftJoin($"Person {careerCounselorAlias} ON {alias}.CareerCounselorId = {careerCounselorAlias}.Id");
query = Build(query, fields["careerCounselor"], careerCounselorAlias);
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Form_Id");
query.SplitOn <Form>("Form_Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "identification": query.Select($"{alias}.Identification"); break;
case "formCreationDate": query.Select($"{alias}.FormCreationDate"); break;
case "formCreationHour": query.Select($"{alias}.FormCreationHour"); break;
case "attestationStatus": query.Select($"{alias}.AttestationStatus"); break;
case "typeForm": query.Select($"{alias}.TypeForm"); break;
case "employerDeclarations":
{
var employerDeclarationAlias = $"{alias}EmployerDeclaration";
query.LeftJoin($"EmployerDeclaration {employerDeclarationAlias} ON {alias}.Form_Id = {employerDeclarationAlias}.Form_Id");
query = _employerDeclarationBuilder.Build(query, kvp.Value, employerDeclarationAlias);
}
break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
var mergedAlias = $"{alias}Merged";
// Deduplicate the person
query.LeftJoin($"Person {mergedAlias} ON {alias}.MergedToPersonId = {mergedAlias}.MergedToPersonId");
query.Select($"{alias}.Id", $"{alias}.MergedToPersonId");
query.SplitOn <Person>("Id");
var fields = QueryBuilderHelper.CollectFields(context.SelectionSet);
if (QueryBuilderHelper.IsConnection(context.SelectionSet))
{
query.Select($"{alias}.CreateDate");
}
if (fields.ContainsKey("firstName"))
{
query.Select($"{mergedAlias}.FirstName");
}
if (fields.ContainsKey("lastName"))
{
query.Select($"{mergedAlias}.LastName");
}
if (fields.ContainsKey("companies"))
{
var personCompanyAlias = $"{alias}PersonCompany";
var companyAlias = $"{alias}Company";
query
.LeftJoin($"PersonCompany {personCompanyAlias} ON {mergedAlias}.Id = {personCompanyAlias}.PersonId")
.LeftJoin($"Company {companyAlias} ON {personCompanyAlias}.CompanyId = {companyAlias}.Id");
query = companyQueryBuilder.Build(query, fields["companies"], companyAlias);
}
if (fields.ContainsKey("emails"))
{
var personEmailAlias = $"{alias}PersonEmail";
var emailAlias = $"{alias}Email";
query
.LeftJoin($"PersonEmail {personEmailAlias} ON {mergedAlias}.Id = {personEmailAlias}.PersonId")
.LeftJoin($"Email {emailAlias} ON {personEmailAlias}.EmailId = {emailAlias}.Id");
query = emailQueryBuilder.Build(query, fields["emails"], emailAlias);
}
if (fields.ContainsKey("phones"))
{
var personPhoneAlias = $"{alias}PersonPhone";
var phoneAlias = $"{alias}Phone";
query
.LeftJoin($"PersonPhone {personPhoneAlias} ON {mergedAlias}.Id = {personPhoneAlias}.PersonId")
.LeftJoin($"Phone {phoneAlias} ON {personPhoneAlias}.PhoneId = {phoneAlias}.Id");
query = phoneQueryBuilder.Build(query, fields["phones"], phoneAlias);
}
if (fields.ContainsKey("supervisor"))
{
var supervisorAlias = $"{alias}Supervisor";
query.LeftJoin($"Person {supervisorAlias} ON {mergedAlias}.SupervisorId = {supervisorAlias}.Id");
query = Build(query, fields["supervisor"], supervisorAlias);
}
if (fields.ContainsKey("careerCounselor"))
{
var careerCounselorAlias = $"{alias}CareerCounselor";
query.LeftJoin($"Person {careerCounselorAlias} ON {mergedAlias}.CareerCounselorId = {careerCounselorAlias}.Id");
query = Build(query, fields["careerCounselor"], careerCounselorAlias);
}
return(query);
}
