public DataTable LimitDataTable(PageList pagelist) { CheckPageList(pagelist); DataTable T = null; string sql = string.Empty; if (pagelist.sql.IsNotEmpty()) { pagelist.page = (pagelist.page < 1 ? 1 : pagelist.page); pagelist.rows = (pagelist.rows < 10 ? 10 : pagelist.rows); if (pagelist.sort.IsNotEmpty()) { if (pagelist.order.IsEmpty()) { pagelist.order = "asc"; } if (pagelist.sql.Contains("order by")) { pagelist.sql = pagelist.sql.Substring(0, pagelist.sql.IndexOf("order by")); } sql = string.Format(" {0} order by {1} {2} limit {3},{4}", pagelist.sql, pagelist.sort, pagelist.order, ((pagelist.page - 1) * pagelist.rows).ToString(), pagelist.rows.ToString()); } else { sql = pagelist.sql + " limit " + ((pagelist.page - 1) * pagelist.rows).ToString() + "," + pagelist.rows.ToString(); } pagelist.limitvalue = sql; Session["LimitSqlValue"] = sql; CommondController commond = new CommondController(_db); T = commond.GetDataTable(sql); } return(T); }
public ActionResult Index(string sqlValue, bool isSecurityLable = false) { if (Request.Form.AllKeys.Contains("isSecurityLable") || Request.QueryString.ToString().IndexOf("isSecurityLable") > 0) { return(View()); } if (!isSecurityLable /*避免通过 Get 直接请求 Index 方法*/ || sqlValue.Trim().IsEmpty()) { return(View()); } CommondController commond = new CommondController(_db); DataTable T = commond.GetDataTableOneRow(sqlValue); Session["SqlValue"] = sqlValue; ViewBag.sqlValue = sqlValue;//sqlValue.Replace("\r","").Replace("\t"," ").Replace("\n"," "); #region T 不为空的时候 if (T != null && T.Rows.Count > 0) { var CName = T.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); var CType = T.Columns.Cast <DataColumn>().Select(x => x.DataType.FullName).ToArray(); ViewBag.ColumnName = CName; ViewBag.ColumnType = CType; ViewBag.zdString = string.Join(",", CName); ViewBag.lxString = string.Join(",", CType); ViewBag.显示的类型 = "System.TimeSpan,System.Byte[]"; ViewBag.排序字段 = CName[0]; ViewBag.排序方式 = "desc"; } #endregion return(View()); }
public ActionResult SimpleQuery() { string startwhere = string.Empty; string report = Request.Form["report"]; CommondController commond = new CommondController(_db); string sqlValue = commond.GetSqlValue(report, isFillter: false); /*TODO: isFillter:false SimpleQuery*/ if (sqlValue.IsEmpty()) { return(Content("no")); } string[] keys = Request.Form.AllKeys; MYSQLInit init = new MYSQLInit(); try { SimpleSqlInjectMethod(init, sqlValue, keys); int rowEf = commond.GetCount(sqlValue + init.GetCurrentSQL(), init.GetCurrentPara()); if (0 == rowEf) { return(Content("no")); } } catch (Exception ex) { BugLog.Write(ex.ToString()); return(Content("error")); } Session["SqlValue"] = GetSimpleSql(report); return(Content("ok")); }
public JsonResult getJsonFromReport(PageList pagelist) { int total = 0; CommondController commond = new CommondController(_db); string sqlValue = commond.GetSqlValue(pagelist.report, isFillter: true);/*TODO:isFiller:true getJsonFromReport*/ List <Dictionary <string, string> > rows = null; if (sqlValue.IsNotEmpty()) { pagelist.sql = sqlValue; total = commond.GetCount(sqlValue); rows = commond.GetJSON(LimitDataTable(pagelist)); } return(Json(new { total = total, rows = rows }, JsonRequestBehavior.AllowGet)); }
//[GZipOrDeflate] public ActionResult Category(string report, string title) { if (report.IsEmpty()) { return(Redirect("/Report/Index")); } CommondController commond = new CommondController(_db); rpt_categorydetail categoryDetail = commond.GetCategoryDetail(report); string sqlValue = null; if (HttpContext.Request.QueryString["CustomQuery"] != null) { sqlValue = SessionHelper.GetSqlValue().IsEmpty() ? categoryDetail.Sqlvalue : SessionHelper.GetSqlValue(); } if (HttpContext.Request.QueryString["RestSetUp"] == null && HttpContext.Request.QueryString["CustomQuery"] == null) { SessionHelper.RestSqlValue(); SessionHelper.RestTotalName(); } if (categoryDetail == null) { BugLog.Write("report=------" + report); throw new ArgumentException("报表类别为空 请联系管理员;"); } DataTable T = commond.GetDataTableOneRow(sqlValue ?? categoryDetail.Sqlvalue); #region T 不为空的时候 if (T != null && T.Rows.Count > 0) { var CName = T.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); var CType = T.Columns.Cast <DataColumn>().Select(x => x.DataType.FullName).ToArray(); ViewBag.ColumnName = CName; ViewBag.ColumnType = CType; ViewBag.Total = categoryDetail.Total.IsEmpty() ? "" : categoryDetail.Total; ViewBag.zdString = string.Join(",", CName); ViewBag.lxString = string.Join(",", CType); ViewBag.排序字段 = categoryDetail.Sort.IsEmpty() ? CName[0] : CName.Contains(categoryDetail.Sort) ? categoryDetail.Sort : CName[0]; ViewBag.排序方式 = categoryDetail.Order.IsEmpty() ? "desc" : categoryDetail.Order; ViewBag.显示的类型 = "System.TimeSpan,System.Byte[]"; ViewBag.Title = categoryDetail.Detailedname ?? title; ViewBag.report = categoryDetail.Id; } #endregion return(View()); }
public ActionResult ETaoPhoto(string Id /*= "040427cf-0cb9-4ef2-8379-5b63df38e98a"*/) { if (string.IsNullOrEmpty(Id)) { return(View()); } MYSQLInit Sql = new MYSQLInit(); Sql.Append("select idCardImg1 as 'F_idCard',idCardImg2 as 'B_idCard' ,license as 'License' , storeImg1 as 'Store_1', storeImg2 as 'Store_2' ,storeImg3 as 'Store_3' ,`name` ,phone,authenticId from etao_authentic"); Sql.Where("authenticId =", Id); DataTable T = new CommondController(_db).GetDataTableWithParam(Sql.GetCurrentSQL(), Sql.GetCurrentPara()); ETaoPhoto model = T.ConvertTo <ETaoPhoto>().FirstOrDefault(); return(View(model)); }
private ActionResult TotalMethod(PageList pageList, TotalType totalTypes) { if (pageList.report.IsEmpty()) { return(Content("参数不能为空")); } CommondController commond = new CommondController(_db); rpt_categorydetail categoryDetail = commond.GetCategoryDetail(pageList.report); if (categoryDetail == null) { return(Content("参数出错")); } string sqlValue = string.Empty; if (totalTypes == TotalType.PageTotal) { sqlValue = Session["LimitSqlValue"].ToString() ?? ""; } else if (totalTypes == TotalType.TableTotal) { sqlValue = commond.GetSqlValue(pageList.report, isFillter: true); /*TODO: isFillter:true TotalMethod*/ } else { return(Content("错误的请求类型")); } if (sqlValue.IsNotEmpty()) { string orderCountSql = commond.GetOrderCountSqlValue(sqlValue); pageList.total = categoryDetail.Total; string[] totalList = categoryDetail.Total.Split(','); ViewBag.columnname = totalList; string sum = totalList.sumField(); sqlValue = string.Format("select {0} from ({1}) xiaoji", sum, sqlValue); DataSet ds = commond.GetDataSet(orderCountSql + ";" + sqlValue); return(PartialView("_PartialTotal", ds)); } return(Content("sql语句为空")); }
public string CheckSQLSuccess(string sqlValue) { if (sqlValue.Trim().IsEmpty()) { return("False"); } try { int ROWCOUNT = new CommondController(_db).ROWCOUNT(sqlValue); if (ROWCOUNT > 0) { return("True"); } else { return("False"); } } catch (Exception ex) { BugLog.Write("错误的 SQL 语句 " + ex.ToString() + "\n\r" + sqlValue); return("Error"); } }
//private ActionResult SimpleQuery1() //{ // string startwhere = string.Empty; // string report = Request.Form["report"]; // int paraIndex = 0; // List<MySqlParameter> paraList = new List<MySqlParameter>(); // string paraName = string.Empty; // string field = string.Empty; // CommondController commond = new CommondController(_db); // string sqlValue = commond.GetSqlValue(report, isFillter: false); /*TODO: isFillter:false SimpleQuery*/ // System.Text.StringBuilder sb = new System.Text.StringBuilder(); // string[] keys = Request.Form.AllKeys; // try // { // #region 遍历表单值 排除report 跟订单状态 // foreach (string name in keys) // { // if ("report" == name || "订单状态" == name || "__RequestVerificationToken" == name) // { // continue; // } // if (name.Contains("日期1") && Request.Form[name].IsNotEmpty()) // { // field = sqlValue.GetFieldSqlByName(name.Substring(0, name.Length - 1)); // paraName = GetParaName(paraIndex++); // paraList.Add(SetParaValue<string>(paraName,Request.Form[name],MySqlDbType.DateTime)); // sb.AppendFormat(" and {0} > {1} ", field, paraName); // continue; // } // if (name.Contains("日期2") && Request.Form[name].IsNotEmpty()) // { // DateTime endTime = DateTime.Parse(Request.Form[name]).AddDays(1); // var dateStr = endTime.ToString("yyyy-MM-dd"); // field = sqlValue.GetFieldSqlByName(name.Substring(0, name.Length - 1)); // paraName = GetParaName(paraIndex++); // paraList.Add(SetParaValue<string>(paraName, dateStr, MySqlDbType.DateTime)); // sb.AppendFormat(" and {0} < {1} ", field, paraName); // continue; // } // if (Request.Form[name].IsNotEmpty()) // { // field = sqlValue.GetFieldSqlByName(name); // paraName = GetParaName(paraIndex++); // paraList.Add(SetParaValue<string>(paraName, "%" + Request.Form[name] + "%", MySqlDbType.String)); // sb.AppendFormat(" and {0} like {1} ", field, paraName); // } // } // #endregion // #region 遍历订单状态 // if (Request.Form["订单状态"].IsNotEmpty()) // keys.toStringMergeChar(',').Contains("订单状态") // { // string[] status = Request.Form["订单状态"].toStringArray(); // var value = sqlValue.GetFieldSqlByName("订单状态"); // sb.AppendFormat(" and {0} in (", value); // for (int i = 0; i < status.Length; i++) // { // sb.AppendFormat("'{0}',", status[i]); // } // startwhere = sb.ToString().TrimEnd(','); // startwhere += ")"; // } // if (startwhere.IsEmpty()) // { // startwhere = sb.ToString(); // } // #endregion // sqlValue = sqlValue.IndexOf("where", StringComparison.OrdinalIgnoreCase) > -1 // ? // sqlValue + startwhere // : // sqlValue + startwhere.Substring(startwhere.IndexOf(" and", StringComparison.OrdinalIgnoreCase)).Insert(0, " where "); // int rowEf = commond.GetCount(sqlValue,paraList.ToArray()); // if (0 == rowEf) // { // return Content("no"); // } // } // catch (Exception ex) // { // BugLog.Write(ex.ToString()); // return Content("error"); // } // Session["SqlValue"] = sqlValue; // return Content("ok"); //} #endregion #region ---- 报表首页 设置功能 ---- public ActionResult SetUpQuery() { string[] keys = Request.Form.AllKeys; if (keys.Length == 3) { return(Content("nochange")); } #region 处理本页统计 本表统计 避免设置后新的字段不在本页统计里面而报错 存在session["totalname"] 里面 if (!string.IsNullOrWhiteSpace(Request.Form["setuptotalname"])) { System.Text.StringBuilder resultcolumnname = new System.Text.StringBuilder(); string[] setuptotalname = Request.Form["setuptotalname"].toStringArray(); foreach (string column in setuptotalname) { foreach (var formKey in keys) { if (column == formKey) { resultcolumnname.AppendFormat("{0},", column); break; } } } if (resultcolumnname.ToString().IsNotEmpty()) { Session["totalname"] = resultcolumnname.ToString().TrimEnd(','); } else { Session["totalname"] = string.Empty; } } #endregion #region 当 session 不为空的时候 if (Session["SqlValue"] != null) { string tablename = "tablename" + Guid.NewGuid().ToString().Replace("-", ""); System.Text.StringBuilder sb = new System.Text.StringBuilder(); string startfrom = string.Empty; foreach (var item in keys) { if (item != "setupreport" && item != "startfrom" && item != "setuptotalname") { sb.Append(tablename + "." + item + ","); } } string sqlValue = string.Format("select {0} from ({1}){2}", sb.ToString().TrimEnd(','), Session["SqlValue"].ToString(), tablename); Session["SqlValue"] = sqlValue; return(Content("ok")); } #endregion CommondController commond = new CommondController(_db); List <rpt_column> rptColumn = commond.GetRptColumnEntity(Request.Form["setupreport"]); #region 当 rptColumn 不为 null 长度大于0 if (rptColumn != null && rptColumn.Count() > 0) { string startfrom = string.Empty; System.Text.StringBuilder sb = new System.Text.StringBuilder(); foreach (var item in keys) { if (item != "setupreport" && item != "setuptotalname") { if ("startfrom" == item) { startfrom = rptColumn.Where(clo => clo.Columnname == "startfrom").Select(clo => clo.Columnvalue).FirstOrDefault(); continue; } sb.AppendFormat(" {0} ,", rptColumn.Where(clo => clo.Columnname == item).Select(clo => clo.Columnvalue).FirstOrDefault()); } } if (sb.ToString() == string.Empty) { sb.Append("*"); } string sqlValue = string.Format("select {0} from {1}", sb.ToString().TrimEnd(','), startfrom); Session["SqlValue"] = sqlValue; return(Content("ok")); } else { return(Content("nothing")); } #endregion }
private string GetSimpleSql(string report) { string startwhere = string.Empty; CommondController commond = new CommondController(_db); string sqlValue = commond.GetSqlValue(report, isFillter: false); /*TODO: isFillter:false SimpleQuery*/ System.Text.StringBuilder sb = new System.Text.StringBuilder(); string[] keys = Request.Form.AllKeys; #region 遍历表单值 排除report 跟订单状态 foreach (string name in keys) { if ("report" == name || "订单状态" == name || "__RequestVerificationToken" == name) { continue; } if (name.Contains("日期1") && Request.Form[name].IsNotEmpty()) { var value = sqlValue.GetFieldSqlByName(name.Substring(0, name.Length - 1)); sb.AppendFormat(" and {0} > '{1}' ", value, Request.Form[name]); continue; } if (name.Contains("日期2") && Request.Form[name].IsNotEmpty()) { DateTime endTime = DateTime.Parse(Request.Form[name]).AddDays(1); var dateStr = endTime.ToString("yyyy-MM-dd"); var value = sqlValue.GetFieldSqlByName(name.Substring(0, name.Length - 1)); sb.AppendFormat(" and {0} < '{1}' ", value, dateStr); continue; } if (Request.Form[name].IsNotEmpty()) { var value = sqlValue.GetFieldSqlByName(name); sb.AppendFormat(" and {0} like '%{1}%' ", value, Request.Form[name]); } } #endregion #region 遍历订单状态 if (Request.Form["订单状态"].IsNotEmpty()) // keys.toStringMergeChar(',').Contains("订单状态") { string[] status = Request.Form["订单状态"].toStringArray(); var value = sqlValue.GetFieldSqlByName("订单状态"); sb.AppendFormat(" and {0} in (", value); for (int i = 0; i < status.Length; i++) { sb.AppendFormat("'{0}',", status[i]); } startwhere = sb.ToString().TrimEnd(','); startwhere += ")"; } if (startwhere.IsEmpty()) { startwhere = sb.ToString(); } #endregion sqlValue = sqlValue.IndexOf("where", StringComparison.OrdinalIgnoreCase) > -1 ? sqlValue + startwhere : sqlValue + startwhere.Substring(startwhere.IndexOf(" and", StringComparison.OrdinalIgnoreCase) + " and".Length).Insert(0, " where "); return(sqlValue); }
public ActionResult AdvancedQuery() { //高级查询 重新查询所有 (重要) string typeInt = "System.Int32System.Int64System.UInt64System.Int16System.IntSystem.DecimalSystem.SingleSystem.DoubleSystem.SByteSystem.Decima"; string report = Request.Form["report"]; string[] ziduan = Request.Form["ziduan"].Split(new char[] { ',' }); string[] leixing = Request.Form["leixing"].Split(new char[] { ',' }); Dictionary <string, FormValue> dic = new Dictionary <string, FormValue>(); int i = 0; foreach (var item in ziduan) { FormValue fv = new FormValue { name = ziduan[i], DateExit = true, value = Request.Form[ziduan[i]] }; if (leixing[i] == "System.DateTime") { #region System.DateTime if (Request.Form[ziduan[i]].Trim() == string.Empty) { fv.DateExit = false; } if ((Request.Form[ziduan[i] + ziduan[i]]).Trim() == string.Empty) { fv.SecondData = false; } else { fv.SecondData = true; string date = Request.Form[ziduan[i] + ziduan[i]]; fv.maxDataTime = DateTime.Parse(date).AddDays(1).ToString(); } #endregion } else if (typeInt.Contains(leixing[i])) { //存在运算符 if (Request.Form[ziduan[i]].Trim() == string.Empty) { fv.DateExit = false; } else { fv.operatorstr = Request.Form[ziduan[i] + "selectname"]; } } else { if (Request.Form[ziduan[i]].Trim() == string.Empty) { fv.DateExit = false; } } dic.Add(leixing[i] + i.ToString(), fv); i++; } CommondController commond = new CommondController(_db); string sql = commond.GetSqlValue(report, isFillter: false); /*TODO: isFillter:false AdvancedQuery*/ if (sql.IsNotEmpty()) { //old method //处理sql拼接 //sqlString = GetSqlValue(sql, dic, Request.Form["title"]); //getSqlByDict(sql, dic); //var count=commond.GetCount(sqlString); //if (0 == count) //return Content("no"); ////保存当前多条件查询的字符串 //Session["SqlValue"] = sqlString; //return Content("ok"); MYSQLInit sqlInit = new MYSQLInit(); SqlInjectMethod(sql, dic, sqlInit); var listcount = commond.GetCount(sql + sqlInit.GetCurrentSQL(), sqlInit.GetCurrentPara()); if (0 == listcount) { return(Content("no")); } else { Session["SqlValue"] = GetSqlValue(sql, dic, Request.Form["title"]); return(Content("ok")); } } else { return(Content("no")); } }