<?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.tmerclub.cloud.supplier.mapper.SupplierDetailMapper">
  <resultMap id="supplierDetailMap" type="com.tmerclub.cloud.supplier.model.SupplierDetail">
    <id column="supplier_id" property="supplierId" />
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="type" property="type"/>
    <result column="supplier_name" property="supplierName"/>
    <result column="intro" property="intro"/>
    <result column="notice_mobile" property="noticeMobile"/>
    <result column="supplier_logo" property="supplierLogo"/>
    <result column="supplier_status" property="supplierStatus"/>
    <result column="supplier_shop_name" property="supplierShopName"/>
    <result column="contact_name" property="contactName"/>
    <result column="contact_phone" property="contactPhone"/>
    <result column="contract_start_time" property="contractStartTime"/>
    <result column="contract_end_time" property="contractEndTime"/>
    <result column="is_create_member" property="isCreateMember"/>
    <result column="is_bind_phone" property="isBindPhone"/>
    <result column="company_info_process_status" property="companyInfoProcessStatus"/>
    <result column="id_card_collect_process_status" property="idCardCollectProcessStatus"/>
    <result column="allinpay_supplier_status" property="allinpaySupplierStatus"/>
    <result column="company_acct_protocol_no" property="companyAcctProtocolNo"/>
    <result column="legal_acct_protocol_no" property="legalAcctProtocolNo"/>
  </resultMap>
  <sql id="Vo_Column_List">
    `supplier_id`,`create_time`,`update_time`,`type`,`supplier_shop_name`,`intro`,`notice_mobile`,`supplier_logo`,`supplier_status`,
    `is_preferred`,`supplier_name`,`contact_name`,`contact_phone`,`contract_start_time`,`contract_end_time`,
    `is_create_member`,`is_bind_phone`,`company_info_process_status`,`id_card_collect_process_status`,`allinpay_supplier_status`,
    `company_acct_protocol_no`,`legal_acct_protocol_no`
  </sql>
  <select id="list" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
    select <include refid="Vo_Column_List"/>
    from supplier_detail
    where supplier_status != -1
    <if test="supplierDetail.paySysType == null or supplierDetail.paySysType != 1">
      and supplier_status != 3 and supplier_status != 4
    </if>
        <if test="supplierDetail.supplierName != null and supplierDetail.supplierName != ''">
            and supplier_name like concat('%', #{supplierDetail.supplierName}, '%')
        </if>
        <if test="supplierDetail.supplierStatus != null">
          <if test="supplierDetail.supplierStatus == 5 and supplierDetail.paySysType == 1">
            and allinpay_supplier_status = 3 or supplier_status = 5
          </if>
          <if test="supplierDetail.supplierStatus == 2 and supplierDetail.paySysType == 1">
            and allinpay_supplier_status = 2 or supplier_status = 2
          </if>
          <if test="supplierDetail.supplierStatus == 3 and supplierDetail.paySysType == 1">
            and allinpay_supplier_status = 4 or supplier_status = 3
          </if>
          <if test="supplierDetail.supplierStatus == 6 and supplierDetail.paySysType == 1">
            and allinpay_supplier_status = 2 and company_info_process_status = 3
          </if>
          <if test="supplierDetail.supplierStatus != 5 and supplierDetail.supplierStatus != 2 and supplierDetail.supplierStatus != 3 and supplierDetail.supplierStatus != 6 and supplierDetail.paySysType == 1">
            and supplier_status = #{supplierDetail.supplierStatus}
          </if>
          <if test="supplierDetail.paySysType == 0">
            and supplier_status = #{supplierDetail.supplierStatus}
          </if>
        </if>
        <if test="supplierDetail.isPreferred != null">
            and is_preferred = #{supplierDetail.isPreferred}
        </if>
    order by update_time desc
  </select>
  <select id="getBySupplierId" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
    select `supplier_id`,`type`,`supplier_shop_name`,`intro`,`notice_mobile`,`supplier_logo`,`supplier_status`,`is_preferred`,
           mobile_background_pic,pc_background_pic,`contact_name`,`supplier_name`,
           `contact_name`,`contact_phone`,`contract_start_time`,`contract_end_time`,
          `is_create_member`,`is_bind_phone`,`company_info_process_status`,`id_card_collect_process_status`,`allinpay_supplier_status`,
          `company_acct_protocol_no`,`legal_acct_protocol_no`,`type`
    from supplier_detail where supplier_id = #{supplierId} and supplier_status != -1 limit 1
  </select>
  <select id="getShoExtensionsBySupplierId" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
    select s.`supplier_id`,s.intro,s.`type`,s.`supplier_shop_name`,s.`intro`,s.`notice_mobile`,s.`supplier_logo`,s.`supplier_status`,
           s.is_preferred,s.type,se.collection_num,s.mobile_background_pic,s.pc_background_pic,s.contract_start_time,s.contract_end_time
    from supplier_detail s
    left join supplier_extension se on se.supplier_id = s.supplier_id
    where s.supplier_id = #{supplierId}
  </select>

  <select id="getSupplierBySupplierId" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
    select <include refid="Vo_Column_List"/> from supplier_detail sd
    where sd.supplier_id = #{supplierId}
  </select>
  <insert id="save" useGeneratedKeys="true" keyProperty="supplierId">
    insert into supplier_detail (`intro`,`notice_mobile`,`supplier_logo`,`mobile_background_pic`,`pc_background_pic`,`supplier_status`,`type`,`is_preferred`,`supplier_name`,`contact_name`,`contact_phone`,`contract_start_time`,`contract_end_time`)
    values (#{supplierDetail.intro},#{supplierDetail.noticeMobile},#{supplierDetail.supplierLogo},#{supplierDetail.mobileBackgroundPic},#{supplierDetail.pcBackgroundPic},#{supplierDetail.supplierStatus},#{supplierDetail.type},#{supplierDetail.isPreferred},#{supplierDetail.supplierName},#{supplierDetail.contactName},#{supplierDetail.contactPhone},#{supplierDetail.contractStartTime},#{supplierDetail.contractEndTime});
  </insert>
  <update id="update">
    update supplier_detail
    <set>
      <if test="supplierDetail.supplierShopName != null">
        `supplier_shop_name` = #{supplierDetail.supplierShopName},
      </if>
      <if test="supplierDetail.intro != null">
        `intro` = #{supplierDetail.intro},
      </if>
      <if test="supplierDetail.noticeMobile != null">
        `notice_mobile` = #{supplierDetail.noticeMobile},
      </if>
      <if test="supplierDetail.supplierLogo != null">
        `supplier_logo` = #{supplierDetail.supplierLogo},
      </if>
      <if test="supplierDetail.mobileBackgroundPic != null">
        `mobile_background_pic` = #{supplierDetail.mobileBackgroundPic},
      </if>
      <if test="supplierDetail.pcBackgroundPic != null">
        `pc_background_pic` = #{supplierDetail.pcBackgroundPic},
      </if>
      <if test="supplierDetail.supplierStatus != null">
        `supplier_status` = #{supplierDetail.supplierStatus},
      </if>
      <if test="supplierDetail.type != null">
        `type` = #{supplierDetail.type},
      </if>
      <if test="supplierDetail.isPreferred != null">
        `is_preferred` = #{supplierDetail.isPreferred},
      </if>
      <if test="supplierDetail.supplierName != null">
        `supplier_name` = #{supplierDetail.supplierName},
      </if>
      <if test="supplierDetail.contactName != null">
        `contact_name` = #{supplierDetail.contactName},
      </if>
      <if test="supplierDetail.contactPhone != null">
        `contact_phone` = #{supplierDetail.contactPhone},
      </if>
      <if test="supplierDetail.contractStartTime != null">
        `contract_start_time` = #{supplierDetail.contractStartTime},
      </if>
      `contract_end_time` = #{supplierDetail.contractEndTime}
    </set>
    where supplier_id = #{supplierDetail.supplierId} limit 1
  </update>
  <delete id="deleteById">
    delete from supplier_detail where supplier_id = #{supplierId}
  </delete>

  <select id="listBySupplierIds" resultMap="supplierDetailMap">
    select `supplier_id`,`supplier_shop_name`,`supplier_name`,`supplier_logo`,`type`,`contact_phone`, `contact_name` from supplier_detail where supplier_id in
    <foreach collection="supplierIds" item="supplierId" open="(" close=")" separator=",">
        #{supplierId}
    </foreach>
  </select>
  <select id="supplierSearchList" resultType="com.tmerclub.cloud.supplier.vo.SupplierDetailAppVO">
    select sd.supplier_id,sd.type,sd.supplier_shop_name,sd.intro,sd.supplier_logo,sd.mobile_background_pic,sd.pc_background_pic,se.collection_num from supplier_detail sd
    LEFT JOIN supplier_extension se ON sd.supplier_id = se.supplier_id
    where sd.supplier_status = 1
    <if test="supplierDetail.paySysType == 1">
      and sd.allinpay_supplier_status = 1
    </if>
    <if test="supplierDetail.supplierName != null and supplierDetail.supplierName != ''">
        and sd.supplier_name like concat('%', #{supplierDetail.supplierName}, '%')
    </if>
    <if test="supplierDetail.isPreferred != null ">
        and sd.is_preferred = #{supplierDetail.isPreferred}
    </if>
    order by sd.type,sd.is_preferred desc
  </select>

  <select id="countSupplierName" resultType="int">
    select ifnull(count(*),0) from supplier_detail where supplier_name = #{supplierName} and supplier_status != -1
    <if test="supplierId != null">
        and supplier_id != #{supplierId}
    </if>
  </select>
  <update id="changeSupplierStatus">
    update supplier_detail set `supplier_status` = #{supplierStatus} where supplier_id = #{supplierId}
  </update>
  <update id="changeSupplierStatusByContractTime">
    update supplier_detail
    <if test="now != null">
      set `supplier_status` =
      if(#{now} &gt;= contract_start_time and (#{now} &lt; contract_end_time or contract_end_time is null), 1, 0)
    </if>
    where `supplier_status` = 1 or `supplier_status` = 0
  </update>

  <select id="supplierExtensionData" resultType="com.tmerclub.cloud.api.supplier.bo.SupplierDetailBO">
    SELECT ad.supplier_id,ad.supplier_shop_name,ad.type,ad.supplier_logo,se.sale_num,se.collection_num FROM supplier_detail ad
    JOIN supplier_extension se ON ad.supplier_id = se.supplier_id
    WHERE ad.supplier_id = #{supplierId}
  </select>

  <select id="getSupplierDetailBySupplierIdAndSupplierName" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
    SELECT sd.supplier_id,sd.supplier_name,sd.type,sd.supplier_logo,sd.supplier_status FROM supplier_detail sd
    <where>
      <if test="supplierIds != null and supplierIds.size()>0">
        sd.supplier_id in
        <foreach collection="supplierIds" item="supplierId" open="(" separator="," close=")">
          #{supplierId}
        </foreach>
      </if>
      <if test="supplierName != null and supplierName != ''">
        and sd.supplier_name like concat('%',#{supplierName},'%')
      </if>
    </where>
  </select>

  <select id="countByMobile" resultType="int">
    SELECT ifnull(count(*),0) FROM supplier_detail WHERE supplier_status = 1 AND notice_mobile = #{mobile}
  </select>
  <select id="countBySupplierStatus" resultType="java.lang.Long">
    SELECT ifnull(count(*), 0) FROM supplier_detail WHERE supplier_status = #{supplierStatus}
  </select>
  <select id="listSupplierIdsOfStatusChangeToStopByContractTime" resultType="java.lang.Long">
    SELECT supplier_id FROM supplier_detail
    WHERE (#{now} &lt; contract_start_time or #{now} &gt;= contract_end_time) and supplier_status = 1
  </select>
  <select id="listSupplierIdsOfStatusChangeToOpenByContractTime" resultType="java.lang.Long">
    SELECT supplier_id FROM supplier_detail
    WHERE #{now} &gt;= contract_start_time and (#{now} &lt; contract_end_time or contract_end_time is null) and supplier_status = 0
  </select>
  <select id="listSimple" resultType="com.tmerclub.cloud.api.supplier.bo.SupplierSimpleBO">
    SELECT supplier_id, supplier_name, supplier_status FROM supplier_detail
    <where>
      <if test="supplierSimpleBO.supplierName != null and supplierSimpleBO.supplierName != ''">
        AND supplier_name like concat('%', #{supplierSimpleBO.supplierName}, '%')
      </if>
      <if test="supplierSimpleBO.supplierStatus != null">
        AND supplier_status = #{supplierSimpleBO.supplierStatus}
      </if>
      <if test="supplierSimpleBO.type != null">
        AND `type` = #{supplierSimpleBO.type}
      </if>
      <if test="supplierSimpleBO.supplierIds != null">
        AND supplier_id in
        <foreach collection="supplierSimpleBO.supplierIds" item="supplierId" open="(" close=")" separator=",">
          #{supplierId}
        </foreach>
      </if>
    </where>
    <if test="supplierSimpleBO.seq == 1">
      ORDER BY supplier_id ASC
    </if>
    <if test="supplierSimpleBO.seq == 2">
      ORDER BY supplier_id DESC
    </if>
  </select>
  <select id="getSupplierIdsBySupplierStatus" resultType="java.lang.Long">
    SELECT `supplier_id` FROM `supplier_detail` WHERE `supplier_status` = #{supplierStatus}
  </select>

  <select id="listSupplierDetail" resultType="com.tmerclub.cloud.supplier.vo.SupplierDetailVO">
    SELECT s.*
    FROM `supplier_detail` s
    <where>
      s.supplier_status = 1
--       商家端
      <if test="supplier.paySysType == 1">
        and s.allinpay_supplier_status = 1
      </if>
      <if test="supplier.sysType == 1">
        and s.supplier_id in
        <foreach collection="supplierIds" item="supplierId" open="(" close=")" separator=",">
          #{supplierId}
        </foreach>
      </if>
    <if test="supplier.supplierName != null and supplier.supplierName != ''">
        and s.supplier_name LIKE CONCAT("%",#{supplier.supplierName},"%")
    </if>
    </where>
    order by s.create_time desc
  </select>

  <select id="listSupplierDetailBySupplierIds" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
    SELECT sd.supplier_id,sd.supplier_name,sd.contact_phone
    FROM supplier_detail sd
    <where>
      <if test="supplierIds != null and supplierIds.size()>0">
        sd.supplier_id in
        <foreach collection="supplierIds" item="supplierId" open="(" separator="," close=")">
          #{supplierId}
        </foreach>
      </if>
    </where>
  </select>
  <select id="listUnCreateMemberSupplierIds" resultType="java.lang.Long">
    SELECT supplier_id FROM supplier_detail WHERE is_create_member = 0 and supplier_status != -1
  </select>
  <update id="updateSupplierToStop">
    UPDATE supplier_detail set allinpay_supplier_status = 2
    where supplier_status in (0,1,2,5) and supplier_id in
    <foreach collection="supplierIds" item="supplierId" open="(" close=")" separator=",">
      #{supplierId}
    </foreach>
  </update>
  <update id="updateSupplierToApplying">
    UPDATE supplier_detail set allinpay_supplier_status = 0, supplier_status = 4
    where supplier_status = 3 and supplier_id in
    <foreach collection="supplierIds" item="supplierId" open="(" close=")" separator=",">
      #{supplierId}
    </foreach>
  </update>
  <update id="updateSupplierIsCreate">
    UPDATE supplier_detail set is_create_member = 1
    where supplier_id in
    <foreach collection="supplierIds" item="supplierId" open="(" close=")" separator=",">
      #{supplierId}
    </foreach>
  </update>
  <update id="updateAllinpaySupplierStatus">
    update supplier_detail set allinpay_supplier_status = #{status}
    where supplier_id = #{supplierId} and is_create_member = 1
  </update>
  <update id="updateSupplierAuditSuccess">
    update supplier_detail set company_info_process_status = 2
    where supplier_id = #{supplierId} and is_create_member = 1
  </update>
  <update id="updateSupplierAuditFail">
    update supplier_detail set company_info_process_status = 3, allinpay_supplier_status = 2
    where supplier_id = #{supplierId} and is_create_member = 1
  </update>
  <update id="updateAllinpayIdCardStatus">
    update supplier_detail set id_card_collect_process_status = #{status}
    where supplier_id = #{supplierId}
  </update>
  <update id="updateSupplierToOpen">
    update supplier_detail set allinpay_supplier_status = 1
    where supplier_id = #{supplierId}
    and is_create_member = 1 and company_info_process_status = 2 and id_card_collect_process_status = 4
  </update>
  <update id="updateSupplierToWaitAudit">
    update supplier_detail set allinpay_supplier_status = 3 where supplier_id = #{supplierId} and is_create_member = 1 and company_info_process_status = 2
  </update>
  <update id="updateSupplierBindPhone">
    update supplier_detail set is_bind_phone = #{status}
    where supplier_id = #{supplierId}
  </update>
  <update id="updateAcctProtocolNoByType">
    update supplier_detail
    <set>
      <if test="type == 2">
        legal_acct_protocol_no = #{acctProtocolNo},
      </if>
      <if test="type == 3">
        company_acct_protocol_no = #{acctProtocolNo}
      </if>
    </set>
    where supplier_id = #{supplierId}
  </update>

  <select id="listSupplierIds" resultType="com.tmerclub.cloud.api.supplier.vo.SupplierApiDetailVO">
   select supplier_id,contact_name,contact_phone  from supplier_detail where supplier_status != -1
  </select>
</mapper>
