<?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.jeeplus.modules.workreimbursementcount.dao.WorkReimbursementMonthDao">
	<select id="findList" resultType="WorkReimbursementMonth" >
		select
		0 "id",
		date_format(i.submitter_date, '%Y-%m') AS "officeName",
		"0" As "type",
		ifnull(sum(i.money),0) AS "money"
		from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		and i.office_id in (select o.id from sys_office o where o.parent_ids LIKE concat('%' #{companyId}, '%'))
		group by date_format(i.submitter_date, '%Y-%m')
		UNION ALL
		select 0 "id",
		"合计" as "officeName" ,
		"0" As "type",
		ifnull(sum(su.money),0) AS "money"
		from (
		select
		0 "id",
		date_format(i.submitter_date, '%Y-%m') AS "officeName",
		"0" As "type",
		sum(i.money) AS "money"
		from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		and i.office_id in (select o.id from sys_office o where o.parent_ids LIKE concat('%' #{companyId}, '%'))
		group by date_format(i.submitter_date, '%Y-%m')
		) su
	</select>

	<select id="findOfficeList" resultType="WorkReimbursementMonth" >
		select
		0 "id",
		date_format(i.submitter_date, '%Y-%m') AS "officeName",
		"0" As "type",
		ifnull(sum(i.money),0) AS "money"
		from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		and i.office_id in (select o.id from sys_office o where o.parent_ids LIKE concat('%' #{companyId}, '%'))
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		<if test="officeId != null and officeId != ''">
			AND i.office_id = #{officeId}
		</if>
		group by date_format(i.submitter_date, '%Y-%m')
		UNION ALL
		select 0 "id",
		"合计" as "officeName" ,
		"0" As "type",
		ifnull(sum(su.money),0) AS "money"
		from (
		select
		0 "id",
		date_format(i.submitter_date, '%Y-%m') AS "officeName",
		"0" As "type",
		ifnull(sum(i.money),0) AS "money"
		from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		and i.office_id in (select o.id from sys_office o where o.parent_ids LIKE concat('%' #{companyId}, '%'))
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		<if test="officeId != null and officeId != ''">
			AND i.office_id = #{officeId}
		</if>
		group by date_format(i.submitter_date, '%Y-%m')
		) su
	</select>




	<select id="findByCompanyList" resultType="WorkReimbursementMonth" >
		SELECT
		a.id AS "id",
		t3.datestr AS "officeName",
		a.type as "type",
		ifnull(sum(DISTINCT t3.money),0) AS "money"
		FROM sys_office a
		LEFT JOIN sys_office t2 ON (t2.parent_ids LIKE concat('%', a.id, '%') AND t2.type = '2')
		LEFT JOIN (select i.id,i.money,submitter_date,i.office_id ,date_format(i.submitter_date, '%Y-%m') as datestr from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		) t3 ON t3.office_id = t2.id
		WHERE
		a.type IN ('1', '3')
		and (a.id != #{companyId} AND a.parent_ids LIKE concat('%' ,#{companyId}, '%'))
		or (t2.branch_office IS NULL AND (a.parent_ids like concat('%',#{companyId},'%') or a.id = #{companyId}))
		GROUP BY
		t3.datestr
		union all
		select 0 "id",
		"合计" as "officeName" ,
		"0" As "type",
		sum(su.money)AS "money"
		from(
		SELECT
		a.id AS "id",
		t3.datestr AS "officeName",
		a.type as "type",
		ifnull(sum(DISTINCT t3.money),0) AS "money"
		FROM sys_office a
		LEFT JOIN sys_office t2 ON (t2.parent_ids LIKE concat('%', a.id, '%') AND t2.type = '2')
		LEFT JOIN (select i.id,i.money,submitter_date,i.office_id ,date_format(i.submitter_date, '%Y-%m') as datestr from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		) t3 ON t3.office_id = t2.id
		WHERE
		a.type IN ('1', '3')
		and (a.id != #{companyId} AND a.parent_ids LIKE concat('%' ,#{companyId}, '%'))
		or (t2.branch_office IS NULL AND (a.parent_ids like concat('%',#{companyId},'%') or a.id = #{companyId}))
		GROUP BY
		t3.datestr
		) su
	</select>

	<!--<select id="findOfficeList" resultType="WorkReimbursementMonth" >
		select
		a.id AS "id",
		t6.datestr AS "officeName",
		ifnull(sum(DISTINCT t6.money),0) AS "money" from sys_office a
		left join (select i.id,i.money,submitter_date,i.office_id ,date_format(i.submitter_date, '%Y-%m') as datestr
		from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		) t6 on t6.office_id = a.id
		<where>
			and a.type ='2'
			<choose>
				<when test="officeId!=null and officeId !=''">
					and a.id = #{officeId}
				</when>
				<otherwise>
					<choose>
						<when test="type !=null and type != ''">
							AND a.branch_office= #{companyId}
						</when>
						<otherwise>
							AND a.parent_ids like concat('%',#{companyId},'%') AND (a.branch_office is NULL OR a.branch_office = '')
						</otherwise>
					</choose>
				</otherwise>
			</choose>
		</where>
		group by t6.datestr
		UNION ALL
		select 0 "id",
		"合计" as "officeName" ,
		"0" As "type",
		sum(su.money)AS "money"
		from(
		select
		a.id AS "id",
		t6.datestr AS "officeName",
		ifnull(sum(DISTINCT t6.money),0) AS "money" from sys_office a
		left join (select i.id,i.money,submitter_date,i.office_id ,date_format(i.submitter_date, '%Y-%m') as datestr
		from work_reimbursement i
		where i.submitter_date
		BETWEEN concat(#{date},'-01-01') and concat(#{date},'-12-31')
		and i.del_flag = '0' and i.status=5
		<if test="ext != '' and ext != null">
			AND i.ext = #{ext}
		</if>
		) t6 on t6.office_id = a.id
		<where>
			and a.type ='2'
			<choose>
				<when test="officeId!=null and officeId !=''">
					and a.id = #{officeId}
				</when>
				<otherwise>
					<choose>
						<when test="type !=null and type != ''">
							AND a.branch_office= #{companyId}
						</when>
						<otherwise>
							AND a.parent_ids like concat('%',#{companyId},'%') AND (a.branch_office is NULL OR a.branch_office = '')
						</otherwise>
					</choose>
				</otherwise>
			</choose>
		</where>
		group by t6.datestr
		) su
	</select>-->

	<select id="getConpanyType" resultType="java.lang.String" >
		select t.type from sys_office t where t.id=#{companyId}
	</select>


</mapper>