<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="HitlisteSalongruppeBv.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-transaction report-nm="posRevenue" report-transaction="de.timeglobe.reportsnew.reports.HitlistSalonGruppeBv">
	</report-transaction>
<!-- 
	<report-result report-dataset="sumBuRevenueSalon">
	</report-result>	-->
	<report-result report-dataset="departments">
	</report-result>
	<!-- <report-result report-dataset="sumPrevYearBuRevenueSalon">
	</report-result>-->
	<!-- <report-result report-dataset="disounts">
	</report-result>
	<report-result report-dataset="disountsPrevYear">
	</report-result> -->
	
	<report-result report-dataset="companies">
	</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="sumBuRevenueSalon">
		<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_total r_total,
         (r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_customer,
         (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_rebate_pos + r_table.r_rabate_inv) r_rebate
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,
            
			
			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) r_total,
			sum(case when h.sales_inv_type=2 then -p.applied_pos_rebate_price else p.applied_pos_rebate_price end ) r_rebate_pos,
			sum(case when h.sales_inv_type=2 then -p.applied_inv_rebate_price else p.applied_inv_rebate_price end ) r_rabate_inv
		
			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 (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 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 
			   ) r_table
			   
			    

		</sql>
	</report-dataset>
	
		<report-dataset name="sumPrevYearBuRevenueSalon">
		<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_total r_total,
         (r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_customer,
         (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_rebate_pos + r_table.r_rabate_inv) r_rebate
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,
            
			
			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) r_total,
			sum(case when h.sales_inv_type=2 then -p.applied_pos_rebate_price else p.applied_pos_rebate_price end ) r_rebate_pos,
			sum(case when h.sales_inv_type=2 then -p.applied_inv_rebate_price else p.applied_inv_rebate_price end ) r_rabate_inv
		
			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 (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 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 
			   ) r_table
			   
			    

		</sql>
	</report-dataset>





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

			select	p.tenant_no r_tenant_no
					,p.pos_cd r_pos_cd
					,h.company_no r_company_no
					,h.department_no r_department_no
					,sum(case when p.payment_direction=1 then -p.payment  else p.payment end ) r_rebate
			from	pos_payments 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 (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 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 @__BUSINESSUNIT_FILTER__h
				AND cast (sales_inv_ts as DATE)  &gt;= cast (@DATE as DATE) AND  cast (sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE)				
				AND p.payment_type = 6
			GROUP BY p.tenant_no 
					,p.pos_cd
					,h.company_no
					,h.department_no
		</sql>
	</report-dataset>




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

			select	p.tenant_no r_tenant_no
					,p.pos_cd r_pos_cd
					,h.company_no r_company_no
					,h.department_no r_department_no
					,sum(case when p.payment_direction=1 then -p.payment  else p.payment end ) r_rebate
			from	pos_payments 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 (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 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 @__BUSINESSUNIT_FILTER__h
				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 p.payment_type = 6
			GROUP BY p.tenant_no 
					,p.pos_cd
					,h.company_no
					,h.department_no
		</sql>
	</report-dataset>

</report>
