/// <summary>
/// Hack the query
/// </summary>
public bool HackQuery(QueryBuilder builder, SqlStatement sqlStatement, SqlStatement whereClause, Type tmodel, PropertyInfo property, string queryPrefix, QueryPredicate predicate, object values, IEnumerable <TableMapping> scopedTables, params KeyValuePair <String, object>[] queryFilter)
{
String cmpTblType = String.Empty, valTblType = String.Empty, keyName = String.Empty;
Type guardType = null, componentType = null;
// We can attempt to hack the address
if (typeof(EntityAddress).IsAssignableFrom(tmodel))
{
cmpTblType = "ent_addr_cmp_tbl";
valTblType = "ent_addr_cmp_val_tbl";
guardType = typeof(AddressComponentKeys);
componentType = typeof(EntityAddressComponent);
keyName = "addr_id";
}
else if (typeof(EntityName).IsAssignableFrom(tmodel))
{
cmpTblType = "ent_name_cmp_tbl";
valTblType = "phon_val_tbl";
guardType = typeof(NameComponentKeys);
componentType = typeof(EntityNameComponent);
keyName = "name_id";
}
else
{
return(false);
}
// Not applicable for us if
// - Not a name or address
// - Predicate is not component.value
// - There is already other where clause stuff
if (guardType == null ||
predicate.Path != "component" ||
predicate.SubPath != "value" ||
!String.IsNullOrEmpty(whereClause.SQL))
{
return(false);
}
whereClause.And($" {keyName} IN (SELECT {keyName} FROM {cmpTblType} AS {queryPrefix}{cmpTblType} ");
// Filter through other name or address components which may be in the query at this query level
List <String> componentTypeGuard = new List <String>();
var localChar = 'a';
int vCount = 0;
foreach (var itm in queryFilter)
{
var pred = QueryPredicate.Parse(itm.Key);
String guardFilter = String.Empty;
// Do we have a guard for address?
if (!String.IsNullOrEmpty(pred.Guard))
{
// Translate Guards to UUIDs
var guards = pred.Guard.Split('|');
for (int i = 0; i < guards.Length; i++)
{
guards[i] = guardType.GetField(guards[i]).GetValue(null).ToString();
}
if (guards.Any(o => o == null))
{
return(false);
}
// Add to where clause
componentTypeGuard.AddRange(guards);
guardFilter = $"AND {queryPrefix}{cmpTblType}.typ_cd_id IN ({String.Join(",", guards.Select(o => $"'{o}'"))})";
}
// Filter on the component value
var value = itm.Value;
if (value is String)
{
value = new List <Object>()
{
value
}
}
;
var qValues = value as List <Object>;
vCount++;
whereClause.Append($"LEFT JOIN (SELECT val_seq_id FROM {valTblType} AS {queryPrefix}{valTblType} WHERE ");
whereClause.Append(builder.CreateSqlPredicate($"{queryPrefix}{valTblType}", "val", componentType.GetProperty("Value"), qValues));
whereClause.Append($") AS {queryPrefix}{valTblType}_{localChar} ON ({queryPrefix}{valTblType}_{localChar++}.val_seq_id = {queryPrefix}{cmpTblType}.val_seq_id {guardFilter})");
}
whereClause.Append($" WHERE ");
if (componentTypeGuard.Count > 0)
{
whereClause.Append($"{queryPrefix}{cmpTblType}.typ_cd_id IN ({String.Join(",", componentTypeGuard.Select(o => $"'{o}'"))}) AND ");
}
whereClause.Append("(");
// Ensure that the left joined objects exist
for (char s = 'a'; s < localChar; s++)
{
whereClause.Append($" {queryPrefix}{valTblType}_{s}.val_seq_id IS NOT NULL ").Append("OR");
}
whereClause.RemoveLast();
whereClause.Append($") GROUP BY {keyName} HAVING COUNT(DISTINCT {queryPrefix}{cmpTblType}.cmp_id) >= {vCount})");
return(true);
}
}
/// <summary>
/// Hack the query
/// </summary>
public bool HackQuery(QueryBuilder builder, SqlStatement sqlStatement, SqlStatement whereClause, Type tmodel, PropertyInfo property, string queryPrefix, QueryPredicate predicate, object values, IEnumerable <TableMapping> scopedTables, params KeyValuePair <String, object>[] queryFilter)
{
String cmpTblType = String.Empty, valTblType = String.Empty, keyName = String.Empty;
Type guardType = null, componentType = null;
// We can attempt to hack the address
if (typeof(EntityAddress).IsAssignableFrom(tmodel))
{
cmpTblType = "ent_addr_cmp_tbl";
valTblType = "ent_addr_cmp_val_tbl";
guardType = typeof(AddressComponentKeys);
componentType = typeof(EntityAddressComponent);
keyName = "addr_id";
}
else if (typeof(EntityName).IsAssignableFrom(tmodel))
{
cmpTblType = "ent_name_cmp_tbl";
valTblType = "phon_val_tbl";
guardType = typeof(NameComponentKeys);
componentType = typeof(EntityNameComponent);
keyName = "name_id";
}
else
{
return(false);
}
// Not applicable for us if
// - Not a name or address
// - Predicate is not component.value
// - There is already other where clause stuff
if (guardType == null ||
predicate.Path != "component" ||
predicate.SubPath != "value" ||
!String.IsNullOrEmpty(whereClause.SQL))
{
return(false);
}
// Pop the last statement off
// var fromClause = sqlStatement.RemoveLast();
var subQueryAlias = $"{queryPrefix}{scopedTables.First().TableName}";
whereClause.And($" {subQueryAlias}.{keyName} IN (");
foreach (var itm in queryFilter)
{
var pred = QueryPredicate.Parse(itm.Key);
String guardFilter = String.Empty;
// Do we have a guard for address?
if (!String.IsNullOrEmpty(pred.Guard))
{
// Translate Guards to UUIDs
var guards = pred.Guard.Split('|');
for (int i = 0; i < guards.Length; i++)
{
guards[i] = guardType.GetField(guards[i]).GetValue(null).ToString();
}
if (guards.Any(o => o == null))
{
return(false);
}
// Add to where clause
guardFilter = $"AND {queryPrefix}{cmpTblType}.typ_cd_id IN ({String.Join(",", guards.Select(o => $"'{o}'"))}) ";
}
// Filter on the component value
var value = itm.Value;
if (value is String)
{
value = new List <Object>()
{
value
}
}
;
var qValues = value as List <Object>;
// Filter based on type and prefix :)
whereClause
.Append($" SELECT {queryPrefix}{cmpTblType}.{keyName} ")
.Append($" FROM {cmpTblType} AS {queryPrefix}{cmpTblType} ")
.Append(" WHERE ")
.Append($" val_seq_id IN (SELECT val_seq_id FROM {valTblType} WHERE ")
.Append(builder.CreateSqlPredicate($"{valTblType}", "val", componentType.GetProperty("Value"), qValues))
.Append(") ")
.Append(guardFilter)
.Append(" INTERSECT ");
}
whereClause.RemoveLast();
whereClause.Append($") ");
whereClause.And($"{subQueryAlias}.obslt_vrsn_seq_id IS NULL ");
return(true);
}
}