/// <summary>
/// 追加关键字筛选Sql
/// </summary>
private static string AppendSqlForKeyWord(string keyWord, int entityId, DBDatabase db, string sqlStr)
{
if (!string.IsNullOrEmpty(keyWord))
{
var sql = new Sql("SELECT Field FROM Sys_field WHERE entityid=" + entityId + " AND IsKeySearch=1");
List <Sys_Field> listfiled = db.Fetch <Sys_Field>(sql);
if (listfiled.Count == 0)
{
return(string.Empty);
}
if (sqlStr.IndexOf("WHERE") != -1)
{
sqlStr += " AND (";
}
else
{
sqlStr += " WHERE (";
}
int i = 0;
foreach (var field in listfiled)
{
if (sqlStr.IndexOf(field.Field.Split(new char[] { '@' })[0]) != -1)
{
if (i++ > 0)
{
sqlStr += " Or ";
}
sqlStr += string.Format("{0} LIKE '%{1}%'", field.Field.Replace("$", "."), keyWord.Trim());
}
}
sqlStr += ")";
}
return(sqlStr);
}
/// <summary>
/// 统计数据
/// </summary>
private static string GetSumDataSql(int entityId, DBDatabase db, string sqlStr)
{
var sql = new Sql("SELECT Field,EntityName,Name FROM Sys_field WHERE entityid=" + entityId + " AND IsEnableSumData=1");
List <Sys_Field> listfiled = db.Fetch <Sys_Field>(sql);
if (listfiled.Count == 0)
{
return(string.Empty);
}
string sumSql = string.Empty;
int i = 0;
foreach (var field in listfiled)
{
if (i++ > 0)
{
sumSql += ",";
}
sumSql += $"SUM({field.EntityName}.{field.Name}) AS {field.Field}";
}
sqlStr = Regex.Replace(sqlStr, @"(SELECT[\s\S]*?FROM)", $"SELECT {sumSql} FROM");
return(sqlStr);
}