def ucreporter_settings_mainpage():
"""отрировка главной странички
:return:
"""
operation_start_time = datetime.now()
html_page_title = 'UC Reporter administration'
html_page_header = 'UC Reporter administration'
form_navigation = SelectNavigation(meta={'csrf': False})
if form_navigation.validate_on_submit():
return redirect(url_for(form_navigation.select_navigation.data))
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
content_type = "main_page"
return render_template('ucreporter_settings_mainpage.html',
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
formNAV=form_navigation)
def recording_page(recording_id):
form_navigation = SelectNavigation(meta={'csrf': False})
sql_request_path = CmsCdrRecordingsTableClass.query.get(recording_id)
conference_id = str(sql_request_path.call_id)
record_path = 'record/' + str(sql_request_path.path).replace("_","/") + '.mp4'
print(record_path)
response = render_template(
'cisco_cms_recording_player.html',
recording_id = record_path,
formNAV = form_navigation,
row = True,
html_page_header = "Record play for " + conference_id,
)
return response
def cms_cospace_view():
page_offset = 0
page_limit = 10
CoSpace_list = []
rows_list = []
# Настройка логирования
CMS_RECEIVER_LOG_FILE_NAME = "../logs/CMS_COSPACE_VIEWER.log"
CMS_RECEIVER_LOG_FILE_SIZE = 2048000
CMS_RECEIVER_LOG_FILE_COUNT = 5
# Диспетчер логов
logger = logging.getLogger('CMS_COSPACE_VIEWER')
#
logger.setLevel(logging.DEBUG)
# Обработчик логов - запись в файлы с перезаписью
if not logger.handlers:
console_output = ": no any handlers in Logger - create new one"
print("CMS_COSPACE_VIEWER " + console_output)
rotate_file_handler = logging.handlers.RotatingFileHandler(CMS_RECEIVER_LOG_FILE_NAME,
maxBytes=CMS_RECEIVER_LOG_FILE_SIZE,
backupCount=CMS_RECEIVER_LOG_FILE_COUNT)
rotate_file_handler.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(asctime)s %(name)s - %(levelname)s: %(message)s')
rotate_file_handler.setFormatter(formatter)
logger.addHandler(rotate_file_handler)
operationStartTime = datetime.now()
html_page_title = 'CMS CoSpace Report'
html_template = 'cisco_cms_cospaceview.html'
form_navigation = SelectNavigation(csrf_enabled=False)
if form_navigation.validate_on_submit():
console_output = "Нет активного запроса"
#print(console_output)
logger.debug(console_output)
return redirect(url_for(form_navigation.select_navigation.data))
form_cmsselection = SelectCMSClusterForCospace(csrf_enabled=False)
if form_cmsselection.validate_on_submit():
cluster_config = cms_sql_request_dict(
"SELECT DISTINCT login,password,ip,api_port FROM cms_servers WHERE cluster='" + form_cmsselection.select_CMSCluster.data + "' LIMIT 1;")
console_output = "get config from SQL"
print(console_output)
logger.debug(console_output)
cms_ip=cluster_config[0]['ip']
cms_port=cluster_config[0]['api_port']
cms_login=cluster_config[0]['login']
cms_password=cluster_config[0]['password']
endOfCycle = False
while not endOfCycle: #запускаем цикл получения списка спейсов
#формируем сслыку на опрос
http_url = "https://" + cms_ip + ":" + cms_port + "/api/v1/coSpaces?limit=" + str(
page_limit) + "&offset=" + str(page_offset)
console_output = cms_ip + ": URL: " + http_url
print(console_output) # debug
logger.debug(console_output)
xml_dict = xmltodict.parse(cms_webrequest(http_url,cms_ip,cms_login,cms_password))
total_coSpaces = xml_dict["coSpaces"]["@total"]
console_output = cms_ip + ": Total number of CallLegs: " + total_coSpaces
#print(console_output) # debug
logger.debug(console_output)
# проверям что есть coSpace
if "coSpace" in xml_dict["coSpaces"]:
# Проверяем тип list или OrderedDict для выбора корректного способа добавления в общий список
if type(xml_dict["coSpaces"]["coSpace"]) is OrderedDict:
CoSpace_list.append(xml_dict["coSpaces"]["coSpace"])
console_output = cms_ip + ": Number of CoSpace from current request: 1"
#print(console_output) # debug
logger.debug(console_output)
elif type(xml_dict["coSpaces"]["coSpace"]) is list:
CoSpace_list.extend(xml_dict["coSpaces"]["coSpace"])
console_output = cms_ip + ": Number of CoSpace from current request: " + str(
len(xml_dict["coSpaces"]["coSpace"]))
#print(console_output) # debug
logger.debug(console_output)
console_output = cms_ip + ": Number of collected CoSpace: " + str(len(CoSpace_list))
#print(console_output) # debug
logger.debug(console_output)
#закрываем цикл
if int(total_coSpaces) > len(CoSpace_list):
page_offset = len(CoSpace_list)
endOfCycle = False
else:
endOfCycle = True
#pprint(CoSpace_list)
# перебираем все активные callLeg
for CoSpace in CoSpace_list:
# забираем CoSpace ID, собираем rows_list
if "@id" in CoSpace:
CoSpace_id = CoSpace["@id"]
rows_list.append(cms_cospace_detail(CoSpace_id,cms_login,cms_password,cms_ip,cms_port))
operationEndTime = datetime.now()
operationDuration = str( operationEndTime - operationStartTime)
console_output = "Done in " + operationDuration
logger.debug(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
rows_list=rows_list,
formNAV=form_navigation,
formCMS=form_cmsselection)
operationEndTime = datetime.now()
operationDuration = str( operationEndTime - operationStartTime)
console_output = "Нет активного запроса (" + operationDuration + ")"
logger.debug(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formCMS=form_cmsselection)
def render():
SEARCH_BY_DN = "DN"
SEARCH_BY_TRANSFER = "Transfer"
html_page_title = 'CUCM CallForward Report'
html_template = 'cisco_callforward.html'
# Temporary values
console_output = "Нет активного запроса"
form_navigation = SelectNavigation(csrf_enabled=False)
if form_navigation.validate_on_submit():
console_output = "Нет активного запроса"
print(console_output)
return redirect(url_for(form_navigation.select_navigation.data))
choise_data = sql_request_dict("SELECT cluster,description FROM cm_servers_list")
form_search = SelectForwardSearchType(csrf_enabled=False)
form_search.select_region.choices = [(choise["cluster"], choise["description"]) for choise in choise_data]
if form_search.validate_on_submit():
console_output = form_search.select_region.data + " " + form_search.select_field.data + " " + form_search.string_field.data
auth_data_list = sql_request_dict(
"SELECT cm_ip,cm_username,cm_password FROM cm_servers_list WHERE cluster='" + form_search.select_region.data + "'") # получаем лист словарей
cucm_ip_address = str(auth_data_list[0]['cm_ip'])
cucm_login = str(auth_data_list[0]['cm_username'])
cucm_password = str(auth_data_list[0]['cm_password'])
# CUCM URL's
cucm_url = "https://" + cucm_ip_address + ":8443/axl/"
console_output = cucm_url + "\n"
#print(console_output)
# V12 CUCM Headers
headers11query = {'Content-Type': 'text/xml', 'SOAPAction': 'CUCM:DB ver=11.5 executeSQLQuery'}
if form_search.select_field.data == SEARCH_BY_DN:
msg_begin = """
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>
select n.dnorpattern, cfd.cfadestination, cfd.cfavoicemailenabled
from numplan n inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid
where n.dnorpattern like '"""
msg_end = """%' order by n.dnorpattern
</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>
"""
msg = msg_begin + form_search.string_field.data + msg_end
elif form_search.select_field.data == SEARCH_BY_TRANSFER:
msg_begin = """
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>select n.dnorpattern, cfd.cfadestination, cfd.cfavoicemailenabled
from numplan n inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid
where cfd.cfadestination like '"""
msg_end = """%' order by n.dnorpattern
</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>
"""
msg = msg_begin + form_search.string_field.data + msg_end
console_output = msg + "\n"
#print(console_output)
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(cucm_url, data=msg.encode('utf-8'), headers=headers11query, verify=False,
auth=(cucm_login, cucm_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + cucm_ip_address
#print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
except:
console_output = "Что-то пошло не так при подключении пользователя " + cucm_login + " к серверу " + cucm_ip_address
print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + cucm_login + " не авторизован для подключения к серверу " + cucm_ip_address
print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(post.status_code) + ": " + post.reason
print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
# Convert output to Dict
console_output = "Данные получены из CUCM " + cucm_ip_address
print(console_output)
xml_dict = xmltodict.parse(post.text)
# Get Dict with phones
if type(xml_dict["soapenv:Envelope"]["soapenv:Body"]["ns:executeSQLQueryResponse"][
"return"]) is collections.OrderedDict:
if "row" in xml_dict["soapenv:Envelope"]["soapenv:Body"]["ns:executeSQLQueryResponse"]["return"]:
if type(xml_dict["soapenv:Envelope"]["soapenv:Body"]["ns:executeSQLQueryResponse"]["return"][
"row"]) is list:
rows_list = xml_dict["soapenv:Envelope"]["soapenv:Body"]["ns:executeSQLQueryResponse"]["return"][
"row"]
elif type(xml_dict["soapenv:Envelope"]["soapenv:Body"]["ns:executeSQLQueryResponse"]["return"][
"row"]) is collections.OrderedDict:
rows_list = [
xml_dict["soapenv:Envelope"]["soapenv:Body"]["ns:executeSQLQueryResponse"]["return"]["row"]]
else:
console_output = "Телефонов соответсвующих запросу не найдено"
print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
else:
console_output = "Телефонов соответсвующих запросу не найдено"
print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
console_output = "Найдено записей: " + str(len(rows_list))
#print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search,
rows_list=rows_list)
else:
if form_search.string_field.errors:
console_output = " ".join(form_search.string_field.errors)
print(console_output)
return render_template(html_template, html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
def cms_cospace_usage():
# Настройка логирования
CMS_RECEIVER_LOG_FILE_NAME = "../logs/CMS_COSPACE_USAGE.log"
CMS_RECEIVER_LOG_FILE_SIZE = 2048000
CMS_RECEIVER_LOG_FILE_COUNT = 5
# Диспетчер логов
logger = logging.getLogger('CMS_COSPACE_USAGE')
logger.setLevel(logging.DEBUG)
# Обработчик логов - запись в файлы с перезаписью
if not logger.handlers:
console_output = ": no any handlers in Logger - create new one"
print("CMS_COSPACE_USAGE " + console_output)
rotate_file_handler = logging.handlers.RotatingFileHandler(
CMS_RECEIVER_LOG_FILE_NAME,
maxBytes=CMS_RECEIVER_LOG_FILE_SIZE,
backupCount=CMS_RECEIVER_LOG_FILE_COUNT)
rotate_file_handler.setLevel(logging.DEBUG)
formatter = logging.Formatter(
'%(asctime)s %(name)s - %(levelname)s: %(message)s')
rotate_file_handler.setFormatter(formatter)
logger.addHandler(rotate_file_handler)
operation_start_time = datetime.now()
html_page_title = 'CMS CoSpace Usage Report'
html_template = 'cisco_cms_cospace_usage.html'
form_cmsselection = SelectCMSClusterForCospace(csrf_enabled=False)
form_navigation = SelectNavigation(csrf_enabled=False)
if form_navigation.validate_on_submit():
console_output = "Нет активного запроса"
logger.debug(console_output)
return redirect(url_for(form_navigation.select_navigation.data))
if form_cmsselection.validate_on_submit():
sql_request_result_string_mounth = """SELECT cms_cdr_calls.Name,COUNT(cms_cdr_calls.Name) AS count_mounth,
SUM(cms_cdr_calls.durationSeconds) AS duration_mounth FROM cms_cdr_calls
INNER JOIN cms_servers ON cms_cdr_calls.cms_ip=cms_servers.ip
WHERE cms_cdr_calls.StartTime >= DATE(NOW()) - INTERVAL 30 DAY AND cms_servers.cluster
LIKE '""" + form_cmsselection.select_CMSCluster.data + """' AND NAME NOT LIKE 'Сове%' GROUP BY NAME ORDER BY NAME"""
sql_request_result_string_last_week = """SELECT cms_cdr_calls.Name,COUNT(cms_cdr_calls.Name) AS count_last_week,
SUM(cms_cdr_calls.durationSeconds) AS duration_last_week FROM cms_cdr_calls
INNER JOIN cms_servers ON cms_cdr_calls.cms_ip=cms_servers.ip
WHERE cms_cdr_calls.StartTime >= DATE(NOW()) - INTERVAL 7 DAY AND cms_servers.cluster
LIKE '""" + form_cmsselection.select_CMSCluster.data + """' AND NAME NOT LIKE 'Сове%' AND NAME NOT LIKE 'none' GROUP BY NAME ORDER BY NAME"""
#Словарь с данными за месяц
rows_list_mounth = sql_request_dict(sql_request_result_string_mounth)
#перевести секунды в часы
for row in rows_list_mounth:
if row["duration_mounth"]:
row["duration_mounth"] = time_format(row["duration_mounth"])
# Словарь с данными за неделю
rows_list_last_week = sql_request_dict(
sql_request_result_string_last_week)
# перевести секунды в часы
for row in rows_list_last_week:
if row["duration_last_week"]:
row["duration_last_week"] = time_format(
row["duration_last_week"])
rows_list = rows_list_mounth
for row in rows_list:
for key in rows_list_last_week:
if row["Name"] == key["Name"]:
if key["count_last_week"]:
row["duration_last_week"] = key["duration_last_week"]
row["count_last_week"] = key["count_last_week"]
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
logger.debug(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
rows_list=rows_list,
formNAV=form_navigation,
formCMS=form_cmsselection)
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Нет активного запроса (" + operation_duration + ")"
logger.debug(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formCMS=form_cmsselection)
def cms_cospace_usage_by_cluster():
# Настройка логирования
CMS_RECEIVER_LOG_FILE_NAME = "../logs/CMS_COSPACE_CLUSTER_USAGE.log"
CMS_RECEIVER_LOG_FILE_SIZE = 2048000
CMS_RECEIVER_LOG_FILE_COUNT = 5
# Диспетчер логов
logger = logging.getLogger('CMS_COSPACE_CLUSTER_USAGE')
logger.setLevel(logging.DEBUG)
# Обработчик логов - запись в файлы с перезаписью
if not logger.handlers:
console_output = ": no any handlers in Logger - create new one"
print("CMS_COSPACE_CLUSTER_USAGE " + console_output)
rotate_file_handler = logging.handlers.RotatingFileHandler(
CMS_RECEIVER_LOG_FILE_NAME,
maxBytes=CMS_RECEIVER_LOG_FILE_SIZE,
backupCount=CMS_RECEIVER_LOG_FILE_COUNT)
rotate_file_handler.setLevel(logging.DEBUG)
formatter = logging.Formatter(
'%(asctime)s %(name)s - %(levelname)s: %(message)s')
rotate_file_handler.setFormatter(formatter)
logger.addHandler(rotate_file_handler)
operation_start_time = datetime.now()
html_page_title = 'CMS CoSpace Usage by cluster Report'
html_template = 'cisco_cms_cospace_by_cluster_usage.html'
form_cmsselection = SelectCMSClusterForReport(meta={'csrf': False})
form_navigation = SelectNavigation(meta={'csrf': False})
if form_navigation.validate_on_submit():
console_output = "Нет активного запроса"
logger.debug(console_output)
renderdata = {
"rendertype": "redirect",
"redirect_to": form_navigation.select_navigation.data
}
return renderdata
if form_cmsselection.validate_on_submit():
sql_request_result_string = """SELECT cms_cdr_calls.Name,COUNT(cms_cdr_calls.Name) AS count_servers,cms_cdr_calls.meeting_id AS id,
MAX(cms_cdr_calls.durationSeconds) AS duration,
SUM(cms_cdr_calls.callLegsMaxActive) AS callLegs FROM cms_cdr_calls
INNER JOIN cms_servers ON cms_cdr_calls.cms_ip=cms_servers.ip
WHERE cms_cdr_calls.StartTime >= DATE(NOW()) - INTERVAL """ + str(
form_cmsselection.integer_field.data
) + """ DAY AND cms_servers.cluster
LIKE '""" + form_cmsselection.select_CMSCluster.data + """' AND NAME NOT LIKE 'Сове%' AND cms_cdr_calls.meeting_id IS NOT NULL GROUP BY id ORDER BY NAME"""
rows_list_period = sql_request_dict(sql_request_result_string)
rows_list = {}
for row in rows_list_period:
if row["Name"]:
row_name = (row["Name"])
else:
row_name = ""
if row["duration"]:
row_duration = int(row["duration"])
else:
row_duration = 0
if row["count_servers"]:
row_count_servers = int(row["count_servers"])
else:
row_count_servers = 0
if row["callLegs"]:
row_calllegs = int(row["callLegs"])
else:
row_calllegs = 0
if row_name in rows_list:
rows_list[row_name]["number_of_confs"] += 1
rows_list[row_name]["avg_servers"] = add_new_value_to_average(
rows_list[row_name]["number_of_confs"],
rows_list[row_name]["avg_servers"], row_count_servers)
rows_list[row_name]["avg_duration"] = add_new_value_to_average(
rows_list[row_name]["number_of_confs"],
rows_list[row_name]["avg_duration"], row_duration)
if row_duration > rows_list[row["Name"]]["max_duration"]:
rows_list[row_name]["max_duration"] = row_duration
rows_list[row_name]["sum_duration"] += row_duration
rows_list[row_name]["avg_calllegs"] = add_new_value_to_average(
rows_list[row_name]["number_of_confs"],
rows_list[row_name]["avg_calllegs"], row_calllegs)
if row_calllegs > rows_list[row["Name"]]["max_calllegs"]:
rows_list[row_name]["max_calllegs"] = row_calllegs
rows_list[row_name]["sum_calllegs"] += row_calllegs
else:
rows_list[row_name] = row
rows_list[row_name]["number_of_confs"] = 1
rows_list[row_name]["avg_servers"] = row_count_servers
rows_list[row_name]["avg_duration"] = row_duration
rows_list[row_name]["max_duration"] = row_duration
rows_list[row_name]["sum_duration"] = row_duration
rows_list[row_name]["avg_calllegs"] = row_calllegs
rows_list[row_name]["max_calllegs"] = row_calllegs
rows_list[row_name]["sum_calllegs"] = row_calllegs
#перевести секунды в часы
for row in rows_list.values():
if row["avg_duration"]:
print(f'avg_duration before: {row["avg_duration"]}')
row["avg_duration"] = time_format_with_days(
round(row["avg_duration"]))
print(f'avg_duration after: {row["avg_duration"]}')
if row["max_duration"]:
print(f'max_duration before: {row["max_duration"]}')
row["max_duration"] = time_format_with_days(
row["max_duration"])
print(f'max_duration after: {row["max_duration"]}')
if row["sum_duration"]:
print(f'sum_duration before: {row["sum_duration"]}')
row["sum_duration"] = time_format_with_days(
row["sum_duration"])
print(f'sum_duration after: {row["sum_duration"]}')
if row["avg_servers"]:
row["avg_servers"] = round(row["avg_servers"], 1)
if row["avg_calllegs"]:
row["avg_calllegs"] = round(row["avg_calllegs"], 1)
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
logger.debug(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
rows_list=rows_list.values(),
formNAV=form_navigation,
formCMS=form_cmsselection)
else:
if form_cmsselection.integer_field.errors:
console_output = " ".join(form_cmsselection.integer_field.errors)
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
logger.debug(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formCMS=form_cmsselection)
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Нет активного запроса (" + operation_duration + ")"
logger.debug(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formCMS=form_cmsselection)
def ucreporter_settings_roomcontroll(system_id):
"""страница настроек Terminal
"""
operation_start_time = datetime.now()
html_page_title = 'UC Reporter administration'
html_page_header = 'RoomControl administration'
form_navigation = SelectNavigation(meta={'csrf': False})
form_roomcontroll = roomcontroll_information(meta={'csrf': False})
if form_navigation.validate_on_submit():
renderdata = {
"content_type": "redirect",
"redirect_to": form_navigation.select_navigation.data
}
return renderdata
if form_roomcontroll.validate_on_submit():
# проверяем наличие ID в базе
sql_request_result_string = "SELECT * FROM cm_roomsystems_table WHERE room_index=" + str(
form_roomcontroll.id_field.text) + ";"
save_data = sql_request_dict(sql_request_result_string)
# если ID отсутствует, создаем запись, если есть обновляем
if not save_data:
print("INSERT")
sql_execute(
"INSERT INTO cm_roomsystems_table SET cm_roomsystems_table=" +
str(form_cucm_server.id_field.text) + ",cluster='" +
form_cucm_server.Cluster_field.data + "',cm_username='******',cm_password='******',cm_ip='" +
form_cucm_server.ip_field.data + "';")
else:
print("UPDATE")
sql_execute("UPDATE cm_roomsystems_table SET cluster='" +
form_cucm_server.Cluster_field.data +
"',cm_username='******',cm_password='******',cm_ip='" +
form_cucm_server.ip_field.data + "' WHERE id='" +
str(form_cucm_server.id_field.text) + "'")
# переходим на список
print("REDIRECT")
renderdata = {
"content_type": "redirect",
"redirect_to": "platform_CUCMservers"
}
return renderdata
# отрисовка страницы изменения серверов.
if system_id:
if system_id == "AddNew":
# Новый пользователь, считаем номер нового ID
sql_request_result_string = "SELECT MAX(id) FROM cm_roomsystems_table;" # забираем максимальный
rows_list = sql_request_dict(sql_request_result_string)
# заполняем форму
index_data = int(rows_list[0]['MAX(id)']) + 1
form_cucm_server.id_field.text = index_data
form_cucm_server.Cluster_field.data = str('cluster')
form_cucm_server.username_field.data = str('cm_username')
form_cucm_server.ip_field.data = str('cm_ip')
form_cucm_server.password_field.data = str('cm_password')
else:
sql_request_result_string = "SELECT * FROM cm_roomsystems_table WHERE id=" + server_id + ";"
rows_list = sql_request_dict(sql_request_result_string)
# заполняем форму
form_cucm_server.id_field.text = str(rows_list[0]['id'])
form_cucm_server.Cluster_field.data = str(rows_list[0]['cluster'])
form_cucm_server.username_field.data = str(
rows_list[0]['cm_username'])
form_cucm_server.ip_field.data = str(rows_list[0]['cm_ip'])
form_cucm_server.password_field.data = str(
rows_list[0]['cm_password'])
if rows_list:
content_type = "cucm_server_edit"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
renderdata = {
"content_type": content_type,
"html_template": "ucreporter_settings_mainpage.html",
"html_page_title": html_page_title,
"html_page_header": html_page_header,
"console_output": console_output,
"form_cucm_server": form_cucm_server,
"rows_list": rows_list,
"form_navigation": form_navigation,
}
return renderdata
# отрисовка данных списка серверов в случае, если не пришел ID сервера.
else:
sql_request_result_string = "SELECT * FROM cm_roomsystems_table;"
rows_list = sql_request_dict(sql_request_result_string)
content_type = "cucm_server_list"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
renderdata = {
"content_type": content_type,
"html_template": "ucreporter_settings_mainpage.html",
"html_page_title": html_page_title,
"html_page_header": html_page_header,
"form_cucm_server": form_cucm_server,
"console_output": console_output,
"rows_list": rows_list,
"form_navigation": form_navigation,
}
return renderdata
def ucreporter_settings_users(user_id):
"""страница настроек пользователей
:param user_id: ID пользователя
:return:
"""
operation_start_time = datetime.now()
html_page_title = 'UC Reporter administration '
html_page_header = 'User administration'
form_navigation = SelectNavigation(meta={'csrf': False})
form_edit_user = UserInformation(meta={'csrf': False})
# нажака нопка перехода на другую страницу
if form_navigation.validate_on_submit():
return redirect(url_for(form_navigation.select_navigation.data))
# нажака нопка SAVE
if form_edit_user.validate_on_submit():
# запрашиваем ID в базе
try:
save_data = UCReporterUsersTableClass.query.get(
form_edit_user.id_field.text)
except:
save_data = False
# если запрос пустой, создаем запись.
if not save_data:
insert_data = UCReporterUsersTableClass(
id=form_edit_user.id_field.text,
username=form_edit_user.UserName_field.data,
password=form_edit_user.Password_field.data,
password_hash=form_edit_user.Password_field.data,
description=form_edit_user.Description_field.data)
try:
db.session.add(insert_data)
db.session.commit()
except:
console_output = "error insert User to SQL"
else:
#забираем пользователя по ID для обнновления
#обновляем данные
save_data.username = form_edit_user.UserName_field.data
save_data.description = form_edit_user.Description_field.data
save_data.password = form_edit_user.Password_field.data
save_data.password_hash = form_edit_user.Password_field.data
#вносим изменения в базу
try:
db.session.add(save_data)
db.session.commit()
console_output = "update done"
except:
console_output = "error update"
# переходим на список
return redirect(url_for('platform_users'))
if user_id:
if user_id == "AddNew":
# Новый пользователь, считаем номер нового ID
#sql_request_result_string = "SELECT MAX(id) FROM ucreporter_users;" # забираем максимальный
#rows_list = sql_request_dict(sql_request_result_string)
# index_data = int(rows_list[0]['MAX(id)']) + 1
# заполняем форму
rows_list = db.session.query(
db.func.max(UCReporterUsersTableClass.id)).scalar()
index_data = int(rows_list) + 1
form_edit_user.id_field.text = index_data
form_edit_user.UserName_field.data = str("username")
form_edit_user.Password_field.data = str("password")
form_edit_user.Description_field.data = str("description")
else:
# заполняем форму
rows_list = UCReporterUsersTableClass.query.get(user_id)
form_edit_user.id_field.text = rows_list.id
form_edit_user.UserName_field.data = rows_list.username
form_edit_user.Password_field.data = rows_list.password
form_edit_user.Description_field.data = rows_list.description
#Проверяем наличие данных для отрисовки страницы.
if rows_list:
content_type = "user_edit"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
return render_template('ucreporter_settings_mainpage.html',
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
form_edit_user=form_edit_user,
rows_list=rows_list,
formNAV=form_navigation)
else:
#забираем все записи из базы
rows_list = UCReporterUsersTableClass.query.all()
content_type = "user_list"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
return render_template('ucreporter_settings_mainpage.html',
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
form_edit_user=form_edit_user,
rows_list=rows_list,
formNAV=form_navigation)
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
content_type = "main_page"
return render_template('ucreporter_settings_mainpage.html',
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
formNAV=form_navigation)
def ucreporter_settings_CUCMservers(server_id):
"""страница настроек CUCM
:param server_id:
:return:
"""
operation_start_time = datetime.now()
html_page_title = 'UC Reporter administration'
html_page_header = 'CUCM Server administration'
form_navigation = SelectNavigation(meta={'csrf': False})
form_cucm_server = CUCMServerInformation(meta={'csrf': False})
if form_navigation.validate_on_submit():
return redirect(url_for(form_navigation.select_navigation.data))
if form_cucm_server.validate_on_submit():
# проверяем наличие ID в базе
sql_request_result_string = "SELECT * FROM cm_servers_list WHERE id=" + str(
form_cucm_server.id_field.text) + ";"
save_data = sql_request_dict(sql_request_result_string)
# если ID отсутствует, создаем запись, если есть обновляем
if not save_data:
print("INSERT")
sql_execute("INSERT INTO cm_servers_list SET id=" +
str(form_cucm_server.id_field.text) + ",cluster='" +
form_cucm_server.Cluster_field.data +
"',cm_username='******',cm_password='******',cm_ip='" +
form_cucm_server.ip_field.data + "';")
else:
print("UPDATE")
sql_execute("UPDATE cm_servers_list SET cluster='" +
form_cucm_server.Cluster_field.data +
"',cm_username='******',cm_password='******',cm_ip='" +
form_cucm_server.ip_field.data + "' WHERE id='" +
str(form_cucm_server.id_field.text) + "'")
# переходим на список
return redirect(url_for('platform_CUCMservers'))
# отрисовка страницы изменения серверов.
if server_id:
if server_id == "AddNew":
# Новый пользователь, считаем номер нового ID
sql_request_result_string = "SELECT MAX(id) FROM cm_servers_list;" # забираем максимальный
rows_list = sql_request_dict(sql_request_result_string)
# заполняем форму
index_data = int(rows_list[0]['MAX(id)']) + 1
form_cucm_server.id_field.text = index_data
form_cucm_server.Cluster_field.data = str('cluster')
form_cucm_server.username_field.data = str('cm_username')
form_cucm_server.ip_field.data = str('cm_ip')
form_cucm_server.password_field.data = str('cm_password')
else:
sql_request_result_string = "SELECT * FROM cm_servers_list WHERE id=" + server_id + ";"
rows_list = sql_request_dict(sql_request_result_string)
# заполняем форму
form_cucm_server.id_field.text = str(rows_list[0]['id'])
form_cucm_server.Cluster_field.data = str(rows_list[0]['cluster'])
form_cucm_server.username_field.data = str(
rows_list[0]['cm_username'])
form_cucm_server.ip_field.data = str(rows_list[0]['cm_ip'])
form_cucm_server.password_field.data = str(
rows_list[0]['cm_password'])
if rows_list:
content_type = "cucm_server_edit"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
return render_template("ucreporter_settings_mainpage.html",
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
rows_list=rows_list,
form_CUCM_server=form_cucm_server,
formNAV=form_navigation)
# отрисовка данных списка серверов в случае, если не пришел ID сервера.
else:
rows_list = CmServerListTableClass.query.all()
content_type = "cucm_server_list"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
return render_template("ucreporter_settings_mainpage.html",
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
rows_list=rows_list,
form_CUCM_server=form_cucm_server,
formNAV=form_navigation)
def ucreporter_settings_CMSservers(server_id):
"""Отображение страницы настроек CMS
:param server_id:
:return:
"""
operation_start_time = datetime.now()
html_page_title = 'UC Reporter administration'
html_page_header = 'CMS administration'
form_navigation = SelectNavigation(meta={'csrf': False})
form_cms_server = CMSServerInformation(meta={'csrf': False})
if form_navigation.validate_on_submit():
return redirect(url_for(form_navigation.select_navigation.data))
if form_cms_server.validate_on_submit():
# проверяем наличие ID в базе
sql_request_result_string = "SELECT * FROM cms_servers WHERE id=" + str(
form_cms_server.id_field.text) + ";"
save_data = sql_request_dict(sql_request_result_string)
# если ID отсутствует, создаем запись, если есть обновляем
if not save_data:
insert_data = CmsServerTableClass(
login=form_cms_server.username_field.data,
password=form_cms_server.password_field.data,
ip=form_cms_server.ip_field.data,
api_port=form_cms_server.API_Port_field.data,
cluster=form_cms_server.cluster_field.data)
print(insert_data)
db.session.add(insert_data)
db.session.commit
print("Insert done")
else:
sql_execute("UPDATE cms_servers SET cluster='" +
form_cms_server.cluster_field.data + "',api_port='" +
form_cms_server.API_Port_field.data + "',login='******',password='******',ip='" +
form_cms_server.ip_field.data + "' WHERE id=" +
str(form_cms_server.id_field.text) + ";")
# переходим на список
return redirect(url_for('platform_CMSservers'))
if server_id:
if server_id == "AddNew":
# Новый пользователь, считаем номер нового ID
sql_request_result_string = "SELECT MAX(id) FROM cms_servers;" # забираем максимальный
rows_list = sql_request_dict(sql_request_result_string)
# заполняем форму
index_data = int(rows_list[0]['MAX(id)']) + 1
form_cms_server.id_field.text = index_data
form_cms_server.API_Port_field.data = str('api_port')
form_cms_server.ip_field.data = str('ip')
form_cms_server.cluster_field.data = str('cluster')
form_cms_server.password_field.data = str('password')
form_cms_server.username_field.data = str('login')
else:
#sql_request_result_string = "SELECT * FROM cms_servers WHERE id=" + server_id + ";"
#rows_list = sql_request_dict(sql_request_result_string)
rows_list = CmsServerTableClass.query.get(server_id)
# заполняем форму
form_cms_server.id_field.text = rows_list.id
form_cms_server.API_Port_field.data = rows_list.api_port
form_cms_server.ip_field.data = rows_list.ip
form_cms_server.cluster_field.data = rows_list.cluster
form_cms_server.password_field.data = rows_list.password
form_cms_server.username_field.data = rows_list.login
if rows_list:
content_type = "cms_server_edit"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
return render_template('ucreporter_settings_mainpage.html',
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
form_CMS_server=form_cms_server,
rows_list=rows_list,
formNAV=form_navigation)
# отрисовка данных списка серверов в случае, если не пришел ID сервера.
else:
rows_list = CmsServerTableClass.query.all()
content_type = "cms_server_list"
operation_end_time = datetime.now()
operation_duration = str(operation_end_time - operation_start_time)
console_output = "Done in " + operation_duration
return render_template('ucreporter_settings_mainpage.html',
html_page_title=html_page_title,
html_page_header=html_page_header,
content_type=content_type,
console_output=console_output,
form_CMS_server=form_cms_server,
rows_list=rows_list,
formNAV=form_navigation)
def aurus_consistency_check():
operationStartTime = datetime.now()
console_output = "Consistency check started"
print(console_output)
html_page_title = 'CUCM with Aurus consistency report'
form_navigation = SelectNavigation(meta={'csrf': False})
if form_navigation.validate_on_submit():
renderdata = {
"rendertype": "redirect",
"redirect_to": form_navigation.select_navigation.data
}
return renderdata
console_output = "Creating cluster selection form"
print(console_output)
choice_data = sql_request_dict(
"SELECT cluster,description FROM cm_servers_list")
form_cluster_selection = SelectCUCMCluster(meta={'csrf': False})
form_cluster_selection.select_cluster.choices = [
(choice["cluster"], choice["description"]) for choice in choice_data
]
if form_cluster_selection.validate_on_submit():
auth_data_list = sql_request_dict(
"SELECT cm_ip,cm_username,cm_password,phoneup_ip,phoneup_username,phoneup_password,phoneup_app_user FROM cm_servers_list WHERE cluster='"
+ form_cluster_selection.select_cluster.data +
"'") # получаем лист словарей
cucm_ip_address = str(auth_data_list[0]['cm_ip'])
cucm_login = str(auth_data_list[0]['cm_username'])
cucm_password = str(auth_data_list[0]['cm_password'])
phoneup_ip_address = str(auth_data_list[0]['phoneup_ip'])
phoneup_login = str(auth_data_list[0]['phoneup_username'])
phoneup_password = str(auth_data_list[0]['phoneup_password'])
phoneup_app_user = str(auth_data_list[0]['phoneup_app_user'])
# CUCM URL's
cucm_url = "https://" + cucm_ip_address + ":8443/axl/"
# V12 CUCM Headers
headers11query = {
'Content-Type': 'text/xml',
'SOAPAction': 'CUCM:DB ver=11.5 executeSQLQuery'
}
# ----------------------------------------------------------
# Get information about lines with recorded option from CUCM
# ----------------------------------------------------------
sql_query = """select rec.name,tm.name as devicetype,d.name as devicename,n.dnorpattern,n.description,trec.name AS recflag from recordingdynamic AS rd
INNER JOIN devicenumplanmap AS mdn ON mdn.pkid==rd.fkdevicenumplanmap
INNER JOIN numplan AS n ON n.pkid==mdn.fknumplan
INNER JOIN typerecordingflag AS trec ON trec.enum==rd.tkrecordingflag
INNER JOIN device AS d ON d.pkid=mdn.fkdevice
INNER JOIN typemodel as tm on d.tkmodel = tm.enum
INNER JOIN recordingprofile as rec on mdn.fkrecordingprofile = rec.pkid"""
renderdata = get_dict_from_cucm(cucm_url, headers11query, cucm_login,
cucm_password, sql_query)
if renderdata["rendertype"] == "success":
devices_with_enabled_record_list = renderdata["rows_list"]
else:
devices_with_enabled_record_list = {}
operationEndTime = datetime.now()
operationDuration = str(operationEndTime - operationStartTime)
console_output = " (Промежуточный результат " + operationDuration + ")"
print(console_output)
# -----------------------------------------------------------
# Get information about devices in application user "phoneup"
# -----------------------------------------------------------
sql_query = """select n.dnorpattern,mdn.display,tm.name as devicetype,device.name as devicename from applicationuserdevicemap
INNER JOIN applicationuser ON applicationuser.pkid = applicationuserdevicemap.fkapplicationuser
INNER JOIN devicenumplanmap AS mdn ON mdn.fkdevice=applicationuserdevicemap.fkdevice
INNER JOIN numplan AS n ON n.pkid==mdn.fknumplan
INNER JOIN device ON device.pkid=applicationuserdevicemap.fkdevice
INNER JOIN typemodel as tm on device.tkmodel = tm.enum
where applicationuser.name = '""" + phoneup_app_user + "' AND tm.name NOT LIKE '%CTI%'"
renderdata = get_dict_from_cucm(cucm_url, headers11query, cucm_login,
cucm_password, sql_query)
if renderdata["rendertype"] == "success":
devices_in_application_user_list = renderdata["rows_list"]
else:
devices_in_application_user_list = {}
operationEndTime = datetime.now()
operationDuration = str(operationEndTime - operationStartTime)
console_output = " (Промежуточный результат " + operationDuration + ")"
print(console_output)
# -------------------------------------------------------------
# Get information about devices enabled for record from PhoneUP
# -------------------------------------------------------------
# phonUP URL's
phoneup_url = "http://" + phoneup_ip_address + "/coreapi/api/Core/GetActivatedDevices?moduleName=record"
console_output = phoneup_url + "\n"
print(console_output)
renderdata = get_dict_from_aurus(phoneup_url, phoneup_login,
phoneup_password)
if renderdata["rendertype"] == "success":
phoneup_activated_devices_list = renderdata["rows_list"]
else:
phoneup_activated_devices_list = {}
operationEndTime = datetime.now()
operationDuration = str(operationEndTime - operationStartTime)
console_output = " (Промежуточный результат " + operationDuration + ")"
print(console_output)
# -------------------------------------------------------------
# Get information about lines enabled for record from PhoneUP
# -------------------------------------------------------------
# phonUP URL's
phoneup_url = "http://" + phoneup_ip_address + "/coreapi/api/Record/GetRecordedLines"
renderdata = get_dict_from_aurus(phoneup_url, phoneup_login,
phoneup_password)
if renderdata["rendertype"] == "success":
phoneup_activated_lines_list = renderdata["rows_list"]
else:
phoneup_activated_lines_list = {}
operationEndTime = datetime.now()
operationDuration = str(operationEndTime - operationStartTime)
console_output = " (Промежуточный результат " + operationDuration + ")"
print(console_output)
result_dict = {}
console_output = "Начинаем формировать итоговый словарь"
print(console_output)
# Add enabled devices to result dict
for enabled_device in devices_with_enabled_record_list:
result_dict[enabled_device["devicename"]] = {
"devicetype": enabled_device["devicetype"],
"devicename": enabled_device["devicename"],
"username": enabled_device["description"],
"dnorpattern": enabled_device["dnorpattern"],
"cucmline_dnorpattern": enabled_device["dnorpattern"]
}
console_output = "Информация о линиях с включенной записью в CUCM добавлена в итоговый словарь"
print(console_output)
# Add devices from application user to result dict
for device_in_app in devices_in_application_user_list:
if device_in_app["devicename"] in result_dict:
result_dict[device_in_app["devicename"]][
"app_devicename"] = device_in_app["devicename"]
result_dict[device_in_app["devicename"]][
"app_username"] = device_in_app["display"]
result_dict[device_in_app["devicename"]][
"app_dnorpattern"] = device_in_app["dnorpattern"]
result_dict[device_in_app["devicename"]][
"app_devicetype"] = device_in_app["devicetype"]
else:
result_dict[device_in_app["devicename"]] = {
"devicetype": device_in_app["devicetype"],
"devicename": device_in_app["devicename"],
"username": device_in_app["display"],
"dnorpattern": device_in_app["dnorpattern"],
"app_devicename": device_in_app["devicename"],
"app_username": device_in_app["display"],
"app_dnorpattern": device_in_app["dnorpattern"],
"app_devicetype": device_in_app["devicetype"]
}
console_output = "Информация об устройствах из Application User в CUCM добавлена в итоговый словарь"
print(console_output)
# Add activated devices from PhoneUP to result dict
for phoneup_activated_device in phoneup_activated_devices_list:
if phoneup_activated_device["Name"] in result_dict:
result_dict[phoneup_activated_device["Name"]][
"phoneup_devicename"] = phoneup_activated_device["Name"]
result_dict[phoneup_activated_device["Name"]][
"phoneup_devicetype"] = phoneup_activated_device["Type"]
else:
if len(phoneup_activated_device["PhoneLines"]) > 0:
phoneup_activated_device_line = phoneup_activated_device[
"PhoneLines"][0]
else:
phoneup_activated_device_line = ""
result_dict[phoneup_activated_device["Name"]] = {
"devicetype": phoneup_activated_device["Type"],
"devicename": phoneup_activated_device["Name"],
"dnorpattern": phoneup_activated_device_line,
"phoneup_devicename": phoneup_activated_device["Name"],
"phoneup_devicetype": phoneup_activated_device["Type"]
}
console_output = "Информация об активированных устройствах в PhoneUP добавлена в итоговый словарь"
print(console_output)
# Add recorded lines from PhoneUP to result dict
# Перебираем в цикле все полученниые линии включенные на запись в Фонапе
for phoneup_activated_lines in phoneup_activated_lines_list:
# Перебираем в цикле все существующие записи итогового словоря чтобы найти нет ли такой линии
line_was_found = False
for record in result_dict.values():
# Проверяем есть ли записываемая линия в записи итогового словаря
if record["dnorpattern"] == phoneup_activated_lines[
"PhoneLine"]:
# Записываемая линия уже есть в записи итогового словаря, тогда
# Дописываем значения в существующую запись
record["line_dnorpattern"] = phoneup_activated_lines[
"PhoneLine"]
# Проверяем есть ли информация об устройстве записываемой линии
if len(phoneup_activated_lines["DeviceNames"]) > 0:
phoneup_activated_line_device = phoneup_activated_lines[
"DeviceNames"][0]
else:
phoneup_activated_line_device = ""
record["line_devicename"] = phoneup_activated_line_device
# Проверяем есть ли информация о контактах записываемой линии
if len(phoneup_activated_lines["Contacts"]) > 0:
phoneup_activated_line_contact = phoneup_activated_lines[
"Contacts"][0]
else:
phoneup_activated_line_contact = ""
record["line_username"] = phoneup_activated_line_contact
line_was_found = True
break
if not line_was_found:
# Записываемой линии нет в итоговом словаре, тогда
# Создаем запись
# Проверяем есть ли информация об устройстве записываемой линии
if len(phoneup_activated_lines["DeviceNames"]) > 0:
if phoneup_activated_lines["DeviceNames"][0] != "Unknown":
phoneup_activated_line_device = phoneup_activated_lines[
"DeviceNames"][0]
else:
phoneup_activated_line_device = ""
else:
phoneup_activated_line_device = ""
# Проверяем есть ли информация о контактах записываемой линии
if len(phoneup_activated_lines["Contacts"]) > 0:
if phoneup_activated_lines["Contacts"][0] != "Unknown":
phoneup_activated_line_contact = phoneup_activated_lines[
"Contacts"][0]
else:
phoneup_activated_line_contact = ""
else:
phoneup_activated_line_contact = ""
result_dict[phoneup_activated_lines["PhoneLine"]] = {
"devicename": phoneup_activated_line_device,
"username": phoneup_activated_line_contact,
"dnorpattern": phoneup_activated_lines["PhoneLine"],
"line_devicename": phoneup_activated_line_device,
"line_dnorpattern": phoneup_activated_lines["PhoneLine"],
"line_username": phoneup_activated_line_contact
}
console_output = "Информация о включенных на запись линиях в PhoneUP добавлена в итоговый словарь"
print(console_output)
# console_output = "result_dict: "
# print(console_output)
# pprint(result_dict)
operationEndTime = datetime.now()
operationDuration = str(operationEndTime - operationStartTime)
console_output = "Найдено записей: " + str(
len(result_dict)) + " (Done in " + operationDuration + ")"
renderdata = {
"rendertype": "success",
"html_template": "ucreporter_aurus.html",
"html_page_title": html_page_title,
"console_output": console_output,
"form_navigation": form_navigation,
"form_cluster_selection": form_cluster_selection,
"rows_list": result_dict
}
return renderdata
console_output = "Нет активного запроса"
renderdata = {
"rendertype": "null",
"html_template": "ucreporter_aurus.html",
"html_page_title": html_page_title,
"console_output": console_output,
"form_navigation": form_navigation,
"form_cluster_selection": form_cluster_selection
}
return renderdata
def phone_cgi():
# Temporary values
html_template = "cisco_phone_metods.html"
html_page_title = 'Phone CSI'
console_output = "Нет активного запроса"
form_navigation = SelectNavigation(meta={'csrf': False})
if form_navigation.validate_on_submit():
#redirect to other page
console_output = "Нет активного запроса"
print(console_output)
return redirect(url_for(form_navigation.select_navigation.data))
choise_data = sql_request_dict(
"SELECT cluster,description FROM cm_servers_list")
form_search = SelectSearchType(csrf_enabled=False)
form_search.select_region.choices = [
(choise["cluster"], choise["description"]) for choise in choise_data
]
if form_search.validate_on_submit():
console_output = form_search.select_region.data + " " + form_search.string_field.data
auth_data_list = sql_request_dict(
"SELECT cm_ip,cm_username,cm_password FROM cm_servers_list WHERE cluster='"
+ form_search.select_region.data + "'") # получаем лист словарей
cucm_ip_address = str(auth_data_list[0]['cm_ip'])
cucm_login = str(auth_data_list[0]['cm_username'])
cucm_password = str(auth_data_list[0]['cm_password'])
print(cucm_password)
# CUCM URL's
cucm_url = "https://" + cucm_ip_address + ":8443/axl/"
console_output = cucm_url + "\n"
print(console_output)
# V12 CUCM Headers
headers11query = {
'Content-Type': 'text/xml',
'SOAPAction': 'CUCM:DB ver=11.5 executeSQLQuery'
}
msg_begin = """
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>select d.name,tm.name as devtype,d.description as description,n.dnorpattern as DN,rp.name as partition,c.name as css,display as display_line from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join numplan as n on dnpm.fknumplan = n.pkid inner join routepartition as rp on n.fkroutepartition=rp.pkid and d.tkclass = 1 inner join typemodel as tm on d.tkmodel = tm.enum inner join callingsearchspace as c on n.fkcallingsearchspace_sharedlineappear = c.pkid where n.dnorpattern like '"""
msg_end = """%'
</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>
"""
msg = msg_begin + form_search.string_field.data + msg_end
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(cucm_url,
data=msg.encode('utf-8'),
headers=headers11query,
verify=False,
auth=(cucm_login, cucm_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + cucm_ip_address
print(console_output)
return render_template(renderdata[html_template],
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
except:
console_output = "Что-то пошло не так при подключении пользователя " + cucm_login + " к серверу " + cucm_ip_address
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + cucm_login + " не авторизован для подключения к серверу " + cucm_ip_address
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
# Convert output to Dict
console_output = "Данные получены из CUCM " + cucm_ip_address
print(console_output)
xml_dict = xmltodict.parse(post.text)
# Get Dict with phones
if type(xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]
["return"]) is collections.OrderedDict:
if "row" in xml_dict["soapenv:Envelope"]["soapenv:Body"][
"ns:executeSQLQueryResponse"]["return"]:
if type(
xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]["return"]["row"]) is list:
rows_list = xml_dict["soapenv:Envelope"]["soapenv:Body"][
"ns:executeSQLQueryResponse"]["return"]["row"]
elif type(xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]["return"]
["row"]) is collections.OrderedDict:
rows_list = [
xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]["return"]["row"]
]
else:
console_output = "Телефонов соответсвующих запросу не найдено"
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
else:
console_output = "Телефонов соответсвующих запросу не найдено"
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
console_output = "Найдено записей: " + str(len(rows_list))
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
rows_list=rows_list,
formNAV=form_navigation,
formSRCH=form_search)
else:
if form_search.string_field.errors:
console_output = " ".join(form_search.string_field.errors)
print(console_output)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
formSRCH=form_search)
def huntreport():
auth_data_list = sql_request_dict(
"SELECT cm_ip,cm_username,cm_password FROM cm_servers_list WHERE cluster='Infocell'"
) # получаем лист словарей
cucm_ip_address = str(auth_data_list[0]['cm_ip'])
cucm_login = str(auth_data_list[0]['cm_username'])
cucm_password = str(auth_data_list[0]['cm_password'])
html_page_title = 'CUCM Hunt Report'
html_template = 'cisco_huntgroup.html'
# CUCM URL's
cucm_url = "https://" + cucm_ip_address + ":8443/axl/"
# V12 CUCM Headers
headers11query = {
'Content-Type': 'text/xml',
'SOAPAction': 'CUCM:DB ver=12.5 executeSQLQuery'
}
msg = """
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/12.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>select lg.name as LineGroup,n.dnorpattern, display, tm.name as devtype, dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner
join device as d on dmap.fkdevice=d.pkid inner join typemodel as tm on d.tkmodel = tm.enum inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid order by lg.name
</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>
"""
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(cucm_url,
data=msg,
headers=headers11query,
verify=False,
auth=(cucm_login, cucm_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + cucm_ip_address
print(console_output)
except:
console_output = "Что-то пошло не так при подключении пользователя " + cucm_login + " к серверу " + cucm_ip_address
print(console_output)
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + cucm_login + " не авторизован для подключения к серверу " + cucm_ip_address
print(console_output)
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
# Convert output to Dict
console_output = "Данные получены из CUCM " + cucm_ip_address
print(console_output)
xml_dict = xmltodict.parse(post.text)
# Get Dict with users
rows_list = xml_dict["soapenv:Envelope"]["soapenv:Body"][
"ns:executeSQLQueryResponse"]["return"]["row"]
# Temporary values
console_output = "Hunt group Start page"
# rows_list = [{"linegroup":"linegroup"},{"dnorpattern":"dnorpattern"},{"display":"display"},{"devtype":"devtype"},{"hlog":"hlog"}]
form_navigation = SelectNavigation(meta={'csrf': False})
if form_navigation.validate_on_submit():
console_output = "Нет активного запроса"
print(console_output)
return redirect(url_for(form_navigation.select_navigation.data))
form_hunt_group = SelectHuntGroup(meta={'csrf': False})
msg = """
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/12.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>select name as LineGroup from linegroup order by name</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>
"""
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(cucm_url,
data=msg,
headers=headers11query,
verify=False,
auth=(cucm_login, cucm_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + cucm_ip_address
print(console_output)
except:
console_output = "Что-то пошло не так при подключении пользователя " + cucm_login + " к серверу " + cucm_ip_address
print(console_output)
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + cucm_login + " не авторизован для подключения к серверу " + cucm_ip_address
print(console_output)
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
# Convert output to Dict
console_output = "Данные получены из CUCM " + cucm_ip_address
print(console_output)
xml_dict = xmltodict.parse(post.text)
# Get Dict with users
hunt_group_list = xml_dict["soapenv:Envelope"]["soapenv:Body"][
"ns:executeSQLQueryResponse"]["return"]["row"]
form_hunt_group.select_hunt_group.choices = [
(row["linegroup"], row["linegroup"]) for row in hunt_group_list
]
if form_hunt_group.validate_on_submit():
msg = """
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/12.5">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>select lg.name as LineGroup,n.dnorpattern, display, tm.name as devtype, dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner
join device as d on dmap.fkdevice=d.pkid inner join typemodel as tm on d.tkmodel = tm.enum inner join devicehlogdynamic
as dhd on dhd.fkdevice=d.pkid where lg.name = '""" + form_hunt_group.select_hunt_group.data + """' order by lg.name
</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>
"""
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(cucm_url,
data=msg,
headers=headers11query,
verify=False,
auth=(cucm_login, cucm_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + cucm_ip_address
print(console_output)
except:
console_output = "Что-то пошло не так при подключении пользователя " + cucm_login + " к серверу " + cucm_ip_address
print(console_output)
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + cucm_login + " не авторизован для подключения к серверу " + cucm_ip_address
print(console_output)
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
# Convert output to Dict
console_output = "Данные получены из CUCM " + cucm_ip_address
print(console_output)
xml_dict = xmltodict.parse(post.text)
if type(xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]
["return"]) is collections.OrderedDict:
if "row" in xml_dict["soapenv:Envelope"]["soapenv:Body"][
"ns:executeSQLQueryResponse"]["return"]:
if type(
xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]["return"]["row"]) is list:
rows_list = xml_dict["soapenv:Envelope"]["soapenv:Body"][
"ns:executeSQLQueryResponse"]["return"]["row"]
elif type(xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]["return"]
["row"]) is collections.OrderedDict:
rows_list = [
xml_dict["soapenv:Envelope"]["soapenv:Body"]
["ns:executeSQLQueryResponse"]["return"]["row"]
]
else:
console_output = "Телефонов в Hunt Group " + form_hunt_group.select_hunt_group.data + " не найдено"
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
form_hunt_group=form_hunt_group)
else:
console_output = "Телефонов в Hunt Group " + form_hunt_group.select_hunt_group.data + " не найдено"
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
form_hunt_group=form_hunt_group)
console_output = "Найдено записей: " + str(len(rows_list))
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
form_hunt_group=form_hunt_group,
rows_list=rows_list)
return render_template(html_template,
html_page_title=html_page_title,
console_output=console_output,
formNAV=form_navigation,
form_hunt_group=form_hunt_group,
rows_list=rows_list)