<?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="org.agt.module.license.dal.mysql.license.LicenseMapper">

    <select id="getBySnAndProjectAndCustomer" resultType="org.agt.module.license.dal.dataobject.license.LicenseDO">
        SELECT
        	l.*
        FROM
        	crm_license_server l
        	LEFT JOIN crm_project p ON l.project_id = p.id
        	AND p.deleted = 0
        	LEFT JOIN crm_customer c ON p.customer_id = c.id
        	AND c.deleted = 0
        WHERE
        	l.deleted = 0
        <if test="serialNo != null and serialNo != ''">
            AND l.serial_no = #{serialNo}
        </if>
        <if test="customerName != null and customerName != ''">
            AND c.name = #{customerName}
        </if>
        <if test="projectName != null and projectName != ''">
            AND p.name = #{projectName}
        </if>
        limit 1
    </select>


    <select id="queryPage" resultType="org.agt.module.license.controller.admin.license.vo.LicenseRespVO">
        SELECT
            l.id,
            l.project_id,
            l.serial_no,
            l.expiry_date,
            l.ran_number,
            l.user_number,
            l.file_url,
            l.applicant,
            l.application_time,
            l.license_admin,
            l.approver,
            l.approve_time,
            l.`status`,
            l.remark,
            l.data_type,
            l.creator,
            l.create_time,
            l.updater,
            l.update_time,
            l.deleted,
            l.tenant_id,
        	p.customer_id
        FROM
        	crm_license_server l
        	LEFT JOIN crm_project p ON l.project_id = p.id
        	AND p.deleted = 0
        WHERE
        	l.deleted = 0
        <if test="query.customerId != null">
            AND p.customer_id = #{query.customerId}
        </if>
        <if test="query.projectId != null">
            AND l.project_id = #{query.projectId}
        </if>
        <if test="query.serialNo != null and query.serialNo != ''">
            AND l.serial_no LIKE CONCAT('%',#{query.serialNo},'%')
        </if>
        <if test="query.expiryDate != null and query.expiryDate.length > 1">
            AND l.expiry_date BETWEEN #{query.expiryDate[0]} AND #{query.expiryDate[1]}
        </if>
        <if test="query.userNumber != null">
            AND l.user_number = #{query.userNumber}
        </if>
        <if test="query.ranNumber != null">
            AND l.ran_number = #{query.ranNumber}
        </if>
        <if test="query.applicant != null">
            AND l.applicant = #{query.applicant}
        </if>
        <if test="query.approver != null">
            AND l.approver = #{query.approver}
        </if>
        <if test="query.status != null">
            AND l.status = #{query.status}
        </if>
        <if test="query.remark != null and query.remark != ''">
            AND l.remark LIKE CONCAT('%',#{query.remark},'%')
        </if>
        <if test="query.applicationTime != null and query.applicationTime.length > 1">
            AND l.application_time BETWEEN #{query.applicationTime[0]} AND #{query.applicationTime[1]}
        </if>
        <if test="query.createTime != null and query.createTime.length > 1">
            AND l.create_time BETWEEN #{query.createTime[0]} AND #{query.createTime[1]}
        </if>
        <if test="query.projectName != null and query.projectName != ''">
            AND p.name LIKE CONCAT('%',#{query.projectName},'%')
        </if>

        <choose>
            <when test="query.sortField != null and query.sortField != '' and query.sortOrder != null and query.sortOrder != ''">
                ORDER BY
                ${query.sortField} ${query.sortOrder}
            </when>
            <otherwise>
                ORDER BY
                l.application_time DESC
            </otherwise>
        </choose>
    </select>
</mapper>