<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="SalesVoucherPReductionStatistics_MS.xsl">
	<title>Bonjournal</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="POSSESSIONID" type="integer"
		hidden="true">
		<preset>@POS_SESSION_ID</preset>
	</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="fast_input">
	</report-result>

	<report-result report-dataset="vouchers_out">
	</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="fast_input">
		<sql>
			select * from fast_input  where tenant_no=@TENANT_NO  AND fast_input_cd= @FAST_INPUT_CD
		</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="vouchers_out">
		<sql>
			SELECT
				pp.payment_ts payment_ts,
			pp.employee_nm cashier_nm,
			pp.payment_ref voucher_cd,
			pp.payment payment,
			coalesce(si.customer_first_nm,'') + ' ' + si.customer_contact_nm customer_nm,
			si.sales_inv_cd sales_inv_cd
			from pos_payments pp
			join sales_invs si ON pp.tenant_no = si.
			tenant_no AND pp.pos_cd = si.pos_cd AND coalesce
			(pp.sales_inv_id,-1000) = si.sales_inv_id
			
			WHERE pp.tenant_no = @TENANT_NO AND pp.pos_cd = @POS_CD AND
			(pp.payment_type = 2 OR pp.payment_type = 7 OR pp.payment_type = 11)
			AND pp.payment_direction = 2 and cast (pp.payment_ts as DATE) >= cast
			(@DATE as DATE) and cast(pp.payment_ts as DATE) &lt;= cast(@TODATE as DATE)
			
			AND pp.sales_inv_id IN (SELECT sales_inv_id FROM  pos_payments pp WHERE pp.tenant_no = @TENANT_NO AND pp.pos_cd = @POS_CD AND
			pp.payment_type = 6  and cast (pp.payment_ts as DATE) >= cast
			(@DATE as DATE) and cast(pp.payment_ts as DATE) &lt;= cast(@TODATE as DATE) AND pp.reporting_cd = @FAST_INPUT_CD )
			
			AND pp.cancel_for_pos_payment_id IS NULL
			AND cast (pp.pos_session_id as char(100)) + '.' + cast (pp.drawer_no as char(10))+ '.' + cast (pp.pos_payment_id as char(10)) NOT IN 
			(SELECT  cast (cancel_for_pos_session_id as char(100)) + '.' + cast (cancel_for_drawer_no as char(10))+ '.' + cast (cancel_for_pos_payment_id as char(10)) FROM
			 pos_payments 			WHERE tenant_no = @TENANT_NO AND pos_cd = @POS_CD AND cancel_for_pos_payment_id is not null)
		</sql>
	</report-dataset>


	

</report>
