queue.sql

-- ----------------------------
-- View structure for `report_jobs`
-- ----------------------------
DROP VIEW IF EXISTS `report_jobs`;
CREATE
	ALGORITHM=UNDEFINED DEFINER=`icheckma_dbo`@`localhost`
	SQL SECURITY DEFINER
	VIEW `report_jobs` AS
		SELECT `report_jobs`.`subject_id`  AS `report`,
			`reports`.`user_id` AS `user`,
			`report_jobs`.`uuid` AS `job`,
			`report_jobs`.`task_id` AS `task`,
			`job_tasks`.`handler` AS `handler`,
			`job_tasks`.`label` AS `task_label`,
			`job_tasks`.`is_enabled` AS `task_enabled`,
			`report_jobs`.`created` AS `created`,
			`report_jobs`.`is_complete` AS `completed`
		FROM (
			`job_tasks` LEFT JOIN (
				`reports` LEFT JOIN `job_queue` `report_jobs` ON (
					(`report_jobs`.`subject_id` = `reports`.`id` )
				)
			) ON (
				(`report_jobs`.`task_id` = `job_tasks`.`id`)
			)
		)
		WHERE (
			`report_jobs`.`is_complete` = 0
		) ORDER  BY
			`report_jobs`.`created`,
			`report_jobs`.`task_id`;  

-- ----------------------------
-- Procedure structure for `next_report`
-- ----------------------------
DROP PROCEDURE IF EXISTS `next_report`;
DELIMITER ;;
CREATE DEFINER=`icheckma_dbo`@`localhost` PROCEDURE `next_report`(out ireport INT(11))
begin
	declare num_rows INT DEFAULT 0;
	declare output_id BIGINT(20) UNSIGNED DEFAULT 0 ;
	declare jobs_cur CURSOR for select
		`report`.`id` as `id`
		from (select
			`job_queue`.`subject_id` as `id`
			from `job_queue`
				where (`is_complete` = 0)
			order by `created` asc) `report`
		limit 1;

	OPEN jobs_cur;
	select FOUND_ROWS() into num_rows;
	if num_rows > 0 then
		fetch jobs_cur into output_id;
	end if;
	set ireport = output_id;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for `reset_development`
-- ----------------------------
DROP PROCEDURE IF EXISTS `reset_development`;
DELIMITER ;;
CREATE DEFINER=`icheckma_dbo`@`localhost` PROCEDURE `reset_development`()
begin
	TRUNCATE job_queue;
	TRUNCATE user_scores;
	TRUNCATE user_profiles;
	TRUNCATE user_meta;
	TRUNCATE reports;
	TRUNCATE payment_logs;
	TRUNCATE merlin_results;
	TRUNCATE user_photos;
	delete from users;
	alter table users AUTO_INCREMENT=1;
end
;;
DELIMITER ;