<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Bonjournal.xsl">
	<title>Bonjournal</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="notes">
	</report-result>
	<report-result report-dataset="firstNote">
	</report-result>
	<report-result report-dataset="lastNote">
	</report-result>
		
<!-- 	<report-result report-dataset="open_d">
	</report-result>
	<report-result report-dataset="open_d_payments">
	</report-result>
-->

<report-transaction report-nm="pos_notes_statistics" report-transaction="de.timeglobe.pos.reporting.PosNotesStatistics">
	</report-transaction>
	<report-result report-dataset="vouchers_in">
	</report-result>

	<report-result report-dataset="vouchers_out">
	</report-result>
	<report-result report-dataset="canceled_notes">
	</report-result>
<!-- <report-result report-dataset="canceled_note_payments">
	</report-result> -->
		<report-result report-dataset="canceled_payments">
	</report-result>
	
		<report-result report-dataset="all_si">
	</report-result>
	<report-result report-dataset="all_si_payments">
	</report-result>
		<report-result report-dataset="payment_types">
	</report-result>
			<report-result report-dataset="purchase_notes">
	</report-result>
				<report-result report-dataset="canceled_purchase_notes">
	</report-result>
		
				<report-result report-dataset="credit_notes">
	</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
		</sql>
	</report-dataset>
	
		<report-dataset name="payment_types">
		<sql>
			select * from pos_payment_types WHERE coalesce(sales_inv,0) =1
		</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="vouchers_out">
		<sql>
			SELECT
			pd.drawer_nm drawer_nm,
			pp.employee_nm cashier_nm,
			pp.payment_ref voucher_cd,
			pp.payment payment,
			coalesce(si.customer_first_nm,'') || ' ' || si.customer_contact_nm customer_nm,
			si.sales_inv_cd sales_inv_cd
			from pos_payments pp
			left join sales_invs si ON pp.tenant_no = si.
			tenant_no AND pp.pos_cd = si.pos_cd AND coalesce
			(pp.sales_inv_id,-1000) = si.sales_inv_id
			left join POS_DRAWERS pd ON
			pd.tenant_no = pp.tenant_no AND pp.pos_cd = pd.pos_cd AND
			pd.drawer_no = pp.drawer_no
			WHERE pp.tenant_no = @TENANT_NO AND pp.pos_cd = @POS_CD AND
			(pp.payment_type = 2 OR pp.payment_type = 7 OR pp.payment_type = 11)
			AND pp.payment_direction = 2 and cast (pp.payment_ts as DATE) = cast
			(@DATE as DATE)
		</sql>
	</report-dataset>


	<report-dataset name="vouchers_in">
		<sql>
			SELECT
			pd.drawer_nm drawer_nm,
			pp.employee_nm cashier_nm,
			pp.payment_ref voucher_cd,
			pp.payment payment,
			coalesce(si.customer_first_nm,'') || ' ' || si.customer_contact_nm customer_nm,
			si.sales_inv_cd sales_inv_cd
			from pos_payments pp
			left join sales_invs si ON pp.tenant_no = si.
			tenant_no AND pp.pos_cd = si.pos_cd AND coalesce
			(pp.sales_inv_id,-1000) = si.sales_inv_id
			left join POS_DRAWERS pd ON
			pd.tenant_no = pp.tenant_no AND pp.pos_cd = pd.pos_cd AND
			pd.drawer_no = pp.drawer_no
			WHERE pp.tenant_no = @TENANT_NO AND pp.pos_cd = @POS_CD AND
			(pp.payment_type = 2 OR pp.payment_type = 7 OR pp.payment_type = 11)
			AND pp.payment_direction = 1 and cast (pp.payment_ts as DATE) = cast
			(@DATE as DATE)
		</sql>
	</report-dataset>

	<report-dataset name="canceled_notes">
		<sql>
			select

			h.sales_inv_id sales_inv_id,
			 h.sales_inv_type sales_inv_type,
			h.sales_inv_cd sales_inv_cd ,
			h.sales_inv_ts sales_inv_ts,
			h.employee_nm employee_nm,
			coalesce(h.customer_first_nm,'') || ' ' || h.customer_contact_nm customer_nm ,
			h.total_gross_price total_gross_price,
			h.cancel_reason cancel_reason
			from sales_invs h
			join sales_invs c 
			  on h.sales_inv_id = c.cancel_for_sales_inv_id
			 and c.tenant_no=h.tenant_no 
			 and c.pos_cd=h.pos_cd 
			 and cast (c.sales_inv_ts as DATE) = cast (@DATE as DATE)
			AND c.cancel_for_sales_inv_id IS NOT NULL
			WHERE h.tenant_no=@TENANT_NO 
			  AND h.pos_cd=@POS_CD 
			  

		</sql>
	</report-dataset>


	<report-dataset name="credit_notes">
		<sql>
			select

			h.sales_inv_id sales_inv_id,
			 h.sales_inv_type sales_inv_type,
			h.sales_inv_cd sales_inv_cd ,
			h.sales_inv_ts sales_inv_ts,
			h.employee_nm employee_nm,
			coalesce(h.customer_first_nm,'') || ' ' || h.customer_contact_nm customer_nm ,
			h.total_gross_price total_gross_price,
			h.cancel_reason cancel_reason
			from sales_invs h
			
			WHERE h.tenant_no=@TENANT_NO 
			  AND h.pos_cd=@POS_CD 
			  AND cast (h.sales_inv_ts as DATE) = cast (@DATE as DATE)
			  	AND h.cancel_for_sales_inv_id IS NULL
			  	AND h.sales_inv_type =2 
			  

		</sql>
	</report-dataset>

<report-dataset name="purchase_notes">
		<sql>
			select

			h.purchase_inv_id purchase_inv_id,
			h.purchase_inv_cd purchase_inv_cd ,
			h.purchase_inv_ts purchase_inv_ts,
			pp.drawer_no drawer_no,
			pp.payment_ts payment_ts,
			pp.payment_type payment_type,
			
			(case when coalesce(purchase_inv_type,1) = 1 THEN h.total_gross_price ELSE -h.total_gross_price END) total_gross_price
			
			from purchase_invs h
			left 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 pp.cancel_for_pos_payment_id is null
			 
			WHERE h.tenant_no=@TENANT_NO 
			  AND h.pos_cd=@POS_CD 
			  AND cast (h.purchase_inv_ts as DATE) = cast (@DATE as DATE)
			  ORDER BY 	h.purchase_inv_ts

		</sql>
	</report-dataset>
	
	<report-dataset name="canceled_purchase_notes">
		<sql>
				select

			h.purchase_inv_id purchase_inv_id,
			h.purchase_inv_cd purchase_inv_cd ,
			h.purchase_inv_ts purchase_inv_ts,
			pp.drawer_no drawer_no,
			pp.payment_ts payment_ts,
			pp.payment_type payment_type,
			
			(case when coalesce(h.purchase_inv_type,1) = 1 THEN h.total_gross_price ELSE -h.total_gross_price END) total_gross_price
			
			from purchase_invs h
			join purchase_invs hCanceled
			ON h.tenant_no = hCanceled.tenant_no AND h.pos_cd = hCanceled.pos_cd AND h.purchase_inv_id = hCanceled.cancel_for_purchase_inv_id
			left 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 pp.cancel_for_pos_payment_id is null
			 
			WHERE h.tenant_no=@TENANT_NO 
			  AND h.pos_cd=@POS_CD 
			  AND cast (h.purchase_inv_ts as DATE) = cast (@DATE as DATE)
			  ORDER BY 	h.purchase_inv_ts

		</sql>
	</report-dataset>

	<report-dataset name="canceled_note_payments">
		<sql>
			select
			h.sales_inv_id sales_inv_id,
			h.drawer_no drawer_no,
			h.payment_type payment_type,
			h.employee_nm employee_nm

			from pos_payments h
			where coalesce(h.sales_inv_id,-11) 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 sales_inv_type=2 AND coalesce(canceled,0) = 0
			     )
			  AND tenant_no=@TENANT_NO and pos_cd=@POS_CD
		</sql>
	</report-dataset>

	<report-dataset name="canceled_payments">
		<sql>
		 select
			     pp.sales_inv_id sales_inv_id,
	     	     si.sales_inv_cd sales_inv_cd,
	     	     pp.drawer_no drawer_no,
	     	     pd.drawer_nm drawer_nm,
			     pp.payment_type payment_type,
			     pp.employee_nm employee_nm,
                 pp.payment_ts payment_ts,
                 pp.cancel_reason cancel_reason,
                 coalesce(si.customer_first_nm,'') || ' ' || si.customer_contact_nm customer_nm,
                 (case when  pp.payment_type = 1 THEN coalesce(pp.payment,0)-coalesce(pp.payment_change,0) ELSE coalesce(pp.payment,0) END) payment
                 
			from pos_payments pp 
			left join sales_invs si 
			  ON pp.tenant_no = si.
			     tenant_no AND pp.pos_cd = si.pos_cd AND 
			     coalesce(pp.sales_inv_id,-1000) = si.sales_inv_id
		    left join POS_DRAWERS pd 
		      ON pd.tenant_no = pp.tenant_no AND pp.pos_cd = pd.pos_cd 
		     AND pd.drawer_no = pp.drawer_no
		   where pp.tenant_no=@TENANT_NO and pp.pos_cd=@POS_CD
			 and cast (pp.payment_ts as DATE) = cast (@DATE as DATE)
             AND pp.cancel_for_pos_session_id IS NOT NULL			
		</sql>
	</report-dataset>

	<report-dataset name="open_d">
		<sql>
     	   select si.sales_inv_id sales_inv_id,
			      si.sales_inv_cd sales_inv_cd,
			      si.sales_inv_ts sales_inv_ts,
			      coalesce(si.customer_first_nm,'') || ' ' || si.customer_contact_nm customer_nm,
			      si.total_gross_price total_gross_price
			 from sales_invs si
			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="open_d_payments">
		<sql>
			select
			      h.sales_inv_id sales_inv_id,
			      h.drawer_no drawer_no,
			      h.payment_type payment_type,
			      h.employee_nm employee_nm
			 from pos_payments h
			where coalesce(h.sales_inv_id,-11) 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
			       )
			  AND tenant_no=@TENANT_NO and pos_cd=@POS_CD
		</sql>
	</report-dataset>
	
		<report-dataset name="all_si">
		<sql>
     	   select si.sales_inv_id sales_inv_id,
                  si.sales_inv_type sales_inv_type,
			      si.sales_inv_cd sales_inv_cd,
			      si.sales_inv_ts sales_inv_ts,
			      coalesce(si.customer_first_nm,'') || ' ' || si.customer_contact_nm customer_nm,
			      CASE WHEN si.sales_inv_type = 1 THEN si.total_gross_price ELSE CASE WHEN si.sales_inv_type = 2 THEN -si.total_gross_price ELSE 0 END END total_gross_price
			 from sales_invs si
			where tenant_no=@TENANT_NO 
			  and pos_cd=@POS_CD and cast (sales_inv_ts as DATE) = cast (@DATE as DATE)
			  ORDER BY sales_inv_ts
			 
		</sql>
	</report-dataset>
	
		<report-dataset name="all_si_payments">
		<sql>
		   select h.sales_inv_id sales_inv_id,
			      h.drawer_no drawer_no,
			      pd.drawer_nm drawer_nm,
			      h.payment_type payment_type_nm,
			      h.employee_nm employee_nm
			 from pos_payments h 
     
             left join POS_DRAWERS pd 
		       ON pd.tenant_no = h.tenant_no AND h.pos_cd = pd.pos_cd 
		      AND pd.drawer_no = h.drawer_no     
			where coalesce(h.sales_inv_id,-11) 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 h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD
			 
			  AND cast (h.payment_ts as DATE) = cast (@DATE as DATE)
		</sql>
	</report-dataset>


	<report-dataset name="old_all_si_payments">
		<sql>
		   select h.sales_inv_id sales_inv_id,
			      h.drawer_no drawer_no,
			      pd.drawer_nm drawer_nm,
			      (case when ppt.report_nm is null THEN (cast(h.payment_type AS CHAR(3))) ELSE ppt.report_nm END) payment_type_nm,
			      h.employee_nm employee_nm
			 from pos_payments h 
        left join pos_payment_types ppt
               on ppt.payment_type = h.payment_type
        	  AND ppt.tenant_no= h.tenant_no
              AND ppt.payment_direction = 0 
              AND ppt.sales_inv = 1     
             left join POS_DRAWERS pd 
		       ON pd.tenant_no = h.tenant_no AND h.pos_cd = pd.pos_cd 
		      AND pd.drawer_no = h.drawer_no     
			where coalesce(h.sales_inv_id,-11) 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 h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD
			  AND h.payment_direction = 1 
			 
		</sql>
	</report-dataset>
</report>
