namespace APIJSON.NET

{

using APIJSON.NET.Services;

using AspectCore.Extensions.Reflection;

using Newtonsoft.Json.Linq;

using SqlSugar;

using System;

using System.Collections.Generic;

using System.Dynamic;

using System.Linq;

using System.Text.RegularExpressions;

/// <summary>

///

/// </summary>

public class SelectTable

{

private readonly IIdentityService _identitySvc;

private readonly ITableMapper _tableMapper;

private readonly SqlSugarClient db;

/// <summary>

///

/// </summary>

/// <param name="identityService"></param>

/// <param name="tableMapper"></param>

/// <param name="dbClient"></param>

public SelectTable(IIdentityService identityService, ITableMapper tableMapper, SqlSugarClient dbClient)

{

_identitySvc = identityService;

_tableMapper = tableMapper;

db = dbClient;

}

/// <summary>

/// 判断表名是否正确

/// </summary>

/// <param name="table"></param>

/// <returns></returns>

public bool IsTable(string table)

{

return db.DbMaintenance.GetTableInfoList().Any(it => it.Name.Equals(table, StringComparison.CurrentCultureIgnoreCase));

}

/// <summary>

/// 判断表的列名是否正确

/// </summary>

/// <param name="table"></param>

/// <param name="col"></param>

/// <returns></returns>

public bool IsCol(string table, string col)

{

return db.DbMaintenance.GetColumnInfosByTableName(table).Any(it => it.DbColumnName.Equals(col, StringComparison.CurrentCultureIgnoreCase));

}

/// <summary>

/// 动态调用方法

/// </summary>

/// <param name="funcname"></param>

/// <param name="param"></param>

/// <param name="types"></param>

/// <returns></returns>

public object ExecFunc(string funcname, object[] param, Type[] types)

{

var method = typeof(FuncList).GetMethod(funcname);

var reflector = method.GetReflector();

var result = reflector.Invoke(new FuncList(), param);

return result;

}

/// <summary>

///

/// </summary>

/// <param name="subtable"></param>

/// <param name="page"></param>

/// <param name="count"></param>

/// <param name="json"></param>

/// <param name="dd"></param>

/// <returns></returns>

public Tuple<dynamic, int> GetTableData(string subtable, int page, int count, int query, string json, JObject dd)

{

var role = _identitySvc.GetSelectRole(subtable);

if (!role.Item1)//没有权限返回异常

{

throw new Exception(role.Item2);

}

string selectrole = role.Item2;

subtable = _tableMapper.GetTableName(subtable);

JObject values = JObject.Parse(json);

page = values["page"] == null ? page : int.Parse(values["page"].ToString());

count = values["count"] == null ? count : int.Parse(values["count"].ToString());

query = values["query"] == null ? query : int.Parse(values["query"].ToString());

values.Remove("page");

values.Remove("count");

var tb = sugarQueryable(subtable, selectrole, values, dd);

if (query == 1)//1-总数

return new Tuple<dynamic, int>(new List<object>(), tb.Count());

else

{

if (count > 0)

{

int total = 0;

if (query == 0)//0-对象

return new Tuple<dynamic, int>(tb.ToPageList(page, count), total);

else

//2-以上全部

return new Tuple<dynamic, int>(tb.ToPageList(page, count, ref total), total);

}

else

{

if (query == 0)

return new Tuple<dynamic, int>(tb.ToList(), 0);

else

return new Tuple<dynamic, int>(tb.ToList(), tb.Count());

}

}

}

/// <summary>

///

/// </summary>

/// <param name="subtable"></param>

/// <param name="json"></param>

/// <param name="dd"></param>

/// <returns></returns>

public dynamic GetFirstData(string subtable, string json, JObject dd)

{

var role = _identitySvc.GetSelectRole(subtable);

if (!role.Item1)//没有权限返回异常

{

throw new Exception(role.Item2);

}

string selectrole = role.Item2;

subtable = _tableMapper.GetTableName(subtable);

JObject values = JObject.Parse(json);

values.Remove("page");

values.Remove("count");

var tb = sugarQueryable(subtable, selectrole, values, dd).First();

var dic = (IDictionary<string, object>)tb;

foreach (var item in values.Properties().Where(it => it.Name.EndsWith("()")))

{

if (item.Value.IsValue())

{

string func = item.Value.ToString().Substring(0, item.Value.ToString().IndexOf("("));

string param = item.Value.ToString().Substring(item.Value.ToString().IndexOf("(") + 1).TrimEnd(')');

var types = new List<Type>();

var paramss = new List<object>();

foreach (var va in param.Split(','))

{

types.Add(typeof(object));

paramss.Add(tb.Where(it => it.Key.Equals(va)).Select(i => i.Value));

}

dic[item.Name] = ExecFunc(func, paramss.ToArray(), types.ToArray());

}

}

return tb;

}

/// <summary>

/// 解析并查询

/// </summary>

/// <param name="query"></param>

/// <returns></returns>

public JObject Query(string queryJson)

{

JObject resultObj = new JObject();

try

{

JObject queryJobj = JObject.Parse(queryJson);

resultObj = Query(queryJobj);

}

catch (Exception ex)

{

resultObj.Add("code", "500");

resultObj.Add("msg", ex.Message);

}

return resultObj;

}

/// <summary>

/// 单表查询

/// </summary>

/// <param name="queryObj"></param>

/// <returns></returns>

public JObject QuerySingle(JObject queryObj)

{

JObject resultObj = new JObject();

resultObj.Add("code", "200");

resultObj.Add("msg", "success");

try

{

int total = 0;

foreach (var item in queryObj)

{

string key = item.Key.Trim();

if (key.EndsWith("[]"))

{

total = QuerySingleList(resultObj, item, "Infos");

}

else if (key.Equals("func"))

{

ExecFunc(resultObj, item);

}

else if (key.Equals("total@"))

{

resultObj.Add("total", total);

}

}

}

catch (Exception ex)

{

resultObj["code"] = "500";

resultObj["msg"] = ex.Message;

}

return resultObj;

}

/// <summary>

/// 解析并查询

/// </summary>

/// <param name="query"></param>

/// <returns></returns>

public JObject Query(JObject queryObj)

{

JObject resultObj = new JObject();

resultObj.Add("code", "200");

resultObj.Add("msg", "success");

try

{

int total = 0;

foreach (var item in queryObj)

{

string key = item.Key.Trim();

if (key.Equals("[]"))

{

total = QueryMoreList(resultObj, item);

}

else if (key.EndsWith("[]"))

{

total = QuerySingleList(resultObj, item);

}

else if (key.Equals("func"))

{

ExecFunc(resultObj, item);

}

else if (key.Equals("total@"))

{

resultObj.Add("total", total);

}

else

{

var template = GetFirstData(key, item.Value.ToString(), resultObj);

if (template != null)

{

resultObj.Add(key, JToken.FromObject(template));

}

}

}

}

catch (Exception ex)

{

resultObj["code"] = "500";

resultObj["msg"] = ex.Message;

}

return resultObj;

}

//单表查询,返回的数据在指定的NodeName节点

private int QuerySingleList(JObject resultObj, KeyValuePair<string, JToken> item, string nodeName)

{

string key = item.Key.Trim();

var jb = JObject.Parse(item.Value.ToString());

int page = jb["page"] == null ? 0 : int.Parse(jb["page"].ToString());

int count = jb["count"] == null ? 10 : int.Parse(jb["count"].ToString());

int query = jb["query"] == null ? 0 : int.Parse(jb["query"].ToString());

int total = 0;

jb.Remove("page"); jb.Remove("count"); jb.Remove("query");

var htt = new JArray();

foreach (var t in jb)

{

var datas = GetTableData(t.Key, page, count, query, t.Value.ToString(), null);

if (query > 0)

{

total = datas.Item2;

}

foreach (var data in datas.Item1)

{

htt.Add(JToken.FromObject(data));

}

}

if (!string.IsNullOrEmpty(nodeName))

{

resultObj.Add(nodeName, htt);

}

else

resultObj.Add(key, htt);

return total;

}

//单表查询

private int QuerySingleList(JObject resultObj, KeyValuePair<string, JToken> item)

{

string key = item.Key.Trim();

return QuerySingleList(resultObj, item, key);

}

//多列表查询

private int QueryMoreList(JObject resultObj, KeyValuePair<string, JToken> item)

{

int total = 0;

var jb = JObject.Parse(item.Value.ToString());

var page = jb["page"] == null ? 0 : int.Parse(jb["page"].ToString());

var count = jb["count"] == null ? 10 : int.Parse(jb["count"].ToString());

var query = jb["query"] == null ? 0 : int.Parse(jb["query"].ToString());

jb.Remove("page"); jb.Remove("count"); jb.Remove("query");

var htt = new JArray();

List<string> tables = new List<string>(), where = new List<string>();

foreach (var t in jb)

{

tables.Add(t.Key); where.Add(t.Value.ToString());

}

if (tables.Count > 0)

{

string table = tables[0];

var temp = GetTableData(table, page, count, query, where[0], null);

if (query > 0)

{

total = temp.Item2;

}

foreach (var dd in temp.Item1)

{

var zht = new JObject();

zht.Add(table, JToken.FromObject(dd));

for (int i = 1; i < tables.Count; i++)

{

string subtable = tables[i];

if (subtable.EndsWith("[]"))

{

subtable = subtable.TrimEnd("[]".ToCharArray());

var jbb = JObject.Parse(where[i]);

page = jbb["page"] == null ? 0 : int.Parse(jbb["page"].ToString());

count = jbb["count"] == null ? 0 : int.Parse(jbb["count"].ToString());

var lt = new JArray();

foreach (var d in GetTableData(subtable, page, count, query, jbb[subtable].ToString(), zht).Item1)

{

lt.Add(JToken.FromObject(d));

}

zht.Add(tables[i], lt);

}

else

{

var ddf = GetFirstData(subtable, where[i].ToString(), zht);

if (ddf != null)

{

zht.Add(subtable, JToken.FromObject(ddf));

}

}

}

htt.Add(zht);

}

}

if (query != 1)

{

resultObj.Add("[]", htt);

}

return total;

}

private void ExecFunc(JObject resultObj, KeyValuePair<string, JToken> item)

{

JObject jb = JObject.Parse(item.Value.ToString());

Type type = typeof(FuncList);

var dataJObj = new JObject();

foreach (var f in jb)

{

var types = new List<Type>();

var param = new List<object>();

foreach (var va in JArray.Parse(f.Value.ToString()))

{

types.Add(typeof(object));

param.Add(va);

}

dataJObj.Add(f.Key, JToken.FromObject(ExecFunc(f.Key, param.ToArray(), types.ToArray())));

}

resultObj.Add("func", dataJObj);

}

private ISugarQueryable<ExpandoObject> sugarQueryable(string subtable, string selectrole, JObject values, JObject dd)

{

if (!IsTable(subtable))

{

throw new Exception($"表名{subtable}不正确！");

}

var tb = db.Queryable(subtable, "tb");

if (values["@column"].IsValue())

{

ProcessColumn(subtable, selectrole, values, tb);

}

else

{

tb.Select(selectrole);

}

List<IConditionalModel> conModels = new List<IConditionalModel>();

if (values["identity"].IsValue())

{

conModels.Add(new ConditionalModel() { FieldName = values["identity"].ToString(), ConditionalType = ConditionalType.Equal, FieldValue = _identitySvc.GetUserIdentity() });

}

foreach (var va in values)

{

string vakey = va.Key.Trim();

string fieldValue = va.Value.ToString();

if (vakey.EndsWith("$"))//模糊查询

{

FuzzyQuery(subtable, conModels, va);

}

else if (vakey.EndsWith("{}"))//逻辑运算

{

ConditionQuery(subtable, conModels, va);

}

else if (vakey.EndsWith("%"))//bwtween查询

{

ConditionBetween(subtable, conModels, va);

}

else if (vakey.EndsWith("@") && dd != null) // 关联上一个table

{

string[] str = fieldValue.Split('/');

string value = string.Empty;

if (str.Length == 3)

{

value = dd[str[1]][str[2]].ToString();

}

else if (str.Length == 2)

{

value = dd[str[0]][str[1]].ToString();

}

conModels.Add(new ConditionalModel() { FieldName = vakey.TrimEnd('@'), ConditionalType = ConditionalType.Equal, FieldValue = value });

}

else if (IsCol(subtable, vakey)) //其他where条件

{

conModels.Add(new ConditionalModel() { FieldName = vakey, ConditionalType = ConditionalType.Equal, FieldValue = fieldValue });

}

}

tb.Where(conModels);

//排序

ProcessOrder(subtable, values, tb);

//分组

PrccessGroup(subtable, values, tb);

//Having

ProcessHaving(values, tb);

return tb;

}

//处理字段重命名 "@column":"toId:parentId"，对应SQL是toId AS parentId，将查询的字段toId变为parentId返回

private void ProcessColumn(string subtable, string selectrole, JObject values, ISugarQueryable<ExpandoObject> tb)

{

var str = new System.Text.StringBuilder(100);

foreach (var item in values["@column"].ToString().Split(','))

{

string[] ziduan = item.Split(':');

string colName = ziduan[0];

var ma = new Regex(@"\((\w+)\)").Match(colName);

//处理max，min这样的函数

if (ma.Success && ma.Groups.Count > 1)

{

colName = ma.Groups[1].Value;

}

//判断列表是否有权限 sum(1)，sum(*),Count(1)这样的值直接有效

if (colName == "*" || int.TryParse(colName, out int colNumber) || (IsCol(subtable, colName) && _identitySvc.ColIsRole(colName, selectrole.Split(','))))

{

if (ziduan.Length > 1)

str.Append(ziduan[0] + " as " + ziduan[1] + ",");

else

str.Append(ziduan[0] + ",");

}

}

if (string.IsNullOrEmpty(str.ToString()))

{

throw new Exception($"表名{subtable}没有可查询的字段！");

}

tb.Select(str.ToString().TrimEnd(','));

}

// "@having":"function0(...)?value0;function1(...)?value1;function2(...)?value2..."，

// SQL函数条件，一般和 @group一起用，函数一般在 @column里声明

private void ProcessHaving(JObject values, ISugarQueryable<ExpandoObject> tb)

{

if (values["@having"].IsValue())

{

List<IConditionalModel> hw = new List<IConditionalModel>();

List<string> havingItems = new List<string>();

if (values["@having"].HasValues)

{

havingItems = values["@having"].Select(p => p.ToString()).ToList();

}

else

{

havingItems.Add(values["@having"].ToString());

}

foreach (var item in havingItems)

{

string and = item.ToString();

var model = new ConditionalModel();

if (and.Contains(">="))

{

model.FieldName = and.Split(new string[] { ">=" }, StringSplitOptions.RemoveEmptyEntries)[0];

model.ConditionalType = ConditionalType.GreaterThanOrEqual;

model.FieldValue = and.Split(new string[] { ">=" }, StringSplitOptions.RemoveEmptyEntries)[1];

}

else if (and.Contains("<="))

{

model.FieldName = and.Split(new string[] { "<=" }, StringSplitOptions.RemoveEmptyEntries)[0];

model.ConditionalType = ConditionalType.LessThanOrEqual;

model.FieldValue = and.Split(new string[] { "<=" }, StringSplitOptions.RemoveEmptyEntries)[1];

}

else if (and.Contains(">"))

{

model.FieldName = and.Split(new string[] { ">" }, StringSplitOptions.RemoveEmptyEntries)[0];

model.ConditionalType = ConditionalType.GreaterThan;

model.FieldValue = and.Split(new string[] { ">" }, StringSplitOptions.RemoveEmptyEntries)[1];

}

else if (and.Contains("<"))

{

model.FieldName = and.Split(new string[] { "<" }, StringSplitOptions.RemoveEmptyEntries)[0];

model.ConditionalType = ConditionalType.LessThan;

model.FieldValue = and.Split(new string[] { "<" }, StringSplitOptions.RemoveEmptyEntries)[1];

}

else if (and.Contains("!="))

{

model.FieldName = and.Split(new string[] { "!=" }, StringSplitOptions.RemoveEmptyEntries)[0];

model.ConditionalType = ConditionalType.NoEqual;

model.FieldValue = and.Split(new string[] { "!=" }, StringSplitOptions.RemoveEmptyEntries)[1];

}

else if (and.Contains("="))

{

model.FieldName = and.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[0];

model.ConditionalType = ConditionalType.Equal;

model.FieldValue = and.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries)[1];

}

hw.Add(model);

}

var d = db.Context.Utilities.ConditionalModelToSql(hw);

//tb.Having(d.Key, d.Value);

tb.Having(string.Join(",", havingItems));

}

}

//"@group":"column0,column1..."，分组方式。如果 @column里声明了Table的id，则id也必须在 @group中声明；其它情况下必须满足至少一个条件:

//1.分组的key在 @column里声明

//2.Table主键在 @group中声明

private void PrccessGroup(string subtable, JObject values, ISugarQueryable<ExpandoObject> tb)

{

if (values["@group"].IsValue())

{

var str = new System.Text.StringBuilder(100);

foreach (var and in values["@group"].ToString().Split(','))

{

if (IsCol(subtable, and))

{

str.Append(and + ",");

}

}

tb.GroupBy(str.ToString().TrimEnd(','));

}

}

//处理排序 "@order":"name-,id"查询按 name降序、id默认顺序 排序的User数组

private void ProcessOrder(string subtable, JObject values, ISugarQueryable<ExpandoObject> tb)

{

if (values["@order"].IsValue())

{

foreach (var item in values["@order"].ToString().Split(','))

{

string col = item.Replace("-", "").Replace("+", "");

if (IsCol(subtable, col))

{

if (item.EndsWith("-"))

{

tb.OrderBy($"{col} desc");

}

else if (item.EndsWith("+"))

{

tb.OrderBy($"{col} asc");

}

else

{

tb.OrderBy($"{col}");

}

}

}

}

}

//条件查询 "key{}":"条件0,条件1..."，条件为任意SQL比较表达式字符串，非Number类型必须用''包含条件的值，如'a'

//&, |, ! 逻辑运算符，对应数据库 SQL 中的 AND, OR, NOT。

// 横或纵与：同一字段的值内条件默认 | 或连接，不同字段的条件默认 & 与连接。

// ① & 可用于"key&{}":"条件"等

// ② | 可用于"key|{}":"条件", "key|{}":[] 等，一般可省略

// ③ ! 可单独使用，如"key!":Object，也可像&,|一样配合其他功能符使用

private void ConditionQuery(string subtable, List<IConditionalModel> conModels, KeyValuePair<string, JToken> va)

{

string vakey = va.Key.Trim();

string field = vakey.TrimEnd("{}".ToCharArray());

if (va.Value.HasValues)

{

List<string> inValues = new List<string>();

foreach (var cm in va.Value)

{

inValues.Add(cm.ToString());

}

conModels.Add(new ConditionalModel() { FieldName = field, ConditionalType = field.EndsWith("!") ? ConditionalType.NotIn : ConditionalType.In, FieldValue = string.Join(",", inValues) });

}

else

{

var ddt = new List<KeyValuePair<WhereType, ConditionalModel>>();

foreach (var and in va.Value.ToString().Split(','))

{

var model = new ConditionalModel();

model.FieldName = field.TrimEnd("&".ToCharArray());//处理&()的查询方式

if (and.StartsWith(">="))

{

model.ConditionalType = ConditionalType.GreaterThanOrEqual;

model.FieldValue = and.TrimStart(">=".ToCharArray());

}

else if (and.StartsWith("<="))

{

model.ConditionalType = ConditionalType.LessThanOrEqual;

model.FieldValue = and.TrimStart("<=".ToCharArray());

}

else if (and.StartsWith(">"))

{

model.ConditionalType = ConditionalType.GreaterThan;

model.FieldValue = and.TrimStart('>');

}

else if (and.StartsWith("<"))

{

model.ConditionalType = ConditionalType.LessThan;

model.FieldValue = and.TrimStart('<');

}

ddt.Add(new KeyValuePair<WhereType, ConditionalModel>((field.EndsWith("&") ? WhereType.And : WhereType.Or), model));

}

conModels.Add(new ConditionalCollections() { ConditionalList = ddt });

}

}

//"key%":"start,end" => "key%":["start,end"]，其中 start 和 end 都只能为 Boolean, Number, String 中的一种，如 "2017-01-01,2019-01-01" ，["1,90000", "82001,100000"] ，可用于连续范围内的筛选

private void ConditionBetween(string subtable, List<IConditionalModel> conModels, KeyValuePair<string, JToken> va)

{

string vakey = va.Key.Trim();

string field = vakey.TrimEnd("%".ToCharArray());

List<string> inValues = new List<string>();

if (va.Value.HasValues)

{

foreach (var cm in va.Value)

{

inValues.Add(cm.ToString());

}

}

else

{

inValues.Add(va.Value.ToString());

}

for (var i = 0; i < inValues.Count; i++)

{

var fileds = inValues[i].Split(',');

if (fileds.Length == 2)

{

var ddt = new List<KeyValuePair<WhereType, ConditionalModel>>();

var leftCondition = new ConditionalModel()

{

FieldName = field,

ConditionalType = ConditionalType.GreaterThanOrEqual,

FieldValue = fileds[0]

};

ddt.Add(new KeyValuePair<WhereType, ConditionalModel>(i == 0 ? WhereType.And : WhereType.Or, leftCondition));

var rightCondition = new ConditionalModel()

{

FieldName = field,

ConditionalType = ConditionalType.LessThanOrEqual,

FieldValue = fileds[1]

};

ddt.Add(new KeyValuePair<WhereType, ConditionalModel>(WhereType.And, rightCondition));

conModels.Add(new ConditionalCollections() { ConditionalList = ddt });

}

}

}

//模糊搜索 "key$":"SQL搜索表达式" => "key$":["SQL搜索表达式"]，任意SQL搜索表达式字符串，如 %key%(包含key), key%(以key开始), %k%e%y%(包含字母k,e,y) 等，%表示任意字符

private void FuzzyQuery(string subtable, List<IConditionalModel> conModels, KeyValuePair<string, JToken> va)

{

string vakey = va.Key.Trim();

string fieldValue = va.Value.ToString();

var conditionalType = ConditionalType.Like;

if (IsCol(subtable, vakey.TrimEnd('$')))

{

//支持三种like查询

if (fieldValue.StartsWith("%") && fieldValue.EndsWith("%"))

{

conditionalType = ConditionalType.Like;

}

else if (fieldValue.StartsWith("%"))

{

conditionalType = ConditionalType.LikeRight;

}

else if (fieldValue.EndsWith("%"))

{

conditionalType = ConditionalType.LikeLeft;

}

conModels.Add(new ConditionalModel() { FieldName = vakey.TrimEnd('$'), ConditionalType = conditionalType, FieldValue = fieldValue.TrimEnd("%".ToArray()).TrimStart("%".ToArray()) });

}

}

}

}