/// <summary>
/// 分析DataKey中Model属性的字符串，分离出主从各表信息用于
/// 下一步生成表连接子句
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
IEnumerable <JoinInfo> GetJoinInfos(DataGateKey key)
{
if (key.Model.IsEmpty())
{
return(null);
}
key.Model = Regex.Replace(key.Model, "\\s+", " ");
string[] models = key.Model.Replace(">", ">%").Replace("=", "=%").Split('%');
return(models.Select(n =>
{
n = n.Trim();
string joinFlag = null;
string alias = null;
string name = null;
if (n.EndsWith(">") || n.EndsWith("="))
{
joinFlag = n.Last().ToString();
n = n.Substring(0, n.Length - 1);
}
var arr = n.Split(' ');
name = arr[0];
if (arr.Length > 1)
{
alias = arr[1];
}
TableMeta tm = null;
//如果Models.json中没有定义表模型，则找Keys.json中
//的QueryFields属性生成一个表模型并放到模型字典中
if (!_tableMetas.ContainsKey(name))
{
if (key.QueryFields.IsEmpty())
{
throw new Exception($"在解析[Key:{key.Key}]的过程中，没有找到[Model:{name}]的定义：在*Models.json中定义的模型，" +
$"或者{nameof(DataGateKey.QueryFields)}中定义的字段列表");
}
tm = CreateTableMeta(key.Model, new JValue(key.QueryFields));
_tableMetas.Add(tm.Name, tm);
}
else
{
tm = _tableMetas[name];
}
var db = GetTempDB(key);
TranslateModelNames(db, tm);
var joinInfo = new JoinInfo
{
Name = name,
Table = tm,
Alias = alias ??
//当模型名和数据库实际名称不对应时，（是手动配置指定的），则用模型名作查询的别名
(tm.DbName == db.GetDbObjName(tm.Name) ? null : tm.Name),
JoinFlag = joinFlag
};
return joinInfo;
}));
}
private void CheckPlusField(TableMeta tm)
{
_callDeepth++;
if (_callDeepth > 10)
{
throw new Exception($"递归层次太多，是否产生了循环引用在模型：'{tm.Name}'?");
}
int cnt = tm.Fields.Count;
//从后往前循环加，是为了让排后面的属性优先级高于前面的同名属性
for (int i = cnt - 1; i >= 0; i--)
{
if (tm.Fields[i].Name.StartsWith("+"))
{
var name = tm.Fields[i].Name.Substring(1);
var child = _tableMetas[name];
CheckPlusField(child);
tm.Fields.RemoveAt(i);
int j = i;
foreach (var field in child.Fields.ToArray())
{
//如果已有相同属性则跳过
if (tm.Fields.All(f => !f.Name.Equals(field.Name, StringComparison.OrdinalIgnoreCase)))
{
tm.Fields.Insert(j++, field);
}
}
}
}
_callDeepth--;
}
//更新单条记录的条件固定为主键值相等
private string CreateKeyFilter(TableMeta tableMeta)
{
var r = String.Join(" AND ", tableMeta.Fields.Where(t => t.PrimaryKey)
.Select(t => $"{t.FixDbName}=@{t.DbName}"));
if (r.IsEmpty())
{
throw new Exception($"PrimaryKey(s) not defined in {tableMeta.Name}");
}
return(r);
}
void TranslateModelNames(DBHelper db, TableMeta tm)
{
tm.DbName = tm.DbName ?? db.GetDbObjName(tm.Name);
tm.FixDbName = db.AddFix(tm.DbName);
foreach (var fm in tm.Fields)
{
if (fm.DbName.IsEmpty())
{
fm.DbName = db.GetDbObjName(fm.Name);
}
fm.FixDbName = db.AddFix(fm.DbName);
}
}
/// <summary>
/// 在结果集中对主表数据去重，并获取子表明细数据放到主表指定字段值中
/// </summary>
/// <param name="tableMeta"></param>
/// <param name="dt"></param>
/// <returns></returns>
private JArray CreateMasterArray(TableMeta tableMeta, DataTable dt)
{
var pkey = tableMeta.PrimaryKey;
JArray newDt = new JArray();
var ids = dt.Rows.Cast <DataRow>().Select(dr => dr[pkey.DbName]).Distinct();
ids.Each(id =>
{
var drs = dt.Rows.Cast <DataRow>().Where(dr => dr[pkey.DbName].Equals(id));
var firstDr = drs.First();
var newRow = new JObject();
tableMeta.Fields.Each(col =>
{
if (col.IsArray)
{
newRow[col.Name] = GetChildItems(col, drs);
return;
}
else if (col.UIType == Consts.OperatorUIType)
{
return;
}
if (col.ForeignField.IsEmpty() && !dt.Columns.Contains(col.DbName))
{
return;
}
if (!col.ForeignField.IsEmpty() && !dt.Columns.Contains(col.Name))
{
return;
}
//在定义了外表字段时，表查询中是用别名表示外表字段名，所以这里应该分开处理
if (!col.ForeignField.IsEmpty())
{
if (dt.Columns.Contains(col.Name))
{
newRow[col.Name] = new JValue(firstDr[col.Name]);
}
}
else
{
newRow[col.Name] = new JValue(firstDr[col.DbName]);
}
});
newDt.Add(newRow);
});
return(newDt);
}
//在新增记录时，对于单一主键的记录，检查主键字段是否自增或是guid,是否值为空,
//如果是自增，则去掉传过来的值，由库自动生成，如果是guid并且为空，则生成一个guid
private void CheckPrimaryKey(TableMeta tableMeta, IDictionary <string, object> psin, out string id, out string getMaxIdSql)
{
id = null;
getMaxIdSql = null;
var pKeyField = tableMeta.PrimaryKey;
if (pKeyField == null)
{
return;
}
if (pKeyField.DataType == "Number")
{
getMaxIdSql = $"select max({pKeyField.FixDbName}) from {tableMeta.FixDbName}";
}
var pkey = psin.Keys.FirstOrDefault(k => k.Equals(pKeyField.Name, StringComparison.OrdinalIgnoreCase));
//没有传主键字段过来
if (pkey == null)
{
pkey = pKeyField.Name;
psin.Add(pkey, null);
}
//当主键为Number型时，认为是自增字段，为空时从参数中去掉，让数据库自动生成
if (pKeyField.DataType == "Number")
{
psin.Remove(pkey);
}
//非number型，并且为空，则认为是32位的guid字符串，为它自动生成
else if (CommOp.IsEmpty(psin[pkey]))
{
id = CommOp.NewId();
psin[pkey] = id;
}
else
{
id = CommOp.ToStr(psin[pkey]);
}
}
//生成排序子句,排序子句的传入规则是 field1 a/d field2 a/d ...
private void CreateOrderStr(DataGateKey gkey, TableMeta tableMeta, IDictionary <string, object> ps)
{
if (!ps.ContainsKey(Consts.SortKey))
{
return;
}
var sortStr = CommOp.ToStr(ps[Consts.SortKey]);
ps.Remove(Consts.SortKey);
var sortArr = sortStr.Split(' ');
List <string> sorts = new List <string>();
for (var i = 0; i < sortArr.Length - 1; i += 2)
{
string f = sortArr[i];
string field = GetSortField(f, gkey);
if (field.IsEmpty())
{
continue;
}
string ad = sortArr[i + 1];
if (ad.StartsWith("d"))
{
sorts.Add(field + " desc");
}
else
{
sorts.Add(field.ToStr());
}
}
string orderby = String.Join(",", sorts);
if (!orderby.IsEmpty())
{
gkey.OrderBy = orderby;
}
}
//将DataTable结果表中来自数据库的名称转成对象属性名称
private void ReNameColumns(TableMeta tableMeta, DataTable dt)
{
//tableMeta==null表示是直接执行的sql，Model为空
if (tableMeta == null)
{
foreach (DataColumn dc in dt.Columns)
{
dc.ColumnName = DB.DbNameConverter.ToPropName(dc.ColumnName);
}
}
else
{
var fieldsDict = tableMeta.Fields
.ToDictionary(f => f.DbName, f => f.Name);
foreach (DataColumn dc in dt.Columns)
{
if (fieldsDict.ContainsKey(dc.ColumnName))
{
dc.ColumnName = fieldsDict[dc.ColumnName];
}
}
}
}
/// <summary>
/// 根据传过来的地址栏filter参数来获取查询条件
/// 与服务端配置的filter合并
/// </summary>
/// <param name="tableMeta"></param>
/// <param name="ps"></param>
/// <returns></returns>
private void CreateFilterStr(DataGateKey gkey, TableMeta tableMeta, IDictionary <string, object> ps)
{
if (!ps.ContainsKey(Consts.FilterKey))
{
return;
}
var filterStr = CommOp.ToStr(ps[Consts.FilterKey]);
var requests = JsonConvert.DeserializeObject <FilterRequest[]>(filterStr);
ps.Remove(Consts.FilterKey);
filterStr = string.Join(" and ", requests.Select(r =>
{
var field = tableMeta.Fields.FirstOrDefault(f => f.Name.Equals(r.Name, StringComparison.OrdinalIgnoreCase));
if (field == null)
{
return(null);
}
string left = field.ForeignField.IsEmpty() ? (gkey.TableJoins[0].Alias ?? tableMeta.Name) + "." + field.Name : field.ForeignField;
//当有sql语句并且有模型定义时
if (!gkey.Sql.IsEmpty() && gkey.TableJoins.Count > 0)
{
left = field.Name;
}
string pName = r.Name + "_f"; //加后缀以免和未知的key冲突
ps[pName] = r.Value;
switch (r.Operator)
{
case "e":
return($"{left}=@{pName}");
//判断日期相等，日期相等比较特殊，很难精确相等，
//因此转成只判断是否在当天
case "de":
var date = CommOp.ToDateTime(r.Value).Date;
var date1 = date.AddDays(1).AddTicks(-1);
ps[pName] = date;
ps[pName + 1] = date1;
return($"{left} between @{pName} and @{pName}1");
case "ne":
return($"{left}!=@{pName}");
case "in":
ps[pName] = CommOp.ToStr(ps[pName]).Split(',');
return($"{left} in (@{pName})");
case "nin":
ps[pName] = CommOp.ToStr(ps[pName]).Split(',');
return($"{left} not in (@{pName})");
case "i":
ps[pName] = "%" + r.Value + '%';
return($"{left} like @{pName}");
case "ni":
ps[pName] = "%" + r.Value + '%';
return($"{left} not like @{pName}");
case "lte":
return($"{left} <= @{pName}");
case "gte":
return($"{left} >= @{pName}");
case "bt":
if (r.Value1.IsDefault())
{
return($"{left} >= @{pName}");
}
if (field.DataType == "Date" || field.DataType == "DateTime")
{
r.Value1 = CommOp.ToDateTime(r.Value1).Date.AddDays(1).AddTicks(-1);
}
ps[pName + 1] = r.Value1;
return($"{left} between @{pName} and @{pName}1");
case "n":
return($"{left} is null");
case "nn":
return($"{left} is not null");
default:
return(null);
//throw new ArgumentException("非法的查询请求:" + r.Operator);
}
}).Where(r => r != null));
//与原有的gkey.Filter合并得到一个and条件
if (!filterStr.IsEmpty())
{
gkey.Filter = gkey.Filter.IsEmpty() ? filterStr : $"({gkey.Filter}) and {filterStr}";
}
}
/// <summary>
/// 在结果集中对主表数据去重，并获取子表明细数据放到主表指定字段值中
/// </summary>
/// <param name="tableMeta"></param>
/// <param name="dt"></param>
/// <returns></returns>
private JArray CreateMasterArray(TableMeta tableMeta, DataTable dt)
{
var pkeys = tableMeta.PrimaryKeys;
JArray newDt = new JArray();
var getPKeyValues = new Func <DataRow, string>(dr =>
{
var keyValues = pkeys.Select(pkey =>
{
return(dr[pkey.DbName].ToString());
});
return(String.Join("^", keyValues));
});
//查询主表中不重复的主键字段值
var keyRowsGroup = dt.Rows.Cast <DataRow>().GroupBy(dr => getPKeyValues(dr)).ToList();
//遍历DataTable结果集， 找到相同的主键DataRow，并组装子表所在子集合
keyRowsGroup.ForEach(drs =>
{
var newRow = new JObject();
//遍历主表的每个字段，找出各类字段定义
tableMeta.Fields.Each(fm =>
{
if (fm.IsArray)
{
_deep = 0;
newRow[fm.Name] = GetChildItems(fm, drs);
return;
}
else if (fm.DataType == Consts.OperatorUIType)
{
return;
}
if (fm.ForeignField.IsEmpty() && !dt.Columns.Contains(fm.DbName))
{
return;
}
if (!fm.ForeignField.IsEmpty() && !dt.Columns.Contains(fm.Name))
{
return;
}
var firstDr = drs.First();
//在定义了外表字段时，表查询中是用别名表示外表字段名，所以这里应该分开处理
if (!fm.ForeignField.IsEmpty())
{
if (dt.Columns.Contains(fm.Name))
{
newRow[fm.Name] = new JValue(firstDr[fm.Name]);
}
}
else
{
newRow[fm.Name] = new JValue(firstDr[fm.DbName]);
}
});
newDt.Add(newRow);
});
return(newDt);
}
/// <summary>
/// 判断是否是虚表（name和DbName不对应）
/// </summary>
/// <param name="meta"></param>
/// <returns></returns>
private bool IsVirtualTable(TableMeta meta)
{
return(DB.GetDbObjName(meta.Name) != meta.DbName);
}
/// <summary>
/// 根据名称查找字段，不分大小写
/// </summary>
/// <param name="gkey"></param>
/// <param name="name"></param>
/// <returns></returns>
public static FieldMeta GetField(this TableMeta tableMeta, string name)
{
return(tableMeta.Fields.FirstOrDefault(f => f.Name.Equals(name, StringComparison.OrdinalIgnoreCase)));
}