<?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="cn.pluss.platform.mapper.BankUnionpayCodeMapper">

	   	<insert id="saveBankUnionpayCode" parameterType="cn.pluss.platform.entity.BankUnionpayCode">
        	insert into tb_pluss_bank_unionpay_code(f_bank_area,f_bank_city,f_cft_area_code,f_cft_city_code,f_unionpay_code,f_branch_name,f_bank_name,f_x,f_y)VALUES(#{f_bank_area},#{f_bank_city},#{f_cft_area_code},#{f_cft_city_code},#{f_unionpay_code},#{f_branch_name},#{f_bank_name},#{f_x},#{f_y})
    	</insert>

	   	<insert id="saveBankUnionpayCodeBatch">
        	insert into tb_pluss_bank_unionpay_code(f_bank_area,f_bank_city,f_cft_area_code,f_cft_city_code,f_unionpay_code,f_branch_name,f_bank_name,f_x,f_y) VALUES 
    			<foreach collection="list" item="item" index="index" separator=",">
			(#{f_bank_area},#{f_bank_city},#{f_cft_area_code},#{f_cft_city_code},#{f_unionpay_code},#{f_branch_name},#{f_bank_name},#{f_x},#{f_y}		)
				</foreach>
    	</insert>

		<select id="queryBankUnionpayCode" parameterType="cn.pluss.platform.entity.BankUnionpayCode" resultType="cn.pluss.platform.entity.BankUnionpayCode">
			SELECT * from tb_pluss_bank_unionpay_code 
				<where> 
					<if test="f_bank_area!= null"> 
					 	and f_bank_area = #{f_bank_area}
					 </if> 
					<if test="f_bank_city!= null"> 
					 	and f_bank_city = #{f_bank_city}
					 </if> 
					<if test="f_cft_area_code!= null"> 
					 	and f_cft_area_code = #{f_cft_area_code}
					 </if> 
					<if test="f_cft_city_code!= null"> 
					 	and f_cft_city_code = #{f_cft_city_code}
					 </if> 
					<if test="f_unionpay_code!= null"> 
					 	and f_unionpay_code = #{f_unionpay_code}
					 </if> 
					<if test="f_branch_name!= null"> 
					 	and f_branch_name = #{f_branch_name}
					 </if> 
					<if test="f_bank_name!= null"> 
					 	and f_bank_name = #{f_bank_name}
					 </if> 
					<if test="f_x!= null"> 
					 	and f_x = #{f_x}
					 </if> 
					<if test="f_y!= null"> 
					 	and f_y = #{f_y}
					 </if> 
				 </where> 
		</select>

		<select id="queryBankUnionpayCodeList" parameterType="cn.pluss.platform.entity.BankUnionpayCode" resultType="cn.pluss.platform.entity.BankUnionpayCode">
			SELECT * from tb_pluss_bank_unionpay_code 
				<where> 
					<if test="f_bank_area!= null"> 
					 and 	f_bank_area = #{f_bank_area}
					 </if> 
					<if test="f_bank_city!= null"> 
					 and 	f_bank_city = #{f_bank_city}
					 </if> 
					<if test="f_cft_area_code!= null"> 
					 and 	f_cft_area_code = #{f_cft_area_code}
					 </if> 
					<if test="f_cft_city_code!= null"> 
					 and 	f_cft_city_code = #{f_cft_city_code}
					 </if> 
					<if test="f_unionpay_code!= null"> 
					 and 	f_unionpay_code = #{f_unionpay_code}
					 </if> 
					<if test="f_branch_name!= null"> 
					 and 	f_branch_name = #{f_branch_name}
					 </if> 
					<if test="f_bank_name!= null"> 
					 and 	f_bank_name = #{f_bank_name}
					 </if> 
					<if test="f_x!= null"> 
					 and 	f_x = #{f_x}
					 </if> 
					<if test="f_y!= null"> 
					 and 	f_y = #{f_y}
					 </if> 
				 </where> 
		</select>

		<update id="updateBankUnionpayCode" parameterType="cn.pluss.platform.entity.BankUnionpayCode" >
			update tb_pluss_bank_unionpay_code 
				  <set> 
					<if test="f_bank_area!= null"> 
					 	f_bank_area = #{f_bank_area},
					 </if> 
					<if test="f_bank_city!= null"> 
					 	f_bank_city = #{f_bank_city},
					 </if> 
					<if test="f_cft_area_code!= null"> 
					 	f_cft_area_code = #{f_cft_area_code},
					 </if> 
					<if test="f_cft_city_code!= null"> 
					 	f_cft_city_code = #{f_cft_city_code},
					 </if> 
					<if test="f_unionpay_code!= null"> 
					 	f_unionpay_code = #{f_unionpay_code},
					 </if> 
					<if test="f_branch_name!= null"> 
					 	f_branch_name = #{f_branch_name},
					 </if> 
					<if test="f_bank_name!= null"> 
					 	f_bank_name = #{f_bank_name},
					 </if> 
					<if test="f_x!= null"> 
					 	f_x = #{f_x},
					 </if> 
					<if test="f_y!= null"> 
					 	f_y = #{f_y}
					 </if> 
				</set>
			where id=#{id} 
		</update>

		<delete id="deleteBankUnionpayCode" parameterType="cn.pluss.platform.entity.BankUnionpayCode" >
			DELETE FROM tb_pluss_bank_unionpay_code where id=#{id}
		</delete>

		<select id="queryBankUnionpayCodePage" parameterType="java.util.Map" resultType="cn.pluss.platform.entity.BankUnionpayCode">
			SELECT * from tb_pluss_bank_unionpay_code 
				<where> 
					<if test="f_bank_area!= null"> 
					and f_bank_area = #{f_bank_area}
					 </if> 
					<if test="f_bank_city!= null"> 
					and f_bank_city = #{f_bank_city}
					 </if> 
					<if test="f_cft_area_code!= null"> 
					and f_cft_area_code = #{f_cft_area_code}
					 </if> 
					<if test="f_cft_city_code!= null"> 
					and f_cft_city_code = #{f_cft_city_code}
					 </if> 
					<if test="f_unionpay_code!= null"> 
					and f_unionpay_code = #{f_unionpay_code}
					 </if> 
					<if test="f_branch_name!= null"> 
					and f_branch_name = #{f_branch_name}
					 </if> 
					<if test="f_bank_name!= null"> 
					and f_bank_name = #{f_bank_name}
					 </if> 
					<if test="f_x!= null"> 
					and f_x = #{f_x}
					 </if> 
					<if test="f_y!= null"> 
					and f_y = #{f_y}
					 </if> 
				 </where> 
			order by id desc  limit #{pageSize}  offset  #{offset}  
		</select>

		<select id="queryBankUnionpayCodePageCount" parameterType="java.util.Map" resultType="java.lang.Integer">
			SELECT count(*) from tb_pluss_bank_unionpay_code 
					<where> 
					<if test="f_bank_area!= null"> 
					 and f_bank_area = #{f_bank_area}
					 </if> 
					<if test="f_bank_city!= null"> 
					 and f_bank_city = #{f_bank_city}
					 </if> 
					<if test="f_cft_area_code!= null"> 
					 and f_cft_area_code = #{f_cft_area_code}
					 </if> 
					<if test="f_cft_city_code!= null"> 
					 and f_cft_city_code = #{f_cft_city_code}
					 </if> 
					<if test="f_unionpay_code!= null"> 
					 and f_unionpay_code = #{f_unionpay_code}
					 </if> 
					<if test="f_branch_name!= null"> 
					 and f_branch_name = #{f_branch_name}
					 </if> 
					<if test="f_bank_name!= null"> 
					 and f_bank_name = #{f_bank_name}
					 </if> 
					<if test="f_x!= null"> 
					 and f_x = #{f_x}
					 </if> 
					<if test="f_y!= null"> 
					 and f_y = #{f_y}
					 </if> 
				</where>
		</select>
		
		<!--查询银行  -->
		<select id="getBankNameList" resultType="java.lang.String">
		     select  F_bank_name from tb_pluss_bank_unionpay_code GROUP BY F_bank_name
		</select>
		
		<resultMap type="cn.pluss.platform.leshua.ProvinceVO" id="provinceVO">
		      <result property="provinceCode" column="F_cft_area_code" jdbcType="VARCHAR"></result>
		      <result property="provinceName" column="F_bank_area" jdbcType="VARCHAR"></result>
		</resultMap>
		
		<resultMap type="cn.pluss.platform.leshua.CityVO" id="cityVO">
		      <result property="cityCode" column="F_cft_city_code" jdbcType="VARCHAR"></result>
              <result property="cityName" column="F_bank_city" jdbcType="VARCHAR"></result>
		</resultMap>
		
		<resultMap type="cn.pluss.platform.leshua.BankNameVO" id="bankNameVO">
              <result property="unionpayCode" column="F_unionpay_code" jdbcType="VARCHAR"></result>
              <result property="branchName" column="F_branch_name" jdbcType="VARCHAR"></result>
        </resultMap>
		
		<!-- 省份 -->
		<select id="getProvince" resultMap="provinceVO">
          select F_cft_area_code,F_bank_area from(select F_cft_area_code,F_bank_area from tb_pluss_bank_unionpay_code order by (F_cft_area_code+0) ASC)a GROUP BY (a.f_cft_area_code+0)
        </select>
        
        <!--省下面的市  -->
        <select id="getCity" resultMap="cityVO">
            SELECT F_cft_city_code,F_bank_city FROM tb_pluss_bank_unionpay_code WHERE F_cft_area_code=#{provinceCode}  GROUP BY F_cft_city_code
        </select>
        
        <!--根据选择的银行，省，市  查下面的支行  -->
        <select id="getBranchName" resultMap="bankNameVO">
            SELECT F_unionpay_code,F_branch_name from tb_pluss_bank_unionpay_code WHERE F_branch_name like CONCAT(#{bankName}, "%", #{areaCode}, "%")
            <if test="brankName!=null and brankName!=''">
             AND
             F_branch_name like CONCAT("%",#{brankName},"%")
             </if>
              GROUP BY F_unionpay_code
        </select>

</mapper>
