<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="InventoryProcess_MS.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-result report-dataset="inventory-item-list">
	</report-result>
	
	<report-result report-dataset="businessunits">
	</report-result>

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

	<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 as item_cd, i.item_nm as item_nm , resultTbl.stock_no as stock_no , s.stock_nm as stock_nm, resultTbl.amount as amountPrev ,  resultTbl2.amount as amountNew, (coalesce(resultTbl2.amount,0) - coalesce(resultTbl.amount,0)) as amountDif
		  FROM 
			( 
			
			SELECT unionTbl.tenant_no as tenant_no,  unionTbl.company_no as company_no, unionTbl.department_no as department_no ,unionTbl.stock_no as stock_no,unionTbl.item_cd as item_cd, SUM( CASE WHEN unionTbl.direction_type = 1 THEN  unionTbl.amount ELSE  -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.pos_cd = tp.pos_cd AND  t.stock_dln_id = tp.stock_dln_id 
                   WHERE coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO AND t.pos_cd = @POS_CD  AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no =  @STOCK_NO  AND t.stock_dln_ts  &lt;  @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)= 2 THEN 1 ELSE 2 END as direction_type   
			   FROM sales_dln_positions tp  
			   JOIN sales_dlns t 
			     ON t.tenant_no = tp.tenant_no AND t.pos_cd = tp.pos_cd AND  t.sales_dln_id = tp.sales_dln_id 
               WHERE  coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO AND t.pos_cd = @POS_CD AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no =  @STOCK_NO  AND  t.sales_dln_ts  &lt; @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.pos_cd = tp.pos_cd 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.pos_cd = @POS_CD  AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no =  @STOCK_NO  AND coalesce(t.stock_dln_ts,t.purchase_dln_ts)  &lt;  @DATE  
			       
			     
			     
			     
			      ) unionTbl 
     			GROUP BY unionTbl.tenant_no,unionTbl.company_no , unionTbl.department_no ,unionTbl.stock_no,unionTbl.item_cd 
     		
     		
     		) resultTbl 
        	JOIN 
     		( 
     		
     		SELECT unionTbl.tenant_no as tenant_no, unionTbl.company_no as company_no, unionTbl.department_no as department_no ,unionTbl.stock_no as stock_no,unionTbl.item_cd as item_cd, SUM( CASE WHEN unionTbl.direction_type = 1 THEN  unionTbl.amount ELSE -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.pos_cd = tp.pos_cd AND  t.stock_dln_id = tp.stock_dln_id 
                   WHERE  coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO AND t.pos_cd = @POS_CD  AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no =  @STOCK_NO  AND  t.stock_dln_ts  &lt;= @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)= 2 THEN 1 ELSE 2 END as direction_type  
			   FROM sales_dln_positions tp  
			   JOIN sales_dlns t 
			     ON t.tenant_no = tp.tenant_no AND t.pos_cd = tp.pos_cd AND  t.sales_dln_id = tp.sales_dln_id 
               WHERE  coalesce(tp.stockable,0) = 1 AND t.tenant_no = @TENANT_NO AND t.pos_cd = @POS_CD AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no =  @STOCK_NO  AND t.sales_dln_ts  &lt;= @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.pos_cd = tp.pos_cd 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.pos_cd = @POS_CD  AND t.company_no =@COMPANY_NO AND t.department_no = @DEPARTMENT_NO AND t.stock_no =  @STOCK_NO  AND  coalesce(t.stock_dln_ts,t.purchase_dln_ts)  &lt;=  @DATE 
			        
			     
			     
			     
			      ) unionTbl 
     			 GROUP BY unionTbl.tenant_no, unionTbl.company_no , unionTbl.department_no ,unionTbl.stock_no,unionTbl.item_cd 
     		
     		
     		
     		
     		) resultTbl2 
     		ON resultTbl.item_cd = resultTbl2.item_cd AND resultTbl.stock_no = resultTbl2.stock_no
     	   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 
			WHERE lower(coalesce(i.item_cd,'') + ' ' + coalesce(i.item_nm, '') + ' ' + coalesce(i.item_ean,'') + ' ' + coalesce(i.item_ean2,'')) like @SEARCH_STRING 
			  
			 AND resultTbl.item_cd IN ( SELECT sdp.item_cd FROM stock_dln_positions sdp WHERE coalesce(sdp.stockable,0) = 1 AND sdp.tenant_no = @TENANT_NO AND sdp.pos_cd = @POS_CD AND sdp.stock_dln_id = @STOCK_DLN_ID )
	
     	
			
		</sql>
		
	</report-dataset>

<!-- 

 -->

	
</report>
