<?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.fjhx.purchase.mapper.purchase.PurchaseMapper">
    <select id="getPage" resultType="com.fjhx.purchase.entity.purchase.vo.PurchaseVo">
        <include refid="pageOrList"/>
    </select>
    <select id="getList" resultType="com.fjhx.purchase.entity.purchase.vo.PurchaseVo">
        <include refid="pageOrList"/>
    </select>
    <sql id="pageOrList">
        SELECT p.id,
               p.purchase_name,
               p.purchase_time,
               p.code,
               p.supply_id,
               p.purchase_content,
               p.purchase_status,
               p.arrival_status,
               p.pay_status,
               p.approved_date,
               p.process_instance_id,
               p.amount,
               p.currency,
               p.rate,
               p.invoice_type,
               p.create_user,
               p.create_time,
               p.update_user,
               p.update_time,
               p.victoriatourist_json,
               p.flow_id,
               p.count,
               p.data_resource,
               p.data_resource_id,
               IF(sum( dg.`status` ) != count( dg.`status` ), 1, 0) isTransit,
               de.dept_name
        FROM purchase p
                 LEFT JOIN purchase_detail pd ON pd.purchase_id = p.id
                 LEFT JOIN bytesailing_item.product_info pi ON pd.bussiness_id = pi.id
                 LEFT JOIN bytesailing_base.sys_dept de
                           ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_id
                 LEFT JOIN bytesailing_victoriatourist.deliver_goods dg ON dg.purchase_id = p.id
            ${ew.customSqlSegment}
        </sql>
    <select id="paymentBill" resultType="com.fjhx.purchase.entity.purchase.dto.PurchaseDto">
        SELECT
            pu.code,
            pu.supply_id,
            bs.`name` supplyName,
            pu.amount,
            IFNULL(SUM(nd.money),0) invoiceAmount,
            pu.pay_status  payStatus,
            pu.currency,
            pu.id
        FROM
            purchase pu
                LEFT JOIN bytesailing_supply.supplier_info bs ON pu.supply_id = bs.id
                LEFT JOIN  invoice_details nd on pu.id = nd.purchase_id
            ${ew.customSqlSegment}
    </select>
    <select id="arrivalAmount" resultType="com.fjhx.purchase.entity.purchase.dto.PurchaseDto">
        SELECT
            SUM(wd.receipt_quantity) * pd.price  amount,
            pd.purchase_id id
        FROM
            bytesailing_wms.stock_wait sw
                JOIN bytesailing_wms.stock_wait_details wd on sw.id = wd.stock_wait_id
                join arrival_detail ad on wd.business_details_id = ad.id
                join purchase_detail pd on ad.purchase_detail_id = pd.id
            ${ew.customSqlSegment}
    </select>
    <select id="paidAmount" resultType="com.fjhx.purchase.entity.purchase.dto.PurchaseDto">
        SELECT
            sum(pd.money) amount,
            p.currency,
            pd.purchase_id id
        FROM
            pay_detail pd
                LEFT JOIN pay p ON pd.pay_id = p.id
            ${ew.customSqlSegment}
    </select>
    <select id="getDepts" resultType="com.ruoyi.common.core.domain.entity.SysDept">
        SELECT
            de.dept_id,
            de.dept_name
        FROM
            purchase_detail pd
                JOIN bytesailing_item.product_info pi ON pd.bussiness_id = pi.id
                JOIN bytesailing_base.sys_dept de ON CAST(json_unquote(pi.victoriatourist_json -> '$.deptId') AS UNSIGNED) = de.dept_id
        GROUP BY
            de.dept_id
    </select>
    <select id="getPurchaseCountList" resultType="com.fjhx.wms.entity.PurchaseDetailPo">
        SELECT
            sum( pd.count ) AS `count`,
	        pd.bussiness_id
        FROM
            purchase_detail pd
            JOIN purchase p ON pd.purchase_id = p.id
        ${ew.customSqlSegment}
    </select>
    <select id="getPurchaseDisqualificationQuantity"
            resultType="com.fjhx.purchase.entity.quality.po.QualityDetail">
        SELECT
            qd.product_info_id AS bussinessId,
            sum( qd.disqualification_quantity ) AS noQualifiedCount
        FROM
            purchase p
                JOIN bytesailing_wms.stock_wait sw ON sw.business_id = p.id and sw.del_flag = 0
                JOIN bytesailing_wms.arrival_stock_records asr ON asr.stock_wait_id = sw.id and asr.del_flag = 0
                JOIN bytesailing_victoriatourist.quality_info qi ON qi.bussiness_id = asr.id and qi.del_flag = 0
                JOIN bytesailing_victoriatourist.quality_details qd ON qd.quality_info_id = qi.id and qd.del_flag = 0
        WHERE
            p.id = #{purchaseId}
        GROUP BY
            qd.product_info_id
    </select>

</mapper>
