<?xml version="1.0"?>

<report name="inventory" pdf-renderer="Inventur.xsl">
	<title>Inventur</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="DEPARTMENT" type="integer" hidden="true">
		<preset>@DEPARTMENT_NO</preset>
	</report-parameter>
	<report-parameter name="POSCD" type="string" hidden="true">
		<preset>@POS_CD</preset>
	</report-parameter>
	<report-parameter name="DATE" type="date" label="Bis 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-parameter name="SEARCHSTRING" type="string">
		<preset>@SEARCH_STRING</preset>
		<input type="text" />
	</report-parameter>
    <report-parameter name="STOCK" type="integer">
	<preset>@STOCK_NO</preset>
		<label>Lager:</label>
		<input type="selectlist">
			<source type="dataset" name="stocks">
			</source>
		</input>
	</report-parameter>
	<report-parameter name="CONTACT" type="integer">
	<preset>@CONTACT_NO</preset>
		<label>Lieferant:</label>
		<input type="selectlist">
			<source type="dataset" name="supplier">
			</source>
		</input>
	</report-parameter>
	<report-parameter name="ITEM_PURCHASE_GROUPS" type="integer">
	<preset>@ITEM_PURCHASE_GROUP_CD</preset>
		<label>Einkausgruppe:</label>
		<input type="selectlist">
			<source type="dataset" name="item_purchase_groups">
			</source>
		</input>
	</report-parameter>
	<report-parameter name="ITEM_GROUPS" type="integer">
	<preset>@ITEM_GROUP_CD</preset>
		<label>Artikelgruppe:</label>
		<input type="selectlist">
			<source type="dataset" name="item_groups">
			</source>
		</input>
	</report-parameter>


	<report-result report-dataset="inventory-item-list">
	</report-result>
	
	<report-result report-dataset="businessunits">
	</report-result>

<report-result report-dataset="stocks">
	</report-result>
	
	<report-result report-dataset="supplier">
	</report-result>
	<report-result report-dataset="item_purchase_groups">
	</report-result>
	<report-result report-dataset="item_groups">
	</report-result>
	<report-result report-dataset="purchase_item_prices">
	</report-result>
	<report-result report-dataset="purchase_item_prices_last_ts">
	</report-result>
	<report-result report-dataset="sales_item_prices">
	</report-result>
	<report-result report-dataset="sales_item_prices_latest">
	</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="stocks">
		<sql>
			select stock_no ID, stock_nm TXT from stocks where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO 
		</sql>
	</report-dataset>


	<report-dataset name="inventory-item-list">
		<sql>
		SELECT resultTbl.item_cd, i.item_nm item_nm, resultTbl.stock_no , s.stock_nm, resultTbl.last_dln_ts , resultTbl.amount, i.item_ean, sSup.contact_no, i.item_purchase_group_cd , coalesce(i.sellable,0) sellable FROM

			( SELECT unionTbl.tenant_no, unionTbl.company_no , unionTbl.department_no ,unionTbl.stock_no,unionTbl.item_cd, MAX(unionTbl.dln_ts) as last_dln_ts, SUM( CASE WHEN unionTbl.direction_type = 1 THEN  unionTbl.amount ELSE ( -1 * unionTbl.amount) END ) AS amount  FROM 

			( 

			  SELECT t.tenant_no as tenant_no ,  t.company_no as company_no, t.department_no as department_no , s.stock_no as stock_no, TIMESTAMP('19881225000000') as dln_ts, t.item_cd as item_cd, 0 as amount, 1 as direction_type 
			     FROM items t  JOIN stocks s ON  t.tenant_no = s.tenant_no AND t.company_no = s.company_no AND t.department_no = s.department_no 
			  WHERE coalesce(t.stockable,0) = 1 AND  t.tenant_no = @TENANT_NO AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND s.stock_no = @STOCK_NO   

			   UNION	ALL	 

			  SELECT  t.tenant_no as tenant_no ,  t.company_no as company_no , t.department_no as department_no, t.stock_no as stock_no,t.stock_dln_ts as dln_ts, tp.item_cd as item_cd, tp.amount as amount , tp.direction_type as direction_type 
			  FROM stock_dln_positions tp   
			  JOIN stock_dlns t  
			  ON t.tenant_no = tp.tenant_no  AND  t.stock_dln_id = tp.stock_dln_id 
			  WHERE coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no = @STOCK_NO AND cast(t.stock_dln_ts as DATE) &lt;=  cast( @DATE as DATE)

			  UNION ALL

			  SELECT  t.tenant_no as tenant_no , t.company_no as company_no , t.department_no as department_no, t.stock_no as stock_no,t.sales_dln_ts as dln_ts,tp.item_cd as item_cd, tp.amount as amount , CASE WHEN coalesce(t.sales_dln_type,0)&lt; 2 THEN 2 ELSE 1 END as direction_type
			  FROM sales_dln_positions tp   
			  JOIN sales_dlns t  
			  ON t.tenant_no = tp.tenant_no AND   t.sales_dln_id = tp.sales_dln_id 
			  WHERE coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO AND  t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no = @STOCK_NO   AND cast ( t.sales_dln_ts as DATE) &lt;=  cast (@DATE as DATE) 

			  UNION  ALL

			  SELECT t.tenant_no as tenant_no , t.company_no as company_no , t.department_no as department_no,  t.stock_no as stock_no,coalesce(t.stock_dln_ts,t.purchase_dln_ts) as dln_ts, tp.item_cd as item_cd, tp.amount as amount , 1 as direction_type 
			  FROM purchase_dln_positions tp   
			  JOIN purchase_dlns t  
			  ON t.tenant_no = tp.tenant_no AND   t.purchase_dln_id = tp.purchase_dln_id 
			  WHERE coalesce(tp.item_cd ,'') != ''  
			  AND coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO  AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no = @STOCK_NO   AND cast ( coalesce(t.stock_dln_ts,t.purchase_dln_ts) as DATE) &lt;=  cast (@DATE as DATE) 

			) unionTbl 

			 GROUP BY unionTbl.tenant_no, unionTbl.company_no , unionTbl.department_no ,unionTbl.stock_no,unionTbl.item_cd ) resultTbl 

			 left join items i on resultTbl.tenant_no = i.tenant_no AND resultTbl.company_no = i.company_no AND resultTbl.department_no = i.department_no AND resultTbl.item_cd = i.item_cd 
			 left join stocks s on resultTbl.tenant_no = s.tenant_no AND resultTbl.company_no = s.company_no AND resultTbl.department_no = s.department_no AND resultTbl.stock_no = s.stock_no 
			 left join item_suppliers sSup on resultTbl.tenant_no = sSup.tenant_no AND resultTbl.company_no = sSup.company_no AND resultTbl.department_no = sSup.department_no AND resultTbl.stock_no = sSup.stock_no AND resultTbl.item_cd = sSup.item_cd
			
			WHERE lower(coalesce(i.item_cd,'') || ' ' || coalesce(i.item_nm, '') || ' ' || coalesce(i.item_ean,'') || ' ' || coalesce(i.item_ean2,'')) like @SEARCH_STRING
			AND  (coalesce(sSup.supplier_no ,-1)=@CONTACT_NO OR @CONTACT_NO IS NULL)
			AND  (coalesce(i.item_purchase_group_cd,'')=@ITEM_PURCHASE_GROUP_CD OR @ITEM_PURCHASE_GROUP_CD IS NULL)
			AND  (coalesce(i.item_group_cd,'')=@ITEM_GROUP_CD OR @ITEM_GROUP_CD IS NULL)
			
			ORDER BY i.item_nm

		</sql>
		
	</report-dataset>
	
	<report-dataset name="supplier">
		<sql>
			select contact_no ID, first_nm, contact_nm

			from contacts 
			where tenant_no=@TENANT_NO
		</sql>
	</report-dataset>
	
	<report-dataset name="item_purchase_groups">
		<sql>
			select item_purchase_group_cd ID, item_purchase_group_nm

			from item_purchase_groups 
			where tenant_no=@TENANT_NO and company_no=@COMPANY_NO 
		</sql>
	</report-dataset>
	
	<report-dataset name="item_groups">
		<sql>
			select item_group_cd ID, item_group_nm, parent_item_group_cd

			from item_groups 
			where tenant_no=@TENANT_NO and company_no=@COMPANY_NO 
		</sql>
	</report-dataset>

	<report-dataset name="purchase_item_prices">
		<sql>
			select item_cd ID, item_net_price, price_ts

			from purchase_item_prices 
			where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no = @DEPARTMENT_NO
			ORDER BY item_cd ASC, price_ts DESC
		</sql>
	</report-dataset>
	
	<report-dataset name="purchase_item_prices_last_ts">
		<sql>
			select item_cd ID, 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
			GROUP BY item_cd
		</sql>
	</report-dataset>
	
	<report-dataset name="sales_item_prices">
		<sql>
			select item_cd ID, item_gross_price, sales_pricelist_id, sales_pricelist_instance_id

			from sales_item_prices 
			where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no = @DEPARTMENT_NO
			ORDER BY item_cd ASC, sales_pricelist_id DESC, sales_pricelist_instance_id DESC
		</sql>
	</report-dataset>
	
	<report-dataset name="sales_item_prices_latest">
		<sql>
			select item_cd ID, max(sales_pricelist_id) max_pricelist_id, max(sales_pricelist_instance_id) max_pricelist_instance_id
			from sales_item_prices
			where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and department_no = @DEPARTMENT_NO
			GROUP BY item_cd, sales_pricelist_id
	
		</sql>
	</report-dataset>
	
</report>
