/// <summary>
/// 根据审核状态获取群组数
/// </summary>
/// <returns></returns>
public Dictionary <GroupManageableCountType, int> GetManageableCounts()
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Dictionary <GroupManageableCountType, int> countType = new Dictionary <GroupManageableCountType, int>();
var sql_selectIsActivated = PetaPoco.Sql.Builder;
sql_selectIsActivated.Select("count(*)").From("spb_Groups");
sql_selectIsActivated.Where("AuditStatus = @0", AuditStatus.Pending);
countType[GroupManageableCountType.Pending] = dao.FirstOrDefault <int>(sql_selectIsActivated);
var sql_selectIsAll = PetaPoco.Sql.Builder;
sql_selectIsAll.Select("count(*)").From("spb_Groups");
countType[GroupManageableCountType.IsAll] = dao.FirstOrDefault <int>(sql_selectIsAll);
var sql_selectIsLast24 = PetaPoco.Sql.Builder;
sql_selectIsLast24.Select("count(*)").From("spb_Groups");
sql_selectIsLast24.Where("DateCreated >= @0 and DateCreated < @1", DateTime.UtcNow.AddDays(-1), DateTime.UtcNow);
countType[GroupManageableCountType.IsLast24] = dao.FirstOrDefault <int>(sql_selectIsLast24);
dao.CloseSharedConnection();
return(countType);
}
/// <summary>
/// 添加相关标签
/// </summary>
/// <remarks>
/// 会为标签添加双向的关联关系,例如:
/// TagA关联到TagB
/// TagB关联到TagA
/// </remarks>
/// <param name="tagNames">相关标签名称集合</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
/// <param name="tagId">标签Id</param>
/// <returns> 影响行数</returns>
public int AddRelatedTagsToTag(string[] tagNames, string tenantTypeId, long ownerId, long tagId)
{
PetaPocoDatabase dao = CreateDAO();
int affectCount = 0;
dao.OpenSharedConnection();
foreach (string tagName in tagNames)
{
if (string.IsNullOrEmpty(tagName))
{
continue;
}
IList <Sql> sqls = new List <Sql>();
//创建标签
var sql = Sql.Builder;
sql.From("tn_Tags")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName);
var tag = dao.FirstOrDefault <Tag>(sql);
if (tag == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_Tags (TenantTypeId,TagName,DateCreated,ItemCount,OwnerCount) values (@0,@1,@2,1,1)", tenantTypeId, tagName, DateTime.UtcNow));
}
//创建标签与用户的关联
sql = Sql.Builder;
sql.From("tn_TagsInOwners")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId);
var tagInOwner = dao.FirstOrDefault <TagInOwner>(sql);
if (tagInOwner == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_TagsInOwners (TenantTypeId,TagName,OwnerId,ItemCount) values (@0,@1,@2,1)", tenantTypeId, tagName, ownerId));
}
//创建标签与内容项的关联
sqls.Add(Sql.Builder.Append("insert into tn_RelatedTags(TagId, RelatedTagId) select @0,TagId from tn_Tags where TenantTypeId = @1 and TagName = @2", tagId, tenantTypeId, tagName));
sqls.Add(Sql.Builder.Append("insert into tn_RelatedTags(TagId, RelatedTagId) select TagId,@0 from tn_Tags where TenantTypeId = @1 and TagName = @2", tagId, tenantTypeId, tagName));
//通过事务来控制多条语句执行时的一致性
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
}
dao.CloseSharedConnection();
if (affectCount > 0)
{
RealTimeCacheHelper.IncreaseAreaVersion("TagId", tagId);
}
return(affectCount);
}
/// <summary>
/// 判断用户是否对同一主体内容产生过从属内容动态，如果产生过则替换成本次操作
/// </summary>
/// <param name="activity"></param>
/// <returns>true-更新成功，false-不存在OwnerType+TenantTypeId+ReferenceId的动态记录</returns>
public bool CheckExistAndUpdateSource(Activity activity)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder;
sql.Select("ActivityId").From("tn_Activities").Where("OwnerId=@0", activity.OwnerId).Where("OwnerType=@0", activity.OwnerType).Where("UserId=@0", activity.UserId)
.Where("TenantTypeId=@0", activity.TenantTypeId).Where("ReferenceId=@0", activity.ReferenceId).Where("ReferenceTenantTypeId=@0", activity.ReferenceTenantTypeId);
long oldActivityId = dao.FirstOrDefault <long>(sql);
int affectCount = 0;
if (oldActivityId > 0)
{
sql = Sql.Builder.Append("Update tn_Activities Set SourceId=@0,DateCreated=@1,LastModified=@2", activity.SourceId, DateTime.UtcNow, DateTime.UtcNow)
.Where("ActivityId=@0", oldActivityId);
affectCount = dao.Execute(sql);
Activity oldActivity = Get(oldActivityId);
oldActivity.SourceId = activity.SourceId;
oldActivity.DateCreated = DateTime.UtcNow;
oldActivity.LastModified = DateTime.UtcNow;
this.OnUpdated(oldActivity);
}
dao.CloseSharedConnection();
return(oldActivityId > 0);
}
/// <summary>
/// 根据用户状态获取用户数
/// </summary>
/// <param name="isActivated">是否激活</param>
/// <param name="isBanned">是否封禁</param>
/// <param name="isModerated">是否管制</param>
public Dictionary <UserManageableCountType, int> GetManageableCounts(bool isActivated, bool isBanned, bool isModerated)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Dictionary <UserManageableCountType, int> countType = new Dictionary <UserManageableCountType, int>();
var sql_selectIsActivated = PetaPoco.Sql.Builder;
sql_selectIsActivated.Select("count(*)").From("tn_Users");
sql_selectIsActivated.Where("IsActivated=@0", isActivated);
countType[UserManageableCountType.IsActivated] = dao.FirstOrDefault <int>(sql_selectIsActivated);
var sql_selectIsBanned = PetaPoco.Sql.Builder;
sql_selectIsBanned.Select("count(*)").From("tn_Users");
sql_selectIsBanned.Where("IsBanned=@0", isBanned);
countType[UserManageableCountType.IsBanned] = dao.FirstOrDefault <int>(sql_selectIsBanned);
var sql_selectIsModerated = PetaPoco.Sql.Builder;
sql_selectIsModerated.Select("count(*)").From("tn_Users");
sql_selectIsModerated.Where("IsModerated=@0", isModerated);
countType[UserManageableCountType.IsModerated] = dao.FirstOrDefault <int>(sql_selectIsModerated);
var sql_selectIsAll = PetaPoco.Sql.Builder;
sql_selectIsAll.Select("count(*)").From("tn_Users");
countType[UserManageableCountType.IsAll] = dao.FirstOrDefault <int>(sql_selectIsAll);
var sql_selectIsLast24 = PetaPoco.Sql.Builder;
sql_selectIsLast24.Select("count(*)").From("tn_Users");
sql_selectIsLast24.Where("DateCreated > @0 and DateCreated < @1", DateTime.UtcNow.AddDays(-1), DateTime.UtcNow.AddDays(1));
countType[UserManageableCountType.IsLast24] = dao.FirstOrDefault <int>(sql_selectIsLast24);
dao.CloseSharedConnection();
return(countType);
}
/// <summary>
/// 更新完成度
/// </summary>
/// <param name="userId">用户Id</param>
public void UpdateIntegrity(long userId)
{
UserProfileSettings userProfileSettings = DIContainer.Resolve <IUserProfileSettingsManager>().GetUserProfileSettings();
int[] integrityItems = userProfileSettings.IntegrityProportions;
int integrity = integrityItems[(int)ProfileIntegrityItems.Birthday];
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
var sql = Sql.Builder;
sql.Select("Count(*)")
.From("spb_EducationExperiences")
.Where("UserId = @0", userId);
int countEducation = dao.ExecuteScalar <int>(sql);
if (countEducation > 0)
{
integrity += integrityItems[(int)ProfileIntegrityItems.EducationExperience];
}
sql = Sql.Builder;
sql.Select("count(*)")
.From("spb_WorkExperiences")
.Where("UserId = @0", userId);
int countWork = dao.ExecuteScalar <int>(sql);
if (countWork > 0)
{
integrity += integrityItems[(int)ProfileIntegrityItems.WorkExperience];
}
sql = Sql.Builder;
sql.Where("userId = @0", userId);
UserProfile userProfile = dao.FirstOrDefault <UserProfile>(sql);
if (userProfile != null)
{
IUser user = new UserService().GetUser(userProfile.UserId);
integrity += (user.HasAvatar ? integrityItems[(int)ProfileIntegrityItems.Avatar] : 0);
integrity += (userProfile.HasHomeAreaCode ? integrityItems[(int)ProfileIntegrityItems.HomeArea] : 0);
integrity += (userProfile.HasIM ? integrityItems[(int)ProfileIntegrityItems.IM] : 0);
integrity += (userProfile.HasIntroduction ? integrityItems[(int)ProfileIntegrityItems.Introduction] : 0);
integrity += (userProfile.HasMobile ? integrityItems[(int)ProfileIntegrityItems.Mobile] : 0);
integrity += (userProfile.HasNowAreaCode ? integrityItems[(int)ProfileIntegrityItems.NowArea] : 0);
userProfile.Integrity = integrity;
Update(userProfile);
}
dao.CloseSharedConnection();
}
/// <summary>
/// 通过被邀请人ID获取邀请人
/// </summary>
/// <param name="userId">被邀请人ID</param>
/// <returns></returns>
public InviteFriendRecord GetInvitingUserId(long userId)
{
var sql_Get = PetaPoco.Sql.Builder;
PetaPocoDatabase dao = CreateDAO();
sql_Get.Select("*")
.From("tn_InviteFriendRecords")
.Where("InvitedUserId = @0", userId);
InviteFriendRecord record = dao.FirstOrDefault <InviteFriendRecord>(sql_Get);
return(record);
}
/// <summary>
/// 获取常用操作
/// </summary>
/// <param name="navigationId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public CommonOperation GetCommonOperation(int navigationId, long userId)
{
Sql sql = Sql.Builder;
PetaPocoDatabase dao = CreateDAO();
sql.Select("*")
.From("tn_CommonOperations")
.Where("UserId=@0", userId)
.Where("NavigationId=@0", navigationId);
CommonOperation commonOperation = null;
commonOperation = dao.FirstOrDefault <CommonOperation>(sql);
return(commonOperation);
}
/// <summary>
/// 获取未使用的Url别名
/// </summary>
/// <param name="aliases">Url别名集合</param>
/// <param name="url">待处理的Url</param>
/// <param name="urlExists">带处理Url是否已存在</param>
public string GetUnusedAlias(string[] aliases, string url, out bool urlExists)
{
urlExists = false;
if (aliases == null || aliases.Length == 0 || string.IsNullOrEmpty(url))
{
return(string.Empty);
}
var sql = PetaPoco.Sql.Builder;
string alias = string.Empty;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
//判断Url是否存在
sql.Select("Alias")
.From("tn_ShortUrls")
.Where("Url = @0", url);
alias = dao.FirstOrDefault <string>(sql);
if (!string.IsNullOrEmpty(alias))
{
urlExists = true;
}
if (!urlExists)
{
foreach (string item in aliases)
{
sql = Sql.Builder;
sql.Select("count(Alias)")
.From("tn_ShortUrls")
.Where("Alias = @0", item);
if (dao.ExecuteScalar <int>(sql) == 0)
{
alias = item;
break;
}
}
}
dao.CloseSharedConnection();
return(alias);
}
/// <summary>
/// 将动态加入到用户动态收件箱
/// </summary>
/// <param name="activityId"></param>
/// <param name="userIds"></param>
public void InsertUserInboxs(long activityId, IEnumerable <long> userIds)
{
if (userIds == null)
{
return;
}
var activity = Get(activityId);
if (activity == null)
{
return;
}
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql;
List <long> activityIds = new List <long> {
activityId
};
if (activity.ReferenceId > 0)
{
sql = Sql.Builder.Select("ActivityId").From("tn_Activities").Where("TenantTypeId=@0", activity.ReferenceTenantTypeId).Where("SourceId=@0", activity.ReferenceId);
activityIds.AddRange(dao.Fetch <long>(sql).Distinct());
}
//若已存在，则不再添加
List <Sql> sqls = new List <Sql>();
foreach (var userId in userIds.Distinct())
{
sql = Sql.Builder.Select("count(*)").From("tn_ActivityUserInbox").Where("ActivityId in (@activityIds)", new { activityIds = activityIds }).Where("UserId=@0", userId);
if (dao.FirstOrDefault <long>(sql) > 0)
{
continue; //已经存在
}
sql = Sql.Builder
.Append(@"insert tn_ActivityUserInbox (UserId,ActivityId) values(@0,@1)", userId, activityId);
sqls.Add(sql);
}
dao.Execute(sqls);
dao.CloseSharedConnection();
}
/// <summary>
/// 获取用户自定义风格
/// </summary>
/// <param name="presentAreaKey">呈现区域标识</param>
/// <param name="ownerId">OwnerId</param>
/// <returns>无相应数据返回null</returns>
public CustomStyleEntity Get(string presentAreaKey, long ownerId)
{
string cacheKey = GetCacheKey_CustomStyleEntity(presentAreaKey, ownerId);
CustomStyleEntity result = cacheService.GetFromFirstLevel <CustomStyleEntity>(cacheKey);
if (result == null)
{
PetaPocoDatabase database = CreateDAO();
var sql = PetaPoco.Sql.Builder;
sql.Select("*").From("spb_CustomStyles").Where("PresentAreaKey=@0", presentAreaKey).Where("OwnerId=@0", ownerId);
result = database.FirstOrDefault <CustomStyleEntity>(sql);
if (result != null)
{
result.CustomStyle = Deserialize(result.SerializedCustomStyle);
}
cacheService.Add(cacheKey, result, CachingExpirationType.UsualSingleObject);
}
return(result);
}
/// <summary>
/// 在用户动态收件箱追溯OwnerId的动态
/// </summary>
/// <remarks>
/// 关注用户/加入群组、取消屏蔽用户/取消屏蔽群组时使用
/// </remarks>
/// <param name="userId">UserId</param>
/// <param name="ownerId">动态拥有者Id</param>
/// <param name="ownerType">动态拥有者类型</param>
/// <param name="traceBackNumber">追溯OwnerId的动态数</param>
public void TraceBackInboxAboutOwner(long userId, long ownerId, int ownerType, int traceBackNumber)
{
//缓存需要即时
//1、仅追溯traceBackNumber条非私有的动态；
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql = Sql.Builder
.Select("ActivityId")
.From("tn_Activities")
.Where("OwnerId=@0", ownerId)
.Where("OwnerType=@0", ownerType)
.Where("IsPrivate=0")
.OrderBy("LastModified desc");
IEnumerable <object> activityIds = dao.FetchTopPrimaryKeys <Activity>(traceBackNumber, sql);
List <Sql> sqls = new List <Sql>();
foreach (var activityId in activityIds.Cast <long>())
{
sql = Sql.Builder.Select("count(*)").From("tn_ActivityUserInbox").Where("ActivityId = @0", activityId).Where("UserId = @0", userId);
if (dao.FirstOrDefault <long>(sql) > 0)
{
continue; //已经存在
}
sql = Sql.Builder.Append("insert tn_ActivityUserInbox (UserId,ActivityId) values(@0,@1)", userId, activityId);
sqls.Add(sql);
}
int affectCount = dao.Execute(sqls);
dao.CloseSharedConnection();
if (affectCount > 0)
{
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
}
/// <summary>
/// 若存在OwnerType+TenantTypeId+ReferenceId的动态记录，则更新其SourceId及DateCreated、LastModified
/// </summary>
/// <param name="ownerType"></param>
/// <param name="tenantTypeId"></param>
/// <param name="referenceId"></param>
/// <param name="sourceId"></param>
/// <returns>true-更新成功，false-不存在OwnerType+TenantTypeId+ReferenceId的动态记录</returns>
public bool UpdateSource(int ownerType, string tenantTypeId, long referenceId, long sourceId)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
Sql sql;
//查找OwnerType+TenantTypeId+ReferenceId的动态记录
sql = Sql.Builder.Select("ActivityId").From("tn_Activities").Where("OwnerType=@0", ownerType).Where("TenantTypeId=@0", tenantTypeId).Where("ReferenceId=@0", referenceId);
long activityId = dao.FirstOrDefault <long>(sql);
int affectCount = 0;
if (activityId > 0)
{
sql = Sql.Builder.Append("Update tn_Activities Set SourceId=@0,DateCreated=@1,LastModified=@2", sourceId, DateTime.UtcNow, DateTime.UtcNow)
.Where("ActivityId=@0", activityId);
affectCount = dao.Execute(sql);
}
dao.CloseSharedConnection();
if (affectCount <= 0 || activityId <= 0)
{
return(false);
}
Activity activity = Get(activityId);
if (activity == null)
{
return(false);
}
activity.SourceId = sourceId;
activity.DateCreated = DateTime.UtcNow;
activity.LastModified = DateTime.UtcNow;
this.OnUpdated(activity);
return(true);
}
/// <summary>
/// 对操作对象进行顶操作
/// </summary>
/// <param name="objectId">操作对象Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="userId">操作用户Id</param>
/// <param name="mode">顶踩的操作模式</param>
/// <returns>是否操作成功，Ture-成功</returns>
public bool Support(long objectId, string tenantTypeId, long userId, AttitudeMode mode = AttitudeMode.Bidirection)
{
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
AttitudeSettings attitudeSettings = attitudeSettingsManager.Get();
AttitudeOnlySupportSettings attitudeOnlySupportSettings = attitudeOnlySupportSettingsManager.Get();
bool returnValue = false;
var sql = Sql.Builder;
sql.Where("ObjectId = @0 and TenantTypeId = @1", objectId, tenantTypeId);
Attitude entity = dao.FirstOrDefault <Attitude>(sql);
if (entity == null)
{
entity = Attitude.New();
entity.TenantTypeId = tenantTypeId;
entity.ObjectId = objectId;
dao.Insert(entity);
}
#region 判断是否双向
if (AttitudeMode.Bidirection == mode)
{
#region 判断是否可修改
//判断是否可修改
if (attitudeSettings.IsModify)
{
#region 判断是否取消
//判断是否可取消
if (attitudeSettings.EnableCancel)
{
IList <Sql> sqls = new List <Sql>();
int affectCount = 0;
//判断是否有过操作记录
bool?isSupport = IsSupport(objectId, tenantTypeId, userId);
switch (isSupport)
{ //顶过记录
case true:
returnValue = false;
break;
//踩过记录
case false:
sqls.Add(Sql.Builder.Append(@"DELETE FROM tn_AttitudeRecords WHERE (UserId = @0) AND (TenantTypeId = @1) and (ObjectId = @2)"
, userId, tenantTypeId, objectId));
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes
SET OpposeCount = OpposeCount - 1, Comprehensive = Comprehensive - @0
WHERE (ObjectId = @1) AND (TenantTypeId = @2)"
, attitudeSettings.OpposeWeights, objectId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
//清除顶踩状态缓存
cacheService.Remove(GetCacheKey_IsSupport(objectId, tenantTypeId, userId));
}
returnValue = true;
}
else
{
returnValue = false;
}
break;
default:
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes
SET SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0
WHERE (ObjectId = @1) AND (TenantTypeId = @2)"
, attitudeSettings.SupportWeights, objectId, tenantTypeId));
sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)"
, objectId, userId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject);
}
returnValue = true;
}
else
{
returnValue = false;
}
break;
}
}
else
{
IList <Sql> sqls = new List <Sql>();
int affectCount = 0;
//判断是否有过操作记录
bool?isSupport = IsSupport(objectId, tenantTypeId, userId);
switch (isSupport)
{ //已经顶过
case true:
returnValue = false;
break;
//已经踩过
case false:
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes
SET OpposeCount = OpposeCount - 1 ,SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0 - @1
WHERE (ObjectId = @2) AND (TenantTypeId = @3)"
, attitudeSettings.SupportWeights, attitudeSettings.OpposeWeights, objectId, tenantTypeId
));
sqls.Add(Sql.Builder.Append(@"UPDATE tn_AttitudeRecords
SET IsSupport =1
WHERE(ObjectId = @0) AND (UserId = @1) AND (TenantTypeId = @2)"
, objectId, userId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject);
}
returnValue = true;
}
else
{
returnValue = false;
}
break;
default:
//没有操作过的记录就 添加记录
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes
SET SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0
WHERE (ObjectId = @1) AND (TenantTypeId = @2)"
, attitudeSettings.SupportWeights, objectId, tenantTypeId));
sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)"
, objectId, userId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject);
}
returnValue = true;
}
else
{
returnValue = false;
}
break;
}
}
#endregion 判断是否取消
}
else
{
//先判断一下是否有顶踩记录
IList <Sql> sqls = new List <Sql>();
int affectCount = 0;
//判断是否有过操作记录
bool?isSupport = IsSupport(objectId, tenantTypeId, userId);
if (isSupport == null)
{
//没有记录就 添加记录
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount + 1, Comprehensive = Comprehensive + @0
WHERE (ObjectId = @2) AND (TenantTypeId = @3)"
, attitudeSettings.SupportWeights, attitudeSettings.OpposeWeights, objectId, tenantTypeId
));
sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)"
, objectId, userId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject);
}
returnValue = true;
}
else
{
returnValue = false;
}
}
}
#endregion 判断是否可修改
}
else
{ //单向
#region 是否可取消操作
//是否可取消操作
if (attitudeOnlySupportSettings.IsCancel)
{
IList <Sql> sqls = new List <Sql>();
int affectCount = 0;
//判断是否有过操作记录
bool?isSupport = IsSupport(objectId, tenantTypeId, userId);
switch (isSupport)
{
//顶过记录
case true:
sqls.Add(Sql.Builder.Append(@"DELETE FROM tn_AttitudeRecords WHERE UserId = @0 and TenantTypeId = @1 and ObjectId = @2"
, userId, tenantTypeId, objectId));
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes
SET SupportCount = SupportCount-1, Comprehensive = SupportCount-1
WHERE ObjectId = @0 and TenantTypeId = @1", objectId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId); //更新缓存
Get(objectId, tenantTypeId, false);
//清除顶踩状态缓存
cacheService.Remove(GetCacheKey_IsSupport(objectId, tenantTypeId, userId));
}
returnValue = true;
}
else
{
returnValue = false;
}
break;
default:
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount+1, Comprehensive = SupportCount+1
WHERE (ObjectId = @0) AND (TenantTypeId = @1)"
, objectId, tenantTypeId));
sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)"
, objectId, userId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject);
}
returnValue = true;
}
else
{
returnValue = false;
}
break;
}
}
else
{
//不可取消的单向操作
IList <Sql> sqls = new List <Sql>();
int affectCount = 0;
//判断是否有过操作记录
bool?isSupport = IsSupport(objectId, tenantTypeId, userId);
if (isSupport == null)
{
sqls.Add(Sql.Builder.Append(@"UPDATE tn_Attitudes SET SupportCount = SupportCount + 1, Comprehensive = SupportCount + 1
WHERE (ObjectId = @0) AND (TenantTypeId = @1)"
, objectId, tenantTypeId));
sqls.Add(Sql.Builder.Append(@"INSERT INTO tn_AttitudeRecords (ObjectId, UserId, TenantTypeId, IsSupport) VALUES (@0, @1, @2, 1)"
, objectId, userId, tenantTypeId));
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(AttitudeRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
//更新缓存
Get(objectId, tenantTypeId, false);
cacheService.Set(GetCacheKey_IsSupport(objectId, tenantTypeId, userId), true, CachingExpirationType.SingleObject);
}
returnValue = true;
}
else
{
returnValue = false;
}
}
}
#endregion 是否可取消操作
}
#endregion 判断是否双向
dao.CloseSharedConnection();
return(returnValue);
}
/// <summary>
/// 刷新数据库
/// </summary>
/// <remarks>
/// 通过Task调用
/// </remarks>
public void Refresh(ConcurrentDictionary <string, OnlineUser> OnlineUsersForProcess)
{
//设计要点：
//1、把OnlineUsersForProcess更新到数据库，如果UserName存在则更新LastActivityTime，否则添加新在线用户
// 可参考CountRepository.ExecQueue()
//2、把超期未活动的用户移除
//3、更新tn_OnlineUserStatistics : 每日一条记录保留最高记录（依据UserCount）
PetaPocoDatabase dao = CreateDAO();
try
{
dao.OpenSharedConnection();
foreach (string key in OnlineUsersForProcess.Keys)
{
OnlineUser onlineUserInDic = null;
OnlineUsersForProcess.TryRemove(key, out onlineUserInDic);
var sql = Sql.Builder;
sql.Select("*")
.From("tn_OnlineUsers")
.Where("UserName = @0", key);
OnlineUser onlineUserInDB = dao.FirstOrDefault <OnlineUser>(sql);
if (onlineUserInDB == null)
{
dao.Insert(onlineUserInDic);
}
else
{
sql = Sql.Builder;
sql.Append("Update tn_OnlineUsers set LastActivityTime=@0,LastAction=@1", onlineUserInDic.LastActivityTime, onlineUserInDic.LastAction)
.Where("UserName=@0", key);
dao.Execute(sql);
}
dao.Execute(Sql.Builder.Append("Update tn_Users set LastActivityTime=@0,LastAction=@1,IpLastActivity=@2", onlineUserInDic.LastActivityTime, onlineUserInDic.LastAction, onlineUserInDic.Ip));
}
//移除超期未活动的用户
UserSettings userSetting = DIContainer.Resolve <IUserSettingsManager>().Get();
var deleteSql = Sql.Builder;
deleteSql.Append("delete from tn_OnlineUsers")
.Where("LastActivityTime < @0", DateTime.UtcNow.AddMinutes(-userSetting.UserOnlineTimeWindow));
dao.Execute(deleteSql);
//更新tn_OnlineUserStatistics : 每日一条记录保留最高记录（依据UserCount）
int loggedUserCount = GetLoggedUsers().Count();
int anonymousUserCount = GetAnonymousUsers().Count();
int total = loggedUserCount + anonymousUserCount;
var updateSql = Sql.Builder;
updateSql.Append("update tn_OnlineUserStatistics set LoggedUserCount=@0,AnonymousCount=@1,UserCount = @2", loggedUserCount, anonymousUserCount, total)
.Where("UserCount < @0", total)
.Where("DateCreated>@0", new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day));
int num = dao.Execute(updateSql);
if (num == 0)
{
var selectSql = Sql.Builder;
selectSql.Select("*")
.From("tn_OnlineUserStatistics")
.Where("DateCreated>@0", new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day));
OnlineUserStatistic statistic = dao.FirstOrDefault <OnlineUserStatistic>(selectSql);
if (statistic == null)
{
OnlineUserStatistic userStatistic = OnlineUserStatistic.New();
userStatistic.DateCreated = DateTime.UtcNow;
userStatistic.LoggedUserCount = loggedUserCount;
userStatistic.AnonymousCount = anonymousUserCount;
userStatistic.UserCount = total;
dao.Insert(userStatistic);
}
}
}
finally
{
dao.CloseSharedConnection();
}
}
/// <summary>
/// 把实体entity添加到数据库
/// </summary>
/// <param name="entity">待创建实体</param>
/// <returns>实体主键</returns>
public override object Insert(Message entity)
{
var sql = Sql.Builder;
IList <Sql> sqls = new List <Sql>();
long senderSessionId = 0, receiverSessionId = 0;
MessageSessionRepository messageSessionRepository = new MessageSessionRepository();
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
object id = base.Insert(entity);
sql.Append("update tn_MessageSessions")
.Append("set LastMessageId = @0,MessageCount = MessageCount + 1,LastModified = @1", id, entity.DateCreated)
.Where("UserId = @0", entity.SenderUserId)
.Where("OtherUserId = @0", entity.ReceiverUserId);
//判断发件人的会话是否存在
if (dao.Execute(sql) == 0)
{
sql = Sql.Builder;
sql.Append(@"insert into tn_MessageSessions(UserId,OtherUserId,LastMessageId,MessageType,MessageCount,LastModified) values (@0,@1,@2,@3,1,@4)", entity.SenderUserId, entity.ReceiverUserId, id, (int)entity.MessageType, entity.DateCreated);
dao.Execute(sql);
}
//获取发件人的会话Id
sql = Sql.Builder;
sql.Select("SessionId")
.From("tn_MessageSessions")
.Where("UserId = @0", entity.SenderUserId)
.Where("OtherUserId = @0", entity.ReceiverUserId);
senderSessionId = dao.FirstOrDefault <long>(sql);
//判断收件人的会话是否存在
sql = Sql.Builder;
sql.Append("update tn_MessageSessions")
.Append("set LastMessageId = @0,MessageCount = MessageCount + 1,UnreadMessageCount = UnreadMessageCount + 1,LastModified = @1", id, entity.DateCreated)
.Where("UserId = @0", entity.ReceiverUserId)
.Where("OtherUserId = @0", entity.SenderUserId);
if (dao.Execute(sql) == 0)
{
sql = Sql.Builder;
sql.Append("insert into tn_MessageSessions(UserId,OtherUserId,LastMessageId,MessageType,MessageCount,UnreadMessageCount,LastModified) values (@0,@1,@2,@3,1,1,@4)", entity.ReceiverUserId, entity.SenderUserId, id, (int)entity.MessageType, entity.DateCreated);
dao.Execute(sql);
}
//获取收件人的会话Id
sql = Sql.Builder;
sql.Select("SessionId")
.From("tn_MessageSessions")
.Where("UserId = @0", entity.ReceiverUserId)
.Where("OtherUserId = @0", entity.SenderUserId);
receiverSessionId = dao.FirstOrDefault <long>(sql);
//添加会话与私信的关系
sqls.Add(Sql.Builder.Append("insert into tn_MessagesInSessions (SessionId,MessageId) values (@0,@1)", senderSessionId, id));
sqls.Add(Sql.Builder.Append("insert into tn_MessagesInSessions (SessionId,MessageId) values (@0,@1)", receiverSessionId, id));
dao.Execute(sqls);
dao.CloseSharedConnection();
#region 缓存处理
if (RealTimeCacheHelper.EnableCache)
{
RealTimeCacheHelper.IncreaseAreaVersion("UserId", entity.SenderUserId);
RealTimeCacheHelper.IncreaseAreaVersion("UserId", entity.ReceiverUserId);
var realTimeCacheHelper = EntityData.ForType(typeof(MessageInSession)).RealTimeCacheHelper;
realTimeCacheHelper.IncreaseAreaVersion("SessionId", senderSessionId);
realTimeCacheHelper.IncreaseAreaVersion("SessionId", receiverSessionId);
realTimeCacheHelper = EntityData.ForType(typeof(MessageSession)).RealTimeCacheHelper;
realTimeCacheHelper.IncreaseAreaVersion("UserId", entity.SenderUserId);
realTimeCacheHelper.IncreaseAreaVersion("UserId", entity.ReceiverUserId);
realTimeCacheHelper.IncreaseEntityCacheVersion(senderSessionId);
realTimeCacheHelper.IncreaseEntityCacheVersion(receiverSessionId);
string cacheKey = realTimeCacheHelper.GetCacheKeyOfEntity(senderSessionId);
MessageSession senderSession = cacheService.Get <MessageSession>(cacheKey);
if (senderSession != null)
{
senderSession.LastMessageId = entity.MessageId;
senderSession.LastModified = entity.DateCreated;
senderSession.MessageCount++;
}
cacheKey = realTimeCacheHelper.GetCacheKeyOfEntity(receiverSessionId);
MessageSession receiverSession = cacheService.Get <MessageSession>(cacheKey);
if (receiverSession != null)
{
receiverSession.LastMessageId = entity.MessageId;
receiverSession.LastModified = entity.DateCreated;
receiverSession.MessageCount++;
receiverSession.UnreadMessageCount++;
}
cacheKey = GetCacheKey_UnreadCount(entity.ReceiverUserId);
int?count = cacheService.Get(cacheKey) as int?;
count = count ?? 0;
count++;
cacheService.Set(cacheKey, count, CachingExpirationType.SingleObject);
}
#endregion 缓存处理
return(id);
}
/// <summary>
/// 更新用户隐私设置指定对象
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="specifyObjects"><remarks>key=itemKey,value=用户指定对象集合</remarks></param>
public void UpdateUserPrivacySpecifyObjects(long userId, Dictionary <string, IEnumerable <UserPrivacySpecifyObject> > specifyObjects)
{
//done:zhangp,by zhengw:
//先遍历集合specifyObjects
//再使用sql语句来获取当前itemKey下的所有指定对象集合oldItemSpecifyObjects
//比较oldItemSpecifyObjects和newItemSpecifyObjects
//1.如果newItemSpecifyObjects为null，则清空
//2.新旧数据取交集，然后进行删除和插入
//done:zhangp,by zhengw:这些局部变量没必要在外边实例化，后面肯定会被覆盖、
//回复：已修改
PetaPocoDatabase dao = CreateDAO();
var sql = Sql.Builder;
List <UserPrivacySpecifyObject> userPrivacySpecifyObjects;
IEnumerable <UserPrivacySpecifyObject> pairs;
IEnumerable <UserPrivacySpecifyObject> deletePairs;
IEnumerable <UserPrivacySpecifyObject> insertPairs;
if (specifyObjects == null || specifyObjects.Count() == 0)
{
return;
}
dao.OpenSharedConnection();
foreach (var item in specifyObjects)
{
sql = Sql.Builder;
sql.Select("tn_UserPrivacySpecifyObjects.*")
.From("tn_UserPrivacySpecifyObjects")
.InnerJoin("tn_UserPrivacySettings")
.On("tn_UserPrivacySettings.Id = tn_UserPrivacySpecifyObjects.UserPrivacySettingId")
.Where("UserId = @0", userId)
.Where("ItemKey = @0", item.Key);
userPrivacySpecifyObjects = dao.Fetch <UserPrivacySpecifyObject>(sql);
//done:zhangp,by zhengw:userPrivacySpecifyObjects.Count为0时就不更新了？我觉得可以把这个判断去掉，两个边界情况都可以使用求交集的办法
//回复：已修改
sql = Sql.Builder;
sql.Select("Id")
.From("tn_UserPrivacySettings")
.Where("UserId = @0", userId)
.Where("ItemKey = @0", item.Key);
long userPrivacySettingId = dao.FirstOrDefault <long>(sql);
if (item.Value == null)
{
foreach (var oldSpecifyObject in userPrivacySpecifyObjects)
{
base.Delete(oldSpecifyObject);
}
}
pairs = userPrivacySpecifyObjects.Intersect(item.Value);
deletePairs = userPrivacySpecifyObjects.Except(pairs);
foreach (var deletePair in deletePairs)
{
base.Delete(deletePair);
}
insertPairs = item.Value.Except(pairs);
foreach (var insertPair in insertPairs)
{
insertPair.UserPrivacySettingId = userPrivacySettingId;
base.Insert(insertPair);
}
}
dao.CloseSharedConnection();
RealTimeCacheHelper.IncreaseAreaVersion("UserId", userId);
}
/// <summary>
/// 为多个内容项添加相同标签
/// </summary>
/// <param name="itemIds">内容项Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
/// <param name="tagName">标签名</param>
public int AddItemsToTag(IEnumerable <long> itemIds, string tenantTypeId, long ownerId, string tagName)
{
PetaPocoDatabase dao = CreateDAO();
IList <Sql> sqls = new List <Sql>();
var sql = Sql.Builder;
dao.OpenSharedConnection();
//创建标签
sql.From("tn_Tags")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName);
var tag = dao.FirstOrDefault <Tag>(sql);
if (tag == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_Tags (TenantTypeId,TagName,DateCreated) values(@0,@1,@2)", tenantTypeId, tagName, DateTime.UtcNow));
}
//创建标签与用户的关联
sql = Sql.Builder;
sql.From("tn_TagsInOwners")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId);
var tagInOwner = dao.FirstOrDefault <TagInOwner>(sql);
if (tagInOwner == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_TagsInOwners (TenantTypeId,TagName,OwnerId) values(@0,@1,@2)", tenantTypeId, tagName, ownerId));
}
int affectCount = 0, itemCount = 0;
foreach (var itemId in itemIds)
{
if (itemId <= 0)
{
continue;
}
//创建标签与内容项的关联
sqls.Add(Sql.Builder.Append("insert into tn_ItemsInTags (TagName,TagInOwnerId,ItemId,TenantTypeId) select @0,Id,@1,@2 from tn_TagsInOwners", tagName, itemId, tenantTypeId)
.Where("TenantTypeId = @0", tenantTypeId)
.Where("OwnerId = @0", ownerId)
.Where("TagName = @0", tagName));
itemCount++;
}
//增加标签相关统计
sqls.Add(Sql.Builder.Append("update tn_Tags Set ItemCount = ItemCount + @2,OwnerCount = OwnerCount + 1 where TenantTypeId = @0 and TagName = @1", tenantTypeId, tagName, itemCount));
//增加拥有者标签内容项统计
sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount + @3 where TenantTypeId = @0 and TagName = @1 and OwnerId = @2", tenantTypeId, tagName, ownerId, itemCount));
//通过事务来控制多条语句执行时的一致性
using (var transaction = dao.GetTransaction())
{
dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
foreach (var itemId in itemIds)
{
if (itemId <= 0)
{
continue;
}
RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId);
}
sql = Sql.Builder;
sql.Select("*")
.From("tn_TagsInOwners")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId);
tagInOwner = dao.First <TagInOwner>(sql);
EntityData entityData = EntityData.ForType(typeof(TagInOwner));
if (tagInOwner != null)
{
entityData.RealTimeCacheHelper.IncreaseEntityCacheVersion(tagInOwner.Id);
}
entityData.RealTimeCacheHelper.IncreaseAreaVersion("OwnerId", ownerId);
}
dao.CloseSharedConnection();
return(affectCount);
}
/// <summary>
/// 对操作对象进行星级评价操作
/// </summary>
/// <param name="objectId">操作对象Id</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="userId">用户的UserId</param>
/// <param name="rateNumber">星级类型</param>
/// <param name="ownerId">拥有者Id</param>
public bool Rated(long objectId, string tenantTypeId, long userId, int rateNumber, long ownerId = 0)
{
RatingSettings ratingSettings = ratingSettingsManager.Get();
//执行Sql语句的集合
List <Sql> sqls = new List <Sql>();
//判断输入的等级数是否符合标准
if (rateNumber < 1 || rateNumber > 5)
{
return(false);
}
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
//先判断是否存在评价信息
var sql = Sql.Builder;
sql.Select("Count(*)")
.From("tn_Ratings")
.Where("ObjectId = @0 AND TenantTypeId = @1", objectId, tenantTypeId);
if (ownerId > 0)
{
sql.Where("OwnerId = @0", ownerId);
}
int affectCount = dao.ExecuteScalar <int>(sql);
//如果记录为0则不存在则先创建评价信息
if (affectCount == 0)
{
//创建 评价信息
sqls.Add(Sql.Builder.Append("INSERT INTO tn_Ratings (ObjectId, TenantTypeId, OwnerId, RateCount, Comprehensive , RateSum ) VALUES (@0, @1, @2,1,@3,@4)", objectId, tenantTypeId, ownerId, rateNumber, rateNumber));
//创建时直接添加新记录数据
for (int i = 1; i <= 5; i++)
{
if (rateNumber == i)
{ //如果等级是用户所选择的等级则直接插入数据 并且创建记录
sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingGrades (ObjectId, TenantTypeId, RateNumber, RateCount) VALUES (@0,@1,@2,1)", objectId, tenantTypeId, i));
sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingRecords (ObjectId, TenantTypeId, RateNumber, UserId, DateCreated) VALUES (@0,@1,@2,@3,@4)", objectId, tenantTypeId, i, userId, DateTime.UtcNow));
continue;
}
sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingGrades (ObjectId, TenantTypeId, RateNumber, RateCount) VALUES (@0,@1,@2,@3)", objectId, tenantTypeId, i, 0));
}
}
else
{
//判断是都有过评价记录
sql = Sql.Builder;
sql.Select("*")
.From("tn_RatingRecords")
.Where("ObjectId = @0 AND TenantTypeId = @1 AND UserId = @2", objectId, tenantTypeId, userId);
//获取评价的记录
RatingRecord ratingRecord = dao.FirstOrDefault <RatingRecord>(sql);
if (ratingRecord == null)
{
sqls.Add(Sql.Builder.Append("INSERT INTO tn_RatingRecords (ObjectId, TenantTypeId, RateNumber, UserId, DateCreated) VALUES (@0,@1,@2,@3,@4)", objectId, tenantTypeId, rateNumber, userId, DateTime.UtcNow));
//更新信息评价数据
sqls.Add(Sql.Builder.Append("UPDATE tn_Ratings SET RateCount = RateCount + 1, Comprehensive=(RateSum + @0)/(RateCount + 1.0) , RateSum=RateSum + @0 where ObjectId = @1 and TenantTypeId = @2 and OwnerId = @3", rateNumber, objectId, tenantTypeId, ownerId));
//更新评价等级统计
sqls.Add(Sql.Builder.Append("UPDATE tn_RatingGrades SET RateCount = RateCount + 1 where ObjectId = @0 and TenantTypeId = @1 and RateNumber = @2", objectId, tenantTypeId, rateNumber));
}
else
{
//用户 再次评价 先判断是否可修改
if (ratingSettings.IsModify)
{
//先检测是否是之前选过的等级
if (ratingRecord.RateNumber != rateNumber)
{
sqls.Add(Sql.Builder.Append("UPDATE tn_RatingRecords SET RateNumber = @0 WHERE ObjectId = @1 AND TenantTypeId = @2 AND UserId = @3", rateNumber, objectId, tenantTypeId, userId));
//先更新之前的等级统计
sqls.Add(Sql.Builder.Append("UPDATE tn_RatingGrades SET RateCount = RateCount - 1 where ObjectId = @0 and TenantTypeId = @1 and RateNumber = @2", objectId, tenantTypeId, ratingRecord.RateNumber));
//然后更新现在的最新等级统计
sqls.Add(Sql.Builder.Append("UPDATE tn_RatingGrades SET RateCount = RateCount + 1 where ObjectId = @0 and TenantTypeId = @1 and RateNumber = @2", objectId, tenantTypeId, rateNumber));
//之后更新评价信息表
sqls.Add(Sql.Builder.Append("UPDATE tn_Ratings SET Comprehensive = (RateSum + @0 - @1)/RateCount , RateSum=RateSum + @0 - @1 where ObjectId=@2 and TenantTypeId = @3 and OwnerId = @4", (float)rateNumber, (float)ratingRecord.RateNumber, objectId, tenantTypeId, ownerId));
}
}
}
}
if (sqls == null)
{
return(false);
}
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
if (RealTimeCacheHelper.EnableCache)
{
EntityData.ForType(typeof(RatingRecord)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
EntityData.ForType(typeof(RatingGrade)).RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
RealTimeCacheHelper.IncreaseAreaVersion("ObjectId", objectId);
}
}
else
{
return(false);
}
dao.CloseSharedConnection();
return(true);
}
/// <summary>
/// 为内容项批量设置标签
/// </summary>
/// <param name="tagNames">标签名称集合</param>
/// <param name="tenantTypeId">租户类型Id</param>
/// <param name="ownerId">拥有者Id</param>
/// <param name="itemId">内容项Id</param>
public int AddTagsToItem(string[] tagNames, string tenantTypeId, long ownerId, long itemId)
{
int affectCount = 0;
PetaPocoDatabase dao = CreateDAO();
dao.OpenSharedConnection();
foreach (string tagName in tagNames)
{
if (string.IsNullOrEmpty(tagName))
{
continue;
}
IList <Sql> sqls = new List <Sql>();
//创建标签
var sql = Sql.Builder;
sql.From("tn_Tags")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName);
var tag = dao.FirstOrDefault <Tag>(sql);
if (tag == null)
{
sqls.Add(Sql.Builder.Append("insert into tn_Tags (TenantTypeId,TagName,DateCreated) values (@0,@1,@2)", tenantTypeId, tagName, DateTime.UtcNow));
}
//增加标签相关统计
sqls.Add(Sql.Builder.Append("update tn_Tags Set ItemCount = ItemCount + 1 where TenantTypeId = @0 and TagName = @1", tenantTypeId, tagName));
//创建标签与用户的关联
sql = Sql.Builder;
sql.From("tn_TagsInOwners")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId);
var tagInOwner = dao.FirstOrDefault <TagInOwner>(sql);
if (tagInOwner == null)
{
sqls.Add(Sql.Builder.Append("update tn_Tags Set OwnerCount = OwnerCount + 1 where TenantTypeId = @0 and TagName = @1", tenantTypeId, tagName));
dao.Execute(Sql.Builder.Append("insert into tn_TagsInOwners (TenantTypeId,TagName,OwnerId) values (@0,@1,@2)", tenantTypeId, tagName, ownerId));
tagInOwner = dao.FirstOrDefault <TagInOwner>(sql);
}
else
{
//增加拥有者标签内容项统计
sqls.Add(Sql.Builder.Append("update tn_TagsInOwners set ItemCount = ItemCount + 1 where TenantTypeId = @0 and TagName = @1 and OwnerId = @2", tenantTypeId, tagName, ownerId));
}
sql = Sql.Builder;
sql.From("tn_ItemsInTags")
.Where("TagName = @0", tagName)
.Where("TagInOwnerId = @0", tagInOwner.Id)
.Where("ItemId = @0", itemId);
var itemInTag = dao.FirstOrDefault <ItemInTag>(sql);
if (itemInTag == null)
{
//创建标签与内容项的关联
sqls.Add(Sql.Builder.Append("insert into tn_ItemsInTags (TagName,TagInOwnerId,ItemId,tenantTypeId) select @0,Id,@1,@2 from tn_TagsInOwners", tagName, itemId, tenantTypeId)
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId));
}
//通过事务来控制多条语句执行时的一致性
using (var transaction = dao.GetTransaction())
{
affectCount = dao.Execute(sqls);
transaction.Complete();
}
if (affectCount > 0)
{
sql = Sql.Builder;
sql.From("tn_TagsInOwners")
.Where("TenantTypeId = @0", tenantTypeId)
.Where("TagName = @0", tagName)
.Where("OwnerId = @0", ownerId);
tagInOwner = dao.First <TagInOwner>(sql);
if (tagInOwner != null)
{
EntityData.ForType(typeof(TagInOwner)).RealTimeCacheHelper.IncreaseEntityCacheVersion(tagInOwner.Id);
}
}
}
if (tagNames.Length > 0)
{
EntityData.ForType(typeof(TagInOwner)).RealTimeCacheHelper.IncreaseAreaVersion("OwnerId", ownerId);
RealTimeCacheHelper.IncreaseAreaVersion("ItemId", itemId);
foreach (var tagName in tagNames)
{
if (string.IsNullOrEmpty(tagName))
{
continue;
}
RealTimeCacheHelper.IncreaseAreaVersion("TagName", tagName);
}
}
dao.CloseSharedConnection();
return(affectCount);
}