<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="HitlisteMitarbeiter.xsl"  csv-renderer="HitlisteMitarbeiterCSV.xsl">
	<title>Hitliste  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-transaction report-nm="posRevenue" report-transaction="de.timeglobe.reportsnew.reports.HitlistMitarbeiter">
	</report-transaction>

	<report-result report-dataset="companies">
	</report-result>

	<report-result report-dataset="departments">
	</report-result>
	
	<report-dataset name="businessunits">
		<sql>
			select * from businessunits where tenant_no=@TENANT_NO 
		</sql>
	</report-dataset>

<report-dataset name="companies">
		<sql>
			select * from companies where tenant_no=@TENANT_NO  
		</sql>
	</report-dataset>
<report-dataset name="departments">
		<sql>
			select * from departments where tenant_no=@TENANT_NO  
		</sql>
	</report-dataset>
	
	
		<report-dataset name="employeeRevenue">
		<sql>


		SELECT 
r_table.r_pos_cd r_pos_cd,
r_table.r_company_no r_company_no,
r_table.r_department_no r_department_no,
r_table.r_businessunit_no r_businessunit_no,
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,

r_table.r_cnt_dt r_cnt_dt,


(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
(r_table.r_total_products/r_table.r_cnt_dt) r_total_product_per_day,
((r_table.type1AmountCustomer - r_table.type2AmountCustomer)/r_table.r_cnt_dt) r_amount_customer_per_day,
(case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) = 0 then 0 else case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) &lt; 0 and r_table.r_total &lt; 0 then -r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_per_customer,
r_table.r_planed_week_working_hours r_planed_week_working_hours

FROM (
select
            h.pos_cd r_pos_cd,
            h.company_no r_company_no,
            h.department_no r_department_no,
            h.businessunit_no r_businessunit_no,
            
			wr.operating_employee_no r_employee_no,
			e.employee_nm r_employee_nm,
			
			COUNT (  DISTINCT (cast(sales_inv_ts AS DATE))) r_cnt_dt,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 1 THEN p.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 2 THEN p.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer,
		
	
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end * wr.operating_employee_factor) r_total,
		
			sum(case when coalesce(p.stockable, 0)=1  then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end * wr.operating_employee_factor) r_total_products,
			max (tmpContracts.planed_week_working_hours) r_planed_week_working_hours
			from sales_inv_positions p 
			join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
			join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and p.sales_inv_position_id = wr.sales_dln_position_id 
			left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and wr.operating_employee_no= e.employee_no
			left join (SELECT 	tenant_no, company_no, department_no, businessunit_no, employee_no, max(planed_week_working_hours)  planed_week_working_hours 
						from 	employee_contracts 
						where 	cast (valid_from as DATE) &lt;= cast (CURRENT_TIMESTAMP as DATE) AND  cast (coalesce(valid_to,CURRENT_TIMESTAMP) as DATE) &gt;= cast (CURRENT_TIMESTAMP as DATE)  
						group by tenant_no, company_no, department_no, businessunit_no, employee_no 
					) tmpContracts
				on tmpContracts.tenant_no = h.tenant_no and tmpContracts.company_no = h.company_no and tmpContracts.department_no = h.department_no  and tmpContracts.businessunit_no = h.businessunit_no and wr.operating_employee_no = tmpContracts.employee_no
			join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) AND b.businessunit_no=coalesce(@BUSINESSUNIT_NO ,b.businessunit_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
	      
	      			                                           
		       	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint                    
	      
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
			 	 AND @__BUSINESSUNIT_FILTER__h
			 
			 
			   GROUP BY  h.pos_cd, h.company_no, h.department_no , h.businessunit_no , wr.operating_employee_no, e.employee_nm
			   ) r_table
			   
			     
			   ORDER BY (r_table.r_total/r_table.r_cnt_dt) DESC
		</sql>
	</report-dataset>
	
	
	
	
		<report-dataset name="employeeRevenuePrevYear">
		<sql>

		
		SELECT 
r_table.r_pos_cd r_pos_cd,
r_table.r_company_no r_company_no,
r_table.r_department_no r_department_no,
r_table.r_businessunit_no r_businessunit_no,
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,

r_table.r_cnt_dt r_cnt_dt,


(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
(r_table.r_total_products/r_table.r_cnt_dt) r_total_product_per_day,
((r_table.type1AmountCustomer - r_table.type2AmountCustomer)/r_table.r_cnt_dt) r_amount_customer_per_day,
(case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) = 0 then 0 else case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) &lt; 0 and r_table.r_total &lt; 0 then -r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_per_customer,
0 r_planed_week_working_hours

FROM (
select
            h.pos_cd r_pos_cd,
            h.company_no r_company_no,
            h.department_no r_department_no,
            h.businessunit_no r_businessunit_no,
            
			wr.operating_employee_no r_employee_no,
			e.employee_nm r_employee_nm,
			
			COUNT (  DISTINCT (cast(sales_inv_ts AS DATE))) r_cnt_dt,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 1 THEN p.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 2 THEN p.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer,
		
	
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end * wr.operating_employee_factor) r_total,
			
			sum(case when coalesce(p.stockable, 0)=1  then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end * wr.operating_employee_factor) r_total_products
				
			from sales_inv_positions p 
			join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
			join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and p.sales_inv_position_id = wr.sales_dln_position_id 
			left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and wr.operating_employee_no= e.employee_no
			join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) AND b.businessunit_no=coalesce(@BUSINESSUNIT_NO ,b.businessunit_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
	       
	       				                                           
		       	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint                    
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= DATEADD(year ,-1,cast (@DATE as DATE)) AND  cast (h.sales_inv_ts as DATE) &lt;= DATEADD(year ,-1,cast (@TODATE as DATE))
			 	 AND @__BUSINESSUNIT_FILTER__h
			 
			 
			   GROUP BY  h.pos_cd, h.company_no, h.department_no , h.businessunit_no , wr.operating_employee_no, e.employee_nm
			   ) r_table
			   
			     
			   ORDER BY (r_table.r_total/r_table.r_cnt_dt) DESC
			    

		</sql>
	</report-dataset>
	
</report>
