public string GetRelationshipQuery(IRelationshipMetadata rel, List <IDataItem> parents)
{
//base query
StringBuilder sb = new StringBuilder("SELECT");
//get base query
sb.AppendFormat(" {0}", rel.RelationTable.Query);
//mod
sb.AppendFormat(" WHERE {0} % {1} = {2}", rel.RelationTable.ModField, Bucket.Catalog.ComputeNode.GlobalBucketCount, Bucket.BucketMod);
//base filter
if (!string.IsNullOrWhiteSpace(rel.RelationTable.Filter))
{
sb.AppendFormat(" AND ({0})", rel.RelationTable.Filter);
}
//handle deleted
if (!string.IsNullOrWhiteSpace(rel.RelationTable.SaturationDeleteField))
{
IColumnMetadata col_delete = rel.RelationTable.ColumnsMetadata.Values.Where(c => c.FieldMapping == rel.RelationTable.SaturationDeleteField).FirstOrDefault();
if (col_delete.ColumnType == typeof(DateTime))
{
sb.AppendFormat(
" AND {0} {1} {2} {3}",
col_delete.ColumnName,
Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator),
((DateTime)rel.RelationTable.SaturationDeleteValue).ToShortDateString(),
((DateTime)rel.RelationTable.SaturationDeleteValue).ToShortTimeString());
}
else if (col_delete.ColumnType == typeof(string))
{
sb.AppendFormat(
" AND {0} {1} '{2}'",
col_delete.ColumnName,
Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator),
rel.RelationTable.SaturationDeleteValue.ToString());
}
else if (col_delete.ColumnType == typeof(bool))
{
sb.AppendFormat(
" AND {0} {1} {2}",
col_delete.ColumnName,
Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator),
((bool)rel.RelationTable.SaturationDeleteValue) ? 1 : 0);
}
else
{
sb.AppendFormat(
" AND {0} {1} {2}",
col_delete.ColumnName,
Comparators.GetNegatedComparator(rel.RelationTable.SaturationDeleteComparator),
rel.RelationTable.SaturationDeleteValue);
}
}
//handle parent relation
StringBuilder parent_keys = new StringBuilder();
foreach (var item in parents)
{
object parent_key = item.GetType().GetField(rel.ParentField).GetValue(item);
if (parent_keys.Length == 0)
{
parent_keys.AppendFormat("{0}", parent_key);
}
else
{
parent_keys.AppendFormat(",{0}", parent_key);
}
}
IColumnMetadata col_parent = rel.RelationTable.ColumnsMetadata.Values.Where(c => c.FieldMapping == rel.ParentRelationshipField).FirstOrDefault();
sb.AppendFormat(" AND {0} in ({1})", col_parent.ColumnName, parent_keys.ToString());
//order by parent key
sb.AppendFormat(" ORDER BY {0}", col_parent.ColumnName);
return(sb.ToString());
}
