<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Kabinettverbrauch_MS.xsl">
	<title>Kabinettverbrauch</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-transaction report-nm="productConsumptionStock" report-transaction="de.timeglobe.pos.reporting.ProductStockConsumption">
	</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="productConsumptionStock">
		<sql>
			SELECT	r_table.item_cd r_item_cd, 
			r_table.item_nm r_item_nm,
			r_table.amount r_amount,
			r_table.max_stock_dln_ts r_max_stock_dln_ts
			FROM
				(SELECT	sdp.item_cd item_cd, 
						i.item_nm item_nm,
						SUM(CASE WHEN sdp.direction_type = 2 then amount ELSE -amount END) amount,
						max(sd.stock_dln_ts) max_stock_dln_ts
				FROM
						stock_dln_positions sdp
				JOIN	stock_dlns sd
				ON		sdp.tenant_no = sd.tenant_no AND sdp.pos_cd = sd.pos_cd	AND sdp.stock_dln_id = sd.stock_dln_id 	  
				JOIN	items i
				ON		sdp.tenant_no = i.tenant_no AND sdp.company_no = i.company_no AND sdp.department_no = i.department_no AND sdp.item_cd = i.item_cd
				WHERE	sdp.tenant_no = @TENANT_NO
				AND		sdp.pos_cd = @POS_CD
				AND		cast (sd.stock_dln_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (sd.stock_dln_ts as DATE) &lt;= cast (@TODATE as DATE) 
				AND		sd.stock_dln_type = 4
				GROUP BY	sdp.item_cd, 
							i.item_nm
				) r_table
			ORDER BY r_table.amount desc,r_table.item_nm
		</sql>
	</report-dataset>


	
	
</report>
