public PageData GetPageData(SqlParams sqlParams)
{
var result = new PageData();
var reportsql = GetReportSql();
var prepareSql = GetPrepareSql();
var totalSql = GetTotalSql();
var orderSql = GetOrderSql(sqlParams.Order);
var footerSql = GetFootSql();
var parameters = ProcessDynamicParams(sqlParams, ref reportsql, ref totalSql, ref prepareSql, ref footerSql);
totalSql = prepareSql + totalSql;
//select * from table limit (start-1)*pageSize,pageSize
int start = (sqlParams.PageIndex - 1) * sqlParams.PageSize;
int end = sqlParams.PageIndex * sqlParams.PageSize + 1;
reportsql = reportsql + " {0} LIMIT {1} , {2}";
var sql = prepareSql + string.Format(reportsql, orderSql, start, sqlParams.PageSize);
var pars = MySqlHelper.DicToSqlParameters(parameters);
result.Data = Db.ExecuteDataTable(sql, pars);
result.Total = Convert.ToInt32(Db.ExecuteScalar(totalSql, pars));
if (footerSql != "")
{
result.Footer = Db.ExecuteDataTable(footerSql, pars);
}
return(result);
}
private Dictionary <string, object> ProcessDynamicParams(SqlParams sqlParams, ref string reportsql, ref string prepareSql)
{
var totalSql = "";
var footerSql = "";
return(ProcessDynamicParams(sqlParams, ref reportsql, ref prepareSql, ref totalSql));
}
public PageData GetPageData(SqlParams sqlParams)
{
var result = new PageData();
var reportsql = GetReportSql();
var prepareSql = GetPrepareSql();
var totalSql = GetTotalSql();
var orderSql = GetOrderSql(sqlParams.Order);
var parameters = ProcessDynamicParams(sqlParams, ref reportsql, ref totalSql, ref prepareSql);
totalSql = prepareSql + totalSql;
int start = (sqlParams.PageIndex - 1) * sqlParams.PageSize + 1;
int end = sqlParams.PageIndex * sqlParams.PageSize + 1;
reportsql = @" select * from (select *,ROW_NUMBER() over({0})
as rows from (" + reportsql + @")as t1) as t2
where t2.rows>={1} and t2.rows<{2} ";
var sql = prepareSql + string.Format(reportsql, orderSql, start, end);
result.Data = Db.ExecuteDataTable(sql, DB.DictToSqlParameters(parameters));
result.Total = (int)Db.ExecuteScalar(totalSql, DB.DictToSqlParameters(parameters));
//if (footerSql != "")
// result.Footer = Db.ExecuteDataTable(footerSql, DB.DictToSqlParameters(parameters));
return(result);
}
/// <summary>
/// 创建消息
/// </summary>
private LogMessage CreateMessage()
{
return(new LogMessage()
{
Level = Level.Description(),
TraceId = TraceId,
Time = DateTime.Now.ToMillisecondString(),
TotalSeconds = GetTotalSeconds(),
Url = GetUrl(),
BusinessId = BusinessId,
Application = GetApplication(),
Tenant = GetTenant(),
Category = Category,
Class = Class,
Method = Method,
Params = Params.ToString(),
Ip = Net.Ip,
Host = Net.Host,
ThreadId = Thread.ThreadId,
UserId = GetIdentity().UserId,
Operator = GetIdentity().FullName,
Role = GetIdentity().Role,
Caption = Caption.ToString(),
Content = Content.ToString(),
Sql = Sql.ToString(),
SqlParams = SqlParams.ToString(),
ErrorCode = ErrorCode,
Error = ErrorMessage,
StackTrace = StackTrace
});
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (!(comboBoxOperateProject.SelectedItem is Project project) || project.ID <= 0)
{
MessageBox.Show("项目不能为空", "提示");
return;
}
if (string.IsNullOrWhiteSpace(richTextBoxOperateContent.Text))
{
MessageBox.Show("内容不能为空", "提示");
return;
}
SqlParams paramDict = new SqlParams();
paramDict.Add("UserID", CommonData.CurrentUser.ID);
paramDict.Add("ProjectID", project.ID);
paramDict.Add("Content", richTextBoxOperateContent.Text.Trim());
paramDict.Add("Source", (int)EnumReportSource.Manual);
if (dateTimePickerOperateFinishTime.Checked && dateTimePickerOperateFinishTime.Value > DateTime.MinValue)
{
paramDict.Add("FinishTime", dateTimePickerOperateFinishTime.Value);
}
if (comboBoxOperateBranch.SelectedItem is Branch branch && branch.ID != CommonData.ItemAllValue)
{
paramDict.Add("BranchID", comboBoxOperateBranch.SelectedValue);
}
if (!string.IsNullOrWhiteSpace(textBoxOperateRelatedID.Text))
{
paramDict.Add("RelatedID", textBoxOperateRelatedID.Text.Trim());
}
CommonData.AccessHelper.Insert("Report", paramDict);
richTextBoxOperateContent.Text = string.Empty;
ShowMessageAskRefresh();
}
/// <summary>
/// 转换
/// </summary>
/// <returns></returns>
public List <Item> To()
{
return(new List <Item>()
{
{ new Item(LogResource.LogName, LogName, 1) },
{ new Item(LogResource.TraceId, TraceId, 2) },
{ new Item(LogResource.OperationTime, OperationTime, 3) },
{ new Item(LogResource.Duration, Duration, 4) },
{ new Item(LogResource.ThreadId, ThreadId, 5) },
{ new Item("Url", Url, 6) },
{ new Item(LogResource.UserId, UserId, 7) },
{ new Item(LogResource.Operator, Operator, 8) },
{ new Item(LogResource.Role, Role, 9) },
{ new Item(LogResource.BusinessId, BussinessId, 10) },
{ new Item(LogResource.Tenant, Tenant, 11) },
{ new Item(LogResource.Application, Application, 12) },
{ new Item(LogResource.Module, Module, 13) },
{ new Item(LogResource.Class, Class, 14) },
{ new Item(LogResource.Method, Method, 15) },
{ new Item(LogResource.Params, Params.ToString(), 16) },
{ new Item(LogResource.Caption, Caption, 17) },
{ new Item(LogResource.Content, Content.ToString(), 18) },
{ new Item(LogResource.Sql, Sql.ToString(), 19) },
{ new Item(LogResource.SqlParams, SqlParams.ToString(), 20) },
{ new Item(LogResource.ErrorCode, Exception?.Code, 21) },
});
}
public ActionResult GetTeacherList(Models.Admin.MSGetUserInfoListIn InModel)
{
SqlParams sqlParams = new SqlParams();
sqlParams.PageIndex = Converter.TryToInt32(InModel.PageIndex, 1);
sqlParams.PageSize = Converter.TryToInt32(InModel.PageSize, sqlParams.PageSize);
sqlParams.addUsefulParam("roleId", (int)RoleType.教职工);
sqlParams.addUsefulParam("PositionType", InModel.PositionType);
sqlParams.addUsefulParam("majorId", InModel.MajorId);
sqlParams.addUsefulParam("keyWords", InModel.KeyWords);
int allCount = 0;
List <Model.Admin.MSUserInfo> userInfoList = BLL.Admin.MSUserInfoBll.GetAllUserInfoList(sqlParams, out allCount);
return(Json(new
{
Rows = userInfoList.Select(u => new
{
u.UserID,
u.UserName,
u.RealName,
Gender = u.Gender == 0 ? "女" : "男",
MajorName = MSDepartmentInfoBll.GetDepartmentNameById(u.MajorID).IfEmptyToString("--"),
u.PositionName,
}),
AllCount = allCount
}, JsonRequestBehavior.AllowGet));
}
public ActionResult GetMajorList(string pageIndex, string pageSize, string keyWords)
{
SqlParams sqlParams = new SqlParams();
sqlParams.PageIndex = Converter.TryToInt32(pageIndex, 1);
sqlParams.PageSize = Converter.TryToInt32(pageSize, sqlParams.PageSize);
sqlParams.addUsefulParam("keyWords", keyWords);
sqlParams.addUsefulParam("DepartmentLevel", (int)Model.DepartmentLevel.专业);
int allCount = 0;
List <Model.Admin.MSDepartmentInfo> departmentList = BLL.Admin.MSDepartmentInfoBll.GetAllMSDepartmentInfoPageList(sqlParams, out allCount);
return(Json(new
{
Rows = departmentList.Select(d =>
{
Model.Admin.MSUserInfo directiorUser = BLL.Admin.MSUserPositionRelationBll.GetUserByDepartmentIdAndPositionType(d.DepartmentID, (int)Model.PositionType.系主任);
return new
{
d.DepartmentID,
d.DepartmentName,
ParentName = BLL.Admin.MSDepartmentInfoBll.GetDepartmentNameById(d.ParentID).IfEmptyToString("--"),
DirectorName = directiorUser == null ? "--" : directiorUser.RealName, // 系主任
DirectorId = directiorUser == null ? 0 : directiorUser.UserID, // 系主任
};
}),
AllCount = allCount
}, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 创建消息
/// </summary>
private LogMessage CreateMessage()
{
//TODO:消息需要再次详细初始化
return(new LogMessage
{
Level = "",//Level.Description(),
TraceId = TraceId,
Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"),
//TotalSeconds = GetTotalSeconds(),
Url = GetUrl(),
Application = GetApplication(),
Class = Class,
Method = Method,
Params = Params.ToString(),
Ip = Net.Ip,
Host = Net.Host,
ThreadId = Thread.CurrentThread.ManagedThreadId.ToString(),
UserId = "",
Operator = "",
Role = "",
Caption = Caption.ToString(),
Content = Content.ToString(),
Sql = Sql.ToString(),
SqlParams = SqlParams.ToString(),
ErrorCode = ErrorCode,
Error = _errorMessage,
StackTrace = _stackTrace
});
}
public PageData Process(string reportName, SqlParams pars, bool isExport, bool isShowFooter = false)
{
PageData result;
DataTable data;
var db = GetReportConfigDb(reportName);
//Sql执行前加工
switch (reportName)
{
//case "ConsumerCardTimes_GetListForLdy":
// Process_ConsumerCardTimes_GetListForLdy(data);
// break;
}
if (isExport)
{
data = db.GetExportData(reportName, pars);
result = new PageData(data, 0);
}
else
{
result = db.GetPageData(reportName, pars);
data = result.Data;
}
//Sql执行后加工
switch (reportName)
{
//case "ConsumerCardTimes_GetListForLdy":
// Process_ConsumerCardTimes_GetListForLdy(data);
// break;
}
return(result);
}
public IEnumerable <T> SqlQuery <T>(SqlParams sqlParams)
{
var reportsql = GetReportSql();
var prepareSql = "";
var parameters = ProcessDynamicParams(sqlParams, ref reportsql, ref prepareSql);
return(Db.SqlQuery <T>(reportsql, DB.DictToSqlParameters(parameters)));
}
public PageData GetPageData(string reportName, SqlParams pars)
{
var reportHelper = new ReportHelper(reportName);
var data = reportHelper.GetPageData(pars);
return(data);
}
/// <summary>
/// 获取Sql参数
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static SqlParameter[] GetSqlParams(CModel model)
{
foreach (var field in model.Fields)
{
SqlParams.Add(new SqlParameter($"@{field}", model[field]));
}
return(SqlParams.ToArray());
}
public DataTable GetSummaryData(SqlParams sqlParams)
{
var reportsql = GetReportSql();
var prepareSql = GetPrepareSql();
var parameters = ProcessDynamicParams(sqlParams, ref reportsql, ref prepareSql);
var sql = prepareSql + reportsql;
return(Db.ExecuteDataTable(sql, MySqlHelper.DicToSqlParameters(parameters)));
}
public static List <Model.Admin.MSUserInfo> GetAllBaseUserInfoList(SqlParams sqlParams)
{
List <Model.Admin.MSUserInfo> msUserInfoList = DAL.Admin.MSUserInfoDal.GetBaseInfoListByCondition(sqlParams);
if (msUserInfoList == null)
{
msUserInfoList = new List <Model.Admin.MSUserInfo>();
}
return(msUserInfoList);
}
private void buttonDelete_Click(object sender, EventArgs e)
{
if (isAdding)
{
BindReminder(-1);
}
else
{
if (richTextBoxContent.Tag == null || !(richTextBoxContent.Tag is Reminder reminder) || reminder == null || reminder.ID <= 0)
{
MessageBox.Show("数据错误。", "提示");
return;
}
//decimal id;
//if (!decimal.TryParse(richTextBoxContent.Tag.ToString(), out id))
//{
// MessageBox.Show("数据错误，ID错误。", "提示");
// return;
//}
SqlParams setParamDict = new SqlParams();
setParamDict.Add("Status", 1);
SqlParams whereParamDict = new SqlParams();
//whereParamDict.Add("ID", id);
whereParamDict.Add("ID", reminder.ID);
CommonData.AccessHelper.Update("Reminder", setParamDict, whereParamDict);
reminderDict.Remove(currentIndex);
Dictionary <int, Reminder> newDict = new Dictionary <int, Reminder>();
foreach (KeyValuePair <int, Reminder> pair in reminderDict)
{
if (pair.Key >= currentIndex)
{
newDict.Add(pair.Key - 1, pair.Value);
}
else
{
newDict.Add(pair.Key, pair.Value);
}
}
reminderDict.Clear();
reminderDict = newDict;
if (currentIndex == 0)
{
currentIndex = reminderDict.Count - 1;
}
else
{
currentIndex--;
}
BindReminder(currentIndex);
if (reminder.ToDo != null && reminder.ToDo.ID > 0)
{
MessageBox.Show("如有需要，请手动在待办事项程序中修改状态。", "提示");
}
}
}
public List <long> GetFullOperationIds(SqlParams sqlParams)
{
var prepareSql = GetPrepareSql();
var reportsql = GetReportSql();
var parameters = ProcessDynamicParams(sqlParams, ref reportsql, ref prepareSql);
var sql = prepareSql + string.Format(@" SELECT Id FROM ({0}) AS FullOperationTemp", reportsql);
return(Db.SqlQuery <long>(sql, DB.DictToSqlParameters(parameters)).ToList());
}
public static List <Model.Admin.MSUserInfo> GetAllUserInfoList(SqlParams sqlParams, out int allCount)
{
allCount = 0;
List <Model.Admin.MSUserInfo> msUserInfoList = DAL.Admin.MSUserInfoDal.GetPageListByCondition(sqlParams, out allCount);
if (msUserInfoList == null)
{
msUserInfoList = new List <Model.Admin.MSUserInfo>();
}
return(msUserInfoList);
}
public List <StatusSummary> Execute(int companyId)
{
this.SqlParams.Clear();
SqlParams.Add("@intCid", companyId);
DataTable dt = base.Execute();
IEnumerable <StatusSummary> summary = base.MapData(dt, typeof(StatusSummary)).Cast <StatusSummary>();
List <StatusSummary> summaryList = summary.ToList();
return(summaryList);
}
public void SetSqlParams(string value)
{
if (string.IsNullOrWhiteSpace(value))
{
return;
}
if (SqlParams == null)
{
SqlParams = new StringBuilder();
}
SqlParams.Append(value);
}
/// <summary>
/// 清理
/// </summary>
private void Clear()
{
Method = string.Empty;
Params.Clear();
Caption.Clear();
Content.Clear();
Sql.Clear();
SqlParams.Clear();
Exception = null;
_errorMessage = string.Empty;
_stackTrace = string.Empty;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !IsCallback)
{
if (OnFirstLoad())
{
return;
}
}
if (GridControl.IsCallback || NavBarCallback.IsCallback)
{
NameValueCollection parameters = SqlParams.Get(CallbackParams.Get(Request), Session);
if (parameters != null)
{
Session["Author"] = parameters["Author"];
Session["Book"] = parameters["Book"];
Session["Sort"] = parameters["Sort"];
Session["Mode"] = parameters["Mode"];
Session["Id"] = GetGuid(parameters["Id"]);
if (NavBarCallback.IsCallback)
{
BuildNavBar();
}
}
}
Author = (String)Session["Author"];
PrepareMenuItems();
BindDataGrid(
(String)Session["Author"],
(String)Session["Book"],
(String)Session["Sort"],
(String)Session["Mode"]);
if (Session["Id"] is Guid)
{
int FocusedRowIndex = GridControl.FindVisibleIndexByKeyValue(Session["Id"]);
if (FocusedRowIndex < 0)
{
FocusedRowIndex = 0;
}
GridControl.FocusedRowIndex = FocusedRowIndex;
Session["Id"] = null;
}
}
public override Params ExecuteStoredProcedure(string strProcedureName, params object[] cmdParams)
{
Params params2;
Params @params = new SqlParams();
SqlConnection objConnection = (SqlConnection)base.objConnection;
try
{
SqlCommand command = new SqlCommand(strProcedureName, objConnection)
{
CommandType = CommandType.StoredProcedure
};
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
SqlCommandBuilder.DeriveParameters(command);
for (int i = 1; i < command.Parameters.Count; i++)
{
if (command.Parameters[i].Direction == ParameterDirection.Input)
{
if (cmdParams.Length <= (i - 1))
{
throw new Exception("Parameter Count does not match.");
}
command.Parameters[i].Value = cmdParams[i - 1];
}
else
{
command.Parameters[i].Value = DBNull.Value;
}
}
command.ExecuteNonQuery();
foreach (SqlParameter parameter in command.Parameters)
{
if ((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Output))
{
@params.Add(parameter);
}
}
params2 = @params;
}
catch (Exception exception)
{
throw exception;
}
finally
{
this.CloseConnection();
}
return(params2);
}
public DataTable GetExportData(SqlParams sqlParams)
{
var reportsql = GetReportSql();
var prepareSql = GetPrepareSql();
var orderSql = GetOrderSql(sqlParams.Order);
var parameters = ProcessDynamicParams(sqlParams, ref reportsql, ref prepareSql);
var sql = prepareSql + reportsql + orderSql;
return(Db.ExecuteDataTable(sql, DB.DictToSqlParameters(parameters)));
}
/// <summary>
/// 清理
/// </summary>
private void Clear()
{
BusinessId = string.Empty;
Method = string.Empty;
Params.Clear();
Caption.Clear();
Content.Clear();
Sql.Clear();
SqlParams.Clear();
Exception = null;
ErrorMessage = string.Empty;
StackTrace = string.Empty;
}
private void buttonSave_Click(object sender, EventArgs e)
{
if (isAdding)
{
SqlParams paramDict = new SqlParams();
paramDict.Add("Content", richTextBoxContent.Text);
paramDict.Add("Status", 0);
decimal id = CommonData.AccessHelper.InsertAndReturnNewIdentity("Reminder", paramDict);
isAdding = false;
originalContent = richTextBoxContent.Text;
int index = reminderDict.Count;
Reminder reminder = new Reminder()
{
ID = id, Content = richTextBoxContent.Text, Status = 0
};
reminderDict.Add(index, reminder);
richTextBoxContent.Tag = reminder;
currentIndex = index;
}
else
{
if (richTextBoxContent.Text == originalContent)
{
return;
}
if (richTextBoxContent.Tag == null || !(richTextBoxContent.Tag is Reminder reminder) || reminder == null || reminder.ID <= 0)
{
MessageBox.Show("数据错误。", "提示");
return;
}
//decimal id;
//if (!decimal.TryParse(richTextBoxContent.Tag.ToString(), out id))
//{
// MessageBox.Show("数据错误，ID错误。", "提示");
// return;
//}
SqlParams setParamDict = new SqlParams();
setParamDict.Add("Content", richTextBoxContent.Text);
SqlParams whereParamDict = new SqlParams();
//whereParamDict.Add("ID", id);
whereParamDict.Add("ID", reminder.ID);
CommonData.AccessHelper.Update("Reminder", setParamDict, whereParamDict);
originalContent = richTextBoxContent.Text;
//KeyValuePair<int, Reminder> pair = reminderDict.FirstOrDefault(r => r.Value.ID == id);
KeyValuePair <int, Reminder> pair = reminderDict.FirstOrDefault(r => r.Value.ID == reminder.ID);
if (pair.Value != null)
{
pair.Value.Content = richTextBoxContent.Text;
}
}
}
public string GetPageData(string reportName)
{
try
{
var pars = new SqlParams().Fill();
var result = report.GetPageData(reportName, pars);
return(result.ToJson());
}
catch (Exception ex)
{
//Response.Write("<script type='text/javascript'>alert('XXX');</script>");
throw new AlertException(ex.ToString());
}
}
private void buttonEdit_Click(object sender, EventArgs e)
{
if (dataGridViewShow.SelectedRows.Count != 1)
{
MessageBox.Show("选中一行后再修改", "提示");
return;
}
if (!(dataGridViewShow.SelectedRows[0].Tag is Report report) || report.ID <= 0)
{
MessageBox.Show("报告数据错误", "提示");
return;
}
if (!(comboBoxOperateProject.SelectedItem is Project project) || project.ID <= 0)
{
MessageBox.Show("项目不能为空", "提示");
return;
}
if (string.IsNullOrWhiteSpace(richTextBoxOperateContent.Text))
{
MessageBox.Show("内容不能为空", "提示");
return;
}
SqlParams setParamDict = new SqlParams();
setParamDict.Add("ProjectID", project.ID);
setParamDict.Add("RelatedID", string.IsNullOrWhiteSpace(textBoxOperateRelatedID.Text) ? null : textBoxOperateRelatedID.Text.Trim());
setParamDict.Add("Content", richTextBoxOperateContent.Text.Trim());
SqlParams whereParamDict = new SqlParams();
if (dateTimePickerOperateFinishTime.Checked && dateTimePickerOperateFinishTime.Value > DateTime.MinValue)
{
setParamDict.Add("FinishTime", dateTimePickerOperateFinishTime.Value);
}
if (comboBoxOperateBranch.SelectedItem is Branch branch)
{
if (branch.ID == CommonData.ItemAllValue)
{
setParamDict.Add("BranchID", null);
}
else
{
setParamDict.Add("BranchID", comboBoxOperateBranch.SelectedValue);
}
}
whereParamDict.Add("ID", report.ID);
CommonData.AccessHelper.Update("Report", setParamDict, whereParamDict);
//MessageBox.Show("修改完成", "提示");
ShowMessageAskRefresh();
}
/// <summary>
/// Merge parameters.
/// </summary>
/// <param name="firstParam">The first parameter to merge.</param>
/// <param name="otherParams">The other prameters to merge.</param>
/// <returns>Merged parameters.</returns>
public static SqlParams Merge(object firstParam, params object[] otherParams)
{
ArgumentNullException.ThrowIfNull(firstParam);
var parameters = new SqlParams();
parameters.AddDynamicParams(firstParam);
foreach (var param in otherParams)
{
parameters.AddDynamicParams(param);
}
return(parameters);
}
/// <summary>
/// 返回删除语句
/// </summary>
/// <param name="model">实体</param>
/// <returns></returns>
public static string GetDelete(CModel model)
{
SqlParamsClear();
StringBuilder sql = new StringBuilder();
sql.Append($" DELETE FROM {model.TableName} ");
sql.Append($" WHERE {model.KeyName}=@{model.KeyName}");
SqlParams.Add(new SqlParameter($"@{model.KeyName}", model.KeyValue));
InitSql = sql.ToString();
return(sql.ToString());
}
