<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="KassenSitzungDetail.xsl">
	<title>Inventur</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="DEPARTMENT" type="integer"
		hidden="true">
		<preset>@DEPARTMENT_NO</preset>
	</report-parameter>
	<report-parameter name="POSCD" type="string" hidden="true">
		<preset>@POS_CD</preset>
	</report-parameter>
	<report-parameter name="POSSESSIONID" type="integer">
		<preset>@POS_SESSION_ID</preset>
	</report-parameter>
	<report-parameter name="TYPE" type="integer">
		<preset>@TYPE</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="inventory-item-list">
	</report-result>

	<report-result report-dataset="pos-session">
	</report-result>

	<report-dataset name="pos-session">
		<sql>
	
SELECT 
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,
r_table.amountCustomer r_amountCustomer,
r_table.amountGoup1 r_amountGoup1,
r_table.amountGoup2 r_amountGoup2,
r_table.amountGoup3 r_amountGoup3,
r_table.r_total_g1 r_total_g1,
r_table.r_total_g2 r_total_g2,
r_table.r_total_g3 r_total_g3,
r_table.r_total r_total,
r_table.r_total_services r_total_services,
r_table.r_total_products r_total_products,
(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
(case when r_table.amountGoup1 = 0 then 0 else r_table.r_posCntDL_G1/r_table.amountGoup1 end) r_services_per_g1,
(case when r_table.amountGoup2 = 0 then 0 else r_table.r_posCntDL_G2/r_table.amountGoup2 end) r_services_per_g2,
(case when r_table.amountGoup3 = 0 then 0 else r_table.r_posCntDL_G2/r_table.amountGoup3 end) r_services_per_g2,
(case when r_table.amountGoup1 = 0 then 0 else r_table.r_total_g1/r_table.amountGoup1 end) r_total_per_g1,
(case when r_table.amountGoup2 = 0 then 0 else r_table.r_total_g2/r_table.amountGoup2 end) r_total_per_g2,
(case when r_table.amountGoup3 = 0 then 0 else r_table.r_total_g3/r_table.amountGoup3 end) r_total_per_g3


	
FROM(
select
			p.employee_no r_employee_no,
			p.employee_nm r_employee_nm,
			COUNT (  DISTINCT (cast(sales_inv_ts AS DATE))) r_cnt_dt,
			COUNT (  DISTINCT p.sales_inv_id) amountCustomer,
			
			COUNT (  DISTINCT (case WHEN coalesce(customer_group_no, -1) = 1 THEN p.sales_inv_id ELSE NULL END)) amountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(customer_group_no, -1) = 2 THEN p.sales_inv_id ELSE NULL END)) amountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(customer_group_no, -1) = 3 THEN p.sales_inv_id ELSE NULL END)) amountGoup3,
			sum(case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end) r_total,
			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) r_total_services,
			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) r_total_products,
			sum(case when coalesce(h.customer_group_no, -1) = 1 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g1,
			sum(case when coalesce(h.customer_group_no, -1) = 2 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g2,

			sum(case when coalesce(h.customer_group_no, -1) = 3 then case when h.sales_inv_type=2 then -p.position_gross_price else p.position_gross_price end else 0 end ) r_total_g3,

sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, -1) = 1 then case when h.sales_inv_type=2 then 0 else 1 end else 0 end else 0 end) r_posCntDL_G1,
sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, -1) = 2 then case when h.sales_inv_type=2 then 0 else 1 end else 0 end else 0 end) r_posCntDL_G2,
sum(case when coalesce(p.stockable, 0)=0 then case when coalesce(h.customer_group_no, -1) = 3 then case when h.sales_inv_type=2 then 0 else 1 end else 0 end else 0 end) r_posCntDL_G3
			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 where tenant_no=1 and pos_cd='K-2-1' )
			  
			   GROUP BY p.employee_no,p.employee_nm
			   ) r_table
		</sql>

	</report-dataset>

	<report-dataset name="sales-inv-1">
		<sql>
			select customer_first_nm, customer_contact_nm, total_gross_price, sales_inv_cd
	        from sales_invs h
	        where h.sales_inv_id in 
	         (
	           select distinct sales_inv_id 
	           from pos_payments 
	           where tenant_no= @TENANT_NO 
	             and pos_cd= @POS_CD 
	             and pos_session_id = @POS_SESSION_ID
	             AND sales_inv_id IS NOT NULL 
	             AND payment_type = 1 

				 
			  )

		</sql>

	</report-dataset>





</report>
