<?xml version="1.0"?>

<report name="cash-report" csv-renderer="VoucherStatisticsCanceledCSV.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_storno">
	</report-result>

	<report-result report-dataset="sales_voucher_in_storno">
	</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_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
			,c.contact_no
			,c.street
			,c.city
			,c.postal_cd
			,c.phone
			,c.email
			,c.mobile
			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
		LEFT JOIN contacts c on si.tenant_no = c.tenant_no and si.customer_no = c.contact_no
	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
			,c.contact_no
			,c.street
			,c.city
			,c.postal_cd
			,c.phone
			,c.email
			,c.mobile
			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
		LEFT JOIN contacts c on si.tenant_no = c.tenant_no and si.customer_no = c.contact_no
	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
			,c.contact_no
			,c.street
			,c.city
			,c.postal_cd
			,c.phone
			,c.email
			,c.mobile
			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
		LEFT JOIN contacts c on si.tenant_no = c.tenant_no and si.customer_no = c.contact_no
	where 
			sv.tenant_no = @TENANT_NO
	AND		sv.pos_cd = @POS_CD
		ORDER BY 	 svIn.SALES_VOUCHER_USE_TS ASC		
		</sql>
	</report-dataset>

</report>
