public static void Write(this QueryElements queryElements, CSideWriter writer)
{
writer.BeginSection("ELEMENTS");
foreach (var queryElement in queryElements)
{
queryElement.Write(writer);
}
writer.EndSection();
}
public Query FilterBetween(QueryColumn column)
{
var table = QueryElements.First(qe => qe.Table.GetAlias() == column.Alias).Table;
var sqlColumn = table.Columns[column.Value];
var parameter = new SqlParameter(sqlColumn.Table.DatabaseDefinition)
{
Name = column.Value
};
sqlColumn.Types.CopyTo(parameter.Types);
var filter = new Filter()
{
Column = column,
Table = table,
Parameter = parameter,
Type = FilterType.Between
};
Filters.Add(filter);
return(this);
}
public static IEnumerable <Invocation> ToInvocations(this QueryElements queryElements) => queryElements.Where(e => e.IndentationLevel.GetValueOrDefault(0) == 0).Select(e => e.ToInvocation());
/// <summary>
/// GetSql
/// </summary>
/// <param name="elementType"></param>
/// <param name="inSelect"></param>
/// <returns></returns>
private static string GetSql(QueryElements elementType, string inSelect)
{
string sql = string.Empty;
switch (elementType)
{
case QueryElements.Deleted:
sql = @"
SELECT DISTINCT
statefacilityidentifier
FROM {0}FRS_Deleted_Facility
WHERE {1} ";
break;
case QueryElements.Facility:
sql = @"
SELECT DISTINCT
f.facilityregistryidentifier,
f.facilitysitename,
f.facilitysitetypename,
f.federalfacilityindicator,
f.triballandindicator,
f.triballandname,
f.congressionaldistrictnumber,
f.legislativedistrictnumber,
f.huccode,
f.locationaddresstext,
f.supplementallocationtext,
f.localityname,
f.countystatefipscode,
f.countyname,
f.stateuspscode,
f.statename,
f.countryname,
f.locationzipcode,
f.locationdescriptiontext,
f.datasourcename,
f.lastreporteddate,
f.statefacilitysystemacronymname,
f.statefacilityidentifier
FROM {0}FRS_FACILITYSITE f
WHERE f.statefacilityidentifier IN ({1})
ORDER BY f.facilitysitename ";
break;
case QueryElements.AltName:
sql = @"
SELECT DISTINCT
a.alternativename,
a.alternativenametypetext,
a.datasourcename,
a.lastreporteddate,
a.statefacilitysystemacronymname,
a.statefacilityidentifier
FROM {0}FRS_AlternateName a
WHERE a.statefacilityidentifier IN ({1})
ORDER BY a.alternativename ";
break;
case QueryElements.EnvInt:
sql = @"
SELECT DISTINCT
e.informationsystemacronymname,
e.informationsystemidentifier,
e.environmentalinteresttypetext,
e.federalstateinterestindicator,
e.environmentalintereststartdate,
e.intereststartdatequalifiertext,
e.environmentalinterestenddate,
e.interestenddatequalifiertext,
e.datasourcename,
e.lastreporteddate,
e.statefacilitysystemacronymname,
e.statefacilityidentifier
FROM {0}FRS_EnvironmentalInterest e
WHERE e.statefacilityidentifier IN ({1})
ORDER BY e.informationsystemacronymname ";
break;
case QueryElements.GeoCoor:
sql = @"
SELECT DISTINCT
g.latitudemeasure,
g.longitudemeasure,
g.horizontalaccuracymeasure,
g.horizontalcollectionmethodtext,
g.horizontalreferencedatumname,
g.sourcemapscalenumber,
g.referencepointtext,
g.datacollectiondate,
g.geometrictypename,
g.locationcommentstext,
g.verticalcollectionmethodtext,
g.verticalmeasure,
g.verticalaccuracymeasure,
g.verticalreferencedatumname,
g.datasourcename,
g.coordinatedatasourcename,
g.subentityidentifier,
g.subentitytypename,
g.statefacilitysystemacronymname,
g.statefacilityidentifier
FROM {0}FRS_GeographicCoordinates g
WHERE g.statefacilityidentifier IN ({1}) ";
break;
case QueryElements.Individual:
sql = @"
SELECT DISTINCT
i.informationsystemacronymname,
i.informationsystemidentifier,
i.environmentalinteresttypetext,
i.affiliationtypetext,
i.affiliationstartdate,
i.affiliationenddate,
i.emailaddresstext,
i.telephonenumber,
i.phoneextension,
i.faxnumber,
i.alternatetelephonenumber,
i.individualfullname,
i.individualtitletext,
i.mailingaddresstext,
i.supplementaladdresstext,
i.mailingaddresscityname,
i.mailingaddressstateuspscode,
i.mailingaddressstatename,
i.mailingaddresscountryname,
i.mailingaddresszipcode,
i.datasourcename,
i.lastreporteddate,
i.statefacilitysystemacronymname,
i.statefacilityidentifier
FROM {0}FRS_Individual i
WHERE i.statefacilityidentifier IN ({1})
ORDER BY i.informationsystemacronymname ";
break;
case QueryElements.MailingAddress:
sql = @"
SELECT DISTINCT
m.affiliationtypetext,
m.mailingaddresstext,
m.supplementaladdresstext,
m.mailingaddresscityname,
m.mailingaddressstateuspscode,
m.mailingaddressstatename,
m.mailingaddresscountryname,
m.mailingaddresszipcode,
m.datasourcename,
m.lastreporteddate,
m.statefacilitysystemacronymname,
m.statefacilityidentifier
FROM {0}FRS_MailingAddress m
WHERE m.statefacilityidentifier IN ({1}) ";
break;
case QueryElements.NAICS:
sql = @"
SELECT DISTINCT
n.informationsystemacronymname,
n.informationsystemidentifier,
n.environmentalinteresttypetext,
n.naicscode,
n.naicsprimaryindicator,
n.datasourcename,
n.lastreporteddate,
n.statefacilitysystemacronymname,
n.statefacilityidentifier
FROM {0}FRS_NAICSCode n
WHERE n.statefacilityidentifier IN ({1})
ORDER BY n.informationsystemacronymname ";
break;
case QueryElements.Org:
sql = @"
SELECT DISTINCT
o.informationsystemacronymname,
o.informationsystemidentifier,
o.environmentalinteresttypetext,
o.affiliationtypetext,
o.affiliationstartdate,
o.affiliationenddate,
o.emailaddresstext,
o.telephonenumber,
o.phoneextension,
o.faxnumber,
o.alternatetelephonenumber,
o.organizationformalname,
o.organizationdunsnumber,
o.organizationtypetext,
o.employeridentifier,
o.statebusinessidentifier,
o.ultimateparentname,
o.ultimateparentdunsnumber,
o.mailingaddresstext,
o.supplementaladdresstext,
o.mailingaddresscityname,
o.mailingaddressstateuspscode,
o.mailingaddressstatename,
o.mailingaddresscountryname,
o.mailingaddresszipcode,
o.datasourcename,
o.lastreporteddate,
o.statefacilitysystemacronymname,
o.statefacilityidentifier
FROM {0}FRS_Organization o
WHERE o.statefacilityidentifier IN ({1})
ORDER BY o.informationsystemacronymname ";
break;
case QueryElements.SIC:
sql = @"
SELECT DISTINCT
s.informationsystemacronymname,
s.informationsystemidentifier,
s.environmentalinteresttypetext,
s.siccode,
s.sicprimaryindicator,
s.datasourcename,
s.lastreporteddate,
s.statefacilitysystemacronymname,
s.statefacilityidentifier
FROM {0}FRS_SICCode s
WHERE s.statefacilityidentifier IN ({1})
ORDER BY s.informationsystemacronymname ";
break;
default:
return(string.Empty);
}
//Format sql on return
return(string.Format(sql, SCHEMA, inSelect));
}
public Query(SqlTable sqlTable, string alias, QueryColumnAliasStrategy queryColumnAliasStrategy, params QueryColumn[] columns)
: base(sqlTable, alias, columns)
{
QueryElements.Add(this);
QueryColumnAliasStrategy = queryColumnAliasStrategy;
}
public Query Join(JoinBase join)
{
Joins.Add(join);
QueryElements.Add(join);
return(this);
}
