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_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 -- Изменили здесь
SELECT '073'
UNION -- Изменили здесь
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 10000
) 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, 0, 0
FROM shuffled_numbers;
-- Удаляем временные таблицы
DROP TABLE numbers;
DROP TABLE shuffled_numbers;
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;
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 10000
) 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, 0, 0
FROM shuffled_numbers;
-- Удаляем временные таблицы
DROP TABLE numbers;
DROP TABLE shuffled_numbers;
END IF;
END|
delimiter ;
CALL check1 ();
Telegram: DB developers channel