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; -- Создаем таблицу pbxu_autocallcampaings_shed_tel CREATE TABLE pbxu_autocallcampaings_shed_tel ( -- Определяем поля и их типы shedule_id INT(11) NOT NULL, cnt INT(11) NOT NULL, phone VARCHAR(50) NOT NULL, attempt_cnt INT(11) NOT NULL DEFAULT 0, autocallcampaing_id INT(11) NOT NULL DEFAULT 0, -- Определяем первичный ключ PRIMARY KEY (shedule_id, cnt), -- Определяем индексы INDEX (attempt_cnt), INDEX (autocallcampaing_id) ); delimiter | CREATE PROCEDURE check1() BEGIN DECLARE run_count INT; SELECT count(*) INTO 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; IF run_count < 1000 THEN -- Предположим, что таблица pbxu_autocallcampaings_shedule уже существует -- Создаем временную таблицу numbers CREATE TEMPORARY TABLE numbers ( id INT NOT NULL AUTO_INCREMENT, number VARCHAR(10) NOT NULL, PRIMARY KEY (id) ); -- Заполняем таблицу случайными номерами для оператора life INSERT INTO numbers (number) SELECT CONCAT_WS('', prefix, LPAD(FLOOR(RAND() * 10000000), 7, '0')) FROM ( SELECT '063' AS prefix UNION ALL SELECT '073' UNION ALL SELECT '093' ) AS prefixes CROSS JOIN ( -- Используем подзапрос для генерации последовательности от 1 до 30000 SELECT @row := @row + 1 AS n FROM ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) t1, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) t2, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) t3, ( SELECT @row := 0 ) t4 LIMIT 3 ) AS numbers; -- Создаем временную таблицу shuffled_numbers CREATE TEMPORARY TABLE shuffled_numbers ( id INT NOT NULL, number VARCHAR(10) NOT NULL, PRIMARY KEY (id) ); -- Перемешиваем номера INSERT INTO shuffled_numbers (id, number) SELECT @row := @row + 1 AS id, number FROM ( SELECT number FROM numbers ORDER BY RAND() ) AS shuffled JOIN ( SELECT @row := 0 ) AS r; -- Вставляем данные в таблицу pbxu_autocallcampaings_shedule INSERT INTO pbxu_autocallcampaings_shedule (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 1, NOW(), 0, 0, number, 'task1', 'Test task 1', 0, '0000-00-00 00:00:00', 0, 0, 0, 0, 0, 0, 1, 0 FROM shuffled_numbers; -- Вставляем данные в таблицу pbxu_autocallcampaings_shed_tel -- Для этого используем LAST_INSERT_ID(), чтобы получить последний сгенерированный id в pbxu_autocallcampaings_shedule -- И соединяем таблицы по номеру телефона INSERT INTO pbxu_autocallcampaings_shed_tel (shedule_id, cnt, phone, attempt_cnt, autocallcampaing_id) SELECT LAST_INSERT_ID(), 0, s.number, 0, 1 FROM shuffled_numbers s JOIN pbxu_autocallcampaings_shedule p ON s.number = p.phone_numbers; -- Удаляем временные таблицы DROP TABLE numbers; DROP TABLE shuffled_numbers; END IF; END| delimiter ; CALL check1 ();