private string BuildInsertQueryV1(Guid itemId, string entityName, List <GroupedColumns> matchingColumns)
{
//create lower tables...
Dictionary <string, string> executedTables = new Dictionary <string, string>();
var query = "";
var tablesWhoHasForeignKeyReference = matchingColumns.Where(t => t.NeedToUpdateColumn != null).ToList();
if (tablesWhoHasForeignKeyReference.Any())
{
foreach (var table in tablesWhoHasForeignKeyReference)
{
query += GetQueryStr(table);
StoreExecutedTables(executedTables, table);
}
}
//create item table..
var itemTable = matchingColumns.FirstOrDefault(t => t.EntityFullName.ToLower().Equals(ItemHelper.ItemClassName.ToLower()));
if (itemTable != null)
{
query += GetQueryStr(itemTable);
StoreExecutedTables(executedTables, itemTable);
}
//create other tables..
foreach (var item in matchingColumns)
{
var clientName = (string.IsNullOrEmpty(item.ClientName)) ? item.EntityFullName : item.ClientName;
var isExecuted = executedTables.Where(t => t.Key.Equals(clientName)).ToList();
if (isExecuted.Any())
{
continue;
}
query += GetQueryStr(item);
}
// add relateions
foreach (var table in tablesWhoHasForeignKeyReference)
{
if (table.NeedToUpdateColumn == null)
{
continue;
}
var columnWithValue = new Dictionary <string, string>();
UpdateQueryBuilder updateQuery = new UpdateQueryBuilder();
columnWithValue.Add(table.NeedToUpdateColumn.ColumnName, table.Id.ToString());
updateQuery.AddTable(table.NeedToUpdateColumn.TableName, columnWithValue);
var targetRow = matchingColumns.FirstOrDefault(t => t.EntityFullName.ToLower().Equals(table.NeedToUpdateColumn.EntityFullName.ToLower()));
if (targetRow == null)
{
continue;
}
updateQuery.AddWhere(table.NeedToUpdateColumn.PrimaryKey, Comparison.Equals, targetRow.Id.ToString(), 1);
query += updateQuery.BuildQuery();
}
IMetadataManager iMetadataManager = new MetadataManager.Contracts.MetadataManager();
var triggers = iMetadataManager.GetTriggerProperties(entityName);
if (triggers.Any())
{
var singletonTrigger = triggers[0];
var bodyProp = singletonTrigger.GetBody();
var search = matchingColumns.FirstOrDefault(t => t.EntityFullName.ToLower().Equals(entityName.ToLower()));
if (search == null)
{
return(TransactionHelper.BuildQuery(query));
}
{
var payload = bodyProp.Select(item => search.Columns.FirstOrDefault(t => t.FieldName.ToLower().Equals(item.ToLower()))).Where(matching => matching != null).ToDictionary <ColumnAndField, string, string>(matching => matching.ColumnName, matching => matching.Value);
if (!payload.Any())
{
return(TransactionHelper.BuildQuery(query));
}
var triggerEngine = new TriggerEngine();
var triggerExecutionPayload = new TriggerExecutionPayload
{
PayloadObj = payload,
ConditionalValue = itemId.ToString()
};
var triggerQuery = triggerEngine.GetQuery(triggers, triggerExecutionPayload);
if (!string.IsNullOrEmpty(triggerQuery))
{
query += triggerQuery;
}
}
}
//------------
return(TransactionHelper.BuildQuery(query));
}
//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));
}