/// <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));
}
/// <summary>
/// 分页读取数据
/// </summary>
/// <param name="recordCount">页面个数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="tableName">从什么表</param>
/// <param name="whereConditional">条件</param>
/// <param name="selectField">选择哪些字段</param>
/// <returns>数据表</returns>
public virtual DataTable GetDataTableByPage(out int recordCount, int pageIndex = 1, int pageSize = 20, string sortExpression = null, string sortDire = null, string tableName = null, string whereConditional = null, string selectField = null)
{
if (tableName.ToUpper().IndexOf("SELECT") >= 0)
{
// 统计总条数
string commandText = string.Empty;
if (string.IsNullOrEmpty(tableName))
{
tableName = this.CurrentTableName;
}
commandText = tableName;
if (tableName.ToUpper().IndexOf("SELECT") >= 0)
{
commandText = "(" + tableName + ") AS T ";
}
commandText = string.Format("SELECT COUNT(1) AS recordCount FROM {0}", commandText);
object returnObject = DbHelper.ExecuteScalar(commandText);
if (returnObject != null)
{
recordCount = int.Parse(returnObject.ToString());
}
else
{
recordCount = 0;
}
return(DbLogic.GetDataTableByPage(DbHelper, recordCount, pageIndex, pageSize, tableName, sortExpression, sortDire));
}
// 这个是调用存储过程的方法
return(DbLogic.GetDataTableByPage(DbHelper, out recordCount, pageIndex, pageSize, sortExpression, sortDire, tableName, whereConditional, selectField));
}
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[]方便一些
/// 宋彪 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="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="sortExpression">排序字段</param>
/// <param name="sortDire">排序顺序</param>
/// <param name="tableName">表名</param>
/// <param name="conditional">查询条件</param>
/// <param name="dbParameters">数据参数</param>
/// <param name="selectField">选择哪些字段</param>
/// <returns>数据表</returns>
public virtual DataTable GetDataTableByPage(out int recordCount, int pageIndex = 0, int pageSize = 20, string sortExpression = null, string sortDire = null, string tableName = null, string conditional = null, IDbDataParameter[] dbParameters = null, string selectField = null)
{
if (string.IsNullOrEmpty(tableName))
{
tableName = this.CurrentTableName;
}
if (tableName.ToUpper().IndexOf("SELECT") >= 0 || DbHelper.CurrentDbType == DotNet.Utilities.CurrentDbType.MySql)
{
// 统计总条数
string commandText = string.Empty;
if (string.IsNullOrEmpty(tableName))
{
tableName = this.CurrentTableName;
}
string whereClause = string.Empty;
if (!string.IsNullOrEmpty(conditional))
{
whereClause = string.Format(" WHERE {0} ", conditional);
}
commandText = tableName;
if (tableName.ToUpper().IndexOf("SELECT") >= 0)
{
commandText = "(" + tableName + ") T ";
// commandText = "(" + tableName + ") AS T ";
}
commandText = string.Format("SELECT COUNT(1) AS recordCount FROM {0} {1}", commandText, whereClause);
object returnObject = DbHelper.ExecuteScalar(commandText, dbParameters);
if (returnObject != null)
{
recordCount = int.Parse(returnObject.ToString());
}
else
{
recordCount = 0;
}
return(DbLogic.GetDataTableByPage(DbHelper, recordCount, pageIndex, pageSize, tableName, dbParameters, sortExpression, sortDire));
}
// 这个是调用存储过程的方法
return(DbLogic.GetDataTableByPage(DbHelper, out recordCount, pageIndex, pageSize, sortExpression, sortDire, tableName, conditional, selectField));
}
/// <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);
}
public DataTable GetUserDataTable(string systemCode, string roleId, string companyId, string userId, string searchValue, out int recordCount, int pageIndex, int pageSize, string orderBy)
{
DataTable result = new DataTable(BaseUserEntity.TableName);
string tableName = BaseUserRoleEntity.TableName;
if (!string.IsNullOrWhiteSpace(systemCode))
{
tableName = systemCode + "UserRole";
}
string commandText = @"SELECT BaseUser.Id
, BaseUser.Code
, BaseUser.UserName
, BaseUser.CompanyName
, BaseUser.DepartmentName
, BaseUser.RealName
, BaseUser.Description
, UserRole.Enabled
, UserRole.CreateOn
, UserRole.CreateBy
, UserRole.ModifiedOn
, UserRole.ModifiedBy
FROM BaseUser,
(SELECT UserId, Enabled, CreateOn, CreateBy, ModifiedOn, ModifiedBy
FROM BaseUserRole
WHERE RoleId = " + DbHelper.GetParameter(BaseUserRoleEntity.FieldRoleId) + @" AND DeletionStateCode = 0) UserRole
WHERE BaseUser.Id = UserRole.UserId
AND BaseUser.DeletionStateCode = 0 ";
if (!string.IsNullOrEmpty(searchValue))
{
// 2016-02-25 吉日嘎拉 增加搜索功能、方便管理
if (searchValue.IndexOf("%") < 0)
{
searchValue = string.Format("%{0}%", searchValue);
}
commandText += " AND (" + BaseUserEntity.FieldCode + " LIKE '" + searchValue + "'"
+ " OR " + BaseUserEntity.FieldUserName + " LIKE '" + searchValue + "'"
+ " OR " + BaseUserEntity.FieldDepartmentName + " LIKE '" + searchValue + "'"
+ " OR " + BaseUserEntity.FieldRealName + " LIKE '" + searchValue + "')";
}
// ORDER BY UserRole.CreateOn DESC ";
commandText = commandText.Replace("BaseUserRole", tableName);
List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
dbParameters.Add(DbHelper.MakeParameter(BaseUserRoleEntity.FieldRoleId, roleId));
if (!string.IsNullOrEmpty(companyId))
{
commandText += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = " + DbHelper.GetParameter(BaseUserEntity.FieldCompanyId);
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldCompanyId, companyId));
}
if (!string.IsNullOrEmpty(userId))
{
commandText += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = " + DbHelper.GetParameter(BaseUserEntity.FieldId);
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldId, userId));
}
commandText = "(" + commandText + ") T ";
// 2015-12-05 吉日嘎拉 增加参数化功能
result = DbLogic.GetDataTableByPage(this.DbHelper, out recordCount, commandText, "*", pageIndex, pageSize, null, dbParameters.ToArray(), orderBy);
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>
/// 获取权限审核
/// </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);
}
/// <summary>
/// 获取等审核信息
/// </summary>
/// <param name="userId">用户主键</param>
/// <param name="categoryCode">分类代码</param>
/// <param name="categorybillFullName"></param>
/// <param name="searchValue">查询字符串</param>
/// <param name="containsTheRejectedDocuments">显示退回的</param>
/// <returns>数据表</returns>
public DataTable GetWaitForAuditByPage(out int recordCount, int pageIndex = 0, int pageSize = 100, string sort = null, string direction = "ASC", string userId = null, string categoryCode = null, string categorybillFullName = null, string searchValue = null, bool containsTheRejectedDocuments = true)
{
if (string.IsNullOrEmpty(userId))
{
userId = this.UserInfo.Id;
}
string sqlQuery = "SELECT * "
+ " FROM " + BaseWorkFlowCurrentEntity.TableName
// 未被删除的，有效的数据，还没能审核结束的
+ " WHERE (" + BaseWorkFlowCurrentEntity.FieldDeletionStateCode + " = 0) "
// Enabled 0 表示，审核还没结束
// Enabled 1 表示，审核已经完成了
+ " AND (" + BaseWorkFlowCurrentEntity.FieldEnabled + " = 0) ";
if (!containsTheRejectedDocuments)
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldAuditStatus + " != 'AuditReject') ";
}
if (!string.IsNullOrEmpty(userId))
{
// 待审核的工作流（指向用户的）
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "='" + userId + "' ";
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserId + " LIKE '%" + userId + "%'";
//（指向角色的）
BaseUserManager userManager = new BaseUserManager(this.UserInfo);
string[] roleIds = userManager.GetRoleIds(userId);
if (roleIds != null && roleIds.Length > 0)
{
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ")";
}
//（指向部门的）
string[] organizeIds = userManager.GetAllOrganizeIds(userId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " OR (" + BaseWorkFlowCurrentEntity.FieldToUserId + " IS NULL AND " + BaseWorkFlowCurrentEntity.FieldToDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
sqlQuery += " ) ";
}
if (!string.IsNullOrEmpty(categoryCode))
{
sqlQuery += " AND ProcessId IN (SELECT Id FROM BaseWorkFlowProcess WHERE (CategoryCode = '" + categoryCode + "')) ";
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.TableName + "." + BaseWorkFlowCurrentEntity.FieldCategoryFullName + " ='" + categorybillFullName + "') ";
}
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
if (!String.IsNullOrEmpty(searchValue))
{
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE '" + searchValue + "'";
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE '" + searchValue + "'";
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE '" + searchValue + "'";
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE '" + searchValue + "'";
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE '" + searchValue + "'";
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE '" + searchValue + "'" + ")";
}
sqlQuery = string.Format("({0})", sqlQuery);
var dt = DbLogic.GetDataTableByPage(this.dbHelper, out recordCount, sqlQuery, "*",
pageIndex, pageSize, null, null, "");
return(dt);
}
/// <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 DataTable GetDataTableByPage(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.GetDataTableByPage(DbHelper, this.CurrentTableName, this.SelectFields, pageIndex, pageSize, whereClause, dbParameters, order, this.CurrentIndex));
}
