a.id AS "id",
a.member_code AS "memberCode",
a.register_id AS "registerId",
a.take_certificate_date AS "takeCertificateDate",
a.level_cn AS "levelCn",
a.level_en AS "levelEn",
a.certificate_code AS "certificateCode",
a.certificate_state AS "certificateState",
a.issue_date AS "issueDate",
a.reviewer AS "reviewer",
a.corsage AS "corsage",
a.corsage_time AS "corsageTime",
a.print_state AS "printState",
a.review_state AS "reviewState",
a.review_time AS "reviewTime",
a.review_remarks AS "reviewRemarks",
a.running_number AS "runningNumber",
a.payment_state AS "paymentState",
a.payment_time AS "paymentTime",
a.proccess_time AS "proccessTime",
a.proccess_state AS "proccessState",
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.isFree AS "isFree",
a.certificateType AS "certificateType",
m.id AS "member.id",
m.gender AS "member.gender",
m.mobile AS "member.mobile",
m.name AS "member.name",
m.name_en AS "member.nameEn",
m.groomer_type AS "member.groomerType",
b.show_level AS "groomerRegister.showLevel",
b.register_code AS "groomerRegister.registerCode",
b.show_code AS "groomerRegister.showCode",
b.show_dog_breed AS "groomerRegister.showDogBreed",
b.grade_time AS "groomerRegister.gradeTime",
b.grade AS "groomerRegister.grade",
b.grade_write AS "groomerRegister.gradeWrite",
b.school_name_cn AS "groomerRegister.schoolNameCn",
b.create_by AS "groomerRegister.createBy",
c.show_name AS "groomerShow.showName",
c.end_time AS "groomerShow.endTime",
c.show_category AS "groomerShow.showCategory",
c.test_level AS "groomerShow.testLevel",
ti.school_type AS "schoolType",
a.paper AS "paper",
a.certificate_source AS "certificateSource"
LEFT JOIN groomer_register b ON a.register_id=b.id
LEFT JOIN groomer_show c ON b.show_code=c.show_code
LEFT JOIN sys_member m ON m.member_code=a.member_code
LEFT JOIN sys_member m2 ON b.create_by = m2.user_id and m2.del_flag = '0'
LEFT JOIN training_institution ti ON ti.school_member_code = m2.member_code and ti.del_flag = '0'
SELECT
a.del_flag = '0'
AND a.id = #{id}
SELECT
a.del_flag = '0'
AND (m.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND b.register_code LIKE CONCAT('%',#{groomerRegister.registerCode},'%')
AND b.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND c.show_category = #{groomerShow.showCategory}
AND b.grade = #{groomerRegister.grade}
AND a.level_en = #{levelEn}
AND a.certificateType = #{certificateType}
AND a.certificate_code = #{certificateCode}
AND a.payment_state = #{paymentState}
AND a.proccess_state = #{proccessState}
AND a.print_state = #{printState}
AND a.certificate_state=#{certificateState}
AND c.show_category = #{showCategory}
AND b.create_by = #{registerCreateBy.id}
and a.level_cn IN
#{levelCn}
ORDER BY ${page.orderBy}
ORDER BY a.create_date DESC
SELECT
a.del_flag = #{DEL_FLAG_NORMAL}
AND a.payment_state = #{paymentState}
ORDER BY ${page.orderBy}
ORDER BY a.update_date DESC
INSERT INTO groomer_qualification_certificate(
id,
member_code,
register_id,
level_cn,
level_en,
corsage,
print_state,
review_state,
running_number,
payment_state,
payment_time,
proccess_state,
proccess_time,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
take_certificate_date,
reviewer,
certificateType,
isFree,
paper,
certificate_code,
certificate_source,
issue_date
) VALUES (
#{id},
#{memberCode},
#{registerId},
#{levelCn},
#{levelEn},
#{corsage},
#{printState},
#{reviewState},
#{runningNumber},
#{paymentState},
#{paymentTime},
#{proccessState},
#{proccessTime},
#{createBy.name},
#{createDate},
#{updateBy.name},
#{updateDate},
#{remarks},
#{delFlag},
#{takeCertificateDate},
#{reviewer},
#{certificateType},
#{isFree},
#{paper},
#{certificateCode},
#{certificateSource},
#{issueDate}
)
UPDATE groomer_qualification_certificate SET
member_code = #{memberCode},
register_id=#{registerId},
take_certificate_date = #{takeCertificateDate},
level_cn = #{levelCn},
level_en = #{levelEn},
certificate_code = #{certificateCode},
issue_date = #{issueDate},
reviewer = #{reviewer},
corsage = #{corsage},
corsage_time = #{corsageTime},
print_state = #{printState},
review_state = #{reviewState},
review_time = #{reviewTime},
review_remarks = #{reviewRemarks},
running_number = #{runningNumber},
payment_state = #{paymentState},
payment_time = #{paymentTime},
proccess_time = #{proccessTime},
proccess_state = #{proccessState},
update_by = #{updateBy.id},
update_date = #{updateDate},
remarks = #{remarks},
paper = #{paper}
WHERE id = #{id}
UPDATE groomer_qualification_certificate SET
del_flag = #{DEL_FLAG_DELETE}
WHERE id = #{id}
SELECT
a.id,
a.level_en,
a.level_cn,
a.issue_date,
a.certificate_code,
a.corsage,
a.corsage_time
FROM
groomer_qualification_certificate a
WHERE
a.certificate_code IS NOT NULL
AND a.certificateType='1'
AND a.certificate_code ]]> '' AND a.del_flag='0' AND
a.member_code = #{memberCode} ORDER BY a.level_en DESC
SELECT
a.take_certificate_date AS "takeCertificateDate",
a.level_en AS "levelEn",
a.certificate_code AS "certificateCode",
b.grade AS "groomerRegister.grade",
c.show_name AS "groomerShow.showName",
b.referee AS "groomerRegister.referee"
FROM
groomer_qualification_certificate a
LEFT JOIN groomer_register b ON a.register_id = b.id
LEFT JOIN groomer_show c ON b.show_code=c.show_code
where a.member_code=#{memberCode} ORDER BY a.create_date DESC
update groomer_qualification_certificate
SET
certificate_code = #{certificateCode},
issue_date = #{issueDate},
proccess_state = #{proccessState},
proccess_time = #{proccessTime}
WHERE id = #{id}
SELECT
a.id,
a.member_code,
a.take_certificate_date,
b.gender,
a.level_cn,
a.level_en,
a.certificate_code,
b.name,
a.certificateType
FROM
groomer_qualification_certificate a
LEFT JOIN groomer_register b ON a.register_id = b.id
LEFT JOIN groomer_show c ON b.show_code=c.show_code
a.del_flag = '0'
AND certificate_code IS NOT NULL
AND certificate_code ]]> ''
AND (b.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND b.register_code LIKE CONCAT('%',#{registerCode},'%')
AND b.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND b.grade = #{groomerRegister.grade}
AND a.level_en = #{levelEn}
AND c.show_category = #{groomerShow.showCategory}
AND a.certificateType = #{certificateType}
AND (b.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND DATE_ADD((DATE_FORMAT( c.end_time, '%Y-%m-%d' ) ) , INTERVAL 61 DAY) > CURRENT_DATE ()
AND a.certificate_state = '0'
AND b.school_name_cn LIKE CONCAT('%',#{schoolNameCn},'%')
ORDER BY a.level_en ASC ,b.name DESC
SELECT
c.name AS "member.name",c.gender AS "member.gender",c.name_en AS "member.nameEn",c.avatar AS "member.avatar",c.member_code AS "member.memberCode",
a.reviewer AS "reviewer",a.level_cn AS "levelCn",a.level_en AS "levelEn",
a.certificate_code AS "certificateCode",a.issue_date AS "issueDate",a.certificateType AS "certificateType",
a.take_certificate_date AS "takeCertificateDate",b.referee AS "groomerRegister.referee",s.start_time AS "createDate"
FROM groomer_qualification_certificate a
LEFT JOIN groomer_register b ON a.register_id = b.id
LEFT JOIN sys_member c ON a.member_code=c.member_code
LEFT JOIN groomer_show s ON b.show_code=s.show_code
WHERE a.id=#{id}
update groomer_qualification_certificate
SET
print_state = #{printState}
where id=#{id}
select count(1) from groomer_qualification_certificate where payment_state='2' and proccess_state='0' and certificateType = '1' and del_flag ='0'
UPDATE groomer_qualification_certificate
SET corsage=#{corsage},
corsage_time=#{corsageTime}
WHERE id=#{id}
SELECT
a.member_code AS "memberCode",
a.issue_date AS "issueDate",
b.name AS "name",
a.level_cn AS "levelCn",
a.level_en AS "levelEn",
a.certificateType AS "certificateType",
a.certificate_code AS "certificateCode",
a.take_certificate_date AS "takeCertificateDate"
FROM
groomer_qualification_certificate a
LEFT JOIN
sys_member b
ON
a.member_code = b.member_code
WHERE
(
a.member_code = #{key}
OR a.certificate_code = #{key}
)
AND a.certificateType != '4'
AND a.del_flag = '0'
AND a.payment_state = '2'
AND a.proccess_state = '1'
ORDER BY
FIELD(a.level_en,'教师级','Master','MASTER','A','B','C') ASC
SELECT
a.member_code AS "memberCode",
a.issue_date AS "issueDate",
b.name AS "name",
a.level_cn AS "levelCn",
a.level_en AS "levelEn",
a.certificateType AS "certificateType",
a.certificate_code AS "certificateCode",
a.take_certificate_date AS "takeCertificateDate"
FROM
groomer_qualification_certificate a
LEFT JOIN
sys_member b
ON
a.member_code = b.member_code
WHERE
(
a.member_code = #{key}
OR a.certificate_code = #{key}
)
AND a.certificateType = '4'
AND a.del_flag = '0'
AND a.payment_state = '2'
AND a.proccess_state = '1'
ORDER BY
FIELD(a.level_en,'MATCH','SENIOR','PRIMARY') ASC
SELECT
s.member_code AS "member.memberCode",
s.name AS "member.name",
s.mobile AS "member.mobile",
s.province AS "member.province",
s.city AS "member.city",
s.area AS "member.area",
s.avatar AS "member.avatar",
GROUP_CONCAT(DISTINCT b.level_en SEPARATOR ',') AS "levelEn",
s.registrant AS "schoolNameCn"
FROM
groomer_qualification_certificate b
LEFT JOIN sys_member s ON s.member_code=b.member_code
LEFT JOIN groomer_register r ON b.register_id = r.id
b.del_flag = '0'
and s.member_code is not null
and s.member_code !=''
and s.city = #{member.city}
and b.member_code = #{member.memberCode}
and b.member_code in (select member_code from groomer_qualification_certificate where level_en = 'MASTER')
GROUP BY s.member_code
ORDER BY b.issue_date desc
select
id,
member_code,
register_id,
level_cn,
level_en,
corsage,
print_state,
review_state,
payment_state,
proccess_state,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
take_certificate_date,
isFree
FROM groomer_qualification_certificate
WHERE register_id=#{registerId} AND del_flag='0' order by create_date DESC limit 1
select id from groomer_qualification_certificate where member_code=#{memberCode} and payment_state ='1' and level_en=#{levelEn} and del_flag='0'
select id from groomer_qualification_certificate where member_code=#{memberCode} and payment_state ='2' and level_en=#{levelEn} and del_flag='0'
SELECT
*
FROM
groomer_qualification_certificate
WHERE
member_code=#{memberCode}
AND del_flag = '0'
ORDER BY
CASE
WHEN level_en = 'MASTER' THEN
1
WHEN level_en = 'A' THEN
2
WHEN level_en = 'B' THEN
3
WHEN level_en = 'C' THEN
4
END;
SELECT sys.name
FROM sys_member sys
INNER JOIN
(SELECT member_code,
min(corsage_time) new_corsage_time
FROM groomer_qualification_certificate
WHERE corsage='1'
AND del_flag='0'
GROUP BY member_code)gqc ON sys.member_code=gqc.member_code
ORDER BY gqc.new_corsage_time ASC
UPDATE groomer_qualification_certificate
SET del_flag='1',
remarks=#{remarks}
WHERE id=#{id}
SELECT
count(*)
FROM
groomer_qualification_certificate
WHERE
member_code=#{memberCode}
AND certificate_code IS NOT NULL
AND certificate_code !=''
AND del_flag = '0'
UPDATE groomer_qualification_certificate
SET payment_state='2',
payment_time=NOW()
WHERE id=#{id}
select
ifnull(max(right(certificate_code,4)),0) as maxNum
from
groomer_qualification_certificate where certificate_code like concat(#{certificateNumPrefix},'%')
select
ifnull(max(right(certificate_code,3)),0) as maxNum
from
groomer_qualification_certificate where certificate_code like concat(#{certificateNumPrefix},'%')
select
id,
member_code,
register_id,
level_cn,
level_en,
corsage,
print_state,
review_state,
payment_state,
proccess_state,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
take_certificate_date,
certificateType,
isFree
FROM groomer_qualification_certificate
WHERE id=#{id} AND del_flag='0' order by create_date DESC limit 1
update groomer_qualification_certificate
SET
certificate_state = '1'
WHERE id = #{id}
SELECT
a.id AS "id",
a.certificate_code AS "certificateCode",
a.certificateType AS "certificateType",
a.member_code AS "memberCode",
m.`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",
m.id AS "member.id"
FROM groomer_qualification_certificate a
LEFT JOIN sys_member m ON m.member_code = a.member_code
WHERE a.member_code = #{memberCode}
AND a.del_flag = '0'
ORDER BY a.certificateType DESC
SELECT
a.del_flag = '0'
AND a.certificate_code is not null and a.certificate_code!=''
AND (m.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND b.register_code LIKE CONCAT('%',#{groomerRegister.registerCode},'%')
AND b.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND c.show_category = #{groomerShow.showCategory}
AND b.grade = #{groomerRegister.grade}
AND a.level_en = #{levelEn}
AND a.certificateType = #{certificateType}
AND a.certificate_code = #{certificateCode}
AND a.payment_state = #{paymentState}
AND a.proccess_state = #{proccessState}
AND a.print_state = #{printState}
AND a.certificate_state=#{certificateState}
AND c.show_category = #{showCategory}
ORDER BY a.create_date DESC
SELECT
a.id AS "id",
a.certificate_code AS "certificateCode",
a.certificateType AS "certificateType",
a.member_code AS "memberCode",
m.`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",
m.id AS "member.id"
FROM groomer_qualification_certificate a
LEFT JOIN sys_member m ON m.member_code = a.member_code
WHERE a.id = #{id}
AND a.del_flag = '0'
ORDER BY a.certificateType DESC
SELECT
a.level_en AS "levelEn",
a.certificate_code AS "certificateCode",
a.issue_date AS "issueDate",
a.certificateType AS "certificateType"
FROM
groomer_qualification_certificate a
WHERE
a.del_flag = '0'
AND a.member_code = #{memberCode}
AND a.certificate_code IS NOT NULL
AND a.certificate_code != ''
GROUP BY
a.level_en,a.certificateType
ORDER BY
a.issue_date DESC,
FIELD(
a.level_en,
'国际高级宠物美容师',
'国际宠物美容师',
'金牌宠物造型师',
'宠物造型师',
'教师级',
'MASTER',
'M',
'A',
'B',
'C'
) ASC
select * from groomer_qualification_certificate
member_code in
#{item.memberCode}
select
id,
member_code,
register_id,
level_cn,
level_en,
corsage,
print_state,
review_state,
payment_state,
proccess_state,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
take_certificate_date,
certificateType,
isFree
FROM groomer_qualification_certificate
WHERE register_id=#{registerId} AND del_flag='0' and isFree ='1'
order by create_date DESC
limit 1
SELECT
gr.*,
CASE
WHEN gqc.level_en='PRIMARY'
THEN '1'
WHEN gqc.level_en='SENIOR'
THEN '2'
WHEN gqc.level_en='MATCH'
THEN '3'
END AS show_level_order
FROM
groomer_qualification_certificate gqc
JOIN
groomer_register gr
ON
gqc.register_id = gr.id
WHERE
gqc.certificateType = '4'
AND gqc.member_code = #{memberCode}
AND gqc.certificate_code is not null
AND gqc.certificate_code != ''
AND gqc.del_flag = '0'
ORDER BY
show_level_order
SELECT
a.del_flag = '0'
AND a.certificate_code = #{certificateCode}
AND a.certificateType = #{certificateType}
limit 1;
SELECT
b.name,
b.name_en as "namePinyin",
b.member_code,
b.gender,
b.avatar as "member.avatar",
gr.referee,
a.reviewer,
a.certificate_code ,
a.issue_date,
a.take_certificate_date ,
a.level_cn,
a.level_en,
a.certificateType as "certificateType"
FROM
groomer_qualification_certificate a
LEFT JOIN
groomer_register gr
ON
gr.id = a.register_id
LEFT JOIN
sys_member b
ON
a.member_code = b.member_code
WHERE
a.del_flag='0'
and a.certificate_code = #{certificateCode}
limit 1
select
id,
member_code,
register_id,
level_cn,
level_en,
corsage,
print_state,
review_state,
payment_state,
proccess_state,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
take_certificate_date,
certificateType,
isFree
FROM groomer_qualification_certificate
WHERE register_id=#{id} and level_en =#{levelEn} AND member_code = #{memberCode} AND del_flag='0' order by create_date DESC limit 0,1
SELECT S.* FROM (select
a.id AS "id",
a.show_code AS "showCode",
a.register_code AS "registerCode",
a.member_code AS "memberCode",
a.team_member_code AS "teamMemberCode",
a.name AS "name",
a.mobile AS "mobile",
a.gender AS "gender",
a.avatar AS "avatar",
a.show_level AS "showLevel",
a.school_name_cn AS "schoolNameCn",
a.fee AS "fee",
a.leave_cancel AS "leaveCancel",
a.show_leave AS "showLeave",
a.show_dog_breed AS "showDogBreed",
a.show_dog_breed_extra AS "showDogBreedExtra",
a.order_price AS "orderPrice",
a.payment_member_code AS "paymentMemberCode",
a.operator AS "operator",
a.grade AS "grade",
a.grade_write AS "gradeWrite",
a.payment_charging_item_id AS "paymentChargingItemId",
a.proccess_state AS "proccessState",
a.payment_state AS "paymentState",
a.review_state AS "reviewState",
a.review_time AS "reviewTime",
a.review_remarks AS "reviewRemarks",
a.running_number AS "runningNumber",
a.payment_time AS "paymentTime",
a.proccess_time AS "proccessTime",
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.test_no AS "testNo",
a.referee AS "referee",
a.grade_time AS "gradeTime",
a.place AS "place",
a.take_certificate_date AS "takeCertificateDate",
b.show_code AS "groomerShow.showCode",
b.fee AS "groomerShow.fee",
b.register_end_time AS "groomerShow.registerEndTime",
b.show_name AS "groomerShow.showName",
b.show_type AS "groomerShow.showType",
b.show_category AS "groomerShow.showCategory",
b.show_address AS "groomerShow.showAddress",
b.start_time AS "groomerShow.startTime",
b.end_time AS "groomerShow.endTime",
b.id AS "groomerShow.id",
b.test_level AS "groomerShow.testLevel",
m.groomer_type AS "member.groomerType",
m.mobile AS "member.mobile",
m.gender AS "member.gender",
m.account_balance AS "member.accountBalance",
m.member_end_date AS "member.memberEndDate",
m.groomer_end_date AS "member.groomerEndDate",
m.id AS "member.id",
m.avatar AS "member.avatar",
g.id AS "groomerQualificationCertificate.id",
g.member_code AS "groomerQualificationCertificate.memberCode",
g.register_id AS "groomerQualificationCertificate.registerId",
g.take_certificate_date AS "groomerQualificationCertificate.takeCertificateDate",
g.level_cn AS "groomerQualificationCertificate.levelCn",
g.level_en AS "groomerQualificationCertificate.levelEn",
g.certificate_code AS "groomerQualificationCertificate.certificateCode",
g.certificate_state AS "groomerQualificationCertificate.certificateState",
g.issue_date AS "groomerQualificationCertificate.issueDate",
g.reviewer AS "groomerQualificationCertificate.reviewer",
g.corsage AS "groomerQualificationCertificate.corsage",
g.corsage_time AS "groomerQualificationCertificate.corsageTime",
g.print_state AS "groomerQualificationCertificate.printState",
g.review_state AS "groomerQualificationCertificate.reviewState",
g.review_time AS "groomerQualificationCertificate.reviewTime",
g.review_remarks AS "groomerQualificationCertificate.reviewRemarks",
g.running_number AS "groomerQualificationCertificate.runningNumber",
g.payment_state AS "groomerQualificationCertificate.paymentState",
g.payment_time AS "groomerQualificationCertificate.paymentTime",
g.proccess_time AS "groomerQualificationCertificate.proccessTime",
g.proccess_state AS "groomerQualificationCertificate.proccessState",
g.create_by AS "groomerQualificationCertificate.createBy.id",
g.create_date AS "groomerQualificationCertificate.createDate",
g.update_by AS "groomerQualificationCertificate.updateBy.id",
g.update_date AS "groomerQualificationCertificate.updateDate",
g.remarks AS "groomerQualificationCertificate.remarks",
g.del_flag AS "groomerQualificationCertificate.delFlag",
g.isFree AS "groomerQualificationCertificate.isFree",
g.certificateType AS "groomerQualificationCertificate.certificateType",
g.paper AS "groomerQualificationCertificate.paper",
1 AS "groomerQualificationCertificate.qualificationCertificateType"
from groomer_qualification_certificate g
LEFT JOIN groomer_register a ON g.register_id=a.id
LEFT JOIN groomer_show b ON a.show_code=b.show_code
LEFT JOIN sys_member m ON m.member_code=a.member_code
a.grade is not null and a.grade not in ('0', '1') and a.grade_write = '1'
and g.del_flag = '0' and a.del_flag = '0' and b.del_flag = '0' and a.member_code = #{memberCode}
and g.register_id !='' and g.register_id is not null and a.show_code != '' and a.show_code is not null and
not (g.certificateType != '1'
AND SUBSTR(a.show_code, 1, 6) < '201913')
UNION ALL
select
i.id AS "id",
i.show_code AS "showCode",
i.register_code AS "registerCode",
i.member_code AS "memberCode",
i.team_member_code AS "teamMemberCode",
i.name AS "name",
i.mobile AS "mobile",
i.gender AS "gender",
i.avatar AS "avatar",
i.show_level AS "showLevel",
i.school_name_cn AS "schoolNameCn",
i.fee AS "fee",
i.leave_cancel AS "leaveCancel",
i.show_leave AS "showLeave",
i.show_dog_breed AS "showDogBreed",
i.show_dog_breed_extra AS "showDogBreedExtra",
i.order_price AS "orderPrice",
i.payment_member_code AS "paymentMemberCode",
i.operator AS "operator",
i.grade AS "grade",
i.grade_write AS "gradeWrite",
i.payment_charging_item_id AS "paymentChargingItemId",
i.proccess_state AS "proccessState",
i.payment_state AS "paymentState",
i.review_state AS "reviewState",
i.review_time AS "reviewTime",
i.review_remarks AS "reviewRemarks",
i.running_number AS "runningNumber",
i.payment_time AS "paymentTime",
i.proccess_time AS "proccessTime",
i.create_by AS "createBy.id",
i.create_date AS "createDate",
i.update_by AS "updateBy.id",
i.update_date AS "updateDate",
i.remarks AS "remarks",
i.del_flag AS "delFlag",
i.test_no AS "testNo",
i.referee AS "referee",
i.grade_time AS "gradeTime",
i.place AS "place",
i.take_certificate_date AS "takeCertificateDate",
j.show_code AS "groomerShow.showCode",
j.fee AS "groomerShow.fee",
j.register_end_time AS "groomerShow.registerEndTime",
j.show_name AS "groomerShow.showName",
j.show_type AS "groomerShow.showType",
j.show_category AS "groomerShow.showCategory",
j.show_address AS "groomerShow.showAddress",
j.start_time AS "groomerShow.startTime",
j.end_time AS "groomerShow.endTime",
j.id AS "groomerShow.id",
j.test_level AS "groomerShow.testLevel",
k.groomer_type AS "member.groomerType",
k.mobile AS "member.mobile",
k.gender AS "member.gender",
k.account_balance AS "member.accountBalance",
k.member_end_date AS "member.memberEndDate",
k.groomer_end_date AS "member.groomerEndDate",
k.id AS "member.id",
k.avatar AS "member.avatar",
f.id AS "groomerQualificationCertificate.id",
f.member_code AS "groomerQualificationCertificate.memberCode",
f.register_id AS "groomerQualificationCertificate.registerId",
f.take_certificate_date AS "groomerQualificationCertificate.takeCertificateDate",
f.level_en AS "groomerQualificationCertificate.levelCn",
f.level_en AS "groomerQualificationCertificate.levelEn",
f.fci_certificate_code AS "groomerQualificationCertificate.certificateCode",
f.certificate_state AS "groomerQualificationCertificate.certificateState",
f.issue_date AS "groomerQualificationCertificate.issueDate",
h.reviewer AS "groomerQualificationCertificate.reviewer",
h.corsage AS "groomerQualificationCertificate.corsage",
h.corsage_time AS "groomerQualificationCertificate.corsageTime",
f.print_state AS "groomerQualificationCertificate.printState",
h.review_state AS "groomerQualificationCertificate.reviewState",
h.review_time AS "groomerQualificationCertificate.reviewTime",
h.review_remarks AS "groomerQualificationCertificate.reviewRemarks",
f.running_number AS "groomerQualificationCertificate.runningNumber",
f.payment_state AS "groomerQualificationCertificate.paymentState",
f.payment_time AS "groomerQualificationCertificate.paymentTime",
h.proccess_time AS "groomerQualificationCertificate.proccessTime",
h.proccess_state AS "groomerQualificationCertificate.proccessState",
f.create_by AS "groomerQualificationCertificate.createBy.id",
f.create_date AS "groomerQualificationCertificate.createDate",
f.update_by AS "groomerQualificationCertificate.updateBy.id",
f.update_date AS "groomerQualificationCertificate.updateDate",
f.remarks AS "groomerQualificationCertificate.remarks",
f.del_flag AS "groomerQualificationCertificate.delFlag",
f.is_free AS "groomerQualificationCertificate.isFree",
f.certificateType AS "groomerQualificationCertificate.certificateType",
f.paper AS "groomerQualificationCertificate.paper",
2 AS "groomerQualificationCertificate.qualificationCertificateType"
from fci_groomer_qualification_certificate f
LEFT JOIN groomer_qualification_certificate h on f.certificate_id = h.id
LEFT JOIN groomer_register i ON f.register_id=i.id
LEFT JOIN groomer_show j ON i.show_code=j.show_code
LEFT JOIN sys_member k ON k.member_code=f.member_code
f.source_type = '2'
and f.del_flag = '0' and i.del_flag = '0' and j.del_flag = '0' and f.member_code = #{memberCode}
) S
order by S.createDate desc
limit #{pageNum}, #{pageSize}
SELECT
count(1)
FROM groomer_qualification_certificate a
a.del_flag = '0'
AND a.member_code = #{memberCode}
AND a.level_en = #{levelEn}
AND a.certificate_code IS NOT NULL
SELECT
a.del_flag = '0'
AND (m.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND b.register_code LIKE CONCAT('%',#{groomerRegister.registerCode},'%')
AND b.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND c.show_category = #{groomerShow.showCategory}
AND b.grade = #{groomerRegister.grade}
AND a.level_en = #{levelEn}
AND a.certificateType = #{certificateType}
AND a.certificate_code = #{certificateCode}
AND a.payment_state = #{paymentState}
AND a.proccess_state = #{proccessState}
AND a.print_state = #{printState}
AND a.certificate_state=#{certificateState}
AND c.show_category = #{showCategory}
AND b.create_by = #{registerCreateBy.id}
and a.level_cn IN
#{levelCn}
AND f.payment_state = #{fciPaymentState}
AND f.print_state = #{fciPrintState}
AND b.school_name_cn LIKE CONCAT('%',#{schoolNameCn},'%')
ORDER BY ${page.orderBy}
ORDER BY a.create_date DESC
select * from (SELECT
a.del_flag = '0'
AND (m.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND b.register_code LIKE CONCAT('%',#{groomerRegister.registerCode},'%')
AND b.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND c.show_category = #{groomerShow.showCategory}
AND b.grade = #{groomerRegister.grade}
AND a.level_en = #{levelEn}
AND a.certificateType = #{certificateType}
AND a.certificate_code = #{certificateCode}
AND a.payment_state = #{paymentState}
AND a.proccess_state = #{proccessState}
AND a.print_state = #{printState}
AND a.certificate_state=#{certificateState}
AND c.show_category = #{showCategory}
AND b.create_by = #{registerCreateBy.id}
and a.level_cn IN
#{levelCn}
AND f.payment_state = #{fciPaymentState}
AND f.print_state = #{fciPrintState}
AND b.school_name_cn LIKE CONCAT('%',#{schoolNameCn},'%')
union all
SELECT
gqc.id AS "id",
gqc.member_code AS "memberCode",
gqc.register_id AS "registerId",
gqc.take_certificate_date AS "takeCertificateDate",
gqc.level_cn AS "levelCn",
gqc.level_en AS "levelEn",
gqc.certificate_code AS "certificateCode",
gqc.certificate_state AS "certificateState",
gqc.issue_date AS "issueDate",
gqc.reviewer AS "reviewer",
gqc.corsage AS "corsage",
gqc.corsage_time AS "corsageTime",
gqc.print_state AS "printState",
gqc.review_state AS "reviewState",
gqc.review_time AS "reviewTime",
gqc.review_remarks AS "reviewRemarks",
gqc.running_number AS "runningNumber",
gqc.payment_state AS "paymentState",
gqc.payment_time AS "paymentTime",
gqc.proccess_time AS "proccessTime",
gqc.proccess_state AS "proccessState",
gqc.create_by AS "createBy.id",
gqc.create_date AS "createDate",
gqc.update_by AS "updateBy.id",
gqc.update_date AS "updateDate",
gqc.remarks AS "remarks",
gqc.del_flag AS "delFlag",
gqc.isFree AS "isFree",
gqc.certificateType AS "certificateType",
sm.id AS "member.id",
sm.gender AS "member.gender",
sm.mobile AS "member.mobile",
sm.name AS "member.name",
sm.name_en AS "member.nameEn",
sm.groomer_type AS "member.groomerType",
gr.show_level AS "groomerRegister.showLevel",
gr.register_code AS "groomerRegister.registerCode",
gr.show_code AS "groomerRegister.showCode",
gr.show_dog_breed AS "groomerRegister.showDogBreed",
gr.grade_time AS "groomerRegister.gradeTime",
gr.grade AS "groomerRegister.grade",
gr.grade_write AS "groomerRegister.gradeWrite",
gr.school_name_cn AS "groomerRegister.schoolNameCn",
gr.create_by AS "groomerRegister.createBy",
gs.show_name AS "groomerShow.showName",
gs.end_time AS "groomerShow.endTime",
gs.show_category AS "groomerShow.showCategory",
gs.test_level AS "groomerShow.testLevel",
ti1.school_type AS "schoolType",
gqc.paper AS "paper",
gqc.certificate_source AS "certificateSource",
fgqc.id AS "fciGroomerQualificationCertificate.id",
fgqc.member_code AS "fciGroomerQualificationCertificate.memberCode",
fgqc.take_certificate_date AS "fciGroomerQualificationCertificate.takeCertificateDate",
fgqc.fci_certificate_code AS "fciGroomerQualificationCertificate.fciCertificateCode",
fgqc.issue_date AS "fciGroomerQualificationCertificate.issueDate",
fgqc.print_state AS "fciGroomerQualificationCertificate.printState",
fgqc.running_number AS "fciGroomerQualificationCertificate.runningNumber",
fgqc.payment_state AS "fciGroomerQualificationCertificate.paymentState",
fgqc.payment_time AS "fciGroomerQualificationCertificate.paymentTime",
fgqc.create_by AS "fciGroomerQualificationCertificate.createBy.id",
fgqc.create_date AS "fciGroomerQualificationCertificate.createDate",
fgqc.update_by AS "fciGroomerQualificationCertificate.updateBy.id",
fgqc.update_date AS "fciGroomerQualificationCertificate.updateDate",
fgqc.remarks AS "fciGroomerQualificationCertificate.remarks",
fgqc.del_flag AS "fciGroomerQualificationCertificate.delFlag",
fgqc.register_id AS "fciGroomerQualificationCertificate.registerId",
fgqc.is_free AS "fciGroomerQualificationCertificate.isFree",
fgqc.certificate_state AS "fciGroomerQualificationCertificate.certificateState",
fgqc.paper AS "fciGroomerQualificationCertificate.paper",
fgqc.certificate_id AS "fciGroomerQualificationCertificate.certificateId",
fgqc.certificateType AS "fciGroomerQualificationCertificate.certificateType",
gr.payment_time AS "grPaymentTime"
FROM groomer_qualification_certificate gqc
LEFT JOIN groomer_register gr ON gqc.register_id=gr.id
LEFT JOIN groomer_show gs ON gr.show_code=gs.show_code
LEFT JOIN sys_member sm ON sm.member_code=gqc.member_code
LEFT JOIN sys_member sm2 ON gs.create_by = sm2.user_id and sm2.del_flag = '0'
LEFT JOIN training_institution ti1 ON ti1.school_member_code = sm2.member_code and ti1.del_flag = '0'
LEFT JOIN fci_groomer_qualification_certificate fgqc ON gqc.id = fgqc.certificate_id
gqc.del_flag = '0' and gqc.level_cn IN ('A', 'B') AND gqc.certificateType = '6'
AND (sm.name LIKE CONCAT('%',#{memberCode},'%') OR gqc.member_code=#{memberCode})
AND gr.register_code LIKE CONCAT('%',#{groomerRegister.registerCode},'%')
AND gr.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND gs.show_category = #{groomerShow.showCategory}
AND gr.grade = #{groomerRegister.grade}
AND gqc.level_en = #{levelEn}
AND gqc.certificateType = #{certificateType}
AND gqc.certificate_code = #{certificateCode}
AND gqc.payment_state = #{paymentState}
AND gqc.proccess_state = #{proccessState}
AND gqc.print_state = #{printState}
AND gqc.certificate_state=#{certificateState}
AND gs.show_category = #{showCategory}
AND gr.create_by = #{registerCreateBy.id}
AND fgqc.payment_state = #{fciPaymentState}
AND fgqc.print_state = #{fciPrintState}
AND gr.school_name_cn LIKE CONCAT('%',#{schoolNameCn},'%')
) AS qb
ORDER BY qb.grPaymentTime
ORDER BY qb.createDate DESC
select
a.id AS "id",
a.member_code AS "memberCode",
a.register_id AS "registerId",
a.take_certificate_date AS "takeCertificateDate",
a.level_cn AS "levelCn",
a.level_en AS "levelEn",
a.certificate_code AS "certificateCode",
a.certificate_state AS "certificateState",
a.issue_date AS "issueDate",
a.reviewer AS "reviewer",
a.corsage AS "corsage",
a.corsage_time AS "corsageTime",
a.print_state AS "printState",
a.review_state AS "reviewState",
a.review_time AS "reviewTime",
a.review_remarks AS "reviewRemarks",
a.running_number AS "runningNumber",
a.payment_state AS "paymentState",
a.payment_time AS "paymentTime",
a.proccess_time AS "proccessTime",
a.proccess_state AS "proccessState",
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.isFree AS "isFree",
a.certificateType AS "certificateType",
f.id AS "fciGroomerQualificationCertificate.id",
f.member_code AS "fciGroomerQualificationCertificate.memberCode",
f.take_certificate_date AS "fciGroomerQualificationCertificate.takeCertificateDate",
f.fci_certificate_code AS "fciGroomerQualificationCertificate.fciCertificateCode",
f.issue_date AS "fciGroomerQualificationCertificate.issueDate",
f.print_state AS "fciGroomerQualificationCertificate.printState",
f.running_number AS "fciGroomerQualificationCertificate.runningNumber",
f.payment_state AS "fciGroomerQualificationCertificate.paymentState",
f.payment_time AS "fciGroomerQualificationCertificate.paymentTime",
f.create_by AS "fciGroomerQualificationCertificate.createBy.id",
f.create_date AS "fciGroomerQualificationCertificate.createDate",
f.update_by AS "fciGroomerQualificationCertificate.updateBy.id",
f.update_date AS "fciGroomerQualificationCertificate.updateDate",
f.remarks AS "fciGroomerQualificationCertificate.remarks",
f.del_flag AS "fciGroomerQualificationCertificate.delFlag",
f.register_id AS "fciGroomerQualificationCertificate.registerId",
f.is_free AS "fciGroomerQualificationCertificate.isFree",
f.certificate_state AS "fciGroomerQualificationCertificate.certificateState",
f.paper AS "fciGroomerQualificationCertificate.paper",
f.certificate_id AS "fciGroomerQualificationCertificate.certificateId",
f.certificateType AS "fciGroomerQualificationCertificate.certificateType",
f.dog_category AS "fciGroomerQualificationCertificate.dogCategory"
FROM groomer_qualification_certificate a
LEFT JOIN fci_groomer_qualification_certificate f ON a.id = f.certificate_id
WHERE a.id = #{id} and a.del_flag = '0' and f.del_flag = '0'
select
id,
member_code,
register_id,
level_cn,
level_en,
corsage,
print_state,
review_state,
payment_state,
proccess_state,
create_by,
create_date,
update_by,
update_date,
remarks,
del_flag,
take_certificate_date,
certificateType,
isFree
FROM groomer_qualification_certificate
WHERE level_en =#{levelEn} AND member_code = #{memberCode} AND del_flag='0' order by create_date DESC limit 0,1
select
a.id,
a.member_code,
a.register_id,
a.level_cn,
a.level_en,
a.corsage,
a.print_state,
a.review_state,
a.payment_state,
a.proccess_state,
a.create_by,
a.create_date,
a.update_by,
a.update_date,
a.remarks,
a.del_flag,
a.take_certificate_date,
a.certificateType,
a.isFree,
a.register_id
FROM groomer_qualification_certificate a
left join groomer_register g on a.register_id = g.id
WHERE a.member_code = #{memberCode} AND g.show_code = #{showCode} AND a.certificateType = '6' AND a.del_flag='0'
SELECT
a.id,
a.member_code,
a.take_certificate_date,
b.gender,
a.level_cn,
a.level_en,
a.certificate_code,
b.name,
a.certificateType,
b.show_dog_breed
FROM groomer_qualification_certificate a
a.del_flag = '0'
AND (m.name LIKE CONCAT('%',#{memberCode},'%') OR a.member_code=#{memberCode})
AND b.register_code LIKE CONCAT('%',#{groomerRegister.registerCode},'%')
AND b.show_code LIKE CONCAT('%',#{groomerRegister.showCode},'%')
AND c.show_category = #{groomerShow.showCategory}
AND b.grade = #{groomerRegister.grade}
AND a.level_en = #{levelEn}
AND a.certificateType = #{certificateType}
AND a.certificate_code = #{certificateCode}
AND a.payment_state = #{paymentState}
AND a.proccess_state = #{proccessState}
AND a.print_state = #{printState}
AND a.certificate_state=#{certificateState}
AND c.show_category = #{showCategory}
AND b.create_by = #{registerCreateBy.id}
and a.level_cn IN
#{levelCn}
AND f.payment_state = #{fciPaymentState}
AND f.print_state = #{fciPrintState}
AND b.school_name_cn LIKE CONCAT('%',#{schoolNameCn},'%')
ORDER BY a.level_en ASC ,b.name DESC