/// <summary>
/// Method to append HAVING clause to query.
/// </summary>
/// <param name="table">Table data from parsed table.</param>
protected virtual void Having(QueryDataModel table)
{
var clause = table.TableClause.FirstOrDefault(n => n.ClauseType.Equals("HAVING", StringComparison.OrdinalIgnoreCase));
if (clause != null)
{
Query.Append(string.Concat(" HAVING ", clause.ClauseValue));
}
}
/// <summary>
/// Method to append JOIN clause to the query.
/// </summary>
/// <param name="table">Table data from parsed table.</param>
protected virtual void Join(QueryDataModel table)
{
var joinClauseList = table.TableClause.Where(n => n.ClauseType.Equals("JOIN", StringComparison.OrdinalIgnoreCase));
foreach (var clause in joinClauseList)
{
Query.Append(string.Concat(" ", clause.JoinType.ToUpper(), " JOIN ", clause.JoinTableName, " ON ", clause.ClauseValue));
}
}
public MNIST28x28BackQueryData(QueryDataModel dataModel)
{
base.InputValues = dataModel.InputValues;
base.Marker = dataModel.Marker;
base.OutputValues = dataModel.OutputValues;
for (int i = 0; i < dataModel.InputValues.Length; i++)
{
base.InputValues[i] = (dataModel.InputValues[i] * 255.0f / 0.99f) - 0.01f;
}
}
/// <summary>
/// Method to generate ALTER TABLE query.
/// </summary>
/// <param name="tableName">Table data from parsed table.</param>
protected virtual void AlterTable(QueryDataModel table)
{
//TODO: handle column contraints.
string alterTableBaseQuery = string.Concat("ALTER TABLE ", Utility.GetTableName(table.TableName), " ");
List <ColumnModel> columnList;
/* Build query to alter columns. */
columnList = table.ColumnList.FindAll(x => string.Equals(x.ColumnOperation, "alter", StringComparison.OrdinalIgnoreCase));
if (columnList.Count > 0)
{
foreach (var columnConfig in columnList)
{
/* Drop constraints before altering column if exists. */
Query.Append(CreateDropConstraintQuery(table.TableName, columnConfig));
Query.Append(string.Concat(alterTableBaseQuery, "ALTER COLUMN ", CreateColumnConfigString(columnConfig, true), ";"));
}
}
/* Build query to add new columns. */
columnList = table.ColumnList.FindAll(x => string.Equals(x.ColumnOperation, "add", StringComparison.OrdinalIgnoreCase));
if (columnList.Count > 0)
{
List <string> columnConfigStringList = new List <string>();
foreach (var columnConfig in columnList)
{
columnConfigStringList.Add(CreateColumnConfigString(columnConfig));
}
Query.Append(string.Concat(alterTableBaseQuery, "ADD ", string.Join(", ", columnConfigStringList), ";"));
}
/* Build query to drop columns. */
columnList = table.ColumnList.FindAll(x => string.Equals(x.ColumnOperation, "drop", StringComparison.OrdinalIgnoreCase));
if (columnList.Count > 0)
{
List <string> columnNameString = new List <string>();
foreach (var columnConfig in columnList)
{
/* Drop constraints before droping column if exists. */
Query.Append(CreateDropConstraintQuery(table.TableName, columnConfig));
columnNameString.Add(columnConfig.ColumnName);
}
Query.Append(string.Concat(alterTableBaseQuery, "DROP COLUMN ", string.Join(", ", columnNameString), ";"));
}
}
/// <summary>
/// Method to generate CREATE TABLE query.
/// </summary>
/// <param name="table">Table data from parsed table.</param>
protected virtual void CreateTable(QueryDataModel table)
{
List <string> columnConfigList = new List <string>();
Query.Append(string.Concat("CREATE TABLE ", Utility.GetTableName(table.TableName), " ( "));
/* Build column config string for each column. */
foreach (var column in table.ColumnList)
{
/* Add column config string to list. */
columnConfigList.Add(CreateColumnConfigString(column));
}
/* Append column config string to query. */
Query.Append(string.Concat(string.Join(", ", columnConfigList), ")"));
}
/// <summary>
/// Method to generate INSERT query.
/// </summary>
/// <param name="requestData">Table data to generate INSERT query.</param>
protected virtual void InsertRecord(QueryDataModel requestData)
{
/* Get number of records to be filled by getting count in row value. */
int numberOfRecords = requestData.ColumnList.First().Values.Count;
/* Variable to store record number and records data. */
Dictionary <int, List <string> > recordsData = new Dictionary <int, List <string> >();
/* Variable to store list of column name. */
List <string> columnNameList = new List <string>(),
/* Variable to store list of parsed record values as string */
parsedRecords = new List <string>();
Query.Append(string.Concat("INSERT INTO ", Utility.GetTableName(requestData.TableName), " ("));
/* To create values group. */
for (int i = 0; i < numberOfRecords; i++)
{
/* Loop through each column. */
foreach (var column in requestData.ColumnList)
{
/* Add column name to columnNameList if it does not exists. */
if (!columnNameList.Contains(column.ColumnName))
{
columnNameList.Add(column.ColumnName);
}
/* Add recordsData item if does not exists. */
if (!recordsData.ContainsKey(i))
{
recordsData.Add(i, new List <string>());
}
/* Add value to recordsData. */
recordsData[i].Add(string.Concat("'", column.Values[i], "'"));
}
}
/* Construct parsed record string from value group. */
foreach (var valueGroup in recordsData)
{
parsedRecords.Add(string.Concat("(", string.Join(", ", valueGroup.Value), ")"));
}
/* Append column name list and values record. */
Query.Append(string.Concat(string.Join(", ", columnNameList), ") VALUES ", string.Join(", ", parsedRecords), ";"));
}
/// <summary>
/// Method to generate UPADTE query.
/// </summary>
/// <param name="requestData">Table data to generate INSERT query.</param>
protected virtual void UpdateRecord(QueryDataModel requestData)
{
/* Variable to store list of column name and values to be set. */
List <string> nameValueList = new List <string>();
Query.Append(string.Concat("UPDATE ", Utility.GetTableName(requestData.TableName), " SET "));
/* Construct list of column name and values to be set. */
foreach (var column in requestData.ColumnList)
{
nameValueList.Add(string.Concat(column.ColumnName, " = '", column.Values.FirstOrDefault(), "'"));
}
/* Append column name and values to be updated. */
Query.Append(string.Join(", ", nameValueList));
/* Append where condition if exists. */
Where(requestData);
/* Append semicolon to mark end of statement. */
Query.Append(";");
}
/// <summary>
/// Query Dynamic Data
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public QueryDataModel QueryData(QueryDataModel model)
{
model.UserId = CurrentUserId;
model.Data = _dm.QueryData(model);
return(model);
}
public QueryDataModel QueryData(QueryDataModel model)
{
return(_service.QueryData(model));
}
public async Task <QueryDataModel> GetDataModelinfo(int UserId, int Year, int Mounth)
{
var data = await _ISysUserServices.GetModelAsync(x => x.Id == UserId && x.IsDelete == false);
if (data == null)
{
return(new QueryDataModel());
}
{
QueryDataModel queryDataModel = new QueryDataModel
{
Id = data.Id,
UserName = data.UserName,
Data = new List <Data>()
};
var formData = await _FillFormService.Query(x => x.UserId == data.Id && x.Year == Year && x.Mounth == Mounth);
if (formData == null)
{
return(new QueryDataModel());
}
foreach (var item in formData)
{
var datas = new Data
{
DutyPeople = item.DutyPeople,
Mounth = item.Mounth,
Year = item.Year,
TableData =
_Mapper.Map <List <ProjectDto> >(JsonConvert.DeserializeObject <List <ProjectDto> >(item.TableData)),
TableFour = new List <ProjectFourDto>(),
TableFive = new List <ProjectFiveDto>()
};
if (item.TableFive == "[]")
{
item.TableFive = string.Empty;
}
else
{
datas.TableFive =
_Mapper.Map <List <ProjectFiveDto> >(JsonConvert.DeserializeObject <List <Conversation> >(item.TableFive));
}
if (item.TableFour == "[]")
{
item.TableFour = string.Empty;
}
else
{
datas.TableFour =
_Mapper.Map <List <ProjectFourDto> >(JsonConvert.DeserializeObject <List <ProjectFourDto> >(item.TableFour));
}
if (datas.TableFour == null || datas.TableFour.Count == 0)
{
datas.TableFour = new List <ProjectFourDto>();
}
if (datas.TableFive == null || datas.TableFive.Count == 0)
{
datas.TableFive = new List <ProjectFiveDto>();
}
queryDataModel.Data.Add(datas);
}
queryDataModel.Count = queryDataModel.Data.Count;
return(queryDataModel);
}
}
/// <summary>
/// Query Dynamic Data
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IList <object> QueryData(QueryDataModel model)
{
// 1. Get Columns
var viewColumns = _viewColumnRep.Table.Where(p => p.ViewId == model.ViewId && p.IsActived).ToList();
var columnIds = viewColumns.Select(s => s.ColumnId).ToArray();
var columns = _columnRep.Include(i => i.DataType).Where(p => columnIds.Contains(p.Id)).ToList();
model.Columns = new List <Common.Models.DataColumn>();
foreach (var item in columns)
{
var viewColumn = viewColumns.Single(s => s.ColumnId == item.Id);
model.Columns.Add(new Common.Models.DataColumn
{
Id = item.Id,
Name = item.Name,
NameDict = item.NameDict,
EnumId = item.EnumId,
DataTypeCode = item.DataType.Code,
IsForeignKey = item.IsForeignKey,
IsPrimaryKey = item.IsPrimaryKey,
Position = viewColumn.Position,
Visible = viewColumn.Visible,
Searchable = item.Searchable,
Sortable = item.Sortable,
Width = viewColumn.Width,
TableAlias = item.TableAlias,
SqlName = StringHelper.ConverToCamelCase(item.SqlName)
});
}
// 2. Create SqlParameters
var moduleIdParam = new SqlParameter("moduleId", model.ModuleId);
var viewIdParam = new SqlParameter("viewId", model.ViewId);
var orderExpressionParam = model.OrderExpression != null ?
new SqlParameter("orderExpression", model.OrderExpression)
: new SqlParameter("orderExpression", DBNull.Value);
var pageIndexParam = new SqlParameter("pageIndex", model.PageIndex);
var pageSizeParam = new SqlParameter("pageSize", model.PageSize);
var currentUserIdParam = new SqlParameter("userId", model.UserId);
var totalRowParam = new SqlParameter
{
ParameterName = "total",
SqlDbType = SqlDbType.Int,
Value = DBNull.Value,
Direction = ParameterDirection.Output
};
var groupJsonParam = new SqlParameter
{
ParameterName = "groupJson",
SqlDbType = SqlDbType.NVarChar,
Size = 4000,
Value = DBNull.Value,
Direction = ParameterDirection.Output
};
var typeInfo = ObjectBuilder.CompileResultTypeInfo("QueryDataResult", "QueryDataResult", model.Columns);
// 3. Excute query
var result = _rep.ExcuteStoreProcedure(typeInfo,
SqlStoreProcedureConstant.SpQueryData,
moduleIdParam,
viewIdParam,
orderExpressionParam,
pageIndexParam,
pageSizeParam,
currentUserIdParam,
totalRowParam,
groupJsonParam);
model.TotalRecord = (int)totalRowParam.Value;
model.GroupJson = (string)groupJsonParam.Value;
//model.GroupByObjs = JsonConvert.DeserializeObject<List<GroupByObj>>((string)groupJsonParam.Value);
return(result.ToList());
}
public QueryDataVM(QueryDataModel model)
{
Marker = model.Marker;
DataModel = model;
}
