<?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.iformall.mapper.WxBillAllMapper">


	<resultMap id="allVoResultMap" type="com.iformall.domain.vo.WxBillAllVo">
		<id column="id" jdbcType="BIGINT" property="id" />
		<result column="tenantId" jdbcType="VARCHAR" property="tenantId" />
		<result column="merchantId" jdbcType="INTEGER" property="merchantId" />
		<result column="shopId" jdbcType="INTEGER" property="shopId" />

		<result column="billTypeValue" jdbcType="INTEGER" property="billTypeValue"/>
		<result column="billType" jdbcType="VARCHAR" property="billType"/>
		<result column="needPay" jdbcType="INTEGER" property="needPay" />

		<result column="merchantName" jdbcType="VARCHAR" property="merchantName" />
		<result column="rentShopType" jdbcType="INTEGER" property="rentShopType"/>
		<result column="receiveDate" jdbcType="TIMESTAMP" property="receiveDate" />
		<result column="expiredDay" jdbcType="INTEGER" property="expiredDay" />
		<result column="status" jdbcType="INTEGER" property="status" />


		<result column="receivePay" jdbcType="INTEGER" property="receivePay" />
		<result column="owe" jdbcType="INTEGER" property="owe" />
		<result column="pay" jdbcType="INTEGER" property="pay" />
		<result column="payDate" jdbcType="TIMESTAMP" property="payDate" />
		<result column="starttime" jdbcType="TIMESTAMP" property="starttime" />
		<result column="endtime" jdbcType="TIMESTAMP" property="endtime" />

		<result column="shopNumber" jdbcType="VARCHAR" property="shopNumber"/>
		<result column="manager" jdbcType="VARCHAR" property="manager"/>
		<result column="managerPhone" jdbcType="VARCHAR" property="managerPhone"/>
		
		<result column="shopInfo" jdbcType="VARCHAR" property="shopInfo"/>
	</resultMap>


	<select id="list" parameterType="com.iformall.domain.vo.WxBillAll" resultMap="allVoResultMap">
		select
		bill.id,bill.tenant_id tenantId,bill.merchant_id merchantId,bill.shop_id shopId,
		bill.bill_type_value billTypeValue,bill.bill_type billType,bill.need_pay needPay,
		bill.receive_pay receivePay,bill.pay,bill.owe,bill.receive_date receiveDate,
		bill.pay_date payDate,bill.expired_day expiredDay,bill.status,bill.starttime,
		bill.endtime,bill.name,bill.rent_shop_type rentShopType,m.name merchantName,
		s.shop_number shopNumber, s.manager, s.manager_phone managerPhone,bill.shop_info shopInfo
		from (
		select id,merchant_id,shop_id,tenant_id,'租金' name,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type,case when shop_info is null then '[]' else shop_info end shop_info
		from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'租赁押金' name,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type,shop_info from wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'物业费' name,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type,case when shop_info is null then '[]' else shop_info end shop_info
		from wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'物业押金' name,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type,shop_info from wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'水费' name,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,
		rent_shop_type,'[]' shop_info
		from wx_bill_daily where type=1
		union
		select id,merchant_id,shop_id,tenant_id,'电费' name,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,
		rent_shop_type,'[]' shop_info
		from wx_bill_daily where type=2
		union
		select id,merchant_id,shop_id,tenant_id,'空调费' name,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,
		rent_shop_type,'[]' shop_info
		from wx_bill_daily where type=3
		union
		select id,merchant_id,shop_id,tenant_id,name,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,
		rent_shop_type,'[]' shop_info
		from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,name,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,
		rent_shop_type,'[]' shop_info
		from wx_bill_other_deposit
		) bill
		left join wx_merchant m on bill.merchant_id=m.id
		left join wx_shop s on bill.shop_id=s.id
		where 1=1
		<if test=" null != tenantId ">
			and bill.tenant_id=#{tenantId}
		</if>
		<if test=" null != month and ''!=month">
			and date_format(bill.receive_date,'%Y-%m')=#{month}
		</if>
		<if test=" null != merchantName and ''!=merchantName ">and m.`name` like concat('%',#{merchantName},'%')</if>
		<if test=" null != billTypeValue ">and bill.bill_type_value = #{billTypeValue}</if>
		<if test=" null != status ">and bill.`status` = #{status}</if>
		<if test=" null != rentShopType ">and bill.rent_shop_type = #{rentShopType}</if>
		
		order by bill.id desc,bill.merchant_id,bill.status,bill.receive_date desc
	
	</select>
	
	
	<select id="queryOweInfo" parameterType="com.iformall.domain.vo.WxBillAll" resultType="hashmap">
		

		select count(bill.id) owncount,IFNULL(sum(bill.owe),0) owe from (
		select id,merchant_id,shop_id,tenant_id,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from
		wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from
		wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from
		wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_daily where
		type=1
		union
		select id,merchant_id,shop_id,tenant_id,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_daily where
		type=2
		union
		select id,merchant_id,shop_id,tenant_id,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_daily where
		type=3
		union
		select id,merchant_id,shop_id,tenant_id,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_other_deposit
		) bill
		where  bill.tenant_id=#{tenantId} and bill.status=#{status}
		<if test=" null != starttime and null!= endtime ">
			and bill.receive_date between #{starttime} and #{endtime}
		</if>
		<if test=" null != month ">
			and date_format(bill.receive_date,'%Y-%m')=#{month}
		</if>
		<if test=" null != rentShopType ">
			and bill.rent_shop_type = #{rentShopType}
		</if>
	</select>
	
	<select id="queryPaidInfo" parameterType="com.iformall.domain.vo.WxBillAll" resultType="hashmap">
  
		select count(bill.id) paycount,IFNULL(sum(bill.pay),0) pay from (
		select id,merchant_id,shop_id,tenant_id,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from
		wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from
		wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from
		wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_daily where
		type=1
		union
		select id,merchant_id,shop_id,tenant_id,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_daily where
		type=2
		union
		select id,merchant_id,shop_id,tenant_id,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_daily where
		type=3
		union
		select id,merchant_id,shop_id,tenant_id,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,rent_shop_type from wx_bill_other_deposit
		) bill
		where bill.tenant_id=#{tenantId} and bill.status=#{status}
		<if test=" null != starttime and null!= endtime ">
			and bill.receive_date between #{starttime} and #{endtime}
		</if>
		<if test=" null != month ">
			and date_format(bill.receive_date,'%Y-%m')=#{month}
		</if>
		<if test=" null != rentShopType ">
			and bill.rent_shop_type = #{rentShopType}
		</if>
	</select>
	
	
	<select id="listData" resultType="hashmap" parameterType="com.iformall.domain.vo.WxBillAll">
		select bill.id,bill.merchant_id merchantId,bill.shop_id shopId,bill.bill_type_value billTypeValue,bill.bill_type billType,bill.need_pay needPay,
		bill.receive_pay receivePay,bill.pay,bill.owe,bill.receive_date receiveDate,bill.pay_date payDate,bill.expired_day expiredDay,bill.status,
		bill.tenant_id tenantId,m.name merchantName,s.shop_number shopNumber,bill.starttime,bill.endtime,bill.name,pb.pay_bill_status payBillStatus,
		pb.pay_time_end tradeTime,pb.transaction_id transactionId,pb.pay_amount payAmount,bill.rent_shop_type
		rentShopType from (
		select id,merchant_id,shop_id,tenant_id,'租金' name,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,starttime,endtime,rent_shop_type
		from wx_bill_rent where is_preview = 0 and rent_contract_id in (select id from wx_rent_contract where status in
		(2,3,4))
		union
		select id,merchant_id,shop_id,tenant_id,'租赁押金' name,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,starttime,endtime,rent_shop_type
		from wx_bill_rent_deposit where rent_contract_id in (select id from wx_rent_contract where status in (2,3,4))
		union
		select id,merchant_id,shop_id,tenant_id,'物业费' name,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,starttime,endtime,rent_shop_type
		from wx_bill_property where is_preview = 0 and property_contract_id in (select id from wx_property_contract
		where status in(2,3,4))
		union
		select id,merchant_id,shop_id,tenant_id,'物业押金' name,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,starttime,endtime,rent_shop_type
		from wx_bill_property_deposit where property_contract_id in (select id from wx_property_contract where status in
		(2,3,4))
		union
		select id,merchant_id,shop_id,tenant_id,'水费' name,5 bill_type_value,'水费' bill_type, 0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=1
		union
		select id,merchant_id,shop_id,tenant_id,'电费' name,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=2
		union
		select id,merchant_id,shop_id,tenant_id,'空调费' name,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=3
		union
		select id,merchant_id,shop_id,tenant_id,name,7 bill_type_vaue,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,comments as name,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other_deposit
		) bill
		left join wx_merchant m on bill.merchant_id=m.id
		left join wx_shop s on bill.shop_id=s.id
		left join wx_pay_bill pb on bill.id=pb.bill_id
		where 1=1
		<if test=" null != id ">
			and bill.id=#{id}
		</if>
		<if test=" null != tenantId ">
			and bill.tenant_id=#{tenantId}
		</if>
		<if test=" null != merchantId ">
			and bill.merchant_id=#{merchantId}
		</if>
		<if test=" null != statusList ">
			and bill.`status` in
			<foreach collection="statusList" index="index" item="status" open="(" separator="," close=")">
				#{status}
			</foreach>
		</if>
		
		<if test=" null != typeList ">
			and bill.`bill_type_value` in
			<foreach collection="typeList" index="index" item="type" open="(" separator="," close=")">
				#{type}
		</foreach>
		</if>
		<if test="null!=starttime and null!=endtime">
			and DATE_FORMAT(bill.receive_date,'%Y-%m') between #{starttime} and #{endtime}
		</if>
		<if test=" null != status ">
			and bill.status=#{status}
		</if>
		<if test=" null != billTypeValue ">
			and bill.bill_type_value=#{billTypeValue}
		</if>
		<if test=" null != rentShopType ">
			and bill.rent_shop_type = #{rentShopType}
		</if>
		order by bill.merchant_id,bill.status,bill.receive_date desc
	</select>
	
	
	<select id="getWaitPayBill" resultType="hashmap">
		select bill.tenant_id tenantId,bill.merchant_id merchantId,bill.receive_date receiveDate,
			bill.need_pay needPay,m.link_phone linkPhone,app.appname,m.email,bill.bill_type billType from (
		select bill.bill_type,bill.tenant_id,bill.merchant_id,bill.receive_date,ifnull(sum(need_pay),0) need_pay from (
		select id,merchant_id,shop_id,tenant_id,'租金' name,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'租赁押金' name,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'物业费' name,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'物业押金' name,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'水费' name,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=1
		union
		select id,merchant_id,shop_id,tenant_id,'电费' name,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=2
	  	union
		select id,merchant_id,shop_id,tenant_id,'空调费' name,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=3
		union
		select id,merchant_id,shop_id,tenant_id,name,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,name,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other_deposit
		) bill
		where bill.status not in(3,5,6)
		group by bill.tenant_id,bill.merchant_id,bill.receive_date) bill
		left join wx_merchant m on bill.merchant_id=m.id
		left join (select tenant_id,`name` appname from wx_appinfo app where type=1) app on bill.tenant_id=app.tenant_id
		where m.status=1 and DATEDIFF(bill.receive_date,now())=m.bill_setting
	</select>
	
	
	<select id="getOweBill" resultType="hashmap">
		select bill.tenant_id tenantId,bill.merchant_id merchantId,bill.owe,m.link_phone linkPhone,app.appname from (
		select bill.tenant_id,bill.merchant_id,sum(owe) owe from (
		select id,merchant_id,shop_id,tenant_id,'租金' name,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'租赁押金' name,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'物业费' name,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'物业押金' name,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'水费' name,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=1
		union
		select id,merchant_id,shop_id,tenant_id,'电费' name,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=2
		union
		select id,merchant_id,shop_id,tenant_id,'空调费' name,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=3
		union
		select id,merchant_id,shop_id,tenant_id,name,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,name,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other_deposit
		) bill
		where DATEDIFF(bill.receive_date,now())&lt;0 and bill.status not in(3,5,6)
		group by bill.tenant_id,bill.merchant_id) bill
		left join wx_merchant m on bill.merchant_id=m.id
		left join (select tenant_id,`name` appname from wx_appinfo app where type=1) app on bill.tenant_id=app.tenant_id
		where m.status=1
	</select>

	<select id="getWaitPayBillForEmail" resultType="hashmap">
		select bill.tenant_id tenantId,bill.merchant_id merchantId,bill.receive_date receiveDate,
		bill.need_pay  needPay,m.link_phone linkPhone,app.appname,m.email,bill.bill_type billType,ws.manager,ws.manager_phone managerPhone  from (
		select bill.bill_type,bill.tenant_id,bill.merchant_id,bill.receive_date,ifnull(sum(need_pay),0) need_pay from (
		select id,merchant_id,shop_id,tenant_id,'租金' name,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'租赁押金' name,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'物业费' name,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'物业押金' name,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'水费' name,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=1
		union
		select id,merchant_id,shop_id,tenant_id,'电费' name,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=2
		union
		select id,merchant_id,shop_id,tenant_id,'空调费' name,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=3
		union
		select id,merchant_id,shop_id,tenant_id,name,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,name,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other_deposit
		) bill
		where bill.status not in(3,5,6)
		group by bill.tenant_id,bill.merchant_id,bill.receive_date,bill.bill_type
		) bill
		left join wx_merchant m on bill.merchant_id=m.id
		left join (select tenant_id,`name` appname from wx_appinfo app where type=1) app on bill.tenant_id=app.tenant_id
		left join wx_merchant_shop wms on wms.merchant_id = m.id
		left join wx_shop ws on wms.shop_id = ws.id
		where m.status=1 and DATEDIFF(bill.receive_date,now())=m.bill_setting
	</select>

	<select id="getOweBillForEmail" resultType="hashmap">
		select bill.tenant_id tenantId,bill.merchant_id merchantId,bill.bill_type billType,bill.owe owe,m.link_phone managerPhone,m.`link_person` manager,m.email,app.appname,bill.receive_date receiveDate,bill.expired_day expiredDay  from (
		select bill.tenant_id,bill.merchant_id,bill.bill_type,sum(owe) owe,bill.receive_date,bill.expired_day from (

		select id,merchant_id,shop_id,tenant_id,'租金' name,1 bill_type_value,'租金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'租赁押金' name,2 bill_type_value,'租赁押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_rent_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'物业费' name,3 bill_type_value,'物业费'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property where is_preview = 0
		union
		select id,merchant_id,shop_id,tenant_id,'物业押金' name,4 bill_type_value,'物业押金'
		bill_type,need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,''
		endtime,rent_shop_type from wx_bill_property_deposit
		union
		select id,merchant_id,shop_id,tenant_id,'水费' name,5 bill_type_value,'水费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=1
		union
		select id,merchant_id,shop_id,tenant_id,'电费' name,6 bill_type_value,'电费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=2
		union
		select id,merchant_id,shop_id,tenant_id,'空调费' name,9 bill_type_value,'空调费' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_daily where type=3
		union
		select id,merchant_id,shop_id,tenant_id,name,7 bill_type_value,'其他' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other
		union
		select id,merchant_id,shop_id,tenant_id,name,8 bill_type_value,'其他押金' bill_type,0 as
		need_pay,receive_pay,pay,owe,DATE_FORMAT(receive_date,'%Y-%m-%d') receive_date,pay_date,expired_day,status,'' starttime,'' endtime,rent_shop_type
		from wx_bill_other_deposit

		) bill
		where DATEDIFF(bill.receive_date,now())&lt;0 and bill.status not in(3,5,6)
		group by bill.tenant_id,bill.merchant_id,bill.bill_type) bill
		left join wx_merchant m on bill.merchant_id=m.id
		left join (select tenant_id,`name` appname from wx_appinfo app where type=1) app on bill.tenant_id=app.tenant_id
		where m.status=1
	</select>
</mapper>
