# By: Riasat Ullah # This file contains db queries for live call routing. from dbqueries import db_services from objects.live_call_routing import LiveCallRouting from psycopg2 import errorcodes from utils import constants, errors, helpers, key_manager, times, var_names from uuid import UUID from validations import configuration_validator, string_validator import configuration import datetime import json import psycopg2 def create_live_call_routing(conn, timestamp, org_id, routing_name, routing_description, phone_iso_code, phone_number, phone_number_type, vendor, vendor_phone_id, greeting_text, greeting_audio_filename, greeting_audio_location, greeting_audio_url, ending_text, ending_audio_filename, ending_audio_location, ending_audio_url, default_service, option_services, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voice_mail, record_call, prompt_call_acceptance, prompt_format, incident_title_format): ''' Create a live call routing specification. :param conn: db connection :param timestamp: timestamp when this live call routing specification is being created :param org_id: ID of the organization the live call routing is for :param routing_name: short name for the live call routing specification :param routing_description: description for the live call routing :param phone_iso_code: ISO code of the country the phone number is for :param phone_number: assigned phone number in the format +xxxxxxxxxxxx :param phone_number_type: type of phone_number :param vendor: the vendor who issued the phone number :param vendor_phone_id: unique identifier provided by the vendor for this phone number (Twilio -> SID) :param greeting_text: the greeting text which will be converted to speech and played when the user calls :param greeting_audio_filename: (str) filename of the greeting audio (only if greeting text not provided) :param greeting_audio_location: (str) s3 key location of the greeting audio (only if greeting text not provided) :param greeting_audio_url: (str) http url to fetch the audio with (only if greeting text no provided) :param ending_text: the ending note which will be converted to speech and played before ending the call :param ending_audio_filename: (str) filename of the ending audio (only if ending text not provided) :param ending_audio_location: (str) s3 key location of the ending audio (only if ending text not provided) :param ending_audio_url: (str) http url to fetch the audio with (only if ending text not provided) :param default_service: (int) ID of the service the call will be forwarded to by default :param option_services: (dict) options mapped to forwarding service IDs - {option: service ID, ...} :param text_language: (str) language the greeting and ending texts are in :param male_voice: (boolean) True if the text to speech voice should be male; False otherwise :param max_forwarding_users: (int) maximum number of users who the call should be forwarded to :param forwarding_timeout: (int) number of seconds after which each forwarding attempt will be ended :param incident_urgency: (int) urgency the created incident will be set to :param block_numbers: (list of str) list of phone numbers to block or disallow :param show_caller_id: (boolean) True if the incoming caller's phone number should be shown when forwarding to on-call users or the routing phone number should be shown :param resolve_answered_calls: (boolean) True if the incidents should be auto-resolved if the call is answered :param resolve_unanswered_calls: (boolean) True if the incidents should be auto-resolved if the call is not answered :param to_alert: (boolean) True if alert notifications should be sent out if the incident remains unresolved :param record_voice_mail: (boolean) True if voice mail should be prompted and recorded :param record_call: (boolean) True if the call should be recorded :param prompt_call_acceptance: (boolean) True if the responder should be prompted to accept the call :param prompt_format: (str) format of the prompt to play :param incident_title_format: (str) format of the title to create the incident with :return: (str) concealed live call routing reference ID :errors: AssertionError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) assert phone_iso_code in configuration.allowed_live_call_routing_countries assert string_validator.is_phone_number(phone_number) assert phone_number_type in configuration.allowed_phone_number_types assert vendor in configuration.allowed_live_call_routing_vendors assert isinstance(vendor_phone_id, str) configuration_validator.validate_live_call_routing_data( routing_name, routing_description, greeting_text, greeting_audio_filename, greeting_audio_location, greeting_audio_url, ending_text, ending_audio_filename, ending_audio_location, ending_audio_url, default_service, option_services, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voice_mail, record_call, prompt_call_acceptance, prompt_format, incident_title_format ) default_serv_id, option_serv_ids = internalize_call_forwarding_services(conn, timestamp, org_id, default_service, option_services) option_serv_ids = json.dumps(option_serv_ids) if option_serv_ids is not None else None if max_forwarding_users is None: max_forwarding_users = configuration.call_routing_default_max_forwarding_users if forwarding_timeout is None: forwarding_timeout = configuration.call_routing_default_max_forwarding_timeout_seconds ref_id = key_manager.generate_reference_key() query = ''' select create_live_call_routing( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (timestamp, constants.end_timestamp, org_id, ref_id, routing_name, routing_description, phone_iso_code, phone_number, phone_number_type, vendor, vendor_phone_id, greeting_text, greeting_audio_filename, greeting_audio_location, greeting_audio_url, ending_text, ending_audio_filename, ending_audio_location, ending_audio_url, default_serv_id, option_serv_ids, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voice_mail, record_call, prompt_call_acceptance, prompt_format, incident_title_format,) try: conn.execute(query, query_params) return key_manager.conceal_reference_key(ref_id) except psycopg2.DatabaseError: raise def edit_live_call_routing(conn, timestamp, org_id, call_routing_ref_id, routing_name, routing_description, greeting_text, greeting_audio_filename, greeting_audio_location, greeting_audio_url, has_greeting_audio_changed, ending_text, ending_audio_filename, ending_audio_location, ending_audio_url, has_ending_audio_changed, default_service, option_services, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voice_mail, record_call, prompt_call_acceptance, prompt_format, incident_title_format): ''' Edit a live call routing specification. :param conn: db connection :param timestamp: timestamp when this live call routing specification is being created :param org_id: ID of the organization the live call routing is for :param call_routing_ref_id: (UUID) unmasked reference key of the live call routing :param routing_name: short name for the live call routing specification :param routing_description: description for the live call routing :param greeting_text: the greeting text which will be converted to speech and played when the user calls :param greeting_audio_filename: (str) filename of the greeting audio (only if greeting text not provided) :param greeting_audio_location: (str) s3 key location of the greeting audio (only if greeting text not provided) :param greeting_audio_url: (str) http url to fetch the audio with (only if greeting text no provided) :param has_greeting_audio_changed: (boolean) true if the greeting audio has been updated :param ending_text: the ending note which will be converted to speech and played before ending the call :param ending_audio_filename: (str) filename of the ending audio (only if ending text not provided) :param ending_audio_location: (str) s3 key location of the ending audio (only if ending text not provided) :param ending_audio_url: (str) http url to fetch the audio with (only if ending text not provided) :param has_ending_audio_changed: (boolean) true if the ending audio has been updated :param default_service: (int) ID of the service the call will be forwarded to by default :param option_services: (dict) options mapped to forwarding service IDs - {option: service ID, ...} :param text_language: (str) language the greeting and ending texts are in :param male_voice: (boolean) True if the text to speech voice should be male; False otherwise :param max_forwarding_users: (int) maximum number of users who the call should be forwarded to :param forwarding_timeout: (int) number of seconds after which each forwarding attempt will be ended :param incident_urgency: (int) urgency the created incident will be set to :param block_numbers: (list of str) list of phone numbers to block or disallow :param show_caller_id: (boolean) True if the incoming caller's phone number should be shown when forwarding to on-call users or the routing phone number should be shown :param resolve_answered_calls: (boolean) True if the incidents should be auto-resolved if the call is answered :param resolve_unanswered_calls: (boolean) True if the incidents should be auto-resolved if the call is not answered :param to_alert: (boolean) True if alert notifications should be sent out if the incident remains unresolved :param record_voice_mail: (boolean) True if voice mail should be prompted and recorded :param record_call: (boolean) True if the call should be recorded :param prompt_call_acceptance: (boolean) True if the responder should be prompted to accept the call :param prompt_format: (str) format of the prompt to play :param incident_title_format: (str) format of the title to create the incident with :errors: AssertionError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) assert isinstance(has_greeting_audio_changed, bool) assert isinstance(has_ending_audio_changed, bool) unmasked_ref_id = key_manager.unmask_reference_key(call_routing_ref_id) configuration_validator.validate_live_call_routing_data( routing_name, routing_description, greeting_text, greeting_audio_filename, greeting_audio_location, greeting_audio_url, ending_text, ending_audio_filename, ending_audio_location, ending_audio_url, default_service, option_services, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voice_mail, record_call, prompt_call_acceptance, prompt_format, incident_title_format ) default_serv_id, option_serv_ids = internalize_call_forwarding_services(conn, timestamp, org_id, default_service, option_services) option_serv_ids = json.dumps(option_serv_ids) if option_serv_ids is not None else None if max_forwarding_users is None: max_forwarding_users = configuration.call_routing_default_max_forwarding_users if forwarding_timeout is None: forwarding_timeout = configuration.call_routing_default_max_forwarding_timeout_seconds query = ''' select edit_live_call_routing( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (timestamp, constants.end_timestamp, org_id, unmasked_ref_id, routing_name, routing_description, greeting_text, greeting_audio_filename, greeting_audio_location, greeting_audio_url, has_greeting_audio_changed, ending_text, ending_audio_filename, ending_audio_location, ending_audio_url, has_ending_audio_changed, default_serv_id, option_serv_ids, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voice_mail, record_call, prompt_call_acceptance, prompt_format, incident_title_format,) try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) else: raise except psycopg2.DatabaseError: raise def add_live_call_routing_audio_info(conn, timestamp, org_id, call_routing_ref_id, greeting_audio_filename, greeting_audio_location, greeting_audio_url, ending_audio_filename, ending_audio_location, ending_audio_url): ''' Updates the greeting and ending audio file information only. This expects new audio files to have been stored. :param conn: db connection :param timestamp: timestamp when this live call routing specification is being created :param org_id: ID of the organization the live call routing is for :param call_routing_ref_id: (UUID) unmasked reference key of the live call routing :param greeting_audio_filename: (str) filename of the greeting audio (only if greeting text not provided) :param greeting_audio_location: (str) s3 key location of the greeting audio (only if greeting text not provided) :param greeting_audio_url: (str) http url to fetch the audio with (only if greeting text no provided) :param ending_audio_filename: (str) filename of the ending audio (only if ending text not provided) :param ending_audio_location: (str) s3 key location of the ending audio (only if ending text not provided) :param ending_audio_url: (str) http url to fetch the audio with (only if ending text not provided) :errors: AssertionError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) unmasked_ref_id = key_manager.unmask_reference_key(call_routing_ref_id) assert (greeting_audio_filename is not None and greeting_audio_location is not None and greeting_audio_url is not None)\ or (ending_audio_filename is not None and ending_audio_location is not None and ending_audio_url is not None) query_params = {'timestamp': timestamp, 'org_id': org_id, 'ref_id': unmasked_ref_id} upd_val = [] if greeting_audio_filename is not None: upd_val.append('greeting_audio_filename = %(grt_name)s') upd_val.append('greeting_audio_location = %(grt_loc)s') upd_val.append('greeting_audio_url = %(grt_url)s') query_params['grt_name'] = greeting_audio_filename query_params['grt_loc'] = greeting_audio_location query_params['grt_url'] = greeting_audio_url if ending_audio_filename is not None: upd_val.append('ending_audio_filename = %(end_name)s') upd_val.append('ending_audio_location = %(end_loc)s') upd_val.append('ending_audio_url = %(end_url)s') query_params['end_name'] = ending_audio_filename query_params['end_loc'] = ending_audio_location query_params['end_url'] = ending_audio_url # We are directly updating the entry because the history of the actual file location # does not seem to be important since the audio files are updated or removed when changes are made. query = ''' update live_call_routing set {0} where call_routing_ref_id = %(ref_id)s and organization_id = %(org_id)s and start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s; '''.format(','.join(upd_val)) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def delete_live_call_routing(conn, timestamp, org_id, call_routing_ref_id): ''' Delete a live call routing specification. :param conn: db connection :param timestamp: timestamp when this request is being made :param org_id: ID of the organization the routing is for :param call_routing_ref_id: reference ID of the live call routing specification (concealed) :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(org_id, int) unmasked_ref_id = key_manager.unmask_reference_key(call_routing_ref_id) query = ''' do $body$ declare lcr_id live_call_routing.call_routing_id%%type; begin update live_call_routing set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and call_routing_ref_id = %(rout_id)s returning call_routing_id into lcr_id; if lcr_id is null then raise check_violation; end if; end; $body$ ''' query_params = {'timestamp': timestamp, 'org_id': org_id, 'rout_id': unmasked_ref_id} try: conn.execute(query, query_params) except psycopg2.IntegrityError as e: if e.pgcode == errorcodes.CHECK_VIOLATION: raise LookupError(errors.err_unknown_resource) else: raise except psycopg2.DatabaseError: raise def get_live_call_routing_details(conn, timestamp, organization_id=None, call_routing_ref_id=None): ''' Get the details of a specific live call routing. It only gets details for external use. :param conn: db connection :param timestamp: timestamp when this request was made :param organization_id: (int) ID of the organization that the live call routing belongs to :param call_routing_ref_id: concealed reference ID for the live call routing :return: (list of dict) of live call routing details :errors: AssertionError, DatabaseError, LookupError, SystemError ''' assert isinstance(timestamp, datetime.datetime) query_params = {'timestamp': timestamp} conditions = [] if organization_id is not None: assert isinstance(organization_id, int) conditions.append(" organization_id = %(org_id)s ") query_params['org_id'] = organization_id if call_routing_ref_id is not None: unmasked_ref_id = key_manager.unmask_reference_key(call_routing_ref_id) conditions.append(" call_routing_ref_id = %(ref_id)s ") query_params['ref_id'] = unmasked_ref_id query = ''' with t1 as( select call_routing_id, call_routing_ref_id, routing_name, routing_description, phone_iso_code, phone_number, phone_number_type, greeting_text, greeting_audio_filename, greeting_audio_url, ending_text, ending_audio_filename, ending_audio_url, default_forwarding_serviceid, option_forwarding_services, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voicemail, record_call, prompt_call_acceptance, prompt_format, incident_title_format from live_call_routing where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s {0} ) , t2 as ( select t1.call_routing_id, -1 as opt, t1.default_forwarding_serviceid as srv from t1 union select t1.call_routing_id, json_object_keys(option_forwarding_services)::int as opt, (option_forwarding_services->>json_object_keys(option_forwarding_services))::int as srv from t1 where json_typeof(option_forwarding_services) != 'null' ) , t3 as ( select t2.*, service_ref_id from t2 join services on t2.srv = services.serviceid where services.start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s ) , t4 as ( select call_routing_id, service_ref_id as dft_srv_ref from t3 where opt = -1 ) , t5 as ( select call_routing_id, json_object_agg(opt, service_ref_id) as opt_srv_ref from t3 where opt >= 0 group by call_routing_id ) select t1.*, t4.dft_srv_ref, t5.opt_srv_ref from t1 join t4 using (call_routing_id) left join t5 using (call_routing_id); '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = [] for lcr_id, lcr_ref, rout_name, rout_desc, iso_code, phone, phone_type, greet_text, greet_fn, greet_url, \ end_text, end_fn, end_url, dft_serv, opt_servs, text_lang, male_vc, max_users, timeout, inc_urg, \ block_nums, show_clr, res_ans, res_unans, to_alert, record_vm, record_call, prompt_call_acc, \ prompt_fmt, inc_ttl_fmt, dft_serv_ref, opt_serv_refs in result: data.append({ var_names.call_routing_ref_id: key_manager.conceal_reference_key(lcr_ref), var_names.routing_name: rout_name, var_names.description: rout_desc, var_names.iso_country_code: iso_code, var_names.phone: phone, var_names.phone_type: phone_type, var_names.greeting_text: greet_text, var_names.greeting_audio_filename: greet_fn, var_names.greeting_audio_url: greet_url, var_names.ending_text: end_text, var_names.ending_audio_filename: end_fn, var_names.ending_audio_url: end_url, var_names.default_forwarding_service: key_manager.conceal_reference_key(dft_serv_ref), var_names.option_forwarding_services: {k: key_manager.conceal_reference_key(UUID(v)) for k, v in opt_serv_refs.items()} if opt_serv_refs is not None else None, var_names.text_language: text_lang, var_names.is_male_voice: male_vc, var_names.max_forwarding_users: max_users, var_names.forwarding_timeout: timeout, var_names.urgency_level: inc_urg, var_names.block_numbers: block_nums, var_names.show_caller_id: show_clr, var_names.resolve_answered_calls: res_ans, var_names.resolve_unanswered_calls: res_unans, var_names.to_alert: to_alert, var_names.record_voicemail: record_vm, var_names.record_call: record_call, var_names.prompt_call_acceptance: prompt_call_acc, var_names.prompt_format: prompt_fmt, var_names.incident_title_format: inc_ttl_fmt }) return data except psycopg2.DatabaseError: raise def get_live_call_routing(conn, timestamp, routing_phone, caller_phone): ''' For internal user only. Get the details of a specific live call routing based on the routing phone number. :param conn: db connection :param timestamp: timestamp when this request was made :param routing_phone: the phone number that is associated with the routing :param caller_phone: the phone number of the caller :return: Live Call Routing object :errors: AssertionError, DatabaseError, LookupError, SystemError ''' assert isinstance(timestamp, datetime.datetime) assert string_validator.is_phone_number(routing_phone) assert string_validator.is_phone_number(caller_phone) query = " select * from get_live_call_routing(%s, %s, %s); " query_params = (timestamp, routing_phone, caller_phone,) try: result = conn.fetch(query, query_params) if len(result) == 1: for lcr, org_id, ref_id, rout_name, iso_code, phone, phone_type, greet_text, greet_fn, greet_url, \ end_text, end_fn, end_url, dft_serv, opt_servs, text_lang, male_vc, max_users, timeout, inc_urg, \ block_nums, show_clr, res_ans, res_unans, to_alert, record_vm, record_call, prompt_call_acc, \ prompt_fmt, inc_ttl_fmt, usr_id, cl_pol, cl_ppl_id, cl_name, cl_email, \ cl_grp_id, cl_grp_name in result: if cl_pol is None and cl_ppl_id is None and cl_grp_id is None: caller_details = None else: caller_details = { var_names.user_id: usr_id, var_names.policy_id: cl_pol, var_names.people_id: cl_ppl_id, var_names.name: cl_name, var_names.email: cl_email, var_names.group_id: cl_grp_id, var_names.group_name: cl_grp_name } lcr_obj = LiveCallRouting(lcr, org_id, rout_name, iso_code, phone, phone_type, greet_text, greet_fn, greet_url, end_text, end_fn, end_url, dft_serv, opt_servs, text_lang, male_vc, max_users, timeout, inc_urg, block_nums, show_clr, res_ans, res_unans, to_alert, record_vm, record_call, call_routing_ref_id=ref_id, caller=caller_phone, prompt_call_acceptance=prompt_call_acc, prompt_format=prompt_fmt, incident_title_format=inc_ttl_fmt, caller_details=caller_details) return lcr_obj elif len(result) == 0: raise LookupError(errors.err_unknown_resource) else: raise SystemError(errors.err_internal_multiple_entries_found) except psycopg2.DatabaseError: raise def list_live_call_routing(conn, timestamp, organization_id): ''' Get the list of live call routing specifications that belong to an organization. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization whose details should be retrieved :return: (list of dict) ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' with t1 as( select call_routing_id, call_routing_ref_id, routing_name, routing_description, phone_iso_code, phone_number, phone_number_type, default_forwarding_serviceid, option_forwarding_services from live_call_routing where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) , t2 as ( select t1.call_routing_id, t1.default_forwarding_serviceid as srv from t1 union select t1.call_routing_id, (option_forwarding_services->>json_object_keys(option_forwarding_services))::int as srv from t1 where json_typeof(option_forwarding_services) != 'null' ) , t3 as ( select t2.*, service_ref_id, service_name from t2 join services on t2.srv = services.serviceid where services.start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s ) select t1.call_routing_ref_id, routing_name, routing_description, phone_iso_code, phone_number, phone_number_type, json_agg(json_build_object( 'service_name', t3.service_name, 'service_ref_id', t3.service_ref_id )) from t1 join t3 using (call_routing_id) group by t1.call_routing_ref_id, routing_name, routing_description, phone_iso_code, phone_number, phone_number_type order by routing_name; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) data = [] if len(result) > 0: for ref_id, rout_name, rout_desc, iso, phone, phone_type, assoc_srvs in result: data.append({ var_names.call_routing_ref_id: key_manager.conceal_reference_key(ref_id), var_names.routing_name: rout_name, var_names.description: rout_desc, var_names.iso_country_code: iso, var_names.phone: phone, var_names.phone_type: phone_type, var_names.associated_services: [ [item[var_names.service_name], key_manager.conceal_reference_key(UUID(item[var_names.service_ref_id]))] for item in assoc_srvs] }) return data except psycopg2.DatabaseError: raise def internalize_call_forwarding_services(conn, timestamp, org_id, default_service, option_services): ''' Internalize the call forwarding service references to service IDs. :param conn: db connection :param timestamp: timestamp when this live call routing specification is being created :param org_id: ID of the organization the live call routing is for :param default_service: concealed reference ID of the service the call will be forwarded to by default :param option_services: (dict) options mapped to forwarding service references - {option: concealed service ref, } :return: (tuple) (int, dict) -> service ID, option service IDs ''' # unmask the service reference ID default_service = key_manager.unmask_reference_key(default_service) all_serv_unmasked = [default_service] if option_services is not None: for key in option_services: option_services[key] = key_manager.unmask_reference_key(option_services[key]) all_serv_unmasked += list(option_services.values()) serv_ref_map = db_services.list_service_ids_from_ref_ids(conn, timestamp, org_id, all_serv_unmasked, unmasked=True, as_dict=True) # map the reference keys to their IDs dft_srv_id, opt_srv_ids = None, None if default_service in serv_ref_map: dft_srv_id = serv_ref_map[default_service] else: raise LookupError(errors.err_unknown_resource) if option_services is not None: opt_srv_ids = dict() for key in option_services: if option_services[key] in serv_ref_map: opt_srv_ids[key] = serv_ref_map[option_services[key]] else: raise LookupError(errors.err_unknown_resource) return dft_srv_id, opt_srv_ids def log_live_call(conn, timestamp, organization_id, call_routing_id, vendor, vendor_event_id, from_number, to_number, instance_id=None, from_iso=None, to_iso=None, call_status=None): ''' Create a live call routing instance. :param conn: db connection :param timestamp: timestamp when this entry is being made :param organization_id: ID of the organization the live call instance belongs to :param call_routing_id: the live call routing ID the instance is associated to :param vendor: vendor who is handling the routing (TWILIO) :param vendor_event_id: the unique call ID provided by the vendor (Twilio -> CallSid) :param from_number: the phone number the call came from (format: +xxxxxxxxxxxx) :param to_number: the phone number the call came to (format: +xxxxxxxxxxxx) :param instance_id: (int) ID of the instance that was created for this call :param from_iso: ISO country code of the caller :param to_iso: ISO country code of the receiving number :param call_status: the status of the call :return: ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(call_routing_id, int) assert vendor in configuration.allowed_live_call_routing_vendors assert isinstance(vendor_event_id, str) assert string_validator.is_phone_number(from_number) assert string_validator.is_phone_number(to_number) if instance_id is not None: assert isinstance(instance_id, int) if from_iso is not None: assert from_iso in constants.all_country_codes.keys() if to_iso is not None: assert to_iso in constants.all_country_codes.keys() if call_status is not None: assert call_status in configuration.allowed_live_call_statuses end_time = timestamp if call_status in [constants.blocked_state, constants.suppressed_state] else None query = ''' insert into live_call_logs ( call_routing_id, vendor, vendor_event_id, instanceid, organization_id, call_date, call_start_timestamp, call_end_timestamp, from_iso_code, from_number, to_iso_code, to_number, call_status ) values ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ); ''' query_params = (call_routing_id, vendor, vendor_event_id, instance_id, organization_id, timestamp.date(), timestamp, end_time, from_iso, from_number, to_iso, to_number, call_status,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_live_call_routing_deletion_details(conn, timestamp, organization_id, call_routing_ref_id=None, vendor=None): ''' Gets the vendor phone ID for the live call routing phone number. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization the routing belongs to :param call_routing_ref_id: (concealed) reference ID of the routing :param vendor: (str) the vendor who manages the number :return: (dict) -> {vendor_phone_id: , greeting_audio_location: , ending_audio_location: } :errors: AssertionError, DatabaseError, LookupError, SystemError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query_params = {'timestamp': timestamp, 'org_id': organization_id} conditions = [] if vendor is not None: assert vendor in configuration.allowed_live_call_routing_vendors conditions.append(' vendor = %(vnd)s ') query_params['vnd'] = vendor if call_routing_ref_id is not None: unmasked_ref = key_manager.unmask_reference_key(call_routing_ref_id) conditions.append(' call_routing_ref_id = %(call_ref)s ') query_params['call_ref'] = unmasked_ref query = ''' select vendor, vendor_phone_id, greeting_audio_location, ending_audio_location from live_call_routing where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s {0}; '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '') try: result = conn.fetch(query, query_params) data = [] for vnd, vnd_ph_id, greet_loc, end_loc in result: data.append({ var_names.vendor_name: vnd, var_names.vendor_phone_id: vnd_ph_id, var_names.greeting_audio_location: greet_loc, var_names.ending_audio_location: end_loc, }) return data except psycopg2.DatabaseError: raise def get_service_associated_live_call_routes(conn, timestamp, org_id, serv_ref): ''' Get the names of the live call routes that are associated with a given service. :param conn: db connection :param timestamp: timestamp when this request is being made :param org_id: ID of the organization :param serv_ref: (str) concealed service reference ID :return: (list) - [routing name 1, routing name 2, ...] ''' matching_routes = [] # set id_on_ref to true to have rules of service equality be mapped on service_ref_id org_live_routes = get_live_call_routing_details(conn, timestamp, organization_id=org_id) for rout in org_live_routes: dft_srv = rout[var_names.default_forwarding_service] opt_srv = rout[var_names.option_forwarding_services] if dft_srv == serv_ref or (opt_srv is not None and serv_ref in opt_srv.values()): matching_routes.append(rout[var_names.routing_name]) return matching_routes def get_basic_live_call_routing_list(conn, timestamp, organization_id): ''' Get the basic live call routing list. :param conn: db connection :param timestamp: timestamp this request is being made on :param organization_id: ID of the organization :return: (list of list) -> [ [routing name, routing ref id], ... ] :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select routing_name, call_routing_ref_id from live_call_routing where start_timestamp <= %s and end_timestamp > %s and organization_id = %s; ''' query_params = (timestamp, timestamp, organization_id,) try: result = conn.fetch(query, query_params) data = [] for name_, key_ in result: data.append([name_, key_manager.conceal_reference_key(key_)]) return data except psycopg2.DatabaseError: raise def get_live_call_routing_analytics_details(conn, start_date, end_date, org_id=None, call_routing_refs=None, service_refs=None, team_refs=None): ''' Get details of incoming calls that have "ended". The details are presented in the format needed for handling live call analytics. :param conn: db connection :param start_date: start period :param end_date: end period :param org_id: (int or list) ID of the organization(s) :param call_routing_refs: (list) of unmasked live call routing ref ids :param service_refs: (list) of unmasked service ref ids :param team_refs: (list) of unmasked team ref ids :return: (dict of list of dict) of details (mapped to org ID) :errors: AssertionError, DatabaseError ''' assert isinstance(start_date, datetime.date) assert isinstance(end_date, datetime.date) assert start_date <= end_date call_events = [constants.call_answered_event, constants.call_ended_event, constants.call_forwarding_event, constants.call_outgoing_event, constants.call_voicemail_prompt_event] query_params = {'start_dt': start_date, 'end_dt': end_date, 'call_evns': call_events, 'fwd_evn': constants.call_forwarding_event} cond = [] if org_id is not None: cond.append(" lcl.organization_id = any(%(org_id)s) ") query_params["org_id"] = helpers.get_int_list(org_id) if call_routing_refs is not None: assert isinstance(call_routing_refs, list) cond.append(''' call_routing_id in ( select call_routing_id from live_call_routing where start_timestamp <= lcl.call_start_timestamp and end_timestamp > lcl.call_start_timestamp and call_routing_ref_id = any(%(call_refs)s) ) ''') query_params['call_refs'] = call_routing_refs if service_refs is not None: assert isinstance(service_refs, list) cond.append(''' tasks.serviceid in ( select serviceid from services where start_timestamp <= lcl.call_start_timestamp and end_timestamp > lcl.call_start_timestamp and service_ref_id = any(%(serv_refs)s) ) ''') query_params['serv_refs'] = service_refs if team_refs is not None: assert isinstance(team_refs, list) cond.append(''' tasks.serviceid in ( select component_id from team_components where component_type_id = %(comp_type)s and start_timestamp <= inst.instance_timestamp and end_timestamp > inst.instance_timestamp and team_id in ( select team_id from teams where start_timestamp <= inst.instance_timestamp and end_timestamp > inst.instance_timestamp and team_ref_id = any(%(tm_refs)s) ) ) ''') query_params['tm_refs'] = team_refs query_params['comp_type'] = configuration.service_component_type_id query = ''' select lcl.organization_id, log_id, vendor, vendor_event_id, timezone(org.organization_timezone, timezone('UTC', lcl.call_start_timestamp)) as local_call_time, call_start_timestamp, call_end_timestamp, call_routing_id, from_iso_code, from_number, to_iso_code, to_number, call_status, call_recording_url, answer_timestamp, answered_by, forwarding_count, tasks.serviceid, inst.organization_instanceid, json_agg(json_build_object( 'event_timestamp', event_timestamp, 'event_type', event_type, 'forward_to', users.user_id, 'iso_country_code', users.iso_country_code ) order by event_timestamp) filter (where lcl.instanceid is not null and ie.instanceid is not null) as inst_events from live_call_logs as lcl join organizations as org on org.organization_id = lcl.organization_id and org.start_timestamp <= lcl.call_start_timestamp and org.end_timestamp > lcl.call_start_timestamp left join task_instances as inst on inst.instanceid = lcl.instanceid left join tasks on tasks.taskid = inst.taskid and tasks.start_timestamp <= inst.instance_timestamp and tasks.end_timestamp > inst.instance_timestamp left join instance_events as ie on ie.instanceid = lcl.instanceid and ie.event_type = any(%(call_evns)s) left join users on event_type = %(fwd_evn)s and (event_log->>'forward_to')::int = users.user_id and users.start_timestamp <= inst.instance_timestamp and users.end_timestamp > inst.instance_timestamp where timezone(org.organization_timezone, timezone('UTC', lcl.call_start_timestamp))::date >= %(start_dt)s and timezone(org.organization_timezone, timezone('UTC', lcl.call_start_timestamp))::date <= %(end_dt)s and lcl.call_end_timestamp is not null {0} group by lcl.organization_id, log_id, vendor, vendor_event_id, local_call_time, call_start_timestamp, call_end_timestamp, call_routing_id, from_iso_code, from_number, to_iso_code, to_number, call_status, call_recording_url, answer_timestamp, answered_by, forwarding_count, tasks.serviceid, inst.organization_instanceid order by call_start_timestamp desc; '''.format(' and ' + ' and '.join(cond) if len(cond) > 0 else '') try: result = conn.fetch(query, query_params) data = dict() for ogid, log_id, vnd, vnd_evn_id, local_time, call_start, call_end, rout_id, from_iso, from_num, \ to_iso, to_num, call_sts, rcd_url, ans_tmsp, ans_by, fwd_count, fwd_serv, inst_id, evnts in result: if ogid not in data: data[ogid] = [] # convert event_timestamp in instance events from string to datetime.datetime if evnts is not None: for item in evnts: item[var_names.event_timestamp] = times.get_timestamp_from_string(item[var_names.event_timestamp]) data[ogid].append({ var_names.log_id: log_id, var_names.vendor_name: vnd, var_names.vendor_id: vnd_evn_id, var_names.call_time: local_time, var_names.call_start_timestamp: call_start, var_names.call_end_timestamp: call_end, var_names.call_routing_id: rout_id, var_names.from_iso: from_iso, var_names.from_number: from_num, var_names.to_iso: to_iso, var_names.to_number: to_num, var_names.call_status: call_sts, var_names.recording_url: rcd_url, var_names.answer_timestamp: ans_tmsp, var_names.answered_by: ans_by, var_names.forwarding_count: fwd_count, var_names.service_id: fwd_serv, var_names.organization_instance_id: inst_id, var_names.events: evnts }) return data except psycopg2.DatabaseError: raise def get_current_component_names_for_call_routing_analytics(conn, timestamp, start_date, org_id): ''' Get all organization live call routing, services and users needed for analytics. :param conn: db connection :param timestamp: timestamp when this request is being made :param start_date: start period :param org_id: ID of the organization :return: (tuple of dicts) call routing, services, users :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(timestamp, datetime.datetime) assert isinstance(start_date, datetime.date) assert isinstance(org_id, int) query_params = {'timestamp': timestamp, 'start_dt': start_date, 'org_id': org_id} query = ''' with routing_timeline as ( select call_routing_id, max(end_timestamp) as max_end from live_call_routing where start_timestamp <= %(timestamp)s and end_timestamp > %(start_dt)s and organization_id = %(org_id)s group by call_routing_id ) , org_routing as ( select lcr.organization_id, json_agg(json_build_object( 'call_routing_id', lcr.call_routing_id, 'call_routing_ref_id', call_routing_ref_id, 'routing_name', routing_name )) as olc_routs from live_call_routing as lcr join routing_timeline on lcr.call_routing_id = routing_timeline.call_routing_id and lcr.end_timestamp = routing_timeline.max_end group by lcr.organization_id ) , services_timeline as ( select serviceid, max(end_timestamp) as max_end from services where start_timestamp <= %(timestamp)s and end_timestamp > %(start_dt)s and organization_id = %(org_id)s group by serviceid ) , org_services as ( select services.organization_id, json_agg(json_build_object( 'service_id', services.serviceid, 'service_ref_id', service_ref_id, 'service_name', service_name )) as olc_servs from services join services_timeline on services.serviceid = services_timeline.serviceid and services.end_timestamp = services_timeline.max_end group by services.organization_id ) , users_timeline as ( select user_id, max(end_timestamp) as max_end from users where start_timestamp <= %(timestamp)s and end_timestamp > %(start_dt)s and organization_id = %(org_id)s group by user_id ) , org_users as ( select users.organization_id, json_agg(json_build_object( 'user_id', users.user_id, 'name', first_name || ' ' || last_name, 'preferred_username', preferred_username )) as olc_users from users join users_timeline on users.user_id = users_timeline.user_id and users.end_timestamp = users_timeline.max_end group by users.organization_id ) select org_users.organization_id, olc_routs, olc_servs, olc_users from org_users left join org_services using(organization_id) left join org_routing using(organization_id); ''' try: result = conn.fetch(query, query_params) org_routs, org_services, org_users = dict(), dict(), dict() if len(result) > 0: if result[0][1] is not None: for item in result[0][1]: org_routs[item[var_names.call_routing_id]] = { var_names.call_routing_ref_id: key_manager.conceal_reference_key( UUID(item[var_names.call_routing_ref_id])), var_names.routing_name: item[var_names.routing_name] } if result[0][2] is not None: for item in result[0][2]: org_services[item[var_names.service_id]] = { var_names.service_ref_id: key_manager.conceal_reference_key( UUID(item[var_names.service_ref_id])), var_names.service_name: item[var_names.service_name] } if result[0][3] is not None: for item in result[0][3]: org_users[item[var_names.user_id]] = { var_names.preferred_username: item[var_names.preferred_username], var_names.name: item[var_names.name] } return org_routs, org_services, org_users except psycopg2.DatabaseError: raise def get_live_call_routing_country_specific_rates(conn, start_date): ''' Get minute rates of all the live call routing countries. :param conn: db connection :param start_date: start period :return: (dict) -> {country code: rate, ...} :errors: AssertionError, DatabaseError ''' assert isinstance(start_date, datetime.date) query = ''' select iso_country_code, call_minute_rate, local_number_rate, mobile_number_rate, toll_free_number_rate, text_rate from live_call_routing_countries where start_date <= %s and end_date > %s; ''' query_params = (start_date, start_date,) try: result = conn.fetch(query, query_params) data = dict() for iso_, minute_, local_, mobile_, toll_free_, txt_ in result: data[iso_] = { var_names.minute_rate: float(minute_) if minute_ is not None else None, var_names.local_number_rate: float(local_) if local_ is not None else None, var_names.mobile_number_rate: float(mobile_) if mobile_ is not None else None, var_names.toll_free_number_rate: float(toll_free_) if toll_free_ is not None else None, var_names.text_rate: float(txt_) if txt_ is not None else None } return data except psycopg2.DatabaseError: raise def get_live_call(conn, timestamp, vendor, vendor_id): ''' For internal user only. Get the live call routing object with details of the call given the vendor id (call SID). :param conn: db connection :param timestamp: timestamp when this request was made :param vendor: the vendor who processed the call :param vendor_id: vendor side ID to identify the call :return: LiveCallRouting object :errors: AssertionError, DatabaseError, LookupError, SystemError ''' assert isinstance(timestamp, datetime.datetime) assert vendor in configuration.allowed_live_call_routing_vendors assert isinstance(vendor_id, str) query = ''' select cr.call_routing_id, cr.organization_id, cr.call_routing_ref_id, routing_name, phone_iso_code, phone_number, phone_number_type, greeting_text, greeting_audio_filename, greeting_audio_url, ending_text, ending_audio_filename, ending_audio_url, default_forwarding_serviceid, option_forwarding_services, text_language, male_voice, max_forwarding_users, forwarding_timeout, incident_urgency, block_numbers, show_caller_id, resolve_answered_calls, resolve_unanswered_calls, to_alert, record_voicemail, record_call, prompt_call_acceptance, lcl.from_number, lcl.vendor_event_id, lcl.instanceid, lcl.call_status, lcl.forwarding_count, lcl.vendor_call_duration from live_call_logs as lcl join live_call_routing as cr using(call_routing_id) where lcl.vendor = %(vndr)s and lcl.vendor_event_id = %(vndr_id)s and cr.start_timestamp <= %(timestamp)s and cr.end_timestamp > %(timestamp)s; ''' query_params = {'timestamp': timestamp, 'vndr': vendor, 'vndr_id': vendor_id} try: result = conn.fetch(query, query_params) if len(result) == 1: for lcr, org_id, ref_id, rout_name, iso_code, phone, phone_type, greet_text, greet_fn, greet_url,\ end_text, end_fn, end_url, dft_serv, opt_servs, text_lang, male_vc, max_users, timeout, inc_urg,\ block_nums, show_clr, res_ans, res_unans, to_alert, record_vm, record_call, prompt_call_acc,\ from_num, vndr_id, inst_id, call_sts, fwd_count, call_dur in result: lcr_obj = LiveCallRouting(lcr, org_id, rout_name, iso_code, phone, phone_type, greet_text, greet_fn, greet_url, end_text, end_fn, end_url, dft_serv, opt_servs, text_lang, male_vc, max_users, timeout, inc_urg, block_nums, show_clr, res_ans, res_unans, to_alert, record_vm, record_call, call_routing_ref_id=ref_id, caller=from_num, twilio_call_sid=vndr_id, instance_id=inst_id, call_status=call_sts, forwarding_count=fwd_count, call_duration=call_dur, prompt_call_acceptance=prompt_call_acc) return lcr_obj return None except psycopg2.DatabaseError: raise def store_live_call_phone_address(conn, timestamp, organization_id, vendor_address_id): ''' Store the vendor provided ID of the address of an organization associated to phone numbers issued from them. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization who the address belongs to :param vendor_address_id: ID of the address provided by the vendor :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(vendor_address_id, str) query = "insert into live_call_phone_addresses values(%s, %s, %s, %s, %s, %s);" query_params = (organization_id, timestamp, constants.end_timestamp, constants.twilio, vendor_address_id, None,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_live_call_phone_address(conn, timestamp, organization_id): ''' Get the vendor phone address associated to an organization. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :return: (str) vendor address ID :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) query = ''' select vendor_address_id from live_call_phone_addresses where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id} try: result = conn.fetch(query, query_params) if len(result) == 0: return None return result[0][0] except psycopg2.DatabaseError: raise def get_return_call_meta_data(conn, timestamp, organization_id, caller_number, organization_instance_id=None): ''' Get the instance ID and task ID of a live call that was received earlier to check if an outbound call is a return call or not. This will help to determine if a new instance needs to be created or not. :param conn: db connection :param timestamp: timestamp :param organization_id: ID of the organization :param caller_number: the number the call would have been received from :param organization_instance_id: organization instance ID :return: (tuple) organization permission, instance ID, task ID; None if there is no match ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert string_validator.is_phone_number(caller_number) if organization_instance_id is not None: assert isinstance(organization_instance_id, int) query = ''' with t1 as ( select organization_id, permissions from organization_permissions where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s ) , t2 as ( select ti.organization_id, ti.instanceid, ti.taskid from task_instances as ti join live_call_logs as lcl using (organization_id, instanceid) where ti.organization_id = %(org_id)s and %(org_inst_id)s is not null and organization_instanceid = %(org_inst_id)s and is_open = true and lcl.from_number = %(caller_phn)s ) select t1.permissions, t2.instanceid, t2.taskid from t1 left join t2 using(organization_id); ''' query_params = {'timestamp': timestamp, 'org_id': organization_id, 'org_inst_id': organization_instance_id, 'caller_phn': caller_number} try: result = conn.fetch(query, query_params) if len(result) > 0: return result[0][0], result[0][1], result[0][2] return None, None, None except psycopg2.DatabaseError: raise def add_call_recording(conn, vendor, vendor_id, recording_url): ''' Add a recording url for a call. :param conn: db connection :param vendor: the vendor who the recording is with :param vendor_id: vendor specific unique ID for the call (Twilio -> CallSid) :param recording_url: the url from where the recording can be retrieved :errors: AssertionError, DatabaseError ''' assert vendor in configuration.allowed_live_call_routing_vendors assert isinstance(vendor_id, str) assert string_validator.is_web_url(recording_url) query = "select add_call_recording(%s, %s, %s);" query_params = (vendor, vendor_id, recording_url,) try: conn.execute(query, query_params) except psycopg2.DatabaseError: raise def get_call_numbers(conn, timestamp, organization_id, instance_id): ''' Get the details of the caller and receiver numbers for a live call associated to a given incident. :param conn: db connection :param timestamp: timestamp when the request is made :param organization_id: organization ID :param instance_id: ID of the instance :return: (dict) -> {from_number: ..., to_number: ...} :error: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert isinstance(instance_id, int) query = ''' select from_number, to_number from live_call_logs as lcl join task_instances as ti using(organization_id, instanceid) where lcl.call_start_timestamp <= %(timestamp)s and lcl.organization_id = %(org_id)s and lcl.instanceid = %(inst_id)s and lcl.call_status != %(out_sts)s and ti.instance_timestamp <= %(timestamp)s and ti.is_open = true; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id, 'inst_id': instance_id, 'out_sts': constants.outgoing} try: result = conn.fetch(query, query_params) output = dict() for from_num, to_num in result: output = {var_names.from_number: from_num, var_names.to_number: to_num} return output except psycopg2.DatabaseError: raise def get_regulations_and_approval_id(conn, timestamp, organization_id, country_code, phone_type): ''' Check whether a number can be purchased given the phone number types and pre-approved bundle IDs along with the bundle IDs. :param conn: db connection :param timestamp: timestamp when the request was made :param organization_id: ID of the organization whose data is being requested :param country_code: ISO code of the country :param phone_type: 'local', 'national', 'mobile' :return: (tuple) can purchase (boolean), docs pending (boolean), req docs (dict), bundle ID (str), address ID (str) :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert country_code in configuration.allowed_live_call_routing_countries assert phone_type in [constants.local, constants.mobile, constants.national] query = ''' select direct_purchase, approval_required_purchase, manual_purchase, required_documents, reg.regulations from live_call_routing_countries as cou left join organization_live_call_routing_regulations as reg on cou.iso_country_code = reg.iso_country_code and reg.start_timestamp <= %(timestamp)s and reg.end_timestamp > %(timestamp)s and reg.organization_id = %(org_id)s and reg.vendor = %(vnd)s where cou.start_date <= %(timestamp)s and cou.end_date > %(timestamp)s and cou.iso_country_code = %(iso)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id, 'iso': country_code, 'vnd': constants.twilio} try: result = conn.fetch(query, query_params) can_purchase = False docs_pending = False if len(result) > 0: for dir_pur_, app_req_pur_, man_pur_, req_docs_, regs_ in result: bundle_id, address_id = None, None if regs_ is not None: for item in regs_: if item[var_names.phone_type] == phone_type: bundle_id = item[var_names.twilio_bundle_id] if var_names.twilio_address_id in item: address_id = item[var_names.twilio_address_id] if dir_pur_ is not None and phone_type in dir_pur_: can_purchase = True elif app_req_pur_ is not None and phone_type in app_req_pur_: if bundle_id is not None: can_purchase = True else: docs_pending = True elif man_pur_ is not None and phone_type is man_pur_: if bundle_id is None: docs_pending = True return can_purchase, docs_pending, req_docs_, bundle_id, address_id return can_purchase, docs_pending, None, None, None except psycopg2.DatabaseError: raise def get_pre_approved_phone_numbers(conn, timestamp, organization_id, iso_code, phone_number_type): ''' Get the list of pre-approved phone numbers an organization has access to. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization :param iso_code: ISO code of the country to search for :param phone_number_type: type of phone number :return: (list of list) -> [[friendly name, phone number], ...] :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert iso_code in configuration.allowed_live_call_routing_countries assert phone_number_type in configuration.allowed_phone_number_types query = ''' select friendly_name, phone_number from organization_pre_approved_phone_numbers where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and vendor = %(vnd)s and iso_country_code = %(iso_code)s and phone_number_type = %(phn_type)s; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id, 'vnd': constants.twilio, 'iso_code': iso_code, 'phn_type': phone_number_type} try: result = conn.fetch(query, query_params) data = [] for friendly_name, phone_number in result: data.append([friendly_name, phone_number]) return data except psycopg2.DatabaseError: raise def get_and_expire_pre_approved_phone_number(conn, timestamp, organization_id, iso_code, phone_number): ''' Get the vendor issued ID of a pre-approved phone number and expire the approval. :param conn: db connection :param timestamp: timestamp when this request is being made :param organization_id: ID of the organization to search for :param iso_code: ISO code of the country to search for :param phone_number: phone number to search for :return: (str) vendor phone ID :errors: AssertionError, DatabaseError ''' assert isinstance(timestamp, datetime.datetime) assert isinstance(organization_id, int) assert iso_code in configuration.allowed_live_call_routing_countries assert string_validator.is_phone_number(phone_number) query = ''' update organization_pre_approved_phone_numbers set end_timestamp = %(timestamp)s where start_timestamp <= %(timestamp)s and end_timestamp > %(timestamp)s and organization_id = %(org_id)s and vendor = %(vnd)s and iso_country_code = %(iso_code)s returning vendor_phone_id; ''' query_params = {'timestamp': timestamp, 'org_id': organization_id, 'vnd': constants.twilio, 'iso_code': iso_code} try: result = conn.fetch(query, query_params) if len(result) > 0: return result[0][0] return None except psycopg2.DatabaseError: raise