<?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.wxapi.basic.WxCommonMapper">
    <!-- 获取系统基础数据 -->
    <select id="getBasicData" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select bd.data_code as "code", sys.f_get_name_i18n( #{i18n}, bd.data_name_i18n) as "name"
        from sys.t_s_basic_data bd
        where bd.flg_valid
          and bd.type_code = #{dataSourceCode}
        order by bd.display_no
    </select>
    <!--获取客户来源数据-->
    <select id="getCustomerSource" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmcf.from_id as id, tmcf.from_name as name ,tmcf.channel_id as "channelId"
        from mst.f_m_customer_from_cp(#{cpId}, false) tmcf
        where tmcf.flg_valid
        <if test="channelId !=null and channelId !=''">
            and tmcf.channel_id = #{channelId}
        </if>
        order by tmcf.display_no
    </select>

    <!--获取卫生间数数据-->
    <select id="getNumberToilets" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmt.toilets_id as id, tmt.toilets_name as name
        from mst.t_m_toilets tmt
        where tmt.flg_valid
        order by tmt.display_no
    </select>

    <!--获取集团字典数据-->
    <select id="getHouseType" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmdd.data_id as id, tmdd.data_value as name
        from mst.t_m_dictionary_data tmdd
        where tmdd.flg_valid
          and tmdd.item_code = #{dataSourceCode}
        order by tmdd.display_no
    </select>

    <!--获取商品分类数据-->
    <select id="getGoodsSpecs" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmgs.specs_id as id, tmgs.specs_name as name
        from mst.t_m_goods_specs tmgs
        where tmgs.flg_valid
          and tmgs.parent_id = 0
          and tmgs.cp_id = #{cpId}
        order by tmgs.display_no, tmgs.specs_id
    </select>

    <!--获取商品其他分类数据-->
    <select id="getGoodsSpecsOrther" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmgs.specs_id as id, tmgs.specs_name as name
        from mst.t_m_goods_specs tmgs
        where tmgs.flg_valid
          and tmgs.parent_id = -8
        order by tmgs.display_no
    </select>

    <!--获取结算方式数据-->
    <select id="getSettlement" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmst.type_id                                      as id,
               tmst.type_name                                    as name,
               sys.f_get_name_i18n( #{i18n}, tsbd.data_name_i18n) as accountname
        from sale.t_m_settlement_type tmst
                 LEFT JOIN sys.t_s_basic_data tsbd ON tsbd.flg_valid AND tsbd.data_code = tmst.account_type
        where tmst.flg_valid
        order by tmst.display_no
    </select>

    <!--获取费用项目数据-->
    <select id="getFeeItem" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmfi.item_id as id, tmfi.item_name as name
        from sale.t_m_fee_item tmfi
        where tmfi.flg_valid
        order by tmfi.display_no
    </select>

    <!--获取组织机构数据-->
    <select id="getOrganization" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmo.org_id as "id",
        tmo.org_name as "name",
        tmop.org_name as "pName",
        tmot.org_name as "tName",
        tmo.cp_id as "cpId"
        from dkic_b.t_mst_org tmo
        left join dkic_b.t_mst_org tmop on tmop.org_id = tmo.parent_id
        left join dkic_b.t_mst_org tmot on tmot.org_id = tmo.top_id
        left join dkic_a.t_a_company tac on tac.cp_id = tmo.cp_id
        where
        tmo.cp_id = #{cpId}
        <!--todo..把1要替换 #{cpId}-->
        and tmo.flg_valid
        <if test="searchText !=null and searchText !=''">
            AND tmo.org_name LIKE concat('%', #{searchText}, '%')
        </if>


    </select>

    <!--范围权限类型-->
    <select id="getPurviewType" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tpt.purview_name_i18n as purviewName,
               tpt.purview_code as "code",
               "row_number"() OVER (ORDER BY tpt.display_no) as "id",
                sys.f_get_name_i18n(tpt.purview_name_i18n, #{i18n}) AS name
        from sys.t_purview_type tpt
        where tpt.flg_valid
        order by tpt.display_no

    </select>

    <!--获取角色数据-->
    <select id="getRole" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmr.role_id as "id",
        tmr.role_code as "code",
        tmr.role_name as "name",
        tmr.cp_id as "cpId"
        from dkic_b.t_mst_role tmr
        where
        tmr.cp_id = #{cpId}
        and tmr.flg_valid
        <if test="searchText !=null and searchText !=''">
            AND tmr.role_name LIKE concat('%', #{searchText}, '%')
        </if>
    </select>

    <!--    获取员工-->
    <select id="getUser" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select u.user_name as "name"
        , u.user_code as "code"
        , u.erp_staff_code as "erpStaffCode"
        , u.user_id as "id"
        , u.org_id as "orgId"
        ,u.job_id as "jobId"
        , u.qv_userid as "qvUserid"
        , tmo.org_name as "orgName"
        , tmo.org_code as "orgCode"
        , tmp.post_name as "postName"
        , tmp.post_id as "postId"
        from core.t_m_user u
        left join dkic_b.t_mst_org tmo on tmo.org_id = u.org_id
        left join core.t_m_post tmp on tmp.post_id = u.post_id
        where u.flg_valid
        and u.cp_id = #{cpId}
        and u.sys_id = #{sysId}
        and u.flg_can_login
        and u.user_type = 1
        <if test="ascpId!=null">
            and u.ascp_id = #{ascpId}
        </if>
        <if test="searchText !=null and searchText !=''">
            AND (u.user_code LIKE concat('%', #{searchText}, '%') or u.user_name LIKE concat('%', #{searchText},'%'))
        </if>
        <if test="name !=null and name !=''">
            AND u.user_name LIKE concat('%', #{name}, '%')
        </if>
        <if test="jobIds !=null and jobIds !=''">
            AND u.job_id = any(#{jobIds, typeHandler=BigIntListTypeHandler})
        </if>

        <if test="docName == '销售订单' and docName!=null ">
            AND (u.org_id = #{orgId} or
            sys.f_has_purviews_user('销售订单-一览', #{currentUser}, jsonb_build_object('订单-查看-部门', u.org_id)))
        </if>
        <if test="docName == '客户资料' and docName!=null  ">
            AND (u.org_id = #{orgId} or
            sys.f_has_purviews_user('客户资料-一览', #{currentUser}, jsonb_build_object('客户-查看-部门', u.org_id)))
        </if>

        <if test="userIds != null and userIds.size() > 0 ">
            order by case u.user_id
            <foreach collection="userIds" index="index" item="item">
                when #{item} then #{item}
            </foreach>
            end
        </if>

        <if test="userIds == null">
            order by u.op_create_time desc
        </if>
    </select>
    <!--    获取员工-->
    <select id="getStaff" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select  u.staff_id as "id" ,
                u.staff_name as "name",
                u.staff_code as "code"  ,
                u.org_id as "orgId"
         from dkic_b.t_mst_staff u
         where u.flg_valid
            and u.hr_status = 1
           and u.flg_ghost = false
         and u.cp_id = #{cpId}
    </select>
    <!--    获取品牌-->
    <select id="getBrand" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select  tmgb.brand_id as "id" ,
                tmgb.brand_code as "code",
                tmgb.brand_name as "name"
        from dkic_b.t_mst_goods_brand tmgb
        where tmgb.flg_valid
          and tmgb.cp_id = #{cpId}
    </select>
    <!--    渠道-->
    <select id="getChannel" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmc.channel_id as id, tmc.channel_name as name
        from mst.t_m_channel tmc
        where tmc.flg_valid
          and tmc.cp_id = #{cpId}
        order by tmc.display_no
    </select>
    <!--    仓库-->
    <select id="getWareHouse" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">

        <if test="orgId != null and orgId != '' and orgId != 0">
            select tmo.def_wh_id, tmw.wh_name as name, tmw.wh_id as id
            from mst.t_m_warehouse tmw
            left join dkic_b.t_mst_org tmo
            on tmw.wh_id = any (tmo.wh_ids)
            and tmw.flg_valid
            where tmo.cp_id = #{cpId}
            and tmo.org_id = #{orgId}
        </if>
        <if test="orgId == null  || orgId == 0">
            select null AS def_wh_id, tmw.wh_name as name, tmw.wh_id as id
            from mst.t_m_warehouse tmw
            where tmw.cp_id = #{cpId}
            and tmw.flg_valid
        </if>
    </select>
    <!--    获取单号-->
    <select id="getDocNo" resultType="java.lang.String">
        select sys.f_create_doc_no(#{cpId}, #{orgId}, #{docCode})
    </select>

    <select id="getOrderStatus" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select sys.f_get_name_i18n( #{i18n}, tsb.data_name_i18n) AS name, tsb.data_code as code
        from sys.t_s_basic_data tsb
        where tsb.flg_valid
          and tsb.type_code = '审'
        order by tsb.display_no
    </select>

    <!--    <select id="getEstate" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">-->
    <!--        select tme.estate_id   as "id",-->
    <!--               tme.estate_Name as "name"-->
    <!--        from mst.t_m_estate tme-->
    <!--        where tme.cp_id = #{cpId}-->
    <!--        order by tme.op_create_time desc-->
    <!--    </select>-->


    <select id="getEstate" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tme.estate_id as "id",
        tme.estate_Name as "name",
        tme.remarks
        from mst.t_m_estate tme
        where tme.cp_id = #{cpId}
        order by tme.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="countByEstateCond" resultType="Long">
        SELECT count(1)
        from mst.t_m_estate tme
        where tme.cp_id = #{cpId}
    </select>

    <select id="selectSaleChannelByOrg" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmo.sales_channel as "salesChannel",
               tmc.channel_name  as "salesChannelName"
        from dkic_b.t_mst_org tmo
                 left join mst.t_m_channel tmc on tmc.channel_id = tmo.sales_channel
        where tmo.cp_id = #{cpId}
          and tmo.org_id = #{orgId}
          and tmo.flg_valid
    </select>

    <select id="selectSettlementByOrg" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select tmst.type_id,
        tmst.type_name,
        tmo.def_settlement_type,
        tmst.account_type,
        tmst.image_receipt_code,
        0 AS sum_amount
        from dkic_b.t_mst_org tmo
        left join sale.t_m_settlement_type tmst on tmst.type_id = any (tmo.settlement_types)
        where tmo.org_id = #{orgId}
        and tmo.cp_id = #{cpId}
        and tmo.flg_valid
        <!--2023/04/17 结算方式数据问题修改 -->
        and tmst.cp_id = #{cpId} and tmst.flg_valid
    </select>
    <!--交货方式-->
    <select id="getDeliveryType" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select data_id as "id" ,
        data_value as "name"
        from mst.t_m_dictionary_data
        where item_code = '销售-交货方式'
        and flg_valid
        <!--    and cp_id = #{cpId}-->
        <!--    and  sys_id = #{sysId}-->
    </select>

    <select id="getRefundBlank" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select data_id    as "id",
               data_value as "name"
        from mst.t_m_dictionary_data
        where item_code = '销售-开户行'
          and flg_valid
    </select>

    <select id="getRejectReasonType" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select data_id    as "id",
               data_value as "name"
        from mst.t_m_dictionary_data
        where item_code = '销售-退货类别'
          and flg_valid
    </select>


    <select id="getReceptionStatus" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select sys.f_get_name_i18n( #{i18n}, tsb.data_name_i18n) AS name, tsb.data_code as code
        from sys.t_s_basic_data tsb
        where tsb.flg_valid
          and tsb.type_code = '跟进方式'
        order by tsb.display_no
    </select>
<!--获取供应商类别-->
    <select id="getSupplierType" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select sys.f_get_name_i18n(tdk.kind_name_i18n, #{i18n} )AS name, tdk.kind_code as code
        from sys.t_data_kind tdk
        where tdk.flg_valid
          and tdk.kind_type = '供应商类别'
        order by tdk.display_no
    </select>

    <!--获取服务类别-->
    <select id="getServiceCategories" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select sys.f_get_name_i18n(tdk.kind_name_i18n, #{i18n}) AS name, tdk.kind_code as code
        from sys.t_data_kind tdk
        where tdk.flg_valid
          and tdk.kind_type = '服务类别'
        order by tdk.display_no
    </select>

    <select id="getCustomerClassified" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select data_id    as "id",
               data_value as "name"
        from mst.t_m_dictionary_data
        where item_code = '客户-客户分类'
          and flg_valid
        order by display_no
    </select>

    <select id="getStoreSampleAdvise" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        SELECT
        tssa.org_id as "id" ,
        tmo.org_name as "name"
        FROM mst.t_store_samples_advise tssa
        left join dkic_b.t_mst_org tmo on tmo.org_id= tssa.org_id
        where
        tssa.cp_id = #{cpId}
        <if test=" userIds != null and userIds.size()>0 ">
            <foreach collection="userIds" index="index" item="item" open="AND (" close=")" separator="or">
                #{item} = any(tssa.user_ids)
            </foreach>
        </if>
    </select>

    <!--获取供应商和客户数据-->
    <select id="getSupplierAndCustomer" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select * from(select tms.supplier_id as "objId",
        tms.supplier_code as "objCode",
        tms.supplier_name as "objName",
        '供应商' as "objTypeName",
        1 as "objType",
        tms.flg_valid as "flgValid",
        tms.contact_phone as "phone",
        tms.address_full as "addressFull",
        null as "customerPhone" ,
        tms.contact_name as "contactName" ,
        tms.contact_phone as "contactPhones"
        from sale.t_m_supplier tms
        where tms.ascp_id = #{ascpId}
        AND tms.flg_valid
        And tms.cp_id = #{cpId}
        <if test="searchText != null and searchText != ''">
            AND(tms.supplier_name LIKE concat('%', #{searchText}, '%')
            OR tms.address_full LIKE concat('%', #{searchText}, '%')
            OR tms.contact_phone  LIKE concat('%', #{searchText}, '%') )
        </if>
        <!--对象类型 1:供应商 2:客户-->
        <if test="objectType != null and objectType.size() > 0">
            AND 1 IN
            <foreach collection="objectType" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        union all
        select cus.customer_id as "objId",
        cus.customer_code as "objCode",
        cus.customer_name as "objName",
        '客户' as "objTypeName",
        2 as "objType",
        cus.flg_valid as "flgValid",
        cus.customer_phone as "phone",
        cus.address_full as "addressFull",
        cus.customer_phone as "customerPhone",
        cus.contact_name as "contactName",
        cus.contact_phones[1] as "contactPhones"
        from sale.t_csm_customer cus
        where
        cus.flg_valid
        AND ((cus.ascp_id = #{ascpId}
        AND cus.cp_id = #{cpId})
        -- 共享客户
        OR cus.customer_id IN (
        SELECT
        DISTINCT
        t.customer_id
        FROM
        sale.t_csm_customer t
        LEFT JOIN sale.t_csm_customer_share t1 ON t.customer_id = t1.customer_id
        WHERE
        t.flg_valid = true
        -- 有效期内
        AND lower(t1.validity_time) &lt;= now() AND upper(t1.validity_time) &gt;= now()
        <if test="userId == null">
            AND t1.share_to_cp_id = #{cpId}
        </if>
        <if test="userId != null">
            AND (t1.share_to_cp_id = #{cpId} OR t1.share_to_user = #{userId})
        </if>
        )
        )
        <if test="searchText != null and searchText != ''">
            AND(cus.customer_name LIKE concat('%', #{searchText}, '%')
          OR cus.address_full LIKE concat('%', #{searchText}, '%')
        OR cus.customer_phone  LIKE concat('%', #{searchText}, '%') )
        </if>
        <!--对象类型 1:供应商 2:客户-->
        <if test="objectType != null and objectType.size() > 0">
            AND 2 IN
            <foreach collection="objectType" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) tt
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!--获取供应商和客户数据分页-->
    <select id="getSupplierAndCustomerByPageCount" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        select count(1) from(select tms.supplier_id as "objId",
        tms.supplier_code as "objCode",
        tms.supplier_name as "objName",
        '供应商' as "objTypeName",
        1 as "objType",
        tms.flg_valid as "flgValid",
        tms.contact_phone as "phone",
        tms.address_full as "addressFull",
        null as "customerPhone" ,
        tms.contact_name as "contactName" ,
        tms.contact_phone as "contactPhones"
        from sale.t_m_supplier tms
        where tms.ascp_id = #{ascpId}
        AND tms.flg_valid
        And tms.cp_id = #{cpId}
        <if test="searchText != null and searchText != ''">
            AND(tms.supplier_name LIKE concat('%', #{searchText}, '%')
            OR tms.address_full LIKE concat('%', #{searchText}, '%')
            OR tms.contact_phone  LIKE concat('%', #{searchText}, '%') )
        </if>
        <!--对象类型 1:供应商 2:客户-->
        <if test="objectType != null and objectType.size() > 0">
            AND 1 IN
            <foreach collection="objectType" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        union all
        select cus.customer_id as "objId",
        cus.customer_code as "objCode",
        cus.customer_name as "objName",
        '客户' as "objTypeName",
        2 as "objType",
        cus.flg_valid as "flgValid",
        cus.customer_phone as "phone",
        cus.address_full as "addressFull",
        cus.customer_phone as "customerPhone",
        cus.contact_name as "contactName",
        cus.contact_phones[1] as "contactPhones"
        from sale.t_csm_customer cus
        where
        cus.flg_valid
        AND ((cus.ascp_id = #{ascpId}
        AND cus.cp_id = #{cpId})
        -- 共享客户
        OR cus.customer_id IN (
        SELECT
        DISTINCT
        t.customer_id
        FROM
        sale.t_csm_customer t
        LEFT JOIN sale.t_csm_customer_share t1 ON t.customer_id = t1.customer_id
        WHERE
        t.flg_valid = true
        -- 有效期内
        AND lower(t1.validity_time) &lt;= now() AND upper(t1.validity_time) &gt;= now()
        <if test="userId == null">
            AND t1.share_to_cp_id = #{cpId}
        </if>
        <if test="userId != null">
            AND (t1.share_to_cp_id = #{cpId} OR t1.share_to_user = #{userId})
        </if>
        )
        )
        <if test="searchText != null and searchText != ''">
            AND(cus.customer_name LIKE concat('%', #{searchText}, '%')
            OR cus.address_full LIKE concat('%', #{searchText}, '%')
            OR cus.customer_phone  LIKE concat('%', #{searchText}, '%') )
        </if>
        <!--对象类型 1:供应商 2:客户-->
        <if test="objectType != null and objectType.size() > 0">
            AND 2 IN
            <foreach collection="objectType" index="index" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) tt
    </select>

    <select id="selectByCategor" resultType="com.dk.common.model.wxapi.wxResponse.WxCommonResponse">
        SELECT
        category_id, parent_id, category_code, category_name, key_words
        FROM mst.t_hg_spu_category
        <where>
            <if test="cpId != null">
                AND (cp_id = #{cpId} or cp_id = 1)
            </if>
        </where>
        order by level_code
    </select>
</mapper>
