<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Kassenbuch_full.xsl">
	<title>Kassenbuch_full</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="pos_drawers">
	</report-result>
	<report-result report-dataset="businessunits">
	</report-result>
			
	<!-- <report-result report-dataset="revenue_acct">
	</report-result> -->
<!-- 	<report-result report-dataset="item_revenue_accounts">
	</report-result>
-->
			<report-result report-dataset="simple_accounts">
	</report-result>
	<report-result report-dataset="payments_in_out">
	</report-result>
	
<report-transaction report-nm="dates" report-transaction="de.timeglobe.pos.reporting.DateList">
	</report-transaction>
<report-transaction report-nm="pos_payment_statistics" report-transaction="de.timeglobe.pos.reporting.PosPaymentStatistics">
	</report-transaction>
<!-- 
<report-transaction report-nm="purchase_notes" report-transaction="de.timeglobe.pos.reporting.PurchaseSimpleAcct">
	</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="pos_drawers">
		<sql>
			select * from pos_drawers where tenant_no=@TENANT_NO and pos_cd=@POS_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="revenue_acct">
		<sql>
			select
			cast (sales_inv_ts as DATE) sales_inv_dt,
			coalesce(i.item_revenue_acct_no,0) item_revenue_acct_no,  
			coalesce(sum(case when h.sales_inv_type=1 then p.position_gross_price else case when h.sales_inv_type=2 then -p.position_gross_price else 0 end end),0) r_revenue_acct_value,
			coalesce(sum(case when h.sales_inv_type=1 then p.position_net_price else case when h.sales_inv_type=2 then -p.position_net_price else 0 end end),0) r_net_revenue_acct_value
			
			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 items i
			       on i.item_cd = p.item_cd
			      AND i.tenant_no = h.tenant_no
			      AND i.company_no = h.company_no
			      AND i.department_no = h.department_no
			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) &gt;= cast (@DATE as DATE) and cast (sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE))
			  AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
			GROUP BY cast (sales_inv_ts as DATE), coalesce(i.item_revenue_acct_no,0)
		</sql>
	</report-dataset>
	
	<report-dataset name="item_revenue_accounts">
		<sql>
			select * from item_revenue_accounts where tenant_no=@TENANT_NO and company_no=@COMPANY_NO AND department_no=@DEPARTMENT_NO
		</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="purchase_notes">
		<sql>
			select
            	cast (pp.payment_ts as DATE) payment_dt,
	            coalesce(pip.simple_acct_cd,'') simple_acct_cd,			
	            coalesce(sum(pip.position_gross_price),0) position_gross_price,
	            coalesce(sum(pip.position_net_price),0) position_net_price
			from purchase_inv_positions pip
			join purchase_invs h
			  on h.tenant_no = pip.tenant_no and h.pos_cd = pip.pos_cd and h.purchase_inv_id = pip.purchase_inv_id
			join  pos_payments pp 
			  on h.purchase_inv_id = coalesce(pp.purchase_inv_id,-111)
			 and pp.tenant_no=h.tenant_no 
			 and pp.pos_cd=h.pos_cd 
			 and cast (pp.payment_ts as DATE) &gt;= cast (@DATE as DATE) and cast (pp.payment_ts as DATE) &lt;= cast (@TODATE as DATE)    
			
			WHERE h.tenant_no=@TENANT_NO 
			  AND h.pos_cd=@POS_CD 
			  GROUP BY cast (pp.payment_ts as DATE), coalesce(pip.simple_acct_cd,'')

		</sql>
	</report-dataset>
	
	<report-dataset name="payments_in_out">
		<sql>
			select
            	cast (pp.payment_ts as DATE) payment_dt,
            	drawer_no drawer_no,
	            coalesce(pp.contra_acct_cd,'-') contra_acct_cd,	
	            pp.payment_direction payment_direction,		
	            coalesce(sum(pp.payment),0) payment
	           
			from pos_payments pp 
			  
			 WHERE pp.tenant_no=@TENANT_NO 
			  AND pp.pos_cd=@POS_CD 
			  AND pp.sales_inv_id  IS NULL
			  AND pp.purchase_inv_id  IS NULL
			  AND cast (pp.payment_ts as DATE) &gt;= cast (@DATE as DATE) and cast (pp.payment_ts as DATE) &lt;= cast (@TODATE as DATE)   
			  GROUP BY cast (pp.payment_ts as DATE), drawer_no, coalesce(pp.contra_acct_cd,'-'), pp.payment_direction 

		</sql>
	</report-dataset>
	
	
		<report-dataset name="payments_voucher">
		<sql>
			select
            	cast (pp.payment_ts as DATE) payment_dt,
	            coalesce(pp.contra_acct_cd,'-') contra_acct_cd,	
	            pp.payment_direction payment_direction,		
	            coalesce(sum(pp.payment),0) payment
	           
			from pos_payments pp 
			  
			 WHERE pp.tenant_no=@TENANT_NO 
			  AND pp.pos_cd=@POS_CD 
			  AND pp.sales_inv_id  IS NULL
			  AND pp.purchase_inv_id  IS NULL
			  AND cast (pp.payment_ts as DATE) &gt;= cast (@DATE as DATE) and cast (pp.payment_ts as DATE) &lt;= cast (@TODATE as DATE)   
			  GROUP BY cast (pp.payment_ts as DATE), coalesce(pp.contra_acct_cd,'-'), pp.payment_direction 

		</sql>
	</report-dataset>
	
</report>
