Supply_chain_finance/web/target/classes/sql/jlw/examinations.md

searchExamination

• 搜索学生时查找的Examinations SELECT * FROM examinations es WHERE 1=1 @if(!isEmpty(examinationId)){ AND es.id=#examinationId# @} @if(!isEmpty(flowId)){ AND es.flow_id=#flowId# AND es.type=0 AND es.mode=1 @} LIMIT 0,1;

getEditTeamExamination

• 获取团队模式下的实训 SELECT * FROM examinations WHERE 1=1 @if(!isEmpty(id)){ AND id = #id# @} @if(!isEmpty(flowId)){ AND flow_id = #flowId# @} @if(!isEmpty(mode)){ AND mode = #mode# @} @if(!isEmpty(type)){ AND type = #type# @} LIMIT 0,1;

getPracticeModeValue

• 获取练习模式下的数据 SELECT * FROM examinations WHERE flow_id = #flowId# AND mode = #isTeam# AND type =0

queryByExaminations

• 查询实训考核数据集合 SELECT @pageTag(){ * @} FROM examinations WHERE teacher_id = #teacher.id# AND EXISTS ( SELECT * FROM classes INNER JOIN examination_classes ON classes.id = examination_classes.class_id WHERE examinations.id = examination_classes.examination_id AND id = #users.classId# ) ORDER BY id DESC

queryStudentExaminations

• 查询学生实训考核数据集合 SELECT @pageTag(){ t.*, t.id examination_id @} FROM examinations t JOIN examination_classes t2 on t.id = t2.examination_id WHERE t.is_delete = 0 AND t2.class_id = #users.classId# ORDER BY t.id DESC

queryByCondition

• 根据不为空的参数进行分页查询

  select @pageTag(){ t.*, t2.name as flow_name @} from examinations t join flows t2 on t.flow_id = t2.id where 1=1 @if(!isEmpty(id)){ and t.id =#id# @} @if(!isEmpty(createdAt)){ and t.created_at =#createdAt# @} @if(!isEmpty(updatedAt)){ and t.updated_at =#updatedAt# @} @if(!isEmpty(deletedAt)){ and t.deleted_at =#deletedAt# @} @if(!isEmpty(data)){ and t.data =#data# @} @if(!isEmpty(name)){ and t.name =#name# @} @if(!isEmpty(examName)){ and t.name like #examName# @} @if(!isEmpty(flowId)){ and t.flow_id =#flowId# @} @if(!isEmpty(mode)){ and t.mode =#mode# @} @if(!isEmpty(startTime)){ and t.start_time =#startTime# @} @if(!isEmpty(endTime)){ and t.end_time =#endTime# @} @if(!isEmpty(teacherId)){ and t.teacher_id =#teacherId# @} @if(!isEmpty(status)){ and t.status =#status# @} @if(!isEmpty(userNum)){ and t.user_num =#userNum# @} @if(!isEmpty(type)){ and t.type =#type# @} @if(!isEmpty(scoresStatus)){ and t.scores_status =#scoresStatus# @} order by t.id desc

queryGradeExaminationList

• 根据不为空的参数进行分页查询 成绩管理 待评分/已评分

  select @pageTag(){ t., t2.name as flow_name, ( select count() from users t3 where t3.class_id in ( select class_id from examination_classes t4 where t4.examination_id = t.id ) ) examination_mans @} from examinations t join flows t2 on t.flow_id = t2.id where t.type = 1 and t.end_time < now() @if(!isEmpty(name)){ and t.name like #'%'+name+'%'# @} @if(!isEmpty(flowId)){ and t.flow_id =#flowId# @} @if(!isEmpty(mode)){ and t.mode =#mode# @} @if(!isEmpty(teacherId)){ and t.teacher_id =#teacherId# @} @if(!isEmpty(scoresStatus)){ and t.scores_status =#scoresStatus# @} order by t.id desc

deleteExaminationsByIds

• 批量删除

  delete from examinations where find_in_set(id,#ids#)

getExaminationsValues

• 根据不为空的参数进行查询

  select t.* from examinations t where 1=1
  @if(!isEmpty(id)){ and t.id =#id# @} @if(!isEmpty(createdAt)){ and t.created_at =#createdAt# @} @if(!isEmpty(updatedAt)){ and t.updated_at =#updatedAt# @} @if(!isEmpty(deletedAt)){ and t.deleted_at =#deletedAt# @} @if(!isEmpty(data)){ and t.data =#data# @} @if(!isEmpty(name)){ and t.name =#name# @} @if(!isEmpty(flowId)){ and t.flow_id =#flowId# @} @if(!isEmpty(mode)){ and t.mode =#mode# @} @if(!isEmpty(startTime)){ and t.start_time =#startTime# @} @if(!isEmpty(endTime)){ and t.end_time =#endTime# @} @if(!isEmpty(teacherId)){ and t.teacher_id =#teacherId# @} @if(!isEmpty(status)){ and t.status =#status# @} @if(!isEmpty(userNum)){ and t.user_num =#userNum# @} @if(!isEmpty(type)){ and t.type =#type# @} @if(!isEmpty(scoresStatus)){ and t.scores_status =#scoresStatus# @}

getValuesByQuery

• 根据不为空的参数进行查询

  select t.* from examinations t where 1=1
  @if(!isEmpty(id)){ and t.id =#id# @} @if(!isEmpty(createdAt)){ and t.created_at =#createdAt# @} @if(!isEmpty(updatedAt)){ and t.updated_at =#updatedAt# @} @if(!isEmpty(deletedAt)){ and t.deleted_at =#deletedAt# @} @if(!isEmpty(data)){ and t.data =#data# @} @if(!isEmpty(name)){ and t.name =#name# @} @if(!isEmpty(flowId)){ and t.flow_id =#flowId# @} @if(!isEmpty(mode)){ and t.mode =#mode# @} @if(!isEmpty(startTime)){ and t.start_time =#startTime# @} @if(!isEmpty(endTime)){ and t.end_time =#endTime# @} @if(!isEmpty(teacherId)){ and t.teacher_id =#teacherId# @} @if(!isEmpty(status)){ and t.status =#status# @} @if(!isEmpty(userNum)){ and t.user_num =#userNum# @} @if(!isEmpty(type)){ and t.type =#type# @} @if(!isEmpty(scoresStatus)){ and t.scores_status =#scoresStatus# @}

updateExaminationById

• 编辑更新修改实训详情

  update examinations set name = #name#, mode = #mode#, start_time = #startTime#, end_time = #endTime# where id = #id#

getExaminationScoreListCnt

• 实训 评分列表/未评分 条数

select count(1) as tatol
from `examinations` t1 
where t1.`teacher_id` = #teacherId# and t1.type = 1
    @if(!isEmpty(flowId)){
        and t1.flow_id = #flowId#
    @}
    @if(!isEmpty(examName)){
        and t1.name like #examName#
    @}
    @if(scoresStatus == 0){
        and t1.`scores_status` = 0
    @}
    @if(scoresStatus > 0){
        and t1.`scores_status` > 0
    @}    
    and ( t1.`status` = 2
           or exists (select `id` from `user_examinations` t2 where t1.`id` = t2.`examination_id` and t2.`status` >= 1 limit 1 )
      )

getExaminationScoreList

• 实训 评分列表/未评分列表

select t1.id examination_id,
       t1.name as examination_name,
       f.id as flow_id,
       f.name as flow_name,
       t1.mode,
       t1.start_time as start,
       t1.end_time as end,
       t1.scores_status as scores_status,
       (select count(*) from  `user_examinations` t2 where t1.`id` = t2.`examination_id` and t2.`status` >= 1) as user_examinations_count
from `examinations` t1 join flows f on t1.flow_id = f.id
where t1.`teacher_id` = #teacherId#
    @if(!isEmpty(flowId)){
        and t1.flow_id = #flowId#
    @}
    @if(!isEmpty(examName)){
        and t1.name like #examName#
    @}
    @if(scoresStatus == 0){
        and t1.`scores_status` = 0
    @}
    @if(scoresStatus > 0){
        and t1.`scores_status` > 0
    @}    
    and ( t1.`status` = 2
           or exists (select `id` from `user_examinations` t2 where t1.`id` = t2.`examination_id` and t2.`status` >= 1 limit 1 )
      )
order by t1.scores_status ASC, t1.id DESC
limit #offset#, #limit# 

getTodoCount

*待评分实训的数量

    select count(1) as total
    from `examinations` t
    where 1=1
      and `teacher_id` = #teacherId#
      and `type` = 1
      and `scores_status` = 0
      and (`status` = 2 or exists (select `id` from `user_examinations` where t.`id` = `user_examinations`.`examination_id` and `status` >= 1 limit 1));

getOneFinishingExamination

• 获取最新一条到结束时间的实训

    select t.id, 
           t.start_time, 
           t.end_time, 
           t.status
    from examinations t
    where t.status < 2
    order by t.end_time;

getExaminationList

• 获取实训列表

  SELECT @pageTag(){ e.id , e.name , e.flow_id , e.mode , e.start_time , e.end_time , e.status , e.user_num , e.type @} FROM examinations e WHERE 1=1 @if(!isEmpty(name)){ AND e.name LIKE #'%'+ name +'%'# @} @if(!isEmpty(flowId)){ AND e.flow_id = #flowId# @} AND e.type = 1 AND e.is_delete = 0 ORDER BY e.created_at DESC