/// <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="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>
/// <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="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="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>
/// <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="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="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="beforeDays">多少天以前的数据</param>
public void Clean(int?beforeDays)
{
PetaPocoDatabase dao = CreateDAO();
var sql_Delete = PetaPoco.Sql.Builder;
if (beforeDays.HasValue && beforeDays.Value > 0)
{
DateTime cleanDate = DateTime.UtcNow.AddDays(-beforeDays.Value);
sql_Delete.Append("delete from tn_Visit where LastVisitTime < @0", cleanDate);
dao.Execute(sql_Delete);
}
else
{
sql_Delete.Append("delete from tn_Visit");
dao.Execute(sql_Delete);
}
}
/// <summary>
/// 主体内容动态的最后更新时间设置为当前时间
/// </summary>
/// <param name="tenantTypeId"></param>
/// <param name="sourceId"></param>
public void UpdateLastModified(string tenantTypeId, long sourceId)
{
PetaPocoDatabase dao = CreateDAO();
Sql sql = Sql.Builder;
sql.Append("update tn_Activities set LastModified = @0 ", DateTime.UtcNow).Where("TenantTypeId=@0", tenantTypeId).Where("SourceId=@0", sourceId).Where("ReferenceId=0");
dao.Execute(sql);
}
/// <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="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="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="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="itemId">内容项Id</param>
/// <param name="tagInOwnerId">标签与拥有者关联</param>
public int DeleteTagFromItem(long itemId, long tagInOwnerId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = Sql.Builder;
sql.Select("TagName,")
.From("tn_ItemsInTags")
.Where("tagInOwnerId = @0", tagInOwnerId)
.Where("ItemId = @0", itemId);
dynamic tagInfo = dao.First <dynamic>(sql);
int affectCount = 0;
if (tagInfo != null)
{
sql = Sql.Builder.Append("delete from tn_ItemsInTags where itemId = @0 and TagInOwnerId = @1", itemId, tagInOwnerId);
affectCount = dao.Execute(sql);
if (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", tagInOwnerId));
sqls.Add(Sql.Builder.Append("update tn_Tags set ItemCount = ItemCount - 1")
.Where("ItemCount > 0 and TagName = @0 and TenantTypeId = @1", tagInfo.TagName, tagInfo.TenantTypeId));
affectCount = dao.Execute(sqls);
RealTimeCacheHelper.IncreaseAreaVersion("TagName", tagInfo.TagName);
RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId);
}
}
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>
/// <remarks>保留动态操作者的收件箱</remarks>
/// <param name="activityId"></param>
public void DeleteActivityFromUserInboxAndSiteInbox(long activityId)
{
Activity activity = Get(activityId);
if (activity == null)
{
return;
}
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder
.Append("delete from tn_ActivityUserInbox").Where("ActivityId=@0", activityId).Where("UserId<>@0", activity.UserId);
dao.Execute(sql);
sql = Sql.Builder
.Append("delete from tn_ActivitySiteInbox where ActivityId=@0 ", activityId);
dao.Execute(sql);
dao.CloseSharedConnection();
}
/// <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>
/// 将动态加入到用户动态收件箱
/// </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="userId">用户Id</param>
/// <param name="userSettings"><remarks>key=itemKey,value=PrivacyStatus</remarks></param>
public void UpdateUserPrivacySettings(long userId, Dictionary <string, PrivacyStatus> userSettings)
{
PetaPocoDatabase dao = CreateDAO();
var sql = Sql.Builder;
sql.Select("*")
.From("tn_UserPrivacySettings")
.Where("UserId = @0", userId);
//done:zhangp,by zhengw:应该共享一个数据库连接
//回复：已经修改
dao.OpenSharedConnection();
UserPrivacySetting setting;
List <UserPrivacySetting> settings = dao.Fetch <UserPrivacySetting>(sql);
if (userSettings == null)
{
return;
}
foreach (var item in userSettings)
{
setting = settings.Find(n => n.ItemKey == item.Key);
if (setting != null)
{
var sqlDel = Sql.Builder;
sqlDel.Append("delete from tn_UserPrivacySpecifyObjects where UserPrivacySettingId in (select Id from tn_UserPrivacySettings where UserId = @0 and ItemKey = @1)", userId, item.Key);
dao.Execute(sqlDel);
EntityData.ForType(typeof(UserPrivacySpecifyObject)).RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
setting.PrivacyStatus = item.Value;
dao.Update(setting);
OnUpdated(setting);
}
else
{
setting = new UserPrivacySetting();
setting.UserId = userId;
setting.ItemKey = item.Key;
setting.PrivacyStatus = item.Value;
base.Insert(setting);
}
}
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 更新用户请求设置
/// </summary>
/// <param name="userId">用户id</param>
/// <param name="userNoticeSettings">用户的设置</param>
public void UpdateUserNoticeSettings(long userId, Dictionary <int, bool> userNoticeSettings)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
string cacheKey = GetCacheKey_UserNoticeSettingses(userId);
Dictionary <int, bool> userNoticeSettingsFormDB = cacheService.Get <Dictionary <int, bool> >(cacheKey);
if (userNoticeSettingsFormDB == null)
{
var sql_Seletct = Sql.Builder;
sql_Seletct.Select("*").From("tn_UserNoticeSettings").Where("UserId=@0", userId);
IEnumerable <dynamic> userSettings = dao.Fetch <dynamic>(sql_Seletct);
userNoticeSettingsFormDB = new Dictionary <int, bool>();
foreach (var item in userSettings)
{
userNoticeSettingsFormDB[item.TypeId] = item.IsAllowable == 1;
}
}
Dictionary <int, bool> diffNoticeSettings = new Dictionary <int, bool>();
Dictionary <int, bool> newNoticeSettings = new Dictionary <int, bool>();
var sql = new List <PetaPoco.Sql>();
foreach (var item in userNoticeSettings)
{
if (userNoticeSettingsFormDB != null && userNoticeSettingsFormDB.ContainsKey(item.Key) && userNoticeSettingsFormDB[item.Key] != item.Value)
{
sql.Add(PetaPoco.Sql.Builder.Append("update tn_UserNoticeSettings set IsAllowable=@0 where UserId=@1 and TypeId=@2", item.Value, userId, item.Key));
}
if (userNoticeSettingsFormDB == null || !userNoticeSettingsFormDB.ContainsKey(item.Key))
{
sql.Add(PetaPoco.Sql.Builder.Append("insert into tn_UserNoticeSettings(UserId,TypeId,IsAllowable) values(@0,@1,@2)", userId, item.Key, item.Value));
}
}
dao.Execute(sql);
dao.CloseSharedConnection();
if (userNoticeSettingsFormDB != null)
{
cacheService.Set(cacheKey, userNoticeSettings, CachingExpirationType.ObjectCollection);
}
}
/// <summary>
/// 删除标签与拥有者关系
/// </summary>
/// <param name="entity">标签与拥有者关系实体</param>
/// <returns>影响行数</returns>
public override int Delete(TagInOwner entity)
{
PetaPocoDatabase dao = CreateDAO();
IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>();
int affectCount = 0;
dao.OpenSharedConnection();
var sql_GetItemIds = PetaPoco.Sql.Builder;
sql_GetItemIds.Select("ItemId")
.From("tn_ItemsInTags")
.Where("TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName);
IEnumerable <object> itemIds = dao.FetchFirstColumn(sql_GetItemIds);
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInOwners where id=@0", entity.Id));
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_ItemsInTags where TagInOwnerId = @0 and TagName = @1", entity.Id, entity.TagName));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
OnDeleted(entity);
foreach (var itemId in itemIds)
{
EntityData.ForType(typeof(ItemInTag)).RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId);
}
EntityData.ForType(typeof(ItemInTag)).RealTimeCacheHelper.IncreaseAreaVersion("TagName", entity.TagName);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 批量更改处理状态
/// </summary>
/// <param name="userId">用户的id</param>
/// <param name="status">要更改的状态</param>
public void BatchSetStatus(long userId, InvitationStatus status)
{
PetaPocoDatabase dao = CreateDAO();
var sql = PetaPoco.Sql.Builder;
sql.Select("Id")
.From("tn_Invitations")
.Where("UserId=@0 and Status=@1", userId, (int)InvitationStatus.Unhandled);
dao.OpenSharedConnection();
IEnumerable <object> ids = dao.FetchFirstColumn(sql);
sql = PetaPoco.Sql.Builder;
sql.Append("update tn_Invitations set Status=@0 where UserId=@1 and Status =@2", (int)status, userId, (int)InvitationStatus.Unhandled);
dao.Execute(sql);
dao.CloseSharedConnection();
//维护缓存
string cacheKey = GetCacheKey_UnhandledInvitationCount(userId);
int? count = cacheService.Get(cacheKey) as int?;
if (count != null)
{
count = 0;
cacheService.Set(cacheKey, count, CachingExpirationType.SingleObject);
}
//更新列表缓存
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
//更新实体缓存
foreach (var id in ids)
{
Invitation invitation = Get(id);
if (invitation != null)
{
invitation.Status = status;
}
RealTimeCacheHelper.IncreaseEntityCacheVersion(id);
}
}
/// <summary>
/// 变更系统数据
/// </summary>
/// <param name="ownerId">ownerId</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="dataKey">数据标识</param>
/// <param name="value">待变更的数值</param>
public void Change(long ownerId, string tenantTypeId, string dataKey, decimal value)
{
PetaPocoDatabase dao = CreateDAO();
OwnerData ownerData = Get(ownerId, tenantTypeId, dataKey);
if (ownerData == null || ownerData.Id == 0)
{
ownerData = OwnerData.New();
ownerData.OwnerId = ownerId;
ownerData.Datakey = dataKey;
ownerData.DecimalValue = value;
ownerData.TenantTypeId = tenantTypeId;
Sql sql = Sql.Builder;
sql.Append("update tn_OwnerData set DecimalValue = @3 where DataKey = @0 and OwnerId = @1 and TenantTypeId = @2", dataKey, ownerId, tenantTypeId, value);
int affectCount = dao.Execute(sql);
if (affectCount == 0)
{
dao.Insert(ownerData);
}
}
else
{
ownerData.DecimalValue = ownerData.DecimalValue + value > 0 ? ownerData.DecimalValue + value : 0;
dao.Update(ownerData);
}
//done:libsh,by zhengw: 同上
//replay:已修改
string cacheKey = GetCacheKey_GetOwnerData(ownerId, dataKey, tenantTypeId);
OwnerData cacheOwnerdata = cacheService.Get <OwnerData>(cacheKey);
if (cacheOwnerdata != null)
{
cacheService.Set(cacheKey, ownerData, CachingExpirationType.SingleObject);
}
}
/// <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);
}
}