private static void associateCodedChild(ITable codeTable, ITable table2, List <Join> possibleJoins, List <ITable> existingTables)
{
// special-case code for code_value table ... it doesn't have a foreign key defined for coded values!
if (codeTable.TableName.ToLower() == "code_value")
{
foreach (var bf in table2.Mappings)
{
if (bf.TableFieldName.ToLower().EndsWith("_code"))
{
// assume it's a coded value we're joining by...
var fieldA = codeTable.GetField("value");
if (!AlreadyReferencingTable(bf, codeTable))
{
var j = new Join {
JoinType = JoinType.Left
};
if (existingTables.Contains(codeTable))
{
j._fromTable = null;
j._toTable = table2;
}
else
{
j._fromTable = codeTable;
j._toTable = table2;
}
j.Conditionals.Add(new Conditional {
FieldA = fieldA, FieldB = bf, Comparator = "=", Operator = null
});
AddReferencingFieldProperty(codeTable, bf);
addLanguageConditionalIfNeeded(j);
possibleJoins.Add(j);
}
}
}
}
}
/// <summary>
/// Determines which Select is the proper one, then adds the field to it, and corresponding table if need be. Also adds to Query Fields collection if needed. Errors if no relationship exists between existing tables and the one to which the new field belongs
/// </summary>
/// <param name="charIndex"></param>
/// <param name="fm"></param>
/// <returns></returns>
public string AddField(int charIndex, IField fm, bool errorOnMissingRelationship, bool errorOnMultipleRelationships, Join joinToUse)
{
var stmt = GetStatement(charIndex);
if (stmt == null)
{
stmt = new Select(null, DataConnectionSpec, LanguageID, errorOnMissingRelationship, errorOnMultipleRelationships);
Selects.Add(stmt);
}
var fld = stmt.AddField(fm, errorOnMissingRelationship, errorOnMultipleRelationships, joinToUse);
if (GetTable(fld.Table.TableName) == null)
{
this.Tables.Add(fld.Table);
}
if (Selects[0] == stmt)
{
// only add fields from first select
if (GetField(fld.DataviewFieldName) == null)
{
this.Fields.Add(fld);
}
}
return(Regenerate(errorOnMissingRelationship, errorOnMultipleRelationships));
}
private static void associateChildAndChild(ITable child1, ITable child2, List <Join> possibleJoins, List <ITable> existingTables)
{
var ppk = child2.GetField(child2.PrimaryKeyFieldName);
var cpk = child1.GetField(child1.PrimaryKeyFieldName);
foreach (var pfk in child2.ForeignKeys)
{
foreach (var cfk in child1.ForeignKeys)
{
if (String.Compare(cfk.TableFieldName, pfk.TableFieldName, true) == 0 ||
cfk.TableFieldName.ToLower().EndsWith(pfk.TableFieldName.ToLower()) ||
pfk.TableFieldName.ToLower().EndsWith(cfk.TableFieldName.ToLower())
)
{
if (!cfk.IsAudit)
{
// since field names match exactly or one field completely ends with another one, we'll assume that they represent the same data
// and can therefore be joined upon
var fieldA = child1.GetField(cfk.TableFieldName);
var fieldB = child2.GetField(pfk.TableFieldName);
if (!AlreadyReferencingTable(fieldA, child2) && !AlreadyReferencingTable(fieldB, child1))
{
var j = new Join {
JoinType = JoinType.Left
};
if (existingTables.Contains(child1))
{
j._fromTable = child1;
j._toTable = child2;
}
else
{
j._fromTable = child2;
j._toTable = child1;
}
j.Conditionals.Add(new Conditional {
FieldA = fieldA, FieldB = fieldB, Comparator = "=", Operator = null
});
AddReferencingFieldProperty(child2, fieldA);
AddReferencingFieldProperty(child1, fieldB);
addLanguageConditionalIfNeeded(j);
possibleJoins.Add(j);
}
}
//} else if (cfk.TableFieldName.ToLower().EndsWith(ppk.TableFieldName.ToLower())) {
// if (!cfk.IsAudit) {
// // since field names match exactly or one field completely ends with another one, we'll assume that they represent the same data
// // and can therefore be joined upon
// var fieldA = child1.GetField(cfk.TableFieldName);
// var fieldB = child2.GetField(ppk.TableFieldName);
// if (!alreadyReferencingTable(fieldA) && !alreadyReferencingTable(fieldB)) {
// var j = new Join { _fromTable = child2, _toTable = child1, JoinType = JoinType.Left };
// j.Conditionals.Add(new Conditional { FieldA = fieldA, FieldB = fieldB, Comparator = "=", Operator = null });
// addReferencingFieldProperty(child2, fieldA);
// addLanguageConditionalIfNeeded(j);
// possibleJoins.Add(j);
// }
// }
}
else if (pfk.TableFieldName.ToLower().EndsWith(cpk.TableFieldName.ToLower()))
{
if (!pfk.IsAudit)
{
// since field names match exactly or one field completely ends with another one, we'll assume that they represent the same data
// and can therefore be joined upon
var fieldA = child1.GetField(cpk.TableFieldName);
var fieldB = child2.GetField(pfk.TableFieldName);
if (!AlreadyReferencingTable(fieldA, child2) && !AlreadyReferencingTable(fieldB, child1))
{
var j = new Join {
JoinType = JoinType.Left
};
if (existingTables.Contains(child2))
{
j._fromTable = child2;
j._toTable = child1;
}
else
{
j._fromTable = child1;
j._toTable = child2;
}
j.Conditionals.Add(new Conditional {
FieldA = fieldA, FieldB = fieldB, Comparator = "=", Operator = null
});
AddReferencingFieldProperty(child2, fieldA);
AddReferencingFieldProperty(child1, fieldB);
addLanguageConditionalIfNeeded(j);
possibleJoins.Add(j);
}
}
}
}
}
}
/// <summary>
/// Determines which Select is the proper one, then adds the table to it. Also adds to Query Tables collection if needed. Optionally errors if no relationship exists between existing tables and the new one.
/// </summary>
/// <param name="charIndex"></param>
/// <param name="fmt"></param>
/// <param name="errorOnMissingRelationship"></param>
/// <returns></returns>
public string AddTable(int charIndex, ITable fmt, bool errorOnMissingRelationship, bool errorOnMultipleRelationships, Join joinToUse)
{
var stmt = GetStatement(charIndex);
if (stmt == null)
{
stmt = new Select(null, DataConnectionSpec, LanguageID, errorOnMissingRelationship, errorOnMultipleRelationships);
Selects.Add(stmt);
}
var tbl = stmt.AddTable(fmt, errorOnMissingRelationship, errorOnMultipleRelationships, joinToUse);
if (GetTable(Toolkit.Coalesce(tbl.AliasName, tbl.TableName) as string) == null)
{
this.Tables.Add(tbl);
}
if (stmt == Selects[0])
{
foreach (var fm in stmt.Fields)
{
if (GetField(fm.DataviewFieldName) == null)
{
this.Fields.Add(fm);
}
}
}
return(Regenerate(errorOnMissingRelationship, errorOnMultipleRelationships));
}
