a.id AS "id",
a.course_code AS "courseCode",
a.course_name AS "courseName",
a.start_date AS "startDate",
a.end_date AS "endDate",
a.teacher AS "teacher",
a.max_num AS "maxNum",
a.course_location AS "courseLocation",
a.course_partner AS "coursePartner",
IF(a.max_num = IFNULL(aa.c,0) && a.states = '0','1',a.states) AS "states",
a.remarks AS "remarks",
a.create_by AS "createBy.id",
a.update_by AS "updateBy.id",
a.create_date AS "createDate",
a.update_date AS "updateDate",
a.del_flag AS "delFlag"
IFNULL(aa.c,0) AS "courseApplyCount"
ac.courseware_name AS "courseCourseware"
LEFT JOIN (
SELECT r.course_id,GROUP_CONCAT(t.courseware_name) AS 'courseware_name'
FROM appreciation_courses_courseware_ref r
LEFT JOIN appreciation_courseware t ON t.id = r.courseware_id
WHERE r.del_flag = '0'
GROUP BY r.course_id
) ac ON ac.course_id = a.id
LEFT JOIN (
SELECT t.course_id,COUNT(t.id) AS 'c'
FROM appreciation_apply t
WHERE t.del_flag = '0'
GROUP BY t.course_id
) aa ON aa.course_id = a.id
INSERT INTO appreciation_courses(
id,
course_code,
course_name,
start_date,
end_date,
teacher,
max_num,
course_location,
course_partner,
states,
remarks,
create_by,
update_by,
create_date,
update_date,
del_flag
) VALUES (
#{id},
#{courseCode},
#{courseName},
#{startDate},
#{endDate},
#{teacher},
#{maxNum},
#{courseLocation},
#{coursePartner},
#{states},
#{remarks},
#{createBy.id},
#{updateBy.id},
#{createDate},
#{updateDate},
#{delFlag}
)
UPDATE appreciation_courses SET
course_code = #{courseCode},
course_name = #{courseName},
start_date = #{startDate},
end_date = #{endDate},
teacher = #{teacher},
max_num = #{maxNum},
course_location = #{courseLocation},
course_partner = #{coursePartner},
remarks = #{remarks},
update_by = #{updateBy.id},
update_date = #{updateDate}
WHERE id = #{id}
UPDATE appreciation_courses SET
del_flag = #{DEL_FLAG_DELETE}
WHERE id = #{id}
UPDATE appreciation_courses SET
states = '2'
WHERE id = #{id}