<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="KomplettePreisliste.xsl">
	<title>Preisliste</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="DATE" 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-result report-dataset="sales_pricelists">
	</report-result>
	<report-result report-dataset="item_groups">
	</report-result>
	<report-result report-dataset="sales_item_prices">
	</report-result>
	<report-result report-dataset="sales_pricelist_instance">
	</report-result>
	<report-result report-dataset="purchase_item_prices">
	</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="sales_pricelists">
		<sql>
			select * from sales_pricelists where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and sales_pricelist_id=@SALES_PRICELIST_ID
		</sql>
	</report-dataset>
	
	<report-dataset name="sales_pricelist_instance">
		<sql>
			select * from sales_pricelist_instances where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and sales_pricelist_id=@SALES_PRICELIST_ID and sales_pricelist_instance_id=@PRICELIST_INSTANCE_ID 
		</sql>
	</report-dataset>
	
	<report-dataset name="item_groups">
		<sql>
			select * from item_groups where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and item_group_cd=coalesce(@ITEM_GROUP_CD,'-') 
		</sql>
	</report-dataset>
	
	<report-dataset name="sales_item_prices_old">
		<sql>
			select i.item_cd,
			sip.item_net_price,
			sip.item_gross_price,
			i.item_nm,
			t.tax_nm,
			prof.item_profession_nm
			from items i left join sales_item_prices sip on i.item_cd = sip.item_cd and sip.sales_pricelist_id=@SALES_PRICELIST_ID and sip.sales_pricelist_instance_id=@PRICELIST_INSTANCE_ID 
			inner join taxes t on i.tax_cd = t.tax_cd
			left join sales_item_professions prof on sip.item_profession_no = prof.item_profession_no
			where i.tenant_no=@TENANT_NO and i.company_no=@COMPANY_NO and i.item_group_cd=coalesce(@ITEM_GROUP_CD,i.item_group_cd) and coalesce(i.sellable,0) = 1
			order by i.item_cd, sip.item_profession_no
		</sql>
	</report-dataset>
	


	<report-dataset name="sales_item_prices">
		<sql>
  select i.item_cd,
			sip.item_net_price,
			sip.item_gross_price,
			i.item_nm,
			t.tax_nm,
			prof.item_profession_nm,
			coalesce(sip.special_price,0) special_price
			from items i left 
			join ( SELECT * FROM sales_item_prices WHERE item_cd || ';;'|| cast(sales_pricelist_instance_id as char(5)) in 
			   (
                 SELECT tt.item_cd || ';;' || cast(tt.maxspii as char(5)) 
			
				  FROM 
				    (SELECT item_cd ,max(sales_pricelist_instance_id) maxspii 
	                   FROM sales_item_prices 
		              where sales_pricelist_id = @SALES_PRICELIST_ID AND sales_pricelist_instance_id &lt;= @PRICELIST_INSTANCE_ID  AND tenant_no = @TENANT_NO AND company_no = @COMPANY_NO AND department_no = @DEPARTMENT_NO  
		           GRoup BY item_cd
	                ) tt
				)
				AND sales_pricelist_id = @SALES_PRICELIST_ID AND tenant_no = @TENANT_NO AND company_no = @COMPANY_NO AND department_no = @DEPARTMENT_NO   
		      ) sip on i.tenant_no=sip.tenant_no and i.company_no=sip.company_no  and i.department_no=sip.department_no AND i.item_cd = sip.item_cd and sip.sales_pricelist_id=@SALES_PRICELIST_ID
			inner join taxes t on i.tenant_no=t.tenant_no and i.company_no=t.company_no  and i.tax_cd = t.tax_cd
			left join sales_item_professions prof on prof.tenant_no=sip.tenant_no and prof.company_no=sip.company_no and prof.department_no=sip.department_no and sip.item_profession_no = prof.item_profession_no
			where i.tenant_no=@TENANT_NO and i.company_no=@COMPANY_NO and i.department_no=@DEPARTMENT_NO and i.item_group_cd=coalesce(@ITEM_GROUP_CD,i.item_group_cd)  and coalesce(i.sellable,0) = 1
		 and coalesce(i.unselectable, 0) = coalesce( @UNSELECTABLE_AS_INT ,0)
			order by i.item_cd, sip.item_profession_no
		</sql>
	</report-dataset>


<report-dataset name="purchase_item_prices">
		<sql>
  SELECT pip.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 price_ts &lt;= coalesce( (select valid_to from sales_pricelist_instances where tenant_no=@TENANT_NO and company_no=@COMPANY_NO AND department_no = @DEPARTMENT_NO and sales_pricelist_id=  @SALES_PRICELIST_ID and sales_pricelist_instance_id= @PRICELIST_INSTANCE_ID),price_ts)
	  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>

