PU500gAj

1DROP TABLE IF EXISTS `doc_outgoing_calls`;
2CREATE TABLE `doc_outgoing_calls` (
3	`ID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
4	`DATE_CALL` DATETIME NOT NULL COMMENT 'Дата звонка',
5	`TYPE` ENUM('health','redirection') NOT NULL COMMENT 'Тип звонка' COLLATE 'utf8_unicode_ci',
6	`PARENT_ID` INT(11) UNSIGNED NOT NULL COMMENT 'ID родительского звонка',
7	`SORT` INT(11) UNSIGNED NOT NULL COMMENT 'Сортировка',
8	`COMMENT` TEXT NOT NULL COMMENT 'Комментарий оператора' COLLATE 'utf8_unicode_ci',
9	`STATUS` INT(11) UNSIGNED NOT NULL COMMENT 'Статус звонка',
10	`DATE_CREATE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Дата создания',
11	`DATE_LOCKED_UNTIL` DATETIME NOT NULL COMMENT 'Дата блокировки',
12	`LOCKED_BY` INT(11) NOT NULL COMMENT 'ID заблокировавшего',
13	`ORDER_ID` INT(11) UNSIGNED NOT NULL COMMENT 'ID заказа',
14	`ENTITY_ID` INT(11) UNSIGNED NOT NULL COMMENT 'ID сущности',
15	PRIMARY KEY (`ID`),
16	INDEX `DATE_CALL_STATUS` (`DATE_CALL`, `STATUS`),
17	INDEX `ORDER_ID` (`ORDER_ID`),
18	INDEX `ENTITY_ID_TYPE` (`ENTITY_ID`, `TYPE`)
19)
20COLLATE='utf8_unicode_ci'
21ENGINE=InnoDB
22AUTO_INCREMENT=81921
23;
24
25
26INSERT INTO doc_outgoing_calls   (ORDER_ID, DATE_CALL, ENTITY_ID, TYPE, SORT, STATUS)
27	
28	SELECT DCC.ORDER_ID,
29           IF(DSD.DATE_RECALL IS NOT NULL, DSD.DATE_RECALL, IF(DCC.DATE_EXECUTION IS NOT NULL, DCC.DATE_EXECUTION + INTERVAL 1 DAY, '2016-01-01 09:00:00')) AS DATE_CALL,
30           0 AS ENTITY_ID,
31           'health' as TYPE,
32           100 as SORT,
33           IF((
34        /* Не показываем отклоненные заявки */ BSO.STATUS_ID != "Z"
35    ) AND (
36        /* Не обзванивать заявки партнеров */ DHTS.UF_CALL_LIST != 1 OR DHTS.UF_CALL_LIST IS NULL
37    ) AND (
38                    /* Для сегодняшнего и последующих листов обзвона показываем заявки только в статусе Заполнен и Выполнен */ 
39                    BSO.STATUS_ID IN ("L", "E")
40                ) AND (
41                (
42                    /* Где дата обзвона равна выбранной дате */
43                    DSD.DATE_RECALL = date_format(NOW(), '%Y-%m-%d 00:00:00')
44                        OR 
45                    (
46                        /* Либо где дата обзвона не указана и с момента выполнения заявки прошло менее 12 дней */
47                        DSD.DATE_RECALL IS NULL 
48                        AND DCC.DATE_EXECUTION < date_format(NOW(), '%Y-%m-%d 00:00:00')
49                    )
50                )
51            ) AND (DCC.DATE_EXECUTION > "2016-11-30 00:00:00"), 1, 3) as STATUS
52           
53    FROM doc_calling_card AS DCC
54
55    LEFT JOIN doc_highload_traffic_source AS DHTS
56            ON DHTS.ID = DCC.TRAFFIC_SOURCE_ID
57
58    INNER JOIN b_sale_order AS BSO
59            ON BSO.ID = DCC.ORDER_ID
60           
61    LEFT JOIN b_sale_order_change AS STATUS_L
62           ON STATUS_L.ORDER_ID = BSO.ID
63          AND STATUS_L.TYPE = "ORDER_STATUS_CHANGED"
64          AND STATUS_L.DATA = "a:1:{s:9:\"STATUS_ID\";s:1:\"L\";}"    
65                 
66    LEFT JOIN b_sale_order_change AS STATUS_G
67           ON STATUS_G.ORDER_ID = BSO.ID
68          AND STATUS_G.TYPE = "ORDER_STATUS_CHANGED"
69          AND STATUS_G.DATA = "a:1:{s:9:\"STATUS_ID\";s:1:\"G\";}"
70
71    INNER JOIN b_sale_status_lang AS BSSL
72            ON BSSL.STATUS_ID = BSO.STATUS_ID
73           AND BSSL.LID = "ru"
74           
75    LEFT JOIN doc_survey_data AS DSD
76           ON DSD.ORDER_ID = DCC.ORDER_ID
77           
78    GROUP BY BSO.ID
79    
80    
81    
82    ORDER BY DCC.ID ASC;
83 INSERT INTO doc_outgoing_calls   (ORDER_ID, DATE_CALL, ENTITY_ID, TYPE, SORT, STATUS)
84 
85SELECT DCC.ORDER_ID,
86           DCCR.DATE_INSERT as DATE_CALL,
87           DCCR.ID as ENTITY_ID,
88           'redirection' as TYPE,
89           1 as SORT,
90           if(DCCR.CONSENT_TO_RECORD = 19 /* Согласен на запись */
91      AND DCCR.STATUS = 82            /* Не звонили */
92      AND DHTS.ID IS NULL, 1,3) as STATUS
93
94    FROM doc_calling_card_redirection AS DCCR
95           
96    LEFT JOIN doc_reference_book AS DRB_SPECIALISTS
97           ON DRB_SPECIALISTS.ID = DCCR.SPECIALIST
98          AND DRB_SPECIALISTS.UF_RB_GROUP NOT IN(0,19,20,21,22,23,24,25,26,27,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,28,29,30,31,32,33,34,35,36,37)
99     
100    LEFT JOIN doc_calling_card_redirection AS SPECIALISTS_COMMENTS
101           ON SPECIALISTS_COMMENTS.ID = DCCR.ID
102          AND SPECIALISTS_COMMENTS.SPECIALIST = DRB_SPECIALISTS.ID
103           
104    LEFT JOIN doc_reference_book AS DRB_ANALISES
105           ON DRB_ANALISES.ID = DCCR.SPECIALIST
106          AND DRB_ANALISES.UF_RB_GROUP IN(0,19,20,21,22,23,24,25,26,27,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,28,29,30,31,32,33,34,35,36,37)
107     
108    LEFT JOIN doc_calling_card_redirection AS ANALISES_COMMENTS
109           ON ANALISES_COMMENTS.ID = DCCR.ID
110          AND ANALISES_COMMENTS.SPECIALIST = DRB_ANALISES.ID
111    
112    INNER JOIN doc_calling_card AS DCC FORCE INDEX FOR JOIN (ORDER_ID)
113            ON DCC.ORDER_ID = DCCR.ORDER_ID
114           /* http://redmine.doctoroncall.ru/issues/2675 - Убрать Санкт-Пертербург из листа обзвона по перенаправлениям */
115           AND DCC.SECTION_CODE != "spb"
116    
117    /* http://redmine.doctoroncall.ru/issues/2570 - Исключить из листа обзвона все партнерские вызовы */
118    LEFT JOIN doc_highload_traffic_source AS DHTS 
119           ON DHTS.ID = DCC.TRAFFIC_SOURCE_ID
120          AND DHTS.UF_SOURCE_SALES = 286
121           
122    INNER JOIN b_sale_order AS BSO
123            ON BSO.ID = DCC.ORDER_ID
124           AND BSO.STATUS_ID != "Z"
125           
126    INNER JOIN b_sale_status_lang AS BSSL
127            ON BSSL.STATUS_ID = BSO.STATUS_ID
128           AND BSSL.LID = "ru"
129           
130    LEFT JOIN doc_survey_data AS DSD
131           ON DSD.ORDER_ID = DCC.ORDER_ID
132           
133    LEFT JOIN b_utm_user BUF
134           ON BUF.VALUE_ID = DCC.DOCTOR_ID
135          AND BUF.FIELD_ID = 34
136           
137    LEFT JOIN b_iblock_element IBE
138           ON IBE.ID = BUF.VALUE_INT
139           
140    LEFT JOIN b_user BU
141           ON BU.ID = DCC.DOCTOR_ID
142    
143
144    GROUP BY DCCR.ORDER_ID
145    
146    ORDER BY DATE_EXECUTION DESC;
147	
148	DROP TABLE IF EXISTS `site_methods`;
149CREATE TABLE IF NOT EXISTS `site_methods` (
150  `ID` int(11) NOT NULL AUTO_INCREMENT,
151  `PATH` varchar(255) NOT NULL,
152  `REQUEST_METHOD` enum('post','put','get','delete') NOT NULL,
153  `CLASS` varchar(255) NOT NULL,
154  `CLASS_METHOD` varchar(255) NOT NULL,
155  PRIMARY KEY (`ID`),
156  UNIQUE KEY `unique_PATH_REQUEST_METHOD` (`PATH`,`REQUEST_METHOD`)
157) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
158INSERT INTO `site_methods` (`ID`, `PATH`, `REQUEST_METHOD`, `CLASS`, `CLASS_METHOD`) VALUES
159	(1, '/outgoing-calls/make-call', 'post', 'Admin\OutgoingCalls', 'makeCall'),
160	(2, '/outgoing-calls/get-next-feedback-call', 'get', 'Admin\OutgoingCalls', 'getNextFeedbackCall'),
161	(3, '/outgoing-calls/end-call', 'post', 'Admin\OutgoingCalls', 'endCall');
162
163	ALTER TABLE `doc_calling_card`
164	ADD COLUMN `Q_1_SELECT` INT(11) UNSIGNED NOT NULL COMMENT 'ДК: Вопрос 1 - выбор ответа' AFTER `Q_1`;