<?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.dromara.zjk.mapper.ZjkProductMapper">
    <update id="updateProductByApproval">
        update zjk_product
        set approval_status = #{approvalStatus},
            approval_reason = #{approvalReason}
        where id = #{id};
    </update>

    <select id="getProduct" resultType="org.dromara.zjk.domain.vo.ZjkProductVo">
        SELECT
            zp.id AS product_id,
            max(zp.product_name)  AS product_name
        FROM
            zjk_item_extreation zie
                INNER JOIN zjk_review_phase zrp on zie.id= zrp.item_extreation_id and zrp.effective = 1 and is_summarize = 0
                INNER JOIN zjk_product zp ON zp.ID = zrp.item_id
        WHERE zie.is_sure =1
          AND SYSDATE >= zrp.review_time
        GROUP BY zp.id

    </select>
    <select id="getItemId" resultType="org.dromara.zjk.domain.vo.ItemVO">
        select zei.id as itemId,zrp.review_sn as status
        from zjk_expert_item zei
        inner join zjk_review_phase zrp on zei.review_id = zrp.id
        where zei.item_id = #{productId} and zei.review_id =#{reviewId}
        limit 1
    </select>
    <select id="getProductReview" resultType="org.dromara.zjk.domain.vo.ZjkReviewProductVo">
        SELECT zrp.id as  review_id, zrp.review_sn
        FROM zjk_review_phase zrp
        inner join zjk_item_extreation zie on zie.id = zrp.item_extreation_id
        WHERE zie.is_sure = 1 and zrp.effective = 1 AND zrp.is_summarize = 0 and zrp.item_id = #{productId} AND SYSDATE >= zrp.review_time

    </select>
    <select id="productPanorama" resultType="org.dromara.zjk.domain.vo.ProductPanoramaVo">
        SELECT MAX(zp.product_name) product_name ,max(zp.product_type) product_type ,max(zrp.review_time)
        review_time,max(zrp.review_sn) review_sn,max(zp.product_scale)product_scale , MAX(ze.work_unit) work_unit
        ,max(zp.user_id) user_id,Max(ze.expert_name) expert_name,MAX(zp.id) product_id,Max(zrp.id) review_id,MAX(zp.create_time) create_time
        FROM zjk_product zp
        left JOIN zjk_review_phase zrp on zp.id=zrp.item_id
        LEFT JOIN zjk_expert ze on ze.user_id = zp.user_id
        WHERE zp.del_flag = 0
        <if test="bo.productName != null and bo.productName !='' ">
            and zp.product_name like '%' || #{bo.productName} || '%'
        </if>
        <if test="bo.workUnit != null and bo.workUnit !='' ">
            and ze.work_unit like '%' || #{bo.workUnit} || '%'
        </if>
        <if test="bo.productType != null and bo.productType !='' ">
            and zp.product_type = #{bo.productType}
        </if>
        <if test="bo.maxProductScale !=null ">
            and  zp.product_Scale &lt; #{bo.maxProductScale}
        </if>
          <if test="bo.minProductScale !=null ">
              and  zp.product_Scale >= #{bo.minProductScale}
        </if>
        <if test="bo.productScale != null and bo.productScale !='' ">
            and zp.product_scale = #{bo.productScale}
        </if>
        <if test="bo.deptId != null ">
            and zp.create_dept = #{bo.deptId}
        </if>
        <if test="bo.startTime != null ">
            and zrp.review_time >= #{bo.startTime}
        </if>
        <if test="bo.endTime != null ">
            and zrp.review_time &lt;= #{bo.endTime}
        </if>
        <choose>
            <when test="bo.reviewSn !=null and  bo.reviewSn == '0'">
                and zrp.review_sn ISNULL
            </when>
            <when test="bo.reviewSn !=null">
                and zrp.review_sn = #{bo.reviewSn}
            </when>
        </choose>
        GROUP BY zp.id
    </select>
    <select id="selectProductReview" resultType="org.dromara.zjk.domain.vo.ZjkProductVo">
        select zpr.*,zp.* ,zpr.id as productReviewId,zpr.create_time
        from zjk_product_reviewsn zpr
        inner join zjk_product zp on zpr.product_id = zp.id
        where zp.del_flag = 0
        <if test="bo.productName != null and bo.getProductName != '' ">
            and zp.product_name like '%' || #{bo.productName} || '%'
        </if>
    </select>

    <select id="getProductTypeCountReport" resultType="org.dromara.zjk.domain.ProductCountReport">
        SELECT
            TO_CHAR(DATE_TRUNC('month', create_time), 'YYYY-MM') AS month,
            COUNT(CASE WHEN product_type = '1' THEN 1 ELSE NULL END) AS "governmentInformation",
            COUNT(CASE WHEN product_type = '2' THEN 1 ELSE NULL END) AS "researchProjects",
            COUNT(CASE WHEN product_type = '3' THEN 1 ELSE NULL END) AS "technologyInformation"
        FROM
            zjk_product
        WHERE
            create_time >= DATE_TRUNC('month', NOW()) - INTERVAL '1 year'  -- 获取最近一年的数据
                AND del_flag = 0  -- 只查询未删除的项目
        GROUP BY
            TO_CHAR(DATE_TRUNC('month', create_time), 'YYYY-MM')  -- 按月分组
        ORDER BY
            month;
    </select>

    <select id="getReviewStageSummary" resultType="org.dromara.zjk.domain.vo.ProjectSummaryStatisticsVo">
        SELECT
            TO_CHAR( DATE_TRUNC( 'month',ps.update_time ), 'YYYY-MM' )  AS mon,
            COUNT ( CASE WHEN ps.review_sn = 1 THEN 1 ELSE NULL END ) AS approval,
            COUNT ( CASE WHEN ps.review_sn = 2 THEN 1 ELSE NULL END ) AS bids,
            COUNT ( CASE WHEN ps.review_sn = 3 THEN 1 ELSE NULL END ) AS initialInspection,
            COUNT ( CASE WHEN ps.review_sn = 4 THEN 1 ELSE NULL END ) AS finalInspection
        FROM
            zjk_product_summarize ps -- 关联审核人信息表
        WHERE   ps.update_time >= ADD_MONTHS ( SYSDATE, - 6 )
        GROUP BY mon
        ORDER BY mon
    </select>

    <select id="projectSummaryStatistics" resultType="org.dromara.zjk.domain.dto.ProjectSummaryStatistics">
        SELECT-- 项目审核数量
              ( SELECT COUNT ( * ) FROM zjk_review_phase WHERE effective = 1 ) AS projectAuditSum,
-- 完成项目总结数量
              ( SELECT COUNT ( * ) FROM zjk_review_phase WHERE is_summarize = 1 AND effective = 1 ) AS projectSummaryQuantity,
-- 待总结数量
              ( SELECT COUNT ( * ) FROM zjk_review_phase WHERE is_summarize = 0 and effective = 1   AND SYSDATE >= review_time  ) AS quantitySummarized,
-- 待评价数量
              ( SELECT COUNT ( * ) FROM zjk_product_summarize WHERE del_flag = 0 and status = 3 ) AS quantityEvaluated,
-- 项目数量
              ( SELECT COUNT ( * ) FROM zjk_product WHERE del_flag = 0 ) AS numberItems,
--待审批总结数量
              ( SELECT COUNT ( * ) FROM zjk_product_summarize WHERE del_flag = 0 and status = 1 ) AS summaryNumberApproved;
    </select>
    <select id="selectProdoctPage" resultType="org.dromara.zjk.domain.vo.ZjkProductVo">
        select *
        from zjk_product
        <where>
            del_flag = 0
            <if test="bo.productSn !=null  and bo.productSn != '' ">
                and product_sn like '||' #{bo.productSn} '||'
            </if>
            <if test="bo.productName !=null  and bo.productName != '' ">
                and product_name like '||' #{bo.productName} '||'
            </if>
            ORDER BY create_time DESC
        </where>

    </select>
    <select id="productPanoramaDept" resultType="org.dromara.zjk.domain.vo.ProductPanoramaDeptVo">
        select sd.*
            from zjk_product  zp
        left join sys_dept sd on sd.dept_id = zp.create_dept
        where zp.del_flag = 0
    </select>
</mapper>
