<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="AusgebliebeneKundenlisteBeratend.xsl">
	<title>AusgebliebeneKundenlisteEmployee</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="employeeList">
	</report-result>
	<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="employeeList">
		<sql>
SELECT e.EMPLOYEE_NO
,e.employee_nm
	FROM  (
		SELECT sip.EMPLOYEE_NO 
			,si.COMPANY_NO
			,si.TENANT_NO
			,si.pos_cd
			,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
					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 War_da_im_Zeitraum
		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
	
		join SALES_INV_POSITIONS sip on sip.tenant_no = si.tenant_no AND sip.pos_cd = si.pos_cd AND sip.sales_inv_id = si.sales_inv_id 
		where si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD  and (cr.group_role is NULL or cr.group_role = 0)  and sip.employee_no is not null
			and coalesce(sip.employee_no, -1) = coalesce(@EMPLOYEE_NO, coalesce(sip.employee_no, -1))
		GROUP BY sip.EMPLOYEE_NO 
			, si.COMPANY_NO
			,si.TENANT_NO
			,si.pos_cd
		) t 
		join employees e on e.TENANT_NO = t.TENANT_NO
		and e.COMPANY_NO = t.COMPANY_NO
		and e.EMPLOYEE_NO = t.EMPLOYEE_NO
where t.War_da_im_Zeitraum = 1
group by e.employee_no,
e.employee_nm
	order by e.employee_nm
	
	</sql>
	</report-dataset>

	<report-dataset name="returningVisitorList">
		<sql>
 SELECT Employee_nm
	,EMPLOYEE_NO
	,sum(Anzahl_Bestand) AS Bestandskunden
	,sum(Neukunde) AS Neukunden
	,sum(Ausgeblieben_Bestand) AS Ausgeblieben_Bestand
	,sum(Ausgeblieben_Neu) AS Ausgeblieben_Neu
FROM (
	SELECT em.EMPLOYEE_NO
		,max(em.EMPLOYEE_NM) AS EMPLOYEE_NM
		,t.customer_no
		,max(CASE WHEN t.War_da_im_Zeitraum = 1 and newCus.customer_no is  NULL
					THEN 1
				ELSE 0
				END) AS Anzahl_Bestand
		,max(CASE 
				WHEN t.War_da_im_Zeitraum = 1
					AND newCus.customer_no is NOT NULL
					THEN 1
				ELSE 0
				END) AS Neukunde
		,max(CASE 
				WHEN newCus.customer_no is  NULL
					AND t.Kam_Danach = 0 and t.War_da_im_Zeitraum = 1
					THEN 1
				ELSE 0
				END) AS Ausgeblieben_Bestand
		,max(CASE 
				WHEN t.War_da_im_Zeitraum = 1
					AND newCus.customer_no is NOT NULL
					AND t.Kam_Danach = 0 and t.War_da_im_Zeitraum = 1
					THEN 1
				ELSE 0
				END) AS Ausgeblieben_Neu
	FROM  (
		SELECT sip.EMPLOYEE_NO 
			,si.COMPANY_NO
			,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
		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
		join SALES_INV_POSITIONS sip on sip.tenant_no = si.tenant_no AND sip.pos_cd = si.pos_cd AND sip.sales_inv_id = si.sales_inv_id 
		where si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD and (cr.group_role is NULL or cr.group_role = 0) 
		and coalesce(sip.employee_no, -1) = coalesce(@EMPLOYEE_NO, coalesce(sip.employee_no, -1))
		
		GROUP BY sip.EMPLOYEE_NO 
			, si.COMPANY_NO
			,si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
		) t 

		LEFT JOIN 	(		 SELECT  	
							sip.EMPLOYEE_NO  EMPLOYEE_NO  
							,si.customer_no customer_no
						
							FROM SALES_INVS si 
							join ( 
							SELECT cus.tenant_no,cus.pos_cd, cus.customer_no
									, cus.minSalesInvId 
										,CASE  
												WHEN  cus.Bestandskunde = 0 
													THEN 1 
												ELSE 0 
												END  AS Neukunde 
											FROM  ( SELECT 
													si.TENANT_NO 
													,si.pos_cd 
													,si.CUSTOMER_NO	 
													,MIN(si.sales_inv_id ) minSalesInvId 
													,MIN(si.sales_inv_ts ) minSalesInvTs 
													,max(CASE when coalesce(cr.IMPORTED,0) = 1 then 1  
														WHEN CAST (si.sales_inv_ts as DATE)   &lt; cast(@DATE as DATE) 
															THEN 1 
															ELSE 0 
															END) AS Bestandskunde 
												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.TENANT_NO 
															,si.pos_cd 
															,si.CUSTOMER_NO 
															
												) cus 
												where cast( cus.minSalesInvTs as DATE) &gt;= cast(@DATE as DATE) and cast( cus.minSalesInvTs as DATE)  &lt;= cast(@TODATE as DATE) 
										) tCus 
								on si.tenant_no = tCus.tenant_no AND si.pos_cd = tCus.pos_cd   and si.sales_inv_id = tCus.minSalesInvId and si.customer_no = tCus.customer_no 
								join SALES_INV_POSITIONS sip on sip.tenant_no = si.tenant_no AND sip.pos_cd = si.pos_cd AND sip.sales_inv_id = si.sales_inv_id  
								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) 
										AND coalesce(tCus.Neukunde,0) = 1 
							GROUP BY
								sip.EMPLOYEE_NO  
								,si.customer_no 
						) newCus
	ON newCus.EMPLOYEE_NO = t.employee_no AND newCus.customer_no = t.CUSTOMER_NO	
	join employees em on em.tenant_no = t.TENANT_NO and em.company_no = t.COMPANY_NO and em.employee_no = t.employee_no
	GROUP BY em.EMPLOYEE_NO
		,t.customer_no
	) t2 
GROUP BY Employee_nm
	,EMPLOYEE_NO 
	
	</sql>
	</report-dataset>

</report>
