<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="UmsatzBeratendeMitarbeiter.xsl">
	<title>Umsatz nach beratenden 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="employeeRevenue">
	</report-result>
	<report-result report-dataset="anzahlDlNachBio">
	</report-result>
<report-result report-dataset="summeAnzahlDlNachBio">
	</report-result>
	<report-result report-dataset="businessunits">
	</report-result>
	<report-result report-dataset="sumRevenue">
	</report-result>
<report-result report-dataset="departmentCurrency">
	</report-result><!--
	<report-result report-dataset="rebates">
	</report-result>-->
		<report-result report-dataset="employeenm">
	</report-result>
	<report-result report-dataset="employee_profession">
	</report-result>
	
	<report-result report-dataset="useBUOptionBio">
	</report-result>
	
	
		<report-dataset name="employeenm">
		<sql>
			Select distinct case when @EMPLOYEE_NO is null then 'Alle' else employee_nm end as employee_nm from EMPLOYEES e where
			e.tenant_no=@TENANT_NO and e.company_no=@COMPANY_NO and
			e.employee_no=coalesce(@EMPLOYEE_NO, e.employee_no)
		</sql>
	</report-dataset>
	<report-dataset name="employee_profession">
		<sql>
			Select distinct case when @PROFESSION_NO is null then 'Alle' else profession_nm end as profession_nm from professions p where
			p.tenant_no=@TENANT_NO and p.company_no=@COMPANY_NO and p.department_no=@DEPARTMENT_NO and
			p.profession_no=coalesce(@PROFESSION_NO, p.profession_no)
		</sql>
	</report-dataset>
    <report-dataset name="departmentCurrency">
		<sql>
			select * from currencies c join departments d on c.tenant_no = d.tenant_no AND c.currency_cd = d.currency_cd  where d.tenant_no=@TENANT_NO and d.company_no=@COMPANY_NO and d.department_no =@DEPARTMENT_NO 
		</sql>
	</report-dataset>
	<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="useBUOptionBio">
		<sql>
			select coalesce(property_value,'0') property_value from businessunit_properties where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and businessunit_no=@BUSINESSUNIT_NO AND property = 'USE_LA_BIO_DL_CNT'
		</sql>
	</report-dataset>


	<report-dataset name="employeeRevenue">
		<sql>
					SELECT 
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,
(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amountCustomer,
(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amountGoup1,
(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amountGoup2,
(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amountGoup3,
r_table.amount_products amount_products,
r_table.r_total_g1_services r_total_g1_services,
r_table.r_total_g2_services r_total_g2_services,
r_table.r_total_g3_services r_total_g3_services,
r_table.r_total_g1 r_total_g1,
r_table.r_total_g2 r_total_g2,
r_table.r_total_g3 r_total_g3,
r_table.r_total r_total,
r_table.r_total_services r_total_services,
r_table.r_total_products r_total_products,
(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
 r_table.r_posCntDL as r_count_services,
(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_posCntDL &lt; 0 then -r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_services_per_cus,

(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_posCntDL_G1 &lt; 0 then -r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_services_per_g1,
(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_posCntDL_G2 &lt; 0 then -r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_services_per_g2,
(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_posCntDL_G3 &lt; 0 then -r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_services_per_g3,

(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_total_g1 &lt; 0 then -r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_total_per_g1,
(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_total_g2 &lt; 0 then -r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_total_per_g2,
(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_total_g3 &lt; 0 then -r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_total_per_g3,

r_table.r_cnt_dt r_cnt_dt


	
FROM(
select
			p.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,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type1AmountGoup3,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type2AmountGoup3,
			 SUM ( case when coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then -coalesce(p.amount, 1)  else coalesce(p.amount, 1)  End ELSE 0 END ) amount_products,
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end ) r_total,
			sum(case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_services,
			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 ) r_total_products,
			sum(case when coalesce(h.customer_group_no, 1) = 1 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g1_services,
			sum(case when coalesce(h.customer_group_no, 1) = 2 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g2_services,
			sum(case when coalesce(h.customer_group_no, 1) = 3 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g3_services,
			sum(case when coalesce(h.customer_group_no, 1) = 1 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g1,
			sum(case when coalesce(h.customer_group_no, 1) = 2 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g2,

			sum(case when coalesce(h.customer_group_no, 1) = 3 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g3,
			sum(case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -1 else 1  end else 0 end ) r_posCntDL,
			sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, 1) = 1 then case when h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end ) r_posCntDL_G1,
			sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, 1) = 2 then case when h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end ) r_posCntDL_G2,
			sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, 1) = 3 then case when h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end ) r_posCntDL_G3
			from sales_inv_positions p  
			left 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
		    left join customer_roles cr on cr.tenant_no = h.tenant_no and h.company_no=cr.company_no and h.CUSTOMER_NO = cr.CONTACT_NO
			left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and p.employee_no= e.employee_no
			where h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD 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.employee_no IS NOT NULL
			   AND coalesce(p.profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(p.profession_no,-1222)  )  AND  coalesce( p.employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( p.employee_no,-1222) )
			   GROUP BY p.employee_no,e.employee_nm 
			   ) r_table
			   order by r_total desc

		</sql>
	</report-dataset>


	<report-dataset name="sumRevenue">
		<sql>
				SELECT 

(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amountCustomer,
(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amountGoup1,
(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amountGoup2,
(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amountGoup3,
r_table.amount_products amount_products,
r_table.r_total_g1_services r_total_g1_services,
r_table.r_total_g2_services r_total_g2_services,
r_table.r_total_g3_services r_total_g3_services,
r_table.r_total_g1 r_total_g1,
r_table.r_total_g2 r_total_g2,
r_table.r_total_g3 r_total_g3,
r_table.r_total r_total,
r_table.r_total_services r_total_services,
r_table.r_total_products r_total_products,
(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
 r_table.r_posCntDL as r_count_services,
(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_posCntDL &lt; 0 then -r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_services_per_cus,

(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_posCntDL_G1 &lt; 0 then -r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_services_per_g1,
(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_posCntDL_G2 &lt; 0 then -r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_services_per_g2,
(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_posCntDL_G3 &lt; 0 then -r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_services_per_g3,

(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_total_g1 &lt; 0 then -r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_total_per_g1,
(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_total_g2 &lt; 0 then -r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_total_per_g2,
(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_total_g3 &lt; 0 then -r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_total_per_g3,

r_table.r_cnt_dt r_cnt_dt,
r_table.r_cnt_employee r_cnt_employee

FROM(
select
				p.tenant_no ,
p.pos_cd,	
COUNT (  DISTINCT (cast(sales_inv_ts AS DATE))) r_cnt_dt,
			COUNT (  DISTINCT    p.employee_no  ) r_cnt_employee,
			
			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,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type1AmountGoup3,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type2AmountGoup3,
		    SUM ( case when coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then -coalesce(p.amount, 1)  else coalesce(p.amount, 1)  End ELSE 0 END ) amount_products,
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end  ) r_total,
			sum(case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_services,
			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  ) r_total_products,
			sum(case when coalesce(h.customer_group_no, 1) = 1 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_g1_services,
			sum(case when coalesce(h.customer_group_no, 1) = 2 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_g2_services,
			sum(case when coalesce(h.customer_group_no, 1) = 3 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_g3_services,
			sum(case when coalesce(h.customer_group_no, 1) = 1 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_g1,
			sum(case when coalesce(h.customer_group_no, 1) = 2 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_g2,
			sum(case when coalesce(h.customer_group_no, 1) = 3 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end  ) r_total_g3,
			sum(case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -1 else 1  end else 0 end ) r_posCntDL,
			sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, 1) = 1 then case when h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end ) r_posCntDL_G1,
			sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, 1) = 2 then case when h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end ) r_posCntDL_G2,
			sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, 1) = 3 then case when h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end ) r_posCntDL_G3
						from sales_inv_positions p 
			left 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
			left join customer_roles cr on cr.tenant_no = h.tenant_no and h.company_no=cr.company_no and h.CUSTOMER_NO = cr.CONTACT_NO
			where  h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD 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.employee_no IS NOT NULL
			 AND coalesce(p.profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(p.profession_no,-1222)  )  AND  coalesce( p.employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( p.employee_no,-1222) )
			  GROUP BY p.tenant_no , p.pos_cd
			 
			   ) r_table

		</sql>
	</report-dataset>


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


		SELECT 		r_table.employee_no employee_no
				  	,r_table.cnt_dl_bio cnt_dl_bio
				  	,r_table.cnt_dl_g1_bio cnt_dl_g1_bio
					,r_table.cnt_dl_g2_bio cnt_dl_g2_bio
					,r_table.cnt_dl_g3_bio cnt_dl_g3_bio
					,(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amountCustomer
					,(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amountGoup1
					,(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amountGoup2
					,(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amountGoup3
					,(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.cnt_dl_bio &lt; 0 then -r_table.cnt_dl_bio/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.cnt_dl_bio/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_services_per_cus

					,(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.cnt_dl_g1_bio &lt; 0 then -r_table.cnt_dl_g1_bio/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.cnt_dl_g1_bio/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_services_per_g1
					,(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.cnt_dl_g2_bio &lt; 0 then -r_table.cnt_dl_g2_bio/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.cnt_dl_g2_bio/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_services_per_g2
					,(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.cnt_dl_g3_bio &lt; 0 then -r_table.cnt_dl_g3_bio/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.cnt_dl_g3_bio/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_services_per_g3

FROM ( 

		SELECT 		t.employeeNo employee_no
					 ,sum(case when coalesce(t.stockable, 0)=0 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END) cnt_dl_bio
					,sum(case when coalesce(t.stockable, 0)=0 then case when coalesce(customer_group_no ,1) = 1 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END ELSE 0 END) cnt_dl_g1_bio
					,sum(case when coalesce(t.stockable, 0)=0 then case when coalesce(customer_group_no ,1) = 2 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END ELSE 0 END) cnt_dl_g2_bio
					,sum(case when coalesce(t.stockable, 0)=0 then case when coalesce(customer_group_no ,1) = 3 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END ELSE 0 END) cnt_dl_g3_bio	
					,COUNT (  DISTINCT   (case WHEN coalesce(t.invType, 1) = 1 THEN t.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer
					,COUNT (  DISTINCT   (case WHEN coalesce(t.invType, 1) = 2 THEN t.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 1 and coalesce(t.customer_group_no, 1) = 1 THEN t.sales_inv_id  ELSE NULL END )) type1AmountGoup1
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 1 and coalesce(t.customer_group_no, 1) = 2 THEN t.sales_inv_id  ELSE NULL END )) type1AmountGoup2
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 1 and coalesce(t.customer_group_no, 1) = 3 THEN t.sales_inv_id  ELSE NULL END)) type1AmountGoup3
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 2 and coalesce(t.customer_group_no, 1) = 1 THEN t.sales_inv_id  ELSE NULL END )) type2AmountGoup1
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 2 and coalesce(t.customer_group_no, 1) = 2 THEN t.sales_inv_id  ELSE NULL END )) type2AmountGoup2
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 2 and coalesce(t.customer_group_no, 1) = 3 THEN t.sales_inv_id  ELSE NULL END)) type2AmountGoup3

FROM (
			SELECT 
						h.Sales_inv_id,
						p.employee_no employeeNo,
						h.customer_group_no,
						CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.item_cd ELSE 'GOUPCD' + irgm.item_report_group_cd END itemCnt_cd,
						coalesce(h.sales_inv_type, 1) invType
						,CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.stockable ELSE 0 END stockable
					
			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
			left join 	item_report_group_members irgm 
				on 		h.tenant_no = irgm.tenant_no 
					AND irgm.company_no = h.company_no 
					AND irgm.department_no = h.department_no 
					AND p.item_cd = irgm.item_cd
			left join 	item_report_groups irg 
				on 		irg.tenant_no = irgm.tenant_no 
					AND irgm.company_no = irg.company_no 
					AND irgm.department_no = irg.department_no 
					AND irg.item_report_category_cd = irgm.item_report_category_cd 
					AND irg.item_report_group_cd = irgm.item_report_group_cd
			
			where 		h.tenant_no=@TENANT_NO 
				and 	h.pos_cd=@POS_CD  
				and 	p.employee_no IS NOT NULL
			 	AND 	coalesce(p.profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(p.profession_no,-1222)  )  AND  coalesce( p.employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( p.employee_no,-1222) )
				and 	h.sales_inv_id in 
							(
								select 	sales_inv_id 
								from 		sales_invs 
								where  		tenant_no=@TENANT_NO 
									and 	pos_cd=@POS_CD  
									AND 	cast (sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) 
									AND  	cast (sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
							)
			
			GROUP BY 	h.sales_inv_id
						, p.employee_no
						,h.customer_group_no
						,(CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.item_cd ELSE 'GOUPCD' + irgm.item_report_group_cd END)
						,coalesce(h.sales_inv_type, 1)
						,CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.stockable ELSE 0 END 
			) t

			GROUP BY t.employeeNo
	)r_table

</sql>
</report-dataset>


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

	SELECT 			r_table.tenant_no employee_no
				  	,r_table.cnt_dl_bio cnt_dl_bio
				  	,r_table.cnt_dl_g1_bio cnt_dl_g1_bio
					,r_table.cnt_dl_g2_bio cnt_dl_g2_bio
					,r_table.cnt_dl_g3_bio cnt_dl_g3_bio
					,(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amountCustomer
					,(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amountGoup1
					,(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amountGoup2
					,(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amountGoup3
					,(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.cnt_dl_bio &lt; 0 then -r_table.cnt_dl_bio/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.cnt_dl_bio/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_services_per_cus

					,(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.cnt_dl_g1_bio &lt; 0 then -r_table.cnt_dl_g1_bio/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.cnt_dl_g1_bio/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_services_per_g1
					,(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.cnt_dl_g2_bio &lt; 0 then -r_table.cnt_dl_g2_bio/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.cnt_dl_g2_bio/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_services_per_g2
					,(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.cnt_dl_g3_bio &lt; 0 then -r_table.cnt_dl_g3_bio/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.cnt_dl_g3_bio/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_services_per_g3

FROM ( 

		SELECT 		t.tenant_no tenant_no
				 ,sum(case when coalesce(t.stockable, 0)=0 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END) cnt_dl_bio
					,sum(case when coalesce(t.stockable, 0)=0 then case when coalesce(customer_group_no ,1) = 1 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END ELSE 0 END) cnt_dl_g1_bio
					,sum(case when coalesce(t.stockable, 0)=0 then case when coalesce(customer_group_no ,1) = 2 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END ELSE 0 END) cnt_dl_g2_bio
					,sum(case when coalesce(t.stockable, 0)=0 then case when coalesce(customer_group_no ,1) = 3 then case when t.invType = 1 THEN 1 else -1 END ELSE 0 END ELSE 0 END) cnt_dl_g3_bio	
					,COUNT (  DISTINCT   (case WHEN coalesce(t.invType, 1) = 1 THEN t.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer
					,COUNT (  DISTINCT   (case WHEN coalesce(t.invType, 1) = 2 THEN t.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 1 and coalesce(t.customer_group_no, 1) = 1 THEN t.sales_inv_id  ELSE NULL END )) type1AmountGoup1
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 1 and coalesce(t.customer_group_no, 1) = 2 THEN t.sales_inv_id  ELSE NULL END )) type1AmountGoup2
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 1 and coalesce(t.customer_group_no, 1) = 3 THEN t.sales_inv_id  ELSE NULL END)) type1AmountGoup3
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 2 and coalesce(t.customer_group_no, 1) = 1 THEN t.sales_inv_id  ELSE NULL END )) type2AmountGoup1
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 2 and coalesce(t.customer_group_no, 1) = 2 THEN t.sales_inv_id  ELSE NULL END )) type2AmountGoup2
					,COUNT (  DISTINCT (case WHEN coalesce(t.invType, 1) = 2 and coalesce(t.customer_group_no, 1) = 3 THEN t.sales_inv_id  ELSE NULL END)) type2AmountGoup3
FROM (
			SELECT 		h.tenant_no,
						h.Sales_inv_id,
						p.employee_no employeeNo,
						h.customer_group_no,
						CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.item_cd ELSE 'GOUPCD' + irgm.item_report_group_cd END itemCnt_cd,
						coalesce(h.sales_inv_type, 1) invType
						,CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.stockable ELSE 0 END stockable
					
			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
			left join 	item_report_group_members irgm 
				on 		h.tenant_no = irgm.tenant_no 
					AND irgm.company_no = h.company_no 
					AND irgm.department_no = h.department_no 
					AND p.item_cd = irgm.item_cd
			left join 	item_report_groups irg 
				on 		irg.tenant_no = irgm.tenant_no 
					AND irgm.company_no = irg.company_no 
					AND irgm.department_no = irg.department_no 
					AND irg.item_report_category_cd = irgm.item_report_category_cd 
					AND irg.item_report_group_cd = irgm.item_report_group_cd
			
			where 		h.tenant_no=@TENANT_NO 
				and 	h.pos_cd=@POS_CD  
				and 	p.employee_no IS NOT NULL
			 	AND 	coalesce(p.profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(p.profession_no,-1222)  )  AND  coalesce( p.employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( p.employee_no,-1222) )
				and 	h.sales_inv_id in 
							(
								select 	sales_inv_id 
								from 		sales_invs 
								where  		tenant_no=@TENANT_NO 
									and 	pos_cd=@POS_CD  
									AND 	cast (sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) 
									AND  	cast (sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
							)
			
			GROUP BY 	h.tenant_no
						,h.sales_inv_id
						, p.employee_no
						,h.customer_group_no
						,(CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.item_cd ELSE 'GOUPCD' + irgm.item_report_group_cd END)
						,coalesce(h.sales_inv_type, 1)
						,CASE WHEN irgm.item_report_group_cd IS  NULL THEN p.stockable ELSE 0 END 
			) t

			GROUP BY t.tenant_no
	)r_table
</sql>
</report-dataset>


	<report-dataset name="rebates">
		<sql>
		
					SELECT 

r_employee_no r_employee_no,
r_employee_nm r_employee_nm,
r_rabate_pos r_rabate_pos,
r_rabate_inv r_rabate_inv,
	r_rabate_pos + r_rabate_inv r_rabate_total 
FROM(
select
			p.employee_no r_employee_no,
			p.employee_nm r_employee_nm,
			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_rabate_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 
			left 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
		  
			where h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD 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.employee_no IS NOT NULL
			   AND coalesce(p.profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(p.profession_no,-1222)  )  AND  coalesce( p.employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( p.employee_no,-1222) )
			   GROUP BY p.employee_no,p.employee_nm   
			
			  ) r_table
			
			
		
			</sql>
	</report-dataset>
	
</report>
