/// <summary>
/// 隐藏问题的答案
/// </summary>
/// <param name="answerId"></param>
/// <returns></returns>
public int HideAnswer(int answerId)
{
var answer = GetUserAnswerById(answerId);
var question = GetQuestionById(answer.QuestionId);
if (answer.IsHide == 0)
{
_dataAccess.ExecuteSql(
@"UPDATE Int_Question SET AnswerCount=AnswerCount-1 WHERE QuestionId=(SELECT QuestionId FROM Int_UserAnswer WHERE AnswerId=" +
answerId + ")");
var result = _dataAccess.UpdateField("Int_UserAnswer", "IsHide", "1", "AnswerId=" + answerId);
SetNewAnswer(question.QuestionId);
return(result);
}
else
{
_dataAccess.ExecuteSql(
@"UPDATE Int_Question SET AnswerCount=AnswerCount+1 WHERE QuestionId=(SELECT QuestionId FROM Int_UserAnswer WHERE AnswerId=" +
answerId + ")");
SetNewAnswer(question.QuestionId);
return(_dataAccess.UpdateField("Int_UserAnswer", "IsHide", "0", "AnswerId=" + answerId));
}
}
/// <summary>
/// 添加关注
/// </summary>
/// <param name="userId">用户Id</param>
/// <param name="attentionUserId">被关注者</param>
public void AddAttention(int userId, int attentionUserId)
{
var sql = string.Format(@"INSERT INTO Int_Relationship (UserId ,UserId2)
VALUES({0} ,{1}) ", userId, attentionUserId);
_dataAccess.ExecuteSql(sql);
}
public void DeleteAbilityResources(IEnumerable <int> recordId)
{
if (recordId.Count() == 0)
{
return;
}
_dataAccess.UpdateField("Ab_AbilityResource", "IsDelete", "1", string.Format("where RecordId in ({0}) ", recordId.GetString()));
//技能中删除了资源，需要重新计算参加岗位胜任力的学员是否通过
var sql = @"
select * from (
select Ab_PostLearnRecord.*,
(
select count(0)
from Ab_AbilityResource
left join Ab_PostAbility on Ab_AbilityResource.AbilityId = Ab_PostAbility.AbilityId and Ab_PostAbility.PostId =Ab_PostLearnRecord.PostId
left join Len_LearningRecord on Len_LearningRecord.LearnPath = 2
and Len_LearningRecord.UserId = Ab_PostLearnRecord.UserId
and Len_LearningRecord.SourceId = Ab_PostAbility.RecordId
and Len_LearningRecord.CourseId = Ab_AbilityResource.ResourceId
where Ab_AbilityResource.ResourceType = 0 and Ab_AbilityResource.IsDelete = 0
and Ab_AbilityResource.ResourceId not in (select CourseId from Ab_PostElectiveCourse where PostId = Ab_PostLearnRecord.PostId)
and Ab_PostAbility.IsDelete = 0
and (Len_LearningRecord.LearnProcess is null or Len_LearningRecord.LearnProcess = 1)
) as NoPassCourseCount
from dbo.Ab_PostLearnRecord where EndTime is null
) as t where t.NoPassCourseCount = 0";
var list = _dataAccess.GetListBySql <AbPostLearnRecord>(sql);
if (!list.Any())
{
return;
}
_dataAccess.ExecuteSql("update Ab_PostLearnRecord set EndTime = '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where RecordId in (" + list.Select(p => p.RecordId).GetString() + ")");
}
/// <summary>
/// 批量删除角色
/// </summary>
/// <param name="roleIds"></param>
/// <returns></returns>
public bool BatchDeleteRole(int[] roleIds)
{
string sql = string.Format("Update Sys_Roles set IsDelete = 1 where RoleId in ({0})", roleIds.GetString());
_dataAccess.DeleteEntities <SysRoleUser>(" Sys_RoleUser.RoleId IN (" + roleIds.GetString() + ")");
return(_dataAccess.ExecuteSql(sql) > 0);
}
/// <summary>
/// 删除多个课件
/// </summary>
/// <param name="id"></param>
/// <param name="time"></param>
/// <returns></returns>
public int DeleteWares(string id, DateTime time)
{
string sql = string.Format(@"update Res_CourseWare set IsDelete=1, LastUpdateTime='{0}'
where WareId in ({1})", time, id);
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 批量下架
/// </summary>
/// <param name="courseId"></param>
/// <param name="dateTime"></param>
/// <returns></returns>
public int BatchDownCourse(string courseId, string dateTime)
{
string sql =
string.Format(@"update Res_Course set LastUpdateTime='{0}',IsPublish=2 where CourseId in ({1})",
dateTime, courseId);
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 批量删除订单
/// </summary>
/// <param name="Ids"></param>
/// <returns></returns>
public bool BatchDeleteBookBuy(int[] Ids)
{
var ids = Ids.GetString();
string sql = string.Format("DELETE FROM Res_BookBuy WHERE Id in ({0})", ids);
_dataAccess.DeleteEntities <Res_BookDeptDetials>(" Res_BookDeptDetials.BookBuyID IN (" + ids + ")");
return(_dataAccess.ExecuteSql(sql) > 0);
}
/// <summary>
/// 批量删除服务中心
/// </summary>
/// <param name="deptIds"></param>
/// <returns></returns>
public bool BatchDeleteDept(int[] deptIds)
{
var ids = deptIds.GetString();
string sql = string.Format("Update Sys_Department set IsDelete=1 where DepartmentId in ({0})", ids);
_dataAccess.DeleteEntities <SysDeptToCourse>(" Sys_DeptToCourse.DeptId IN (" + ids + ")");
return(_dataAccess.ExecuteSql(sql) > 0);
}
/// <summary>
/// 批量删除用户
/// </summary>
/// <param name="userIds"></param>
/// <returns></returns>
public bool BatchDeleteUser(int[] userIds)
{
var ids = userIds.GetString();
string sql = string.Format("Update Sys_Users set IsDelete=1 where UserId in ({0})", ids);
_dataAccess.DeleteEntities <SysRoleUser>(" Sys_RoleUser.UserId IN (" + ids + ")");
return(_dataAccess.ExecuteSql(sql) > 0);
}
/// <summary>
/// 更新状态
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int UpdateStatusLearningRecord(int id)
{
var sql = @"UPDATE Len_LearningRecord
SET LearnProcess=1
WHERE RecordId=" + id;
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 更新租户
/// </summary>
/// <param name="query">更的东西</param>
/// <param name="ids">id集合</param>
/// <returns></returns>
public int UpdateTenant(string query, string ids)
{
var sql = string.Format(@"UPDATE Sys_Tenant
SET {0}
WHERE TenantId IN ({1})", query, ids);
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 删除教材
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int DeleteTeacherBook(string ids)
{
var sql = string.Format(@"UPDATE Res_TeacherBook
SET IsDelete=1
WHERE BookId IN ({0})", ids);
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 更新分类
/// </summary>
/// <param name="sort"></param>
/// <returns></returns>
public int DeleteSort(string ids)
{
var sql = string.Format(@"
UPDATE Sys_TagSort
SET IsDelete=1
WHERE SotrID IN ({0})", ids);
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 审批
/// </summary>
/// <param name="id"></param>
/// <param name="status"></param>
/// <returns></returns>
public int UpdateLineRemitStatus(int id, int status)
{
var sql = string.Format(@"
UPDATE Money_LineRemit
SET Status={0}
WHERE ID={1}", status, id);
return(_dataAccess.ExecuteSql(sql));
}
public int DeleteExampapers(int[] ids)
{
if (ids.Length == 0)
{
return(0);
}
var deleteSql = "UPDATE Res_SurveyExampaper SET Status=1 WHERE ExampaperID IN (" + ids.GetString() + ")";
return(_dataAccess.ExecuteSql(deleteSql));
}
/// <summary>
/// 更新字段，自定义
/// </summary>
/// <param name="id"></param>
/// <param name="query"></param>
/// <returns></returns>
public int UpdateByQuery(string ids, string query)
{
var sql = string.Format(@"UPDATE Tr_TrainClass
SET {0}
WHERE ClassId in ({1})", query, ids.TrimEnd(',').TrimStart(','));
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 批量删除角色
/// </summary>
/// <param name="roleIds"></param>
/// <returns></returns>
public bool BatchDeleteRole(int[] roleIds)
{
string sql = string.Format("Update Sys_OperationsRole set IsDelete = 1 where RoleId in ({0})", roleIds.GetString());
if (_dataAccess.ExecuteSql(sql) > 0)//删除角色和用户的关联
{
var delsql = string.Format(@"DELETE FROM Sys_AdminRole
WHERE RoleId IN ({0})", roleIds.GetString());
return(_dataAccess.ExecuteSql(sql) > 0);
}
return(false);
}
/// <summary>
/// 公开资源
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int OpenResource(int[] ids)
{
string sqlwhere = @"update KL_Resource set Status=0,IsOpen=1,LastUpdateTime=GETDATE() where ResourceId in (" + ids.GetString() + ")";
foreach (var id in ids)
{
FullTextSearchProvider.UpdateIndex <KL_Resource>("knowledge", id, new { status = 0, isOpen = 1 }, true);
}
return(_dataAccess.ExecuteSql(sqlwhere));
}
/// <summary>
/// 添加积分记录
/// </summary>
/// <param name="ruleId"></param>
/// <param name="integration"></param>
/// <param name="userId"></param>
/// <param name="tenantId"></param>
/// <param name="desc"></param>
public void AddIntegration(int ruleId, int integration, int userId, int tenantId, string desc = "")
{
var userIntegral = new UserIntegral();
userIntegral.RuleId = ruleId;
userIntegral.Integral = integration;
userIntegral.TimeToGet = DateTime.Now;
userIntegral.UserId = userId;
userIntegral.IntegralDesc = desc;
#region 冗余处理
if (IsUseRedis)
{
var userTotal = rdHelper.HGet(userTotalIntegralKey, userId.ToString());
if (userTotal == null)
{
userTotal = ((int)_dataAccess.ExecuteScalar("select sum(integral) from int_userintegral where UserId=" + userId) + integration).ToString();
}
else
{
userTotal = (userTotal.ToInt32() + integration).ToString();
}
rdHelper.HSet(userTotalIntegralKey, userId.ToString(), userTotal.ToString());
}
else
{
//使用MySql
var existsSql = "SELECT COUNT(*) FROM Int_UserTotalIntegral WHERE UserId=" + userId;
if (_dataAccess.ExecuteScalar(existsSql).ToInt32() == 0)
{
//新增
var insertSql =
string.Format("INSERT into Int_UserTotalIntegral(UserId,TenantId,TotalIntegral)values({0},{1},{2});", userId, tenantId,
integration);
_dataAccess.ExecuteSql(insertSql);
}
else
{
_dataAccess.ExecuteSql(
string.Format(
"update Int_UserTotalIntegral set TotalIntegral=TotalIntegral+{0} WHERE UserId={1}",
integration, userId));
}
}
#endregion
_dataAccess.AddEntity(userIntegral);
}
/// <summary>
/// 执行批量注册审批
/// </summary>
/// <param name="approvalUserId">审批人Id</param>
/// <param name="autoIds">自增ID集合</param>
/// <param name="approvalStatus">审批状态1-通过 2-拒绝</param>
/// <param name="refuseReson">拒绝原因</param>
/// <param name="approvalMark">审批备注</param>
/// <returns></returns>
public int BatchUpdateRegisterApproval(int approvalUserId, string autoIds, int approvalStatus,
string refuseReson = "", string approvalMark = "")
{
string sql =
string.Format(@" UPDATE Sys_RegisterApproval SET ApprovalStatus={0},RefuseReson='{1}',ApprovalMark='{2}',ApprovalUserId={3},ApprovalTime='{4}'
WHERE AutoId IN ({5}) AND ApprovalStatus=0 ", approvalStatus, refuseReson, approvalMark, approvalUserId, DateTime.Now,
autoIds.GetArray().GetString());
if (approvalStatus == 1) //审批通过则将人员设为正常状态
{
sql += string.Format(@"
UPDATE Sys_Users SET IsDelete=0 WHERE UserId IN (select UserId from Sys_RegisterApproval where AutoId IN ({0})) ",
autoIds.GetArray().GetString());
}
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
///
/// </summary>
/// <param name="postIds"></param>
public void DeletePost(int[] postIds)
{
string sql = string.Format(@"update Sys_Posts set status = 1 where PostId in ({0})
update Sys_Users set postId = 0 where PostId in ({0})
", postIds.GetString());
_dataAccess.ExecuteSql(sql);
}
/// <summary>
/// 批量冻结/解冻
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public int UpdateAllAdmin(string ids, string query)
{
var sql = string.Format(@"UPDATE Sys_Admin
SET {1}
WHERE AdminId IN ({0})", ids, query);
return(_dataAccess.ExecuteSql(sql));
}
/// <summary>
/// 更新培训考试的信息
/// </summary>
/// <param name="query"></param>
/// <param name="updatesql"></param>
/// <returns></returns>
public int UpdateTrainExam(string query, string updatesql)
{
var sql = string.Format(@"
UPDATE Tr_TrainClassToExam
SET {0}
WHERE {1}", updatesql, query);
return(_dataAccess.ExecuteSql(sql));
}
public bool RemovePostAbility(int recordId)
{
var model = _dataAccess.Get <Ab_PostAbility>(recordId);
model.IsDelete = 1;
var doflag = _dataAccess.UpdateEntity(model) > 0;
if (doflag)
{
_dataAccess.ExecuteSql("delete from Ab_PostElectiveCourse where PostId = " + model.PostId + " and AbilityId = " + model.AbilityId);
}
return(doflag);
}
public void SaveTenantPermissions(int tenantId, int[] permissionIds)
{
var insertSql = new StringBuilder(@"DELETE FROM Sys_TenantFunc WHERE TenantId=" + tenantId);
foreach (int menuId in permissionIds)
{
if (menuId == 0)
{
continue;
}
insertSql.AppendFormat(@"
INSERT INTO Sys_TenantFunc
( TenantId, PermissionId )VALUES ( {0}, {1} )" , tenantId, menuId);
}
_dataAccess.ExecuteSql(insertSql.ToString());
}
private static readonly int REMOVEDIFF = 60; //多少秒执行一次删除不活动用户
/// <summary>
/// Checks the online.
/// </summary>
/// <param name="userId">User identifier.</param>
/// <param name="tenantId">Tenant identifier.</param>
public static void CheckOnline(int userId, int tenantId)
{
lock (lockHelper)
{
string sessionId = System.Web.HttpContext.Current.Session.SessionID;
//数据表中是否有用户的记录
OnlineUser onlineUser = _dataAccess.GetList <OnlineUser>("sys_onlineuser.UserId=" + userId).FirstOrDefault();// onlineUsers.Find(p => p.UserId == userId);
if (onlineUser != null && onlineUser.SessionId != sessionId)
{
//SESSION变了？？？
//onlineUsers.Remove(onlineUser);
_dataAccess.DeleteEntities <OnlineUser>("sys_onlineuser.UserId=" + userId);
onlineUser = null;
}
if (onlineUser != null)
{
//有；更新我的状态
onlineUser.LastActiveTime = DateTime.Now;
onlineUser.UserWhere = AtWhere();
onlineUser.UserId = userId;
_dataAccess.UpdateEntity(onlineUser, "SessionId");
}
else
{
//无；加入关于我的在线信息
onlineUser = new OnlineUser();
onlineUser.SessionId = sessionId;
onlineUser.TenantId = tenantId;
onlineUser.UserId = userId;
onlineUser.LastActiveTime = DateTime.Now;
onlineUser.LoginTime = DateTime.Now;
onlineUser.VisitorIp = System.Web.HttpContext.Current.Request.UserHostAddress;
onlineUser.UserWhere = AtWhere();
_dataAccess.AddEntity(onlineUser);
}
//如果离上次删除的时间间隔超过设定值
TimeSpan tsRemove = DateTime.Now - LastRemove;
if (tsRemove.Seconds > REMOVEDIFF)
{
_dataAccess.ExecuteSql(string.Format(@"DELETE FROM sys_onlineuser
where TIMESTAMPDIFF(SECOND,LastActiveTime,now())>{0};", OFFLINEDIFF * 60));
//重置上次删除时间
LastRemove = DateTime.Now;
}
}
}
/// <summary>
/// 添加回复
/// </summary>
/// <param name="reply"></param>
/// <returns></returns>
public int AddTopicReply(Reply reply)
{
var sql = @"SELECT ISNULL(MAX(OrderNum),0) FROM bbs_Reply WHERE TopicId=" + reply.TopicId;
lock (ReplyOrderHelper)
{
int orderNum = (int)_dataAccess.ExecuteScalar(sql);
reply.OrderNum = orderNum + 1;
_dataAccess.AddEntity(reply);
}
//冗余
var updateSql =
string.Format(@"UPDATE bbs_Topic
SET ReplyCount=ReplyCount+1,LastReplyUserId={0},LastReplyTime='{1}',LastUpdateTime='{1}'
WHERE TopicId={2}", reply.UserId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), reply.TopicId);
_dataAccess.ExecuteSql(updateSql);
_dataAccess.UpdateFieldCalc("bbs_SubSection", "ReplyCount", "ReplyCount+1", "SubSectionId=(SELECT SubSectionId FROM bbs_Topic WHERE TopicId=" + reply.TopicId + ")");
IndexTopic(reply.TopicId);
return(reply.ReplyId);
}
/// <summary>
/// 退回学习卡时，一并退回学习卡记录
/// </summary>
/// <param name="number">退回数量</param>
/// <param name="id">购买学习卡ID</param>
/// <returns></returns>
public int ReturnBuyCard(int number, int id)
{
return(_dataAccess.ExecuteSql(string.Format("UPDATE Res_CardSoldRecord SET Status=2 WHERE Id IN (SELECT TOP {0} id FROM Res_CardSoldRecord WHERE SoldId={1} AND Status=0)", number, id)));
}
public void PublishNotes(int noteID)
{
string sql = string.Format(@"UPDATE Sys_Notes SET PublishTime=getdate(), PublishFlag=1 WHERE NoteId={0}", noteID);
_dataAccess.ExecuteSql(sql);
}
/// <summary>
/// 审批用户
/// </summary>
/// <param name="ids"></param>
/// <param name="status"></param>
/// <param name="remark"></param>
public void ApprovalUser(string ids, int status, string remark = "")
{
string sql = string.Format(@"update Tr_TrainClassToUser set ApproveStatus='{0}',ApproveTime='{1}'
,Remark='{2}' where ID in ({3})", status.ToString(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), remark, ids);
_dataAccess.ExecuteSql(sql);
}
