<?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.openhis.web.inventorymanage.mapper.PurchaseReturnMapper">
    <select id="selectDetail" resultType="com.openhis.web.inventorymanage.dto.PurchaseReturnDetailDto">
        SELECT T1.bus_no,
               T1.original_bus_no,
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN T1.item_quantity
                   ELSE 0
                   END     AS item_quantity,               --退库数量
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN (
                       SELECT COALESCE(SUM(T9.item_quantity), 0)
                       FROM wor_supply_request AS T9
                       WHERE T9.original_bus_no = T1.original_bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                         AND T9.status_enum = #{agree}
                   )
                   ELSE (
                       SELECT COALESCE(SUM(T9.item_quantity), 0)
                       FROM wor_supply_request AS T9
                       WHERE T9.original_bus_no = T1.bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                         AND T9.status_enum = #{agree}
                   )
                   END     AS returned_quantity,           --已退数量
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN T1.id
                   ELSE NULL
                   END   AS  id,                      --采购退货id
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN NULL
                   ELSE T1.id
                   END   AS  purchase_id,                   --采购入库id
               T2.quantity AS quantity,                    --库存数量
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN (
                           SELECT T9.item_quantity
                               FROM wor_supply_request AS T9
                               WHERE T1.original_bus_no = T9.bus_no
                                 AND T9.item_id = T1.item_id
                                 AND T9.lot_number = T1.lot_number
                                 AND T9.delete_flag = '0'
                       )
                   ELSE T3.quantity
                   END   AS  purchase_quantity,            --采购数量
               T11.amount AS price,                         --采购单价
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN (
                       SELECT T9.unit_code
                       FROM wor_supply_request AS T9
                       WHERE T1.original_bus_no = T9.bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                   )
                   ELSE T1.unit_code
                   END   AS  purchase_unit_code,            --采购单位
               T1.unit_code         AS measurement_unit_code,       -- 物品计量单位
               T4.unit_code,                                        -- 包装单位
               T4.min_unit_code,                                    -- 最小单位
               T5."name"   AS purpose_location_name,       --仓库
               T6."name"   AS purpose_location_store_name, --货位
               T4."name"   AS item_name,                   --药品
               T1.lot_number,                              --批次号
               T1.reason,                                  --退货原因
               T1.remake,                                  --备注
               T1.item_id,                                 --物品编码
               T1.purpose_location_id,                     --目的仓库ID
               T1.purpose_location_store_id,               --目的货位ID
               T1.purpose_type_enum,                       -- 目的仓库类型
               T1.supplier_id,            -- 供应商id
               T7."name"            AS supplier_name,               -- 供应商名称
               T8.total_volume,                                     -- 规格
               T4.manufacturer_text AS manufacturer,                -- 生产厂商
               #{medicine}          AS item_type,
               T1.start_time,                                       -- 开始时间
               T1.end_time,                                         -- 结束时间
               T1.invoice_no,                                   -- 发票号
               T1.trace_no,                                   -- 追溯码
               T2.packaging_levels,             -- 追溯码包装层级
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN T1.practitioner_id
                   ELSE NULL
                   END   AS  practitioner_id,               --经手人
               T4.part_percent                              --拆零比
        FROM wor_supply_request T1
                 LEFT JOIN wor_inventory_item T2
                           ON T1.item_id = T2.item_id
                               AND T1.purpose_location_id = T2.location_id
                               AND T1.lot_number = T2.lot_number
                               AND T2.delete_flag = '0'
                 LEFT JOIN wor_supply_delivery T3
                           ON T1.id = T3.request_id
                               AND T3.delete_flag = '0'
                 INNER JOIN med_medication_definition T4
                            ON T1.item_id = T4.id
                                AND T4.delete_flag = '0'
                 LEFT JOIN med_medication T8
                           ON T8.medication_def_id = T4.id
                               AND T8.delete_flag = '0'
                 LEFT JOIN adm_location T5
                           ON T1.purpose_location_id = T5.id
                               AND T5.delete_flag = '0'
                 LEFT JOIN adm_location T6
                           ON T1.purpose_location_store_id = T6.id
                               AND T6.delete_flag = '0'
                 LEFT JOIN adm_supplier T7
                           ON T7.id = T1.supplier_id
                               AND T7.delete_flag = '0'
                 LEFT JOIN adm_charge_item_definition AS T10
                           ON T10.instance_id = T2.item_id
                               AND T10.delete_flag = '0'
                 LEFT JOIN adm_charge_item_def_detail AS T11
                           ON T11.definition_id = T10.id
                               AND T11.delete_flag = '0'
                               AND T11.condition_value = T2.lot_number
        WHERE T1.bus_no = #{busNo}
          AND T1.item_table = #{medicationTableName}
          AND T1.delete_flag = '0'
        UNION
        SELECT T1.bus_no,
               T1.original_bus_no,
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN T1.item_quantity
                   ELSE NULL
                   END     AS item_quantity,               --退库数量
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN (
                       SELECT COALESCE(SUM(T9.item_quantity), 0)
                       FROM wor_supply_request AS T9
                       WHERE T9.original_bus_no = T1.original_bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                         AND T9.status_enum = #{agree}
                   )
                   ELSE (
                       SELECT COALESCE(SUM(T9.item_quantity), 0)
                       FROM wor_supply_request AS T9
                       WHERE T9.original_bus_no = T1.bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                         AND T9.status_enum = #{agree}
                   )
                   END     AS returned_quantity,           --已退数量
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN T1.id
                   ELSE NULL
                   END   AS  id,                      --采购退货id
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN NULL
                   ELSE T1.id
                   END   AS  purchase_id,                   --采购入库id
               T2.quantity AS quantity,                    --库存数量
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN (
                       SELECT T9.item_quantity
                       FROM wor_supply_request AS T9
                       WHERE T1.original_bus_no = T9.bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                   )
                   ELSE T3.quantity
                   END   AS  purchase_quantity,            --采购数量
               T11.amount AS price,                         --采购单价
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN (
                       SELECT T9.unit_code
                       FROM wor_supply_request AS T9
                       WHERE T1.original_bus_no = T9.bus_no
                         AND T9.item_id = T1.item_id
                         AND T9.lot_number = T1.lot_number
                         AND T9.delete_flag = '0'
                   )
                   ELSE T1.unit_code
                   END   AS  purchase_unit_code,            --采购单位
               T1.unit_code         AS measurement_unit_code,       -- 物品计量单位
               T4.unit_code,                                        -- 包装单位
               T4.min_unit_code,                                    -- 最小单位
               T5."name"   AS purpose_location_name,       --仓库
               T6."name"   AS purpose_location_store_name, --货位
               T4."name"   AS item_name,                   --药品
               T1.lot_number,                              --批次号
               T1.reason,                                  --退货原因
               T1.remake,                                  --备注
               T1.item_id,                                 --物品编码
               T1.purpose_location_id,                     --目的仓库ID
               T1.purpose_location_store_id,               --目的货位ID
               T1.purpose_type_enum,                       -- 目的仓库类型
               T1.supplier_id,            -- 供应商id
               T7."name"            AS supplier_name,           -- 供应商名称
               T4."size"            AS total_volume,            -- 规格
               T4.manufacturer_text AS manufacturer,            -- 生产厂商
               #{device}            AS item_type,
               T1.start_time,                                 -- 开始时间
               T1.end_time,                                   -- 结束时间
               T1.invoice_no,                                   -- 发票号
               T1.trace_no,                                   -- 追溯码
               T2.packaging_levels,              -- 追溯码包装层级
               CASE
                   WHEN T1.type_enum = #{purchaseReturn}
                       THEN T1.practitioner_id
                   ELSE NULL
                   END   AS  practitioner_id,               --经手人
               T4.part_percent                              --拆零比
        FROM wor_supply_request T1
                 LEFT JOIN wor_inventory_item T2
                           ON T2.item_id = T1.item_id
                               AND T2.location_id = T1.purpose_location_id
                               AND T2.lot_number = T1.lot_number
                               AND T2.delete_flag = '0'
                 LEFT JOIN wor_supply_delivery T3
                           ON T1.id = T3.request_id
                               AND T3.delete_flag = '0'
                 INNER JOIN adm_device_definition T4
                            ON T1.item_id = T4.id
                                AND T4.delete_flag = '0'
                 LEFT JOIN adm_location T5
                           ON T1.purpose_location_id = T5.id
                               AND T5.delete_flag = '0'
                 LEFT JOIN adm_location T6
                           ON T1.purpose_location_store_id = T6.id
                               AND T6.delete_flag = '0'
                 LEFT JOIN adm_supplier T7
                           ON T7.id = T1.supplier_id
                               AND T7.delete_flag = '0'
                 LEFT JOIN adm_charge_item_definition AS T10
                           ON T10.instance_id = T2.item_id
                               AND T10.delete_flag = '0'
                 LEFT JOIN adm_charge_item_def_detail AS T11
                           ON T11.definition_id = T10.id
                               AND T11.delete_flag = '0'
                               AND T11.condition_value = T2.lot_number
        WHERE T1.bus_no = #{busNo}
          AND T1.item_table = #{deviceTableName}
          AND T1.delete_flag = '0'
    </select>
    <select id="selectGeneratedDetail"
            resultType="com.openhis.web.inventorymanage.dto.PurchaseReturnPageDto">
        SELECT T1.bus_no AS supply_bus_no,
               T1.status_enum,
               T1.type_enum,
               T1.supplier_id,
               T1.practitioner_id,
               T1.applicant_id,
               T1.approver_id,
               MIN(T1.create_time) AS create_time,           -- 制单时间
               T1.approval_time
        FROM wor_supply_request AS T1
        WHERE T1.original_bus_no = #{busNo}
          AND T1.type_enum = #{purchaseReturn}
          AND T1.delete_flag = '0'
        GROUP BY T1.bus_no,
                 T1.status_enum,
                 T1.type_enum,
                 T1.supplier_id,
                 T1.practitioner_id,
                 T1.applicant_id,
                 T1.approver_id,
                 T1.approval_time
        ORDER BY T1.bus_no DESC
    </select>
</mapper>
