def __init__(self, textBrowser, path):
self.textBrowser = textBrowser
msg = u"开始初始化读取timesheet."
LOG.info(msg)
self.textBrowser.append(msg)
self.config = Config()
self.project_query_report_path = self.config.get_project_query_report_path(
)
self.total_cost = None
self.path = path
self.sheet = None
self.first_row_values = None
self.nrows = None
self.ncols = None
self.textBrowser = textBrowser
self.all_projectid_list = list()
self.projects_dict = dict()
self.title_index_dict = dict()
self.exclude_project_id = self.__get_exclude_project_id()
# 初始化表格函数
self.__init_sheet_obj()
self.textBrowser.append(u"初始化TimeSheet报表完成")
#
self.weight = WeightData()
self.project_query_obj = QueryProject(textBrowser,
self.project_query_report_path)
self.is_right_ratio = False
self.project_ratio_list = self.__calculation()
def __init__(self, textBrowser, path):
self.textBrowser = textBrowser
self.conf = Config()
self.excel = ExcelRead(self.textBrowser, path)
self.sheet = self.excel.get_sheet_by_keywords(
[PROJECT_ID, SS_COST_CETER])
self.index_of_title = self.excel.get_col_index_of_title()
self.nrows = self.sheet.nrows
self.textBrowser.append(u"初始化项目查询报表完成")
def __init__(self, textBrowser, path, sheet_name):
self.conf = Config()
self.path = path
self.textBrowser = textBrowser
self.ROW = self.conf.get_cost_center_name_list()
self.COL = self.conf.get_cost_class_list()
try:
self.workbook = xlwt.Workbook(encoding='utf-8')
self.worksheet = self.workbook.add_sheet(
sheet_name, cell_overwrite_ok=True)
self.inital_excel()
except Exception as e:
msg = u"打开excel文件失败，文件名称：%s, 原因：%s" % (self.path, e)
raise Exception(msg)
def __init__(self):
self.config = Config()
self.timesheet_report_path = self.config.get_timesheet_report_path()
self.profit_report_path = self.config.get_profit_report_path()
self.project_query_report_path = self.config.get_project_query_report_path(
)
self.bc_report_path = self.config.get_bc_report_path()
self.bc_sheet_name = self.config.get_bc_sheet_name()
self.output_path = self.config.get_output_path()
self.textBrowser = self.config.get_textBrowser()
#time_sheet
self.monthly_obj = MonthlyDataExcel(self.textBrowser,
self.timesheet_report_path)
self.project_ratio_datalist = self.monthly_obj.get_project_ratio_datalist(
)
#利润表
self.profit_obj = ProfitData(self.textBrowser, self.profit_report_path)
#项目综合查询
self.project_query_obj = QueryProject(self.textBrowser,
self.project_query_report_path)
self.weight_data_dict = dict()
self.weight_data_dict = WeightData().get_weight_data_dict()
#BC 报表
self.bc_report_obj = BCReport(self.textBrowser, self.bc_report_path)
self.bc_report_obj.init_monthly_table(self.bc_sheet_name)
self.bc_row_title_index = self.bc_report_obj.get_row_title_index()
self.bc_col_title_index = self.bc_report_obj.get_col_title_index()
#结果表
self.write_obj = WritingExcel(self.textBrowser, self.output_path,
self.bc_sheet_name)
# 初始化成本中心对象，初始化所有成本中心的各项cost
self.cost_center_obj = CostCenter()
self.__init_cost_center()
# 通过比率计算后的待更新的成本中心数据
self.new_cost_center_obj = CostCenter()
def __init__(self):
if not WeightData.instance_flag:
self.config = Config()
self.textBrowser = self.config.get_textBrowser()
self.sheet = None
self.path = self.config.get_weight_path()
if self.path:
excel = ExcelRead(self.textBrowser, self.path)
sheet_name = self.get_sheet_name()
self.sheet = excel.get_sheet_by_name(sheet_name)
self.title_index_dict = excel.get_col_index_of_title()
self.cost_center_name_list = self.get_cost_center_name()
self.weight_data_dict = self._get_weight_data_dict()
WeightData.instance_flag = True
else: # 权重路径未设置的情况下，初始化为空
self.weight_data_dict = dict()
WeightData.instance_flag = True
def __init__(self, textBrowser, path):
self.textBrowser = textBrowser
msg = u"开始初始化读取利润表"
self.textBrowser.append(msg)
LOG.info(msg)
self.conf = Config()
excel = ExcelRead(self.textBrowser, path)
sheet_name = self.__get_sheet_name()
self.sheet = excel.get_sheet_by_name(sheet_name)
self.index_of_title = excel.get_col_index_of_title()
excel.clear()
self.rowsn = self.sheet.nrows
self.colsn = self.sheet.ncols
self.bc_template_index = self.__get_index(u"BC模板分类")
self.cost_center_index = self.__get_index(u"成本中心")
self.project_id_index = self.__get_index(u"外部订单号")
self.money_index = self.__get_index(u"公司货币金额")
self.profit_dict = dict()
self.all_row_datalist = self.__get_all_row_datalist()
self.textBrowser.append(u"初始化利润报表完成")
class WritingExcel(object):
def __init__(self, textBrowser, path, sheet_name):
self.conf = Config()
self.path = path
self.textBrowser = textBrowser
self.ROW = self.conf.get_cost_center_name_list()
self.COL = self.conf.get_cost_class_list()
try:
self.workbook = xlwt.Workbook(encoding='utf-8')
self.worksheet = self.workbook.add_sheet(
sheet_name, cell_overwrite_ok=True)
self.inital_excel()
except Exception as e:
msg = u"打开excel文件失败，文件名称：%s, 原因：%s" % (self.path, e)
raise Exception(msg)
def inital_excel(self):
style = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
style.font = font
for j in range(len(self.COL)):
self.worksheet.write(j + 1, 0, self.COL[j], style)
for i in range(len(self.ROW)):
self.worksheet.write(0, i + 1, self.ROW[i], style)
def save(self):
self.workbook.save(self.path)
def write(self, row_name, col_name, value):
# 由于表格前面有空格，因此要+1
x = self.ROW.index(row_name) + 1
y = self.COL.index(col_name) + 1
# 由于行列索引号和实际是反的，因此要倒换x,y的顺序
self.worksheet.write(y, x, value)
class QueryProject(object):
"""
项目综合查询 表 处理类
"""
def __init__(self, textBrowser, path):
self.textBrowser = textBrowser
self.conf = Config()
self.excel = ExcelRead(self.textBrowser, path)
self.sheet = self.excel.get_sheet_by_keywords(
[PROJECT_ID, SS_COST_CETER])
self.index_of_title = self.excel.get_col_index_of_title()
self.nrows = self.sheet.nrows
self.textBrowser.append(u"初始化项目查询报表完成")
def get_cost_ceter_name(self, project_id):
"""
通过项目ID获取项目对应的成本中心
:param project_id: 项目ID
:return: 成本中心
"""
cost_ceter_name_index = self.index_of_title.get(SS_COST_CETER)
for i in range(self.nrows):
rows_data = self.sheet.row_values(i)
if project_id in rows_data:
return self._ceter_name(project_id,
rows_data[cost_ceter_name_index])
def _ceter_name(self, project_id, name):
"""
根据筛选出的实施成本中心名称，获得对应成本中心名称
:param name:实施成本中心名称
:return:成本中心名称
"""
re_dict = self.conf.get_cost_center_re_dict()
for ceter_name, re_pattern in re_dict.items():
if re.findall(re_pattern, name):
return ceter_name
msg = u"未找到对应的成本中心：%s，project id: %s" % (name, project_id)
self.textBrowser.append(msg)
return None
class MonthlyDataExcel(object):
""" 该Excel sheet页名称不确定，sheet 数量不确定，因此通过关键字来匹配，
包含”费率，成本“的页签 为目标页签
"""
def __init__(self, textBrowser, path):
self.textBrowser = textBrowser
msg = u"开始初始化读取timesheet."
LOG.info(msg)
self.textBrowser.append(msg)
self.config = Config()
self.project_query_report_path = self.config.get_project_query_report_path(
)
self.total_cost = None
self.path = path
self.sheet = None
self.first_row_values = None
self.nrows = None
self.ncols = None
self.textBrowser = textBrowser
self.all_projectid_list = list()
self.projects_dict = dict()
self.title_index_dict = dict()
self.exclude_project_id = self.__get_exclude_project_id()
# 初始化表格函数
self.__init_sheet_obj()
self.textBrowser.append(u"初始化TimeSheet报表完成")
#
self.weight = WeightData()
self.project_query_obj = QueryProject(textBrowser,
self.project_query_report_path)
self.is_right_ratio = False
self.project_ratio_list = self.__calculation()
def __init_sheet_obj(self):
"""
获取表格数据
:return:
"""
excel = ExcelRead(self.textBrowser, self.path)
self.sheet = excel.get_sheet_by_keywords([u'费率', u'成本'])
excel.clear()
# 首行所有数据
self.title_index_dict = excel.get_col_index_of_title()
self.nrows = self.sheet.nrows
self.ncols = self.sheet.ncols
def __get_all_projects_list(self):
"""
获取所有项目编号的list
:return: 所有项目的set
"""
if u"项目编号" not in self.title_index_dict:
msg = u"未找到项目编号列"
self.textBrowser.append(msg)
raise Exception(msg)
index_of_projectid_col_num = self.title_index_dict.get(u"项目编号")
# 所有项目编号数据
self.all_projectid_list = self.__get_all_project_id_list(
index_of_projectid_col_num)
def __get_all_project_id_list(self, index_of_projectid_col_num):
col_list = self.sheet.col_values(index_of_projectid_col_num)
# 去掉第一行Title行
col_list.pop(0)
all_projectid_list = set(col_list)
if not all_projectid_list:
msg = u"获取所有的项目编号为空"
self.textBrowser.append(msg)
raise Exception(msg)
return all_projectid_list
def __current_project_data(self, project_id, cost_ceter_index, cost_index,
item_dict):
"""
通过项目编号获取当前项目关联 所有成本中心 数据行列表
:param project_id: 项目ID
:param cost_ceter_index: 项目ID
:param cost_index: 项目ID
:return: 项目数据列表
"""
for i in range(self.nrows):
row_value = self.sheet.row_values(i)
if project_id in row_value:
self.__find_data_in_row(cost_ceter_index, cost_index,
row_value, item_dict)
def get_project_ratio_datalist(self):
"""
获取所有项目数据，项目id -key，项目关联数据行 -value
:return: 项目数据字典
"""
return self.project_ratio_list
def __filter_project(self):
if not self.all_projectid_list:
self.__get_all_projects_list()
# 成本中心列 的index值
cost_ceter_index = self.title_index_dict[u"成本中心"]
# 成本列 的index值
cost_index = self.title_index_dict[u"成本"]
error_project_id = list()
filter_project_id = list()
for project_id in self.all_projectid_list:
# 排除项目
if self.__is_exclude_project_id(project_id):
filter_project_id.append(project_id)
continue
# 当前项目所有成本中心对应的成本数据 累加字典，{成都企业IT部：1000000，杭州企业IT部：200000，...}
item = dict()
self.__current_project_data(project_id, cost_ceter_index,
cost_index, item)
if not item:
continue
# 剔除只有一个成本中心，且为主成本中心的项目
# 从项目查询中去查询主要成本中心
main_center_name = self.project_query_obj.get_cost_ceter_name(
project_id)
if main_center_name is None:
error_project_id.append(project_id)
continue
# 比较当前项目只有一个成本中心，且成本中心和主要成本中心相等则不纳入计算
if len(item.keys()) == 1 and item.keys()[0] == main_center_name:
continue
# 计算获得满足条件的项目
self.projects_dict[project_id] = item
if filter_project_id:
msg = u"过滤掉的项目id：%s" % filter_project_id
LOG.info(msg)
self.textBrowser.append(msg)
if error_project_id:
msg = u"存在脏数据，项目id: %s 在<项目综合查询表>中不存在，请确认项目月度timesheet报表中的项目ID" \
u"在项目综合查询中是否存在" % error_project_id
self.textBrowser.append(msg)
LOG.info(msg)
def __calculation(self):
"""
获取project 对象list
:param
:return: [project1,project2,project3...}
"""
self.__filter_project()
project_list = list()
for project_id, cost_dict in self.projects_dict.items():
#创建一个project对象用于保存project_id和其占比
project = Project()
current_project_weight = self.__get_weight(project_id)
if current_project_weight:
weight_ratio_dict = self.__get_ratio_with_weight(
project_id, current_project_weight, cost_dict)
ratio_dict = self.__get_ratio_without_weight(
project_id, cost_dict)
project.set_project_id(project_id)
project.set_ratio({
"ratio": ratio_dict,
"weight_ratio": weight_ratio_dict
})
else:
ratio_dict = self.__get_ratio_without_weight(
project_id, cost_dict)
project.set_project_id(project_id)
project.set_ratio({"ratio": ratio_dict})
LOG.info("project_id: %s, ratio_dict: %s" %
(project_id, ratio_dict))
project_list.append(project)
if self.is_right_ratio:
msg = u"表格数据异常，请整改后重试."
LOG.error(msg)
self.textBrowser.append(msg)
raise Exception(msg)
return project_list
def __find_data_in_row(self, cost_ceter_index, cost_index, row, item_dict):
""" 将同一个项目下的成本中心拆分{成本中心1：totalcost,成本中心2：totalcost, other:totalcost}
:param cost_ceter_index:
:param row:
:param item_dict:
:return:
"""
if not row:
raise Exception(u"该行数据为空")
re_dict = self.config.get_cost_center_re_dict()
flag = False
for k, r in re_dict.items():
if re.match(r, row[cost_ceter_index]):
value = item_dict.get(k, 0)
value += row[cost_index]
item_dict[k] = value
flag = True
break
if flag == False:
value = item_dict.get("other", 0)
value += row[cost_index]
item_dict["other"] = value
def __get_exclude_project_id(self):
result = self.config.get_value("Default",
"Exclude_Project_ID").split("|")
return [value for value in result if value]
def __is_exclude_project_id(self, project_id):
if not self.exclude_project_id:
return False
for r in self.exclude_project_id:
if re.findall(r, str(project_id)):
return True
return False
def __get_weight(self, project_id):
"""
获取当前项目id的权重数据字典
:param project_id:
:return:
"""
weight_data_dict = self.weight.get_weight_data_dict()
current_project_weight = weight_data_dict.get(project_id)
return current_project_weight
def __get_ratio_with_weight(self, project_id, current_project_weight,
cost_dict):
"""
获取有权重的 成本中心占比 比率 字典
:param project_id:当前项目ID
:param current_project_weight: 当前项目的权重字典
:param cost_dict: 当前项目的各成本中心总收入字典
:return:
"""
# 判断ts中的成本中心和权重表中成本中心是否一一对应
list1 = current_project_weight.keys()
list2 = cost_dict.keys()
if not Util.is_equal(list1, list2):
msg = u"项目ID：%s,TS中成本中心和权重表中成本中心无法对应!!!" % project_id
self.is_right_ratio = True
LOG.error(msg)
self.textBrowser.append(msg)
total_cost = 0
#获取当前项目总成本*权重的值，得到新的总成本
for cost_ceter, cost in cost_dict.items():
# 获取成本中心权重
weight_value = current_project_weight.get(cost_ceter)
if cost_ceter == "other":
weight_value = 1
if weight_value is None:
msg = u"项目ID：%s, 成本中心：%s 权重为空!!!" % (project_id, cost_ceter)
LOG.warn(msg)
self.textBrowser.append(msg)
weight_value = 1
self.is_right_ratio = True
total_cost += cost * weight_value
if total_cost == 0:
msg = u"项目ID：%s 总成本为0，请检查TS表格" % project_id
LOG.error(msg)
self.textBrowser.append(msg)
self.is_right_ratio = True
total_cost = -1
# 当前项目对应的各成本中心的总成本 * 当前成本中心权重值 / 当前项目新的总成本
weigth_ratio = dict()
for cost_ceter, cost in cost_dict.items():
weight_value = current_project_weight.get(cost_ceter, 0)
if weight_value is None:
weight_value = 1
weigth_ratio[cost_ceter] = cost * weight_value / total_cost
return weigth_ratio
def __get_ratio_without_weight(self, project_id, cost_dict):
"""
获取没有权重的成本中心占比 字典
:param project_id:
:param cost_dict:
:return:
"""
# 计算总成本，用于求比值的分母
total_cost = 0
for cost in cost_dict.values():
total_cost += cost
if total_cost == 0:
msg = u"项目ID：%s 总成本为0，请检查TS表格" % project_id
LOG.error(msg)
self.textBrowser.append(msg)
self.is_right_ratio = True
total_cost = -1
ratio = dict()
for cost_ceter, cost in cost_dict.items():
ratio[cost_ceter] = cost / total_cost
return ratio
class ProfitData(object):
"""
利润中心表
"""
def __init__(self, textBrowser, path):
self.textBrowser = textBrowser
msg = u"开始初始化读取利润表"
self.textBrowser.append(msg)
LOG.info(msg)
self.conf = Config()
excel = ExcelRead(self.textBrowser, path)
sheet_name = self.__get_sheet_name()
self.sheet = excel.get_sheet_by_name(sheet_name)
self.index_of_title = excel.get_col_index_of_title()
excel.clear()
self.rowsn = self.sheet.nrows
self.colsn = self.sheet.ncols
self.bc_template_index = self.__get_index(u"BC模板分类")
self.cost_center_index = self.__get_index(u"成本中心")
self.project_id_index = self.__get_index(u"外部订单号")
self.money_index = self.__get_index(u"公司货币金额")
self.profit_dict = dict()
self.all_row_datalist = self.__get_all_row_datalist()
self.textBrowser.append(u"初始化利润报表完成")
def __get_all_row_datalist(self):
projects_datalist = []
for i in range(self.rowsn):
try:
row = self.sheet.row_values(i)
projects_datalist.append(row)
except Exception as e:
msg = "获取行内容失败行号:%d, 原因：%s" % (i, e)
LOG.info(msg)
self.textBrowser.append(str(msg))
raise Exception
return projects_datalist
def get_project_costitem_dict(self, project_id):
if not project_id:
raise Exception("proejct id 为空")
current_profit = dict()
# 获取分类字典
cost_class_list = self.conf.get_cost_class_list()
for row in self.all_row_datalist:
if project_id != row[self.project_id_index]:
continue
key = row[self.bc_template_index]
if key in cost_class_list:
try:
value = current_profit.get(key, 0)
value += row[self.money_index]
current_profit[key] = value
except Exception as e:
msg = "获取行数据，失败原因：%s，行：%s,列标题%e" % (e, row, key)
LOG.error(msg)
raise Exception
return current_profit
def __get_index(self, title):
index = self.index_of_title.get(title)
if not index:
msg = "利润表中不存在:%s列，请检查利润表中标题行" % title
LOG.error(msg)
self.textBrowser.append(msg)
raise Exception
msg = u"获取利润行首：%s 所在索引号：%d" % (title, index)
LOG.info(msg)
return index
def __get_sheet_name(self):
return self.conf.get_value("SHEET_NAME", "profit_sheet_name")
class Caculating(object):
def __init__(self):
self.config = Config()
self.timesheet_report_path = self.config.get_timesheet_report_path()
self.profit_report_path = self.config.get_profit_report_path()
self.project_query_report_path = self.config.get_project_query_report_path(
)
self.bc_report_path = self.config.get_bc_report_path()
self.bc_sheet_name = self.config.get_bc_sheet_name()
self.output_path = self.config.get_output_path()
self.textBrowser = self.config.get_textBrowser()
#time_sheet
self.monthly_obj = MonthlyDataExcel(self.textBrowser,
self.timesheet_report_path)
self.project_ratio_datalist = self.monthly_obj.get_project_ratio_datalist(
)
#利润表
self.profit_obj = ProfitData(self.textBrowser, self.profit_report_path)
#项目综合查询
self.project_query_obj = QueryProject(self.textBrowser,
self.project_query_report_path)
self.weight_data_dict = dict()
self.weight_data_dict = WeightData().get_weight_data_dict()
#BC 报表
self.bc_report_obj = BCReport(self.textBrowser, self.bc_report_path)
self.bc_report_obj.init_monthly_table(self.bc_sheet_name)
self.bc_row_title_index = self.bc_report_obj.get_row_title_index()
self.bc_col_title_index = self.bc_report_obj.get_col_title_index()
#结果表
self.write_obj = WritingExcel(self.textBrowser, self.output_path,
self.bc_sheet_name)
# 初始化成本中心对象，初始化所有成本中心的各项cost
self.cost_center_obj = CostCenter()
self.__init_cost_center()
# 通过比率计算后的待更新的成本中心数据
self.new_cost_center_obj = CostCenter()
def update(self):
LOG.info(u"开始更新BC报表数据")
for project in self.project_ratio_datalist:
project_id = project.get_project_id()
# 需要拆分的部分项目
profit_data = self.profit_obj.get_project_costitem_dict(project_id)
# 该项目对应的正确成本中心
main_center_name = self.project_query_obj.get_cost_ceter_name(
project_id)
self.__caculation(project, profit_data, main_center_name)
self.__write_to_excel()
def _is_weight_error(self, weight, center_name):
"""
权重异常判断
:return:
"""
if center_name in weight:
del (weight[center_name])
if weight.keys():
msg = u"存在项目id: %s 权重表异常"
self.textBrowser.append(msg)
def __write_to_excel(self):
LOG.info(u"将更新后的BC报表数据保存")
zone_dict = self.config.get_zone_dict()
costitem_dict = self.config.get_cost_class_dict()
for zone, center_name in zone_dict.items():
for item, cost_item in costitem_dict.items():
value = getattr(self.cost_center_obj, zone + "_" + item)
self.write_obj.write(center_name, cost_item, value)
# 保存文件
self.write_obj.save()
def __get_x_index(self, name):
"""
获取横向坐标
:param name:
:return:
"""
return self.bc_row_title_index.get(name)
def __get_y_index(self, name):
"""
获取纵向坐标 字典
:param name:
:return:
"""
return self.bc_col_title_index.get(name)
def __init_cost_center(self):
"""
初始化所有的成本中心值
:return:
"""
zone_dict = self.config.get_zone_dict()
for zone_en, zone_zh in zone_dict.items():
try:
colx_index = self.__get_x_index(zone_zh)
if not colx_index:
LOG.info("BC报表中不存在'%s'列" % zone_zh)
zone_dict.pop(zone_en)
self.config.set_zone_dict(zone_dict)
continue
except Exception as e:
msg = u"未从表格中解析到%s所在列的列号, error: %s" % (zone_zh, str(e))
LOG.error(msg)
self.textBrowser.append(msg)
raise Exception(msg)
costitem_dict = self.config.get_cost_class_dict()
for costitem_en, costitem_zh in costitem_dict.items():
try:
value = 0
if costitem_zh == "Revenue":
costitem_zh = u"总收入"
if costitem_zh == "Sales Tax":
costitem_zh = u"税金"
rowx_index = self.__get_y_index(costitem_zh)
if rowx_index:
# 获取bc报表单元格值
value = self.bc_report_obj.get_monthly_cell_value(
rowx_index, colx_index)
if not value:
value = 0
# # 初始化对应成本中心，各项值
# if costitem_zh == "Revenue" or costitem_zh == u"总收入":
# value = abs(value)
setattr(self.cost_center_obj, zone_en + "_" + costitem_en,
value)
except Exception as err:
msg = u"总成本中心解析出错, 总成本中心：%s, 项目：%s, error: %s" % (
zone_zh, costitem_zh, str(err))
LOG.error(msg)
self.textBrowser.append(msg)
raise Exception(msg)
LOG.info(u"总成本中心数据初始化完毕。")
def __caculation(self, project, profit_data, master_costcenter_zh):
zone_dict = self.config.get_zone_dict()
costitem_dict = self.config.get_cost_class_dict()
master_zone_en = Util.get_zone_en(master_costcenter_zh, zone_dict)
ratio_dict = project.get_ratio().get("ratio")
weight_ratio_dict = project.get_ratio().get("weight_ratio")
for slave_csostcenter_zh, ratio in ratio_dict.items():
# 成本中心为other的不计算
if slave_csostcenter_zh == "other":
continue
if master_costcenter_zh == slave_csostcenter_zh:
# 遍历出的主成本中心时，不需要计算成本变化
continue
weight_ratio = 0
if weight_ratio_dict:
weight_ratio = weight_ratio_dict.get(slave_csostcenter_zh)
if not weight_ratio: # 当获取不到权重占比时，就采用一般占比
weight_ratio = ratio
slave_costcenter_en = Util.get_zone_en(slave_csostcenter_zh,
zone_dict)
for costitem, value in profit_data.items():
"""
如果有成本中心（x,y），总收入分别为A,B
主要成本中心为x
总金额占比：x:y = 0.4:0.6
权重占比为x:y=0.2:0.8
成本中心x 总收入和税金 x-0.8*a , 其他收入 x-0.6a
成本中心y 总收入和税金：B+0.8a, 其他收入 x+0.6a
"""
if costitem == "Revenue" or costitem == u"税金":
temp_value = value * weight_ratio
else:
temp_value = value * ratio
if costitem == "Revenue":
temp_value = 0 - temp_value
costitem_en = Util.get_costitem_en(costitem, costitem_dict)
# 主要成本中心默认的总成本
master_value = getattr(self.cost_center_obj,
master_zone_en + '_' + costitem_en)
# 次要成本中心默认值
slave_value = getattr(self.cost_center_obj,
slave_costcenter_en + '_' + costitem_en)
# 从主要成本中心扣除成本
new_master_value = master_value - temp_value
# 次要成本中心增加
new_slave_value = slave_value + temp_value
# 更新成本中心
setattr(self.cost_center_obj,
master_zone_en + '_' + costitem_en, new_master_value)
setattr(self.cost_center_obj,
slave_costcenter_en + '_' + costitem_en,
new_slave_value)
def __init__(self):
QWidget.__init__(self)
self.config = Config()
class Ui_Dialog(QWidget):
def __init__(self):
QWidget.__init__(self)
self.config = Config()
def setupUi(self, Dialog):
Dialog.setObjectName(_fromUtf8(u"财务报表统计"))
Dialog.resize(632, 466)
self.groupBox = QtGui.QGroupBox(Dialog)
self.groupBox.setGeometry(QtCore.QRect(0, 0, 553, 210))
self.groupBox.setObjectName(_fromUtf8("groupBox"))
self.formLayoutWidget = QtGui.QWidget(self.groupBox)
self.formLayoutWidget.setGeometry(QtCore.QRect(10, 20, 500, 191))
self.formLayoutWidget.setObjectName(_fromUtf8("formLayoutWidget"))
self.formLayout = QtGui.QFormLayout(self.formLayoutWidget)
self.formLayout.setFieldGrowthPolicy(
QtGui.QFormLayout.AllNonFixedFieldsGrow)
self.formLayout.setContentsMargins(-1, 0, -1, -1)
self.formLayout.setHorizontalSpacing(6)
self.formLayout.setObjectName(_fromUtf8("formLayout"))
self.pushButton = QtGui.QPushButton(Dialog)
self.pushButton.setGeometry(QtCore.QRect(555, 180, 75, 23))
self.pushButton.setObjectName(_fromUtf8("pushButton"))
self.pushButton.clicked.connect(self.run)
self.timesheetLabel = QtGui.QLabel(self.formLayoutWidget)
self.timesheetLabel.setObjectName(_fromUtf8("timesheetLabel"))
self.formLayout.setWidget(0, QtGui.QFormLayout.LabelRole,
self.timesheetLabel)
self.timesheetLineEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.timesheetLineEdit.setEnabled(True)
self.timesheetLineEdit.setObjectName(_fromUtf8("timesheetLineEdit"))
self.formLayout.setWidget(0, QtGui.QFormLayout.FieldRole,
self.timesheetLineEdit)
self.Li_run_zhong_xin_label = QtGui.QLabel(self.formLayoutWidget)
self.Li_run_zhong_xin_label.setObjectName(
_fromUtf8("Li_run_zhong_xin_label"))
self.formLayout.setWidget(1, QtGui.QFormLayout.LabelRole,
self.Li_run_zhong_xin_label)
self.Li_Run_ReportEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.Li_Run_ReportEdit.setObjectName(_fromUtf8("LineEdit"))
self.formLayout.setWidget(1, QtGui.QFormLayout.FieldRole,
self.Li_Run_ReportEdit)
self.proeject_query_label = QtGui.QLabel(self.formLayoutWidget)
self.proeject_query_label.setObjectName(
_fromUtf8("proeject_query_label"))
self.formLayout.setWidget(2, QtGui.QFormLayout.LabelRole,
self.proeject_query_label)
self.proeject_queryEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.proeject_queryEdit.setObjectName(_fromUtf8("LineEdit_2"))
self.formLayout.setWidget(2, QtGui.QFormLayout.FieldRole,
self.proeject_queryEdit)
self.bCLabel = QtGui.QLabel(self.formLayoutWidget)
self.bCLabel.setObjectName(_fromUtf8("bCLabel"))
self.formLayout.setWidget(3, QtGui.QFormLayout.LabelRole, self.bCLabel)
self.bCLineEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.bCLineEdit.setObjectName(_fromUtf8("bCLineEdit"))
self.formLayout.setWidget(3, QtGui.QFormLayout.FieldRole,
self.bCLineEdit)
self.formLayout_3 = QtGui.QFormLayout()
self.formLayout_3.setObjectName(_fromUtf8("formLayout_3"))
self.bCSheetLabel = QtGui.QLabel(self.formLayoutWidget)
self.bCSheetLabel.setObjectName(_fromUtf8("bCSheetLabel"))
self.formLayout_3.setWidget(0, QtGui.QFormLayout.LabelRole,
self.bCSheetLabel)
self.bCSheetLineEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.bCSheetLineEdit.setInputMask(_fromUtf8(""))
self.bCSheetLineEdit.setEchoMode(QtGui.QLineEdit.Normal)
self.bCSheetLineEdit.setAlignment(QtCore.Qt.AlignCenter)
self.bCSheetLineEdit.setObjectName(_fromUtf8("bCSheetLineEdit"))
self.formLayout_3.setWidget(0, QtGui.QFormLayout.FieldRole,
self.bCSheetLineEdit)
self.formLayout.setLayout(4, QtGui.QFormLayout.FieldRole,
self.formLayout_3)
self.weightLabel = QtGui.QLabel(self.formLayoutWidget)
self.weightLabel.setObjectName(_fromUtf8("weightLabel"))
self.formLayout.setWidget(5, QtGui.QFormLayout.LabelRole,
self.weightLabel)
self.weightEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.weightEdit.setObjectName(_fromUtf8("weight"))
self.formLayout.setWidget(5, QtGui.QFormLayout.FieldRole,
self.weightEdit)
self.formLayout.setLayout(6, QtGui.QFormLayout.FieldRole,
self.formLayout_3)
self.outputpathLabel = QtGui.QLabel(self.formLayoutWidget)
self.outputpathLabel.setObjectName(_fromUtf8("outputpathLabel"))
self.formLayout.setWidget(7, QtGui.QFormLayout.LabelRole,
self.outputpathLabel)
self.outputpathEdit = QtGui.QLineEdit(self.formLayoutWidget)
self.outputpathEdit.setObjectName(_fromUtf8("LineEdit_3"))
self.formLayout.setWidget(7, QtGui.QFormLayout.FieldRole,
self.outputpathEdit)
self.groupBox_2 = QtGui.QGroupBox(Dialog)
self.groupBox_2.setGeometry(QtCore.QRect(5, 210, 620, 250))
self.groupBox_2.setObjectName(_fromUtf8("groupBox_2"))
self.textBrowser = QtGui.QTextBrowser(self.groupBox_2)
self.textBrowser.setGeometry(QtCore.QRect(5, 14, 610, 232))
self.textBrowser.setObjectName(_fromUtf8("textBrowser"))
self.toolButton_time_sheet = QtGui.QToolButton(Dialog)
self.toolButton_time_sheet.setGeometry(QtCore.QRect(510, 19, 40, 20))
self.toolButton_time_sheet.setObjectName(
_fromUtf8("toolButton_time_sheet"))
self.toolButton_lirun = QtGui.QToolButton(Dialog)
self.toolButton_lirun.setGeometry(QtCore.QRect(510, 45, 40, 20))
self.toolButton_lirun.setObjectName(_fromUtf8("toolButton_lirun"))
self.toolButton_project_qurey = QtGui.QToolButton(Dialog)
self.toolButton_project_qurey.setGeometry(QtCore.QRect(
510, 74, 40, 20))
self.toolButton_project_qurey.setObjectName(
_fromUtf8("toolButton_project_qurey"))
self.toolButton_bc = QtGui.QToolButton(Dialog)
self.toolButton_bc.setGeometry(QtCore.QRect(510, 100, 40, 20))
self.toolButton_bc.setObjectName(_fromUtf8("toolButton_bc"))
self.toolButton_weight = QtGui.QToolButton(Dialog)
self.toolButton_weight.setGeometry(QtCore.QRect(510, 150, 40, 20))
self.toolButton_weight.setObjectName(_fromUtf8("toolButton_weight"))
#
self.toolButton_output = QtGui.QToolButton(Dialog)
self.toolButton_output.setGeometry(QtCore.QRect(510, 180, 40, 20))
self.toolButton_output.setObjectName(_fromUtf8("toolButton_output"))
self.connect(self.toolButton_time_sheet, SIGNAL("clicked()"),
lambda: self.brower_file(self.timesheetLineEdit))
self.connect(self.toolButton_lirun, SIGNAL("clicked()"),
lambda: self.brower_file(self.Li_Run_ReportEdit))
self.connect(self.toolButton_project_qurey, SIGNAL("clicked()"),
lambda: self.brower_file(self.proeject_queryEdit))
self.connect(self.toolButton_bc, SIGNAL("clicked()"),
lambda: self.brower_file(self.bCLineEdit))
self.connect(self.toolButton_weight, SIGNAL("clicked()"),
lambda: self.brower_file(self.weightEdit))
self.connect(self.toolButton_output, SIGNAL("clicked()"),
lambda: self.brower_dir(self.outputpathEdit))
self.retranslateUi(Dialog)
QtCore.QMetaObject.connectSlotsByName(Dialog)
def retranslateUi(self, Dialog):
Dialog.setWindowTitle(_translate("Dialog", "财务报表统计", None))
self.groupBox.setTitle(_translate("Dialog", "路径输入框", None))
self.timesheetLabel.setText(_translate("Dialog", "TimeSheet报表", None))
self.Li_run_zhong_xin_label.setText(
_translate("Dialog", "利润中心报表", None))
self.proeject_query_label.setText(
_translate("Dialog", "项目综合查询报表", None))
self.bCLabel.setText(_translate("Dialog", "BC报表", None))
self.bCSheetLabel.setText(
_translate("Dialog", "BC报表中的sheet页名称 ", None))
self.bCSheetLineEdit.setText(_translate("Dialog", "2019-07", None))
self.weightLabel.setText(_translate("Dialog", "权重表", None))
self.outputpathLabel.setText(_translate("Dialog", "汇总后输出路径", None))
self.groupBox_2.setTitle(_translate("Dialog", "输出", None))
self.pushButton.setText(_translate("Dialog", "Run", None))
self.toolButton_time_sheet.setText(_translate("Dialog", "...", None))
self.toolButton_lirun.setText(_translate("Dialog", "...", None))
self.toolButton_project_qurey.setText(_translate(
"Dialog", "...", None))
self.toolButton_bc.setText(_translate("Dialog", "...", None))
self.toolButton_weight.setText(_translate("Dialog", "...", None))
self.toolButton_output.setText(_translate("Dialog", "...", None))
def initial_config(self):
self.bc_sheet_name = unicode(self.bCSheetLineEdit.text())
self.bc_report_path = unicode(self.bCLineEdit.text())
self.li_run_report_path = unicode(self.Li_Run_ReportEdit.text())
self.proeject_query_report_path = unicode(
self.proeject_queryEdit.text())
self.timesheet_report_path = unicode(self.timesheetLineEdit.text())
self.weight_path = unicode(self.weightEdit.text())
self.output_path = os.path.join(unicode(self.outputpathEdit.text()),
"result.xls")
self.config.set_timesheet_report_path(self.timesheet_report_path)
self.config.set_bc_report_path(self.bc_report_path)
self.config.set_bc_sheet_name(self.bc_sheet_name)
self.config.set_profit_report_path(self.li_run_report_path)
self.config.set_project_query_report_path(
self.proeject_query_report_path)
self.config.set_weight_path(self.weight_path)
self.config.set_output_path(self.output_path)
self.config.set_textBrowser(self.textBrowser)
def brower_file(self, line_edit):
path = QFileDialog.getOpenFileName(self, 'Open file', '.', "*")
if path:
line_edit.setText(unicode(path))
def brower_dir(self, line_edit):
dir_path = QFileDialog.getExistingDirectory(self, "select directory ",
"/")
if dir_path:
line_edit.setText(dir_path)
def is_empty(self, path):
if not path:
return False
return True
def run(self):
if self.get_is_run():
self.textBrowser.clear()
time.sleep(0.2)
self.textBrowser.insertPlainText(u"正在处理，请稍等...\n")
return
self.initial_config()
result = map(self.is_empty, [
self.proeject_query_report_path, self.bc_report_path,
self.timesheet_report_path, self.li_run_report_path,
self.bc_sheet_name, self.output_path
])
if False in result:
msg = u"输入框不能为空\n"
self.textBrowser.insertPlainText(msg)
LOG.info(msg)
return
t = threading.Thread(target=self.caculate)
t.setDaemon(True)
t.start()
self.textBrowser.clear()
self.textBrowser.insertPlainText(u"请稍后...\n")
self.set_is_run(True)
def caculate(self):
try:
main_process = process.Caculating()
main_process.update()
self.textBrowser.append("completed.")
self.textBrowser.append(u"文件已经输出到：%s" % self.output_path)
LOG.info(u"计算完成，文件路径：%s" % self.output_path)
except Exception as e:
LOG.info(e)
if type(e) != unicode or type(e) != str:
e = str(e)
self.textBrowser.append(e)
self.set_is_run(False)
def set_is_run(self, boolean):
global is_run
is_run = boolean
def get_is_run(self):
global is_run
return is_run
class WeightData(object):
'''
获取权重数据表数据
'''
instance_flag = False
_instance_lock = threading.Lock()
def __new__(cls, *args, **kwargs):
if not hasattr(WeightData, "_instance"):
with WeightData._instance_lock:
if not hasattr(WeightData, "_instance"):
WeightData._instance = object.__new__(cls)
return WeightData._instance
def __init__(self):
if not WeightData.instance_flag:
self.config = Config()
self.textBrowser = self.config.get_textBrowser()
self.sheet = None
self.path = self.config.get_weight_path()
if self.path:
excel = ExcelRead(self.textBrowser, self.path)
sheet_name = self.get_sheet_name()
self.sheet = excel.get_sheet_by_name(sheet_name)
self.title_index_dict = excel.get_col_index_of_title()
self.cost_center_name_list = self.get_cost_center_name()
self.weight_data_dict = self._get_weight_data_dict()
WeightData.instance_flag = True
else: # 权重路径未设置的情况下，初始化为空
self.weight_data_dict = dict()
WeightData.instance_flag = True
def get_sheet_name(self):
try:
sheet_name = self.config.get_value("Weight", "weight_sheet_name")
except Exception as e:
msg = u"从配置文件获取 weight_sheet_name 出错： %s" % e
LOG.error(msg)
self.textBrowser.append(str(msg))
return None
return sheet_name
def get_cost_center_name(self):
return self.config.get_cost_center_name_list()
def _get_weight_data_dict(self):
"""
获取 权重 数据对象的字典集合，key为project id
:return: 数据字典
"""
if not self.title_index_dict.has_key(u"项目编号"):
msg = u"未找到项目编号列"
self.textBrowser.append(msg)
raise Exception(msg)
index_of_project_col_num = self.title_index_dict.get(u"项目编号")
zone_dict = self.config.get_zone_dict()
# 取交集，判断权重表 与 配置文件中的所有成本中心，是否存在交集
if not Util.has_intersection(self.title_index_dict.keys(), zone_dict.values()):
msg = u"权重表中的成本中心名称和配置的成本中心名称无法对应。"
LOG.error(msg)
raise Exception(msg)
weight_data_dict = dict()
for row in range(1, self.sheet.nrows):
tmp_dict = dict()
proejct_id = self.sheet.cell_value(row, index_of_project_col_num)
# 获取各成本中心权重
for zone_US, zone_ZH in zone_dict.items():
col_index = self.title_index_dict.get(zone_ZH)
# 权重表中不存在该条成本中心，则继续循环
if not col_index:
continue
value = self.sheet.cell_value(row, col_index)
if value:
tmp_dict[zone_ZH] = value
weight_data_dict[proejct_id] = tmp_dict
return weight_data_dict
def get_weight_data_dict(self):
return self.weight_data_dict