/// <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); }