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 ();
Telegram: DB developers channel