/// <summary> /// 用户批量更新提醒设置 /// </summary> /// <param name="userId">用户Id</param> /// <param name="userReminderSettings">用户提醒设置集合</param> public void BatchUpdateUserReminderSettings(long userId, IEnumerable <UserReminderSettings> userReminderSettings) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); bool hasSetting = false; var sql = PetaPoco.Sql.Builder; sql.Select("Id") .From("tn_UserReminderSettings") .Where("userId=@0", userId); List <long> ids = dao.Fetch <long>(sql); IEnumerable <UserReminderSettings> oldUserReminderSettings = PopulateEntitiesByEntityIds(ids); foreach (var userReminderSetting in userReminderSettings) { foreach (var oldUserReminderSetting in oldUserReminderSettings) { if (oldUserReminderSetting.ReminderInfoTypeId == userReminderSetting.ReminderInfoTypeId && oldUserReminderSetting.ReminderModeId == userReminderSetting.ReminderModeId) { hasSetting = true; if (oldUserReminderSetting.ReminderThreshold == userReminderSetting.ReminderThreshold && oldUserReminderSetting.IsEnabled == userReminderSetting.IsEnabled && oldUserReminderSetting.IsRepeated == userReminderSetting.IsRepeated && oldUserReminderSetting.RepeatInterval == userReminderSetting.RepeatInterval) { break; } else { oldUserReminderSetting.IsEnabled = userReminderSetting.IsEnabled; oldUserReminderSetting.IsRepeated = userReminderSetting.IsRepeated; oldUserReminderSetting.ReminderThreshold = userReminderSetting.ReminderThreshold; oldUserReminderSetting.RepeatInterval = userReminderSetting.RepeatInterval; dao.Update(oldUserReminderSetting); break; } } } if (!hasSetting) { dao.Insert(userReminderSetting); } hasSetting = false; } dao.CloseSharedConnection(); RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); }
/// <summary> /// 将动态加入到站点动态收件箱 /// </summary> /// <param name="activityId"></param> public void InsertSiteInbox(long activityId) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Sql sql = Sql.Builder.Select("ActivityId").From("tn_ActivitySiteInbox").Where("ActivityId=@0", activityId); IEnumerable <long> activityIds = dao.Fetch <long>(sql); if (activityIds == null || activityIds.Count() == 0) { sql = Sql.Builder.Append("insert tn_ActivitySiteInbox(ActivityId) values(@0)", activityId); dao.Execute(sql); } dao.CloseSharedConnection(); }
/// <summary> /// 将动态加入到用户动态收件箱 /// </summary> /// <param name="activityId"></param> /// <param name="userIds"></param> public void InsertUserInboxs(long activityId, IEnumerable <long> userIds) { if (userIds == null) { return; } var activity = Get(activityId); if (activity == null) { return; } PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Sql sql; List <long> activityIds = new List <long> { activityId }; if (activity.ReferenceId > 0) { sql = Sql.Builder.Select("ActivityId").From("tn_Activities").Where("TenantTypeId=@0", activity.ReferenceTenantTypeId).Where("SourceId=@0", activity.ReferenceId); activityIds.AddRange(dao.Fetch <long>(sql).Distinct()); } //若已存在,则不再添加 List <Sql> sqls = new List <Sql>(); foreach (var userId in userIds.Distinct()) { sql = Sql.Builder.Select("count(*)").From("tn_ActivityUserInbox").Where("ActivityId in (@activityIds)", new { activityIds = activityIds }).Where("UserId=@0", userId); if (dao.FirstOrDefault <long>(sql) > 0) { continue; //已经存在 } sql = Sql.Builder .Append(@"insert tn_ActivityUserInbox (UserId,ActivityId) values(@0,@1)", userId, activityId); sqls.Add(sql); } dao.Execute(sqls); dao.CloseSharedConnection(); }
/// <summary> /// 清除内容项的所有标签 /// </summary> /// <param name="itemId">内容项Id</param> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">拥有者Id</param> public int ClearTagsFromItem(long itemId, string tenantTypeId, long ownerId) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); var sql = PetaPoco.Sql.Builder; sql.Select("IT.TagName") .From("tn_ItemsInTags IT") .InnerJoin("tn_TagsInOwners TIO") .On("IT.TagInOwnerId = TIO.Id") .Where("TIO.TenantTypeId = @0", tenantTypeId) .Where("TIO.OwnerId = @0", ownerId) .Where("IT.ItemId = @0", itemId); List <string> tagNames = dao.Fetch <string>(sql); sql = Sql.Builder; sql.Append("delete from tn_ItemsInTags where ItemId = @0 and exists(select 1 from tn_TagsInOwners TIO where TIO.TenantTypeId = @1 and OwnerId = @2 and TIO.Id = TagInOwnerId)", itemId, tenantTypeId, ownerId); int affectCount = dao.Execute(sql); if (affectCount > 0) { List <Sql> sqls = new List <Sql>(); foreach (string tagName in tagNames) { sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount - 1") .Where("ItemCount > 0 and OwnerId = @0 and TagName = @1 and TenantTypeId = @2", ownerId, tagName, tenantTypeId)); sqls.Add(Sql.Builder.Append("update tn_Tags set ItemCount = ItemCount - 1") .Where("ItemCount > 0 and TagName = @0 and TenantTypeId = @1", tagName, tenantTypeId)); RealTimeCacheHelper.IncreaseAreaVersion("TagName", tagName); } dao.Execute(sqls); RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 更新用户的隐私设置 /// </summary> /// <param name="userId">用户Id</param> /// <param name="userSettings"><remarks>key=itemKey,value=PrivacyStatus</remarks></param> public void UpdateUserPrivacySettings(long userId, Dictionary <string, PrivacyStatus> userSettings) { PetaPocoDatabase dao = CreateDAO(); var sql = Sql.Builder; sql.Select("*") .From("tn_UserPrivacySettings") .Where("UserId = @0", userId); //done:zhangp,by zhengw:应该共享一个数据库连接 //回复:已经修改 dao.OpenSharedConnection(); UserPrivacySetting setting; List <UserPrivacySetting> settings = dao.Fetch <UserPrivacySetting>(sql); if (userSettings == null) { return; } foreach (var item in userSettings) { setting = settings.Find(n => n.ItemKey == item.Key); if (setting != null) { var sqlDel = Sql.Builder; sqlDel.Append("delete from tn_UserPrivacySpecifyObjects where UserPrivacySettingId in (select Id from tn_UserPrivacySettings where UserId = @0 and ItemKey = @1)", userId, item.Key); dao.Execute(sqlDel); EntityData.ForType(typeof(UserPrivacySpecifyObject)).RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); setting.PrivacyStatus = item.Value; dao.Update(setting); OnUpdated(setting); } else { setting = new UserPrivacySetting(); setting.UserId = userId; setting.ItemKey = item.Key; setting.PrivacyStatus = item.Value; base.Insert(setting); } } dao.CloseSharedConnection(); RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); }
/// <summary> /// 更新用户请求设置 /// </summary> /// <param name="userId">用户id</param> /// <param name="userNoticeSettings">用户的设置</param> public void UpdateUserNoticeSettings(long userId, Dictionary <int, bool> userNoticeSettings) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); string cacheKey = GetCacheKey_UserNoticeSettingses(userId); Dictionary <int, bool> userNoticeSettingsFormDB = cacheService.Get <Dictionary <int, bool> >(cacheKey); if (userNoticeSettingsFormDB == null) { var sql_Seletct = Sql.Builder; sql_Seletct.Select("*").From("tn_UserNoticeSettings").Where("UserId=@0", userId); IEnumerable <dynamic> userSettings = dao.Fetch <dynamic>(sql_Seletct); userNoticeSettingsFormDB = new Dictionary <int, bool>(); foreach (var item in userSettings) { userNoticeSettingsFormDB[item.TypeId] = item.IsAllowable == 1; } } Dictionary <int, bool> diffNoticeSettings = new Dictionary <int, bool>(); Dictionary <int, bool> newNoticeSettings = new Dictionary <int, bool>(); var sql = new List <PetaPoco.Sql>(); foreach (var item in userNoticeSettings) { if (userNoticeSettingsFormDB != null && userNoticeSettingsFormDB.ContainsKey(item.Key) && userNoticeSettingsFormDB[item.Key] != item.Value) { sql.Add(PetaPoco.Sql.Builder.Append("update tn_UserNoticeSettings set IsAllowable=@0 where UserId=@1 and TypeId=@2", item.Value, userId, item.Key)); } if (userNoticeSettingsFormDB == null || !userNoticeSettingsFormDB.ContainsKey(item.Key)) { sql.Add(PetaPoco.Sql.Builder.Append("insert into tn_UserNoticeSettings(UserId,TypeId,IsAllowable) values(@0,@1,@2)", userId, item.Key, item.Value)); } } dao.Execute(sql); dao.CloseSharedConnection(); if (userNoticeSettingsFormDB != null) { cacheService.Set(cacheKey, userNoticeSettings, CachingExpirationType.ObjectCollection); } }
/// <summary> /// 把动态最后更新时间设置为当前时间 /// </summary> /// <param name="activityId"></param> public bool UpdateLastModified(long activityId) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Activity activity = Get(activityId); Sql sql = Sql.Builder; sql.Append("Update tn_Activities Set LastModified=@0", DateTime.UtcNow) .Where("ActivityId=@0", activityId); int affectCount = dao.Execute(sql); List <long> userIds = null; if (affectCount > 0) { sql = Sql.Builder.Select("UserId").From("tn_ActivityUserInbox").Where("ActivityId=@0", activityId); userIds = dao.Fetch <long>(sql); } dao.CloseSharedConnection(); if (affectCount == 0) { return(false); } #region 更新缓存 activity.LastModified = DateTime.UtcNow; this.OnUpdated(activity); //让拥有者的粉丝及时看到这条动态的更新 foreach (var userId in userIds) { RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); } #endregion return(true); }
/// <summary> /// 更新用户隐私设置指定对象 /// </summary> /// <param name="userId">用户Id</param> /// <param name="specifyObjects"><remarks>key=itemKey,value=用户指定对象集合</remarks></param> public void UpdateUserPrivacySpecifyObjects(long userId, Dictionary <string, IEnumerable <UserPrivacySpecifyObject> > specifyObjects) { //done:zhangp,by zhengw: //先遍历集合specifyObjects //再使用sql语句来获取当前itemKey下的所有指定对象集合oldItemSpecifyObjects //比较oldItemSpecifyObjects和newItemSpecifyObjects //1.如果newItemSpecifyObjects为null,则清空 //2.新旧数据取交集,然后进行删除和插入 //done:zhangp,by zhengw:这些局部变量没必要在外边实例化,后面肯定会被覆盖、 //回复:已修改 PetaPocoDatabase dao = CreateDAO(); var sql = Sql.Builder; List <UserPrivacySpecifyObject> userPrivacySpecifyObjects; IEnumerable <UserPrivacySpecifyObject> pairs; IEnumerable <UserPrivacySpecifyObject> deletePairs; IEnumerable <UserPrivacySpecifyObject> insertPairs; if (specifyObjects == null || specifyObjects.Count() == 0) { return; } dao.OpenSharedConnection(); foreach (var item in specifyObjects) { sql = Sql.Builder; sql.Select("tn_UserPrivacySpecifyObjects.*") .From("tn_UserPrivacySpecifyObjects") .InnerJoin("tn_UserPrivacySettings") .On("tn_UserPrivacySettings.Id = tn_UserPrivacySpecifyObjects.UserPrivacySettingId") .Where("UserId = @0", userId) .Where("ItemKey = @0", item.Key); userPrivacySpecifyObjects = dao.Fetch <UserPrivacySpecifyObject>(sql); //done:zhangp,by zhengw:userPrivacySpecifyObjects.Count为0时就不更新了?我觉得可以把这个判断去掉,两个边界情况都可以使用求交集的办法 //回复:已修改 sql = Sql.Builder; sql.Select("Id") .From("tn_UserPrivacySettings") .Where("UserId = @0", userId) .Where("ItemKey = @0", item.Key); long userPrivacySettingId = dao.FirstOrDefault <long>(sql); if (item.Value == null) { foreach (var oldSpecifyObject in userPrivacySpecifyObjects) { base.Delete(oldSpecifyObject); } } pairs = userPrivacySpecifyObjects.Intersect(item.Value); deletePairs = userPrivacySpecifyObjects.Except(pairs); foreach (var deletePair in deletePairs) { base.Delete(deletePair); } insertPairs = item.Value.Except(pairs); foreach (var insertPair in insertPairs) { insertPair.UserPrivacySettingId = userPrivacySettingId; base.Insert(insertPair); } } dao.CloseSharedConnection(); RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); }
/// <summary> /// 更新积分统计 /// </summary> /// <param name="userId">用户Id</param> /// <param name="pointCategory2PointsDictionary"><remarks>key=PointCategory,value=Points</remarks>积分分类-积分字典</param> /// <returns>修订后应获取到的积分值</returns> public Dictionary <string, int> UpdateStatistic(long userId, Dictionary <PointCategory, int> pointCategory2PointsDictionary) { Dictionary <string, int> dictionary = new Dictionary <string, int>(); RWLock.EnterWriteLock(); PetaPocoDatabase dao = CreateDAO(); try { dao.OpenSharedConnection(); //1、检查当日积分统计是否存在,不存在创建 //2、检查是否超过当日限额,如果未超过更新当日积分累计 var sql = Sql.Builder; sql.Select("*") .From("tn_PointStatistics") .Where("UserId = @0", userId) .Where("StatisticalYear = @0", DateTime.UtcNow.Year) .Where("StatisticalMonth = @0", DateTime.UtcNow.Month) .Where("StatisticalDay = @0", DateTime.UtcNow.Day); IEnumerable <PointStatistic> pointStatistices = dao.Fetch <PointStatistic>(sql); //初始化 foreach (var pair in pointCategory2PointsDictionary) { dictionary[pair.Key.CategoryKey] = pair.Value; } //当日积分统计不存在 if (pointStatistices == null || pointStatistices.Count() == 0) { //创建当日积分统计 foreach (var pair in pointCategory2PointsDictionary) { if (pair.Key.QuotaPerDay <= 0) { continue; } var pointStatistic = PointStatistic.New(); pointStatistic.UserId = userId; pointStatistic.PointCategoryKey = pair.Key.CategoryKey; pointStatistic.Points = pair.Value; dao.Insert(pointStatistic); } } else { //检查是积分限额,调整用户最终获取到的积分 foreach (var pair in pointCategory2PointsDictionary) { if (pair.Key.QuotaPerDay <= 0) { continue; } var category = pair.Key; var pointStatistic = pointStatistices.FirstOrDefault(n => n.PointCategoryKey == category.CategoryKey); if (pointStatistic == null) { continue; } if (pair.Value > 0 && pointStatistic.Points + pair.Value > category.QuotaPerDay)//超过限额 { dictionary[pair.Key.CategoryKey] = 0; } else { pointStatistic.Points += pair.Value; dao.Update(pointStatistic); } } } } finally { dao.CloseSharedConnection(); RWLock.ExitWriteLock(); } //更新用户分区缓存 RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); return(dictionary); }
/// <summary> /// 更新私信的阅读状态 /// </summary> /// <param name="sessionId">私信会话Id</param> /// <param name="userId">会话拥有者UserId</param> public bool SetIsRead(long sessionId, long userId) { var sql = Sql.Builder; bool isRead = false; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); sql.Select("MessageId") .From("tn_Messages") .Append("Where ReceiverUserId = @0 and IsRead = 0", userId) .Append(" and exists(select 1 from tn_MessagesInSessions where tn_Messages.MessageId = tn_MessagesInSessions.MessageId and tn_MessagesInSessions.SessionId = @0)", sessionId); //获取未读私信Id List <long> ids = dao.Fetch <long>(sql); if (ids.Count() > 0) { sql = Sql.Builder.Append("update tn_Messages") .Append("Set IsRead = 1") .Where("MessageId in (@MessageIds)", new { MessageIds = ids }); dao.Execute(sql); //更新会话的未读信息数 MessageSessionRepository repository = new MessageSessionRepository(); MessageSession session = repository.Get(sessionId); if (session != null) { session.UnreadMessageCount = session.UnreadMessageCount > ids.Count() ? session.UnreadMessageCount - ids.Count() : 0; repository.Update(session); } #region 处理缓存 RealTimeCacheHelper.IncreaseAreaVersion("ReceiverUserId", userId); string cacheKey = GetCacheKey_UnreadCount(userId); int? count = cacheService.Get(cacheKey) as int?; if (count != null) { if (count >= ids.Count && count > 0) { count -= ids.Count; } cacheService.Set(cacheKey, count, CachingExpirationType.SingleObject); } #endregion 处理缓存 isRead = true; } dao.CloseSharedConnection(); return(isRead); }
/// <summary> /// 从数据库中删除实体 /// </summary> /// <param name="entity">待删除私信实体</param> /// <param name="userId">私信会话拥有者</param> /// <param name="sessionId">私信会话Id</param> /// <returns>操作后影响行数</returns> public int Delete(Message entity, long sessionId) { var sql = Sql.Builder; IList <Sql> sqls = new List <Sql>(); int affectCount = 0; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); List <string> dd = new List <string>(); sql.From("tn_MessageSessions") .Where("(UserId = @0 and OtherUserId = @1) or (UserId = @1 and OtherUserId = @0)", entity.SenderUserId, entity.ReceiverUserId); List <MessageSession> sessions = dao.Fetch <MessageSession>(sql); if (sessions.Count > 0) { //处理相关会话的计数,如果会话中仅当前一条私信则删除会话 foreach (var session in sessions) { if (session.SessionId != sessionId) { continue; } if (session.MessageCount > 1) { sqls.Add(Sql.Builder.Append("update tn_MessageSessions") .Append("set MessageCount = MessageCount - 1") .Where("SessionId = @0", session.SessionId)); } else { sqls.Add(Sql.Builder.Append("delete from tn_MessageSessions where SessionId = @0", session.SessionId)); } //删除会话与私信的关系 sqls.Add(Sql.Builder.Append("delete from tn_MessagesInSessions where SessionId = @0 and MessageId = @1", session.SessionId, entity.MessageId)); } using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); if (sessions.Count == 1) { affectCount += base.Delete(entity); } else { foreach (var session in sessions) { EntityData.ForType(typeof(MessageInSession)).RealTimeCacheHelper.IncreaseAreaVersion("SessionId", session.SessionId); } } transaction.Complete(); } } dao.CloseSharedConnection(); #region 更新缓存 //更新私信会话的缓存 var sessionCacheHelper = EntityData.ForType(typeof(MessageSession)).RealTimeCacheHelper; sessionCacheHelper.IncreaseAreaVersion("UserId", entity.SenderUserId); sessionCacheHelper.IncreaseAreaVersion("UserId", entity.ReceiverUserId); sessions.ForEach((n) => { sessionCacheHelper.IncreaseEntityCacheVersion(n.SessionId); }); #endregion 更新缓存 return(affectCount); }