<?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.pj.tb_orders.TbOrdersMapper">




	<!-- ================================== 查询相关 ================================== -->
	<!-- select id, level_one_order_id, order_no, goods_id, goods_name, goods_from, shipper_phone, shipper_name, driver_id, driver_name, driver_phone, car_id, car_type, consignee_name, consignee_phone, declaration_number, order_finish, loading_address, unloading_address, comment, update_time, create_time, update_name, delete_status, update_by, create_by, create_name, trade_area_id, trade_area_name from tb_orders  -->

	<!-- 通用映射：自动模式 -->
	<resultMap id="model" autoMapping="true" type="com.pj.tb_orders.TbOrders"></resultMap>

	<!-- 公共查询sql片段 -->
	<sql id="select_sql">
		select *
		from tb_orders
	</sql>


	<!-- 查集合 - 根据条件（参数为空时代表忽略指定条件） [G] -->
	<select id="getList" resultMap="model">
		<include refid="select_sql"></include>
		<where>
			<if test=' this.has("id") '> and id = #{id} </if>
			<if test=' this.has("levelOneOrderId") '> and level_one_order_id = #{levelOneOrderId} </if>
			<if test=' this.has("orderNo") '> and order_no like concat('%',#{orderNo},'%') </if>
			<if test=' this.has("goodsId") '> and goods_id = #{goodsId} </if>
			<if test=' this.has("purchaserId") '> and purchaser_id = #{purchaserId} </if>
			<if test=' this.has("goodsName") '> and goods_name like concat('%',#{goodsName},'%') </if>
			<if test=' this.has("goodsFrom") '> and goods_from = #{goodsFrom} </if>
			<if test=' this.has("shipperPhone") '> and shipper_phone = #{shipperPhone} </if>
			<if test=' this.has("shipperName") '> and shipper_name = #{shipperName} </if>
			<if test=' this.has("driverId") '> and driver_id = #{driverId} </if>
			<if test=' this.has("driverName") '> and driver_name = #{driverName} </if>
			<if test=' this.has("driverPhone") '> and driver_phone = #{driverPhone} </if>
			<if test=' this.has("carId") '> and car_id = #{carId} </if>
			<if test=' this.has("veNo") '> and ve_no like concat('%', #{veNo},'%') </if>
			<if test=' this.has("consigneeName") '> and consignee_name = #{consigneeName} </if>
			<if test=' this.has("consigneePhone") '> and consignee_phone = #{consigneePhone} </if>
			<if test=' this.has("declarationNumber") '> and declaration_number = #{declarationNumber} </if>
			<if test=' this.has("orderFinish") '> and order_finish = #{orderFinish} </if>
			<if test=' this.has("loadingAddress") '> and loading_address = #{loadingAddress} </if>
			<if test=' this.has("unloadingAddress") '> and unloading_address = #{unloadingAddress} </if>
			<if test=' this.has("comment") '> and comment = #{comment} </if>
			<if test=' this.has("updateTime") '> and update_time = #{updateTime} </if>
			<if test=' this.has("createTime") '> and create_time = #{createTime} </if>
			<if test=' this.has("updateName") '> and update_name = #{updateName} </if>
			<if test=' this.has("deleteStatus") '> and delete_status = #{deleteStatus} </if>
			<if test=' this.has("updateBy") '> and update_by = #{updateBy} </if>
			<if test=' this.has("createBy") '> and create_by = #{createBy} </if>
			<if test=' this.has("createName") '> and create_name = #{createName} </if>
			<if test=' this.has("tradeAreaId") '> and trade_area_id = #{tradeAreaId} </if>
			<if test=' this.has("tradeAreaName") '> and trade_area_name = #{tradeAreaName} </if>
			<if test=' this.has("leaderId") '> and leader_id = #{leaderId} </if>
			<if test=' this.has("leaderName") '> and leader_name like concat('%',#{leaderName},'%')  </if>
			<if test=' this.has("fkGoodsDemandId") '> and fk_goods_demand_id = #{fkGoodsDemandId} </if>
			<if test=' this.has("isDelivery") '> and is_delivery = #{isDelivery} </if>
			<if test=' this.has("isPay") '> and is_pay = #{isPay} </if>
			<if test=' this.has("payTax") '> and pay_tax = #{payTax} </if>
			<if test=' this.has("beOrder") and beOrder==1'> and purchaser_id is null </if>
			<if test=' this.has("beOrder") and beOrder==2'> and purchaser_id is not null </if>
			<if test=' this.has("dateBegin") '>and date_format(create_time,'%Y-%m-%d') &gt;=#{dateBegin}</if>
			<if test=' this.has("dateEnd") '>and date_format(create_time,'%Y-%m-%d') &lt;=#{dateEnd}</if>
		</where>
		order by
		<choose>
			<when test='sortType == 1'> id desc </when>
			<when test='sortType == 2'> level_one_order_id desc </when>
			<when test='sortType == 3'> order_no desc </when>
			<when test='sortType == 4'> goods_id desc </when>
			<when test='sortType == 5'> goods_name desc </when>
			<when test='sortType == 6'> goods_from desc </when>
			<when test='sortType == 7'> shipper_phone desc </when>
			<when test='sortType == 8'> shipper_name desc </when>
			<when test='sortType == 9'> driver_id desc </when>
			<when test='sortType == 10'> driver_name desc </when>
			<when test='sortType == 11'> driver_phone desc </when>
			<when test='sortType == 12'> car_id desc </when>
			<when test='sortType == 13'> car_type desc </when>
			<when test='sortType == 14'> consignee_name desc </when>
			<when test='sortType == 15'> consignee_phone desc </when>
			<when test='sortType == 16'> declaration_number desc </when>
			<when test='sortType == 17'> order_finish desc </when>
			<when test='sortType == 18'> loading_address desc </when>
			<when test='sortType == 19'> unloading_address desc </when>
			<when test='sortType == 20'> comment desc </when>
			<when test='sortType == 21'> update_time desc </when>
			<when test='sortType == 22'> create_time desc </when>
			<when test='sortType == 23'> update_name desc </when>
			<when test='sortType == 24'> delete_status desc </when>
			<when test='sortType == 25'> update_by desc </when>
			<when test='sortType == 26'> create_by desc </when>
			<when test='sortType == 27'> create_name desc </when>
			<when test='sortType == 28'> trade_area_id desc </when>
			<when test='sortType == 29'> trade_area_name desc </when>
			<otherwise> id desc </otherwise>
		</choose>
	</select>

	<select id="getLevelTwoList" resultMap="model">
		select * from tb_orders where purchaser_id is null and (purchaser_name is null or purchaser_name='')
		<if test=' this.has("goodsName") '>and (goods_name like concat('%', #{goodsName}, '%') or ve_no like upper(concat('%', #{goodsName}, '%')) )</if>
	</select>

	<!--已完成和累计交易额 -->
	<select id="totalMoney" resultType="java.util.Map">
		SELECT
				( SELECT SUM( resale_price ) FROM tb_orders  <if test='tradeAreaId != null '> WHERE trade_area_id = #{tradeAreaId} </if> ) AS totalMoney,
				( SELECT SUM( resale_price )  FROM tb_orders WHERE order_finish=1 <if test='tradeAreaId != null '> AND trade_area_id = #{tradeAreaId} </if>) AS finishMoney,
				( SELECT COUNT(id)  FROM tb_orders <if test='tradeAreaId != null '> WHERE trade_area_id = #{tradeAreaId} </if>) AS orders
	</select>
	<!--查询所有统计年份 -->
	<select id="selectYear" resultType="java.util.Map">
		SELECT YEAR(create_time) AS year FROM tb_orders GROUP BY YEAR(create_time) ORDER BY year
	</select>
	<!--根据年份查询每月订单交易额和交易量统计 -->
	<select id="orderStatistics" resultType="java.util.Map">
		SELECT
			months.month AS name,
		    <if test="type=='交易额'">
			SUM(tb_orders.resale_price) AS value
			</if>
		    <if test="type=='交易量'">
			COUNT(tb_orders.id) AS value
			</if>
		FROM
			(
			SELECT 1 AS month UNION ALL
			SELECT 2 AS month UNION ALL
			SELECT 3 AS month UNION ALL
			SELECT 4 AS month UNION ALL
			SELECT 5 AS month UNION ALL
			SELECT 6 AS month UNION ALL
			SELECT 7 AS month UNION ALL
			SELECT 8 AS month UNION ALL
			SELECT 9 AS month UNION ALL
			SELECT 10 AS month UNION ALL
			SELECT 11 AS month UNION ALL
			SELECT 12 AS month
			) AS months
			LEFT JOIN
			tb_orders ON
			MONTH(tb_orders.create_time) = months.month AND YEAR(tb_orders.create_time) =#{year}
		    <if test='tradeAreaId != null '> and tb_orders.trade_area_id = #{tradeAreaId} </if>
		GROUP BY
			months.month
		ORDER BY
			months.month
	</select>
    <select id="getOnSaleList" resultType="com.pj.tb_orders.TbOrders">
		select * from tb_orders
		where purchaser_id is null and purchaser_name is  null
		<if test=' this.has("veNo") '>and ve_no like concat('%',#{veNo},'%')</if>
		<if test=' this.has("productName") '>and goods_names like concat('%',#{productName},'%')</if>
		<if test=' this.has("dateBegin") '>and date_format(create_time,'%Y-%m-%d') &gt;=#{dateBegin}</if>
		<if test=' this.has("dateEnd") '>and date_format(create_time,'%Y-%m-%d') &lt;=#{dateEnd}</if>
		order by create_time desc
	</select>
	<select id="getPurchaserStatistics" resultType="com.pj.tb_orders.vo.OrderStaticsDTO">
	select * from (
		SELECT
			count( id ) as totalCount,
			SUM( net_wet ) as totalNet,
			SUM( total_weight ) as totalWeight,
			SUM( resale_price ) as totalPrice,
			purchaser_id as purchaserId,
		DATE_FORMAT(create_time,'%Y-%m-%d') as staticsDate
		FROM
			tb_orders
		WHERE
			purchaser_id =#{purchaserId}
		<if test=' this.has("veNo") '>and ve_no like concat('%',#{veNo},'%')</if>
		<if test=' this.has("productName") '>and goods_names like concat('%',#{productName},'%')</if>
		<if test=' this.has("dateBegin") '>and date_format(create_time,'%Y-%m-%d') &gt;=#{dateBegin}</if>
		<if test=' this.has("dateEnd") '>and date_format(create_time,'%Y-%m-%d') &lt;=#{dateEnd}</if>
		<if test=' this.has("isPay") '> and is_pay = #{isPay} </if>
		<if test=' this.has("goodsName") '> and goods_name like concat('%',#{goodsName},'%') </if>
		<if test=' this.has("leaderName") '> and leader_name like concat('%',#{leaderName},'%')  </if>
		GROUP BY
		purchaser_id,DATE_FORMAT(create_time,'%Y-%m-%d')) as tmp
		ORDER BY staticsDate desc

	</select>

</mapper>
