public void Update_Transaction()
{
try
{
SqlMapper.BeginTransaction();
var list = SqlMapper.Query <T_Test>(new RequestContext
{
Scope = "T_Test",
SqlId = "GetList",
Request = null
});
foreach (var test in list)
{
test.Name = test.Name + "-Update";
int exeNum = SqlMapper.Execute(new RequestContext
{
Scope = "T_Test",
SqlId = "Update",
Request = test
});
}
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollbackTransaction();
throw ex;
}
}
/// <summary>
/// 上架下架网课
/// </summary>
/// <param name="Model"></param>
/// <param name="System_Station_ID"></param>
/// <returns></returns>
public bool UpdateCourseIsPutaway(CampusModel Model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
W_Course model = Orm.Single <W_Course>(x => x.System_Station_ID == System_Station_ID && x.ID == Model.ID);
if (model == null)
{
throw new ApiException("您要操作的数据不存在");
}
model.IsPutaway = Model.IsPutaway;
if (Orm.Update(model) <= 0)
{
throw new ApiException("修改失败，请重新操作");
}
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
/// <summary>
/// 拖拽排序（拖拽完就保存）
/// </summary>
/// <param name="new_prev_order">拖拽后上节点排序值</param>
/// <param name="currDisciplineId">当前被拖拽ID</param>
/// <param name="currParentId">当前被拖拽ID的父ID</param>
/// <returns></returns>
public bool MoveDiscipline(SortDisciplineModel model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
W_Discipline disModel = Orm.Single <W_Discipline>(x => x.ID == model.currDisciplineId && x.System_Station_ID == System_Station_ID);
if (disModel == null)
{
throw new ApiException("请刷新重试，您要操作的数据不存在");
}
if (model.currParentId == 0)
{
SqlMapper.Update("MoveIDDiscipline", new { new_prev_order = model.new_prev_order, currParentId = 0, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID });
}
else if (model.currParentId > 0)
{
SqlMapper.Update("MoveIDDiscipline", new { currParentId = model.currParentId, new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID });
}
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
public void Insert_Transaction()
{
try
{
SqlMapper.BeginTransaction();
int i = 0;
int insertNum = 10;
long preId = 0;
for (i = 0; i < insertNum; i++)
{
preId = SqlMapper.ExecuteScalar <long>(new RequestContext
{
Scope = "T_Test",
SqlId = "Insert",
Request = new T_Test {
Name = $"Name-{preId}"
}
});
}
SqlMapper.CommitTransaction();
Assert.Equal <int>(i, insertNum);
}
catch (Exception ex)
{
SqlMapper.RollbackTransaction();
throw ex;
}
}
public bool RemoveUsersFromRoles(string applicationName, string[] usernames, string[] rolenames)
{
bool b = false;
try {
SqlMapper.BeginTransaction();
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
Sys_UserInRole ur = new Sys_UserInRole();
ur.ApplicationName = applicationName;
ur.UserName = username;
ur.RoleName = rolename;
Delete(ur);
}
}
SqlMapper.CommitTransaction();
b = true;
}
catch (Exception ex) {
SqlMapper.RollBackTransaction();
throw ex;
}
return(b);
}
/// <summary>
/// 删除网课及对应的关系表
/// </summary>
/// <param name="Model"></param>
/// <param name="System_Station_ID"></param>
/// <returns></returns>
public bool DeleteCourse(CampusModel Model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
W_Course model = Orm.Single <W_Course>(x => x.System_Station_ID == System_Station_ID && x.ID == Model.ID);
if (model == null)
{
throw new ApiException("您要操作的数据不存在");
}
if (model.Valid == 1)
{
throw new ApiException("请先禁用，再执行删除操作");
}
int count = SqlMapper.QueryForObject <int>("GetCourseOrderCount", new { ID = Model.ID });
if (count > 0)
{
throw new ApiException("该课程已被网校学生购买无法删除！");
}
Orm.Delete <W_Course>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID);
Orm.Delete <W_Course_Chapters>(x => x.Course_ID == Model.ID && x.System_Station_ID == System_Station_ID);
int isDelete = SqlMapper.QueryForObject <int>("DeleteCourse", new { ID = Model.ID, System_Station_ID = System_Station_ID });
Orm.Delete <W_Course_Unit>(x => x.Course_ID == Model.ID && x.System_Station_ID == System_Station_ID);
Orm.Delete <W_DataInfo>(x => x.BusID == Model.ID && x.System_Station_ID == System_Station_ID);
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
/// <summary>
/// 修改组合题材料
/// </summary>
/// <param name="id"></param>
/// <param name="content"></param>
/// <returns></returns>
public bool UpdateQuestionData(int id, string content, int storeId)
{
W_QuestionData model = Orm.Single <W_QuestionData>(x => x.ID == id);
if (model == null)
{
throw new ApiException("题目不存在或者已删除");
}
model.Content = content;
List <W_Question> list = Orm.Select <W_Question>(x => x.QuestionData_ID == id).ToList();
SqlMapper.BeginTransaction();
try
{
if (Orm.Update(model) > 0)
{
foreach (W_Question question in list)
{
if (question.QuestionStore_ID != storeId)
{
question.QuestionStore_ID = storeId;
Orm.Update <W_Question>(question);
}
}
}
SqlMapper.CommitTransaction();
}
catch
{
SqlMapper.RollBackTransaction();
return(false);
}
return(true);
}
/// <summary>
/// 给章节添加题目
/// </summary>
/// <returns></returns>
public bool AddChapterQuestions(ChapterPostModel model)
{
W_Chapter paper = Orm.Single <W_Chapter>(x => x.ID == model.ChapterID);
if (paper == null)
{
throw new ApiException("章节不存在或者已删除");
}
SqlMapper.BeginTransaction();
try
{
foreach (int questionId in model.questionIds)
{
Orm.Insert <W_Chapter_Question>(new W_Chapter_Question()
{
Chapter_ID = model.ChapterID,
Question_ID = questionId
});
}
SqlMapper.CommitTransaction();
}
catch
{
SqlMapper.RollBackTransaction();
return(false);
}
return(true);
}
/// <summary>
/// 自动组卷
/// </summary>
/// <param name="examPaperId"></param>
/// <returns></returns>
public bool CombinationExamPaper(int examPaperId)
{
W_ExamPaper paper = Orm.Single <W_ExamPaper>(x => x.ID == examPaperId);
if (paper == null)
{
throw new ApiException("试卷不存在或者已删除");
}
//查出试卷所有题型
List <W_ExamPaperDetail> paperDetails = Orm.Select <W_ExamPaperDetail>(x => x.ExamPaper_ID == examPaperId).ToList();
if (paperDetails.Sum(x => x.Sorce * x.Num) < paper.PassScore)
{
throw new ApiException("试卷及格分数大于所有试卷题型总分，不符合大自然规律，请确保试卷题型总分大于及格分数再组卷");
}
//根据试卷题库ID查出题目
List <W_Question> questions = Orm.Select <W_Question>(x => x.QuestionStore_ID == paper.QuestionStore_ID).ToList();
SqlMapper.BeginTransaction();
try
{
//删除原先已组好的试题
Orm.Delete <W_ExamPaperDetail_Detail>(x => x.ExamPaper_ID == paper.ID);
//依据试卷题型的题目类型与题目数量组成试卷
foreach (W_ExamPaperDetail pd in paperDetails)
{
List <W_Question> tempList;
if (pd.QuestionType_ID != 7)
{
tempList = questions.Where(x => x.QuestionType_ID == pd.QuestionType_ID && x.QuestionData_ID == 0).OrderBy(x => Guid.NewGuid()).Take(pd.Num).ToList();
}
else//组合题
{
tempList = questions.Where(x => x.QuestionData_ID > 0).OrderBy(x => x.QuestionData_ID).Take(pd.Num).ToList();
}
if (tempList.Count < pd.Num)
{
throw new ApiException("组卷失败：" + pd.QuestionType_Name + "题目不够");
}
foreach (W_Question question in tempList)
{
Orm.Insert <W_ExamPaperDetail_Detail>(new W_ExamPaperDetail_Detail()
{
ExamPaper_ID = paper.ID,
ExamPaper_Detail_ID = pd.ID,
Question_ID = question.ID
});
}
}
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw new ApiException(ex.Message);
}
return(true);
}
/// <summary>
/// 创建目录并保存 hx add 20180105
/// </summary>
/// <param name="Message">创建消息</param>
/// <param name="PolyvInfo_ID">主表ID</param>
/// <param name="cataid">分类cataid</param>
/// <param name="Valid">禁用/启用</param>
/// <param name="PolyvSource"></param>
/// <param name="IsAdd">是添加还是修改</param>
/// <returns></returns>
public dynamic SavePolyvDetail(bool isAdd, int Station_ID, int PolyvInfo_ID, string cataid, int Valid, int PolyvSource)
{
bool save = false;
W_PolyvInfo_Detail PolyvInfoModel_Detail = new W_PolyvInfo_Detail();
//明细表添加数据
PolyvInfoModel_Detail.System_Station_ID = Station_ID;
PolyvInfoModel_Detail.PolyvInfo_ID = PolyvInfo_ID;
PolyvInfoModel_Detail.cataid = cataid;
PolyvInfoModel_Detail.Valid = Valid;
try
{
SqlMapper.BeginTransaction();//开启事务
if (isAdd)
{
//添加明细数据
Orm.Insert(PolyvInfoModel_Detail);
//修改配置表账号类型
if (SqlMapper.Update("UpdatePolyvinfoType", new { PolyvinfoType = PolyvSource, System_Station_ID = Station_ID }) > 0)
{
save = true;
}
}
else
{
//修改明细数据
if (SqlMapper.Update("UpdatePolyvInfoDetail", new { PolyvInfo_ID = PolyvInfo_ID, cataid = cataid, Valid = Valid, System_Station_ID = Station_ID }) > 0)
{
if (SqlMapper.Update("UpdatePolyvinfoType", new { PolyvinfoType = PolyvSource, System_Station_ID = Station_ID }) > 0)
{
save = true;
}
}
else
{
throw new ApiException("设置不成功，明细修改异常！");
}
}
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
return(save);
}
/// <summary>
/// 禁用、启用学科
/// </summary>
public bool SetDisciplineValid(W_Discipline model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
if (model.ID < 0)
{
throw new ApiException("无效参数");
}
W_Discipline modelDiscipline = Orm.Single <W_Discipline>(x => x.ID == model.ID && x.System_Station_ID == System_Station_ID);
if (modelDiscipline == null)
{
throw new ApiException("操作失败，您无权操作！");
}
if (model.Valid == 1)
{
if (modelDiscipline.CID != 0)
{
W_Discipline CidModel = Orm.Single <W_Discipline>(x => x.ID == modelDiscipline.CID && x.System_Station_ID == System_Station_ID);
if (CidModel.Valid == 0)
{
throw new ApiException("请先启用上级学科！");
}
}
modelDiscipline.Valid = model.Valid;
if (Orm.Update(modelDiscipline) <= 0)
{
throw new ApiException("修改失败");
}
}
else
{
List <W_Discipline> DisModel = SqlMapper.QueryForList <W_Discipline>("GetDisciplineValid", new { ID = modelDiscipline.ID, System_Station_ID = System_Station_ID }).ToList();
if (DisModel != null && DisModel.Count > 0)
{
DisModel.ForEach(X =>
{
SqlMapper.Update("UpdateDisciplineValid", new { ID = X.ID, Valid = model.Valid, System_Station_ID = System_Station_ID });
GetDiscipline_ChildSetValid(X.ID, model.Valid, System_Station_ID);
});
}
modelDiscipline.Valid = model.Valid;
if (Orm.Update(modelDiscipline) <= 0)
{
throw new ApiException("操作失败");
}
}
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
public async void QueryAsync()
{
int i = 0;
SqlMapper.BeginTransaction();
for (i = 0; i < 10; i++)
{
var list = await SqlMapper.QueryAsync <T_Test>(new RequestContext
{
Scope = "T_Test",
SqlId = "GetList",
Request = new { Ids = new long[] { 1, 2, 3, 4 } }
});
}
SqlMapper.CommitTransaction();
Assert.True(i == 10);
}
/// <summary>
/// 给试卷题型添加题目
/// </summary>
/// <returns></returns>
public bool AddPaperDetailQuestions(ExamPaperPostModel model)
{
W_ExamPaper paper = Orm.Single <W_ExamPaper>(x => x.ID == model.examPaperId);
if (paper == null)
{
throw new ApiException("试卷不存在或者已删除");
}
W_ExamPaperDetail paperDetail = Orm.Single <W_ExamPaperDetail>(x => x.ID == model.examPaperDetailId);
if (paperDetail == null)
{
throw new ApiException("试卷题型不存在或者已删除");
}
if (paperDetail.ExamPaper_ID != paper.ID)
{
throw new ApiException("试卷题型与试卷不匹配");
}
List <W_ExamPaperDetail_Detail> list = Orm.Select <W_ExamPaperDetail_Detail>(x => x.ExamPaper_ID == model.examPaperId && x.ExamPaper_Detail_ID == model.examPaperDetailId).ToList();
if (list.Count + model.questionIds.Length > paperDetail.Num)
{
throw new ApiException("选择的题目不能超过配置的数目");
}
SqlMapper.BeginTransaction();
try
{
foreach (int questionId in model.questionIds)
{
Orm.Insert <W_ExamPaperDetail_Detail>(new W_ExamPaperDetail_Detail()
{
ExamPaper_ID = model.examPaperId,
ExamPaper_Detail_ID = model.examPaperDetailId,
Question_ID = questionId
});
}
SqlMapper.CommitTransaction();
}
catch
{
SqlMapper.RollBackTransaction();
return(false);
}
return(true);
}
/// <summary>
/// 加入购物车 hx add 2018-01-13
/// </summary>
/// <returns></returns>
public dynamic PutInShoppingCar(string Course_IDs, string StuId)
{
bool put = false;
if (string.IsNullOrEmpty(Course_IDs))
{
throw new ApiException("参数Course_IDs为空！");
}
if (string.IsNullOrEmpty(StuId))
{
throw new ApiException("参数StuId为空！");
}
string[] arr = Course_IDs.Split(',');
if (arr.Length > 0)
{
try
{
SqlMapper.BeginTransaction();//开启事务
foreach (string id in arr)
{
if (id != null)
{
W_Shoppingcar model = new W_Shoppingcar();
model.Student_ID = StuId;
model.Course_ID = int.Parse(id);
Orm.Insert <W_Shoppingcar>(model);
}
}
put = true;
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
}
return(put);
}
/// <summary>
/// 删除章节题目
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public bool DeleteChapterQuestion(ChapterPostModel request)
{
SqlMapper.BeginTransaction();
try
{
foreach (var item in request.questionIds)
{
Orm.Delete <W_Chapter_Question>(x => x.Question_ID == item && x.Chapter_ID == request.ChapterID);
}
SqlMapper.CommitTransaction();
}
catch
{
SqlMapper.RollBackTransaction();
return(false);
}
return(true);
}
/// <summary>
/// 订单数据回滚
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool DeleteOrder(int id, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
if (Orm.Delete <W_Order>(x => x.ID == id && x.System_Station_ID == System_Station_ID) > 0)
{
Orm.Delete <W_Order_Detail>(x => x.CID == id);
}
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
/// <summary>
/// 批量删除订单课程 hx add 2018-01-12
/// </summary>
public bool DeleteOrderCourseByCID(CampusModel model)
{
bool flag = false;
if (string.IsNullOrEmpty(model.Ids))
{
throw new ApiException("删除参数Ids，不能为空！");
}
string[] ids = model.Ids.Split(',');
if (ids.Length > 0)
{
try
{
SqlMapper.BeginTransaction();//开启事务
for (int i = 0; i < ids.Length; i++)
{
if (!string.IsNullOrEmpty(ids[i]))
{
SqlMapper.Update("DeleteOrderCourseByCID", new { CID = ids[i] });
SqlMapper.Update("UpdateW_OrderState", new { CID = ids[i] });
}
}
SqlMapper.CommitTransaction(); //提交
flag = true;
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction(); //回滚
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
}
return(flag);
}
/// <summary>
/// Commit transaction.
/// </summary>
public bool CommitTransaction()
{
logger.Start("CommitTransaction");
logger.Info("CommitTransaction");
try
{
sqlMapEmployee.CommitTransaction();
logger.Info("CommitTransaction successful.");
logger.End("CommitTransaction.");
return(true);
}
catch (Exception ex)
{
logger.Error(ex.ToString());
error = ex;
logger.Info("CommitTransaction fail.");
logger.End("CommitTransaction.");
return(false);
}
}
/// <summary>
/// 删除订单课程 hx add 2018-01-12
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool DeleteOrderCourse(CampusModel model)
{
bool flag = false;
W_Order_Detail Ordermodel = Orm.Single <W_Order_Detail>(x => x.ID == model.orderDetailId);
if (Ordermodel == null)
{
throw new ApiException("未找到该数据，请刷新后再试");
}
try
{
SqlMapper.BeginTransaction();//开启事务
if (SqlMapper.Update("DeleteOrderCourse", new { orderDetailId = model.orderDetailId }) > 0)
{
int count = SqlMapper.QueryForObject <int>("OrderByDetailId_Count", new { CID = Ordermodel.CID });
if (count == 0)
{
SqlMapper.Update("UpdateW_OrderState", new { CID = Ordermodel.CID });
}
else
{
SqlMapper.Update("UpdateW_OrderPrice", new { CID = Ordermodel.CID });
}
flag = true;
}
SqlMapper.CommitTransaction(); //提交
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction(); //回滚
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
return(flag);
}
/// <summary>
/// 章节选择题目
/// </summary>
/// <param name="chapterId"></param>
/// <param name="list"></param>
/// <returns></returns>
public bool CheckChapterQuestion(int chapterId, List <Question> list)
{
SqlMapper.BeginTransaction();
try
{
foreach (Question question in list)
{
Orm.Insert <W_Chapter_Question>(new W_Chapter_Question()
{
Chapter_ID = chapterId,
Question_ID = question.ID
});
}
SqlMapper.CommitTransaction();
}
catch
{
SqlMapper.RollBackTransaction();
return(false);
}
return(true);
}
/// <summary>
/// 删除网课章节及对应的关系表
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public dynamic DeleteCourseChapters(W_Course_Chapters Model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
W_Course_Chapters model = Orm.Single <W_Course_Chapters>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID);
if (model == null)
{
throw new ApiException("未找到对应数据，请查看再试");
}
Orm.Delete <W_Course_Chapters>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID);
Orm.Delete <W_DataInfo>(x => x.BusID == Model.ID && x.BusType == 1);
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
/// <summary>
/// 删除单元及对应的关系表
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public dynamic DeleteCourseUnit(W_Course_Unit Model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
W_Course_Unit model = Orm.Single <W_Course_Unit>(x => x.ID == Model.ID && x.System_Station_ID == System_Station_ID);
if (model == null)
{
throw new ApiException("找不到对应数据，请重试");
}
Orm.Delete <W_Course_Unit_ClassTime>(x => x.Unit_ID == Model.ID);
Orm.Delete <W_Course_Unit>(x => x.ID == Model.ID);
Orm.Delete <W_DataInfo>(x => x.BusID == Model.ID && x.BusType == 2);
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
/// <summary>
/// 拖拽排序（拖拽完就保存）
/// </summary>
/// <param name="old_prev_order">拖拽前上节点排序值</param>
/// <param name="old_next_order">拖拽前下节点排序值</param>
/// <param name="new_prev_order">拖拽后上节点排序值</param>
/// <param name="new_next_order">拖拽后下节点排序值</param>
/// <param name="currDisciplineId">当前被拖拽ID</param>
/// <param name="currParentId">当前被拖拽ID的父ID</param>
/// <param name="System_Station_ID">系统ID</param>
/// <returns></returns>
public bool SortDiscipline(SortDisciplineModel model, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
W_Discipline Disciplinemodel = Orm.Single <W_Discipline>(x => x.ID == model.currDisciplineId && x.System_Station_ID == System_Station_ID);
if (model.new_prev_order == model.old_prev_order && model.new_next_order == model.old_next_order) //没移动
{
return(false);
}
if (model.new_prev_order == 0) //第一种情况：拖到最上面 （拖拽后上一层order为0）
{
Disciplinemodel.Orders = 1;
if (Orm.Update(Disciplinemodel) > 0)
{
if (model.old_next_order == 0) //从最下面拖到最上面
{
SqlMapper.Update("SortDiscipline", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, currDisciplineId = model.currDisciplineId });
}
else
{
SqlMapper.Update("SortDiscipline1", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, old_next_order = model.old_next_order, currDisciplineId = model.currDisciplineId });
}
}
}
else if (model.new_next_order == 0)//第二种情况：拖到最下面（拖拽后下一层order为0）
{
if (SqlMapper.Update("SortDiscipline2", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, currDisciplineId = model.currDisciplineId }) > 0)
{
if (model.old_prev_order == 0) //从最上面拖到最下面
{
SqlMapper.Update("SortDiscipline3", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, currDisciplineId = model.currDisciplineId });
}
else
{
SqlMapper.Update("SortDiscipline4", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, old_prev_order = model.old_prev_order, currDisciplineId = model.currDisciplineId });
}
}
}
else if (model.new_prev_order > 0 && model.new_next_order > 0) //第三种情况：拖到中间
{
if (model.new_prev_order < model.old_prev_order && model.new_next_order < model.old_next_order) //往上拖
{
if (SqlMapper.Update("SortDiscipline5", new { new_next_order = model.new_next_order, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID }) > 0)
{
if (model.old_next_order == 0) //从最下面往上拖
{
SqlMapper.Update("SortDiscipline6", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId });
}
else
{
SqlMapper.Update("SortDiscipline7", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, old_next_order = model.old_next_order, new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId });
}
}
}
else if (model.new_prev_order > model.old_prev_order && model.new_next_order > model.old_next_order)//往下拖
{
if (SqlMapper.Update("SortDiscipline8", new { new_prev_order = model.new_prev_order, currDisciplineId = model.currDisciplineId, System_Station_ID = System_Station_ID }) > 0)
{
if (model.old_prev_order == 0) //从最上面往下拖
{
SqlMapper.Update("SortDiscipline9", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, new_next_order = model.new_next_order, currDisciplineId = model.currDisciplineId });
}
else
{
SqlMapper.Update("SortDiscipline10", new { currParentId = model.currParentId, System_Station_ID = System_Station_ID, new_next_order = model.new_next_order, old_prev_order = model.old_prev_order, currDisciplineId = model.currDisciplineId });
}
}
}
}
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
public void TestTransaction()
{
var sqlCmd = $"{ConvertSeparate(Insert, DatabaseType.PostgreSQL)}; update account set \"name\" = 'Eddie in transaction' where id in (SELECT currval(pg_get_serial_sequence('account','id')));";
var result = Brook.Load("posql").Transaction(sqlCmd, new List <DbParameter[]> {
new[]
{
Brook.Load("posql").Parameter("@name", "QQ1"),
Brook.Load("posql").Parameter("@email", $"*****@*****.**")
}, new[]
{
Brook.Load("posql").Parameter("@name", "QQ2"),
Brook.Load("posql").Parameter("@email", $"*****@*****.**")
}
});
if (!result.Ok)
{
TestContext.WriteLine($"Why:{result.Err}");
}
result = Brook.Load("posql").Transaction("insert into account (name,email)values('Eddie', '@email');");
if (!result.Ok)
{
TestContext.WriteLine($"Why:{result.Err}");
}
var qq = Brook.Load("posql").First <Account>("SELECT id AS \"Id\", name AS \"Name\", email AS \"Email\",time AS \"Time\" FROM account order by id desc limit 1;");
SqlMapper db = null;
try
{
db = Brook.Load("posql");
var name = $"我是交易1-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var t = db.Table(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[Table] Id = {t.Rows[0]["Id"]} Name = {t.Rows[0]["Name"]} Email = {t.Rows[0]["Email"]} ");
db.CommitTransaction();
name = $"我是交易2-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var account = db.First <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[First] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
var accounts = db.Query <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[Query] Id = {accounts[0].Id} Name = {accounts[0].Name} Email = {accounts[0].Email}");
db.CommitTransaction();
name = $"我是交易3-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
db.RollbackTransaction();
name = $"我不是交易1-{DateTime.Now:HHmmss}";
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var ds = db.DataSet(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[DataSet] Id = {ds.Tables[0].Rows[0]["Id"]} Name = {ds.Tables[0].Rows[0]["Name"]} Email = {ds.Tables[0].Rows[0]["Email"]}");
name = $"我是交易4-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
db.ChangeDatabase("postgres");
//throw new Exception("QQ");
}
catch (Exception e)
{
TestContext.WriteLine(e);
if (db != null)
{
db.RollbackTransaction();
db.Dispose();
}
}
}
/// <summary>
/// 添加老师
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool TeacherInsert(TeacherModel model)
{
bool add = false;
if (model.System_Station_ID == 0)
{
throw new ApiException("机构ID不存在！");
}
if (string.IsNullOrEmpty(model.Name))
{
throw new ApiException("姓名不能为空！");
}
if (string.IsNullOrEmpty(model.CardNo))
{
//throw new ApiException("身份证不能为空！");
}
else
{
string CardNumRegexStr = "(^\\d{15}$)|(^\\d{18}$)|(^\\d{17}(\\d|X|x)$)";
if (!Regex.IsMatch(model.CardNo, CardNumRegexStr))
{
throw new ApiException("身份证格式不正确");
}
//查询老师身份证是否存在
TeacherModel teacherModel = SqlMapper.QueryForObject <TeacherModel>("GetTeacherByWhere", new { CardNo = model.CardNo, Phone = "", System_Station_ID = model.System_Station_ID, TeacherDetail_ID = 0 });
if (teacherModel != null)
{
throw new ApiException("身份证为" + model.CardNo + "的老师已存在，姓名为" + teacherModel.Name);
}
}
//查询老师手机号码是否存在
TeacherModel PhoneModel = SqlMapper.QueryForObject <TeacherModel>("GetTeacherByWhere", new { CardNo = "", Phone = model.Phone, System_Station_ID = model.System_Station_ID, TeacherDetail_ID = 0 });
if (PhoneModel != null)
{
throw new ApiException("电话号码为" + model.Phone + "的老师已存在，姓名为" + PhoneModel.Name);
}
try
{
SqlMapper.BeginTransaction();//开启事务
//根据身份证查询老师主表
T_TeacherInfo teacher = null;
if (!string.IsNullOrEmpty(model.CardNo))
{
teacher = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = model.CardNo, Phone = "" });
}
//根据手机号码查询老师主表
T_TeacherInfo teacher2 = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = "", Phone = model.Phone });
if (teacher == null && teacher2 == null) //主表没有，插入主表
{
T_TeacherInfo teacher3 = new T_TeacherInfo();
teacher3.Name = model.Name;
teacher3.Sex = model.Sex;
teacher3.CardNo = model.CardNo;
teacher3.JobTitle = model.JobTitle;
teacher3.HeadImage = model.HeadImage;
teacher3.Phone = model.Phone;
teacher3.Email = model.Email;
teacher3.Birthday = model.Birthday;
teacher3.Address = model.Address;
teacher3.Education_ID = model.Education_ID;
teacher3.GraduateSchool = model.GraduateSchool;
teacher3.Introduction = model.Introduction;
teacher3.AddPerson = model.AddPerson;
teacher3.AddTime = model.AddTime;
int id = (int)Orm.Insert(teacher3, true);
if (id > 0)
{
//添加主表成功，添加子表
model.ID = id;
add = InsertTeacherDetail(model);
}
else
{
throw new ApiException("添加老师主表失败！");
}
}
else //主表有，插入子表
{
if (teacher != null && teacher2 != null)
{
//如果是同一主表
if (teacher.ID == teacher2.ID)
{
model.ID = teacher.ID;
}
else
{
//不是同一主表，以身份证为主表
model.ID = teacher.ID;
model.CardNo = ""; //不更新身份证
model.Phone = ""; //不更新号码
}
}
else if (teacher != null && teacher2 == null)
{
model.ID = teacher.ID;
}
else if (teacher == null && teacher2 != null)
{
model.ID = teacher2.ID;
}
int updateTeacher = SqlMapper.Update("TeacherUpdate", new
{
Name = model.Name,
Sex = model.Sex,
CardNo = model.CardNo,
JobTitle = model.JobTitle,
HeadImage = model.HeadImage,
Phone = model.Phone,
Email = model.Email,
Birthday = model.Birthday,
Address = model.Address,
Education_ID = model.Education_ID,
GraduateSchool = model.GraduateSchool,
Introduction = model.Introduction,
ID = model.ID,
//System_Station_ID = model.System_Station_ID
});
if (updateTeacher > 0) //修改主表
{
//添加子表
add = InsertTeacherDetail(model);
}
}
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
return(add);
}
/// <summary>
/// 修改老师
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool TeacherUpdate(TeacherModel model)
{
if (model.System_Station_ID == 0)
{
throw new ApiException("机构ID不存在！");
}
if (model.TeacherDetail_ID == 0)
{
throw new ApiException("TeacherDetail_ID不能为空！");
}
if (string.IsNullOrEmpty(model.Name))
{
throw new ApiException("姓名不能为空！");
}
if (string.IsNullOrEmpty(model.CardNo))
{
//throw new ApiException("身份证不能为空！");
}
else
{
string CardNumRegexStr = "(^\\d{15}$)|(^\\d{18}$)|(^\\d{17}(\\d|X|x)$)";
if (!Regex.IsMatch(model.CardNo, CardNumRegexStr))
{
throw new ApiException("身份证格式不正确！");
}
}
//根据老师详细ID查询老师
TeacherModel teacherModel = SqlMapper.QueryForObject <TeacherModel>("GetTeacherByWhere", new { CardNo = "", Phone = "", System_Station_ID = model.System_Station_ID, TeacherDetail_ID = model.TeacherDetail_ID });
if (teacherModel == null)
{
throw new ApiException("老师不存在，TeacherDetail_ID是否正确！");
}
//如果身份证有变更
if ((!string.IsNullOrEmpty(model.CardNo)) && model.CardNo != teacherModel.CardNo)
{
//判断身份证是否已使用
T_TeacherInfo teacher = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = model.CardNo, Phone = "" });
if (teacher != null)
{
throw new ApiException("身份证已存在，姓名为：" + teacher.Name);
}
}
//如果手机号有变更
if (model.Phone != teacherModel.Phone)
{
//判断身份证是否已使用
T_TeacherInfo teacher = SqlMapper.QueryForObject <T_TeacherInfo>("GetTeacherByCardOrPhone", new { CardNo = "", Phone = model.Phone });
if (teacher != null)
{
throw new ApiException("手机号已存在，姓名为：" + teacher.Name);
}
}
bool update = false;
try
{
SqlMapper.BeginTransaction();//开启事务
int updateTeacher = SqlMapper.Update("TeacherUpdate", new
{
Name = model.Name,
Sex = model.Sex,
CardNo = model.CardNo,
JobTitle = model.JobTitle,
HeadImage = model.HeadImage,
Phone = model.Phone,
Email = model.Email,
Birthday = model.Birthday,
Address = model.Address,
Education_ID = model.Education_ID,
GraduateSchool = model.GraduateSchool,
Introduction = model.Introduction,
ID = model.ID,
System_Station_ID = model.System_Station_ID
});
if (updateTeacher > 0)
{
//删除老师学科关系
SqlMapper.Delete("DeleteTeacherDiscipline", new { TeacherDetail_ID = model.TeacherDetail_ID, System_Station_ID = model.System_Station_ID });
//添加老师学科关系
if (model.Teacher_DisciplineIds != null && model.Teacher_DisciplineIds.Length > 0)
{
string[] dics = model.Teacher_DisciplineIds.Split(',');
if (dics.Length > 0)
{
T_TeacherInfo_Detail_Discipline teacherD_Dis = null;
foreach (string disciplineId in dics)
{
if (string.IsNullOrEmpty(disciplineId))
{
continue;
}
teacherD_Dis = new T_TeacherInfo_Detail_Discipline();
teacherD_Dis.TeacherDetail_ID = model.TeacherDetail_ID;
teacherD_Dis.System_Station_ID = model.System_Station_ID;
teacherD_Dis.Discipline_ID = int.Parse(disciplineId);
teacherD_Dis.AddTime = model.AddTime;
teacherD_Dis.AddPerson = model.AddPerson;
//添加老师所教学科
Orm.Insert(teacherD_Dis);
}
}
}
update = true;
}
else
{
throw new ApiException("修改老师信息失败！");
}
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
return(update);
}
/// <summary>
/// 提交事务
/// </summary>
public virtual void CommitTransaction()
{
SqlMapper.CommitTransaction();
}
/// <summary>
/// 学生开课数据录入
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public bool AddOrderStudent(OrderModel model, int System_Station_ID, string StuId)
{
bool flag = false;
string stuID = string.IsNullOrEmpty(StuId) ? model.StuId : StuId;
Random ran = new Random();
model.AddTime = DateTime.Now;
model.PayTime = DateTime.Now;
model.OrderNo = DateTime.Now.ToString("yyyyMMddHHmmss") + ran.Next(1000, 9999).ToString();
model.StuId = stuID;
model.System_Station_ID = System_Station_ID;
model.OrderId = Guid.NewGuid().ToString("N").ToUpper();
if (!string.IsNullOrWhiteSpace(model.Course_IDs))
{
W_Order order = new W_Order();
order.StuId = model.StuId;
order.OrderNo = model.OrderNo;
order.System_Station_ID = model.System_Station_ID;
order.AddTime = model.AddTime;
order.Price = model.Price;
order.PayStatus = model.PayStatus;
order.PayType = model.PayType;
order.PayTime = model.PayTime;
order.PayPrice = model.PayPrice;
order.TicketNumber = model.TicketNumber;
order.OrderState = model.OrderState;
order.OrderId = model.OrderId;
try
{
SqlMapper.BeginTransaction();//开启事务
//添加订单
int id = (int)Orm.Insert(order, true);
if (id > 0)
{
string[] c_id = model.Course_IDs.Split(',');
string[] c_price = model.Prices.Split(',');
for (int i = 0; i < c_id.Length; i++)
{
W_Order_Detail orderdetail = new W_Order_Detail();
orderdetail.CID = id;
orderdetail.Course_ID = int.Parse(c_id[i]);
orderdetail.Price = decimal.Parse(c_price[i]);
//添加订单详细
Orm.Insert(orderdetail);
}
flag = true;
}
SqlMapper.CommitTransaction(); //提交
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction(); //回滚
throw new ApiException(ex.Message);
}
finally
{
//SqlMapper.CloseConnection();
}
}
return(flag);
}
/// <summary>
/// 添加录播的观看视频
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public dynamic SaveCourse_Look(W_Course_Look_DetailModel Model, string StuId, int System_Station_ID)
{
SqlMapper.BeginTransaction();
try
{
if (Model.CourseChapters_ID <= 0)
{
throw new ApiException("章节ID不能小于0");
}
if (Model.LookType < 0)
{
throw new ApiException("观看类型LookType不能为空！");
}
Model.EndTime = DateTime.Now;
Model.AddTime = DateTime.Now;
W_Course_Look_Detail OldModel = SqlMapper.QueryForObject <W_Course_Look_Detail>("GetCourse_Look_Detail", new { StuId = StuId, CourseChapters_ID = Model.CourseChapters_ID });
if (OldModel != null)
{
OldModel.EndTime = OldModel.EndTime.AddSeconds(double.Parse(Model.LookTime.ToString()));
if (SqlMapper.Update("UpdateCourse_Look_Detail", new { ID = OldModel.ID, EndTime = OldModel.EndTime }) <= 0)
{
throw new ApiException("更新最后播放时间出错");
}
CourseLookTimeModel LookTimeModel = SqlMapper.QueryForObject <CourseLookTimeModel>("GetCourseLookTimeModel", new { stuid = StuId, CourseChapters_ID = Model.CourseChapters_ID });
int TotalSeconds = 0; //已播放的秒数
int Duration = 0; //视频的时长
if (LookTimeModel != null)
{
TotalSeconds = LookTimeModel.TotalSeconds;
Duration = LookTimeModel.Duration;
}
else
{
throw new ApiException("未找到用户的播放记录");
}
//未获取到视频播放时长
if (Duration <= 0)
{
throw new ApiException("视频的时长为0");
}
//默认观看6分钟添加记录
int LookDuration = 6;
//查询配置信息 视频播放时长
W_Configuration ConfigurationModel = new HConfigurationMapper().GetWConfiguration(System_Station_ID);
if (ConfigurationModel != null)
{
LookDuration = ConfigurationModel.LookDuration;
}
//判断学生观看视频记录时间是否大于设置的时长 或者 视频时长小于设置的时长的时候判断观看时长和视频时长相差不到10秒的时候执行
if (TotalSeconds >= LookDuration * 60 || (Duration < LookDuration * 60 && (Duration - TotalSeconds) <= 10))
{
int counts = SqlMapper.QueryForObject <int>("GetW_Course_LookCounts", new { stuid = StuId, CourseChapters_ID = Model.CourseChapters_ID });
if (counts == 0)
{
W_Course_Look CourseLook = new W_Course_Look();
CourseLook.CourseChapters_ID = Model.CourseChapters_ID;
CourseLook.IP = Model.IP;
CourseLook.LookTime = DateTime.Now;
CourseLook.LookType = Model.LookType;
CourseLook.StuId = StuId;
Orm.Insert <W_Course_Look>(CourseLook);
}
}
}
else
{
W_Course_Look_Detail model = new W_Course_Look_Detail();
model.AddTime = Model.AddTime;
model.CourseChapters_ID = Model.CourseChapters_ID;
model.EndTime = Model.EndTime;
model.IP = Model.IP;
model.LookType = Model.LookType;
model.StuId = StuId;
Orm.Insert(model);
}
SqlMapper.CommitTransaction();
return(true);
}
catch (Exception ex)
{
SqlMapper.RollBackTransaction();
throw ex;
}
}
/// <summary>
/// 交卷
/// </summary>
/// <param name="model"></param>
/// <param name="stuId"></param>
/// <returns></returns>
public bool SubmitPractice(PracticeModel model, string stuId)
{
W_DoExamResult result = Orm.Select <W_DoExamResult>(x => x.ID == model.resultId).FirstOrDefault();
if (result == null)
{
throw new ApiException("对象错误");
}
else if (string.IsNullOrEmpty(stuId) || result.StuId != stuId)
{
throw new ApiException("登陆超时，请重新登陆");
}
result.EndTime = DateTime.Now;
result.Valid = 1;//交卷
SqlMapper.BeginTransaction();
try
{
//获取试卷题型对应的分数
List <W_ExamPaperDetail> detailList = null;
if (result.BusType == 0)//试卷模式
{
//更新试卷测试次数
W_ExamPaper paper = Orm.Single <W_ExamPaper>(x => x.ID == result.BusID);
paper.DoCount += 1;
Orm.Update <W_ExamPaper>(paper);
//试卷题型对应得分
detailList = Orm.Select <W_ExamPaperDetail>(x => x.ExamPaper_ID == result.BusID).ToList();
}
else//更改章节练习状态
{
W_ChapterPractice cp = Orm.Single <W_ChapterPractice>(x => x.ID == result.BusID);
cp.Status = 1;
Orm.Update <W_ChapterPractice>(cp);
}
//保存做题记录明细
foreach (Question question in model.list)
{
decimal sorce = 0;
if (result.BusType == 0)//试卷模式
{
W_ExamPaperDetail temp = detailList.FirstOrDefault(x => x.QuestionType_ID == question.QuestionType_ID);
sorce = temp != null ? temp.Sorce : 0;
}
Orm.Insert <W_DoExamResult_Detail>(new W_DoExamResult_Detail()
{
DoExamResult_ID = result.ID,
Question_ID = question.ID,
StuID = result.StuId,
MyAnswer = question.MyAnswer,
Judge = question.Judge,
Sorce = sorce,
UpdateTime = result.EndTime
});
//更新题目信息
SqlMapper.Update("UpdateQuestion", new { ID = question.ID, Judge = question.Judge, EasyWrongAnswer = question.MyAnswer });
}
//修改做题记录主表
Orm.Update <W_DoExamResult>(result);
SqlMapper.CommitTransaction();
}
catch
{
SqlMapper.RollBackTransaction();
return(false);
}
return(true);
}