/// <summary> /// 添加标签与分组关联 /// </summary> /// <param name="tagName">标签名</param> /// <param name="groupId">拥有者Id</param> /// <param name="tenantTypeId">租户类型Id</param> public int AddTagInGroup(string tagName, long groupId, string tenantTypeId) { PetaPocoDatabase dao = CreateDAO(); int affectCount = 0; dao.OpenSharedConnection(); var sql = PetaPoco.Sql.Builder; sql.Select("count(*)") .From("tn_TagsInGroups") .Where("GroupId = @0", groupId) .Where("TagName = @0", tagName) .Where("TenantTypeId = @0", tenantTypeId); affectCount = dao.ExecuteScalar <int>(sql); if (affectCount == 0) { sql = PetaPoco.Sql.Builder; sql.Append("INSERT INTO tn_TagsInGroups (GroupId, TagName,TenantTypeId) VALUES (@0,@1,@2)", groupId, tagName, tenantTypeId); affectCount = dao.Execute(sql); if (affectCount > 0) { RealTimeCacheHelper.IncreaseAreaVersion("GroupId", groupId); } } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 保存用户自定义风格 /// </summary> /// <param name="presentAreaKey">呈现区域标识</param> /// <param name="ownerId">OwnerId</param> /// <param name="customStyle">自定义风格实体</param> public void Save(string presentAreaKey, long ownerId, CustomStyle customStyle) { PetaPocoDatabase database = CreateDAO(); database.OpenSharedConnection(); CustomStyleEntity entity = Get(presentAreaKey, ownerId); string customStyleXml = Serialize(customStyle); if (entity != null) { customStyle.LastModified = DateTime.UtcNow; entity.CustomStyle = customStyle; entity.SerializedCustomStyle = customStyleXml; entity.LastModified = DateTime.UtcNow; database.Update(entity); } else { entity = CustomStyleEntity.New(); entity.CustomStyle = customStyle; entity.PresentAreaKey = presentAreaKey; entity.OwnerId = ownerId; entity.SerializedCustomStyle = customStyleXml; database.Insert(entity); } database.CloseSharedConnection(); cacheService.Set(GetCacheKey_CustomStyleEntity(presentAreaKey, ownerId), entity, CachingExpirationType.UsualSingleObject); }
/// <summary> /// 执行队列 /// </summary> public void ExecQueue() { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); var tempCreateVisitQueue = new Queue <long>(CreateVisitQueue); CreateVisitQueue.Clear(); while (tempCreateVisitQueue.Count > 0) { Visit visit = Get(tempCreateVisitQueue.Dequeue()); if (visit == null) { continue; } dao.Insert(visit); } //将队列中的数据更新到数据库 var tempUpdateVisitQueue = new Queue <long>(UpdateVisitQueue); UpdateVisitQueue.Clear(); while (tempUpdateVisitQueue.Count > 0) { Visit visit = Get(tempUpdateVisitQueue.Dequeue()); if (visit == null) { continue; } dao.Update(visit); } dao.CloseSharedConnection(); }
/// <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="privacyItems">待更新的隐私项目规则集合</param> public void UpdatePrivacyItems(IEnumerable <PrivacyItem> privacyItems) { if (privacyItems == null) { return; } List <Sql> sqls = new List <Sql>(); PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); foreach (var privacyItem in privacyItems) { sqls.Add(Sql.Builder.Append("update tn_PrivacyItems") .Append("set ItemName = @0, Description = @1, DisplayOrder = @2, PrivacyStatus = @3", privacyItem.ItemName, privacyItem.Description, privacyItem.DisplayOrder, privacyItem.PrivacyStatus) .Append("where ItemKey = @0 and ItemGroupId = @1 and ApplicationId = @2", privacyItem.ItemKey, privacyItem.ItemGroupId, privacyItem.ApplicationId)); RealTimeCacheHelper.IncreaseEntityCacheVersion(privacyItem.ItemKey); OnUpdated(privacyItem); } dao.Execute(sqls); //done:zhangp,by zhengw:需要递增全局版本号 dao.CloseSharedConnection(); RealTimeCacheHelper.IncreaseGlobalVersion(); }
/// <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="tagNames">标签名</param> /// <param name="groupId">分组Id</param> /// <param name="tenantTypeId">租户类型Id</param> public int BatchAddTagsInGroup(IEnumerable <string> tagNames, long groupId, string tenantTypeId) { int affectCount = 0; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); dao.Execute(Sql.Builder.Append("delete from tn_TagsInGroups where GroupId = @0", groupId)); IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>(); foreach (var tagName in tagNames) { if (string.IsNullOrEmpty(tagName)) { continue; } sqls.Add(Sql.Builder.Append("INSERT INTO tn_TagsInGroups (GroupId, TagName,TenantTypeId) VALUES (@0,@1,@2)", groupId, tagName, tenantTypeId)); } affectCount = dao.Execute(sqls); if (affectCount > 0) { RealTimeCacheHelper.IncreaseAreaVersion("GroupId", groupId); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 删除标签与成员的关系实体 /// </summary> /// <param name="entity">待处理的实体</param> /// <returns></returns> public override int Delete(ItemInTag entity) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); int affectCount = base.Delete(entity); if (entity != null && affectCount > 0) { List <Sql> sqls = new List <Sql>(); sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount - 1") .Where("ItemCount > 0 and Id = @0", entity.TagInOwnerId)); sqls.Add(Sql.Builder.Append("update tn_Tags set ItemCount = ItemCount - 1") .Where("ItemCount > 0 and TagName = @0 and TenantTypeId = @1", entity.TagName, entity.TenantTypeId)); affectCount = dao.Execute(sqls); RealTimeCacheHelper.IncreaseAreaVersion("TagName", entity.TagName); RealTimeCacheHelper.IncreaseAreaVersion("ItemId", entity.ItemId); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 从数据库删除实体 /// </summary> /// <param name="entity">标签实体</param> /// <returns>影响行数</returns> public override int Delete(T entity) { IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>(); int affectCount = 0; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_Tags where TagId = @0", entity.TagId)); sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_ItemsInTags where TagInOwnerId in (Select DISTINCT Id from tn_TagsInOwners where TenantTypeId = @0 and TagName = @1)", entity.TenantTypeId, entity.TagName)); sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInOwners where TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName)); sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInGroups where TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { //更新实体缓存 OnDeleted(entity); RealTimeCacheHelper.IncreaseGlobalVersion(); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 根据审核状态获取群组数 /// </summary> /// <returns></returns> public Dictionary <GroupManageableCountType, int> GetManageableCounts() { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Dictionary <GroupManageableCountType, int> countType = new Dictionary <GroupManageableCountType, int>(); var sql_selectIsActivated = PetaPoco.Sql.Builder; sql_selectIsActivated.Select("count(*)").From("spb_Groups"); sql_selectIsActivated.Where("AuditStatus = @0", AuditStatus.Pending); countType[GroupManageableCountType.Pending] = dao.FirstOrDefault <int>(sql_selectIsActivated); var sql_selectIsAll = PetaPoco.Sql.Builder; sql_selectIsAll.Select("count(*)").From("spb_Groups"); countType[GroupManageableCountType.IsAll] = dao.FirstOrDefault <int>(sql_selectIsAll); var sql_selectIsLast24 = PetaPoco.Sql.Builder; sql_selectIsLast24.Select("count(*)").From("spb_Groups"); sql_selectIsLast24.Where("DateCreated >= @0 and DateCreated < @1", DateTime.UtcNow.AddDays(-1), DateTime.UtcNow); countType[GroupManageableCountType.IsLast24] = dao.FirstOrDefault <int>(sql_selectIsLast24); dao.CloseSharedConnection(); return(countType); }
/// <summary> /// 判断用户是否对同一主体内容产生过从属内容动态,如果产生过则替换成本次操作 /// </summary> /// <param name="activity"></param> /// <returns>true-更新成功,false-不存在OwnerType+TenantTypeId+ReferenceId的动态记录</returns> public bool CheckExistAndUpdateSource(Activity activity) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Sql sql = Sql.Builder; sql.Select("ActivityId").From("tn_Activities").Where("OwnerId=@0", activity.OwnerId).Where("OwnerType=@0", activity.OwnerType).Where("UserId=@0", activity.UserId) .Where("TenantTypeId=@0", activity.TenantTypeId).Where("ReferenceId=@0", activity.ReferenceId).Where("ReferenceTenantTypeId=@0", activity.ReferenceTenantTypeId); long oldActivityId = dao.FirstOrDefault <long>(sql); int affectCount = 0; if (oldActivityId > 0) { sql = Sql.Builder.Append("Update tn_Activities Set SourceId=@0,DateCreated=@1,LastModified=@2", activity.SourceId, DateTime.UtcNow, DateTime.UtcNow) .Where("ActivityId=@0", oldActivityId); affectCount = dao.Execute(sql); Activity oldActivity = Get(oldActivityId); oldActivity.SourceId = activity.SourceId; oldActivity.DateCreated = DateTime.UtcNow; oldActivity.LastModified = DateTime.UtcNow; this.OnUpdated(oldActivity); } dao.CloseSharedConnection(); return(oldActivityId > 0); }
/// <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="category">待创建分类</param> /// <returns></returns> public override object Insert(T category) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); long primaryKey = Convert.ToInt64(dao.Insert(category)); if (primaryKey > 0) { List <Sql> sqls = new List <Sql>(); if (category.ParentId > 0) { sqls.Add(Sql.Builder.Append("Update tn_Categories set ChildCount = ChildCount + 1 Where CategoryId = @0", category.ParentId)); } sqls.Add(Sql.Builder.Append("update tn_Categories set DisplayOrder = @0 where CategoryId = @0", category.CategoryId)); int affectCount = dao.Execute(sqls); category.DisplayOrder = category.CategoryId; base.OnInserted(category); } dao.CloseSharedConnection(); return(primaryKey); }
/// <summary> /// 批量添加分组给标签 /// </summary> /// <param name="groupIds">分组Id集合</param> /// <param name="tagName">标签名</param> /// <param name="tenantTypeId">租户类型Id</param> public int BatchAddGroupsToTag(IEnumerable <long> groupIds, string tagName, string tenantTypeId) { int affectCount = 0; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); dao.Execute(Sql.Builder.Append("delete from tn_TagsInGroups where tagName = @0 and TenantTypeId = @1", tagName, tenantTypeId)); IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>(); foreach (var groupId in groupIds) { //if (groupId <= 0) // continue; sqls.Add(Sql.Builder.Append("INSERT INTO tn_TagsInGroups (GroupId, TagName,TenantTypeId) VALUES (@0,@1,@2)", groupId, tagName, tenantTypeId)); } affectCount = dao.Execute(sqls); if (affectCount > 0) { groupIds.ToList().ForEach(n => { RealTimeCacheHelper.IncreaseAreaVersion("GroupId", n); }); EntityData.ForType(typeof(TagGroup)).RealTimeCacheHelper.IncreaseAreaVersion("TenantTypeId", tenantTypeId); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 添加相关标签 /// </summary> /// <remarks> /// 会为标签添加双向的关联关系,例如: /// TagA关联到TagB /// TagB关联到TagA /// </remarks> /// <param name="tagNames">相关标签名称集合</param> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">拥有者Id</param> /// <param name="tagId">标签Id</param> /// <returns> 影响行数</returns> public int AddRelatedTagsToTag(string[] tagNames, string tenantTypeId, long ownerId, long tagId) { PetaPocoDatabase dao = CreateDAO(); int affectCount = 0; dao.OpenSharedConnection(); foreach (string tagName in tagNames) { if (string.IsNullOrEmpty(tagName)) { continue; } IList <Sql> sqls = new List <Sql>(); //创建标签 var sql = Sql.Builder; sql.From("tn_Tags") .Where("TenantTypeId = @0", tenantTypeId) .Where("TagName = @0", tagName); var tag = dao.FirstOrDefault <Tag>(sql); if (tag == null) { sqls.Add(Sql.Builder.Append("insert into tn_Tags (TenantTypeId,TagName,DateCreated,ItemCount,OwnerCount) values (@0,@1,@2,1,1)", tenantTypeId, tagName, DateTime.UtcNow)); } //创建标签与用户的关联 sql = Sql.Builder; sql.From("tn_TagsInOwners") .Where("TenantTypeId = @0", tenantTypeId) .Where("TagName = @0", tagName) .Where("OwnerId = @0", ownerId); var tagInOwner = dao.FirstOrDefault <TagInOwner>(sql); if (tagInOwner == null) { sqls.Add(Sql.Builder.Append("insert into tn_TagsInOwners (TenantTypeId,TagName,OwnerId,ItemCount) values (@0,@1,@2,1)", tenantTypeId, tagName, ownerId)); } //创建标签与内容项的关联 sqls.Add(Sql.Builder.Append("insert into tn_RelatedTags(TagId, RelatedTagId) select @0,TagId from tn_Tags where TenantTypeId = @1 and TagName = @2", tagId, tenantTypeId, tagName)); sqls.Add(Sql.Builder.Append("insert into tn_RelatedTags(TagId, RelatedTagId) select TagId,@0 from tn_Tags where TenantTypeId = @1 and TagName = @2", tagId, tenantTypeId, tagName)); //通过事务来控制多条语句执行时的一致性 using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } } dao.CloseSharedConnection(); if (affectCount > 0) { RealTimeCacheHelper.IncreaseAreaVersion("TagId", tagId); } return(affectCount); }
/// <summary> /// 更新内容隐私设置 /// </summary> /// <param name="privacyable">可隐私接口</param> /// <param name="specifyObjects"><remarks>key=specifyObjectTypeId,value=内容指定对象集合</remarks></param> public void UpdatePrivacySettings(IPrivacyable privacyable, Dictionary <int, IEnumerable <ContentPrivacySpecifyObject> > specifyObjects) { //回复:已修改 var sql = Sql.Builder; //List<ContentPrivacySpecifyObject> userPrivacySpecifyObjects; //IEnumerable<ContentPrivacySpecifyObject> pairs; //IEnumerable<ContentPrivacySpecifyObject> deletePairs; //IEnumerable<ContentPrivacySpecifyObject> insertPairs; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); sql.Append("delete from tn_ContentPrivacySpecifyObjects") .Where("ContentId = @0", privacyable.ContentId) .Where("TenantTypeId = @0", privacyable.TenantTypeId); dao.Execute(sql); if (specifyObjects == null) { specifyObjects = new Dictionary <int, IEnumerable <ContentPrivacySpecifyObject> >(); } foreach (var item in specifyObjects) { //sql = Sql.Builder; //done:zhangp,by zhengw:SpecifyObjectId=>SpecifyObjectTypeId //回复:已修改 foreach (var insertPair in item.Value) { dao.Insert(insertPair); } //sql.Select("*") // .From("tn_ContentPrivacySpecifyObjects") // .Where("ContentId = @0", privacyable.ContentId) // .Where("TenantTypeId = @0", privacyable.TenantTypeId) // .Where("SpecifyObjectTypeId = @0", item.Key); //userPrivacySpecifyObjects = dao.Fetch<ContentPrivacySpecifyObject>(sql); //done:zhangp,by zhengw:userPrivacySpecifyObjects.Count为0时就不更新了?我觉得可以把这个判断去掉,两个边界情况都可以使用求交集的办法 //回复:已修改 //pairs = userPrivacySpecifyObjects.Intersect(item.Value); //deletePairs = userPrivacySpecifyObjects.Except(pairs); //foreach (var deletePair in deletePairs) // dao.Delete(deletePair); //insertPairs = item.Value.Except(pairs); //foreach (var insertPair in insertPairs) // dao.Insert(insertPair); } dao.CloseSharedConnection(); RealTimeCacheHelper.IncreaseAreaVersion("TenantTypeId", privacyable.TenantTypeId); }
/// <summary> /// 更新完成度 /// </summary> /// <param name="userId">用户Id</param> public void UpdateIntegrity(long userId) { UserProfileSettings userProfileSettings = DIContainer.Resolve <IUserProfileSettingsManager>().GetUserProfileSettings(); int[] integrityItems = userProfileSettings.IntegrityProportions; int integrity = integrityItems[(int)ProfileIntegrityItems.Birthday]; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); var sql = Sql.Builder; sql.Select("Count(*)") .From("spb_EducationExperiences") .Where("UserId = @0", userId); int countEducation = dao.ExecuteScalar <int>(sql); if (countEducation > 0) { integrity += integrityItems[(int)ProfileIntegrityItems.EducationExperience]; } sql = Sql.Builder; sql.Select("count(*)") .From("spb_WorkExperiences") .Where("UserId = @0", userId); int countWork = dao.ExecuteScalar <int>(sql); if (countWork > 0) { integrity += integrityItems[(int)ProfileIntegrityItems.WorkExperience]; } sql = Sql.Builder; sql.Where("userId = @0", userId); UserProfile userProfile = dao.FirstOrDefault <UserProfile>(sql); if (userProfile != null) { IUser user = new UserService().GetUser(userProfile.UserId); integrity += (user.HasAvatar ? integrityItems[(int)ProfileIntegrityItems.Avatar] : 0); integrity += (userProfile.HasHomeAreaCode ? integrityItems[(int)ProfileIntegrityItems.HomeArea] : 0); integrity += (userProfile.HasIM ? integrityItems[(int)ProfileIntegrityItems.IM] : 0); integrity += (userProfile.HasIntroduction ? integrityItems[(int)ProfileIntegrityItems.Introduction] : 0); integrity += (userProfile.HasMobile ? integrityItems[(int)ProfileIntegrityItems.Mobile] : 0); integrity += (userProfile.HasNowAreaCode ? integrityItems[(int)ProfileIntegrityItems.NowArea] : 0); userProfile.Integrity = integrity; Update(userProfile); } dao.CloseSharedConnection(); }
/// <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="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="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="userId">被提醒用户Id</param> /// <param name="reminderModeId">提醒方式Id</param> /// <param name="reminderInfoTypeId">提醒信息类型Id</param> /// <param name="objectIds">提醒对象Id集合</param> public void UpdateRecoreds(long userId, int reminderModeId, int reminderInfoTypeId, IEnumerable <long> objectIds) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); var sql = Sql.Builder; IEnumerable <ReminderRecord> records = this.GetRecords(userId, reminderModeId, reminderInfoTypeId); foreach (var objectId in objectIds) { sql.Append("Update tn_ReminderRecords set LastReminderTime = @0 where UserId = @1 and ReminderModeId = @2 and ReminderInfoTypeId = @3 and ObjectId = @4", DateTime.UtcNow, userId, reminderModeId, reminderInfoTypeId, objectId); dao.Execute(sql); } dao.CloseSharedConnection(); RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); }
/// <summary> /// 获取未使用的Url别名 /// </summary> /// <param name="aliases">Url别名集合</param> /// <param name="url">待处理的Url</param> /// <param name="urlExists">带处理Url是否已存在</param> public string GetUnusedAlias(string[] aliases, string url, out bool urlExists) { urlExists = false; if (aliases == null || aliases.Length == 0 || string.IsNullOrEmpty(url)) { return(string.Empty); } var sql = PetaPoco.Sql.Builder; string alias = string.Empty; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); //判断Url是否存在 sql.Select("Alias") .From("tn_ShortUrls") .Where("Url = @0", url); alias = dao.FirstOrDefault <string>(sql); if (!string.IsNullOrEmpty(alias)) { urlExists = true; } if (!urlExists) { foreach (string item in aliases) { sql = Sql.Builder; sql.Select("count(Alias)") .From("tn_ShortUrls") .Where("Alias = @0", item); if (dao.ExecuteScalar <int>(sql) == 0) { alias = item; break; } } } dao.CloseSharedConnection(); return(alias); }
/// <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="isActivated">是否激活</param> /// <param name="isBanned">是否封禁</param> /// <param name="isModerated">是否管制</param> public Dictionary <UserManageableCountType, int> GetManageableCounts(bool isActivated, bool isBanned, bool isModerated) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Dictionary <UserManageableCountType, int> countType = new Dictionary <UserManageableCountType, int>(); var sql_selectIsActivated = PetaPoco.Sql.Builder; sql_selectIsActivated.Select("count(*)").From("tn_Users"); sql_selectIsActivated.Where("IsActivated=@0", isActivated); countType[UserManageableCountType.IsActivated] = dao.FirstOrDefault <int>(sql_selectIsActivated); var sql_selectIsBanned = PetaPoco.Sql.Builder; sql_selectIsBanned.Select("count(*)").From("tn_Users"); sql_selectIsBanned.Where("IsBanned=@0", isBanned); countType[UserManageableCountType.IsBanned] = dao.FirstOrDefault <int>(sql_selectIsBanned); var sql_selectIsModerated = PetaPoco.Sql.Builder; sql_selectIsModerated.Select("count(*)").From("tn_Users"); sql_selectIsModerated.Where("IsModerated=@0", isModerated); countType[UserManageableCountType.IsModerated] = dao.FirstOrDefault <int>(sql_selectIsModerated); var sql_selectIsAll = PetaPoco.Sql.Builder; sql_selectIsAll.Select("count(*)").From("tn_Users"); countType[UserManageableCountType.IsAll] = dao.FirstOrDefault <int>(sql_selectIsAll); var sql_selectIsLast24 = PetaPoco.Sql.Builder; sql_selectIsLast24.Select("count(*)").From("tn_Users"); sql_selectIsLast24.Where("DateCreated > @0 and DateCreated < @1", DateTime.UtcNow.AddDays(-1), DateTime.UtcNow.AddDays(1)); countType[UserManageableCountType.IsLast24] = dao.FirstOrDefault <int>(sql_selectIsLast24); dao.CloseSharedConnection(); return(countType); }
/// <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="activityId"></param> public void DeleteActivity(long activityId) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); this.DeleteByEntityId(activityId); Sql sql = Sql.Builder .Append("delete from tn_ActivityUserInbox where ActivityId=@0 ", activityId); dao.Execute(sql); sql = Sql.Builder .Append("delete from tn_ActivitySiteInbox where ActivityId=@0 ", activityId); dao.Execute(sql); dao.CloseSharedConnection(); }
/// <summary> /// 创建实体 /// </summary> /// <param name="entity">待创建实体</param> /// <returns></returns> public override object Insert(T entity) { Sql sql = Sql.Builder; sql.Append("select count(*) from tn_Tags where TagName = @0 and TenantTypeId = @1", entity.TagName, entity.TenantTypeId); PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); object id = 0; if (dao.ExecuteScalar <int>(sql) == 0) { id = base.Insert(entity); } dao.CloseSharedConnection(); return(id); }
/// <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="userId">用户Id</param> /// <param name="roleNames">赋予用户的用户角色</param> public void AddUserToRoles(long userId, List <string> roleNames) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); RemoveUserRoles(userId); var sqlInsert = Sql.Builder; UserInRole userInRole = new UserInRole(); userInRole.UserId = userId; foreach (var roleName in roleNames) { userInRole.RoleName = roleName; dao.Insert(userInRole); } dao.CloseSharedConnection(); //增加版本 RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); }