SELECT Customer.customerId,
 Customer.customerNr,
 Customer.spcsDealNr,
 Customer.customerName,
 CustomerContact.name,
 CustomerContact.phone1,
 Customer.objectCity,
 MAX(TheLastDate.regdate) AS regdate,
 (SELECT MAX(regdate) FROM jgl_order WHERE jgl_order.alarmsystemid = CustomerFilesFields.value_int GROUP BY jgl_order.alarmsystemid) AS TheLastDateYes
	FROM jgl_customerFiles AS CustomerFiles
	INNER JOIN jgl_customer AS Customer ON CustomerFiles.customerId = Customer.customerId
	LEFT OUTER JOIN jgl_customerContact AS CustomerContact ON CustomerContact.customerId = Customer.customerId AND CustomerContact.defaultCustomerContact = 1
	INNER JOIN jgl_customerFilesFields AS CustomerFilesFields ON CustomerFiles.customerFileId = CustomerFilesFields.customerFileId AND CustomerFilesFields.field = 'dealAlarmSystemId'
	INNER JOIN jgl_customerFilesFields AS OptionFields ON CustomerFiles.customerFileId = OptionFields.customerFileId AND OptionFields.field = 'dealalarm_options'
	LEFT OUTER JOIN jgl_order AS TheLastDate ON TheLastDate.alarmsystemid = CustomerFilesFields.value_int AND TheLastDate.revisionDone = 1
	LEFT OUTER JOIN jgl_customerRevision As CustomerRevision ON CustomerRevision.customerId = Customer.customerId AND CustomerRevision.status = 'inactive'
		WHERE
		CustomerFiles.softType = 'dealalarm'
		AND (CONCAT(Customer.customerNr, ':',Customer.spcsDealNr) LIKE '%%')
		AND Customer.customerName LIKE '%%'
		AND Customer.objectCity LIKE '%%'
		AND Customer.customerName NOT LIKE '%XXX%'
		AND ((
			TheLastDate.regdate < (NOW() - INTERVAL 1 YEAR)
			AND (OptionFields.value LIKE '2;%' OR OptionFields.value LIKE ';2;%')
		    )
		OR (
			TheLastDate.regdate < (NOW() - INTERVAL 3 YEAR)
			AND (OptionFields.value LIKE '11;%' OR OptionFields.value LIKE ';11;%')
		   )
		OR TheLastDate.regdate IS NULL
		)
		AND ((OptionFields.value LIKE '11;%' OR OptionFields.value LIKE ';11;%') OR (OptionFields.value LIKE '2;%' OR OptionFields.value LIKE ';2;%'))
		AND CustomerRevision.customerRevisionId IS NULL ORDER BY TheLastDate.regdate
GROUP BY CustomerFilesFields.value_int