By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601446 fiddles created (47995 in the last week).
SET SQL_MODE='ALLOW_INVALID_DATES';
CREATE TABLE `wtb_tokens` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'User name',
`orderid` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '-1 for preview channel tokens',
`token` varchar(128) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Hashed tokens',
`token_clean` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`is_expired` tinyint(1) NOT NULL DEFAULT '0',
`disconnect_time` float NOT NULL DEFAULT '0',
`encoder` tinyint(1) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expires` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`service_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'to link the token with a service for preview channels',
`video_id` int(11) NOT NULL COMMENT 'to link the token with a a video for preview',
PRIMARY KEY (`id`),
UNIQUE KEY `index_token` (`token`),
KEY `index_orderid` (`orderid`),
KEY `index_username` (`username`)
);
✓
✓
hidden batch(es)
CREATE TABLE `wtb_orders` (
`orderid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`username` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`msisdn` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`order_type` varchar(999) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Subscription',
`bundle_id` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`service_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`object_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`pkg_id_fk` int(11) DEFAULT NULL,
`app_id` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`balance` float NOT NULL,
`cost` float NOT NULL DEFAULT '0',
`is_paid` int(1) NOT NULL DEFAULT '0',
`is_renew` tinyint(1) NOT NULL DEFAULT '1',
`is_test` tinyint(1) NOT NULL DEFAULT '0',
`duration` int(11) NOT NULL DEFAULT '0',
`extended_duration` int(11) NOT NULL DEFAULT '0',
`disconnect_time` int(11) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`paid_on` timestamp NULL DEFAULT NULL,
`was_renewed` tinyint(1) NOT NULL DEFAULT '0',
`renew_orderid` varchar(50) DEFAULT NULL,
`renewed_at` varchar (50) NOT NULL,
`generated_by` varchar(100),
`gift_to` varchar(125),
`price_id` int(11) NOT NULL DEFAULT '0',
`renewal_price_id` int(11) NOT NULL DEFAULT '0',
`renewal_pkg_id` int(11) NOT NULL DEFAULT '0',
`bypass` tinyint(1) NOT NULL DEFAULT '0',
`bypass_reason` text,
`reset_access` tinyint(1) NOT NULL DEFAULT '0',
`extend_access` tinyint(1) NOT NULL DEFAULT '0',
`is_revoked` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`orderid`),
KEY `service_id_index` (`service_id`),
KEY `index_username` (`username`),
KEY `wtb_orders_paid_index` (`is_paid`,`bundle_id`),
KEY `prices_bundle_id_index` (`bundle_id`)
);
SELECT * FROM (
SELECT
wtb_orders.orderid,
wtb_orders.duration,
wtb_orders.is_renew,
wtb_orders.bundle_id,
wtb_orders.service_id,
wtb_orders.object_name,
wtb_orders.pkg_id_fk,
expires,
wtb_tokens.disconnect_time
FROM wtb_orders
JOIN wtb_tokens ON wtb_orders.orderid=wtb_tokens.orderid
WHERE
wtb_orders.username = "96190000002" AND
(
(
((expires + INTERVAL 5 DAY) >= NOW())
OR
(expires=0 AND wtb_tokens.orderid IS NOT NULL)
) OR
expires=0
)
ORDER by expires DESC LIMIT 1000000
) as tmp
GROUP BY
tmp.pkg_id_fk,
tmp.bundle_id,
tmp.service_id
UNION
(
SELECT ord.orderid,ord.duration,ord.is_renew,ord.bundle_id,ord.service_id,ord.object_name,ord.pkg_id_fk,tk.expires,tk.disconnect_time FROM wtb_orders AS ord
JOIN wtb_tokens AS tk ON ord.orderid=tk.orderid
WHERE tk.username= "96190000002"
AND (((tk.expires >= NOW())
OR (tk.expires=0 AND tk.orderid IS NOT NULL))
OR tk.expires=0)
)
