<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Transaktionsjournal.xsl">
	<title>Transaktionsjournal</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="drawers">
	</report-result>

	<report-result report-dataset="pos_payments">
	</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="drawers">
		<sql>
			select * from pos_drawers where tenant_no=@TENANT_NO and pos_cd=@POS_CD and drawer_no = @DRAWER_NO 
		</sql>
	</report-dataset>


	
	<report-dataset name="pos_payments">
		<sql>
			select
			pp.pos_session_id,
			pp.pos_payment_id,
			pt.report_nm,
			pp.payment_booking_ts,
			pp.payment_ts,
			pp.payment_type,
			pp.payment_direction,
			pp.payment,
            pp.payment_change,
            (coalesce(pp.payment,0) - coalesce( pp.payment_change,0)) payment_diff,
			case when not pp.sales_inv_id is null
			then
			case when siv.sales_inv_type=2
			then
			'Storno-'
			else
			''
			end
			|| 'VK-Beleg '
			|| cast (siv.sales_inv_no as char(15))
			||'/'
			|| coalesce(siv.customer_first_nm, '')
			|| ' '
			|| coalesce(siv.customer_contact_nm,'')
			else
			case when
			not pp.purchase_inv_id is null
			then
			'EK-Beleg '
			||  coalesce(piv.purchase_inv_cd,'')
			||'/'
			|| trim(cast (pp.purchase_inv_id as char(15)))	
			||'/'
			|| coalesce(piv.supplier_first_nm, '')
			|| ' '
			|| coalesce(piv.supplier_contact_nm,'')
			else
			''
			end
			end
			ref_txt,
			pp.payment_ref,
			pp.cancel_reason,
			pp.employee_nm,
			case when pp.cancel_for_pos_payment_id IS NULL THEN '' ELSE ' (Storno)' END storno_ref_txt,
			case when pp.cancel_for_pos_payment_id IS NULL THEN '' ELSE ' Storno: ' || pp.cancel_reason END storno_ref_reason_txt
			


			from pos_payments pp
			left join pos_payment_types pt on pt.tenant_no=pp.tenant_no and pt.payment_type=pp.payment_type and pt.payment_direction=pp.payment_direction and pt.sales_inv=(case when pp.sales_inv_id is null then 0
			else 1 end) and pt.purchase_inv=(case when pp.purchase_inv_id is null then 0 else 1 end)
			left join sales_invs siv on siv.tenant_no=pp.tenant_no and siv.pos_cd=pp.pos_cd and siv.sales_inv_id=pp.sales_inv_id
			left join purchase_invs piv on piv.tenant_no=pp.tenant_no and piv.pos_cd=pp.pos_cd and piv.purchase_inv_id=pp.purchase_inv_id
			  
			where pp.tenant_no=@TENANT_NO
			and pp.pos_cd=@POS_CD
			and pp.drawer_no = @DRAWER_NO
			and cast(pp.PAYMENT_BOOKING_TS AS DATE) >= cast( @DATE AS DATE) 
			and cast(pp.PAYMENT_BOOKING_TS AS DATE) &lt;= cast( @TODATE AS DATE) 
			order by 1,2
		</sql>
	</report-dataset>
	
	

</report>
