/// <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);
}
public async Task <ResultObj <Dictionary <string, object> > > getListData(QuerySearchDto querySearchModel)
{
ResultObj <Dictionary <string, object> > reObj = new ResultObj <Dictionary <string, object> >();
try
{
if (querySearchModel.whereList == null && !string.IsNullOrWhiteSpace(querySearchModel.whereListStr))
{
querySearchModel.whereList = TypeChange.ToJsonObject <List <QueryRowBtnShowCondition> >(querySearchModel.whereListStr);
}
if (querySearchModel.paraList == null && !string.IsNullOrWhiteSpace(querySearchModel.paraListStr))
{
querySearchModel.paraList = TypeChange.ToJsonObject <List <QueryPara> >(querySearchModel.paraListStr);
}
//添加AdministratorModel对象加入到参数列表中,以方便在Query里进行过滤
if (querySearchModel.paraList == null)
{
querySearchModel.paraList = new List <QueryPara>();
}
List <QueryPara> QueryPara = new List <QueryPara>();
if (querySearchModel.paraList == null)
{
querySearchModel.paraList = new List <QueryPara>();
}
querySearchModel.paraList.AddRange(QueryPara);
reObj = await _query.QueryPageExecute(querySearchModel);
// reObj.Msg="";
// Session[string.Format("SQL_{0}", querySearchModel.Code)] = sqlStr;
}
catch (Exception ex)
{
LogHelper.WriteErrorLog(typeof(QueryController), ex.ToString());
reObj.success = false;
reObj.msg = ex.Message;
}
return(reObj);
}
public async Task <IActionResult> downFileGet(string code, int page, int rows, string order, string sort)
{
QuerySearchDto postJson = new QuerySearchDto();
postJson.code = code;
postJson.sort = sort;
postJson.order = order;
postJson.page = 1;
postJson.rows = 1000000000;
// var reData =await _query.QueryExecuteCsv(querySearchModel);
// Session[string.Format("SQL_{0}", querySearchModel.Code)] = sqlStr;
var tmepObj = await _query.QueryExecuteCsv(postJson);
if (tmepObj.success)
{
return(File(tmepObj.data.ToArray(), "application/octet-stream", string.Format("{0}.csv", postJson.code)));
}
return(Ok(tmepObj));
}
/// <summary>
/// 根据Query的SQL 生成需要的SQL
/// </summary>
/// <param name="inEnt"></param>
/// <param name="querySql"></param>
/// <param name="whereStr"></param>
/// <returns></returns>
public string MakeSql(QuerySearchDto inEnt, string querySql, ref string whereStr)
{
if (inEnt.paraList == null)
{
inEnt.paraList = new List <QueryPara>();
}
if (inEnt.whereList == null)
{
if (string.IsNullOrEmpty(inEnt.whereListStr))
{
inEnt.whereList = new List <QueryRowBtnShowCondition>();
}
else
{
inEnt.whereList = TypeChange.ToJsonObject <List <QueryRowBtnShowCondition> >(inEnt.whereListStr);
}
}
if (inEnt.paraList == null)
{
if (string.IsNullOrEmpty(inEnt.paraListStr))
{
inEnt.paraList = new List <QueryPara>();
}
else
{
inEnt.paraList = TypeChange.ToJsonObject <List <QueryPara> >(inEnt.paraListStr);
}
}
//替换地址参数
foreach (var tmp in inEnt.paraList)
{
if (tmp.value == "@(NOWDATA)")
{
tmp.value = DateTime.Today.ToString("yyyy-MM-dd");
}
querySql = querySql.Replace("@(" + tmp.paraName + ")", tmp.value);
}
//替换搜索的参数
foreach (var tmp in inEnt.whereList)
{
if (string.IsNullOrEmpty(tmp.objFiled))
{
tmp.objFiled = tmp.fieldName;
}
if (string.IsNullOrEmpty(tmp.fieldName))
{
tmp.fieldName = tmp.objFiled;
}
querySql = querySql.Replace("@(" + tmp.objFiled + ")", tmp.value);
}
StringBuilder whereSb = new StringBuilder();
foreach (var tmp in inEnt.whereList.Where(x => x.opType != null && !string.IsNullOrEmpty(x.opType) && !string.IsNullOrEmpty(x.value)))
{
if (tmp.fieldType == null)
{
tmp.fieldType = "string";
}
var nowType = tmp.fieldType.ToLower();
int subIndex = tmp.fieldType.IndexOf(".");
if (subIndex > -1)
{
nowType = nowType.Substring(subIndex + 1);
}
switch (nowType)
{
case "text":
case "string":
switch (tmp.opType)
{
case "in":
whereSb.Append(string.Format(" {0} {1} ('{2}') and ", tmp.objFiled, tmp.opType, tmp.value.Replace(",", "','")));
break;
default:
if (tmp.opType == "like")
{
tmp.value = "%" + tmp.value + "%";
}
whereSb.Append(string.Format(" {0} {1} '{2}' and ", tmp.objFiled, tmp.opType, tmp.value));
break;
}
break;
case "date":
case "datetime":
switch (tmp.opType)
{
case "not between":
case "between":
whereSb.Append(string.Format(" {0} {1} '{2}' and ", tmp.objFiled, tmp.opType, string.Join("' and '", tmp.value.Split('~').ToList().Select(x => x.Trim()))));
break;
default:
whereSb.Append(string.Format(" {0} {1} '{2}' and ", tmp.objFiled, tmp.opType, tmp.value));
break;
}
break;
default:
if (tmp.opType == "in")
{
whereSb.Append(string.Format(" {0} {1} ('{2}') and ", tmp.objFiled, tmp.opType, tmp.value.Replace(",", "','")));
}
else
{
whereSb.Append(string.Format(" {0} {1} {2} and ", tmp.objFiled, tmp.opType, tmp.value));
}
break;
}
}
if (whereSb.Length > 4)
{
whereSb = whereSb.Remove(whereSb.Length - 4, 4);
}
whereStr = whereSb.ToString();
return(querySql);
}
/// <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);
}
}
/// <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);
}
