public DataSet GetDataSet(string sqlValue)
{
DataSet ds = new DataSet();
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(ds);
}
public DataSet GetDataSet(string sql, object[] para)
{
DataSet ds = new DataSet();
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
if (para != null && para.Length > 0)
{
cmd.Parameters.AddRange(para);
}
da.Fill(ds);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(ds);
}
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 static string GetMD5String(this string input)
{
if (null != input && !"".Equals(input))
{
string s = null;
char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8',
'9', 'a', 'b', 'c', 'd', 'e', 'f' };
try
{
MD5 md = MD5.Create();
byte[] tmp = md.ComputeHash(Encoding.UTF8.GetBytes(input));
char[] str = new char[16 * 2];
int k = 0;
for (int i = 0; i < 16; i++)
{
byte byte0 = tmp[i];
str[k++] = hexDigits[byte0 >> 4 & 0xf];
str[k++] = hexDigits[byte0 & 0xf];
}
s = new String(str);
}
catch (Exception e)
{
BugLog.Write(e.ToString());
}
return(s);
}
else
{
return(null);
}
}
// GET: BugLogs/Details/5
public ActionResult Details(int id)
{
BugLog bug = bugRepository.GetBugByID(id);
ViewBag.Bugs = bugRepository.GetBugByID(id);
ViewBag.Message = "Bug Detail";
return View(bug);
}
public DataTable GetDataTable(string sqlValue, /*
* MySqlParameter[] dic*/Array dic = null)
{
DataTable dt = new DataTable();
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
if (dic != null && dic.Length > 0)
{
cmd.Parameters.AddRange(dic);
}
da.Fill(dt);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(dt);
}
public BugLog SaveBugLog(BugLog bugLog)
{
using (var repositoriesContainer = new LmPlatformRepositoriesContainer())
{
repositoriesContainer.BugLogsRepository.Save(bugLog);
repositoriesContainer.ApplyChanges();
}
return(bugLog);
}
public ActionResult Edit(BugLog bug)
{
try
{
// TODO: Add update logic here
bugRepository.UpdateBug(bug);
return RedirectToAction("Index");
}
catch
{
return View(bug);
}
}
public ActionResult Create(BugLog bug)
{
try
{
// TODO: Add insert logic here
bugRepository.AddBug(bug);
return RedirectToAction("Index");
}
catch
{
return View(bug);
}
}
public ActionResult Delete(int id, FormCollection collection)
{
BugLog bug = bugRepository.GetBugByID(id);
try
{
bugRepository.DeleteBug(bug);
return RedirectToAction("Index");
}
catch
{
return View(bug);
}
}
public void ClearCache()
{
List <string> strPath = new List <string>();
strPath.Add(AppDomain.CurrentDomain.BaseDirectory + "SysLog\\Cache_rpt_categorydetails.log");
strPath.Add(AppDomain.CurrentDomain.BaseDirectory + "SysLog\\Cache_rpt_column.log");
foreach (var path in strPath)
{
BugLog.Write("清除 缓存" + path, path);
}
Response.Redirect("/Report/Index", true);
// Session.Clear();
}
public int AddCategory(Category category)
{
try
{
rpt_category rpt_category = new rpt_category();
rpt_category.Id = category.Id;
rpt_category.Catrgoryname = category.CategoryName;
_db.rpt_category.Add(rpt_category);
return(_db.SaveChanges());
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
return(0);
}
}
//[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());
}
/// <summary>
/// 检查sql语句是否符合正常的输入
/// </summary>
/// <param name="sql">执行的sql语句</param>
/// <returns></returns>
private bool checkSql(string sql)
{
if (sql != string.Empty && sql != null)
{
string[] excluded = "drop,delete,update,database,exec,xp_cmdshell,sysadmin,dbcreator,diskadmin,processadmin,server,admin,setupadmin,securityadmin,bulkadmin,declare,alert ,create table,mysqladmin,mysqldump,insert ,create ,use ,show ,tables ,information_schema".Split(',');
string sqlstr = sql.ToString().ToLower();
foreach (var indexstr in excluded)
{
if (sqlstr.Contains(indexstr))
{
BugLog.Write("输入排除的sql关键字！！");
return(false);
}
}
}
return(true);
}
public DataTable GetDataTableWithParam(string sqlValue, /*
* MySqlParameter[] dic*/Array dic)
{
DataTable dt = new DataTable();
//using (MySqlConnection connection = new MySqlConnection(_db.Database.Connection.ConnectionString))
//{
// if (connection.State == ConnectionState.Closed) connection.Open();
// using (MySqlCommand cmd = new MySqlCommand(sqlValue, connection))
// {
// MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
// adapter.SelectCommand.CommandType = CommandType.Text;
// if (dic != null && dic.Length != 0)
// {
// cmd.Parameters.AddRange(dic);
// }
// adapter.Fill(dt);
// }
//}
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
if (dic != null && dic.Length > 0)
{
cmd.Parameters.AddRange(dic);
}
da.Fill(dt);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(dt);
}
public ActionResult SaveBug(AddOrEditBugViewModel model)
{
model.Save(WebSecurity.CurrentUserId, _currentProjectId);
var bugLog = new BugLog
{
BugId = model.BugId,
UserId = WebSecurity.CurrentUserId,
UserName = ProjectManagementService.GetCreatorName(WebSecurity.CurrentUserId),
PrevStatusId = _prevBugStatus,
CurrStatusId = model.StatusId,
LogDate = DateTime.Now
};
if (model.BugId != 0)
{
model.SaveBugLog(bugLog);
}
return(null);
}
public int GetCount(string sqlValue, Array para, bool optimization = true)
{
int count = 0;
try
{
using (DbReport context = new DbReport())
{
var fromIndex = sqlValue.IndexOf("from", StringComparison.OrdinalIgnoreCase);
var resutlSql = "select '' " + sqlValue.Substring(fromIndex);
//修改 select '' 空内容 来优化 sql //参考todo #001
count = context.Database.SqlQuery <int>(string.Format("select count(*) from ( {0} ) a", optimization ? resutlSql : sqlValue), (object[])para).FirstOrDefault();
}
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
}
return(count);
}
/// <summary>
/// 获取报表类别的详细信息<有缓存></有缓存>
/// </summary>
/// <param name="report">报表列别ID</param>
/// <returns></returns>
public rpt_categorydetail GetCategoryDetail(string report)
{
try
{
List <rpt_categorydetail> categorydetail = CacheHelper.Cache.RetrieveObject <List <rpt_categorydetail> >("Cache_rpt_categorydetails");
if (categorydetail == null)
{
categorydetail = _db.rpt_categorydetail.Select(rpt => rpt).ToList <rpt_categorydetail>();
if (categorydetail != null)
{
var path = AppDomain.CurrentDomain.BaseDirectory + "SysLog\\Cache_rpt_categorydetails.log";
BugLog.NoAsyncWrite("载入缓存 Cache_rpt_categorydetails", path);
CacheHelper.Cache.AddObjectWithFileChange("Cache_rpt_categorydetails", categorydetail, path);
}
}
return(categorydetail.Where(rpt => rpt.Id == report).FirstOrDefault());
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
return(new rpt_categorydetail());
}
}
public List <rpt_column> GetRptColumnEntity(string report)
{
try
{
List <rpt_column> column = CacheHelper.Cache.RetrieveObject <List <rpt_column> >("Cache_rpt_column");
if (column == null)
{
column = _db.rpt_column.Select(rpt => rpt).ToList <rpt_column>();
if (column != null)
{
var path = AppDomain.CurrentDomain.BaseDirectory + "SysLog\\Cache_rpt_column.log";
BugLog.NoAsyncWrite("载入缓存 Cache_rpt_column", path);
CacheHelper.Cache.AddObjectWithFileChange("Cache_rpt_column", column, path);
}
}
return(column.Where(rpt => rpt.Columnreport == report).ToList());
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
return(new List <rpt_column>());
}
}
/// <summary>
/// 获取Sql语句的 DataTable 数据集
/// </summary>
/// <param name="sqlValue">sql语句</param>
/// <returns></returns>
public DataTable GetDataTable(string sqlValue)
{
DataSet ds = new DataSet();
//using (MySqlConnection connection = new MySqlConnection(_db.Database.Connection.ConnectionString))
//{
// if (connection.State == ConnectionState.Closed) connection.Open();
// using (MySqlCommand cmd = new MySqlCommand(sqlValue, connection))
// {
// MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
// adapter.SelectCommand.CommandType = CommandType.Text;
// adapter.Fill(ds);
// }
//}
using (DbReport context = new DbReport())
{
try
{
var cmd = context.Database.Connection.CreateCommand();
cmd.CommandText = sqlValue;
cmd.CommandType = CommandType.Text;
DbDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
context.Database.Connection.Close();
}
}
return(ds.Tables[0] ?? new DataTable());
}
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 void Awake()
{
Instance = this;
gameObject.SetActive(false);
}
public ActionResult SimpleQuery2()
{
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*/
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())
{
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();
}
if (sb.ToString().IsEmpty())
{
Session["SqlValue"] = sqlValue;
return(Content("ok"));
}
#endregion
sqlValue = sqlValue.IndexOf("where", StringComparison.OrdinalIgnoreCase) > -1
?
sqlValue + startwhere
:
sqlValue + startwhere.Substring(startwhere.IndexOf(" and", StringComparison.OrdinalIgnoreCase) + " and".Length).Insert(0, " where ");
int rowEf = commond.GetCount(sqlValue);
if (0 == rowEf)
{
return(Content("no"));
}
}
catch (Exception ex)
{
BugLog.Write(ex.ToString());
return(Content("error"));
}
Session["SqlValue"] = sqlValue;
return(Content("ok"));
}
// GET: BugLogs/Create
public ActionResult Create()
{
BugLog bug = new BugLog();
return View(bug);
}
public void SaveBugLog(BugLog bugLog)
{
new BugManagementService().SaveBugLog(bugLog);
}
// GET: BugLogs/Delete/5
public ActionResult Delete(int id)
{
BugLog bug = bugRepository.GetBugByID(id);
return View(bug);
}