def deal_data(self):
cur = datetime.datetime.now()
year = cur.year
month = cur.month
# 获取demo
excel_data = deepcopy(Globals.get_origin_excel_data())
target_file_name = self.out_put_filename
origin_data = []
target_rows = []
# 处理数据
for i in range(len(origin_data)):
one_data = origin_data[i]
row = deepcopy(Globals.pingzheng_demo)
row2 = deepcopy(Globals.pingzheng_demo)
target_rows.append(row)
target_rows.append(row2)
row[Globals.get_pingzheng_idx("记账日期")] = data_str
row2[Globals.get_pingzheng_idx("记账日期")] = data_str
row[Globals.get_pingzheng_idx("业务日期")] = data_str
row2[Globals.get_pingzheng_idx("业务日期")] = data_str
row[Globals.get_pingzheng_idx("辅助账业务日期")] = data_str
row2[Globals.get_pingzheng_idx("辅助账业务日期")] = data_str
row[Globals.get_pingzheng_idx("会计期间")] = str(cur.month)
row2[Globals.get_pingzheng_idx("会计期间")] = str(cur.month)
row[Globals.get_pingzheng_idx("凭证类型")] = "转"
row2[Globals.get_pingzheng_idx("凭证类型")] = "转"
row[Globals.get_pingzheng_idx("凭证号")] = "20180600269"
row2[Globals.get_pingzheng_idx("凭证号")] = "20180600269"
row[Globals.get_pingzheng_idx("分录号")] = i * 2 + 1
row2[Globals.get_pingzheng_idx("分录号")] = i * 2 + 2
zhaiyao = ""
row[Globals.get_pingzheng_idx("摘要")] = zhaiyao
row2[Globals.get_pingzheng_idx("摘要")] = zhaiyao
row[Globals.get_pingzheng_idx("科目")] = ""
row2[Globals.get_pingzheng_idx("科目")] = ""
row[Globals.get_pingzheng_idx("方向")] = 1
row[Globals.get_pingzheng_idx("原币金额")] = 1
row2[Globals.get_pingzheng_idx("原币金额")] = 1
row2[Globals.get_pingzheng_idx("原币金额")] = -1
row[Globals.get_pingzheng_idx("借方金额")] = 1
row[Globals.get_pingzheng_idx("现金流量标记")] = 2
row2[Globals.get_pingzheng_idx("现金流量标记")] = 2
row[Globals.get_pingzheng_idx("辅助账摘要")] = zhaiyao
row2[Globals.get_pingzheng_idx("辅助账摘要")] = zhaiyao
row[Globals.get_pingzheng_idx("核算项目1")] = "长益租户"
row[Globals.get_pingzheng_idx("名称1")] = ""
row[Globals.get_pingzheng_idx("编码1")] = ""
for row in target_rows:
excel_data["凭证"].append(row)
save_data(target_file_name, excel_data)
Globals.eval_date_format(target_file_name, ["B", "C", "BN"])
def deal_zhifubao_data(self):
target_excel = {"凭证": []}
target_data = target_excel["凭证"]
row1 = [
"公司",
"记账日期",
"业务日期",
"会计期间",
"凭证类型",
"凭证号",
"分录号",
"摘要",
"科目",
"币种",
"汇率",
"方向",
"原币金额",
"数量",
"单价",
"借方金额",
"贷方金额",
"制单人",
"过账人",
"审核人",
"附件数量",
"过账标记",
"机制凭证模块",
"删除标记",
"凭证序号",
"单位",
"参考信息",
"是否有现金流量",
"现金流量标记",
"业务编号",
"结算方式",
"结算号",
"辅助账摘要",
"核算项目1",
"编码1",
"名称1",
"核算项目2",
"编码2",
"名称2",
"核算项目3",
"编码3",
"名称3",
"核算项目4",
"编码4",
"名称4",
"核算项目5",
"编码5",
"名称5",
"核算项目6",
"编码6",
"名称6",
"核算项目7",
"编码7",
"名称7",
"核算项目8",
"编码8",
"名称8",
"发票号",
"换票证号",
"客户",
"费用类别",
"收款人",
"物料",
"财务组织",
"供应商",
"辅助账业务日期",
"到期日",
]
target_data.append(row1)
row2 = [
"公司", "记账日期", "会计期间", "凭证类型", "凭证号", "币种", "分录号", "对方分录号", "主表信息",
"附表信息", "原币", "本位币", "报告币", "主表金额系数", "附表金额系数", "性质", "核算项目1",
"编码1", "名称1", "核算项目2", "编码2", "名称2", "核算项目3", "编码3", "名称3",
"核算项目4", "编码4", "名称4", "核算项目5", "编码5", "名称5", "核算项目6", "编码6",
"名称6", "核算项目7", "编码7", "名称7", "核算项目8", "编码8", "名称8"
]
target_excel["现金流量"] = [row2]
refer_data = self.refer_data
bank_data = self.my_data
target_rows = []
row_num = len(bank_data) - 1
date_str = self.label.text()
month = date_str.split('/')[1]
last_month = int(month) - 2
if last_month == 0:
last_month = 12
elif last_month == -1:
last_month = 11
if last_month < 10:
last_month = "0" + str(last_month)
else:
last_month = str(last_month)
year = date_str.split('/')[0]
day = date_str.split('/')[2]
# 替换信息
# replace_dict = self.get_replace_dict()
# 银行凭证号
#pingzhenghao_bank_idx = bank_data[0].index("凭证号")
# 原币金额索引
#yuanbijine_bank_idx = bank_data[0].index("原币金额")
# 核算项目索引
#hesuanxiangmu_bank_idx = bank_data[0].index("核算项目")
# 贷方金额
daifangjine_bank_idx = bank_data[0].index("贷方发生额")
# 摘要
zhaiyao_bank_idx = bank_data[0].index("摘要")
# 交易时间
jiaoyitime_bank_idx = bank_data[0].index("交易时间")
for i in range(row_num):
idx = i + 1
row = []
row2 = []
target_rows.append(row)
# target_rows.append(row2)
# 公司
row.append("E018")
row2.append("E018")
# 记账日期
row.append(date_str)
row2.append(date_str)
# 业务日期
row.append(date_str)
row2.append(date_str)
# 会计期间
row.append(int(month))
row2.append(int(month))
# 凭证类型
row.append("收")
row2.append("收")
# 凭证号
row.append("20181100963")
row2.append("20181100963")
# 分录号
row.append(idx)
row2.append(idx * 2)
# 摘要
jiaoyitime_str = bank_data[idx][jiaoyitime_bank_idx].split()[0]
middle_str = "收到 微信一点停支付宝返款-"
zhiayao_str = bank_data[idx][zhaiyao_bank_idx]
zhaiyao_value = jiaoyitime_str + middle_str + zhiayao_str
row.append(zhaiyao_value)
row2.append("")
# 科目
row.append("1002.01")
row2.append("1002.01")
# 币种
row.append("BB01")
row2.append("BB01")
# 汇率
row.append(1)
row2.append(1)
# 方向
row.append(1)
row2.append(0)
# 原币金额
money_str = bank_data[idx][daifangjine_bank_idx].replace(",", "")
curr_money = float(money_str)
row.append(curr_money)
row2.append(curr_money)
# sum = sum + int(curr_money * 100)
# 数量
row.append(0)
row2.append(0)
# 单价
row.append(0)
row2.append(0)
# 借方金额
row.append(curr_money)
row2.append("")
# 贷方金额
row.append("")
row2.append("")
# 制单人
row.append(Globals.eas_name)
row2.append(Globals.eas_name)
# 过账人
row.append("")
row2.append("")
# 审核人
row.append("")
row2.append("")
# 附件数量
row.append(1)
row2.append(1)
# 过账标记
row.append("TRUE")
row2.append("TRUE")
# 机制凭证
row.append("")
row2.append("")
# 删除标记
row.append("FALSE")
row2.append("FALSE")
# 凭证序号
row.append("1544876875657--0")
row2.append("1544876875657--0")
# 单位
row.append("")
row2.append("")
# 参考信息
row.append("")
row2.append("")
# 是否有现金流量
row.append("")
row2.append("")
# 现金流量标记
row.append(6)
row2.append(6)
# 业务编号
row.append("")
row2.append("")
# 结算方式
row.append("")
row2.append("")
# 结算号
row.append("")
row2.append("")
# 辅助账摘要
row.append(zhaiyao_value)
row2.append("")
# 核算项目1
row.append("银行账户")
row2.append("")
# 编码1
row.append("001.E018")
row2.append("")
# 名称1
row.append("工商银行古北新区支行1001282509300071274")
row2.append("")
# 核算项目2
row.append("现金流量项目")
row2.append("")
# 编码2
row.append("111")
row2.append("")
# 名称2
row.append("出租业务所收到的现金")
row2.append("")
# 项目3
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目4
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目5
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目6
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目7
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目8
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 发票号
row.append("")
row2.append("")
# 换票证号
row.append("")
row2.append("")
# 客户
row.append("")
row2.append("")
# 费用类别
row.append("")
row2.append("")
# 收款人
row.append("")
row2.append("")
# 物料
row.append("")
row2.append("")
# 财务组织
row.append("")
row2.append("")
# 供应商
row.append("")
row2.append("")
# 辅助帐业务日期
row.append(date_str)
row2.append("")
# 到期日
row.append("")
row2.append("")
for row in target_rows:
target_data.append(row)
# 处理结算的
last_row = deepcopy(target_data[-1])
title_row = target_data[0]
jine_idx = title_row.index("原币金额")
sum = 0
for i in range(row_num):
idx = i + 1
row = target_data[idx]
sum = sum + float(row[jine_idx])
last_row[title_row.index("摘要")] = "收到 上海七宝万科停车场停车费-支付宝返款"
last_row[title_row.index("科目")] = "1122.99"
last_row[title_row.index("方向")] = "0"
last_row[title_row.index("原币金额")] = float("%.2f" % (sum))
last_row[title_row.index("借方金额")] = ""
last_row[title_row.index("贷方金额")] = float("%.2f" % (sum))
last_row[title_row.index("辅助账摘要")] = "收到 上海七宝万科停车场停车费-支付宝返款"
last_row[title_row.index("核算项目1")] = "客户"
last_row[title_row.index("编码1")] = "0503.E018"
last_row[title_row.index("分录号")] = len(target_data)
idx = title_row.index("名称1")
last_row[idx] = "微信一点停"
last_row[idx + 1] = ""
last_row[idx + 2] = ""
last_row[idx + 3] = ""
target_data.append(last_row)
save_data(self.out_put_filename, target_excel)
# 处理日期
Globals.eval_date_format(self.out_put_filename, ["B", "C", "BN"])
def deal_data(self):
target_excel = {"凭证":[]}
target_data = target_excel["凭证"]
row1 = [
"公司",
"记账日期",
"业务日期",
"会计期间",
"凭证类型",
"凭证号",
"分录号",
"摘要",
"科目",
"币种",
"汇率",
"方向",
"原币金额",
"数量",
"单价",
"借方金额",
"贷方金额",
"制单人",
"过账人",
"审核人",
"附件数量",
"过账标记",
"机制凭证模块",
"删除标记",
"凭证序号",
"单位",
"参考信息",
"是否有现金流量",
"现金流量标记",
"业务编号",
"结算方式",
"结算号",
"辅助账摘要",
"核算项目1",
"编码1",
"名称1",
"核算项目2",
"编码2",
"名称2",
"核算项目3",
"编码3",
"名称3",
"核算项目4",
"编码4",
"名称4",
"核算项目5",
"编码5",
"名称5",
"核算项目6",
"编码6",
"名称6",
"核算项目7",
"编码7",
"名称7",
"核算项目8",
"编码8",
"名称8",
"发票号",
"换票证号",
"客户",
"费用类别",
"收款人",
"物料",
"财务组织",
"供应商",
"辅助账业务日期",
"到期日",
]
target_data.append(row1)
row2 = ["公司", "记账日期", "会计期间", "凭证类型", "凭证号", "币种", "分录号", "对方分录号", "主表信息", "附表信息", "原币", "本位币", "报告币", "主表金额系数", "附表金额系数", "性质", "核算项目1", "编码1", "名称1", "核算项目2", "编码2", "名称2", "核算项目3", "编码3", "名称3", "核算项目4", "编码4", "名称4", "核算项目5", "编码5", "名称5", "核算项目6", "编码6", "名称6", "核算项目7", "编码7", "名称7", "核算项目8", "编码8", "名称8" ]
target_excel["现金流量"] = [row2]
refer_data = self.bank_data
bank_data = self.my_data
target_rows = []
row_num = len(bank_data) - 1
for i in range(row_num):
target_rows.append([])
date_str = self.label.text()
month = date_str.split('/')[1]
# 替换信息
replace_dict = self.get_replace_dict(refer_data)
# 银行凭证号
pingzhenghao_bank_idx = bank_data[0].index("凭证号")
# 摘要索引
zhaoyao_bank_idx = bank_data[0].index("摘要")
# 方向索引
fangxiang_bank_idx = bank_data[0].index("方向")
# 原币金额索引
yuanbijine_bank_idx = bank_data[0].index("原币金额")
# 借方金额索引
jiefangjine_bank_idx = bank_data[0].index("借方金额")
# 贷方金额
daifangjine_bank_idx = bank_data[0].index("贷方金额")
# 科目索引
kemu_bank_idx = bank_data[0].index("科目")
# 名称1索引
mingcheng1_idx = bank_data[0].index("名称1")
idx = 0
for row in target_rows:
idx = idx + 1
# 公司
row.append("E018")
# 记账日期
row.append(date_str)
# 业务日期
row.append(date_str)
# 会计期间
row.append(int(month))
# 凭证类型
row.append("付")
# 凭证号
row.append("20180600828")
# 分录号
row.append(idx)
# 摘要
zhaiyao_value = bank_data[idx][pingzhenghao_bank_idx] + " "+ bank_data[idx][zhaoyao_bank_idx]
row.append(zhaiyao_value)
# 科目
kemu_bank_data = str(bank_data[idx][kemu_bank_idx])
if kemu_bank_data in replace_dict["科目"]:
kemu_bank_data = replace_dict["科目"][kemu_bank_data]
# 如果科目是6401.06并且摘要里面有报销
if kemu_bank_data == "6401.06" and "报销：" in zhaiyao_value:
kemu_bank_data = "6401.07.01"
row.append(kemu_bank_data)
else:
self.log("基础信息需要补充地产科目：" + kemu_bank_data, ShowWindow.LOG_ERROR)
row.append("")
# 币种
row.append("BB01")
# 汇率
row.append(1)
# 方向
row.append(int(bank_data[idx][fangxiang_bank_idx]))
# 原币金额
row.append(bank_data[idx][yuanbijine_bank_idx])
# 数量
row.append(0)
# 单价
row.append(0)
# 借方金额
row.append(bank_data[idx][jiefangjine_bank_idx])
# 贷方金额
row.append(bank_data[idx][daifangjine_bank_idx])
# 制单人
row.append(Globals.eas_name)
# 过账人
row.append("")
# 审核人
row.append("")
# 附件数量
row.append(1)
# 过账标记
row.append("TRUE")
# 机制凭证
row.append("")
# 删除标记
row.append("FALSE")
# 凭证序号
row.append("1533086430813--0")
# 单位
row.append("")
# 参考信息
row.append("")
# 是否有现金流量
row.append("")
# 现金流量标记
row.append(6)
# 业务编号
row.append("")
# 结算方式
row.append("")
# 结算号
row.append("")
# 辅助账摘要
row.append(zhaiyao_value)
# 核算项目1
hesuanxiangmu1_value = ""
if kemu_bank_data in replace_dict["核算项目1"]:
hesuanxiangmu1_value = replace_dict["核算项目1"][kemu_bank_data]
row.append(hesuanxiangmu1_value)
else:
row.append("")
# 编码1
if kemu_bank_data in replace_dict["编码1"]:
row.append(replace_dict["编码1"][kemu_bank_data])
else:
row.append("")
# 名称1
mingcheng1_value = ""
if kemu_bank_data in replace_dict["名称1"]:
mingcheng1_value = replace_dict["名称1"][kemu_bank_data]
row.append(mingcheng1_value)
else:
if hesuanxiangmu1_value == "部门" or hesuanxiangmu1_value == "职员":
mingcheng1_value = bank_data[idx][mingcheng1_idx]
row.append(mingcheng1_value)
else:
row.append("")
# 根据部门名称再次计算编码1
if hesuanxiangmu1_value == "部门" or hesuanxiangmu1_value == "职员":
if mingcheng1_value in replace_dict["编码1(用于部门职员)"]:
row[-2] = replace_dict["编码1(用于部门职员)"][mingcheng1_value]
else:
self.log("部门：" + mingcheng1_value + "没有对应编码", self.LOG_ERROR)
# 核算项目2
if kemu_bank_data in replace_dict["核算项目2"]:
row.append(replace_dict["核算项目2"][kemu_bank_data])
else:
row.append("")
# 编码2
if kemu_bank_data in replace_dict["编码2"]:
row.append(replace_dict["编码2"][kemu_bank_data])
else:
row.append("")
# 名称2
if kemu_bank_data in replace_dict["名称2"]:
row.append(replace_dict["名称2"][kemu_bank_data])
else:
row.append("")
# 项目3
row.append("")
row.append("")
row.append("")
# 项目4
row.append("")
row.append("")
row.append("")
# 项目5
row.append("")
row.append("")
row.append("")
# 项目6
row.append("")
row.append("")
row.append("")
# 项目7
row.append("")
row.append("")
row.append("")
# 项目8
row.append("")
row.append("")
row.append("")
# 发票号
row.append("")
# 换票证号
row.append("")
# 客户
row.append("")
# 费用类别
row.append("")
# 收款人
row.append("")
# 物料
row.append("")
# 财务组织
row.append("")
# 供应商
row.append("")
# 辅助帐业务日期
row.append(date_str)
# 到期日
row.append("")
for row in target_rows:
target_data.append(row)
save_data(self.out_put_filename, target_excel)
# 处理日期
Globals.eval_date_format(self.out_put_filename, ["B", "C", "BN"])
def deal_data(self):
target_excel = {"凭证": []}
target_data = target_excel["凭证"]
row1 = [
"公司",
"记账日期",
"业务日期",
"会计期间",
"凭证类型",
"凭证号",
"分录号",
"摘要",
"科目",
"币种",
"汇率",
"方向",
"原币金额",
"数量",
"单价",
"借方金额",
"贷方金额",
"制单人",
"过账人",
"审核人",
"附件数量",
"过账标记",
"机制凭证模块",
"删除标记",
"凭证序号",
"单位",
"参考信息",
"是否有现金流量",
"现金流量标记",
"业务编号",
"结算方式",
"结算号",
"辅助账摘要",
"核算项目1",
"编码1",
"名称1",
"核算项目2",
"编码2",
"名称2",
"核算项目3",
"编码3",
"名称3",
"核算项目4",
"编码4",
"名称4",
"核算项目5",
"编码5",
"名称5",
"核算项目6",
"编码6",
"名称6",
"核算项目7",
"编码7",
"名称7",
"核算项目8",
"编码8",
"名称8",
"发票号",
"换票证号",
"客户",
"费用类别",
"收款人",
"物料",
"财务组织",
"供应商",
"辅助账业务日期",
"到期日",
]
target_data.append(row1)
row2 = [
"公司", "记账日期", "会计期间", "凭证类型", "凭证号", "币种", "分录号", "对方分录号", "主表信息",
"附表信息", "原币", "本位币", "报告币", "主表金额系数", "附表金额系数", "性质", "核算项目1",
"编码1", "名称1", "核算项目2", "编码2", "名称2", "核算项目3", "编码3", "名称3",
"核算项目4", "编码4", "名称4", "核算项目5", "编码5", "名称5", "核算项目6", "编码6",
"名称6", "核算项目7", "编码7", "名称7", "核算项目8", "编码8", "名称8"
]
target_excel["现金流量"] = [row2]
refer_data = self.refer_data
bank_data = self.my_data
target_rows = []
row_num = len(bank_data) - 1
date_str = self.label.text()
month = date_str.split('/')[1]
last_month = int(month) - 2
if last_month == 0:
last_month = 12
elif last_month == -1:
last_month = 11
if last_month < 10:
last_month = "0" + str(last_month)
else:
last_month = str(last_month)
year = date_str.split('/')[0]
day = date_str.split('/')[2]
# 替换信息
replace_dict = self.get_replace_dict()
# 银行凭证号
pingzhenghao_bank_idx = bank_data[0].index("凭证号")
# 原币金额索引
yuanbijine_bank_idx = bank_data[0].index("原币金额")
# 核算项目索引
hesuanxiangmu_bank_idx = bank_data[0].index("核算项目")
# 贷方金额
daifangjine_bank_idx = bank_data[0].index("贷方")
# 摘要
zhaiyao_bank_idx = bank_data[0].index("摘要")
for i in range(row_num):
idx = i + 1
row = []
row2 = []
target_rows.append(row)
target_rows.append(row2)
# 公司
row.append("E018")
row2.append("E018")
# 记账日期
row.append(date_str)
row2.append(date_str)
# 业务日期
row.append(date_str)
row2.append(date_str)
# 会计期间
row.append(int(month))
row2.append(int(month))
# 凭证类型
row.append("转")
row2.append("转")
# 凭证号
row.append("20181100961")
row2.append("20181100961")
# 分录号
row.append(idx * 2 - 1)
row2.append(idx * 2)
# 摘要
replace_zhaiyao_str = "结转" + year + last_month
zhaiyao_value = replace_zhaiyao_str + bank_data[idx][
zhaiyao_bank_idx][2:]
row.append(zhaiyao_value)
row2.append(zhaiyao_value)
# 科目
row.append("1122.01.01")
row2.append("6001.13")
# 币种
row.append("BB01")
row2.append("BB01")
# 汇率
row.append(1)
row2.append(1)
# 方向
row.append(1)
row2.append(0)
# 原币金额
row.append(bank_data[idx][yuanbijine_bank_idx])
row2.append(bank_data[idx][yuanbijine_bank_idx])
# 数量
row.append(0)
row2.append(0)
# 单价
row.append(0)
row2.append(0)
# 借方金额
row.append(bank_data[idx][daifangjine_bank_idx])
row2.append("")
# 贷方金额
row.append("")
row2.append(bank_data[idx][daifangjine_bank_idx])
# 制单人
row.append(Globals.eas_name)
row2.append(Globals.eas_name)
# 过账人
row.append("")
row2.append("")
# 审核人
row.append("")
row2.append("")
# 附件数量
row.append(1)
row2.append(1)
# 过账标记
row.append("TRUE")
row2.append("TRUE")
# 机制凭证
row.append("")
row2.append("")
# 删除标记
row.append("FALSE")
row2.append("FALSE")
# 凭证序号
row.append("1544857876410--0")
row2.append("1544857876410--0")
# 单位
row.append("")
row2.append("")
# 参考信息
row.append("")
row2.append("")
# 是否有现金流量
row.append("")
row2.append("")
# 现金流量标记
row.append(2)
row2.append(2)
# 业务编号
row.append("")
row2.append("")
# 结算方式
row.append("")
row2.append("")
# 结算号
row.append("")
row2.append("")
# 辅助账摘要
row.append(zhaiyao_value)
row2.append("")
# 核算项目1
row.append("长益租户")
row2.append("")
# 编码1
row.append("")
row2.append("")
# 名称1
name1 = bank_data[idx][hesuanxiangmu_bank_idx].replace(
"长益租户:", "", 1)
name1 = name1[:-1]
row.append(name1)
row2.append("")
# 根据名称1修改编码1
name_number_dict = replace_dict["name_number"]
if name1 in name_number_dict:
row[-2] = name_number_dict[name1]
# 核算项目2
row.append("")
row2.append("")
# 编码2
row.append("")
row2.append("")
# 名称2
row.append("")
row2.append("")
# 项目3
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目4
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目5
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目6
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目7
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 项目8
row.append("")
row2.append("")
row.append("")
row2.append("")
row.append("")
row2.append("")
# 发票号
row.append("")
row2.append("")
# 换票证号
row.append("")
row2.append("")
# 客户
row.append("")
row2.append("")
# 费用类别
row.append("")
row2.append("")
# 收款人
row.append("")
row2.append("")
# 物料
row.append("")
row2.append("")
# 财务组织
row.append("")
row2.append("")
# 供应商
row.append("")
row2.append("")
# 辅助帐业务日期
row.append(date_str)
row2.append("")
# 到期日
row.append("")
row2.append("")
idx = 0
for row in target_rows:
if len(row) == len(target_data[0]):
target_data.append(row)
else:
print(idx, len(row) - len(target_data[0]))
idx = idx + 1
save_data(self.out_put_filename, target_excel)
# 处理日期
Globals.eval_date_format(self.out_put_filename, ["B", "C", "BN"])
def deal_pingzheng_data(self):
# 先检测一遍基础信息是否齐全
gongsimingcheng_idx = self.my_data[0].index("公司名称")
is_ok = True
for i in range(1, len(self.my_data)):
if len(self.my_data[i]) > gongsimingcheng_idx and self.my_data[i][
gongsimingcheng_idx] and self.my_data[i][0]:
if self.my_data[i][gongsimingcheng_idx] not in self.refer_data:
self.log(
"公司: " + self.my_data[i][gongsimingcheng_idx] +
" 不在基础信息表中", self.LOG_ERROR)
is_ok = False
if not is_ok:
self.log("一定要注意删掉禁用的自定义核算项目！", self.LOG_ERROR)
return
else:
self.log("公司名称检测ok!")
# 基础信息
out_put_file_dir = Globals.desktop_path + "主合同确认引入凭证/"
if not os.path.exists(out_put_file_dir):
os.mkdir(out_put_file_dir)
cur = datetime.datetime.now()
kemu_date_str = str(cur.year) + "%02d" % cur.month
kemu_date_str_last_month = Globals.get_time_text_year_lastmonth()
kemu_data_str_next_month = Globals.get_time_text_year_nextmonth()
output_filenames = (
# key，税率，非主力店科目，主力店科目，摘要，文件名，row1科目，是否是核算项目, 是否用上个月
("电费", 1.13, "6401.24.01.02", "6401.24.01.02",
"结转" + kemu_date_str_last_month + "电费 %s-%s-%s", "结转主合同电费.xlsx",
"2203.01.01", 0, 1),
("电费", 1.13, "2203.01.01", "2203.01.01",
"确认" + kemu_date_str_last_month + "电费 %s-%s-%s", "确认主合同电费.xlsx",
"1122.01.01", 1, 1),
("水费", 1.03, "6401.24.02.02", "6401.24.02.02",
"结转" + kemu_date_str_last_month + "水费 %s-%s-%s", "结转主合同水费.xlsx",
"2203.01.01", 0, 1),
("水费", 1.03, "2203.01.01", "2203.01.01",
"确认" + kemu_date_str_last_month + "水费 %s-%s-%s", "确认主合同水费.xlsx",
"1122.01.01", 1, 1),
("仓库租赁费", 1.09, "2203.01.01", "2203.01.01",
"确认" + kemu_data_str_next_month + "仓库租赁费 %s-%s-%s",
"确认主合仓库租赁费.xlsx", "1122.01.01", 1, 0),
("固定租金", 1.09, "2203.01.01", "2203.01.01",
"确认" + kemu_data_str_next_month + "固定租金 %s-%s-%s",
"确认主合同固定租金.xlsx", "1122.01.01", 1, 0),
("广告位租赁费", 1.09, "2203.01.01", "2203.01.01",
"确认" + kemu_data_str_next_month + "广告位租赁费 %s-%s-%s",
"确认主合同广告位租赁费.xlsx", "1122.01.01", 1, 0),
("推广费（固定）", 1.06, "2203.01.01", "2203.01.01",
"确认" + kemu_data_str_next_month + "推广费（固定） %s-%s-%s",
"确认主合同推广费（固定）.xlsx", "1122.01.01", 1, 0),
("物业管理费", 1.06, "2203.01.01", "2203.01.01",
"确认" + kemu_data_str_next_month + "物业管理费 %s-%s-%s",
"确认主合同物业管理费.xlsx", "1122.01.01", 1, 0),
)
for file_info in output_filenames:
# 主key
title_key = file_info[0]
# 税率
shuilv = file_info[1]
# 非主力科目
not_zhuli_kemu = file_info[2]
# 主力科目
zhuli_kemu = file_info[3]
# 摘要str
zhaiyao_str = file_info[4]
# 文件名
file_name = file_info[5]
# row1科目
kemu_name1 = file_info[6]
# 核算项目都有
is_queren = file_info[7]
# 是否上月
is_last_month = file_info[8]
# 获取demo
excel_data = deepcopy(Globals.get_origin_excel_data())
target_file_name = out_put_file_dir + file_name
title_idx = self.my_data[0].index(title_key)
origin_data = []
for i in range(1, len(self.my_data)):
if len(self.my_data[i]) > title_idx and self.my_data[i][
title_idx] and self.my_data[i][0]:
origin_data.append(self.my_data[i])
dianpuzhaopai_idx = self.my_data[0].index("店铺招牌")
puweihao_idx = self.my_data[0].index("铺位号")
def key_func(elem):
return elem[puweihao_idx]
origin_data.sort(key=key_func)
target_rows = []
data_str = str(cur.year) + "/" + str(cur.month) + "/" + str(
cur.day)
# 处理数据
for i in range(len(origin_data)):
one_data = origin_data[i]
row = deepcopy(Globals.pingzheng_demo)
row2 = deepcopy(Globals.pingzheng_demo)
target_rows.append(row)
target_rows.append(row2)
row[Globals.get_pingzheng_idx("记账日期")] = data_str
row2[Globals.get_pingzheng_idx("记账日期")] = data_str
row[Globals.get_pingzheng_idx("业务日期")] = data_str
row2[Globals.get_pingzheng_idx("业务日期")] = data_str
row[Globals.get_pingzheng_idx("辅助账业务日期")] = data_str
row2[Globals.get_pingzheng_idx("辅助账业务日期")] = data_str
row[Globals.get_pingzheng_idx("会计期间")] = str(cur.month)
row2[Globals.get_pingzheng_idx("会计期间")] = str(cur.month)
row[Globals.get_pingzheng_idx("凭证类型")] = "转"
row2[Globals.get_pingzheng_idx("凭证类型")] = "转"
row[Globals.get_pingzheng_idx("凭证号")] = "20180600269"
row2[Globals.get_pingzheng_idx("凭证号")] = "20180600269"
row[Globals.get_pingzheng_idx("分录号")] = i * 2 + 1
row2[Globals.get_pingzheng_idx("分录号")] = i * 2 + 2
shop_name = one_data[gongsimingcheng_idx]
zhiapai_name = one_data[dianpuzhaopai_idx]
puweihao_name = one_data[puweihao_idx]
zhaiyao = zhaiyao_str % (shop_name, zhiapai_name,
puweihao_name)
row[Globals.get_pingzheng_idx("摘要")] = zhaiyao
row2[Globals.get_pingzheng_idx("摘要")] = zhaiyao
row[Globals.get_pingzheng_idx("科目")] = kemu_name1
if self.is_zhuli(shop_name):
row2[Globals.get_pingzheng_idx("科目")] = zhuli_kemu
else:
row2[Globals.get_pingzheng_idx("科目")] = not_zhuli_kemu
row[Globals.get_pingzheng_idx("方向")] = 1
if is_queren:
row2[Globals.get_pingzheng_idx("方向")] = 0
else:
row2[Globals.get_pingzheng_idx("方向")] = 1
jine_num = float("%.2f" % (one_data[title_idx] / shuilv))
row[Globals.get_pingzheng_idx("原币金额")] = jine_num
if is_queren:
row2[Globals.get_pingzheng_idx("原币金额")] = jine_num
else:
row2[Globals.get_pingzheng_idx("原币金额")] = -jine_num
row[Globals.get_pingzheng_idx("借方金额")] = jine_num
if is_queren:
pass
else:
row2[Globals.get_pingzheng_idx("借方金额")] = -jine_num
if is_queren:
row2[Globals.get_pingzheng_idx("贷方金额")] = jine_num
else:
pass
row[Globals.get_pingzheng_idx("现金流量标记")] = 2
row2[Globals.get_pingzheng_idx("现金流量标记")] = 2
row[Globals.get_pingzheng_idx("辅助账摘要")] = zhaiyao
row2[Globals.get_pingzheng_idx("辅助账摘要")] = zhaiyao
row[Globals.get_pingzheng_idx("核算项目1")] = "长益租户"
row[Globals.get_pingzheng_idx("名称1")] = shop_name
row[Globals.get_pingzheng_idx("编码1")] = self.get_bianma(
shop_name)
if is_queren:
row2[Globals.get_pingzheng_idx("核算项目1")] = "长益租户"
row2[Globals.get_pingzheng_idx("名称1")] = shop_name
row2[Globals.get_pingzheng_idx("编码1")] = self.get_bianma(
shop_name)
else:
row2[Globals.get_pingzheng_idx("核算项目1")] = "部门"
row2[Globals.get_pingzheng_idx("编码1")] = "102.E018"
row2[Globals.get_pingzheng_idx("名称1")] = "七宝物业部"
for row in target_rows:
excel_data["凭证"].append(row)
save_data(target_file_name, excel_data)
Globals.eval_date_format(target_file_name, ["B", "C", "BN"])
def save_data(self, target_file_name, data):
save_data(target_file_name, data)
# 处理日期
Globals.eval_date_format(target_file_name, ["B", "C", "BN"])
def deal_data(self):
cur = datetime.datetime.now()
year = cur.year
month = cur.month
data_str = str(year) + "/" +str(month) + "/" + str(cur.day)
# 获取demo
excel_data = deepcopy(Globals.get_origin_excel_data())
target_file_name = self.out_put_filename
origin_data = []
cur_year_month_str = Globals.get_time_text_year_curmonth()
try:
dangyue_jine_idx = self.my_data[0].index(cur_year_month_str)
except:
dangyue_jine_idx = self.my_data[0].index(int(cur_year_month_str))
for i in range(1, len(self.my_data)):
row = self.my_data[i]
if row[0] and row[1] and row[dangyue_jine_idx]:
origin_data.append(row)
bumen_idx = self.my_data[0].index("部门")
gongyingshang_idx = self.my_data[0].index("供应商全称")
kemu_idx = self.my_data[0].index("科目名称")
zhaiyao_idx = self.my_data[0].index("摘要")
hetonghao_idx = self.my_data[0].index("合同订单号")
target_rows = []
# 处理数据
for i in range(len(origin_data)):
one_data = origin_data[i]
row = deepcopy(Globals.pingzheng_demo)
row2 = deepcopy(Globals.pingzheng_demo)
target_rows.append(row)
target_rows.append(row2)
row[Globals.get_pingzheng_idx("记账日期")] = data_str
row2[Globals.get_pingzheng_idx("记账日期")] = data_str
row[Globals.get_pingzheng_idx("业务日期")] = data_str
row2[Globals.get_pingzheng_idx("业务日期")] = data_str
row[Globals.get_pingzheng_idx("辅助账业务日期")] = data_str
row2[Globals.get_pingzheng_idx("辅助账业务日期")] = data_str
row[Globals.get_pingzheng_idx("会计期间")] = str(cur.month)
row2[Globals.get_pingzheng_idx("会计期间")] = str(cur.month)
row[Globals.get_pingzheng_idx("凭证类型")] = "转"
row2[Globals.get_pingzheng_idx("凭证类型")] = "转"
row[Globals.get_pingzheng_idx("凭证号")] = "20180600269"
row2[Globals.get_pingzheng_idx("凭证号")] = "20180600269"
row[Globals.get_pingzheng_idx("分录号")] = i * 2 + 1
row2[Globals.get_pingzheng_idx("分录号")] = i * 2 + 2
kemu_name = one_data[kemu_idx]
kemu_bianma1 = self.refer_data["科目名称"][kemu_name]["科目"]
row[Globals.get_pingzheng_idx("科目")] = kemu_bianma1
kemu_bianma2 = self.refer_data["部门"][one_data[bumen_idx]]["科目"]
row2[Globals.get_pingzheng_idx("科目")] = kemu_bianma2
row[Globals.get_pingzheng_idx("方向")] = 1
row2[Globals.get_pingzheng_idx("方向")] = 0
jine = float("%.2f" % (one_data[dangyue_jine_idx]))
row[Globals.get_pingzheng_idx("原币金额")] = jine
row2[Globals.get_pingzheng_idx("原币金额")] = jine
row[Globals.get_pingzheng_idx("借方金额")] = jine
row2[Globals.get_pingzheng_idx("贷方金额")] = jine
row[Globals.get_pingzheng_idx("现金流量标记")] = 2
row2[Globals.get_pingzheng_idx("现金流量标记")] = 2
row[Globals.get_pingzheng_idx("核算项目1")] = "部门"
row2[Globals.get_pingzheng_idx("核算项目1")] = "供应商"
mingcheng1 = one_data[bumen_idx]
row[Globals.get_pingzheng_idx("名称1")] = self.refer_data["部门"][mingcheng1]["名称1"]
gongyingshang_name = one_data[gongyingshang_idx]
row2[Globals.get_pingzheng_idx("名称1")] = gongyingshang_name
row[Globals.get_pingzheng_idx("编码1")] = self.refer_data["部门"][mingcheng1]["编码1"]
row2[Globals.get_pingzheng_idx("编码1")] = self.refer_data["供应商全称"][gongyingshang_name]["编码"]
hesuanxiangmu2 = ""
if str(kemu_bianma1) == "6401.38":
hesuanxiangmu2 = "关联公司"
row[Globals.get_pingzheng_idx("核算项目2")] = hesuanxiangmu2
row[Globals.get_pingzheng_idx("编码2")] = "Y023"
row[Globals.get_pingzheng_idx("名称2")] = "上海万科物业服务有限公司"
zhaiyao = cur_year_month_str + "预提（暂估）" + str(one_data[bumen_idx]) + "-" + gongyingshang_name + "-" + str(one_data[zhaiyao_idx]) + "-" + str(one_data[hetonghao_idx])
row[Globals.get_pingzheng_idx("摘要")] = zhaiyao
row2[Globals.get_pingzheng_idx("摘要")] = zhaiyao
row[Globals.get_pingzheng_idx("辅助账摘要")] = zhaiyao
row2[Globals.get_pingzheng_idx("辅助账摘要")] = zhaiyao
for row in target_rows:
excel_data["凭证"].append(row)
save_data(target_file_name, excel_data)
Globals.eval_date_format(target_file_name, ["B", "C", "BN"])
