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)); }