<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="BVEntwicklung.xsl">
	<title>Betriebsvergleich</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="DATE" 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="customerVisits">
	</report-result>-->

	<report-result report-dataset="categorylist">
	</report-result>
<!--
	<report-result report-dataset="serviceStructure_dl">
	</report-result>

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

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


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


	
	<report-result report-dataset="ama-data">
	</report-result>
	
	<report-result report-dataset="lma-data">
	</report-result>-->
	
<report-transaction report-nm="bv" report-transaction="de.timeglobe.pos.reporting.bv.Betriebsvergleich">
	</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="customerVisits">
		<sql>
 SELECT
	sum(Anzahl_Bestand) AS Bestandskunden
	,sum(Neukunde) AS Neukunden
	,sum(CASE WHen customerGroupNo = 1 THEN Anzahl_Bestand ELSE 0 END ) AS Bestandskunden_cg1
	,sum(CASE WHen customerGroupNo = 1 THEN Neukunde ELSE 0 END ) AS Neukunden_cg1
	,sum(CASE WHen customerGroupNo = 2 THEN Anzahl_Bestand ELSE 0 END ) AS Bestandskunden_cg2
	,sum(CASE WHen customerGroupNo = 2 THEN Neukunde ELSE 0 END ) AS Neukunden_cg2
	,sum(CASE WHen customerGroupNo = 3 THEN Anzahl_Bestand ELSE 0 END ) AS Bestandskunden_cg3
	,sum(CASE WHen customerGroupNo = 3 THEN Neukunde ELSE 0 END ) AS Neukunden_cg3
	,sum(CASE WHen customerGroupNo = 1 THEN 1 ELSE 0 END ) AS customer_cg1
	,sum(CASE WHen customerGroupNo = 2 THEN 1 ELSE 0 END ) AS customer_cg2
	,sum(CASE WHen customerGroupNo = 3 THEN 1 ELSE 0 END ) AS customer_cg3

	
FROM (
	SELECT 
		t.customer_no
		,t.customerGroupNo
		,max(CASE WHEN t.War_da_im_Zeitraum = 1 and t.Bestandskunde = 1
					THEN 1
				ELSE 0
				END) AS Anzahl_Bestand
		,max(CASE 
				WHEN t.War_da_im_Zeitraum = 1
					AND t.Bestandskunde = 0
					THEN 1
				ELSE 0
				END) AS Neukunde

	
	FROM (
			SELECT 		si.CUSTOMER_NO
						,coalesce(cr.customer_group_no,1) customerGroupNo
						,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
			left join 	customer_roles cr on cr.TENANT_NO = si.tenant_no and cr.CONTACT_NO = si.CUSTOMER_NO and cr.COMPANY_NO = si.COMPANY_NO and cr.CUSTOMER_NO = si.CUSTOMER_NO
		
			where 		si.tenant_no = @TENANT_NO 
					and si.pos_cd=@POS_CD
		 			AND	coalesce(group_role,0) = 0
			GROUP BY 	si.CUSTOMER_NO
						,coalesce(cr.customer_group_no,1)
						,si.TENANT_NO
						,si.pos_cd
		) t
	group by 	t.customer_no
				,t.customerGroupNo
	) t2 

	
	</sql>
	</report-dataset>


	<report-dataset name="categorylist">
		<sql>
			SELECT 
						item_report_category_cd 
						,item_report_category_nm	
						,coalesce(use_customer_group,0) use_customer_group
			FROM 		item_report_categories
			where 		tenant_no=@TENANT_NO 
				and 	company_no =@COMPANY_NO  
				and 	department_no =@DEPARTMENT_NO  
			
			
		</sql>
	</report-dataset>



	<report-dataset name="serviceStructure_dl">
		<sql>
			SELECT 
						irg.item_report_category_cd item_report_category_cd
						,irgm.item_report_group_cd item_report_group_cd
						,irg.item_report_group_nm item_report_group_nm
						,sum(case when coalesce(h.sales_inv_type, 1) = 1 THEN 1 else -1 END) cnt_dl
					
			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 irgm.company_no = h.company_no 
					AND irgm.department_no = h.department_no 
					AND p.item_cd = irgm.item_cd
			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 	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) 

			
			GROUP BY 	irg.item_report_category_cd 
						,irgm.item_report_group_cd
						,irg.item_report_group_nm
			
		</sql>
	</report-dataset>

	<report-dataset name="serviceStructure_withcustomergroup_dl">
		<sql>
			SELECT 
						irg.item_report_category_cd item_report_category_cd
						,irgm.item_report_group_cd item_report_group_cd
						,irg.item_report_group_nm item_report_group_nm
						,sum(case when coalesce(h.customer_group_no,1) = 1 then  case when coalesce(h.sales_inv_type, 1) = 1 THEN 1 else -1 END ELSE 0 END) cnt_dl_g1
						,sum(case when coalesce(h.customer_group_no,1) = 2 then  case when coalesce(h.sales_inv_type, 1) = 1 THEN 1 else -1 END ELSE 0 END) cnt_dl_g2
						,sum(case when coalesce(h.customer_group_no,1) = 3 then  case when coalesce(h.sales_inv_type, 1) = 1 THEN 1 else -1 END ELSE 0 END) cnt_dl_g3
					
			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 irgm.company_no = h.company_no 
					AND irgm.department_no = h.department_no 
					AND p.item_cd = irgm.item_cd
			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 	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) 

			
			GROUP BY 	irg.item_report_category_cd 
						,irgm.item_report_group_cd
						,irg.item_report_group_nm
		
			
		</sql>
	</report-dataset>

	<report-dataset name="categoryData">
		<sql>
	SELECT	r_result.item_report_category_cd item_report_category_cd
			,(r_result.type1AmountCustomer - r_result.type2AmountCustomer) amount_customer
			,r_result.r_total_services r_total_services
			,r_result.r_total_products r_total_products
	FROM (
			SELECT 
						coalesce(irg.item_report_category_cd,'-')  item_report_category_cd
						,COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 1 THEN h.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer
						,COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 2 THEN h.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer
						,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
			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 	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) 

			
			GROUP BY 	coalesce(irg.item_report_category_cd,'-')  
		) r_result	
	
		</sql>
	</report-dataset>

	<report-dataset name="globalData">
		<sql>
	SELECT	r_result.tenant_no tenant_no
			,(r_result.type1AmountCustomer - r_result.type2AmountCustomer) amount_customer
			,r_result.cnt_working_days cnt_working_days
			,(r_result.type1AmountGoup1 - r_result.type2AmountGoup1) r_amount_group1
			,(r_result.type1AmountGoup2 - r_result.type2AmountGoup2) r_amount_group2
			,(r_result.type1AmountGoup3 - r_result.type2AmountGoup3) r_amount_group3
			,r_result.r_posCntDL r_cnt_dl
			,r_result.r_posCntDL_G1 r_cnt_dl_cg1
			,r_result.r_posCntDL_G2 r_cnt_dl_cg2
			,r_result.r_posCntDL_G3 r_cnt_dl_cg3
			
	FROM (
		SELECT 
					h.tenant_no
					,COUNT (  DISTINCT   cast (h.sales_inv_ts as DATE) ) cnt_working_days
					,COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 1 THEN h.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer
					,COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 2 THEN h.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)=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 
		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 (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) 
			AND  	cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
		GROUP BY	h.tenant_no
	) r_result	
		
	
		</sql>
	</report-dataset>	




	<report-dataset name="ama-data">
		<sql>
SELECT 

			(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_customer,
			(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amount_group1,
			(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amount_group2,
			(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amount_group3,
			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    wr.operating_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
			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 		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 
			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.tenant_no
						,p.pos_cd
					
	) r_table

		</sql>
	</report-dataset>


	<report-dataset name="lma-data">
		<sql>
SELECT 

			(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_customer,
			(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amount_group1,
			(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amount_group2,
			(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amount_group3,
			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
			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 	p.employee_no IS NOT NULL
				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.tenant_no
						,p.pos_cd
					
	) r_table

		</sql>
	</report-dataset>


</report>
