<?xml version="1.0"?>

<report name="cash-report" csv-renderer="RabattaktionenKundenCsv.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="rebatesInvPercent">
	</report-result>
	
	<report-result report-dataset="rebatesPosPercent">
	</report-result>
    	<report-result report-dataset="rebatesPos">
	</report-result>

		<report-result report-dataset="employeemode">
	</report-result>
		<report-result report-dataset="employeeList">
	</report-result>
	<report-result report-dataset="fastInput">
	</report-result>
	

		<report-result report-dataset="employee">
	</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="fastInput">
		<sql>
			select fast_input_nm from fast_inputs where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and market_no=@MARKET_NO and fast_input_cd=coalesce(@FAST_INPUT_CD,'######')
		</sql>
	</report-dataset>

<report-dataset name="employeemode">
		<sql>
			select 'ausf' employee_mode from tenants where tenant_no=@TENANT_NO
		</sql>
	</report-dataset>


	<report-dataset name="employee">
		<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="employeeList">
		<sql>
		
	
	
select	r_table.rr_tenant_no rr_tenant_no
			,r_table.rr_pos_cd rr_pos_cd
			,r_table.rr_employee_no rr_employee_no
			,r_table.rr_employee_nm rr_employee_nm
			
FROM (
	select	p.tenant_no rr_tenant_no
			,p.pos_cd rr_pos_cd
			,wr.operating_employee_no rr_employee_no
			,e.employee_nm rr_employee_nm
	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 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 cast (sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
								 )
		AND wr.operating_employee_no = coalesce(@EMPLOYEE_NO,wr.operating_employee_no  )
			   GROUP BY p.tenant_no ,p.pos_cd,wr.operating_employee_no ,e.employee_nm


) r_table
		
			</sql>
	</report-dataset>


	<report-dataset name="rebatesInvPercent">
		<sql>
		
	
	
select	r_table.rr_tenant_no rr_tenant_no
			,r_table.rr_pos_cd rr_pos_cd
			,r_table.rr_employee_no rr_employee_no
			,r_table.rr_customer_no rr_customer_no
			,c.first_nm first_nm
			,c.contact_nm contact_nm
			,c.phone customer_phone
			,c.mobile customer_mobile
			,c.email customer_email
			,c.postal_cd customer_postal_cd
			,c.city customer_city
			,c.street customer_street
			,r_table.reporting_cd reporting_cd
            ,r_table.reporting_nm reporting_nm

		   	,(r_table.anzahl_typ1 -r_table.anzahl_typ2) anzahl
			,r_table.rr_inv_rebate_base_price rr_rebate_base_price
			,r_table.rr_rabate_inv rr_rabate
			,r_table.rr_gross_price rr_gross_price
FROM (
	select	p.tenant_no rr_tenant_no
			,p.pos_cd rr_pos_cd
			,wr.operating_employee_no rr_employee_no
			,h.company_no rr_company_no
			,h.customer_no rr_customer_no
			,coalesce(h.reporting_cd,'-') reporting_cd
            ,coalesce(h.reporting_nm,'-') reporting_nm
			,count(	distinct (case when h.sales_inv_type=1 then p.sales_inv_id else null end)) anzahl_typ1
			,count(	distinct (case when h.sales_inv_type=2 then p.sales_inv_id else null end)) anzahl_typ2
			,sum(case when h.sales_inv_type=2 then -p.inv_rebate_base_price else p.inv_rebate_base_price end * wr.operating_employee_factor) rr_inv_rebate_base_price
			,sum(case when h.sales_inv_type=2 then -p.applied_inv_rebate_price else p.applied_inv_rebate_price end * wr.operating_employee_factor) rr_rabate_inv
			,sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end * wr.operating_employee_factor) rr_gross_price
	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
	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 h.inv_rebate_rate_percent IS NOT NULL
			AND wr.operating_employee_no = coalesce(@EMPLOYEE_NO,wr.operating_employee_no  )
			AND coalesce(@FAST_INPUT_CD,coalesce(h.reporting_cd,'-')) = coalesce(h.reporting_cd,'-')
			    GROUP BY p.tenant_no ,p.pos_cd,wr.operating_employee_no ,h.company_no, h.customer_no , coalesce(h.reporting_cd,'-'),coalesce(h.reporting_nm,'-')


) r_table

 JOIN planet_contact_master pcm  on pcm.TENANT_NO =  r_table.rr_tenant_no  and pcm.POS_CD =  r_table.rr_pos_cd  and pcm.CONTACT_NO =  r_table.rr_customer_no
 JOIN planet_main_contacts c on pcm.tenant_no = c.tenant_no and pcm.master_cd = c.master_cd
		
			</sql>
	</report-dataset>

<report-dataset name="rebatesPosPercent">
		<sql>
select	r_table.rr_tenant_no rr_tenant_no
			,r_table.rr_pos_cd rr_pos_cd
			,r_table.rr_employee_no rr_employee_no
			,r_table.rr_customer_no rr_customer_no
			,c.first_nm first_nm
			,c.contact_nm contact_nm
			,c.phone customer_phone
			,c.mobile customer_mobile
			,c.email customer_email
			,c.postal_cd customer_postal_cd
			,c.city customer_city
			,c.street customer_street
			,r_table.reporting_cd reporting_cd
             ,r_table.reporting_nm reporting_nm
		   	,(r_table.anzahl_typ1 -r_table.anzahl_typ2) anzahl
			,r_table.rr_pos_rebate_base_price rr_rebate_base_price
			,r_table.rr_rabate_pos rr_rabate
			,r_table.rr_pos_gross_price rr_gross_price
FROM (
	select	p.tenant_no rr_tenant_no
			,p.pos_cd rr_pos_cd
			,wr.operating_employee_no rr_employee_no
			,h.company_no rr_company_no
			,h.customer_no rr_customer_no
			,coalesce(p.reporting_cd,'-') reporting_cd
            ,coalesce(p.reporting_nm,'-') reporting_nm
		   	,count(	distinct (case when h.sales_inv_type=1 then p.sales_inv_id else null end)) anzahl_typ1
			,count(	distinct (case when h.sales_inv_type=2 then p.sales_inv_id else null end)) anzahl_typ2
			,sum(case when h.sales_inv_type=2 then -p.pos_rebate_base_price else p.pos_rebate_base_price end * wr.operating_employee_factor) rr_pos_rebate_base_price
			,sum(case when h.sales_inv_type=2 then -p.applied_pos_rebate_price else p.applied_pos_rebate_price end * wr.operating_employee_factor) rr_rabate_pos
			,sum(case when h.sales_inv_type=2 then -p.inv_rebate_base_price else p.inv_rebate_base_price end * wr.operating_employee_factor) rr_pos_gross_price
	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) 
								)
			AND wr.operating_employee_no = coalesce(@EMPLOYEE_NO,wr.operating_employee_no  )
			AND coalesce(p.pos_rebate_rate_percent ,0.0) &gt; 0
			AND coalesce(p.applied_pos_rebate_price,0.0) &gt; 0
			AND coalesce(@FAST_INPUT_CD,coalesce(p.reporting_cd,'-')) = coalesce(p.reporting_cd,'-')
			    GROUP BY p.tenant_no ,p.pos_cd,wr.operating_employee_no ,h.company_no, h.customer_no ,coalesce(p.reporting_cd,'-'),coalesce(p.reporting_nm,'-')
) r_table
 JOIN planet_contact_master pcm  on pcm.TENANT_NO =  r_table.rr_tenant_no  and pcm.POS_CD =  r_table.rr_pos_cd  and pcm.CONTACT_NO =  r_table.rr_customer_no
 JOIN planet_main_contacts c on pcm.tenant_no = c.tenant_no and pcm.master_cd = c.master_cd


				</sql>
	</report-dataset>


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

select	r_table.rr_tenant_no rr_tenant_no
			,r_table.rr_pos_cd rr_pos_cd
			,r_table.rr_employee_no rr_employee_no
			,r_table.rr_customer_no rr_customer_no
			,c.first_nm first_nm
			,c.contact_nm contact_nm
			,c.phone customer_phone
			,c.mobile customer_mobile
			,c.email customer_email
			,c.postal_cd customer_postal_cd
			,c.city customer_city
			,c.street customer_street
			,r_table.reporting_cd reporting_cd
            ,r_table.reporting_nm reporting_nm
		   	,(r_table.anzahl_typ1 -r_table.anzahl_typ2) anzahl
			,r_table.rr_pos_rebate_base_price rr_rebate_base_price
			,r_table.rr_rabate_pos rr_rabate
			,r_table.rr_pos_gross_price rr_gross_price
FROM (
	select	p.tenant_no rr_tenant_no
			,p.pos_cd rr_pos_cd
			,wr.operating_employee_no rr_employee_no
			,h.company_no rr_company_no
			,h.customer_no rr_customer_no
			,coalesce(p.reporting_cd,'-') reporting_cd
            ,coalesce(p.reporting_nm,'-') reporting_nm
		   	,count(	distinct (case when h.sales_inv_type=1 then p.sales_inv_id else null end)) anzahl_typ1
			,count(	distinct (case when h.sales_inv_type=2 then p.sales_inv_id else null end)) anzahl_typ2
			,sum(case when h.sales_inv_type=2 then -p.pos_rebate_base_price else p.pos_rebate_base_price end * wr.operating_employee_factor) rr_pos_rebate_base_price
			,sum(case when h.sales_inv_type=2 then -p.applied_pos_rebate_price else p.applied_pos_rebate_price end * wr.operating_employee_factor) rr_rabate_pos
			,sum(case when h.sales_inv_type=2 then -p.inv_rebate_base_price else p.inv_rebate_base_price end * wr.operating_employee_factor) rr_pos_gross_price
	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) 
								)
			AND wr.operating_employee_no = coalesce(@EMPLOYEE_NO,wr.operating_employee_no  )
			AND coalesce(@FAST_INPUT_CD,coalesce(p.reporting_cd,'-')) = coalesce(p.reporting_cd,'-')
			AND coalesce(p.pos_rebate_price ,0.0) &gt; 0
			AND coalesce(p.applied_pos_rebate_price,0.0) &gt; 0
			    GROUP BY p.tenant_no ,p.pos_cd,wr.operating_employee_no,h.company_no, h.customer_no ,coalesce(p.reporting_cd,'-'),coalesce(p.reporting_nm,'-')
) r_table

 JOIN planet_contact_master pcm  on pcm.TENANT_NO =  r_table.rr_tenant_no  and pcm.POS_CD =  r_table.rr_pos_cd  and pcm.CONTACT_NO =  r_table.rr_customer_no
 JOIN planet_main_contacts c on pcm.tenant_no = c.tenant_no and pcm.master_cd = c.master_cd

				</sql>
	</report-dataset>




</report>
