<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<object-views xmlns="http://axelor.com/xml/ns/object-views"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://axelor.com/xml/ns/object-views http://axelor.com/xml/ns/object-views/object-views_5.2.xsd">

    <custom name="month.timesheet.status.per.employee" title="Month Timesheet Status Per Employee">
        <dataset type="sql">
			<![CDATA[
				SELECT au.name, 
				CASE
					WHEN ht.status_select=1 THEN 'Draft'
					WHEN ht.status_select=2 THEN 'Waiting Validation'
					WHEN ht.status_select=3 THEN 'Validated'
					WHEN ht.status_select=3 THEN 'Refused'
					END AS status_select,
					ht.from_date, ht.to_date
				FROM hr_employee he 
				INNER JOIN auth_user au ON au.employee = he.id
				LEFT JOIN hr_timesheet ht ON au.id = ht.user_id 
				WHERE (he.archived = false OR he.archived is NULL) AND he.manager_user = :userId AND (current_date >= ht.from_date AND current_date <= ht.to_date)
										AND ht.status_select < 5
				union 
				SELECT au2.name, null, null, null
				FROM hr_employee he2 
				INNER JOIN auth_user au2 ON au2.employee = he2.id
				WHERE (he2.archived = false OR he2.archived is NULL) AND he2.manager_user = :userId AND au2.id not in (
					SELECT au3.id
					FROM hr_employee he3 
					INNER JOIN auth_user au3 ON au3.employee = he3.id 
					LEFT JOIN hr_timesheet ht2 ON au3.id = ht2.user_id 
					WHERE he3.manager_user = :userId AND (current_date >= ht2.from_date AND current_date <= ht2.to_date)
										AND ht2.status_select < 5
				)
				ORDER BY name;
			]]>
		</dataset>
        <template>
			<![CDATA[
				<report-table data='data' columns='name,status_select,from_date,to_date'></report-table>
			]]>
		</template>
    </custom>

</object-views>