<?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.com.countrygarden.bi.main.dao.mediaResource.MediaOrderCycleMapper">
	<resultMap id="BaseOrderRunBillMap" type="cn.com.countrygarden.bi.main.dto.mediaResource.MediaOrderRunBillDto">
		<id property="orderId" column="order_id"/>
		<result property="orderCode" column="order_code"/>
		<result property="orderStartDate" column="order_start_date"/>
		<result property="orderEndDate" column="order_end_date"/>
		<result property="orderAmount" column="order_amount"/>
		<result property="orderStatus" column="order_status"/>
		<result property="customerId" column="customer_id"/>
		<result property="remark" column="remark"/>
		<result property="sort" column="sort"/>
		<result property="version" column="version"/>
		<result property="isDelete" column="is_delete"/>
		<result property="createTime" column="create_time"/>
		<result property="createBy" column="create_by"/>
		<result property="createByName" column="create_by_name"/>
		<result property="modTime" column="mod_time"/>
		<result property="modBy" column="mod_by"/>
		<result property="modByName" column="mod_by_name"/>

		<result property="mediaType" column="media_type"/>
		<result property="mediaCode" column="media_code"/>
		<result property="mediaForm" column="media_form"/>
		<result property="mediaWidth" column="media_width"/>
		<result property="mediaHeight" column="media_height"/>
		<result property="mediaUnit" column="media_unit"/>
		<result property="mediaStatus" column="media_status"/>
		<result property="siteOne" column="site_one"/>
		<result property="siteTwo" column="site_two"/>
		<result property="organCode" column="organ_code"/>
		<result property="organName" column="organ_name"/>
		<result property="commId" column="comm_id"/>
		<result property="commName" column="comm_name"/>
		<result property="commAddress" column="comm_address"/>

		<result property="customerType" column="customer_type"/>
		<result property="customerName" column="customer_name"/>
		<result property="contactPhone" column="contact_phone"/>
		<result property="contactName" column="contact_name"/>

	</resultMap>

	<resultMap id="BaseOrderRunMap" type="cn.com.countrygarden.bi.main.dto.mediaResource.MediaOrderRunDto">
		<id property="orderRunId" column="order_run_id"/>
		<result property="orderId" column="order_id"/>
		<result property="orderRunCode" column="order_run_code"/>
		<result property="remark" column="remark"/>
		<result property="sort" column="sort"/>
		<result property="version" column="version"/>
		<result property="isDelete" column="is_delete"/>
		<result property="createTime" column="create_time"/>
		<result property="createBy" column="create_by"/>
		<result property="createByName" column="create_by_name"/>
		<result property="modTime" column="mod_time"/>
		<result property="modBy" column="mod_by"/>
		<result property="modByName" column="mod_by_name"/>
		<result property="orderCode" column="order_code"/>
	</resultMap>

	<select id="findBillList" resultMap="BaseOrderRunBillMap">
		SELECT
			o.order_id,
			o.order_code,
			o.order_start_date,
			o.order_end_date,
			o.order_amount,
			o.order_status,
			o.customer_id,
			o.remark,
			o.sort,
			o.version,
			o.is_delete,
			o.create_time,
			o.create_by,
			o.create_by_name,
			o.mod_time,
			o.mod_by,
			o.mod_by_name,
			r.media_type,
			r.media_code,
			r.media_form,
			r.media_width,
			r.media_height,
			r.media_unit,
			r.media_status,
			r.site_one,
			r.site_two,
			r.organ_code,
			r.organ_name,
			r.comm_id,
			r.comm_name,
			r.comm_address,
			c.customer_id,
			c.customer_type,
			c.customer_name,
			c.contact_phone,
			c.contact_name
		FROM t_media_order o
		JOIN t_media_order_bill ob ON o.order_id = ob.order_id
		JOIN t_media_resource r ON ob.media_id = r.media_id
		LEFT JOIN t_media_customer c on c.customer_id = o.customer_id
		WHERE
		<include refid="BaseFindSql"/>
		<if test="pageSize != '-1'">
			limit #{pageOffset}, #{pageSize}
		</if>
	</select>

	<select id="findBillCount" resultType="long">
		SELECT
			COUNT(1)
		FROM t_media_order o
		JOIN t_media_order_bill ob ON o.order_id = ob.order_id
		JOIN t_media_resource r ON ob.media_id = r.media_id
		LEFT JOIN t_media_customer c on c.customer_id = o.customer_id
		WHERE
		<include refid="BaseFindSql"/>
	</select>

	<sql id="BaseFindSql">
		1 = 1
		<if test="customerId != null and customerId != ''">
			and o.customer_id = #{customerId}
		</if>
		<if test="orderCode != null">
			and o.order_code like concat('%', #{orderCode}, '%')
		</if>
		<if test="orderStartDate != null">
			and o.order_start_date >= #{orderStartDate}
		</if>
		<if test="orderEndDate != null">
			and o.order_end_date <![CDATA[<=]]> #{orderEndDate}
		</if>
		<if test="orderStatus != null and orderStatus != ''">
			and o.order_status = #{orderStatus}
		</if>
	</sql>


	<select id="findOrderRunNear" resultMap="BaseOrderRunMap">
		SELECT
			o.order_id,
			r.order_run_id,
			r.order_run_code,
			r.sort,
			o.order_code
		FROM t_media_order o
		LEFT JOIN t_media_order_run r on r.order_id = o.order_id and r.is_delete = 0
		WHERE
			o.is_delete = 0 AND o.order_id = #{orderId}
		ORDER BY r.sort DESC
		limit 1
	</select>

	<select id="findUseNum" resultType="int">
		SELECT
			 count(1)
		FROM t_media_cycle c
		WHERE
		c.cycle_time ${startSign} #{startDate}
		AND c.cycle_time ${endSign} #{endDate}
		AND c.media_id = #{mediaId}
		and is_delete = 0
	</select>

	<delete id="deleteCycle">
		DELETE FROM t_media_cycle
		WHERE media_id = #{mediaId}
		and cycle_time <![CDATA[>=]]> #{startDate}
		and cycle_time <![CDATA[<=]]> #{endDate}
	</delete>

	<select id="findMediaResourceCode" resultType="string">
		select media_code from t_media_resource where  media_id = #{mediaId}
	</select>
</mapper>