#region
|
|
using System;
|
using System.Data;
|
using System.Text;
|
using CSFramework.DB;
|
using CSFrameworkV5.Common;
|
using CSFrameworkV5.Core;
|
using CSFrameworkV5.Interfaces;
|
using CSFrameworkV5.Models;
|
|
#endregion
|
|
namespace CSFrameworkV5.DataAccess
|
{
|
public class dalLog : dalBase, IBridge_Log
|
{
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="user">当前用户登录信息</param>
|
public dalLog(Loginer user)
|
: base(user)
|
{
|
_Database = DatabaseProvider.SystemDatabase;
|
}
|
|
public void ClearAll()
|
{
|
_Database.ExecuteSQL("DELETE FROM sys_LogOperation");
|
}
|
|
public void ClearLog(DateTime beginDate, DateTime endDate)
|
{
|
var sql =
|
$"DELETE FROM sys_LogOperation WHERE OperationTime BETWEEN {_Database.ParamSymboName}D1 AND {_Database.ParamSymboName}D2";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("D1", DbType.DateTime, beginDate);
|
cmd.AddParam("D2", DbType.DateTime, endDate);
|
_Database.ExecuteCommand(cmd.Command);
|
}
|
|
public void ClearLog(string user, DateTime beginDate, DateTime endDate)
|
{
|
var sql =
|
$"DELETE FROM sys_LogOperation WHERE Account={_Database.ParamSymboName}Account AND OperationTime BETWEEN {_Database.ParamSymboName}D1 AND {_Database.ParamSymboName}D2";
|
var cmd = _Database.CreateCommand(sql);
|
cmd.AddParam("Account", DbType.String, user);
|
cmd.AddParam("D1", DbType.DateTime, beginDate);
|
cmd.AddParam("D2", DbType.DateTime, endDate);
|
_Database.ExecuteCommand(cmd.Command);
|
}
|
|
public int DeleteLoginLog(string isids)
|
{
|
var cmd = _Database.CreateSqlProc("usp_DeleteLoginLog");
|
cmd.AddParam("isids", isids);
|
var i = _Database.ExecuteCommand(cmd.Command);
|
return i;
|
}
|
|
public int DeleteOperateLog(string isids)
|
{
|
var cmd = _Database.CreateSqlProc("usp_DeleteOperateLog");
|
cmd.AddParam("isids", isids);
|
var i = _Database.ExecuteCommand(cmd.Command);
|
return i;
|
}
|
|
public DataSet GetDynamicReport(string[] years)
|
{
|
//组合SQL1
|
var sb = new StringBuilder();
|
sb.AppendLine("SELECT Account,OperationType, ");
|
for (var i = 0; i <= years.Length - 1; i++)
|
{
|
var year = years[i].Trim(); //必须去空格!
|
var item =
|
$" SUM(CASE WHEN YEAR(OperationTime)={year} THEN 1 ELSE 0 END) AS Y{year} ";
|
if (i < years.Length - 1) item += ", ";
|
|
sb.AppendLine(item);
|
}
|
|
sb.AppendLine("FROM sys_LogOperation ");
|
sb.AppendLine("GROUP BY Account,OperationType; ");
|
var sql1 = sb.ToStringEx();
|
|
var p = DatabaseProvider.SystemDatabase.ParamSymboName;
|
|
//组合SQL2
|
var sql2 = "SELECT YEAR(OperationTime) AS YearItem, " +
|
" SUM(CASE WHEN OperationType = " + p +
|
"OperationType1 THEN 1 ELSE 0 END) AS OpenForm, " +
|
" SUM(CASE WHEN OperationType = " + p +
|
"OperationType2 THEN 1 ELSE 0 END) AS Exception, " +
|
" SUM(CASE WHEN OperationType = " + p +
|
"OperationType3 THEN 1 ELSE 0 END) AS CustomError " +
|
"FROM sys_LogOperation " +
|
"GROUP BY YEAR(OperationTime); ";
|
|
var cmd =
|
DatabaseProvider.SystemDatabase.CreateCommand(sql1 + sql2);
|
cmd.AddParam("OperationType1", "OpenForm");
|
cmd.AddParam("OperationType2", "Exception");
|
cmd.AddParam("OperationType3", "CustomError");
|
|
var ds = DatabaseProvider.SystemDatabase.GetDataSet(cmd.Command);
|
return ds;
|
}
|
|
public DataTable QueryLoginLog(string loginType, string loginUser,
|
string loginIP, string loginMAC,
|
DateTime LoginD1, DateTime LoginD2)
|
{
|
var cmd = _Database.CreateCommand("");
|
var p = _Database.ParamSymboName; //SQL参数符号
|
|
var sb = new StringBuilder();
|
sb.AppendLine(
|
"SELECT isid,Account,LoginResult,CurrentTime,IP,MAC,LoginType FROM sys_LogLogin WHERE 1=1 ");
|
|
if (!string.IsNullOrEmpty(loginType))
|
{
|
sb.AppendLine($" AND LoginType={p}LoginType");
|
cmd.AddParam("LoginType", loginType);
|
}
|
|
if (!string.IsNullOrEmpty(loginUser))
|
{
|
sb.AppendLine($" AND Account={p}Account");
|
cmd.AddParam("Account", loginUser);
|
}
|
|
if (!string.IsNullOrEmpty(loginIP))
|
{
|
sb.AppendLine($" AND IP={p}IP");
|
cmd.AddParam("IP", loginIP);
|
}
|
|
if (!string.IsNullOrEmpty(loginMAC))
|
{
|
sb.AppendLine($" AND MAC={p}MAC");
|
cmd.AddParam("MAC", loginMAC);
|
}
|
|
if (LoginD1 > DateTime.Parse("1901-01-01"))
|
{
|
sb.AppendLine($" AND CurrentTime>={p}CurrentTime1");
|
cmd.AddParam("CurrentTime1",
|
ConvertEx.ToCharYYYYMMDDLong(LoginD1));
|
}
|
|
if (LoginD2 > DateTime.Parse("1901-01-01"))
|
{
|
sb.AppendLine($" AND CurrentTime<={p}CurrentTime2");
|
cmd.AddParam("CurrentTime2",
|
ConvertEx.ToCharYYYYMMDDLong(LoginD2));
|
}
|
|
cmd.Command.CommandText =
|
CodeSafeHelper.GetSafeSQL(sb.ToStringEx());
|
return _Database.GetTable(cmd.Command, "sys_LogLogin");
|
}
|
|
public DataTable QueryOperateLog(string Content, DateTime Date1,
|
DateTime Date2, string OperateType,
|
string User)
|
{
|
var cmd = _Database.CreateCommand("");
|
var p = _Database.ParamSymboName; //SQL参数符号
|
|
var sb = new StringBuilder();
|
sb.AppendLine("SELECT * FROM sys_LogOperation WHERE 1=1 ");
|
|
if (!string.IsNullOrEmpty(Content))
|
{
|
sb.AppendLine(
|
$" AND (MSG LIKE {p}MSG OR FormName LIKE {p}FormName OR FormCaption LIKE {p}FormCaption OR MenuName LIKE {p}MenuName)");
|
cmd.AddParam("MSG", "%" + Content + "%");
|
cmd.AddParam("FormName", "%" + Content + "%");
|
cmd.AddParam("FormCaption", "%" + Content + "%");
|
cmd.AddParam("MenuName", "%" + Content + "%");
|
}
|
|
if (!string.IsNullOrEmpty(OperateType))
|
{
|
sb.AppendLine($" AND OperationType={p}OperateType");
|
cmd.AddParam("OperateType", OperateType);
|
}
|
|
if (!string.IsNullOrEmpty(User))
|
{
|
sb.AppendLine($" AND Account={p}Account");
|
cmd.AddParam("Account", User);
|
}
|
|
if (Date1 > DateTime.Parse("1901-01-01"))
|
{
|
sb.AppendLine($" AND OperationTime>={p}OperationTime1");
|
cmd.AddParam("OperationTime1", Date1);
|
}
|
|
if (Date2 > DateTime.Parse("1901-01-01"))
|
{
|
sb.AppendLine($" AND OperationTime<={p}OperationTime2");
|
cmd.AddParam("OperationTime2", Date2);
|
}
|
|
cmd.Command.CommandText =
|
CodeSafeHelper.GetSafeSQL(sb.ToStringEx());
|
return _Database.GetTable(cmd.Command,
|
sys_LogOperation.__TableName);
|
}
|
|
/// <summary>
|
/// 写操作日志
|
/// </summary>
|
/// <param name="content">String或sys_LogOperation对象</param>
|
public void WriteLog(object content)
|
{
|
if (content is sys_LogOperation)
|
{
|
var log = content as sys_LogOperation;
|
|
var data = new DbDataUpdate(_Database);
|
data.AddObject(log, "isid");
|
}
|
else if (content is string)
|
{
|
var log = new sys_LogOperation
|
{
|
MSG = content.ToStringEx(),
|
OperationTime = DateTime.Now,
|
OperationType = LogTypeSystem.Normal.ToStringEx()
|
};
|
|
var data = new DbDataUpdate(_Database);
|
data.AddObject(log, "isid");
|
}
|
}
|
|
/// <summary>
|
/// 高级查询 - 查询条件参数化
|
/// </summary>
|
/// <param name="whereSQL"></param>
|
/// <param name="parameters"></param>
|
/// <returns></returns>
|
public object AdvancedSearch(string whereSQL,
|
DbParameterModel[] parameters)
|
{
|
var sql = "SELECT * FROM sys_LogOperation WHERE 1=1 ";
|
|
var cmd = _Database.CreateCommand("");
|
var p = _Database.ParamSymboName; //SQL参数符号
|
|
//有查询条件
|
if (!string.IsNullOrWhiteSpace(whereSQL))
|
{
|
//替换参数符号
|
whereSQL = whereSQL.Replace(DbParameterModel.ParamSymbol, p);
|
sql = sql + " AND " + whereSQL;
|
if (parameters != null)
|
foreach (var m in parameters)
|
//替换参数符号
|
cmd.AddParam(
|
m.ParamName.Replace(DbParameterModel.ParamSymbol,
|
p), m.ParamValue);
|
}
|
|
//返回DataTable
|
cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(sql);
|
return _Database.GetTable(cmd.Command,
|
sys_LogOperation.__TableName);
|
}
|
}
|
}
|
