// find entities in relations where given entity's primary key is being used as foreign key
public List <string> GetChildEntities(MainTableRowSelectModel model)
{
List <string> allChildEntities = _relations.AsEnumerable()
.Where(r => r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.table)
.Select(r => r.Field <string>(RelationDataColumns.CHILD_TABLE)).Distinct().OrderBy(o => o).ToList();
List <string> childEntities = new List <string>();
if (model.hideChilEntitiesWhenNoData)
{
// if need to hide child entities when no related data found, query each child entities to check if data exists
foreach (string childEntity in allChildEntities)
{
RelatedDataSelectModel fakeModel = new RelatedDataSelectModel {
topN = 1, fromEntity = model.table, toEntity = childEntity,
toEntityType = RelationType.CHILD, keyVals = model.colNameVals
};
List <JQDTFriendlyColumnInfo> columnsForFrontEnd = new List <JQDTFriendlyColumnInfo>();
DataTable dt = GetParentOrChildData(fakeModel, ref columnsForFrontEnd);
if (dt.Rows.Count > 0)
{
childEntities.Add(childEntity);
}
}
}
else
{
childEntities = allChildEntities;
}
return(childEntities);
}
//// returns jquery datatables friendly list of columns
//private List<JQDTFriendlyColumnInfo> JQDTFriendlyColumnList(DataTable dt)
//{
// List<JQDTFriendlyColumnInfo> list = new List<JQDTFriendlyColumnInfo>();
// foreach (DataColumn col in dt.Columns)
// {
// if (Type.GetTypeCode(col.DataType).IsNumericColumn())
// {
// list.Add(new JQDTFriendlyColumnInfo(col.ColumnName, ColumnTypeToCategory(col.DataType) AppConst.JQDT_COL_ALIGN.RIGHT));
// }
// else
// {
// list.Add(new JQDTFriendlyColumnInfo(col.ColumnName));
// }
// }
// return list;
//}
public DataTable GetParentOrChildData(RelatedDataSelectModel model, ref List <JQDTFriendlyColumnInfo> columnsForFrontEnd)
{
// set model.fromEntityForeignKey and remove fk from table name if needed (in case there are multiple fks for same parent)
if (model.toEntityType == RelationType.PARENT && model.toEntity.Contains(PARENT_TABLE_FK_SEPARATOR))
{
var elements = model.toEntity.Split(PARENT_TABLE_FK_SEPARATOR);
model.toEntity = elements[1];
model.fromEntityForeignKey = elements[0];
}
List <JQDTFriendlyColumnInfo> fromEntityCols = _dataAccess.GetColumns(model.fromEntity);
List <JQDTFriendlyColumnInfo> toEntityCols = _dataAccess.GetColumns(model.toEntity);
string criteria = _dataAccess.ColNameValToCriteria(model.keyVals, fromEntityCols);
string sql = _dataAccess.BuildBasicSql("*", model.fromEntity, criteria, fromEntityCols[0].name, model.ascDesc, 1);
DataTable dt = _dataAccess.GetData(sql); // get from table row for passed pk values
List <ColNameValueModel> colVals;
if (model.toEntityType == RelationType.PARENT)
{
colVals = ChildToParentColVals(model, dt);
}
else
{
colVals = ParentToChildColVals(model, dt);
}
string toCiteria = _dataAccess.ColNameValToCriteria(colVals, toEntityCols);
sql = _dataAccess.BuildBasicSql("*", model.toEntity, toCiteria, toEntityCols[0].name, model.ascDesc, model.topN);
DataTable toDt = EntitySqlToDtForFrontEnd(model.toEntity, sql, ref columnsForFrontEnd);
return(toDt);
}
private List <ColNameValueModel> ChildToParentColVals(RelatedDataSelectModel model, DataTable childTable)
{
List <ColNameValueModel> colVals = new List <ColNameValueModel>();
foreach (var match in _relations.AsEnumerable()
.Where(r => r.Field <string>(RelationDataColumns.CHILD_TABLE) == model.fromEntity &&
r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.toEntity))
{
string cNm = match.Field <string>(RelationDataColumns.PARENT_KEY);
string cVal = "";
if (String.IsNullOrEmpty(model.fromEntityForeignKey))
{
cVal = childTable.Rows[0][match.Field <string>(RelationDataColumns.CHILD_KEY)].ToString();
}
else
{
// just in case child table contains multiple fk columns (e.g. billing address id, shipping address id )
// assumed key is made of one column only (non composite)
cVal = childTable.Rows[0][model.fromEntityForeignKey].ToString();
}
if (!colVals.Any(o => o.colName == cNm && o.colValue == cVal))
{
colVals.Add(new ColNameValueModel {
colName = cNm, colValue = cVal
});
}
}
return(colVals);
}
private List <ColNameValueModel> ParentToChildColVals(RelatedDataSelectModel model, DataTable parentTable)
{
List <ColNameValueModel> colVals = new List <ColNameValueModel>();
foreach (var match in _relations.AsEnumerable()
.Where(r => r.Field <string>(RelationDataColumns.PARENT_TABLE) == model.fromEntity &&
r.Field <string>(RelationDataColumns.CHILD_TABLE) == model.toEntity))
{
// search by child key but set parent key because searching in parent
colVals.Add(new ColNameValueModel
{
colName = match.Field <string>(RelationDataColumns.CHILD_KEY),
colValue = parentTable.Rows[0][match.Field <string>(RelationDataColumns.PARENT_KEY)].ToString()
});
}
return(colVals);
}
public IActionResult ParentOrChildGetData([FromBody] RelatedDataSelectModel model)
{
try
{
List <JQDTFriendlyColumnInfo> columnsForFrontEnd = new List <JQDTFriendlyColumnInfo>();
DataTable dt = _businessLayer.GetParentOrChildData(model, ref columnsForFrontEnd);
return(Json(new
{
recordsFiltered = dt.Rows.Count,
recordsTotal = dt.Rows.Count,
data = dt.JQDTFriendlyTableData(),
columns = columnsForFrontEnd,
sortColIndex = 0,
ascDesc = model.ascDesc.ToLower()
}));
}
catch (Exception ex)
{
return(Json(new { error = ex.Message }));
}
}
