<?xml version="1.0"?>

<report name="cash-report" csv-renderer="AusgebliebeneKundenlisteAusfCSV.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="newVisitorList">
	</report-result>
		<report-result report-dataset="employeeVisitorList_z1">
	</report-result>
		<report-result report-dataset="employeeVisitorList_z2">
	</report-result>



	<report-result report-dataset="businessunits">
	</report-result>
	
<report-transaction report-nm="returningCustomerRevenue" report-transaction="de.timeglobe.reportsnew.reports.ReturningCustomerRevenue">
	</report-transaction>


	<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 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  
		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_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="visitorList">
		<sql>
SELECT t.EMPLOYEE_NO
		,cg.CUSTOMER_GROUP_NM as customer_group_nm
	,pcm.CONTACT_NO	as CUSTOMER_NO
	,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
	,cr.comment as comment
	,(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 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 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 -sip.position_gross_price ELSE sip.position_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 -sip.position_gross_price ELSE sip.position_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
	
		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 
		join sales_dln_pos_work_records sdpwr
		on si.tenant_no = sdpwr.tenant_no
		and si.pos_cd = sdpwr.pos_cd
		and si.sales_dln_id = sdpwr.sales_dln_id
			and sip.sales_inv_position_id = sdpwr.sales_dln_position_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 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 (t.War_da_im_Zeitraum = 1 or t.Kam_Danach = 1)
	
	
	</sql>
	</report-dataset>
	

		<report-dataset name="newVisitorList">
		<sql>
			 SELECT  	
				wr.operating_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  
			 		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 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 
			 		and coalesce(wr.operating_employee_no, -1) = coalesce(@EMPLOYEE_NO, coalesce(wr.operating_employee_no, -1))
				 GROUP BY
			 		wr.operating_EMPLOYEE_NO  
				 	,si.customer_no 
			 	
			 	ORDER BY 	wr.operating_EMPLOYEE_NO  
				 			,si.customer_no  
	
	</sql>
	</report-dataset>


		<report-dataset name="employeeVisitorList_z1">
		<sql>
			SELECT  
			 	si.customer_no 	customer_no
				,wr.operating_EMPLOYEE_NO  EMPLOYEE_NO  
				,e.EMPLOYEE_NM  EMPLOYEE_NM 
			 	
			
			 FROM SALES_INVS si 
			 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  
			 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 
			 join employees e on e.TENANT_NO = si.TENANT_NO
				and e.COMPANY_NO = si.COMPANY_NO
				and e.EMPLOYEE_NO = wr.operating_EMPLOYEE_NO
			 
			 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.customer_no 	,
				wr.operating_EMPLOYEE_NO  ,e.EMPLOYEE_NM
			 	
			 	
	
	</sql>
	</report-dataset>

	
<report-dataset name="employeeVisitorList_z2">
		<sql>
			SELECT  
			 	si.customer_no 	customer_no
				,wr.operating_EMPLOYEE_NO  EMPLOYEE_NO  
			 	,e.EMPLOYEE_NM  EMPLOYEE_NM 
			
			 FROM SALES_INVS si 
			 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  
			 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 
			 join employees e on e.TENANT_NO = si.TENANT_NO
				and e.COMPANY_NO = si.COMPANY_NO
				and e.EMPLOYEE_NO = wr.operating_EMPLOYEE_NO
			 where 	si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD AND CAST (si.sales_inv_ts as DATE)  &gt;= cast(@DATE_COMPARE as DATE) 
			 		AND CAST (si.sales_inv_ts as DATE)   &lt;= cast(@TODATE_COMPARE as DATE) 
			GROUP BY
			 	si.customer_no 	,
				wr.operating_EMPLOYEE_NO   ,e.EMPLOYEE_NM
			 	
			 	
	
	</sql>
	</report-dataset>


</report>

