<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qmth.sop.business.mapper.TBCrmDetailMapper">

    <select id="list" resultType="com.qmth.sop.business.entity.TBCrmDetail">
        select t.id,
               t.crm_no as crmNo,
               t.flow_deployment_id as flowDeploymentId,
               t.sop_no as sopNo,
               t.status,
               t.course_name as courseName,
               IF(t.exam_student_count >= 0,t.exam_student_count,null) as examStudentCount,
               t.contacts,
               t.mobile_number as mobileNumber,
               t.service_scope as serviceScope,
               IF(t.scan_start_time >= 0,t.scan_start_time,null) as scanStartTime,
               IF(t.scan_end_time >= 0,t.scan_end_time,null) as scanEndTime,
               IF(t.mark_paper_start_time >= 0,t.mark_paper_start_time,null) as markPaperStartTime,
               IF(t.mark_paper_end_time >= 0,t.mark_paper_end_time,null) as markPaperEndTime,
               t.create_id as createId,
               t.create_time as createTime,
               t.update_id as updateId,
               t.update_time as updateTime,
               t.publish_time as publishTime,tbsi.status as sopStatus from t_b_crm_detail t
        left join t_f_custom_flow_entity tfcfe on tfcfe.code = t.sop_no
        left join t_b_sop_info tbsi on tbsi.sop_no = t.sop_no
        where t.crm_no = #{crmNo}
        order by t.course_name
    </select>

    <select id="findByServiceIdAndCustomId" resultType="com.qmth.sop.business.entity.TBCrmDetail">
        SELECT
            tbcd.*
        FROM
            t_b_crm_detail tbcd
                LEFT JOIN
            t_b_crm tbc ON tbcd.crm_no = tbc.crm_no
        <where>
            <if test="serviceId != null">
                AND tbc.service_id = #{serviceId}
            </if>
            <if test="customId != null">
                AND tbc.custom_id = #{customId}
            </if>
        </where>
    </select>
</mapper>
