<?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.zfire.mall.manager.commonmapper.engin.info.EnginInfoOrderReserveMapper">

    <resultMap id="listResultMap" type="com.zfire.mall.manager.bean.engin.info.EnginInfoOrderBean">
        <result column="engin_info_id" jdbcType="VARCHAR" property="enginInfoId" />
        <result column="admin_company_id" jdbcType="VARCHAR" property="adminCompanyId" />
        <result column="customer_id" jdbcType="VARCHAR" property="customerId" />
        <result column="customer_number" jdbcType="VARCHAR" property="customerNumber" />
        <result column="customer_name" jdbcType="VARCHAR" property="customerName" />
        <result column="engin_info_no" jdbcType="VARCHAR" property="enginInfoNo" />
        <result column="order_date" jdbcType="TIMESTAMP" property="orderDate" />
        <result column="main_id" jdbcType="VARCHAR" property="mainId" />
        <result column="main_name" jdbcType="VARCHAR" property="mainName" />
        <result column="project_name" jdbcType="VARCHAR" property="projectName" />
        <result column="project_type" jdbcType="VARCHAR" property="projectType" />
        <result column="project_note" jdbcType="VARCHAR" property="projectNote" />
        <result column="machine_type" jdbcType="VARCHAR" property="machineType" />
        <result column="use_unit" jdbcType="VARCHAR" property="useUnit" />
        <result column="install_address" jdbcType="VARCHAR" property="installAddress" />
        <result column="project_no" jdbcType="VARCHAR" property="projectNo" />
        <result column="engin_factory_no" jdbcType="VARCHAR" property="enginFactoryNo" />
        <result column="engin_sign_type" jdbcType="VARCHAR" property="enginSignType" />
        <result column="trade_category" jdbcType="VARCHAR" property="tradeCategory" />
        <result column="linkman" jdbcType="VARCHAR" property="linkman" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="tel" jdbcType="VARCHAR" property="tel" />
        <result column="declare_no" jdbcType="VARCHAR" property="declareNo" />
        <result column="power_category" jdbcType="VARCHAR" property="powerCategory" />
        <result column="service_id" jdbcType="VARCHAR" property="serviceId" />
        <result column="service_name" jdbcType="VARCHAR" property="serviceName" />
        <result column="contract_expire_date" jdbcType="TIMESTAMP" property="contractExpireDate" />
        <result column="ge_li_iner_note" jdbcType="VARCHAR" property="geLiInerNote" />
        <result column="ge_li_note" jdbcType="VARCHAR" property="geLiNote" />
        <result column="file_no" jdbcType="VARCHAR" property="fileNo" />
        <result column="remark" jdbcType="VARCHAR" property="remark" />
        <result column="examine_status" jdbcType="VARCHAR" property="examineStatus" />
        <result column="examine_note" jdbcType="VARCHAR" property="examineNote" />
        <result column="create_by" jdbcType="VARCHAR" property="createBy" />
        <result column="create_name" jdbcType="VARCHAR" property="createName" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_by" jdbcType="VARCHAR" property="updateBy" />
        <result column="update_name" jdbcType="VARCHAR" property="updateName" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
        <result column="submit_by" jdbcType="VARCHAR" property="submitBy" />
        <result column="submit_name" jdbcType="VARCHAR" property="submitName" />
        <result column="submit_time" jdbcType="TIMESTAMP" property="submitTime" />
        <result column="confirm_by" jdbcType="VARCHAR" property="confirmBy" />
        <result column="confirm_name" jdbcType="VARCHAR" property="confirmName" />
        <result column="confirm_time" jdbcType="TIMESTAMP" property="confirmTime" />
        <result column="abandon_by" jdbcType="VARCHAR" property="abandonBy" />
        <result column="abandon_name" jdbcType="VARCHAR" property="abandonName" />
        <result column="abandon_time" jdbcType="TIMESTAMP" property="abandonTime" />
        <result column="is_plan_order" jdbcType="BOOLEAN" property="isPlanOrder" />
        <collection property="items" resultMap="itemResultList" />
    </resultMap>

    <resultMap id="itemResultList" type="com.zfire.mall.manager.bean.engin.info.EnginInfoOrderItemBean">
        <result column="itemId" jdbcType="VARCHAR" property="id" />
        <result column="parent_id" jdbcType="VARCHAR" property="parentId" />
        <result column="engin_info_no" jdbcType="INTEGER" property="enginInfoNo" />
        <result column="sale_type_id" jdbcType="VARCHAR" property="saleTypeId" />
        <result column="sale_type_code" jdbcType="VARCHAR" property="saleTypeCode" />
        <result column="sale_type_name" jdbcType="VARCHAR" property="saleTypeName" />
        <result column="material_id" jdbcType="VARCHAR" property="materialId" />
        <result column="material_number" jdbcType="VARCHAR" property="materialNumber" />
        <result column="material_name" jdbcType="VARCHAR" property="materialName" />
        <result column="material_old_number" jdbcType="VARCHAR" property="materialOldNumber" />
        <result column="specification" jdbcType="VARCHAR" property="specification" />
        <result column="unit" jdbcType="VARCHAR" property="unit" />
        <result column="price" jdbcType="DECIMAL" property="price" />
        <result column="qty" jdbcType="INTEGER" property="qty" />
        <result column="has_deliver_qty" jdbcType="INTEGER" property="hasDeliverQty" />
        <result column="has_order_qty" jdbcType="INTEGER" property="hasOrderQty" />
        <result column="total_amount" jdbcType="DECIMAL" property="totalAmount" />
        <result column="item_remark" jdbcType="VARCHAR" property="remark" />
        <result column="tax_rate" jdbcType="DECIMAL" property="taxRate" />
    </resultMap>

    <select id="listPage" resultType="com.zfire.mall.manager.bean.engin.info.EnginInfoOrderListBean">
        SELECT
        a.*,
        b.sale_type_id,
        b.sale_type_code,
        b.sale_type_name,
        b.material_id,
        b.material_number,
        b.material_name,
        b.material_old_number,
        b.specification,
        b.unit,
        b.price,
        b.qty,
        b.total_amount,
        b.remark AS item_remark,
        b.has_order_qty,
        b.tax_rate,
        kdm.material_group_name,
        kdm.category_name,
        b.has_deliver_qty,
        ifnull(SUM(iao.refundable_qty),0) as has_send_qty
        FROM
        engin_info_order a JOIN engin_info_order_item b ON a.engin_info_id = b.engin_info_id
        join king_dee_material kdm on b.material_id = kdm.id
        LEFT JOIN invoice_application_order iao on a.engin_info_no = iao.ref_engin_record_no and b.material_id = iao.material_id and b.sale_type_id = iao.sale_type_id
        <where>
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="enginInfoNo != null and enginInfoNo != ''">
                AND a.engin_info_no LIKE CONCAT('%', #{enginInfoNo}, '%')
            </if>
            <if test="productCategoryId != null and productCategoryId != ''">
                AND a.main_id = #{productCategoryId}
            </if>
            <if test="customerKeyword != null and customerKeyword != ''">
                AND (a.customer_name LIKE CONCAT('%', #{customerKeyword}, '%')
                OR a.customer_number LIKE CONCAT('%', #{customerKeyword}, '%')
                )
            </if>
            <if test="projectName != null and projectName != ''">
                AND a.project_name LIKE CONCAT('%', #{projectName}, '%')
            </if>
            <if test="machineType != null and machineType != ''">
                AND a.machine_type = #{orderType}
            </if>
            <if test="useUnit != null and useUnit != ''">
                AND a.use_unit LIKE CONCAT('%', #{useUnit}, '%')
            </if>
            <if test="installAddress != null and installAddress != ''">
                AND a.install_address LIKE CONCAT('%', #{installAddress}, '%')
            </if>
            <if test="projectNo != null and projectNo != ''">
                AND a.project_no LIKE CONCAT('%', #{projectNo}, '%')
            </if>
            <if test="enginFactoryNo != null and enginFactoryNo != ''">
                AND a.engin_factory_no LIKE CONCAT('%', #{enginFactoryNo}, '%')
            </if>
            <if test="enginSignType != null and enginSignType != ''">
                AND a.engin_sign_type = #{enginSignType}
            </if>
            <if test="serviceId != null and serviceId != ''">
                AND a.service_id = #{serviceId}
            </if>
            <if test="startContractExpireDate != null and startContractExpireDate!=''">
                AND a.contract_expire_date <![CDATA[ >= ]]> #{startContractExpireDate}
            </if>
            <if test="endContractExpireDate != null and endContractExpireDate!=''">
                AND a.contract_expire_date <![CDATA[ <= ]]> #{endContractExpireDate}
            </if>
            <if test="startOrderDate != null and startOrderDate!=''">
                AND a.order_date <![CDATA[ >= ]]> #{startOrderDate}
            </if>
            <if test="endOrderDate != null and endOrderDate!=''">
                AND a.order_date <![CDATA[ <= ]]> #{endOrderDate}
            </if>
            <if test="createName != null and createName != ''">
                AND a.create_name LIKE CONCAT('%', #{createName},'%')
            </if>
            <if test="confirmName != null and confirmName != ''">
                AND a.confirm_name LIKE CONCAT('%', #{confirmName},'%')
            </if>
            <if test="specification != null and specification != ''">
                AND b.specification LIKE CONCAT('%', #{specification},'%')
            </if>
            <if test="isClose != null">
                and a.is_close = #{isClose}
            </if>
            <if test="enginOrderType != null and enginOrderType != ''">
                and a.engin_order_type = #{enginOrderType}
            </if>
            <if test="examineStatus != null and examineStatus.size > 0">
                AND a.examine_status in
                <foreach item="item" index="index" collection="examineStatus" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="adminCompanyIds != null and adminCompanyIds.size > 0">
                AND a.admin_company_id in
                <foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="serviceIds != null and serviceIds.size > 0">
                and a.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            AND a.del = 0
        </where>
        GROUP BY b.id
        ORDER BY b.id DESC
    </select>
    <select id="detail" resultMap="listResultMap">
        SELECT
        a.*,
        b.id AS itemId,
        b.engin_info_id,
        b.sale_type_id,
        b.sale_type_code,
        b.sale_type_name,
        b.material_id,
        b.material_number,
        b.material_name,
        b.material_old_number,
        b.specification,
        b.unit,
        b.price,
        b.qty,
        b.total_amount,
        b.has_deliver_qty,
        b.has_order_qty,
        b.remark AS item_remark,
        b.tax_rate,
        d.`name` AS customer_name
        FROM
        engin_info_order a JOIN engin_info_order_item b ON a.engin_info_id = b.engin_info_id
        LEFT JOIN king_dee_customer d ON a.customer_id = d.id
        <where>
            a.engin_info_id = #{id}
            <if test="customerId != null and customerId != ''">
                AND a.customer_id = #{customerId}
            </if>
            <if test="adminCompanyIds != null and adminCompanyIds.size > 0">
                AND a.admin_company_id in
                <foreach item="item" index="index" collection="adminCompanyIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="serviceIds != null and serviceIds.size > 0">
                and a.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            AND a.del = 0
        </where>
    </select>


    <select id="queryEnginInfoSaleExec" resultType="com.zfire.mall.manager.bean.engin.info.EnginInfoSaleExecBean">
          select
            c.order_date,
            c.customer_number,
            c.customer_name,
            a.ref_project_name,
            d.sale_type_name,
            d.material_number,
            d.material_old_number,
            d.material_name,
            d.specification,
            d.qty,
            d.price,
            d.qty * d.price as 'total_amount',
            ifnull((d.qty - sum(if(a.examine_status!='SAVE',ifnull(b.qty, 0),0))), 0) as 'wdQty',
            ifnull((d.qty - sum(if(a.examine_status!='SAVE',ifnull(b.qty, 0),0))), 0) * d.price 'wdAmount',
            ifnull(sum(ifnull(b.direct_transfer_qty,0)), 0) as 'direct_transfer_qty',
            ifnull(sum(ifnull(b.retired_qty,0)), 0) as 'retired_qty',
            ifnull(sum(ifnull(b.refund_product_qty,0)), 0) as 'refund_product_qty',
            c.service_name,
            c.confirm_name,
            '否',
            c.remark,
            c.ge_li_iner_note,
            a.ref_engin_record_no,
            a.ref_power_category,
            c.contract_expire_date,
            c.install_address,
            c.phone,
            c.engin_sign_type
        from engin_info_order c
        join engin_info_order_item d on d.engin_info_id = c.engin_info_id
        left join engin_order a on a.ref_engin_record_no = c.engin_info_no
        left join engin_order_item b on a.engin_order_no = b.engin_order_no and b.material_id = d.material_id
        <include refid="queryEnginInfoSaleExecWhere"></include>
            group by d.id
            order by d.id desc
    </select>

    <sql id="queryEnginInfoSaleExecWhere">
        <where>
            <if test="startOrderDate != null and startOrderDate !='' and endOrderDate != null and endOrderDate !=''">
                and c.order_date between #{startOrderDate} and #{endOrderDate}
            </if>
            <if test="customerId != null and customerId !=''">
                and c.customer_id = #{customerId}
            </if>
            <if test="refProjectName != null and refProjectName !=''">
                and c.project_name like concat('%',#{refProjectName},'%')
            </if>
            <if test="specification != null and specification !=''">
                and d.specification like concat('%',#{specification},'%')
            </if>
            <if test="refEnginRecordNo != null and refEnginRecordNo !=''">
                and c.engin_info_no like concat('%',#{refEnginRecordNo},'%')
            </if>
            <if test="isClose != null">

            </if>
            <if test="confirmName != null and confirmName !=''">
                and c.confirm_name like  concat('%',#{confirmName},'%')
            </if>
            <if test="serviceId != null and serviceId !=''">
                and c.service_id = #{serviceId}
            </if>
            <if test="serviceIds != null and serviceIds.size > 0">
                and c.service_id in
                <foreach item="item" index="index" collection="serviceIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </sql>


    <select id="v2ListPage" resultType="com.zfire.mall.manager.bean.engin.v2.info.EnginInfoOrderVO">
        SELECT
            a.*,
            b.sale_type_id,
            b.sale_type_code,
            b.sale_type_name,
            b.material_id,
            b.material_number,
            b.material_name,
            b.material_old_number,
            b.specification,
            b.unit,
            b.price,
            b.qty,
            b.total_amount,
            b.remark AS item_remark,
            b.has_order_qty,
            b.tax_rate,
            kdm.material_group_name,
            kdm.category_name,
            b.has_deliver_qty,
        ifnull(SUM(iao.refundable_qty),0) as has_send_qty,
            IFNULL(b.qty, 0) - IFNULL(b.has_order_qty, 0) AS surplusQty
        FROM
            engin_info_order a JOIN engin_info_order_item b ON a.engin_info_id = b.engin_info_id
                               JOIN king_dee_material kdm ON b.material_id = kdm.id
                            LEFT JOIN invoice_application_order iao on a.engin_info_no = iao.ref_engin_record_no and b.material_id = iao.material_id and b.sale_type_id = iao.sale_type_id

        ${ex.query}
        AND a.del = 0
        GROUP BY b.id
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY create_time DESC
        </if>
        ${ex.orderBy}
    </select>
    <select id="v2ExecListPage" resultType="com.zfire.mall.manager.bean.engin.v2.info.EnginInfoSaleExecVO">
        SELECT
            c.order_date,
            c.customer_number,
            c.customer_name,
            a.ref_project_name,
            d.sale_type_name,
            d.material_number,
            d.material_old_number,
            d.material_name,
            d.specification,
            d.qty,
            d.price,
            d.qty * d.price as 'total_amount',
            ifnull((d.qty - sum(if(a.examine_status!='SAVE',ifnull(b.qty, 0),0))), 0) as 'wdQty',
            ifnull((d.qty - sum(if(a.examine_status!='SAVE',ifnull(b.qty, 0),0))), 0) * d.price 'wdAmount',
            ifnull(sum(ifnull(b.direct_transfer_qty,0)), 0) as 'direct_transfer_qty',
            ifnull(sum(ifnull(b.retired_qty,0)), 0) as 'retired_qty',
            ifnull(sum(ifnull(b.refund_product_qty,0)), 0) as 'refund_product_qty',
            c.service_name,
            c.confirm_name,
            '否',
            c.remark,
            c.ge_li_iner_note,
            a.ref_engin_record_no,
            a.ref_power_category,
            c.contract_expire_date,
            c.install_address,
            c.phone,
            c.engin_sign_type
        FROM
        engin_info_order c
        JOIN engin_info_order_item d ON d.engin_info_id = c.engin_info_id
        LEFT JOIN engin_order a ON a.ref_engin_record_no = c.engin_info_no
        LEFT JOIN engin_order_item b ON a.engin_order_no=b.engin_order_no AND b.material_id=d.material_id
        ${ex.query}
        GROUP BY d.id
        <if test="ex.orderBy == null or ex.orderBy ==''">
            ORDER BY d.id DESC
        </if>
        ${ex.orderBy}
    </select>


    <select id="v2ExecListTotal" resultType="java.lang.Integer">
        select
          count(distinct d.id)
        from
          engin_info_order c
        JOIN engin_info_order_item d ON d.engin_info_id = c.engin_info_id
        LEFT JOIN engin_order a ON a.ref_engin_record_no = c.engin_info_no
        LEFT JOIN engin_order_item b ON a.engin_order_no=b.engin_order_no AND b.material_id=d.material_id
        ${ex.query}
    </select>


</mapper>