def query(values):
"""service层处理查询网关接口信息请求"""
returnCode = 0
errorMessage = "操作成功"
t_ems_eq = Equipment()
try:
# 一次查询条数
eq_info = t_ems_eq.all(**values)
if len(eq_info) == 0:
returnCode = 501001
errorMessage = "sn信息不存在"
elif eq_info[0]['sold'] == 2:
returnCode = 501004
errorMessage = "sn已出售"
elif eq_info[0]['status'] == 0:
returnCode = 501005
errorMessage = "sn为损坏设备"
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
result = {"returnCode": returnCode, "errorMessage": errorMessage}
return result
def update(values):
"""service层处理新增网关接口信息请求"""
returnCode = 0
errorMessage = "操作成功"
try:
t_ems_equipment = Equipment()
params = {"sn": values.pop("sn")}
query_result = t_ems_equipment.all(**params)
if len(query_result) == 0:
returnCode = 501001
errorMessage = "sn信息不存在"
return
elif query_result[0]['sold'] == 2:
returnCode = 501004
errorMessage = "sn已出售"
return
elif query_result[0]['status'] == 0:
returnCode = 501005
errorMessage = "损坏设备不可被绑定"
return
# 更新数据到数据库(oms调用此接口，意味着设备已经被订单绑定)
values['sold'] = 2
data_result = t_ems_equipment.update(values, **params)
if data_result == 0:
returnCode = 501100
errorMessage = "操作数据库错误"
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
return returnCode, errorMessage
def update(values):
"""service层处理更新设备信息"""
returnCode = 0
errorMessage = "操作成功"
try:
t_ems_equipment = Equipment()
# 查询需更新信息是否存在
params = {"sn": values["sn"]}
query_result = t_ems_equipment.all(**params)
for item in values.keys():
if item not in ['sn', 'status', 'desc']:
values.pop(item)
if len(query_result) == 0:
returnCode = 501001
errorMessage = "sn信息不存在"
data_result = t_ems_equipment.update(values, **params)
if data_result == 0:
returnCode = 501100
errorMessage = "操作数据库错误"
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
return returnCode, errorMessage
def insert(path, fileparm):
"""service层处理新增sn信息请求"""
returnCode = 0
errorMessage = "操作成功"
recordNum = 0
retude = returnCode
try:
# 吧文件给analysis() 解析 ！！！！
RUNLOG.info(path)
list_rows, returnCode, errorMessage = analysis(path)
print list_rows
if returnCode != 0:
retude = returnCode
return
for param in list_rows:
# 将表格中的数据存入数据库
returnCode, errorMessage, status = db_insert(param)
if status == 2:
fileparm['status'] = 2
if returnCode == 0:
recordNum += 1
else:
retude = returnCode
fileparm['file_name'] = path
# 表格导入状态存到数据库
t_ems_import_record = ImportRecord()
t_ems_import_record.insert(fileparm)
except BaseException:
retude = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
return retude, errorMessage, recordNum
def get(self):
"""
user_group调用cas接口组装返回数据
"""
start_time = datetime.datetime.now()
returnCode = 0
errorMessage = "操作成功"
operator_list = []
total_size = 0
values = {}
try:
username = self.cookies.get("tokenUserName").value
username = username[
3:-3] if username[:3] == "%22" else username # 去除%22 双引号
auth_result = cas(self.cookies, self.request.path,
self.request.method.lower())
if auth_result['result'] == 'CAS_SUCCESS':
url = self.request.uri
if "start_num" not in url:
returnCode = 406400
errorMessage = "参数错误"
return
if "operator_type" not in url:
returnCode = 406400
errorMessage = "参数错误"
return
else:
start_num = self.get_argument("start_num")
start_num = int(start_num)
values['start_num'] = start_num
values['operator_type'] = self.get_argument(
"operator_type")
if "size" in url:
size = self.get_argument("size")
values["size"] = int(size)
else:
values["size"] = 10
if 'operator_name' in url:
operator_name = self.get_argument("operator_name")
values["operator_name"] = operator_name
returnCode, errorMessage, operator_list, total_size = select(
values)
else:
returnCode = -2
errorMessage = auth_result['result']
except Exception:
returnCode = 506000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
self.request.body = dumps(values)
end_time = datetime.datetime.now() # 操作日志记录
execute_time = (end_time -
start_time).microseconds / 1000 # 业务操作耗时
record_operation_log(self.request, '1', username, returnCode,
errorMessage, execute_time) # 操作日志记录
self.write(dumps({"returnCode": returnCode, "errorMessage": errorMessage,\
"operator_list": operator_list, "total_size": total_size}))
def analysis(path):
"""
解析表格
"""
list_rows = []
returnCode = 0
errorMessage = "操作成功"
try:
workbook = xlrd.open_workbook(path)
for sheet_name in workbook.sheet_names():
sheet2 = workbook.sheet_by_name(sheet_name)
lst_1_line = sheet2.row_values(0)
for row in range(1, sheet2.nrows): # 确认读取的行数是？
res = {}
rows = sheet2.row_values(row)
if row == 1:
eq_type = sheet2.row_values(row)[-1]
if not isinstance(eq_type, int):
if isinstance(eq_type, float):
RUNLOG.info("In excel, the eq_type is float")
eq_type = int(eq_type)
else:
RUNLOG.info(
"In excel, the eq_type is not int or float")
returnCode = 501300
errorMessage = "表格不规范"
return
continue
elif row == 2 or row == 3 or row == 4:
continue
elif row != 2:
if not isinstance(rows, list):
RUNLOG.info("In excel, the rows_type is not list")
returnCode = 501300
errorMessage = "表格不规范"
return
if not isinstance(rows[0], unicode):
RUNLOG.info("In excel, the rows[0] is not unicode")
returnCode = 501300
errorMessage = "表格不规范"
return
res['sn'] = rows[0]
res['eq_type'] = eq_type
if row >= 3:
list_rows.append(res)
else:
del res
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
return list_rows, returnCode, errorMessage
def write_xls(self, data_array):
"""
将数据写进表格
"""
sio = StringIO.StringIO()
try:
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet 1', cell_overwrite_ok=True)
sheet.write(0, 0, 'State ',
xlwt.easyxf('font: height 250, '
'bold off'))
sheet.write(0, 1, 'New sales', xlwt.easyxf('font: height 250'))
sheet.write(0, 2, 'Accumulated sales',
xlwt.easyxf('font: height 250'))
hang = 1
first_col = sheet.col(0) # xlwt中是行和列都是从0开始计算的
sec_col = sheet.col(1)
thrd_col = sheet.col(2)
first_col.width = 150 * 30
sec_col.width = 150 * 30
thrd_col.width = 250 * 30
# 对查询好的字典信息按照州名降序排列
data_array = sorted(data_array,
key=lambda data_array: data_array['state'])
for data in data_array:
for lie in range(len(data.keys())):
if lie == 0:
content = data['state']
elif lie == 1:
content = data['newSales']
elif lie == 2:
content = data['accumulatedSales']
sheet.write(
hang, lie, content,
xlwt.easyxf(
'font: height 250, name Arial, colour_index black, '
'bold off, italic off; align: wrap on, vert centre, horiz left;'
))
hang = hang + 1
sio = StringIO.StringIO()
wbk.save(sio)
# wbk.save('/opt/powercloud/dcs.xls')
# ## raise gen.Return(sio)
except BaseException:
RUNLOG.error(traceback.format_exc())
finally:
return sio
def get(self):
"""
通过此接口查询系统直播流详情请求
参数说明：
unique_type 必选 正整数 8 表示从哪开始查询
examine_column 必选 字符 64 网元id
异常：
-1 ： 操作失败
400: 参数错误
500: 系统内部错误
1000: 操作数据库错误
5000: 输入参数已经存在
"""
returnCode = -1
errorMessage = "操作失败"
total_size = 0
result = {"returnCode": returnCode, "errorMessage": errorMessage}
values = {}
try:
# 读取参数
arguments = self.request.arguments
RUNLOG.info("receive request arguments = %s" % arguments)
for key, value in arguments.items():
values[key] = value[0]
# 参数合法性校验
for item in ["unique_type", "examine_column", "examine_data"]:
if item not in values.keys():
returnCode = 401400
errorMessage = "参数错误"
raise MessageError(returnCode, errorMessage)
result = unique_query(values)
except MessageError, ex:
result["returnCode"] = ex.error_code
result["errorMessage"] = ex.errorMessage
RUNLOG.warn("returnCode = %s, errorMessage = %s" %
(returnCode, errorMessage))
class UniqueHandler(web.RequestHandler):
"""唯一性验证接口"""
def data_received(self, chunk):
pass
def get(self):
"""
通过此接口查询系统直播流详情请求
参数说明：
unique_type 必选 正整数 8 表示从哪开始查询
examine_column 必选 字符 64 网元id
异常：
-1 ： 操作失败
400: 参数错误
500: 系统内部错误
1000: 操作数据库错误
5000: 输入参数已经存在
"""
returnCode = -1
errorMessage = "操作失败"
total_size = 0
result = {"returnCode": returnCode, "errorMessage": errorMessage}
values = {}
try:
# 读取参数
arguments = self.request.arguments
RUNLOG.info("receive request arguments = %s" % arguments)
for key, value in arguments.items():
values[key] = value[0]
# 参数合法性校验
for item in ["unique_type", "examine_column", "examine_data"]:
if item not in values.keys():
returnCode = 401400
errorMessage = "参数错误"
raise MessageError(returnCode, errorMessage)
result = unique_query(values)
except MessageError, ex:
result["returnCode"] = ex.error_code
result["errorMessage"] = ex.errorMessage
RUNLOG.warn("returnCode = %s, errorMessage = %s" %
(returnCode, errorMessage))
except BaseException:
result["returnCode"] = 501000
result["recordNum"] = "系统内部错误"
RUNLOG.error(traceback.format_exc())
def db_insert(values):
"""
数据库插入操作
:param values:
:return:
"""
returnCode = 0
errorMessage = "操作成功"
session = SESSION()
status = 1 # 1成功 2失败
try:
t_ems_equipment = Equipment()
t_failing_sn = FailingSn()
if len(values['sn']) != 14:
returnCode = 501400
errorMessage = "sn参数不合法"
fail_sn = session.query(FailingSn).filter(
FailingSn.sn == values['sn']).all()
if len(fail_sn) == 0:
values['status'] = 2
t_failing_sn.insert(values)
return
sn_obj = session.query(Equipment).filter(
Equipment.sn == values['sn']).all()
if len(sn_obj) == 0:
data_result = t_ems_equipment.insert(values)
if data_result["errorcode"] == -1:
returnCode = 501100
errorMessage = "操作数据库错误"
RUNLOG.info("操作数据库错误")
status = 2
RUNLOG.info("sn:%s新增失败" % values['sn'])
# 插入数据库失败的数据，先查看是不是已经存在设备正常表中了，如果没有，请插入到设备异常表
if len(sn_obj) == 0:
values['status'] = 2
t_failing_sn.insert(values)
else:
# 对于新增成功的设备，先处理设备异常表中是否存在此设备号并删除
session.query(FailingSn).filter(
FailingSn.sn == values['sn']).delete(
synchronize_session=False)
session.flush()
session.commit()
except BaseException:
session.rollback()
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
session.close()
return returnCode, errorMessage, status
def db_insert(values):
"""
数据库插入操作
:param values:
:return:
"""
returnCode = 0
errorMessage = "操作成功"
session = SESSION()
try:
t_ems_equipment = Equipment()
if len(values['sn']) >= 16:
RUNLOG.info("sn:%s长度大于16！！！" % values['sn'])
failing_insert(values)
va_sn = {'sn': values['sn']}
data_ex = t_ems_equipment.all(**va_sn)
if len(data_ex) != 0:
returnCode = 501002
errorMessage = "数据库已存在此数据"
return
data_result = t_ems_equipment.insert(values)
if data_result["errorcode"] == -1:
returnCode = 501100
errorMessage = "操作数据库错误"
RUNLOG.info("操作数据库错误")
RUNLOG.info("sn:%s新增失败" % values['sn'])
if "way" in values.keys():
failing_insert(values)
else:
# RUNLOG.info("sn:%s新增成功之后，删除表t_failing_sn中的这条sn记录" % values['sn'])
session.query(FailingSn).filter(FailingSn.sn == values['sn']).delete(synchronize_session=False)
session.flush()
session.commit()
except BaseException:
session.rollback()
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
session.close()
return returnCode, errorMessage
def get(self):
username = None
values = {}
try:
username = self.cookies.get("tokenUserName").value
username = username[
3:-3] if username[:3] == "%22" else username # 去除%22 双引号
auth_result = cas(self.cookies, self.request.path,
self.request.method.lower())
if auth_result['result'] == 'CAS_SUCCESS':
self.set_header('Content-Type', 'application/octet-stream')
self.set_header('Content-Disposition',
'attachment; filename=sales_trend.xls')
arguments = self.request.arguments
RUNLOG.info("receive request arguments = %s" % arguments)
for key, value in arguments.items():
values[key] = value[0]
# # 参数合法性校验
for item in ["startTime", "endTime"]:
if item not in values.keys():
returnCode = 406400
errorMessage = "参数错误"
result = {
"returnCode": returnCode,
"errorMessage": errorMessage
}
RUNLOG.info(str(result))
return
returnCode, errorMessage, list_info = query(values)
self.file_exits()
# 将表格存入文件流
sio = yield self.write_xls(list_info)
except BaseException:
returnCode = 506000
errorMessage = "系统内部错误"
result = {"returnCode": returnCode, "errorMessage": errorMessage}
RUNLOG.info(str(result))
RUNLOG.error(traceback.format_exc())
finally:
self.write(sio.getvalue())
self.finish()
# size = t_cdn_gateway.count("table_id", **count_params)
# from dao import SESSION
# from utils.globals import converter
# 测试mysql的session,是ok的
# session = SESSION()
# obj = session.query(CDNGateWay).filter(CDNGateWay.name == count_params['name']).order_by(CDNGateWay.table_id.desc()).all()
# obj2 = converter(obj)
# size = len(obj2)
# session.close()
# print t_cdn_gateway.all(**count_params)
else:
raise MessageError(501004, "参数错误")
if size != 0:
raise MessageError(501007, "参数已存在")
except MessageError, ex:
returnCode = ex.error_code
errorMessage = ex.errorMessage
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
result = {
"returnCode": returnCode,
"errorMessage": errorMessage,
}
return result
def select(values):
"""门户关联管理操作员查询接口调用函数，service层查询逻辑处理。
参数：
values 字典
"""
returnCode = 0
errorMessage = "操作成功"
result = {}
child_list = []
operator_info_list = []
total_size = 0
try:
# 分页查询条目和起点计算
# page_num = int(values.get("pageNum"))
# 每页展示条数默认为10，当大于1000时，取1000
# page_size = 1000 if int(values.get("pageSize", 10)) > 1000 else int(values.get("pageSize", 10))
key = "ertjmFRGyriRFogjCDf38978"
page_num = values['start_num']
page_size = values['size']
offset = int(page_num) * int(page_size) # 分页起点
limit = offset + int(page_size) # 每页展示条数
params = {
"key": key,
"page": 1,
"results_per_page": 9999,
"operatorType": "2"
}
portal_id = values.get("portalId")
status = values.get("status")
operator_name = values.get("operatorName")
auth_status = values.get("authStatus")
# 设置查询条件
if operator_name:
params["operatorName"] = operator_name
url = CAS_OPERATOR_SEARCH + '/api/cas/operator'
# 循环请求3次
for i in range(3):
response = requests.get(url=url,
params=params,
headers={
"Connection": "close",
'content-type': 'application/json'
})
if response.status_code is not 200:
logstr = "portalRefAuth release get cas error: status_code %d in %d release" \
% (response.status_code, i+1)
RUNLOG.error(logstr)
elif response.content:
result = loads(response.content)
except Exception:
returnCode = 500
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
total_size = result['num_results']
operator_list = result['objects']
return returnCode, errorMessage, operator_list, total_size
def linux_main():
"""程序入口main函数"""
try:
options.parse_command_line()
port = options.port
APP.listen(int(port))
tornado.ioloop.IOLoop.instance().start()
tornado.ioloop.IOLoop.instance().close()
except Exception:
print traceback.format_exc()
#create_daemon()
options.parse_command_line()
APP.listen(options.port)
tornado.ioloop.IOLoop.instance().start()
if __name__ == '__main__':
reload(sys)
sys.setdefaultencoding('utf8')
RUNLOG.loginit(LOG_CONF_PATH, LOG_DB_PATH, 30)
if "Windows" in platform.system():
windows_main()
elif "Linux" in platform.system():
linux_main()
else:
print platform.system()
def insert(values):
"""service层处理新增网关接口信息请求"""
returnCode = 0
errorMessage = "操作成功"
recordNum = 0
try:
if len(values['sn']) == 0:
returnCode = 501400
errorMessage = 'sn参数不合法'
return
# way判断新增设备的方式，0单条 1批量
if int(values["way"]) == 0:
recordNum = 1
values.pop("way")
eq_type = values['sn'].split("-")
if len(eq_type) < 3:
returnCode = 501400
errorMessage = 'sn参数不合法'
return
values['eq_type'] = eq_type[1]
# 插入数据到数据库
if len(values['sn']) != 14:
returnCode = 501400
errorMessage = "sn参数不合法"
return
returnCode, errorMessage = db_insert(values)
elif int(values["way"]) == 1:
start_num = values.pop("startValue")
end_num = values.pop("endValue")
if (start_num == end_num) or (len(start_num) != 8) or (len(end_num) != 8):
returnCode = 501400
errorMessage = "sn参数不合法"
return
sn_former = values["sn"] + '-'
recordNum = 0
eq_type = sn_former.split("-")
values['eq_type'] = eq_type[1]
snum = 0
for index in end_num:
if index != '0':
break
else:
snum = snum + 1
if end_num[snum] != '0':
break
if snum != 0:
# endnum中不是有效数字，需要处理数字前面的0,（先判断并分离前面的0，在做循环）
sn_former = sn_former + end_num[:snum]
for num in range(int(start_num[snum - 1:]), int(end_num[snum - 1:]) + 1):
digit_num = len(str(int(end_num) + 1))
len_num = len(str(num))
if len_num < digit_num:
num = (digit_num - len_num) * '0' + str(num)
recordNum += 1
values["sn"] = sn_former + str(num)
print values["sn"]
# if len(values['sn']) != 14:
# returnCode = 501400
# errorMessage = "sn参数不合法"
# continue
returnCode, errorMessage = db_insert(values)
else:
# endnum 全是有效数字，例如10000000
for num in range(int(start_num), int(end_num) + 1):
recordNum += 1
values["sn"] = sn_former + str(num)
print values["sn"]
# if len(values['sn']) != 14:
# returnCode = 501400
# errorMessage = "sn参数不合法"
# return
returnCode, errorMessage = db_insert(values)
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
return returnCode, errorMessage, recordNum
def query(values):
"""service层处理查询网关接口信息请求"""
returnCode = 0
errorMessage = "操作成功"
eq_info_list = []
total_size = 0
t_ems_eq = Equipment()
try:
# 一次查询条数
params = {"filters": []}
count_params = {"filters": []}
params["limit"] = int(values['pageSize'])
params["offset"] = (int(values["pageNum"]) - 1) * int(
values['pageSize'])
params["order_by"] = "update_time"
params["direction"] = "desc"
# 根据sn模糊查询
if "sn" in values.keys():
strs = values["sn"]
strs = strs.replace('%', '[%')
sn = strs.replace('_', '[_')
params["filters"].append({"op": "like", "value": sn, "name": "sn"})
count_params["filters"].append({
"op": "like",
"value": sn,
"name": "sn"
})
# 根据eq_type模糊查询
if "eqType" in values.keys():
strs = values["eqType"]
strs = strs.replace('%', '[%')
eqType = strs.replace('_', '[_')
params["filters"].append({
"op": "==",
"value": eqType,
"name": "eq_type"
})
count_params["filters"].append({
"op": "==",
"value": eqType,
"name": "eq_type"
})
# 根据status模糊查询
if "status" in values.keys():
strs = values["status"]
strs = strs.replace('%', '[%')
status = strs.replace('_', '[_')
params["filters"].append({
"op": "==",
"value": status,
"name": "status"
})
count_params["filters"].append({
"op": "==",
"value": status,
"name": "status"
})
# 根据sold模糊查询
if "sold" in values.keys():
strs = values["sold"]
strs = strs.replace('%', '[%')
sold = strs.replace('_', '[_')
params["filters"].append({
"op": "==",
"value": sold,
"name": "sold"
})
count_params["filters"].append({
"op": "==",
"value": sold,
"name": "sold"
})
# 根据起始时间模糊查询startTime，endTime
if "startTime" in values.keys():
strs = values["startTime"]
strs = strs.replace('%', '[%')
startTime = strs.replace('_', '[_')
params["filters"].append({
"op": ">=",
"value": startTime,
"name": "update_time"
})
count_params["filters"].append({
"op": ">=",
"value": startTime,
"name": "update_time"
})
if "endTime" in values.keys():
strs = values["endTime"]
strs = strs.replace('%', '[%')
endTime = strs.replace('_', '[_')
params["filters"].append({
"op": "<=",
"value": endTime,
"name": "update_time"
})
count_params["filters"].append({
"op": "<=",
"value": endTime,
"name": "update_time"
})
# 数据库查询
eq_info_list = t_ems_eq.all(**params)
total_size = len(eq_info_list)
if len(eq_info_list) != 0:
for eq_info in eq_info_list:
if "update_time" not in eq_info.keys(
) or not eq_info['update_time']:
continue
else:
eq_info['update_time'] = eq_info['update_time'].strftime(
"%Y-%m-%d %H:%M:%S")
total_size = t_ems_eq.count("sn", **count_params)
except BaseException:
returnCode = 501000
errorMessage = "系统内部错误"
RUNLOG.error(traceback.format_exc())
finally:
result = {"returnCode": returnCode, "errorMessage": errorMessage}
result['list'] = eq_info_list
result['total'] = total_size # 总记录数
result['size'] = len(eq_info_list) # 当前页的数量
result['pages'] = int(total_size) / int(values['pageSize']) # 总页数
if int(total_size) % int(values['pageSize']) != 0:
result['pages'] = result['pages'] + 1
result['pageNum'] = values['pageNum'] # 当前页
result['pageSize'] = values['pageSize'] # 每页的数量
return result