/// <summary>
/// 分页查询
/// </summary>
/// <param name="searchValue">查询字段</param>
/// <param name="companyId">公司主键</param>
/// <param name="departmentId">部门主键</param>
/// <param name="roleId">角色主键</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="order">排序</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(string searchValue, string companyId, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null)
{
string whereClause = BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldEnabled + " = 1 ";
if (!String.IsNullOrEmpty(companyId))
{
whereClause += " AND (" + BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldCompanyId + " = " + companyId + ")";
}
if (!String.IsNullOrEmpty(departmentId))
{
whereClause += " AND (" + BaseStaffEntity.TableName + "." + BaseStaffEntity.FieldDepartmentId + " = " + departmentId + ")";
}
if (!string.IsNullOrEmpty(searchValue))
{
searchValue = "'" + StringUtil.GetSearchString(searchValue) + "'";
whereClause += " AND (" + BaseStaffEntity.FieldRealName + " LIKE " + searchValue;
whereClause += " OR " + BaseStaffEntity.FieldUserName + " LIKE " + searchValue;
whereClause += " OR " + BaseStaffEntity.FieldQuickQuery + " LIKE " + searchValue + ")";
// whereClause += " OR " + BaseStaffEntity.FieldSimpleSpelling + " LIKE " + searchValue + ")";
}
recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereClause);
this.CurrentTableName = "BaseStaff";
return(DbLogic.GetDataTableByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, order));
}
public DataTable GetDataTableByPage(BaseUserInfo userInfo, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy)
{
DataTable result = null;
recordCount = 0;
string connectionString = string.Empty;
connectionString = ConfigurationHelper.AppSettings("OpenMasDbConnection", BaseSystemInfo.EncryptDbConnection);
if (!string.IsNullOrEmpty(connectionString))
{
if (SecretUtil.IsSqlSafe(conditions))
{
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SqlServer, connectionString))
{
recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbParameters);
result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbParameters, orderBy);
}
}
else
{
if (System.Web.HttpContext.Current != null)
{
// 记录注入日志
FileUtil.WriteMessage("userInfo:" + userInfo.Serialize() + " " + conditions, System.Web.HttpContext.Current.Server.MapPath("~/Log/") + "SqlSafe" + DateTime.Now.ToString(BaseSystemInfo.DateFormat) + ".txt");
}
}
}
return(result);
}
/// <summary>
/// 获取分页数据（防注入功能的）
/// </summary>
/// <param name="recordCount">记录条数</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="selectField">选择字段</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="conditions">查询条件</param>
/// <param name="dbParameters">查询参数</param>
/// <param name="orderBy">排序字段</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(BaseUserInfo userInfo, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, List <KeyValuePair <string, object> > dbParameters, string orderBy)
{
DataTable result = null;
// 判断是否已经登录的用户？
var userManager = new BaseUserManager(userInfo);
recordCount = 0;
// 判断是否已经登录的用户？
if (userManager.UserIsLogOn(userInfo))
{
if (SecretUtil.IsSqlSafe(conditions))
{
recordCount = DbLogic.GetCount(DbHelper, tableName, conditions, DbHelper.MakeParameters(dbParameters));
result = DbLogic.GetDataTableByPage(DbHelper, tableName, selectField, pageIndex, pageSize, conditions, DbHelper.MakeParameters(dbParameters), orderBy);
}
else
{
if (System.Web.HttpContext.Current != null)
{
// 记录注入日志
DotNet.Utilities.FileUtil.WriteMessage("userInfo:" + userInfo.Serialize() + " " + conditions, System.Web.HttpContext.Current.Server.MapPath("~/Log/") + "SqlSafe" + DateTime.Now.ToString(BaseSystemInfo.DateFormat) + ".txt");
}
}
}
return(result);
}
/// <summary>
/// 获取分页数据（防注入功能的）
/// 宋彪 2014-06-25 构造List<KeyValuePair<string, object>>比IDbDataParameter[]方便一些
/// dbHelper.MakeParameters(dbParameters)--》IDbDataParameter[]
/// </summary>
/// <param name="recordCount">记录条数</param>
/// <param name="dbHelper">dbHelper</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="selectField">选择字段</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="conditions">查询条件</param>
/// <param name="dbParameters">查询参数</param>
/// <param name="orderBy">排序字段</param>
/// <returns>数据表</returns>
public static IDataReader GetDataReaderByPage(IDbHelper dbHelper, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy)
{
IDataReader result = null;
recordCount = 0;
if (null != dbHelper)
{
recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbParameters);
result = DbLogic.GetDataReaderByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbParameters, orderBy);
}
return(result);
}
/// <summary>
/// 获取分页数据（防注入功能的）
/// 宋彪 2014-06-25 构造List<KeyValuePair<string, object>>比IDbDataParameter[]方便一些
/// 宋彪 2015-11-13 增加输出最大记录数量，增加是否输出分页数的方法
/// dbHelper.MakeParameters(dbParameters)--》IDbDataParameter[]
/// </summary>
/// <param name="recordCount">记录条数</param>
/// <param name="dbHelper">dbHelper</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="selectField">选择字段</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="conditions">查询条件</param>
/// <param name="dbParameters">查询参数</param>
/// <param name="orderBy">排序字段</param>
/// <param name="maxOutPut">最大输出数量</param>
/// <param name="showRecordCount">是否显示分页数量</param>
/// <returns>数据表</returns>
public static DataTable GetDataTableByPage(IDbHelper dbHelper, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, IDbDataParameter[] dbParameters, string orderBy, int?maxOutPut = null, bool?showRecordCount = true)
{
DataTable result = null;
recordCount = 0;
if (null != dbHelper)
{
if (showRecordCount == true)
{
recordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbParameters);
recordCount = recordCount > maxOutPut ? (int)maxOutPut : recordCount;
}
result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbParameters, orderBy);
}
return(result);
}
/// <summary>
/// 获取分页数据（防注入功能的）
/// </summary>
/// <param name="recordCount">记录条数</param>
/// <param name="tableName">数据来源表名</param>
/// <param name="selectField">选择字段</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="conditions">查询条件</param>
/// <param name="dbParameters">查询参数</param>
/// <param name="orderBy">排序字段</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(BaseUserInfo userInfo, out int recordCount, string tableName, string selectField, int pageIndex, int pageSize, string conditions, List <KeyValuePair <string, object> > dbParameters, string orderBy)
{
DataTable result = null;
int myRecordCount = 0;
var dt = new DataTable(BaseModuleEntity.TableName);
var parameter = ServiceInfo.Create(userInfo, MethodBase.GetCurrentMethod());
ServiceUtil.ProcessUserCenterReadDb(userInfo, parameter, (dbHelper) =>
{
// 判断是否已经登录的用户？
var userManager = new BaseUserManager(userInfo);
// 判断是否已经登录的用户？
if (userManager.UserIsLogOn(userInfo))
{
if (SecretUtil.IsSqlSafe(conditions))
{
myRecordCount = DbLogic.GetCount(dbHelper, tableName, conditions, dbHelper.MakeParameters(dbParameters));
result = DbLogic.GetDataTableByPage(dbHelper, tableName, selectField, pageIndex, pageSize, conditions, dbHelper.MakeParameters(dbParameters), orderBy);
}
else
{
if (System.Web.HttpContext.Current != null)
{
// 记录注入日志
FileUtil.WriteMessage("userInfo:" + userInfo.Serialize() + " " + conditions, System.Web.HttpContext.Current.Server.MapPath("~/Log/") + "SqlSafe" + DateTime.Now.ToString(BaseSystemInfo.DateFormat) + ".txt");
}
}
}
});
recordCount = myRecordCount;
return(result);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="searchValue">查询字段</param>
/// <param name="companyId">公司主键</param>
/// <param name="departmentId">部门主键</param>
/// <param name="roleId">角色主键</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="order">排序</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(string searchValue, string companyId, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null)
{
string whereClause = BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = 1 "
+ " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldIsVisible + " = 1 "
+ " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " > 0 ";
if (!String.IsNullOrEmpty(companyId))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = '" + companyId + "')";
}
if (!String.IsNullOrEmpty(departmentId))
{
/*
* 用非递归调用的建议方法
* sqlQuery += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId
+ " IN ( SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldId + " = " + departmentId + " OR " + BaseOrganizeEntity.FieldParentId + " = " + departmentId + ")";
*/
/*
* BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.UserInfo);
* string[] ids = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
* if (ids != null && ids.Length > 0)
* {
* whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSubCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(ids) + "))";
+ }
*/
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " = " + departmentId + ")";
}
if (!string.IsNullOrEmpty(roleId))
{
string tableNameUserRole = UserInfo.SystemCode + "UserRole";
whereClause += " AND ( " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN "
+ " (SELECT " + BaseUserRoleEntity.FieldUserId
+ " FROM " + tableNameUserRole
+ " WHERE " + BaseUserRoleEntity.FieldRoleId + " = " + roleId + ""
+ " AND " + BaseUserRoleEntity.FieldEnabled + " = 1"
+ " AND " + BaseUserRoleEntity.FieldDeletionStateCode + " = 0)) ";
}
if (!string.IsNullOrEmpty(searchValue))
{
searchValue = "'" + StringUtil.GetSearchString(searchValue) + "'";
whereClause += " AND (" + BaseUserEntity.FieldRealName + " LIKE " + searchValue;
whereClause += " OR " + BaseUserEntity.FieldUserName + " LIKE " + searchValue;
whereClause += " OR " + BaseUserEntity.FieldQuickQuery + " LIKE " + searchValue;
whereClause += " OR " + BaseUserEntity.FieldSimpleSpelling + " LIKE " + searchValue + ")";
}
recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereClause);
this.CurrentTableName = "BaseUser";
if (this.ShowUserLogOnInfo)
{
this.CurrentTableName = BaseUserEntity.TableName + " LEFT OUTER JOIN " + BaseUserLogOnEntity.TableName + " ON " + BaseUserEntity.TableName + ".Id = " + BaseUserLogOnEntity.TableName + ".Id ";
}
switch (DbHelper.CurrentDbType)
{
case CurrentDbType.SqlServer:
case CurrentDbType.Access:
this.SelectFields = BaseUserEntity.TableName + ".* ";
if (this.ShowUserLogOnInfo)
{
this.SelectFields += "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldFirstVisit
+ "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldPreviousVisit
+ "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldLastVisit
+ "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldIPAddress
+ "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldMACAddress
+ "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldLogOnCount
+ "," + BaseUserLogOnEntity.TableName + "." + BaseUserLogOnEntity.FieldUserOnLine;
}
break;
case CurrentDbType.Oracle:
case CurrentDbType.MySql:
case CurrentDbType.DB2:
break;
}
return(DbLogic.GetDataTableByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, order));
}
/// <summary>
/// 获取分页DataTable
/// </summary>
/// <param name="recordCount">记录总数</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="whereConditional">条件</param>
/// <param name="order">排序字段</param>
/// <returns>数据表</returns>
public virtual DataTable GetDataTableByPage(out int recordCount, int pageIndex, int pageSize, string whereConditional, string order)
{
recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereConditional);
return(DbLogic.GetDataTableByPage(DbHelper, this.CurrentTableName, pageIndex, pageSize, whereConditional, order));
}
/// <summary>
/// 获取分页DataTable
/// </summary>
/// <param name="recordCount">记录总数</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="pageSize">每页显示多少条</param>
/// <param name="whereClause">条件</param>
/// <param name="order">排序字段</param>
/// <returns>数据表</returns>
public virtual IDataReader GetDataReaderByPage(out int recordCount, int pageIndex, int pageSize, string whereClause, IDbDataParameter[] dbParameters, string order)
{
recordCount = DbLogic.GetCount(DbHelper, this.CurrentTableName, whereClause, dbParameters, this.CurrentIndex);
return(DbLogic.GetDataReaderByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, dbParameters, order, this.CurrentIndex));
}
/// <summary>
/// 获取权限审核
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="startDate">开始日期</param>
/// <param name="endDate">结束日期</param>
/// <param name="companyId">公司主键</param>
/// <param name="userId">用户主键</param>
/// <param name="result">权限主键</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示条数</param>
/// <returns>数据表</returns>
public DataTable PermissionMonitor(BaseUserInfo userInfo, DateTime startDate, DateTime endDate, string companyId, string userId, string permissionId, out int recordCount, int pageIndex = 0, int pageSize = 20)
{
DataTable result = null;
recordCount = 0;
int myRecordCount = 0;
var parameter = ServiceInfo.Create(userInfo, MethodBase.GetCurrentMethod());
ServiceUtil.ProcessUserCenterReadDb(userInfo, parameter, (dbHelper) =>
{
string whereClause = string.Empty;
List <KeyValuePair <string, object> > dbParameters = new List <KeyValuePair <string, object> >();
if (startDate != null)
{
if (!string.IsNullOrEmpty(whereClause))
{
whereClause += " AND ";
}
whereClause += BasePermissionEntity.FieldCreateOn + " >= " + DotNet.Utilities.DbHelper.GetParameter(BaseSystemInfo.ServerDbType, "startDate");
dbParameters.Add(new KeyValuePair <string, object>("startDate", startDate));
}
if (endDate != null)
{
if (!string.IsNullOrEmpty(whereClause))
{
whereClause += " AND ";
}
whereClause += BasePermissionEntity.FieldCreateOn + " <= " + DotNet.Utilities.DbHelper.GetParameter(BaseSystemInfo.ServerDbType, "endDate");
dbParameters.Add(new KeyValuePair <string, object>("endDate", endDate));
}
string tableName = BasePermissionEntity.TableName;
if (userInfo != null)
{
tableName = userInfo.SystemCode + "Permission";
}
myRecordCount = DbLogic.GetCount(dbHelper, tableName, whereClause, dbHelper.MakeParameters(dbParameters));
result = DbLogic.GetDataTableByPage(dbHelper, tableName, "*", pageIndex, pageSize, whereClause, dbHelper.MakeParameters(dbParameters), BasePermissionEntity.FieldCreateOn + " DESC");
if (!result.Columns.Contains("ResourceCategoryName"))
{
result.Columns.Add("ResourceCategoryName".ToUpper());
}
if (!result.Columns.Contains("PermissionName"))
{
result.Columns.Add("PermissionName".ToUpper());
}
if (!result.Columns.Contains("PermissionCode"))
{
result.Columns.Add("PermissionCode".ToUpper());
}
if (!result.Columns.Contains("ResourceName"))
{
result.Columns.Add("ResourceName".ToUpper());
}
if (!result.Columns.Contains("CompanyName"))
{
result.Columns.Add("CompanyName".ToUpper());
}
foreach (DataRow dr in result.Rows)
{
string id = dr["PermissionId"].ToString();
BaseModuleEntity moduleEntity = BaseModuleManager.GetObjectByCache(userInfo, id);
if (moduleEntity != null)
{
dr["PermissionName"] = moduleEntity.FullName;
dr["PermissionCode"] = moduleEntity.Code;
}
if (dr["ResourceCategory"].ToString().Equals(BaseUserEntity.TableName))
{
id = dr["ResourceId"].ToString();
BaseUserEntity userEntity = BaseUserManager.GetObjectByCache(id);
if (userEntity != null)
{
dr["ResourceName"] = userEntity.RealName;
dr["CompanyName"] = userEntity.CompanyName;
dr["ResourceCategoryName"] = "用户";
}
}
else if (dr["ResourceCategory"].ToString().Equals(BaseOrganizeEntity.TableName))
{
id = dr["ResourceId"].ToString();
BaseOrganizeEntity organizeEntity = BaseOrganizeManager.GetObjectByCache(id);
if (organizeEntity != null)
{
dr["ResourceName"] = organizeEntity.FullName;
dr["ResourceCategoryName"] = "网点";
}
}
else if (dr["ResourceCategory"].ToString().Equals(BaseRoleEntity.TableName))
{
id = dr["ResourceId"].ToString();
BaseRoleEntity roleEntity = BaseRoleManager.GetObjectByCache(userInfo, id);
if (roleEntity != null)
{
dr["ResourceName"] = roleEntity.RealName;
dr["ResourceCategoryName"] = "角色";
}
}
}
});
recordCount = myRecordCount;
return(result);
}
