// Because of how jqxDataTable works:
// In a data table with a DropDownList, the selected *display name* associated with the *lookup field name* is returned.
// We need to convert this to the non-aliased master table FK ID and lookup the value from the FK table given the name.
// ANNOYINGLY, THIS MEANS THAT THE NAME MUST ALSO BE UNIQUE! This should be acceptable, if not even good practice, but it's still an annoying constraint.
public void FixupLookups(ViewName viewName, Dictionary <string, object> kvParams)
{
ViewInfo view = (ViewInfo)db.GetView(viewName.Value);
// Find all lookup fields:
view.AllFields.Where(f => f.IsFK).ForEach(f =>
{
object val;
TableForeignKey tfk = f.References[0]; // assume only one FK involvement for this field.
// assume a non-clustered fk.
string fkFieldName = tfk.FkPkMap.First().Key; // fk field name
string pkFieldName = tfk.FkPkMap.First().Value; // pk field name of referenced table.
Assert.That(tfk.ValueFields.Count > 0, "View: " + view.Name + " -> To resolve foreign keys, at least one display field must be defined in the foreign key relationship for " + f.TableFieldInfo.TableInfo.Name + "." + fkFieldName + " -> " + tfk.PkTable + "." + pkFieldName);
string aliasedPkValueFieldName = tfk.ValueFields[0];
// Not all FK's in the view may be defined, especially when there are sub-classes of the GenericTableController that
// populate the FK values based on session parameters, for example.
if (view.Exists(aliasedPkValueFieldName))
{
string dealiasedPkValueFieldName = view.GetField(aliasedPkValueFieldName).TableFieldInfo.FieldName;
// Aliases can be null for table joins where we're just getting an ID or other fields that don't map to a lookup that needs to be resolved.
// TODO: What was this issue really?
if (kvParams.TryGetValue(aliasedPkValueFieldName, out val))
{
// Remove the *value field* name
kvParams.Remove(aliasedPkValueFieldName);
// "Please Choose" is text that the jqx dropdown might return, but it seems to return an empty field.
if ((val != null) && (!String.IsNullOrEmpty(val.ToString())) && (!val.ToString().ToLower().BeginsWith("please choose")))
{
// Replace with the non-aliased *FK field* name.
// TODO: This doesn't handle multiple columns referencing the same FK table.
// Now, lookup the value in the FK table. Sigh, this requires a database query. If the jqxDataTable / jqxDropDownList worked correctly with ID's, this wouldn't be necessary!
// Assume one value field.
object id = db.QueryScalar("select ID from " + tfk.PkTable + " where " + dealiasedPkValueFieldName + " = @val", new Dictionary <string, object>()
{
{ "val", val }
});
Assert.That(id != null, "Expected to resolve the foreign key ID for primary key table " + tfk.PkTable + " with display value of " + val.ToString().SingleQuote());
// The update will throw an exception if a null FK is not permitted but the lookup returned a null for id.
kvParams[fkFieldName] = id;
}
else
{
Assert.That(f.TableFieldInfo.IsNullable, "The FK " + fkFieldName + " in the view " + view.Name + " is not marked as nullable. A value is required.");
kvParams[fkFieldName] = null;
}
}
}
});
}