Advertisement
Makcimm

Untitled

Aug 24th, 2019
1,025
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH
  2.       unicredit_calls_log AS
  3.         (
  4.         SELECT
  5.           'Лог звонков'                                 AS report_name
  6.             , 'UNICREDIT_TM.PKG_REPORTS.FNC_GET_CALLS_LOG'  AS report_location
  7.         FROM dual
  8.         ),
  9.       last_call_block AS
  10.         (
  11.         SELECT
  12.           csh.fid_contact
  13.             , csh.fid_call
  14.             , MAX(csh.fid_status) KEEP(DENSE_RANK LAST ORDER BY CAST(csh.status_update_time AS DATE)) AS fid_status
  15.         FROM contact_statuses_history csh
  16.                JOIN contacts ct
  17.                     ON ct.id_contact    = csh.fid_contact
  18.                       AND ct.fid_scenario IN (1, 4)
  19.         WHERE csh.status_update_time < i_finish_time
  20.         GROUP BY csh.fid_contact, csh.fid_call
  21.         ),
  22.       dialer_leg_63 AS
  23.         (
  24.         SELECT
  25.           cli.session_id
  26.             , clg.created     AS dialer_created
  27.             , clg.voip_reason AS dialer_voip_reason
  28.             , clg.text_reason AS dialer_text_reason
  29.             , clg.internal_reason AS dialer_internal_reason
  30.         FROM calls cli
  31.                JOIN contacts ct
  32.                     ON ct.id_contact    = cli.fid_contact
  33.                       AND ct.fid_scenario IN (1, 4)
  34.                JOIN naucrm62.call_legs clg
  35.                     ON clg.session_id       = cli.session_id
  36.                       AND clg.src_abonent_type = 'IVR'
  37.         WHERE clg.created >=  i_init_time
  38.           AND clg.created <   i_finish_time
  39.         ),
  40.       abonent_leg_63 AS
  41.         (
  42.         SELECT
  43.           cli.session_id
  44.             , MAX(clg.created) KEEP(DENSE_RANK FIRST ORDER BY clg.created)    AS abonent_created
  45.             , MAX(clg.connected) KEEP(DENSE_RANK FIRST ORDER BY clg.created)  AS abonent_connected
  46.             , MAX(clg.ended) KEEP(DENSE_RANK FIRST ORDER BY clg.created)      AS abonent_ended
  47.             , MAX(clg.voip_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS abonent_voip_reason
  48.             , MAX(clg.text_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS abonent_text_reason
  49.             , MAX(clg.internal_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS abonent_internal_reason
  50.         FROM calls cli
  51.                JOIN contacts ct
  52.                     ON ct.id_contact    = cli.fid_contact
  53.                       AND ct.fid_scenario IN (1, 4)
  54.                JOIN naucrm62.call_legs clg
  55.                     ON clg.session_id       = cli.session_id
  56.                       AND clg.dst_abonent_type = 'UNKNOWN'
  57.         WHERE clg.dst_id  NOT LIKE  'call_intrusion_%'
  58.           AND clg.created >=        i_init_time
  59.           AND clg.created <         i_finish_time
  60.         GROUP BY cli.session_id
  61.         ),
  62.       operator_leg_63 AS
  63.         (
  64.         SELECT
  65.           cli.session_id
  66.             , MAX(clg.created) KEEP(DENSE_RANK LAST ORDER BY clg.created)     AS oper_created
  67.             , MAX(clg.connected) KEEP(DENSE_RANK LAST ORDER BY clg.created)   AS oper_connected
  68.             , MAX(clg.ended) KEEP(DENSE_RANK LAST ORDER BY clg.created)       AS oper_ended
  69.             , MAX(clg.dst_abonent) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS oper_login
  70.             , MAX(clg.voip_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS oper_voip_reason
  71.             , MAX(clg.text_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS oper_text_reason
  72.             , MAX(clg.internal_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS oper_internal_reason
  73.         FROM calls cli
  74.                JOIN contacts ct
  75.                     ON ct.id_contact    = cli.fid_contact
  76.                       AND ct.fid_scenario IN (1, 4)
  77.                JOIN naucrm62.call_legs clg
  78.                     ON clg.session_id       = cli.session_id
  79.                       AND clg.dst_abonent_type = 'SP'
  80.                       AND clg.intrusion        = 0
  81.         WHERE clg.dst_id  NOT LIKE  'call_intrusion_%'
  82.           AND clg.created >=        i_init_time
  83.           AND clg.created <         i_finish_time
  84.         GROUP BY cli.session_id
  85.         ),
  86.       ivr_leg_63 AS
  87.         (
  88.         SELECT
  89.           cli.session_id
  90.             , MAX(clg.created) KEEP(DENSE_RANK LAST ORDER BY clg.created)     AS ivr_created
  91.             , MAX(clg.connected) KEEP(DENSE_RANK LAST ORDER BY clg.created)   AS ivr_connected
  92.             , MAX(clg.ended) KEEP(DENSE_RANK LAST ORDER BY clg.created)       AS ivr_ended
  93.             , MAX(clg.dst_abonent) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS ivr_login
  94.             , MAX(clg.voip_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS ivr_voip_reason
  95.             , MAX(clg.text_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS ivr_text_reason
  96.             , MAX(clg.internal_reason) KEEP(DENSE_RANK LAST ORDER BY clg.created) AS ivr_internal_reason
  97.         FROM calls cli
  98.                JOIN contacts ct
  99.                     ON ct.id_contact    = cli.fid_contact
  100.                       AND ct.fid_scenario IN (1, 4)
  101.                JOIN naucrm62.call_legs clg
  102.                     ON clg.session_id       = cli.session_id
  103.                       AND clg.dst_abonent_type = 'IVR'
  104.                       AND clg.intrusion        = 0
  105.         WHERE clg.created >=        i_init_time
  106.           AND clg.created <         i_finish_time
  107.         GROUP BY cli.session_id
  108.         ),
  109.       hold_info_63 AS
  110.         (
  111.         SELECT
  112.           cli.session_id
  113.             , SUM(naucrm62.intervaltosec(ncs.ended - ncs.entered)) AS hold_time
  114.         FROM calls cli
  115.                JOIN contacts ct
  116.                     ON ct.id_contact    = cli.fid_contact
  117.                       AND ct.fid_scenario IN (1, 4)
  118.                JOIN phones p
  119.                     ON p.id_phone = cli.fid_phone
  120.                JOIN naucrm62.call_status ncs
  121.                     ON ncs.session_id = cli.session_id
  122.                       AND p.phone_number = SUBSTR(ncs.destination_id, -10)
  123.         GROUP BY cli.session_id
  124.         ),
  125.       wrp_info_63 AS
  126.         (
  127.         SELECT
  128.           ol.session_id
  129.             , SUM(NVL(nsc.duration, 0)) AS acw_time
  130.         FROM operator_leg_63 ol
  131.                JOIN naucrm62.status_changes nsc
  132.                     ON nsc.reason         =       ol.session_id
  133.                       AND nsc.status         =       'wrapup'
  134.                       AND nsc.login          =       ol.oper_login
  135.                       AND ol.oper_connected  IS NOT  NULL
  136.         GROUP BY ol.session_id
  137.         ),
  138.       nau_info_63 AS
  139.         (
  140.         SELECT
  141.           dl.session_id
  142.             , naucrm62.intervaltosec(NVL(al.abonent_connected
  143.                                               , al.abonent_ended) - dl.dialer_created)       AS answ_wait_time
  144.           --          , CASE WHEN ol.oper_created IS NOT NULL
  145.           --                    THEN 'Распределён на оператора'
  146.           --                  when ol.oper_created IS NULL
  147.           --                        and al.abonent_connected is not null
  148.           --                        and naucrm62.intervaltosec(al.abonent_ended - al.abonent_connected) > 0
  149.           --                  then 'Потерян в очереди'
  150.           --                 ELSE 'Не распределялся в очередь'
  151.           --            END                                                                   AS to_oper_transfered
  152.             , CASE WHEN ol.oper_created IS NOT NULL
  153.                      THEN 'Распределён на оператора'
  154.                    WHEN il.ivr_created IS NOT NULL
  155.                      THEN 'Потерян в очереди'
  156.                    ELSE 'Не распределялся в очередь'
  157.             END                                                                   AS to_oper_transfered
  158.             , CASE WHEN ol.oper_created IS NOT NULL
  159.                      THEN
  160.                      CASE WHEN ol.oper_connected IS NOT NULL
  161.                             THEN 'Звонок принят'
  162.                           ELSE 'Звонок потерян'
  163.                        END
  164.                    ELSE ''
  165.             END                                                                   AS to_oper_connected
  166.             , naucrm62.intervaltosec(NVL(ol.oper_created,al.abonent_ended) - al.abonent_connected)        AS queue_time
  167.             , naucrm62.intervaltosec(NVL(ol.oper_connected
  168.                                               , ol.oper_ended) - ol.oper_created)          AS ringing_time
  169.  
  170.             , naucrm62.intervaltosec(ol.oper_ended - ol.oper_connected)             AS talk_time
  171.             , naucrm62.intervaltosec(ol.oper_ended - ol.oper_connected) -
  172.               NVL(hi.hold_time, 0)                                                  AS talk_time_no_hold
  173.             , NVL(hi.hold_time, 0)                                                  AS hold_time
  174.             , NVL(wi.acw_time, 0)                                                   AS acw_time
  175.             , COALESCE(ol.oper_voip_reason,il.ivr_voip_reason,al.abonent_voip_reason,dl.dialer_voip_reason) AS voip_reason
  176.             , COALESCE(ol.oper_internal_reason,il.ivr_internal_reason,al.abonent_internal_reason,dl.dialer_internal_reason) AS internal_reason
  177.             , COALESCE(ol.oper_text_reason,il.ivr_text_reason,al.abonent_text_reason,dl.dialer_text_reason) AS text_reason
  178.         FROM dialer_leg_63 dl
  179.                LEFT JOIN abonent_leg_63 al
  180.                          ON dl.session_id = al.session_id
  181.                LEFT JOIN operator_leg_63 ol
  182.                          ON dl.session_id = ol.session_id
  183.                LEFT JOIN ivr_leg_63 il
  184.                          ON il.session_id = dl.session_id
  185.                LEFT JOIN hold_info_63 hi
  186.                          ON hi.session_id = dl.session_id
  187.                LEFT JOIN wrp_info_63 wi
  188.                          ON wi.session_id = dl.session_id
  189.         )
  190.       SELECT
  191.         cls.id_call --ID звонка
  192.           , cls.fid_contact --ID контакта
  193.           , cd.customer_rk --ID Клиента
  194.           , reg.registry_name --Реестр
  195.           , TO_CHAR(ct.creation_date, 'DD.MM.YYYY HH24:MI')                                   AS creation_date --Дата загрузки контакта в обзвон
  196.           , cd.first_name --Имя
  197.           , cd.middle_name --Отчество
  198.           , dr.region_name --Регион
  199.           , mp.phone_number                                                                   AS phone_number --Номер телефона
  200.           , dpt.type_name                                                                     AS type_phone
  201.           , cd.xsell_type --Программа кредитования
  202.           , TO_CHAR(cd.end_date, 'DD.MM.YYYY')                                                AS end_date --Срок действия предложения по ПК
  203.           , cd.cl_limit --Предварительно одобренный лимит
  204.           , cd.cl_limit_cpi --Предварительно одобренный лимит со страхованием
  205.           , cd.cl_payment --Ежемесячный платеж
  206.           , cd.cl_payment_cpi --Ежемесячный платеж со страхованием
  207.           , cd.cl_rate --% ставка без страхования
  208.           , cd.cl_rate_cpi --% ставка с учетом страхования
  209.           , cd.cc_limit --Предварительно одобренный лимит по КК
  210.           , cd.cc_rate --% ставка по КК
  211.           , dcs.status_text                                                                   AS call_status --Результат звонка
  212.           , dcts.status_text                                                                  AS contact_status --Результат контакта
  213.           , DECODE(dcs.status_group, 'agree', TO_CHAR(inq.meeting_date, 'DD.MM.YYYY'), NULL)  AS meeting_date --Дата встречи
  214.           , DECODE(dcs.status_group, 'agree', inq.meeting_time, NULL)                         AS meeting_time --Время встречи
  215.           , DECODE(dcs.status_group, 'agree', doc.city_name, NULL)                            AS meeting_city --Город встречи
  216.           , CASE WHEN inq.fid_meeting_type  = 1 AND dcs.status_group = 'agree'
  217.                    THEN DO.office_address
  218.                  WHEN inq.fid_meeting_type  = 2
  219.                    AND dcs.status_group      = 'agree'
  220.                    THEN inq.client_address
  221.                  ELSE NULL
  222.           END                                                                               AS client_address --Адрес встречи
  223.           , DECODE(dcs.status_group, 'agree', DO.office_name, NULL)                           AS office_name --Название отделения
  224.           , DECODE(dcs.status_group, 'agree', dmt.meeting_type_name, NULL)                    AS meeting_type_name --Тип встречи
  225.           , DECODE(dcs.status_group, 'agree', dpt.product_type_code, NULL)                    AS product_type_code --Вид продукта
  226.           , cls.call_no --№ попытки
  227.           , TO_CHAR(cls.call_back_time, 'DD.MM.YYYY HH24:MI')                                 AS call_back_time --Дата и время перезвона
  228.           , TO_CHAR(cls.call_start_time, 'DD.MM.YYYY HH24:MI')                                AS call_start_time --Начало звонка
  229.           , TO_CHAR(cls.call_finish_time, 'DD.MM.YYYY HH24:MI')                               AS call_finish_time --Окончание звонка
  230.           , NVL(ni63.answ_wait_time, 0)                                                       AS answ_wait_time --Время ожидания ответа абонента, сек
  231.           , ni63.to_oper_transfered                                                           AS to_oper_transfered --Распределение на оператора
  232.           , ni63.to_oper_connected                                                            AS to_oper_connected --Результат ответа оператора
  233.           , NVL(ni63.queue_time, 0)                                                           AS queue_time --Время нахождения звонка в очереди
  234.           , NVL(ni63.ringing_time, 0)                                                         AS ringing_time --Время ответа оператора (RingingTime), сек
  235.           , NVL(ni63.talk_time_no_hold, 0)                                                    AS talk_time_no_hold --Разговор, сек (TalkTime)
  236.           , NVL(ni63.hold_time, 0)                                                            AS hold_time --Удержание вызова, сек(HoldTime)
  237.           , NVL(ni63.acw_time, 0)                                                             AS acw_time --Поствызывная обработка, сек (ACW)
  238.           , NVL(ni63.ringing_time, 0) + NVL(ni63.talk_time, 0) + NVL(ni63.acw_time, 0)        AS aht --Время обработки звонка (AHT), сек
  239.           , cls.operator_login --Оператор
  240.           , cls.session_id --Звуковой файл
  241.           , cls.comments --Комментарий оператора
  242.           , ni63.voip_reason --SIP-код
  243.           , ir.description  AS internal_reason --Пояснение завершения звонка из наумена/Расшивфорка из Наумен
  244.           , ni63.text_reason --Текстовый сип код/ Расшифровка
  245.       FROM calls cls
  246.              JOIN contacts ct
  247.                   ON ct.id_contact = cls.fid_contact
  248.              JOIN contact_data cd
  249.                   ON cd.fid_contact = ct.id_contact
  250.              JOIN registries reg
  251.                   ON reg.id_registry = ct.fid_registry
  252.              JOIN d_call_statuses dcs
  253.                   ON dcs.id_status = cls.fid_status
  254.              JOIN last_call_block lcb
  255.                   ON lcb.fid_call     = cls.id_call
  256.                     AND lcb.fid_contact  = cls.fid_contact
  257.              JOIN d_contact_statuses dcts
  258.                   ON dcts.id_status = lcb.fid_status
  259.              LEFT JOIN d_regions dr
  260.                        ON dr.id_region = ct.fid_region
  261.              LEFT JOIN inquiries inq
  262.                        ON inq.fid_contact  = cls.fid_contact
  263.              LEFT JOIN d_office_cities doc
  264.                        ON doc.id_city = inq.fid_meeting_city
  265.              LEFT JOIN d_offices DO
  266.                        ON DO.id_office = inq.fid_office
  267.              LEFT JOIN d_meeting_types dmt
  268.                        ON dmt.id_meeting_type = inq.fid_meeting_type
  269.              LEFT JOIN d_product_types dpt
  270.                        ON dpt.id_product_type = inq.fid_product_type
  271.              LEFT JOIN phones mp
  272.                        ON mp.fid_contact     = ct.id_contact
  273.                          AND mp.id_phone = cls.fid_phone
  274.              JOIN d_phone_types dpt
  275.                   ON dpt.id_phone_type = mp.fid_phone_type
  276.              LEFT JOIN nau_info_63 ni63
  277.                        ON ni63.session_id = cls.session_id
  278.              LEFT JOIN d_nau_internal_reasons ir
  279.                        ON ir.code = ni63.internal_reason
  280.              LEFT JOIN registries_groups drg
  281.                        ON reg.fid_reg_group = drg.id_group
  282.       WHERE cls.call_start_time >=  i_init_time
  283.         AND cls.call_start_time <   i_finish_time
  284.         AND (reg.id_registry = i_id_registry
  285.         OR i_id_registry IS  NULL
  286.         )
  287.         AND reg.id_registry <> 448
  288.         AND (drg.id_group = i_id_reg_group
  289.         OR i_id_reg_group IS  NULL
  290.         )
  291.         AND ct.fid_scenario IN (1, 4)
  292.       ORDER BY cls.call_start_time
  293.   ;
Advertisement
RAW Paste Data Copied
Advertisement