· 6 years ago · Jul 02, 2019, 06:36 PM
1DROP TABLE IF EXISTS aheevaccs.`pm_maya_ops_hro_canada_anglophone_1906`;
2CREATE TABLE IF NOT EXISTS aheevaccs.`pm_maya_ops_hro_canada_anglophone_1906` (
3
4 `RECORD_ID` int(11) NOT NULL AUTO_INCREMENT,
5 `PHONE_TYPE` varchar(10) DEFAULT NULL,
6 `AGENT_ID` varchar(10) DEFAULT NULL,
7 `RECORD_STATUS` varchar(30) NOT NULL DEFAULT 'Ready',
8 `CALL_RESULT` varchar(25) DEFAULT NULL,
9 `RECORD_TYPE` varchar(20) NOT NULL DEFAULT 'General',
10 `daily_from` varchar(4) NOT NULL DEFAULT '0001',
11 `daily_to` varchar(4) NOT NULL DEFAULT '2359',
12 `call_time` datetime DEFAULT NULL,
13 `CAMPAIGN_name` varchar(50) DEFAULT NULL,
14 `CHAIN` int(11) NOT NULL DEFAULT '0',
15 `DIAL_SCHED_TIME` datetime DEFAULT NULL,
16 `ALTERNATE_CALL_BACK_PHONE` varchar(30) DEFAULT NULL,
17 `TIME_ZONE` char(4) DEFAULT NULL,
18 `extraData` text,
19 `RESULT_CODE` char(2) DEFAULT NULL,
20 `REFUSAL_REASON_CODE` char(3) DEFAULT NULL,
21 `DO_NOT_CALL` tinyint(1) DEFAULT '0',
22 `END_TIME` datetime DEFAULT NULL,
23 `CUSTOMER_TYPE` varchar(5) DEFAULT NULL,
24 `TRACKNUM` varchar(50) DEFAULT NULL,
25 `TRANSFER` varchar(5) DEFAULT NULL,
26 `COMPLETED` varchar(5) DEFAULT NULL,
27 `LISTEN_LEVEL` varchar(50) DEFAULT NULL,
28 `PHONE_OWNER` varchar(5) DEFAULT NULL,
29 `LANGUAGE_TYPE` char(2) DEFAULT NULL,
30 `attempts` int(11) DEFAULT '0',
31 `COMMENTS` text,
32 `PHONE_NUMBER` varchar(30) NOT NULL DEFAULT '',
33 `CALLBACK_NUMBER` varchar(30) NOT NULL DEFAULT '',
34 `DIAL_TREATMENT_TIME` datetime DEFAULT NULL,
35 `CANCELED_AGENT_ID` varchar(10) DEFAULT NULL,
36 `CANCELED_STATE` varchar(20) DEFAULT NULL,
37 `id_succ` varchar(255) DEFAULT '',
38 `ID` varchar(255) DEFAULT '',
39 `QUALITE_LIGNE` char(1) default NULL,
40 `PROBLEME_LIGNE` varchar(20) default NULL,
41 `correction` tinyint(4) NOT NULL default '0',
42 `valide` tinyint(4) NOT NULL default '0',
43 PRIMARY KEY (`RECORD_ID`,`PHONE_NUMBER`),
44 KEY `PHONE_NUMBER` (`PHONE_NUMBER`),
45 KEY `m_record_id` (`RECORD_ID`),
46 KEY `status_type_chain` (`RECORD_STATUS`,`RECORD_TYPE`,`CHAIN`),
47 KEY `agent_id` (`AGENT_ID`),
48 KEY ID (ID),
49 KEY id_succ(id_succ)
50) ENGINE=InnoDB DEFAULT CHARSET=utf8;
51
52
53REPLACE aheevaccs.pm_maya_ops_hro_canada_anglophone_1906( PHONE_NUMBER , id , id_succ , extraData )
54SELECT
55 CONCAT(call_id, '1', phone_office) phone_office,
56 a.id,
57 a.id_succ,
58 'default:http://panoramix/scriptops/?list_name=pm_maya_ops_hro_canada_anglophone_1906&LANG=Fr'
59FROM (
60 SELECT
61 a.id,
62 a.id_succ,
63 a.phone_office ,
64 cmp.call_id,
65 a.annual_revenue,
66 a.annual_revenue_group,
67 a.employees,
68 a.eff_group
69 FROM percycrm.accounts a
70 JOIN percycrm.campagne_ops cmp ON cmp.id = a.id_campagne
71 WHERE
72 a.id_campagne='hro_canada_anglophone_1906'
73 AND
74 a.phone_office <> ''
75 AND
76 a.phone_office IS NOT NULL
77 GROUP BY a.phone_office
78) a
79ORDER BY RAND();
80
81
82CREATE TABLE IF NOT EXISTS aheevaccs.pm_maya_ops_hro_canada_anglophone_1906_treatment (
83 `record_id` int(11) NOT NULL,
84 `phone_number` varchar(30) NOT NULL,
85 `treatment_id` int(11) NOT NULL DEFAULT '0',
86 `cycle_or_dialonce` varchar(30) DEFAULT NULL,
87 `number_of_attempts` int(11) DEFAULT '0',
88 `removed` int(1) DEFAULT '0',
89 `last_attempts` int(1) DEFAULT '0',
90 PRIMARY KEY (`record_id`,`phone_number`,`treatment_id`)
91) ENGINE=InnoDB DEFAULT CHARSET=utf8;