<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Kassenbuch.xsl">
	<title>Kassenbericht</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="revenue">
	</report-result>
	<report-result report-dataset="notes">
	</report-result>
	<report-result report-dataset="open">
	</report-result>
	<report-result report-dataset="open30">
	</report-result>
	<report-result report-dataset="open_d">
	</report-result>
	<report-result report-dataset="pos_sessions">
	</report-result>
<!-- 	<report-result report-dataset="pos_payments">
	</report-result> -->
	<report-result report-dataset="cash_start">
	</report-result>
	<report-result report-dataset="cash_end">
	</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="open">
		<sql>
			select sum(case when iv.sales_inv_type=2 then -iv.total_gross_price else iv.total_gross_price end) r_total,
			sum(case when iv.sales_inv_type=2 then -1 else 1 end) c_total
			from sales_invs iv
			where iv.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD and year(cast (sales_inv_ts as DATE)) = year(cast (@DATE as DATE))) 
			and booking_state is null
		</sql>
	</report-dataset>
	<report-dataset name="open30">
		<sql>
			select sum(case when iv.sales_inv_type=2 then -iv.total_gross_price else iv.total_gross_price end) r_total,
			sum(case when iv.sales_inv_type=2 then -1 else 1 end) c_total
			from sales_invs iv
			where iv.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD and year(cast (sales_inv_ts as DATE)) = year(cast (@DATE as DATE)))
			
			and cast({ fn timestampadd(sql_tsi_day, -30, @DATE) } as date) >  cast (sales_inv_ts as DATE)
			 
			and booking_state is null
		</sql>
	</report-dataset>
	<report-dataset name="open_d">
		<sql>
			select sum(case when iv.sales_inv_type=2 then -iv.total_gross_price else iv.total_gross_price end) r_total,
			sum(case when iv.sales_inv_type=2 then -1 else 1 end) c_total
			from sales_invs iv
			where iv.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD and cast (sales_inv_ts as DATE) = cast (@DATE as DATE))
			and booking_state is null
		</sql>
	</report-dataset>
	<report-dataset name="revenue">
		<sql>
			select
			sum(case when h.sales_inv_type=1 then p.position_gross_price else 0 end) r_revenue,
			sum(case when h.sales_inv_type=1 then case when coalesce(p.stockable, 0)=0 then p.position_gross_price else 0 end else 0 end) r_revenue_services,
			sum(case when h.sales_inv_type=1 then case when coalesce(p.stockable, 0)=1 then p.position_gross_price else 0 end else 0 end) r_revenue_products,
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else 0 end) r_canceled,
			sum(case when h.sales_inv_type=2 then case when coalesce(p.stockable, 0)=0 then -p.position_gross_price else 0 end else 0 end) r_canceled_services,
			sum(case when h.sales_inv_type=2 then case when coalesce(p.stockable, 0)=1 then -p.position_gross_price else 0 end else 0 end) r_canceled_products,
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end) r_total,
			sum(case when coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end) r_total_services,
			sum(case when coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end) r_total_products
			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
			where h.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD and cast (sales_inv_ts as DATE) = cast (@DATE as DATE))
		</sql>
	</report-dataset>
	<report-dataset name="notes">
		<sql>
			select
			sum(case when h.sales_inv_type=1 then 1 else 0 end) r_revenue,
			sum(case when h.sales_inv_type=2 then case when coalesce(h.canceled,0) = 0 then 1 else 0 end else 0 end) r_canceled
			from sales_invs h
			where h.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD and cast (sales_inv_ts as DATE) = cast (@DATE as DATE))
		</sql>
	</report-dataset>
	<report-dataset name="pos_payments">
		<sql>
			select
			pp.pos_session_id,
			pp.pos_payment_id,
			pt.report_nm,
			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, '')
			|| ' '
			|| siv.customer_contact_nm
			else
			case when
			not pp.purchase_inv_id is null
			then
			'EK-Beleg '
			|| piv.purchase_inv_cd
			||'/'
			|| coalesce(piv.supplier_first_nm, '')
			|| ' '
			|| piv.supplier_contact_nm
			else
			''
			end
			end
			ref_txt,
			pp.payment_ref,
			pp.employee_nm


			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.pos_session_id in (select pos_session_id from pos_sessions where tenant_no=@TENANT_NO and pos_cd=@POS_CD and cast (session_start_ts as DATE) = cast (@DATE as DATE))
			order by 1,2
		</sql>
	</report-dataset>
	<report-dataset name="pos_sessions">
		<sql>
			select
			sum(cash_in) t_cash_in,
			sum(cash_out) t_cash_out,
			sum(cash_sales_in-cash_sales_return_out) t_cash_sales_in,
			sum(cash_purchase_out-cash_purchase_return_in) t_cash_purchase_out,
			sum(cash_debt_out) t_cash_debt_out,
			sum(cash_debt_in) t_cash_debt_in,
			sum(ec_sales_in) t_ec_sales_in,
			sum(cc_sales_in) t_cc_sales_in,
			sum(transfer_sales_in) t_transfer_sales_in,
			sum(transfer_purchase_out) t_transfer_purchase_out,
			sum(creditnote_sales_in) t_creditnote_sales_in,
			sum(creditnote_sales_out) t_creditnote_sales_out,
			sum(discount_sales_out) t_discount_sales_out,
			sum(voucher_sales_in) t_voucher_sales_in,
			sum(voucher_out) t_voucher_out
			from pos_sessions 
			
			where tenant_no=@TENANT_NO
			and pos_cd=@POS_CD
			and cast (session_start_ts as DATE) = cast (@DATE as DATE)
			
		</sql>
	</report-dataset>
	<report-dataset name="cash_start">
		<sql>
			SELECT ss.session_start_ts, ss.cash_start from pos_sessions ss where ss.pos_session_id=(select min(ms.pos_session_id) from pos_sessions ms where ms.tenant_no=@TENANT_NO and ms.pos_cd=@POS_CD and cast
			(ms.session_start_ts as DATE) = cast (@DATE as DATE))
		</sql>
	</report-dataset>
	<report-dataset name="cash_end">
		<sql>
			SELECT ss.pos_session_id, case when ss.session_end_ts is null then 'Vorläufiger ' else '' end r_draft, ss.session_end_ts, ss.cash_end from pos_sessions ss where ss.tenant_no=@TENANT_NO and ss.pos_cd=@POS_CD and ss.pos_session_id=(select max(ms.pos_session_id) from pos_sessions ms where ms.tenant_no=@TENANT_NO and ms.pos_cd=@POS_CD and cast
			(ms.session_start_ts as DATE) = cast (@DATE as DATE))
		</sql>
	</report-dataset>

</report>
