/// <summary>
/// 添加标签与分组关联
/// </summary>
/// <param name="tagName">标签名</param>
/// <param name="groupId">拥有者Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
public int AddTagInGroup(string tagName, long groupId, string tenantTypeId)
{
PetaPocoDatabase dao = CreateDAO();
int affectCount = 0;
dao.OpenSharedConnection();
var sql = PetaPoco.Sql.Builder;
sql.Select("count(*)")
.From("tn_TagsInGroups")
.Where("GroupId = @0", groupId)
.Where("TagName = @0", tagName)
.Where("TenantTypeId = @0", tenantTypeId);
affectCount = dao.ExecuteScalar <int>(sql);
if (affectCount == 0)
{
sql = PetaPoco.Sql.Builder;
sql.Append("INSERT INTO tn_TagsInGroups (GroupId, TagName,TenantTypeId) VALUES (@0,@1,@2)", groupId, tagName, tenantTypeId);
affectCount = dao.Execute(sql);
if (affectCount > 0)
{
RealTimeCacheHelper.IncreaseAreaVersion("GroupId", groupId);
}
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 保存用户自定义风格
/// </summary>
/// <param name="presentAreaKey">呈现区域标识</param>
/// <param name="ownerId">OwnerId</param>
/// <param name="customStyle">自定义风格实体</param>
public void Save(string presentAreaKey, long ownerId, CustomStyle customStyle)
{
PetaPocoDatabase database = CreateDAO();
database.OpenSharedConnection();
CustomStyleEntity entity = Get(presentAreaKey, ownerId);
string customStyleXml = Serialize(customStyle);
if (entity != null)
{
customStyle.LastModified = DateTime.UtcNow;
entity.CustomStyle = customStyle;
entity.SerializedCustomStyle = customStyleXml;
entity.LastModified = DateTime.UtcNow;
database.Update(entity);
}
else
{
entity = CustomStyleEntity.New();
entity.CustomStyle = customStyle;
entity.PresentAreaKey = presentAreaKey;
entity.OwnerId = ownerId;
entity.SerializedCustomStyle = customStyleXml;
database.Insert(entity);
}
database.CloseSharedConnection();
cacheService.Set(GetCacheKey_CustomStyleEntity(presentAreaKey, ownerId), entity, CachingExpirationType.UsualSingleObject);
}
/// <summary>
/// 执行队列
/// </summary>
public void ExecQueue()
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var tempCreateVisitQueue = new Queue <long>(CreateVisitQueue);
CreateVisitQueue.Clear();
while (tempCreateVisitQueue.Count > 0)
{
Visit visit = Get(tempCreateVisitQueue.Dequeue());
if (visit == null)
{
continue;
}
dao.Insert(visit);
}
//将队列中的数据更新到数据库
var tempUpdateVisitQueue = new Queue <long>(UpdateVisitQueue);
UpdateVisitQueue.Clear();
while (tempUpdateVisitQueue.Count > 0)
{
Visit visit = Get(tempUpdateVisitQueue.Dequeue());
if (visit == null)
{
continue;
}
dao.Update(visit);
}
dao.CloseSharedConnection();
}
/// <summary>
/// 解除符合解除管制标准的用户（永久管制的用户不会自动解除管制）
/// </summary>
/// <param name="noModeratedUserPoint">用户自动接触管制状态所需的积分（用户综合积分）</param>
/// <returns>被解除管制的用户集合</returns>
public IEnumerable <User> NoModeratedUsers(int noModeratedUserPoint)
{
if (noModeratedUserPoint <= 0)
{
return(new List <User>());
}
PointSettings pointSettings = DIContainer.Resolve <IPointSettingsManager>().Get();
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql_select = PetaPoco.Sql.Builder;
sql_select.Select("UserId")
.From("tn_Users")
.Where("IsForceModerated = @0 ", false)
.Where("IsModerated = @0", true);
sql_select.Where("ExperiencePoints * @0 + ReputationPoints * @1 > @2", pointSettings.ExperiencePointsCoefficient, pointSettings.ReputationPointsCoefficient, noModeratedUserPoint);
IEnumerable <object> userIds_object = dao.FetchFirstColumn(sql_select);
var sql_update = PetaPoco.Sql.Builder;
sql_update.Append("update tn_Users set IsModerated = @0 ", false)
.Where("IsForceModerated = @0 ", false)
.Where("IsModerated = @0", true)
.Where("ExperiencePoints * @0 + ReputationPoints * @1 > @2", pointSettings.ExperiencePointsCoefficient, pointSettings.ReputationPointsCoefficient, noModeratedUserPoint);
dao.Execute(sql_update);
dao.CloseSharedConnection();
return(PopulateEntitiesByEntityIds <long>(userIds_object.Cast <long>()));
}
/// <summary>
/// 更新隐私规则
/// </summary>
/// <param name="privacyItems">待更新的隐私项目规则集合</param>
public void UpdatePrivacyItems(IEnumerable <PrivacyItem> privacyItems)
{
if (privacyItems == null)
{
return;
}
List <Sql> sqls = new List <Sql>();
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
foreach (var privacyItem in privacyItems)
{
sqls.Add(Sql.Builder.Append("update tn_PrivacyItems")
.Append("set ItemName = @0, Description = @1, DisplayOrder = @2, PrivacyStatus = @3", privacyItem.ItemName, privacyItem.Description, privacyItem.DisplayOrder, privacyItem.PrivacyStatus)
.Append("where ItemKey = @0 and ItemGroupId = @1 and ApplicationId = @2", privacyItem.ItemKey, privacyItem.ItemGroupId, privacyItem.ApplicationId));
RealTimeCacheHelper.IncreaseEntityCacheVersion(privacyItem.ItemKey);
OnUpdated(privacyItem);
}
dao.Execute(sqls);
//done:zhangp,by zhengw:需要递增全局版本号
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseGlobalVersion();
}
/// <summary>
/// 创建提醒记录
/// </summary>
/// <param name="userId">被提醒用户Id</param>
/// <param name="reminderMode">提醒方式</param>
/// <param name="reminderInfoType">提醒信息类型</param>
/// <param name="objectIds">提醒对象Id集合</param>
public void CreateRecords(long userId, int reminderMode, int reminderInfoType, IEnumerable <long> objectIds)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = Sql.Builder;
sql.Select("ObjectId")
.From("tn_ReminderRecords")
.Where("UserId=@0", userId)
.Where("ReminderModeId=@0", reminderMode)
.Where("ReminderInfoTypeId=@0", reminderInfoType);
IEnumerable <object> oldObjectIds_object = dao.FetchFirstColumn(sql);
IEnumerable <long> oldObjectIds = oldObjectIds_object.Cast <long>();
foreach (var objectId in objectIds)
{
if (!oldObjectIds.Contains(objectId))
{
ReminderRecord record = ReminderRecord.New();
record.UserId = userId;
record.ReminderModeId = reminderMode;
record.ReminderInfoTypeId = reminderInfoType;
record.ObjectId = objectId;
dao.Insert(record);
}
}
dao.CloseSharedConnection();
//更新缓存
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 批量添加标签与分组关联
/// </summary>
/// <param name="tagNames">标签名</param>
/// <param name="groupId">分组Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
public int BatchAddTagsInGroup(IEnumerable <string> tagNames, long groupId, string tenantTypeId)
{
int affectCount = 0;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
dao.Execute(Sql.Builder.Append("delete from tn_TagsInGroups where GroupId = @0", groupId));
IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>();
foreach (var tagName in tagNames)
{
if (string.IsNullOrEmpty(tagName))
{
continue;
}
sqls.Add(Sql.Builder.Append("INSERT INTO tn_TagsInGroups (GroupId, TagName,TenantTypeId) VALUES (@0,@1,@2)", groupId, tagName, tenantTypeId));
}
affectCount = dao.Execute(sqls);
if (affectCount > 0)
{
RealTimeCacheHelper.IncreaseAreaVersion("GroupId", groupId);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 删除标签与成员的关系实体
/// </summary>
/// <param name="entity">待处理的实体</param>
/// <returns></returns>
public override int Delete(ItemInTag entity)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
int affectCount = base.Delete(entity);
if (entity != null && affectCount > 0)
{
List <Sql> sqls = new List <Sql>();
sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount - 1")
.Where("ItemCount > 0 and Id = @0", entity.TagInOwnerId));
sqls.Add(Sql.Builder.Append("update tn_Tags set ItemCount = ItemCount - 1")
.Where("ItemCount > 0 and TagName = @0 and TenantTypeId = @1", entity.TagName, entity.TenantTypeId));
affectCount = dao.Execute(sqls);
RealTimeCacheHelper.IncreaseAreaVersion("TagName", entity.TagName);
RealTimeCacheHelper.IncreaseAreaVersion("ItemId", entity.ItemId);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 从数据库删除实体
/// </summary>
/// <param name="entity">标签实体</param>
/// <returns>影响行数</returns>
public override int Delete(T entity)
{
IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>();
int affectCount = 0;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_Tags where TagId = @0", entity.TagId));
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_ItemsInTags where TagInOwnerId in (Select DISTINCT Id from tn_TagsInOwners where TenantTypeId = @0 and TagName = @1)", entity.TenantTypeId, entity.TagName));
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInOwners where TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName));
sqls.Add(PetaPoco.Sql.Builder.Append("delete from tn_TagsInGroups where TenantTypeId = @0 and TagName = @1", entity.TenantTypeId, entity.TagName));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
//更新实体缓存
OnDeleted(entity);
RealTimeCacheHelper.IncreaseGlobalVersion();
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 根据审核状态获取群组数
/// </summary>
/// <returns></returns>
public Dictionary <GroupManageableCountType, int> GetManageableCounts()
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Dictionary <GroupManageableCountType, int> countType = new Dictionary <GroupManageableCountType, int>();
var sql_selectIsActivated = PetaPoco.Sql.Builder;
sql_selectIsActivated.Select("count(*)").From("spb_Groups");
sql_selectIsActivated.Where("AuditStatus = @0", AuditStatus.Pending);
countType[GroupManageableCountType.Pending] = dao.FirstOrDefault <int>(sql_selectIsActivated);
var sql_selectIsAll = PetaPoco.Sql.Builder;
sql_selectIsAll.Select("count(*)").From("spb_Groups");
countType[GroupManageableCountType.IsAll] = dao.FirstOrDefault <int>(sql_selectIsAll);
var sql_selectIsLast24 = PetaPoco.Sql.Builder;
sql_selectIsLast24.Select("count(*)").From("spb_Groups");
sql_selectIsLast24.Where("DateCreated >= @0 and DateCreated < @1", DateTime.UtcNow.AddDays(-1), DateTime.UtcNow);
countType[GroupManageableCountType.IsLast24] = dao.FirstOrDefault <int>(sql_selectIsLast24);
dao.CloseSharedConnection();
return(countType);
}
/// <summary>
/// 判断用户是否对同一主体内容产生过从属内容动态，如果产生过则替换成本次操作
/// </summary>
/// <param name="activity"></param>
/// <returns>true-更新成功，false-不存在OwnerType+TenantTypeId+ReferenceId的动态记录</returns>
public bool CheckExistAndUpdateSource(Activity activity)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder;
sql.Select("ActivityId").From("tn_Activities").Where("OwnerId=@0", activity.OwnerId).Where("OwnerType=@0", activity.OwnerType).Where("UserId=@0", activity.UserId)
.Where("TenantTypeId=@0", activity.TenantTypeId).Where("ReferenceId=@0", activity.ReferenceId).Where("ReferenceTenantTypeId=@0", activity.ReferenceTenantTypeId);
long oldActivityId = dao.FirstOrDefault <long>(sql);
int affectCount = 0;
if (oldActivityId > 0)
{
sql = Sql.Builder.Append("Update tn_Activities Set SourceId=@0,DateCreated=@1,LastModified=@2", activity.SourceId, DateTime.UtcNow, DateTime.UtcNow)
.Where("ActivityId=@0", oldActivityId);
affectCount = dao.Execute(sql);
Activity oldActivity = Get(oldActivityId);
oldActivity.SourceId = activity.SourceId;
oldActivity.DateCreated = DateTime.UtcNow;
oldActivity.LastModified = DateTime.UtcNow;
this.OnUpdated(oldActivity);
}
dao.CloseSharedConnection();
return(oldActivityId > 0);
}
/// <summary>
/// 删除动态源时删除动态
/// </summary>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="sourceId">动态源内容id</param>
public void DeleteSource(string tenantTypeId, long sourceId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder.Select("ActivityId").From("tn_Activities")
.Where("(TenantTypeId=@0 and sourceId=@1) or ( ReferenceTenantTypeId=@0 and ReferenceId=@1 )", tenantTypeId, sourceId);
IEnumerable <object> activityIds = dao.FetchFirstColumn(sql);
if (activityIds.Count() > 0)
{
IEnumerable <long> longActivityIds = activityIds.Cast <long>();
sql = Sql.Builder.Append("delete from tn_ActivityUserInbox").Where("ActivityId in (@ActivityIds)", new { ActivityIds = longActivityIds });
dao.Execute(sql);
sql = Sql.Builder.Append("delete from tn_ActivitySiteInbox").Where("ActivityId in (@ActivityIds)", new { ActivityIds = longActivityIds });
dao.Execute(sql);
foreach (var activityId in longActivityIds)
{
this.DeleteByEntityId(activityId);
}
}
dao.CloseSharedConnection();
}
/// <summary>
/// 创建分类
/// </summary>
/// <param name="category">待创建分类</param>
/// <returns></returns>
public override object Insert(T category)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
long primaryKey = Convert.ToInt64(dao.Insert(category));
if (primaryKey > 0)
{
List <Sql> sqls = new List <Sql>();
if (category.ParentId > 0)
{
sqls.Add(Sql.Builder.Append("Update tn_Categories set ChildCount = ChildCount + 1 Where CategoryId = @0", category.ParentId));
}
sqls.Add(Sql.Builder.Append("update tn_Categories set DisplayOrder = @0 where CategoryId = @0", category.CategoryId));
int affectCount = dao.Execute(sqls);
category.DisplayOrder = category.CategoryId;
base.OnInserted(category);
}
dao.CloseSharedConnection();
return(primaryKey);
}
/// <summary>
/// 批量添加分组给标签
/// </summary>
/// <param name="groupIds">分组Id集合</param>
/// <param name="tagName">标签名</param>
/// <param name="tenantTypeId">租户类型Id</param>
public int BatchAddGroupsToTag(IEnumerable <long> groupIds, string tagName, string tenantTypeId)
{
int affectCount = 0;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
dao.Execute(Sql.Builder.Append("delete from tn_TagsInGroups where tagName = @0 and TenantTypeId = @1", tagName, tenantTypeId));
IList <PetaPoco.Sql> sqls = new List <PetaPoco.Sql>();
foreach (var groupId in groupIds)
{
//if (groupId <= 0)
// continue;
sqls.Add(Sql.Builder.Append("INSERT INTO tn_TagsInGroups (GroupId, TagName,TenantTypeId) VALUES (@0,@1,@2)", groupId, tagName, tenantTypeId));
}
affectCount = dao.Execute(sqls);
if (affectCount > 0)
{
groupIds.ToList().ForEach(n =>
{
RealTimeCacheHelper.IncreaseAreaVersion("GroupId", n);
});
EntityData.ForType(typeof(TagGroup)).RealTimeCacheHelper.IncreaseAreaVersion("TenantTypeId", tenantTypeId);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 添加相关标签
/// </summary>
/// <remarks>
/// 会为标签添加双向的关联关系,例如:
/// TagA关联到TagB
/// TagB关联到TagA
/// </remarks>
/// <param name="tagNames">相关标签名称集合</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
/// <param name="tagId">标签Id</param>
/// <returns> 影响行数</returns>
public int AddRelatedTagsToTag(string[] tagNames, string tenantTypeId, long ownerId, long tagId)
{
PetaPocoDatabase dao = CreateDAO();
int affectCount = 0;
dao.OpenSharedConnection();
foreach (string tagName in tagNames)
{
if (string.IsNullOrEmpty(tagName))
{
continue;
}
IList <Sql> sqls = new List <Sql>();
//创建标签
var sql = Sql.Builder;
sql.From("tn_Tags")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName);
var tag = dao.FirstOrDefault <Tag>(sql);
if (tag == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_Tags (TenantTypeId,TagName,DateCreated,ItemCount,OwnerCount) values (@0,@1,@2,1,1)", tenantTypeId, tagName, DateTime.UtcNow));
}
//创建标签与用户的关联
sql = Sql.Builder;
sql.From("tn_TagsInOwners")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId);
var tagInOwner = dao.FirstOrDefault <TagInOwner>(sql);
if (tagInOwner == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_TagsInOwners (TenantTypeId,TagName,OwnerId,ItemCount) values (@0,@1,@2,1)", tenantTypeId, tagName, ownerId));
}
//创建标签与内容项的关联
sqls.Add(Sql.Builder.Append("insert into tn_RelatedTags(TagId, RelatedTagId) select @0,TagId from tn_Tags where TenantTypeId = @1 and TagName = @2", tagId, tenantTypeId, tagName));
sqls.Add(Sql.Builder.Append("insert into tn_RelatedTags(TagId, RelatedTagId) select TagId,@0 from tn_Tags where TenantTypeId = @1 and TagName = @2", tagId, tenantTypeId, tagName));
//通过事务来控制多条语句执行时的一致性
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
}
dao.CloseSharedConnection();
if (affectCount > 0)
{
RealTimeCacheHelper.IncreaseAreaVersion("TagId", tagId);
}
return(affectCount);
}
/// <summary>
/// 更新内容隐私设置
/// </summary>
/// <param name="privacyable">可隐私接口</param>
/// <param name="specifyObjects"><remarks>key=specifyObjectTypeId,value=内容指定对象集合</remarks></param>
public void UpdatePrivacySettings(IPrivacyable privacyable, Dictionary <int, IEnumerable <ContentPrivacySpecifyObject> > specifyObjects)
{
//回复：已修改
var sql = Sql.Builder;
//List<ContentPrivacySpecifyObject> userPrivacySpecifyObjects;
//IEnumerable<ContentPrivacySpecifyObject> pairs;
//IEnumerable<ContentPrivacySpecifyObject> deletePairs;
//IEnumerable<ContentPrivacySpecifyObject> insertPairs;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
sql.Append("delete from tn_ContentPrivacySpecifyObjects")
.Where("ContentId = @0", privacyable.ContentId)
.Where("TenantTypeId = @0", privacyable.TenantTypeId);
dao.Execute(sql);
if (specifyObjects == null)
{
specifyObjects = new Dictionary <int, IEnumerable <ContentPrivacySpecifyObject> >();
}
foreach (var item in specifyObjects)
{
//sql = Sql.Builder;
//done:zhangp,by zhengw:SpecifyObjectId=>SpecifyObjectTypeId
//回复：已修改
foreach (var insertPair in item.Value)
{
dao.Insert(insertPair);
}
//sql.Select("*")
// .From("tn_ContentPrivacySpecifyObjects")
// .Where("ContentId = @0", privacyable.ContentId)
// .Where("TenantTypeId = @0", privacyable.TenantTypeId)
// .Where("SpecifyObjectTypeId = @0", item.Key);
//userPrivacySpecifyObjects = dao.Fetch<ContentPrivacySpecifyObject>(sql);
//done:zhangp,by zhengw:userPrivacySpecifyObjects.Count为0时就不更新了？我觉得可以把这个判断去掉，两个边界情况都可以使用求交集的办法
//回复：已修改
//pairs = userPrivacySpecifyObjects.Intersect(item.Value);
//deletePairs = userPrivacySpecifyObjects.Except(pairs);
//foreach (var deletePair in deletePairs)
// dao.Delete(deletePair);
//insertPairs = item.Value.Except(pairs);
//foreach (var insertPair in insertPairs)
// dao.Insert(insertPair);
}
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseAreaVersion("TenantTypeId", privacyable.TenantTypeId);
}
/// <summary>
/// 更新完成度
/// </summary>
/// <param name="userId">用户Id</param>
public void UpdateIntegrity(long userId)
{
UserProfileSettings userProfileSettings = DIContainer.Resolve <IUserProfileSettingsManager>().GetUserProfileSettings();
int[] integrityItems = userProfileSettings.IntegrityProportions;
int integrity = integrityItems[(int)ProfileIntegrityItems.Birthday];
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = Sql.Builder;
sql.Select("Count(*)")
.From("spb_EducationExperiences")
.Where("UserId = @0", userId);
int countEducation = dao.ExecuteScalar <int>(sql);
if (countEducation > 0)
{
integrity += integrityItems[(int)ProfileIntegrityItems.EducationExperience];
}
sql = Sql.Builder;
sql.Select("count(*)")
.From("spb_WorkExperiences")
.Where("UserId = @0", userId);
int countWork = dao.ExecuteScalar <int>(sql);
if (countWork > 0)
{
integrity += integrityItems[(int)ProfileIntegrityItems.WorkExperience];
}
sql = Sql.Builder;
sql.Where("userId = @0", userId);
UserProfile userProfile = dao.FirstOrDefault <UserProfile>(sql);
if (userProfile != null)
{
IUser user = new UserService().GetUser(userProfile.UserId);
integrity += (user.HasAvatar ? integrityItems[(int)ProfileIntegrityItems.Avatar] : 0);
integrity += (userProfile.HasHomeAreaCode ? integrityItems[(int)ProfileIntegrityItems.HomeArea] : 0);
integrity += (userProfile.HasIM ? integrityItems[(int)ProfileIntegrityItems.IM] : 0);
integrity += (userProfile.HasIntroduction ? integrityItems[(int)ProfileIntegrityItems.Introduction] : 0);
integrity += (userProfile.HasMobile ? integrityItems[(int)ProfileIntegrityItems.Mobile] : 0);
integrity += (userProfile.HasNowAreaCode ? integrityItems[(int)ProfileIntegrityItems.NowArea] : 0);
userProfile.Integrity = integrity;
Update(userProfile);
}
dao.CloseSharedConnection();
}
/// <summary>
/// 用户批量更新提醒设置
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="userReminderSettings">用户提醒设置集合</param>
public void BatchUpdateUserReminderSettings(long userId, IEnumerable <UserReminderSettings> userReminderSettings)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
bool hasSetting = false;
var sql = PetaPoco.Sql.Builder;
sql.Select("Id")
.From("tn_UserReminderSettings")
.Where("userId=@0", userId);
List <long> ids = dao.Fetch <long>(sql);
IEnumerable <UserReminderSettings> oldUserReminderSettings = PopulateEntitiesByEntityIds(ids);
foreach (var userReminderSetting in userReminderSettings)
{
foreach (var oldUserReminderSetting in oldUserReminderSettings)
{
if (oldUserReminderSetting.ReminderInfoTypeId == userReminderSetting.ReminderInfoTypeId && oldUserReminderSetting.ReminderModeId == userReminderSetting.ReminderModeId)
{
hasSetting = true;
if (oldUserReminderSetting.ReminderThreshold == userReminderSetting.ReminderThreshold &&
oldUserReminderSetting.IsEnabled == userReminderSetting.IsEnabled &&
oldUserReminderSetting.IsRepeated == userReminderSetting.IsRepeated &&
oldUserReminderSetting.RepeatInterval == userReminderSetting.RepeatInterval)
{
break;
}
else
{
oldUserReminderSetting.IsEnabled = userReminderSetting.IsEnabled;
oldUserReminderSetting.IsRepeated = userReminderSetting.IsRepeated;
oldUserReminderSetting.ReminderThreshold = userReminderSetting.ReminderThreshold;
oldUserReminderSetting.RepeatInterval = userReminderSetting.RepeatInterval;
dao.Update(oldUserReminderSetting);
break;
}
}
}
if (!hasSetting)
{
dao.Insert(userReminderSetting);
}
hasSetting = false;
}
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 清除用户所有私信会话
/// </summary>
/// <param name="userId">用户Id</param>
public void ClearSessionsFromUser(long userId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
List <Sql> sqls = new List <Sql>();
var sql = Sql.Builder;
sql.Select("SessionId")
.From("tn_MessageSessions")
.Where("UserId = @0", userId);
IEnumerable <object> sessionids = dao.FetchFirstColumn(sql);
if (sessionids.Count() > 0)
{
//清除私信实体脚本
sqls.Add(Sql.Builder.Append("delete from tn_Messages where exists(")
.Append("select 1 from tn_MessagesInSessions MIS where tn_Messages.MessageId = MIS.MessageId and exists(")
.Append("select 1 from tn_MessageSessions MS where MS.SessionId = MIS.SessionId and MS.UserId = @0 and not exists(", userId)
.Append("select 1 from tn_MessageSessions SUBMS Where SUBMS.UserId = MS.OtherUserId and SUBMS.OtherUserId = MS.UserId)))"));
//清除私信与会话关联脚本
sqls.Add(Sql.Builder.Append("delete from tn_MessagesInSessions where exists(")
.Append("select 1 from tn_MessageSessions MS where MS.SessionId = tn_MessagesInSessions.SessionId and MS.UserId = @0)", userId));
//清除私信会话
sqls.Add(Sql.Builder.Append("delete from tn_MessageSessions where UserId = @0", userId));
using (var transaction = dao.GetTransaction())
{
dao.Execute(sqls);
transaction.Complete();
}
}
dao.CloseSharedConnection();
#region 更新缓存
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
sessionids.ToList().ForEach((n) =>
{
RealTimeCacheHelper.IncreaseEntityCacheVersion(n);
});
#endregion
}
/// <summary>
/// 将动态加入到站点动态收件箱
/// </summary>
/// <param name="activityId"></param>
public void InsertSiteInbox(long activityId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder.Select("ActivityId").From("tn_ActivitySiteInbox").Where("ActivityId=@0", activityId);
IEnumerable <long> activityIds = dao.Fetch <long>(sql);
if (activityIds == null || activityIds.Count() == 0)
{
sql = Sql.Builder.Append("insert tn_ActivitySiteInbox(ActivityId) values(@0)", activityId);
dao.Execute(sql);
}
dao.CloseSharedConnection();
}
/// <summary>
/// 更新提醒记录（只更新最后提醒时间）
/// </summary>
/// <param name="userId">被提醒用户Id</param>
/// <param name="reminderModeId">提醒方式Id</param>
/// <param name="reminderInfoTypeId">提醒信息类型Id</param>
/// <param name="objectIds">提醒对象Id集合</param>
public void UpdateRecoreds(long userId, int reminderModeId, int reminderInfoTypeId, IEnumerable <long> objectIds)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = Sql.Builder;
IEnumerable <ReminderRecord> records = this.GetRecords(userId, reminderModeId, reminderInfoTypeId);
foreach (var objectId in objectIds)
{
sql.Append("Update tn_ReminderRecords set LastReminderTime = @0 where UserId = @1 and ReminderModeId = @2 and ReminderInfoTypeId = @3 and ObjectId = @4", DateTime.UtcNow, userId, reminderModeId, reminderInfoTypeId, objectId);
dao.Execute(sql);
}
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 获取未使用的Url别名
/// </summary>
/// <param name="aliases">Url别名集合</param>
/// <param name="url">待处理的Url</param>
/// <param name="urlExists">带处理Url是否已存在</param>
public string GetUnusedAlias(string[] aliases, string url, out bool urlExists)
{
urlExists = false;
if (aliases == null || aliases.Length == 0 || string.IsNullOrEmpty(url))
{
return(string.Empty);
}
var sql = PetaPoco.Sql.Builder;
string alias = string.Empty;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
//判断Url是否存在
sql.Select("Alias")
.From("tn_ShortUrls")
.Where("Url = @0", url);
alias = dao.FirstOrDefault <string>(sql);
if (!string.IsNullOrEmpty(alias))
{
urlExists = true;
}
if (!urlExists)
{
foreach (string item in aliases)
{
sql = Sql.Builder;
sql.Select("count(Alias)")
.From("tn_ShortUrls")
.Where("Alias = @0", item);
if (dao.ExecuteScalar <int>(sql) == 0)
{
alias = item;
break;
}
}
}
dao.CloseSharedConnection();
return(alias);
}
/// <summary>
/// 将动态加入到用户动态收件箱
/// </summary>
/// <param name="activityId"></param>
/// <param name="userIds"></param>
public void InsertUserInboxs(long activityId, IEnumerable <long> userIds)
{
if (userIds == null)
{
return;
}
var activity = Get(activityId);
if (activity == null)
{
return;
}
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql;
List <long> activityIds = new List <long> {
activityId
};
if (activity.ReferenceId > 0)
{
sql = Sql.Builder.Select("ActivityId").From("tn_Activities").Where("TenantTypeId=@0", activity.ReferenceTenantTypeId).Where("SourceId=@0", activity.ReferenceId);
activityIds.AddRange(dao.Fetch <long>(sql).Distinct());
}
//若已存在，则不再添加
List <Sql> sqls = new List <Sql>();
foreach (var userId in userIds.Distinct())
{
sql = Sql.Builder.Select("count(*)").From("tn_ActivityUserInbox").Where("ActivityId in (@activityIds)", new { activityIds = activityIds }).Where("UserId=@0", userId);
if (dao.FirstOrDefault <long>(sql) > 0)
{
continue; //已经存在
}
sql = Sql.Builder
.Append(@"insert tn_ActivityUserInbox (UserId,ActivityId) values(@0,@1)", userId, activityId);
sqls.Add(sql);
}
dao.Execute(sqls);
dao.CloseSharedConnection();
}
/// <summary>
/// 根据用户状态获取用户数
/// </summary>
/// <param name="isActivated">是否激活</param>
/// <param name="isBanned">是否封禁</param>
/// <param name="isModerated">是否管制</param>
public Dictionary <UserManageableCountType, int> GetManageableCounts(bool isActivated, bool isBanned, bool isModerated)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Dictionary <UserManageableCountType, int> countType = new Dictionary <UserManageableCountType, int>();
var sql_selectIsActivated = PetaPoco.Sql.Builder;
sql_selectIsActivated.Select("count(*)").From("tn_Users");
sql_selectIsActivated.Where("IsActivated=@0", isActivated);
countType[UserManageableCountType.IsActivated] = dao.FirstOrDefault <int>(sql_selectIsActivated);
var sql_selectIsBanned = PetaPoco.Sql.Builder;
sql_selectIsBanned.Select("count(*)").From("tn_Users");
sql_selectIsBanned.Where("IsBanned=@0", isBanned);
countType[UserManageableCountType.IsBanned] = dao.FirstOrDefault <int>(sql_selectIsBanned);
var sql_selectIsModerated = PetaPoco.Sql.Builder;
sql_selectIsModerated.Select("count(*)").From("tn_Users");
sql_selectIsModerated.Where("IsModerated=@0", isModerated);
countType[UserManageableCountType.IsModerated] = dao.FirstOrDefault <int>(sql_selectIsModerated);
var sql_selectIsAll = PetaPoco.Sql.Builder;
sql_selectIsAll.Select("count(*)").From("tn_Users");
countType[UserManageableCountType.IsAll] = dao.FirstOrDefault <int>(sql_selectIsAll);
var sql_selectIsLast24 = PetaPoco.Sql.Builder;
sql_selectIsLast24.Select("count(*)").From("tn_Users");
sql_selectIsLast24.Where("DateCreated > @0 and DateCreated < @1", DateTime.UtcNow.AddDays(-1), DateTime.UtcNow.AddDays(1));
countType[UserManageableCountType.IsLast24] = dao.FirstOrDefault <int>(sql_selectIsLast24);
dao.CloseSharedConnection();
return(countType);
}
/// <summary>
/// 清除内容项的所有标签
/// </summary>
/// <param name="itemId">内容项Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
public int ClearTagsFromItem(long itemId, string tenantTypeId, long ownerId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = PetaPoco.Sql.Builder;
sql.Select("IT.TagName")
.From("tn_ItemsInTags IT")
.InnerJoin("tn_TagsInOwners TIO")
.On("IT.TagInOwnerId = TIO.Id")
.Where("TIO.TenantTypeId = @0", tenantTypeId)
.Where("TIO.OwnerId = @0", ownerId)
.Where("IT.ItemId = @0", itemId);
List <string> tagNames = dao.Fetch <string>(sql);
sql = Sql.Builder;
sql.Append("delete from tn_ItemsInTags where ItemId = @0 and exists(select 1 from tn_TagsInOwners TIO where TIO.TenantTypeId = @1 and OwnerId = @2 and TIO.Id = TagInOwnerId)", itemId, tenantTypeId, ownerId);
int affectCount = dao.Execute(sql);
if (affectCount > 0)
{
List <Sql> sqls = new List <Sql>();
foreach (string tagName in tagNames)
{
sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount - 1")
.Where("ItemCount > 0 and OwnerId = @0 and TagName = @1 and TenantTypeId = @2", ownerId, tagName, tenantTypeId));
sqls.Add(Sql.Builder.Append("update tn_Tags set ItemCount = ItemCount - 1")
.Where("ItemCount > 0 and TagName = @0 and TenantTypeId = @1", tagName, tenantTypeId));
RealTimeCacheHelper.IncreaseAreaVersion("TagName", tagName);
}
dao.Execute(sqls);
RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 更新用户的隐私设置
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="userSettings"><remarks>key=itemKey,value=PrivacyStatus</remarks></param>
public void UpdateUserPrivacySettings(long userId, Dictionary <string, PrivacyStatus> userSettings)
{
PetaPocoDatabase dao = CreateDAO();
var sql = Sql.Builder;
sql.Select("*")
.From("tn_UserPrivacySettings")
.Where("UserId = @0", userId);
//done:zhangp,by zhengw:应该共享一个数据库连接
//回复：已经修改
dao.OpenSharedConnection();
UserPrivacySetting setting;
List <UserPrivacySetting> settings = dao.Fetch <UserPrivacySetting>(sql);
if (userSettings == null)
{
return;
}
foreach (var item in userSettings)
{
setting = settings.Find(n => n.ItemKey == item.Key);
if (setting != null)
{
var sqlDel = Sql.Builder;
sqlDel.Append("delete from tn_UserPrivacySpecifyObjects where UserPrivacySettingId in (select Id from tn_UserPrivacySettings where UserId = @0 and ItemKey = @1)", userId, item.Key);
dao.Execute(sqlDel);
EntityData.ForType(typeof(UserPrivacySpecifyObject)).RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
setting.PrivacyStatus = item.Value;
dao.Update(setting);
OnUpdated(setting);
}
else
{
setting = new UserPrivacySetting();
setting.UserId = userId;
setting.ItemKey = item.Key;
setting.PrivacyStatus = item.Value;
base.Insert(setting);
}
}
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 删除拥有者动态
/// </summary>
/// <param name="activityId"></param>
public void DeleteActivity(long activityId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
this.DeleteByEntityId(activityId);
Sql sql = Sql.Builder
.Append("delete from tn_ActivityUserInbox where ActivityId=@0 ", activityId);
dao.Execute(sql);
sql = Sql.Builder
.Append("delete from tn_ActivitySiteInbox where ActivityId=@0 ", activityId);
dao.Execute(sql);
dao.CloseSharedConnection();
}
/// <summary>
/// 创建实体
/// </summary>
/// <param name="entity">待创建实体</param>
/// <returns></returns>
public override object Insert(T entity)
{
Sql sql = Sql.Builder;
sql.Append("select count(*) from tn_Tags where TagName = @0 and TenantTypeId = @1", entity.TagName, entity.TenantTypeId);
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
object id = 0;
if (dao.ExecuteScalar <int>(sql) == 0)
{
id = base.Insert(entity);
}
dao.CloseSharedConnection();
return(id);
}
/// <summary>
/// 更新用户请求设置
/// </summary>
/// <param name="userId">用户id</param>
/// <param name="userNoticeSettings">用户的设置</param>
public void UpdateUserNoticeSettings(long userId, Dictionary <int, bool> userNoticeSettings)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
string cacheKey = GetCacheKey_UserNoticeSettingses(userId);
Dictionary <int, bool> userNoticeSettingsFormDB = cacheService.Get <Dictionary <int, bool> >(cacheKey);
if (userNoticeSettingsFormDB == null)
{
var sql_Seletct = Sql.Builder;
sql_Seletct.Select("*").From("tn_UserNoticeSettings").Where("UserId=@0", userId);
IEnumerable <dynamic> userSettings = dao.Fetch <dynamic>(sql_Seletct);
userNoticeSettingsFormDB = new Dictionary <int, bool>();
foreach (var item in userSettings)
{
userNoticeSettingsFormDB[item.TypeId] = item.IsAllowable == 1;
}
}
Dictionary <int, bool> diffNoticeSettings = new Dictionary <int, bool>();
Dictionary <int, bool> newNoticeSettings = new Dictionary <int, bool>();
var sql = new List <PetaPoco.Sql>();
foreach (var item in userNoticeSettings)
{
if (userNoticeSettingsFormDB != null && userNoticeSettingsFormDB.ContainsKey(item.Key) && userNoticeSettingsFormDB[item.Key] != item.Value)
{
sql.Add(PetaPoco.Sql.Builder.Append("update tn_UserNoticeSettings set IsAllowable=@0 where UserId=@1 and TypeId=@2", item.Value, userId, item.Key));
}
if (userNoticeSettingsFormDB == null || !userNoticeSettingsFormDB.ContainsKey(item.Key))
{
sql.Add(PetaPoco.Sql.Builder.Append("insert into tn_UserNoticeSettings(UserId,TypeId,IsAllowable) values(@0,@1,@2)", userId, item.Key, item.Value));
}
}
dao.Execute(sql);
dao.CloseSharedConnection();
if (userNoticeSettingsFormDB != null)
{
cacheService.Set(cacheKey, userNoticeSettings, CachingExpirationType.ObjectCollection);
}
}
/// <summary>
/// 把用户加入到一组角色中
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="roleNames">赋予用户的用户角色</param>
public void AddUserToRoles(long userId, List <string> roleNames)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
RemoveUserRoles(userId);
var sqlInsert = Sql.Builder;
UserInRole userInRole = new UserInRole();
userInRole.UserId = userId;
foreach (var roleName in roleNames)
{
userInRole.RoleName = roleName;
dao.Insert(userInRole);
}
dao.CloseSharedConnection();
//增加版本
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
