/// <summary>
/// Not Like条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string NotLikeParse(NotLikeCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" NOT LIKE ");
if (condition.ParamValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.ParamValue;
string valueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append("CONCAT('%', ");
conditionStr.Append(valueColName);
conditionStr.Append(", '%')");
}
else
{
string paramName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + paramName);
this.ParamCollection.Add(new MySqlParameter(paramName, "%" + condition.ParamValue.ToString() + "%"));
}
conditionStr.Append(" ");
return(conditionStr.ToString());
}
/// <summary>
/// 大于等于条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string LargeEqualParse(LargeEqualCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" >= ");
if (condition.ParamValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.ParamValue;
string valueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(valueColName);
}
else
{
string paramName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + paramName);
ParamCollection.Add(new MySqlParameter(paramName, condition.ParamValue));
}
conditionStr.Append(" ");
return(conditionStr.ToString());
}
public static bool SetSearchColumProc(SqlConnection con, SearchColumn model)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.Parameters.Add(new SqlParameter("columnId", model.columnId));
cmd.Parameters.Add(new SqlParameter("categoryId", model.categoryId));
cmd.Parameters.Add(new SqlParameter("@category", model.category));
cmd.Parameters.Add(new SqlParameter("tableId", model.tableId));
cmd.Parameters.Add(new SqlParameter("@columnType", model.columnType));
cmd.Parameters.Add(new SqlParameter("columnName", model.columnName));
cmd.Parameters.Add(new SqlParameter("columnDisplayName", model.columnDisplayName));
cmd.Parameters.Add(new SqlParameter("returnedColumnName", model.returnedColumnName));
cmd.Parameters.Add(new SqlParameter("returnedColumnType", model.returnedColumnType));
cmd.Parameters.Add(new SqlParameter("datalistId", model.datalistId));
cmd.Parameters.Add(new SqlParameter("isNullable", model.isNullable));
cmd.Parameters.Add(new SqlParameter("isCustomData", model.isCustomData));
cmd.Parameters.Add(new SqlParameter("isCustomDataJsonArray", model.isCustomDataJsonArray));
cmd.Parameters.Add(new SqlParameter("canBeIncludedInResults", model.canBeIncludedInResults));
cmd.Parameters.Add(new SqlParameter("originTypeId", model.originTypeId));
cmd.Parameters.Add(new SqlParameter("originIdColumn", model.originIdColumn == null?"":model.originIdColumn));
cmd.Parameters.Add(new SqlParameter("displayMaskId", model.displayMaskId == null?"":model.displayMaskId));
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "prAddTWColumns";
cmd.ExecuteNonQuery();
}
return(true);
}
/// <summary>
/// Evaluate
/// </summary>
public static string Evaluate(SearchColumn searchColumn)
{
return($@" /// <summary>
/// {searchColumn.TitleValue}
/// </summary>
[Display(Name = ""{searchColumn.TitleValue}"")]
public {searchColumn.TypeCSharp} {searchColumn.InternalNameCSharp} {{ get; set; }}");
}
public async Task<List<dynamic>> GetByFormAsync(string formName, SearchColumn searchColumn, string searchValue)
{
var requestUrl = string.Format(
GetRecordsByFormWithSearchRequestUri,
Token,
formName,
searchColumn,
searchValue);
return await GetByFormAsyncFromUrl(requestUrl);
}
private string BuildSchemaField(SearchColumn searchColumn)
{
var searchColumnEntitySchema = _userConnection.EntitySchemaManager.GetInstanceByName(searchColumn.ReferenceSchemaName);
var filterColumn = searchColumnEntitySchema.Columns.GetByName(searchColumn.ColumnName);
var columnName = filterColumn.IsLookupType
? _globalSearchColumnUtils.GetPrimaryColumnAlias(filterColumn, searchColumn.SchemaName)
: _globalSearchColumnUtils.GetAlias(filterColumn, searchColumnEntitySchema);
return(columnName.ToLowerInvariant());
}
public async Task<List<dynamic>> GetByViewAsync(string viewName, SearchColumn searchColumn, string searchValue)
{
var requestUrl = string.Format(
GetRecordsByViewWithSearchRequestUri,
Token,
viewName,
searchColumn,
searchValue);
return await GetByViewFromUrlAsync(requestUrl);
}
/// <summary>
/// NOT IN条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string NotInParse(NotInCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" NOT IN ( ");
string paramName = string.Empty;
int i = 0;
foreach (object pvalue in condition.ParamValueList)
{
if (i != 0)
{
conditionStr.Append(",");
}
if (pvalue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)pvalue;
string valueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(valueColName);
}
else
{
if (!string.IsNullOrEmpty(pvalue.ToString()))
{
paramName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + paramName);
ParamCollection.Add(new MySqlParameter(paramName, pvalue));
}
else
{
conditionStr.Append("''");
}
}
i++;
}
conditionStr.Append(") ");
return(conditionStr.ToString());
}
public static Expression <Func <T, bool> > ConvertToLambda <T>(List <ColumnModel> columnModels,
SearchColumn searchColumn, List <ColumnInformation> columnInformations) where T : class
{
if (columnModels == null || searchColumn == null)
{
return(PredicateBuilder.True <T>());
}
Filter parametros = ConvertToFilter(columnModels, searchColumn, columnInformations);
Expression <Func <T, bool> > expresionsLambdaSet = MergeRules <T>(parametros);
return(expresionsLambdaSet ?? PredicateBuilder.True <T>());
}
/// <summary>
/// Between条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string BetweenParse(BetweenCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" BETWEEN ");
if (condition.StartValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.StartValue;
string startValueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(startValueColName);
}
else
{
string startParamName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + startParamName);
this.ParamCollection.Add(new MySqlParameter(startParamName, condition.StartValue));
}
conditionStr.Append(" AND ");
if (condition.EndValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.EndValue;
string endValueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(endValueColName);
}
else
{
string endParamName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + endParamName);
this.ParamCollection.Add(new MySqlParameter(endParamName, condition.EndValue));
}
conditionStr.Append(" ");
return(conditionStr.ToString());
}
/// <summary>
/// Adds the columns to the grid and defines the data source for restful searching
/// </summary>
private DataTableColumn InitialiseResult(SearchColumn column)
{
switch (column.ColumnType)
{
case "text":
DataTableTextColumn textColumn = new DataTableTextColumn();
textColumn.FieldName = column.FieldName;
return(textColumn);
case "date":
DataTableDateColumn dateColumn = new DataTableDateColumn();
dateColumn.FieldName = column.FieldName;
return(dateColumn);
case "icon":
DataTableIconColumn iconColumn = new DataTableIconColumn();
iconColumn.FieldName = column.FieldName;
iconColumn.TrueIcon = column.ColumnIconTrueIcon;
iconColumn.TrueTooltip = column.ColumnIconTrueText;
iconColumn.TrueClass = column.ColumnIconTrueCss;
iconColumn.FalseIcon = column.ColumnIconFalseIcon;
iconColumn.FalseTooltip = column.ColumnIconFalseText;
iconColumn.FalseClass = column.ColumnIconFalseCss;
return(iconColumn);
case "link":
DataTableLinkColumn linkColumn = new DataTableLinkColumn();
linkColumn.LinkText = column.ColumnLinkText;
linkColumn.LinkHref = column.ColumnLinkHref;
return(linkColumn);
case "html":
DataTableCustomColumn customColumn = new DataTableCustomColumn();
customColumn.Controls.Add(new LiteralControl()
{
Text = column.ColumnHtml
});
return(customColumn);
case "time":
DataTableDateTimeColumn datetimeColumn = new DataTableDateTimeColumn();
datetimeColumn.FieldName = column.FieldName;
return(datetimeColumn);
}
return(null);
}
/// <summary>
/// 等于条件解析；如果条件值为NULL或空字符串时则判断字段的空字符串或NULL值
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string EqualParse(EqualCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" = ");
if (condition.ParamValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.ParamValue;
string valueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(valueColName);
}
else
{
if (condition.ParamValue != null && !condition.ParamValue.ToString().Equals(string.Empty))
{
string paramName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + paramName);
ParamCollection.Add(new DBParam(paramName, condition.ParamValue));
}
else
{
conditionStr.Insert(0, " (");
conditionStr.Append("'' ");
conditionStr.Append(" OR ");
conditionStr.Append(queryColName);
conditionStr.Append(" IS NULL) ");
}
}
conditionStr.Append(" ");
return(conditionStr.ToString());
}
/// <summary>
/// LIKE条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string LikeParse(LikeCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" LIKE ");
if (condition.ParamValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.ParamValue;
string valueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append("'%' +");
conditionStr.Append(valueColName);
conditionStr.Append("+ '%'");
}
else
{
string paramName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("'%' +");
conditionStr.Append("@" + paramName);
conditionStr.Append("+ '%'");
ParamCollection.Add(new DBParam(paramName, condition.ParamValue, System.Data.DbType.String));
}
conditionStr.Append(" ");
return(conditionStr.ToString());
}
public void AddColumn(string dataPropertyName, string columnHeader, int fillWeight = 100, bool searchable = false, bool visible = true)
{
BusinessLayer.General.Logging.Log("Adding column to FHList form '" + Name + "': " + columnHeader, BusinessLayer.General.Logging.LogLevel.Debug);
DataGridViewColumn dgvCol = new DataGridViewTextBoxColumn();
dgvCol.Name = "COL" + dataPropertyName;
dgvCol.DataPropertyName = dataPropertyName;
dgvCol.FillWeight = fillWeight;
dgvCol.HeaderText = columnHeader;
dgvCol.Visible = visible;
dgvList.Columns.Add(dgvCol);
_columns.Add(dataPropertyName, dgvCol.Name);
BusinessLayer.General.Logging.Log(". Columnname: " + dgvCol.Name + ", Data property name: " + dgvCol.DataPropertyName + ", "
+ "Fill weight: " + dgvCol.FillWeight + ", Header text: " + dgvCol.HeaderText, BusinessLayer.General.Logging.LogLevel.Debug);
if (searchable)
{
BusinessLayer.General.Logging.Log(".. Column is searchable, adding column to search field combobox", BusinessLayer.General.Logging.LogLevel.Debug);
pnlTop.Visible = true;
SearchColumn sc = new SearchColumn();
sc.ColumnHeader = dgvCol.HeaderText;
sc.ColumnName = dgvCol.Name;
scs.Add(sc);
Tuple <string, string> searchField = new Tuple <string, string>(dgvCol.Name,
Common.Localization.Translate(dgvCol.HeaderText, Common.FormHandling.GetProgram(this).Code));
cmbField.Items.Add(searchField);
cmbField.DisplayMember = "Item2";
cmbField.SelectedIndex = 0;
}
Common.FormHandling.SetGridEdit(dgvList, false);
BusinessLayer.General.Logging.Log("Column added to FHList '" + Name + "': " + columnHeader, BusinessLayer.General.Logging.LogLevel.Debug);
}
/// <summary>
/// 非相似条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回非相似条件对象</returns>
public static NotLikeCondition NotLike(SearchColumn column, object paramVal)
{
NotLikeCondition cond = new NotLikeCondition(column, paramVal);
return cond;
}
/// <summary>
/// 非包含集合条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="paramValueList">比较的值列表</param>
/// <returns>返回非包含集合条件对象</returns>
public static NotInCondition NotIn(ConditionRelation relation, SearchColumn column, object[] paramValueList)
{
NotInCondition cond = new NotInCondition(relation, column, paramValueList);
return cond;
}
/// <summary>
/// Between条件，用于查询介于两个值之间的值
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="stratVal">开始值</param>
/// <param name="endVal">结束值</param>
/// <returns>返回Between条件对象</returns>
public static BetweenCondition Between(ConditionRelation relation, SearchColumn column, object stratVal, object endVal)
{
BetweenCondition cond = new BetweenCondition(relation, column, stratVal, endVal);
return cond;
}
/// <summary>
/// 相似条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回相似条件对象</returns>
public static LikeCondition Like(SearchColumn column, object paramVal)
{
LikeCondition cond = new LikeCondition(column, paramVal);
return cond;
}
/// <summary>
/// 小于条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回小于条件对象</returns>
public static LessThanCondition LessThan(ConditionRelation relation, SearchColumn column, object paramVal)
{
LessThanCondition cond = new LessThanCondition(relation, column, paramVal);
return cond;
}
/// <summary>
/// 小于条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回小于条件对象</returns>
public static LessThanCondition LessThan(SearchColumn column, object paramVal)
{
LessThanCondition cond = new LessThanCondition(column, paramVal);
return cond;
}
/// <summary>
/// 大于等于条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回大于等于条件对象</returns>
public static LargeEqualCondition LargeEqual(ConditionRelation relation, SearchColumn column, object paramVal)
{
LargeEqualCondition cond = new LargeEqualCondition(relation, column, paramVal);
return cond;
}
/// <summary>
/// 空条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <returns>返回空条件对象</returns>
public static IsNullCondition IsNull(ConditionRelation relation, SearchColumn column)
{
IsNullCondition cond = new IsNullCondition(relation, column);
return cond;
}
/// <summary>
/// 空条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <returns>返回空条件对象</returns>
public static IsNullCondition IsNull(SearchColumn column)
{
IsNullCondition cond = new IsNullCondition(column);
return cond;
}
private static Filter ConvertToFilter(IEnumerable <ColumnModel> columnModels, SearchColumn searchColumn, IEnumerable <ColumnInformation> columnInformations)
{
IEnumerable <ColumnModel> searchableColumnModels;
string groupFilter;
bool genericSearch = false;
if (!string.IsNullOrEmpty(searchColumn.Value))
{
groupFilter = "or";
searchableColumnModels = columnModels.Where(p => p.Searchable);
genericSearch = true;
}
else
{
groupFilter = "and";
searchableColumnModels = columnModels.Where(p => !string.IsNullOrEmpty(p.Search.Value) && p.Searchable);
}
return(new Filter
{
GroupOp = groupFilter,
Rules = searchableColumnModels
.Select(p => CrearRuleColumna(p, genericSearch ? searchColumn.Value : p.Search.Value, columnInformations)).ToList()
});
}
/// <summary>
/// 子查询非包含条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="sql">子查询SQL</param>
/// <returns>返回子查询非包含条件对象</returns>
public static SQLNotInCondition SQLNotIn(ConditionRelation relation, SearchColumn column, string sql)
{
SQLNotInCondition cond = new SQLNotInCondition(relation, column, sql);
return cond;
}
/// <summary>
/// 非相似条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回非相似条件对象</returns>
public static NotLikeCondition NotLike(ConditionRelation relation, SearchColumn column, object paramVal)
{
NotLikeCondition cond = new NotLikeCondition(relation, column, paramVal);
return cond;
}
/// <summary>
/// 子查询非包含条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="sql">子查询SQL</param>
/// <returns>返回子查询非包含条件对象</returns>
public static SQLNotInCondition SQLNotIn(SearchColumn column, string sql)
{
SQLNotInCondition cond = new SQLNotInCondition(column, sql);
return cond;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="sql">子查询语句</param>
public NotExistsCondition(SearchColumn column, string sql)
: this(ConditionRelation.And, column, sql)
{
}
/// <summary>
/// 等于条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="paramVal">比较的值</param>
/// <returns>返回等于条件对象</returns>
public static EqualCondition Equal(SearchColumn column, object paramVal)
{
EqualCondition cond = new EqualCondition(column, paramVal);
return cond;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="relation">关联关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="sql">子查询语句</param>
public NotExistsCondition(ConditionRelation relation, SearchColumn column, string sql)
: base(relation, column)
{
this.SQL = sql;
}
/// <summary>
/// 包含集合条件
/// </summary>
/// <param name="column">查询字段对象</param>
/// <param name="paramValueList">比较的值列表</param>
/// <returns>返回集合条件对象</returns>
public static InCondition In(SearchColumn column, object[] paramValueList)
{
InCondition cond = new InCondition(column, paramValueList);
return cond;
}
/// <summary>
/// 子查询不等于条件
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="sql">子查询SQL</param>
/// <returns>返回子查询不等于条件对象</returns>
public static SQLNotEqualCondition SQLNotEqual(ConditionRelation relation, SearchColumn column, string sql)
{
SQLNotEqualCondition cond = new SQLNotEqualCondition(relation, column, sql);
return(cond);
}