<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="Kundenkarten.xsl">
	<title>Kundenkarten</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="contact">
	</report-result>
	<report-result report-dataset="businessunits">
	</report-result>

	<report-result report-dataset="ccc">
	</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="ccc">
		<sql>
			select ccc.valid_from,
			ccc.valid_to,
			ccc.card_no,
			cond.sales_pricelist_id,
			cond.item_condition_cd,
			cond.sales_credit_cd,
			sp.sales_pricelist_nm,
			cond.condition_nm
			from customer_contract_conditions ccc 
			left join   (
			   SELEct c.condition_nm condition_nm
			         ,case when bc.condition_cd is null then mc.tenant_no ELSE bc.tenant_no END tenant_no
                     ,case when bc.condition_cd is null then mc.company_no ELSE bc.company_no END company_no
	                 ,case when bc.condition_cd is null then mc.department_no ELSE bc.department_no END department_no
	                 ,case when bc.condition_cd is null then mc.condition_cd ELSE bc.condition_cd END condition_cd
	                 ,case when bc.condition_cd is null then mc.market_no ELSE bc.market_no END market_no
	                 ,case when bc.condition_cd is null then mc.sales_pricelist_id ELSE bc.sales_pricelist_id END sales_pricelist_id
	                 ,case when bc.condition_cd is null then mc.item_condition_cd ELSE bc.item_condition_cd END item_condition_cd
	                 ,case when bc.condition_cd is null then mc.sales_credit_cd ELSE bc.sales_credit_cd END sales_credit_cd
                  from 
                      conditions c
           left  join businessunit_conditions bc
                   on bc.tenant_no = c.tenant_no 
                  AND bc.condition_cd = c.condition_cd
                  AND bc.businessunit_no = @BUSINESSUNIT_NO
            left join market_conditions mc
                   on mc.tenant_no = c.tenant_no 
                  AND mc.condition_cd = c.condition_cd
                WHERE (case when bc.condition_cd is null then mc.condition_cd ELSE bc.condition_cd END) is not null
			  
			  ) cond
			  on cond.tenant_no = ccc.tenant_no 
			  AND cond.company_no = ccc.company_no
			  AND cond.department_no = ccc.department_no
			  AND cond.condition_cd = ccc.condition_cd
			  
			left join sales_pricelists sp on cond.tenant_no = sp.tenant_no and cond.sales_pricelist_id = sp.sales_pricelist_id
			where ccc.tenant_no=@TENANT_NO and ccc.company_no=@COMPANY_NO and ccc.customer_no=@CUSTOMER_NO and (cast(ccc.valid_to as date) >= cast(@NOW as DATE) or ccc.valid_to is null) 
			GROUP BY ccc.valid_from,
			ccc.valid_to,
			ccc.card_no,
			cond.sales_pricelist_id,
			cond.item_condition_cd,
			cond.sales_credit_cd,
			sp.sales_pricelist_nm
			,cond.condition_nm
			order by ccc.valid_from DESC
		</sql>
	</report-dataset>
	
	
	<report-dataset name="ccc_alt">
		<sql>
			select ccc.valid_from,
			ccc.valid_to,
			ccc.card_no,
			ccc.sales_pricelist_id,
			ccc.item_condition_cd,
			ccc.sales_credit_cd,
			sp.sales_pricelist_nm,
			sum (coalesce (scb.sales_credit_points,0))sales_credit_points
			from customer_contract_conditions ccc 
			left join   (
			   SELEct case when bc.condition_cd is null then mc.tenant_no ELSE bc.tenant_no END tenant_no
                     ,case when bc.condition_cd is null then mc.company_no ELSE bc.company_no END company_no
	                 ,case when bc.condition_cd is null then mc.department_no ELSE bc.department_no END department_no
	                 ,case when bc.condition_cd is null then mc.condition_cd ELSE bc.condition_cd END condition_cd
	                 ,case when bc.condition_cd is null then mc.market_no ELSE bc.market_no END market_no
	                 ,case when bc.condition_cd is null then mc.sales_pricelist_id ELSE bc.sales_pricelist_id END sales_pricelist_id
	                 ,case when bc.condition_cd is null then mc.item_condition_cd ELSE bc.item_condition_cd END item_condition_cd
	                 ,case when bc.condition_cd is null then mc.sales_credit_cd ELSE bc.sales_credit_cd END sales_credit_cd
                  from 
                      conditions c
           left  join businessunit_conditions bc
                   on bc.tenant_no = c.tenant_no 
                  AND bc.condition_cd = c.condition_cd
                  AND bc.businessunit_no = @BUSINESSUNIT_NO
            left join market_conditions mc
                   on mc.tenant_no = c.tenant_no 
                  AND mc.condition_cd = c.condition_cd
                WHERE coalesce (bc.condition_cd,mc.condition_cd) is not null
			  
			  ) cond
			  on cond.tenant_no = ccc.tenant_no 
			  AND cond.company_no = ccc.company_no
			  AND cond.department_no = ccc.department_no
			  AND cond.condition_cd = ccc.condition_cd
			  
			left join sales_pricelists sp on cond.sales_pricelist_id = sp.sales_pricelist_id
			left join sales_credit_balance scb on cond.sales_credit_cd = scb.sales_credit_cd and ccc.customer_no = scb.customer_no and ccc.customer_contract_no = scb.customer_contract_no and ccc.valid_from = scb.valid_from
			where ccc.tenant_no=@TENANT_NO and ccc.company_no=@COMPANY_NO and ccc.customer_no=@CUSTOMER_NO and (cast(ccc.valid_to as date) >= cast(@NOW as DATE) or ccc.valid_to is null) 
			GROUP BY ccc.valid_from,
			ccc.valid_to,
			ccc.card_no,
			ccc.sales_pricelist_id,
			ccc.item_condition_cd,
			ccc.sales_credit_cd,
			sp.sales_pricelist_nm
			order by ccc.valid_from DESC
		</sql>
	</report-dataset>
	
	
	<report-dataset name="contact">
		<sql>
			select c.contact_no,
			c.first_nm,
			c.contact_nm,
			c.street,
			c.postal_cd,
			c.city,
			c.country_cd,
			c.phone,
			c.mobile,
			c.email,
			cr.comment,
			cr.birthday,
			cg.customer_group_nm
			from contacts c inner join customer_roles cr on c.contact_no = cr.contact_no and c.tenant_no = cr.tenant_no
			inner join customer_groups cg on cr.customer_group_no = cg.customer_group_no and cr.tenant_no = cg.tenant_no
			where c.tenant_no=@TENANT_NO and c.contact_no=@CONTACT_NO
		</sql>
	</report-dataset>




</report>

