<?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.report.ReportMapper">

	<select id="getCommentByProject" resultType="org.agt.module.license.controller.admin.report.vo.ReportProgressProjectRespVO">
		<include refid="comment"></include>
		ORDER BY
			pmt.project_max_update_time DESC,
			c.update_time DESC
	</select>

	<select id="getCommentByStaff" resultType="org.agt.module.license.controller.admin.report.vo.ReportProgressProjectRespVO">
		<include refid="comment"></include>
		ORDER BY
		mt.max_update_time DESC,
		c.update_time DESC
	</select>

	<sql id="comment">
		SELECT
			p.`name` project_name,
			p.`status` status,
			p.business_status,
			p.start_time,
			cu.`name` customer_name,
			l.serial_no,
			u.nickname author,
			c.*,
			u.avatar,
			pc.user_id reply_user_id,
			pu.nickname reply_user,
			bo.nickname business_owner_name,
			toa.nickname technical_owner_a_name,
			tob.nickname technical_owner_b_name
		FROM
			crm_project p
			LEFT JOIN crm_customer cu ON p.customer_id = cu.id
			AND cu.deleted = 0
			LEFT JOIN ( SELECT project_id, GROUP_CONCAT( serial_no ) serial_no FROM crm_license_server WHERE deleted = 0 GROUP BY project_id ) l ON l.project_id = p.id
			LEFT JOIN crm_comment c ON p.id = c.project_id
			AND c.deleted = 0
			LEFT JOIN system_users u ON c.user_id = u.id
			AND u.deleted = 0
			LEFT JOIN ( SELECT user_id, max( update_time ) max_update_time FROM crm_comment WHERE deleted = 0 GROUP BY user_id ) mt ON u.id = mt.user_id
			LEFT JOIN ( SELECT project_id, max( update_time ) project_max_update_time FROM crm_comment WHERE deleted = 0 GROUP BY project_id ) pmt ON p.id = pmt.project_id
			LEFT JOIN crm_comment pc ON c.parent_id = pc.id
			AND pc.deleted = 0
			LEFT JOIN system_users pu ON pc.user_id = pu.id
			AND pu.deleted = 0
			LEFT JOIN system_users bo ON p.business_owner = bo.id
			AND bo.deleted = 0
			LEFT JOIN system_users toa ON p.technical_owner_a = toa.id
			AND toa.deleted = 0
			LEFT JOIN system_users tob ON p.technical_owner_b = tob.id
			AND tob.deleted = 0
		WHERE
			p.deleted = 0
			AND c.id IS NOT NULL
		<if test="query.customerId != null">
			AND p.customer_id = #{query.customerId}
		</if>
		<if test="query.projectId != null">
			AND p.id = #{query.projectId}
		</if>
		<if test="query.serialNo != null and query.serialNo != ''">
			AND l.serial_no LIKE CONCAT('%',#{query.serialNo},'%')
		</if>
		<if test="query.updateTime != null and query.updateTime.length > 1">
			AND c.update_time BETWEEN #{query.updateTime[0]} AND #{query.updateTime[1]}
		</if>
		<if test="query.updateTimeStart != null and query.updateTimeEnd != null">
			AND DATE_FORMAT(c.update_time, '%Y-%m-%d') BETWEEN DATE_FORMAT(#{query.updateTimeStart}, '%Y-%m-%d') AND DATE_FORMAT(#{query.updateTimeEnd}, '%Y-%m-%d')
		</if>
		<if test="query.authorId != null">
			AND u.id = #{query.authorId}
		</if>
	</sql>

	 <select id="getExpiryLicense" resultType="org.agt.module.license.controller.admin.report.vo.ReportLicenseRespVO">
        SELECT
            l.id,
            l.project_id,
            l.serial_no,
            l.expiry_date,
		 	l.expiry_date expiry_date_format,
            l.ran_number,
            l.user_number,
            l.file_url,
            l.applicant,
            l.application_time,
            l.license_admin,
            l.approver,
            l.approve_time,
            p.`status`,
            p.business_status,
            l.remark,
            l.data_type,
            l.creator,
            l.create_time,
            l.updater,
            l.update_time,
            l.deleted,
            l.tenant_id,
        	p.customer_id,
        	p.start_time,
		 	p.`name` project_name,
		 	c.`name` customer_name,
		 	bo.nickname business_owner_name,
		 	toa.nickname technical_owner_a_name,
		 	tob.nickname technical_owner_b_name
        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
		 	LEFT JOIN system_users bo ON p.business_owner = bo.id
		 	AND bo.deleted = 0
		 	LEFT JOIN system_users toa ON p.technical_owner_a = toa.id
		 	AND toa.deleted = 0
		 	LEFT JOIN system_users tob ON p.technical_owner_b = tob.id
		 	AND tob.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.expiryDateStart != null and query.expiryDateEnd != null">
			 AND DATE_FORMAT(l.expiry_date, '%Y-%m-%d') BETWEEN DATE_FORMAT(#{query.expiryDateStart}, '%Y-%m-%d') AND DATE_FORMAT(#{query.expiryDateEnd}, '%Y-%m-%d')
		 </if>
		 <if test="query.isExpired != null and query.isExpired">
			 AND DATE_FORMAT(l.expiry_date, '%Y%m%d') &lt; DATE_FORMAT(now(), '%Y%m%d')
			 ORDER BY
			 l.expiry_date DESC
		 </if>
		 <if test="query.isExpired != null and !query.isExpired">
			 AND DATE_FORMAT(l.expiry_date, '%Y%m%d') >= DATE_FORMAT(now(), '%Y%m%d')
			 ORDER BY
			 l.expiry_date
		 </if>
    </select>
</mapper>