<?xml version="1.0"?>

<report name="export-empl-stats-report" csv-renderer="ExportStatistikBeratendMitarbeiter.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="employees">
	</report-result>
	
	<report-result report-dataset="employeeDlCntPerReportGroup">
	</report-result>
	
	<report-result report-dataset="employeeDlCntTotal">
	</report-result>
	<report-result report-dataset="newCustomerList">
	</report-result>
	
	<report-result report-dataset="employeeDlCntPerCategory">
	</report-result>
	<report-transaction report-nm="crs_work_statistics" report-transaction="de.timeglobe.pos.reporting.GetCRSWorkStatistics">
	</report-transaction>
	<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="employees">
		<sql>
	select e.employee_no, e.employee_nm, Min(ec.valid_from) valid_from from employees e 
			
			join employee_contracts ec 
			  on e.tenant_no = ec.tenant_no 
			 and e.company_no = ec.company_no 
			 and e.employee_no = ec.employee_no
			
			where e.tenant_no=@TENANT_NO 
			  and e.company_no=@COMPANY_NO 
			  and ec.department_no=@DEPARTMENT_NO 
			  and ec.businessunit_no=@BUSINESSUNIT_NO
			  and  (valid_from is null OR cast (valid_from as DATE) &lt;= cast (@TODATE as DATE))  
			  and (valid_to is null OR cast (valid_to as DATE) &gt;= cast (@DATE as DATE))
			GROUP BY e.employee_no, e.employee_nm  
			  		
			  		
					</sql>
	</report-dataset>

	<report-dataset name="employeeDlCntTotal">
		<sql>
		select
            p.employee_no r_employee_no,
			
			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(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 coalesce(p.stockable, 0)=1 then 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 else 0 end) r_vk_g1,
			sum(case when coalesce(p.stockable, 0)=1 then 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 else 0 end) r_vk_g2,
			sum(case when coalesce(p.stockable, 0)=1 then 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 else 0 end ) r_vk_g3,
			COUNT (  DISTINCT (case WHEN coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) d_customer_amount,
			COUNT (  DISTINCT (case WHEN coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) h_customer_amount,
			COUNT (  DISTINCT (case WHEN coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) k_customer_amount
			
			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
		 
			 
			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) )
			  GROUP BY p.employee_no 
		</sql>
	</report-dataset>

<report-dataset name="employeeDlCntPerCategoryDD">
		<sql>
		select
          
			p.employee_no  r_employee_no,
		
			irgm.item_report_category_cd item_report_category_cd, 
			
			SUM ( case when coalesce(p.stockable, 0)=0 then case when irgm.item_report_group_cd IN ('','','') THEN  case when h.sales_inv_type=2 then -coalesce(p.amount, 1) else coalesce(p.amount, 1)  End else 0  End else 0 end) amountItemsPerReportGroup_farbfaktor_service,
			SUM ( case when h.sales_inv_type=2 then -coalesce(p.amount, 1)  else coalesce(p.amount, 1)  End ) amountItemsPerCategory,
			SUM ( case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -coalesce(p.amount, 1) else coalesce(p.amount, 1)  End else 0 end) amountItemsPerCategory_service,
			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) amountItemsPerCategory_product,
			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

			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 item_report_group_members irgm on h.tenant_no = irgm.tenant_no AND h.company_no = irgm.company_no AND h.department_no = irgm.department_no AND p.item_cd = irgm.item_cd
		 
			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) )
			  GROUP BY p.employee_no 
			
		</sql>
	</report-dataset>

<!-- 	SUM ( case when coalesce(p.stockable, 0)=0 then case when irgm.item_report_group_cd IN ('','','') THEN  case when h.sales_inv_type=2 then -coalesce(p.amount, 1) else coalesce(p.amount, 1)  End else 0  End else 0 end) amountItemsPerReportGroup_farbfaktor_service, -->
<report-dataset name="employeeDlCntPerCategory">
		<sql>
		select
          
			p.employee_no  r_employee_no,
			irgm.item_report_category_cd item_report_category_cd, 
			COUNT (DISTINCT p.sales_inv_id) cnt_sales_invs,
		
			SUM ( case when h.sales_inv_type=2 then -coalesce(p.amount, 1)  else coalesce(p.amount, 1)  End ) amountItemsPerCategory,
			SUM ( case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -coalesce(p.amount, 1) else coalesce(p.amount, 1)  End else 0 end) amountItemsPerCategory_service,
			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) amountItemsPerCategory_product,
			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

			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 
				join item_report_group_members irgm on h.tenant_no = irgm.tenant_no AND h.company_no = irgm.company_no AND h.department_no = irgm.department_no AND p.item_cd = irgm.item_cd
		 
			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) )
			  GROUP BY p.employee_no ,
				irgm.item_report_category_cd 
		</sql>
	</report-dataset>


	<report-dataset name="employeeDlCntPerReportGroup">
		<sql>
		select
          
			p.employee_no r_employee_no,
			
			irgm.item_report_category_cd item_report_category_cd, 
			irgm.item_report_group_cd item_report_group_cd,
			
			SUM ( case when h.sales_inv_type=2 then -coalesce(p.amount, 1)  else coalesce(p.amount, 1)  End ) amountItemsPerReportGroup,
			SUM ( case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -coalesce(p.amount, 1) else coalesce(p.amount, 1)  End else 0 end) amountItemsPerReportGroup_service,
		
			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) amountItemsPerReportGroup_product,
			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

			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 item_report_group_members irgm on h.tenant_no = irgm.tenant_no AND h.company_no = irgm.company_no AND h.department_no = irgm.department_no AND p.item_cd = irgm.item_cd 
		 
			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) )
			  GROUP BY p.employee_no ,
			
			irgm.item_report_category_cd, 
			irgm.item_report_group_cd
		</sql>
	</report-dataset>


	<report-dataset name="newCustomerList">
		<sql>
 SELECT
	EMPLOYEE_NO  r_employee_no,
	SUM (  case WHEN coalesce(t2.customer_group_no, 1) = 1 THEN t2.Neukunde  ELSE 0 END ) d_new_customer,
    SUM (  case WHEN coalesce(t2.customer_group_no, 1) = 2 THEN t2.Neukunde  ELSE 0 END ) h_new_customer,
    SUM (  case WHEN coalesce(t2.customer_group_no, 1) = 3 THEN t2.Neukunde  ELSE 0 END ) k_new_customer	
FROM (
	SELECT t.EMPLOYEE_NO EMPLOYEE_NO,
		t.customer_group_no
		,t.customer_no
		
		,MAX(CASE 
				WHEN t.War_da_im_Zeitraum = 1
					AND t.Bestandskunde = 0
					THEN 1
				ELSE 0
				END) AS Neukunde
				
	FROM  (
		SELECT sip.EMPLOYEE_NO
		,coalesce(si.customer_group_no,1)  customer_group_no
			,si.COMPANY_NO
			,si.CUSTOMER_NO
			
			,si.TENANT_NO
			,si.pos_cd
			,max(CASE when cr.IMPORTED = 1 then 1 
				WHEN DATE(sales_inv_ts)  &lt; cast(@DATE as DATE)
					THEN 1
					ELSE 0
					END) AS Bestandskunde
			,max(CASE 
					WHEN DATE (sales_inv_ts) &gt;= cast(@DATE as DATE)
						AND DATE (sales_inv_ts)  &lt;= cast(@TODATE as DATE)
						THEN 1
					ELSE 0
					END) AS War_da_im_Zeitraum
			
		FROM SALES_INVS si
		 join customer_roles cr on cr.TENANT_NO = si.tenant_no and cr.COMPANY_NO = si.COMPANY_NO and cr.CONTACT_NO = si.CUSTOMER_NO 
		and coalesce(group_role,0) = 0
		join SALES_INV_POSITIONS sip on sip.tenant_no = si.tenant_no AND sip.pos_cd = si.pos_cd AND sip.sales_inv_id = si.sales_inv_id 
	
		where si.tenant_no = @TENANT_NO and si.pos_cd=@POS_CD
		GROUP BY sip.EMPLOYEE_NO 
		,coalesce(si.customer_group_no,1)
			, si.COMPANY_NO
			,si.CUSTOMER_NO
			,si.TENANT_NO
			,si.pos_cd
		) t 
	
	GROUP BY t.EMPLOYEE_NO
	   ,t.customer_group_no
		,t.customer_no
	) t2 
GROUP BY EMPLOYEE_NO 
 
	
	</sql>
	</report-dataset>
	
</report>
