/// <summary>
/// 根据角色获取清单
/// </summary>
/// <param name="systemCode">系统编码</param>
/// <param name="roleIds"></param>
/// <param name="companyId"></param>
/// <returns></returns>
public List <BaseUserEntity> GetListByRole(string systemCode, string[] roleIds, string companyId = null)
{
var result = new List <BaseUserEntity>();
var dbParameters = new List <IDbDataParameter>();
var userRoleTableName = systemCode + "UserRole";
var sql = "SELECT " + SelectFields
+ " FROM " + BaseUserEntity.CurrentTableName
+ " , (SELECT " + BaseUserRoleEntity.FieldUserId
+ " FROM " + userRoleTableName
+ " WHERE " + BaseUserRoleEntity.FieldRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ")"
+ " AND " + BaseUserRoleEntity.FieldEnabled + " = 1 "
+ " AND " + BaseUserRoleEntity.FieldDeleted + " = 0) B "
+ " WHERE " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldId + " = B." + BaseUserRoleEntity.FieldUserId
+ " AND " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldEnabled + " = 1 "
+ " AND " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldDeleted + "= 0";
if (!string.IsNullOrWhiteSpace(companyId))
{
sql += " AND " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldCompanyId + " = " + DbHelper.GetParameter(BaseUserEntity.FieldCompanyId);
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldCompanyId, companyId));
}
using (var dr = DbHelper.ExecuteReader(sql, dbParameters.ToArray()))
{
result = GetList <BaseUserEntity>(dr);
}
return(result);
}
/// <summary>
/// 获取能访问的字段列表
/// </summary>
/// <param name="tableCode">表名</param>
/// <returns>数据表</returns>
public DataTable GetTableColumns(string userId, string tableCode)
{
// 当前用户对哪些资源有权限（用户自己的权限 + 相应的角色拥有的权限）
BasePermissionScopeManager permissionScopeManager = new BasePermissionScopeManager(DbHelper, UserInfo);
string[] ids = permissionScopeManager.GetResourceScopeIds(userId, "TableColumns", "ColumnAccess");
// 获取有效的，没删除标志的
string sqlQuery = " SELECT * FROM BaseTableColumns WHERE (DeletionStateCode = 0 AND Enabled = 1) ";
// 是否指定了表名
if (!string.IsNullOrEmpty(tableCode))
{
sqlQuery += " AND (TableCode = '" + tableCode + "') ";
}
// 公开的或者按权限来过滤字段
sqlQuery += " AND (IsPublic = 1 ";
if (ids != null && ids.Length > 0)
{
string idList = StringUtil.ArrayToList(ids);
sqlQuery += " OR Id IN (" + idList + ")";
}
sqlQuery += ") ORDER BY SortCode ";
return(DbHelper.Fill(sqlQuery));
}
public void Test_Utilities_StringUtil()
{
Console.WriteLine("Test_Utilities_ReflectionUtil");
string a = StringUtil.GetLike("field1", "三味书屋");
Console.WriteLine(string.Format("GetLike三味书屋=>{0}", a));
string a1 = StringUtil.GetSearchString("三味书屋", true);
Console.WriteLine(string.Format("GetSearchString(\"三味书屋\",true)=>{0}", a1));
string a2 = StringUtil.GetSearchString("三味书屋");
Console.WriteLine(string.Format("GetSearchString(\"三味书屋\")=>{0}", a2));
string[] arr = new string[] { "一", "而", "三层" };
bool b = StringUtil.Exists(arr, "一");
bool b1 = StringUtil.Exists(arr, "而");
bool b2 = StringUtil.Exists(arr, "三层");
Assert.AreEqual(true, b || b1 || b2);
string[] arr1 = new string[] { "元老1", "元老2", "元老3" };
string[] c = StringUtil.Concat(arr1, "我是新合并进来的");
Console.WriteLine(string.Format("Concat()后的数组元素=>{0}", string.Join(",", c)));
string[] arr2 = new string[] { "元老1", "元老2", "元老3" };
string[] arr3 = new string[] { "新来的1", "新来的2", "新来的3" };
string[] d = StringUtil.Concat(arr2, arr3);
Console.WriteLine(string.Format("Concat()后的数组元素=>{0}", string.Join(",", d)));
string[] arr4 = new string[] { "元老1", "元老2", "移除的元老3" };
string[] e = StringUtil.Remove(arr4, "移除的元老3");
Console.WriteLine(string.Format("Remove()后的数组元素=>{0}", string.Join(",", e)));
string[] arr5 = new string[] { "元老1", "元老2", "元老3" };
string f = StringUtil.ArrayToList(arr5);
Console.WriteLine(string.Format("ArrayToList()后的数组元素=>{0}", f));
string[] arr6 = new string[] { "元老1", "元老2", "元老3" };
string g = StringUtil.ArrayToList(arr5, "'");
Console.WriteLine(string.Format("ArrayToList()后的数组元素=>{0}", g));
string h = StringUtil.RepeatString("重复", 4);
Console.WriteLine(string.Format("RepeatString(\"重复\", 4)=>{0}", h));
string i = StringUtil.GetFirstPinyin("罗小明");
Console.WriteLine(string.Format("GetFirstPinyin(\"罗小明\")=>{0}", i));
string j = StringUtil.GetPinyin("罗小明");
Console.WriteLine(string.Format("GetPinyin(\"罗小明\")=>{0}", j));
}
/// <summary>
/// 获得条件语句
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="parameters"></param>
/// <param name="relation"></param>
/// <returns></returns>
public static string GetWhereString(IDbHelper dbHelper, List <KeyValuePair <string, object> > parameters, string relation)
{
string returnValue = string.Empty;
if (parameters == null)
{
return(returnValue);
}
string subSqlQuery = string.Empty;
foreach (var parameter in parameters)
{
if ((parameter.Key != null) && (parameter.Key.Length > 0))
{
//if (values[i] == null || String.IsNullOrEmpty(values[i].ToString()))
if (parameter.Value == null)
{
subSqlQuery = " (" + parameter.Key + " IS NULL) ";
}
else
{
if (parameter.Value is Array)
{
if (((Array)parameter.Value).Length > 0)
{
subSqlQuery = " (" + parameter.Key + " IN (" + StringUtil.ArrayToList((string[])parameter.Value, "'") + ")) ";
}
else
{
subSqlQuery = " (" + parameter.Key + " IS NULL) ";
}
}
else
{
subSqlQuery = " (" + parameter.Key + " = " + dbHelper.GetParameter(parameter.Key) + ") ";
//if ((values[i].ToString().IndexOf('[') >= 0) || (values[i].ToString().IndexOf(']') >= 0))
//{
// values[i] = values[i].ToString().Replace("[", "/[");
// values[i] = values[i].ToString().Replace("]", "/]");
// values[i] = SqlSafe(values[i].ToString());
// subSqlQuery = " (" + names[i] + " LIKE '" + values[i] + "' ESCAPE '/') ";
// values[i] = null;
// subSqlQuery = " (" + names[i] + " LIKE ? ESCAPE '/') ";
//}
}
// 这里操作，就会有些重复了，不应该进行处理
// values[i] = this.SqlSafe(values[i].ToString());
}
returnValue += subSqlQuery + relation;
}
}
if (returnValue.Length > 0)
{
returnValue = returnValue.Substring(0, returnValue.Length - relation.Length - 1);
}
return(returnValue);
}
/// <summary>
/// 获得条件语句
/// 20110523 吉日嘎拉，改进空数组
/// </summary>
/// <param name="dbHelper">数据库连接</param>
/// <param name="names">字段名</param>
/// <param name="values">字段值</param>
/// <param name="relation">逻辑关系</param>
/// <returns>字符串</returns>
public static string GetWhereString(IDbHelper dbHelper, ref string[] names, Object[] values, string relation)
{
string returnValue = string.Empty;
string subSqlQuery = string.Empty;
for (int i = 0; i < names.Length; i++)
{
if ((names[i] != null) && (names[i].Length > 0))
{
//if (values[i] == null || String.IsNullOrEmpty(values[i].ToString()))
if (values[i] == null)
{
subSqlQuery = " (" + names[i] + " IS NULL) ";
// 这里就不需要参数化了
names[i] = null;
}
else
{
if (values[i] is Array)
{
if (((Array)values[i]).Length > 0)
{
subSqlQuery = " (" + names[i] + " IN (" + StringUtil.ArrayToList((string[])values[i], "'") + ")) ";
}
else
{
subSqlQuery = " (" + names[i] + " IS NULL) ";
}
// 这里就不需要参数化了
names[i] = null;
}
else
{
subSqlQuery = " (" + names[i] + " = " + dbHelper.GetParameter(names[i]) + ") ";
//if ((values[i].ToString().IndexOf('[') >= 0) || (values[i].ToString().IndexOf(']') >= 0))
//{
// values[i] = values[i].ToString().Replace("[", "/[");
// values[i] = values[i].ToString().Replace("]", "/]");
// values[i] = SqlSafe(values[i].ToString());
// subSqlQuery = " (" + names[i] + " LIKE '" + values[i] + "' ESCAPE '/') ";
// values[i] = null;
// subSqlQuery = " (" + names[i] + " LIKE ? ESCAPE '/') ";
//}
}
// 这里操作，就会有些重复了，不应该进行处理
// values[i] = this.SqlSafe(values[i].ToString());
}
returnValue += subSqlQuery + relation;
}
}
if (returnValue.Length > 0)
{
returnValue = returnValue.Substring(0, returnValue.Length - relation.Length - 1);
}
return(returnValue);
}
/// <summary>
/// 根据角色获取数据表
/// </summary>
/// <param name="systemCode">系统编码</param>
/// <param name="roleIds"></param>
/// <param name="companyId"></param>
/// <returns></returns>
public DataTable GetDataTableByRole(string systemCode, string[] roleIds, string companyId = null)
{
if (string.IsNullOrEmpty(systemCode))
{
systemCode = "Base";
}
var userRoleTableName = systemCode + "UserRole";
var sql = "SELECT " + SelectFields + " FROM " + BaseUserEntity.CurrentTableName
+ " WHERE " + BaseUserEntity.FieldEnabled + " = 1 "
+ " AND " + BaseUserEntity.FieldDeleted + "= 0 "
+ " AND ( " + BaseUserEntity.FieldId + " IN "
+ " (SELECT " + BaseUserRoleEntity.FieldUserId
+ " FROM " + userRoleTableName
+ " WHERE " + BaseUserRoleEntity.FieldRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ")"
+ " AND " + BaseUserRoleEntity.FieldEnabled + " = 1"
+ " AND " + BaseUserRoleEntity.FieldDeleted + " = 0)) "
+ " ORDER BY " + BaseUserEntity.FieldSortCode;
return(DbHelper.Fill(sql));
}
/// <summary>
/// 获取数据表
/// </summary>
/// <param name="userIds"></param>
/// <param name="name"></param>
/// <param name="value"></param>
/// <param name="beginDate"></param>
/// <param name="endDate"></param>
/// <param name="processId"></param>
/// <returns></returns>
private string GetDataTableSql(string[] userIds, string name, string value, string beginDate, string endDate, string processId = null)
{
var sql = "SELECT * FROM " + BaseLogEntity.CurrentTableName + " WHERE 1=1 ";
if (!string.IsNullOrEmpty(value))
{
sql += string.Format(" AND {0} = '{1}' ", name, value);
}
if (!string.IsNullOrEmpty(processId))
{
// sql += string.Format(" AND {0} = '{1}' ", BaseLogEntity.FieldProcessId, processId);
}
if (!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate))
{
beginDate = DateTime.Parse(beginDate).ToShortDateString();
endDate = DateTime.Parse(endDate).AddDays(1).ToShortDateString();
}
// 注意安全问题
if (userIds != null)
{
sql += string.Format(" AND {0} IN ({1}) ", BaseLogEntity.FieldUserId, StringUtil.ArrayToList(userIds));
}
switch (DbHelper.CurrentDbType)
{
case CurrentDbType.Access:
// Access 中的时间分隔符 是 “#”
if (beginDate.Trim().Length > 0)
{
sql += string.Format(" AND CreateTime >= #{0}#", beginDate);
}
if (endDate.Trim().Length > 0)
{
sql += string.Format(" AND CreateTime <= #{0}#", endDate);
}
break;
case CurrentDbType.SqlServer:
if (beginDate.Trim().Length > 0)
{
sql += string.Format(" AND CreateTime >= '{0}'", beginDate);
}
if (endDate.Trim().Length > 0)
{
sql += string.Format(" AND CreateTime <= '{0}'", endDate);
}
break;
case CurrentDbType.Oracle:
if (beginDate.Trim().Length > 0)
{
sql += string.Format(" AND CreateTime >= TO_DATE( '{0}','yyyy-mm-dd hh24-mi-ss') ", beginDate);
}
if (endDate.Trim().Length > 0)
{
sql += string.Format(" AND CreateTime <= TO_DATE('{0}','yyyy-mm-dd hh24-mi-ss')", endDate);
}
break;
}
sql += " ORDER BY CreateTime DESC ";
return(sql);
}
/// <summary>
/// 获取用户主键数组
/// 2015-11-03 吉日嘎拉 优化程序、用ExecuteReader提高性能
/// </summary>
/// <param name="systemCode">系统编号</param>
/// <param name="roleIds">角色主键数组</param>
/// <returns>用户主键数组</returns>
public string[] GetUserIds(string systemCode, string[] roleIds)
{
string[] result = null;
if (roleIds != null && roleIds.Length > 0)
{
var tableName = systemCode + "UserRole";
var commandText = "SELECT DISTINCT " + BaseUserRoleEntity.FieldUserId + " FROM " + tableName + " WHERE " + BaseUserRoleEntity.FieldRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ") "
+ " AND (" + BaseUserRoleEntity.FieldUserId + " IN (SELECT " + BaseUserEntity.FieldId + " FROM " + BaseUserEntity.CurrentTableName + " WHERE " + BaseUserEntity.FieldDeleted + " = 0)) AND (" + BaseUserRoleEntity.FieldDeleted + " = 0)";
var ids = new List <string>();
var dataReader = DbHelper.ExecuteReader(commandText);
if (dataReader != null && !dataReader.IsClosed)
{
while (dataReader.Read())
{
ids.Add(dataReader["UserId"].ToString());
}
dataReader.Close();
}
result = ids.ToArray();
}
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);
}
string GetSqlQueryByRole(string systemCode, string[] roleIds)
{
var tableNameRoleOrganization = systemCode + "RoleOrganization";
var sql = "SELECT * FROM " + BaseOrganizationEntity.CurrentTableName
+ " WHERE " + BaseOrganizationEntity.FieldEnabled + " = 1 "
+ " AND " + BaseOrganizationEntity.FieldDeleted + "= 0 "
+ " AND ( " + BaseOrganizationEntity.FieldId + " IN "
+ " (SELECT " + BaseRoleOrganizationEntity.FieldOrganizationId
+ " FROM " + tableNameRoleOrganization
+ " WHERE " + BaseRoleOrganizationEntity.FieldRoleId + " IN (" + StringUtil.ArrayToList(roleIds) + ")"
+ " AND " + BaseRoleOrganizationEntity.FieldEnabled + " = 1"
+ " AND " + BaseRoleOrganizationEntity.FieldDeleted + " = 0)) "
+ " ORDER BY " + BaseOrganizationEntity.FieldSortCode;
return(sql);
}
private string GetSearchConditional(string systemCode, string permissionCode, string where, string[] roleIds, bool?enabled, string auditStates, string companyId = null, string departmentId = null, bool onlyOnLine = false)
{
string whereClause = BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldIsVisible + " = 1 ";
if (enabled.HasValue)
{
if (enabled == true)
{
whereClause += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = 1 ";
}
else
{
whereClause += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = 0 ";
}
}
if (onlyOnLine)
{
whereClause += " AND " + BaseUserEntity.TableName + ".Id IN (SELECT Id FROM " + BaseUserLogOnEntity.TableName + " WHERE UserOnLine = 1) ";
}
if (!String.IsNullOrEmpty(where))
{
// 传递过来的表达式，还是搜索值？
if (where.IndexOf("AND") < 0 && where.IndexOf("=") < 0)
{
where = StringUtil.GetSearchString(where);
whereClause += " AND ("
+ BaseUserEntity.TableName + "." + BaseUserEntity.FieldUserName + " LIKE '" + where + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSimpleSpelling + " LIKE '" + where + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCode + " LIKE '" + where + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRealName + " LIKE '" + where + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldQuickQuery + " LIKE '" + where + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyName + " LIKE '" + where + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentName + " LIKE '" + where + "'"
// + " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDescription + " LIKE '" + search + "'"
+ ")";
}
else
{
whereClause += " AND (" + where + ")";
}
}
if (!string.IsNullOrEmpty(departmentId))
{
/*
* BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.DbHelper, 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.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(ids) + "))";
+ }
*/
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " = '" + departmentId + "')";
}
if (!string.IsNullOrEmpty(companyId))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = '" + companyId + "')";
}
if (!String.IsNullOrEmpty(auditStates))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldAuditStatus + " = '" + auditStates + "'";
// 待审核
if (auditStates.Equals(AuditStatus.WaitForAudit.ToString()))
{
whereClause += " OR " + BaseUserEntity.TableName + ".Id IN ( SELECT Id FROM " + BaseUserLogOnEntity.TableName + " WHERE LockEndDate > " + dbHelper.GetDbNow() + ") ";
}
whereClause += ")";
}
if (enabled != null)
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = " + ((bool)enabled ? 1 : 0) + ")";
}
if ((roleIds != null) && (roleIds.Length > 0))
{
string roles = StringUtil.ArrayToList(roleIds, "'");
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN (" + "SELECT " + BaseUserRoleEntity.FieldUserId + " FROM " + BaseUserRoleEntity.TableName + " WHERE " + BaseUserRoleEntity.FieldRoleId + " IN (" + roles + ")" + "))";
}
// 是否过滤用户， 获得组织机构列表， 这里需要一个按用户过滤得功能
if (!string.IsNullOrEmpty(permissionCode) &&
(!UserInfo.IsAdministrator) &&
(BaseSystemInfo.UsePermissionScope))
{
// string permissionCode = "Resource.ManagePermission";
string permissionId = BaseModuleManager.GetIdByCodeByCache(systemCode, permissionCode);
if (!string.IsNullOrEmpty(permissionId))
{
// 从小到大的顺序进行显示，防止错误发生
BaseUserScopeManager userPermissionScopeManager = new BaseUserScopeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = userPermissionScopeManager.GetOrganizeIds(UserInfo.SystemCode, this.UserInfo.Id, permissionId);
// 没有任何数据权限
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.NotAllowed).ToString()))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = NULL ) ";
}
// 按详细设定的数据
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.ByDetails).ToString()))
{
BasePermissionScopeManager permissionScopeManager = new BasePermissionScopeManager(DbHelper, UserInfo);
string[] userIds = permissionScopeManager.GetUserIds(UserInfo.SystemCode, UserInfo.Id, permissionCode);
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN (" + string.Join(",", userIds) + ")) ";
}
// 自己的数据，仅本人
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.OnlyOwnData).ToString()))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = " + this.UserInfo.Id + ") ";
}
// 用户所在工作组数据
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.UserWorkgroup).ToString()))
{
// whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " = " + this.UserInfo.WorkgroupId + ") ";
}
// 用户所在部门数据
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.UserDepartment).ToString()))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " = " + this.UserInfo.DepartmentId + ") ";
}
// 用户所在分支机构数据
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.UserSubCompany).ToString()))
{
// whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSubCompanyId + " = '" + this.UserInfo.SubCompanyId + "') ";
}
// 用户所在公司数据
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.UserCompany).ToString()))
{
whereClause += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = '" + this.UserInfo.CompanyId + "') ";
}
// 全部数据，这里就不用设置过滤条件了
if (StringUtil.Exists(organizeIds, ((int)PermissionOrganizeScope.AllData).ToString()))
{
}
}
}
return(whereClause);
}
/// <summary>
/// 获取已审核流程列表
/// </summary>
/// <param name="categoryId">分类主键</param>
/// <param name="categorybillFullName">流程</param>
/// <param name="searchValue">关键字</param>
/// <returns></returns>
public DataTable GetAuditRecord(string categoryCode, string categorybillFullName = null, string searchValue = null)
{
// 2013-01-27 同角色审核过的也要显示，这样知道同角色的人都审核过什么内容
string[] roleIds = new BaseUserManager(this.UserInfo).GetRoleIds(this.UserInfo.Id);
string sqlQuery = @" SELECT TOP 400 BaseWorkFlowCurrent.*
FROM BaseWorkFlowCurrent,
(SELECT CurrentFlowId AS CurrentFlowId, MAX(AuditDate) AS AuditDate
FROM BaseWorkFlowHistory
WHERE (BaseWorkFlowHistory.DeletionStateCode = 0)
AND (BaseWorkFlowHistory.AuditUserId = '" + this.UserInfo.Id + "'"
+ " OR " + BaseWorkFlowHistoryEntity.FieldAuditUserId + " LIKE '%" + this.UserInfo.Id + "%'"
+ " OR BaseWorkFlowHistory.ToUserId = '" + this.UserInfo.Id + "'"
+ " OR " + BaseWorkFlowHistoryEntity.FieldToUserId + " LIKE '%" + this.UserInfo.Id + "%'";
if (roleIds != null && roleIds.Length > 0)
{
sqlQuery += " OR BaseWorkFlowHistory.ToRoleId IN (" + StringUtil.ArrayToList(roleIds) + ") ";
}
sqlQuery += @" ) AND (BaseWorkFlowHistory.DeletionStateCode = 0)
GROUP BY CurrentFlowId
) AS BaseWorkFlowHistory
WHERE BaseWorkFlowCurrent.DeletionStateCode = 0
AND BaseWorkFlowCurrent.Id = BaseWorkFlowHistory.CurrentFlowId";
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
if (!string.IsNullOrEmpty(categoryCode))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
var dt = templateManager.Search(string.Empty, categoryCode, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dt, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryFullName ='" + categorybillFullName + "') ";
}
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
sqlQuery += " ORDER BY BaseWorkFlowHistory.AuditDate DESC ";
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
/// <summary>
/// 提交审核
/// </summary>
private void SendToBy()
{
// 返回值
string returnValue = string.Empty;
// 返回状态
string returnStatusCode = string.Empty;
// 执行步骤流
if (this.WorkFlowType.Equals("StepFlow"))
{
// 没有值的话重新获取一下
if (string.IsNullOrEmpty(this.WorkFlowId))
{
// 重新获取工作流主键
this.WorkFlowId = DotNetService.Instance.WorkFlowProcessAdminService.GetProcessId(this.UserInfo, this.WorkFlowCode);
}
// 获取步骤列表
DataTable dataTable = DotNetService.Instance.WorkFlowActivityAdminService.GetDataTable(this.UserInfo, this.WorkFlowId);
// 返回值
returnValue = DotNetService.Instance.WorkFlowCurrentService.AuditStatr(this.UserInfo, this.CategoryId, this.CategoryFullName, this.ObjectIds, StringUtil.ArrayToList(this.ObjectFullNames), this.WorkFlowCode, this.txtAuditIdea.Text, out returnStatusCode, dataTable);
}
// 执行自由流
else if (this.WorkFlowType.Equals("FreeFlow"))
{
// 检查选择的有效性
if (this.CheckInput())
{
// 提交自由流程
// returnValue = DotNetService.Instance.WorkFlowCurrentService.AuditFreeStart(this.UserInfo, this.CategoryId, this.CategoryFullName, this.ObjectIds, StringUtil.ArrayToList(this.ObjectFullNames), this.WorkFlowCode, this.txtAuditIdea.Text, this.WorkFlowCategory, this.SelectedIds[0], this.SelectedFullName, string.Empty, out returnStatusCode);
}
else
{
return;
}
}
// 提交成功
if (returnStatusCode == StatusCode.OK.ToString())
{
// 是否显示信息
if (BaseSystemInfo.ShowInformation)
{
MessageBox.Show(AppMessage.MSG0256, AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
// 提交失败
else
{
// 是否显示信息
if (BaseSystemInfo.ShowInformation)
{
MessageBox.Show(AppMessage.MSG0257, AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
public string[] GetUserIds(string[] roleIds)
{
string tableName = BaseUserRoleEntity.TableName;
if (!string.IsNullOrEmpty(BaseSystemInfo.SystemCode))
{
tableName = BaseSystemInfo.SystemCode + "UserRole";
}
string[] userIds = null;
if (roleIds != null && roleIds.Length > 0)
{
// 需要显示未被删除的用户
string sqlQuery = " SELECT Id AS UserId FROM BaseUser WHERE (RoleId IN ( " + StringUtil.ArrayToList(roleIds) + ")) AND (DeletionStateCode = 0) AND (Enabled = 1) "
+ " UNION SELECT UserId FROM " + tableName + " WHERE (RoleId IN (" + StringUtil.ArrayToList(roleIds) + ")) "
+ " AND (UserId IN (SELECT Id FROM BaseUser WHERE (DeletionStateCode = 0))) AND (DeletionStateCode = 0) ";
DataTable dataTable = DbHelper.Fill(sqlQuery);
userIds = BaseBusinessLogic.FieldToArray(dataTable, BaseUserRoleEntity.FieldUserId);
}
return(userIds);
}
/// <summary>
/// 获取用户主键数组
/// 2015-11-03 吉日嘎拉 优化程序、用ExecuteReader提高性能
/// </summary>
/// <param name="systemCode">系统编号</param>
/// <param name="roleIds">角色主键数组</param>
/// <returns>用户主键数组</returns>
public string[] GetUserIds(string systemCode, string[] roleIds)
{
string[] result = null;
if (roleIds != null && roleIds.Length > 0)
{
// 需要显示未被删除的用户
string tableName = systemCode + "UserRole";
string commandText = "SELECT UserId FROM " + tableName + " WHERE RoleId IN (" + StringUtil.ArrayToList(roleIds) + ") "
+ " AND (UserId IN (SELECT Id FROM " + BaseUserEntity.TableName + " WHERE DeletionStateCode = 0)) AND (DeletionStateCode = 0)";
List <string> ids = new List <string>();
using (IDataReader dr = DbHelper.ExecuteReader(commandText))
{
while (dr.Read())
{
ids.Add(dr["UserId"].ToString());
}
}
// 这里不需要有重复数据、用程序的方式把重复的数据去掉
// result = BaseBusinessLogic.FieldToArray(dt, BaseUserRoleEntity.FieldUserId).Distinct<string>().Where(t => !string.IsNullOrEmpty(t)).ToArray();
result = ids.ToArray();
}
return(result);
}
private string GetUserSql(string[] organizationIds, bool idOnly = false)
{
var field = idOnly ? BaseUserEntity.FieldId : "*";
var sql = "SELECT " + field
+ " FROM " + BaseUserEntity.CurrentTableName
// 从用户表里去找
+ " WHERE " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldDeleted + " = 0 "
+ " AND " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldEnabled + " = 1 "
+ " AND (" + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldWorkgroupId + " IN ( " + StringUtil.ArrayToList(organizationIds) + ") "
+ " OR " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizationIds) + ") "
+ " OR " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldSubCompanyId + " IN (" + StringUtil.ArrayToList(organizationIds) + ") "
+ " OR " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizationIds) + ")) "
// 从用户兼职表里去取用户
/*
+ " OR " + BaseUserEntity.FieldId + " IN ("
+ "SELECT " + BaseUserOrganizationEntity.FieldUserId
+ " FROM " + BaseUserOrganizationEntity.CurrentTableName
+ " WHERE (" + BaseUserOrganizationEntity.CurrentTableName + "." + BaseUserOrganizationEntity.FieldDeleted + " = 0 ) "
+ " AND (" + BaseUserOrganizationEntity.CurrentTableName + "." + BaseUserOrganizationEntity.FieldWorkgroupId + " IN ( " + StringUtil.ArrayToList(organizationIds) + ") "
+ " OR " + BaseUserOrganizationEntity.CurrentTableName + "." + BaseUserOrganizationEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizationIds) + ") "
+ " OR " + BaseUserOrganizationEntity.CurrentTableName + "." + BaseUserOrganizationEntity.FieldSubCompanyId + " IN (" + StringUtil.ArrayToList(organizationIds) + ") "
+ " OR " + BaseUserOrganizationEntity.CurrentTableName + "." + BaseUserOrganizationEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizationIds) + "))) "
*/
+ " ORDER BY " + BaseUserEntity.CurrentTableName + "." + BaseUserEntity.FieldSortCode;
return(sql);
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="userInfo"></param>
/// <param name="departmentId">部门编号</param>
/// <param name="searchValue">查询字段</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="sortExpression">排序字段</param>
/// <param name="sortDire">排序方向</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(BaseUserInfo userInfo, string departmentId, string searchValue, out int recordCount, int pageIndex = 1, int pageSize = 20, string sortExpression = null, string sortDire = null)
{
string whereConditional = BaseRoleEntity.FieldDeletionStateCode + " = 0 ";
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.DbHelper, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
whereConditional += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
}
if (!string.IsNullOrEmpty(searchValue))
{
searchValue = "'" + StringUtil.GetSearchString(searchValue) + "'";
whereConditional += " AND (" + BaseUserEntity.FieldRealName + " LIKE " + searchValue;
whereConditional += " OR " + BaseUserEntity.FieldUserName + " LIKE " + searchValue + ")";
}
return(GetDataTableByPage(out recordCount, pageIndex, pageSize, sortExpression, sortDire, this.CurrentTableName, whereConditional, "*"));
}
public DataTable Search(string permissionScopeItemCode, string search, string[] roleIds, bool?enabled, string auditStates, string departmentId)
{
search = StringUtil.GetSearchString(search);
string sqlQuery = " SELECT " + BaseUserEntity.TableName + ".* "
+ "," + BaseRoleEntity.TableName + "." + BaseRoleEntity.FieldRealName + " AS RoleName "
+ " FROM " + BaseUserEntity.TableName
+ " LEFT OUTER JOIN " + BaseRoleEntity.TableName
+ " ON " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRoleId + " = " + BaseRoleEntity.TableName + "." + BaseRoleEntity.FieldId
// 被删除的排出在外比较好一些
+ " WHERE " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldIsVisible + " = 1 ";
if (!String.IsNullOrEmpty(search))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldUserName + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCode + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRealName + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldQuickQuery + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentName + " LIKE '" + search + "'"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDescription + " LIKE '" + search + "')";
}
if (!string.IsNullOrEmpty(departmentId))
{
BaseOrganizeManager organizeManager = new BaseOrganizeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
}
if (!String.IsNullOrEmpty(auditStates))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldAuditStatus + " = '" + auditStates + "')";
}
if (enabled != null)
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = " + ((bool)enabled ? 1:0) + ")";
}
if ((roleIds != null) && (roleIds.Length > 0))
{
string roles = StringUtil.ArrayToList(roleIds, "'");
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldRoleId + " IN (" + roles + ") ";
sqlQuery += " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN (" + "SELECT " + BaseUserRoleEntity.FieldUserId + " FROM " + BaseUserRoleEntity.TableName + " WHERE " + BaseUserRoleEntity.FieldRoleId + " IN (" + roles + ")" + "))";
}
// 是否过滤用户， 获得组织机构列表， 这里需要一个按用户过滤得功能
if ((!UserInfo.IsAdministrator) && (BaseSystemInfo.UsePermissionScope))
{
// string permissionScopeItemCode = "Resource.ManagePermission";
BasePermissionItemManager permissionItemManager = new BasePermissionItemManager(this.DbHelper, this.UserInfo);
string permissionScopeItemId = permissionItemManager.GetId(new KeyValuePair <string, object>(BasePermissionItemEntity.FieldCode, permissionScopeItemCode));
if (!string.IsNullOrEmpty(permissionScopeItemId))
{
// 从小到大的顺序进行显示，防止错误发生
BaseUserScopeManager userPermissionScopeManager = new BaseUserScopeManager(this.DbHelper, this.UserInfo);
string[] organizeIds = userPermissionScopeManager.GetOrganizeIds(this.UserInfo.Id, permissionScopeItemId);
// 没有任何数据权限
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.None).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = NULL ) ";
}
// 按详细设定的数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.Detail).ToString()))
{
BasePermissionScopeManager permissionScopeManager = new BasePermissionScopeManager(DbHelper, UserInfo);
string[] userIds = permissionScopeManager.GetUserIds(UserInfo.Id, permissionScopeItemCode);
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " IN (" + BaseBusinessLogic.ObjectsToList(userIds) + ")) ";
}
// 自己的数据，仅本人
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.User).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldId + " = " + this.UserInfo.Id + ") ";
}
// 用户所在工作组数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserWorkgroup).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " = " + this.UserInfo.WorkgroupId + ") ";
}
// 用户所在部门数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserDepartment).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " = " + this.UserInfo.DepartmentId + ") ";
}
// 用户所在分支机构数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserSubCompany).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSubCompanyId + " = " + this.UserInfo.SubCompanyId + ") ";
}
// 用户所在公司数据
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.UserCompany).ToString()))
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " = " + this.UserInfo.CompanyId + ") ";
}
// 全部数据，这里就不用设置过滤条件了
if (StringUtil.Exists(organizeIds, ((int)PermissionScope.All).ToString()))
{
}
}
}
sqlQuery += " ORDER BY " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSortCode;
return(DbHelper.Fill(sqlQuery));
}
/// <summary>
/// 获取等审核信息
/// </summary>
/// <param name="userId">用户主键</param>
/// <param name="categoryCode">分类代码</param>
/// <param name="searchValue">查询字符串</param>
/// <param name="showAuditReject">显示退回的</param>
/// <returns>数据表</returns>
public DataTable GetWaitForAudit(string userId = null, string categoryCode = null, string categorybillFullName = null, string searchValue = null, bool showAuditReject = true)
{
if (string.IsNullOrEmpty(userId))
{
userId = this.UserInfo.Id;
}
string sqlQuery = " SELECT * "
+ " FROM " + BaseWorkFlowCurrentEntity.TableName
// 未被删除的，有效的数据，还没能审核结束的
+ " WHERE (" + BaseWorkFlowCurrentEntity.FieldDeletionStateCode + " = 0) "
// Enabled 0 表示，审核还没结束
+ " AND (" + BaseWorkFlowCurrentEntity.FieldEnabled + " = 0) ";
if (!showAuditReject)
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldAuditStatus + " != 'AuditReject') ";
}
if (!string.IsNullOrEmpty(userId))
{
// 待审核的工作流（指向用户的）
switch (BaseSystemInfo.UserCenterDbType)
{
case DbTypes.Access:
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "= '" + userId + "' ";
break;
default:
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldToUserId + "=" + userId + " ";
break;
}
//（指向角色的）
BaseUserManager userManager = new BaseUserManager(this.UserInfo);
string[] roleIds = userManager.GetAllRoleIds(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))
{
BaseWorkFlowBillTemplateManager templateManager = new BaseWorkFlowBillTemplateManager(this.DbHelper, this.UserInfo);
DataTable dataTable = templateManager.Search(string.Empty, categoryCode, string.Empty, null, false);
string categoryCodes = BaseBusinessLogic.FieldToList(dataTable, BaseWorkFlowBillTemplateEntity.FieldCode);
if (!string.IsNullOrEmpty(categoryCodes))
{
sqlQuery += " AND (BaseWorkFlowCurrent.CategoryCode IN (" + categoryCodes + ")) ";
}
}
if (!string.IsNullOrEmpty(categorybillFullName))
{
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.TableName + "." + BaseWorkFlowCurrentEntity.FieldCategoryFullName + " ='" + categorybillFullName + "') ";
}
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
if (!String.IsNullOrEmpty(searchValue))
{
searchValue = searchValue.Trim();
sqlQuery += " AND (" + BaseWorkFlowCurrentEntity.FieldObjectFullName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditIdea + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldAuditStatusName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToDepartmentName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName);
sqlQuery += " OR " + BaseWorkFlowCurrentEntity.FieldToUserRealName + " LIKE " + DbHelper.GetParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldObjectFullName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditUserRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditIdea, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldAuditStatusName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToDepartmentName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseWorkFlowCurrentEntity.FieldToUserRealName, searchValue));
}
// 排序字段
sqlQuery += " ORDER BY " + BaseWorkFlowCurrentEntity.FieldSendDate;
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
private string GetSearchConditional(string permissionCode, string condition, bool?enabled, string auditStates, string companyId = null, string departmentId = null)
{
var sb = BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldDeleted + " = 0 ";
if (enabled.HasValue)
{
if (enabled == true)
{
sb += " AND " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldEnabled + " = 1 ";
}
else
{
sb += " AND " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldEnabled + " = 0 ";
}
}
if (!string.IsNullOrEmpty(condition))
{
// 传递过来的表达式，还是搜索值？
if (condition.IndexOf("AND") < 0 && condition.IndexOf("=") < 0)
{
condition = StringUtil.GetSearchString(condition);
sb += " AND ("
+ BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldUserName + " LIKE '%" + condition + "%'"
// + " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldSimpleSpelling + " LIKE '%" + where + "%'"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldEmployeeNumber + " LIKE '%" + condition + "%'"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldRealName + " LIKE '%" + condition + "%'"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldQuickQuery + " LIKE '%" + condition + "%'"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldCompanyName + " LIKE '%" + condition + "%'"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldDepartmentName + " LIKE '%" + condition + "%'"
// + " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldDescription + " LIKE '%" + search + "%'"
+ ")";
}
else
{
sb += " AND (" + condition + ")";
}
}
if (!string.IsNullOrEmpty(departmentId))
{
/*
* BaseOrganizationManager organizationManager = new BaseOrganizationManager(this.DbHelper, this.UserInfo);
* string[] ids = organizationManager.GetChildrensId(BaseOrganizationEntity.FieldId, departmentId, BaseOrganizationEntity.FieldParentId);
* if (ids != null && ids.Length > 0)
* {
* condition += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(ids) + "))";
+ }
*/
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldDepartmentId + " = " + departmentId + ")";
}
if (!string.IsNullOrEmpty(companyId))
{
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldCompanyId + " = " + companyId + ")";
}
if (enabled != null)
{
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldEnabled + " = " + ((bool)enabled ? 1 : 0) + ")";
}
// 是否过滤用户， 获得组织机构列表， 这里需要一个按用户过滤得功能
if (!string.IsNullOrEmpty(permissionCode) && (!BaseUserManager.IsAdministrator(UserInfo.Id.ToString())) && (BaseSystemInfo.UsePermissionScope))
{
// string permissionCode = "Resource.ManagePermission";
var permissionId = new BaseModuleManager().GetIdByCodeByCache(UserInfo.SystemCode, permissionCode);
if (!string.IsNullOrEmpty(permissionId))
{
// 从小到大的顺序进行显示，防止错误发生
var userPermissionScopeManager = new BaseUserScopeManager(DbHelper, UserInfo);
var organizationIds = userPermissionScopeManager.GetOrganizationIds(UserInfo.SystemCode, UserInfo.Id.ToString(), permissionId);
// 没有任何数据权限
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.NotAllowed).ToString()))
{
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldId + " = NULL ) ";
}
// 按详细设定的数据
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.ByDetails).ToString()))
{
var permissionScopeManager = new BasePermissionScopeManager(DbHelper, UserInfo, CurrentTableName);;
var userIds = permissionScopeManager.GetUserIds(UserInfo.SystemCode, UserInfo.Id.ToString(), permissionCode);
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldId + " IN (" + StringUtil.ArrayToList(userIds) + ")) ";
}
// 自己的数据，仅本人
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.OnlyOwnData).ToString()))
{
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldId + " = " + UserInfo.Id + ") ";
}
// 用户所在工作组数据
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.UserWorkgroup).ToString()))
{
// condition += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldWorkgroupId + " = " + this.UserInfo.WorkgroupId + ") ";
}
// 用户所在部门数据
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.UserDepartment).ToString()))
{
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldDepartmentId + " = " + UserInfo.DepartmentId + ") ";
}
// 用户所在分支机构数据
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.UserSubCompany).ToString()))
{
// condition += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldSubCompanyId + " = " + this.UserInfo.SubCompanyId + ") ";
}
// 用户所在公司数据
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.UserCompany).ToString()))
{
sb += " AND (" + BaseStaffEntity.CurrentTableName + "." + BaseStaffEntity.FieldCompanyId + " = " + UserInfo.CompanyId + ") ";
}
// 全部数据，这里就不用设置过滤条件了
if (StringUtil.Exists(organizationIds, ((int)PermissionOrganizationScope.AllData).ToString()))
{
}
}
}
return(sb);
}
/// <summary>
/// 按区域主键获取组织机构主键数组
/// </summary>
/// <param name="areaIds">区域主键数组</param>
/// <returns>组织机构主键数组</returns>
public string[] GetIdsByAreaIds(string[] areaIds)
{
string[] result = null;
if (areaIds != null && areaIds.Length > 0)
{
string commandText = " SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldEnabled + " = 1 AND " + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 "
+ " AND (" + BaseOrganizeEntity.FieldProvinceId + " IN (" + StringUtil.ArrayToList(areaIds) + ")"
+ " OR " + BaseOrganizeEntity.FieldCityId + " IN (" + StringUtil.ArrayToList(areaIds) + ")"
+ " OR " + BaseOrganizeEntity.FieldStreetId + " IN (" + StringUtil.ArrayToList(areaIds) + ")"
+ " OR " + BaseOrganizeEntity.FieldDistrictId + " IN (" + StringUtil.ArrayToList(areaIds) + "))";
List <string> ids = new List <string>();
using (IDataReader dr = DbHelper.ExecuteReader(commandText))
{
while (dr.Read())
{
ids.Add(dr[BaseOrganizeEntity.FieldId].ToString());
}
}
result = ids.ToArray();
}
return(result);
}
/// <summary>
/// 按条件分页高级查询(带记录状态Enabled和删除状态Deleted)
/// </summary>
/// <param name="systemCode">系统编码</param>
/// <param name="categoryCode">分类编码</param>
/// <param name="companyId">查看公司主键</param>
/// <param name="departmentId">查看部门主键</param>
/// <param name="roleId">角色编号</param>
/// <param name="roleIdExcluded">排除的角色编号</param>
/// <param name="moduleId">模块编号</param>
/// <param name="moduleIdExcluded">排除的模块编号</param>
/// <param name="parentId">父节点编号</param>
/// <param name="startTime">创建开始时间</param>
/// <param name="endTime">创建结束时间</param>
/// <param name="searchKey">查询关键字</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="sortExpression">排序字段</param>
/// <param name="sortDirection">排序方向</param>
/// <param name="showDisabled">是否显示无效记录</param>
/// <param name="showDeleted">是否显示已删除记录</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(string systemCode, string categoryCode, string companyId, string departmentId, string roleId, string roleIdExcluded, string moduleId, string moduleIdExcluded, string parentId, string startTime, string endTime, string searchKey, out int recordCount, int pageNo = 1, int pageSize = 20, string sortExpression = "CreateTime", string sortDirection = "DESC", bool showDisabled = true, bool showDeleted = true)
{
var sb = Pool.StringBuilder.Get().Append(" 1 = 1");
//是否显示无效记录
if (!showDisabled)
{
sb.Append(" AND " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldEnabled + " = 1");
}
//是否显示已删除记录
if (!showDeleted)
{
sb.Append(" AND " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldDeleted + " = 0");
}
//分类
if (!string.IsNullOrEmpty(categoryCode))
{
sb.Append(" AND " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldCategoryCode + " = N'" + categoryCode + "'");
}
if (ValidateUtil.IsInt(companyId))
{
//只选择下一级
//sb.Append(" AND " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldDepartmentId
// + " IN ( SELECT " + BaseOrganizationEntity.FieldId
// + " FROM " + BaseOrganizationEntity.CurrentTableName
// + " WHERE " + BaseOrganizationEntity.FieldId + " = " + departmentId + " OR " + BaseOrganizationEntity.FieldParentId + " = " + departmentId + ")";
//所有下级的都列出来
var ids = GetChildrensId(BaseOrganizationEntity.FieldId, departmentId, BaseOrganizationEntity.FieldParentId);
if (ids != null && ids.Length > 0)
{
sb.Append(" AND (" + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " IN (" + StringUtil.ArrayToList(ids) + "))");
}
sb.Append(" AND (" + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldCompanyId + " = " + companyId + ")");
}
if (ValidateUtil.IsInt(departmentId))
{
sb.Append(" AND " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " = " + departmentId);
}
//是否显示已隐藏记录
//if (!showInvisible)
//{
// sb.Append(" AND " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldIsVisible + " = 1 ";
//}
//角色
var tableNameRoleOrganization = UserInfo.SystemCode + "RoleOrganization";
if (!string.IsNullOrEmpty(systemCode))
{
tableNameRoleOrganization = systemCode + "RoleOrganization";
}
//指定角色
if (!string.IsNullOrEmpty(roleId) && ValidateUtil.IsNumeric(roleId))
{
sb.Append(" AND ( " + BaseOrganizationEntity.FieldId + " IN ");
sb.Append(" (SELECT DISTINCT " + BaseRoleOrganizationEntity.FieldOrganizationId);
sb.Append(" FROM " + tableNameRoleOrganization);
sb.Append(" WHERE " + BaseRoleOrganizationEntity.FieldRoleId + " = '" + roleId + "'");
sb.Append(" AND " + BaseRoleOrganizationEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BaseRoleOrganizationEntity.FieldDeleted + " = 0)) ");
}
//排除指定角色
if (!string.IsNullOrEmpty(roleIdExcluded) && ValidateUtil.IsNumeric(roleIdExcluded))
{
sb.Append(" AND ( " + BaseOrganizationEntity.FieldId + " NOT IN ");
sb.Append(" (SELECT DISTINCT " + BaseRoleOrganizationEntity.FieldOrganizationId);
sb.Append(" FROM " + tableNameRoleOrganization);
sb.Append(" WHERE " + BaseRoleOrganizationEntity.FieldRoleId + " = '" + roleIdExcluded + "'");
sb.Append(" AND " + BaseRoleOrganizationEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BaseRoleOrganizationEntity.FieldDeleted + " = 0)) ");
}
//用户菜单模块表
var tableNamePermission = UserInfo.SystemCode + "Permission";
if (!string.IsNullOrEmpty(systemCode))
{
tableNamePermission = systemCode + "Permission";
}
//指定的菜单模块
if (!string.IsNullOrEmpty(moduleId) && ValidateUtil.IsNumeric(moduleId))
{
sb.Append(" AND ( " + BaseOrganizationEntity.FieldId + " IN ");
sb.Append(" (SELECT DISTINCT " + BasePermissionEntity.FieldResourceId);
sb.Append(" FROM " + tableNamePermission);
sb.Append(" WHERE " + BasePermissionEntity.FieldPermissionId + " = '" + moduleId + "'");
sb.Append(" AND " + BasePermissionEntity.FieldResourceCategory + " = '" + BaseOrganizationEntity.CurrentTableName + "' ");
sb.Append(" AND " + BasePermissionEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BasePermissionEntity.FieldDeleted + " = 0)) ");
}
//排除指定菜单模块
if (!string.IsNullOrEmpty(moduleIdExcluded) && ValidateUtil.IsNumeric(moduleIdExcluded))
{
sb.Append(" AND ( " + BaseOrganizationEntity.FieldId + " NOT IN ");
sb.Append(" (SELECT DISTINCT " + BasePermissionEntity.FieldResourceId);
sb.Append(" FROM " + tableNamePermission);
sb.Append(" WHERE " + BasePermissionEntity.FieldPermissionId + " = '" + moduleIdExcluded + "'");
sb.Append(" AND " + BasePermissionEntity.FieldResourceCategory + " = '" + BaseOrganizationEntity.CurrentTableName + "' ");
sb.Append(" AND " + BasePermissionEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BasePermissionEntity.FieldDeleted + " = 0)) ");
}
//父级编号
if (!string.IsNullOrEmpty(parentId) && ValidateUtil.IsNumeric(parentId))
{
sb.Append(" AND ( ");
//本级
sb.Append(BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " = " + parentId);
//下级
sb.Append(" OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldParentId + " = " + parentId);
//下下级
sb.Append(" OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldParentId + " IN ");
sb.Append(" (SELECT " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " FROM " + BaseOrganizationEntity.CurrentTableName + " WHERE " + BaseOrganizationEntity.CurrentTableName + "." + BaseModuleEntity.FieldParentId + " = " + parentId + ") ");
//下下下级
sb.Append(" OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldParentId + " IN ");
sb.Append(" (SELECT " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " FROM " + BaseOrganizationEntity.CurrentTableName + " WHERE " + BaseOrganizationEntity.CurrentTableName + "." + BaseModuleEntity.FieldParentId + " IN ");
sb.Append(" (SELECT " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " FROM " + BaseOrganizationEntity.CurrentTableName + " WHERE " + BaseOrganizationEntity.CurrentTableName + "." + BaseModuleEntity.FieldParentId + " = " + parentId + ") ");
sb.Append(" ) ");
//下下下下级，做个组织机构实际应用应该足够了
sb.Append(" OR " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldParentId + " IN ");
sb.Append(" (SELECT " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " FROM " + BaseOrganizationEntity.CurrentTableName + " WHERE " + BaseOrganizationEntity.CurrentTableName + "." + BaseModuleEntity.FieldParentId + " IN ");
sb.Append(" (SELECT " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " FROM " + BaseOrganizationEntity.CurrentTableName + " WHERE " + BaseOrganizationEntity.CurrentTableName + "." + BaseModuleEntity.FieldParentId + " IN ");
sb.Append(" (SELECT " + BaseOrganizationEntity.CurrentTableName + "." + BaseOrganizationEntity.FieldId + " FROM " + BaseOrganizationEntity.CurrentTableName + " WHERE " + BaseOrganizationEntity.CurrentTableName + "." + BaseModuleEntity.FieldParentId + " = " + parentId + ") ");
sb.Append(" ) ");
sb.Append(" ) ");
//闭合
sb.Append(" ) ");
}
//创建时间
if (ValidateUtil.IsDateTime(startTime))
{
sb.Append(" AND " + BaseOrganizationEntity.FieldCreateTime + " >= '" + startTime + "'");
}
if (ValidateUtil.IsDateTime(endTime))
{
sb.Append(" AND " + BaseOrganizationEntity.FieldCreateTime + " <= DATEADD(s,-1,DATEADD(d,1,'" + endTime + "'))");
}
//关键词
if (!string.IsNullOrEmpty(searchKey))
{
searchKey = StringUtil.GetLikeSearchKey(dbHelper.SqlSafe(searchKey));
sb.Append(" AND (" + BaseOrganizationEntity.FieldName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseOrganizationEntity.FieldCode + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseOrganizationEntity.FieldShortName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseOrganizationEntity.FieldStandardName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseOrganizationEntity.FieldDescription + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseOrganizationEntity.FieldQuickQuery + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseOrganizationEntity.FieldSimpleSpelling + " LIKE N'%" + searchKey + "%')");
}
sb.Replace(" 1 = 1 AND ", "");
return(GetDataTableByPage(out recordCount, pageNo, pageSize, sortExpression, sortDirection, CurrentTableName, sb.Put(), null, "*"));
}
/// <summary>
/// 按部门获取部门用户,包括子部门的用户
/// </summary>
/// <param name="departmentId">部门主键</param>
/// <returns>数据表</returns>
public DataTable SearchByDepartment(string departmentId, string searchValue)
{
string sqlQuery = " SELECT " + BaseUserEntity.TableName + ".* "
+ " FROM " + BaseUserEntity.TableName;
sqlQuery += " WHERE (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDeletionStateCode + " = 0 ";
sqlQuery += " AND " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldEnabled + " = 1 ) ";
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.DbHelper, this.UserInfo);
string[] organizeIds = organizeManager.GetChildrensId(BaseOrganizeEntity.FieldId, departmentId, BaseOrganizeEntity.FieldParentId);
if (organizeIds != null && organizeIds.Length > 0)
{
sqlQuery += " AND (" + BaseUserEntity.TableName + "." + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(organizeIds) + ")"
+ " OR " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(organizeIds) + "))";
}
}
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
searchValue = searchValue.Trim();
if (!String.IsNullOrEmpty(searchValue))
{
sqlQuery += " AND (" + BaseUserEntity.FieldUserName + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldUserName);
sqlQuery += " OR " + BaseUserEntity.FieldCode + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldCode);
sqlQuery += " OR " + BaseUserEntity.FieldRealName + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldRealName);
sqlQuery += " OR " + BaseUserEntity.FieldDepartmentName + " LIKE " + DbHelper.GetParameter(BaseUserEntity.FieldDepartmentName) + ")";
if (searchValue.IndexOf("%") < 0)
{
searchValue = "%" + searchValue + "%";
}
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldUserName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldCode, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldRealName, searchValue));
dbParameters.Add(DbHelper.MakeParameter(BaseUserEntity.FieldDepartmentName, searchValue));
}
sqlQuery += " ORDER BY " + BaseUserEntity.TableName + "." + BaseUserEntity.FieldSortCode;
return(DbHelper.Fill(sqlQuery, dbParameters.ToArray()));
}
private bool CheckUserOrganizationPermission(string systemCode, string userId, string permissionId, string[] organizationIds)
{
var result = false;
var errorMark = 0;
if (!ValidateUtil.IsInt(userId))
{
return(false);
}
if (string.IsNullOrEmpty(permissionId))
{
return(false);
}
if (organizationIds == null || organizationIds.Length == 0)
{
return(false);
}
if (string.IsNullOrWhiteSpace(systemCode))
{
if (UserInfo != null && !UserInfo.SystemCode.IsNullOrEmpty() && !UserInfo.SystemCode.IsNullOrWhiteSpace())
{
systemCode = UserInfo.SystemCode;
if (!BaseSystemInfo.SystemCode.IsNullOrEmpty() && !BaseSystemInfo.SystemCode.Equals(systemCode))
{
systemCode = BaseSystemInfo.SystemCode;
}
}
}
var tableName = systemCode + "Permission";
var sql = "SELECT COUNT(*) "
+ " FROM " + tableName
+ " WHERE " + BasePermissionEntity.FieldResourceCategory + " = " + DbHelper.GetParameter(BasePermissionEntity.FieldResourceCategory)
+ " AND " + BasePermissionEntity.FieldResourceId + " IN (" + StringUtil.ArrayToList(organizationIds) + ") "
+ " AND " + BasePermissionEntity.FieldPermissionId + " = " + DbHelper.GetParameter(BasePermissionEntity.FieldPermissionId)
+ " AND " + BasePermissionEntity.FieldEnabled + " = " + DbHelper.GetParameter(BasePermissionEntity.FieldEnabled)
+ " AND " + BasePermissionEntity.FieldDeleted + " = " + DbHelper.GetParameter(BasePermissionEntity.FieldDeleted);
var rowCount = 0;
var dbParameters = new List <IDbDataParameter>
{
DbHelper.MakeParameter(BasePermissionEntity.FieldResourceCategory, BaseOrganizationEntity.CurrentTableName),
DbHelper.MakeParameter(BasePermissionEntity.FieldPermissionId, permissionId),
DbHelper.MakeParameter(BasePermissionEntity.FieldEnabled, 1),
DbHelper.MakeParameter(BasePermissionEntity.FieldDeleted, 0)
};
try
{
errorMark = 1;
var returnObject = DbHelper.ExecuteScalar(sql, dbParameters.ToArray());
if (returnObject != null)
{
rowCount = int.Parse(returnObject.ToString());
}
result = rowCount > 0;
}
catch (Exception ex)
{
var writeMessage = "BasePermissionManager.CheckUserOrganizationPermission:发生时间:" + DateTime.Now
+ Environment.NewLine + "errorMark = " + errorMark
+ Environment.NewLine + "Message:" + ex.Message
+ Environment.NewLine + "Source:" + ex.Source
+ Environment.NewLine + "StackTrace:" + ex.StackTrace
+ Environment.NewLine + "TargetSite:" + ex.TargetSite
+ Environment.NewLine;
LogUtil.WriteLog(writeMessage, "Exception");
}
return(result);
}
/// <summary>
/// 按条件分页高级查询(带记录状态Enabled和删除状态Deleted)
/// </summary>
/// <param name="systemCode">系统编码</param>
/// <param name="organizationId">查看公司主键</param>
/// <param name="userId">查看用户主键</param>
/// <param name="roleId">角色编号</param>
/// <param name="roleIdExcluded">排除角色编号</param>
/// <param name="moduleId">模块菜单编号</param>
/// <param name="moduleIdExcluded">排除模块菜单编号</param>
/// <param name="showInvisible">是否显示隐藏</param>
/// <param name="disabledUserOnly">仅显示禁用</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="searchKey">查询关键字</param>
/// <param name="recordCount">记录数</param>
/// <param name="pageNo">当前页</param>
/// <param name="pageSize">每页显示</param>
/// <param name="sortExpression">排序字段</param>
/// <param name="sortDirection">排序方向</param>
/// <param name="showDisabled">是否显示无效记录</param>
/// <param name="showDeleted">是否显示已删除记录</param>
/// <returns>数据表</returns>
public DataTable GetDataTableByPage(string systemCode, string organizationId, string userId, string roleId, string roleIdExcluded, string moduleId, string moduleIdExcluded, bool showInvisible, bool disabledUserOnly, string startTime, string endTime, string searchKey, out int recordCount, int pageNo = 1, int pageSize = 20, string sortExpression = "CreateTime", string sortDirection = "DESC", bool showDisabled = true, bool showDeleted = true)
{
//用户表名
var tableNameUser = BaseUserEntity.CurrentTableName;
//用户登录表名
var tableNameUserLogon = BaseUserLogonEntity.CurrentTableName;
var sb = Pool.StringBuilder.Get().Append(" 1 = 1");
//只显示已锁定用户
if (disabledUserOnly)
{
sb.Append(" AND " + BaseUserEntity.FieldEnabled + " = 0 ");
//已锁定
showDisabled = true;
//未删除
showDeleted = false;
}
//是否显示无效记录
if (!showDisabled)
{
sb.Append(" AND " + BaseUserEntity.FieldEnabled + " = 1 ");
}
//是否显示已删除记录
if (!showDeleted)
{
sb.Append(" AND " + BaseUserEntity.FieldDeleted + " = 0 ");
}
if (ValidateUtil.IsInt(organizationId) && int.Parse(organizationId) > 0)
{
//只选择当前
//sb.Append(" AND (" + BaseUserEntity.FieldCompanyId + " = " + organizationId + ")";
//只选择当前和下一级
//sb.Append(" AND " + BaseUserEntity.FieldDepartmentId
// + " IN ( SELECT " + BaseOrganizationEntity.FieldId
// + " FROM " + BaseOrganizationEntity.CurrentTableName
// + " WHERE " + BaseOrganizationEntity.FieldId + " = " + organizationId + " OR " + BaseOrganizationEntity.FieldParentId + " = " + organizationId + ")";
//所有下级的都列出来
var organizationManager = new BaseOrganizationManager(UserInfo);
var ids = organizationManager.GetChildrensId(BaseOrganizationEntity.FieldId, organizationId, BaseOrganizationEntity.FieldParentId);
if (ids != null && ids.Length > 0)
{
sb.Append(" AND (" + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.FieldSubCompanyId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.FieldSubDepartmentId + " IN (" + StringUtil.ArrayToList(ids) + ")"
+ " OR " + BaseUserEntity.FieldWorkgroupId + " IN (" + StringUtil.ArrayToList(ids) + "))");
}
}
//if (ValidateUtil.IsInt(departmentId))
//{
// sb.Append(" AND " + BaseUserEntity.FieldDepartmentId + " = " + departmentId;
//}
if (ValidateUtil.IsInt(userId))
{
// sb.Append(" AND UserId = " + userId);
}
//是否显示已隐藏记录
if (!showInvisible)
{
sb.Append(" AND " + BaseUserEntity.FieldIsVisible + " = 1 ");
}
//角色
var tableNameUserRole = UserInfo.SystemCode + "UserRole";
if (!string.IsNullOrEmpty(systemCode))
{
tableNameUserRole = systemCode + "UserRole";
}
//指定角色
if (ValidateUtil.IsInt(roleId))
{
sb.Append(" AND ( " + BaseUserEntity.FieldId + " IN ");
sb.Append(" (SELECT DISTINCT " + BaseUserRoleEntity.FieldUserId);
sb.Append(" FROM " + tableNameUserRole);
sb.Append(" WHERE " + BaseUserRoleEntity.FieldRoleId + " = '" + roleId + "'");
sb.Append(" AND " + BaseUserRoleEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BaseUserRoleEntity.FieldDeleted + " = 0)) ");
}
//排除指定角色
if (ValidateUtil.IsInt(roleIdExcluded))
{
sb.Append(" AND ( " + BaseUserEntity.FieldId + " NOT IN ");
sb.Append(" (SELECT DISTINCT " + BaseUserRoleEntity.FieldUserId);
sb.Append(" FROM " + tableNameUserRole);
sb.Append(" WHERE " + BaseUserRoleEntity.FieldRoleId + " = '" + roleIdExcluded + "'");
sb.Append(" AND " + BaseUserRoleEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BaseUserRoleEntity.FieldDeleted + " = 0)) ");
}
//用户菜单模块表
var tableNamePermission = UserInfo.SystemCode + "Permission";
if (!string.IsNullOrEmpty(systemCode))
{
tableNamePermission = systemCode + "Permission";
}
//指定的菜单模块
if (ValidateUtil.IsInt(moduleId))
{
sb.Append(" AND ( " + BaseUserEntity.FieldId + " IN ");
sb.Append(" (SELECT DISTINCT " + BasePermissionEntity.FieldResourceId);
sb.Append(" FROM " + tableNamePermission);
sb.Append(" WHERE " + BasePermissionEntity.FieldPermissionId + " = '" + moduleId + "'");
sb.Append(" AND " + BasePermissionEntity.FieldResourceCategory + " = '" + BaseUserEntity.CurrentTableName + "' ");
sb.Append(" AND " + BasePermissionEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BasePermissionEntity.FieldDeleted + " = 0)) ");
}
//排除指定菜单模块
if (ValidateUtil.IsInt(moduleIdExcluded))
{
sb.Append(" AND ( " + BaseUserEntity.FieldId + " NOT IN ");
sb.Append(" (SELECT DISTINCT " + BasePermissionEntity.FieldResourceId);
sb.Append(" FROM " + tableNamePermission);
sb.Append(" WHERE " + BasePermissionEntity.FieldPermissionId + " = '" + moduleIdExcluded + "'");
sb.Append(" AND " + BasePermissionEntity.FieldResourceCategory + " = '" + BaseUserEntity.CurrentTableName + "' ");
sb.Append(" AND " + BasePermissionEntity.FieldEnabled + " = 1");
sb.Append(" AND " + BasePermissionEntity.FieldDeleted + " = 0)) ");
}
//关键词
if (!string.IsNullOrEmpty(searchKey))
{
searchKey = StringUtil.GetLikeSearchKey(dbHelper.SqlSafe(searchKey));
sb.Append(" AND (" + BaseUserEntity.FieldRealName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldUserName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldNickName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldCompanyName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldSubCompanyName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldDepartmentName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldSubDepartmentName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldWorkgroupName + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldCode + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldDescription + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldQuickQuery + " LIKE N'%" + searchKey + "%'");
sb.Append(" OR " + BaseUserEntity.FieldSimpleSpelling + " LIKE N'%" + searchKey + "%')");
}
if (ValidateUtil.IsDateTime(startTime))
{
sb.Append(" AND " + BaseUserEntity.FieldCreateTime + " >= '" + startTime + "'");
}
if (ValidateUtil.IsDateTime(endTime))
{
sb.Append(" AND " + BaseUserEntity.FieldCreateTime + " <= DATEADD(s,-1,DATEADD(d,1,'" + endTime + "'))");
}
sb.Replace(" 1 = 1 AND ", "");
//重新构造viewName
var viewName = string.Empty;
viewName = "SELECT DISTINCT " + tableNameUser + "." + BaseUserEntity.FieldId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUserFrom;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUserName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldRealName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldNickName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCode;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIdCard;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldQuickQuery;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSimpleSpelling;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCompanyId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCompanyName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubCompanyId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubCompanyName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDepartmentId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDepartmentName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubDepartmentId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubDepartmentName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkgroupId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkgroupName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkCategory;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSecurityLevel;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldTitle;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDuty;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldLang;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldGender;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldBirthday;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldScore;
//viewName += "," + tableNameUser + "." + BaseUserEntity.FieldFans;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldHomeAddress;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSignature;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldTheme;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsStaff;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsVisible;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldProvince;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCity;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDistrict;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldAuditStatus;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldEnabled;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDeleted;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSortCode;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDescription;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsAdministrator;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsCheckBalance;
//用户表
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCreateTime;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCreateUserId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCreateBy;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUpdateTime;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUpdateUserId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUpdateBy;
//用户登录表
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldAllowStartTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldAllowEndTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLockStartTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLockEndTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldFirstVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldPreviousVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLastVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldChangePasswordTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLogonCount;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldConcurrentUser;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUserOnline;
//不从用户登录表读取这些字段，从用户表读取即可
//viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldCreateTime;
//viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldCreateUserId;
//viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldCreateBy;
//viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUpdateTime;
//viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUpdateUserId;
//viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUpdateBy;
viewName += " FROM " + tableNameUser + " INNER JOIN " + tableNameUserLogon;
viewName += " ON " + tableNameUser + "." + BaseUserEntity.FieldId + " = " + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUserId;
//指定角色，就读取相应的UserRole授权日期
if (ValidateUtil.IsInt(roleId))
{
viewName = "SELECT DISTINCT " + tableNameUser + "." + BaseUserEntity.FieldId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUserFrom;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUserName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldRealName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldNickName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCode;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIdCard;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldQuickQuery;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSimpleSpelling;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCompanyId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCompanyName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubCompanyId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubCompanyName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDepartmentId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDepartmentName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubDepartmentId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubDepartmentName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkgroupId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkgroupName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkCategory;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSecurityLevel;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldTitle;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDuty;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldLang;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldGender;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldBirthday;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldScore;
//viewName += "," + tableNameUser + "." + BaseUserEntity.FieldFans;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldHomeAddress;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSignature;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldTheme;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsStaff;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsVisible;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldProvince;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCity;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDistrict;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldAuditStatus;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldEnabled;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDeleted;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSortCode;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDescription;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsAdministrator;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsCheckBalance;
//用户登录表
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldAllowStartTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldAllowEndTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLockStartTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLockEndTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldFirstVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldPreviousVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLastVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldChangePasswordTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLogonCount;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldConcurrentUser;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUserOnline;
//授权日期
viewName += "," + tableNameUserRole + "." + BaseUserRoleEntity.FieldCreateTime;
viewName += "," + tableNameUserRole + "." + BaseUserRoleEntity.FieldCreateUserId;
viewName += "," + tableNameUserRole + "." + BaseUserRoleEntity.FieldCreateBy;
viewName += "," + tableNameUserRole + "." + BaseUserRoleEntity.FieldUpdateTime;
viewName += "," + tableNameUserRole + "." + BaseUserRoleEntity.FieldUpdateUserId;
viewName += "," + tableNameUserRole + "." + BaseUserRoleEntity.FieldUpdateBy;
viewName += " FROM " + tableNameUser + " INNER JOIN " + tableNameUserRole;
viewName += " ON " + tableNameUser + "." + BaseUserEntity.FieldId + " = " + tableNameUserRole + "." + BaseUserRoleEntity.FieldUserId;
viewName += " INNER JOIN " + tableNameUserLogon;
viewName += " ON " + tableNameUser + "." + BaseUserEntity.FieldId + " = " + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUserId;
viewName += " WHERE (" + tableNameUserRole + "." + BaseUserRoleEntity.FieldRoleId + " = " + roleId + ")";
}
//指定菜单模块，就读取相应的Permission授权日期
else if (ValidateUtil.IsInt(moduleId))
{
viewName = "SELECT DISTINCT " + tableNameUser + "." + BaseUserEntity.FieldId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUserFrom;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldUserName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldRealName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldNickName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCode;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIdCard;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldQuickQuery;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSimpleSpelling;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCompanyId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCompanyName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubCompanyId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubCompanyName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDepartmentId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDepartmentName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubDepartmentId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSubDepartmentName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkgroupId;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkgroupName;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldWorkCategory;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSecurityLevel;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldTitle;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDuty;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldLang;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldGender;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldBirthday;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldScore;
//viewName += "," + tableNameUser + "." + BaseUserEntity.FieldFans;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldHomeAddress;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSignature;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldTheme;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsStaff;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsVisible;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldProvince;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldCity;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDistrict;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldAuditStatus;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldEnabled;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDeleted;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldSortCode;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldDescription;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsAdministrator;
viewName += "," + tableNameUser + "." + BaseUserEntity.FieldIsCheckBalance;
//用户登录表
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldAllowStartTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldAllowEndTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLockStartTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLockEndTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldFirstVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldPreviousVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLastVisitTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldChangePasswordTime;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldLogonCount;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldConcurrentUser;
viewName += "," + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUserOnline;
//授权日期
viewName += "," + tableNamePermission + "." + BasePermissionEntity.FieldCreateTime;
viewName += "," + tableNamePermission + "." + BasePermissionEntity.FieldCreateUserId;
viewName += "," + tableNamePermission + "." + BasePermissionEntity.FieldCreateBy;
viewName += "," + tableNamePermission + "." + BasePermissionEntity.FieldUpdateTime;
viewName += "," + tableNamePermission + "." + BasePermissionEntity.FieldUpdateUserId;
viewName += "," + tableNamePermission + "." + BasePermissionEntity.FieldUpdateBy;
viewName += " FROM " + tableNameUser + " INNER JOIN " + tableNamePermission;
viewName += " ON " + tableNameUser + "." + BaseUserEntity.FieldId + " = " + tableNamePermission + "." + BasePermissionEntity.FieldResourceId;
viewName += " INNER JOIN " + tableNameUserLogon;
viewName += " ON " + tableNameUser + "." + BaseUserEntity.FieldId + " = " + tableNameUserLogon + "." + BaseUserLogonEntity.FieldUserId;
viewName += " WHERE (" + tableNamePermission + "." + BasePermissionEntity.FieldResourceCategory + " = '" + tableNameUser + "')";
viewName += " AND (" + tableNamePermission + "." + BasePermissionEntity.FieldPermissionId + " = " + moduleId + ")";
}
//从视图读取
if (!string.IsNullOrEmpty(viewName))
{
tableNameUser = viewName;
}
return(GetDataTableByPage(out recordCount, pageNo, pageSize, sortExpression, sortDirection, tableNameUser, sb.Put(), null, "*"));
}
public int BroadcastProcess(string systemCode, bool allcompany, string[] roleIds, string[] areaIds, string[] companyIds, bool subCompany, string[] departmentIds, bool subDepartment, string[] userIds, string message, bool onlineOnly, MessageFunction functionCode = MessageFunction.SystemPush, DateTime?expireAt = null)
{
int result = 0;
// 1: 这里需要生成一个sql语句。
// 2: 只发给中天客户端，中天客户端有登录过的用户？
// 3: 一边读取一边执行发送指令，马上能见效。
// 4: 节约数据库资源，节约服务器资源。
// 5: 广播提醒，过期时间1周就可以了，太时间了，浪费内存资源。
// 2015-09-29 吉日嘎拉 从最高到最低的顺序判断
string commandText = " SELECT " + BaseUserEntity.FieldId
+ " FROM " + BaseUserEntity.TableName
+ " WHERE " + BaseUserEntity.FieldEnabled + " = 1 "
+ " AND " + BaseUserEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseUserEntity.FieldUserFrom + " = 'Base' ";
if (allcompany)
{
// 什么都不过滤
}
if (areaIds != null)
{
// 这个需要进行叠加处理
commandText += " AND " + BaseUserEntity.FieldCompanyId + " IN ( "
+ " SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldEnabled + " = 1 "
+ " AND " + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 "
+ " AND (" + BaseOrganizeEntity.FieldProvinceId + " IN (" + StringUtil.ArrayToList(areaIds) + ")"
+ " OR " + BaseOrganizeEntity.FieldCityId + " IN (" + StringUtil.ArrayToList(areaIds) + ")"
+ " OR " + BaseOrganizeEntity.FieldStreetId + " IN (" + StringUtil.ArrayToList(areaIds) + ")"
+ " OR " + BaseOrganizeEntity.FieldDistrictId + " IN (" + StringUtil.ArrayToList(areaIds) + ")) )";
}
if (subCompany)
{
commandText += " AND " + BaseUserEntity.FieldCompanyId + " IN ( "
+ " SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldEnabled + " = 1 "
+ "AND " + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 "
+ "AND " + BaseOrganizeEntity.FieldId + " IN (" + StringUtil.ArrayToList(companyIds) + ") UNION "
+ " SELECT " + BaseOrganizeEntity.FieldId
+ " FROM " + BaseOrganizeEntity.TableName
+ " WHERE " + BaseOrganizeEntity.FieldEnabled + " = 1 "
+ "AND " + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 "
+ " START WITH " + BaseOrganizeEntity.FieldParentId + " IN (" + StringUtil.ArrayToList(companyIds) + ") "
+ " CONNECT BY PRIOR " + BaseOrganizeEntity.FieldId + " = " + BaseOrganizeEntity.FieldParentId + ")";
}
if (companyIds != null && companyIds.Length > 0)
{
commandText += " AND " + BaseUserEntity.FieldCompanyId + " IN (" + StringUtil.ArrayToList(companyIds) + ")";
}
if (subDepartment)
{
commandText += " AND " + BaseUserEntity.FieldDepartmentId + " IN ( "
+ " SELECT " + BaseDepartmentEntity.FieldId
+ " FROM " + BaseDepartmentEntity.TableName
+ " WHERE " + BaseDepartmentEntity.FieldEnabled + " = 1 "
+ " AND " + BaseDepartmentEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 "
+ "AND " + BaseDepartmentEntity.FieldId + " IN (" + StringUtil.ArrayToList(departmentIds) + ") UNION "
+ " SELECT " + BaseDepartmentEntity.FieldId
+ " FROM " + BaseDepartmentEntity.TableName
+ " WHERE " + BaseDepartmentEntity.FieldEnabled + " = 1 "
+ " AND " + BaseDepartmentEntity.FieldDeletionStateCode + " = 0 "
+ " AND " + BaseOrganizeEntity.FieldDeletionStateCode + " = 0 "
+ " START WITH " + BaseDepartmentEntity.FieldParentId + " IN (" + StringUtil.ArrayToList(departmentIds) + ") "
+ " CONNECT BY PRIOR " + BaseDepartmentEntity.FieldId + " = " + BaseDepartmentEntity.FieldParentId + ") ";
}
if (departmentIds != null && departmentIds.Length > 0)
{
commandText += " AND " + BaseUserEntity.FieldDepartmentId + " IN (" + StringUtil.ArrayToList(departmentIds) + ")";
}
if (roleIds != null && roleIds.Length > 0)
{
string tableName = systemCode + "UserRole";
commandText += " AND " + BaseUserEntity.FieldId + " IN ( SELECT UserId FROM " + tableName + " WHERE RoleId IN (" + StringUtil.ArrayToList(roleIds) + "))";
}
if (userIds != null && userIds.Length > 0)
{
commandText += " AND " + BaseUserEntity.FieldId + " IN (" + StringUtil.ArrayToList(userIds) + ")";
}
using (var redisClient = PooledRedisHelper.GetMessageClient())
{
var userManager = new BaseUserManager(this.UserInfo);
using (IDataReader dataReader = userManager.ExecuteReader(commandText))
{
while (dataReader.Read())
{
string[] receiverIds = new string[] { dataReader[BaseUserEntity.FieldId].ToString() };
BaseMessageEntity entity = new BaseMessageEntity();
// entity.Id = BaseBusinessLogic.NewGuid();
entity.FunctionCode = functionCode.ToString();
// entity.FunctionCode = MessageFunction.SystemPush.ToString();
// entity.FunctionCode = MessageFunction.Remind.ToString();
entity.Contents = message;
entity.CreateCompanyId = this.UserInfo.CompanyId;
entity.CreateCompanyName = this.UserInfo.CompanyName;
entity.CreateDepartmentId = this.UserInfo.DepartmentId;
entity.CreateDepartmentName = this.UserInfo.DepartmentName;
entity.CreateUserId = this.UserInfo.Id;
entity.CreateBy = this.UserInfo.RealName;
entity.IPAddress = this.UserInfo.IPAddress;
entity.CreateOn = DateTime.Now;
entity.IsNew = 1;
entity.ReadCount = 0;
entity.DeletionStateCode = 0;
if (!expireAt.HasValue)
{
expireAt = DateTime.Now.AddDays(5);
}
result = Send(redisClient, entity, receiverIds, false, expireAt);
}
}
}
return(result);
}