<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="PosSalesTip.xsl" csv-renderer="PosSalesTipCSV.xsl">
	<title>Trinkgeld</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="DATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="POSSESSIONID" type="integer"
		hidden="true">
		<preset>@POS_SESSION_ID</preset>
	</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="employeenm">
	</report-result>

<report-result report-dataset="pos_sales_tip">
	</report-result>


	<report-dataset name="employeenm">
		<sql>
			Select distinct case when @EMPLOYEE_NO is null then 'Alle' else employee_nm end as employee_nm from EMPLOYEES e where
			e.tenant_no=@TENANT_NO and e.company_no=@COMPANY_NO and
			e.employee_no=coalesce(@EMPLOYEE_NO, e.employee_no)
		</sql>
	</report-dataset>


	<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="pos_sales_tip">
		<sql>
			
		SELECT	cast (pst.pos_sales_tip_ts as DATE)  day
				,pstp.company_no
				,pstp.tip_for_employee_no employee_no
				,coalesce(e.employee_nm, '-') employee_nm
				, SUM (CASE WHEN pstp.pos_sales_tip_position_type = 2 THEN -pstp.tip_value ELSE pstp.tip_value END) tip_value 

		from pos_Sales_tips pst
		JOIN pos_sales_tip_positions pstp
		ON 	pst.tenant_no = pstp.tenant_no
			AND pst.pos_cd = pstp.pos_cd
			AND pst.pos_sales_tip_id  = pstp.pos_sales_tip_id

		LEFT JOIN employees e
		ON e.tenant_no = pstp.tenant_no
			AND e.company_no = pstp.company_no
			AND e.employee_no = pstp.tip_for_employee_no
		WHERE 	pst.tenant_no =  @TENANT_NO AND pst.pos_cd = @POS_CD 
			AND	cast (pst.pos_sales_tip_ts as DATE) &gt;=  cast(@DATE as DATE) 
			AND cast (pst.pos_sales_tip_ts as DATE) &lt;=  cast	(@TODATE as DATE)  
			AND coalesce(@EMPLOYEE_NO , pstp.tip_for_employee_no) = pstp.tip_for_employee_no
		GROUP BY 	cast (pst.pos_sales_tip_ts as DATE) 
					,pstp.company_no
					,pstp.tip_for_employee_no 
					,coalesce(e.employee_nm, '-')
					
		</sql>
	</report-dataset>


</report>
