<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="WiederkehrerListe.xsl">
	<title>Wiederkehrerliste</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="returningVisitorList">
	</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="returningVisitorList">
		<sql>
 SELECT
	sum(Anzahl_Bestand) AS Bestandskunden
	,sum(Neukunde) AS Neukunden
	,sum(Wiederkehrer_Bestand) AS Wiederkehrer_Bestand
	,sum(Wiederkehrer_Neu) AS Wiederkehrer_neu
FROM (
	SELECT 
		t.customer_no
		,max(CASE WHEN t.War_da_im_Zeitraum = 1 and t.Bestandskunde = 1
					THEN 1
				ELSE 0
				END) AS Anzahl_Bestand
		,max(CASE 
				WHEN t.War_da_im_Zeitraum = 1
					AND t.Bestandskunde = 0
					THEN 1
				ELSE 0
				END) AS Neukunde
		,max(CASE 
				WHEN t.Bestandskunde = 1
					AND t.Kam_Danach = 1 and t.War_da_im_Zeitraum = 1
					THEN 1
				ELSE 0
				END) AS Wiederkehrer_Bestand
		,max(CASE 
				WHEN t.War_da_im_Zeitraum = 1
					AND t.Bestandskunde = 0
					AND t.Kam_Danach = 1 and t.War_da_im_Zeitraum = 1
					THEN 1
				ELSE 0
				END) AS Wiederkehrer_Neu
FROM (
		SELECT si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
			,max(CASE when coalesce(cr.IMPORTED,0) = 1 then 1 
				WHEN DATE(sales_inv_ts)  &lt; cast(@DATE as DATE)
					THEN 1
					ELSE 0
					END) AS Bestandskunde
			,max(CASE 
					WHEN DATE (sales_inv_ts) &gt;= cast(@DATE as DATE)
						AND DATE (sales_inv_ts)  &lt;= cast(@TODATE as DATE)
						THEN 1
					ELSE 0
					END) AS War_da_im_Zeitraum
			,max(CASE 
					WHEN DATE (sales_inv_ts) &gt;= cast(@DATE_COMPARE as DATE)
						AND DATE (sales_inv_ts)  &lt;= cast(@TODATE_COMPARE as DATE)
						THEN 1
					ELSE 0
					END) AS Kam_Danach
		FROM SALES_INVS si
		join customer_roles cr on cr.TENANT_NO = si.tenant_no and cr.CONTACT_NO = si.CUSTOMER_NO and cr.COMPANY_NO = si.COMPANY_NO and cr.CUSTOMER_NO = si.CUSTOMER_NO
		and coalesce(group_role,0) = 0
		where si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD
		GROUP BY si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
		) t
	group by t.customer_no
	) t2 

	
	</sql>
	</report-dataset>

</report>
