public static List <FieldFunction> LoadFieldFunction()
{
List <FieldFunction> _list = new List <FieldFunction>();
FieldFunction ff1 = new FieldFunction();
ff1.CODE = "";
ff1.LABEL = "None";
_list.Add(ff1);
FieldFunction ff2 = new FieldFunction();
ff2.CODE = "LBL";
ff2.LABEL = "Line Label";
_list.Add(ff2);
FieldFunction ff3 = new FieldFunction();
ff3.CODE = "SUM";
ff3.LABEL = "Sum(*)";
_list.Add(ff3);
FieldFunction ff4 = new FieldFunction();
ff4.CODE = "MAX";
ff4.LABEL = "Max(*)";
_list.Add(ff4);
FieldFunction ff5 = new FieldFunction();
ff5.CODE = "MIN";
ff5.LABEL = "Min(*)";
_list.Add(ff5);
FieldFunction ff6 = new FieldFunction();
ff6.CODE = "AVG";
ff6.LABEL = "Average(*)";
_list.Add(ff6);
FieldFunction ff7 = new FieldFunction();
ff7.CODE = "CNT";
ff7.LABEL = "Count(*)";
_list.Add(ff7);
FieldFunction ff8 = new FieldFunction();
ff8.CODE = "CNTNB";
ff8.LABEL = "Count Non Blank(*)";
_list.Add(ff8);
return(_list);
}
private static string getFunctionSelectText(IDbTranslator dbTranslator, string fieldName, FieldFunction function)
{
string fileInfo = "";
switch (function)
{
case FieldFunction.Average:
fileInfo = string.Format("AVG({0}) AS MP", dbTranslator.Quote(fieldName));
break;
case FieldFunction.Sum:
fileInfo = string.Format("SUM({0}) AS MP", dbTranslator.Quote(fieldName));
break;
case FieldFunction.Max:
fileInfo = string.Format("MAX({0}) AS MP", dbTranslator.Quote(fieldName));
break;
case FieldFunction.Min:
fileInfo = string.Format("MIN({0}) AS MP", dbTranslator.Quote(fieldName));
break;
case FieldFunction.Count:
fileInfo = "COUNT(1) AS MP";
break;
default:
break;
}
return(fileInfo);
}
/// <summary>
/// 生成Function的SQL声明
/// </summary>
/// <param name="dbTranslator"></param>
/// <param name="entityInfo"></param>
/// <param name="expr"></param>
/// <returns></returns>
public static SqlStatement BuildFunctionSqlStatement(IDbTranslator dbTranslator, IEntityMapper entityInfo, Expression whereExpr, string fieldName, FieldFunction function)
{
IExpressionParser parser = new ExpressionParser(entityInfo);
//设置查询条件
var dpc = new DataParameterCollection();
string strWhere = "";
if (whereExpr != null)
{
strWhere = parser.ToSQL(whereExpr, dbTranslator, dpc);
}
if (strWhere.Length > 0)
{
strWhere = "WHERE " + strWhere;
}
//设置Function
string functionText = getFunctionSelectText(dbTranslator, fieldName, function);
//生成SQL
string sqlString = string.Format("SELECT {0} FROM {1} {2}",
functionText,
dbTranslator.Quote(entityInfo.TableName),
strWhere);
return(new SqlStatement(System.Data.CommandType.Text, sqlString, dpc));
}
