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