def cdr_receiver():
# Настройка логирования
logger = logger_init('CMS_RECEIVER', logging.DEBUG)
try:
cdr = xmltodict.parse(request.data) #get OrderedDict
cdr_dict = json.loads(json.dumps(cdr)) #trasfrorm OrderedDict to Dict
if "HTTP_X_FORWARDED_FOR" in request.environ:
cms_ip = str(
request.environ['HTTP_X_FORWARDED_FOR']) # забираем IP
elif "REMOTE_ADDR" in request.environ:
cms_ip = str(request.environ['REMOTE_ADDR']) # забираем IP
else:
cms_ip = "UNKNOWN"
if type(cdr_dict['records']['record']) is list:
console_output = cms_ip + ": We get record list"
logger.debug(console_output)
record_list = cdr_dict['records']['record']
else:
console_output = cms_ip + ": We get not record list"
logger.debug(console_output)
record_list = [cdr_dict['records']['record']]
console_output = cms_ip + ": Number of records in list: " + str(
len(record_list))
logger.debug(console_output)
for record_item in record_list:
console_output = "CMS_RECEIVER " + cms_ip + ": record_item"
logger.debug(console_output)
logger.debug("\n" + pformat(record_item))
if record_item[
'@type'] == 'callLegStart': #проверяем что, это новый коллег
console_output = cms_ip + ": We get callLegStart"
logger.debug(console_output)
console_output = cms_ip + ": Start to process callLegStart"
logger.debug(console_output)
if "@id" in record_item['callLeg']:
callleg_id = str(
record_item['callLeg']['@id']) # забираем callleg ID
console_output = cms_ip + ": We get callLegID from callLegStart"
logger.debug(console_output)
# забираем sipcall_id
if "sipCallId" in record_item['callLeg']:
sipcall_id = str(record_item['callLeg']['sipCallId'])
console_output = cms_ip + ": We get sipCallID from callLegStart"
logger.debug(console_output)
else:
sipcall_id = "none"
# забираем call_id
if "call" in record_item['callLeg']:
call_id = str(record_item['callLeg']['call'])
console_output = cms_ip + ": We get callID from callLegStart"
logger.debug(console_output)
else:
call_id = "none"
# забираем remoteAddress
if "remoteAddress" in record_item['callLeg']:
remoteAddress = str(
record_item['callLeg']['remoteAddress'])
console_output = cms_ip + ": We get remoteAddress from callLegStart"
logger.debug(console_output)
else:
remoteAddress = "none"
# забираем remoteParty
if "remoteParty" in record_item['callLeg']:
remoteParty = str(
record_item['callLeg']['remoteParty'])
console_output = cms_ip + ": We get remoteParty from callLegStart"
logger.debug(console_output)
else:
remoteParty = "none"
# забираем localAddress
if "localAddress" in record_item['callLeg']:
localAddress = str(
record_item['callLeg']['localAddress'])
console_output = cms_ip + ": We get localAddress from callLegStart"
logger.debug(console_output)
else:
localAddress = "none"
# забираем displayName
if "displayName" in record_item['callLeg']:
displayName = str(
record_item['callLeg']['displayName'])
console_output = cms_ip + ": We get displayName from callLegStart"
logger.debug(console_output)
else:
displayName = "none"
# забираем время лега
if "@time" in record_item:
callLegStartTime = str(record_item['@time'])
callLegStartTimeMSK = str(
datetime.datetime.strptime(callLegStartTime,
"%Y-%m-%dT%H:%M:%SZ") +
datetime.timedelta(hours=3))
console_output = cms_ip + ": We get callLegTime from callLegStart"
logger.debug(console_output)
else:
callLegStartTime = "none"
callLegStartTimeMSK = "none"
timenow = str(datetime.datetime.now())
# забираем GuestConnection
if "guestConnection" in record_item['callLeg']:
guestConnection = str(
record_item['callLeg']['guestConnection'])
console_output = cms_ip + ": We get guestConnection from callLegStart"
logger.debug(console_output)
else:
guestConnection = "none"
# забираем recording
if "recording" in record_item['callLeg']:
recording = str(record_item['callLeg']['recording'])
console_output = cms_ip + ": We get recording from callLegStart"
logger.debug(console_output)
else:
recording = "none"
# забираем CallLeg type
if "type" in record_item['callLeg']:
callLeg_type = str(record_item['callLeg']['type'])
console_output = cms_ip + ": We get type from callLegStart"
logger.debug(console_output)
else:
callLeg_type = "none"
# забираем CallLeg subtype
if "subType" in record_item['callLeg']:
callLeg_subtype = str(
record_item['callLeg']['subType'])
console_output = cms_ip + ": We get subType from callLegStart"
logger.debug(console_output)
else:
callLeg_subtype = "none"
### добавляем идентификаторы в базу
cms_sql_request("INSERT INTO cms_cdr_records SET date='" +
timenow + "',startTime='" +
callLegStartTimeMSK + "',cms_ip='" +
cms_ip + "',callleg_id='" + callleg_id +
"',sipcall_id='" + sipcall_id +
"',call_id='" + call_id +
"',displayName='" + displayName +
"',localAddress='" + localAddress +
"',remoteParty='" + remoteParty +
"',guestConnection='" + guestConnection +
"',recording='" + recording +
"',callLeg_type='" + callLeg_type +
"',callLeg_subtype='" + callLeg_subtype +
"',remoteAddress='" + remoteAddress + "';")
console_output = cms_ip + ": SIP ID: " + sipcall_id + " and " + callleg_id + " inserted to database"
logger.debug(console_output)
else:
console_output = cms_ip + ": CallLeg Id not found in callLegStart - nothing was inserted in DB"
logger.debug(console_output)
if record_item['@type'] == 'callLegUpdate':
console_output = cms_ip + ": we get callLegUpdate"
logger.debug(console_output)
callleg_id = str(
record_item['callLeg']
['@id']) # забираем callleg ID для обновления базы данных
if "call" in record_item['callLeg']:
call_id = str(
record_item['callLeg']['call']) # забираем reason
cm_sqlupdate(call_id, 'cms_cdr_records', 'call_id',
'callleg_id',
callleg_id) # дополняем информацию о вызове
#cms_sql_request(
# "UPDATE cms_cdr_records,cms_cdr_calls SET coSpace_name=cms_cdr_calls.name WHERE cms_cdr_calls.id='" + call_id + "';") # берем Имя спэйса из другой таблицы.
if record_item['@type'] == 'callLegEnd':
console_output = cms_ip + ": we get callLegEnd"
logger.debug(console_output)
# забираем callleg ID для обновления базы данных
if "@id" in record_item['callLeg']:
callleg_id = str(record_item['callLeg']['@id'])
console_output = cms_ip + ": We get callLegID from callLegEnd"
logger.debug(console_output)
# забираем durationSeconds
if "durationSeconds" in record_item['callLeg']:
durationSeconds = str(
record_item['callLeg']
['durationSeconds']) # забираем durationSeconds
console_output = cms_ip + ": We get durationSeconds from callLegEnd"
logger.debug(console_output)
else:
durationSeconds = "none"
# забираем reason
if "reason" in record_item['callLeg']:
reason = str(record_item['callLeg']['reason'])
console_output = cms_ip + ": We get reason from callLegEnd"
logger.debug(console_output)
else:
reason = "none"
# забираем remoteTeardown
if "remoteTeardown" in record_item['callLeg']:
remoteTeardown = str(
record_item['callLeg']['remoteTeardown'])
console_output = cms_ip + ": We get remoteTeardown from callLegEnd"
logger.debug(console_output)
else:
remoteTeardown = "none"
# забираем время лега
if "@time" in record_item:
callLegEndTime = str(record_item['@time'])
callLegEndTimeMSK = str(
datetime.datetime.strptime(callLegEndTime,
"%Y-%m-%dT%H:%M:%SZ") +
datetime.timedelta(hours=3))
console_output = cms_ip + ": We get callLegEndTime from callLegEnd"
logger.debug(console_output)
else:
callLegEndTime = "none"
callLegEndTimeMSK = "none"
#проверяем наличие информации о Аудио
if "rxAudio" in record_item['callLeg']:
if "codec" in record_item['callLeg']['rxAudio']:
acodecrx = str(
record_item['callLeg']['rxAudio']
['codec']) # забираем тип кодека аудио RX
else:
acodecrx = "none"
# собираем статистику вызова
if "packetStatistics" in record_item['callLeg'][
'rxAudio']:
if "packetGap" in record_item['callLeg'][
'rxAudio']['packetStatistics']:
if "density" in record_item['callLeg'][
'rxAudio']['packetStatistics'][
'packetGap']:
rxAudio_packetGap_density = str(
record_item['callLeg']['rxAudio']
['packetStatistics']['packetGap']
['density'])
else:
rxAudio_packetGap_density = "none"
if "duration" in record_item['callLeg'][
'rxAudio']['packetStatistics'][
'packetGap']:
rxAudio_packetGap_duration = str(
record_item['callLeg']['rxAudio']
['packetStatistics']['packetGap']
['duration'])
else:
rxAudio_packetGap_duration = "none"
else:
rxAudio_packetGap_density = "none"
rxAudio_packetGap_duration = "none"
if "packetLossBursts" in record_item['callLeg'][
'rxAudio']['packetStatistics']:
if "density" in record_item['callLeg'][
'rxAudio']['packetStatistics'][
'packetLossBursts']:
rxAudio_packetLossBurst_density = str(
record_item['callLeg']['rxAudio']
['packetStatistics']
['packetLossBursts']['density'])
else:
rxAudio_packetLossBurst_density = "none"
if "duration" in record_item['callLeg'][
'rxAudio']['packetStatistics'][
'packetLossBursts']:
rxAudio_packetLossBurst_duration = str(
record_item['callLeg']['rxAudio']
['packetStatistics']
['packetLossBursts']['duration'])
else:
rxAudio_packetLossBurst_duration = "none"
else:
rxAudio_packetLossBurst_density = "none"
rxAudio_packetLossBurst_duration = "none"
else:
rxAudio_packetGap_density = "none"
rxAudio_packetGap_duration = "none"
rxAudio_packetLossBurst_density = "none"
rxAudio_packetLossBurst_duration = "none"
else:
acodecrx = "none"
rxAudio_packetGap_density = "none"
rxAudio_packetGap_duration = "none"
rxAudio_packetLossBurst_density = "none"
rxAudio_packetLossBurst_duration = "none"
if "txAudio" in record_item['callLeg']:
if "codec" in record_item['callLeg']['txAudio']:
acodectx = str(
record_item['callLeg']['txAudio']
['codec']) # забираем тип кодека аудио TX
else:
acodectx = "none"
else:
acodectx = "none"
#проверяем наличие информации о Видео
if "rxVideo" in record_item['callLeg']:
if "codec" in record_item['callLeg']['rxVideo']:
vcodecrx = str(
record_item['callLeg']['rxVideo']
['codec']) # забираем тип кодека аудио RX
else:
vcodecrx = "none"
# собираем статистику вызова
if "packetStatistics" in record_item['callLeg'][
'rxVideo']:
if "packetGap" in record_item['callLeg'][
'rxVideo']['packetStatistics']:
if "density" in record_item['callLeg'][
'rxVideo']['packetStatistics'][
'packetGap']:
rxVideo_packetGap_density = str(
record_item['callLeg']['rxVideo']
['packetStatistics']['packetGap']
['density'])
else:
rxVideo_packetGap_density = "none"
if "duration" in record_item['callLeg'][
'rxVideo']['packetStatistics'][
'packetGap']:
rxVideo_packetGap_duration = str(
record_item['callLeg']['rxVideo']
['packetStatistics']['packetGap']
['duration'])
else:
rxVideo_packetGap_duration = "none"
else:
rxVideo_packetGap_density = "none"
rxVideo_packetGap_duration = "none"
if "packetLossBursts" in record_item['callLeg'][
'rxVideo']['packetStatistics']:
if "density" in record_item['callLeg'][
'rxVideo']['packetStatistics'][
'packetLossBursts']:
rxVideo_packetLossBurst_density = str(
record_item['callLeg']['rxVideo']
['packetStatistics']
['packetLossBursts']['density'])
else:
rxVideo_packetLossBurst_density = "none"
if "duration" in record_item['callLeg'][
'rxVideo']['packetStatistics'][
'packetLossBursts']:
rxVideo_packetLossBurst_duration = str(
record_item['callLeg']['rxVideo']
['packetStatistics']
['packetLossBursts']['duration'])
else:
rxVideo_packetLossBurst_duration = "none"
else:
rxVideo_packetLossBurst_density = "none"
rxVideo_packetLossBurst_duration = "none"
else:
rxVideo_packetGap_density = "none"
rxVideo_packetGap_duration = "none"
rxVideo_packetLossBurst_density = "none"
rxVideo_packetLossBurst_duration = "none"
else:
vcodecrx = "none"
rxVideo_packetGap_density = "none"
rxVideo_packetGap_duration = "none"
rxVideo_packetLossBurst_density = "none"
rxVideo_packetLossBurst_duration = "none"
if "txVideo" in record_item['callLeg']:
if "codec" in record_item['callLeg']['txVideo']:
vcodectx = str(
record_item['callLeg']['txVideo']
['codec']) # забираем тип кодека видео TX
else:
vcodectx = "none"
if "maxSizeHeight" in record_item['callLeg'][
'txVideo']:
maxSizeHeight_videoTX = str(
record_item['callLeg']['txVideo']
['maxSizeHeight']
) # забираем максимальную высоту видео TX
else:
maxSizeHeight_videoTX = "none"
if "maxSizeWidth" in record_item['callLeg']['txVideo']:
maxSizeWidth_videoTX = str(
record_item['callLeg']['txVideo']
['maxSizeWidth']
) # забираем максимальную ширину видео TX
else:
maxSizeWidth_videoTX = "none"
else:
vcodectx = "none"
maxSizeHeight_videoTX = "none"
maxSizeWidth_videoTX = "none"
# проверяем наличие информации об аларме
if "alarm" in record_item['callLeg']:
# проверяем наличие информации о типе аларме
if "@type" in record_item['callLeg']['alarm']:
alarm_type = str(record_item['callLeg']['alarm']
['@type']) # забираем тип аларма
else:
alarm_type = "none"
# проверяем наличие информации о продолжительности аларма
if "@durationPercentage" in record_item['callLeg'][
'alarm']:
alarm_value = str(
record_item['callLeg']['alarm']
['@durationPercentage']) # забираем тип аларма
else:
alarm_value = "none"
else:
alarm_type = "none"
alarm_value = "none"
### обновляем информацию о вызове
console_output = cms_ip + ": insert CallLegEnd data to database"
#print("CMS_RECEIVER " + console_output)
logger.debug(console_output)
cms_sql_request(
"UPDATE cms_cdr_records SET txAudio_codec='" +
acodectx + "',endTime='" + callLegEndTimeMSK +
"',durationSeconds='" + durationSeconds +
"',reason='" + reason + "',remoteTeardown='" +
remoteTeardown + "',txVideo_codec='" + vcodectx +
"',txVideo_maxHeight='" + maxSizeHeight_videoTX +
"',txVideo_maxWidth='" + maxSizeWidth_videoTX +
"',rxVideo_codec='" + vcodecrx +
"',rxVideo_packetGap_density='" +
rxVideo_packetGap_density +
"',rxVideo_packetGap_duration='" +
rxVideo_packetGap_duration +
"',rxVideo_packetLossBurst_density='" +
rxVideo_packetLossBurst_density +
"',rxVideo_packetLossBurst_duration='" +
rxVideo_packetLossBurst_duration +
"',rxAudio_codec='" + acodecrx +
"',rxAudio_packetGap_density='" +
rxAudio_packetGap_density +
"',rxAudio_packetGap_duration='" +
rxAudio_packetGap_duration +
"',rxAudio_packetLossBurst_density='" +
rxAudio_packetLossBurst_density +
"',rxAudio_packetLossBurst_duration='" +
rxAudio_packetLossBurst_duration + "',alarm_type='" +
alarm_type + "',alarm_value='" + alarm_value +
"' WHERE callleg_id='" + callleg_id + "';")
console_output = cms_ip + ": call detail updated from callLegEnd"
logger.debug(console_output)
else:
console_output = cms_ip + ": CallLeg Id not found in callLegEnd - nothing was inserted in DB"
logger.debug(console_output)
if record_item['@type'] == 'callStart':
console_output = cms_ip + ": we get callStart"
logger.debug(console_output)
if "@id" in record_item['call']:
call_id = str(
record_item['call']['@id']) # забираем call ID
console_output = cms_ip + ": We get callID from callStart"
logger.debug(console_output)
# забираем coSpace
if "coSpace" in record_item['call']:
coSpace = str(record_item['call']['coSpace'])
console_output = cms_ip + ": We get coSpace from callStart"
logger.debug(console_output)
else:
coSpace = "none"
# забираем name
if "name" in record_item['call']:
name = str(record_item['call']['name'])
console_output = cms_ip + ": We get name from callStart"
logger.debug(console_output)
else:
name = "none"
# забираем callCorrelator
if "callCorrelator" in record_item['call']:
call_correlator = str(
record_item['call']['callCorrelator'])
console_output = cms_ip + ": We get callCorrelator from callStart"
logger.debug(console_output)
else:
call_correlator = "none"
# забираем время
if "@time" in record_item:
starttime = str(record_item['@time'])
starttimeMSK = str(
datetime.datetime.strptime(starttime,
"%Y-%m-%dT%H:%M:%SZ") +
datetime.timedelta(hours=3))
console_output = cms_ip + ": We get start time from callStart"
logger.debug(console_output)
console_output = cms_ip + ": Call start time:" + starttimeMSK
logger.debug(console_output)
else:
starttime = "none"
starttimeMSK = "none"
console_output = cms_ip + ": cospace: " + coSpace + " time: " + starttime
logger.debug(console_output)
# Добавление активного вызова - начало
console_output = cms_ip + ": Request meeting_id_list form DB: " + "SELECT meeting_id FROM cms_cdr_active_calls WHERE callCorrelator_id='" + call_correlator + "'"
logger.debug(console_output)
meeting_id_list = sql_request_dict(
"SELECT meeting_id FROM cms_cdr_active_calls WHERE callCorrelator_id='"
+ str(call_correlator) + "'")
if not meeting_id_list:
meeting_id = str(uuid.uuid1())
console_output = cms_ip + ": insert new active CALL to database with new meeting_id: " + meeting_id
logger.debug(console_output)
# insert new active call IDs to database
sql_execute(
"INSERT INTO cms_cdr_active_calls SET meeting_id='"
+ meeting_id + "',coSpace_id='" + coSpace +
"',callCorrelator_id='" + call_correlator +
"',call_id='" + call_id + "';")
else:
console_output = cms_ip + ": active call already exists in database with call_correlator: " + call_correlator
logger.debug(console_output)
if meeting_id_list[0]["meeting_id"]:
meeting_id = str(meeting_id_list[0]["meeting_id"])
console_output = cms_ip + ": insert existing active call in database with meeting_id: " + meeting_id
logger.debug(console_output)
sql_execute(
"INSERT INTO cms_cdr_active_calls SET meeting_id='"
+ meeting_id + "',coSpace_id='" + coSpace +
"',callCorrelator_id='" + call_correlator +
"',call_id='" + call_id + "';")
else:
console_output = cms_ip + ": meeting_id not found for callCorrelator: " + call_correlator
meeting_id = "UNKNOWN"
logger.debug(console_output)
# Добавление активного вызова - конец
if not cm_sqlselect_dict('id', 'cms_cdr_calls', 'id',
call_id):
console_output = cms_ip + ": insert CALL to database"
logger.debug(console_output)
# insert IDs to database
cms_sql_request("INSERT INTO cms_cdr_calls SET id='" +
call_id + "',StartTime='" +
starttimeMSK + "',coSpace='" +
coSpace + "',cms_ip='" + cms_ip +
"',callCorrelator='" +
call_correlator + "',meeting_id='" +
meeting_id + "',name='" + name + "';")
else:
console_output = cms_ip + ": Space ID data already presence"
logger.debug(console_output)
if record_item['@type'] == 'callEnd':
console_output = cms_ip + ": we get callEnd"
logger.debug(console_output)
if "@id" in record_item['call']:
call_id = str(record_item['call']['@id'])
console_output = cms_ip + ": We get callID from callEnd"
logger.debug(console_output)
# забираем call_callLegsMaxActive
if "callLegsMaxActive" in record_item['call']:
call_callLegsMaxActive = str(
record_item['call']['callLegsMaxActive'])
console_output = cms_ip + ": We get call_callLegsMaxActive from callEnd"
logger.debug(console_output)
else:
call_callLegsMaxActive = "none"
# забираем call_durationSeconds
if "durationSeconds" in record_item['call']:
call_durationSeconds = str(
record_item['call']['durationSeconds'])
console_output = cms_ip + ": We get call_durationSeconds from callEnd"
logger.debug(console_output)
else:
call_durationSeconds = "none"
# забираем время
if "@time" in record_item:
call_endtime = str(record_item['@time'])
call_endtimeMSK = str(
datetime.datetime.strptime(call_endtime,
"%Y-%m-%dT%H:%M:%SZ") +
datetime.timedelta(hours=3))
console_output = cms_ip + ": Call end time: " + call_endtimeMSK
logger.debug(console_output)
else:
call_endtime = "none"
call_endtimeMSK = "none"
if cm_sqlselect_dict('id', 'cms_cdr_calls', 'id', call_id):
console_output = cms_ip + ": update CALL to database"
logger.debug(console_output)
# insert IDs to database
cms_sql_request("UPDATE cms_cdr_calls SET EndTime='" +
call_endtimeMSK +
"',callLegsMaxActive='" +
call_callLegsMaxActive +
"',durationSeconds='" +
call_durationSeconds +
"' WHERE cms_cdr_calls.id='" +
call_id + "';")
else:
console_output = cms_ip + ": Call " + call_id + " is not found in DB"
logger.debug(console_output)
# Удаление активного вызова - начало
console_output = cms_ip + ": Start to delete active call: " + "DELETE FROM cms_cdr_active_calls WHERE call_id='" + call_id + "'"
logger.debug(console_output)
sql_execute(
"DELETE FROM cms_cdr_active_calls WHERE call_id='" +
str(call_id) + "'")
console_output = cms_ip + ": End to delete active call"
logger.debug(console_output)
# Удаление активного вызова - конец
else:
console_output = cms_ip + ": callEnd does not contain CallID"
logger.debug(console_output)
if record_item['@type'] == 'recordingStart':
console_output = cms_ip + ": we get recordingStart"
logger.debug(console_output)
if "@id" in record_item['recording']:
recording_id = str(record_item['recording']['@id'])
console_output = cms_ip + ": We get RecordingID from recordingStart"
logger.debug(console_output)
# забираем path
if "path" in record_item['recording']:
recording_path = str(record_item['recording']['path'])
console_output = cms_ip + ": We get path from recordingStart"
logger.debug(console_output)
else:
recording_path = "none"
# забираем call_id
if "call" in record_item['recording']:
recording_call_id = str(
record_item['recording']['call'])
console_output = cms_ip + ": We get call_id from recordingStart"
logger.debug(console_output)
else:
recording_call_id = "none"
# забираем callLeg_id
if "callLeg" in record_item['recording']:
recording_callLeg_id = str(
record_item['recording']['callLeg'])
console_output = cms_ip + ": We get callLeg_id from recordingStart"
logger.debug(console_output)
else:
recording_callLeg_id = "none"
### добавляем информацию о записи в базу
cms_sql_request(
"INSERT INTO cms_cdr_recordings SET recording_id='" +
recording_id + "',path='" + recording_path +
"',call_id='" + recording_call_id + "',callLeg_id='" +
recording_callLeg_id + "';")
console_output = cms_ip + ": RecordingID: " + recording_id + " inserted to database"
logger.debug(console_output)
return ('', 204)
except:
console_output = cms_ip + "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!<<<Parser failure>>!"
print("CMS_RECEIVER " + console_output)
logger.error(console_output)
pprint(cdr_dict)
logger.error("\n" + pformat(record_item))
return ('', 204)
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 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 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 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_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 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 submit_order(systemindex):
auth_data_list = sql_request_dict(
"SELECT room_ip,room_user,room_password FROM cm_roomsystems_table WHERE room_index='"
+ systemindex + "'") # получаем лист словарей
roomkit_access_data_ip = str(auth_data_list[0]['room_ip']) # забираем IP
roomkit_access_data_login = str(
auth_data_list[0]['room_user']) # забираем пользователя
roomkit_access_data_password = str(
auth_data_list[0]['room_password']) # забираем пароль
print("Roomcontrol: submit order " + request.method)
set_value(systemindex, "CoffeeCount", "0")
set_value(systemindex, "TeaCount", "0")
# URL
http_url = "http://" + roomkit_access_data_ip + "/putxml"
# HTTP Headers
http_headers = {'Content-Type': 'text/xml'}
http_data = """
<Command>
<UserInterface>
<Message>
<Alert>
<Display>
<Duration>15</Duration>
<Text>Заказ принят</Text>
</Display>
</Alert>
</Message>
</UserInterface>
</Command>"""
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(http_url,
data=http_data.encode('utf-8'),
headers=http_headers,
verify=False,
auth=(roomkit_access_data_login,
roomkit_access_data_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + roomkit_access_data_ip
print(console_output)
return "OK"
except:
console_output = "Что-то пошло не так при подключении пользователя " + roomkit_access_data_login + " к серверу " + roomkit_access_data_ip
print(console_output)
return "OK"
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + roomkit_access_data_login + " не авторизован для подключения к серверу " + roomkit_access_data_ip
print(console_output)
return "OK"
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
return "OK"
# Convert output to Dict
console_output = "Данные получены от " + roomkit_access_data_ip
print(console_output)
print("Send submit to phone")
return '<CiscoIPPhoneText><Title>Заказ</Title><Text>Заказ подтвержден</Text><SoftKeyItem><Name>Выход</Name><URL>SoftKey:Exit</URL><Position>1</Position></SoftKeyItem></CiscoIPPhoneText>'.encode(
'utf-8')
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 send_order(systemindex):
#credentials from database
auth_data_list = sql_request_dict(
"SELECT server_config_table.server_port,server_config_table.server_ip, cm_phones_table.phone_ip,cm_phones_table.phone_user,cm_phones_table.phone_password FROM cm_phones_table INNER JOIN server_config_table ON server_config_table.server_index=cm_phones_table.phone_index WHERE server_config_table.server_index='"
+ systemindex + "'") # получаем лист словарей
#submit_server = cm_sqlselect("server_ip", "server_config_table", "server_index", "0")
#submit_server_port = cm_sqlselect("server_port", "server_config_table", "server_index", "0")
#phone_access_data_ip = cm_sqlselect("phone_ip", "cm_phones_table", "phone_index", systemindex)
#phone_access_data_login = cm_sqlselect("phone_user", "cm_phones_table", "phone_index", systemindex)
#phone_access_data_password = cm_sqlselect("phone_password", "cm_phones_table", "phone_index", systemindex)
phone_access_data_ip = str(auth_data_list[0]['phone_ip']) # забираем IP
phone_access_data_login = str(
auth_data_list[0]['phone_user']) # забираем пользователя
phone_access_data_password = str(
auth_data_list[0]['phone_password']) # забираем пароль
submit_server = str(auth_data_list[0]['server_ip'])
submit_server_port = str(auth_data_list[0]['server_port'])
widget_data_CoffeeCount = cm_sqlselect("widget_data", "widget_table",
"widget_name", "CoffeeCount")
widget_data_TeaCount = cm_sqlselect("widget_data", "widget_table",
"widget_name", "TeaCount")
print("Roomcontrol: Send order")
# URL
http_url = "http://" + phone_access_data_ip + "/CGI/Execute"
# HTTP Headers
http_headers = {'Content-Type': 'text/xml; charset=utf-8'}
message = "Прошу подать следующие напитки \nКофе: " + str(
widget_data_CoffeeCount) + "\n" + "Чай: " + str(widget_data_TeaCount)
http_data = 'XML=<?xml version="1.0" encoding="utf-8" ?><CiscoIPPhoneText><Title>Конференц-зал</Title><Text>' + message + '</Text><SoftKeyItem><Name>Подтвердить</Name><URL method="post">http://' + submit_server + ':' + submit_server_port + '/SubmitOrder</URL><Position>4</Position></SoftKeyItem></CiscoIPPhoneText>'
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(http_url,
data=http_data.encode('utf-8'),
headers=http_headers,
verify=False,
auth=(phone_access_data_login,
phone_access_data_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + phone_access_data_ip
print(console_output)
return
except:
console_output = "Что-то пошло не так при подключении пользователя " + phone_access_data_login + " к серверу " + phone_access_data_ip
print(console_output)
return
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + phone_access_data_login + " не авторизован для подключения к серверу " + phone_access_data_ip
print(console_output)
return
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
return
# Convert output to Dict
console_output = "Данные получены от " + phone_access_data_ip
print(console_output)
http_data = 'XML= <CiscoIPPhoneExecute><ExecuteItem URL="Play:Chime.raw" /></CiscoIPPhoneExecute>'
# Create the Requests Connection
try:
post = requests.post(http_url,
data=http_data,
headers=http_headers,
verify=False,
auth=(phone_access_data_login,
phone_access_data_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + phone_access_data_ip
print(console_output)
return
except:
console_output = "Что-то пошло не так при подключении пользователя " + phone_access_data_login + " к серверу " + phone_access_data_ip
print(console_output)
return
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + phone_access_data_login + " не авторизован для подключения к серверу " + phone_access_data_ip
print(console_output)
return
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
return
# Convert output to Dict
console_output = "Данные получены от " + phone_access_data_ip
print(console_output)
def get_value(systemindex):
# credentials from database
#roomkit_access_data_ip = cm_sqlselect("room_ip", "cm_roomsystems_table", "room_index", systemindex)
#roomkit_access_data_login = cm_sqlselect("room_user", "cm_roomsystems_table", "room_index", systemindex)
#roomkit_access_data_password = cm_sqlselect("room_password", "cm_roomsystems_table", "room_index", systemindex)
auth_data_list = sql_request_dict(
"SELECT room_ip,room_user,room_password FROM cm_roomsystems_table WHERE room_index='"
+ systemindex + "'") # получаем лист словарей
roomkit_access_data_ip = str(auth_data_list[0]['room_ip']) # забираем IP
roomkit_access_data_login = str(
auth_data_list[0]['room_user']) # забираем пользователя
roomkit_access_data_password = str(
auth_data_list[0]['room_password']) # забираем пароль
widget_data = {}
print("Выполняется функция считывания значений виджетов get_value")
# URL
http_url = "http://" + roomkit_access_data_ip + "/getxml"
# HTTP Headers
http_headers = {'Content-Type': 'text/xml'}
http_params = {"location": "/Status/UserInterface/Extensions"}
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
get = requests.get(http_url,
params=http_params,
headers=http_headers,
verify=False,
auth=(roomkit_access_data_login,
roomkit_access_data_password))
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + roomkit_access_data_ip
print(console_output)
return
except:
console_output = "Что-то пошло не так при подключении пользователя " + roomkit_access_data_login + " к серверу " + roomkit_access_data_ip
print(console_output)
return
# Check is answer is successful
if get.status_code == 401:
console_output = "Пользователь " + roomkit_access_data_login + " не авторизован для подключения к серверу " + roomkit_access_data_ip
print(console_output)
return
if get.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
get.status_code) + ": " + get.reason
print(console_output)
return
# Convert output to Dict
console_output = "Данные получены от " + roomkit_access_data_ip
print(console_output)
xml_dict = xmltodict.parse(get.text)
# Get Dict with phones
widget_list = xml_dict["Status"]["UserInterface"]["Extensions"]["Widget"]
print(xml_dict)
if type(widget_list) is list:
for widget in widget_list:
widget_data[widget["WidgetId"]] = widget["Value"]
if "CoffeeCount" in widget_data:
if widget_data["CoffeeCount"] is None:
set_value(systemindex, "CoffeeCount", "0")
if "TeaCount" in widget_data:
if widget_data["TeaCount"] is None:
set_value(systemindex, "TeaCount", "0")
print("Roomcontrol: Установлены исходные значения для виджетов:")
pprint(widget_data)
def set_value(systemindex, widget_name, widget_value):
# credentials from database
#roomkit_access_data_ip = cm_sqlselect("room_ip", "cm_roomsystems_table", "room_index", systemindex)
#roomkit_access_data_login = cm_sqlselect("room_user", "cm_roomsystems_table", "room_index", systemindex)
#roomkit_access_data_password = cm_sqlselect("room_password", "cm_roomsystems_table", "room_index", systemindex)
auth_data_list = sql_request_dict(
"SELECT room_ip,room_user,room_password FROM cm_roomsystems_table WHERE room_index='"
+ systemindex + "'") # получаем лист словарей
roomkit_access_data_ip = str(auth_data_list[0]['room_ip']) # забираем IP
roomkit_access_data_login = str(
auth_data_list[0]['room_user']) # забираем пользователя
roomkit_access_data_password = str(
auth_data_list[0]['room_password']) # забираем пароль
print("Выполняется функция установки значений виджетов set_value")
# URL
http_url = "http://" + roomkit_access_data_ip + "/putxml"
# HTTP Headers
http_headers = {'Content-Type': 'text/xml'}
http_data = """
<Command>
<UserInterface>
<Extensions>
<Widget>
<SetValue>
<WidgetId>""" + widget_name + """</WidgetId>
<Value>""" + widget_value + """</Value>
</SetValue>
</Widget>
</Extensions>
</UserInterface>
</Command>
"""
# disable warning about untrusted certs
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
# Create the Requests Connection
try:
post = requests.post(http_url,
data=http_data,
headers=http_headers,
verify=False,
auth=(roomkit_access_data_login,
roomkit_access_data_password))
cm_sqlupdate(widget_value, "widget_table", "widget_data",
"widget_name", widget_name)
except requests.exceptions.ConnectionError:
console_output = "Ошибка соединения с сервером " + roomkit_access_data_ip
print(console_output)
return
except:
console_output = "Что-то пошло не так при подключении пользователя " + roomkit_access_data_login + " к серверу " + roomkit_access_data_ip
print(console_output)
return
# Check is answer is successful
if post.status_code == 401:
console_output = "Пользователь " + roomkit_access_data_login + " не авторизован для подключения к серверу " + roomkit_access_data_ip
print(console_output)
return
if post.status_code != 200:
console_output = "Ошибка при подключении к серверу: " + str(
post.status_code) + ": " + post.reason
print(console_output)
return
# Convert output to Dict
console_output = "Данные получены от " + roomkit_access_data_ip
print(console_output)
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)
