/// <summary>
/// 解除符合解除管制标准的用户（永久管制的用户不会自动解除管制）
/// </summary>
/// <param name="noModeratedUserPoint">用户自动接触管制状态所需的积分（用户综合积分）</param>
/// <returns>被解除管制的用户集合</returns>
public IEnumerable <User> NoModeratedUsers(int noModeratedUserPoint)
{
if (noModeratedUserPoint <= 0)
{
return(new List <User>());
}
PointSettings pointSettings = DIContainer.Resolve <IPointSettingsManager>().Get();
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql_select = PetaPoco.Sql.Builder;
sql_select.Select("UserId")
.From("tn_Users")
.Where("IsForceModerated = @0 ", false)
.Where("IsModerated = @0", true);
sql_select.Where("ExperiencePoints * @0 + ReputationPoints * @1 > @2", pointSettings.ExperiencePointsCoefficient, pointSettings.ReputationPointsCoefficient, noModeratedUserPoint);
IEnumerable <object> userIds_object = dao.FetchFirstColumn(sql_select);
var sql_update = PetaPoco.Sql.Builder;
sql_update.Append("update tn_Users set IsModerated = @0 ", false)
.Where("IsForceModerated = @0 ", false)
.Where("IsModerated = @0", true)
.Where("ExperiencePoints * @0 + ReputationPoints * @1 > @2", pointSettings.ExperiencePointsCoefficient, pointSettings.ReputationPointsCoefficient, noModeratedUserPoint);
dao.Execute(sql_update);
dao.CloseSharedConnection();
return(PopulateEntitiesByEntityIds <long>(userIds_object.Cast <long>()));
}
/// <summary>
/// 删除动态源时删除动态
/// </summary>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="sourceId">动态源内容id</param>
public void DeleteSource(string tenantTypeId, long sourceId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder.Select("ActivityId").From("tn_Activities")
.Where("(TenantTypeId=@0 and sourceId=@1) or ( ReferenceTenantTypeId=@0 and ReferenceId=@1 )", tenantTypeId, sourceId);
IEnumerable <object> activityIds = dao.FetchFirstColumn(sql);
if (activityIds.Count() > 0)
{
IEnumerable <long> longActivityIds = activityIds.Cast <long>();
sql = Sql.Builder.Append("delete from tn_ActivityUserInbox").Where("ActivityId in (@ActivityIds)", new { ActivityIds = longActivityIds });
dao.Execute(sql);
sql = Sql.Builder.Append("delete from tn_ActivitySiteInbox").Where("ActivityId in (@ActivityIds)", new { ActivityIds = longActivityIds });
dao.Execute(sql);
foreach (var activityId in longActivityIds)
{
this.DeleteByEntityId(activityId);
}
}
dao.CloseSharedConnection();
}
/// <summary>
/// 获取类别的内容项集合
/// </summary>
/// <param name="categoryId">分类的Id集合</param>
/// <returns>内容项的ID集合</returns>
public IEnumerable <long> GetItemIds(long categoryId, IEnumerable <long> categorieIds)
{
PetaPocoDatabase dao = CreateDAO();
//获取缓存
StringBuilder categoriesIdsStringBuilder = new StringBuilder();
foreach (long id in categorieIds)
{
categoriesIdsStringBuilder.AppendFormat("-{0}", id);
}
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "CategoryId", categoryId));
cacheKey.AppendFormat("allCategoryIds-{0}", categoriesIdsStringBuilder.ToString());
IEnumerable <long> itemIds = null;
itemIds = cacheService.Get <IEnumerable <long> >(cacheKey.ToString());
if (itemIds == null)
{
var sql = PetaPoco.Sql.Builder;
sql.Select("ItemId").From("tn_ItemsInCategories").Where(" CategoryId in (@ids)", new { ids = categorieIds });
itemIds = dao.FetchFirstColumn(sql).Cast <long>();
cacheService.Add(cacheKey.ToString(), itemIds, CachingExpirationType.UsualObjectCollection);
}
return(itemIds);
}
/// <summary>
/// 创建提醒记录
/// </summary>
/// <param name="userId">被提醒用户Id</param>
/// <param name="reminderMode">提醒方式</param>
/// <param name="reminderInfoType">提醒信息类型</param>
/// <param name="objectIds">提醒对象Id集合</param>
public void CreateRecords(long userId, int reminderMode, int reminderInfoType, IEnumerable <long> objectIds)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = Sql.Builder;
sql.Select("ObjectId")
.From("tn_ReminderRecords")
.Where("UserId=@0", userId)
.Where("ReminderModeId=@0", reminderMode)
.Where("ReminderInfoTypeId=@0", reminderInfoType);
IEnumerable <object> oldObjectIds_object = dao.FetchFirstColumn(sql);
IEnumerable <long> oldObjectIds = oldObjectIds_object.Cast <long>();
foreach (var objectId in objectIds)
{
if (!oldObjectIds.Contains(objectId))
{
ReminderRecord record = ReminderRecord.New();
record.UserId = userId;
record.ReminderModeId = reminderMode;
record.ReminderInfoTypeId = reminderInfoType;
record.ObjectId = objectId;
dao.Insert(record);
}
}
dao.CloseSharedConnection();
//更新缓存
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 获取标签的所有内容项集合
/// </summary>
/// <param name="tagName">标签名称</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
/// <returns>返回指定的内容项Id集合</returns>
public IEnumerable <long> GetItemIds(string tagName, string tenantTypeId, long?ownerId)
{
PetaPocoDatabase dao = CreateDAO();
//获取缓存
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "TagName", tagName));
cacheKey.AppendFormat(":ItemIds-TenantTypeId:{0}-OwnerId:{0}", tenantTypeId, ownerId.HasValue ? ownerId.ToString() : string.Empty);
//组装sql语句
var sql = PetaPoco.Sql.Builder;
if (ownerId.HasValue && ownerId > 0)
{
sql.Select("ItemId")
.From("tn_ItemsInTags IT")
.InnerJoin("tn_TagsInOwners TIO")
.On("IT.TagInOwnerId = TIO.Id")
.Where("TIO.OwnerId = @0", ownerId);
if (!string.IsNullOrEmpty(tenantTypeId))
{
sql.Where("TIO.TenantTypeId = @0", tenantTypeId);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.Where("TIO.TagName = @0", tagName);
}
}
else
{
sql.Select("ItemId")
.From("tn_ItemsInTags");
if (!string.IsNullOrEmpty(tenantTypeId))
{
sql.Where("TenantTypeId = @0", tenantTypeId);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.Where("TagName = @0", tagName);
}
}
IEnumerable <long> itemIds = null;
itemIds = cacheService.Get <IEnumerable <long> >(cacheKey.ToString());
if (itemIds == null)
{
itemIds = dao.FetchFirstColumn(sql).Cast <long>();
cacheService.Add(cacheKey.ToString(), itemIds, CachingExpirationType.ObjectCollection);
}
return(itemIds);
}
/// <summary>
/// 获取敏感词(管理员后台用)
/// </summary>
/// <param name="keyword">带过滤文字关键字</param>
/// <param name="typeId">敏感词类型</param>
/// <returns>待过滤文字集合</returns>
public IEnumerable <SensitiveWord> GetSensitiveWords(string keyword, int?typeId)
{
PetaPocoDatabase dao = CreateDAO();
Sql sql = Sql.Builder;
sql.Select("*")
.From("tn_SensitiveWords");
if (typeId.HasValue && typeId.Value > 0)
{
sql.Where("TypeId = @0", typeId);
}
IEnumerable <object> ids = null;
if (string.IsNullOrEmpty(keyword))
{
//获取缓存Key
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.GlobalVersion));
cacheKey.AppendFormat("SensitiveWords:TypeId-{0}", typeId.ToString());
ids = cacheService.Get <IEnumerable <object> >(cacheKey.ToString());
if (ids == null)
{
ids = dao.FetchFirstColumn(sql);
cacheService.Add(cacheKey.ToString(), ids, CachingExpirationType.ObjectCollection);
}
}
else
{
sql.Where("Word like @0", "%" + keyword + "%");
ids = dao.FetchFirstColumn(sql);
}
return(PopulateEntitiesByEntityIds(ids));
}
/// <summary>
/// 清除用户所有私信会话
/// </summary>
/// <param name="userId">用户Id</param>
public void ClearSessionsFromUser(long userId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
List <Sql> sqls = new List <Sql>();
var sql = Sql.Builder;
sql.Select("SessionId")
.From("tn_MessageSessions")
.Where("UserId = @0", userId);
IEnumerable <object> sessionids = dao.FetchFirstColumn(sql);
if (sessionids.Count() > 0)
{
//清除私信实体脚本
sqls.Add(Sql.Builder.Append("delete from tn_Messages where exists(")
.Append("select 1 from tn_MessagesInSessions MIS where tn_Messages.MessageId = MIS.MessageId and exists(")
.Append("select 1 from tn_MessageSessions MS where MS.SessionId = MIS.SessionId and MS.UserId = @0 and not exists(", userId)
.Append("select 1 from tn_MessageSessions SUBMS Where SUBMS.UserId = MS.OtherUserId and SUBMS.OtherUserId = MS.UserId)))"));
//清除私信与会话关联脚本
sqls.Add(Sql.Builder.Append("delete from tn_MessagesInSessions where exists(")
.Append("select 1 from tn_MessageSessions MS where MS.SessionId = tn_MessagesInSessions.SessionId and MS.UserId = @0)", userId));
//清除私信会话
sqls.Add(Sql.Builder.Append("delete from tn_MessageSessions where UserId = @0", userId));
using (var transaction = dao.GetTransaction())
{
dao.Execute(sqls);
transaction.Complete();
}
}
dao.CloseSharedConnection();
#region 更新缓存
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
sessionids.ToList().ForEach((n) =>
{
RealTimeCacheHelper.IncreaseEntityCacheVersion(n);
});
#endregion
}
/// <summary>
/// 删除用户角色
/// </summary>
/// <param name="role">待删除的用户角色</param>
/// <returns>删除成功返回1，否则返回0</returns>
public override int Delete(Role role)
{
PetaPocoDatabase dao = CreateDAO();
if (role == null)
{
return(0);
}
var sql = Sql.Builder;
sql.Select("Id")
.From("tn_UsersInRoles")
.Where("RoleName = @0", role.RoleName);
IEnumerable <long> userInRoleIds = dao.FetchFirstColumn(sql).Cast <long>();
var sqlUserInRolesDelete = Sql.Builder;
var sqlRolesDelete = Sql.Builder;
int count;
sqlUserInRolesDelete.Append("Delete from tn_UsersInRoles where RoleName = @0", role.RoleName);
sqlRolesDelete.Append("Delete from tn_Roles where RoleName =@0", role.RoleName);
base.OnDeleted(Get(role.RoleName));
using (var scope = dao.GetTransaction())
{
dao.Execute(sqlUserInRolesDelete);
count = dao.Execute(sqlRolesDelete);
scope.Complete();
}
OnDeleted(role);
ICacheService cacheService = DIContainer.Resolve <ICacheService>();
Caching.RealTimeCacheHelper userInRoleCacheHelper = EntityData.ForType(typeof(UserInRole)).RealTimeCacheHelper;
foreach (var userInRoleId in userInRoleIds)
{
UserInRole userInRole = cacheService.Get <UserInRole>(userInRoleCacheHelper.GetCacheKeyOfEntity(userInRoleId));
if (userInRole != null)
{
userInRoleCacheHelper.MarkDeletion(userInRole);
}
}
return(count);
}
/// <summary>
/// 获取常用操作
/// </summary>
/// <param name="userId">用户Id</param>
/// <returns></returns>
public IEnumerable <InitialNavigation> GetCommonOperations(long userId)
{
Sql sql = Sql.Builder;
PetaPocoDatabase dao = CreateDAO();
sql.Select("tn_InitialNavigations.NavigationId")
.From("tn_InitialNavigations")
.InnerJoin("tn_CommonOperations").On("tn_CommonOperations.NavigationId=tn_InitialNavigations.NavigationId")
.Where("tn_InitialNavigations.PresentAreaKey=@0", PresentAreaKeysOfBuiltIn.ControlPanel)
.Where("tn_CommonOperations.UserId=@0", userId);
IEnumerable <object> ids = null;
ids = dao.FetchFirstColumn(sql);
return(PopulateEntitiesByEntityIds(ids));
}
/// <summary>
/// 功能搜索
/// </summary>
/// <param name="keyword">关键字</param>
/// <returns></returns>
public IEnumerable <InitialNavigation> SearchOperations(string keyword)
{
PetaPocoDatabase dao = CreateDAO();
Sql sql = Sql.Builder
.Select("*")
.From("tn_InitialNavigations")
.Where("PresentAreaKey=@0", PresentAreaKeysOfBuiltIn.ControlPanel);
if (!string.IsNullOrEmpty(keyword))
{
sql.Where("NavigationText like @0", "%" + keyword + "%");
}
IEnumerable <object> commonOperations = null;
commonOperations = dao.FetchFirstColumn(sql);
return(PopulateEntitiesByEntityIds(commonOperations));
}
/// <summary>
/// 删除标签与拥有者关系
/// </summary>
/// <param name="entity">标签与拥有者关系实体</param>
/// <returns>影响行数</returns>
public override int Delete(TagInOwner entity)
{
PetaPocoDatabase dao = CreateDAO();
IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>();
int affectCount = 0;
dao.OpenSharedConnection();
var sql_GetItemIds = PetaPoco.Sql.Builder;
sql_GetItemIds.Select("ItemId")
.From("tn_ItemsInTags")
.Where("TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName);
IEnumerable <object> itemIds = dao.FetchFirstColumn(sql_GetItemIds);
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInOwners where id=@0", entity.Id));
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_ItemsInTags where TagInOwnerId = @0 and TagName = @1", entity.Id, entity.TagName));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
OnDeleted(entity);
foreach (var itemId in itemIds)
{
EntityData.ForType(typeof(ItemInTag)).RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId);
}
EntityData.ForType(typeof(ItemInTag)).RealTimeCacheHelper.IncreaseAreaVersion("TagName", entity.TagName);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 批量更改处理状态
/// </summary>
/// <param name="userId">用户的id</param>
/// <param name="status">要更改的状态</param>
public void BatchSetStatus(long userId, InvitationStatus status)
{
PetaPocoDatabase dao = CreateDAO();
var sql = PetaPoco.Sql.Builder;
sql.Select("Id")
.From("tn_Invitations")
.Where("UserId=@0 and Status=@1", userId, (int)InvitationStatus.Unhandled);
dao.OpenSharedConnection();
IEnumerable <object> ids = dao.FetchFirstColumn(sql);
sql = PetaPoco.Sql.Builder;
sql.Append("update tn_Invitations set Status=@0 where UserId=@1 and Status =@2", (int)status, userId, (int)InvitationStatus.Unhandled);
dao.Execute(sql);
dao.CloseSharedConnection();
//维护缓存
string cacheKey = GetCacheKey_UnhandledInvitationCount(userId);
int? count = cacheService.Get(cacheKey) as int?;
if (count != null)
{
count = 0;
cacheService.Set(cacheKey, count, CachingExpirationType.SingleObject);
}
//更新列表缓存
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
//更新实体缓存
foreach (var id in ids)
{
Invitation invitation = Get(id);
if (invitation != null)
{
invitation.Status = status;
}
RealTimeCacheHelper.IncreaseEntityCacheVersion(id);
}
}
/// <summary>
/// 批量将所有未处理通知修改为已处理状态
/// </summary>
/// <param name="userId">接收人Id</param>
public void BatchSetIsHandled(long userId)
{
PetaPocoDatabase dao = CreateDAO();
var sql = PetaPoco.Sql.Builder;
sql.Select("Id").From("tn_Notices")
.Where("UserId=@0", userId)
.Where("Status=@0", (int)NoticeStatus.Unhandled);
IEnumerable <object> ids_object = dao.FetchFirstColumn(sql);
IEnumerable <long> ids = ids_object.Cast <long>();
sql = PetaPoco.Sql.Builder;
sql.Append("UPDATE tn_Notices Set Status=@0", (int)NoticeStatus.Handled)
.Where("UserId=@0", userId)
.Where("Status=@0", (int)NoticeStatus.Unhandled);
dao.Execute(sql);
//更新缓存
foreach (long id in ids)
{
Notice notice = Get(id);
if (notice != null)
{
notice.Status = NoticeStatus.Handled;
}
RealTimeCacheHelper.IncreaseEntityCacheVersion(id);
}
string cacheKey = GetCacheKey_UnhandledNoticeCount(userId);
int? count = cacheService.Get(cacheKey) as int?;
if (count != null)
{
count = 0;
cacheService.Set(cacheKey, count, CachingExpirationType.SingleObject);
}
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
