<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="VoucherStatistics.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="sales_voucher_out">
	</report-result>


	<report-result report-dataset="sales_voucher_in">
	</report-result>
	
		<report-result report-dataset="sales_voucher_in2">
	</report-result>

			<report-result report-dataset="voucher_uses_in_other_pos">
	</report-result>
		<report-result report-dataset="sales_voucher_out_storno">
	</report-result>

	<report-result report-dataset="sales_voucher_in_storno">
	</report-result>

	<report-result report-dataset="vouchers_in2">
	</report-result>

	<report-result report-dataset="sales_voucher_out_disabled">
	</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="sales_voucher_out">
		<sql>
			
			SELECT svu.SALES_VOUCHER_USE_TS  voucher_use_ts
			,svu.EMPLOYEE_NM  employee_nm
			,sv.SALES_VOUCHER_CD  voucher_cd
			,sv.VOUCHER_VALUE voucher_value
			,coalesce(svu.CUSTOMER_FIRST_NM, '') || ' ' || svu.CUSTOMER_CONTACT_NM customer_nm
			,si.sales_inv_cd sales_inv_cd
			,svu.voucher_state
			,svu.used_by_external_pos_ref
			,svIn.SALES_VOUCHER_USE_TS redeemed_sales_voucher_use_ts
			,svIn.voucher_state redeemed_sales_voucher_state
		FROM sales_vouchers sv
		
		JOIN sales_voucher_uses svu ON sv.tenant_no = svu.tenant_no
			AND sv.pos_cd = svu.pos_cd
			AND sv.sales_voucher_id = svu.sales_voucher_id
			AND svu.voucher_state = 1 
		JOIN (
					SELECT distinct	us.tenant_no, us.pos_cd, us.sales_voucher_id  

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD 
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state not in (2) 
		
				) svNotIn
				ON 			svNotIn.tenant_no = sv.tenant_no
				AND 		svNotIn.pos_cd = sv.pos_cd
				AND 		svNotIn.sales_voucher_id = sv.sales_voucher_id
		LEFT JOIN (
					SELECT us.tenant_no, us.pos_cd, us.sales_voucher_id, us.sales_voucher_use_id, us.SALES_VOUCHER_USE_TS , us.used_sales_inv_id , us.EMPLOYEE_NM, us.used_by_external_pos_ref, us.CUSTOMER_FIRST_NM, us.CUSTOMER_CONTACT_NM, us.voucher_state

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD
								
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state  in (3,4,6) 
		
				) svIn
				ON 			svIn.tenant_no = sv.tenant_no
				AND 		svIn.pos_cd = sv.pos_cd
				AND 		svIn.sales_voucher_id = sv.sales_voucher_id		
		left outer join sales_invs si on svu.tenant_no = si.tenant_no and svu.pos_cd = si.pos_cd and svu.used_sales_inv_id = si.sales_inv_id
		where  sv.tenant_no = @TENANT_NO AND sv.pos_cd = @POS_CD 
		and cast (svu.SALES_VOUCHER_USE_TS as DATE) >= cast
					(@DATE as DATE) and cast(svu.SALES_VOUCHER_USE_TS as DATE) &lt;= cast(@TODATE as DATE)
				ORDER BY 	svu.SALES_VOUCHER_USE_TS ASC
			
		</sql>
	</report-dataset>


<report-dataset name="sales_voucher_in">
		<sql>
			
		
SELECT  svIn.SALES_VOUCHER_USE_TS  voucher_use_ts
			,svIn.EMPLOYEE_NM  employee_nm
			,sv.SALES_VOUCHER_CD  voucher_cd
			,sv.VOUCHER_VALUE voucher_value
			, sv.sales_voucher_ts voucher_ts
			,coalesce(svIn.CUSTOMER_FIRST_NM, '') || ' ' || svIn.CUSTOMER_CONTACT_NM customer_nm
			,si.sales_inv_cd sales_inv_cd
			,svIn.voucher_state
			,svIn.used_by_external_pos_ref FROM sales_vouchers sv

			JOIN (
					SELECT 	distinct svu.tenant_no, svu.pos_cd, svu.sales_voucher_id 
					FROM 	sales_voucher_uses svu
					where 		svu.tenant_no = @TENANT_NO AND svu.pos_cd = @POS_CD
						AND		CAST (svu.SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)     
						AND 	CAST (svu.SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE) 
						AND 	svu.voucher_state = 1 
		
				) svOut
				ON 		svOut.tenant_no = sv.tenant_no
				AND 		svOut.pos_cd = sv.pos_cd
				AND 		svOut.sales_voucher_id = sv.sales_voucher_id
		
			JOIN (
					SELECT us.tenant_no, us.pos_cd, us.sales_voucher_id, us.sales_voucher_use_id, us.SALES_VOUCHER_USE_TS , us.used_sales_inv_id , us.EMPLOYEE_NM, us.used_by_external_pos_ref, us.CUSTOMER_FIRST_NM, us.CUSTOMER_CONTACT_NM, us.voucher_state

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state  in (3) 
		
				) svIn
				ON 			svIn.tenant_no = sv.tenant_no
				AND 		svIn.pos_cd = sv.pos_cd
				AND 		svIn.sales_voucher_id = sv.sales_voucher_id



		left outer join sales_invs si on svIn.tenant_no = si.tenant_no and svIn.pos_cd = si.pos_cd and svIn.used_sales_inv_id = si.sales_inv_id
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
		ORDER BY 	 svIn.SALES_VOUCHER_USE_TS ASC	
		</sql>
	</report-dataset>

<report-dataset name="sales_voucher_in2">
		<sql>
			
		
SELECT  svIn.SALES_VOUCHER_USE_TS  voucher_use_ts
			,svIn.EMPLOYEE_NM  employee_nm
			,sv.SALES_VOUCHER_CD  voucher_cd
			,sv.VOUCHER_VALUE voucher_value
			, sv.sales_voucher_ts voucher_ts
			,coalesce(svIn.CUSTOMER_FIRST_NM, '') || ' ' || svIn.CUSTOMER_CONTACT_NM customer_nm
			,si.sales_inv_cd sales_inv_cd
			,svIn.voucher_state
			,svIn.used_by_external_pos_ref FROM sales_vouchers sv

			JOIN (
					SELECT 	distinct svu.tenant_no, svu.pos_cd, svu.sales_voucher_id 
					FROM 	sales_voucher_uses svu
					where 		svu.tenant_no = @TENANT_NO AND svu.pos_cd = @POS_CD
						AND		CAST (svu.SALES_VOUCHER_USE_TS  AS DATE) &lt; CAST (@DATE  AS DATE)      
						AND 	svu.voucher_state = 1 
		
				) svOut
				ON 		svOut.tenant_no = sv.tenant_no
				AND 		svOut.pos_cd = sv.pos_cd
				AND 		svOut.sales_voucher_id = sv.sales_voucher_id
		
			JOIN (
					SELECT us.tenant_no, us.pos_cd, us.sales_voucher_id, us.sales_voucher_use_id, us.SALES_VOUCHER_USE_TS , us.used_sales_inv_id , us.EMPLOYEE_NM, us.used_by_external_pos_ref, us.CUSTOMER_FIRST_NM, us.CUSTOMER_CONTACT_NM, us.voucher_state

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state  in (3) 
		
				) svIn
				ON 			svIn.tenant_no = sv.tenant_no
				AND 		svIn.pos_cd = sv.pos_cd
				AND 		svIn.sales_voucher_id = sv.sales_voucher_id
		left outer join sales_invs si on svIn.tenant_no = si.tenant_no and svIn.pos_cd = si.pos_cd and svIn.used_sales_inv_id = si.sales_inv_id
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
			ORDER BY 	 svIn.SALES_VOUCHER_USE_TS ASC	
		</sql>
	</report-dataset>


<report-dataset name="sales_voucher_out_storno">
		<sql>
			
		
SELECT  svIn.SALES_VOUCHER_USE_TS  voucher_use_ts
			,svIn.EMPLOYEE_NM  employee_nm
			,sv.SALES_VOUCHER_CD  voucher_cd
			,sv.VOUCHER_VALUE voucher_value
			, sv.sales_voucher_ts voucher_ts
			,coalesce(svIn.CUSTOMER_FIRST_NM, '') || ' ' || svIn.CUSTOMER_CONTACT_NM customer_nm
			,si.sales_inv_cd sales_inv_cd
			,svIn.voucher_state
			,svIn.used_by_external_pos_ref FROM sales_vouchers sv
		
			JOIN (
					SELECT us.tenant_no, us.pos_cd, us.sales_voucher_id, us.sales_voucher_use_id, us.SALES_VOUCHER_USE_TS , us.used_sales_inv_id , us.EMPLOYEE_NM, us.used_by_external_pos_ref, us.CUSTOMER_FIRST_NM, us.CUSTOMER_CONTACT_NM, us.voucher_state

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state  in (2) 
		
				) svIn
				ON 			svIn.tenant_no = sv.tenant_no
				AND 		svIn.pos_cd = sv.pos_cd
				AND 		svIn.sales_voucher_id = sv.sales_voucher_id
		left outer join sales_invs si on svIn.tenant_no = si.tenant_no and svIn.pos_cd = si.pos_cd and svIn.used_sales_inv_id = si.sales_inv_id
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
		ORDER BY 	 svIn.SALES_VOUCHER_USE_TS ASC		
		</sql>
	</report-dataset>

<report-dataset name="sales_voucher_out_disabled">
		<sql>
			
		
SELECT  svIn.SALES_VOUCHER_USE_TS  voucher_use_ts
			,svIn.EMPLOYEE_NM  employee_nm
			,sv.SALES_VOUCHER_CD  voucher_cd
			,sv.VOUCHER_VALUE voucher_value
			, sv.sales_voucher_ts voucher_ts
			,coalesce(svIn.CUSTOMER_FIRST_NM, '') || ' ' || svIn.CUSTOMER_CONTACT_NM customer_nm
			,si.sales_inv_cd sales_inv_cd
			,svIn.voucher_state
			,svIn.used_by_external_pos_ref FROM sales_vouchers sv
		
			JOIN (
					SELECT us.tenant_no, us.pos_cd, us.sales_voucher_id, us.sales_voucher_use_id, us.SALES_VOUCHER_USE_TS , us.used_sales_inv_id , us.EMPLOYEE_NM, us.used_by_external_pos_ref, us.CUSTOMER_FIRST_NM, us.CUSTOMER_CONTACT_NM, us.voucher_state

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state  in (6) 
		
				) svIn
				ON 			svIn.tenant_no = sv.tenant_no
				AND 		svIn.pos_cd = sv.pos_cd
				AND 		svIn.sales_voucher_id = sv.sales_voucher_id
		left outer join sales_invs si on svIn.tenant_no = si.tenant_no and svIn.pos_cd = si.pos_cd and svIn.used_sales_inv_id = si.sales_inv_id
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
		ORDER BY 	 svIn.SALES_VOUCHER_USE_TS ASC		
		</sql>
	</report-dataset>


<report-dataset name="sales_voucher_in_storno">
		<sql>
			
		
SELECT  svIn.SALES_VOUCHER_USE_TS  voucher_use_ts
			,svIn.EMPLOYEE_NM  employee_nm
			,sv.SALES_VOUCHER_CD  voucher_cd
			,sv.VOUCHER_VALUE voucher_value
			, sv.sales_voucher_ts voucher_ts
			,coalesce(svIn.CUSTOMER_FIRST_NM, '') || ' ' || svIn.CUSTOMER_CONTACT_NM customer_nm
			,si.sales_inv_cd sales_inv_cd
			,svIn.voucher_state
			,svIn.used_by_external_pos_ref FROM sales_vouchers sv

			JOIN (
					SELECT us.tenant_no, us.pos_cd, us.sales_voucher_id, us.sales_voucher_use_id, us.SALES_VOUCHER_USE_TS , us.used_sales_inv_id , us.EMPLOYEE_NM, us.used_by_external_pos_ref, us.CUSTOMER_FIRST_NM, us.CUSTOMER_CONTACT_NM, us.voucher_state

					FROM 	sales_voucher_uses us
					JOIN 	(
								SELECT 	tenant_no, pos_cd, sales_voucher_id , max(sales_voucher_use_id) maxId
								FROM 	sales_voucher_uses 
								WHERE 		tenant_no = @TENANT_NO 
									AND 	pos_cd = @POS_CD
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &gt;= CAST (@DATE  AS DATE)   
									AND 	CAST (SALES_VOUCHER_USE_TS  AS DATE) &lt;= CAST (@TODATE  AS DATE)  
								GROUP BY  tenant_no, pos_cd, sales_voucher_id
							) maxUse	
					on 		us.tenant_no  = maxUse.tenant_no 
						AND us.pos_cd =  maxUse.pos_cd 
						AND us.sales_voucher_id  = maxUse.sales_voucher_id 
						AND us.sales_voucher_use_id  = maxUse.maxId
					where us.voucher_state  in (5) 
		
				) svIn
				ON 			svIn.tenant_no = sv.tenant_no
				AND 		svIn.pos_cd = sv.pos_cd
				AND 		svIn.sales_voucher_id = sv.sales_voucher_id
		left outer join sales_invs si on svIn.tenant_no = si.tenant_no and svIn.pos_cd = si.pos_cd and svIn.used_sales_inv_id = si.sales_inv_id
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
			ORDER BY 	 svIn.SALES_VOUCHER_USE_TS ASC
		</sql>
	</report-dataset>

	<report-dataset name="voucher_uses_in_other_pos">
		<sql>
		SELECT evu.external_voucher_use_ts  voucher_use_ts
		, sv.sales_voucher_ts voucher_ts
			,evu.EMPLOYEE_NM  employee_nm
			,evu.external_VOUCHER_CD  voucher_cd
			,evu.external_VOUCHER_VALUE voucher_value
			,coalesce(evu.CUSTOMER_FIRST_NM, '') || ' ' || evu.CUSTOMER_CONTACT_NM customer_nm
			,evu.used_sales_inv_cd sales_inv_cd
			,evu.voucher_state
			,evu.businessunit_nm businessunit_nm
		FROM  external_voucher_uses evu
		join 
		sales_vouchers sv
		on sv.tenant_no = evu.tenant_no
		AND sv.sales_voucher_cd = evu.external_voucher_cd
		
		where  sv.tenant_no = @TENANT_NO AND sv.pos_cd = @POS_CD 
		and cast (evu.external_voucher_use_ts as DATE) &gt;= cast
					(@DATE as DATE) and cast(evu.external_voucher_use_ts as DATE) &lt;= cast(@TODATE as DATE)
		and evu.cancel_for_external_voucher_use_id is null
		AND  evu.pos_cd  || ';' || cast(evu.external_voucher_use_id as char(10)) not in( 
				      SELECT  pos_cd  ||';' || cast(cancel_for_external_voucher_use_id as char(10))
				        FROM external_voucher_uses where  tenant_no = @TENANT_NO AND cancel_for_external_voucher_use_id IS NOT NULL )
		ORDER BY 	 evu.external_voucher_use_ts ASC
		</sql>
	</report-dataset>


	<report-dataset name="vouchers_in2">
		<sql>
			SELECT
			pp.external_voucher_use_ts payment_ts,
			pp.employee_nm cashier_nm,
			pp.external_VOUCHER_CD voucher_cd,
			pp.external_VOUCHER_VALUE payment,
			coalesce(pp.customer_first_nm,'') || ' ' || pp.customer_contact_nm customer_nm,		
			pp.used_sales_inv_cd sales_inv_cd
			
			from external_voucher_uses pp
			
			
			WHERE pp.tenant_no = @TENANT_NO AND pp.pos_cd = @POS_CD AND
			
			 cast (pp.external_voucher_use_ts as DATE) >= cast
			(@DATE as DATE) and cast(pp.external_voucher_use_ts as DATE) &lt;= cast(@TODATE as DATE)
		
			
			AND pp.cancel_for_external_voucher_use_id IS NULL
				AND  pp.pos_cd  || ';' || cast(external_voucher_use_id as char(10)) not in( 
				      SELECT  pos_cd  ||';' || cast(cancel_for_external_voucher_use_id as char(10))
				        FROM external_voucher_uses where  tenant_no = @TENANT_NO AND cancel_for_external_voucher_use_id IS NOT NULL )
			ORDER BY 	 pp.external_voucher_use_ts ASC
	
		</sql>
	</report-dataset>

</report>
