private void ConvertQueryModel(QueryModel queryModel, PageQuerySqlEntity sqlEntity)
{
//1、Fields——fields
if (queryModel.Fields.IsEmpty())
{
throw new Exception("查询字段不能为空！");
}
//重建表名——将bd.dict_code转换为bd.dict_code 'bd.dict_code',bd.dict_code bd_dict_code
//var fields = queryModel.Fields.ToList();
var fields = new List <string>();
queryModel.Fields.ToList().ForEach(f => fields.Add(string.Format("{0} {1}", f, f.Replace('.', '_'))));
sqlEntity.fields = string.Join(",", fields);
//2、TableRelations——tables
if (queryModel.TableRelation == null || queryModel.TableRelation.PrimeTable.IsEmpty())
{
throw new Exception("查询表不能为空！");
}
StringBuilder sb = new StringBuilder();
ConvertTableRelation(queryModel, sb);
sqlEntity.tables = sb.ToString();
//3、Conditions——where
ConvertConditions(queryModel.Conditions, sqlEntity.where);
//4、Sorts——order
ConvertSorts(queryModel.Sorts, sqlEntity.order);
}
public OptResult Query(QueryModel queryModel)
{
OptResult rst = null;
if (queryModel == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_ExeQuery + "，查询模型参数不能为空！");
return(rst);
}
try
{
PageQuerySqlEntity sqlEntity = _cqRep.GetPageQuerySql(SqlName_PageQuery);
if (sqlEntity == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_ExeQuery + "，未能获取sql配置！");
return(rst);
}
//1、查询模型转换
ConvertQueryModel(queryModel, sqlEntity);
//2、执行查询
IEnumerable <dynamic> data = _cqRep.PageQueryBySp <dynamic>(sqlEntity: sqlEntity, page: queryModel.Page);
rst = OptResult.Build(ResultCode.Success, Msg_ExeQuery, new
{
total = queryModel.Page.total,
pagecount = queryModel.Page.pageTotal,
rows = data
});
}
catch (Exception ex)
{
LogHelper.LogError(Msg_ExeQuery, ex);
rst = OptResult.Build(ResultCode.DbError, Msg_ExeQuery);
}
return(rst);
}
public OptResult QueryByPage(PageQuery page)
{
OptResult rst = null;
if (page == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + "，分页参数不能为空！");
return(rst);
}
PageQuerySqlEntity sqlEntity = _perRep.GetPageQuerySql(SqlName_PageQuery);
if (sqlEntity == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + "，未能获取sql配置！");
return(rst);
}
//构造where
#region where条件
if (page.conditions != null && page.conditions.Count > 0)
{
if (page.conditions.ContainsKey("per_code") && !page.conditions["per_code"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and p.per_code='{0}' ", page.conditions["per_code"]);
}
if (page.conditions.ContainsKey("per_name") && !page.conditions["per_name"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and p.per_name like '%{0}%' ", page.conditions["per_name"]);
}
if (page.conditions.ContainsKey("per_type") && !page.conditions["per_type"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and p.per_type = '{0}' ", page.conditions["per_type"]);
}
if (page.conditions.ContainsKey("per_parent") && !page.conditions["per_parent"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and p.per_parent = '{0}' ", page.conditions["per_parent"]);
}
else if (page.conditions.ContainsKey("per_parent_name") && !page.conditions["per_parent_name"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and pp.per_name like '%{0}%' ", page.conditions["per_parent_name"]);
}
}
#endregion
try
{
var pers = _perRep.PageQueryBySp <dynamic>(sqlEntity: sqlEntity, page: page);
rst = OptResult.Build(ResultCode.Success, Msg_QueryByPage, new
{
total = page.total,
pagecount = page.pageTotal,
rows = pers
});
}
catch (Exception ex)
{
LogHelper.LogError(Msg_QueryByPage, ex);
rst = OptResult.Build(ResultCode.DbError, Msg_QueryByPage);
}
return(rst);
}
public OptResult QueryByPage(PageQuery page)
{
OptResult rst = null;
if (page == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + "，分页参数不能为空！");
return(rst);
}
PageQuerySqlEntity sqlEntity = _fieldRep.GetPageQuerySql(SqlName_PageQuery);
if (sqlEntity == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_QueryByPage + "，未能获取sql配置！");
return(rst);
}
//构造where
#region where条件
if (page.conditions != null && page.conditions.Count > 0)
{
if (page.conditions.ContainsKey("tbname") && !page.conditions["tbname"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and qt.tbname like '%{0}%' ", page.conditions["tbname"]);
}
if (page.conditions.ContainsKey("tbalias") && !page.conditions["tbalias"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and qt.alias like '%{0}%' ", page.conditions["tbalias"]);
}
if (page.conditions.ContainsKey("tbid") && !page.conditions["tbid"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and qf.tbid = '{0}' ", page.conditions["tbid"]);
}
if (page.conditions.ContainsKey("fieldname") && !page.conditions["fieldname"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and qf.fieldname like '%{0}%' ", page.conditions["fieldname"]);
}
if (page.conditions.ContainsKey("displayname") && !page.conditions["displayname"].IsEmpty())
{
sqlEntity.where.AppendFormat(" and qf.displayname like '%{0}%' ", page.conditions["displayname"]);
}
}
#endregion
try
{
var fields = _fieldRep.PageQueryBySp <FieldDto>(sqlEntity: sqlEntity, page: page);
rst = OptResult.Build(ResultCode.Success, Msg_QueryByPage, new
{
total = page.total,
pagecount = page.pageTotal,
rows = fields
});
}
catch (Exception ex)
{
LogHelper.LogError(Msg_QueryByPage, ex);
rst = OptResult.Build(ResultCode.DbError, Msg_QueryByPage);
}
return(rst);
}
private DynamicParameters BuildParamsForPageQueryBySp(PageQuerySqlEntity sqlEntity, PageQuery page)
{
var param = new DynamicParameters();
param.Add("_fields", sqlEntity.fields);
param.Add("_tables", sqlEntity.tables);
param.Add("_where", sqlEntity.where.ToString());
param.Add("_orderby", sqlEntity.order.ToString());
param.Add("_pageindex", page.pageIndex, dbType: System.Data.DbType.Int32);
param.Add("_pageSize", page.pageSize, dbType: System.Data.DbType.Int32);
param.Add("_totalcount", page.total, dbType: System.Data.DbType.Int32, direction: ParameterDirection.Output);
param.Add("_pagecount", page.pageTotal, dbType: System.Data.DbType.Int32, direction: ParameterDirection.Output);
return(param);
}
public IEnumerable <TReturn> PageQueryBySp <TFirst, TSecond, TReturn>(PageQuerySqlEntity sqlEntity, PageQuery page, Func <TFirst, TSecond, TReturn> map, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int?commandTimeout = null)
{
try
{
var param = BuildParamsForPageQueryBySp(sqlEntity, page);
var lst = this.DbSession.Connection.Query <TFirst, TSecond, TReturn>(sql: sqlEntity.sp_name, map: map, param: param,
transaction: transaction, buffered: buffered, splitOn: splitOn, commandTimeout: commandTimeout, commandType: CommandType.StoredProcedure);
page.total = param.Get <Int32>("_totalcount");
page.pageTotal = param.Get <Int32>("_pagecount");
return(lst);
}
catch
{
Dispose();
throw;
}
}
public OptResult QueryByPage(PageQuery page)
{
OptResult rst = null;
if (page == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_PageQuery + "，分页参数不能为空！");
return(rst);
}
PageQuerySqlEntity sqlEntity = _cardRep.GetPageQuerySql(SqlName_PageQuery);
if (sqlEntity == null)
{
rst = OptResult.Build(ResultCode.ParamError, Msg_PageQuery + "，未能获取sql配置！");
return(rst);
}
if (page.conditions != null && page.conditions.Count > 0)
{
if (page.conditions.ContainsKey("idcard") && page.conditions["idcard"].IsNotEmpty())
{
sqlEntity.where.AppendFormat(" and cn.idcard='{0}' ", page.conditions["idcard"]);
}
}
try
{
var cards = _cardRep.PageQueryBySp <dynamic>(sqlEntity: sqlEntity, page: page);
rst = OptResult.Build(ResultCode.Success, Msg_PageQuery, new
{
total = page.total,
pagecount = page.pageTotal,
rows = cards
});
}
catch (Exception ex)
{
LogHelper.LogError(Msg_PageQuery, ex);
rst = OptResult.Build(ResultCode.DbError, Msg_PageQuery);
}
return(rst);
}