/// <summary>
/// 获取所有数据
/// msg为SQL语句
/// </summary>
/// <param name="inEnt"></param>
/// <returns></returns>
public async Task <ResultObj <Dictionary <string, object> > > QueryExecute(QuerySearchDto inEnt)
{
ResultObj <Dictionary <string, object> > reObj = new ResultObj <Dictionary <string, object> >();
Dictionary <string, object> reEnt = new Dictionary <string, object>();
SysQueryEntity query = await dal.Single(i => i.code == inEnt.code);
if (query == null)
{
return(reObj);
}
IList <QueryCfg> cfg = TypeChange.ToJsonObject <List <QueryCfg> >(query.queryCfgJson);
string whereStr = "";
string AllSql = MakeSql(inEnt, query.queryConf, ref whereStr);
reObj.msg = AllSql;
if (string.IsNullOrEmpty(inEnt.orderStr))
{
inEnt.orderStr = "(SELECT 0)";
}
try
{
reObj.dataList = dal.Query(AllSql);
reObj.total = reObj.dataList.Count();
}
catch
{
return(reObj);
}
return(reObj);
}
/// <summary>
/// 获取Csv数据,支持大数据下载
/// </summary>
/// <param name="inEnt"></param>
/// <param name="sqlStr"></param>
/// <returns></returns>
public async Task <ResultObj <List <byte> > > QueryExecuteCsv(QuerySearchDto inEnt)
{
ResultObj <List <byte> > reObj = new ResultObj <List <byte> >();
List <byte> reEnt = new List <byte>();
SysQueryEntity query = await dal.Single(i => i.code == inEnt.code);
if (query == null)
{
return(reObj);
}
JObject cfg = TypeChange.JsonToObject(query.queryCfgJson);
Dictionary <string, string> dict = new Dictionary <string, string>();
foreach (var item in cfg)
{
try
{
dict.Add(item.Key, item.Value["title"].ToString());
}
catch
{
}
}
string whereStr = "";
string AllSql = MakeSql(inEnt, query.queryConf, ref whereStr);
//如果条件为空
if (!string.IsNullOrEmpty(whereStr))
{
if (!whereStr.Trim().ToLower().StartsWith("where"))
{
whereStr = "where " + whereStr;
}
AllSql = string.Format(@"
SELECT T.* FROM
(
{0}
) T {1}
", AllSql, whereStr);
}
try
{
reObj.msg = AllSql;
reObj.data = dal.ExecuteBytesAsync(AllSql, null, dict);
}
catch (Exception e)
{
reObj.msg = e.Message;
reObj.success = false;
return(reObj);
}
return(reObj);
}
/// <summary>
/// 执行分页数据
/// </summary>
/// <param name="inEnt"></param>
/// <param name="sqlStr"></param>
/// <returns></returns>
public async Task <ResultObj <Dictionary <string, object> > > QueryPageExecute(QuerySearchDto inEnt)
{
ResultObj <Dictionary <string, object> > reObj = new ResultObj <Dictionary <string, object> >();
Dictionary <string, object> reEnt = new Dictionary <string, object>();
SysQueryEntity query = await dal.Single(i => i.code == inEnt.code);
if (query == null)
{
return(reObj);
}
string whereStr = "";
string AllSql = MakeSql(inEnt, query.queryConf, ref whereStr);
if (string.IsNullOrWhiteSpace(inEnt.orderStr))
{
inEnt.orderStr = "(SELECT 0)";
}
reObj.msg = MakePageSql(AllSql, inEnt.page, inEnt.rows, inEnt.orderStr, whereStr);
try
{
var sqlList = reObj.msg.Split(';');
if (sqlList.Count() > 0)
{
reObj.dataList = dal.Query(sqlList[0]);
}
if (sqlList.Count() > 1)
{
int allNum = 0;
int.TryParse(await dal.ExecuteScalarAsync(sqlList[1]), out allNum);
reObj.total = allNum;
}
}
catch (Exception e)
{
LogHelper.WriteErrorLog(this.GetType(), "执行分页数据失败", e);
return(reObj);
}
return(reObj);
}
/// <summary>
/// 生成配置数据
/// </summary>
/// <param name="code"></param>
/// <param name="sqlStr"></param>
/// <returns></returns>
public async Task <ResultObj <string> > MakeQueryCfg(string code)
{
ResultObj <String> reObj = new ResultObj <String>();
QuerySearchDto inEnt = new QuerySearchDto()
{
code = code
};
JObject reEnt = new JObject();
SysQueryEntity query = await dal.Single(i => i.code == inEnt.code);
if (query == null)
{
return(reObj);
}
IList <QueryCfg> cfg = new List <QueryCfg>();
if (!string.IsNullOrEmpty(query.queryCfgJson))
{
cfg = TypeChange.ToJsonObject <List <QueryCfg> >(query.queryCfgJson);
}
string whereStr = "";
string AllSql = MakeSql(inEnt, query.queryConf, ref whereStr);
reObj.msg = MakePageSql(AllSql);
try
{
DataTable dt = dal.GetDataTable(reObj.msg);
for (int i = 0; i < dt.Columns.Count; i++)
{
var t = dt.Columns[i];
var tmp = t.DataType.FullName.ToLower().Substring(t.DataType.FullName.IndexOf(".") + 1);
IList <string> numberList = new[] { "int", "decimal", "double", "int64", "int16" };
if (numberList.Contains(tmp))
{
tmp = "int";
}
string searchType = "";
switch (tmp)
{
case "int":
searchType = "numberbox";
break;
case "datetime":
searchType = "datetimebox";
break;
default:
searchType = "text";
break;
}
JObject itemObj = new JObject();
itemObj["title"] = t.ColumnName;
itemObj["type"] = searchType;
itemObj["editable"] = true;
reEnt[t.ColumnName] = itemObj;
}
reObj.data = reEnt.ToString();
return(reObj);
}
catch
{
return(reObj);
}
}
