public static bool updateOrNewConfig(string key, string value)
{
MysqlDatas data = getValue(key);
if (!data.hasRows)
{
InsertQueryBuilder query = new InsertQueryBuilder();
query.Table = _table;
query.addValue("config_key", key);
query.addValue("config_value", value);
query.addValue("created_at", Helper.GetDatabaseDateFormat());
string cmd = query.BuildQuery();
return(Utils.Mysql.execute(cmd));
}
else
{
UpdateQueryBuilder query = new UpdateQueryBuilder();
query.Table = _table;
query.addSet("config_value", value);
query.AddWhere("config_key", Comparison.Equals, key);
string cmd = query.BuildQuery();
return(Utils.Mysql.execute(cmd));
}
return(false);
}
private bool UpdateValueInForeignTable(Guid tenantId, string tableName, string entityPrefix, string primaryKey, List <ColumnAndField> matchingColumns, string primaryValue)
{
var queryBuilder = new InsertQueryBuilder();
var insertColumns = new Dictionary <string, string> ();
foreach (var match in matchingColumns)
{
if (match.TableName.Equals(tableName) && match.EntityPrefix.Equals(entityPrefix))
{
if (match.Value != null)
{
insertColumns.Add(match.ColumnName, match.Value.ToString());
}
}
}
if (!insertColumns.Any())
{
return(true);
}
UpdateQueryBuilder query = new UpdateQueryBuilder();
query.AddTable(tableName, insertColumns);
query.AddWhere(primaryKey, Comparison.Equals, primaryValue.ToString(), 1);
var updateQuery = query.BuildQuery();
IQueryAdmin admin = new QueryAdmin();
var status = admin.UpdateResult(tenantId, "inverse", updateQuery);
return(status);
}
public TriggerExecutionMessage ExecuteTrigger(TriggerExecutionPayload payload)
{
var updateQuery = new UpdateQueryBuilder();
var columnWithValue = new Dictionary <string, string>();
var value = payload.PayloadObj.Where(item => !string.IsNullOrEmpty(item.Value)).Aggregate(string.Empty, (current, item) => current + (item.Value + " "));
columnWithValue.Add("[Name]", value.TrimEnd());
updateQuery.AddTable("[dbo].[Item]", columnWithValue);
updateQuery.AddWhere("[Id]", Comparison.Equals, payload.ConditionalValue);
var queryRes = updateQuery.BuildQuery();
var message = new TriggerExecutionMessage
{
Message = queryRes
};
return(message);
}
public static bool deleteValue(int id)
{
DeleteQueryBuilder query = new DeleteQueryBuilder();
query.Table = _table;
query.AddWhere("id", Comparison.Equals, id);
string cmd = query.BuildQuery();
bool result = Utils.Mysql.execute(cmd);
if (result)
{
UpdateQueryBuilder update = new UpdateQueryBuilder();
update.Table = "users";
update.addSet("faculty", "0");
update.AddWhere("faculty", Comparison.Equals, id);
cmd = update.BuildQuery();
Utils.Mysql.execute(cmd);
}
return(result);
}
public static bool blockedUser(int id)
{
/* Hard Delete */
/*
* DeleteQueryBuilder query = new DeleteQueryBuilder();
* query.Table = _table;
* query.AddWhere("id", Comparison.Equals, id);
* string cmd = query.BuildQuery();
* bool result = Mysql.execute(cmd);
* return result;
*/
/*Soft Delete*/
UpdateQueryBuilder query = new UpdateQueryBuilder();
query.Table = _table;
query.addSet("status", "blocked");
query.AddWhere("id", Comparison.Equals, id);
string cmd = query.BuildQuery();
bool result = Mysql.execute(cmd);
return(result);
}
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));
}