<?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.WorkInvoiceCompanyDao">
	<select id="findCompanyList" resultType="WorkInvoiceCompany" >
	select
		  a.id AS "id",
		  a.top_company AS "officeName",
		  a.type As "type",
		 ifnull(count(DISTINCT t3.id),0) AS "invoiceCount",
		 ifnull( sum(t3.tax_money),0) AS "invoiceMoneys",
		 ifnull( sum(t3.income_money),0) AS "incomeMoneys"
		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.* from work_invoice_detail i where i.del_flag = '0' and i.state in ('正常','作废')
		<if test="date != '' and date != null">
			AND YEAR(i.date) = #{date}
		</if>
		) t3 on t3.office_id = t2.id
		where a.type in('1','3')
		and ( a.parent_id = #{companyId} or a.id = #{companyId})
		group by a.id
		order by a.create_date ASC;
	</select>

	<select id="get" resultType="WorkInvoiceCompany" >
		select * from ( select
		a.id AS "id",
		a.name AS "officeName",
		a.type As "type",
		ifnull(count(DISTINCT t3.id),0) AS "invoiceCount",
		ifnull( sum(DISTINCT t3.tax_money),0) AS "invoiceMoneys",
		ifnull( sum(DISTINCT t3.income_money),0) AS "incomeMoneys"
		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.* from work_invoice_detail i where i.del_flag = '0' and i.state in ('正常','作废')
		<if test="date != '' and date != null">
			AND YEAR(i.date) = #{date}
		</if>
		) t3 on t3.office_id = t2.id
		where a.type in('1','3') and ( a.parent_ids like concat('%',  #{companyId},'%') or a.id = #{companyId})
		group by a.id
		order by a.create_date ASC;) info
		WHERE info.id = #{id}
	</select>
</mapper>