相关sql练习

最新推荐文章于 2024-09-14 18:42:05 发布

weixin_46974942

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量103

点赞数 2

文章标签： sql python 数据库

本文链接：https://blog.csdn.net/weixin_46974942/article/details/140949925

版权

Plan

<?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.cntaiping.pls.plan.main.mapper.PlanMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="planResultMap" type="com.cntaiping.pls.plan.main.entity.Plan">
        <result column="id" property="id"/>
        <result column="item_id" property="itemId"/>
        <result column="item_seq" property="itemSeq"/>
        <result column="item_code" property="itemCode"/>
        <result column="item_name" property="itemName"/>
        <result column="merge_code" property="mergeCode"/>
        <result column="plan_code" property="planCode"/>
        <result column="item_product_name" property="itemProductName"/>
        <result column="item_product_code" property="itemProductCode"/>
        <result column="item_descr" property="itemDescr"/>
        <result column="advice_purchase_mode" property="advicePurchaseMode"/>
        <result column="advice_start_date" property="adviceStartDate"/>
        <result column="budget_amount" property="budgetAmount"/>
        <result column="budget_amount_currency" property="budgetAmountCurrency"/>
        <result column="budget_exchange_rate" property="budgetExchangeRate"/>
        <result column="budget_amount_cny" property="budgetAmountCny"/>
        <result column="purchasing_years" property="purchasingYears"/>
        <result column="own_master_org_id" property="ownMasterOrgId"/>
        <result column="own_master_org_name" property="ownMasterOrgName"/>
        <result column="demand_org_id" property="demandOrgId"/>
        <result column="demand_org_name" property="demandOrgName"/>
        <result column="demand_org_dept_id" property="demandOrgDeptId"/>
        <result column="demand_org_dept_name" property="demandOrgDeptName"/>
        <result column="demand_user_id" property="demandUserId"/>
        <result column="demand_user_name" property="demandUserName"/>
        <result column="perform_org_id" property="performOrgId"/>
        <result column="perform_org_name" property="performOrgName"/>
        <result column="perform_org_dept_id" property="performOrgDeptId"/>
        <result column="perform_org_dept_name" property="performOrgDeptName"/>
        <result column="central_purchase_mode" property="centralPurchaseMode"/>
        <result column="central_start_date" property="centralStartDate"/>
        <result column="master_purchase_mode" property="masterPurchaseMode"/>
        <result column="master_start_date" property="masterStartDate"/>
        <result column="branch_purchase_mode" property="branchPurchaseMode"/>
        <result column="branch_start_date" property="branchStartDate"/>
        <result column="company_name" property="companyName"/>
        <result column="is_XinChang" property="isXinchang"/>
        <result column="remark" property="remark"/>
        <result column="report_time" property="reportTime"/>
        <result column="operation_org_dept" property="operationOrgDept"/>
        <result column="excute_amount" property="excuteAmount"/>
        <result column="usable_amount" property="usableAmount"/>
        <result column="use_of_status" property="useOfStatus"/>
        <result column="use_of_desc" property="useOfDesc"/>
        <result column="manual_apply_id" property="manualApplyId"/>
        <result column="manual_apply_code" property="manualApplyCode"/>
        <result column="manual_apply_name" property="manualApplyName"/>
        <result column="manual_apply_desc" property="manualApplyDesc"/>
        <result column="meeting_time" property="meetingTime"/>
        <result column="meeting_result" property="meetingResult"/>
        <result column="meeting_result_remark" property="meetingResultRemark"/>
        <result column="is_release" property="isRelease"/>
        <result column="release_time" property="releaseTime"/>
        <result column="is_close" property="isClose"/>
        <result column="operation_stage" property="operationStage"/>
        <result column="rebuild_plan_id" property="rebuildPlanId"/>
        <result column="create_time" property="createTime"/>
        <result column="create_user_id" property="createUserId"/>
        <result column="update_time" property="updateTime"/>
        <result column="update_user_id" property="updateUserId"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <resultMap id="planListResultMap" type="com.cntaiping.pls.plan.main.vo.PlanListVO">
        <!-- 计划明细信息 -->
        <result column="plan_id" property="planId"/>
        <result column="plan_code" property="planCode"/>
        <result column="item_id" property="itemId"/>
        <result column="item_seq" property="itemSeq"/>
        <result column="item_code" property="itemCode"/>
        <result column="item_name" property="itemName"/>
        <result column="merge_code" property="mergeCode"/>
        <result column="item_product_name" property="itemProductName"/>
        <result column="item_product_code" property="itemProductCode"/>
        <result column="item_descr" property="itemDescr"/>
        <result column="advice_purchase_mode" property="advicePurchaseMode"/>
        <result column="advice_start_date" property="adviceStartDate"/>
        <result column="budget_amount" property="budgetAmount"/>
        <result column="budget_amount_currency" property="budgetAmountCurrency"/>
        <result column="budget_exchange_rate" property="budgetExchangeRate"/>
        <result column="budget_amount_cny" property="budgetAmountCny"/>
        <result column="purchasing_years" property="purchasingYears"/>
        <result column="own_master_org_id" property="ownMasterOrgId"/>
        <result column="own_master_org_name" property="ownMasterOrgName"/>
        <result column="demand_org_id" property="demandOrgId"/>
        <result column="demand_org_name" property="demandOrgName"/>
        <result column="demand_org_dept_id" property="demandOrgDeptId"/>
        <result column="demand_org_dept_name" property="demandOrgDeptName"/>
        <result column="demand_user_id" property="demandUserId"/>
        <result column="demand_user_name" property="demandUserName"/>
        <result column="perform_org_id" property="performOrgId"/>
        <result column="perform_org_name" property="performOrgName"/>
        <result column="perform_org_dept_id" property="performOrgDeptId"/>
        <result column="perform_org_dept_name" property="performOrgDeptName"/>
        <result column="central_purchase_mode" property="centralPurchaseMode"/>
        <result column="central_start_date" property="centralStartDate"/>
        <result column="master_purchase_mode" property="masterPurchaseMode"/>
        <result column="master_start_date" property="masterStartDate"/>
        <result column="branch_purchase_mode" property="branchPurchaseMode"/>
        <result column="branch_start_date" property="branchStartDate"/>
        <result column="company_name" property="companyName"/>
        <result column="is_XinChang" property="isXinchang"/>
        <result column="remark" property="remark"/>
        <result column="report_time" property="reportTime"/>
        <result column="operation_org_dept" property="operationOrgDept"/>
        <result column="use_of_status" property="useOfStatus"/>
        <result column="use_of_desc" property="useOfDesc"/>
        <result column="manual_apply_id" property="manualApplyId"/>
        <result column="manual_apply_code" property="manualApplyCode"/>
        <result column="manual_apply_name" property="manualApplyName"/>
        <result column="manual_apply_desc" property="manualApplyDesc"/>
        <result column="meeting_time" property="meetingTime"/>
        <result column="meeting_result" property="meetingResult"/>
        <result column="meeting_result_remark" property="meetingResultRemark"/>
        <result column="is_release" property="isRelease"/>
        <result column="release_time" property="releaseTime"/>
        <result column="is_close" property="isClose"/>
        <result column="operation_stage" property="operationStage"/>
        <result column="rebuild_plan_id" property="rebuildPlanId"/>
        <result column="is_turn" property="isTurn"/>
        <result column="is_empower" property="isEmpower"/>
        <!-- 计划申请单信息 -->
        <result column="apply_id" property="applyId"/>
        <result column="apply_code" property="applyCode"/>
        <result column="apply_name" property="applyName"/>
        <!-- 计划通知信息 -->
        <result column="notice_id" property="noticeId"/>
        <result column="notice_code" property="noticeCode"/>
        <result column="notice_name" property="noticeName"/>
        <result column="year" property="year"/>
        <result column="plan_status" property="planStatus"/>
        <!-- 采购计划权限表信息 -->
        <result column="operation_org_id" property="operationOrgId"/>
        <result column="operation_org_name" property="operationOrgName"/>
        <result column="operation_org_dept_id" property="operationOrgDeptId"/>
        <result column="operation_org_dept_name" property="operationOrgDeptName"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, item_id, item_seq, item_code, item_name, item_descr, merge_code, plan_code, item_product_name, item_product_code,
        advice_purchase_mode, advice_start_date, budget_amount, budget_amount_currency, budget_exchange_rate,
        budget_amount_cny, purchasing_years, own_master_org_id, own_master_org_name, demand_org_id, demand_org_name,
        demand_org_dept_id, demand_org_dept_name, demand_user_id, demand_user_name, perform_org_id, perform_org_name,
        perform_org_dept_id, perform_org_dept_name, central_purchase_mode, central_start_date, master_purchase_mode,
        master_start_date, branch_purchase_mode, branch_start_date, company_name, is_XinChang, remark, report_time,
        operation_org_dept, excute_amount, use_of_status, use_of_desc, manual_apply_id, manual_apply_code, manual_apply_name,
        manual_apply_desc, meeting_time, meeting_result, meeting_result_remark, is_release, release_time, is_close,
        operation_stage, rebuild_plan_id
    </sql>

    <sql id="Base_Column_List_pp">
        pp.id, pp.id plan_id, pp.item_id, pp.item_seq, pp.item_code, pp.item_name, pp.item_descr, pp.merge_code,
        pp.plan_code, pp.item_product_name, pp.item_product_code, pp.advice_purchase_mode, pp.advice_start_date,
        pp.budget_amount, pp.budget_amount_currency, pp.budget_exchange_rate, pp.budget_amount_cny, pp.purchasing_years,
        pp.own_master_org_id, pp.own_master_org_name, pp.demand_org_id, pp.demand_org_name, pp.demand_org_dept_id,
        pp.demand_org_dept_name, pp.demand_user_id, pp.demand_user_name, pp.perform_org_id, pp.perform_org_name,
        pp.perform_org_dept_id, pp.perform_org_dept_name, pp.central_purchase_mode, pp.central_start_date,
        pp.master_purchase_mode, pp.master_start_date, pp.branch_purchase_mode, pp.branch_start_date, pp.company_name,
        pp.is_XinChang, pp.remark, pp.report_time, pp.operation_org_dept, pp.use_of_status, pp.use_of_desc,
        pp.manual_apply_id, pp.manual_apply_code, pp.manual_apply_name, pp.manual_apply_desc, pp.meeting_time,
        pp.meeting_result, pp.meeting_result_remark, pp.is_release, pp.release_time, pp.is_close, pp.operation_stage,
        pp.rebuild_plan_id
    </sql>

    <!-- 根据applyId分页查询申请单下面的计划明细信息 -->
    <select id="selectByApplyIdPage" resultMap="planListResultMap">
        select
            <include refid="Base_Column_List_pp"/>,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.notice_id,
            ppa.notice_code,
            ppa.notice_name,
            ppp.plan_status
        from
            `pls_plan` pp,
            `pls_plan_apply` ppa,
            `pls_plan_permission` ppp,
            `pls_plan_apply_relation` ppar
        where
            pp.is_deleted = 0
            and ppp.is_deleted = 0
            and ppa.is_deleted = 0
            and ppar.is_deleted = 0
            and pp.id = ppar.plan_id
            and ppa.id = ppar.apply_id
            and ppa.id = #{planQueryVO.applyId}
            and pp.id = ppp.plan_id
            and ppp.demand_org_dept_id
                in <foreach collection="planQueryVO.childIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            <if test="planQueryVO.searchKey != null and planQueryVO.searchKey !=''">
                and (
                pp.item_product_name like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_product_code like CONCAT('%', #{planQueryVO.searchKey}, '%')
                )
            </if>
            <if test="planQueryVO.reportMainBody != null and planQueryVO.reportMainBody !=''">
                and ppa.apply_org_dept_id = #{planQueryVO.reportMainBody}
            </if>
        <include refid="QUERY_LIST_COLUMN"/>
    </select>

    <!-- 根据planId集合结合PlanQueryVO查询计划明细信息 -->
    <select id="selectByIdsAndQueryVO" resultMap="planListResultMap">
        select
            <include refid="Base_Column_List_pp"/>,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.notice_id,
            ppa.notice_code,
            ppa.notice_name,
            ppp.is_turn,
            ppp.is_empower,
            ppp.plan_status,
            ppp.operation_org_id,
            ppp.operation_org_name,
            ppp.operation_org_dept_id,
            ppp.operation_org_dept_name
        from
            `pls_plan` pp,
            `pls_plan_apply` ppa,
            `pls_plan_permission` ppp,
            `pls_plan_apply_relation` ppar
        where
            pp.is_deleted = 0
            and ppa.is_deleted = 0
            and ppar.is_deleted = 0
            and ppp.is_deleted = 0
            and pp.id = ppar.plan_id
            and ppa.id = ppar.apply_id
            and ppar.is_disabled = '0'
            and pp.id = ppp.plan_id
            and ppp.demand_org_dept_id
                in <foreach collection="planQueryVO.childIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            and ppa.notice_id = #{planQueryVO.planNoticeId}
            <if test="planStatusList!=null and planStatusList.size>0">
                and ppp.plan_status in <foreach collection="planStatusList" open="(" close=")" separator="," item="i">#{i} </foreach>
            </if>
            <if test="notInPlanIdList != null and notInPlanIdList.size>0">
                and pp.id not in <foreach collection="notInPlanIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
            <if test="planQueryVO.searchKey != null and planQueryVO.searchKey !=''">
                and (
                pp.item_product_name like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_product_code like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_code like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_name like CONCAT('%', #{planQueryVO.searchKey}, '%')
                )
            </if>
            <if test="planQueryVO.reportMainBody != null and planQueryVO.reportMainBody !=''">
                and ppa.apply_org_dept_id = #{planQueryVO.reportMainBody}
            </if>
            <!-- 页面全部字段筛选 -->
            <include refid="QUERY_LIST_COLUMN"/>
        group by pp.id
        <if test="planQueryVO.orderByComlus!='' and planQueryVO.orderByComlus!=null and
                    planQueryVO.orderByType!='' and planQueryVO.orderByType!=null ">
            <if test="planQueryVO.orderByComlus=='centralStartDate'">
                <if test="planQueryVO.orgLevel == '1'.toString()">
                    order by pp.central_start_date
                </if>
                <if test="planQueryVO.orgLevel == '2'.toString()">
                    order by pp.master_start_date
                </if>
                <if test="planQueryVO.orgLevel == '3'.toString()">
                    order by pp.branch_start_date
                </if>
            </if>
            <if test="planQueryVO.orderByComlus=='adviceStartDate'">
                order by pp.advice_start_date
            </if>
            <if test="planQueryVO.orderByComlus=='itemCode'">
                order by pp.item_code
            </if>
            <if test="planQueryVO.orderByType=='desc'">
                desc
            </if>
            <if test="planQueryVO.orderByComlus=='itemCode' || planQueryVO.orderByComlus='centralStartDate'">
                 ,pp.merge_code
            </if>
            <if test="planQueryVO.orderByComlus=='itemCode'">
                ,pp.id desc
            </if>
        </if>
        <if test="planQueryVO.orderByComlus=='' or planQueryVO.orderByComlus==null or
                    planQueryVO.orderByType=='' or planQueryVO.orderByType==null ">
            order by pp.id desc
        </if>
    </select>

    <!-- 根据当前部门id结合PlanQueryVO查询可查看范围的已发布计划明细信息 -->
    <select id="selectEffectPlanByDeptIdAndQueryVO" resultMap="planListResultMap">
        select
            <include refid="Base_Column_List_pp"/>,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.notice_id,
            ppa.notice_code,
            ppa.notice_name,
            ppp.is_turn,
            ppp.is_empower,
            ppp.plan_status
        from
            `pls_plan` pp,
            `pls_plan_apply` ppa,
            `pls_plan_permission` ppp,
            `pls_plan_apply_relation` ppar
        where
            pp.is_deleted = 0
            and ppa.is_deleted = 0
            and ppp.is_deleted = 0
            and ppar.is_deleted = 0
            and pp.is_close = '0'
            and ppar.is_disabled = '0'
            and pp.is_release = '1'
            and pp.id = ppar.plan_id
            and pp.id = ppp.plan_id
            and ppa.apply_type != '3'
            and ppa.id = ppar.apply_id
            and ppa.notice_id = #{planQueryVO.planNoticeId}
            <!-- 已发布状态 -->
            and ppp.plan_status = '40'
            and ppp.demand_org_dept_id
                in <foreach collection="departmentIds" open="(" close=")" separator="," item="i"> #{i} </foreach>
            <if test="planQueryVO.searchKey != null and planQueryVO.searchKey !=''">
                and (
                pp.item_product_name like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_product_code like CONCAT('%', #{planQueryVO.searchKey}, '%')
                )
            </if>
            <if test="planQueryVO.reportMainBody != null and planQueryVO.reportMainBody !=''">
                and ppa.apply_org_dept_id = #{planQueryVO.reportMainBody}
            </if>
        <!-- 页面全部字段筛选 -->
            <include refid="QUERY_LIST_COLUMN"/>
        group by pp.id
        order by pp.id desc
    </select>

    <!-- 根据当前部门id结合PlanQueryVO查询可查看范围的已发布计划明细信息 -->
    <select id="selectTrackPlanListByNoticeId" resultMap="planListResultMap">
        select
            <include refid="Base_Column_List_pp"/>,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.notice_id,
            ppa.notice_code,
            ppa.notice_name,
            ppp.is_turn,
            ppp.is_empower,
            ppp.plan_status
        from
            `pls_plan` pp,
            `pls_plan_apply` ppa,
            `pls_plan_permission` ppp,
            `pls_plan_apply_relation` ppar
        where
            pp.is_deleted = 0
            and ppa.is_deleted = 0
            and ppp.is_deleted = 0
            and ppar.is_deleted = 0
            and ppar.is_disabled = '0'
            and pp.id = ppar.plan_id
            and pp.id = ppp.plan_id
            and ppa.apply_type != '3'
            and ppa.id = ppar.apply_id
            and ppa.notice_id = #{planQueryVO.planNoticeId}
            and ppp.demand_org_dept_id
                in <foreach collection="departmentIds" open="(" close=")" separator="," item="i"> #{i} </foreach>
            <if test="planQueryVO.searchKey != null and planQueryVO.searchKey !=''">
                and (
                pp.item_product_name like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_product_code like CONCAT('%', #{planQueryVO.searchKey}, '%')
                )
            </if>
            <if test="planQueryVO.reportMainBody != null and planQueryVO.reportMainBody !=''">
                and ppa.apply_org_dept_id = #{planQueryVO.reportMainBody}
            </if>
            <!-- 页面全部字段筛选 -->
            <include refid="QUERY_LIST_COLUMN"/>
        group by pp.id
        order by pp.id desc
    </select>

    <!-- 根据部门id查询转交或者授权到当前部门的计划 -->
    <select id="selectAuthorAndTurnPlanByDeptId" resultMap="planListResultMap">
        select
            <include refid="Base_Column_List_pp"/>,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.notice_id,
            ppa.notice_code,
            ppa.notice_name,
            ppp.is_turn,
            ppp.is_empower,
            ppp.plan_status
        from
            `pls_plan` pp,
            `pls_plan_apply` ppa,
            `pls_plan_permission` ppp,
            `pls_plan_apply_relation` ppar
        where
            pp.is_deleted = 0
            and ppa.is_deleted = 0
            and ppp.is_deleted = 0
            and ppar.is_deleted = 0
            and ppar.is_disabled = '0'
            and pp.id = ppar.plan_id
            and pp.id = ppp.plan_id
            and ppa.apply_type = '1'
            and ppa.id = ppar.apply_id
            and (ppp.is_turn = '2' or ppp.is_empower = '2')
            and ppp.demand_org_dept_id
                in <foreach collection="departmentIds" open="(" close=")" separator="," item="i"> #{i} </foreach>
            <if test="planQueryVO.searchKey != null and planQueryVO.searchKey !=''">
                and (
                pp.item_product_name like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_product_code like CONCAT('%', #{planQueryVO.searchKey}, '%')
                )
            </if>
            <if test="planQueryVO.reportMainBody != null and planQueryVO.reportMainBody !=''">
                and ppa.apply_org_dept_id = #{planQueryVO.reportMainBody}
            </if>
            <!-- 页面全部字段筛选 -->
            <include refid="QUERY_LIST_COLUMN"/>
        group by pp.id
        order by pp.id desc
    </select>

    <sql id="QUERY_LIST_COLUMN">
        <!-- 页面全部字段筛选 -->
        <!-- 页面全部字段筛选 -->
        <!-- 页面全部字段筛选 -->
        <if test="planQueryVO.planCode!=null and planQueryVO.planCode!=''">
            and pp.plan_code like CONCAT('%',  #{planQueryVO.planCode}, '%')
        </if>
        <if test="planQueryVO.mergeCode!=null and planQueryVO.mergeCode!=''">
            and pp.merge_code like CONCAT('%',  #{planQueryVO.mergeCode}, '%')
        </if>
        <if test="planQueryVO.budgetAmountMin!=null and planQueryVO.budgetAmountMin!=''
                    and planQueryVO.budgetAmountMax!=null and planQueryVO.budgetAmountMax!=''">
            and (pp.budget_amount BETWEEN #{planQueryVO.budgetAmountMin} AND #{planQueryVO.budgetAmountMax})
        </if>
        <if test="planQueryVO.budgetAmountCurrencyList!=null and planQueryVO.budgetAmountCurrencyList.size>0">
            and pp.budget_amount_currency in <foreach collection="planQueryVO.budgetAmountCurrencyList" open="(" close=")" separator="," item="i"> #{i} </foreach>
        </if>
        <if test="planQueryVO.budgetExchangeRateMin!=null and planQueryVO.budgetExchangeRateMin!=''
                    and planQueryVO.budgetExchangeRateMax!=null and planQueryVO.budgetExchangeRateMax!=''">
            and (pp.budget_exchange_rate BETWEEN #{planQueryVO.budgetExchangeRateMin} AND #{planQueryVO.budgetExchangeRateMax})
        </if>
        <if test="planQueryVO.purchasingYearsMin!=null and planQueryVO.purchasingYearsMin!=''
                    and planQueryVO.purchasingYearsMax!=null and planQueryVO.purchasingYearsMax!=''">
            and (pp.purchasing_years BETWEEN #{planQueryVO.purchasingYearsMin} AND #{planQueryVO.purchasingYearsMax})
        </if>
        <if test="planQueryVO.budgetAmountCnyMin!=null and planQueryVO.budgetAmountCnyMin!=''
                    and planQueryVO.budgetAmountCnyMax!=null and planQueryVO.budgetAmountCnyMax!=''">
            and (pp.budget_amount_cny BETWEEN #{planQueryVO.budgetAmountCnyMin} AND #{planQueryVO.budgetAmountCnyMax})
        </if>
        <if test="planQueryVO.itemCode!=null and planQueryVO.itemCode!=''">
            and pp.item_code like CONCAT('%',  #{planQueryVO.itemCode}, '%')
        </if>
        <if test="planQueryVO.itemName!=null and planQueryVO.itemName!=''">
            and pp.item_name like CONCAT('%',  #{planQueryVO.itemName}, '%')
        </if>
        <if test="planQueryVO.itemProductName!=null and planQueryVO.itemProductName!=''">
            and pp.item_product_name like CONCAT('%',  #{planQueryVO.itemProductName}, '%')
        </if>
        <if test="planQueryVO.performOrgDeptName!=null and planQueryVO.performOrgDeptName!=''">
            and pp.perform_org_dept_name like CONCAT('%',  #{planQueryVO.performOrgDeptName}, '%')
        </if>
        <if test="planQueryVO.performOrgName!=null and planQueryVO.performOrgName!=''">
            and pp.perform_org_name like CONCAT('%',  #{planQueryVO.performOrgName}, '%')
        </if>
        <if test="planQueryVO.useOfDesc!=null and planQueryVO.useOfDesc!=''">
            and pp.use_of_desc like CONCAT('%',  #{planQueryVO.useOfDesc}, '%')
        </if>
        <if test="planQueryVO.useOfStatusList!=null and planQueryVO.useOfStatusList.size>0">
            and pp.use_of_status in <foreach collection="planQueryVO.useOfStatusList" open="(" close=")" separator="," item="i">#{i} </foreach>
        </if>
        <if test="planQueryVO.planIdList!=null and planQueryVO.planIdList.size>0">
            and ppp.plan_id in <foreach collection="planQueryVO.planIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
        </if>
        <if test="planQueryVO.planStatusList!=null and planQueryVO.planStatusList.size>0">
            and ppp.plan_status  in <foreach collection="planQueryVO.planStatusList" open="(" close=")" separator="," item="i">#{i} </foreach>
        </if>
        <if test="planQueryVO.manualApplyName != null and planQueryVO.manualApplyName !=''">
            and pp.manual_apply_name like CONCAT('%', #{planQueryVO.manualApplyName}, '%')
        </if>
        <if test="planQueryVO.manualApplyDesc != null and planQueryVO.manualApplyDesc !=''">
            and pp.manual_apply_desc like CONCAT('%', #{planQueryVO.manualApplyDesc}, '%')
        </if>
        <if test="planQueryVO.manualApplyCode != null and planQueryVO.manualApplyCode !=''">
            and pp.manual_apply_code like CONCAT('%', #{planQueryVO.manualApplyCode}, '%')
        </if>
        <if test="planQueryVO.advicePurchaseModeList!=null and planQueryVO.advicePurchaseModeList.size>0">
            and pp.advice_purchase_mode in <foreach collection="planQueryVO.advicePurchaseModeList" open="(" close=")" separator="," item="i"> #{i} </foreach>
        </if>
        <if test="planQueryVO.adviceStartDateStart!=null and planQueryVO.adviceStartDateStart!=''
                    and planQueryVO.adviceStartDateEnd!=null and planQueryVO.adviceStartDateEnd!=''">
            and (DATE_FORMAT(pp.advice_start_date, '%Y-%m-%d') BETWEEN #{planQueryVO.adviceStartDateStart} AND #{planQueryVO.adviceStartDateEnd})
        </if>
        <if test="planQueryVO.demandOrgName!=null and planQueryVO.demandOrgName!=''">
            and pp.demand_org_name like CONCAT('%',  #{planQueryVO.demandOrgName}, '%')
        </if>
        <if test="planQueryVO.demandOrgDeptName!=null and planQueryVO.demandOrgDeptName!=''">
            and pp.demand_org_dept_name like CONCAT('%',  #{planQueryVO.demandOrgDeptName}, '%')
        </if>
        <if test="planQueryVO.demandUserName!=null and planQueryVO.demandUserName!=''">
            and pp.demand_user_name like CONCAT('%',  #{planQueryVO.demandUserName}, '%')
        </if>
        <if test="planQueryVO.noticeCode!=null and planQueryVO.noticeCode!=''">
            and ppa.notice_code like CONCAT('%',  #{planQueryVO.noticeCode}, '%')
        </if>
        <if test="planQueryVO.noticeName!=null and planQueryVO.noticeName!=''">
            and ppa.notice_name like CONCAT('%',  #{planQueryVO.noticeName}, '%')
        </if>
        <if test="planQueryVO.applyCode!=null and planQueryVO.applyCode!=''">
            and ppa.apply_code like CONCAT('%',  #{planQueryVO.applyCode}, '%')
        </if>
        <if test="planQueryVO.applyName!=null and planQueryVO.applyName!=''">
            and ppa.apply_name like CONCAT('%',  #{planQueryVO.applyName}, '%')
        </if>
        <if test="planQueryVO.departmentPurchaseModeList!=null and planQueryVO.departmentPurchaseModeList.size>0">
            <if test="planQueryVO.orgLevel == '1'.toString()">
                and pp.central_purchase_mode
            </if>
            <if test="planQueryVO.orgLevel == '2'.toString()">
                and pp.master_purchase_mode
            </if>
            <if test="planQueryVO.orgLevel == '3'.toString()">
                and pp.branch_purchase_mode
            </if>
            in <foreach collection="planQueryVO.departmentPurchaseModeList" open="(" close=")" separator="," item="i"> #{i} </foreach>
        </if>
        <if test="planQueryVO.departmentStartDateStart!=null and planQueryVO.departmentStartDateStart!=''
                    and planQueryVO.departmentStartDateEnd!=null and planQueryVO.departmentStartDateEnd!=''">
            <if test="planQueryVO.orgLevel == '1'.toString()">
                and (DATE_FORMAT(pp.central_start_date, '%Y-%m-%d') BETWEEN #{planQueryVO.departmentStartDateStart} AND #{planQueryVO.departmentStartDateEnd})
            </if>
            <if test="planQueryVO.orgLevel == '2'.toString()">
                and (DATE_FORMAT(pp.master_start_date, '%Y-%m-%d') BETWEEN #{planQueryVO.departmentStartDateStart} AND #{planQueryVO.departmentStartDateEnd})
            </if>
            <if test="planQueryVO.orgLevel == '3'.toString()">
                and (DATE_FORMAT(pp.branch_start_date, '%Y-%m-%d') BETWEEN #{planQueryVO.departmentStartDateStart} AND #{planQueryVO.departmentStartDateEnd})
            </if>
        </if>
        <if test="planQueryVO.operationOrgDept!=null and planQueryVO.operationOrgDept!=''">
            and pp.operation_org_dept like CONCAT('%',  #{planQueryVO.operationOrgDept}, '%')
        </if>
        <if test="planQueryVO.releaseTimeStart!=null and planQueryVO.releaseTimeStart!=''
                    and planQueryVO.releaseTimeEnd!=null and planQueryVO.releaseTimeEnd!=''">
            and (DATE_FORMAT(pp.release_time, '%Y-%m-%d') BETWEEN #{planQueryVO.releaseTimeStart} AND #{planQueryVO.releaseTimeEnd})
        </if>
        <!-- 页面全部字段筛选 -->
        <!-- 页面全部字段筛选 -->
        <!-- 页面全部字段筛选 -->
    </sql>

    <!-- 根据当前部门id查询需求部门创建的所有计划明细信息 -->
    <select id="queryDemandPlanList" resultMap="planListResultMap">
        select
            <include refid="Base_Column_List_pp"/>,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.notice_id,
            ppa.notice_code,
            ppa.notice_name,
            ppn.year,
            ppp.plan_status
        from
            `pls_plan` pp,
            `pls_plan_permission` ppp,
            `pls_plan_notice` ppn,
            `pls_plan_apply` ppa,
            `pls_plan_apply_relation` ppar
        where
            pp.is_deleted = 0
            and ppp.is_deleted = 0
            and ppn.is_deleted = 0
            and ppa.is_deleted = 0
            and ppar.is_deleted = 0
            and pp.id = ppp.plan_id
            and pp.id = ppar.plan_id
            and ppn.id = ppa.notice_id
            <if test="planQueryVO.planNoticeId!=null">
                and ppn.id = #{planQueryVO.planNoticeId}
            </if>
            and ppa.id = ppar.apply_id
            and ppa.apply_type = '1'
            and ppar.is_disabled = '0'
            and ppp.is_edit ='1'
            and pp.create_user_id = #{userId}
            <!-- and pp.demand_org_dept_id = #{deptId} -->
            <if test="planQueryVO.searchKey != null and planQueryVO.searchKey !=''">
                and (
                pp.item_product_name like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_code like CONCAT('%', #{planQueryVO.searchKey}, '%') or
                pp.item_name like CONCAT('%', #{planQueryVO.searchKey}, '%')
                )
            </if>
            <if test="planQueryVO.reportMainBody != null and planQueryVO.reportMainBody !=''">
                and ppa.apply_org_dept_id = #{planQueryVO.reportMainBody}
            </if>
            <if test="planQueryVO.year != null and planQueryVO.year !=''">
                and ppn.year = #{planQueryVO.year}
            </if>
            <if test="planStatusList != null and planStatusList.size>0">
                and ppp.plan_status in <foreach collection="planStatusList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
            <if test="useOfStatusList != null and useOfStatusList.size>0">
                and pp.use_of_status in <foreach collection="useOfStatusList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
            <if test="planQueryVO.planIdList!=null and planQueryVO.planIdList.size>0">
            and pp.id in <foreach collection="planQueryVO.planIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
        order by pp.id desc
    </select>

    <!-- 通过采购明细id批量修改采购计划明细表数据 -->
    <update id="updatePlanInfoByPlanIdList">
        update `pls_plan`
        set `operation_stage` = `operation_stage`+1,
            `report_time` = #{reportTime}
        where
            `is_deleted` = '0'
            and `id` in <foreach collection="planIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
    </update>




    <select id="queryPlanByApplyInfo" resultMap="planResultMap">
        select
            <include refid="Base_Column_List"/>,
            ifNull(usable_amount, budget_amount) usable_amount
        from
            pls_plan
        where
            is_deleted = '0'
            and is_release = '1'
            and is_close = '0'
            and use_of_status != '20'
            <if test="searchKey != null and searchKey !=''">
                and (
                    plan_code like CONCAT('%', #{searchKey}, '%') or
                    demand_user_name like CONCAT('%', #{searchKey}, '%') or
                    item_product_name like CONCAT('%', #{searchKey}, '%') or
                    item_product_code like CONCAT('%', #{searchKey}, '%')
                )
            </if>

            <if test="demandDeptId != null and demandDeptId !=''">
                and (demand_org_dept_id = #{demandDeptId} OR demand_user_id = #{demandUserId})
            </if>
            <if test="performOrgId != null and performOrgId !=''">
                and (perform_org_id = #{performOrgId} OR demand_user_id = #{demandUserId})
            </if>

            <if test="checkedIdList != null and checkedIdList.size>0">
                and id not in <foreach collection="checkedIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
        order by update_time desc
    </select>

    <select id="queryPlanByIdAndPlanStatus" resultMap="planResultMap">
        select
            <include refid="Base_Column_List_pp"/>
        from
            pls_plan pp,
            pls_plan_permission ppp
        where
            pp.is_deleted = '0'
            and ppp.is_deleted = '0'
            and ppp.plan_id = pp.id
            and ppp.plan_status = #{planStatus}
            and ppp.demand_org_dept_id = #{demandDeptId}
            and pp.create_user_id = #{createUserId}
            <if test="notInPlanIdList != null and notInPlanIdList.size > 0">
                and pp.id not in <foreach collection="notInPlanIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
    </select>

    <select id="queryPlanDate" resultMap="planResultMap">
      SELECT
      p.id,p.budget_amount,p.use_of_status,
       (CASE WHEN p.central_purchase_mode IS NOT NULL AND p.central_purchase_mode !=""  THEN p.central_purchase_mode
       WHEN p.master_purchase_mode  IS NOT NULL AND p.master_purchase_mode !="" THEN p.master_purchase_mode
       WHEN p.branch_purchase_mode IS NOT NULL  AND  p.branch_purchase_mode !="" THEN p.branch_purchase_mode
       ELSE p.advice_purchase_mode END) AS advice_purchase_mode
      FROM
         pls_plan_notice pn JOIN pls_plan_apply pa ON pn.id=pa.notice_id
      JOIN pls_plan_apply_relation par ON  pa.id=par.apply_id
      JOIN  pls_plan p ON par.plan_id=p.id
      WHERE
      pn.is_deleted = '0'
      AND pa.is_deleted='0'
      AND par.is_deleted='0'
      AND p.is_deleted='0'
      AND pa.apply_type='1'
      AND p.is_release='1'
      AND pn.year= #{year}
      AND p.demand_org_id in
        <foreach collection="childCompanyIdList" item="mode" index="index" open="(" separator=","
                 close=")">
            #{mode}
        </foreach>
    </select>


    <select id="selectMeetingNotGoPlanId" resultType="Long">
        SELECT
            ppp.plan_id
        FROM
            pls_plan_apply ppa,
            pls_plan_permission ppp,
            pls_plan_apply_relation ppar
        WHERE
            ppa.is_deleted = '0'
            and ppp.is_deleted = '0'
            and ppar.is_deleted = '0'
            and ppp.plan_id = ppar.plan_id
            and ppa.id = ppar.apply_id
            and ppa.apply_org_dept_id = #{departmentId}
            and ppa.apply_type = '3'
            and ppp.plan_status = '34'
    </select>

    <select id="selectIssueInfoByPlanId" resultType="Map">
        SELECT
            ppar.plan_id planId,
            ppar.apply_id applyId
        FROM
            pls_plan_apply ppa,
            pls_plan_apply_relation ppar
        WHERE
            ppa.is_deleted = '0'
            and ppar.is_deleted = '0'
            and ppa.id = ppar.apply_id
            and ppa.apply_type = '3'
            <if test="planIdList != null and planIdList.size > 0">
                and ppar.plan_id in <foreach collection="planIdList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
    </select>

    <select id="queryMaxPlanSerialNoByNoticeId" resultType="String">
        SELECT MAX(t.merge_code)
        FROM (SELECT MAX(pp.merge_code) merge_code
              FROM pls_plan pp,
                   pls_plan_apply ppa,
                   pls_plan_notice ppn,
                   pls_plan_apply_relation ppar
              WHERE pp.is_deleted = '0'
                AND ppa.is_deleted = '0'
                AND ppn.is_deleted = '0'
                AND ppar.is_deleted = '0'
                AND pp.id = ppar.plan_id
                AND ppa.id = ppar.apply_id
                AND ppa.notice_id = ppn.id
                AND pp.is_release = '1'
                AND ppa.apply_type != '3'
                AND pp.merge_code like '%-%'
                AND ppn.id = #{planNoticeId}
              UNION ALL
              SELECT
                  MAX(pp.merge_code) merge_code
              FROM
                  pls_plan pp,
                  pls_plan_apply ppa,
                  pls_plan_notice ppn,
                  pls_plan_apply_relation ppar
              WHERE
                  pp.is_deleted = '0'
                AND ppa.is_deleted = '0'
                AND ppn.is_deleted = '0'
                AND ppar.is_deleted = '0'
                AND pp.id = ppar.plan_id
                AND ppa.id = ppar.apply_id
                AND ppa.notice_id = ppn.id
                AND ppa.apply_type = '3'
                AND pp.merge_code like '%-%'
                AND ppn.id = #{planNoticeId}
            ) t
    </select>
</mapper>

planApply

<?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.cntaiping.pls.plan.apply.mapper.PlanApplyMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="planApplyResultMap" type="com.cntaiping.pls.plan.apply.entity.PlanApply">
        <result column="id" property="id"/>
        <result column="notice_id" property="noticeId"/>
        <result column="notice_code" property="noticeCode"/>
        <result column="notice_name" property="noticeName"/>
        <result column="notice_item_id" property="noticeItemId"/>
        <result column="report_main_body" property="reportMainBody"/>
        <result column="apply_type" property="applyType"/>
        <result column="issue_id" property="issueId"/>
        <result column="apply_code" property="applyCode"/>
        <result column="apply_name" property="applyName"/>
        <result column="apply_status" property="applyStatus"/>
        <result column="apply_org_id" property="applyOrgId"/>
        <result column="apply_org_name" property="applyOrgName"/>
        <result column="apply_org_dept_id" property="applyOrgDeptId"/>
        <result column="apply_org_dept_name" property="applyOrgDeptName"/>
        <result column="take_org_id" property="takeOrgId"/>
        <result column="take_org_name" property="takeOrgName"/>
        <result column="take_org_dept_id" property="takeOrgDeptId"/>
        <result column="take_org_dept_name" property="takeOrgDeptName"/>
        <result column="approve_time" property="approveTime"/>
        <result column="plan_num" property="planNum"/>
        <result column="create_time" property="createTime"/>
        <result column="create_user_id" property="createUserId"/>
        <result column="update_time" property="updateTime"/>
        <result column="update_user_id" property="updateUserId"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <!-- 采购计划申请单列表查询结果 -->
    <resultMap id="planApplyListResultMap" type="com.cntaiping.pls.plan.apply.vo.PlanApplyListVO">
        <!-- 采购计划通知信息 -->
        <result column="notice_item_id" property="noticeItemId"/>
        <result column="notice_id" property="noticeId"/>
        <result column="notice_code" property="noticeCode"/>
        <result column="notice_name" property="noticeName"/>
        <result column="plan_type" property="planType"/>
        <result column="year" property="year"/>
        <result column="report_end_time" property="reportEndTime"/>
        <result column="release_org_name" property="releaseOrgName"/>
        <!-- 采购计划申请单信息 -->
        <result column="issue_id" property="issueId"/>
        <result column="apply_id" property="applyId"/>
        <result column="apply_code" property="applyCode"/>
        <result column="apply_name" property="applyName"/>
        <result column="report_main_body" property="reportMainBody"/>
        <result column="apply_type" property="applyType"/>
        <result column="apply_status" property="applyStatus"/>
        <result column="approve_time" property="approveTime"/>
        <result column="apply_org_dept_id" property="applyOrgDeptId"/>
        <!-- 申请单提报计划明细数量 -->
        <result column="plan_num" property="planNum"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, notice_id, notice_code, notice_name, notice_item_id, report_main_body, apply_type, issue_id, apply_code,
        apply_name, apply_status, apply_org_id, apply_org_name, apply_org_dept_id, apply_org_dept_name, take_org_id,
        take_org_name,take_org_dept_id, take_org_dept_name, approve_time, plan_num
    </sql>

    <sql id="Base_Column_List_ppa">
        ppa.id, ppa.notice_id, ppa.notice_code, ppa.notice_name, ppa.notice_item_id, ppa.report_main_body, ppa.apply_type,
        ppa.issue_id, ppa.apply_code, ppa.apply_name, ppa.apply_status, ppa.apply_org_id, ppa.apply_org_name,
        ppa.apply_org_dept_id, ppa.apply_org_dept_name, ppa.take_org_id, ppa.take_org_name,ppa.take_org_dept_id,
        ppa.take_org_dept_name, ppa.approve_time, ppa.plan_num
    </sql>

    <!-- 根据当前部门查询采购计划申请单、采购通知等信息 -->
    <select id="selectPlanApplyByNoticeIdPage" resultMap="planApplyListResultMap">
        select
            ppni.id notice_item_id,
            ppni.notice_id,
            ppni.notice_code,
            ppni.notice_name,
            ppni.plan_type,
            ppni.year,
            ppni.report_end_time,
            ppni.release_org_name,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.report_main_body,
            ppa.apply_type,
            ppa.apply_status,
            ppa.approve_time,
            ppa.plan_num,
            ppa.apply_org_dept_id
        from
            pls_plan_apply ppa,
            pls_plan_notice_item ppni
        where
            ppa.is_deleted = 0
            and ppni.is_deleted = 0
            and ppa.notice_id = ppni.notice_id
            <if test="orgId!=null">
                and ppa.apply_org_id = #{orgId}
            </if>
            <if test="orgId==null">
                and ppa.apply_org_dept_id
                    in <foreach collection="departmentIds" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
            and ppa.apply_type = #{planApplyQueryVO.applyType}
            and ppni.id = ppa.notice_item_id
            <if test="planApplyQueryVO.reportMainBody!=null">
                and ppa.apply_org_id = #{planApplyQueryVO.reportMainBody}
            </if>
            <if test="planApplyQueryVO.planNoticeId!=null">
                and ppa.notice_id = #{planApplyQueryVO.planNoticeId}
            </if>
            <if test="planApplyQueryVO.searchKey!=null and planApplyQueryVO.searchKey!=''">
                and (ppa.apply_code like concat('%', #{planApplyQueryVO.searchKey}, '%')
                    or ppni.notice_code like concat('%', #{planApplyQueryVO.searchKey}, '%')
                    or ppa.apply_name like concat('%', #{planApplyQueryVO.searchKey}, '%')
                    or ppni.notice_name like concat('%', #{planApplyQueryVO.searchKey}, '%'))
            </if>
            <if test="planApplyQueryVO.year!=null and planApplyQueryVO.year!=''">
                and ppni.year = #{planApplyQueryVO.year}
            </if>
            <if test="planApplyQueryVO.applyStatusList!=null and planApplyQueryVO.applyStatusList.size>0">
                and ppa.apply_status in <foreach collection="planApplyQueryVO.applyStatusList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
        order by ppa.id desc
    </select>

    <!-- 根据当前部门查询采购计划申请单、采购通知等信息 -->
    <select id="selectMeetingPlanApply" resultMap="planApplyListResultMap">
        select
            NULL notice_item_id,
            ppn.id notice_id,
            ppn.notice_code,
            ppn.notice_name,
            ppn.plan_type,
            ppn.year,
            ppn.report_end_time,
            ppn.release_org_name,
            ppa.issue_id,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.report_main_body,
            ppa.apply_type,
            ppa.apply_status,
            ppa.approve_time,
            ppa.plan_num,
            ppa.apply_org_dept_id
        from
            pls_plan_notice ppn,
            pls_plan_apply ppa
        where
            ppa.is_deleted = 0
            and ppn.is_deleted = 0
            and ppa.notice_id = ppn.id
            and ppa.apply_type = '3'
            and ppa.apply_org_dept_id
                in <foreach collection="departmentIds" open="(" close=")" separator="," item="i"> #{i} </foreach>
            and ppn.id = #{planApplyQueryVO.planNoticeId}
            <if test="planApplyQueryVO.reportMainBody!=null">
                and ppa.apply_org_id = #{planApplyQueryVO.reportMainBody}
            </if>
            <if test="planApplyQueryVO.searchKey!=null and planApplyQueryVO.searchKey!=''">
                and (ppa.apply_code like concat('%', #{planApplyQueryVO.searchKey}, '%')
                or ppn.notice_code like concat('%', #{planApplyQueryVO.searchKey}, '%')
                or ppa.apply_name like concat('%', #{planApplyQueryVO.searchKey}, '%')
                or ppn.notice_name like concat('%', #{planApplyQueryVO.searchKey}, '%'))
            </if>
            <if test="planApplyQueryVO.year!=null and planApplyQueryVO.year!=''">
                and ppn.year = #{planApplyQueryVO.year}
            </if>
            <if test="planApplyQueryVO.applyStatusList!=null and planApplyQueryVO.applyStatusList.size>0">
                and ppa.apply_status in <foreach collection="planApplyQueryVO.applyStatusList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
        order by ppa.id desc
    </select>

    <!-- 已提报采购计划申请单查询列表分页查询（集采部门采购计划申请单查询页面） -->
    <!-- 查询提报到当前部门的采购计划申请单、采购通知等信息 -->
    <select id="selectReportPlanApplyByNoticeIdPage" resultMap="planApplyListResultMap">
        select
            ppn.id notice_id,
            ppn.notice_code,
            ppn.notice_name,
            ppn.plan_type,
            ppn.year,
            ppn.report_end_time,
            ppn.release_org_name,
            ppa.id apply_id,
            ppa.apply_code,
            ppa.apply_name,
            ppa.report_main_body,
            ppa.apply_type,
            ppa.apply_status,
            ppa.approve_time,
            ppa.plan_num,
            ppa.apply_org_dept_id
        from
            pls_plan_apply ppa,
            pls_plan_notice ppn
        where
            ppa.is_deleted = 0
            and ppn.is_deleted = 0
            and ppa.take_org_dept_id
                in <foreach collection="departmentIds" open="(" close=")" separator="," item="i"> #{i} </foreach>
            and ppn.id = #{queryVO.planNoticeId}
            and ppa.notice_id = ppn.id
            and (ppa.apply_type = "1" or ppa.apply_type = "2")
            and ppa.apply_status = "30"
            <if test="queryVO.reportMainBody!=null">
                and ppa.apply_org_id = #{queryVO.reportMainBody}
            </if>
            <if test="queryVO.searchKey!=null and queryVO.searchKey!=''">
                and (ppa.apply_code like concat('%', #{queryVO.searchKey}, '%')
                    or ppn.notice_code like concat('%', #{queryVO.searchKey}, '%')
                    or ppa.apply_name like concat('%', #{queryVO.searchKey}, '%')
                    or ppn.notice_name like concat('%', #{queryVO.searchKey}, '%'))
            </if>
            <if test="queryVO.year!=null and queryVO.year!=''">
                and ppn.year = #{queryVO.year}
            </if>
            <if test="queryVO.applyStatusList!=null and queryVO.applyStatusList.size>0">
                and ppa.apply_status in <foreach collection="queryVO.applyStatusList" open="(" close=")" separator="," item="i"> #{i} </foreach>
            </if>
        order by ppa.id desc
    </select>

    <!-- 通过计划明细Id和计划申请单类型查询生效的采购计划申请单-->
    <select id="selectApplyByPlanIdAndApplyType" resultMap="planApplyListResultMap">
        select
            <include refid="Base_Column_List_ppa"/>
        from
            pls_plan_apply ppa,
            pls_plan_apply_relation ppar
        where
            ppa.is_deleted = 0
            and ppar.is_deleted = 0
            and ppa.id = ppar.apply_id
            and ppar.plan_id = #{planId}
            and ppa.apply_type = #{applyType}
    </select>

    <!-- 通过计划明细Id和计划申请单类型查询生效的采购计划申请单-->
    <select id="selectExistApplyByPlanIdsAndPlanType" resultType="java.lang.Integer">
        select
            count(ppa.id)
        from
            pls_plan_apply ppa,
            pls_plan_apply_relation ppar
        where
            ppa.is_deleted = 0
            and ppar.is_deleted = 0
            and ppa.id = ppar.apply_id
            and ppa.apply_status in ('10','20','60')
            and ppar.plan_id = #{planId}
            and ppa.apply_type = #{applyType}
            and ppa.id != #{planId}
    </select>

    <delete id="removePlan">
        delete from pls_plan where import_status='1'
    </delete>
    <delete id="removePlanApply">
        delete from pls_plan_apply where import_status='1'
    </delete>
    <delete id="removePlanApplyRelation">
        delete from pls_plan_apply_relation where import_status='1'
    </delete>
    <delete id="removePlanPermission">
        delete from pls_plan_permission where import_status='1'
    </delete>

</mapper>