<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Anwesenheitsbericht.xsl">
	<title>Anwesenheitsbericht</title>

	<report-parameter name="TENANT" type="integer" hidden="true">
		<preset>@TENANT_NO</preset>
	</report-parameter>
	<report-parameter name="COMPANY" type="integer" hidden="true">
		<preset>@COMPANY_NO</preset>
	</report-parameter>
	<report-parameter name="TODATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
		<report-parameter name="FROMDATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="BUSINESSUNIT" type="integer">
		<label>Filiale:</label>
		<input type="selectlist">
			<source type="dataset" name="businessunits">
			</source>
		</input>
	</report-parameter>

	<report-result report-dataset="businessunits">
	</report-result>
	<report-result report-dataset="employeelog">
	</report-result>
	<report-result report-dataset="employees">
	</report-result>


	<report-dataset name="businessunits">
		<sql>
			select * from businessunits where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and businessunit_no=@BUSINESSUNIT_NO
		</sql>
	</report-dataset>

	<report-dataset name="employeelog">
		<sql>
			select employee_no,
			from_ts,
			to_ts,
			comment_txt,
			{fn TIMESTAMPDIFF (SQL_TSI_MINUTE, from_ts, to_ts)} as log_hour
			
			from employee_contract_att_times 
			 where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and businessunit_no=@BUSINESSUNIT_NO and pos_cd=@POS_CD 
			 and cast (from_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (from_ts as DATE) &lt;= cast (@TODATE as DATE)
			 and cast (to_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (to_ts as DATE) &lt;= cast (@TODATE as DATE)
			 order by from_ts
		</sql>
	</report-dataset>
	
	<report-dataset name="employees">
		<sql>
			select DISTINCT e.EMPLOYEE_NO,e.employee_nm from employees e
			join employee_contracts ec ON e.TENANT_NO  = ec.TENANT_NO AND e.COMPANY_NO = ec.COMPANY_NO and e.employee_no = ec.employee_no 
			where e.tenant_no=@TENANT_NO and e.company_no=@COMPANY_NO
			and cast (VALID_FROM as DATE) &lt;= cast (@TODATE as DATE)
			and (cast (valid_to as DATE) &gt;= cast (@DATE as DATE) OR valid_to IS null)
			AND COALESCE (e.UNSELECTABLE,0) = 0 
			
		</sql>
	</report-dataset>
	
</report>
