DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `findJobs`(IN `adrss` VARCHAR(200), IN `blder` VARCHAR(155), IN `sburb` VARCHAR(40), IN `pc` INT, IN `pllttype` VARCHAR(30))
BEGIN

    SELECT * from jobs 
    WHERE address = adrss AND builder = blder AND suburb = sburb AND postcode = pc AND pallettype = pllttype LIMIT 1;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getMergedJobs`(
    IN adrss VARCHAR(200),
    IN blder VARCHAR(155),
    IN sburb VARCHAR(40),
    IN pc INT,
    IN pllttype VARCHAR(30)
)
BEGIN
	
    SELECT * from `tempjobs` 
    WHERE `address` = adrss AND `builder` = blder AND `suburb` = sburb AND `postcode` = pc AND `pallettype` = pllttype;

END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `readDistinctTempJobsAndSumQTY`(IN `uid` INT)
BEGIN
	SELECT DISTINCT 
orderid,
orderno,
SUM(quantity) AS quantity,
SUM(remaining) AS remaining,
builder,
contactname,
contactno,
address,
suburb,
postcode,
orderdate,
map,
uploadedjobid,
appointmentdate,
producttype,
priority,
booked,
priorityisflag,
completed,
deleted,
temppriority,
jobclassification,
callstatus,
area,
rate,
prioritylock,
driverassigned,
siteinstruction,
merged,
deliverydocket,
xyard,
uploadeddate,
pallettype,
visited,
clientid,
deliverydate,
emailsent,
visitedcount,
callinstartdate,
callin,
callincount,
callinstatus,
timelinestatus,
timelinedate, COUNT(*) as repetitions from tempjobs where uploadedjobid = uid GROUP BY address, suburb, postcode, pallettype, builder HAVING repetitions >= 1  ORDER BY address ASC;

END$$
DELIMITER ;
