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));
}
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语句为空"));
}
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"));
}
}