<?xml version="1.0"?>

<report name="export-empl-stats-report" csv-renderer="ExportStatistikZoellner.xsl"  >
	<title>Umsatz nach ausführenden Mitarbeitern</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-parameter name="BUSINESSUNIT" type="integer">
		<label>Filiale:</label>
		<input type="selectlist">
			<source type="dataset" name="businessunits">
			</source>
		</input>
	</report-parameter>

	<report-result report-dataset="businessunits">
	</report-result>
	<report-result report-dataset="employees">
	</report-result>
<!--	
	<report-result report-dataset="employeeDlCntPerReportGroup">
	</report-result> -->
		<report-result report-dataset="newCustomerList">
	</report-result>
<!--	<report-result report-dataset="employeeDlCntTotal">
	</report-result>
	
	<report-result report-dataset="employeeDlCntPerCategory">
	</report-result>-->
	<report-transaction report-nm="crs_work_statistics" report-transaction="de.timeglobe.pos.reporting.GetCRSWorkStatistics">
	</report-transaction> 

		<report-transaction report-nm="statistics" report-transaction="de.timeglobe.reportsnew.reports.ExportStatistikZoellner">
	</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="employees">
		<sql>
			select e.employee_no, e.employee_nm, Min(ec.valid_from)  valid_from from employees e 
			
			join employee_contracts ec 
			  on e.tenant_no = ec.tenant_no 
			 and e.company_no = ec.company_no 
			 and e.employee_no = ec.employee_no
			
			where e.tenant_no=@TENANT_NO 
			  and e.company_no=@COMPANY_NO 
			  and ec.department_no=@DEPARTMENT_NO 
			  and ec.businessunit_no=@BUSINESSUNIT_NO
			  and  (valid_from is null OR cast (valid_from as DATE) &lt;= cast (@TODATE as DATE))  
			  and (valid_to is null OR cast (valid_to as DATE) &gt;= cast (@DATE as DATE))
			GROUP BY e.employee_no, e.employee_nm  
			  		
			
			
					</sql>
	</report-dataset>




		<report-dataset name="newCustomerList">
		<sql>

 
 		 SELECT
	t.EMPLOYEE_NO  r_employee_no,
	SUM (  case WHEN coalesce(t.customer_group_no, 1) = 1 THEN t.Neukunde  ELSE 0 END ) d_new_customer,
    SUM (  case WHEN coalesce(t.customer_group_no, 1) = 2 THEN t.Neukunde  ELSE 0 END ) h_new_customer,
    SUM (  case WHEN coalesce(t.customer_group_no, 1) = 3 THEN t.Neukunde  ELSE 0 END ) k_new_customer	
				
FROM (
 SELECT  distinct
	sip.EMPLOYEE_NO  EMPLOYEE_NO,
	coalesce(cr.customer_group_no, 1) customer_group_no
	,coalesce(tCus.Neukunde,0) Neukunde
	, si.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 cr.IMPORTED = 1 then 1 
								WHEN DATE (si.sales_inv_ts)  &lt; cast(@DATE as DATE)
									THEN 1
									ELSE 0
									END) AS Bestandskunde
						 FROM SALES_INVS si
						 left join customer_roles cr on cr.TENANT_NO = si.tenant_no and cr.COMPANY_NO = si.COMPANY_NO and cr.CONTACT_NO = si.CUSTOMER_NO 
							and coalesce(group_role,0) = 0
						where 	 (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
		left  join customer_roles cr on cr.TENANT_NO = si.tenant_no and cr.COMPANY_NO = si.COMPANY_NO and cr.CONTACT_NO = si.CUSTOMER_NO 
		 		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 DATE (si.sales_inv_ts) &gt;= cast(@DATE as DATE)
										AND DATE (si.sales_inv_ts)  &lt;= cast(@TODATE as DATE)
			AND (cr.group_role is NULL or cr.group_role = 0) 
	)t
GROUP BY t.EMPLOYEE_NO 
 
	
	</sql>
	</report-dataset>
	
</report>
