Mochinov

Untitled

May 6th, 2021
475
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.     def get_records_query(self):
  2.         """
  3.        Получаем список детальной информации о разговорах
  4.        :return:
  5.        """
  6.         if self.order_by in ("caller_number", "called_number", "zone", "b_leg",
  7.                              "call_marked",
  8.                              # TODO возможно все же нужно оставить
  9.                              # "group_name",
  10.                   "queue_idstat",
  11.             "wait_sec", "bsl_delta",
  12.                              "datetime", "session_end", "disconnect_code",
  13.                              "billable_session_length", "price", "summ") and self.order_type in ['DESC', 'ASC']:
  14.             order_by_dict = {
  15.                 "caller_number": 3,
  16.                 "called_number": 4,
  17.                 "b_leg": 10,
  18.                 "disconnect_code": 23,
  19.                 "bsl_delta": 12,
  20.                 "queue_idstat": 9,
  21.                 # "zone": 6,
  22.                 "datetime": 7,
  23.                 "session_end": 8,
  24.                 "billable_session_length": 11,
  25.                 "wait_sec": 13,
  26.                 "summ": 14,
  27.                 "price": 15,
  28.                 "call_marked": 17
  29.             }
  30.             ord_col_num = order_by_dict.get(self.order_by, "b.id")
  31.            
  32.             order = '{} {}'.format(ord_col_num , self.order_type)
  33.         else:
  34.             order = "b.id desc"
  35.  
  36.         sql = """
  37.        select
  38.           b.general, -- 1
  39.            b.caller_group_number, -- 2
  40.            b.caller_number, -- 3
  41.            array_agg(distinct b.called_number) as called_number, -- 4
  42.            array_agg(distinct b.answer_number) as answer_number, -- 5
  43.            array_agg(dc.disconnect_rus) as disconnect_code, -- 6
  44.            array_agg(b.datetime + (%(time_shift)s||' hours')::interval) as dts, -- 7
  45.            array_agg(b.session_end + (%(time_shift)s||' hours')::interval) as dte, -- 8
  46.            array_agg(coalesce(q.name, get_schema_name(b.dialed_extension))) as queues, -- 9
  47.            array_agg(case
  48.            when b.b_leg is not null and inum.person_name is not null
  49.            then inum.person_name || '( '|| b.b_leg||')'
  50.            when b.b_leg is not null and inum.person_name is null and length(b.b_leg)>0
  51.            then b.b_leg
  52.            else
  53.            ''
  54.            end) as answered, -- 10          
  55.            array_agg(b.session_length::integer) as bsl, -- 11
  56.            array_agg((case
  57.                     when b.called_account_id=%(account_id)s and (b.b_leg is null or b.b_leg = '') and inum.person_name is null
  58.                     then 0
  59.                     when b.called_account_id=%(account_id)s
  60.                     then b.billable_session_length - b.wait_sec
  61.                    
  62.                     when b.account_id = %(account_id)s and (b.b_leg is null or b.b_leg = '')
  63.                     then 0
  64.                     else
  65.                     b.billable_session_length end )::integer) as bsl_delta, -- 12
  66.            array_agg(( case
  67.                     when b.called_account_id=%(account_id)s and (b.b_leg is null or b.b_leg = '') and inum.person_name is null
  68.                     then b.billable_session_length
  69.                     when b.called_account_id=%(account_id)s
  70.                     then b.wait_sec
  71.                     when b.account_id = %(account_id)s and (b.b_leg is null or b.b_leg = '')
  72.                     then b.session_length
  73.                     else b.wait_sec end )::integer) as wl, -- 13
  74.            array_agg(b.summ) as summ, -- 14
  75.            array_agg(b.price) as price, --  15
  76.            array_agg(distinct b.file) as file, -- 16
  77.            array_agg( b.marked) as marked, --  17
  78.            array_agg( b.id) as id,  -- 18
  79.            array_agg( z.name) as zone,  -- 19
  80.            array_agg( b.flag_record_out) as outgoing,  -- 20
  81.            array_agg( b.session_id) as session,  -- 21
  82.            array_agg(get_tarif_time((case
  83.                     when b.called_account_id=%(account_id)s and (b.b_leg is null or b.b_leg = '') and inum.person_name is null
  84.                     then 0
  85.                     when b.called_account_id=%(account_id)s
  86.                     then b.billable_session_length - b.wait_sec
  87.                    
  88.                     when b.account_id = %(account_id)s and (b.b_leg is null or b.b_leg = '')
  89.                     then 0
  90.                     else
  91.                     b.billable_session_length end)::integer, b.account_id, %(account_id)s)) as tariff_time, --22
  92.            array_agg( case when b.hangup_side = 'recv_bye' or b.hangup_side = 'send_refuse' then 'А' when b.hangup_side is null or length(b.hangup_side)<1 then ' ' else  'Б' end) as hangup_side, --23
  93.            array_agg(b.fifo_service_rating) as fifo_service_rating --24
  94.        from billservice_phonetransaction b
  95.        left join tel_zones z on b.tel_zone_id = z.id
  96.        left join internal_numbers inum on inum.username=b.b_leg -- and inum.account_id=b.account_id
  97.        left join disconnect_code dc on dc.disconnect_eng=b.disconnect_code
  98.        left join fs_queue q on b.queue_idstat=q.id
  99.        where (1=1) {}
  100.        group by
  101.            b.general,
  102.            b.caller_group_number,
  103.            b.caller_number
  104.        order by {}
  105.        """.format(self.filter_content, order)
  106.  
  107.         return sql, self.filter_args
  108.  
  109.  
RAW Paste Data