def add_same_and_opposite_subjects(df_xsz, records, grades):
'''
在序时账中添加对方科目和相同方科目
:param df_xsz:序时账
:param records:所有凭证记录
:return:序时账中添加same_subjects（相同方科目名称）和opposite_subjects（对方科目名称）
'''
df_xsz_new = df_xsz.copy().set_index(
['month', 'vocher_type', 'vocher_num', 'subentry_num'])
df_xsz_new["same_subjects"] = ""
df_xsz_new["opposite_subjects"] = ""
df_xsz_new["nature"] = ""
for record in records:
df_tmp = df_xsz[(df_xsz["month"] == record["month"])
& (df_xsz["vocher_num"] == record["vocher_num"])
& (df_xsz["vocher_type"] == record["vocher_type"])]
subjects = get_entry_subjects(df_tmp, "tb_subject")
debit_subjects_list = subjects["debit"]
credit_subjects_list = subjects["credit"]
df_entry_debit = df_tmp[df_tmp["direction"] == "借"]
df_entry_credit = df_tmp[df_tmp["direction"] == "贷"]
for obj in gen_df_line(df_entry_debit):
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'same_subjects'] = json.dumps(debit_subjects_list,
ensure_ascii=False)
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'opposite_subjects'] = json.dumps(
credit_subjects_list, ensure_ascii=False)
if obj["tb_subject"] in ["其他应收款", "其他应付款", "应交税费"]:
nature = get_subject_nature(obj, debit_subjects_list,
credit_subjects_list, df_tmp,
grades)
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'nature'] = nature
for obj in gen_df_line(df_entry_credit):
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'same_subjects'] = json.dumps(credit_subjects_list,
ensure_ascii=False)
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'opposite_subjects'] = json.dumps(
debit_subjects_list, ensure_ascii=False)
if obj["tb_subject"] in ["应付账款", "预付款项", "其他应收款", "其他应付款", "应交税费"]:
nature = get_subject_nature(obj, debit_subjects_list,
credit_subjects_list, df_tmp,
grades)
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'nature'] = nature
return df_xsz_new.reset_index()
def get_account_nature(df_xsz, name):
'''
从本年度序时账获取供应商款项性质
:param df_xsz: 序时账
:param name: 供应商名称
:return: 供应商采购的款项性质
'''
df_tmp_xsz = df_xsz[(df_xsz["auxiliary"].str.contains(name))
& (df_xsz["credit"].abs() > 0)]
if len(df_tmp_xsz) > 0:
for obj in gen_df_line(df_tmp_xsz[:1]):
df_supplier_xsz = df_xsz[
(df_xsz["month"] == obj["month"])
& (df_xsz["vocher_type"] == obj["vocher_type"]) &
(df_xsz["vocher_num"] == obj["vocher_num"]) &
(df_xsz["debit"].abs() > 0)]
for i in long_term_assets:
if df_supplier_xsz["tb_subject"].str.contains(i).any():
return "长期资产"
for i in inventory:
if df_supplier_xsz["tb_subject"].str.contains(i).any():
return "材料费"
for i in expense:
if df_supplier_xsz["tb_subject"].str.contains(i).any():
return "费用"
return "材料费"
def aduit_entry(company_name, start_time, end_time, session, engine,
add_suggestion, subject_descs):
# 获取科目余额表和序时账
df_km, df_xsz = get_new_km_xsz_df(company_name, start_time, end_time,
engine, add_suggestion, session)
# 获取科目级次
df_km_gradation = df_km.drop_duplicates('subject_gradation', keep='first')
grades = len(df_km_gradation)
# 合并序时账和标准科目对照表
df_std = pd.read_sql_table('subjectcontrast', engine)
df_std = df_std[["origin_subject", "tb_subject"]]
df_xsz = pd.merge(df_xsz,
df_std,
how="left",
left_on="subject_name_1",
right_on="origin_subject")
df_xsz["tb_subject"].fillna(df_xsz["subject_name_1"], inplace=True)
df_xsz["direction"] = df_xsz["debit"].apply(lambda x: "借"
if abs(x) > 0 else "贷")
# df_xsz["nature"] = df_xsz["auxiliary"].apply(get_supplier_nature, args=(df_xsz,))
# df_xsz["subject_name_nature"] = df_xsz["tb_subject"] + df_xsz["nature"]
# 获取所有的凭证记录
df_xsz_record = df_xsz[["month", "vocher_num",
"vocher_type"]].drop_duplicates()
records = df_xsz_record.to_dict('records')
# 添加相同方向会计科目和对方会计科目
df_xsz = add_same_and_opposite_subjects(df_xsz, records, grades)
# 获取每一笔凭证
start_time = datetime.strptime(start_time, '%Y-%m-%d')
end_time = datetime.strptime(end_time, '%Y-%m-%d')
# 获取可能未通过应付职工薪酬核算的职工薪酬项目
not_through_salary_entries = get_not_through_salary_entry(
df_xsz, grades, start_time, end_time)
for record in records:
# 获取单笔凭证
# if record["month"] != 1 and record["vocher_num"]!=178:
# continue
df_tmp = df_xsz[(df_xsz["month"] == record["month"])
& (df_xsz["vocher_num"] == record["vocher_num"])
& (df_xsz["vocher_type"] == record["vocher_type"])]
# 处理没有通过应付职工薪酬核算的职工薪酬
if len(not_through_salary_entries) > 0:
res = "{}-{}-{}".format(record["month"], record["vocher_type"],
record["vocher_num"])
if res in not_through_salary_entries:
for obj in gen_df_line(df_tmp):
add_event(company_name, start_time, end_time, session,
"职工薪酬-未通过应付职工薪酬核算", obj)
continue
# 处理其他凭证
df_split_entries = split_entry(df_tmp)
for df_split_entry in df_split_entries:
print(df_split_entry)
handle_entry(df_split_entry, record, subject_descs, grades,
start_time, end_time, session)
def add_entry_desc(company_name, start_time, end_time, session, df_entry,
desc):
'''
为凭证所有的分录添加相同的描述
:param company_name: 公司名
:param start_time: 开始时间
:param end_time: 结束时间
:param session: 数据库session
:param df_entry: 记账凭证
:param desc: 描述
:return: 向数据库添加该凭证的描述
'''
for obj in gen_df_line(df_entry):
add_event(company_name, start_time, end_time, session, desc, obj)
def get_entry_subjects(df_one_entry, subject_name_grade):
'''
获取凭证的借贷方科目
:param df_one_entry: 一笔凭证
:return: 借方科目列表和贷方科目列表组成的字典{"debit":debit_subjects_list,"credit":credit_subjects_list}
'''
debit_subjects = set()
credit_subjects = set()
# 获取凭证的借贷方
for obj in gen_df_line(df_one_entry):
if obj["debit"] > 1e-5:
debit_subjects.add(obj[subject_name_grade])
else:
credit_subjects.add((obj[subject_name_grade]))
debit_subjects_list = list(debit_subjects)
credit_subjects_list = list(credit_subjects)
debit_subjects_list.sort()
credit_subjects_list.sort()
return {"debit": debit_subjects_list, "credit": credit_subjects_list}
def check_subject_and_desc_contains(df_one_entry, strs, grades):
'''
检查凭证摘要或科目中是否包含特定的字符串
:param df_one_entry:凭证
:param strs:字符串或者列表
:param grades:科目级次
:return:包含或不包含
'''
if not isinstance(strs, list):
raise Exception("需要传入字符串列表")
for obj in gen_df_line(df_one_entry):
containers = []
desc = obj["description"]
containers.append(desc)
for grade in range(grades):
subject_name = "subject_name_{}".format(grade + 1)
containers.append(obj[subject_name])
for container in containers:
for str in strs:
if str in container:
return True
return False
def split_entry(df_entry):
'''
将一笔凭证拆分成多笔凭证，根据摘要拆分，根据
:param df_entry:
:return:
'''
# 本年利润凭证不予分拆
not_split_subjects = ["本年利润"]
for not_split_subject in not_split_subjects:
if len(df_entry[df_entry["subject_name_1"] == not_split_subject]) > 0:
return [df_entry]
subjects = get_entry_subjects(df_entry, "subject_name_1")
debit_subjects_list = subjects["debit"]
credit_subjects_list = subjects["credit"]
df_entry_debit = df_entry[df_entry["direction"] == "借"]
df_entry_credit = df_entry[df_entry["direction"] == "贷"]
# 货币资金支付不同种类的费用进行分拆，收到货币资金，如果对方科目唯一进行分拆
# 如果一方是货币资金另一方包含两个科目（除去应交税费)，则进行分拆，
# 如果一方是货币资金，另一方是多个科目
if len(debit_subjects_list) == 1 and (debit_subjects_list[0] in [
"库存现金", "银行存款"
]) and len(credit_subjects_list) > 1:
res = one_to_many_split(df_entry_debit, df_entry_credit, "debit")
return res
elif len(credit_subjects_list) == 1 and (credit_subjects_list[0] in [
"库存现金", "银行存款"
]) and len(debit_subjects_list) > 1:
res = one_to_many_split(df_entry_credit, df_entry_debit, "credit")
return res
# 如果货币资金在多个科目一方
elif len(debit_subjects_list) == 1 and (debit_subjects_list[0] not in [
"库存现金", "银行存款"
]) and len(credit_subjects_list) > 1 and (
("库存现金" in credit_subjects_list) or ("银行存款" in credit_subjects_list)):
# 如果多个科目中不包含应交税费，则全部分拆,否则仅分拆货币资金
res = one_to_many_split(df_entry_debit, df_entry_credit, "debit")
return res
elif len(credit_subjects_list) == 1 and (credit_subjects_list[0] not in [
"库存现金", "银行存款"
]) and len(debit_subjects_list) > 1 and (("库存现金" in debit_subjects_list) or
("银行存款" in debit_subjects_list)):
# 如果多个科目中不包含应交税费，则全部分拆,否则仅分拆货币资金
res = one_to_many_split(df_entry_debit, df_entry_credit, "debit")
return res
# 判断摘要是否不止一个,并且借方和贷方同时有多个项目
# 根据相同的摘要进行分类
df_entry_desctiption = df_entry.drop_duplicates(subset=["description"])
if len(df_entry_desctiption) > 1 and len(debit_subjects_list) > 1 and len(
credit_subjects_list) > 1:
res = []
for obj in gen_df_line(df_entry_desctiption):
df_tmp = df_entry[df_entry["description"] == obj["description"]]
if math.isclose(df_tmp["debit"].sum(),
df_tmp["credit"].sum(),
rel_tol=1e-5):
res.append(df_tmp)
if len(res) > 0:
splited = pd.concat(res)
df_duplicated = pd.concat([df_entry, splited])
df_leave = df_duplicated.drop_duplicates(keep=False)
if len(df_leave) > 0:
res.append(df_leave)
return res
else:
return [df_entry]
else:
return [df_entry]
def anylyse_entry_next(company_name, start_time, end_time, session, engine,
add_suggestion):
# 长期待摊费用特别处理
# 1、长期待摊费用借方代表长期资产
# 2、长期待摊费用贷方如果是摊销代表长期资产摊销
# 获取科目余额表和序时账
df_km, df_xsz = get_new_km_xsz_df(company_name, start_time, end_time,
engine, add_suggestion, session)
# 获取所有的凭证记录
df_xsz_record = df_xsz[["month", "vocher_num",
"vocher_type"]].drop_duplicates()
records = df_xsz_record.to_dict('records')
# 1/按照借方和贷方分类
dict_tmp = defaultdict(list)
df_cash_std = pd.read_excel('../data/subject_contrast.xlsx',
sheet_name="cash")
df_cash_std = df_cash_std.set_index("origin")
for record in records:
# 获取每一笔凭证
df_tmp = df_xsz[(df_xsz["month"] == record["month"])
& (df_xsz["vocher_num"] == record["vocher_num"])
& (df_xsz["vocher_type"] == record["vocher_type"])]
debit_subjects = set()
credit_subjects = set()
# 获取凭证的借贷方
for obj in gen_df_line(df_tmp):
if obj["subject_name_1"] == "长期待摊费用":
if abs(obj["debit"]) > 1e-5:
subjct_name = "长期资产"
else:
subjct_name = "长期资产摊销和折旧"
else:
subjct_name = df_cash_std.at[obj["subject_name_1"], "cash"]
if abs(obj["debit"]) > 1e-5:
debit_subjects.add(subjct_name)
else:
credit_subjects.add(subjct_name)
debit_subjects_list = list(debit_subjects)
credit_subjects_list = list(credit_subjects)
debit_subjects_list.sort()
credit_subjects_list.sort()
debit_subject_desc = "%".join(debit_subjects_list)
credit_subjects_desc = "%".join(credit_subjects_list)
entry_desc = debit_subject_desc + "@" + credit_subjects_desc
dict_tmp[entry_desc].append(
(obj["month"], obj["vocher_num"], obj["vocher_type"]))
start_time = datetime.strptime(start_time, '%Y-%m-%d')
end_time = datetime.strptime(end_time, '%Y-%m-%d')
for key in dict_tmp:
entryclassify = EntryClassify(start_time=start_time,
end_time=end_time,
company_name=company_name,
step=2,
desc=key,
number=len(dict_tmp[key]),
records=json.dumps(dict_tmp[key],
ensure_ascii=False))
session.add(entryclassify)
session.commit()