<?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.project.ProjectMapper">

    <select id="selectMaxCode" resultType="Integer">
        SELECT max(`code` + 0) from crm_project;
    </select>

    <select id="selectOldMaxCode" resultType="Integer">
        SELECT max(`code` + 0) from crm_project where `code` + 0 &lt; 2000
    </select>

    <select id="queryPage" resultType="org.agt.module.license.controller.admin.project.vo.ProjectRespVO">
        <include refid="queryProjects"></include>
    </select>

    <select id="queryList" resultType="org.agt.module.license.controller.admin.project.vo.ProjectRespVO">
        SELECT
            p.*,
            c.serial_no
        FROM
            crm_project p
                LEFT JOIN crm_license_server c ON p.id = c.project_id
                AND c.deleted = 0
        WHERE
            p.deleted = 0
        <if test="query.customerId != null">
            AND p.customer_id = #{query.customerId}
        </if>
        <if test="query.name != null and query.name != ''">
            AND p.name LIKE CONCAT('%',#{query.name},'%')
        </if>
        <if test="query.code != null and query.code != ''">
            AND p.code LIKE CONCAT('%',#{query.code },'%')
        </if>
        <if test="query.contractCode != null and query.contractCode != ''">
            AND p.contract_code LIKE CONCAT('%',#{query.contractCode},'%')
        </if>
        <if test="query.businessStatus != null">
            AND p.business_status = #{query.businessStatus}
        </if>
        <if test="query.businessOwner != null">
            AND p.business_owner = #{query.businessOwner}
        </if>
        <if test="query.customerOwner != null and query.customerOwner != ''">
            AND p.customer_owner LIKE CONCAT('%',#{query.customerOwner},'%')
        </if>
        <if test="query.startTime != null and query.startTime.length > 1">
            AND p.start_time BETWEEN #{query.startTime[0]} AND #{query.startTime[1]}
        </if>
        <if test="query.endTime != null and query.endTime.length > 1">
            AND p.end_time BETWEEN #{query.endTime[0]} AND #{query.endTime[1]}
        </if>
        <if test="query.createTime != null and query.createTime.length > 1">
            AND p.create_time BETWEEN #{query.createTime[0]} AND #{query.createTime[1]}
        </if>
        <if test="query.status != null">
            AND p.status = #{query.status}
        </if>
        <if test="query.envInfo != null and query.envInfo != ''">
            AND p.env_info LIKE CONCAT('%',#{query.envInfo},'%')
        </if>
        <if test="query.serialNo != null and query.serialNo != ''">
            AND c.serial_no LIKE CONCAT('%',#{query.serialNo},'%')
        </if>
        <if test="query.technicalOwner != null">
            AND (p.technical_owner_a = #{query.technicalOwner}
                     OR p.technical_owner_b = #{query.technicalOwner}
                     OR p.technical_owner_c = #{query.technicalOwner})
        </if>
        ORDER BY
            p.create_time DESC
    </select>


    <sql id="queryProjects">
        SELECT
        	p.*
        FROM
        	(
        	SELECT
        		p.*,
        		l.serial_no,
        		ifnull( pc.comment_num, 0 ) comment_num,
        		ifnull( pl.apply_count, 0 ) apply_count
        	FROM
        		crm_project p
        		LEFT JOIN ( SELECT project_id, GROUP_CONCAT( serial_no ) serial_no FROM crm_license_server WHERE deleted = 0 GROUP BY project_id ) l ON p.id = l.project_id
        		LEFT JOIN ( SELECT c.project_id, count( c.id ) comment_num FROM crm_comment c WHERE c.deleted = 0 GROUP BY c.project_id ) pc ON p.id = pc.project_id
        		LEFT JOIN (
        		SELECT
        			l.project_id,
        			count( lh.id ) + count(l.serial_no) apply_count
        		FROM
        			crm_license_server l
        			LEFT JOIN crm_license_server_history lh ON l.id = lh.license_id
        			AND lh.deleted = 0
        		WHERE
        			l.deleted = 0
        		GROUP BY
        			l.project_id
        		) pl ON p.id = pl.project_id
        	WHERE
        	p.deleted = 0
        	) p
        <where>
        <if test="query.customerId != null">
            AND p.customer_id = #{query.customerId}
        </if>
        <if test="query.name != null and query.name != ''">
            AND p.name LIKE CONCAT('%',#{query.name},'%')
        </if>
        <if test="query.code != null and query.code != ''">
            AND p.code LIKE CONCAT('%',#{query.code },'%')
        </if>
        <if test="query.contractCode != null and query.contractCode != ''">
            AND p.contract_code LIKE CONCAT('%',#{query.contractCode},'%')
        </if>
        <if test="query.businessStatus != null">
            AND p.business_status = #{query.businessStatus}
        </if>
        <if test="query.businessOwner != null">
            AND p.business_owner = #{query.businessOwner}
        </if>
        <if test="query.customerOwner != null and query.customerOwner != ''">
            AND p.customer_owner LIKE CONCAT('%',#{query.customerOwner},'%')
        </if>
        <if test="query.startTime != null and query.startTime.length > 1">
            AND p.start_time BETWEEN #{query.startTime[0]} AND #{query.startTime[1]}
        </if>
        <if test="query.endTime != null and query.endTime.length > 1">
            AND p.end_time BETWEEN #{query.endTime[0]} AND #{query.endTime[1]}
        </if>
        <if test="query.createTime != null and query.createTime.length > 1">
            AND p.create_time BETWEEN #{query.createTime[0]} AND #{query.createTime[1]}
        </if>
        <if test="query.status != null">
            AND p.status = #{query.status}
        </if>
        <if test="query.envInfo != null and query.envInfo != ''">
            AND p.env_info LIKE CONCAT('%',#{query.envInfo},'%')
        </if>
        <if test="query.serialNo != null and query.serialNo != ''">
            AND p.serial_no LIKE CONCAT('%',#{query.serialNo},'%')
        </if>
        <if test="query.technicalOwner != null">
            AND (p.technical_owner_a = #{query.technicalOwner}
                     OR p.technical_owner_b = #{query.technicalOwner}
                     OR p.technical_owner_c = #{query.technicalOwner})
        </if>
        </where>
        <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
                p.create_time DESC
            </otherwise>
        </choose>
    </sql>

    <select id="getLicenseProjects" resultType="org.agt.module.license.controller.admin.project.vo.ProjectRespVO">
        SELECT
        	p.*
        FROM
        	`crm_project` p
        	LEFT JOIN `crm_license_server` l ON p.id = l.project_id
        	AND l.deleted = 0
        WHERE
        	p.deleted = 0
            <if test="query.customerId != null and query.customerId != ''">
                AND p.customer_id = #{query.customerId}
            </if>
        	AND l.id IS NULL
    </select>

    <select id="getBySnAndProjectAndCustomer" resultType="org.agt.module.license.dal.dataobject.project.ProjectDO">
        SELECT
        	p.*
        FROM
        	crm_project p
        	LEFT JOIN crm_customer c ON p.customer_id = c.id
        	AND c.deleted = 0
        	LEFT JOIN crm_license_server l ON l.project_id = p.id
        	AND l.deleted = 0
        WHERE
        	p.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>
</mapper>