<?xml version="1.0"?>

<report name="cash-report" csv-renderer="KundenGeburtstagsliste.xsl">
	<title>Kundendaten</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="MONTH" type="integer" label="Monat">
		<preset>@MONTH</preset>
		
	</report-parameter>
	
	
	
	<report-result report-dataset="contact">
	</report-result>




	<report-dataset name="contact">
		<sql>
			select c.contact_no contact_no,
			case when coalesce(salutation,'' ) = '' THEN case when coalesce(cr.customer_group_no,-1)= 1 THEN 'Frau' ELSE  case when coalesce(cr.customer_group_no,-1)= 2 THEN 'Herr' ELSE  '' END END ELSE salutation END salutation,
			c.title title,
			c.first_nm first_nm,
			c.contact_nm contact_nm,
			c.street street,
			c.postal_cd postal_cd,
			c.city city,
			c.country_cd country_cd,
			c.phone phone,
			c.mobile mobile,
			c.email email,
			cr.comment comment,
			cr.birthday birthday
			
			from contacts c inner join customer_roles cr on c.tenant_no = cr.tenant_no and c.contact_no = cr.contact_no  
			
			where c.tenant_no=@TENANT_NO and @MONTH = coalesce(month(cr.birthday  ),14) AND coalesce(cr.unselectable,0) =0 AND company_no =@COMPANY_NO
			and coalesce(cr.customer_group_no,-1) = coalesce(@CUSTOMER_GROUP_NO,coalesce(cr.customer_group_no,-1))
			ORDER BY DAY( cr.birthday) ASC
		</sql>
	</report-dataset>



</report>

