<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="UmsatzentwicklungVerkaufMitarbeiter.xsl">
	<title>Umsatz im Zeitverlauf</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-replacement name="Time">
		<replacement-selection name="Month" default="true">
			<replacement-parameter name="Label">
					case when month_value &lt; 10 then '0' + cast((month_value) as
					CHAR(1)) + '/' + cast((year_value) as CHAR(4))
					else cast((month_value) as CHAR(2)) + '/' + cast((year_value) as
					CHAR(4)) end
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value * 10000 + month_value * 100
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					month(h.sales_inv_ts) month_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts) ,
					month(h.sales_inv_ts)
			</replacement-parameter>
		</replacement-selection>
		
		<replacement-selection name="Quarter" default="false">
			<replacement-parameter name="Label">
					'Q' + cast(quarter_value as CHAR(1)) + '/' + cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + quarter_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+2)/ 3 quarter_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+2)/ 3
			</replacement-parameter>
		</replacement-selection>
		
		
		<replacement-selection name="Tertial" default="false">
			<replacement-parameter name="Label">
					'T' + cast(tertial_value as CHAR(1)) + '/' + cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + tertial_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+3)/ 4 tertial_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+3)/ 4
			</replacement-parameter>
		</replacement-selection>
		
		<replacement-selection name="HalfYear" default="false">
			<replacement-parameter name="Label">
					'HJ.' + cast(halfyear_value as CHAR(1)) + '/' + cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + halfyear_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+5)/ 6 halfyear_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+5)/ 6
			</replacement-parameter>
		</replacement-selection>
		
		<replacement-selection name="FullYear" default="false">
			<replacement-parameter name="Label">
					cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + fullyear_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+11)/ 12 fullyear_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+11)/ 12
			</replacement-parameter>
		</replacement-selection>
		
		
		

	</report-replacement>


	<report-result report-dataset="businessunits">
	</report-result>
	
	<report-result report-dataset="employee">
	</report-result>

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

	

	
<report-result report-dataset="useBUOptionBio">
	</report-result>
	
	<report-result report-dataset="employeemode">
	</report-result>
	<report-result report-dataset="employee_profession">
	</report-result>
<report-transaction report-nm="revenueData" report-transaction="de.timeglobe.reportsnew.reports.Umsatzentwicklung">
	</report-transaction>

	<report-dataset name="employee_profession">
		<sql>
			Select distinct case when @PROFESSION_NO is null then 'Alle' else profession_nm end as profession_nm from professions p where
			p.tenant_no=@TENANT_NO and p.company_no=@COMPANY_NO and p.department_no=@DEPARTMENT_NO and
			p.profession_no=coalesce(@PROFESSION_NO, p.profession_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="employeemode">
		<sql>
			select 'ausf' employee_mode from tenants where tenant_no=@TENANT_NO
		</sql>
	</report-dataset>
	<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="departmentCurrency">
		<sql>
			select * from currencies c join departments d on c.tenant_no =
			d.tenant_no AND c.currency_cd = d.currency_cd where
			d.tenant_no=@TENANT_NO and d.company_no=@COMPANY_NO and
			d.department_no =@DEPARTMENT_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>
