/**
 * This query will build up the 3 temporary tables,
 * and the runner will then query these tables.
 */
 
-- Create all the temporary tables we need...
CREATE TEMPORARY TABLE IF NOT EXISTS `nacl_crosstab_1` (
  `JOB_NO` VARCHAR(20) NOT NULL,
  `NACL_DATE` DATE,
  PRIMARY KEY (`JOB_NO`)
);
 
CREATE TEMPORARY TABLE IF NOT EXISTS `nacl_crosstab_2` (
  `JOB_NO` VARCHAR(20) NOT NULL,
  `NACL_DATE` DATE,
  PRIMARY KEY (`JOB_NO`)
);
 
CREATE TEMPORARY TABLE IF NOT EXISTS `nacl_crosstab_3` (
  `JOB_NO` VARCHAR(20) NOT NULL,
  `NACL_DATE` DATE,
  PRIMARY KEY (`JOB_NO`)
);
 
-- Ensure they're empty before we insert.
TRUNCATE TABLE `nacl_crosstab_1`;
TRUNCATE TABLE `nacl_crosstab_2`;
TRUNCATE TABLE `nacl_crosstab_3`;
 
-- Insert the data we want into these temp tables.
INSERT INTO `nacl_crosstab_1` (`JOB_NO`, `NACL_DATE`)
SELECT r.JOB_NO, MIN(l.`DATE`)
FROM repairs r
INNER JOIN nacllog l ON (l.`JOB_NO` = r.`JOB_NO`)
WHERE (l.`REASON` = 'C')
	OR (l.`REASON` = '')
	OR (l.`REASON` IS NULL)
GROUP BY r.`JOB_NO`;
 
INSERT INTO `nacl_crosstab_2` (`JOB_NO`, `NACL_DATE`)
SELECT c.JOB_NO, MIN(l.`DATE`)
FROM nacl_crosstab_1 c
INNER JOIN nacllog l ON (l.`JOB_NO` = c.`JOB_NO`)
	AND (l.`DATE` > c.`NACL_DATE`)
WHERE (l.`REASON` = 'C')
	OR (l.`REASON` = '')
	OR (l.`REASON` IS NULL)
GROUP BY c.`JOB_NO`;
 
INSERT INTO `nacl_crosstab_3` (`JOB_NO`, `NACL_DATE`)
SELECT c.JOB_NO, MIN(l.`DATE`)
FROM nacl_crosstab_2 c
INNER JOIN nacllog l ON (l.`JOB_NO` = c.`JOB_NO`)
	AND (l.`DATE` > c.`NACL_DATE`)
WHERE (l.`REASON` = 'C')
	OR (l.`REASON` = '')
	OR (l.`REASON` IS NULL)
GROUP BY
	c.`JOB_NO`;
 
-- Finally, get the results in a usable format.
SELECT
	r.JOB_NO AS `CURRENT_JOB`,
	r.`COMP_DATE` AS `COMPLETED`,
	r.`HOUSE_KEY`,
	TRIM(h.`HOUSENO`) AS `HOUSENO`,
	h.`ROAD`,
	h.`DISTRICT`,
	h.`POSTCODE`,
	MAX(r2.`COMP_DATE`) AS `Latest Service`,
	DATE_FORMAT(MAX(v.`VISIT_END`), '%Y-%m-%d') AS `Last Visit`,
	MAX(n.`DATE`) AS `Last NACL`,
	n1.`NACL_DATE` AS `NACL_1`,
	n2.`NACL_DATE` AS `NACL_2`,
	n3.`NACL_DATE` AS `NACL_3`
FROM
	repairs r
LEFT OUTER JOIN rep_visits v1 ON (v1.`JOB_NO` = r.`JOB_NO`)
    AND (v1.`VISIT_END` BETWEEN :startdate AND :stopdate)
INNER JOIN
	house h ON (h.`HOUSE_KEY` = r.`HOUSE_KEY`)
LEFT OUTER JOIN
	repairs r2 ON (r2.`HOUSE_KEY` = r.`HOUSE_KEY`)
		AND (r2.`CALL_TYPE` = r.`CALL_TYPE`)
		AND (r2.`WORK_COMP` != 'X')
LEFT OUTER JOIN
	rep_visits v ON (v.`JOB_NO` = r2.`JOB_NO`)
LEFT OUTER JOIN
    `nacllog` n ON (n.`JOB_NO` = r.`JOB_NO`)
LEFT OUTER JOIN
	`nacl_crosstab_1` n1 ON (n1.`JOB_NO` = r.`JOB_NO`)
LEFT OUTER JOIN
	`nacl_crosstab_2` n2 ON (n2.`JOB_NO` = r.`JOB_NO`)
LEFT OUTER JOIN
	`nacl_crosstab_3` n3 ON (n3.`JOB_NO` = r.`JOB_NO`)
WHERE
    (r.`CALL_TYPE` = 'PM')
	AND (r.`WORK_COMP` != 'X')
	AND (
	    (v1.`JOB_NO` IS NOT NULL)
	    OR (n1.`JOB_NO` IS NOT NULL)
	    OR (n2.`JOB_NO` IS NOT NULL)
	    OR (n3.`JOB_NO` IS NOT NULL)
    )
    AND (r.HOUSE_KEY != '')
GROUP BY
	r.`JOB_NO`
ORDER BY
	h.`ROAD`, h.HOUSENO