a.id AS "id", a.certificate_code AS "certificateCode", a.certificate_type AS "certificateType", a.member_code AS "memberCode", a.cn_name AS "cnName", a.payment_state AS "paymentState", a.running_number AS "runningNumber", a.payment_time AS "paymentTime", a.review_state AS "reviewState", a.reviewer AS "reviewer", a.review_time AS "reviewTime", a.review_remarks AS "reviewRemarks", a.print_state AS "printState", a.issue_date AS "issueDate", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", a.remarks AS "remarks", a.del_flag AS "delFlag", a.payment_way as "paymentWay", a.total_price as "totalPrice", a.apply_time as "applyTime", m.id AS "member.id" LEFT JOIN sys_member m ON m.member_code=a.member_code SELECT FROM international_groomer_certificate a WHERE a.id = #{id} SELECT FROM international_groomer_certificate a WHERE a.member_code = #{memberCode} AND a.del_flag = '0' GROUP BY a.certificate_type DESC SELECT FROM international_groomer_certificate a a.del_flag = #{DEL_FLAG_NORMAL} AND (a.cn_name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode}) AND a.certificate_code = #{certificateCode} AND a.certificate_type = #{certificateType} AND a.payment_state = #{paymentState} AND a.review_state = #{reviewState} AND a.print_state = #{printState} AND date_format(a.apply_time, '%Y-%m-%d') = ]]> date_format(#{beginCreateDate}, '%Y-%m-%d') AND date_format(a.apply_time, '%Y-%m-%d') date_format(#{endCreateDate}, '%Y-%m-%d') AND a.issue_date = #{issueDate} ORDER BY ${page.orderBy} ORDER BY a.certificate_code DESC SELECT FROM international_groomer_certificate a a.del_flag = #{DEL_FLAG_NORMAL} ORDER BY ${page.orderBy} ORDER BY a.update_date DESC select ifnull(max(right(certificate_code,4)),0) as maxNum from international_groomer_certificate where certificate_code like concat(#{certificateNumPrefix},'%') select count(id) from international_groomer_certificate where member_code = #{memberCode} and certificate_type = #{certificateType} and del_flag = '0' INSERT INTO international_groomer_certificate( id, certificate_code, certificate_type, member_code, cn_name, payment_state, running_number, payment_time, review_state, reviewer, review_time, review_remarks, print_state, issue_date, create_by, create_date, update_by, update_date, remarks, del_flag, payment_way, total_price, apply_time ) VALUES ( #{id}, #{certificateCode}, #{certificateType}, #{memberCode}, #{cnName}, #{paymentState}, #{runningNumber}, #{paymentTime}, #{reviewState}, #{reviewer}, #{reviewTime}, #{reviewRemarks}, #{printState}, #{issueDate}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag}, #{paymentWay}, #{totalPrice}, #{applyTime} ) INSERT INTO international_groomer_certificate( id, certificate_code, certificate_type, member_code, cn_name, payment_state, running_number, payment_time, review_state, reviewer, review_time, review_remarks, print_state, issue_date, create_by, create_date, update_by, update_date, remarks, del_flag, payment_way, total_price, apply_time ) select #{id}, #{certificateCode}, ( select concat('CKU-',#{certificateType},'-',year(now()),LPAD(ifnull(max(right(certificate_code,4)),0)+1, 4, 0)) from international_groomer_certificate where certificate_code like concat('CKU-',#{certificateType},'-',year(now()),'%')) as maxNum, #{certificateType}, #{memberCode}, #{cnName}, #{paymentState}, #{runningNumber}, #{paymentTime}, #{reviewState}, #{reviewer}, #{reviewTime}, #{reviewRemarks}, #{printState}, #{issueDate}, #{createBy.id}, #{createDate}, #{updateBy.id}, #{updateDate}, #{remarks}, #{delFlag}, #{paymentWay}, #{totalPrice}, #{applyTime} from dual UPDATE international_groomer_certificate SET certificate_code = #{certificateCode}, certificate_type = #{certificateType}, member_code = #{memberCode}, -- en_name = #{enName}, cn_name = #{cnName}, payment_state = #{paymentState}, running_number = #{runningNumber}, payment_time = #{paymentTime}, review_state = #{reviewState}, reviewer = #{reviewer}, review_time = #{reviewTime}, review_remarks = #{reviewRemarks}, print_state = #{printState}, issue_date = #{issueDate}, update_by = #{updateBy.id}, update_date = #{updateDate}, remarks = #{remarks} WHERE id = #{id} UPDATE international_groomer_certificate SET print_state = #{printState} WHERE id = #{id} UPDATE international_groomer_certificate SET del_flag = #{DEL_FLAG_DELETE} WHERE id = #{id} DELETE FROM international_groomer_certificate WHERE member_code = #{memberCode} ${sql} SELECT FROM international_groomer_certificate a WHERE a.member_code = #{memberCode} and certificate_type = #{type}