<?xml version="1.0"?>

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

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

	<report-result report-dataset="useBUOptionBio">
	</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="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_dt r_dt,
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.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,

r_table.r_cnt_dt r_cnt_dt


	
FROM(
select
            cast(sales_inv_ts AS DATE) r_dt,
			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,
			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 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)=0 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_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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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 * wr.operating_employee_factor) 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
			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
			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  sales_inv_ts  &gt;= cast(DATE(@DATE) as TIMESTAMP)
									AND sales_inv_ts    &lt;= {fn TIMESTAMPADD(SQL_TSI_DAY, 1, cast(DATE(@TODATE) as TIMESTAMP))})
			  AND coalesce(wr.operating_profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(wr.operating_profession_no,-1222)  )  AND wr.operating_employee_no = coalesce(@EMPLOYEE_NO,wr.operating_employee_no  )
			   GROUP BY cast(sales_inv_ts AS DATE), wr.operating_employee_no,e.employee_nm
			   ) r_table
		</sql>
	</report-dataset>

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


		SELECT 		r_table.r_dt r_dt
					,r_table.employee_no employee_no
				  	,r_table.cnt_dl_bio cnt_dl_bio
		FROM ( 
				SELECT 
									t.r_dt r_dt
									,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
				FROM (
							SELECT 
										h.Sales_inv_id
										,cast(sales_inv_ts AS DATE) r_dt
										,wr.operating_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
							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 	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 	coalesce(wr.operating_profession_no,-1222) = coalesce(@PROFESSION_NO,coalesce(wr.operating_profession_no,-1222)  )  AND  coalesce( wr.operating_employee_no,-1222) = coalesce(@EMPLOYEE_NO,coalesce( wr.operating_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  sales_inv_ts  &gt;= cast(DATE(@DATE) as TIMESTAMP)
													AND sales_inv_ts    &lt;= {fn TIMESTAMPADD(SQL_TSI_DAY, 1, cast(DATE(@TODATE) as TIMESTAMP))})
											)
							
							GROUP BY 	h.sales_inv_id
										,cast(sales_inv_ts AS DATE)
										,wr.operating_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.r_dt
										,t.employeeNo
			)r_table

</sql>
</report-dataset>


</report>
