<?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.dk.mdm.mapper.report.ReportMapper">

    <!--库存查询-->
    <select id="getIvtDetailReport" resultType="java.util.Map">
        select tpi.inv_id as "invId",
        tpi.sku_id as "skuId",
        tmgs.sku_code as "skuCode",
        tmgs.sku_name as "skuName",
        tmgs.sku_model as "skuModel",
        tmgs.brand_id as "brandId",
        tmgb.brand_name as "brandName",
        tmgs.category_id as "categoryId",
        tmgc.cat_name as "categoryName",
        tmgs.unit_id as "unitId",
        tmgu.unit_name as "unitName",
        tmgs.sub_unit_id as "subUnitId",
        tmgs.conversion_factor as "conversionFactor",
        tmgs.series_id as "seriesId",
        tmgseries.series_name as "seriesName",
        tmgs.sku_spec as "skuSpec",
        tmgs.price_purchase as "pricePurchase",
        tmgs.price_standard as "priceStandard",
        tmgs.price_wholesale as "priceWholesale",
        tmgs.price_limited as "priceLimited",
        case when sku_images is null then null else sku_images -> 0 ->> 'url' end as "path",
        tpi.non_std_code as "nonStdCode",
        sys.f_remove_zero(tpi.inv_qty) as "invQty",
        sys.f_remove_zero(tpi.outing_qty) as "outingQty",
        sys.f_remove_zero(tpi.usable_qty) as "usableQty",
        sys.f_remove_zero(tpi.freeze_qty) as "freezeQty",
        tpi.wh_id as "whId",
        tmw.wh_code as "whCode",
        tmw.wh_name as "whName",
        dkic_b.f_get_last_ivt_price(tpi.inv_id,null,null,null) as "priceInto",
        tpi.remarks,
        tpi.cost_price as "costPrice",
        tpi.cost_amt as "costAmt"
        --,dkic_b.f_box_piece(tmgsu.unit_name,tmgu.unit_name,FLOOR(tpi.inv_qty/tmgs.pack_box),FLOOR(tpi.inv_qty%tmgs.pack_box)) AS "boxPiece"
        from dkic_b.t_psi_inventory as tpi
        inner join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        inner join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgs.brand_id = tmgb.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgs.category_id = tmgc.cat_id
        left join dkic_b.t_mst_goods_series as tmgseries on tmgs.series_id = tmgseries.series_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgsu on tmgs.sub_unit_id = tmgsu.unit_id
        where tpi.flg_valid
        AND tpi.cp_id = #{cpId}
        AND tpi.inv_qty != 0
        <if test="skuId != null">
            AND tpi.sku_id = #{skuId} ::uuid
        </if>
        <if test="nonStdCode != null">
            AND tpi.non_std_code = #{nonStdCode}
        </if>
        <if test="skuCode != null">
            AND tmgs.sku_code LIKE concat('%',my_ex.likequery(#{skuCode}),'%')
        </if>
        <if test="skuName != null">
            AND tmgs.sku_name LIKE concat('%',my_ex.likequery(#{skuName}),'%')
        </if>
        <if test="whName != null">
            AND tmgs.wh_name LIKE concat('%',my_ex.likequery(#{whName}),'%')
        </if>
        <if test="whId != null">
            AND tpi.wh_id = #{whId} ::uuid
        </if>
        order by tpi.sku_id,tpi.inv_id desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!--库存查询count-->
    <select id="getIvtDetailReportCount" resultType="java.lang.Long">
        SELECT count(1)
        from dkic_b.t_psi_inventory as tpi
        inner join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        inner join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgs.brand_id = tmgb.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgs.category_id = tmgc.cat_id
        left join dkic_b.t_mst_goods_series as tmgseries on tmgs.series_id = tmgseries.series_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        where tpi.flg_valid
        AND tpi.cp_id = #{cpId}
        AND tpi.inv_qty != 0
        <if test="skuId != null">
            AND tpi.sku_id = #{skuId} ::uuid
        </if>
        <if test="nonStdCode != null">
            AND tpi.non_std_code = #{nonStdCode}
        </if>
        <if test="skuCode != null">
            AND tmgs.sku_code LIKE concat('%',my_ex.likequery(#{skuCode}),'%')
        </if>
        <if test="skuName != null">
            AND tmgs.sku_name LIKE concat('%',my_ex.likequery(#{skuName}),'%')
        </if>
        <if test="whName != null">
            AND tmgs.wh_name LIKE concat('%',my_ex.likequery(#{whName}),'%')
        </if>
        <if test="whId != null">
            AND tpi.wh_id = #{whId} ::uuid
        </if>
    </select>
    <!--库存批次查询-->
    <select id="getIvtDetailBatchReport" resultType="java.util.Map">
        select tpib.inv_id as "invId",
        tpib.sku_id as "skuId",
        tmgs.sku_code as "skuCode",
        tmgs.sku_name as "skuName",
        tmgs.sku_model as "skuModel",
        tmgs.brand_id as "brandId",
        tmgb.brand_name as "brandName",
        tmgs.category_id as "categoryId",
        tmgc.cat_name as "categoryName",
        tmgs.unit_id as "unitId",
        tmgu.unit_name as "unitName",
        tmgs.sub_unit_id as "subUnitId",
        tmgs.conversion_factor as "conversionFactor",
        tmgs.series_id as "seriesId",
        tmgseries.series_name as "seriesName",
        tmgs.sku_spec as "skuSpec",
        tpib.non_std_code as "nonStdCode",
        sys.f_remove_zero(tpit.inv_qty) as "invQty",
        sys.f_remove_zero(tpit.usable_qty) as "usableQty",
        tpib.wh_id as "whId",
        tmw.wh_code as "whCode",
        tmw.wh_name as "whName",
        tpib.remarks,
        tpib.cost_price as "costPrice",
        tpib.cost_amt as "costAmt",
        --dkic_b.f_box_piece(tmgsu.unit_name,tmgu.unit_name,FLOOR(tpib.inv_qty/tmgs.pack_box),FLOOR(tpib.inv_qty%tmgs.pack_box)) AS "boxPiece",
        case when sku_images is null then null else sku_images -> 0 ->> 'url' end as "path",
        COALESCE(tpo.out_no,'') || COALESCE(tpi.into_no,'') || COALESCE(tpt.transfer_no,'') as "fromNo",
        COALESCE(sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) ,'')
        || COALESCE(sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}),'')
        || COALESCE(sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}),'') as "fromTypeName",
        sys.f_remove_zero(tpib.inv_qty) as "inventoryQty"
        from dkic_b.t_psi_inventory_batch as tpib
        inner join dkic_b.t_psi_inventory as tpit on tpit.inv_id = tpib.inv_id
        inner join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpib.wh_id
        inner join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpib.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgs.brand_id = tmgb.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgs.category_id = tmgc.cat_id
        left join dkic_b.t_mst_goods_series as tmgseries on tmgs.series_id = tmgseries.series_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgsu on tmgs.sub_unit_id = tmgsu.unit_id
        left join dkic_b.t_psi_outbound tpo on tpo.out_id = tpib.from_id
        left join sys.t_data_kind as tdk1 on tpo.out_type = tdk1.kind_code
        left join dkic_b.t_psi_inbound tpi on tpi.into_id = tpib.from_id
        left join sys.t_data_kind as tdk2 on tpi.into_type = tdk2.kind_code
        left join dkic_b.t_psi_transfer tpt on tpt.transfer_id = tpib.from_id
        left join sys.t_data_kind as tdk3 on tpt.transfer_type = tdk3.kind_code
        where tpib.flg_valid
        AND tpib.cp_id = #{cpId}
        AND tpib.inv_qty != 0
        <if test="skuId != null">
            AND tpib.sku_id = #{skuId} ::uuid
        </if>
        <if test="nonStdCode != null">
            AND tpib.non_std_code = #{nonStdCode}
        </if>
        <if test="skuCode != null">
            AND tmgs.sku_code LIKE concat('%',my_ex.likequery(#{skuCode}),'%')
        </if>
        <if test="skuName != null">
            AND tmgs.sku_name LIKE concat('%',my_ex.likequery(#{skuName}),'%')
        </if>
        <if test="whName != null">
            AND tmgs.wh_name LIKE concat('%',my_ex.likequery(#{whName}),'%')
        </if>
        <if test="whId != null">
            AND tpib.wh_id = #{whId} ::uuid
        </if>
        order by tpib.sku_id,tpit.inv_id desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!--库存批次查询count-->
    <select id="getIvtDetailBatchReportCount" resultType="java.lang.Long">
        SELECT count(1)
        from dkic_b.t_psi_inventory_batch as tpib
        inner join dkic_b.t_psi_inventory as tpit on tpit.inv_id = tpib.inv_id
        inner join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpib.wh_id
        inner join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpib.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgs.brand_id = tmgb.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgs.category_id = tmgc.cat_id
        left join dkic_b.t_mst_goods_series as tmgseries on tmgs.series_id = tmgseries.series_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgsu on tmgs.sub_unit_id = tmgsu.unit_id
        left join dkic_b.t_psi_outbound tpo on tpo.out_id = tpib.from_id
        left join dkic_b.t_psi_inbound tpi on tpi.into_id = tpib.from_id
        left join dkic_b.t_psi_transfer tpt on tpt.transfer_id = tpib.from_id
        where tpib.flg_valid
        AND tpib.cp_id = #{cpId}
        AND tpib.inv_qty != 0
        <if test="skuId != null">
            AND tpib.sku_id = #{skuId} ::uuid
        </if>
        <if test="nonStdCode != null">
            AND tpib.non_std_code = #{nonStdCode}
        </if>
        <if test="skuCode != null">
            AND tmgs.sku_code LIKE concat('%',my_ex.likequery(#{skuCode}),'%')
        </if>
        <if test="skuName != null">
            AND tmgs.sku_name LIKE concat('%',my_ex.likequery(#{skuName}),'%')
        </if>
        <if test="whName != null">
            AND tmgs.wh_name LIKE concat('%',my_ex.likequery(#{whName}),'%')
        </if>
        <if test="whId != null">
            AND tpib.wh_id = #{whId} ::uuid
        </if>
    </select>

    <!-- 采购明细报表明细条件列 -->
    <sql id="Condition_PurDetailReport">
        <where>
            tppi.flg_valid
            <if test="purNo != null and purNo != ''">
                AND tpp.pur_no like concat('%', my_ex.likequery(#{purNo}) , '%')
            </if>
            <if test="purTypeList != null and purTypeList.size()>0">
                AND tpp.pur_type =any(#{purTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="purStatusList != null and purStatusList.size()>0">
                AND tpp.pur_status =any(#{purStatusList,typeHandler=StringListTypeHandler})
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND tpp.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>
            <if test="contactName != null and contactName != ''">
                AND tpp.contact_name like concat('%', my_ex.likequery(#{contactName}) , '%')
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND tpp.contact_phone like concat('%', my_ex.likequery(#{contactPhone}) , '%')
            </if>
            <if test="remarks != null and remarks != ''">
                AND tpp.remarks like concat('%', my_ex.likequery(#{remarks}) , '%')
            </if>
            <if test="cpId != null">
                AND tpp.cp_id = #{cpId}
            </if>
            <if test="orgIdList != null  and orgIdList.size()>0">
                AND tpp.org_id =
                any(#{orgIdList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="staffIdList != null  and staffIdList.size()>0">
                AND tpp.staff_id =
                any(#{staffIdList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="pickupDateStart != null">
                AND tpp.pickup_date &gt;= #{pickupDateStart}::timestamp with time zone
            </if>
            <if test="pickupDateEnd != null">
                AND tpp.pickup_date &lt; #{pickupDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="goodsSeriesList != null  and goodsSeriesList.size()>0">
                AND tmgs.series_id =
                any(#{goodsSeriesList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsCategoryList != null  and goodsCategoryList.size()>0">
                AND tmgs.category_id =
                any(#{goodsCategoryList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsBrandList != null  and goodsBrandList.size()>0">
                AND tmgs.brand_id =
                any(#{goodsBrandList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="skuName != null and skuName != ''">
                AND tmgs.sku_name like concat('%', my_ex.likequery(#{skuName}) , '%')
            </if>
            <if test="skuModel != null and skuModel != ''">
                AND tmgs.sku_model like concat('%', my_ex.likequery(#{skuModel}) , '%')
            </if>
        </where>
    </sql>
    <!-- 采购明细报表主表条件列 -->
    <sql id="Condition_PurReport">
        <where>
            <if test="purNo != null and purNo != ''">
                AND tpp.pur_no like concat('%', my_ex.likequery(#{purNo}) , '%')
            </if>
            <if test="purTypeList != null and purTypeList.size()>0">
                AND tpp.pur_type =any(#{purTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="purStatusList != null and purStatusList.size()>0">
                AND tpp.pur_status =any(#{purStatusList,typeHandler=StringListTypeHandler})
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND tpp.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>
            <if test="contactName != null and contactName != ''">
                AND tpp.contact_name like concat('%', my_ex.likequery(#{contactName}) , '%')
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND tpp.contact_phone like concat('%', my_ex.likequery(#{contactPhone}) , '%')
            </if>
            <if test="remarks != null and remarks != ''">
                AND tpp.remarks like concat('%', my_ex.likequery(#{remarks}) , '%')
            </if>
            <if test="cpId != null">
                AND tpp.cp_id = #{cpId}
            </if>
            <if test="orgIdList != null  and orgIdList.size()>0">
                AND tpp.org_id =
                any(#{orgIdList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="staffIdList != null  and staffIdList.size()>0">
                AND tpp.staff_id =
                any(#{staffIdList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="pickupDateStart != null">
                AND tpp.pickup_date &gt;= #{pickupDateStart}::timestamp with time zone
            </if>
            <if test="pickupDateEnd != null">
                AND tpp.pickup_date &lt; #{pickupDateEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>
    <!-- 销售明细报表明细条件列 -->
    <sql id="Condition_OrdDetailReport">
        <where>
            t.flg_valid
            <if test="orderNo != null and orderNo != ''">
                AND t.order_no LIKE concat('%',my_ex.likequery(#{orderNo}),'%')
            </if>
            <if test="ordTypeList != null and ordTypeList.size()>0">
                AND t.ord_type =any(#{ordTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="addressFull != null and addressFull != ''">
                AND t.address_full LIKE concat('%',my_ex.likequery(#{addressFull}),'%')
            </if>
            <if test="contactName != null and contactName != ''">
                AND t.contact_name LIKE concat('%',my_ex.likequery(#{contactName}),'%')
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="remarks != null and remarks != ''">
                AND t.remarks LIKE concat('%',my_ex.likequery(#{remarks}),'%')
            </if>
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="cusPhone != null and cusPhone != ''">
                AND tmc.cus_phone LIKE concat('%',my_ex.likequery(#{cusPhone}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="ordStatusList != null and ordStatusList.size() > 0">
                AND t.ord_status =any(#{ordStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="deliveryDateStart != null and deliveryDateEnd != null">
                AND t.delivery_date &gt;= #{deliveryDateStart}::timestamp with time zone
                AND t.delivery_date &lt; #{deliveryDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="goodsSeriesList != null  and goodsSeriesList.size()>0">
                AND tmgs.series_id =
                any(#{goodsSeriesList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsCategoryList != null  and goodsCategoryList.size()>0">
                AND tmgs.category_id =
                any(#{goodsCategoryList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsBrandList != null  and goodsBrandList.size()>0">
                AND tmgs.brand_id =
                any(#{goodsBrandList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="skuName != null and skuName != ''">
                AND tmgs.sku_name like concat('%', my_ex.likequery(#{skuName}) , '%')
            </if>
            <if test="skuModel != null and skuModel != ''">
                AND tmgs.sku_model like concat('%', my_ex.likequery(#{skuModel}) , '%')
            </if>
            <if test="whList != null  and whList.size()>0">
                AND toi.wh_id =
                any(#{whList,typeHandler=UuidListTypeHandler})
            </if>
        </where>
    </sql>
    <!-- 销售明细报表主表条件列 -->
    <sql id="Condition_OrdReport">
        <where>
            <if test="orderNo != null and orderNo != ''">
                AND t.order_no LIKE concat('%',my_ex.likequery(#{orderNo}),'%')
            </if>
            <if test="ordTypeList != null and ordTypeList.size()>0">
                AND t.ord_type =any(#{ordTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="ordStatusList != null and ordStatusList.size() > 0">
                AND t.ord_status =any(#{ordStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="addressFull != null and addressFull != ''">
                AND t.address_full LIKE concat('%',my_ex.likequery(#{addressFull}),'%')
            </if>
            <if test="contactName != null and contactName != ''">
                AND t.contact_name LIKE concat('%',my_ex.likequery(#{contactName}),'%')
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="remarks != null and remarks != ''">
                AND t.remarks LIKE concat('%',my_ex.likequery(#{remarks}),'%')
            </if>
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="cusPhone != null and cusPhone != ''">
                AND tmc.cus_phone LIKE concat('%',my_ex.likequery(#{cusPhone}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="deliveryDateStart != null and deliveryDateEnd != null">
                AND t.delivery_date &gt;= #{deliveryDateStart}::timestamp with time zone
                AND t.delivery_date &lt; #{deliveryDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>
    <!-- 出库明细报表明细条件列 -->
    <sql id="Condition_OutboundDetailReport">
        <where>
            t.flg_valid
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="outTypeRList != null and outTypeRList.size()>0">
                AND t.out_type =any(#{outTypeRList,typeHandler=StringListTypeHandler})
            </if>
            <if test="outTypeRList == null || outTypeRList.size()==0">
                AND (t.out_type = '出库类型-其他出库'
                    or t.out_type =  '出库类型-销售出库')
            </if>
            <if test="outNo != null and outNo != ''">
                AND t.out_no LIKE concat('%',my_ex.likequery(#{outNo}),'%')
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND t.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="outTypeList != null and outTypeList.size()>0">
                AND t.out_type =any(#{outTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="fromNo != null and fromNo != ''">
                AND t.from_no LIKE concat('%',my_ex.likequery(#{fromNo}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffName != null and staffName != ''">
                AND tms.staff_name LIKE concat('%',my_ex.likequery(#{staffName}),'%')
            </if>
            <if test="orgName != null and orgName != ''">
                AND tmo.org_name LIKE concat('%',my_ex.likequery(#{orgName}),'%')
            </if>
            <if test="outStatusList != null and outStatusList.size() > 0">
                AND t.out_status =any(#{outStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="outDateStart != null and outDateEnd != null">
                AND t.out_date &gt;= #{outDateStart}::timestamp with time zone
                AND t.out_date &lt; #{outDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="goodsSeriesList != null  and goodsSeriesList.size()>0">
                AND tmgs.series_id =
                any(#{goodsSeriesList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsCategoryList != null  and goodsCategoryList.size()>0">
                AND tmgs.category_id =
                any(#{goodsCategoryList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsBrandList != null  and goodsBrandList.size()>0">
                AND tmgs.brand_id =
                any(#{goodsBrandList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="skuName != null and skuName != ''">
                AND tpoi.sku_name like concat('%', my_ex.likequery(#{skuName}) , '%')
            </if>
            <if test="skuModel != null and skuModel != ''">
                AND tpoi.sku_model like concat('%', my_ex.likequery(#{skuModel}) , '%')
            </if>
            <if test="brandName != null and brandName != ''">
                AND tmgb.brand_name like concat('%', my_ex.likequery(#{brandName}) , '%')
            </if>
            <if test="whName != null and whName != ''">
                AND tmw.wh_name like concat('%', my_ex.likequery(#{whName}) , '%')
            </if>
            <if test="nonStdCode != null and nonStdCode != ''">
                AND tpi.non_std_code like concat('%', my_ex.likequery(#{nonStdCode}) , '%')
            </if>
            <if test="searchText !=null and searchText != ''">
                AND (
                t.out_no   LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_phone LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_name LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                )
            </if>
            <if test="whList != null  and whList.size()>0">
                AND tpi.wh_id =
                any(#{whList,typeHandler=UuidListTypeHandler})
            </if>
        </where>
    </sql>

    <!-- 出库明细报表明细条件列-入库类型-采退入库 -->
    <sql id="Condition_OutInboundDetailReport">
        <where>
            tpi.flg_valid
            <if test="cpId != null">
                AND tpi.cp_id = #{cpId}
            </if>
            <if test="outTypeRList != null and outTypeRList.size()>0">
                AND tpi.into_type =any(#{outTypeRList,typeHandler=StringListTypeHandler})
            </if>
            <if test="outTypeRList == null || outTypeRList.size()==0">
                AND tpi.into_type = '入库类型-采退入库'
            </if>
            <if test="outNo != null and outNo != ''">
                AND tpi.into_no LIKE concat('%',my_ex.likequery(#{outNo}),'%')
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND tpi.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>

            <if test="outTypeList != null and outTypeList.size()>0">
                AND tpi.into_type =any(#{outTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="fromNo != null and fromNo != ''">
                AND tpi.from_no LIKE concat('%',my_ex.likequery(#{fromNo}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND tpi.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND tpi.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="outStatusList != null and outStatusList.size() > 0">
                AND tpi.into_status =any(#{outStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="outDateStart != null and outDateEnd != null">
                AND tpi.out_date &gt;= #{outDateStart}::timestamp with time zone
                AND tpi.out_date &lt; #{outDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND tpi.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND tpi.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="goodsSeriesList != null  and goodsSeriesList.size()>0">
                AND tmgs.series_id =
                any(#{goodsSeriesList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsCategoryList != null  and goodsCategoryList.size()>0">
                AND tmgs.category_id =
                any(#{goodsCategoryList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsBrandList != null  and goodsBrandList.size()>0">
                AND tmgs.brand_id =
                any(#{goodsBrandList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="skuName != null and skuName != ''">
                AND tpii.sku_name like concat('%', my_ex.likequery(#{skuName}) , '%')
            </if>
            <if test="skuModel != null and skuModel != ''">
                AND tpii.sku_model like concat('%', my_ex.likequery(#{skuModel}) , '%')
            </if>
            <if test="brandName != null and brandName != ''">
                AND tmgb.brand_name like concat('%', my_ex.likequery(#{brandName}) , '%')
            </if>
            <if test="whName != null and whName != ''">
                AND tmw.wh_name like concat('%', my_ex.likequery(#{whName}) , '%')
            </if>
            <if test="nonStdCode != null and nonStdCode != ''">
                AND tpii.non_std_code like concat('%', my_ex.likequery(#{nonStdCode}) , '%')
            </if>
            <if test="searchText !=null and searchText != ''">
                AND (
                tpi.into_no   LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_phone LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_name LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.address_full LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                )
            </if>
            <if test="whList != null  and whList.size()>0">
                AND tpii.wh_id =
                any(#{whList,typeHandler=UuidListTypeHandler})
            </if>
        </where>
    </sql>

    <!-- 出库明细报表主表条件列 -->
    <sql id="Condition_OutboundReport">
        <where>
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="outNo != null and outNo != ''">
                AND t.out_no LIKE concat('%',my_ex.likequery(#{outNo}),'%')
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND t.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="outTypeList != null and outTypeList.size()>0">
                AND t.out_type =any(#{outTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="fromNo != null and fromNo != ''">
                AND t.from_no LIKE concat('%',my_ex.likequery(#{fromNo}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="outStatusList != null and outStatusList.size() > 0">
                AND t.out_status =any(#{outStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="outDateStart != null and outDateEnd != null">
                AND t.out_date &gt;= #{outDateStart}::timestamp with time zone
                AND t.out_date &lt; #{outDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>
    <!-- 入库明细报表明细条件列 -->
    <sql id="Condition_IntoboundDetailReport">
        <where>
            tpoi.flg_valid
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="intoNo != null and intoNo != ''">
                AND t.into_no LIKE concat('%',my_ex.likequery(#{outNo}),'%')
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND t.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="intoTypeList != null and intoTypeList.size()>0">
                AND t.into_type =any(#{intoTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="fromNo != null and fromNo != ''">
                AND t.from_no LIKE concat('%',my_ex.likequery(#{fromNo}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="intoStatusList != null and intoStatusList.size() > 0">
                AND t.into_status =any(#{intoStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="intoDateStart != null and intoDateEnd != null">
                AND t.into_date &gt;= #{intoDateStart}::timestamp with time zone
                AND t.into_date &lt; #{intoDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="goodsSeriesList != null  and goodsSeriesList.size()>0">
                AND tmgs.series_id =
                any(#{goodsSeriesList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsCategoryList != null  and goodsCategoryList.size()>0">
                AND tmgs.category_id =
                any(#{goodsCategoryList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="goodsBrandList != null  and goodsBrandList.size()>0">
                AND tmgs.brand_id =
                any(#{goodsBrandList,typeHandler=UuidListTypeHandler})
            </if>
            <if test="skuName != null and skuName != ''">
                AND tmgs.sku_name like concat('%', my_ex.likequery(#{skuName}) , '%')
            </if>
            <if test="skuModel != null and skuModel != ''">
                AND tmgs.sku_model like concat('%', my_ex.likequery(#{skuModel}) , '%')
            </if>
            <if test="whList != null  and whList.size()>0">
                AND tpi.wh_id =
                any(#{whList,typeHandler=UuidListTypeHandler})
            </if>
        </where>
    </sql>
    <!-- 入库明细报表主表条件列 -->
    <sql id="Condition_IntoboundReport">
        <where>
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="intoNo != null and intoNo != ''">
                AND t.into_no LIKE concat('%',my_ex.likequery(#{outNo}),'%')
            </if>
            <if test="supIdlist != null and supIdlist.size()>0">
                AND t.sup_id = any(#{supIdlist,typeHandler=UuidListTypeHandler})
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="intoTypeList != null and intoTypeList.size()>0">
                AND t.into_type =any(#{intoTypeList,typeHandler=StringListTypeHandler})
            </if>
            <if test="fromNo != null and fromNo != ''">
                AND t.from_no LIKE concat('%',my_ex.likequery(#{fromNo}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="intoStatusList != null and intoStatusList.size() > 0">
                AND t.into_status =any(#{intoStatusList, typeHandler=StringListTypeHandler})
            </if>
            <if test="intoDateStart != null and intoDateEnd != null">
                AND t.into_date &gt;= #{intoDateStart}::timestamp with time zone
                AND t.into_date &lt; #{intoDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>
    <!-- 客户收款明细表条件列 -->
    <sql id="Condition_RecPayDetailReport">
        <where>
            t.rp_type in ('收付款类型-收款', '收付款类型-退收款')
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="rpNo != null and rpNo != ''">
                AND t.rp_no LIKE concat('%',my_ex.likequery(#{rpNo}),'%')
            </if>
            <if test="contactPhone != null and contactPhone != ''">
                AND t.contact_phone LIKE concat('%',my_ex.likequery(#{contactPhone}),'%')
            </if>
            <if test="biznisNo != null and biznisNo != ''">
                AND t.biznis_no LIKE concat('%',my_ex.likequery(#{biznisNo}),'%')
            </if>
            <if test="cusName != null and cusName != ''">
                AND tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND t.org_id =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND t.staff_id =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffName != null and staffName != ''">
                AND tms.staff_name LIKE concat('%',my_ex.likequery(#{staffName}),'%')
            </if>
            <if test="orgName != null and orgName != ''">
                AND tmo.org_name LIKE concat('%',my_ex.likequery(#{orgName}),'%')
            </if>
            <if test="accDateStart != null and accDateEnd != null">
                AND t.acc_date &gt;= #{accDateStart}::timestamp with time zone
                AND t.acc_date &lt; #{accDateEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND t.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND t.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="searchText !=null and searchText != ''">
                AND (
                t.rp_no   LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_phone LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_name LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                )
            </if>
        </where>
    </sql>
    <!-- 采购明细报表主表 -->
    <select id="getPurReport" resultType="java.util.Map">
        select
        tpp.pur_id as "purId"
        ,tpp.pur_no as "purNo"
        ,tmo.org_name as "orgName"
        ,tms.staff_name as "staffName"
        ,tms1.staff_name as "makeStaffName"
        ,tmp.sup_name as "supplierName"
        ,tpp.pickup_date as "pickupDate"
        ,sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) as "purStatusName"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "purTypeName"
        ,tpp.contact_name as "contactName"
        ,tpp.contact_phone as "contactPhone"
        ,sys.f_remove_zero(tpp.intoing_qty) as "intoingQtySum"
        ,tpp.intoing_amt as "intoingAmtSum"
        ,sys.f_remove_zero(tpp.into_qty) as "intoQtySum"
        ,tpp.into_amt as "intoAmtSum"
        ,sys.f_remove_zero(tpp.return_qty) as "returnInQtySum"
        ,tpp.return_amt as "returnInAmtSum"
        ,tpp.amt_payable as "amtPayable"
        ,tpp.amt_residue as "amtResidue"
        ,tpp.make_time as "makeTime"
        ,tpp.remarks
        FROM  dkic_b.t_psi_purchase tpp
        Left join dkic_b.t_mst_org tmo on tpp.org_id = tmo.org_id
        Left join dkic_b.t_mst_supplier tmp on tpp.sup_id = tmp.sup_id
        Left join dkic_b.t_mst_staff tms on tpp.staff_id = tms.staff_id
        Left join dkic_b.t_mst_staff tms1 on tpp.make_staff = tms1.staff_id
        Left join dkic_b.t_mst_warehouse tmw on tpp.wh_id = tmw.wh_id
        left join sys.t_data_kind tdk2 on tdk2.kind_code = tpp.pur_status
        left join sys.t_data_kind tdk1 on tdk1.kind_code = tpp.pur_type
        <include refid="Condition_PurReport"/>
        order by tpp.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 采购明细报表主表Count-->
    <select id="getPurReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_purchase tpp
        Left join dkic_b.t_mst_org tmo on tpp.org_id = tmo.org_id
        Left join dkic_b.t_mst_supplier tmp on tpp.sup_id = tmp.sup_id
        Left join dkic_b.t_mst_staff tms on tpp.staff_id = tms.staff_id
        Left join dkic_b.t_mst_staff tms1 on tpp.make_staff = tms1.staff_id
        Left join dkic_b.t_mst_warehouse tmw on tpp.wh_id = tmw.wh_id
        left join sys.t_data_kind tdk2 on tdk2.kind_code = tpp.pur_status
        left join sys.t_data_kind tdk1 on tdk1.kind_code = tpp.pur_type
        <include refid="Condition_PurReport"/>
    </select>
    <!-- 采购明细报表明细 -->
    <select id="getPurDetailReport" resultType="java.util.Map">
        select
        tpp.pur_id as "purId"
        ,tpp.pur_no as "purNo"
        ,tmo.org_name as "orgName"
        ,tms.staff_name as "staffName"
        ,tms1.staff_name as "makeStaffName"
        ,tmp.sup_name as "supplierName"
        ,tpp.pickup_date as "pickupDate"
        ,sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) as "purStatusName"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "purTypeName"
        ,tpp.contact_name as "contactName"
        ,tpp.contact_phone as "contactPhone"
        ,sys.f_remove_zero(tpp.intoing_qty) as "intoingQtySum"
        ,tpp.intoing_amt as "intoingAmtSum"
        ,sys.f_remove_zero(tpp.into_qty) as "intoQtySum"
        ,tpp.into_amt as "intoAmtSum"
        ,sys.f_remove_zero(tpp.return_qty) as "returnQtySum"
        ,tpp.return_amt as "returnAmtSum"
        ,tpp.amt_payable as "amtPayable"
        ,tpp.amt_residue as "amtResidue"
        ,tpp.remarks
        ,tpo.order_no as "orderNo"
        ,ib.into_no as "intoNo"
        ,tmgs.sku_name as "skuName"
        ,tmgs.sku_model as "skuModel"
        ,tmgs.sku_spec as "skuSpec"
        ,tppi.non_std_code as "nonStdCode"
        ,tmw.wh_name as "whName"
        ,tmgb.short_name as "shortName"
        ,tmgb.brand_name as "brandName"
        ,tmgc.cat_name as "catName"
        ,series.series_name as "seriesName"
        , sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}) as "intoStatus"
        ,tppi.price_pur as "pricePur"
        ,sys.f_remove_zero(tppi.item_qty) as "itemQty"
        ,concat(sys.f_remove_zero(tppi.item_qty) ,tmgu.unit_name)  as "itemQtyPiece"
        ,tppi.item_amt as "itemAmt"
        ,sys.f_remove_zero(tppi.intoing_qty) as "intoingQty"
        ,tppi.intoing_amt as "intoingAmt"
        ,sys.f_remove_zero(tppi.into_qty) as "intoQty"
        ,tppi.into_amt as "intoAmt"
        ,sys.f_remove_zero(tppi.return_qty) as "returnQty"
        ,tppi.return_amt as "returnAmt"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,tppi.box,tppi.piece) as "boxPiece"
        FROM dkic_b.t_psi_purchase as tpp
        Left join dkic_b.t_mst_org as tmo on tpp.org_id = tmo.org_id
        Left join dkic_b.t_mst_supplier as tmp on tpp.sup_id = tmp.sup_id
        Left join dkic_b.t_mst_staff as tms  on tpp.staff_id = tms.staff_id
        Left join dkic_b.t_mst_staff as tms1  on tpp.make_staff = tms1.staff_id
        Left join dkic_b.t_mst_warehouse as tmw on tpp.wh_id = tmw.wh_id
        left join sys.t_data_kind as tdk2 on tdk2.kind_code = tpp.pur_status
        left join sys.t_data_kind tdk1 on tdk1.kind_code = tpp.pur_type
        left join dkic_b.t_psi_purchase_item as tppi on tppi.pur_id = tpp.pur_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tppi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_order as tpo on tpo.order_id = tppi.from_id
        left join dkic_b.t_psi_inbound_item as ibi on ibi.from_item_id=tppi.item_id
        left join dkic_b.t_psi_inbound as ib on ibi.into_id=ib.into_id
        left join sys.t_data_kind tdk3 on tdk3.kind_code = tppi.into_status
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
        <include refid="Condition_PurDetailReport"/>
        order by tpp.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 采购明细报表明细Count-->
    <select id="getPurDetailReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_purchase as tpp
        Left join dkic_b.t_mst_org as tmo on tpp.org_id = tmo.org_id
        Left join dkic_b.t_mst_supplier as tmp on tpp.sup_id = tmp.sup_id
        Left join dkic_b.t_mst_staff as tms  on tpp.staff_id = tms.staff_id
        Left join dkic_b.t_mst_staff as tms1  on tpp.make_staff = tms1.staff_id
        Left join dkic_b.t_mst_warehouse as tmw on tpp.wh_id = tmw.wh_id
        left join sys.t_data_kind as tdk2 on tdk2.kind_code = tpp.pur_status
        left join sys.t_data_kind tdk1 on tdk1.kind_code = tpp.pur_type
        left join dkic_b.t_psi_purchase_item as tppi on tppi.pur_id = tpp.pur_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tppi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_order as tpo on tpo.order_id = tppi.from_id
        left join dkic_b.t_psi_inbound_item as ibi on ibi.from_item_id=tppi.item_id
        left join dkic_b.t_psi_inbound as ib on ibi.into_id=ib.into_id
        left join sys.t_data_kind tdk3 on tdk3.kind_code = tppi.into_status
        <include refid="Condition_PurDetailReport"/>
    </select>
    <!-- 销售明细报表主表 -->
    <select id="getOrderReport" resultType="java.util.Map">
        SELECT
        t.order_id as "orderId",
        t.order_no as "orderNo",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "orderTypeName",
        tmo.org_name as "orgName",
        tms.staff_name as "staffName",
        tmc.cus_id as "cusId",
        tmc.cus_name as "cusName",
        tmc.cus_phone as "cusPhone",
        t.address_full as "addressFull",
        t.contact_name as "contactName",
        t.contact_phone as "contactPhone",
        t.sales_channel as "salesChannel",
        tmsc.channel_name as "channelName",
        t.delivery_date as "deliveryDate",
        sys.f_remove_zero(t.sum_quantity) as "sumQuantity",
        t.sum_standard as "sumStandard",
        t.sum_amount as "sumAmount",
        t.sale_discount as "saleDiscount",
        sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) as "orderStatusName",
--         t.amt_receivable as "amtReceivable",
--         t.amt_handle as "amtHandle",
--         t.amt_residue as "amtResidue",
        t.remarks,
        makestaff.staff_name as "makeStaffName",
        t.make_time as "makeTime"
        FROM dkic_b.t_psi_order as t
        left join dkic_b.t_mst_sale_channel as tmsc on t.sales_channel = tmsc.channel_id
        left join dkic_b.t_mst_org as tmo on t.org_id = tmo.org_id
        left join dkic_b.t_mst_staff as tms on t.staff_id = tms.staff_id
        left join dkic_b.t_mst_staff as makestaff on t.make_staff = makestaff.staff_id
        left join dkic_b.t_mst_customer as tmc on t.cus_id = tmc.cus_id
        left join dkic_b.t_mst_dictionary_data as tmdd on tmc.cus_from = tmdd.data_id
        left join sys.t_data_kind as tdk1 on t.order_type = tdk1.kind_code
        left join sys.t_data_kind as tdk2 on t.order_status = tdk2.kind_code
        <include refid="Condition_OrdReport"/>
        order by t.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 销售明细报表主表Count-->
    <select id="getOrderReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_order as t
        left join dkic_b.t_mst_sale_channel as tmsc on t.sales_channel = tmsc.channel_id
        left join dkic_b.t_mst_org as tmo on t.org_id = tmo.org_id
        left join dkic_b.t_mst_staff as tms on t.staff_id = tms.staff_id
        left join dkic_b.t_mst_staff as makestaff on t.make_staff = makestaff.staff_id
        left join dkic_b.t_mst_customer as tmc on t.cus_id = tmc.cus_id
        left join dkic_b.t_mst_dictionary_data as tmdd on tmc.cus_from = tmdd.data_id
        left join sys.t_data_kind as tdk1 on t.order_type = tdk1.kind_code
        left join sys.t_data_kind as tdk2 on t.order_status = tdk2.kind_code
        <include refid="Condition_OrdReport"/>
    </select>
    <!-- 销售明细报表明细 -->
    <select id="getOrderDetailReport" resultType="java.util.Map">
        select
        t.order_id as "orderId"
        ,t.order_no as "orderNo"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "orderTypeName"
        ,tmo.org_name as "orgName"
        ,tms.staff_name as "staffName"
        ,tmc.cus_id as "cusId"
        ,tmc.cus_name as "cusName"
        ,tmc.cus_phone as "cusPhone"
        ,t.address_full as "addressFull"
        ,t.contact_name as "contactName"
        ,t.contact_phone as "contactPhone"
        ,t.sales_channel as "salesChannel"
        ,tmsc.channel_name as "channelName"
        ,t.delivery_date as "deliveryDate"
        ,sys.f_remove_zero(t.sum_quantity) as "sumQuantity"
        ,t.sum_standard as "sumStandard"
        ,t.sum_amount as "sumAmount"
        ,t.sale_discount as "saleDiscount"
        ,sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) as "orderStatusName"
--          ,t.amt_receivable as "amtReceivable"
--          ,t.amt_handle as "amtHandle"
--          ,t.amt_residue as "amtResidue"
        ,t.remarks
        ,makestaff.staff_name as "makeStaffName"
        ,t.make_time as "makeTime"
        ,tmgs.sku_name as "skuName"
        ,tmgs.sku_model as "skuModel"
        ,tmgs.sku_spec as "skuSpec"
        ,toi.non_std_code as "nonStdCode"
        ,tmgb.short_name as "shortName"
        ,tmgb.brand_name as "brandName"
        ,tmw.wh_name as "whName"
        ,tmgc.cat_name as "catName"
        ,series.series_name as "seriesName"
        ,toi.price_std as "priceStd"
        ,sys.f_remove_zero(toi.item_qty) as "itemQty"
        ,concat(sys.f_remove_zero(toi.item_qty) ,tmgu.unit_name)  as "itemQtyPiece"
        ,toi.amt_std as "amtStd"
        ,toi.price_sale as "priceSale"
        ,toi.item_amt as "itemAmt"
        ,toi.price_discount as "priceDiscount"
        ,tpo.out_no as "outNo"
        ,sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}) AS "outStatusName"
        ,sys.f_remove_zero(toi.outing_qty) as "outingQty"
        ,toi.outing_amt as "outingAmt"
        ,sys.f_remove_zero(toi.out_qty) as "outQty"
        ,toi.out_amt as "outAmt"
        ,sys.f_remove_zero(toi.return_qty) as "returnQty"
        ,toi.return_amt as "returnAmt"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,toi.box,toi.piece) as "boxPiece"
        FROM dkic_b.t_psi_order as t
        left join dkic_b.t_mst_sale_channel tmsc on t.sales_channel = tmsc.channel_id
        left join dkic_b.t_mst_org tmo on t.org_id = tmo.org_id
        left join dkic_b.t_mst_staff tms on t.staff_id = tms.staff_id
        left join dkic_b.t_mst_staff as makestaff on t.make_staff = makestaff.staff_id
        left join dkic_b.t_mst_customer tmc on t.cus_id = tmc.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmc.cus_from = tmdd.data_id
        left join sys.t_data_kind as tdk1 on t.order_type = tdk1.kind_code
        left join sys.t_data_kind as tdk2 on t.order_status = tdk2.kind_code
        left join dkic_b.t_psi_order_item as toi on toi.order_id = t.order_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = toi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join sys.t_data_kind as tdk3 on tdk3.kind_code = toi.out_status
        left join dkic_b.t_psi_outbound as tpo on tpo.from_id=t.order_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = toi.wh_id
        <include refid="Condition_OrdDetailReport"/>
        order by toi.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 销售明细报表明细Count-->
    <select id="getOrderDetailReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_order as t
        left join dkic_b.t_mst_sale_channel tmsc on t.sales_channel = tmsc.channel_id
        left join dkic_b.t_mst_org tmo on t.org_id = tmo.org_id
        left join dkic_b.t_mst_staff tms on t.staff_id = tms.staff_id
        left join dkic_b.t_mst_staff as makestaff on t.make_staff = makestaff.staff_id
        left join dkic_b.t_mst_customer tmc on t.cus_id = tmc.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmc.cus_from = tmdd.data_id
        left join sys.t_data_kind as tdk1 on t.order_type = tdk1.kind_code
        left join sys.t_data_kind as tdk2 on t.order_status = tdk2.kind_code
        left join dkic_b.t_psi_order_item as toi on toi.order_id = t.order_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = toi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join sys.t_data_kind as tdk3 on tdk3.kind_code = toi.out_status
        left join dkic_b.t_psi_outbound as tpo on tpo.from_id=t.order_id
        <include refid="Condition_OrdDetailReport"/>
    </select>
    <!-- 出库明细报表主表 -->
    <select id="getOutboundReport" resultType="java.util.Map">
        SELECT
        t.out_id as "outId",
        t.out_no as "outNo",
        t.from_no as "fromNo",
        tmo.org_name as "orgName",
        tms.staff_name as "staffName",
        tmc.cus_id as "cusId",
        tmc.cus_name as "cusName",
        tmc.cus_phone as "cusPhone",
        tmc.cus_from as "cusFrom",
        tmc.contact_phone as "contactPhone",
        tmc.address_full as "addressFull",
        tmdd.data_value as cus_from_name,
        tmp.sup_name as "supplierName",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "outStatusName",
        sys.f_get_name_i18n(tdktype.kind_name_i18n, #{i18n}) as "outTypeName",
        sys.f_remove_zero(t.outing_qty) as "outingQtySum",
        t.outing_amt as "outingAmtSum",
        sys.f_remove_zero(t.out_qty) as "outQtySum",
        t.out_amt as "outAmtSum",
        sys.f_remove_zero(t.return_qty) as "returnOutQtySum",
        t.return_amt as "returnOutAmtSum",
        t.out_date as "outDate",
        t.make_time as "makeTime",
        t.remarks,
        makestaff.staff_name as "makeStaffName"
        FROM dkic_b.t_psi_outbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.out_status
        LEFT JOIN sys.t_data_kind tdktype ON tdktype.kind_code = t.out_type
        <include refid="Condition_OutboundReport"/>
        order by t.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 出库明细报表主表Count-->
    <select id="getOutboundReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_outbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.out_status
        LEFT JOIN sys.t_data_kind tdktype   ON tdktype.kind_code = t.out_type
        <include refid="Condition_OutboundReport"/>
    </select>
    <!-- 出库明细报表明细 -->
    <select id="getOutboundDetailReport" resultType="java.util.Map">
        select * from (
        select
        t.out_id as "outId"
        ,t.out_no as "outNo"
        ,t.from_no as "fromNo"
        ,tmo.org_name as "orgName"
        ,tms.staff_name as "staffName"
        ,tmc.cus_id as "cusId"
        ,tmc.cus_name as "cusName"
        ,tmc.cus_phone as "cusPhone"
        ,tmc.cus_from as "cusFrom"
        ,tmc.contact_phone as "contactPhone"
        ,tmc.address_full as "addressFull"
        ,tmdd.data_value as cus_from_name
        ,tmp.sup_name as "supplierName"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "outStatusName"
        ,sys.f_get_name_i18n(tdktype.kind_name_i18n, #{i18n}) as "outTypeName"
        ,sys.f_remove_zero(t.outing_qty) as "outingQtySum"
        ,t.outing_amt as "outingAmtSum"
        ,sys.f_remove_zero(t.out_qty) as "outQtySum"
        ,t.out_amt as "outAmtSum"
        ,sys.f_remove_zero(t.return_qty) as "returnOutQtySum"
        ,t.return_amt as "returnOutAmtSum"
        ,t.out_date as "outDate"
        ,t.remarks
        ,makestaff.staff_name as "makeStaffName"
        ,tpoi.sku_name as "skuName"
        ,tpoi.sku_model as "skuModel"
        ,tmgs.sku_spec as "skuSpec"
        ,tpi.non_std_code as "nonStdCode"
        ,tmgb.short_name as "shortName"
        ,tmgb.brand_name as "brandName"
        ,CASE WHEN tpoi.sku_id IS NULL THEN '外协' ELSE tmw.wh_name end as "whName"
        ,tmgc.cat_name as "catName"
        ,series.series_name as "seriesName"
        ,sys.f_remove_zero(tpoi.outing_qty) as "outingQty"
        ,concat(sys.f_remove_zero(tpoi.outing_qty),tmgu.unit_name) as "outingQtyPiece"
        ,tpoi.outing_amt as "outingAmt"
        ,concat(sys.f_remove_zero(tpoi.out_qty),tmgu.unit_name) as "outQtyPiece"
        ,sys.f_remove_zero(tpoi.out_qty) as "outQty"
        ,tpoi.out_amt as "outAmt"
        ,tpoi.fact_amt as "factAmt"
        ,tpoi.disc_amt as "discAmt"
        ,tpoi.discount as "discount"
        ,sys.f_remove_zero(tpoi.return_qty) as "returnQty"
        ,tpoi.return_amt as "returnAmt"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,tpoi.outing_box,tpoi.outing_piece) as "outingBoxPiece"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,tpoi.out_box,tpoi.out_piece) as "outBoxPiece"
        ,t.op_update_time
        FROM dkic_b.t_psi_outbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.out_status
        left join sys.t_data_kind tdktype ON tdktype.kind_code = t.out_type
        left join dkic_b.t_psi_outbound_item as tpoi on tpoi.out_id = t.out_id
        left join dkic_b.t_mst_supplier tmsp on tpoi.sup_id = tmsp.sup_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpoi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_inventory as tpi on tpi.inv_id = tpoi.inv_id
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
        <include refid="Condition_OutboundDetailReport"/>
        union all
        select
        tpi.into_id as "outId"
        ,tpi.into_no as "outNo"
        ,tpi.from_no as "fromNo"
        ,tmo.org_name as "orgName"
        ,tms.staff_name as "staffName"
        ,tmc.cus_id as "cusId"
        ,tmc.cus_name as "cusName"
        ,tmc.cus_phone as "cusPhone"
        ,tmc.cus_from as "cusFrom"
        ,tmc.contact_phone as "contactPhone"
        ,tmc.address_full as "addressFull"
        ,tmdd.data_value as cus_from_name
        ,tmp.sup_name as "supplierName"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "outStatusName"
        ,sys.f_get_name_i18n(tdktype.kind_name_i18n, #{i18n}) as "outTypeName"
        ,sys.f_remove_zero(tpi.intoing_qty) as "outingQtySum"
        ,tpi.intoing_amt as "outingAmtSum"
        ,sys.f_remove_zero(tpi.into_qty) as "outQtySum"
        ,tpi.into_amt as "outAmtSum"
        ,sys.f_remove_zero(tpi.return_qty) as "returnOutQtySum"
        ,tpi.return_amt as "returnOutAmtSum"
        ,tpi.into_date as "outDate"
        ,tpi.remarks
        ,makestaff.staff_name as "makeStaffName"
        ,tpii.sku_name as "skuName"
        ,tpii.sku_model as "skuModel"
        ,tmgs.sku_spec as "skuSpec"
        ,tpii.non_std_code as "nonStdCode"
        ,tmgb.short_name as "shortName"
        ,tmgb.brand_name as "brandName"
        ,tmw.wh_name as "whName"
        ,tmgc.cat_name as "catName"
        ,series.series_name as "seriesName"
        ,sys.f_remove_zero(tpii.intoing_qty) as "outingQty"
        ,concat(sys.f_remove_zero(abs(tpii.intoing_qty)),tmgu.unit_name) as "outingQtyPiece"
        ,tpii.intoing_amt as "outingAmt"
        ,concat(sys.f_remove_zero(abs(tpii.into_qty)),tmgu.unit_name) as "outQtyPiece"
        ,sys.f_remove_zero(tpii.into_qty) as "outQty"
        ,tpii.into_amt as "outAmt"
        ,tpii.into_amt+tpii.intoing_amt as "factAmt"
        ,0.00 as "discAmt"
        ,0.00 as "discount"
        ,sys.f_remove_zero(tpii.return_qty) as "returnQty"
        ,tpii.return_amt as "returnAmt"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,abs(tpii.intoing_box),abs(tpii.intoing_piece)) as "outingBoxPiece"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,abs(tpii.into_box),abs(tpii.into_piece)) as "outBoxPiece"
        ,tpi.op_update_time
        FROM dkic_b.t_psi_inbound as tpi
        left join dkic_b.t_mst_org tmo on tmo.org_id = tpi.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = tpi.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = tpi.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = tpi.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = tpi.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = tpi.into_status
        left join sys.t_data_kind tdktype ON tdktype.kind_code = tpi.into_type
        left join dkic_b.t_psi_inbound_item as tpii on tpii.into_id = tpi.into_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpii.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_inventory as tpin on tpin.inv_id = tpii.inv_id
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
        <include refid="Condition_OutInboundDetailReport"/>
        ) tt
        order by tt.op_update_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 出库明细报表明细Count-->
    <select id="getOutboundDetailReportCount" resultType="java.lang.Long">
        SELECT
        count(1) from (
            SELECT 1
            FROM dkic_b.t_psi_outbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.out_status
        left join sys.t_data_kind tdktype ON tdktype.kind_code = t.out_type
        left join dkic_b.t_psi_outbound_item as tpoi on tpoi.out_id = t.out_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpoi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_inventory as tpi on tpi.inv_id = tpoi.inv_id
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
            <include refid="Condition_OutboundDetailReport"/>
            union all
            select
            1
            FROM dkic_b.t_psi_inbound as tpi
        left join dkic_b.t_mst_org tmo on tmo.org_id = tpi.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = tpi.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = tpi.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = tpi.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = tpi.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = tpi.into_status
        left join sys.t_data_kind tdktype ON tdktype.kind_code = tpi.into_type
        left join dkic_b.t_psi_inbound_item as tpii on tpii.into_id = tpi.into_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpii.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_inventory as tpin on tpin.inv_id = tpii.inv_id
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
            <include refid="Condition_OutInboundDetailReport"/>
        ) t
    </select>
    <!-- 入库明细报表主表 -->
    <select id="getInboundlReport" resultType="java.util.Map">
        SELECT
        t.into_id as "intoId",
        t.into_no as "intoNo",
        t.from_no as "fromNo",
        tmo.org_name as "orgName",
        tms.staff_name as "staffName",
        tmc.cus_id as "cusId",
        tmc.cus_name as "cusName",
        tmc.cus_phone as "cusPhone",
        tmc.cus_from as "cusFrom",
        tmc.contact_phone as "contactPhone",
        tmc.address_full as "addressFull",
        tmdd.data_value as cus_from_name,
        tmp.sup_name as "supplierName",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "intoStatusName",
        sys.f_get_name_i18n(tdktype.kind_name_i18n, #{i18n}) as "intoTypeName",
        sys.f_remove_zero(t.intoing_qty) as "intoingQtySum",
        t.intoing_amt as "intoingAmtSum",
        sys.f_remove_zero(t.into_qty) as "intoQtySum",
        t.into_amt as "intoAmtSum",
        sys.f_remove_zero(t.return_qty) as "returnintoQtySum",
        t.return_amt as "returnintoAmtSum",
        t.into_date as "intoDate",
        tmw.wh_name as "whName",
        t.make_time as "makeTime",
        t.remarks,
        makestaff.staff_name as "makeStaffName"
        FROM dkic_b.t_psi_inbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.into_status
        left join sys.t_data_kind tdktype ON tdktype.kind_code = t.into_type
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = t.wh_id
        <include refid="Condition_IntoboundReport"/>
        order by t.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 入库明细报表主表Count-->
    <select id="getInboundlReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_inbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.into_status
        left join sys.t_data_kind tdktype   ON tdktype.kind_code = t.into_type
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = t.wh_id
        <include refid="Condition_IntoboundReport"/>
    </select>
    <!-- 入库明细报表明细 -->
    <select id="getInboundDetailReport" resultType="java.util.Map">
        select
        t.into_id as "intoId"
        ,t.into_no as "intoNo"
        ,t.from_no as "fromNo"
        ,tmo.org_name as "orgName"
        ,tms.staff_name as "staffName"
        ,tmc.cus_id as "cusId"
        ,tmc.cus_name as "cusName"
        ,tmc.cus_phone as "cusPhone"
        ,tmc.cus_from as "cusFrom"
        ,tmc.contact_phone as "contactPhone"
        ,tmc.address_full as "addressFull"
        ,tmdd.data_value as cus_from_name
        ,tmp.sup_name as "supplierName"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "intoStatusName"
        ,sys.f_get_name_i18n(tdktype.kind_name_i18n, #{i18n}) as "intoTypeName"
        ,sys.f_remove_zero(t.intoing_qty) as "intoingQtySum"
        ,t.intoing_amt as "intoingAmtSum"
        ,sys.f_remove_zero(t.into_qty) as "intoQtySum"
        ,t.into_amt as "intoAmtSum"
        ,sys.f_remove_zero(t.return_qty) as "returnInQtySum"
        ,t.return_amt as "returnInAmtSum"
        ,t.into_date as "intoDate"
        ,t.remarks
        ,makestaff.staff_name as "makeStaffName"
        ,tmgs.sku_name as "skuName"
        ,tmgs.sku_model as "skuModel"
        ,tmgs.sku_spec as "skuSpec"
        ,tpi.non_std_code as "nonStdCode"
        ,tmgb.short_name as "shortName"
        ,tmgb.brand_name as "brandName"
        ,tmw.wh_name as "whName"
        ,tmgc.cat_name as "catName"
        ,series.series_name as "seriesName"
        ,sys.f_remove_zero(tpoi.intoing_qty) as "intoingQty"
        ,concat(sys.f_remove_zero(tpoi.intoing_qty),tmgu.unit_name)  as "intoingQtyPiece"
        ,tpoi.intoing_amt as "intoingAmt"
        ,sys.f_remove_zero(tpoi.into_qty) as "intoQty"
        ,concat(sys.f_remove_zero(tpoi.into_qty),tmgu.unit_name)  as "intoQtyPiece"
        ,tpoi.into_amt as "intoAmt"
        ,sys.f_remove_zero(tpoi.return_qty) as "returnQty"
        ,tpoi.return_amt as "returnAmt"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,tpoi.intoing_box,tpoi.intoing_piece) as "intoingBoxPiece"
        ,dkic_b.f_box_piece(tmgus.unit_name,tmgu.unit_name,tpoi.into_box,tpoi.into_piece) as "intoBoxPiece"
        FROM dkic_b.t_psi_inbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.into_status
        LEFT JOIN sys.t_data_kind tdktype ON tdktype.kind_code = t.into_type
        left join dkic_b.t_psi_inbound_item as tpoi on tpoi.into_id = t.into_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpoi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_inventory as tpi on tpi.inv_id = tpoi.inv_id
        left join dkic_b.t_mst_unit as tmgu on tmgs.unit_id = tmgu.unit_id
        left join dkic_b.t_mst_unit as tmgus on tmgs.sub_unit_id = tmgus.unit_id
        left join dkic_b.t_mst_warehouse as tmw on tmw.wh_id = tpi.wh_id
        <include refid="Condition_IntoboundDetailReport"/>
        order by tpoi.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 入库明细报表明细Count-->
    <select id="getInboundDetailReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_inbound as t
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        Left join dkic_b.t_mst_supplier tmp on tmp.sup_id = t.sup_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.cus_id
        left join dkic_b.t_mst_dictionary_data tmdd on tmdd.data_id = tmc.cus_from
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.into_status
        LEFT JOIN sys.t_data_kind tdktype   ON tdktype.kind_code = t.into_type
        left join dkic_b.t_psi_inbound_item as tpoi on tpoi.into_id = t.into_id
        left join dkic_b.t_mst_goods_sku as tmgs on tmgs.sku_id = tpoi.sku_id
        left join dkic_b.t_mst_goods_brand as tmgb on tmgb.brand_id = tmgs.brand_id
        left join dkic_b.t_mst_goods_category as tmgc on tmgc.cat_id = tmgs.category_id
        left join dkic_b.t_mst_goods_series as series on series.series_id = tmgs.series_id
        left join dkic_b.t_psi_inventory as tpi on tpi.inv_id = tpoi.inv_id
        <include refid="Condition_IntoboundDetailReport"/>
    </select>
    <!-- 客户收款明细报表主表 -->
    <select id="getRecPayDetailReport" resultType="java.util.Map">
        SELECT
        t.rp_no as "rpNo",
        t.biznis_no as "biznisNo",
        tmo.org_name as "orgName",
        tms.staff_name as "staffName",
        tmc.cus_id as "cusId",
        tmc.cus_name as "cusName",
        tmc.cus_phone as "cusPhone",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) as "rpTypeName",
        tmma.mac_name as "macName",
        tmrpi.amt_rec as "amtRec",
        t.acc_date as "accDate",
        t.remarks,
        makestaff.staff_name as "makeStaffName"
        FROM dkic_b.t_mac_rec_pay_item as tmrpi
        left join dkic_b.t_mac_rec_pay t on tmrpi.rp_id = t.rp_id
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.object_id
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.rp_type
        left join dkic_b.t_mst_money_account tmma on tmma.mac_id = tmrpi.mac_id
        <include refid="Condition_RecPayDetailReport"/>
        order by t.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <!-- 客户收款明细报表主表Count-->
    <select id="getRecPayDetailReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_mac_rec_pay_item as tmrpi
        left join dkic_b.t_mac_rec_pay t on tmrpi.rp_id = t.rp_id
        left join dkic_b.t_mst_org tmo on tmo.org_id = t.org_id
        left join dkic_b.t_mst_staff tms on tms.staff_id = t.staff_id
        left join dkic_b.t_mst_staff as makestaff on makestaff.staff_id = t.make_staff
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = t.object_id
        left join sys.t_data_kind as tdk1 on tdk1.kind_code = t.rp_type
        left join dkic_b.t_mst_money_account tmma on tmma.mac_id = tmrpi.mac_id
        <include refid="Condition_RecPayDetailReport"/>
    </select>
    <sql id="ConditionPurTrackingReport">
        <where>
            <if test="cpId != null">
                tpp.cp_id = #{cpId}
            </if>
            <if test="staffIds != null and staffIds.size()>0">
                AND tpp.staff_id = any(#{staffIds, typeHandler=uuidListTypeHandler})
            </if>
            <if test="orgIds != null and orgIds.size() > 0">
                AND tpp.org_id = any(#{orgIds, typeHandler=uuidListTypeHandler})
            </if>
        </where>
    </sql>
    <!--  销售跟踪表 列表查询 -->
    <select id="getSalesTrackingReport" resultType="java.util.Map">
        SELECT
            tpo.order_id AS "orderId"
             ,tpo.order_no AS "orderNo"
             ,tpo.order_type AS "orderType"
             ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) AS "orderTypeName"
             ,sys.f_remove_zero(tpo.sum_quantity) AS "sumQuantity"
             ,tpo.sum_amount AS "sumAmount"
             ,tpo.sum_standard AS "sumStandard"
             ,tpo.sale_discount AS "saleDiscount"
             ,tpo.order_status AS "orderStatus"
             ,sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) as "orderStatusName"
             ,tpo.out_status AS "outStatus"
             ,sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}) as "outStatusName"
             ,sys.f_remove_zero(tpo.outing_qty) AS "outingQty"
             ,tpo.outing_amt AS "outingAmt"
             ,sys.f_remove_zero(tpo.out_qty) AS "outQty"
             ,tpo.out_amt AS "outAmt"
             ,sys.f_remove_zero(tpo.return_qty) AS "returnQty"
             ,tpo.return_amt AS "returnAmt"
--              ,tpo.amt_receivable AS "amtReceivable"
--              ,tpo.amt_handle AS "amtHandle"
--              ,tpo.amt_residue AS "amtResidue"
             ,tpo.make_time AS "makeTime"
            ,tmc.cus_code AS "cusCode"
            ,tmc.cus_name  As "cusName"
            ,tmc.cus_phone  As "cusPhone"
            ,tpo.address_area AS "addressArea"
            ,tpo.address_name AS "addressName"
            ,tpo.address_no AS "addressNo"
            ,tpo.address_gcj02 AS "addressGcj02"
            ,tpo.address_full AS "addressFull"
            ,tpo.contact_name AS "contactName"
            ,tpo.contact_phone AS "contactPhone"
            ,tpo.sales_channel AS "salesChannel"
            ,tpo.make_time AS "makeTime"
            ,tms.staff_name as "staffName"
            ,tmo.org_name   as "orgName"
            ,tmrp.sum_amt_rec AS "sumAmtRec"
            ,tmrp.sum_should_handle AS "sumShouldHandle"
            ,tmrp.sum_use_payment_residue  AS "sumUsePaymentResidue"
            ,tmrp.sum_waive_amt AS "sumWaiveAmt"
        FROM
            dkic_b.t_psi_order tpo
        left join dkic_b.t_mst_customer tmc on tpo.cus_id = tmc.cus_id
        left join sys.t_data_kind as tdk1 on tpo.order_type = tdk1.kind_code
        left join sys.t_data_kind as tdk2 on tpo.order_status = tdk2.kind_code
        left join sys.t_data_kind as tdk3 on tpo.out_status = tdk3.kind_code
        left join dkic_b.t_mst_staff tms on tpo.staff_id = tms.staff_id
        left join dkic_b.t_mst_org tmo on tpo.org_id = tmo.org_id
        left join dkic_b.t_mac_rec_pay tmrp on  tmrp.biznis_id = tpo.order_id
        <include refid="ConditionSalesTrackingReport"/>
        <if test="sort!=null and sort == 'balanceDesc'">
            ORDER BY  tpo.make_time  desc
        </if>
        <if test="sort!=null and sort == 'balanceAsc'">
            ORDER BY  tpo.make_time  asc
        </if>
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>
    <sql id="ConditionSalesTrackingReport">
        <where>
            <if test="orderNo != null">
                tpo.order_no = #{orderNo}
            </if>
            <if test="cusName != null">
                tmc.cus_name  = #{cusName}
            </if>
            <if test="cpId != null">
                tpo.cp_id = #{cpId}
            </if>
            <if test="createtimeStart != null and createtimeEnd != null">
                AND tpo.op_create_time &gt;= #{createtimeStart}::timestamp with time zone
                AND tpo.op_create_time &lt; #{createtimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if  test="searchText != null">
                AND (tpo.order_no  like concat('%', my_ex.likequery(#{searchText}) , '%')
                OR tmc.cus_code   like concat('%', my_ex.likequery(#{searchText}) , '%')
                OR tmc.cus_name   like concat('%', my_ex.likequery(#{searchText}) , '%')
                OR tmc.cus_phone   like concat('%', my_ex.likequery(#{searchText}) , '%')
                OR tpo.address_full   like concat('%', my_ex.likequery(#{searchText}) , '%')
                OR  tpo.remarks like concat('%', my_ex.likequery(#{searchText}) , '%')
                )
            </if>
        </where>
    </sql>
    <!--  销售明细查询 -->
    <select id="getSalesItemReport" resultType="java.util.Map">
        SELECT
            t.item_id AS "itemId",
            t.order_id AS "orderId",
            t.item_index AS "itemIndex",
            t.sku_id AS "skuId",
            tmgs.sku_code  as "skuCode",
            t.sku_model as "skuModel",
            t.sku_name  as "skuName",
            tmgb.brand_name AS "brandName",
            t.item_qty AS "itemQty",
            sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}) as "outStatusName",
            t.price_std AS "priceStd",
            t.amt_std AS "amtStd",
            t.price_sale AS "priceSale",
            t.item_amt AS "itemAmt",
            t.price_discount AS "priceDiscount",
            t.non_std_code AS "nonStdCode",
            t.out_status AS "outStatus",
        sys.f_remove_zero(t.outing_qty) AS "outingQty",
            t.outing_amt AS "outingAmt",
        sys.f_remove_zero(t.out_qty) AS "outQty",
            t.out_amt AS "outAmt",
        sys.f_remove_zero(t.return_qty) AS "returnQty",
            t.return_amt AS "returnAmt",
            t.remarks,
            t.wh_id AS "whId",
            tmw.wh_name AS "whName",
            tpo.order_no AS "orderNo",
            tpo.make_time AS "makeTime",
             tmc.cus_id AS "cusId",
             tmc.cus_code AS "cusCode",
             tmc.cus_name AS "cusName",
             tmc.cus_phone AS "cusPhone",
        tmgs.sku_spec as "skuSpec",
        concat ( sys.f_remove_zero ( T.item_qty ), tmgu.unit_name ) AS "itemQtyPiece",
        dkic_b.f_box_piece ( tmgus.unit_name, tmgu.unit_name, T.BOX, T.piece ) AS "boxPiece"
        FROM dkic_b.t_psi_order_item as t
        inner join dkic_b.t_psi_order tpo on tpo.order_id = t.order_id
        left join dkic_b.t_mst_goods_sku tmgs on t.sku_id = tmgs.sku_id
        left join dkic_b.t_mst_warehouse tmw on tmw.wh_id = t.wh_id
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = tpo.cus_id
        LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
        left join sys.t_data_kind as tdk3 on t.out_status = tdk3.kind_code
        LEFT JOIN dkic_b.t_mst_unit AS tmgu ON tmgs.unit_id = tmgu.unit_id
        LEFT JOIN dkic_b.t_mst_unit AS tmgus ON tmgs.sub_unit_id = tmgus.unit_id
        <include refid="ConditionSaleItem"/>
        order by tpo.make_time,t.item_index
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>

    </select>
    <sql id="ConditionSaleItem">
        <where>
            <if test="orderId != null and orderId != ''">
                AND t.order_id = #{orderId}::uuid
            </if>
            <if test="itemIndex != null">
                AND t.item_index = #{itemIndex}
            </if>
            <if test="skuId != null and skuId != ''">
                AND t.sku_id = #{skuId}
            </if>
            <if test="itemQty != null">
                AND t.item_qty = #{itemQty}
            </if>
            <if test="priceStd != null">
                AND t.price_std = #{priceStd}
            </if>
            <if test="amtStd != null">
                AND t.amt_std = #{amtStd}
            </if>
            <if test="priceSale != null">
                AND t.price_sale = #{priceSale}
            </if>
            <if test="itemAmt != null">
                AND t.item_amt = #{itemAmt}
            </if>
            <if test="priceDiscount != null">
                AND t.price_discount = #{priceDiscount}
            </if>
            <if test="nonStdCode != null and nonStdCode != ''">
                AND t.non_std_code = #{nonStdCode}
            </if>
            <if test="outStatus != null and outStatus != ''">
                AND t.out_status = #{outStatus}
            </if>
            <if test="outingQty != null">
                AND t.outing_qty = #{outingQty}
            </if>
            <if test="outingAmt != null">
                AND t.outing_amt = #{outingAmt}
            </if>
            <if test="outQty != null">
                AND t.out_qty = #{outQty}
            </if>
            <if test="outAmt != null">
                AND t.out_amt = #{outAmt}
            </if>
            <if test="returnQty != null">
                AND t.return_qty = #{returnQty}
            </if>
            <if test="returnAmt != null">
                AND t.return_amt = #{returnAmt}
            </if>
            <if test="remarks != null and remarks != ''">
                AND t.remarks = #{remarks}
            </if>
            <if test="flgValid != null">
                AND t.flg_valid = #{flgValid}
            </if>
            <if test="cpId != null">
                AND t.cp_id = #{cpId}
            </if>
            <if test="makeTimeStart != null and makeTimeEnd != null">
                AND tpo.make_time &gt;= #{makeTimeStart}::timestamp with time zone
                AND tpo.make_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
            </if>
            <if test="skuName != null and skuName != ''">
                AND t.sku_name like concat('%', my_ex.likequery(#{skuName}) , '%')
            </if>
            <if test="skuModel != null and skuModel != ''">
                AND t.sku_model like concat('%', my_ex.likequery(#{skuModel}) , '%')
            </if>
            <if test="brandName != null and brandName != ''">
                AND tmgb.brand_name like concat('%', my_ex.likequery(#{brandName}) , '%')
            </if>
            <if test="whName != null and whName != ''">
                AND tmw.wh_name like concat('%', my_ex.likequery(#{whName}) , '%')
            </if>
            <if test="nonStdCode != null and nonStdCode != ''">
                AND t.non_std_code like concat('%', my_ex.likequery(#{nonStdCode}) , '%')
            </if>
            <if test="searchText !=null and searchText != ''">
                AND (
                tpo.order_no   LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_phone LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                or tmc.cus_name LIKE concat('%',my_ex.likequery(#{searchText}),'%')
                )
            </if>
            <if test="isFlgGift != null and isFlgGift == true">
                AND t.flg_gift
            </if>
            <if test="isExtCo != null and isExtCo == true">
                AND t.sku_id  is null
            </if>
        </where>
    </sql>
    <select id="getSalesItemReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM
         dkic_b.t_psi_order_item t
        inner join dkic_b.t_psi_order tpo on tpo.order_id = t.order_id
        inner join dkic_b.t_mst_goods_sku tmgs on t.sku_id = tmgs.sku_id
        left join dkic_b.t_mst_warehouse tmw on tmw.wh_id = t.wh_id
        left join dkic_b.t_mst_customer tmc on tmc.cus_id = tpo.cus_id
        LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
        left join sys.t_data_kind as tdk3 on tpo.out_status = tdk3.kind_code
        LEFT JOIN dkic_b.t_mst_unit AS tmgu ON tmgs.unit_id = tmgu.unit_id
        LEFT JOIN dkic_b.t_mst_unit AS tmgus ON tmgs.sub_unit_id = tmgus.unit_id
        <include refid="ConditionSaleItem"/>
    </select>

    <select id="getSalesTrackingReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM
            dkic_b.t_psi_order tpo
        <include refid="ConditionSalesTrackingReport"/>
    </select>

    <!-- 收发存汇总报表 -->
    <select id="getIvtSumReport" resultType="java.util.Map">
        select dkic_b.f_query_ivt_rds(#{cpId},#{currentPage},#{pageSize}, #{querys, typeHandler=JsonTypeHandler})
    </select>

    <!-- 收发存汇总报表明细 -->
    <select id="getIvtItemReport" resultType="java.util.Map">
        select dkic_b.f_query_ivt_rds_item(#{cpId},#{invId} ::uuid,#{invType},#{accDateStart} ::date
                   ,#{accDateEnd} ::date,#{i18n} ,#{currentPage},#{pageSize})
    </select>

    <select id="getSalesTrackingReportDetail"  resultType="java.util.Map">
        SELECT T."id",
               T."no",
               T."itemId",
               T."type",
               T."skuCode",
               T."skuName",
               T."skuModel",
               T."skuImages",
               T."brandName",
               T."shortName",
               T."returnQty",
               T."outQty",
               T."outingQty",
               T."priceSale",
               T."itemQty",
               T."sumQuantity",
               T."sumAmount",
               T."sumAmtRec",
               T."sumShouldHandle",
               T."sumUsePaymentResidue",
               T."sumWaiveAmt",
               T."makeTime",
               T."whId",
               T."whName",
               T."remarks",
               T."nonStdCode"
        FROM
            (
                SELECT
                    tpo.order_id AS "id",
                    tpo.order_no AS "no",
                    tpoi.item_id AS "itemId",
                    '销售订单' AS "type",
                    tmgs.sku_code AS "skuCode",
                    tpoi.sku_name AS "skuName",
                    tpoi.sku_model AS "skuModel",
                    tmgs.sku_images AS "skuImages",
                    tmgb.brand_name AS "brandName",
                    tmgb.short_name AS "shortName",
                    sys.f_remove_zero(tpo.return_qty) AS "returnQty",
                    sys.f_remove_zero(tpo.out_qty) AS "outQty",
                    sys.f_remove_zero(tpo.outing_qty) AS "outingQty",
                    tpoi.price_sale AS "priceSale",
                    sys.f_remove_zero(tpoi.item_qty) AS "itemQty",
                    sys.f_remove_zero(tpo.sum_quantity) AS "sumQuantity",
                    tpo.sum_amount AS "sumAmount",
                    0.00 AS "sumAmtRec",
                    0.00 AS "sumShouldHandle",
                    0.00 AS "sumUsePaymentResidue",
                    0.00 AS "sumWaiveAmt",
                    tpo.make_time AS "makeTime",
                    tpoi.wh_id AS "whId",
                    tmw.wh_name AS "whName",
                    tpoi.remarks AS "remarks",
                    tpoi.non_std_code AS "nonStdCode"
                FROM
                    dkic_b.t_psi_order_item tpoi
                        LEFT JOIN dkic_b.t_psi_order tpo ON tpo.order_id = tpoi.order_id
                        LEFT JOIN dkic_b.t_mst_goods_sku tmgs ON tmgs.sku_id = tpoi.sku_id
                        LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
                        LEFT JOIN dkic_b.t_mst_warehouse tmw ON tmw.wh_id = tpoi.wh_id
                WHERE
                    tpo.order_id =  #{id}::uuid
                UNION ALL
                SELECT
                    tpobi.out_id AS "id",
                    tpob.out_no AS "no",
                    tpobi.item_id AS "itemId",
                    '销售出库' AS "type",
                    tmgs.sku_code AS "skuCode",
                    tpobi.sku_name AS "skuName",
                    tpobi.sku_model AS "skuModel",
                    tmgs.sku_images AS "skuImages",
                    tmgb.brand_name AS "brandName",
                    tmgb.short_name AS "shortName",
                    sys.f_remove_zero(tpob.return_qty) AS "returnQty",
                    sys.f_remove_zero(tpob.out_qty) AS "outQty",
                    sys.f_remove_zero(tpob.outing_qty) AS "outingQty",
                    tpobi.price_out AS "priceSale",
                    CASE

                    WHEN tpobi.out_status = '出库状态-出库中' THEN
                    sys.f_remove_zero(tpobi.outing_qty) ELSE sys.f_remove_zero(tpobi.out_qty)
                    END AS "itemQty",
                    0.00 AS "sumQuantity",
                    0.00 AS "sumAmount",
                    0.00 AS "sumAmtRec",
                    0.00 AS "sumShouldHandle",
                    0.00 AS "sumUsePaymentResidue",
                    0.00 AS "sumWaiveAmt",
                    tpob.make_time AS "makeTime",
                    NULL AS "whId",
                    NULL AS "whName",
                    tpobi.remarks AS "remarks",
                    tpobi.non_std_code AS "nonStdCode"
                FROM
                    dkic_b.t_psi_outbound_item tpobi
                    LEFT JOIN dkic_b.t_psi_outbound tpob ON tpob.out_id = tpobi.out_id
                    LEFT JOIN dkic_b.t_mst_goods_sku tmgs ON tmgs.sku_id = tpobi.sku_id
                    LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
                WHERE
                    tpob.from_id =  #{id}::uuid
                  AND tpob.out_type = '出库类型-销售出库'
                UNION ALL
                SELECT
                    tpobi.out_id AS "id",
                    tpob.out_no AS "no",
                    tpobi.item_id AS "itemId",
                    '销售退货' AS "type",
                    tmgs.sku_code AS "skuCode",
                    tpobi.sku_name AS "skuName",
                    tpobi.sku_model AS "skuModel",
                    tmgs.sku_images AS "skuImages",
                    tmgb.brand_name AS "brandName",
                    tmgb.short_name AS "shortName",
                    sys.f_remove_zero(tpob.return_qty) AS "returnQty",
                    sys.f_remove_zero(tpob.out_qty) AS "outQty",
                    sys.f_remove_zero(tpob.outing_qty) AS "outingQty",
                    tpobi.price_out AS "priceSale",
                    CASE

                    WHEN tpobi.out_status = '出库状态-出库中' THEN
                    sys.f_remove_zero(tpobi.outing_qty) ELSE sys.f_remove_zero(tpobi.out_qty)
                    END AS "itemQty",
                    0.00 AS "sumQuantity",
                    0.00 AS "sumAmount",
                    0.00 AS "sumAmtRec",
                    0.00 AS "sumShouldHandle",
                    0.00 AS "sumUsePaymentResidue",
                    0.00 AS "sumWaiveAmt",
                    tpob.make_time AS "makeTime",
                    NULL AS "whId",
                    NULL AS "whName",
                    tpobi.remarks AS "remarks",
                    tpobi.non_std_code AS "nonStdCode"
                FROM
                    dkic_b.t_psi_outbound_item tpobi
                    LEFT JOIN dkic_b.t_psi_outbound tpob ON tpob.out_id = tpobi.out_id
                    LEFT JOIN dkic_b.t_mst_goods_sku tmgs ON tmgs.sku_id = tpobi.sku_id
                    LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
                WHERE
                    tpob.from_id =  #{id}::uuid
                  AND tpob.out_type = '出库类型-销售出库退货'
                UNION ALL
                SELECT
                    tmrp.rp_id AS "id",
                    tmrp.rp_no AS "no",
                    NULL AS "itemId",
                    '核销信息' AS "type",
                    NULL AS "skuCode",
                    NULL AS "skuName",
                    NULL AS "skuModel",
                    NULL AS "skuImages",
                    NULL AS "brandName",
                    NULL AS "shortName",
                    NULL AS "returnQty",
                    NULL AS "outQty",
                    NULL AS "outingQty",
                    NULL AS "priceSale",
                    NULL AS "itemQty",
                    0.00 AS "sumQuantity",
                    0.00 AS "sumAmount",
                    tmrp.sum_amt_rec AS "sumAmtRec",
                    tmrp.sum_should_handle AS "sumShouldHandle",
                    tmrp.sum_use_payment_residue AS "sumUsePaymentResidue",
                    tmrp.sum_waive_amt AS "sumWaiveAmt",
                    NULL AS "makeTime",
                    NULL AS "whId",
                    NULL AS "whName",
                    tmrp.remarks AS "remarks",
                    NULL AS "nonStdCode"
                FROM
                    dkic_b.t_mac_rec_pay tmrp
                WHERE
                    tmrp.biznis_id =  #{id}::uuid
            ) T
    </select>

    <!--  采购跟踪表 列表查询 -->
    <select id="getPurTrackingReport" resultType="java.util.Map">
        SELECT
        tpp.pur_id AS "purId"
        ,tpp.pur_no AS "purNo"
        ,tpp.pur_type AS "purType"
        ,sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) AS "purTypeName"
        ,tpp.org_id AS "orgId"
        ,tmo.org_name AS "orgName"
        ,tpp.staff_id AS "staffId"
        ,tmsta.staff_name AS "staffName"
        ,tpp.sup_id AS "supId"
        ,tpp.contact_name AS "contactName"
        ,tpp.contact_phone AS "contactPhone"
        ,tpp.wh_id AS "whId"
        ,tpp.pickup_date AS "pickupDate"
        ,sys.f_remove_zero(tpp.sum_quantity) AS "sumQuantity"
        ,tpp.sum_standard AS "sumStandard"
        ,tpp.sum_amount AS "sumAmount"
        ,tpp.pur_discount AS "purDiscount"
        ,tpp.pur_status AS "purStatus"
        ,sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) as "purStatusName"
        ,tpp.into_status AS "intoStatus"
        ,sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}) as "intoStatusName"
        ,sys.f_remove_zero(tpp.intoing_qty) AS "intoingQty"
        ,tpp.intoing_amt AS "intoingAmt"
        ,sys.f_remove_zero(tpp.into_qty) AS "intoQty"
        ,tpp.into_amt AS "intoAmt"
        ,sys.f_remove_zero(tpp.return_qty) AS "returnQty"
        ,tpp.return_amt AS "returnAmt"
        ,tpp.amt_payable AS "amtPayable"
        ,tpp.amt_handle AS "amtHandle"
        ,tpp.amt_residue AS "amtResidue"
        ,tpp.remarks AS "remarks"
        ,tpp.annex_paths AS "annexPaths"
        ,tpp.make_staff AS "makeStaff"
        ,tpp.make_time AS "makeTime"
        ,tpp.flg_valid AS "flgValid"
        ,tms.sup_code AS "supCode"
        ,tms.sup_name As "supName"
        ,tms.contact_phone As "contactPhone"
        FROM
        dkic_b.t_psi_purchase tpp
        left join dkic_b.t_mst_supplier tms on tpp.sup_id = tms.sup_id
        left join sys.t_data_kind as tdk1 on tpp.pur_type = tdk1.kind_code
        left join sys.t_data_kind as tdk2 on tpp.pur_status = tdk2.kind_code
        left join sys.t_data_kind as tdk3 on tpp.into_status = tdk3.kind_code
        left join dkic_b.t_mst_org as tmo on tpp.org_id = tmo.org_id
        left join dkic_b.t_mst_staff as tmsta on tpp.staff_id = tmsta.staff_id
        <include refid="ConditionPurTrackingReport"/>
        order by tpp.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <select id="getPurTrackingReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM
        dkic_b.t_psi_purchase tpp
        <include refid="ConditionPurTrackingReport"/>
    </select>

    <!--采购跟踪表通用查询映射结果 -->
    <resultMap id="PurTrackingReportResultMapResponse" type="com.dk.mdm.model.response.report.ReportResponse">
        <id column="id" property="id"/>
        <result column="no" property="no"/>
        <result column="type" property="type"/>
        <result column="contactPhone" property="contactPhone" />
        <result column="contactName" property="contactName" />
        <result column="whId" property="whId" typeHandler="UuidTypeHandler"/>
        <result column="makeTime" property="makeTime" typeHandler="TimestampTypeHandler"/>
        <result column="pickupDate" property="pickupDate" typeHandler="TimestampTypeHandler"/>
        <result column="sumQuantity" property="sumQuantity"/>
        <result column="intoQty" property="intoQty"/>
        <result column="returnQty" property="returnQty"/>
        <result column="sumAmtRec" property="sumAmtRec"/>
        <result column="sumShouldHandle" property="sumShouldHandle"/>
        <result column="sumUsePaymentResidue" property="sumUsePaymentResidue"/>
        <result column="sumWaiveAmt" property="sumWaiveAmt"/>
        <result column="makeTime" property="makeTime" typeHandler="TimestampTypeHandler"/>
        <collection property="itemResponseList" resultMap="PurTrackingReportItemListMap" columnPrefix="list_"/>
    </resultMap>

    <!--   采购跟踪表 关联映射-->
    <resultMap id="PurTrackingReportItemListMap" type="java.util.Map">
        <id column="itemId" property="itemId"/>
        <result column="id" property="id" typeHandler="UuidTypeHandler"/>
        <result column="type" property="type"/>
        <result column="skuId" property="skuId" typeHandler="UuidTypeHandler"/>
        <result column="skuCode" property="skuCode"/>
        <result column="skuName" property="skuName"/>
        <result column="skuModel" property="skuModel"/>
        <result column="skuImages" property="skuImages" typeHandler="JsonTypeHandler"/>
        <result column="brandName" property="brandName"/>
        <result column="shortName" property="shortName"/>
        <result column="pricePur" property="pricePur"/>
        <result column="itemQty" property="itemQty"/>
        <result column="remarks" property="remarks"/>
        <result column="sumQuantity" property="sumQuantity"/>
        <result column="sumAmount" property="sumAmount"/>
        <result column="no" property="no"/>
        <result column="whName" property="whName"/>
        <result column="nonStdCode" property="nonStdCode"/>
    </resultMap>
    
    <!--采购跟踪表 明细查询-->
    <select id="getPurTrackingReportDetail" resultMap="PurTrackingReportResultMapResponse">
        SELECT T."id"
             ,T."no"
             ,T."type"
             ,T."contactName"
             ,T."contactPhone"
             ,T."whId"
             ,T."makeTime"
             ,T."pickupDate"
             ,T."sumQuantity"
             ,T."intoQty"
             ,T."returnQty"
             ,T."sumAmtRec"
             ,T."sumShouldHandle"
             ,T."sumUsePaymentResidue"
             ,T."sumWaiveAmt"
             ,T."itemId" AS list_itemId
             ,T."id" AS list_id
             ,T."type" AS list_type
             ,T."skuId" AS list_skuId
             ,T."skuCode" AS list_skuCode
             ,T. "skuName" AS list_skuName
             ,T."skuModel" AS list_skuModel
             ,T. "skuImages" AS list_skuImages
             ,T."brandName" AS list_brandName
             ,T."shortName" AS list_shortName
             ,T."pricePur" AS list_pricePur
             ,T."itemQty" AS list_itemQty
             ,T."remarks"  AS list_remarks
             ,T."sumQuantity"  AS list_sumQuantity
             ,T."sumAmount"  AS list_sumAmount
             ,T."no" AS list_no
             ,T."whName" AS list_whName
             ,T."nonStdCode" AS list_nonStdCode
        FROM
            (
                SELECT
                    tpp.pur_id AS "id"
                     ,tpp.pur_no AS "no"
                     ,tpp.contact_name AS "contactName"
                     ,tpp.contact_phone AS "contactPhone"
                     ,tpp.wh_id AS "whId"
                     ,tpp.pickup_date AS "pickupDate"
                     ,sys.f_remove_zero(tpp.sum_quantity) AS "sumQuantity"
                     ,tpp.sum_amount AS "sumAmount"
                     ,sys.f_remove_zero(tpp.into_qty) AS "intoQty"
                     ,sys.f_remove_zero(tpp.return_qty) AS "returnQty"
                     ,0.00 AS "sumAmtRec"
                     ,0.00 AS "sumShouldHandle"
                     ,0.00 AS "sumUsePaymentResidue"
                     ,0.00 AS "sumWaiveAmt"
                     ,tppi.item_id AS "itemId"
                     ,'采购订单' AS "type"
                     ,tppi.sku_id AS "skuId"
                     ,tmgs.sku_code AS "skuCode"
                     ,tmgs.sku_name AS "skuName"
                     ,tmgs.sku_model AS "skuModel"
                     ,tmgs.sku_images AS "skuImages"
                     ,tmgb.brand_name AS "brandName"
                     ,tmgb.short_name AS "shortName"
                     ,tppi.price_pur AS "pricePur"
                     ,sys.f_remove_zero(tppi.item_qty) AS "itemQty"
                     ,tppi.remarks AS "remarks"
                     ,tpp.make_time AS  "makeTime"
                     ,tmw.wh_name AS "whName"
                     ,tppi.non_std_code AS "nonStdCode"
                FROM
                    dkic_b.t_psi_purchase_item tppi
                        LEFT JOIN dkic_b.t_psi_purchase tpp ON tpp.pur_id = tppi.pur_id
                        LEFT JOIN dkic_b.t_mst_warehouse tmw ON tpp.wh_id = tmw.wh_id
                        LEFT JOIN dkic_b.t_mst_goods_sku tmgs ON tmgs.sku_id = tppi.sku_id
                        LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
                WHERE
                    tpp.pur_id = #{id}::uuid
                UNION  ALL
                SELECT
                    tpi.into_id AS "id"
                        ,tpi.into_no AS "no"
                        ,NULL AS "contactName"
                        ,NULL AS "contactPhone"
                        ,NULL AS "whId"
                        ,NULL AS "pickupDate"
                        ,0.00 AS "sumQuantity"
                        ,0.00 AS "sumAmount"
                        ,NULL AS "intoQty"
                        ,0 AS "returnQty"
                        ,0.00 AS "sumAmtRec"
                        ,0.00 AS "sumShouldHandle"
                        ,0.00 AS "sumUsePaymentResidue"
                        ,0.00 AS "sumWaiveAmt"
                        ,tpii.item_id AS "itemId"
                        ,'入库信息' AS "type"
                        ,tpii.sku_id AS "skuId"
                        ,tmgs.sku_code AS "skuCode"
                        ,tmgs.sku_name AS "skuName"
                        ,tmgs.sku_model AS "skuModel"
                        ,tmgs.sku_images AS "skuImages"
                        ,tmgb.brand_name AS "brandName"
                        ,tmgb.short_name AS "shortName"
                        ,tpii.price_into AS "pricePur"
                        , CASE
                    WHEN tpii.into_status = '入库状态-入库中' THEN  tpii.intoing_qty
                    ELSE tpii.into_qty
                    END AS "itemQty"
                        ,tpii.remarks AS "remarks"
                        ,tpi.make_time AS  "makeTime"
                        ,NULL AS "whName"
                        ,tpii.non_std_code AS "nonStdCode"
                FROM
                    dkic_b.t_psi_inbound_item tpii
                    LEFT JOIN dkic_b.t_psi_inbound tpi ON tpi.into_id = tpii.into_id
                    LEFT JOIN dkic_b.t_mst_goods_sku tmgs ON tmgs.sku_id = tpii.sku_id
                    LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
                WHERE
                    tpi.from_id = #{id}::uuid
                  AND tpi.into_type = '入库类型-采购入库'
                UNION  ALL
                SELECT
                    tpi.into_id AS "id"
                        ,tpi.into_no AS "no"
                        ,NULL AS "contactName"
                        ,NULL AS "contactPhone"
                        ,NULL AS "whId"
                        ,NULL AS "pickupDate"
                        ,0.00 AS "sumQuantity"
                        ,0.00 AS "sumAmount"
                        ,NULL AS "intoQty"
                        ,0 AS "returnQty"
                        ,0.00 AS "sumAmtRec"
                        ,0.00 AS "sumShouldHandle"
                        ,0.00 AS "sumUsePaymentResidue"
                        ,0.00 AS "sumWaiveAmt"
                        ,tpii.item_id AS "itemId"
                        ,'采购退货' AS "type"
                        ,tpii.sku_id AS "skuId"
                        ,tmgs.sku_code AS "skuCode"
                        ,tmgs.sku_name AS "skuName"
                        ,tmgs.sku_model AS "skuModel"
                        ,tmgs.sku_images AS "skuImages"
                        ,tmgb.brand_name AS "brandName"
                        ,tmgb.short_name AS "shortName"
                        ,tpii.price_into AS "pricePur"
                        , CASE
                    WHEN tpii.into_status = '入库状态-入库中' THEN  sys.f_remove_zero(tpii.intoing_qty)
                    ELSE sys.f_remove_zero(tpii.into_qty)
                    END AS "itemQty"
                        ,tpii.remarks AS "remarks"
                        ,tpi.make_time AS  "makeTime"
                        ,NULL AS "whName"
                        ,tpii.non_std_code AS "nonStdCode"
                FROM
                    dkic_b.t_psi_inbound_item tpii
                    LEFT JOIN dkic_b.t_psi_inbound tpi ON tpi.into_id = tpii.into_id
                    LEFT JOIN dkic_b.t_mst_goods_sku tmgs ON tmgs.sku_id = tpii.sku_id
                    LEFT JOIN dkic_b.t_mst_goods_brand tmgb ON tmgb.brand_id = tmgs.brand_id
                WHERE
                    tpi.from_id = #{id}::uuid
                  AND tpi.into_type = '入库类型-采购入库退货'
                UNION ALL
                SELECT
                    tmrp.rp_id AS "id"
                        ,tmrp.rp_no AS "no"
                        ,NULL AS "contactName"
                        ,NULL AS "contactPhone"
                        ,NULL AS "whId"
                        ,NULL AS "pickupDate"
                        ,0.00 AS "sumQuantity"
                        ,0.00 AS "sumAmount"
                        ,NULL AS "intoQty"
                        ,NULL AS "returnQty"
                        ,tmrp.sum_amt_rec AS "sumAmtRec"
                        ,tmrp.sum_should_handle AS "sumShouldHandle"
                        ,tmrp.sum_use_payment_residue AS "sumUsePaymentResidue"
                        ,tmrp.sum_waive_amt AS "sumWaiveAmt"
                        ,Null AS "itemId"
                        ,'核销信息' AS "type"
                        ,Null AS "skuId"
                        ,Null AS "skuCode"
                        ,Null AS "skuName"
                        ,Null AS "skuModel"
                        ,Null AS "skuImages"
                        ,Null AS "brandName"
                        ,Null AS "shortName"
                        ,Null AS "pricePur"
                        ,Null AS "itemQty"
                        ,Null AS "remarks"
                        ,tmrp.make_time AS  "makeTime"
                        ,NULL AS "whName"
                        ,NULL AS "nonStdCode"
                FROM
                    dkic_b.t_mac_rec_pay tmrp

                WHERE
                    tmrp.biznis_id= #{id}::uuid
            ) T

    </select>

    <!--   日结对账表列表查询-->
    <select id="getDailyReconciliationReport" resultType="java.util.Map">
        SELECT
        T.item_id AS "itemId",
        T.item_id AS "accItemId",
        T.acc_item_type AS "accItemType",
        T.object_id AS "objectId",
        tmc.cus_code AS "cusCode",
        tmc.cus_name AS "cusName",
        tmc.cus_phone AS "cusPhone",
        tmc.address_full AS "addressFull",
        T.org_id AS "orgId",
        tmo.org_name AS "orgName",
        T.staff_id AS "staffId",
        tms.staff_name AS "staffName",
        T.biznis_type AS "biznisType",
        T.biznis_id AS "biznisId",
        T.biznis_no AS "bizniso",
        T.acc_date AS "accDate",
        T.rec_status AS "recStatus",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) AS "recStatusName",
        T.amt_rec AS "amtRec",
        T.amt_pay AS "amtPay",
        T.amt_should AS "amtShould",
        T.amt_handle AS "amtHandle",
        T.amt_waive AS "amtWaive",
        T.amt_residue  AS "amtNotHandle",
        T.amt_residue  AS "amtReceivableHandle",
        T.amt_residue AS "amtResidue",
        T.remarks AS  "remarks" ,
        T.make_staff AS "makeStaff",
        T.make_time AS "makeTime",
        T.flg_valid AS "flgValid" ,
        T.cp_id AS "cpId" ,
        ord.totalOrderAmount  as "totalOrderAmount"
        FROM dkic_b.t_mac_account_item as T
        LEFT JOIN (select sum(tpo.sum_amount) as totalOrderAmount ,tpo.cus_id from dkic_b.t_psi_order tpo group by tpo.cus_id ) ord ON ord.cus_id =T.object_id
        LEFT JOIN dkic_b.t_mac_other_receivable as tmor ON tmor.receivable_id = T.biznis_id and T.biznis_type = 't_mac_other_receivable'
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = T.object_id
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = T.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = T.staff_id
        LEFT JOIN sys.t_data_kind as tdk1 ON T.rec_status = tdk1.kind_code
        <include refid="ConditionDailyReconciliationReport"/>
        order by  T.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!--  日结对账表列表查询数量-->
    <select id="getDailyReconciliationReportCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_mac_account_item as T
        LEFT JOIN (select sum(tpo.sum_amount) as totalOrderAmount ,tpo.cus_id from dkic_b.t_psi_order tpo group by tpo.cus_id ) ord ON ord.cus_id =T.object_id
        LEFT JOIN dkic_b.t_mac_other_receivable as tmor ON tmor.receivable_id = T.biznis_id and T.biznis_type = 't_mac_other_receivable'
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = T.object_id
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = T.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = T.staff_id
        LEFT JOIN sys.t_data_kind as tdk1 ON T.rec_status = tdk1.kind_code
        <include refid="ConditionDailyReconciliationReport"/>
    </select>

    <!--  日结对账表查询条件-->
    <sql id="ConditionDailyReconciliationReport">
        <where>
            <if test="cpId != null">
                AND  T.cp_id = #{cpId}
            </if>
            <if test="staffId != null">
                AND  T.staff_id = #{staffId}
            </if>
            <if test="orgId != null">
                AND T.org_id = #{orgId}
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND T.org_id  =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND T.staff_id  =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="object_id != null">
                AND T.object_id = #{object_id}
            </if>
            <if test="cusName != null">
                AND  tmc.cus_name LIKE concat('%',my_ex.likequery(#{cusName}),'%')
            </if>
            <if test="cusPhone != null">
                AND  tmc.cus_phone LIKE concat('%',my_ex.likequery(#{cusPhone}),'%')
            </if>
            <if test="accDateStart != null and accDateEnd != null">
                AND T.acc_date &gt;= #{accDateStart}::timestamp with time zone
                AND T.acc_date &lt; #{accDateEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>
    
    <!--   日结对账表-查销售订单-->
    <select id="getDailyReconciliationReportByOrder" resultType="java.util.Map">
        SELECT 
        T.order_id AS "orderId",
        T.order_no AS "orderNo",
        T.order_type AS "orderType",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) AS "orderTypeName",
        T.org_id AS "orgId",
        tmo.org_name  AS "orgName",
        T.staff_id AS "staffId",
        tms.staff_name  AS "staffName",
        T.cus_id AS "cusId",
        tmc.cus_code  AS "cusCode",
        tmc.cus_name  AS "cusName",
        tmc.cus_phone AS "cusPhone",
        tmc.cus_from AS "cusFrom",
        tmdd.data_value AS "cusFromName",
        T.address_area AS "addressArea",
        T.address_name AS "addressName",
        T.address_no AS "addressNo",
        T.address_gcj02 AS "addressGcj02",
        T.address_full AS "addressFull",
        T.contact_name AS "contactName",
        T.contact_phone AS "contactPhone",
        T.sales_channel AS "salesChannel",
        tmsc.channel_name AS "channelName",
        T.delivery_date AS "deliveryDate",
        sys.f_remove_zero(T.sum_quantity) AS "sumQuantity",
        T.sum_standard AS "sumStandard",
        T.sum_amount AS "sumAmount",
        T.sale_discount AS "saleDiscount",
        T.order_status AS "orderStatus",
        sys.f_get_name_i18n(tdk2.kind_name_i18n, #{i18n}) AS "orderStatusName",
        T.out_status AS "orderStatus",
        sys.f_get_name_i18n(tdk3.kind_name_i18n, #{i18n}) AS "outStatusName",
        sys.f_remove_zero(T.outing_qty) AS "outingQty",
        T.outing_amt AS "outingAmt",
        sys.f_remove_zero(T.out_qty) AS "outQty",
        T.out_amt AS "outAmt",
        sys.f_remove_zero(T.return_qty) AS "returnQty",
        T.return_amt AS "returnAmt",
--         T.amt_receivable AS "amtReceivable",
--         T.amt_handle AS "amtHandle",
--         T.amt_residue AS "amtResidue",
        T.remarks AS "remarks",
        T.annex_paths AS "annexPaths",
        makestaff.staff_name AS "makeStaffName",
        T.make_time AS "makeTime",
        T.cp_id AS "cpId",
        T.flg_valid AS "flgValid"
        FROM dkic_b.t_psi_order AS T
        LEFT JOIN dkic_b.t_mst_sale_channel tmsc ON T.sales_channel = tmsc.channel_id
        LEFT JOIN dkic_b.t_mst_org tmo ON T.org_id = tmo.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON T.staff_id = tms.staff_id
        LEFT JOIN dkic_b.t_mst_staff as makestaff ON T.make_staff = makestaff.staff_id
        LEFT JOIN dkic_b.t_mst_customer tmc ON T.cus_id = tmc.cus_id
        LEFT JOIN dkic_b.t_mst_dictionary_data tmdd ON tmc.cus_from = tmdd.data_id
        LEFT JOIN sys.t_data_kind as tdk1 ON T.order_type = tdk1.kind_code
        LEFT JOIN sys.t_data_kind as tdk2 ON T.order_status = tdk2.kind_code
        LEFT JOIN sys.t_data_kind as tdk3 ON T.out_status = tdk3.kind_code
        <include refid="ConditionDailyReconciliationReportByOrder"/>
        order by T.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!--  日结对账表-查销售订单数量-->
    <select id="getDailyReconciliationReportByOrderCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_order AS T
        LEFT JOIN dkic_b.t_mst_sale_channel tmsc ON T.sales_channel = tmsc.channel_id
        LEFT JOIN dkic_b.t_mst_org tmo ON T.org_id = tmo.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON T.staff_id = tms.staff_id
        LEFT JOIN dkic_b.t_mst_staff as makestaff ON T.make_staff = makestaff.staff_id
        LEFT JOIN dkic_b.t_mst_customer tmc ON T.cus_id = tmc.cus_id
        LEFT JOIN dkic_b.t_mst_dictionary_data tmdd ON tmc.cus_from = tmdd.data_id
        LEFT JOIN sys.t_data_kind as tdk1 ON T.order_type = tdk1.kind_code
        LEFT JOIN sys.t_data_kind as tdk2 ON T.order_status = tdk2.kind_code
        LEFT JOIN sys.t_data_kind as tdk3 ON T.out_status = tdk3.kind_code
        <include refid="ConditionDailyReconciliationReportByOrder"/>
    </select>

    <!--  日结对账表查询条件-->
    <sql id="ConditionDailyReconciliationReportByOrder">
        <where>
            <if test="orderNo != null">
                AND T.order_no = #{orderNo}
            </if>
            <if test="cusName != null">
                AND  tmc.cus_name = #{cusName}
            </if>
            <if test="cusPhone != null">
                AND  tmc.cus_phone = #{cusPhone}
            </if>
            <if test="cus_id != null">
                AND  T.cus_id = #{cusId}
            </if>
            <if test="staffId != null">
                AND  T.staff_id = #{staffId}
            </if>
            <if test="orgId != null">
                AND T.org_id = #{orgId}
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND T.org_id  =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND T.staff_id  =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="accDateStart != null and accDateEnd != null">
                AND T.make_time &gt;= #{accDateStart}::timestamp with time zone
                AND T.make_time &lt; #{accDateEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>

    <!--   日结对账表-查销售出库-->
    <select id="getDailyReconciliationReportByOut" resultType="java.util.Map">
        SELECT 
        T.out_id AS "outId",
        T.out_no AS "outNo",
        T.out_type AS "outType",
        T.from_id AS "fromId",
        T.from_no AS "fromNo",
        T.from_no AS "biznisNo",
        T.org_id AS "orgId",
        tmo.org_name AS "orgName",
        T.staff_id AS "staffId",
        tms.staff_name AS"staffName",
        T.cus_id AS "cusId",
        tmc.cus_code AS "cusCode",
        tmc.cus_name AS "cusName",
        tmc.cus_phone AS "cusPhone",
        tmc.cus_from AS "cusFrom",
        tmc.contact_phone AS "contactPhone",
        tmc.address_full AS "addressFull",
        tmdd.data_value  AS cus_from_name,
        T.sup_id AS "supId",
        tmp.sup_name  AS "supplierName",
        T.out_status AS "outStatus",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) AS "outStatusName",
        sys.f_get_name_i18n(tdktype.kind_name_i18n, #{i18n}) AS  "outTypeName",
        sys.f_remove_zero(T.outing_qty) AS "outingQty",
        T.outing_amt AS "outingAmt",
        sys.f_remove_zero(T.out_qty) AS "outQty",
        T.out_amt AS "outAmt",
        sys.f_remove_zero(T.return_qty) AS "returnQty",
        T.return_amt AS "returnAmt",
        T.out_date AS "outDate",
        T.remarks AS "remarks",
        T.annex_paths AS "annexPaths",
        T.make_staff AS "makeStaff",
        makestaff.staff_name  AS "makeStaffName",
        T.make_time AS "makeTime",
        T.receivable_id AS "receivableId",
        T.cp_id AS "cpId",
        T.flg_valid AS "flgValid",
        tmd.data_value as "outReasonName"
        FROM dkic_b.t_psi_outbound as T
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = T.org_id
        LEFT JOIN dkic_b.t_mst_supplier tmp ON tmp.sup_id = T.sup_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = T.staff_id
        LEFT JOIN dkic_b.t_mst_staff as makestaff ON makestaff.staff_id = T.make_staff
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = T.cus_id
        LEFT JOIN dkic_b.t_mst_dictionary_data tmdd ON tmdd.data_id = tmc.cus_from
        LEFT JOIN sys.t_data_kind as tdk1 ON tdk1.kind_code = T.out_status
        LEFT JOIN sys.t_data_kind tdktype   ON tdktype.kind_code = T.out_type
        LEFT JOIN dkic_b.t_mst_dictionary_data tmd ON tmd.data_id = T.out_reason
        <include refid="ConditionDailyReconciliationReportByOut"/>
        order by T.make_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!--  日结对账表-查销售订单数量-->
    <select id="getDailyReconciliationReportByOutCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_psi_outbound as T
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = T.org_id
        LEFT JOIN dkic_b.t_mst_supplier tmp ON tmp.sup_id = T.sup_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = T.staff_id
        LEFT JOIN dkic_b.t_mst_staff as makestaff ON makestaff.staff_id = T.make_staff
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = T.cus_id
        LEFT JOIN dkic_b.t_mst_dictionary_data tmdd ON tmdd.data_id = tmc.cus_from
        LEFT JOIN sys.t_data_kind as tdk1 ON tdk1.kind_code = T.out_status
        LEFT JOIN sys.t_data_kind tdktype   ON tdktype.kind_code = T.out_type
        LEFT JOIN dkic_b.t_mst_dictionary_data tmd ON tmd.data_id = T.out_reason
        <include refid="ConditionDailyReconciliationReportByOut"/>
    </select>

    <!--  日结对账表查询条件-->
    <sql id="ConditionDailyReconciliationReportByOut">
        <where>
            <if test="outNo != null">
                AND T.out_no = #{outNo}
            </if>
            <if test="cusName != null">
                AND tmc.cus_name = #{cusName}
            </if>
            <if test="cusPhone != null">
                AND  tmc.cus_phone = #{cusPhone}
            </if>
            <if test="cus_id != null">
                AND T.cus_id = #{cusId}
            </if>
            <if test="staffId != null">
                AND  T.staff_id = #{staffId}
            </if>
            <if test="orgId != null">
                AND T.org_id = #{orgId}
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND T.org_id  =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND T.staff_id  =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="accDateStart != null and accDateEnd != null">
                AND T.make_time &gt;= #{accDateStart}::timestamp with time zone
                AND T.make_time &lt; #{accDateEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>

    <!--   日结对账表-查收款单 -->
    <select id="getDailyReconciliationReportByPay" resultType="java.util.Map">
        select 
        T.rp_id AS "rpId",
        T.rp_no AS "rpNo",
        T.rp_type AS "rpType",
        sys.f_get_name_i18n(tdk1.kind_name_i18n, #{i18n}) AS "rpTypeName",
        T.object_id AS "objectId",
        tmc.cus_code  AS "cusCode",
        tmc.cus_name  AS "cusName",
        tmc.cus_phone AS "cusPhone",
        tmc.address_full AS "addressFull",
        T.org_id AS "orgId",
        tmo.org_name AS "orgName",
        T.staff_id AS "staffId",
        tms.staff_name AS "staffName",
        T.sum_amt_rec AS "sumAmtRec",
        T.sum_amt_pay AS "sumAmtPay",
        T.sum_should_handle AS "sumShouldHandle",
        T.sum_use_receipt_residue AS "sumUseReceiptResidue",
        T.sum_use_payment_residue AS "sumUsePaymentResidue",
        T.sum_amt_receivable_handle AS "sumAmtReceivableHandle",
        T.sum_amt_payable_handle AS "sumAmtPayableHandle",
        T.sum_waive_amt AS "sumWaiveAmt",
        T.acc_date AS "accDate",
        T.remarks AS "remarks",
        T.annex_paths AS "annexPaths",
        T.biznis_type AS "biznisType",
        T.biznis_id AS "biznisId",
        T.biznis_no AS "biznisNo",
        T.flg_lock AS "flgLock",
        T.make_staff AS "makeStaff",
        makestaff.staff_name AS "makeStaffName",
        T.make_time AS "makeTime",
        T.flg_valid AS "flgValid",
        T.cp_id AS "cpId",
        tma.receivable_residue AS "receivableResidue",
        tma.receipt_residue AS "receiptResidue"
        FROM dkic_b.t_mac_rec_pay as T
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = T.object_id
        LEFT JOIN sys.t_data_kind as tdk1 ON tdk1.kind_code = T.rp_type
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = T.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = T.staff_id
        LEFT JOIN dkic_b.t_mst_staff as makestaff ON makestaff.staff_id = T.make_staff
        LEFT JOIN  dkic_b.t_mac_account tma  ON tma.object_id = T.object_id and tma.object_type in ('对象类型-客户')
        <include refid="ConditionDailyReconciliationReportByPay"/>
        order by T.op_create_time desc
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>

    </select>

    <!--  日结对账表-查收款单数量-->
    <select id="getDailyReconciliationReportByPayCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM dkic_b.t_mac_rec_pay as T
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = T.object_id
        LEFT JOIN sys.t_data_kind as tdk1 ON tdk1.kind_code = T.rp_type
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = T.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = T.staff_id
        LEFT JOIN dkic_b.t_mst_staff as makestaff ON makestaff.staff_id = T.make_staff
        LEFT JOIN  dkic_b.t_mac_account tma  ON tma.object_id = T.object_id and tma.object_type in ('对象类型-客户')
        <include refid="ConditionDailyReconciliationReportByPay"/>
    </select>

    <!--  日结对账表-查收款单查询条件-->
    <sql id="ConditionDailyReconciliationReportByPay">
        <where>
            T.rp_type = '收付款类型-收款'
            <if test="rpNo != null">
                AND  T.rp_no = #{rpNo}
            </if>
            <if test="cusName != null">
                AND tmc.cus_name = #{cusName}
            </if>
            <if test="cusPhone != null">
                AND tmc.cus_phone = #{cusPhone}
            </if>
            <if test="cus_id != null">
                AND T.cus_id = #{cusId}
            </if>
            <if test="staffId != null">
                AND  T.staff_id = #{staffId}
            </if>
            <if test="orgId != null">
                AND T.org_id = #{orgId}
            </if>
            <if test="orgIdList != null and orgIdList.size() > 0">
                AND T.org_id  =any(#{orgIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="staffIdList != null and staffIdList.size() > 0">
                AND T.staff_id  =any(#{staffIdList, typeHandler=uuidListTypeHandler})
            </if>
            <if test="accDateStart != null and accDateEnd != null">
                AND T.make_time &gt;= #{accDateStart}::timestamp with time zone
                AND T.make_time &lt; #{accDateEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
    </sql>

    <!-- 供应商往来余额表 -->
    <select id="getSupAccount" resultType="java.util.Map">
        SELECT
        tms.sup_name AS "supName",
        tms.sup_id as "objectId",
        SUM ( tmai.amt_pay ) as "sumAmtPay",
        SUM ( tmai.amt_handle ) as "sumAmtHandle"
        FROM
        dkic_b.t_mac_account_item tmai
        INNER JOIN dkic_b.t_mst_supplier tms ON tmai.object_id = tms.sup_id
        <where>
            tmai.flg_valid
            AND tmai.acc_item_type IN ( '账款类型-应付', '账款类型-付款' )
            and tmai.cp_id = #{cpId}
            <!--开始日期-->
            <if test="accDateStart != null">
                AND tmai.acc_date &gt;= #{accDateStart}::timestamp with time zone
            </if>
            <!--截止日期-->
            <if test="accDateEnd != null">
                AND tmai.acc_date &lt;= #{accDateEnd}::timestamp with time zone + interval '1 day'
            </if>
        </where>
        GROUP BY tms.sup_name,tms.sup_id
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!-- 供应商往来余额表明细 -->
    <select id="getSupAccountItem" resultType="java.util.Map">
        SELECT
        tmai.item_id as "itemId"
        ,tmai.acc_item_type as "accItemType"
        ,sys.f_get_name_i18n(tdk.kind_name_i18n,#{i18n}) AS "accItemTypeName"
        ,tmai.org_id as "orgId"
        ,tmo.org_name as "orgName"
        ,tmai.staff_id as "staffId"
        ,tms.staff_name as "staffName"
        ,tmai.amt_pay as "amtPay"
        ,tmai.amt_handle as "amtHandle"
        FROM dkic_b.t_mac_account_item tmai
        LEFT JOIN dkic_b.t_mst_org tmo ON tmo.org_id = tmai.org_id
        LEFT JOIN dkic_b.t_mst_staff tms ON tms.staff_id = tmai.staff_id
        LEFT JOIN sys.t_data_kind tdk ON tdk.kind_code = tmai.acc_item_type
        where tmai.flg_valid
        and tmai.acc_item_type in ('账款类型-应付','账款类型-付款')
        and tmai.object_id = #{objectId}::uuid
    </select>


    <!-- 资金日记账-->
    <select id="getFundReport" resultType="java.util.Map">
        SELECT *
        FROM dkic_b.f_get_fund_report(#{accDateStart}::date,#{accDateEnd}::date,#{cpId},#{currentPage},#{pageSize},#{macIds, typeHandler=UuidListTypeHandler}::uuid[])
    </select>

    <!-- 销售简报-->
    <select id="getReportBatch" resultType="Map">
        <!-- 我的页面 助手部分 -->
        <if test="param.type == 'f_my_sale_info'">
            select f_my_sale_info as report from "dkic_b"."f_my_sale_info"(#{param.cpId}, #{param.orgId}, #{param.userId, typeHandler=UuidTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'))
        </if>

        <!-- 我的页面 销售业务进度 月 -->
        <if test="param.type == 'f_kpi_order_rate_month'">
            select f_kpi_order_rate_month as report from "dkic_b"."f_kpi_order_rate_month"(to_date(#{param.rateDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgId},#{param.userId, typeHandler=UuidTypeHandler})
        </if>
        <!-- 我的页面 销售业务进度 年 -->
        <if test="param.type == 'f_kpi_order_rate_year'">
            select f_kpi_order_rate_year as report from "dkic_b"."f_kpi_order_rate_year"(to_date(#{param.rateDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgId},#{param.userId, typeHandler=UuidTypeHandler})
        </if>
        <!-- 我的页面 收款任务进度 月 -->
        <if test="param.type == 'f_kpi_repay_rate_month'">
            select f_kpi_repay_rate_month as report from "dkic_b"."f_kpi_repay_rate_month"(to_date(#{param.rateDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgId},#{param.userId, typeHandler=UuidTypeHandler})
        </if>
        <!-- 我的页面 收款任务进度 年 -->
        <if test="param.type == 'f_kpi_repay_rate_year'">
            select f_kpi_repay_rate_year as report from "dkic_b"."f_kpi_repay_rate_year"(to_date(#{param.rateDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgId},#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 报表页面 销售分析 -->
        <if test="param.type == 'f_report_order_analysis_home'">
            select f_report_order_analysis_home as report from "dkic_b"."f_report_order_analysis_home"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>
        <!-- 报表页面 收款分析 -->
        <if test="param.type == 'f_report_repay_analysis'">
            select f_report_repay_analysis as report from "dkic_b"."f_report_repay_analysis"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>
        <!-- 报表页面 客户分析 -->
        <if test="param.type == 'f_report_csm_analysis_home'">
            select f_report_csm_analysis_home as report from "dkic_b"."f_report_csm_analysis_home"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>


        <!-- 图形部分 -->
        <!-- 报表页面 客户来源分布 -->
        <if test="param.type == 'f_report_csm_from_order'">
            select f_report_csm_from_order as report from "dkic_b"."f_report_csm_from_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 报表页面 产品分布 -->
        <if test="param.type == 'f_report_spu_analysis'">
            select f_report_spu_analysis as report from "dkic_b"."f_report_spu_analysis"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 报表页面 部门成交 -->
        <if test="param.type == 'f_report_index_org_order'">
            select f_report_index_org_order as report from "dkic_b"."f_report_index_org_order"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 报表页面 部门收款 -->
        <if test="param.type == 'f_report_index_org_repay'">
            select f_report_index_org_repay as report from "dkic_b"."f_report_index_org_repay"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 报表页面 业务员成交 -->
        <if test="param.type == 'f_report_index_staff_order'">
            select f_report_index_staff_order as report from "dkic_b"."f_report_index_staff_order"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 报表页面 业务员收款 -->
        <if test="param.type == 'f_report_index_staff_repay'">
            select f_report_index_staff_repay as report from "dkic_b"."f_report_index_staff_repay"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 明细页面 收款分析-收款任务 f_report_index_org_repay 部分收款同一个函数-->

        <!-- 明细页面 收款分析-欠款情况 -->
        <if test="param.type == 'f_report_repay_debt'">
            select f_report_repay_debt as report from "dkic_b"."f_report_repay_debt"(to_date(#{param.monthDate},'yyyy-MM-dd'),#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 明细页面 客户分析-报价 -->
        <if test="param.type == 'f_report_csm_analysis_offer'">
            select f_report_csm_analysis_offer as report from "dkic_b"."f_report_csm_analysis_offer"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 明细页面 客户分析-成交 -->
        <if test="param.type == 'f_report_csm_analysis_order'">
            select f_report_csm_analysis_order as report from "dkic_b"."f_report_csm_analysis_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 明细页面 客户来源分布-报价 -->
        <if test="param.type == 'f_report_csm_from_offer'">
            select f_report_csm_from_offer as report from "dkic_b"."f_report_csm_from_offer"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <!-- 明细页面 客户来源分布-潜客 -->
        <if test="param.type == 'f_report_csm_from_filing'">
            select f_report_csm_from_filing as report from "dkic_b"."f_report_csm_from_filing"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>


        <!-- 明细页面 销售分析5个 第一个就是部门成交-->

        <if test="param.type == 'f_report_order_analysis_reject'">
            select f_report_order_analysis_reject as report from "dkic_b"."f_report_order_analysis_reject"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <if test="param.type == 'f_report_order_analysis_csm'">
            select f_report_order_analysis_csm as report from "dkic_b"."f_report_order_analysis_csm"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <if test="param.type == 'f_report_order_analysis_activity'">
            select f_report_order_analysis_activity as report from "dkic_b"."f_report_order_analysis_activity"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>

        <if test="param.type == 'f_report_order_analysis_specs'">
            select f_report_order_analysis_specs as report from "dkic_b"."f_report_order_analysis_specs"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler})
        </if>


        <!--订单参考利润-->
        <if test="param.type == 'f_report_s201_sale_reference_profit'">
            select f_report_s201_sale_reference_profit as report from "dkic_b"."f_report_s201_sale_reference_profit"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!--出库参考利润-->
        <if test="param.type == 'f_report_s201_out_reference_profit'">
            select f_report_s201_out_reference_profit as report from "dkic_b"."f_report_s201_out_reference_profit"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>



        <!-- 报表页面 销售分析 -->
        <if test="param.type == 'f_report_s001_order_analysis_home'">
            select f_report_s001_order_analysis_home as report from "dkic_b"."f_report_s001_order_analysis_home"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>
        <!-- 报表页面 收款分析 -->
        <if test="param.type == 'f_report_s001_repay_analysis'">
            select f_report_s001_repay_analysis as report from "dkic_b"."f_report_s001_repay_analysis"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>
        <!-- 报表页面 客户分析 -->
        <if test="param.type == 'f_report_s001_csm_analysis_home'">
            select f_report_s001_csm_analysis_home as report from "dkic_b"."f_report_s001_csm_analysis_home"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- 图形部分 -->
        <!-- 报表页面 客户来源分布 -->
        <if test="param.type == 'f_report_s001_csm_from_order'">
            select f_report_s001_csm_from_order as report from "dkic_b"."f_report_s001_csm_from_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 产品分布 -->
        <if test="param.type == 'f_report_s001_spu_analysis'">
            select f_report_s001_spu_analysis as report from "dkic_b"."f_report_s001_spu_analysis"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 部门成交 -->
        <if test="param.type == 'f_report_s001_index_org_order'">
            select f_report_s001_index_org_order as report from "dkic_b"."f_report_s001_index_org_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 部门收款 -->
        <if test="param.type == 'f_report_s001_index_org_repay'">
            select f_report_s001_index_org_repay as report from "dkic_b"."f_report_s001_index_org_repay"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 业务员成交 -->
        <if test="param.type == 'f_report_s001_index_staff_order'">
            select f_report_s001_index_staff_order as report from "dkic_b"."f_report_s001_index_staff_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 业务员收款 -->
        <if test="param.type == 'f_report_s001_index_staff_repay'">
            select f_report_s001_index_staff_repay as report from "dkic_b"."f_report_s001_index_staff_repay"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 收款分析-收款任务 f_report_s001_index_org_repay 部分收款同一个函数-->

        <!-- 明细页面 收款分析-欠款情况 -->
        <if test="param.type == 'f_report_s001_repay_debt'">
            select f_report_s001_repay_debt as report from "dkic_b"."f_report_s001_repay_debt"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户分析-报价 -->
        <if test="param.type == 'f_report_s001_csm_analysis_offer'">
            select f_report_s001_csm_analysis_offer as report from "dkic_b"."f_report_s001_csm_analysis_offer"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户分析-成交 -->
        <if test="param.type == 'f_report_s001_csm_analysis_order'">
            select f_report_s001_csm_analysis_order as report from "dkic_b"."f_report_s001_csm_analysis_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户来源分布-报价 -->
        <if test="param.type == 'f_report_s001_csm_from_offer'">
            select f_report_s001_csm_from_offer as report from "dkic_b"."f_report_s001_csm_from_offer"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户来源分布-潜客 -->
        <if test="param.type == 'f_report_s001_csm_from_filing'">
            select f_report_s001_csm_from_filing as report from "dkic_b"."f_report_s001_csm_from_filing"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- 明细页面 销售分析5个 第一个就是部门成交-->

        <if test="param.type == 'f_report_s001_order_analysis_reject'">
            select f_report_s001_order_analysis_reject as report from "dkic_b"."f_report_s001_order_analysis_reject"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <if test="param.type == 'f_report_s001_order_analysis_csm'">
            select f_report_s001_order_analysis_csm as report from "dkic_b"."f_report_s001_order_analysis_csm"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <if test="param.type == 'f_report_s001_order_analysis_activity'">
            select f_report_s001_order_analysis_activity as report from "dkic_b"."f_report_s001_order_analysis_activity"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <if test="param.type == 'f_report_s001_order_analysis_specs'">
            select f_report_s001_order_analysis_specs as report from "dkic_b"."f_report_s001_order_analysis_specs"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- s002部分 -->
        <!-- 报表页面 销售分析 -->
        <if test="param.type == 'f_report_s002_order_analysis_home'">
            select f_report_s002_order_analysis_home as report from "dkic_b"."f_report_s002_order_analysis_home"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>
        <!-- 报表页面 收款分析 -->
        <if test="param.type == 'f_report_s002_repay_analysis'">
            select f_report_s002_repay_analysis as report from "dkic_b"."f_report_s002_repay_analysis"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>
        <!-- 报表页面 客户分析 -->
        <if test="param.type == 'f_report_s002_csm_analysis_home'">
            select f_report_s002_csm_analysis_home as report from "dkic_b"."f_report_s002_csm_analysis_home"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- 图形部分 -->
        <!-- 报表页面 客户来源分布 -->
        <if test="param.type == 'f_report_s002_csm_from_order'">
            select f_report_s002_csm_from_order as report from "dkic_b"."f_report_s002_csm_from_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 产品分布 -->
        <if test="param.type == 'f_report_s002_spu_analysis'">
            select f_report_s002_spu_analysis as report from "dkic_b"."f_report_s002_spu_analysis"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- 报表页面 业务员成交 -->
        <if test="param.type == 'f_report_s002_index_staff_order'">
            select f_report_s002_index_staff_order as report from "dkic_b"."f_report_s002_index_staff_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 报表页面 业务员收款 -->
        <if test="param.type == 'f_report_s002_index_staff_repay'">
            select f_report_s002_index_staff_repay as report from "dkic_b"."f_report_s002_index_staff_repay"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- 明细页面 收款分析-欠款情况 -->
        <if test="param.type == 'f_report_s002_repay_debt'">
            select f_report_s002_repay_debt as report from "dkic_b"."f_report_s002_repay_debt"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户分析-报价 -->
        <if test="param.type == 'f_report_s002_csm_analysis_offer'">
            select f_report_s002_csm_analysis_offer as report from "dkic_b"."f_report_s002_csm_analysis_offer"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户分析-成交 -->
        <if test="param.type == 'f_report_s002_csm_analysis_order'">
            select f_report_s002_csm_analysis_order as report from "dkic_b"."f_report_s002_csm_analysis_order"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户来源分布-报价 -->
        <if test="param.type == 'f_report_s002_csm_from_offer'">
            select f_report_s002_csm_from_offer as report from "dkic_b"."f_report_s002_csm_from_offer"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 明细页面 客户来源分布-潜客 -->
        <if test="param.type == 'f_report_s002_csm_from_filing'">
            select f_report_s002_csm_from_filing as report from "dkic_b"."f_report_s002_csm_from_filing"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>


        <!-- 明细页面 销售分析5个 第一个就是部门成交-->

        <if test="param.type == 'f_report_s002_order_analysis_reject'">
            select f_report_s002_order_analysis_reject as report from "dkic_b"."f_report_s002_order_analysis_reject"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <if test="param.type == 'f_report_s002_order_analysis_csm'">
            select f_report_s002_order_analysis_csm as report from "dkic_b"."f_report_s002_order_analysis_csm"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <if test="param.type == 'f_report_s002_order_analysis_activity'">
            select f_report_s002_order_analysis_activity as report from "dkic_b"."f_report_s002_order_analysis_activity"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <if test="param.type == 'f_report_s002_order_analysis_specs'">
            select f_report_s002_order_analysis_specs as report from "dkic_b"."f_report_s002_order_analysis_specs"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 销售分析 -->
        <!-- 部门品类排行 -->
        <if test="param.type == 'f_report_s003_kpi_org_category'">
            select f_report_s003_kpi_org_category as report from "dkic_b"."f_report_s003_kpi_org_category"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 部门单品排行 -->
        <if test="param.type == 'f_report_s003_kpi_org_spu'">
            select f_report_s003_kpi_org_spu as report from "dkic_b"."f_report_s003_kpi_org_spu"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 业务员品类排行 -->
        <if test="param.type == 'f_report_s003_kpi_staff_category'">
            select f_report_s003_kpi_staff_category as report from "dkic_b"."f_report_s003_kpi_staff_category"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>

        <!-- 业务员单品排行 -->
        <if test="param.type == 'f_report_s003_kpi_staff_spu'">
            select f_report_s003_kpi_staff_spu as report from "dkic_b"."f_report_s003_kpi_staff_spu"(#{param.cpId},#{param.orgIds,typeHandler=UuidListTypeHandler},#{param.staffIds,typeHandler=UuidListTypeHandler},to_date(#{param.beginDate},'yyyy-MM-dd'),to_date(#{param.endDate},'yyyy-MM-dd'),#{param.userId, typeHandler=UuidTypeHandler},#{param.dateType})
        </if>
    </select>

    <!-- 客户往来账 -->
    <select id="getMacRecCurrentReport" resultType="java.util.Map">
        select dkic_b.f_query_mac_rec_current(#{cpId},#{currentPage},#{pageSize}, #{querys, typeHandler=JsonTypeHandler})
    </select>

    <!-- 客户往来账明细 -->
    <select id="getMacRecCurrentItemReport" resultType="java.util.Map">
        select dkic_b.f_query_mac_rec_current_item(#{cpId},#{objectId} ::uuid,#{orgId} ::uuid,#{macType},#{accDateStart} ::date
                   ,#{accDateEnd} ::date,#{i18n} ,#{currentPage},#{pageSize})
    </select>

    <!-- 客户往来账 -->
    <select id="getMacPayCurrentReport" resultType="java.util.Map">
        select dkic_b.f_query_mac_pay_current(#{cpId},#{currentPage},#{pageSize}, #{querys, typeHandler=JsonTypeHandler})
    </select>

    <!-- 客户往来账明细 -->
    <select id="getMacPayCurrentItemReport" resultType="java.util.Map">
        select dkic_b.f_query_mac_pay_current_item(#{cpId},#{objectId} ::uuid,#{orgId} ::uuid,#{macType},#{accDateStart} ::date
                   ,#{accDateEnd} ::date,#{i18n} ,#{currentPage},#{pageSize})
    </select>
    <!--  根据用户信息和筛选信息查询账款明细  -->
    <select id="getCusRecPayReportItemByUser" resultType="java.util.Map">
        SELECT a.object_id as "objectId"
             , COALESCE(SUM(a.amt_rec), 0) as "sumAmtRec"
        FROM
            dkic_b.t_mac_account_item a
        WHERE a.flg_valid
          AND a.cp_id = #{cpId}
          AND a.object_id = #{objectId}::uuid
        group by a.object_id, a.biznis_no, a.acc_date, a.staff_id
    </select>

    <!--  查询欠款用户信息  -->
    <select id="getUserArrears" resultType="java.util.Map">
        SELECT a.object_id as "objectId"
        , tmc.cus_name as "cusName"
        , tmc.cus_phone as "cusPhone"
        , SUM(COALESCE(tpo.out_amt, 0) + COALESCE(tpo.return_amt, 0)) - SUM(COALESCE(a.amt_rec, 0)) as arrears
        FROM
        dkic_b.t_psi_order tpio
        LEFT JOIN dkic_b.t_mac_account_item a ON a.object_id = tpio.cus_id
        LEFT JOIN dkic_b.t_psi_outbound tpo ON tpo.out_id = a.biznis_id
        LEFT JOIN dkic_b.t_mac_rec_pay tmrp ON tmrp.rp_id = a.biznis_id
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = a.object_id
        WHERE a.flg_valid
        AND a.cp_id = #{cpId}
        AND tmc.cus_name IS NOT NULL AND tmc.cus_name != ''
        <if test="objectId != null">
            AND a.object_id = #{objectId}::uuid
        </if>
        <if test="searchText != null and searchText != ''">
            AND (tmc.cus_name LIKE concat('%',#{searchText},'%')
            OR tmc.cus_phone LIKE concat('%',#{searchText},'%')
            OR tmc.address_full LIKE concat('%',#{searchText},'%'))
        </if>
        GROUP BY a.object_id, tmc.cus_name, tmc.cus_phone, tmc.address_full
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <select id="getPsiOrderCount" resultType="java.util.Map">
        SELECT
        tpo.cus_id as "objectId"
        , COALESCE(SUM(tpo.fact_amt), 0) as "facAmt"
        FROM dkic_b.t_psi_order tpo
        WHERE tpo.flg_valid
        AND tpo.cp_id = #{cpId}
        <if test="objectId != null">
            AND tpo.cus_id = #{objectId}::uuid
        </if>
        <if test="staffIds != null and staffIds.size() > 0">
            AND tpo.staff_id IN
            <foreach collection="staffIds" index="index" item="item" separator="," open="(" close=")">
                #{item}::uuid
            </foreach>
        </if>
        <if test="orgIds != null and orgIds.size() > 0">
            AND tpo.org_id IN
            <foreach collection="orgIds" index="index" item="item" separator="," open="(" close=")">
                #{item}::uuid
            </foreach>
        </if>
        group by tpo.cus_id, tpo.make_time, tpo.fact_amt, tpo.order_no
    </select>

    <select id="getOutboundAmtByUser" resultType="java.util.Map">
        select
            tpo.cus_id as "objectId"
            ,COALESCE(SUM(tpo.out_amt), 0) as "outAmt"
        from dkic_b.t_psi_outbound tpo
        where tpo.flg_valid
        and tpo.out_type = '出库类型-销退回库'
        AND tpo.cp_id = #{cpId}
        <if test="objectId != null">
            AND tpo.cus_id = #{objectId}::uuid
        </if>
        group by tpo.cus_id, tpo.out_amt
    </select>
    <select id="getStartAmt" resultType="java.util.Map">
        SELECT
        COALESCE(SUM(tmor.sum_amt_receivable), 0)AS "startAmt"
        FROM dkic_b.t_mac_other_receivable tmor
        WHERE tmor.flg_valid AND tmor.cp_id = #{cpId}
        <if test="objectId != null">
            AND tmor.object_id = #{objectId}::uuid
        </if>
        AND tmor.make_time::date &lt;= CURRENT_DATE
    </select>

    <select id="getPsiOrderByUser" resultType="java.util.Map">
        SELECT
            tpo.cus_id as "objectId"
             , tpo.order_no as "biznisNo"
             , tpo.make_time::date as "accDate"
             , COALESCE(SUM(tpo.fact_amt), 0) as "facAmt"
        FROM dkic_b.t_psi_order tpo
        WHERE tpo.flg_valid
        AND tpo.cp_id = #{cpId}
        <if test="objectId != null">
            AND tpo.cus_id = #{objectId}::uuid
        </if>
        <if test="staffId != null">
            AND tpo.staff_id = #{staffId}::uuid
        </if>
        <if test="orgId != null">
            AND tpo.org_id = #{orgId}::uuid
        </if>
        <if test="staffIds != null and staffIds.size() > 0">
            AND tpo.staff_id IN
            <foreach collection="staffIds" index="index" item="item" separator="," open="(" close=")">
                #{item}::uuid
            </foreach>
        </if>
        <if test="orgIds != null and orgIds.size() > 0">
            AND tpo.org_id IN
            <foreach collection="orgIds" index="index" item="item" separator="," open="(" close=")">
                #{item}::uuid
            </foreach>
        </if>
        group by tpo.cus_id, tpo.make_time, tpo.fact_amt, tpo.order_no
    </select>

    <!--获取销售额-->
    <select id="getOrderAmt" resultType="java.util.Map">
        select COALESCE(SUM(tpio.fact_amt), 0) as "orderAmt" from dkic_b.t_psi_order tpio where tpio.flg_valid
        AND tpio.flg_valid AND tpio.cp_id = #{cpId}
        <if test="objectId != null">
            AND tpio.cus_id = #{objectId}::uuid
        </if>
        AND tpio.make_time::date &lt;= CURRENT_DATE
    </select>
    <!--获取退货额-->
    <select id="getOutboundAmt" resultType="java.util.Map">
        select COALESCE(SUM(tpo.out_amt), 0) as "outboundAmt" from dkic_b.t_psi_outbound tpo where tpo.flg_valid and tpo.out_type = '出库类型-销退回库'
      AND tpo.flg_valid AND tpo.cp_id = #{cpId}
        <if test="objectId != null">
            AND tpo.cus_id = #{objectId}::uuid
        </if>
      AND tpo.make_time::date &lt;= CURRENT_DATE
    </select>
    <!--获取收支额-->
    <select id="getIncomeExpenditure" resultType="java.util.Map">
        SELECT
            COALESCE(SUM(a.amt_rec), 0) as "arrears"
        FROM dkic_b.t_mac_account_item a
        LEFT JOIN dkic_b.t_mst_customer tmc ON tmc.cus_id = a.object_id
        WHERE a.flg_valid
        AND a.cp_id = #{cpId}
        AND tmc.cus_name IS NOT NULL AND tmc.cus_name != ''
        <if test="objectId != null">
            AND tmc.cus_id = #{objectId}::uuid
        </if>
        AND a.make_time::date &lt;= CURRENT_DATE
    </select>
    <!--获取欠款额-->
    <select id="getDebt" resultType="java.util.Map">
        SELECT
            COALESCE(SUM(tpo.out_amt), 0) as "outAmt"
        FROM dkic_b.t_mac_account_item a
        LEFT JOIN dkic_b.t_psi_outbound tpo ON tpo.out_id = a.biznis_id
        WHERE a.flg_valid
        AND a.cp_id = #{cpId}
        AND tpo.out_amt &lt; 0
        AND a.make_time::date &lt;= CURRENT_DATE
    </select>

    <!--region 销售利润-->
    <!--
    2024-06-24 11:25 成本和销售额都从出库明细中取
    -->

    <select id="getSaleProfit" resultType="java.util.Map">
        select
        t.outbound, t.inbound,
        t.outbound - t.inbound "profit",
        <include refid="getSaleProfitRate"/>
        from (
        select
        <include refid="getSaleProfitInOutBound"/>
        from dkic_b.t_psi_outbound_item tpoi
        left join dkic_b.t_psi_outbound tpo on tpo.out_id = tpoi.out_id
        <include refid="getSaleProfitCond"/>
        <include refid="getSaleProfitDateCond"/>
        <if test="orgIds!=null and orgIds.size()>0">
            and (
            tpo.org_id = any(#{orgIds, typeHandler=com.dk.common.infrastructure.handler.UuidListTypeHandler})
            )
        </if>
        ) t
    </select>

    <select id="getSaleProfitGroupByDate" resultType="java.util.Map">
        select t.acc_date "accDate",
        t.outbound - t.inbound "profit",
        <include refid="getSaleProfitRate"/>
        from (select
        <if test="byDate==null or (byDate!=null and byDate=='false')">
            to_char(tpoi.op_create_time, 'yyyy-mm') acc_date,
        </if>
        <if test="byDate!=null and byDate=='true'">
            to_char(tpoi.op_create_time, 'mm-dd') acc_date,
        </if>
        <include refid="getSaleProfitInOutBound"/>
        from dkic_b.t_psi_outbound_item tpoi
        inner join dkic_b.t_psi_outbound tpo  on tpoi.out_id = tpo.out_id
        <include refid="getSaleProfitCond"/>
        AND tpoi.op_create_time >= now() - interval
        <if test="byDate==null or (byDate!=null and byDate=='false')">
            '1 year'
        </if>
        <if test="byDate!=null and byDate=='true'">
            '1 month'
        </if>
        AND tpoi.op_create_time &lt; now()
        group by
        <if test="byDate==null or (byDate!=null and byDate=='false')">
            to_char(tpoi.op_create_time, 'yyyy-mm')
        </if>
        <if test="byDate!=null and byDate=='true'">
            to_char(tpoi.op_create_time, 'mm-dd')
        </if>
        ) t
        order by t.acc_date
    </select>

    <select id="getSaleProfitGroupByOrg" resultType="java.util.Map">
        select tmo.org_name "orgName", tt.profit, tt."profitRate"
        from (
        select t.org_id,
        t.outbound - t.inbound "profit",
        <include refid="getSaleProfitRate"/>
        from (select tpo.org_id org_id,
        <include refid="getSaleProfitInOutBound"/>
        from dkic_b.t_psi_outbound_item tpoi
        left join dkic_b.t_psi_outbound tpo on tpo.out_id = tpoi.out_id
        <include refid="getSaleProfitCond"/>
        <include refid="getSaleProfitDateCond"/>
        group by tpo.org_id) t
        ) tt
        left join dkic_b.t_mst_org tmo on tmo.org_id = tt.org_id
        <include refid="getSaleProfitLimit"/>
    </select>

    <select id="getSaleProfitGroupByStaff" resultType="java.util.Map">
        select tms.staff_name "staffName", tt.profit, tt."profitRate"
        from (
        select t.staff_id,
        t.outbound - t.inbound "profit",
        <include refid="getSaleProfitRate"/>
        from (select tpo.staff_id staff_id,
        <include refid="getSaleProfitInOutBound"/>
        from dkic_b.t_psi_outbound_item tpoi
        left join dkic_b.t_psi_outbound tpo on tpo.out_id = tpoi.out_id
        <include refid="getSaleProfitCond"/>
        <include refid="getSaleProfitDateCond"/>
        <if test="orgIds!=null and orgIds.size()>0">
            and (
            tpo.org_id = any(#{orgIds, typeHandler=com.dk.common.infrastructure.handler.UuidListTypeHandler})
            )
        </if>
        group by tpo.staff_id) t
        ) tt
        left join dkic_b.t_mst_staff tms on tms.staff_id = tt.staff_id
        <include refid="getSaleProfitLimit"/>
    </select>

    <select id="getSaleProfitGroupBySku" resultType="java.util.Map">
        select tt.sku_name "skuName", tt.profit, tt."profitRate"
        from (
        select t.sku_name,
        t.outbound - t.inbound "profit",
        <include refid="getSaleProfitRate"/>
        from (select coalesce((tmgs.sku_model || coalesce('(' || tmgs.sku_name || ')', '')), (tpoi.sku_model || coalesce('(' || tpoi.sku_name || ')', ''))) sku_name,
        <include refid="getSaleProfitInOutBound"/>
        from dkic_b.t_psi_outbound_item tpoi
        inner join dkic_b.t_psi_outbound tpo  on tpoi.out_id = tpo.out_id
        left join dkic_b.t_mst_goods_sku tmgs on tmgs.sku_id = tpoi.sku_id
        <include refid="getSaleProfitCond"/>
        <include refid="getSaleProfitDateCond"/>
        group by coalesce((tmgs.sku_model || coalesce('(' || tmgs.sku_name || ')', '')), (tpoi.sku_model || coalesce('(' || tpoi.sku_name || ')', '')))
        ) t
        ) tt
        <include refid="getSaleProfitLimit"/>
    </select>

    <sql id="getSaleProfitInOutBound">
        sum(coalesce(tpoi.fact_amt, 0)) outbound,
        sum(coalesce(tpoi.cost_amt, 0)) inbound
    </sql>

    <sql id="getSaleProfitCond">
        where tpoi.cp_id = #{cpId} and tpoi.out_status = '出库状态-已出库'
        AND tpoi.out_type IN ( '出库类型-销售出库', '出库类型-销退回库' )
        and tpoi.flg_valid
        and tpo.flg_valid
    </sql>

    <sql id="getSaleProfitDateCond">
        <if test="makeTimeStart != null and makeTimeEnd != null">
            AND tpoi.op_create_time &gt;= #{makeTimeStart}::timestamp with time zone
            AND tpoi.op_create_time &lt; #{makeTimeEnd}::timestamp with time zone + interval '1 day'
        </if>
    </sql>

    <sql id="getSaleProfitLimit">
        <if test="orderBy != null and orderBy=='profitAsc'">
            order by tt.profit
        </if>
        <if test="orderBy == null or orderBy=='profitDesc'">
            order by tt.profit desc
        </if>
        <if test="orderBy != null and orderBy=='rateAsc'">
            order by tt."profitRate"
        </if>
        <if test="orderBy != null and orderBy=='rateDsc'">
            order by tt."profitRate" desc
        </if>
        <if test="limit!=null and limit">
            limit 5
        </if>
    </sql>

    <sql id="getSaleProfitRate">
        case
        when t.outbound = 0 then 0
        else
        round((t.outbound - t.inbound) / t.outbound * 100, 2)
        end "profitRate"
    </sql>

    <!-- 经营概况 -->
    <select id="getBusinessOverview" resultType="java.util.Map">
        select dkic_b.f_query_business_overview(#{cpId}, #{querys, typeHandler=JsonTypeHandler})
    </select>

    <!-- 销售汇总 -->
    <select id="getOrderSummary" resultType="java.util.Map">
        select dkic_b.f_get_order_summary(#{cpId}, #{querys, typeHandler=JsonTypeHandler})
    </select>

    <!-- 销售汇总明细 -->
    <select id="getOrderSummaryItem" resultType="java.util.Map">
        select dkic_b.f_get_order_summary_item(#{cpId}, #{querys, typeHandler=JsonTypeHandler}, #{lang})
    </select>

    <!-- 资金日记账-->
    <select id="getFundReportWeb" resultType="java.util.Map">
        SELECT *
        FROM
            dkic_b.f_get_fund_report_web(#{accDateStart}::date,#{accDateEnd}::date,#{cpId},#{currentPage},#{pageSize},#{macIds, typeHandler=UuidListTypeHandler}::uuid[])
    </select>
    <!--endregion-->
</mapper>
