ODBC функции
Все функции для работы с PostgreSQL через ODBC.
| Функция | DSN | Тип | Описание |
|---|---|---|---|
| ADD_QUEUE_MEMBER | asterisk | READ | |
| CHECK_INTERFACE_IN_QUEUE | asterisk | READ | |
| CHECK_OPERATOR | asterisk | READ | |
| CHECK_OPERATOR_IN_QUEUE | asterisk | READ | |
| CHECK_QUEUE_EXISTS | asterisk | READ | |
| CHECK_WORKING_HOURS | asterisk | READ | |
| COUNT_PAUSED_MEMBERS | asterisk | READ | |
| GET_ENDPOINT_LANG | asterisk | READ | |
| GET_ENDPOINT_OUTBOUND_CLI | asterisk | READ | |
| GET_HOTLINE_QUEUE | asterisk | READ | |
| GET_OPERATOR_BY_ENDPOINT | asterisk | READ | |
| GET_OPERATOR_BY_INTERFACE | asterisk | READ | |
| GET_OPERATOR_BY_QUEUE | asterisk | READ | |
| GET_OPERATOR_QUEUE | asterisk | READ | |
| GET_ORG_ROUTING | asterisk | READ | |
| GET_QUEUE_INTERFACE | asterisk | READ | |
| GET_QUEUE_OPERATOR_NUM | asterisk | READ | |
| GET_QUEUE_TENANT | asterisk | READ | |
| GET_ROUTE_GREETING | asterisk | READ | |
| GET_TENANT_BY_DID | asterisk | READ | |
| GET_TENANT_BY_ENDPOINT | asterisk | READ | |
| GET_TENANT_BY_QUEUE | asterisk | READ | |
| GET_TENANT_BY_REQUEST | asterisk | READ | |
| LOG_DIRECT_CALL | asterisk | READ | |
| LOG_OUTBOUND_CALL | asterisk | READ | |
| LOG_PAUSE_ACTION | asterisk | READ | |
| LOG_QUEUE_ACTION | asterisk | READ | |
| LOG_QUEUE_CALL_ABANDONED | asterisk | READ | |
| LOG_QUEUE_CALL_ANSWERED | asterisk | READ | |
| LOG_TTS_CALL | asterisk | READ | |
| REMOVE_QUEUE_MEMBER | asterisk | READ | |
| SET_ENDPOINT_LANG | asterisk | READ | |
| SET_MEMBER_PAUSE | asterisk | READ | |
| asterisk | asterisk |
ADD_QUEUE_MEMBER
DSN: asterisk
Тип: READ
SQL:
INSERT INTO queue_members (queue_name, interface, membername, uniqueid, operator_num, paused, penalty, tenant_id) VALUES ('${SQL_ESC(${ARG1})}', '${SQL_ESC(${ARG2})}', '${SQL_ESC(${ARG5})}', '${SQL_ESC(${ARG4})}', '${SQL_ESC(${ARG5})}', 0, 0, ${ARG6}) RETURNING operator_num
Используется в: - extensions.conf — управление очередями
CHECK_INTERFACE_IN_QUEUE
DSN: asterisk
Тип: READ
SQL:
SELECT COUNT(*) FROM queue_members WHERE interface = '${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2}
Используется в: - extensions.conf — управление очередями
CHECK_OPERATOR
DSN: asterisk
Тип: READ
SQL:
SELECT COUNT(*) FROM operators WHERE operator_num = '${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2}
Используется в: - extensions_pause.conf — паузы операторов
CHECK_OPERATOR_IN_QUEUE
DSN: asterisk
Тип: READ
SQL:
SELECT COUNT(*) FROM queue_members WHERE queue_name = '${SQL_ESC(${ARG1})}' AND operator_num = '${SQL_ESC(${ARG2})}' AND tenant_id = ${ARG3}
Используется в: - extensions.conf — управление очередями - extensions_pause.conf — паузы операторов
CHECK_QUEUE_EXISTS
DSN: asterisk
Тип: READ
SQL:
SELECT COUNT(*) FROM queues WHERE name = '${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2}
Используется в: - extensions.conf — управление очередями
CHECK_WORKING_HOURS
DSN: asterisk
Тип: READ
SQL:
SELECT COALESCE((SELECT CASE WHEN is_working_day = TRUE AND CURRENT_TIME BETWEEN start_time AND end_time THEN 'open' ELSE 'closed' END FROM working_hours WHERE queue_name = '${SQL_ESC(${ARG1})}' AND day_of_week = (${ARG2} + 6) % 7 AND tenant_id = ${ARG3}), 'closed')
Используется в: - extensions.conf — проверка рабочих часов
COUNT_PAUSED_MEMBERS
DSN: asterisk
Тип: READ
SQL:
SELECT COUNT(*) FROM queue_members WHERE queue_name = '${SQL_ESC(${ARG1})}' AND paused = 1 AND tenant_id = ${ARG2}
GET_ENDPOINT_LANG
DSN: asterisk
Тип: READ
SQL:
SELECT COALESCE(NULLIF(ui_language, ''), 'ru') FROM ps_endpoints WHERE id = '${SQL_ESC(${ARG1})}'
GET_ENDPOINT_OUTBOUND_CLI
DSN: asterisk
Тип: READ
SQL:
SELECT CONCAT(COALESCE(outgoing_cli, ''), '|', CASE WHEN allow_long_distance THEN 't' ELSE 'f' END) FROM ps_endpoints WHERE id = '${SQL_ESC(${ARG1})}'
GET_HOTLINE_QUEUE
DSN: asterisk
Тип: READ
SQL:
SELECT value FROM system_settings WHERE key = 'hotline_queue' AND (SELECT value FROM system_settings WHERE key = 'hotline_enabled') = 'true'
Используется в: - extensions.conf — управление очередями
GET_OPERATOR_BY_ENDPOINT
DSN: asterisk
Тип: READ
SQL:
SELECT operator_num FROM queue_members WHERE interface = 'PJSIP/${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2} LIMIT 1
Используется в: - extensions_pause.conf — паузы операторов
GET_OPERATOR_BY_INTERFACE
DSN: asterisk
Тип: READ
SQL:
SELECT operator_num FROM queue_members WHERE interface = '${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2} LIMIT 1
Используется в: - extensions_pause.conf — паузы операторов
GET_OPERATOR_BY_QUEUE
DSN: asterisk
Тип: READ
SQL:
SELECT operator_num, interface FROM queue_members WHERE queue_name = '${SQL_ESC(${ARG1})}' AND paused = 0 AND tenant_id = ${ARG2} LIMIT 1
Используется в: - extensions.conf — управление очередями - extensions_pause.conf — паузы операторов
GET_OPERATOR_QUEUE
DSN: asterisk
Тип: READ
SQL:
SELECT queue_name FROM queue_members WHERE operator_num='${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2}
Используется в: - extensions.conf — управление очередями - extensions_pause.conf — паузы операторов
GET_ORG_ROUTING
DSN: asterisk
Тип: READ
SQL:
SELECT o.phone_city_code || '|' || o.phone_zone_code || '|' || o.phone_local_digits FROM ps_endpoints e JOIN organizations o ON o.id = e.tenant_id WHERE e.id = '${SQL_ESC(${ARG1})}'
GET_QUEUE_INTERFACE
DSN: asterisk
Тип: READ
SQL:
SELECT interface FROM queue_members WHERE queue_name = '${SQL_ESC(${ARG1})}' AND paused = 0 AND tenant_id = ${ARG2} LIMIT 1
Используется в: - extensions.conf — управление очередями
GET_QUEUE_OPERATOR_NUM
DSN: asterisk
Тип: READ
SQL:
SELECT operator_num FROM queue_members WHERE queue_name = '${SQL_ESC(${ARG1})}' AND paused = 0 AND tenant_id = ${ARG2} LIMIT 1
Используется в: - extensions.conf — управление очередями - extensions_pause.conf — паузы операторов
GET_QUEUE_TENANT
DSN: asterisk
Тип: READ
SQL:
SELECT tenant_id FROM queues WHERE name = '${SQL_ESC(${ARG1})}' LIMIT 1
Используется в: - extensions.conf — управление очередями
GET_ROUTE_GREETING
DSN: asterisk
Тип: READ
SQL:
SELECT greeting_file FROM incoming_routes WHERE destination_value = "${ARG1}" AND destination_type = "queue" AND is_active = true AND greeting_file IS NOT NULL AND greeting_file != "" LIMIT 1
GET_TENANT_BY_DID
DSN: asterisk
Тип: READ
SQL:
SELECT CONCAT(tenant_id, '|', destination_type, '|', destination_value, '|', COALESCE(greeting_file, ''), '|', CASE WHEN personal_data_enabled THEN '1' ELSE '0' END, '|', CASE WHEN recording_notice_enabled THEN '1' ELSE '0' END) FROM incoming_routes WHERE did = '${SQL_ESC(${ARG1})}' AND is_active = TRUE LIMIT 1
GET_TENANT_BY_ENDPOINT
DSN: asterisk
Тип: READ
SQL:
SELECT tenant_id FROM ps_endpoints WHERE id = '${SQL_ESC(${ARG1})}' LIMIT 1
GET_TENANT_BY_QUEUE
DSN: asterisk
Тип: READ
SQL:
SELECT tenant_id FROM queues WHERE name = '${SQL_ESC(${ARG1})}' LIMIT 1
Используется в: - extensions.conf — управление очередями
GET_TENANT_BY_REQUEST
DSN: asterisk
Тип: READ
SQL:
SELECT get_organization_by_request_id('${SQL_ESC(${ARG1})}'::text)
LOG_DIRECT_CALL
DSN: asterisk
Тип: READ
SQL:
INSERT INTO call_detail_records (uniqueid, linkedid, call_timestamp, call_date, call_time, caller_id, called_number, operator_terminal, call_status, queue_wait_time, duration, billsec, tenant_id, recording_file, recording_file_caller, recording_file_operator) VALUES ('${SQL_ESC(${ARG1})}', NULLIF('${SQL_ESC(${ARG2})}',''), '${SQL_ESC(${ARG3})}', '${SQL_ESC(${ARG4})}', '${SQL_ESC(${ARG5})}', '${SQL_ESC(${ARG6})}', '${SQL_ESC(${ARG7})}', '${SQL_ESC(${ARG8})}', '${ARG9}', ${ARG10}, ${ARG11}, ${ARG12}, ${ARG13}, NULLIF('${SQL_ESC(${ARG14})}',''), NULLIF('${SQL_ESC(${ARG15})}',''), NULLIF('${SQL_ESC(${ARG16})}','')) RETURNING id
LOG_OUTBOUND_CALL
DSN: asterisk
Тип: READ
SQL:
INSERT INTO call_detail_records (uniqueid, linkedid, call_timestamp, call_date, call_time, caller_id, called_number, operator_terminal, call_status, duration, billsec, tenant_id, call_direction, recording_file, call_category) VALUES ('${SQL_ESC(${ARG1})}', NULLIF('${SQL_ESC(${ARG2})}',''), '${SQL_ESC(${ARG3})}', '${SQL_ESC(${ARG4})}', '${SQL_ESC(${ARG5})}', '${SQL_ESC(${ARG6})}', '${SQL_ESC(${ARG7})}', '${SQL_ESC(${ARG8})}', '${ARG9}', ${ARG10}, ${ARG11}, ${ARG12}, 'outgoing', NULLIF('${SQL_ESC(${ARG13})}',''), NULLIF('${SQL_ESC(${ARG14})}','')) RETURNING id
LOG_PAUSE_ACTION
DSN: asterisk
Тип: READ
SQL:
INSERT INTO operator_pause_log (operator_num, interface, queue_name, action, pause_type, tenant_id) VALUES ('${ARG1}', '${ARG2}', '${ARG3}', '${ARG4}', '${ARG5}', ${ARG6})
LOG_QUEUE_ACTION
DSN: asterisk
Тип: READ
SQL:
INSERT INTO operator_queue_log (operator_num, interface, queue_name, action, tenant_id) VALUES ('${ARG1}', '${ARG2}', '${ARG3}', '${ARG4}', ${ARG5}) RETURNING id
Используется в: - extensions.conf — управление очередями
LOG_QUEUE_CALL_ABANDONED
DSN: asterisk
Тип: READ
SQL:
INSERT INTO call_detail_records (uniqueid, linkedid, call_timestamp, call_date, call_time, caller_id, called_number, queue_name, call_status, hangup_cause, queue_wait_time, duration, tenant_id) VALUES ('${SQL_ESC(${ARG1})}', NULLIF('${SQL_ESC(${ARG2})}',''), '${SQL_ESC(${ARG3})}', '${SQL_ESC(${ARG4})}', '${SQL_ESC(${ARG5})}', '${SQL_ESC(${ARG6})}', '${SQL_ESC(${ARG7})}', '${SQL_ESC(${ARG8})}', 'abandoned', '${SQL_ESC(${ARG9})}', ${ARG10}, ${ARG11}, ${ARG12}) RETURNING id
Используется в: - extensions.conf — управление очередями
LOG_QUEUE_CALL_ANSWERED
DSN: asterisk
Тип: READ
SQL:
INSERT INTO call_detail_records (uniqueid, linkedid, call_timestamp, call_date, call_time, caller_id, called_number, queue_name, operator_num, operator_terminal, call_status, queue_wait_time, duration, billsec, tenant_id, recording_file, recording_file_caller, recording_file_operator) VALUES ('${SQL_ESC(${ARG1})}', NULLIF('${SQL_ESC(${ARG2})}',''), '${SQL_ESC(${ARG3})}', '${SQL_ESC(${ARG4})}', '${SQL_ESC(${ARG5})}', '${SQL_ESC(${ARG6})}', '${SQL_ESC(${ARG7})}', '${SQL_ESC(${ARG8})}', NULLIF('${SQL_ESC(${ARG9})}',''), NULLIF('${SQL_ESC(${ARG10})}',''), 'answered', ${ARG11}, ${ARG12}, ${ARG13}, ${ARG14}, NULLIF('${SQL_ESC(${ARG15})}',''), NULLIF('${SQL_ESC(${ARG16})}',''), NULLIF('${SQL_ESC(${ARG17})}','')) RETURNING id
Используется в: - extensions.conf — управление очередями
LOG_TTS_CALL
DSN: asterisk
Тип: READ
SQL:
INSERT INTO call_detail_records ( uniqueid, linkedid, call_timestamp, call_date, call_time, caller_id, called_number, call_status, duration, billsec, tenant_id, call_direction, call_category, request_id ) VALUES ( '${SQL_ESC(${ARG1})}', NULLIF('${SQL_ESC(${ARG2})}',''), '${SQL_ESC(${ARG3})}'::timestamp, '${SQL_ESC(${ARG4})}'::date, '${SQL_ESC(${ARG5})}'::time, '${SQL_ESC(${ARG6})}', '${SQL_ESC(${ARG7})}', '${SQL_ESC(${ARG8})}', ${ARG9}::integer, ${ARG10}::integer, CASE WHEN '${SQL_ESC(${ARG11})}' = '' OR '${SQL_ESC(${ARG11})}' = '0' THEN NULL ELSE ${ARG11}::integer END, 'tts_outgoing', 'tts', '${SQL_ESC(${ARG12})}' ) RETURNING id
REMOVE_QUEUE_MEMBER
DSN: asterisk
Тип: READ
SQL:
DELETE FROM queue_members WHERE interface = '${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG2} RETURNING operator_num
Используется в: - extensions.conf — управление очередями
SET_ENDPOINT_LANG
DSN: asterisk
Тип: READ
SQL:
UPDATE ps_endpoints SET ui_language = '${SQL_ESC(${ARG1})}' WHERE id = '${SQL_ESC(${ARG2})}' RETURNING ui_language
SET_MEMBER_PAUSE
DSN: asterisk
Тип: READ
SQL:
UPDATE queue_members SET paused = ${ARG2}, pause_reason = '${SQL_ESC(${ARG3})}', paused_at = CASE WHEN ${ARG2} = 1 THEN CURRENT_TIMESTAMP ELSE NULL END WHERE operator_num = '${SQL_ESC(${ARG1})}' AND tenant_id = ${ARG4} RETURNING queue_name
asterisk
DSN: asterisk
Тип: ``
SQL: