<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Dienstleistungsverkauf.xsl" csv-renderer="DienstleistungsverkaufCSV.xsl">
	<title>Dienstleistungsverkauf</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="serviceRevenue">
	</report-result>

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

    <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="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="serviceRevenue">
		<sql>
			SELECT 

              r_table.r_item_cd r_item_cd,
              r_table.r_max_dt r_max_dt,
              r_table.r_total_services r_total_services,
              r_table.r_total_services_amount r_total_services_amount
            FROM(
              select
			  
			      p.item_cd r_item_cd,
			      MAX ( cast(sales_inv_ts AS DATE)) r_max_dt,
			      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)=0 then case when h.sales_inv_type=2 then -p.amount else p.amount end else 0 end) r_total_services_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) )
			   and   coalesce(p.stockable, 0)=0
			  GROUP BY  p.item_cd
			) r_table
			order by r_total_services_amount desc

		</sql>
	</report-dataset>

	
		<report-dataset name="items">
		<sql>
			SELECT * FROM items where coalesce(stockable,0) = 0 AND tenant_no=@TENANT_NO AND company_no = @COMPANY_NO AND department_no = @DEPARTMENT_NO

		</sql>
	</report-dataset>

	
</report>
