<?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.WorkReimbursementInfoDao">
    
	<sql id="workReimbursementInfoColumns">
		a.id AS "id",
		a.number AS "number",
		a.office_id AS "officeId",
		a.submitter_id AS "submitterId",
		su.name AS "submitterName",
		a.project_id as "projectId",
		a.submitter_date AS "submitterDate",
		a.company_id AS "companyId",
		p.project_name AS "projectName",
		ifnull(a.money,0) AS "money",
		a.status AS "status",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.del_flag AS "delFlag",
		o.name AS "officeName",
		u.name AS "su.name",
		a.ext AS "ext"
	</sql>
	
	<sql id="workReimbursementInfoJoins">
        LEFT JOIN project_records p on p.id = a.project_id
		LEFT JOIN sys_office o ON o.id = a.office_id
		LEFT JOIN sys_office s ON s.id = a.company_id
		LEFT JOIN sys_user u ON u.id = a.create_by
		LEFT JOIN sys_user su ON su.id = a.submitter_id
	</sql>
	
	<!--<select id="findInfoList" resultType="WorkReimbursementInfo" >
		SELECT 
			<include refid="workReimbursementInfoColumns"/>
		FROM work_reimbursement a
		<include refid="workReimbursementInfoJoins"/>
		<where>
			<if test="id != null and id != ''">
				AND a.id = #{id}
			</if>
			<if test="projectId != null and projectId != ''">
				AND a.project_id = #{projectId}
			</if>
			<if test="status != '' and status != null">
				AND a.status = #{status}
			</if>
			<if test="officeId != '' and officeId != null">
				AND a.office_id = #{officeId}
			</if>
			<if test="submitterDate != '' and submitterDate != null">
				AND a.submitter_date = #{submitterDate}
			</if>
			<if test="submitterId != null and submitterId != ''">
				AND a.submitter_id = #{submitterId}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>-->
	<select id="findInfoList" resultType="WorkReimbursementInfo" >
		SELECT
		<include refid="workReimbursementInfoColumns"/>
		FROM work_reimbursement a
		<include refid="workReimbursementInfoJoins"/>
		where
		a.office_id=#{officeId} and a.del_flag=0 and a.status=5
		<if test="date != '' and date != null">
			AND YEAR(a.submitter_date) = #{date}
		</if>
		<if test="ext != '' and ext != null">
			AND a.ext = #{ext}
		</if>
	</select>

</mapper>