vrsteel.sql

SELECT 
	t1.`id`, 
	t1.`uid`, 
	t1.`proticketid`, 
	t1.`subject`, 
	t1.`created`, 
	t1.`lastreply`, 
	t1.`status`, 
	t1.`ticketnoti`, 
	t1.`topic` 
FROM 
	(
		SELECT 
			ticket.`id`, 
			ticket.`uid`, 
			ticket.`proticketid`, 
			ticket.`subject`, 
			ticket.`created`, 
			ticket.`lastreply`, 
			ticket.`status`, 
			(
				(
					select 
						count(*) 
					from 
						wp_js_ticket_replies as r 
					where 
						r.`ticketid` = ticket.`id` 
						and r.`admin_is_read` = 0
				) + (
					CASE WHEN ticket.isanswered = 1 THEN 0 ELSE 1 END
				)
			) as ticketnoti, 
			1 as topic 
		FROM 
			`wp_js_ticket_tickets` AS ticket 
		WHERE 
			1 = 1 
			AND ticket.`status` IN (0, 1, 3, 4, 6)
	) AS t1 
WHERE 
	t1.`ticketnoti` > 0 
UNION ALL 
SELECT 
	t2.`id`, 
	t2.`uid`, 
	t2.`proticketid`, 
	t2.`subject`, 
	t2.`created`, 
	t2.`lastreply`, 
	t2.`status`, 
	t2.`ticketnoti`, 
	t2.`topic` 
FROM 
	(
		SELECT 
			ticket.`id`, 
			ticket.`uid`, 
			ticket.`proticketid`, 
			ticket.`subject`, 
			ticket.`created`, 
			ticket.`lastreply`, 
			ticket.`status`, 
			(
				(
					select 
						count(*) 
					from 
						wp_js_ticket_replies as r 
					where 
						r.`ticketid` = ticket.`id` 
						and r.`admin_is_read` = 0
				) + (
					CASE WHEN ticket.isanswered = 1 THEN 0 ELSE 1 END
				)
			) as ticketnoti, 
			2 as topic 
		FROM 
			`wp_js_ticket_tickets` AS ticket 
		WHERE 
			1 = 1 
			AND ticket.`status` IN (0, 1, 3, 4, 6)
	) AS t2 
WHERE 
	t2.`ticketnoti` = 0 
ORDER BY 
	`topic`, 
	CASE WHEN `topic` = 1 THEN CASE WHEN lastreply > created THEN lastreply WHEN lastreply < created THEN created END ELSE 1 END DESC, 
	CASE WHEN `topic` = 2 THEN `status` ELSE 1 END, 
	`ticketnoti` DESC, 
	CASE WHEN `topic` = 1 THEN `status` ELSE 1 END, 
	CASE WHEN `topic` = 2 THEN CASE WHEN lastreply > created THEN lastreply WHEN lastreply < created THEN created END ELSE 1 END DESC 
LIMIT 
	0, 100