class UserInfoService(object):
"""Class FieldsInfoService"""
def __init__(self):
self._db_user_info_service = DBService("DBUserInfo")
self._db_dept_info_service = DBService("DBDeptInfo")
def find_user_info(self, attribute, content):
user_info = self._db_user_info_service.db_find_one_by_attribute(
attribute, content)
if user_info is None:
obj = {
"user_id": 10,
"user_name": content,
"dept_id": 25,
"role": "visitor"
}
result = self.obj_2_json(obj)
else:
result = json.dumps(user_info,
default=DBUserInfo.obj_2_json,
sort_keys=False,
ensure_ascii=False,
indent=4)
return result
def find_his_dept_info(self, user_name):
try:
dept_id = self._db_user_info_service.db_find_one_by_attribute(
"user_name", user_name).dept_id
dept_info = self._db_dept_info_service.db_find_one_by_attribute(
"dept_id", dept_id)
result = json.dumps(dept_info,
default=DBDeptInfo.obj_2_json_2,
sort_keys=False,
ensure_ascii=False,
indent=4)
except Exception as e:
raise e
else:
return result
@staticmethod
def obj_2_json(obj):
return json.dumps(obj, ensure_ascii=False)
class DeptInfoService(object):
"""Class FieldsInfoService"""
def __init__(self):
self._db_dept_info_service = DBService("DBDeptInfo")
def find_branch_list(self, branch_name):
branch_dept_list = self._db_dept_info_service.db_find_list_by_attribute(
"dept_type", 2)
result = json.dumps(branch_dept_list,
default=DBDeptInfo.obj_2_json,
sort_keys=False,
ensure_ascii=False,
indent=4)
return result
def find_branch_kv_list(self, branch_name):
branch_dept_list = self._db_dept_info_service.db_find_list_by_attribute(
"dept_type", 2)
result = []
for branch in branch_dept_list:
result.append({"dept_name": branch.dept_name})
return result
def find_dept_info(self, dept_id):
try:
dept_info = self._db_dept_info_service.db_find_one_by_attribute(
"dept_id", dept_id)
result = json.dumps(dept_info,
default=DBDeptInfo.obj_2_json_2,
sort_keys=False,
ensure_ascii=False,
indent=4)
except Exception as e:
raise e
else:
return result
@staticmethod
def obj_2_json(obj):
return json.dumps(obj, ensure_ascii=False)
class LogService(object):
"""Class Log"""
def __init__(self):
self._db_log_service = DBService("DBLog")
def submit_log_json(self, request_json):
log = LogService.read_json(request_json)
result = self._db_log_service.db_save(log)
self._db_log_service.db_commit()
return result
def submit_log(self, user_name, page, resource, method, content):
# if not isinstance(content, str):
# content_obj = json.loads(content)
# else:
if page is None:
page = "Server"
content_str = str(content)
log = Log(user_name, page, resource, method, content_str)
result = self._db_log_service.db_save(log)
self._db_log_service.db_commit()
return result
@staticmethod
def read_json(json_raw):
if isinstance(json_raw, str):
json_obj = json.loads(json_raw)
else:
json_obj = json_raw
user_name = json_obj['user_name']
page = json_obj['page']
resource = json_obj['resource']
method = json_obj['method']
content = json_obj['content']
log = Log(user_name, page, resource, method, content)
return log
def __init__(self):
self._db_performance_service = DBService("DBPerformance")
self._db_dept_info_service = DBService("DBDeptInfo")
self._db_fields_info_service = DBService("DBFieldsInfo")
class PerformanceService(object):
def __init__(self):
self._db_performance_service = DBService("DBPerformance")
self._db_dept_info_service = DBService("DBDeptInfo")
self._db_fields_info_service = DBService("DBFieldsInfo")
"""Class Performance"""
def submit_performance(self, request_json):
performance = PerformanceService.read_json(request_json)
check = self.check_exist(performance.get_date, performance.get_dept_id)
if check is not None:
logging.info("该条记录已存在！")
result = self._db_performance_service.db_update(performance, check)
else:
result = self._db_performance_service.db_save(performance)
if result:
self._db_performance_service.db_commit()
return True
else:
return False
def check_exist(self, date, dept_id):
exist = None
result = self._db_performance_service.db_find_column_by_attribute_list(
["date", "dept_id"], [date, dept_id], "id")
if len(result) > 0:
exist = result[0].id
return exist
@staticmethod
def read_json(json_raw):
"""很重要的函数，用来整理json"""
if isinstance(json_raw, str):
json_obj = json.loads(json_raw)
else:
json_obj = json_raw
dept_id = json_obj['dept_id']
date = json_obj['date']
submit_user = json_obj['submit_user']
if 'comments' in json_obj:
comments = json_obj['comments']
elif 'comments' in json_obj['extra_fields']:
comments = json_obj['extra_fields']['comments']
else:
comments = ""
extra_fields = PerformanceService.clean_extra_fields(
json_obj['extra_fields'])
performance = Performance(dept_id, date, submit_user, comments,
extra_fields)
return performance
def pre_check_submission(self, date):
branch_dept_list = self._db_dept_info_service.db_find_list_by_attribute(
"dept_type", 2)
performance_list = self._db_performance_service.db_find_list_by_attribute(
"date", date)
done_list, submission_list, unsubmission_list = [], [], []
dept_name_list = dict()
for dept in branch_dept_list:
dept_name_list[dept.dept_id] = dept.dept_name
for performance in performance_list:
done_list.append({
"dept_id": performance.dept_id,
"submit_user": performance.submit_user
})
for item in dept_name_list:
c = False
for i, p in enumerate(performance_list):
if item == p.dept_id:
submission_list.append({
"dept_name":
dept_name_list[p.dept_id],
"submit_user":
p.submit_user
})
c = True
break
if not c:
unsubmission_list.append({"dept_name": dept_name_list[item]})
return submission_list, unsubmission_list
def check_submission(self, date):
submission_list, unsubmission_list = self.pre_check_submission(date)
json_data = {
"date": date.strftime("%Y-%m-%d"),
"submission_list": submission_list,
"unsubmission_list": unsubmission_list
}
result = self.obj_2_json(json_data)
return result
@staticmethod
def obj_2_json(obj):
return json.dumps(obj, ensure_ascii=False)
def display(self, date, dept_name):
performance = {"submit_user": "******"}
obj = self.find_performance_by_date(date, "dept_name", dept_name)
if len(obj) > 0:
d = obj[0]
performance = {
"submit_user": d.submit_user,
"date": str(d.date),
"submit_time": str(d.submit_time),
"comments": d.comments,
"extra_fields": self.rewrite_extra_fields(d.extra_fields)
}
result = self.obj_2_json(performance)
return result
def find(self, date, dept_id):
performance = {"submit_user": "", "submit_time": ""}
obj = self.find_performance_by_date(date, "dept_id", dept_id)
if len(obj) > 0:
d = obj[0]
performance = {
"submit_user": d.submit_user,
"submit_time": str(d.submit_time)
}
result = self.obj_2_json(performance)
return result
def find_performance_by_date(self, date, attribute, content):
dept_id = self._db_dept_info_service.db_find_column_by_attribute(
attribute, content, "dept_id")[0].dept_id
db = self._db_performance_service.db_find_list_by_attribute_list(
["date", "dept_id"], [date, dept_id])
return db
def find_performance_by_range(self, date_begin, data_end, attribute,
content):
dept_id = self._db_dept_info_service.db_find_column_by_attribute(
attribute, content, "dept_id")[0].dept_id
db = self._db_performance_service.db_find_list_by_attribute_list2(
["date", "dept_id"], [[date_begin, data_end], dept_id])
return db
def rewrite_extra_fields(self, extra_fields):
extra_fields_full = []
fields_list = self._db_fields_info_service.db_find_list_by_attribute_list_order_by(
["business", "status"], ["corporate", "1"], "order_index")
name_list = {}
unit_list = {}
order_list = []
for item in fields_list:
name_list[item.field_id] = item.field_name
unit_list[item.field_id] = item.field_unit
order_list.append(item.field_id)
for field_id in order_list:
if field_id in extra_fields:
extra_fields_full.append({
"field_name": name_list[field_id],
"field_value": extra_fields[field_id],
"field_unit": unit_list[field_id]
})
return extra_fields_full
@staticmethod
def clean_extra_fields(extra_fields):
"""根据个性化需求，整理附加字段"""
new_extra_fields = dict()
for field_key in extra_fields:
value = extra_fields[field_key]
new_value = ""
if field_key == "comments":
continue
elif field_key == "field_4":
if len(value) == 1:
new_value = "无"
else:
new_value = value
elif field_key == "field_6":
if value == "0":
new_value = "无"
else:
new_value = value.replace("减少",
"-").replace("增加", "+").replace(
"万", "").replace("万元", "")
if value[0].isdigit():
new_value = "+" + new_value
# new_value = new_value + "万"
else:
new_value = value
new_extra_fields[field_key] = new_value
return new_extra_fields
def __init__(self):
self._db_log_service = DBService("DBLog")
def __init__(self):
self._db_fields_info_service = DBService("DBFieldsInfo")
class StatisticsService(object):
"""Class StatisticsService"""
def __init__(self):
self._db_fields_info_service = DBService("DBFieldsInfo")
def get_data_from_db(self, date_begin, date_end, mode):
title_line, data, total_line = [], [], ["汇总", ""]
performance_service = PerformanceService()
dept_info_service = DeptInfoService()
field_id_list, field_summable_list = [], []
type_list = ["1", "1"]
unsubmission_list = []
fields_list, branch_list = [], []
if mode == "daily":
fields_list = self._db_fields_info_service.db_find_list_by_attribute_list_order_by(
["business", "status"], ["corporate", "1"], "order_index")
branch_list, unsubmission_list = performance_service.pre_check_submission(
date_begin)
elif mode == "range":
fields_list = self._db_fields_info_service.db_find_list_by_attribute_list_order_by(
["business", "status", "statistics"], ["corporate", "1", "1"],
"order_index")
branch_list = dept_info_service.find_branch_kv_list("wangjing")
elif mode == "detail":
fields_list = self._db_fields_info_service.db_find_list_by_attribute_list_order_by(
["business", "status"], ["corporate", "1"], "order_index")
branch_list = dept_info_service.find_branch_kv_list("wangjing")
# 生成title
title_line.append("序号")
title_line.append("网点")
if mode == "detail":
title_line.append("日期")
type_list.append("1")
total_line.append("")
elif mode == "range":
title_line.append("报送天数")
type_list.append("1")
total_line.append("")
for field in fields_list:
type_list.append(field.field_type)
if field.field_type == "int":
title_line.append("%s(%s)" %
(field.field_name, field.field_unit))
elif field.field_type == "float":
title_line.append("%s(%s)" %
(field.field_name, field.field_unit))
else:
title_line.append("%s" % field.field_name)
if field.statistics == 1:
field_summable_list.append(field.field_id)
total_line.append(0)
else:
total_line.append("")
field_id_list.append(field.field_id)
if (mode == "detail") | (mode == "daily"):
title_line.append("报送人")
logger.info("Title行生成完毕。")
# 生成data
row = []
line_num = 1
if mode == "daily":
# 简单汇总当日所有网点
for i, branch in enumerate(branch_list):
"""第一层循环，以网点名称生成行"""
performance_list = performance_service.find_performance_by_date(
date_begin, "dept_name", branch["dept_name"])
if len(performance_list) == 1:
row = [i + 1, branch["dept_name"]]
line_num += 1
performance = performance_list[0]
extra_fields = performance.extra_fields
for j, a_f_id in enumerate(field_id_list):
"""第二层循环，以可用字段生成列"""
if a_f_id in extra_fields:
row.append(extra_fields[a_f_id])
if a_f_id in field_summable_list:
if extra_fields[a_f_id] != 0:
if type_list[j + 2] == "int":
total_line[j + 2] += int(
extra_fields[a_f_id])
elif type_list[j + 2] == "float":
total_line[j + 2] += float(
extra_fields[a_f_id])
else:
pass
else:
row.append("")
row.append(branch["submit_user"])
else:
pass
data.append(row)
type_list.append("1")
for i, branch in enumerate(unsubmission_list):
row = [i + line_num, branch["dept_name"]]
temp_row = ["" for x in range(0, len(field_id_list))]
temp_row.append("")
row.extend(temp_row)
data.append(row)
total_line.append("")
else:
if mode == "range":
# 将某时间段内，每个网点业绩求和汇总
for i, branch in enumerate(branch_list):
"""第一层循环，以网点名称生成行"""
performance_list = performance_service.find_performance_by_range(
date_begin, date_end, "dept_name", branch["dept_name"])
row = [i + 1, branch["dept_name"], len(performance_list)]
if len(performance_list) > 0:
temp_list = [
0 for x in range(0, len(field_summable_list))
]
for k, performance in enumerate(performance_list):
extra_fields = performance.extra_fields
for j, a_f_id in enumerate(field_id_list):
"""第二层循环，以可用字段生成列"""
if a_f_id in extra_fields:
if a_f_id in field_summable_list:
if extra_fields[a_f_id] != 0:
if type_list[j + 3] == "int":
temp_list[j] += int(
extra_fields[a_f_id])
total_line[j + 3] += int(
extra_fields[a_f_id])
elif type_list[j + 3] == "float":
temp_list[j] += float(
extra_fields[a_f_id])
total_line[j + 3] += float(
extra_fields[a_f_id])
else:
pass
else:
pass
# row.append("" for x in range(0, len(field_summable_list)))
row.extend(temp_list)
else:
temp_list = [
"" for x in range(0, len(field_summable_list))
]
row.extend(temp_list)
data.append(row)
type_list.append("1")
elif mode == "detail":
# 将某时间段内，每个网点业绩列出来
for i, branch in enumerate(branch_list):
"""第一层循环，以网点名称生成行"""
performance_list = performance_service.find_performance_by_range(
date_begin, date_end, "dept_name", branch["dept_name"])
if len(performance_list) > 0:
for k, performance in enumerate(performance_list):
row = [
line_num, branch["dept_name"],
performance.date.strftime("%Y-%m-%d")
]
line_num += 1
extra_fields = performance.extra_fields
for j, a_f_id in enumerate(field_id_list):
"""第二层循环，以可用字段生成列"""
if a_f_id in extra_fields:
row.append(extra_fields[a_f_id])
if a_f_id in field_summable_list:
if extra_fields[a_f_id] != 0:
if type_list[j + 3] == "int":
total_line[j + 3] += int(
extra_fields[a_f_id])
elif type_list[j + 3] == "float":
total_line[j + 3] += float(
extra_fields[a_f_id])
else:
pass
else:
row.append("")
row.append(performance.submit_user)
data.append(row)
else:
pass
total_line.append("")
type_list.append("1")
logger.info("Total行生成完毕。")
return title_line, data, total_line, type_list
@staticmethod
def create_files(date_begin, date_end, mode):
try:
title_line, data, total_line, type_list = StatisticsService(
).get_data_from_db(date_begin, date_end, mode)
logger.info("统计数据Data构造成功")
if mode == "daily":
subject = "{} 网点报送汇总".format(date_begin)
xls_file_name = os.path.join(
GLOBAL_CONFIG.get_field("Excel", "xls_dir"),
date_begin) + ".xls"
html_file_name = os.path.join(
GLOBAL_CONFIG.get_field("Html", "html_dir"),
date_begin) + ".html"
elif mode == "range":
subject = "{} ~ {} 网点报送汇总".format(date_begin, date_end)
xls_file_name = os.path.join(
GLOBAL_CONFIG.get_field("Excel", "xls_dir"),
"{}_{}_汇总".format(date_begin, date_end)) + ".xls"
html_file_name = os.path.join(
GLOBAL_CONFIG.get_field("Html", "html_dir"),
"{}_{}_汇总".format(date_begin, date_end)) + ".html"
else:
subject = "{} ~ {} 网点报送明细".format(date_begin, date_end)
xls_file_name = os.path.join(
GLOBAL_CONFIG.get_field("Excel", "xls_dir"),
"{}_{}_明细".format(date_begin, date_end)) + ".xls"
html_file_name = os.path.join(
GLOBAL_CONFIG.get_field("Html", "html_dir"),
"{}_{}_明细".format(date_begin, date_end)) + ".html"
# Html
html_style_list = HtmlService().get_style_list(mode)
html_content = HtmlService().data_to_html(subject, title_line,
data, total_line,
html_style_list)
HtmlService().html_2_file(html_content, html_file_name)
logger.info("html内容构造成功：" + html_file_name)
# xls
XlsService().data_to_xls(xls_file_name, title_line, data,
total_line, type_list, mode)
logger.info("xls内容构造成功：" + xls_file_name)
return True
except Exception as e:
logger.error("Error: 内容构造失败:")
logger.error(e)
return False
def __init__(self):
self._db_dept_info_service = DBService("DBDeptInfo")
def __init__(self):
self._db_fields_info_service = DBService("DBFieldsInfo")
self._from_addr = ""
self._password = ""
self._to_addr = []
self._smtp_server = ""
class FieldsInfoService(object):
"""Class Performance"""
def __init__(self):
self._db_fields_info_service = DBService("DBFieldsInfo")
def find_fields_list(self):
fields_list = self._db_fields_info_service.db_find_list_by_attribute_order_by(
"business", "corporate", "order_index")
result = json.dumps(fields_list,
default=DBFieldsInfo.obj_2_json,
sort_keys=False,
ensure_ascii=False,
indent=4)
return result
def find_available_fields_name(self):
fields_list = self._db_fields_info_service.db_find_list_by_attribute_list_order_by(
["business", "status"], ["corporate", "1"], "order_index")
result = json.dumps(fields_list,
default=DBFieldsInfo.obj_2_json_simple,
sort_keys=False,
ensure_ascii=False,
indent=4)
return result
def update_field(self, field_id, request_json):
old_fields = self._db_fields_info_service.db_find_one_by_attribute(
"field_id", field_id)
setattr(old_fields, request_json["update_k"], request_json["update_v"])
self._db_fields_info_service.db_update_db(old_fields)
return True
def create_field(self, request_json):
field = self.fill_full_field(request_json)
result = self._db_fields_info_service.db_save(field)
if result:
self._db_fields_info_service.db_commit()
return True
else:
return False
def sort_field(self, request_json):
if isinstance(request_json, str):
new_order = json.loads(request_json)
else:
new_order = request_json
try:
for item in new_order["new_order"]:
field_id = item["id"]
new = item["new_order"]
field = self._db_fields_info_service.db_find_one_by_attribute(
"id", field_id)
field.order_index = new
result = self._db_fields_info_service.db_update(
field, field_id)
if result:
self._db_fields_info_service.db_commit()
except Exception as e:
logging.error(e)
return False
else:
self._db_fields_info_service.db_commit()
return True
def check_exist(self, new_order):
exist = None
result = self._db_fields_info_service.db_find_column_by_attribute(
"new_order", new_order, id)
if len(result) > 0:
exist = result[0].id
return exist
def fill_full_field(self, json_raw):
if isinstance(json_raw, str):
json_obj = json.loads(json_raw)
else:
json_obj = json_raw
max_id_field = self._db_fields_info_service.db_find_max_id()
field_id = max_id_field.field_id.split('_')[0] + "_" + str(
int(max_id_field.field_id.split('_')[1]) + 1)
field_name = json_obj['field_name']
field_type = json_obj['field_type']
field_unit = json_obj['field_unit']
max_order_field = self._db_fields_info_service.db_find_max_order()
order_index = max_order_field.order_index + 1
field = FieldsInfo(field_id, field_name, "corporate", field_type,
field_unit, 1, order_index, 1)
return field