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");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "firstname":
query.Select($"{alias}.FirstName");
break;
case "lastname":
query.Select($"{alias}.LastName");
break;
case "address":
var addressAlias = $"{alias}Address";
query.InnerJoin($"Addresses {addressAlias} on {alias}.AddressId = {addressAlias}.Id");
query = new AddressQuery().Build(query, field.Value, addressAlias);
break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <City>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "name":
query.Select($"{alias}.Name");
break;
case "zipcode":
query.Select($"{alias}.ZipCode");
break;
case "country":
var countryAlias = $"{alias}Country";
query.InnerJoin($"Countries {countryAlias} on {alias}.CountryId = {countryAlias}.Id");
query = new CountryQuery().Build(query, field.Value, countryAlias);
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)
{
// Always get the ID of the email
query.Select($"{alias}.Id");
// Tell Dapper where the Email class begins (at the Id we just selected)
query.SplitOn <Email>("Id");
var fields = context.GetSelectedFields();
if (fields.ContainsKey("address"))
{
query.Select($"{alias}.Address");
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Email>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "address": query.Select($"{alias}.Address"); break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Phone>("Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "number": query.Select($"{alias}.Number"); break;
case "type": query.Select($"{alias}.Type"); break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.EmployerDeclaration_Id");
query.SplitOn <EmployerDeclaration>("EmployerDeclaration_Id");
var fields = context.GetSelectedFields();
foreach (var kvp in fields)
{
switch (kvp.Key)
{
case "quarter": query.Select($"{alias}.Quarter"); break;
case "CompanyID": query.Select($"{alias}.CompanyID"); break;
case "EmployerDeclaration_Id": query.Select($"{alias}.EmployerDeclaration_Id"); break;
}
}
return(query);
}
public SqlQueryContext Build(SqlQueryContext query, IHaveSelectionSet context, string alias)
{
query.Select($"{alias}.Id");
query.SplitOn <Country>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "name":
query.Select($"{alias}.Name");
break;
case "isocode":
query.Select($"{alias}.IsoCode");
break;
}
}
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)
{
query.Select($"{alias}.Id");
query.SplitOn <Address>("Id");
foreach (var field in context.GetSelectedFields())
{
switch (field.Key.ToLower())
{
case "street":
query.Select($"{alias}.Street");
break;
case "city":
var cityAlias = $"{alias}City";
query.InnerJoin($"Cities {cityAlias} on {alias}.CityId = {cityAlias}.Id");
query = new CityQuery().Build(query, field.Value, cityAlias);
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);
}