<?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.goafanti.common.dao.MemberGradeMapper">


	<resultMap id="memberOrderMap" type="com.goafanti.common.model.MemberOrder">
		<id column="id" property="id" />
		<result column="menber_type" property="menberType" />
		<result column="grade_name" property="gradeName" />
		<result column="price" property="price" />
		<result column="valid_month" property="validMonth" />
		<result column="order_state" property="orderState" />
		<result column="order_time" property="orderTime" />
		<result column="user_id" property="userId" />
		<result column="payment_time" property="paymentTime" />
		<result column="payment_value" property="paymentValue" />
		<result column="payment_type" property="paymentType" />
		<result column="payment_id" property="paymentId" />
		<result column="valid_time" property="validTime" />
	</resultMap>


	<resultMap id="memberGradeMap" type="com.goafanti.member.bo.MemberGrade">
		<id column="id" property="id" />
		<result column="grade_name" property="gradeName" />
		<result column="business" property="business" />
		<result column="cost" property="cost" />
		<result column="member_rank" property="memberRank" />
	</resultMap>



	<!-- 查询所有 -->
	<select id="selectMemberGrade" resultMap="memberGradeMap">
		SELECT * FROM
		member_grade;
	</select>

	<!-- 删除会员等级 -->
	<delete id="delGrade" parameterType="int">
		DELETE FROM member_grade
		WHERE id=#{id}
	</delete>

	<!-- 新增会员等级是否存在 -->
	<select id="selectRank" parameterType="com.goafanti.member.bo.MemberGrade"
		resultType="int">
		SELECT COUNT(*) FROM member_grade WHERE
		member_rank=#{memberRank}
	</select>


	<!-- 新增会员等级 -->
	<insert id="addGradeName" parameterType="com.goafanti.member.bo.MemberGrade"
		useGeneratedKeys="true" keyProperty="id">
		INSERT INTO
		member_grade(grade_name,member_rank) VALUES (#{gradeName},#{memberRank})
	</insert>


	<!-- 为等级添加业务和费用 -->
	<update id="addBusinessAndCost" parameterType="com.goafanti.member.bo.MemberGrade">
		UPDATE
		member_grade SET business=#{business},cost=#{cost} WHERE id=#{id}
	</update>


	<!-- 前台会员订单查询 -->
	<select id="selectMemberOrder" parameterType="java.util.Map"
		resultMap="memberOrderMap">
		SELECT mo.id,menber_type , mg.grade_name, price,
		valid_month,order_state ,order_time ,user_id ,
		payment_time,payment_value,payment_type ,payment_id ,valid_time
		FROM member_order mo
		LEFT JOIN member_grade mg ON mg.member_rank=mo.menber_type
		WHERE user_id=#{id} and mo.order_state &lt; 9
		LIMIT #{pNos},#{pSize}
	</select>


	<!-- 后台会员订单查询 -->
	<select id="selectMemberOrderBa" parameterType="java.util.Map"
		resultMap="memberOrderMap">
		SELECT mo.id,menber_type ,u.mobile ,u.nickname,mg.grade_name, price,
		valid_month,order_state ,order_time ,user_id ,
		payment_time
		,payment_value,payment_type ,payment_id ,valid_time FROM member_order
		mo
		LEFT JOIN member_grade mg ON mg.member_rank=mo.menber_Type
		LEFT JOIN user u ON
		u.id=mo.user_id
		WHERE 1=1
		<if test="id!=null">AND mo.id = #{id}</if>
		<if test="mobile!=null">AND u.mobile LIKE CONCAT('%',#{mobile},'%')</if>
		<if test="nickname!=null"> AND u.nickname LIKE CONCAT('%',#{nickname},'%')</if>
		<if test="orderState!=null">AND order_state = #{orderState}</if>
		<if test="orderState==null">AND order_state &lt; 9</if>
		<if test="startTimes!=null"> AND order_time&gt;=#{startTimes}</if>
		<if test="endTimes!=null">AND order_time&lt;=#{endTimes}</if>
		LIMIT #{pNos},#{pSize}
	</select>


	<!-- 后台查询总记录 -->
	<select id="countMemberOrderWhere" parameterType="java.util.Map"
		resultType="int">
		SELECT COUNT(*) FROM member_order mo
		LEFT JOIN user u ON u.id=mo.user_id
		WHERE 1=1
		<if test="id!=null">AND mo.id = #{id}</if>
		<if test="mobile!=null">AND u.mobile LIKE CONCAT('%',#{mobile},'%')</if>
		<if test="nickname!=null"> AND u.nickname LIKE CONCAT('%',#{nickname},'%')</if>
		<if test="orderState!=null">AND order_state = #{orderState}</if>
		<if test="orderState==null">AND order_state &lt; 9</if>
		<if test="startTimes!=null"> AND order_time&gt;=#{startTimes}</if>
		<if test="endTimes!=null">AND order_time&lt;=#{endTimes}</if>
	</select>

	<!-- 前台生成订单 -->
	<insert id="addBasicsMemberOrder" parameterType="com.goafanti.common.model.MemberOrder">
		INSERT INTO
		member_order(id,menber_type,price,valid_month,order_state,order_time,user_id)
		VALUES(#{id},#{menberType},#{price},#{validMonth},#{orderState},#{orderTime},#{userId})
	</insert>

	<select id="countMemberOrder" parameterType="String"  resultType="int">
		SELECT COUNT(*) FROM
		member_order
		WHERE  user_id=#{userId} and order_state &lt; 9
	</select>
	
	<select id="selectMemberBusiness" parameterType="int"  resultType="java.lang.String">
		SELECT business FROM
		member_grade
		WHERE  member_rank=#{memberRank}
	</select>

	<!-- 后台订单更改 -->
	<update id="updateMemberOrder" parameterType="java.util.Map" >
		UPDATE member_order SET 
		menber_type=#{menberType},
		valid_month=#{validMonth},
		payment_value=#{paymentValue},
		order_state=#{orderState},
		payment_time=#{paymentTime},
		payment_type=#{paymentType},
		payment_id=#{paymentId} 
		WHERE id=#{id}
	</update>
	
	<!-- 后台订单软删除 -->
	<update id="delMemberOrder">
		UPDATE member_order SET order_state=9 
		WHERE id=#{id} and order_state = 0
		<if test="uid!=null">AND user_id = #{uid}</if>
	</update>

</mapper>