<?xml version="1.0"?>

<report name="jedox-export" csv-renderer="JedoxExport_MS.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
              ,cus_visits.amountVisits amountvisits
	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 planet_customer_roles cr on cr.tenant_no = h.tenant_no  and h.pos_cd = cr.pos_cd and h.company_no=cr.company_no AND h.CUSTOMER_NO = cr.CONTACT_NO
	left join planet_contacts c on c.tenant_no = h.tenant_no and h.pos_cd = c.pos_cd 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
left join (		select
			 si.tenant_no ,
             si.pos_cd,	
             si.customer_no, 
             COUNT  (DISTINCT (si.sales_inv_id * 10+1 )) amountVisits
		FROM
		    sales_inv_positions p2
			join sales_invs si on si.tenant_no = p2.tenant_no and si.pos_cd = p2.pos_cd and si.sales_inv_id = p2.sales_inv_id
			join sales_dln_pos_work_records wr2 on si.tenant_no = wr2.tenant_no and si.pos_cd = wr2.pos_cd and si.sales_dln_id = wr2.sales_dln_id and p2.sales_inv_position_id = wr2.sales_dln_position_id 
			where  si.tenant_no=@TENANT_NO  and si.company_no=@COMPANY_NO and coalesce(si.pos_cd, '-poscd--')= coalesce(@POS_CD,coalesce(si.pos_cd,'-poscd--'))  
			GROUP BY si.tenant_no , si.pos_cd, si.customer_no ) cus_visits
			on cus_visits.tenant_no = h.tenant_no AND cus_visits.pos_cd = h.pos_cd AND cus_visits.customer_no = h.customer_no
	 join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
	        FROM user_group_businessunit ugb 
	        join businessunits b 
	          on ugb.tenant_no = b.tenant_no 
	         AND ugb.company_no = b.company_no
	         AND ugb.department_no = b.department_no
	         AND ugb.businessunit_no = b.businessunit_no
	         
	        WHERE b.tenant_no=@TENANT_NO and b.company_no=@COMPANY_NO and b.department_no=@DEPARTMENT_NO 
	             AND ugb.user_group_cd IN ( SELECT user_group_cd 
	                                          FROM user_group_members
	                                           WHERE tenant_no =@TENANT_NO
	                                           AND login_nm = @USER_LOGIN_NM
	                                            )
	          	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint                                               
	                                           
	        ) buFilter  
	on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
	where   h.tenant_no=@TENANT_NO and h.company_no=@COMPANY_NO and h.department_no=@DEPARTMENT_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 h.company_no=@COMPANY_NO and h.department_no=@DEPARTMENT_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 (pos_cd+';' + cast(sales_inv_id as VARCHAR(10))) IN 
      (SELECT DISTINCT (h.pos_cd+';' + cast(h.sales_inv_id as VARCHAR(10))) 
       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 h.company_no=@COMPANY_NO and h.department_no=@DEPARTMENT_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 sales_inv_id is not null 
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 h.company_no=@COMPANY_NO and h.department_no=@DEPARTMENT_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>
