def get_account_nature(df_xsz, name):
'''
从本年度序时账获取供应商款项性质
:param df_xsz: 序时账
:param name: 供应商名称
:return: 供应商采购的款项性质
'''
df_tmp_xsz = df_xsz[(df_xsz["auxiliary"].str.contains(name, regex=False))
& (df_xsz["credit"].abs() > 0)]
if len(df_tmp_xsz) > 0:
for obj in gen_df_line(df_tmp_xsz.tail(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 add_entry_desc(start_time, end_time, session, df_entry, desc, count,
records_length):
'''
为凭证所有的分录添加相同的描述
: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(start_time, end_time, session, desc, obj)
# session.commit()
# 如果另外的线程调用会引发database is locked错误
if (count % 500 == 0) or (count == (records_length - 1)):
session.commit()
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 abs(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 add_nature(auxiliaries, df_xsz_last, session):
'''
为供应商添加属性
:param auxiliaries: 供应商
:param df_xsz_last: 凭证
:param session: 数据库session
:return:
'''
for auxiliary in auxiliaries:
df_tmp_xsz = df_xsz_last[(
df_xsz_last["auxiliary"].str.contains(auxiliary.name, regex=False))
& (df_xsz_last["credit"].abs() > 0)]
if len(df_tmp_xsz) > 0:
for obj in gen_df_line(df_tmp_xsz.tail(1)):
df_supplier_xsz = df_xsz_last[
(df_xsz_last["month"] == obj["month"])
& (df_xsz_last["vocher_type"] == obj["vocher_type"]) &
(df_xsz_last["vocher_num"] == obj["vocher_num"]) &
(df_xsz_last["debit"].abs() > 0)]
auxiliary.nature = get_nature(df_supplier_xsz)
session.commit()
def handle_one_entry(start_time, end_time, df_xsz, record,
not_through_salary_entries, grades, session, count,
records_length):
'''
:param start_time:
:param end_time:
:param df_xsz:
:param record:
:param not_through_salary_entries:
:param grades:
:param session:
:param count:
:param records_length:
:return:
'''
df_tmp = get_df_one_entry(df_xsz, record)
# 处理没有通过应付职工薪酬核算的职工薪酬
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(start_time, end_time, session, "职工薪酬-未通过应付职工薪酬核算",
obj)
session.commit()
else:
df_split_entries = split_entry(df_tmp)
for df_split_entry in df_split_entries:
handle_entry(df_split_entry, record, grades, start_time,
end_time, session, count, records_length)
else:
df_split_entries = split_entry(df_tmp)
for df_split_entry in df_split_entries:
handle_entry(df_split_entry, record, grades, start_time, end_time,
session, count, records_length)
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:
if container == None:
continue
for str in strs:
if str in container:
return True
return False
def get_occours(df_obj_xsz, abs_terminal_value, direction):
sum = 0.0
occurs = []
for item in gen_df_line(df_obj_xsz):
sum = sum + item.get(direction)
if sum <= abs_terminal_value:
occurs.append({
"occur_time": item["record_time"],
"value": item.get(direction)
})
else:
occurs.append({
"occur_time":
item["record_time"],
"value":
abs_terminal_value - (sum - item.get(direction))
})
break
if sum < abs_terminal_value:
occurs.append({
"occur_time": 633801600000,
"value": abs_terminal_value - sum
})
return occurs
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 add_same_and_opposite_subjects_nature_transactionvolume(
session, df_xsz, records, grades, start_time, end_time):
'''
在序时账中添加对方科目/相同方科目/款项性质/交易金额（借方合计数）/合并借贷方名称/交易凭证发生次数合计
:param df_xsz:序时账
:param records:所有凭证记录
:return:
'''
start_time = datetime.strptime(start_time, '%Y-%m-%d')
end_time = datetime.strptime(end_time, '%Y-%m-%d')
df_xsz_new = df_xsz.copy().set_index(
['month', 'vocher_type', 'vocher_num', 'subentry_num'])
df_xsz_new = df_xsz_new.sort_index()
df_xsz_new["same_subjects"] = ""
df_xsz_new["opposite_subjects"] = ""
df_xsz_new["entry_desc"] = ""
df_xsz_new["transaction_volume"] = 0.00
df_xsz_new["entry_classify_count"] = 0
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")
transaction_volume = df_tmp["debit"].sum()
debit_subjects_list = subjects["debit"]
credit_subjects_list = subjects["credit"]
# 合并科目名称
debit_subject_desc = "%".join(debit_subjects_list)
credit_subjects_desc = "%".join(credit_subjects_list)
entry_desc = debit_subject_desc + "@" + credit_subjects_desc
entry_classify = session.query(EntryClassify).filter(
EntryClassify.start_time == start_time,
EntryClassify.end_time == end_time,
EntryClassify.desc == entry_desc,
).one()
entry_classify_count = entry_classify.number
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)
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'transaction_volume'] = transaction_volume
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'entry_desc'] = entry_desc
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'entry_classify_count'] = entry_classify_count
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)
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'transaction_volume'] = transaction_volume
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'entry_desc'] = entry_desc
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'entry_classify_count'] = entry_classify_count
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 save_account_occur_times_to_db(engine, session, start_time, end_time):
'''
将往来款的发生时间放在数据库
:param engine:
:param session:
:param start_time:
:param end_time:
:return:
'''
start_time = datetime.strptime(start_time, '%Y-%m-%d')
end_time = datetime.strptime(end_time, '%Y-%m-%d')
deleteAccountAge(session, start_time, end_time)
# 获取调整分录
df_adjustment = pd.read_sql(
session.query(AduitAdjustment).filter(
AduitAdjustment.year <= end_time.year,
AduitAdjustment.month <= end_time.month).statement, engine)
# 获取以前年度所有的序时账，合并审计调整分录
df_xsz_all = pd.read_sql(
session.query(ChronologicalAccount).filter(
ChronologicalAccount.year <= end_time.year,
ChronologicalAccount.month <= end_time.month).statement, engine)
df_xsz_all = df_xsz_all.append(df_adjustment)
df_xsz_all = df_xsz_all.sort_values(by=['record_time', 'vocher_num'],
ascending=False)
df_xsz_all["search_auxiliary"] = df_xsz_all["auxiliary"].str.replace(
r'[\(\)]+', '')
# 获取当年度的序时账，合并审计调整分录
df_xsz = pd.read_sql(
session.query(ChronologicalAccount).filter(
ChronologicalAccount.year == end_time.year,
ChronologicalAccount.month <= end_time.month).statement, engine)
df_xsz = df_xsz.append(df_adjustment)
df_xsz = df_xsz.sort_values(by=['record_time', 'vocher_num'],
ascending=False)
df_xsz["search_auxiliary"] = df_xsz["auxiliary"].str.replace(
r'[\(\)]+', '')
# 获取科目余额表，并根据序时账重新计算科目余额表
df_subject_balance = pd.read_sql(
session.query(SubjectBalance).filter(
SubjectBalance.start_time == start_time,
SubjectBalance.end_time == end_time,
).statement, engine)
df_subject_balance_new = recaculate_km(df_subject_balance, df_xsz,
"audited")
# 获取辅助核算明细表，并根据序时账重算辅助核算明细表
df_auxiliary = pd.read_sql(
session.query(Auxiliary).filter(
Auxiliary.start_time == start_time,
Auxiliary.end_time == end_time,
).statement, engine)
# 部门辅助核算不包含
df_auxiliary = df_auxiliary[~df_auxiliary["type_name"].str.contains("部门")]
df_auxiliary_new = recaculate_auxiliary(df_auxiliary, df_xsz)
for setting in settings:
df_detail = get_subject_detail(df_subject_balance_new,
df_auxiliary_new, setting["values"])
df_detail["origin_subject"] = setting["name"]
# 获取所有项目的发生时间
for obj in gen_df_line(df_detail):
occur_times = get_occour_times(obj, df_xsz_all)
account_age = AccountAge(
start_time=start_time,
end_time=end_time,
subject_num=obj["subject_num"],
origin_subject=obj["origin_subject"],
subject_name=obj["subject_name"],
source=obj["source"],
direction=obj["direction"],
initial_amount=obj["initial_amount"],
origin_debit=obj["origin_debit"],
origin_credit=obj["origin_credit"],
origin_terminal=obj["origin_terminal"],
origin_terminal_value=obj["origin_terminal_value"],
debit_amount=obj["debit_amount"],
credit_amount=obj["credit_amount"],
terminal_amount=obj["terminal_amount"],
terminal_value=obj["terminal_value"],
occour_times=json.dumps(occur_times, cls=DateEncoder))
session.add(account_age)
session.commit()
def get_profit_distribution(df_km, df_xsz, add_suggestion, start_time,
end_time, session, engine):
'''
分析利润分配科目
:param df_km:
:param df_xsz:
:return:
'''
# 获取标准科目对照表
df_std = pd.read_sql_table('subjectcontrast', engine)
# 分析序时账分别填列
# 获取利润分配的所有明细账
# 获取利润分配的科目编码
subject_num_profit = get_subject_num_by_name("利润分配", df_km)
# 获取利润分配的所有凭证包含借贷方
if subject_num_profit:
df_profit_xsz = get_xsz_by_subject_num(df_xsz,
grade=1,
subject_num=subject_num_profit)
df_this_year_profit_xsz = df_profit_xsz[df_profit_xsz["subject_name_1"]
== "本年利润"]
# 从利润分配凭证中减去本年利润结转类凭证
for obj in gen_df_line(df_this_year_profit_xsz):
df_profit_xsz = df_profit_xsz[~(
(df_profit_xsz["month"] == obj["month"]) &
(df_profit_xsz["vocher_num"] == obj["vocher_num"]) &
(df_profit_xsz["vocher_type"] == obj["vocher_type"]))]
# 检查利润分配-提取盈余公积
# 获取利润分配凭证中的提取盈余公积的凭证
subject_num_reserve = get_subject_num_by_name("盈余公积", df_km)
df_xsz_reserve = pd.DataFrame()
if subject_num_reserve:
df_xsz_reserve = get_xsz_by_subject_num(
df_profit_xsz, grade=1, subject_num=subject_num_reserve)
# 检查利润分配-转为资本
subject_num_paid_up_capital = get_subject_num_by_similar_name(
"实收资本", df_km)
if not subject_num_paid_up_capital:
subject_num_paid_up_capital = get_subject_num_by_similar_name(
"股本", df_km)
subject_num_captial_reserve = get_subject_num_by_name("资本公积", df_km)
subject_num_capital = []
for item in [subject_num_paid_up_capital, subject_num_captial_reserve]:
if item:
subject_num_capital.append(item)
df_xsz_capital = get_xsz_by_subject_num(
df_profit_xsz, grade=1, subject_num=subject_num_capital)
# 扣除调整分录
df_xsz_capital = get_none_adjust_xsz(df_xsz_capital)
# 分配股利
subject_num_ividend_payable = get_subject_num_by_name("应付股利", df_km)
subject_num_cash1 = get_subject_num_by_name("现金", df_km)
subject_num_cash2 = get_subject_num_by_name("库存现金", df_km)
subject_num_bank_deposit = get_subject_num_by_name("银行存款", df_km)
subject_num_dividend = []
for item in [
subject_num_ividend_payable, subject_num_cash1,
subject_num_cash2, subject_num_bank_deposit
]:
if item:
subject_num_dividend.append(item)
df_xsz_dividend = get_xsz_by_subject_num(
df_profit_xsz, grade=1, subject_num=subject_num_dividend)
# 扣除调整分录
df_xsz_dividend = get_none_adjust_xsz(df_xsz_dividend)
# 利润调整1--以前年度损益调整
subject_num_prior_year_income_adjustment = get_subject_num_by_name(
"以前年度损益调整", df_km)
df_xsz_adjustment = pd.DataFrame()
if subject_num_prior_year_income_adjustment:
df_xsz_adjustment = get_xsz_by_subject_num(
df_profit_xsz,
grade=1,
subject_num=subject_num_prior_year_income_adjustment)
df_profit_xsz_adjustment = df_xsz_adjustment[
df_xsz_adjustment["subject_name_1"] == "以前年度损益调整"]
# 利润调整2--未通过损益表项目直接计入本年利润
df_none_profit_and_loss_xsz = get_not_through_profit_and_loss_to_this_year_profit(
df_km, df_xsz, start_time, end_time, add_suggestion, session)
# 本年利润
subject_num_this_year_profit = get_subject_num_by_name("本年利润", df_km)
df_xsz_this_year_profit = pd.DataFrame()
if subject_num_this_year_profit:
df_xsz_this_year_profit = get_xsz_by_subject_num(
df_profit_xsz,
grade=1,
subject_num=subject_num_this_year_profit)
df_has_check = pd.concat([
df_xsz_this_year_profit, df_xsz_adjustment, df_xsz_dividend,
df_xsz_capital, df_xsz_reserve
])
df_has_check = df_has_check[["month", "vocher_num",
"vocher_type"]].drop_duplicates()
df_tmp = df_profit_xsz.copy()
# 获取未检查的可能的调整分录
for obj in gen_df_line(df_has_check):
df_tmp = df_tmp[~((df_tmp["month"] == obj["month"]) &
(df_tmp["vocher_num"] == obj["vocher_num"]) &
(df_tmp["vocher_type"] == obj["vocher_type"]))]
# 利润调整3- -直接计入利润分配科目
df_rest_profit = pd.DataFrame()
if len(df_tmp) > 0:
# 扣除掉损益结转类科目
# （1）如果含有非损益类科目，则不是损益结转凭证
# （2）如果不含有非损益类科目，但损益类科目方向不相反（费用类为借方，收入类为贷方，则认定为调整分录）
profit_and_loss = df_tmp[df_tmp["subject_num"].str.startswith("6")]
profit_and_loss = pd.merge(profit_and_loss,
df_std,
how="inner",
left_on='subject_name_1',
right_on="origin_subject")
for obj in gen_df_line(profit_and_loss):
if (obj["direction"] == "借" and abs(obj["credit"]) > 0) or (
obj["direction"] == "贷" and abs(obj["debit"]) > 0):
df_tmp = df_tmp[~(
(df_tmp["month"] == obj["month"]) &
(df_tmp["vocher_num"] == obj["vocher_num"]) &
(df_tmp["vocher_type"] == obj["vocher_type"]))]
df_rest_profit = df_tmp[df_tmp["subject_name_1"] != "利润分配"]
df_profit_and_other_xsz_adjustment = pd.concat([
df_rest_profit, df_profit_xsz_adjustment,
df_none_profit_and_loss_xsz
])
return df_profit_and_other_xsz_adjustment
else:
raise Exception("公司没有设置利润分配科目")
def add_std_not_exist_subject(df_km, df_std, session):
'''
实现步骤：
按照科目编码对科目余额表进行升序排列
# 资产判断
#如果在流动资产内，则为流动资产
# 如果在所有流动资产之后，在所有非流动资产之前，分类为流动资产
# 如果在非流动资产内，则为非流动资产
# 负债判断
# 同流动资产和非流动资产
# 权益类
# 收入类
# 成本费用类
:param df_km:
:param df_std:
:return:同时修改TBsubject和subject_contrast
'''
df_km = df_km.copy()
df_std = df_std.copy()
# 按照科目编码对科目余额表进行升序排列
df_km = df_km.sort_values(by=['subject_num'])
std_not_match_subject = {"坏账准备", "本年利润", "利润分配", "以前年度损益调整"}
# 获取一级科目余额表，用于检查是否存在未识别的一级科目，且至少在期初/期末或本期借贷方有发生额
df_km_first_subject = get_not_null_df_km(df_km, 1)
# 检查是否有未识别的一级会计科目
df_km_first_subject_merge_std = pd.merge(df_km_first_subject,
df_std,
how="left",
left_on='subject_name',
right_on="origin_subject")
df_km_first_subject_not_match = df_km_first_subject_merge_std[
df_km_first_subject_merge_std['coefficient'].isna()]
diff_subject_names = set(df_km_first_subject_not_match['subject_name'])
res_diff_subject_names = diff_subject_names - std_not_match_subject
if len(res_diff_subject_names) == 0:
# 没有未匹配的以及科目
return
# 遍历每一个标准科目表中不存在的科目，分别增加科目对照表和TBSubject
for obj in gen_df_line(df_km_first_subject_not_match):
if obj["subject_name"] in std_not_match_subject:
continue
index_num = df_km_first_subject_merge_std[
df_km_first_subject_merge_std["subject_name"] ==
obj["subject_name"]].index.values[0]
last_class = df_km_first_subject_merge_std.at[index_num - 1,
"second_class"]
next_class = df_km_first_subject_merge_std.at[index_num + 1,
"second_class"]
if obj["subject_type"] == "资产":
if (next_class == "流动资产") or (next_class == "非流动资产"
and last_class == "流动资产"):
this_class = "流动资产"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 100, TBSubject.order < 200).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加流动资产和总资产
modify_subjects = ["流动资产合计", "资产总计"]
if obj["direction_x"] == "借":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
else:
this_class = "非流动资产"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 200, TBSubject.order < 300).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加非流动资产和总资产
modify_subjects = ["非流动资产合计", "资产总计"]
if obj["direction_x"] == "借":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
first_class = "资产"
elif obj["subject_type"] == "负债":
if (next_class == "流动负债") or (next_class == "非流动负债"
and last_class == "非流动负债"):
this_class = "流动负债"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 300, TBSubject.order < 400).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加流动负债和总负债/负债和所有者权益
modify_subjects = ["流动负债合计", "负债合计", "负债和股东权益总计"]
if obj["direction_x"] == "贷":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
else:
this_class = "非流动负债"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 400, TBSubject.order < 500).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加非流动负债和总负债/负债和所有者权益
modify_subjects = ["非流动负债合计", "负债合计", "负债和股东权益总计"]
if obj["direction_x"] == "贷":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
first_class = "负债"
elif obj["subject_type"] == "权益":
if ("股本" in obj['subject_name']) or ("实收资本"
in obj["subject_name"]):
subjectcontrast = SubjectContrast(
origin_subject=obj["subject_name"],
tb_subject="股本(实收资本）",
fs_subject="股本",
coefficient=1,
direction="贷",
first_class="权益",
second_class="所有者权益")
session.add(subjectcontrast)
session.commit()
continue
this_class = "所有者权益"
first_class = "权益"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 500, TBSubject.order < 600).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加所有者权益和负债和所有者权益
modify_subjects = ["归属于母公司股东权益合计", "股东权益合计", "负债和股东权益总计"]
if obj["direction_x"] == "贷":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
elif obj["subject_type"] == "成本":
this_class = "流动资产"
first_class = "资产"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 100, TBSubject.order < 200).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加流动资产和总资产
modify_subjects = ["流动资产合计", "资产总计"]
if obj["direction_x"] == "借":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
elif obj["subject_type"] == "损益":
if obj["direction"] == "贷":
if (next_class == "收入") or (next_class == "成本费用"):
this_class = "收入"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 600, TBSubject.order < 700).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加 一、营业总收入/三、营业利润（亏损以“－”号填列）/四、利润总额（亏损总额以“－”号填列）/五、净利润（净亏损以“－”号填列）/
# 九、可供分配的利润/ 十、可供投资者分配的利润/ 十一、未分配利润/未分配利润/归属于母公司股东权益合计/股东权益合计/负债和股东权益总计
modify_subjects = [
"一、营业总收入", "三、营业利润（亏损以“－”号填列）", "四、利润总额（亏损总额以“－”号填列）",
"五、净利润（净亏损以“－”号填列）", "九、可供分配的利润", "十、可供投资者分配的利润",
"十一、未分配利润", "未分配利润", "归属于母公司股东权益合计", "股东权益合计",
"负债和股东权益总计"
]
if obj["direction_x"] == "贷":
modify_all_tb(modify_subjects, obj["subject_name"],
"+", session)
else:
modify_all_tb(modify_subjects, obj["subject_name"],
"-", session)
else:
this_class = "收益"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 800, TBSubject.order < 900).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加 三、营业利润（亏损以“－”号填列）/四、利润总额（亏损总额以“－”号填列）/五、净利润（净亏损以“－”号填列）/
# 九、可供分配的利润/ 十、可供投资者分配的利润/ 十一、未分配利润/未分配利润/归属于母公司股东权益合计/股东权益合计/负债和股东权益总计
modify_subjects = [
"三、营业利润（亏损以“－”号填列）", "四、利润总额（亏损总额以“－”号填列）",
"五、净利润（净亏损以“－”号填列）", "九、可供分配的利润", "十、可供投资者分配的利润",
"十一、未分配利润", "未分配利润", "归属于母公司股东权益合计", "股东权益合计",
"负债和股东权益总计"
]
if obj["direction_x"] == "贷":
modify_all_tb(modify_subjects, obj["subject_name"],
"+", session)
else:
modify_all_tb(modify_subjects, obj["subject_name"],
"-", session)
else:
this_class = "成本费用"
tb_obj = session.query(TBSubject).filter(
TBSubject.order > 700, TBSubject.order < 800).order_by(
TBSubject.order.desc()).first()
order = tb_obj.order
# 增加 三、营业利润（亏损以“－”号填列）/四、利润总额（亏损总额以“－”号填列）/五、净利润（净亏损以“－”号填列）/
# 九、可供分配的利润/ 十、可供投资者分配的利润/ 十一、未分配利润/未分配利润/归属于母公司股东权益合计/股东权益合计/负债和股东权益总计
modify_subjects = [
"三、营业利润（亏损以“－”号填列）", "四、利润总额（亏损总额以“－”号填列）",
"五、净利润（净亏损以“－”号填列）", "九、可供分配的利润", "十、可供投资者分配的利润",
"十一、未分配利润", "未分配利润", "归属于母公司股东权益合计", "股东权益合计", "负债和股东权益总计"
]
if obj["direction_x"] == "贷":
modify_all_tb(modify_subjects, obj["subject_name"], "+",
session)
else:
modify_all_tb(modify_subjects, obj["subject_name"], "-",
session)
first_class = "损益"
else:
raise Exception("类型识别错误，请检查类别设置是否正确")
# 增加类别
fs_subject = input("{}请输入报表对应的名称{}".format(
obj["subject_name"], df_std[df_std["second_class"] == this_class]
["fs_subject"].unique()))
subjectcontrast = SubjectContrast(origin_subject=obj["subject_name"],
tb_subject=obj['subject_name'],
fs_subject=fs_subject,
coefficient=1,
direction=obj["direction_x"],
first_class=first_class,
second_class=this_class)
session.add(subjectcontrast)
tbsubject = TBSubject(show=obj["subject_name"],
subject=obj["subject_name"],
direction=obj["direction_x"],
order=order + 1)
session.add(tbsubject)
session.commit()
def check_profit_subject_dirction(df_km, df_xsz, engine, add_suggestion,
start_time, end_time, session):
'''
检查序时账中的损益科目是否核算方向正确，不正确的调整方向
:param df_km: 科目余额表
:param df_xsz: 待修改的序时账
:return: 返回调整后的序时账
'''
# 检查是否所有的损益类项目的核算都正确
# 第一步：获取所有损益类核算项目
# 第二部：获取所有损益类核算项目的标准方向
# 第三部：检查是否存在相反方向核算且不属于损益结转的情况
# 第四步：将核算方向相反的凭证予以调整
df_xsz_new = df_xsz.copy().set_index(
['month', 'vocher_type', 'vocher_num', 'subentry_num'])
# 第一步：获取所有损益类核算项目
df_km_first_subject = df_km[df_km['subject_gradation'] == 1]
df_km_first_subject_profit = df_km_first_subject[
df_km_first_subject["subject_num"].str.startswith('6')]
df_km_first_subject_profit = df_km_first_subject_profit[[
'subject_num', 'subject_name'
]]
# 第二部：获取所有损益类核算项目的标准方向
df_std = pd.read_sql_table('subjectcontrast', engine)
df_km_first_subject_profit_direction = pd.merge(df_km_first_subject_profit,
df_std,
how="inner",
left_on='subject_name',
right_on="origin_subject")
df_km_first_subject_profit_direction = df_km_first_subject_profit_direction[
["subject_num", "subject_name", "direction"]]
# 所有的损益类凭证
df_xsz_profit = df_xsz[df_xsz['subject_name_1'].isin(
df_km_first_subject_profit_direction['subject_name'])]
# 合并损益类凭证列表和损益类核算项目的标准方向
df_xsz_profit = pd.merge(df_xsz_profit,
df_km_first_subject_profit_direction,
how="left",
left_on="subject_name_1",
right_on="subject_name",
suffixes=('_x', ''))
# 获取核算相反的凭证
df_xsz_profit_reverse = df_xsz_profit[(
(df_xsz_profit["direction"] == "借") &
(df_xsz_profit["credit"].abs() > 1e-5)) | (
(df_xsz_profit["direction"] == "贷") &
(df_xsz_profit["debit"].abs() > 1e-5))]
df_xsz_profit_reverse_record = df_xsz_profit_reverse[[
"month", "vocher_num", "vocher_type"
]].drop_duplicates()
# 获取相反凭证的记录
reverse_records = df_xsz_profit_reverse_record.to_dict('records')
# 获取相反凭证的完整借贷方
df_xsz_profit_reverse = pd.merge(df_xsz,
df_xsz_profit_reverse_record,
how="inner",
on=["month", "vocher_num", "vocher_type"])
# 第三部：检查是否存在相反方向核算且不属于损益结转的情况
# 检查每一笔相反凭证中是否包含本年利润，如果包含则是正常的结转损益，不用理会
for record in reverse_records:
df_tmp = df_xsz_profit_reverse[
(df_xsz_profit_reverse["month"] == record["month"])
& (df_xsz_profit_reverse["vocher_num"] == record["vocher_num"])
& (df_xsz_profit_reverse["vocher_type"] == record["vocher_type"])]
# 检查凭证中是否包含本年利润，如果不包含则调整序时账
if (not df_tmp["subject_name_1"].str.contains(
'本年利润', regex=False).any()) and (
not df_tmp["subject_name_1"].str.contains(
'利润分配', regex=False).any()):
# 第四部：修改序时账
# 合并标准方向
df_tmp = pd.merge(df_tmp,
df_km_first_subject_profit_direction,
left_on="subject_num_1",
right_on="subject_num",
how="left",
suffixes=("", "_y"))
for obj in gen_df_line(df_tmp):
if obj['direction'] == "借" and abs(obj['credit']) > 1e-5:
# 借贷方金额互换
tmp = df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'],
obj['subentry_num']), 'credit']
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'debit'] = -tmp
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'credit'] = 0.00
tmp1 = df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'],
obj['subentry_num']),
'credit_foreign_currency']
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'debit_foreign_currency'] = -tmp1
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'credit_foreign_currency'] = 0.00
# 提建议
add_suggestion(
kind="会计处理",
content=
"{}年{}月{}-{}号凭证损益类项目记账不符合规范，建议收入类科目发生时计入贷方，费用类项目发生时计入借方"
.format(obj['year'], obj['month'], obj['vocher_type'],
obj['vocher_num']),
start_time=start_time,
end_time=end_time,
session=session)
elif obj['direction'] == "贷" and abs(obj['debit']) > 1e-5:
# 借贷方金额互换
tmp = df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'],
obj['subentry_num']), 'debit']
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'credit'] = -tmp
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'debit'] = 0.00
tmp1 = df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'],
obj['subentry_num']),
'debit_foreign_currency']
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'credit_foreign_currency'] = -tmp1
df_xsz_new.loc[(obj['month'], obj['vocher_type'],
obj['vocher_num'], obj['subentry_num']),
'debit_foreign_currency'] = 0.00
# 提建议
add_suggestion(
kind="会计处理",
content=
"{}年{}月{}-{}号凭证损益类项目记账不符合规范，建议收入类科目发生时计入贷方，费用类项目发生时计入借方"
.format(obj['year'], obj['month'], obj['vocher_type'],
obj['vocher_num']),
start_time=start_time,
end_time=end_time,
session=session)
df_xsz_new = df_xsz_new.reset_index()
return df_xsz_new