<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Tagesabschluss.xsl">
	<title>Tagesabschluss</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="drawers">
	</report-result>
	<report-result report-dataset="notes">
	</report-result>
		<report-result report-dataset="firstNote">
	</report-result>
		<report-result report-dataset="lastNote">
	</report-result>
	<report-result report-dataset="open">
	</report-result>

	<report-result report-dataset="open_b">
	</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="revenue_acct_service">
	</report-result>
<report-result report-dataset="item_revenue_accounts">
	</report-result>
	<report-result report-dataset="revenue_acct_product">
	</report-result>
		<report-result report-dataset="pos_sessions_acct">
	</report-result>

	<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="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="drawers">
		<sql>
			select * from pos_drawers d
			join 	
            	(	SELECT tenant_no , pos_cd , drawer_no  
            	      FROM pos_sessions  
            	      where tenant_no=@TENANT_NO and pos_cd=@POS_CD and session_end_ts IS NOT NULL and cast
		                 	(session_end_ts as DATE) = cast (@DATE as DATE) 
			          GROUP by tenant_no , pos_cd , drawer_no  
			     )mps
			on mps.tenant_no = d.tenant_no and mps.pos_cd = d.pos_cd and mps.drawer_no = d.drawer_no
			where d.tenant_no=@TENANT_NO and d.pos_cd=@POS_CD
		</sql>
	</report-dataset>

	<report-dataset name="open">
		<sql>
			select coalesce(sum(case when iv.sales_inv_type=2 then -iv.total_gross_price else iv.total_gross_price end),0) r_total,
			coalesce(sum(1),0) 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) &lt;= cast (@DATE as DATE))) 
			and coalesce(booking_state,0) =0
		</sql>
	</report-dataset>

<report-dataset name="open_b">
		<sql>
			select coalesce(sum(case when iv.sales_inv_type=2 then -iv.total_gross_price else iv.total_gross_price end),0) r_total,
			coalesce(sum(case when iv.sales_inv_type=2 then -1 else 1 end),0) 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) &lt; cast (@DATE as DATE))
			and coalesce(booking_state,0) =0
		</sql>
	</report-dataset>

	<report-dataset name="open_d">
		<sql>
			select coalesce(sum(case when iv.sales_inv_type=2 then -iv.total_gross_price else iv.total_gross_price end),0) r_total,
			coalesce(sum(case when iv.sales_inv_type=2 then -1 else 1 end),0) 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 coalesce(booking_state,0) =0
		</sql>
	</report-dataset>
	<report-dataset name="revenue">
		<sql>
			select
			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,
			coalesce(sum(case when h.sales_inv_type=1 then (coalesce(p.applied_pos_rebate_price,0) + coalesce(p.applied_inv_rebate_price,0))  else case when h.sales_inv_type=2 then -(coalesce(p.applied_pos_rebate_price,0) + coalesce(p.applied_inv_rebate_price,0)) else 0 end end),0) r_rebate,
			coalesce(sum(case when coalesce(p.stockable, 0)=0 then 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 ELSE 0 end),0) r_revenue_services,
			coalesce(sum(case when coalesce(p.stockable, 0)=1 then 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 ELSE 0 end),0)  r_revenue_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 si where si.tenant_no=p.tenant_no and si.pos_cd=p.pos_cd and cast (sales_inv_ts as DATE) = cast (@DATE as DATE))
			  AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
		</sql>
	</report-dataset>
	<report-dataset name="revenueOld">
		<sql>
			select
			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,
			coalesce(sum(case when h.sales_inv_type=1 then (coalesce(p.applied_pos_rebate_price,0) + coalesce(p.applied_inv_rebate_price,0))  else case when h.sales_inv_type=2 then -(coalesce(p.applied_pos_rebate_price,0) + coalesce(p.applied_inv_rebate_price,0)) else 0 end end),0) r_rebate,
			coalesce(sum(case when coalesce(p.stockable, 0)=0 then 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 ESLE 0 end),0) r_revenue_services,
			coalesce(sum(case when coalesce(p.stockable, 0)=1 then 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 ESLE 0 end),0)  r_revenue_products,
			coalesce(sum(case when h.sales_inv_type=2 AND h.cancel_for_sales_inv_id IS  NULL then 0 else -p.position_gross_price end),0) r_canceled,
			coalesce(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),0) r_canceled_services,
			coalesce(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),0) r_canceled_products,
			coalesce(sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end),0) r_total,
			coalesce(sum(case when h.sales_inv_type=1 then (coalesce(p.applied_pos_rebate_price,0) + coalesce(p.applied_inv_rebate_price,0))  else -(coalesce(p.applied_pos_rebate_price,0) + coalesce(p.applied_inv_rebate_price,0)) end),0) r_total_rebate,
			coalesce(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),0) r_total_services,
			coalesce(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),0) 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 si where si.tenant_no=p.tenant_no and si.pos_cd=p.pos_cd and cast (sales_inv_ts as DATE) = cast (@DATE as DATE))
			  AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
		</sql>
	</report-dataset>
	
	
		<report-dataset name="revenue_acct">
		<sql>
			select
			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
			
			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) = cast (@DATE as DATE))
			  AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
			GROUP BY coalesce(i.item_revenue_acct_no,0)
		</sql>
	</report-dataset>
		
<report-dataset name="revenue_acct_service">
		<sql>
			select
			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
			
			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) = cast (@DATE as DATE))
			  AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
			  AND coalesce(p.stockable, 0)=0
			GROUP BY coalesce(i.item_revenue_acct_no,0)
		</sql>
	</report-dataset>
		
		<report-dataset name="revenue_acct_product">
		<sql>
			select
			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
			
			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) = cast (@DATE as DATE))
			  AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
			    AND coalesce(p.stockable, 0)=1
			GROUP BY coalesce(i.item_revenue_acct_no,0)
		</sql>
	</report-dataset>
		
	<report-dataset name="notes">
		<sql>
			select
			coalesce(sum(1),0) r_revenue,
			coalesce(sum(case when  h.cancel_for_sales_inv_id IS Not NULL then  1 else 0 end),0) r_canceled,
			coalesce(sum(case when  h.cancel_for_sales_inv_id IS Not NULL then total_gross_price  else 0 end),0) r_canceled_value,
			coalesce(sum(case when h.sales_inv_type=2  AND h.cancel_for_sales_inv_id IS  NULL then  1 else 0 end),0) r_credit_notes,
			coalesce(sum(case when h.sales_inv_type=2  AND h.cancel_for_sales_inv_id IS  NULL then total_gross_price  else 0 end),0) r_credit_notes_value,
			count(distinct(h.customer_no)) r_cnt_customer
			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="firstNote">
		<sql>
			select
			h.sales_inv_cd sales_inv_cd
			from sales_invs h
			where h.sales_inv_ts in (select min(sales_inv_ts) 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="lastNote">
		<sql>
			select
			h.sales_inv_cd sales_inv_cd
			from sales_invs h
			where h.sales_inv_ts in (select max(sales_inv_ts) 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_sessions">
		<sql>
			select
			drawer_no t_drawer_no,
			coalesce(sum(cash_in),0) t_cash_in,
			coalesce(sum(cash_out),0) t_cash_out,
			coalesce(sum(cash_sales_in-cash_sales_return_out),0) t_cash_sales_in,
			coalesce(sum(cash_purchase_out-cash_purchase_return_in),0) t_cash_purchase_out,
			coalesce(sum(cash_debt_out),0) t_cash_debt_out,
			coalesce(sum(cash_debt_in),0) t_cash_debt_in,
			coalesce(sum(ec_sales_in),0) t_ec_sales_in,
			coalesce(sum(cc_sales_in),0) t_cc_sales_in,
			coalesce(sum(transfer_sales_in),0) t_transfer_sales_in,
			coalesce(sum(transfer_purchase_out),0) t_transfer_purchase_out,
			coalesce(sum(creditnote_sales_in),0) t_creditnote_sales_in,
			coalesce(sum(creditnote_sales_out),0) t_creditnote_sales_out,
			coalesce(sum(discount_sales_out),0) t_discount_sales_out,
			coalesce(sum(voucher_sales_in),0) t_voucher_sales_in,
			coalesce(sum(voucher_out),0) t_voucher_out
			from pos_sessions 
			
			where tenant_no=@TENANT_NO
			and pos_cd=@POS_CD
			and session_end_ts IS NOT NULL
			and cast (session_end_ts as DATE) = cast (@DATE as DATE)
			GROUP BY drawer_no
			
		</sql>
	</report-dataset>
	
			<report-dataset name="pos_sessions_acct">
		<sql>
			select
			pp.drawer_no t_drawer_no,
			  coalesce(contra_acct_cd, pd.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(payment,0)-coalesce(payment_change,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(payment,0)-coalesce(payment_change,0)) ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END) t_acct_cd_cash_out
			  
			
			from pos_payments pp 
			join pos_sessions ps 
			  ON pp.tenant_no = ps.tenant_no
			      AND pp.pos_cd = ps.pos_cd
			      AND pp.drawer_no = ps.drawer_no
			      AND pp.pos_session_id = ps.pos_session_id
			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 ps.session_end_ts IS NOT NULL
			      and cast (ps.session_end_ts as DATE) = cast (@DATE as DATE)
			  
			  
			GROUP BY pp.drawer_no, coalesce(pp.contra_acct_cd, pd.acct_cd) 
		</sql>
	</report-dataset>
	
	
	<report-dataset name="cash_start">
		<sql>
		
			SELECT ss.drawer_no t_drawer_no, ss.session_start_ts, coalesce(ss.cash_start,0) cash_start
	      	from pos_sessions ss join 	
            	(	SELECT tenant_no , pos_cd , drawer_no  ,min(session_start_ts) m_start_ts 
            	      FROM pos_sessions  
            	      where tenant_no=@TENANT_NO and pos_cd=@POS_CD and session_end_ts IS NOT NULL and cast
		                 	(session_end_ts as DATE) = cast (@DATE as DATE) 
			          GROUP by tenant_no , pos_cd , drawer_no  
			     )mps
			on mps.tenant_no = ss.tenant_no and mps.pos_cd = ss.pos_cd and mps.drawer_no = ss.drawer_no AND mps.m_start_ts = ss.session_start_ts
			
		
		</sql>
	</report-dataset>
	<report-dataset name="cash_end">
		<sql>
			SELECT ss.drawer_no t_drawer_no, ss.session_end_ts,  coalesce(ss.cash_end ,0) cash_end
	      	from pos_sessions ss join 	
            	(	SELECT tenant_no , pos_cd , drawer_no  ,max(session_end_ts) m_end_ts 
            	      FROM pos_sessions  
            	      where tenant_no=@TENANT_NO and pos_cd=@POS_CD and session_end_ts IS NOT NULL and cast
		                 	(session_end_ts as DATE) = cast (@DATE as DATE) 
			          GROUP by tenant_no , pos_cd , drawer_no  
			     )mps
			on mps.tenant_no = ss.tenant_no and mps.pos_cd = ss.pos_cd and mps.drawer_no = ss.drawer_no AND mps.m_end_ts = ss.session_end_ts
		</sql>
	</report-dataset>

</report>
