<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Sitzungsbericht.xsl">
	<title>Sitzungsbericht</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="drawers">
	</report-result>
<!-- 	<report-result report-dataset="pos_sessions">
	</report-result>
	<report-result report-dataset="pos_sessions_acct">
	</report-result>
<report-result report-dataset="simple_accounts">
	</report-result>
	
	<report-result report-dataset="cash_start">
	</report-result>
	<report-result report-dataset="cash_end">
	</report-result>-->
<report-transaction report-nm="pospay_session_statistics" report-transaction="de.timeglobe.pos.reporting.PosPaymentSessionStatistic">
	</report-transaction>
<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  
		</sql>
	</report-dataset>

	<report-dataset name="simple_accounts">
		<sql>
			select * from simple_accounts where tenant_no=@TENANT_NO and company_no=@COMPANY_NO AND department_no=@DEPARTMENT_NO
		</sql>
	</report-dataset>
	<report-dataset name="pos_sessions">
		<sql>
			select
			sum(cast(cash_in as decimal(16,2))) t_cash_in,
			sum(cast(cash_out as decimal(16,2))) t_cash_out,
			sum(cast(cash_sales_in as decimal(16,2))- cast(cash_sales_return_out as decimal(16,2))) t_cash_sales_in,
			sum(cast(cash_purchase_out as decimal(16,2)) - cast(cash_purchase_return_in as decimal(16,2))) t_cash_purchase_out,
			sum(cast(cash_debt_out as decimal(16,2))) t_cash_debt_out,
			sum(cast(cash_debt_in as decimal(16,2))) t_cash_debt_in,
			sum(cast(ec_sales_in as decimal(16,2))) t_ec_sales_in,
			sum(cast(cc_sales_in as decimal(16,2))) t_cc_sales_in,
			sum(cast(transfer_sales_in as decimal(16,2))) t_transfer_sales_in,
			sum(cast(transfer_purchase_out as decimal(16,2))) t_transfer_purchase_out,
			sum(cast(creditnote_sales_in as decimal(16,2))) t_creditnote_sales_in,
			sum(cast(creditnote_sales_out as decimal(16,2))) t_creditnote_sales_out,
			sum(cast(discount_sales_out as decimal(16,2))) t_discount_sales_out,
			sum(cast(voucher_sales_in as decimal(16,2))) t_voucher_sales_in,
			sum(cast(voucher_out as decimal(16,2))) t_voucher_out
			from pos_sessions
			where tenant_no=@TENANT_NO
			and pos_cd=@POS_CD
			and pos_session_id = @POS_SESSION_ID
			and drawer_no = @DRAWER_NO
		</sql>
	</report-dataset>
	
	
	<report-dataset name="pos_sessions_acct">
		<sql>
			select
			 coalesce(contra_acct_cd, '-') contra_acct_cd,
				  sum(case when payment_type = 1 THEN CASE WHEN sales_inv_id IS NULL THEN CASE WHEN purchase_inv_id IS NULL THEN CASE WHEN payment_direction = 1 THEN (coalesce(cast(payment as decimal(16,2)),0)-coalesce(cast(payment_change as decimal(16,2)),0)) ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_acct_cd_cash_in,
			  sum(case when payment_type = 1 THEN CASE WHEN sales_inv_id IS NULL THEN CASE WHEN purchase_inv_id IS NULL THEN CASE WHEN payment_direction = 2 THEN (coalesce(cast(payment as decimal(16,2)),0)-coalesce(cast(payment_change as decimal(16,2)),0)) ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_acct_cd_cash_out
		  
			
			from pos_payments pp
			left join pos_drawers pd
			       ON pp.tenant_no = pd.tenant_no
			      AND pp.pos_cd = pd.pos_cd
			      AND pp.drawer_no = pd.drawer_no
			where pp.tenant_no=@TENANT_NO
	    	  and pp.pos_cd=@POS_CD
			  and pp.pos_session_id = @POS_SESSION_ID
			  and pp.drawer_no = @DRAWER_NO
			  
			  
			GROUP BY  coalesce(pp.contra_acct_cd, '-') 
		</sql>
	</report-dataset>
	
	
	<!-- 
	
		  sum(case when payment_type = 1 THEN CASE WHEN sales_inv_id IS NULL THEN CASE WHEN purchase_inv_id IS NULL THEN CASE WHEN payment_direction = 1 THEN CASE WHEN contra_acct_cd IS NULL THEN 0 ELSE  (coalesce(payment,0)-coalesce(payment_change,0)) END ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_acct_cd_cash_in,
			  sum(case when payment_type = 1 THEN CASE WHEN sales_inv_id IS NULL THEN CASE WHEN purchase_inv_id IS NULL THEN CASE WHEN payment_direction = 2 THEN CASE WHEN contra_acct_cd IS NULL THEN 0 ELSE  (coalesce(payment,0)-coalesce(payment_change,0)) END ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_acct_cd_cash_out,
			  sum(case when payment_type = 1 THEN CASE WHEN sales_inv_id IS NULL THEN CASE WHEN purchase_inv_id IS NULL THEN CASE WHEN payment_direction = 1 THEN CASE WHEN contra_acct_cd IS NULL THEN (coalesce(payment,0)-coalesce(payment_change,0)) ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_cash_in,
			  sum(case when payment_type = 1 THEN CASE WHEN sales_inv_id IS NULL THEN CASE WHEN purchase_inv_id IS NULL THEN CASE WHEN payment_direction = 2 THEN CASE WHEN contra_acct_cd IS NULL THEN (coalesce(payment,0)-coalesce(payment_change,0)) ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_cash_out
		
	
	 -->
	
	<report-dataset name="cash_start">
		<sql>
			SELECT ss.session_start_ts, cast(ss.cash_start as decimal(16,2)) cash_start, ss.pos_session_cd from pos_sessions ss where tenant_no=@TENANT_NO and pos_cd=@POS_CD and ss.pos_session_id=@POS_SESSION_ID and ss.drawer_no = @DRAWER_NO
		</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, cast( ss.cash_end as decimal(16,2)) cash_end from pos_sessions ss where ss.tenant_no=@TENANT_NO and ss.pos_cd=@POS_CD and ss.pos_session_id= @POS_SESSION_ID and ss.drawer_no = @DRAWER_NO
		</sql>
	</report-dataset>

</report>
