<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Kabinettverbrauch.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="productConsumptionStock">
	</report-result>-->
	<report-result report-dataset="purchase_item_prices">
	</report-result>
	<report-result report-dataset="item_suppliers">
	</report-result>
	
	<report-result report-dataset="businessunits">
	</report-result>
	<report-transaction report-nm="productConsumptionStock" report-transaction="de.timeglobe.pos.reporting.ProductStockConsumption">
	</report-transaction>
	<report-result report-dataset="supplierNm">
	</report-result>

<report-dataset name="supplierNm">
		<sql>
			Select distinct case when @I_SUPPLIER_NO is null then 'Alle' else contact_nm end as supplier_nm 
				from contacts e where
			e.tenant_no=@TENANT_NO AND e.contact_no=coalesce(@I_SUPPLIER_NO, e.contact_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="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.tenant_no,
						sdp.company_no,
						sdp.department_no,
						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.tenant_no,
						sdp.company_no,
						sdp.department_no,
						sdp.item_cd, 
						i.item_nm
				) r_table
			 left JOIN 
			  			( 	SELECT 	tenant_no
						  			,company_no
									,department_no
									,item_cd
									,coalesce(@I_SUPPLIER_NO , -100) hasSupplier
							FROM 	item_suppliers
							WHERE 	contact_no = coalesce(@I_SUPPLIER_NO , -100)
							GROUP BY 
									tenant_no
						  			,company_no
									,department_no
									,item_cd
									,coalesce(@I_SUPPLIER_NO , -100)
				  		) itemsWithSupplier
			  
				on 		itemsWithSupplier.tenant_no = r_table.tenant_no 
					AND	itemsWithSupplier.company_no = r_table.company_no 
					AND	itemsWithSupplier.department_no = r_table.department_no 
					AND	itemsWithSupplier.item_cd = r_table.item_cd 

			WHERE (  @I_SUPPLIER_NO IS NULL OR  coalesce(itemsWithSupplier.hasSupplier , -100) = coalesce(@I_SUPPLIER_NO , -100))

			ORDER BY r_table.amount desc,r_table.item_nm
		</sql>
	</report-dataset>


	<report-dataset name="purchase_item_prices">
		<sql>
  SELECT pip.item_cd item_cd,max( pip.item_net_price) item_net_price 
  FROM purchase_item_prices pip
  join (
    SELECT item_cd, 
           MAX(price_ts ) max_price_ts
      FROM purchase_item_prices
	  WHERE tenant_no = @TENANT_NO AND company_no = @COMPANY_NO AND department_no = @DEPARTMENT_NO 
	    AND  cast (price_ts as DATE) &lt;= cast (@TODATE as DATE)
	  GROUP BY item_cd
	 ) tt
	 on tt.item_cd = pip.item_cd AND tt.max_price_ts = pip.price_ts
   WHERE tenant_no = @TENANT_NO AND company_no = @COMPANY_NO AND department_no = @DEPARTMENT_NO 
   GROUP BY pip.item_cd
		</sql>
	</report-dataset>
	
	<report-dataset name="item_suppliers">
		<sql>
			SELECT i.item_cd, c.contact_nm FROM item_suppliers i join contacts c on i.contact_no = c.contact_no where i.tenant_no=@TENANT_NO AND i.company_no = @COMPANY_NO AND i.department_no = @DEPARTMENT_NO
			order by i.item_cd ASC, c.contact_nm ASC
		</sql>
	</report-dataset>
	
</report>
