/// <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 GetDTByPage(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.IndexOf("SELECT", System.StringComparison.OrdinalIgnoreCase) >= 0 || DBProvider.CurrentDbType == RDIFramework.Utilities.CurrentDbType.MySql)
{
// 统计总条数
string commandText = string.Empty;
if (string.IsNullOrEmpty(tableName))
{
tableName = this.CurrentTableName;
}
commandText = tableName;
if (tableName.IndexOf("SELECT", System.StringComparison.OrdinalIgnoreCase) >= 0)
{
commandText = "(" + tableName + ") AS T ";
}
string whereStatement = string.Empty;
if (!string.IsNullOrEmpty(whereConditional))
{
whereStatement = string.Format(" WHERE {0} ", whereConditional);
}
commandText = string.Format("SELECT COUNT(1) AS recordCount FROM {0} {1}", commandText, whereStatement);
object returnObject = DBProvider.ExecuteScalar(commandText);
recordCount = returnObject != null?int.Parse(returnObject.ToString()) : 0;
if (DBProvider.CurrentDbType == RDIFramework.Utilities.CurrentDbType.MySql)
{
return(DbCommonLibary.GetDTByPage(DBProvider, tableName, recordCount, pageIndex, pageSize, whereConditional, sortExpression, sortDire));
}
return(DbCommonLibary.GetDTByPage(DBProvider, recordCount, pageIndex, pageSize, tableName, sortExpression, sortDire));
}
// 这个是调用存储过程的方法
return(DbCommonLibary.GetDTByPage(DBProvider, out recordCount, pageIndex, pageSize, sortExpression, sortDire, tableName, whereConditional, selectField));
}
/// <summary>
/// 获取数据权限目标主键
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="resourceCategory">资源类别</param>
/// <param name="targetId">目标资源主键</param>
/// <param name="targetResourceCategory">目标资源类别</param>
/// <param name="permissionItemCode">操作权限编号</param>
/// <returns>资源主键数组</returns>
public string[] GetPermissionScopeResourceIds(UserInfo userInfo, string resourceCategory, string targetId, string targetResourceCategory, string permissionItemCode)
{
string[] returnValue = null;
var parameter = ParameterUtil.CreateWithLog(userInfo, MethodBase.GetCurrentMethod());
ServiceUtil.ProcessRDIReadDb(userInfo, parameter, dbProvider =>
{
var permissionItemManager = new PiPermissionItemManager(dbProvider, userInfo);
var permissionItemId = permissionItemManager.GetId(PiPermissionItemTable.FieldCode, permissionItemCode);
var names = new string[6];
var values = new object[6];
names[0] = PiPermissionScopeTable.FieldResourceCategory;
values[0] = resourceCategory;
names[1] = PiPermissionScopeTable.FieldTargetId;
values[1] = targetId;
names[2] = PiPermissionScopeTable.FieldPermissionId;
values[2] = permissionItemId;
names[3] = PiPermissionScopeTable.FieldTargetCategory;
values[3] = targetResourceCategory;
names[4] = PiPermissionScopeTable.FieldDeleteMark;
values[4] = 0;
names[5] = PiPermissionScopeTable.FieldEnabled;
values[5] = 1;
returnValue = DbCommonLibary.GetIds(dbProvider, PiPermissionScopeTable.TableName, names, values, PiPermissionScopeTable.FieldResourceId);
});
return(returnValue);
}
public virtual IDataReader GetDataReaderByPage(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) && (this.DBProvider.CurrentDbType != CurrentDbType.MySql))
{
return(DbCommonLibary.GetDataReaderByPage(this.DBProvider, out recordCount, pageIndex, pageSize, sortExpression, sortDire, tableName, conditional, selectField));
}
string str = string.Empty;
if (string.IsNullOrEmpty(tableName))
{
tableName = this.CurrentTableName;
}
string str2 = string.Empty;
if (!string.IsNullOrEmpty(conditional))
{
str2 = string.Format(" WHERE {0} ", conditional);
}
str = tableName;
if (tableName.ToUpper().IndexOf("SELECT") >= 0)
{
str = "(" + tableName + ") T ";
}
str = string.Format("SELECT COUNT(1) AS recordCount FROM {0} {1}", str, str2);
object obj2 = this.DBProvider.ExecuteScalar(str, dbParameters);
recordCount = obj2 != null?int.Parse(obj2.ToString()) : 0;
return(DbCommonLibary.GetDataReaderByPage(this.DBProvider, recordCount, pageIndex, pageSize, tableName, dbParameters, sortExpression, sortDire));
}
public DataTable GetFileDTByPage(out int recordCount, int pageIndex = 1, int pageSize = 20, string whereConditional = "", string order = "")
{
this.SelectField = CiFileTable.FieldId
+ " ," + CiFileTable.FieldFolderId
+ " ," + CiFileTable.FieldFileName
+ " ," + CiFileTable.FieldFilePath
+ " ," + CiFileTable.FieldFileSize
+ " ," + CiFileTable.FieldReadCount
+ " ," + CiFileTable.FieldCategory
+ " ," + CiFileTable.FieldDescription
+ " ," + CiFileTable.FieldEnabled
+ " ," + CiFileTable.FieldSortCode
+ " ," + CiFileTable.FieldCreateUserId
+ " ," + CiFileTable.FieldCreateBy
+ " ," + CiFileTable.FieldCreateOn
+ " ," + CiFileTable.FieldModifiedUserId
+ " ," + CiFileTable.FieldModifiedBy
+ " ," + CiFileTable.FieldModifiedOn
+ " , (SELECT " + CiFolderTable.FieldFolderName
+ " FROM " + CiFolderTable.TableName
+ " WHERE " + CiFolderTable.FieldId + " = '" + CiFileTable.FieldFolderId +
"') AS FolderFullName ";
recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional);
return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order));
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id">主键</param>
/// <returns>影响行数</returns>
public int Delete(string id)
{
int returnValue = 0;
PiStaffEntity staffEntity = this.GetEntity(id);
if (!string.IsNullOrEmpty(staffEntity.UserId))
{
// 删除角色用户关联表
returnValue += DbCommonLibary.Delete(DBProvider, PiUserRoleTable.TableName, PiUserRoleTable.FieldUserId, staffEntity.UserId);
// 删除用户的权限数据
// 删除用户的权限范围数据
// 删除相关的用户数据
PiUserManager userManager = new PiUserManager(DBProvider, UserInfo);
returnValue += userManager.DeleteEntity(staffEntity.UserId);
}
//删除员工组织关系关联表
returnValue += DbCommonLibary.Delete(DBProvider, PiStaffOrganizeTable.TableName, PiStaffOrganizeTable.FieldStaffId, id);
// 删除员工本表
returnValue += DbCommonLibary.Delete(DBProvider, PiStaffTable.TableName, PiStaffTable.FieldId, id);
return(returnValue);
}
/// <summary>
/// 获得序列
/// </summary>
/// <param name="fullName">序列名</param>
/// <param name="defaultSequence">默认序列</param>
/// <returns>序列实体</returns>
protected CiSequenceEntity GetSequenceByLock(string fullName, int defaultSequence)
{
CiSequenceEntity sequenceEntity = new CiSequenceEntity();
// 这里主要是为了判断是否存在
sequenceEntity = this.GetEntityByName(fullName);
if (sequenceEntity == null)
{
// 这里添加记录时加锁机制。
// 是否已经被锁住
this.ReturnStatusCode = StatusCode.CanNotLock.ToString();
for (int i = 0; i < SystemInfo.LockNoWaitCount; i++)
{
// 被锁定的记录数
int lockCount = DbCommonLibary.LockNoWait(DBProvider, CiSequenceTable.TableName, CiSequenceTable.FieldFullName, CiSequenceTable.TableName);
if (lockCount > 0)
{
sequenceEntity.FullName = fullName;
sequenceEntity.Reduction = defaultSequence - 1;
sequenceEntity.Sequence = defaultSequence;
sequenceEntity.Step = DefaultStep;
this.AddEntity(sequenceEntity);
this.ReturnStatusCode = StatusCode.LockOK.ToString();
break;
}
else
{
System.Threading.Thread.Sleep(RandomHelper.GetRandom(1, SystemInfo.LockNoWaitTickMilliSeconds));
}
}
if (this.ReturnStatusCode == StatusCode.LockOK.ToString())
{
// XuWangBin 这个是否能省略
sequenceEntity = this.GetEntityByName(fullName);
}
}
else
{
// 若记录已经存在，加锁，然后读取记录。
// 是否已经被锁住
this.ReturnStatusCode = StatusCode.CanNotLock.ToString();
for (int i = 0; i < SystemInfo.LockNoWaitCount; i++)
{
// 被锁定的记录数
int lockCount = DbCommonLibary.LockNoWait(DBProvider, CiSequenceTable.TableName, CiSequenceTable.FieldFullName, fullName);
if (lockCount > 0)
{
sequenceEntity = this.GetEntityByName(fullName);
this.ReturnStatusCode = StatusCode.LockOK.ToString();
break;
}
else
{
System.Threading.Thread.Sleep(RandomHelper.GetRandom(1, SystemInfo.LockNoWaitTickMilliSeconds));
}
}
}
return(sequenceEntity);
}
public virtual DataTable GetDT(KeyValuePair <string, object> parameter, string order)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >();
parameters.Add(parameter);
return(DbCommonLibary.GetDT(DBProvider, this.CurrentTableName, parameters, 0, order));
}
/// <summary>
/// 检查用户的网卡Mac地址
/// </summary>
/// <param name="macAddress">Mac地址</param>
/// <param name="userId">用户主键</param>
/// <returns>是否符合限制</returns>
private bool CheckMacAddress(string macAddress, string userId)
{
bool returnValue = false;
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(CiParameterTable.FieldParameterId, userId),
new KeyValuePair <string, object>(CiParameterTable.FieldCategoryKey, "MacAddress"),
new KeyValuePair <string, object>(CiParameterTable.FieldEnabled, 1)
};
DataTable dt = DbCommonLibary.GetDT(this.DBProvider, CiParameterTable.TableName, parameters);
if (dt.Rows.Count > 0)
{
string parameterCode = string.Empty;
string parameterCotent = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
parameterCode = dt.Rows[i][CiParameterTable.FieldParameterCode].ToString();
parameterCotent = dt.Rows[i][CiParameterTable.FieldParameterContent].ToString();
returnValue = (macAddress.ToLower()).Equals(parameterCotent.ToLower());
if (returnValue)
{
break;
}
}
}
return(returnValue);
}
public virtual IDataReader GetDataReader(KeyValuePair <string, object> parameter, string order)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > {
parameter
};
return(DbCommonLibary.GetDataReader(this.DBProvider, this.CurrentTableName, parameters, 0, order));
}
public virtual IDataReader GetDataReaderByParent(string parentId)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > {
new KeyValuePair <string, object>(BusinessLogic.FieldParentId, parentId)
};
return(DbCommonLibary.GetDataReader(this.DBProvider, this.CurrentTableName, parameters, 0, BusinessLogic.FieldSortCode));
}
public virtual int SetProperty(KeyValuePair <string, object> whereParameter, List <KeyValuePair <string, object> > parameters)
{
var whereParameters = new List <KeyValuePair <string, object> > {
whereParameter
};
return(DbCommonLibary.SetProperty(DBProvider, this.CurrentTableName, whereParameters, parameters));
}
/// <summary>
/// 获得主键列表
/// </summary>
/// <param name="parameter">键值对（字段名/字段值）</param>
/// <returns>主键列表</returns>
public virtual string[] GetIds(KeyValuePair <string, object> parameter)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > {
parameter
};
return(DbCommonLibary.GetProperties(DBProvider, this.CurrentTableName, parameters, 0, BusinessLogic.FieldId));
}
public virtual DataTable GetDT(KeyValuePair <string, object> parameter, int topLimit = 0, string order = null)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > {
parameter
};
return(DbCommonLibary.GetDT(DBProvider, this.CurrentTableName, parameters, topLimit, order));
}
public virtual string GetProperty(KeyValuePair <string, object> parameter, string targetField)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > {
parameter
};
return(DbCommonLibary.GetProperty(DBProvider, this.CurrentTableName, parameters, targetField));
}
public virtual bool Exists(KeyValuePair <string, object> parameter, object id)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> > {
parameter
};
return(DbCommonLibary.Exists(DBProvider, this.CurrentTableName, parameters, new KeyValuePair <string, object>(this.PrimaryKey, id)));
}
public virtual string GetProperty(object id, string targetField)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(BusinessLogic.FieldId, id)
};
return(DbCommonLibary.GetProperty(DBProvider, this.CurrentTableName, parameters, targetField));
}
public virtual List <T> GetList <T>(int topLimit = 0, string order = null) where T : BaseEntity, new()
{
List <T> list = new List <T>();
using (IDataReader dr = DbCommonLibary.GetDataReader(DBProvider, this.CurrentTableName, null, topLimit, order))
{
list = GetList <T>(dr);
}
return(list);
}
public virtual List <T> GetList <T>(string name, Object[] values, string order = null) where T : BaseEntity, new()
{
List <T> list = new List <T>();
using (IDataReader dr = DbCommonLibary.GetDataReader(DBProvider, this.CurrentTableName, name, values, order))
{
list = GetList <T>(dr);
}
return(list);
}
public virtual List <T> GetList <T>(string[] ids) where T : BaseEntity, new()
{
List <T> list = new List <T>();
using (IDataReader dr = DbCommonLibary.GetDataReader(DBProvider, this.CurrentTableName, BusinessLogic.FieldId, ids))
{
list = GetList <T>(dr);
}
return(list);
}
public virtual string[] GetProperties(KeyValuePair <string, object> parameter1, KeyValuePair <string, object> parameter2, int topLimit, string targetField)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >
{
parameter1,
parameter2
};
return(DbCommonLibary.GetProperties(DBProvider, this.CurrentTableName, parameters, topLimit, targetField));
}
public virtual List <T> GetList <T>(List <KeyValuePair <string, object> > parameters, string order) where T : BaseEntity, new()
{
List <T> list = new List <T>();
using (IDataReader dr = DbCommonLibary.GetDataReader(DBProvider, this.CurrentTableName, parameters, 0, order))
{
list = GetList <T>(dr);
}
return(list);
}
public virtual bool Exists(KeyValuePair <string, object> parameter1, KeyValuePair <string, object> parameter2)
{
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >
{
parameter1,
parameter2
};
return(DbCommonLibary.Exists(DBProvider, this.CurrentTableName, parameters));
}
public virtual int SetProperty(string name, Object[] values, string targetField, object targetValue)
{
var returnValue = 0;
if (values == null)
{
returnValue += DbCommonLibary.SetProperty(DBProvider, this.CurrentTableName, name, string.Empty, targetField, targetValue);
}
else
{
returnValue += values.Sum(t => DbCommonLibary.SetProperty(DBProvider, this.CurrentTableName, name, t, targetField, targetValue));
}
return(returnValue);
}
/// <summary>
/// 检查用户IP地址
/// </summary>
/// <param name="ipAddress">IP地址</param>
/// <returns>是否符合限制</returns>
private bool CheckIPAddress(string ipAddress, string userId)
{
bool returnValue = false;
List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(CiParameterTable.FieldParameterId, userId),
new KeyValuePair <string, object>(CiParameterTable.FieldCategoryKey, "IPAddress"),
new KeyValuePair <string, object>(CiParameterTable.FieldEnabled, 1)
};
DataTable dt = DbCommonLibary.GetDT(this.DBProvider, CiParameterTable.TableName, parameters);
if (dt.Rows.Count > 0)
{
string parameterCode = string.Empty;
string parameterCotent = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
parameterCode = dt.Rows[i][CiParameterTable.FieldParameterCode].ToString();
parameterCotent = dt.Rows[i][CiParameterTable.FieldParameterContent].ToString();
switch (parameterCode)
{
// 匹配单个IP
case "Single":
returnValue = CheckSingleIPAddress(ipAddress, parameterCotent);
break;
// 匹配ip地址段
case "Range":
returnValue = CheckIPAddressWithRange(ipAddress, parameterCotent);
break;
// 匹配带掩码的地址段
case "Mask":
returnValue = CheckIPAddressWithMask(ipAddress, parameterCotent);
break;
}
if (returnValue)
{
break;
}
}
}
return(returnValue);
}
/// <summary>
/// 删除实体
/// </summary>
/// <param name="id">主键</param>
/// <returns>影响行数</returns>
public int Delete(string id)
{
int returnValue = 0;
// 删除用户角色表
returnValue += DbCommonLibary.Delete(DBProvider, PiUserRoleTable.TableName, PiUserRoleTable.FieldRoleId, id);
// 删除角色表
var parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(PiRoleTable.FieldId, id),
new KeyValuePair <string, object>(PiRoleTable.FieldAllowDelete, 1)
};
returnValue += DbCommonLibary.Delete(DBProvider, PiRoleTable.TableName, parameters);
return(returnValue);
}
/// <summary>
/// 用户添加之前执行的方法
/// </summary>
/// <param name="userEntity">用户实体</param>
/// <param name="statusCode">状态码</param>
public void BeforeAdd(PiUserEntity userEntity, out string statusCode)
{
statusCode = StatusCode.OK.ToString();
if (!string.IsNullOrEmpty(userEntity.UserName) && this.Exists(PiUserTable.FieldUserName, userEntity.UserName, PiUserTable.FieldDeleteMark, "0") || DbCommonLibary.Exists(DBProvider, PiStaffTable.TableName, PiStaffTable.FieldUserName, userEntity.UserName, PiStaffTable.FieldDeleteMark, "0"))
{
// 用户名已重复
statusCode = StatusCode.ErrorUserExist.ToString();
}
else
{
// 检查编号是否重复
if (!string.IsNullOrEmpty(userEntity.Code) && this.Exists(PiUserTable.FieldCode, userEntity.Code, PiUserTable.FieldDeleteMark, "0"))
{
// 编号已重复
statusCode = StatusCode.ErrorCodeExist.ToString();
}
if (userEntity.IsStaff == 1)
{
var parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(PiStaffTable.FieldUserName, userEntity.UserName),
new KeyValuePair <string, object>(PiStaffTable.FieldDeleteMark, 0)
};
if (DbCommonLibary.Exists(DBProvider, PiStaffTable.TableName, parameters))
{
// 编号已重复
statusCode = StatusCode.ErrorNameExist.ToString();
}
if (!string.IsNullOrEmpty(userEntity.Code))
{
parameters = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>(PiStaffTable.FieldCode, userEntity.Code),
new KeyValuePair <string, object>(PiStaffTable.FieldDeleteMark, 0)
};
if (DbCommonLibary.Exists(DBProvider, PiStaffTable.TableName, parameters))
{
// 编号已重复
statusCode = StatusCode.ErrorCodeExist.ToString();
}
}
}
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////
/// 资源权限设定关系相关
//////////////////////////////////////////////////////////////////////////////////////////////////////
#region public string[] GetResourcePermissionItemIds(UserInfo userInfo, string resourceCategory, string resourceId) 获取资源权限主键数组
/// <summary>
/// 获取资源权限主键数组
/// </summary>
/// <param name="userInfo">用户</param>
/// <param name="resourceCategory">资源分类</param>
/// <param name="resourceId">资源主键</param>
/// <returns>操作权限主键数组</returns>
public string[] GetResourcePermissionItemIds(UserInfo userInfo, string resourceCategory, string resourceId)
{
string[] returnValue = null;
var parameter = ParameterUtil.CreateWithMessage(userInfo, MethodBase.GetCurrentMethod(), this.serviceName, RDIFrameworkMessage.PermissionService_GetResourcePermissionItemIds);
ServiceUtil.ProcessRDIReadDb(userInfo, parameter, dbProvider =>
{
var names = new string[2];
var values = new string[2];
names[0] = PiPermissionTable.FieldResourceCategory;
values[0] = resourceCategory;
names[1] = PiPermissionTable.FieldResourceId;
values[1] = resourceId;
DataTable dataTable = DbCommonLibary.GetDT(dbProvider, PiPermissionTable.TableName, names, values);
returnValue = BusinessLogic.FieldToArray(dataTable, PiPermissionTable.FieldPermissionId);
});
return(returnValue);
}
/// <summary>
/// 判断用户是否有有相应的权限
/// </summary>
/// <param name="userId">用户主键</param>
/// <param name="permissionItemCode">权限编号</param>
/// <returns>有权限</returns>
public bool CheckPermission(string userId, string permissionItemCode)
{
PiPermissionItemManager permissionItemManager = new PiPermissionItemManager(DBProvider);
string permissionItemId = permissionItemManager.GetIdByCode(permissionItemCode);
// 没有找到相应的权限
if (String.IsNullOrEmpty(permissionItemId))
{
return(false);
}
string[] names = new string[3];
string[] values = new string[3];
names[0] = PiPermissionTable.FieldResourceCategory;
values[0] = PiUserTable.TableName;
names[1] = PiPermissionTable.FieldResourceId;
values[1] = userId;
names[2] = PiPermissionTable.FieldEnabled;
values[2] = "1";
return(DbCommonLibary.Exists(DBProvider, this.CurrentTableName, names, values));
}
public DataTable GetChildrenStaffs(string organizeId)
{
PiOrganizeManager organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo);
string[] organizeIds = null;
switch (DBProvider.CurrentDbType)
{
case CurrentDbType.Access:
case CurrentDbType.SqlServer:
string organizeCode = DbCommonLibary.GetProperty(dbProvider, PiOrganizeTable.TableName, PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldCode); //this.GetCodeById(organizeId);
organizeIds = organizeManager.GetChildrensIdByCode(PiOrganizeTable.FieldCode, organizeCode);
break;
case CurrentDbType.Oracle:
organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldParentId);
break;
case CurrentDbType.MySql:
organizeIds = organizeManager.GetMySqlChildrensId(PiOrganizeTable.FieldId, organizeId, PiOrganizeTable.FieldParentId);
break;
}
return(this.GetDTByOrganizes(organizeIds));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="searchValue">查询字段</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 GetDTByPage(string searchValue, string departmentId, string roleId, out int recordCount, int pageIndex = 0, int pageSize = 20, string order = null)
{
string whereConditional = PiUserTable.TableName + "." + PiUserTable.FieldDeleteMark + " = 0 "
+ " AND " + PiUserTable.TableName + "." + PiUserTable.FieldEnabled + " = 1 "
+ " AND " + PiUserTable.TableName + "." + PiUserTable.FieldIsVisible + " = 1 ";
if (!String.IsNullOrEmpty(departmentId))
{
/*
* 用非递归调用的建议方法
* sqlQuery += " AND " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId
+ " IN ( SELECT " + PiOrganizeTable.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + PiOrganizeTable.FieldId + " = " + departmentId + " OR " + PiOrganizeTable.FieldParentId + " = " + departmentId + ")";
*/
PiOrganizeManager organizeManager = new PiOrganizeManager(this.DBProvider, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(PiOrganizeTable.FieldId, departmentId, PiOrganizeTable.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
whereConditional += " AND (" + PiUserTable.TableName + "." + PiUserTable.FieldCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubCompanyId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldSubDepartmentId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + ")"
+ " OR " + PiUserTable.TableName + "." + PiUserTable.FieldWorkgroupId + " IN (" + StringHelper.ArrayToList(organizeIds, "'") + "))";
}
}
if (!string.IsNullOrEmpty(roleId))
{
string tableNameUserRole = PiUserRoleTable.TableName;
whereConditional += " AND ( " + PiUserTable.TableName + "." + PiUserTable.FieldId + " IN "
+ " (SELECT + " + PiUserRoleTable.FieldUserId
+ " FROM " + tableNameUserRole
+ " WHERE " + PiUserRoleTable.FieldRoleId + " = '" + roleId + "'"
+ " AND " + PiUserRoleTable.FieldEnabled + " = 1"
+ " AND " + PiUserRoleTable.FieldDeleteMark + " = 0)) ";
}
if (!string.IsNullOrEmpty(searchValue))
{
searchValue = "'" + StringHelper.GetSearchString(searchValue) + "'";
whereConditional += " AND (" + PiUserTable.FieldRealName + " LIKE " + searchValue;
whereConditional += " OR " + PiUserTable.FieldUserName + " LIKE " + searchValue + ")";
//whereConditional += " AND (" + searchValue + ")"; ;
}
recordCount = DbCommonLibary.GetCount(DBProvider, this.CurrentTableName, whereConditional);
this.CurrentTableName = "PIUSER LEFT OUTER JOIN PIUSERLOGON ON PIUSER.ID = PIUSERLOGON.ID ";
switch (DBProvider.CurrentDbType)
{
case CurrentDbType.SqlServer:
case CurrentDbType.Access:
case CurrentDbType.Oracle:
case CurrentDbType.MySql:
this.SelectField = PiUserTable.TableName + ".* "
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldFirstVisit
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldPreviousVisit
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLastVisit
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldIPAddress
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldMACAddress
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldLogOnCount
+ "," + PiUserLogOnTable.TableName + "." + PiUserLogOnTable.FieldUserOnLine;
break;
case CurrentDbType.DB2:
break;
}
return(DbCommonLibary.GetDTByPage(DBProvider, this.CurrentTableName, this.SelectField, pageIndex, pageSize, whereConditional, order));
}