<?xml version="1.0"?>

<report name="jedox-export" csv-renderer="JedoxExport.xsl">
	<title>JedoxExportCsv</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="Datum:">
		<preset>@DATE</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="TODATE" type="date" label="Bis Datum:">
		<preset>@TODATE</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="order_positions">
	</report-result>
	<report-result report-dataset="customer_visits">
	</report-result>
	<report-result report-dataset="sales_inv_cash_discount">
	</report-result>
		<report-result report-dataset="sales_inv_position_cnt">
	</report-result>
	<report-result report-dataset="item_groups">
	</report-result>
	<report-result report-dataset="items">
	</report-result>

	<report-dataset name="order_positions">
		<sql>
	
	select
		p.tenant_no ,
        p.pos_cd,	
        h.sales_inv_id,
        h.sales_inv_ts,
        h.BU_NM_2,
        p.sales_inv_position_id,
       (case when h.sales_inv_type=2 then -p.position_gross_price  else p.position_gross_price end)  * wr.operating_employee_factor position_gross_price,
        h.customer_no, h.customer_salutation, h.customer_first_nm, h.customer_contact_nm, h.customer_postal_cd,h.customer_group_no,h.customer_group_nm
        ,h.customer_contract_no, h.customer_contract_nm,h.cc_condition_sales_pricelist_id,h.cc_condition_cd ,h.v_condition_cd,
        cr.BIRTHDAY, c.EMAIL,
         e.employee_nm operating_employee_nm, wr.operating_employee_no,
        p.tax_rate_percent,
        p.item_cd,
        p.item_nm,
        p.amount,
        coalesce( p.stockable,0) stockable,
        case when coalesce (p.inv_rebate_rate_percent,0) &lt;&gt; 0 then p.inv_rebate_rate_percent ELSE coalesce(p.pos_rebate_rate_percent,0) END rebate_rate_percent
        
	from sales_inv_positions p 
	left join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
	left join customer_roles cr on cr.tenant_no = h.tenant_no and  h.company_no=cr.company_no AND h.CUSTOMER_NO = cr.CONTACT_NO
	left join contacts c on c.tenant_no = h.tenant_no and h.CUSTOMER_NO = c.CONTACT_NO
	join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and p.sales_inv_position_id = wr.sales_dln_position_id 
	left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and wr.operating_employee_no= e.employee_no
	where   h.tenant_no=@TENANT_NO and coalesce(h.pos_cd, '-poscd--')= coalesce(@POS_CD,coalesce(h.pos_cd,'-poscd--'))  AND cast (sales_inv_ts as DATE) &gt;= cast(@DATE  as DATE) AND  cast (sales_inv_ts as DATE)  &lt;= cast(@TODATE as DATE) 
    ORDER BY p.tenant_no , p.pos_cd,	h.sales_inv_id
	
	
	
	
	
			
		</sql>
	</report-dataset>


	<report-dataset name="customer_visits">
		<sql>
		
		select
			 h.tenant_no ,
             h.pos_cd,	
             h.customer_no, 
             COUNT  (DISTINCT (h.sales_inv_id * 10+1 )) amountVisits
	
		FROM
		    sales_inv_positions p 
			join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
			join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and p.sales_inv_position_id = wr.sales_dln_position_id 
			
		where   h.tenant_no=@TENANT_NO and coalesce(h.pos_cd, '-poscd--')= coalesce(@POS_CD,coalesce(h.pos_cd,'-poscd--'))  
		GROUP BY h.tenant_no , h.pos_cd, h.customer_no
		
		</sql>
		</report-dataset>



<report-dataset name="sales_inv_cash_discount">
		<sql>
		


SELECT	tenant_no ,
        pos_cd,	
        sales_inv_id ,
        sum( case when payment_direction = 1 THEN -payment ELSE payment END) cashDiscount
FROM POS_PAYMENTS 
where PAYMENT_TYPE = 6  
   AND sales_inv_id IN 
      (SELECT DISTINCT h.sales_inv_id 
       FROM sales_inv_positions p 
	   join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
	   join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and p.sales_inv_position_id = wr.sales_dln_position_id 
	   where   h.tenant_no=@TENANT_NO and  coalesce(h.pos_cd, '-poscd--')= coalesce(@POS_CD,coalesce(h.pos_cd,'-poscd--'))  AND cast (sales_inv_ts as DATE) &gt;= cast(@DATE  as DATE) AND  cast (sales_inv_ts as DATE)  &lt;= cast(@TODATE as DATE))
   AND  tenant_no=@TENANT_NO and pos_cd=@POS_CD 
GROUP BY tenant_no ,pos_cd,sales_inv_id 


</sql>
		</report-dataset>



<report-dataset name="sales_inv_position_cnt">
		<sql>
		
select
	h.tenant_no ,
    h.pos_cd,	
    h.sales_inv_id, 
    COUNT  (DISTINCT (char(p.sales_inv_position_id )) || ';' || char(wr.SALES_DLN_POS_REC_ID )) amountPositions
	
FROM
	 sales_inv_positions p 
 join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
 join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and p.sales_inv_position_id = wr.sales_dln_position_id 
 where   h.tenant_no=@TENANT_NO and coalesce(h.pos_cd, '-poscd--')= coalesce(@POS_CD,coalesce(h.pos_cd,'-poscd--'))  AND cast (sales_inv_ts as DATE) &gt;= cast(@DATE  as DATE) AND  cast (sales_inv_ts as DATE)  &lt;= cast(@TODATE as DATE)
GROUP BY h.tenant_no , h.pos_cd, h.sales_inv_id

</sql>
		</report-dataset>

	<report-dataset name="item_groups">
		<sql>
			select item_group_cd ID, item_group_nm, coalesce(parent_item_group_cd,'') parent_item_group_cd

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


	<report-dataset name="items">
		<sql>
			select item_cd, item_group_cd 

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



</report>
