/// <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); } }