<?xml version="1.0"?>

<report name="cash-report" csv-renderer="UmsatzBeratendeMitarbeiterCSV.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="businessunits">
	</report-result>


	<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="employeeRevenue">
		<sql>
				SELECT 
r_table.r_dt r_dt,
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,
r_table.r_profession_no r_profession_no,
r_table.r_profession_nm r_profession_nm,
r_table.amountCustomer r_amount_customer,
r_table.amountGoup1 r_amount_group1,
r_table.amountGoup2 r_amount_group2,
r_table.amountGoup3 r_amount_group3,
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.amountCustomer = 0 then 0 else r_table.r_posCntDL/cast(r_table.amountCustomer as float) end) r_services_per_cus,
(case when r_table.amountGoup1 = 0 then 0 else r_table.r_posCntDL_G1/cast(r_table.amountGoup1 as float) 	end)  r_services_per_g1,
(case when r_table.amountGoup2 = 0 then 0 else r_table.r_posCntDL_G2/cast(r_table.amountGoup2 as float) 	end) r_services_per_g2,
(case when r_table.amountGoup3 = 0 then 0 else r_table.r_posCntDL_G3/cast(r_table.amountGoup3 as float) 	end) r_services_per_g3,
(case when r_table.amountGoup1 = 0 then 0 else r_table.r_total_g1/r_table.amountGoup1 end) r_total_per_g1,
(case when r_table.amountGoup2 = 0 then 0 else r_table.r_total_g2/r_table.amountGoup2 end) r_total_per_g2,
(case when r_table.amountGoup3 = 0 then 0 else r_table.r_total_g3/r_table.amountGoup3 end) r_total_per_g3,
r_table.r_cnt_dt r_cnt_dt
FROM(
select
            cast(sales_inv_ts AS DATE) r_dt,
			wr.employee_no r_employee_no,
			wr.employee_nm r_employee_nm,
			wr.profession_no r_profession_no,
			wr.profession_nm r_profession_nm,
			COUNT (  DISTINCT (cast(sales_inv_ts AS DATE))) r_cnt_dt,
			COUNT (  DISTINCT  p.sales_inv_id ) amountCustomer,
			COUNT (  DISTINCT (case WHEN coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) amountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) amountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) amountGoup3,
			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.CUSTOMER_NO = cr.CONTACT_NO
		    join sales_inv_positions wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_inv_id = wr.sales_inv_id and p.sales_inv_position_id = wr.sales_inv_position_id 
			where h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD 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) )
			 	  and p.employee_no IS NOT NULL
			    AND coalesce(wr.profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(wr.profession_no,-1222)  )  AND  coalesce( wr.employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( wr.employee_no,-1222) )
			   GROUP BY cast(sales_inv_ts AS DATE), wr.employee_no,wr.employee_nm, wr.profession_no, wr.profession_nm
			   ) r_table

		</sql>
	</report-dataset>

	
</report>
