/// <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> /// <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="userId">用户Id</param> public void ClearSessionsFromUser(long userId) { PetaPocoDatabase dao = CreateDAO(); dao.OpenSharedConnection(); List <Sql> sqls = new List <Sql>(); var sql = Sql.Builder; sql.Select("SessionId") .From("tn_MessageSessions") .Where("UserId = @0", userId); IEnumerable <object> sessionids = dao.FetchFirstColumn(sql); if (sessionids.Count() > 0) { //清除私信实体脚本 sqls.Add(Sql.Builder.Append("delete from tn_Messages where exists(") .Append("select 1 from tn_MessagesInSessions MIS where tn_Messages.MessageId = MIS.MessageId and exists(") .Append("select 1 from tn_MessageSessions MS where MS.SessionId = MIS.SessionId and MS.UserId = @0 and not exists(", userId) .Append("select 1 from tn_MessageSessions SUBMS Where SUBMS.UserId = MS.OtherUserId and SUBMS.OtherUserId = MS.UserId)))")); //清除私信与会话关联脚本 sqls.Add(Sql.Builder.Append("delete from tn_MessagesInSessions where exists(") .Append("select 1 from tn_MessageSessions MS where MS.SessionId = tn_MessagesInSessions.SessionId and MS.UserId = @0)", userId)); //清除私信会话 sqls.Add(Sql.Builder.Append("delete from tn_MessageSessions where UserId = @0", userId)); using (var transaction = dao.GetTransaction()) { dao.Execute(sqls); transaction.Complete(); } } dao.CloseSharedConnection(); #region 更新缓存 RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId); sessionids.ToList().ForEach((n) => { RealTimeCacheHelper.IncreaseEntityCacheVersion(n); }); #endregion }
/// <summary> /// 删除用户角色 /// </summary> /// <param name="role">待删除的用户角色</param> /// <returns>删除成功返回1,否则返回0</returns> public override int Delete(Role role) { PetaPocoDatabase dao = CreateDAO(); if (role == null) { return(0); } var sql = Sql.Builder; sql.Select("Id") .From("tn_UsersInRoles") .Where("RoleName = @0", role.RoleName); IEnumerable <long> userInRoleIds = dao.FetchFirstColumn(sql).Cast <long>(); var sqlUserInRolesDelete = Sql.Builder; var sqlRolesDelete = Sql.Builder; int count; sqlUserInRolesDelete.Append("Delete from tn_UsersInRoles where RoleName = @0", role.RoleName); sqlRolesDelete.Append("Delete from tn_Roles where RoleName =@0", role.RoleName); base.OnDeleted(Get(role.RoleName)); using (var scope = dao.GetTransaction()) { dao.Execute(sqlUserInRolesDelete); count = dao.Execute(sqlRolesDelete); scope.Complete(); } OnDeleted(role); ICacheService cacheService = DIContainer.Resolve <ICacheService>(); Caching.RealTimeCacheHelper userInRoleCacheHelper = EntityData.ForType(typeof(UserInRole)).RealTimeCacheHelper; foreach (var userInRoleId in userInRoleIds) { UserInRole userInRole = cacheService.Get <UserInRole>(userInRoleCacheHelper.GetCacheKeyOfEntity(userInRoleId)); if (userInRole != null) { userInRoleCacheHelper.MarkDeletion(userInRole); } } return(count); }
/// <summary> /// 删除标签与拥有者关系 /// </summary> /// <param name="entity">标签与拥有者关系实体</param> /// <returns>影响行数</returns> public override int Delete(TagInOwner entity) { PetaPocoDatabase dao = CreateDAO(); IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>(); int affectCount = 0; dao.OpenSharedConnection(); var sql_GetItemIds = PetaPoco.Sql.Builder; sql_GetItemIds.Select("ItemId") .From("tn_ItemsInTags") .Where("TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName); IEnumerable <object> itemIds = dao.FetchFirstColumn(sql_GetItemIds); sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInOwners where id=@0", entity.Id)); sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_ItemsInTags where TagInOwnerId = @0 and TagName = @1", entity.Id, entity.TagName)); using (var transaction = dao.GetTransaction()) { affectCount = dao.Execute(sqls); transaction.Complete(); } if (affectCount > 0) { OnDeleted(entity); foreach (var itemId in itemIds) { EntityData.ForType(typeof(ItemInTag)).RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId); } EntityData.ForType(typeof(ItemInTag)).RealTimeCacheHelper.IncreaseAreaVersion("TagName", entity.TagName); } dao.CloseSharedConnection(); return(affectCount); }
/// <summary> /// 删除私信会话 /// </summary> /// <param name="entity">待删除实体</param> /// <returns>操作后影响行数</returns> public override int Delete(MessageSession entity) { PetaPocoDatabase dao = CreateDAO(); List <Sql> sqls = new List <Sql>(); int affectCount = 0; dao.OpenSharedConnection(); var sql = Sql.Builder; sql.Select("SessionId") .From("tn_MessageSessions") .Where("UserId = @0", entity.UserId) .Where("OtherUserId = @0", entity.OtherUserId); affectCount = dao.Execute(sql); using (var transaction = dao.GetTransaction()) { if (affectCount <= 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 MIS.SessionId = @0)", entity.SessionId)); } //清除私信与会话关联脚本 sqls.Add(Sql.Builder.Append("delete from tn_MessagesInSessions where SessionId = @0", entity.SessionId)); dao.Execute(sqls); affectCount = base.Delete(entity); transaction.Complete(); } 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="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="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); }
/// <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> /// 把fromCategoryId移动到toCategoryId /// </summary> /// <remarks> /// 将一个分类移动到另一个分类,并作为另一个分类的子分类 /// </remarks> /// <param name="fromCategoryId">被移动类别</param> /// <param name="toCategoryId">目标类别</param> public void Move(long fromCategoryId, long toCategoryId) { //初始分类实体 T fromCategory = Get(fromCategoryId); //目标分类实体 T toCategory = Get(toCategoryId); IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>(); //1 修改本分类的ParentID var sql_UpdateFromCategory = PetaPoco.Sql.Builder; sql_UpdateFromCategory.Append("update tn_Categories set ParentId = @0", toCategoryId).Where("CategoryId=@0", fromCategoryId); sqls.Add(sql_UpdateFromCategory); //2 更新本分类及所有后代分类的Depth //获得所有fromCategory的后代Category IList <T> fromChildCategories = new List <T>(); IEnumerable <T> ownerCategories = GetOwnerCategories(toCategory.OwnerId, toCategory.TenantTypeId); this.RecurseGetChildren(fromCategory, fromChildCategories, ownerCategories.ToList()); //更新fromCategory的Depth int fromCategoryDepth = toCategory.Depth + 1; var sql_UpdateFromCategoryDepth = PetaPoco.Sql.Builder; sql_UpdateFromCategoryDepth.Append("update tn_Categories set Depth = @0", fromCategoryDepth).Where("CategoryId=@0", fromCategory.CategoryId); sqls.Add(sql_UpdateFromCategoryDepth); //循环更新fromCategory每一个后代Category的Depth foreach (T fromChildCategory in fromChildCategories) { int fromChildCategoryDepth = fromCategoryDepth + fromChildCategory.Depth - fromCategory.Depth; fromChildCategory.Depth = fromChildCategoryDepth; var sql_UpdateFromChildCategoryDepth = PetaPoco.Sql.Builder; sql_UpdateFromChildCategoryDepth.Append("update tn_Categories set Depth = @0", fromChildCategoryDepth).Where("CategoryId=@0", fromChildCategory.CategoryId); sqls.Add(sql_UpdateFromChildCategoryDepth); } //3 toCategory的ChildCount+1 var sql_UpdateToCategory = PetaPoco.Sql.Builder; sql_UpdateToCategory.Append("update tn_Categories set ChildCount = ChildCount+1 ").Where("CategoryId=@0", toCategoryId); sqls.Add(sql_UpdateToCategory); //4 fromCategory的Parent的ChildCount-1 var sql_UpdateFromParentCategory = PetaPoco.Sql.Builder; if (fromCategory.ParentId > 0) { sql_UpdateFromParentCategory.Append("update tn_Categories set ChildCount = ChildCount-1 ").Where("ChildCount > 0 and CategoryId = @0", fromCategory.ParentId); sqls.Add(sql_UpdateFromParentCategory); } //影响的行数 int effectLineCount = 0; PetaPocoDatabase dao = CreateDAO(); //使用事务 using (var scope = dao.GetTransaction()) { effectLineCount = dao.Execute(sqls); //事务结束 scope.Complete(); } #region 处理缓存 if (effectLineCount > 0) { //单体缓存fromCategory,toCategory,fromParentCategory RealTimeCacheHelper.IncreaseEntityCacheVersion(fromCategoryId); RealTimeCacheHelper.IncreaseEntityCacheVersion(toCategoryId); if (fromCategory.ParentId > 0) { RealTimeCacheHelper.IncreaseEntityCacheVersion(fromCategory.ParentId); } fromCategory.ParentId = toCategory.CategoryId; fromCategory.Depth = toCategory.Depth + 1; //列表缓存 RealTimeCacheHelper.IncreaseAreaVersion("OwnerId", toCategory.OwnerId); //该用户的缓存 RealTimeCacheHelper.IncreaseAreaVersion("ParentId", toCategory.ParentId); //父级分类列表缓存 RealTimeCacheHelper.IncreaseAreaVersion("ParentId", toCategory.CategoryId); //本级分类列表缓存 RealTimeCacheHelper.IncreaseAreaVersion("ParentId", fromCategory.CategoryId); if (ownerCategories.Count() > 1) { foreach (var ownerCategory in ownerCategories) { RealTimeCacheHelper.IncreaseEntityCacheVersion(ownerCategory.CategoryId); } } //全局缓存 RealTimeCacheHelper.IncreaseGlobalVersion(); //处理内容项对应的缓存 //toCategory以及父级 List <long> toCategoryParentsIds = new List <long>(); RecurseGetParents(toCategoryId, toCategoryParentsIds); toCategoryParentsIds.Add(toCategoryId); foreach (long categoryId in toCategoryParentsIds) { EntityData.ForType(typeof(ItemInCategory)).RealTimeCacheHelper.IncreaseAreaVersion("CategoryId", toCategoryId); } //fromCategory父级 List <long> fromCategoryParentsIds = new List <long>(); RecurseGetParents(fromCategoryId, fromCategoryParentsIds); foreach (long categoryId in fromCategoryParentsIds) { EntityData.ForType(typeof(ItemInCategory)).RealTimeCacheHelper.IncreaseAreaVersion("CategoryId", toCategoryId); } } #endregion }
/// <summary> /// 从fromCategoryId并入到toCategoryId /// </summary> /// <remarks> /// 例如:将分类fromCategoryId合并到分类toCategoryId,那么fromCategoryId分类下的所有子分类和实体全部归到toCategoryId分类,同时删除fromCategoryId分类 /// </remarks> /// <param name="fromCategoryId">源类别</param> /// <param name="toCategoryId">目标类别</param> public void Merge(long fromCategoryId, long toCategoryId) { //为处理缓存做准备 T fromCategory = Get(fromCategoryId); #region 组装sql //1 将所有的子分类合并到toCategoryId下 var sql_ChildCategoryUpdate = PetaPoco.Sql.Builder; sql_ChildCategoryUpdate.Append("update tn_Categories set ParentId = @0", toCategoryId).Where("ParentId = @0", fromCategoryId); //2 将fromCategoryId下的所有直属实体类合并到toCategoryId下 var sql_ChildItemsUpdate = PetaPoco.Sql.Builder; sql_ChildItemsUpdate.Append("update tn_ItemsInCategories set CategoryId = @0", toCategoryId).Where("CategoryId = @0", fromCategoryId); //3 更新后代分类深度 //获得所有fromCategory的后代Category T toCategory = Get(toCategoryId); IList <T> fromChildCategories = new List <T>(); IEnumerable <T> ownerCategories = GetOwnerCategories(toCategory.OwnerId, toCategory.TenantTypeId); this.RecurseGetChildren(fromCategory, fromChildCategories, ownerCategories.ToList()); //循环更新fromCategory每一个后代Category的Depth IList <Sql> sql_UpdateFromChildCategoryDepths = new List <Sql>(); foreach (T fromChildCategory in fromChildCategories) { int fromChildCategoryDepth = toCategory.Depth + fromChildCategory.Depth - fromCategory.Depth; fromChildCategory.Depth = fromChildCategoryDepth; sql_UpdateFromChildCategoryDepths.Add(Sql.Builder.Append("update tn_Categories set Depth = @0", fromChildCategoryDepth).Where("CategoryId=@0", fromChildCategory.CategoryId)); } //4 修改toCategory 的属性:ChildCount,ItemCount //见下:sql_ToCategoryChildCountUpdate //5 最后删除fromCategory var sql_FromCategoryDelete = PetaPoco.Sql.Builder; sql_FromCategoryDelete.Append("delete tn_Categories").Where("CategoryId = @0", fromCategoryId); //6 修改原先from的父级分类的子分类数减一 var sql_FromParentCategoryChildCountUpdate = PetaPoco.Sql.Builder; if (fromCategory.ParentId > 0) { sql_FromParentCategoryChildCountUpdate.Append("update tn_Categories set ChildCount = ChildCount-1 ").Where("ChildCount > 0 and CategoryId = @0", fromCategory.ParentId); } #endregion int childCategoryCount = 0 /*子分类数*/, childItemCount = 0 /*内容数*/, updateToCategoryCount = 0, deleteEffectLineCount = 0, affectCount = 0, updateDepthCount = 0; PetaPocoDatabase dao = CreateDAO(); //在同一个事务中执行 using (var scope = dao.GetTransaction()) { //1 childCategoryCount = dao.Execute(sql_ChildCategoryUpdate); //2 childItemCount = dao.Execute(sql_ChildItemsUpdate); //3 var sql_ToCategoryChildCountUpdate = PetaPoco.Sql.Builder; sql_ToCategoryChildCountUpdate.Append("update tn_Categories set ChildCount =ChildCount + @0,ItemCount=ItemCount + @1", childCategoryCount, childItemCount).Where("CategoryId = @0", toCategoryId); updateToCategoryCount = dao.Execute(sql_ToCategoryChildCountUpdate); //4 updateDepthCount = dao.Execute(sql_UpdateFromChildCategoryDepths); //5 deleteEffectLineCount = dao.Execute(sql_FromCategoryDelete); //6 if (fromCategory.ParentId > 0) { affectCount = dao.Execute(sql_FromParentCategoryChildCountUpdate); } //事务结束 scope.Complete(); } #region 缓存处理 //标记删除 if (deleteEffectLineCount > 0) { RealTimeCacheHelper.MarkDeletion(fromCategory); } if (updateToCategoryCount > 0) { //实体缓存,toCategory RealTimeCacheHelper.IncreaseEntityCacheVersion(toCategoryId); //处理所有的列表缓存toCategory 相关的列表 RealTimeCacheHelper.IncreaseAreaVersion("OwnerId", toCategory.OwnerId); RealTimeCacheHelper.IncreaseAreaVersion("ParentId", toCategory.CategoryId); //处理全局缓存(后台管理) RealTimeCacheHelper.IncreaseGlobalVersion(); } if (updateDepthCount > 0 && ownerCategories.Count() > 1) { foreach (var ownerCategory in ownerCategories) { if (ownerCategory.ParentId == fromCategoryId) { ownerCategory.ParentId = toCategoryId; } RealTimeCacheHelper.IncreaseEntityCacheVersion(ownerCategory.CategoryId); } } //处理分类下的内容项的缓存 if (childItemCount > 0) { //toCategory以及父级 List <long> toCategoryParentsIds = new List <long>(); RecurseGetParents(toCategoryId, toCategoryParentsIds); toCategoryParentsIds.Add(toCategoryId); foreach (long categoryId in toCategoryParentsIds) { EntityData.ForType(typeof(ItemInCategory)).RealTimeCacheHelper.IncreaseAreaVersion("CategoryId", toCategoryId); } //fromCategory父级 List <long> fromCategoryParentsIds = new List <long>(); RecurseGetParents(fromCategoryId, fromCategoryParentsIds); foreach (long categoryId in fromCategoryParentsIds) { EntityData.ForType(typeof(ItemInCategory)).RealTimeCacheHelper.IncreaseAreaVersion("CategoryId", toCategoryId); } } #endregion }
/// <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); }