<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zfire.mall.manager.commonmapper.StockK3Mapper">

    <resultMap id="sockPosition" type="com.zfire.mall.manager.bean.k3.StockPositionStock">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="stock_cordon" jdbcType="DECIMAL" property="stockCordon"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <collection property="kingDeeStocks" ofType="com.zfire.mall.manager.plus.entity.KingDeeStock" select="stocks" column="id">
            <id column="id" jdbcType="VARCHAR" property="id"/>
            <result column="number" jdbcType="VARCHAR" property="number"/>
            <result column="name" jdbcType="VARCHAR" property="name"/>
            <result column="useOrgNumber" jdbcType="VARCHAR" property="useOrgName"/>
            <result column="useOrgName" jdbcType="VARCHAR" property="useOrgName"/>
            <result column="syncTime" jdbcType="TIMESTAMP" property="syncTime"/>
            <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
            <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
            <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
            <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
            <result column="forbid_status" jdbcType="VARCHAR" property="forbidStatus"/>
            <result column="principal_name" jdbcType="VARCHAR" property="principalName"/>
            <result column="stock_property" jdbcType="VARCHAR" property="stockProperty"/>
            <result column="third_stock_type" jdbcType="VARCHAR" property="thirdStockType"/>
            <result column="document_status" jdbcType="VARCHAR" property="documentStatus"/>
            <result column="allow_minus_qty" jdbcType="VARCHAR" property="allowMinusQty"/>
            <result column="open_location" jdbcType="VARCHAR" property="openLocation"/>
            <result column="not_exp_qty" jdbcType="VARCHAR" property="notExpQty"/>
            <result column="available_alert" jdbcType="VARCHAR" property="availableAlert"/>
            <result column="create_org_name" jdbcType="VARCHAR" property="createOrgName"/>
            <result column="supplier_name" jdbcType="VARCHAR" property="supplierName"/>
            <result column="customer_name" jdbcType="VARCHAR" property="customerName"/>
            <result column="address" jdbcType="VARCHAR" property="address"/>
            <result column="tel" jdbcType="VARCHAR" property="tel"/>
            <result column="description" jdbcType="VARCHAR" property="description"/>
            <result column="group_name" jdbcType="VARCHAR" property="groupName"/>
            <result column="gys_stock" jdbcType="VARCHAR" property="gysStock"/>
            <result column="gys_stock_number" jdbcType="VARCHAR" property="gysStockNumber"/>
            <result column="third_stock_no" jdbcType="VARCHAR" property="thirdStockNo"/>
            <result column="stock_status_type" jdbcType="VARCHAR" property="stockStatusType"/>
            <result column="def_stock_status_name" jdbcType="VARCHAR" property="defStockStatusName"/>
            <result column="def_receive_status_name" jdbcType="VARCHAR" property="defReceiveStatusName"/>
            <result column="allow_mrp_plan" jdbcType="VARCHAR" property="allowMrpPlan"/>
            <result column="allow_lock" jdbcType="VARCHAR" property="allowLock"/>
            <result column="available_picking" jdbcType="VARCHAR" property="availablePicking"/>
            <result column="sorting_priority" jdbcType="VARCHAR" property="sortingPriority"/>
            <result column="locList_formatter" jdbcType="VARCHAR" property="loclistFormatter"/>
            <result column="Inquire" jdbcType="TINYINT" property="inquire"/>
        </collection>
    </resultMap>
    <select id="listStock" resultMap="sockPosition">
        SELECT
            sc.*
        FROM
            stock_correspond sc
                LEFT JOIN stock_correspond_position scp ON sc.id = scp.correspond_id
                LEFT JOIN king_dee_stock kds ON scp.stock_id = kds.id
        where 1=1
        <if test="name != null and name != ''">
            and sc.name like  CONCAT('%', #{name},'%')
        </if>
        <if test="stockName != null and stockName != ''">
            and kds.name like  CONCAT('%', #{stockName},'%')
        </if>
        <if test="id != null and id != ''">
            and sc.id =  #{id}
        </if>
        group by sc.id
        order by sc.create_time desc
    </select>

    <select id="stocks" resultType="com.zfire.mall.manager.plus.entity.KingDeeStock">
        SELECT
        kds.*
        FROM
        stock_correspond sc
        LEFT JOIN stock_correspond_position scp ON sc.id = scp.correspond_id
        LEFT JOIN king_dee_stock kds ON scp.stock_id = kds.id
        where sc.id=#{id}
    </select>


    <select id="listPosition" resultType="com.zfire.mall.manager.plus.entity.KingDeeStock">
        SELECT
            kds.*
        FROM
            king_dee_stock kds
                LEFT JOIN stock_correspond_position scp ON kds.id = scp.stock_id
        where 1=1
            <if test="correspondId != null and correspondId != ''">
                and scp.correspond_id = #{correspondId}
            </if>
        <if test="inquire != null and inquire != ''">
            and kds.Inquire = #{inquire}
        </if>
        order by kds.create_time desc
    </select>

    <select id="visitAccList" resultType="com.zfire.mall.manager.bean.admin.AdminUserVisitAccBean">
          select
            b.old_number as 'materialOldNumber',
            b.number as 'materialNumber',
            b.name as 'materialName',
            b.specification,
            c.name as 'stockName',
            a.update_time
        from stock_acc a
        join king_dee_material b on a.material_id = b.id
        join king_dee_stock c on c.id=a.stock_id
        where a.direct_flag=1 and a.ref_type != '调拨单'

            <if test="visitTime != null and visitTime !=''">
                and a.update_time >= #{visitTime}
            </if>
            <if test="specification != null and specification !=''">
                and b.specification like concat('%',#{specification},'%')
            </if>
            <if test="materialIds != null and materialIds.size > 0">
                and a.material_id in
                <foreach item="item" index="index" collection="materialIds" open="(" separator=","
                         close=")">
                    #{item}
                </foreach>
            </if>
            <if test="correspondIds != null and correspondIds.size > 0">
                and a.stock_id in (
                    select stock_id from stock_correspond_position where correspond_id in
                    <foreach item="item" index="index" collection="correspondIds" open="(" separator=","
                             close=")">
                        #{item}
                    </foreach>
                )
            </if>

        order by a.update_time desc,a.id desc
    </select>


    <select id="visitAccCount" resultType="java.lang.Integer">
        select
            count(1) total
        from stock_acc a
        join king_dee_material b on a.material_id = b.id
        join king_dee_stock c on c.id=a.stock_id
        <where>
            <if test="visitTime != null and visitTime !=''">
                a.update_time >= #{visitTime}
            </if>
        </where>
    </select>

    <select id="listV2" resultType="com.zfire.mall.manager.bean.supplier.KingDeeStockVO">
        SELECT
        a.*
        FROM
        king_dee_stock a
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listStockV2" resultType="com.zfire.mall.manager.bean.k3.StockPositionStockVO">
        SELECT
        a.*,
        group_concat(c.name) 'stockName'
        FROM
        stock_correspond a
        join stock_correspond_position b on a.id = b.correspond_id
        join king_dee_stock c on b.stock_id = c.id
        ${ex.query}
        GROUP BY a.id
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
        ${ex.orderBy}
    </select>
    <select id="listTollV2" resultType="com.zfire.mall.manager.bean.listV2.StockTollVO">
        SELECT
        a.*
        FROM
        stock_toll a
        ${ex.query}
        <if test="ex.orderBy == null or ex.orderBy ==''">
            order by a.create_time desc
        </if>
        ${ex.orderBy}
    </select>


</mapper>