public Guid?GetEntityID(sysBpmsEntityDef entityDef, sysBpmsVariable variable, List <QueryModel> additionalParams)
{
string whereClause = VariableEngine.GenerateWhereClause(variable);
string orderClause = VariableEngine.GenerateOrderClause(variable);
//get sql query parameter from additionalParams
List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList();
//add variable dependies to whereclause which have relation with current variable.
this.AddDependencyClause(variable, queryParams, null, ref whereClause, additionalParams);
string sqlQuery = $" select {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} {whereClause} {orderClause} ";
this.AddDefaultParams(sqlQuery, queryParams);
this.AddVariableParameters(ref sqlQuery, queryParams);
queryParams = queryParams.Where(c => sqlQuery.Contains(c.ParameterName)).ToList();
DataTable dataTable = new DataBaseQueryService(this.UnitOfWork).GetBySqlQuery(sqlQuery, true, null, queryParams.ToArray());
if (dataTable.Rows.Count > 0)
{
return(Guid.Parse(dataTable.Rows[0][0].ToString()));
}
return(null);
}
/// <param name="containerQuery">It is generally used in combosearch which add a parent query that filter table's rows according to query parameter and text field</param>
/// <param name="includes">It is used in List variable in DataGrid and chart is each item in array is like (Product.Name,Order.Name)</param>
public DataTable GetEntity(sysBpmsEntityDef entityDef, sysBpmsVariable variable, List <QueryModel> additionalParams, PagingProperties currentPaging, string containerQuery = null, string[] includes = null)
{
string columnName = string.Empty;
string includesQuery = string.Empty;
switch ((sysBpmsVariable.e_VarTypeLU)variable.VarTypeLU)
{
case sysBpmsVariable.e_VarTypeLU.Integer:
case sysBpmsVariable.e_VarTypeLU.String:
case sysBpmsVariable.e_VarTypeLU.Uniqueidentifier:
case sysBpmsVariable.e_VarTypeLU.Decimal:
case sysBpmsVariable.e_VarTypeLU.DateTime:
case sysBpmsVariable.e_VarTypeLU.Boolean:
columnName = variable.FieldName;
break;
case sysBpmsVariable.e_VarTypeLU.List when variable.RelationTypeLU == (int)sysBpmsVariable.e_RelationTypeLU.Entity && includes?.Any() == true:
case sysBpmsVariable.e_VarTypeLU.Object when includes?.Any() == true:
EntityDefService entityDefService = new EntityDefService(base.UnitOfWork);
foreach (var p in entityDef.Properties.Where(c => c.DbType == EntityPropertyModel.e_dbType.Entity && includes.Any(d => d.Contains(c.Name))))
{
string foreingName = entityDefService.GetInfo(p.RelationToEntityID.Value).FormattedTableName;
string includesColumnNames = string.Join(",", includes.Where(d => d.Split('.')[0] == p.Name).Select(c => $"{c.Split('.')[1]} as {p.Name}__{c.Split('.')[1]}"));
if (!includesColumnNames.Contains($"{p.Name}__ID"))
{
includesColumnNames += "," + $"ID as {p.Name}__ID";
}
includesQuery += $" left join (select {includesColumnNames} from {foreingName}) {foreingName} on {entityDef.FormattedTableName}.{p.Name}={foreingName}.{p.Name}__ID " + Environment.NewLine;
}
break;
}
columnName = string.IsNullOrWhiteSpace(columnName) ? "*" : columnName;
string whereClause = VariableEngine.GenerateWhereClause(variable);
string orderClause = VariableEngine.GenerateOrderClause(variable);
//get sql query parameter from additionalParams
List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList();
//add variable dependies to whereclause which have relation with current variable.
this.AddDependencyClause(variable, queryParams, null, ref whereClause, additionalParams);
string sqlQuery = $" select {columnName} from {entityDef.FormattedTableName} {includesQuery} {whereClause} {orderClause} ";
//If containerQuery is not null
if (!string.IsNullOrWhiteSpace(containerQuery))
{
sqlQuery = (sqlQuery.Contains("order") && (!sqlQuery.ToLower().Contains("top") && !sqlQuery.ToLower().Contains("offset"))) ? $"{sqlQuery} OFFSET 0 ROWS " : sqlQuery;
sqlQuery = string.Format(containerQuery, sqlQuery);
}
this.AddDefaultParams(sqlQuery, queryParams);
this.AddVariableParameters(ref sqlQuery, queryParams);
queryParams = queryParams.Where(c => sqlQuery.Contains(c.ParameterName)).ToList();
return(new DataBaseQueryService(this.UnitOfWork).GetBySqlQuery(sqlQuery, true, currentPaging, queryParams.ToArray()));
}
/// <summary>
/// this method save entityDef after form post action.
/// </summary>
public Tuple <ResultOperation, Guid?> SaveIntoDataBase(sysBpmsEntityDef entityDef, sysBpmsVariable variable, DataModel dataModel, List <QueryModel> additionalParams, Dictionary <string, DataModel> allSavedEntities)
{
ResultOperation resultOperation = new ResultOperation();
List <SqlParameter> columnParams = this.GetColumsAsParams(entityDef, dataModel.ToList(), variable);
string whereClause = VariableEngine.GenerateWhereClause(variable);
string orderClause = VariableEngine.GenerateOrderClause(variable);
//get sql query parameter from additionalParams
List <SqlParameter> queryParams = QueryModel.GetSqlParameter(base.GetAllQueryModels(additionalParams) ?? new List <QueryModel>()).ToList();
//variable.VariableDependencies is null retrieve it from database
variable.DependentVariableDependencies = variable.DependentVariableDependencies ?? new VariableDependencyService(base.UnitOfWork).GetList(variable.ID, null);
//add variable dependies to whereclause which have relation with current variable.
this.AddDependencyClause(variable, queryParams, columnParams, ref whereClause, additionalParams, allSavedEntities);
string sqlQueryIsExist = $@"(select top(1) {entityDef.FormattedTableName}.ID from {entityDef.FormattedTableName} {whereClause} {orderClause})";
string sqlInsertQuery = $@"INSERT INTO {entityDef.FormattedTableName}
({(base.EngineSharedModel.CurrentThreadID.HasValue ? "ThreadID," : "")}{string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@')))})
OUTPUT inserted.ID
VALUES
({(base.EngineSharedModel.CurrentThreadID.HasValue ? ("'" + base.EngineSharedModel.CurrentThreadID.Value + "',") : "")}{string.Join(",", columnParams.Select(c => c.ParameterName))})";
this.AddDefaultParams((sqlInsertQuery + sqlQueryIsExist), queryParams);
this.AddVariableParameters(ref sqlInsertQuery, queryParams);
this.AddVariableParameters(ref sqlQueryIsExist, queryParams);
queryParams = columnParams.Union(queryParams).Where(c => (sqlInsertQuery + sqlQueryIsExist).Contains(c.ParameterName)).GroupBy(c => c.ParameterName).Select(c => c.FirstOrDefault()).ToList();
DataTable dataTableIsExist = new DataBaseQueryService(base.UnitOfWork).GetBySqlQuery(sqlQueryIsExist, true, null, queryParams.ToArray());
Guid? entityIsExistId = dataTableIsExist != null && dataTableIsExist.Rows.Count > 0 ? dataTableIsExist.Rows[0][0].ToGuidObj() : (Guid?)null;
if (entityIsExistId.HasValue)
{
string sqlUpdateQuery = $@"update {entityDef.FormattedTableName} set {string.Join(",", columnParams.Select(c => c.ParameterName.TrimStart('@') + "=" + c.ParameterName))} where ID='{entityIsExistId.ToStringObj()}'";
//update entity
new DataBaseQueryService(base.UnitOfWork).ExecuteBySqlQuery(sqlUpdateQuery, true, queryParams.ToArray());
}
else
{
//insert entity
this.CheckMandatory(resultOperation, entityDef, columnParams);
if (resultOperation.IsSuccess)
{
entityIsExistId = new DataBaseQueryService(base.UnitOfWork).ExecuteScalar <Guid>(sqlInsertQuery, true, queryParams.ToArray()).ToGuidObjNull();
}
}
return(new Tuple <ResultOperation, Guid?>(resultOperation, entityIsExistId));
}
