private string GetValueFromForeignKeyColumns(Guid tenantId, string tableName, string prefix, string primaryKey, string columnsName, string resourceId)
{
var targetValue = string.Empty;
var selectQueryBuilder = new SelectQueryBuilder();
selectQueryBuilder.SelectFromTable(tableName, prefix);
selectQueryBuilder.AddWhere(columnsName, Comparison.Equals, resourceId, 1);
var selectQuery = selectQueryBuilder.BuildQuery();
IQueryReview review = new QueryReview();
var targetResult = review.GetResult(tenantId, tableName, selectQuery);
if (targetResult != null && targetResult.Rows.Count > 0)
{
var targetColumn = primaryKey.TrimStart('[').TrimEnd(']');
if (!targetResult.Columns.Contains(targetColumn))
{
return(targetValue);
}
targetValue = targetResult.Rows[0][targetColumn].ToString();
}
return(targetValue);
}
//tested on
//batchtype (26th July 2019),
public dynamic BuildDeleteQuery(Guid tenantId, Guid userId, Guid itemId, string entityName)
{
IMetadataManager entityManager = new MetadataManager.Contracts.MetadataManager();
var columns = entityManager.GetColumnNameByEntityName(entityName, null);
//@todo need to add options based column methods.
if (!columns.Any())
{
return(string.Empty);
}
var query = "";
var tableName = entityManager.GetTableNameByEntityname(entityName);
var primaryKey = entityManager.GetPrimaryKeyByEntityname(entityName);
var selectQueryBuilder = new SelectQueryBuilder();
selectQueryBuilder.SelectFromTable(tableName, string.Empty);
selectQueryBuilder.AddWhere(primaryKey, Comparison.Equals, itemId.ToString(), 1);
var selectQuery = selectQueryBuilder.BuildQuery();
IQueryReview review = new QueryReview();
var targetResult = review.GetResult(tenantId, entityName, selectQuery);
if (targetResult == null || targetResult.Rows.Count == 0)
{
return("");
}
//delete entity
query += GetDeleteQuery(tableName, primaryKey, itemId.ToString());
//delete item
//@todo need to change business constat isPicklist..
var isItem = entityManager.EntityIsAnItem(entityName, BusinessConstant.IsPickList);
if (isItem)
{
query += GetDeleteQuery(ItemHelper.ItemTableName, ItemHelper.ItemTablePrimarykey, itemId.ToString());
}
foreach (var col in columns)
{
if (col.DataType.ToString().ToLower().Equals("picklist") || col.DataType.ToString().ToLower().Equals("lookup"))
{
continue;
}
if (!col.EntityFullName.ToLower().Equals(entityName.ToLower()))
{
continue;
}
var targetColum = col.ColumnName.TrimStart('[').TrimEnd(']');
var targetValue = targetResult.Rows[0][targetColum].ToString();
//inverse matching column
if (!string.IsNullOrEmpty(col.InverseColumnName) && !string.IsNullOrEmpty(col.InverseTableName))
{
query += GetDeleteQuery(col.InverseTableName, col.InverseColumnName, targetValue);
}
//foreign key matching column
if (!string.IsNullOrEmpty(col.ReferenceColumnName) && !string.IsNullOrEmpty(col.ReferenceTableName))
{
query += GetDeleteQuery(col.InverseTableName, col.InverseColumnName, targetValue);
}
}
return(TransactionHelper.BuildQuery(query));
}
