/// <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(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="tagName">标签名</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <returns></returns>
public T Get(string tagName, string tenantTypeId)
{
ICacheService cacheService = DIContainer.Resolve <ICacheService>();
string cacheKey = "TagIdToTagNames::TenantTypeId:" + tenantTypeId;
Dictionary <string, long> tagNameToIds = cacheService.Get <Dictionary <string, long> >(cacheKey);
long tagId = 0;
if (tagNameToIds == null)
{
tagNameToIds = new Dictionary <string, long>();
}
if (!tagNameToIds.ContainsKey(tagName) || tagNameToIds[tagName] == 0)
{
Sql sql = Sql.Builder;
sql.Select("TagId")
.From("tn_Tags")
.Where("TagName = @0", StringUtility.StripSQLInjection(tagName))
.Where("TenantTypeId = @0", tenantTypeId);
PetaPocoDatabase dao = CreateDAO();
tagId = dao.ExecuteScalar <long>(sql);
tagNameToIds[tagName] = tagId;
cacheService.Set(cacheKey, tagNameToIds, CachingExpirationType.UsualObjectCollection);
}
else
{
tagId = tagNameToIds[tagName];
}
return(tagId == 0 ? null : Get(tagId));
}
/// <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="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="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="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>
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="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="categoryId">分类的Id集合</param>
/// <returns>内容项的ID集合</returns>
public IEnumerable <long> GetItemIds(long categoryId, IEnumerable <long> categorieIds)
{
PetaPocoDatabase dao = CreateDAO();
//获取缓存
StringBuilder categoriesIdsStringBuilder = new StringBuilder();
foreach (long id in categorieIds)
{
categoriesIdsStringBuilder.AppendFormat("-{0}", id);
}
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "CategoryId", categoryId));
cacheKey.AppendFormat("allCategoryIds-{0}", categoriesIdsStringBuilder.ToString());
IEnumerable <long> itemIds = null;
itemIds = cacheService.Get <IEnumerable <long> >(cacheKey.ToString());
if (itemIds == null)
{
var sql = PetaPoco.Sql.Builder;
sql.Select("ItemId").From("tn_ItemsInCategories").Where(" CategoryId in (@ids)", new { ids = categorieIds });
itemIds = dao.FetchFirstColumn(sql).Cast <long>();
cacheService.Add(cacheKey.ToString(), itemIds, CachingExpirationType.UsualObjectCollection);
}
return(itemIds);
}
//done:zhengw,by mazq 这个方法为什么要重写呢？
//zhengw回复：已删除
/// <summary>
/// 获取我的邀请好友记录
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="pageIndex">页码</param>
/// <returns>被邀请的好友Id列表</returns>
public PagingEntityIdCollection GetMyInviteFriendRecords(long userId, int pageIndex = 1)
{
PetaPocoDatabase dao = CreateDAO();
//仿照其他分页方法做
var sql = Sql.Builder;
sql.Select("InvitedUserId")
.From("tn_InviteFriendRecords")
//done:bianchx by libsh，不需要加判断
//回复：已经删除了对应的判断。
.Where("UserId = @0", userId)
.OrderBy("DateCreated desc");
PagingEntityIdCollection peic = null;
if (pageIndex <= CacheablePageCount)
{
string cacheKey = GetCacheKey_InviteUserIds(userId);
peic = cacheService.Get <PagingEntityIdCollection>(cacheKey);
if (peic == null)
{
peic = dao.FetchPagingPrimaryKeys(PrimaryMaxRecords, pageSize * CacheablePageCount, 1, "InvitedUserId", sql);
peic.IsContainsMultiplePages = true;
cacheService.Add(cacheKey, peic, CachingExpirationType.ObjectCollection);
}
}
else
{
peic = dao.FetchPagingPrimaryKeys(PrimaryMaxRecords, pageSize, pageIndex, "InvitedUserId", sql);
}
return(peic);
}
/// <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="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>
/// <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="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="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="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>
/// <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>
/// <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 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="tagName">标签名称</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
/// <returns>返回指定的内容项Id集合</returns>
public IEnumerable <long> GetItemIds(string tagName, string tenantTypeId, long?ownerId)
{
PetaPocoDatabase dao = CreateDAO();
//获取缓存
StringBuilder cacheKey = new StringBuilder(RealTimeCacheHelper.GetListCacheKeyPrefix(CacheVersionType.AreaVersion, "TagName", tagName));
cacheKey.AppendFormat(":ItemIds-TenantTypeId:{0}-OwnerId:{0}", tenantTypeId, ownerId.HasValue ? ownerId.ToString() : string.Empty);
//组装sql语句
var sql = PetaPoco.Sql.Builder;
if (ownerId.HasValue && ownerId > 0)
{
sql.Select("ItemId")
.From("tn_ItemsInTags IT")
.InnerJoin("tn_TagsInOwners TIO")
.On("IT.TagInOwnerId = TIO.Id")
.Where("TIO.OwnerId = @0", ownerId);
if (!string.IsNullOrEmpty(tenantTypeId))
{
sql.Where("TIO.TenantTypeId = @0", tenantTypeId);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.Where("TIO.TagName = @0", tagName);
}
}
else
{
sql.Select("ItemId")
.From("tn_ItemsInTags");
if (!string.IsNullOrEmpty(tenantTypeId))
{
sql.Where("TenantTypeId = @0", tenantTypeId);
}
if (!string.IsNullOrEmpty(tagName))
{
sql.Where("TagName = @0", tagName);
}
}
IEnumerable <long> itemIds = null;
itemIds = cacheService.Get <IEnumerable <long> >(cacheKey.ToString());
if (itemIds == null)
{
itemIds = dao.FetchFirstColumn(sql).Cast <long>();
cacheService.Add(cacheKey.ToString(), itemIds, CachingExpirationType.ObjectCollection);
}
return(itemIds);
}
/// <summary>
/// 通过被邀请人ID获取邀请人
/// </summary>
/// <param name="userId">被邀请人ID</param>
/// <returns></returns>
public InviteFriendRecord GetInvitingUserId(long userId)
{
var sql_Get = PetaPoco.Sql.Builder;
PetaPocoDatabase dao = CreateDAO();
sql_Get.Select("*")
.From("tn_InviteFriendRecords")
.Where("InvitedUserId = @0", userId);
InviteFriendRecord record = dao.FirstOrDefault <InviteFriendRecord>(sql_Get);
return(record);
}
/// <summary>
/// 请求结束时执行
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void OnEndRequest(object sender, System.EventArgs e)
{
//by mazq,2012-9-11 过滤掉了异步请求
if (HttpContext.Current.Request.AcceptTypes.Contains("text/html"))
{
if (HttpContext.Current.Request.Headers.Get("X-Requested-With") == null)
{
var handler = new TraceHttpHandler();
handler.ProcessRequest(HttpContext.Current, PetaPocoDatabase.GetTracedSqls(), stopwatchOfResponseTime);
}
}
}
/// <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>
/// 获取常用操作
/// </summary>
/// <param name="navigationId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public CommonOperation GetCommonOperation(int navigationId, long userId)
{
Sql sql = Sql.Builder;
PetaPocoDatabase dao = CreateDAO();
sql.Select("*")
.From("tn_CommonOperations")
.Where("UserId=@0", userId)
.Where("NavigationId=@0", navigationId);
CommonOperation commonOperation = null;
commonOperation = dao.FirstOrDefault <CommonOperation>(sql);
return(commonOperation);
}
/// <summary>
/// 获取未使用的Url别名
/// </summary>
/// <param name="aliases">Url别名集合</param>
/// <param name="url">待处理的Url</param>
/// <param name="urlExists">带处理Url是否已存在</param>
public string GetUnusedAlias(string[] aliases, string url, out bool urlExists)
{
urlExists = false;
if (aliases == null || aliases.Length == 0 || string.IsNullOrEmpty(url))
{
return(string.Empty);
}
var sql = PetaPoco.Sql.Builder;
string alias = string.Empty;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
//判断Url是否存在
sql.Select("Alias")
.From("tn_ShortUrls")
.Where("Url = @0", url);
alias = dao.FirstOrDefault <string>(sql);
if (!string.IsNullOrEmpty(alias))
{
urlExists = true;
}
if (!urlExists)
{
foreach (string item in aliases)
{
sql = Sql.Builder;
sql.Select("count(Alias)")
.From("tn_ShortUrls")
.Where("Alias = @0", item);
if (dao.ExecuteScalar <int>(sql) == 0)
{
alias = item;
break;
}
}
}
dao.CloseSharedConnection();
return(alias);
}