/// <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> /// <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="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="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="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> /// 通过被邀请人ID获取邀请人 /// </summary> /// <param name="userId">被邀请人ID</param> /// <returns></returns> public InviteFriendRecord GetInvitingUserId(long userId) { var sql_Get = PetaPoco.Sql.Builder; PetaPocoDatabase dao = CreateDAO(); sql_Get.Select("*") .From("tn_InviteFriendRecords") .Where("InvitedUserId = @0", userId); InviteFriendRecord record = dao.FirstOrDefault <InviteFriendRecord>(sql_Get); return(record); }
/// <summary> /// 获取常用操作 /// </summary> /// <param name="navigationId"></param> /// <param name="userId"></param> /// <returns></returns> public CommonOperation GetCommonOperation(int navigationId, long userId) { Sql sql = Sql.Builder; PetaPocoDatabase dao = CreateDAO(); sql.Select("*") .From("tn_CommonOperations") .Where("UserId=@0", userId) .Where("NavigationId=@0", navigationId); CommonOperation commonOperation = null; commonOperation = dao.FirstOrDefault <CommonOperation>(sql); return(commonOperation); }
/// <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="presentAreaKey">呈现区域标识</param> /// <param name="ownerId">OwnerId</param> /// <returns>无相应数据返回null</returns> public CustomStyleEntity Get(string presentAreaKey, long ownerId) { string cacheKey = GetCacheKey_CustomStyleEntity(presentAreaKey, ownerId); CustomStyleEntity result = cacheService.GetFromFirstLevel <CustomStyleEntity>(cacheKey); if (result == null) { PetaPocoDatabase database = CreateDAO(); var sql = PetaPoco.Sql.Builder; sql.Select("*").From("spb_CustomStyles").Where("PresentAreaKey=@0", presentAreaKey).Where("OwnerId=@0", ownerId); result = database.FirstOrDefault <CustomStyleEntity>(sql); if (result != null) { result.CustomStyle = Deserialize(result.SerializedCustomStyle); } cacheService.Add(cacheKey, result, CachingExpirationType.UsualSingleObject); } return(result); }
/// <summary> /// 在用户动态收件箱追溯OwnerId的动态 /// </summary> /// <remarks> /// 关注用户/加入群组、取消屏蔽用户/取消屏蔽群组时使用 /// </remarks> /// <param name="userId">UserId</param> /// <param name="ownerId">动态拥有者Id</param> /// <param name="ownerType">动态拥有者类型</param> /// <param name="traceBackNumber">追溯OwnerId的动态数</param> public void TraceBackInboxAboutOwner(long userId, long ownerId, int ownerType, int traceBackNumber) { //缓存需要即时 //1、仅追溯traceBackNumber条非私有的动态; PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Sql sql = Sql.Builder .Select("ActivityId") .From("tn_Activities") .Where("OwnerId=@0", ownerId) .Where("OwnerType=@0", ownerType) .Where("IsPrivate=0") .OrderBy("LastModified desc"); IEnumerable <object> activityIds = dao.FetchTopPrimaryKeys <Activity>(traceBackNumber, sql); List <Sql> sqls = new List <Sql>(); foreach (var activityId in activityIds.Cast <long>()) { sql = Sql.Builder.Select("count(*)").From("tn_ActivityUserInbox").Where("ActivityId = @0", activityId).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); } int affectCount = dao.Execute(sqls); dao.CloseSharedConnection(); if (affectCount > 0) { RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); } }
/// <summary> /// 若存在OwnerType+TenantTypeId+ReferenceId的动态记录,则更新其SourceId及DateCreated、LastModified /// </summary> /// <param name="ownerType"></param> /// <param name="tenantTypeId"></param> /// <param name="referenceId"></param> /// <param name="sourceId"></param> /// <returns>true-更新成功,false-不存在OwnerType+TenantTypeId+ReferenceId的动态记录</returns> public bool UpdateSource(int ownerType, string tenantTypeId, long referenceId, long sourceId) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); Sql sql; //查找OwnerType+TenantTypeId+ReferenceId的动态记录 sql = Sql.Builder.Select("ActivityId").From("tn_Activities").Where("OwnerType=@0", ownerType).Where("TenantTypeId=@0", tenantTypeId).Where("ReferenceId=@0", referenceId); long activityId = dao.FirstOrDefault <long>(sql); int affectCount = 0; if (activityId > 0) { sql = Sql.Builder.Append("Update tn_Activities Set SourceId=@0,DateCreated=@1,LastModified=@2", sourceId, DateTime.UtcNow, DateTime.UtcNow) .Where("ActivityId=@0", activityId); affectCount = dao.Execute(sql); } dao.CloseSharedConnection(); if (affectCount <= 0 || activityId <= 0) { return(false); } Activity activity = Get(activityId); if (activity == null) { return(false); } activity.SourceId = sourceId; activity.DateCreated = DateTime.UtcNow; activity.LastModified = DateTime.UtcNow; this.OnUpdated(activity); return(true); }
/// <summary> /// 对操作对象进行顶操作 /// </summary> /// <param name="objectId">操作对象Id</param> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="userId">操作用户Id</param> /// <param name="mode">顶踩的操作模式</param> /// <returns>是否操作成功,Ture-成功</returns> public bool Support(long objectId, string tenantTypeId, long userId, AttitudeMode mode = AttitudeMode.Bidirection) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); AttitudeSettings attitudeSettings = attitudeSettingsManager.Get(); AttitudeOnlySupportSettings attitudeOnlySupportSettings = attitudeOnlySupportSettingsManager.Get(); bool returnValue = false; var sql = Sql.Builder; sql.Where("ObjectId = @0 and TenantTypeId = @1", objectId, tenantTypeId); Attitude entity = dao.FirstOrDefault <Attitude>(sql); if (entity == null) { entity = Attitude.New(); entity.TenantTypeId = tenantTypeId; entity.ObjectId = objectId; dao.Insert(entity); } #region 判断是否双向 if (AttitudeMode.Bidirection == mode) { #region 判断是否可修改 //判断是否可修改 if (attitudeSettings.IsModify) { #region 判断是否取消 //判断是否可取消 if (attitudeSettings.EnableCancel) { IList <Sql> sqls = new List <Sql>(); int affectCount = 0; //判断是否有过操作记录 bool?isSupport = IsSupport(objectId, tenantTypeId, userId); switch (isSupport) { //顶过记录 case true: returnValue = false; break; //踩过记录 case false: sqls.Add(Sql.Builder.Append(@"DELETE FROM tn_AttitudeRecords WHERE (UserId = @0) AND (TenantTypeId = @1) and (ObjectId = @2)" , userId, tenantTypeId, objectId)); sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET OpposeCount = OpposeCount - 1, Comprehensive = Comprehensive - @0 WHERE (ObjectId = @1) AND (TenantTypeId = @2)" , attitudeSettings.OpposeWeights, objectId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); //清除顶踩状态缓存 cacheService.Remove(GetCacheKey_IsSupport(objectId, tenantTypeId, userId)); } returnValue = true; } else { returnValue = false; } break; default: sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0 WHERE (ObjectId = @1) AND (TenantTypeId = @2)" , attitudeSettings.SupportWeights, objectId, tenantTypeId)); sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)" , objectId, userId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject); } returnValue = true; } else { returnValue = false; } break; } } else { IList <Sql> sqls = new List <Sql>(); int affectCount = 0; //判断是否有过操作记录 bool?isSupport = IsSupport(objectId, tenantTypeId, userId); switch (isSupport) { //已经顶过 case true: returnValue = false; break; //已经踩过 case false: sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET OpposeCount = OpposeCount - 1 ,SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0 - @1 WHERE (ObjectId = @2) AND (TenantTypeId = @3)" , attitudeSettings.SupportWeights, attitudeSettings.OpposeWeights, objectId, tenantTypeId )); sqls.Add(Sql.Builder.Append(@"UPDATE tn_AttitudeRecords SET IsSupport =1 WHERE(ObjectId = @0) AND (UserId = @1) AND (TenantTypeId = @2)" , objectId, userId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject); } returnValue = true; } else { returnValue = false; } break; default: //没有操作过的记录就 添加记录 sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0 WHERE (ObjectId = @1) AND (TenantTypeId = @2)" , attitudeSettings.SupportWeights, objectId, tenantTypeId)); sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)" , objectId, userId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject); } returnValue = true; } else { returnValue = false; } break; } } #endregion 判断是否取消 } else { //先判断一下是否有顶踩记录 IList <Sql> sqls = new List <Sql>(); int affectCount = 0; //判断是否有过操作记录 bool?isSupport = IsSupport(objectId, tenantTypeId, userId); if (isSupport == null) { //没有记录就 添加记录 sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0 WHERE (ObjectId = @2) AND (TenantTypeId = @3)" , attitudeSettings.SupportWeights, attitudeSettings.OpposeWeights, objectId, tenantTypeId )); sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)" , objectId, userId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject); } returnValue = true; } else { returnValue = false; } } } #endregion 判断是否可修改 } else { //单向 #region 是否可取消操作 //是否可取消操作 if (attitudeOnlySupportSettings.IsCancel) { IList <Sql> sqls = new List <Sql>(); int affectCount = 0; //判断是否有过操作记录 bool?isSupport = IsSupport(objectId, tenantTypeId, userId); switch (isSupport) { //顶过记录 case true: sqls.Add(Sql.Builder.Append(@"DELETE FROM tn_AttitudeRecords WHERE UserId = @0 and TenantTypeId = @1 and ObjectId = @2" , userId, tenantTypeId, objectId)); sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount-1, Comprehensive = SupportCount-1 WHERE ObjectId = @0 and TenantTypeId = @1", objectId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); //清除顶踩状态缓存 cacheService.Remove(GetCacheKey_IsSupport(objectId, tenantTypeId, userId)); } returnValue = true; } else { returnValue = false; } break; default: sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount+1, Comprehensive = SupportCount+1 WHERE (ObjectId = @0) AND (TenantTypeId = @1)" , objectId, tenantTypeId)); sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)" , objectId, userId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject); } returnValue = true; } else { returnValue = false; } break; } } else { //不可取消的单向操作 IList <Sql> sqls = new List <Sql>(); int affectCount = 0; //判断是否有过操作记录 bool?isSupport = IsSupport(objectId, tenantTypeId, userId); if (isSupport == null) { sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount + 1, Comprehensive = SupportCount + 1 WHERE (ObjectId = @0) AND (TenantTypeId = @1)" , objectId, tenantTypeId)); sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)" , objectId, userId, tenantTypeId)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存 Get(objectId, tenantTypeId, false); cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject); } returnValue = true; } else { returnValue = false; } } } #endregion 是否可取消操作 } #endregion 判断是否双向 dao.CloseSharedConnection(); return(returnValue); }
/// <summary> /// 刷新数据库 /// </summary> /// <remarks> /// 通过Task调用 /// </remarks> public void Refresh(ConcurrentDictionary <string, OnlineUser> OnlineUsersForProcess) { //设计要点: //1、把OnlineUsersForProcess更新到数据库,如果UserName存在则更新LastActivityTime,否则添加新在线用户 // 可参考CountRepository.ExecQueue() //2、把超期未活动的用户移除 //3、更新tn_OnlineUserStatistics : 每日一条记录保留最高记录(依据UserCount) PetaPocoDatabase dao = CreateDAO(); try { dao.OpenSharedConnection(); foreach (string key in OnlineUsersForProcess.Keys) { OnlineUser onlineUserInDic = null; OnlineUsersForProcess.TryRemove(key, out onlineUserInDic); var sql = Sql.Builder; sql.Select("*") .From("tn_OnlineUsers") .Where("UserName = @0", key); OnlineUser onlineUserInDB = dao.FirstOrDefault <OnlineUser>(sql); if (onlineUserInDB == null) { dao.Insert(onlineUserInDic); } else { sql = Sql.Builder; sql.Append("Update tn_OnlineUsers set LastActivityTime=@0,LastAction=@1", onlineUserInDic.LastActivityTime, onlineUserInDic.LastAction) .Where("UserName=@0", key); dao.Execute(sql); } dao.Execute(Sql.Builder.Append("Update tn_Users set LastActivityTime=@0,LastAction=@1,IpLastActivity=@2", onlineUserInDic.LastActivityTime, onlineUserInDic.LastAction, onlineUserInDic.Ip)); } //移除超期未活动的用户 UserSettings userSetting = DIContainer.Resolve <IUserSettingsManager>().Get(); var deleteSql = Sql.Builder; deleteSql.Append("delete from tn_OnlineUsers") .Where("LastActivityTime < @0", DateTime.UtcNow.AddMinutes(-userSetting.UserOnlineTimeWindow)); dao.Execute(deleteSql); //更新tn_OnlineUserStatistics : 每日一条记录保留最高记录(依据UserCount) int loggedUserCount = GetLoggedUsers().Count(); int anonymousUserCount = GetAnonymousUsers().Count(); int total = loggedUserCount + anonymousUserCount; var updateSql = Sql.Builder; updateSql.Append("update tn_OnlineUserStatistics set LoggedUserCount=@0,AnonymousCount=@1,UserCount = @2", loggedUserCount, anonymousUserCount, total) .Where("UserCount < @0", total) .Where("DateCreated>@0", new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day)); int num = dao.Execute(updateSql); if (num == 0) { var selectSql = Sql.Builder; selectSql.Select("*") .From("tn_OnlineUserStatistics") .Where("DateCreated>@0", new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day)); OnlineUserStatistic statistic = dao.FirstOrDefault <OnlineUserStatistic>(selectSql); if (statistic == null) { OnlineUserStatistic userStatistic = OnlineUserStatistic.New(); userStatistic.DateCreated = DateTime.UtcNow; userStatistic.LoggedUserCount = loggedUserCount; userStatistic.AnonymousCount = anonymousUserCount; userStatistic.UserCount = total; dao.Insert(userStatistic); } } } finally { dao.CloseSharedConnection(); } }
/// <summary> /// 把实体entity添加到数据库 /// </summary> /// <param name="entity">待创建实体</param> /// <returns>实体主键</returns> public override object Insert(Message entity) { var sql = Sql.Builder; IList <Sql> sqls = new List <Sql>(); long senderSessionId = 0, receiverSessionId = 0; MessageSessionRepository messageSessionRepository = new MessageSessionRepository(); PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); object id = base.Insert(entity); sql.Append("update tn_MessageSessions") .Append("set LastMessageId = @0,MessageCount = MessageCount + 1,LastModified = @1", id, entity.DateCreated) .Where("UserId = @0", entity.SenderUserId) .Where("OtherUserId = @0", entity.ReceiverUserId); //判断发件人的会话是否存在 if (dao.Execute(sql) == 0) { sql = Sql.Builder; sql.Append(@"insert into tn_MessageSessions(UserId,OtherUserId,LastMessageId,MessageType,MessageCount,LastModified) values (@0,@1,@2,@3,1,@4)", entity.SenderUserId, entity.ReceiverUserId, id, (int)entity.MessageType, entity.DateCreated); dao.Execute(sql); } //获取发件人的会话Id sql = Sql.Builder; sql.Select("SessionId") .From("tn_MessageSessions") .Where("UserId = @0", entity.SenderUserId) .Where("OtherUserId = @0", entity.ReceiverUserId); senderSessionId = dao.FirstOrDefault <long>(sql); //判断收件人的会话是否存在 sql = Sql.Builder; sql.Append("update tn_MessageSessions") .Append("set LastMessageId = @0,MessageCount = MessageCount + 1,UnreadMessageCount = UnreadMessageCount + 1,LastModified = @1", id, entity.DateCreated) .Where("UserId = @0", entity.ReceiverUserId) .Where("OtherUserId = @0", entity.SenderUserId); if (dao.Execute(sql) == 0) { sql = Sql.Builder; sql.Append("insert into tn_MessageSessions(UserId,OtherUserId,LastMessageId,MessageType,MessageCount,UnreadMessageCount,LastModified) values (@0,@1,@2,@3,1,1,@4)", entity.ReceiverUserId, entity.SenderUserId, id, (int)entity.MessageType, entity.DateCreated); dao.Execute(sql); } //获取收件人的会话Id sql = Sql.Builder; sql.Select("SessionId") .From("tn_MessageSessions") .Where("UserId = @0", entity.ReceiverUserId) .Where("OtherUserId = @0", entity.SenderUserId); receiverSessionId = dao.FirstOrDefault <long>(sql); //添加会话与私信的关系 sqls.Add(Sql.Builder.Append("insert into tn_MessagesInSessions (SessionId,MessageId) values (@0,@1)", senderSessionId, id)); sqls.Add(Sql.Builder.Append("insert into tn_MessagesInSessions (SessionId,MessageId) values (@0,@1)", receiverSessionId, id)); dao.Execute(sqls); dao.CloseSharedConnection(); #region 缓存处理 if (RealTimeCacheHelper.EnableCache) { RealTimeCacheHelper.IncreaseAreaVersion("UserId", entity.SenderUserId); RealTimeCacheHelper.IncreaseAreaVersion("UserId", entity.ReceiverUserId); var realTimeCacheHelper = EntityData.ForType(typeof(MessageInSession)).RealTimeCacheHelper; realTimeCacheHelper.IncreaseAreaVersion("SessionId", senderSessionId); realTimeCacheHelper.IncreaseAreaVersion("SessionId", receiverSessionId); realTimeCacheHelper = EntityData.ForType(typeof(MessageSession)).RealTimeCacheHelper; realTimeCacheHelper.IncreaseAreaVersion("UserId", entity.SenderUserId); realTimeCacheHelper.IncreaseAreaVersion("UserId", entity.ReceiverUserId); realTimeCacheHelper.IncreaseEntityCacheVersion(senderSessionId); realTimeCacheHelper.IncreaseEntityCacheVersion(receiverSessionId); string cacheKey = realTimeCacheHelper.GetCacheKeyOfEntity(senderSessionId); MessageSession senderSession = cacheService.Get <MessageSession>(cacheKey); if (senderSession != null) { senderSession.LastMessageId = entity.MessageId; senderSession.LastModified = entity.DateCreated; senderSession.MessageCount++; } cacheKey = realTimeCacheHelper.GetCacheKeyOfEntity(receiverSessionId); MessageSession receiverSession = cacheService.Get <MessageSession>(cacheKey); if (receiverSession != null) { receiverSession.LastMessageId = entity.MessageId; receiverSession.LastModified = entity.DateCreated; receiverSession.MessageCount++; receiverSession.UnreadMessageCount++; } cacheKey = GetCacheKey_UnreadCount(entity.ReceiverUserId); int?count = cacheService.Get(cacheKey) as int?; count = count ?? 0; count++; cacheService.Set(cacheKey, count, CachingExpirationType.SingleObject); } #endregion 缓存处理 return(id); }
/// <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="itemIds">内容项Id</param> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">拥有者Id</param> /// <param name="tagName">标签名</param> public int AddItemsToTag(IEnumerable <long> itemIds, string tenantTypeId, long ownerId, string tagName) { PetaPocoDatabase dao = CreateDAO(); IList <Sql> sqls = new List <Sql>(); var sql = Sql.Builder; dao.OpenSharedConnection(); //创建标签 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) values(@0,@1,@2)", 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) values(@0,@1,@2)", tenantTypeId, tagName, ownerId)); } int affectCount = 0, itemCount = 0; foreach (var itemId in itemIds) { if (itemId <= 0) { continue; } //创建标签与内容项的关联 sqls.Add(Sql.Builder.Append("insert into tn_ItemsInTags (TagName,TagInOwnerId,ItemId,TenantTypeId) select @0,Id,@1,@2 from tn_TagsInOwners", tagName, itemId, tenantTypeId) .Where("TenantTypeId = @0", tenantTypeId) .Where("OwnerId = @0", ownerId) .Where("TagName = @0", tagName)); itemCount++; } //增加标签相关统计 sqls.Add(Sql.Builder.Append("update tn_Tags Set ItemCount = ItemCount + @2,OwnerCount = OwnerCount + 1 where TenantTypeId = @0 and TagName = @1", tenantTypeId, tagName, itemCount)); //增加拥有者标签内容项统计 sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount + @3 where TenantTypeId = @0 and TagName = @1 and OwnerId = @2", tenantTypeId, tagName, ownerId, itemCount)); //通过事务来控制多条语句执行时的一致性 using (var transaction = dao.GetTransaction()) { dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { foreach (var itemId in itemIds) { if (itemId <= 0) { continue; } RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId); } sql = Sql.Builder; sql.Select("*") .From("tn_TagsInOwners") .Where("TenantTypeId = @0", tenantTypeId) .Where("TagName = @0", tagName) .Where("OwnerId = @0", ownerId); tagInOwner = dao.First <TagInOwner>(sql); EntityData entityData = EntityData.ForType(typeof(TagInOwner)); if (tagInOwner != null) { entityData.RealTimeCacheHelper.IncreaseEntityCacheVersion(tagInOwner.Id); } entityData.RealTimeCacheHelper.IncreaseAreaVersion("OwnerId", ownerId); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 对操作对象进行星级评价操作 /// </summary> /// <param name="objectId">操作对象Id</param> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="userId">用户的UserId</param> /// <param name="rateNumber">星级类型</param> /// <param name="ownerId">拥有者Id</param> public bool Rated(long objectId, string tenantTypeId, long userId, int rateNumber, long ownerId = 0) { RatingSettings ratingSettings = ratingSettingsManager.Get(); //执行Sql语句的集合 List <Sql> sqls = new List <Sql>(); //判断输入的等级数是否符合标准 if (rateNumber < 1 || rateNumber > 5) { return(false); } PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); //先判断是否存在评价信息 var sql = Sql.Builder; sql.Select("Count(*)") .From("tn_Ratings") .Where("ObjectId = @0 AND TenantTypeId = @1", objectId, tenantTypeId); if (ownerId > 0) { sql.Where("OwnerId = @0", ownerId); } int affectCount = dao.ExecuteScalar <int>(sql); //如果记录为0则不存在则先创建评价信息 if (affectCount == 0) { //创建 评价信息 sqls.Add(Sql.Builder.Append("INSERT INTO tn_Ratings (ObjectId, TenantTypeId, OwnerId, RateCount, Comprehensive , RateSum ) VALUES (@0, @1, @2,1,@3,@4)", objectId, tenantTypeId, ownerId, rateNumber, rateNumber)); //创建时直接添加新记录数据 for (int i = 1; i <= 5; i++) { if (rateNumber == i) { //如果等级是用户所选择的等级则直接插入数据 并且创建记录 sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingGrades (ObjectId, TenantTypeId, RateNumber, RateCount) VALUES (@0,@1,@2,1)", objectId, tenantTypeId, i)); sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingRecords (ObjectId, TenantTypeId, RateNumber, UserId, DateCreated) VALUES (@0,@1,@2,@3,@4)", objectId, tenantTypeId, i, userId, DateTime.UtcNow)); continue; } sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingGrades (ObjectId, TenantTypeId, RateNumber, RateCount) VALUES (@0,@1,@2,@3)", objectId, tenantTypeId, i, 0)); } } else { //判断是都有过评价记录 sql = Sql.Builder; sql.Select("*") .From("tn_RatingRecords") .Where("ObjectId = @0 AND TenantTypeId = @1 AND UserId = @2", objectId, tenantTypeId, userId); //获取评价的记录 RatingRecord ratingRecord = dao.FirstOrDefault <RatingRecord>(sql); if (ratingRecord == null) { sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingRecords (ObjectId, TenantTypeId, RateNumber, UserId, DateCreated) VALUES (@0,@1,@2,@3,@4)", objectId, tenantTypeId, rateNumber, userId, DateTime.UtcNow)); //更新信息评价数据 sqls.Add(Sql.Builder.Append("UPDATE tn_Ratings SET RateCount = RateCount + 1, Comprehensive=(RateSum + @0)/(RateCount + 1.0) , RateSum=RateSum + @0 where ObjectId = @1 and TenantTypeId = @2 and OwnerId = @3", rateNumber, objectId, tenantTypeId, ownerId)); //更新评价等级统计 sqls.Add(Sql.Builder.Append("UPDATE tn_RatingGrades SET RateCount = RateCount + 1 where ObjectId = @0 and TenantTypeId = @1 and RateNumber = @2", objectId, tenantTypeId, rateNumber)); } else { //用户 再次评价 先判断是否可修改 if (ratingSettings.IsModify) { //先检测是否是之前选过的等级 if (ratingRecord.RateNumber != rateNumber) { sqls.Add(Sql.Builder.Append("UPDATE tn_RatingRecords SET RateNumber = @0 WHERE ObjectId = @1 AND TenantTypeId = @2 AND UserId = @3", rateNumber, objectId, tenantTypeId, userId)); //先更新之前的等级统计 sqls.Add(Sql.Builder.Append("UPDATE tn_RatingGrades SET RateCount = RateCount - 1 where ObjectId = @0 and TenantTypeId = @1 and RateNumber = @2", objectId, tenantTypeId, ratingRecord.RateNumber)); //然后更新现在的最新等级统计 sqls.Add(Sql.Builder.Append("UPDATE tn_RatingGrades SET RateCount = RateCount + 1 where ObjectId = @0 and TenantTypeId = @1 and RateNumber = @2", objectId, tenantTypeId, rateNumber)); //之后更新评价信息表 sqls.Add(Sql.Builder.Append("UPDATE tn_Ratings SET Comprehensive = (RateSum + @0 - @1)/RateCount , RateSum=RateSum + @0 - @1 where ObjectId=@2 and TenantTypeId = @3 and OwnerId = @4", (float)rateNumber, (float)ratingRecord.RateNumber, objectId, tenantTypeId, ownerId)); } } } } if (sqls == null) { return(false); } using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { if (RealTimeCacheHelper.EnableCache) { EntityData.ForType(typeof(RatingRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); EntityData.ForType(typeof(RatingGrade)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); } } else { return(false); } dao.CloseSharedConnection(); return(true); }
/// <summary> /// 为内容项批量设置标签 /// </summary> /// <param name="tagNames">标签名称集合</param> /// <param name="tenantTypeId">租户类型Id</param> /// <param name="ownerId">拥有者Id</param> /// <param name="itemId">内容项Id</param> public int AddTagsToItem(string[] tagNames, string tenantTypeId, long ownerId, long itemId) { int affectCount = 0; PetaPocoDatabase dao = CreateDAO(); 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) values (@0,@1,@2)", tenantTypeId, tagName, DateTime.UtcNow)); } //增加标签相关统计 sqls.Add(Sql.Builder.Append("update tn_Tags Set ItemCount = ItemCount + 1 where TenantTypeId = @0 and TagName = @1", tenantTypeId, tagName)); //创建标签与用户的关联 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("update tn_Tags Set OwnerCount = OwnerCount + 1 where TenantTypeId = @0 and TagName = @1", tenantTypeId, tagName)); dao.Execute(Sql.Builder.Append("insert into tn_TagsInOwners (TenantTypeId,TagName,OwnerId) values (@0,@1,@2)", tenantTypeId, tagName, ownerId)); tagInOwner = dao.FirstOrDefault <TagInOwner>(sql); } else { //增加拥有者标签内容项统计 sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount + 1 where TenantTypeId = @0 and TagName = @1 and OwnerId = @2", tenantTypeId, tagName, ownerId)); } sql = Sql.Builder; sql.From("tn_ItemsInTags") .Where("TagName = @0", tagName) .Where("TagInOwnerId = @0", tagInOwner.Id) .Where("ItemId = @0", itemId); var itemInTag = dao.FirstOrDefault <ItemInTag>(sql); if (itemInTag == null) { //创建标签与内容项的关联 sqls.Add(Sql.Builder.Append("insert into tn_ItemsInTags (TagName,TagInOwnerId,ItemId,tenantTypeId) select @0,Id,@1,@2 from tn_TagsInOwners", tagName, itemId, tenantTypeId) .Where("TenantTypeId = @0", tenantTypeId) .Where("TagName = @0", tagName) .Where("OwnerId = @0", ownerId)); } //通过事务来控制多条语句执行时的一致性 using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { sql = Sql.Builder; sql.From("tn_TagsInOwners") .Where("TenantTypeId = @0", tenantTypeId) .Where("TagName = @0", tagName) .Where("OwnerId = @0", ownerId); tagInOwner = dao.First <TagInOwner>(sql); if (tagInOwner != null) { EntityData.ForType(typeof(TagInOwner)).RealTimeCacheHelper.IncreaseEntityCacheVersion(tagInOwner.Id); } } } if (tagNames.Length > 0) { EntityData.ForType(typeof(TagInOwner)).RealTimeCacheHelper.IncreaseAreaVersion("OwnerId", ownerId); RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId); foreach (var tagName in tagNames) { if (string.IsNullOrEmpty(tagName)) { continue; } RealTimeCacheHelper.IncreaseAreaVersion("TagName", tagName); } } dao.CloseSharedConnection(); return(affectCount); }