<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="AusgebliebeneKundenlisteAusf.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="returningVisitorList">
	</report-result>
	<report-result report-dataset="businessunits">
	</report-result>
		<report-result report-dataset="employeeList">
	</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 sdpwr.operating_employee_no as employee_no
			,si.COMPANY_NO
			,si.TENANT_NO
			,si.pos_cd
			,max(CASE 
					WHEN DATE (sales_inv_ts) &gt;= cast(@DATE as DATE)
						AND DATE (sales_inv_ts)  &lt;= cast(@TODATE as DATE)
						THEN 1
					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 War_da_im_Zeitraum
		FROM SALES_INVS si
		left 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
		join sales_dln_pos_work_records sdpwr
		on si.tenant_no = sdpwr.tenant_no
		and si.company_no = sdpwr.company_no
		and si.pos_cd = sdpwr.pos_cd
		and si.sales_dln_id = sdpwr.sales_dln_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(sdpwr.operating_employee_no, -1) = coalesce(@EMPLOYEE_NO, coalesce(sdpwr.operating_employee_no, -1))
		GROUP BY sdpwr.operating_employee_no
			, si.COMPANY_NO
			,si.CUSTOMER_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 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 sdpwr.operating_employee_no as 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 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
		left 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
		join sales_dln_pos_work_records sdpwr
		on si.tenant_no = sdpwr.tenant_no
		and si.company_no = sdpwr.company_no
		and si.pos_cd = sdpwr.pos_cd
		and si.sales_dln_id = sdpwr.sales_dln_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(sdpwr.operating_employee_no, -1) = coalesce(@EMPLOYEE_NO, coalesce(sdpwr.operating_employee_no, -1))
		GROUP BY sdpwr.operating_employee_no
			, si.COMPANY_NO
			,si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
		) t 
		LEFT JOIN 	(	
						
			SELECT t1.EMPLOYEE_NO, 
				t1.customer_no
			FROM
			(
					 SELECT  	
							
						si.tenant_no tenant_no
							,si.pos_cd pos_cd
							,si.sales_inv_id sales_inv_id
							,si.customer_no customer_no
							,wr.operating_EMPLOYEE_NO  EMPLOYEE_NO  
						
							FROM 	
									 SALES_INV_POSITIONS sip 
								join SALES_INVS si 	on sip.tenant_no = si.tenant_no AND sip.pos_cd = si.pos_cd AND sip.sales_inv_id = si.sales_inv_id  
								join sales_dln_pos_work_records wr on si.tenant_no = wr.tenant_no and si.pos_cd = wr.pos_cd and si.sales_dln_id = wr.sales_dln_id and sip.sales_inv_position_id = wr.sales_dln_position_id 
							where si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD AND si.sales_inv_ts  &gt;= cast(DATE(@DATE) as TIMESTAMP)
																AND si.sales_inv_ts    &lt;= {fn TIMESTAMPADD(SQL_TSI_DAY, 1, cast(DATE(@TODATE) as TIMESTAMP))}
						)	t1
						join (
							SELECT cus.tenant_no,cus.pos_cd
									, cus.minSalesInvId 
									, cus.customer_no
										,CASE  
												WHEN  cus.Bestandskunde = 0 
													THEN 1 
												ELSE 0 
												END  AS Neukunde 
											FROM  ( SELECT 
													h.TENANT_NO 
													,h.pos_cd 
													,h.CUSTOMER_NO	 
													,MIN(h.sales_inv_id ) minSalesInvId 
													,MIN(h.sales_inv_ts ) minSalesInvTs 
													,max(CASE when coalesce(cr.IMPORTED,0) = 1 then 1  
														WHEN  h.sales_inv_ts   &lt;  cast(DATE(@DATE) as TIMESTAMP) 
															THEN 1 
															ELSE 0 
															END) AS Bestandskunde 
												FROM SALES_INVS h
												left join customer_roles cr on cr.TENANT_NO = h.tenant_no and cr.COMPANY_NO = h.COMPANY_NO and cr.CONTACT_NO = h.CUSTOMER_NO  
													and coalesce(group_role,0) = 0 
													where h.tenant_no = @TENANT_NO and h.pos_cd=@POS_CD    and (cr.group_role is NULL or cr.group_role = 0) 
												GROUP BY   h.TENANT_NO 
															,h.pos_cd 
															,h.CUSTOMER_NO 
															
												) cus 
													where  cus.minSalesInvTs  &gt;= cast(DATE(@DATE) as TIMESTAMP) and cus.minSalesInvTs   &lt;= {fn TIMESTAMPADD(SQL_TSI_DAY, 1, cast(DATE(@TODATE) as TIMESTAMP))}
									

						) t2 
								
						on t1.tenant_no = t2.tenant_no AND t1.pos_cd = t2.pos_cd AND t1.sales_inv_id = t2.minSalesInvId and t2.customer_no = t1.customer_no					
					WHERE t2.	Neukunde = 1					
					GROUP BY	t1.EMPLOYEE_NO, 
								t1.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>
