<?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.ozs.pm.mapper.PmDemandMapper">

    <select id="selectByDemandIdList" parameterType="java.util.List" resultType="com.ozs.pm.doman.PmDemand">
        select * from pm_demand where project_status &gt;= 12 and demand_id in
        <foreach item="item" collection="demandIdList" separator="," open="(" close=")" index="">
            #{item}
        </foreach>
    </select>

    <select id="selectExtractionExpert" parameterType="com.ozs.base.domain.vo.BaseExpertVo"
            resultType="com.ozs.pm.doman.PmDemand">
        select * from pm_demand where demand_id in (select demand_id from pm_demand_expert_ref group by demand_id)
        <if test="projectName != null and projectName != ''">
            and project_name like '%' + #{projectName} + '%'
        </if>
        <if test="startTime != null  "><!-- 开始时间检索 -->
            and date_format(plan_demand_sub_time,'%y%m%d') &gt;= date_format(#{startTime},'%y%m%d')
        </if>
        <if test="endTime != null  "><!-- 结束时间检索 -->
            and date_format(plan_demand_sub_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
        </if>
    </select>

    <select id="purchaseProjectStatistical" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countId
        from pm_demand
        WHERE purchase_dept_id = #{purchaseDeptId}
    </select>

    <select id="purchaseProjectStatisticalYWC" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countYWC, ifnull(sum(evaluation), 0) evaluationYWC
        from pm_demand
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND project_status = '17'
    </select>

    <select id="purchaseProjectStatisticalXCJ" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        -- DATE_FORMAT(plan_demand_sub_time,'%Y')
        select count(demand_id) countXCJ, ifnull(sum(evaluation), 0) evaluationXCJ
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND project_status != '17'
          and year(plan_demand_sub_time) = year(NOW())
    </select>

    <select id="purchaseProjectStatisticalQNWC" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        -- DATE_FORMAT(plan_demand_sub_time,'%Y')
        select count(demand_id) countQNWC, ifnull(sum(evaluation), 0) evaluationQNWC
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND project_status = '17'
          and year(plan_demand_sub_time) = year(NOW()) - 1
    </select>

    <select id="countByProjectAttr" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT project_status columnName, COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
        GROUP BY project_status
    </select>

    <select id="countByPurchaseMode" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT purchase_mode columnName, COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
        GROUP BY purchase_mode
    </select>

    <select id="countByEvaluationLt" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND evaluation &lt; #{evaluation}
    </select>

    <select id="countByEvaluationBet" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND evaluation BETWEEN #{evaluation} and #{evaluationEnd}
    </select>
    <select id="countByEvaluationGt" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND evaluation &gt; #{evaluation}
    </select>

    <select id="countProjectExceed" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
          and DATE_FORMAT(create_time, '%Y') = YEAR(NOW())
    </select>

    <select id="countThisYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.lang.Integer">
        SELECT COUNT(demand_id) num
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND DATE_FORMAT(create_time, '%Y') = YEAR(NOW())
    </select>

    <select id="countProjectExceedYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT DATE_FORMAT(create_time, '%Y') columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
        GROUP BY DATE_FORMAT(create_time, '%Y')
        order by create_time asc
    </select>

    <select id="countProjectExceedMonth" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT DATE_FORMAT(create_time, '%Y-%m') columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
          and MONTH(
                      create_time) = #{month}
          AND DATE_FORMAT(create_time
                  , '%Y') = #{year}
        GROUP BY DATE_FORMAT(create_time, '%Y-%m')
    </select>

    <select id="countProjectExceedQuarter" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT DATE_FORMAT(create_time, '%Y-${quarter}季度') columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
          and QUARTER(create_time) = #{quarter}
          AND DATE_FORMAT(create_time, '%Y') = #{year}
    </select>

    <select id="countProjectExceedAll" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.PmDemand">
        SELECT *
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)

    </select>

    <select id="majorProjectByStatus" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND find_in_set('1', project_attr)
          AND `project_status` = #{projectStatus}
          AND DATE_FORMAT(create_time, '%Y') = year(now())
    </select>

    <select id="countMajorProjectExceed" resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
          AND find_in_set('1', project_attr)
          and DATE_FORMAT(create_time, '%Y') = YEAR(NOW())
    </select>

    <select id="taskQuantityYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT YEAR(NOW()) columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE find_in_set('1'
            , project_attr)
          AND `project_status` = #{code}
          and DATE_FORMAT(create_time
                  , '%Y') = YEAR(NOW())
    </select>

    <select id="taskQuantityLastYear" resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo"
            parameterType="java.lang.String">
        SELECT YEAR (NOW()) - 1 columnName, COUNT (demand_id) num
        FROM `pm_demand`
        WHERE find_in_set('1'
            , project_attr)
          AND `project_status` = #{code}
          and DATE_FORMAT(create_time
            , '%Y') = YEAR (NOW()) - 1
    </select>

    <select id="countEveryStatusNum" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT project_status columnName, COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND `project_status` IN (9, 10, 11, 12, 13, 14)
          and DATE_FORMAT(create_time, '%Y') = YEAR(NOW())
        GROUP BY project_status
    </select>

    <select id="countEveryStatusNumTwo" resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT project_status columnName, COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE `project_status` IN (15, 16, 17)
          and DATE_FORMAT(create_time, '%Y') = YEAR (NOW())
        GROUP BY project_status
    </select>

    <select id="selectByPlanId" parameterType="java.lang.Long" resultType="com.ozs.pm.doman.PmDemand">
        select *
        from pm_demand
        where plan_id = #{planId} limit 1
    </select>

    <select id="countMajorProjectLastYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT YEAR(NOW()) - 1 columnName, COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND find_in_set('1', project_attr)
          and DATE_FORMAT(create_time, '%Y') = YEAR(NOW()) - 1
    </select>

    <select id="countProjectMajorByYear" resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT DATE_FORMAT(create_time, '%Y') columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE find_in_set('1', project_attr)
        GROUP BY DATE_FORMAT(create_time, '%Y')
        order by create_time asc
    </select>

    <select id="countProjectMajorByMonth" resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT DATE_FORMAT(create_time, '%Y-%m') columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND find_in_set('1', project_attr)
        GROUP BY DATE_FORMAT(create_time, '%Y-%m')
    </select>

    <select id="countProjectMajorByQuarter" resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT create_time columnName, COUNT(demand_id) num
        FROM `pm_demand`
        WHERE purchase_dept_id = #{purchaseDeptId}
          AND find_in_set('1', project_attr)
          and QUARTER(create_time) = #{quarter}
          AND DATE_FORMAT(create_time, '%Y') = #{year}
    </select>

    <!-- 正常推荐项目 -->
    <select id="purchaseProjectExecuteNormalPropulsion" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countNormalPropulsion, ifnull(sum(evaluation), 0) evaluationNormalPropulsion
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND project_status != '17'
          and (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
    </select>

    <select id="purchaseProjectExecuteIncomplete" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countIncomplete, ifnull(sum(evaluation), 0) evaluationIncomplete
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND project_status in (4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
    </select>

    <select id="selectMinYear" resultType="java.lang.Integer">
        select min(year (plan_demand_sub_time))
        from pm_demand
    </select>

    <select id="selectpurchaseProjectCompleteNumberYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(a.demand_id)                countNum,
               ifnull(sum(a.evaluation), 0)      evaluation,
               year(a.plan_demand_sub_time)      yy,
               ifnull(sum(b.contract_amount), 0) contractAmount
        from (
                 select evaluation, plan_demand_sub_time, demand_id
                 from pm_demand
                 where purchase_dept_id = #{purchaseDeptId}
                   AND project_status = '17'
             ) a
                 left join (
            select contract_amount, demand_id
            from pm_contract_info
        ) b on a.demand_id = b.demand_id

        group by year(plan_demand_sub_time)
    </select>

    <select id="selectpurchaseProjectCompleteNumberQuarter" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select ifnull(sum(a.evaluation), 0)      evaluation,
               quarter(a.plan_demand_sub_time)   yy,
               ifnull(sum(b.contract_amount), 0) contractAmount
        from (
                 select evaluation, plan_demand_sub_time, demand_id
                 from pm_demand
                 where purchase_dept_id = #{purchaseDeptId}
                   AND project_status = '17'
                   and year(plan_demand_sub_time) = year(NOW())
             ) a
                 left join (
            select contract_amount, demand_id
            from pm_contract_info
        ) b on a.demand_id = b.demand_id
        group by concat(Year(a.plan_demand_sub_time), quarter(a.plan_demand_sub_time))
    </select>

    <select id="selectpurchaseProjectCompleteNumberMonth" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select ifnull(sum(a.evaluation), 0)            evaluation,
               date_format(plan_demand_sub_time, '%m') yy,
               ifnull(sum(b.contract_amount), 0)       contractAmount
        from (
                 select evaluation, plan_demand_sub_time, demand_id
                 from pm_demand
                 where purchase_dept_id = #{purchaseDeptId}
                   AND project_status = '17'
                   and year(plan_demand_sub_time) = year(NOW())
             ) a
                 left join (
            select contract_amount, demand_id
            from pm_contract_info
        ) b on a.demand_id = b.demand_id
        group by date_format(a.plan_demand_sub_time, '%y%m')
    </select>

    <select id="selectPurchaseTaskFinishYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(a.demand_id)                countNum,
               ifnull(sum(a.evaluation), 0)      evaluation,
               year(a.plan_demand_sub_time)      yy,
               ifnull(sum(b.contract_amount), 0) contractAmount
        from (
                 select evaluation, plan_demand_sub_time, demand_id
                 from pm_demand
                 where purchase_dept_id = #{purchaseDeptId}
                   AND project_status in (15, 16, 17)
             ) a
                 left join (
            select contract_amount, demand_id
            from pm_contract_info
        ) b on a.demand_id = b.demand_id
        group by year(a.plan_demand_sub_time)
    </select>

    <select id="selectPurchaseTaskFinishQuarter" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select ifnull(sum(a.evaluation), 0)      evaluation,
               quarter(a.plan_demand_sub_time)   yy,
               ifnull(sum(b.contract_amount), 0) contractAmount
        from (
                 select evaluation, plan_demand_sub_time, demand_id
                 from pm_demand
                 where purchase_dept_id = #{purchaseDeptId}
                   AND project_status in (15, 16, 17)
                   and year(plan_demand_sub_time) = year(NOW())
             ) a
                 left join (
            select contract_amount, demand_id
            from pm_contract_info
        ) b on a.demand_id = b.demand_id
        group by concat(Year(a.plan_demand_sub_time), quarter(a.plan_demand_sub_time))
    </select>

    <select id="selectPurchaseTaskFinishMonth" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select ifnull(sum(a.evaluation), 0)            evaluation,
               date_format(plan_demand_sub_time, '%m') yy,
               ifnull(sum(b.contract_amount), 0)       contractAmount
        from (
                 select evaluation, plan_demand_sub_time, demand_id
                 from pm_demand
                 where purchase_dept_id = #{purchaseDeptId}
                   AND project_status in (15, 16, 17)
                   and year(plan_demand_sub_time) = year(NOW())
             ) a
                 left join (
            select contract_amount, demand_id
            from pm_contract_info
        ) b on a.demand_id = b.demand_id
        group by date_format(a.plan_demand_sub_time, '%y%m')
    </select>

    <select id="countProjectExceedAndMajor" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="com.ozs.pm.doman.vo.responseVo.StatisticalChartsResVo">
        SELECT COUNT(demand_id) num, SUM(evaluation) evaluationTotal
        FROM `pm_demand`
        WHERE (real_demand_commit_time > plan_demand_sub_time
            or real_purchase_finish_time > plan_purchase_finish_time
            or real_deliver_time > plan_deliver_time)
          AND find_in_set('1', project_attr)
          and DATE_FORMAT(create_time, '%Y') = YEAR(NOW())
    </select>

    <select id="purchaseProjectDistribution" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countNumber from pm_demand where purchase_dept_id=#{purchaseDeptId} AND project_status
        in
        <foreach item="item" collection="projectStatusList" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="purchaseProjectNumberAnalysisYear" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countNum, ifnull(sum(evaluation), 0) evaluationSum, year(plan_demand_sub_time) yy
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
        group by year(plan_demand_sub_time)
    </select>

    <select id="purchaseProjectNumberAnalysisQuarter" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countNum, ifnull(sum(evaluation), 0) evaluationSum, quarter(plan_demand_sub_time) yy
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND year(plan_demand_sub_time) = year(NOW())
        group by concat(Year(plan_demand_sub_time), quarter(plan_demand_sub_time))
    </select>

    <select id="purchaseProjectNumberAnalysisOldYearQuarter" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id) countNum, ifnull(sum(evaluation), 0) evaluationSum, quarter(plan_demand_sub_time) yy
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND year(plan_demand_sub_time) = year(NOW() - 1)
        group by concat(Year(plan_demand_sub_time), quarter(plan_demand_sub_time))
    </select>

    <select id="purchaseProjectNumberAnalysisMonth" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id)                        countNum,
               ifnull(sum(evaluation), 0)              evaluationSum,
               date_format(plan_demand_sub_time, '%m') yy
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND year(plan_demand_sub_time) = year(NOW())
        group by date_format(plan_demand_sub_time, '%y%m')
    </select>

    <select id="purchaseProjectNumberAnalysisOldYearMonth" parameterType="com.ozs.pm.doman.vo.requestVo.PmDemandReqVo"
            resultType="java.util.Map">
        select count(demand_id)                        countNum,
               ifnull(sum(evaluation), 0)              evaluationSum,
               date_format(plan_demand_sub_time, '%m') yy
        from pm_demand
        where purchase_dept_id = #{purchaseDeptId}
          AND year(plan_demand_sub_time) = year(NOW() - 1)
        group by date_format(plan_demand_sub_time, '%y%m')
    </select>
</mapper>
