SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `pbxu_autocallcampaings_shedule` ( `id` int(11) NOT NULL AUTO_INCREMENT, `autocallcampaing_id` int(11) NOT NULL DEFAULT '0', `insert_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `in_work` int(1) NOT NULL DEFAULT '0', `attempt_cnt` int(3) NOT NULL DEFAULT '0', `phone_numbers` varchar(400) NOT NULL DEFAULT '', `task_id` varchar(200) NOT NULL DEFAULT '', `task_name` varchar(400) NOT NULL DEFAULT '', `res` int(1) NOT NULL DEFAULT '0', `res_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `bill_sec` int(11) NOT NULL DEFAULT '0', `answer_cnt` int(11) NOT NULL DEFAULT '0', `listen_cnt` int(11) NOT NULL DEFAULT '0', `noanswer_cnt` int(11) NOT NULL DEFAULT '0', `busy_cnt` int(11) NOT NULL DEFAULT '0', `failed_cnt` int(11) NOT NULL DEFAULT '0', `run` int(1) NOT NULL DEFAULT '1', `stoplist_cnt` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `in_work` (`in_work`), KEY `res` (`res`), KEY `attempt_cnt` (`attempt_cnt`), KEY `res_date` (`res_date`), KEY `autocallcompany_id` (`autocallcampaing_id`), KEY `run` (`run`), KEY `phone_numbers` (`phone_numbers`(255)), KEY `task_id` (`task_id`) ) ENGINE=InnoDB AUTO_INCREMENT=24827083 DEFAULT CHARSET=utf8; CREATE TEMPORARY TABLE numbers ( id INT NOT NULL AUTO_INCREMENT, number VARCHAR(10) NOT NULL, PRIMARY KEY (id) ); INSERT INTO pbxu_autocallcampaings_shedule (id, autocallcampaing_id, insert_date, in_work, attempt_cnt, phone_numbers, task_id, task_name, res, res_date, bill_sec, answer_cnt, listen_cnt, noanswer_cnt, busy_cnt, failed_cnt, run, stoplist_cnt) SELECT n.id, 3, NOW(), 0, 1, n.number, 'task1', 'Test task 1', 0, '0000-00-00 00:00:00', 0, 0, 0, 0, 0, 0, 1, 0 FROM numbers AS n; -- Заполняем таблицу случайными номерами для оператора life INSERT INTO numbers (number) SELECT CONCAT(prefix, LPAD(FLOOR(RAND() * 10000000), 7, '0')) FROM ( SELECT '063' AS prefix UNION ALL SELECT '073' UNION ALL SELECT '093' ) AS prefixes CROSS JOIN ( SELECT 1 AS n FROM seq_1_to_100 ) AS numbers; -- Перемешиваем номера UPDATE numbers AS n JOIN ( SELECT n1.id, n2.number FROM numbers AS n1 JOIN numbers AS n2 ON n1.id <> n2.id JOIN ( SELECT n1.id, FLOOR(RAND() * (MAX(n2.id) - MIN(n2.id) + 1)) + MIN(n2.id) AS new_id FROM numbers AS n1 JOIN numbers AS n2 ) AS r ON n1.id = r.id ) AS t ON n.id = t.id SET n.number = t.number; -- Вставляем данные в таблицу pbxu_autocallcampaings_shedule INSERT INTO pbxu_autocallcampaings_shedule (id, autocallcampaing_id, insert_date, in_work, attempt_cnt, phone_numbers, task_id, task_name, res, res_date, bill_sec, answer_cnt, listen_cnt, noanswer_cnt, busy_cnt, failed_cnt, run, stoplist_cnt) SELECT id, 1, NOW(), 0, 1, number, 'task1', 'Test task 1', 0, '0000-00-00 00:00:00', 0, 0, 0, 0, 0, 0, 1, 0 FROM numbers; -- Удаляем временную таблицу DROP TABLE numbers; SELECT * FROM pbxu_autocallcampaings_shedule; select count(run) as run_count from pbxu_autocallcampaings_shedule; delimiter | CREATE PROCEDURE check1() BEGIN SELECT autocallcampaing_id, count(*) as run_count FROM pbxu_autocallcampaings_shedule WHERE autocallcampaing_id = 1 AND answer_cnt = 0 AND listen_cnt = 0 AND noanswer_cnt = 0 AND busy_cnt = 0 AND failed_cnt = 0; END| delimiter ; CALL check1 ();