<?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.ConsultOrderMapper">

	<select id="findConsultOrderListByPage" resultType="org.springframework.ui.ModelMap">
		select
		co.id as id,
		co.price as price,
		co.order_state as orderState,
		co.order_time as orderTime,
		DATE_FORMAT(co.order_time, '%Y-%m-%d %H:%I:%S') as orderTimeFormatted,
		co.user_id as userId,
		co.to_user_id as toUserId,
		co.payment_time as paymentTime,
		DATE_FORMAT(co.payment_time, '%Y-%m-%d %H:%I:%S') as paymentTimeFormatted,
		co.payment_value as paymentValue,
		co.payment_type as paymentType,
		co.payment_id as paymentId,
		u.nickname as nickname,
		u.number as number,
		tu.nickname as toNickname,
		tui.username as toUsername,
		tu.number as toNumber
		from consult_order co
		left join `user` u on co.user_id = u.id
		left join `user` tu on co.to_user_id = tu.id
		left join `user_identity` tui on co.to_user_id = tui.uid
		where 1=1
		<if test="number != null">
			and u.number= #{number,jdbcType=INTEGER}
		</if>
		<if test="tnumber != null">
			and tu.number= #{tnumber,jdbcType=INTEGER}
		</if>
		<if test="uid != null">
			and u.id= #{uid,jdbcType=VARCHAR}
		</if>
		<if test="id != null">
			and co.id= #{id,jdbcType=BIGINT}
		</if>
		<if test="pid != null">
			and co.payment_id= #{pid,jdbcType=VARCHAR}
		</if>
		<if test="state != null">
			and co.order_state= #{state,jdbcType=INTEGER}
		</if>
		<if test="state == null">
			and co.order_state &lt; 9
		</if>
		order by co.id DESC
		<if test="page_sql!=null">
			${page_sql}
		</if>
	</select>

	<select id="findConsultOrderCount" resultType="java.lang.Integer">
		select count(*)
		from consult_order co
		<if test="number != null">
		left join `user` u on co.user_id = u.id
		</if>
		<if test="tnumber != null">
		left join `user` tu on co.to_user_id = tu.id
		</if>
		where 1=1
		<if test="number != null">
			and u.number= #{number,jdbcType=INTEGER}
		</if>
		<if test="tnumber != null">
			and tu.number= #{tnumber,jdbcType=INTEGER}
		</if>
		<if test="uid != null">
			and co.user_id= #{uid,jdbcType=VARCHAR}
		</if>
		<if test="id != null">
			and co.id= #{id,jdbcType=BIGINT}
		</if>
		<if test="pid != null">
			and co.payment_id= #{pid,jdbcType=VARCHAR}
		</if>
		<if test="state != null">
			and co.order_state= #{state,jdbcType=INTEGER}
		</if>
		<if test="state == null">
			and co.order_state &lt; 9
		</if>
	</select>

</mapper>