<?xml version="1.0"?>

<report name="cash-report" csv-renderer="AusgebliebeneKundenlisteCSV.xsl">
	<title>AusgebliebeneKundenliste</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="visitorList">
	</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="visitorList">
		<sql>
 SELECT
	coalesce(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
	,co.phone as phone
	,cr.birthday as r_dt_birthday
	,(case when t.Bestandskunde = 1 THEN 'nein' else 'ja' END)  neukunde
	, (case when t.War_da_im_Zeitraum = 0 THEN 'nein' else 'ja' END) r_visit_z1
	,(case when t.Kam_Danach = 0 THEN 'nein' else 'ja' END) r_visit_z2 
	,t.r_total_z2
	,t.r_total_z1
FROM (
		SELECT si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
			,max(CASE when coalesce(cr.IMPORTED,0) = 1 then 1 
				WHEN cast(sales_inv_ts as DATE)  &lt; cast(@DATE as DATE)
					THEN 1
					ELSE 0
					END) AS Bestandskunde
			,max(CASE 
					WHEN cast(sales_inv_ts as DATE) &gt;= cast(@DATE as DATE)
						AND cast(sales_inv_ts as DATE)  &lt;= cast(@TODATE as DATE)
						THEN 1
					ELSE 0
					END) AS War_da_im_Zeitraum
			,max(CASE 
					WHEN cast(sales_inv_ts as DATE) &gt;= cast(@DATE_COMPARE as DATE)
						AND cast(sales_inv_ts as DATE)  &lt;= cast(@TODATE_COMPARE as DATE)
						THEN 1
					ELSE 0
					END) AS Kam_Danach,
			sum(CASE 
					WHEN cast(sales_inv_ts as DATE) &gt;= cast(@DATE_COMPARE as DATE)
						AND cast(sales_inv_ts as DATE)  &lt;= cast(@TODATE_COMPARE as DATE)
						THEN (case when coalesce(si.sales_inv_type, 1 )= 2 THEN -si.total_gross_price ELSE si.total_gross_price END)
					ELSE 0
					END) r_total_z2,
			sum(CASE 
					WHEN cast(sales_inv_ts as DATE) &gt;= cast(@DATE as DATE)
						AND cast(sales_inv_ts as DATE)  &lt;= cast(@TODATE as DATE)
						THEN (case when coalesce(si.sales_inv_type, 1 )= 2 THEN -si.total_gross_price ELSE si.total_gross_price END)
					ELSE 0
					END) r_total_z1
		FROM SALES_INVS si
	 	JOIN planet_contact_master pcm  on pcm.TENANT_NO = si.tenant_no and pcm.POS_CD = si.POS_CD and pcm.CONTACT_NO = si.CUSTOMER_NO  
		left JOIN planet_main_customer_roles cr ON pcm.tenant_no = cr.tenant_no AND pcm.master_cd = cr.master_cd and coalesce(group_role,0) = 0
		
		where si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD and (cr.group_role is NULL or cr.group_role = 0) 
		GROUP BY si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
		) t
	JOIN planet_contact_master pcm  on pcm.TENANT_NO = t.tenant_no and pcm.POS_CD = t.POS_CD and pcm.CONTACT_NO = t.CUSTOMER_NO  
		LEFT JOIN planet_main_customer_roles cr ON pcm.tenant_no = cr.tenant_no AND pcm.master_cd = cr.master_cd and coalesce(group_role,0) = 0
		JOIN planet_main_contacts co on pcm.tenant_no = co.tenant_no and pcm.master_cd = co.master_cd
		LEFT JOIN planet_main_customer_groups cg ON cr.TENANT_NO = cg.TENANT_NO AND cr.CUSTOMER_GROUP_NO = cg.CUSTOMER_GROUP_NO
where coalesce(cr.group_role, 0) = 0 and (t.War_da_im_Zeitraum = 1 or t.Kam_Danach = 1)
	

	
	</sql>
	</report-dataset>
	

	

	
</report>

