<?xml version="1.0"?>

<report name="cash-report" csv-renderer="KundenListeCSV.xsl">
	<title>Kundenliste</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-result report-dataset="customerList">
	</report-result>
	<report-result report-dataset="businessunits">
	</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="customerList">
		<sql>
	Select
	cg.CUSTOMER_GROUP_NM as customer_group_nm
	,co.CONTACT_NM	as contact_nm
	,co.FIRST_NM as first_nm
	,co.email	as email
	,co.STREET	as street
	,co.POSTAL_CD as postal_cd
	,co.CITY	as city
	,cr.birthday as r_dt_birthday
	,max(gross_in_selection) as r_total_selection
	,min(totalGross) AS r_total_gross
	,min(si_total.first_visit) AS r_dt_first_visit
	,max(si_total.last_visit) AS r_dt_last_visit
FROM (
	SELECT si.tenant_no
		,si.pos_cd
		,si.CUSTOMER_NO
		,sum(CASE WHEN si.sales_inv_type = 2 THEN - si.total_gross_price ELSE si.total_gross_price END) gross_in_selection
	FROM sales_invs si
	WHERE si.tenant_no=@TENANT_NO and si.pos_cd=@POS_CD AND cast (si.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (si.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE)
	GROUP BY si.tenant_no
		,si.pos_cd
		,si.CUSTOMER_NO
	) si_selection
JOIN contacts co ON si_selection.TENANT_NO = co.TENANT_NO
	AND si_selection.CUSTOMER_NO = co.CONTACT_NO
JOIN (
	SELECT tenant_no
		,pos_cd
		,CUSTOMER_NO
		,min(sales_inv_ts) AS first_visit
		,max(sales_inv_ts) AS last_visit
		,sum(CASE WHEN sales_inv_type = 2 THEN - total_gross_price ELSE total_gross_price END) AS totalGross
	FROM sales_invs si2
	 where si2.tenant_no=@TENANT_NO and si2.pos_cd=@POS_CD
	GROUP BY tenant_no
		,pos_cd
		,CUSTOMER_NO
	) si_total ON si_selection.TENANT_NO = si_total.tenant_no
	AND si_selection.pos_cd = si_total.pos_cd
	AND si_selection.CUSTOMER_NO = si_total.CUSTOMER_NO
JOIN customer_roles cr ON co.tenant_no = cr.tenant_no
	AND co.contact_no = cr.CONTACT_NO 
JOIN customer_groups cg ON cr.TENANT_NO = cg.TENANT_NO
	AND cr.COMPANY_NO = cg.COMPANY_NO
	AND cr.CUSTOMER_GROUP_NO = cg.CUSTOMER_GROUP_NO 
where coalesce(cr.group_role, 0) = 0
	and coalesce(cr.CUSTOMER_GROUP_NO, -1) = coalesce(@CUSTOMER_GROUP_NO, coalesce(cr.CUSTOMER_GROUP_NO, -1))
GROUP BY si_selection.CUSTOMER_NO
	,cg.CUSTOMER_GROUP_NM
	,co.CONTACT_NM
	,co.FIRST_NM
	,co.email
	,co.STREET
	,co.POSTAL_CD
	,co.CITY
	,cr.birthday
order by co.CONTACT_NM

	</sql>
	</report-dataset>



	
</report>
