def fileCloumnDuplicateCheck(file_name):
book = pd.read_excel(file_name)
columns_list = list(book.columns)
for name in columns_list:
if ".1" in name or ".2" in name or ".3" in name:
raise DataFormatError("文件的标题列有重复项，请检查！")
def fileTitleCheck(file_name, standrad_list):
book = pd.read_excel(file_name)
need_check_list = list(book.columns)
# 判断标准列表中的元素是否是导入文件第一列的子集，如果是，说明含有必须项
res = set(standrad_list).issubset(need_check_list)
if not res:
raise DataFormatError("文件标题列含有非法值或者你选择导入的文件不正确，请检查。")
def fileCLoumnsCheck(file_name):
book = pd.read_excel(file_name)
list_need_check_na = [
"员工编号", "人员编号", "身份证号码", "银行账号", "工资项", "变更后岗位系数", "证件号码", "个人月缴额",
"身份证号", "个人编号", "缴费基数", "证照号码", "工号", "身份信息", "部室", "民族", "员工子组名称",
"SAP部门代码"
]
list_need_check_dumplicate = [
"员工编号", "人员编号", "身份证号码", "银行账号", "证件号码", "身份证号", "个人编号", "证照号码",
"工号"
]
list_need_check_dumplicate2 = [
"人员编号", "银行账号", "身份证号码", "证件号码", "身份证号", "个人编号", "证照号码", "工号"
]
# 如果表中含有工资项这一列，那么允许员工编号重复，因为一个员工可能会导入多个工资项
if "工资项" in book.columns:
for str_col_name in list_need_check_dumplicate2:
if str_col_name in book.columns:
# 检查是否有重复
if book[str_col_name].duplicated().any():
raise DataFormatError(
"文件内{}这一列含有重复项，请检查。".format(str_col_name))
else:
for str_col_name in list_need_check_dumplicate:
if str_col_name in book.columns:
# 检查是否有重复
if book[str_col_name].duplicated().any():
raise DataFormatError(
"文件内{}这一列含有重复项，请检查。".format(str_col_name))
for str_col_name in list_need_check_na:
if str_col_name in book.columns:
# 检查是否有空值
if book[str_col_name].isna().any():
raise DataFormatError(
"文件内{}这一列含有空白值，请检查。".format(str_col_name))
def dfChangeDataFormat(self, df_bouns):
list_need_change_format = ['应发奖金', '其他补发', '其他补扣', '累计收入额',
'累计减除费用', '累计子女教育', '累计赡养老人', '累计继续教育',
'累计住房贷款利息', '累计住房租金', '累计专项扣除', '累计已预缴税额', '工资已缴税额']
try:
for str_col_name in list_need_change_format:
if str_col_name in df_bouns.columns:
df_bouns[str_col_name] = df_bouns[str_col_name].astype(float)
except ValueError as e:
raise DataFormatError(r"你所导入的文件当中，应该为全部为数值的某一列含有字母、字符等无法进行计算的内容，请核实。具体错误信息如下：{}"
.format(str(e)))
else:
return df_bouns
def excelSplit(self):
str_file_name = QFileDialog.getOpenFileName(self.window, "请选择拆分文件",
'./')[0]
str_file_path = QFileDialog.getExistingDirectory(
self.window, "请选择拆分到那个文件夹", './')
print(str_file_path)
if str_file_name != "" and str_file_path != "":
df_all_detail = pd.read_excel(str_file_name)
res = self.boolCheckTitle(df_all_detail)
if res:
list_dept_neme = df_all_detail["部室"].unique()
for item in list_dept_neme:
df_dept = df_all_detail[df_all_detail["部室"] == item]
df_dept.to_excel(str_file_path + "/" +
item.replace("\n", "") + ".xlsx")
else:
raise DataFormatError("拆分文件和拆分后文件夹必须指定，否则无法拆分！")
def dfChangeDataFormat(self, df_wage):
df_wage.fillna("0", inplace=True)
list_need_change_format = [
'岗序', '薪等', '岗位工资系数', '奖金系数', '预支年薪', '岗位工资', '技能保留工资', '年功保留工资',
'竞业津贴', '技术津贴', '回民补贴', '女工洗理费', '住房补贴', '工作补贴', '交通补贴', '班中餐补贴',
'误餐补贴', '通讯补贴', '异地津贴', '异地差旅费', '外租房津贴', '其他补发', '其他补扣',
'养老保险员工实缴', '失业保险员工实缴', '医疗保险员工实缴', '住房公积金员工实缴', '养老保险-个人补缴',
'失业保险-个人补缴', '医疗保险-个人补缴', '住房公积金-个人补缴', '财务计税附加', '累计收入额',
'累计减除费用', '累计专项扣除', '累计子女教育', '累计赡养老人', '累计继续教育', '累计住房贷款利息',
'累计住房租金', '年假天数', '事假天数', '病假天数', '工伤假天数', '探亲假天数', '婚假天数', '丧假天数',
'丧假天数', '产假天数', '育儿假天数', '节育假天数', '旷工天数', '高温出勤', '有毒有害出勤',
'工作补贴出勤', '节假日加班', "加班天数", '中班天数', '夜班天数', "上月岗位工资系数", "累计应补(退)税额",
"累计已预缴税额"
]
# list_need_change_format = ['岗序', '薪等', '岗位工资系数', '奖金系数', '预支年薪',
# '岗位工资', '技能保留工资', '年功保留工资', '竞业津贴', '技术津贴',
# '回民补贴', '女工洗理费', '住房补贴', '工作补贴', '交通补贴',
# '班中餐补贴', '误餐补贴', '通讯补贴', '异地津贴', '异地差旅费',
# '外租房津贴', '其他补发', '其他补扣',
# '养老保险员工实缴', '失业保险员工实缴',
# '医疗保险员工实缴', '住房公积金员工实缴',
# '养老保险-个人补缴', '失业保险-个人补缴', '医疗保险-个人补缴',
# '住房公积金-个人补缴', '累计收入额', '累计减除费用',
# '累计专项扣除', '累计子女教育', '累计赡养老人', '累计继续教育',
# '累计住房贷款利息', '累计住房租金', '年假天数', '事假天数', '病假天数',
# '工伤假天数', '探亲假天数', '婚假天数', '丧假天数', '丧假天数',
# '产假天数', '育儿假天数', '节育假天数', '旷工天数', '高温出勤',
# '有毒有害出勤', '工作补贴出勤', '节假日加班', '中班天数', '夜班天数', "上月岗位工资系数"
# , "累计应补(退)税额", "累计已预缴税额"]
try:
for str_col_name in list_need_change_format:
if str_col_name in df_wage.columns:
df_wage[str_col_name] = df_wage[str_col_name].astype(float)
except ValueError as e:
raise DataFormatError(
r"你所导入的文件当中，应该为全部为数值的某一列含有字母、字符等无法进行计算的内容，请核实。具体错误信息如下：{}".
format(str(e)))
else:
return df_wage
def fileNameCheck(file_name):
file_name = ".".join([s.lower() for s in file_name.split(".")])
if not (file_name.endswith("xlsx") or file_name.endswith("xls")):
raise DataFormatError("文件扩展名不正确，应为'xlsx'或者'xls'!")
def wageDataCheck(self, df_wage):
# 一、检查各项保险数值与社保系统中的数据是否相符
float_endowment_insurance = df_wage["养老保险员工实缴"].sum().round(2)
float_unemployement_insurance = df_wage["失业保险员工实缴"].sum().round(2)
float_medical_insurance = df_wage["医疗保险员工实缴"].sum().round(2)
float_hourse_insurance = df_wage["住房公积金员工实缴"].sum().round(2)
float_big_ill_insurance = df_wage["大额保险-个人"].sum().round(2)
float_tex = df_wage["个人所得税"].sum()
float_endowment_insurance_extra = df_wage["养老保险-个人补缴"].sum().round(2)
float_unemployement_insurance_extra = df_wage["失业保险-个人补缴"].sum().round(
2)
float_medical_insurance_extra = df_wage["医疗保险-个人补缴"].sum().round(2)
float_hourse_insurance_extra = df_wage["住房公积金-个人补缴"].sum().round(2)
# 下面将数据库中各类保险数值导入计算
# 养老
df_endowment_insurance = pd.read_sql_query(
"select 个人缴 from t{}EndowmentInsuranceDetail".format(self.date),
self.conn)
float_endowment_insurance_2 = df_endowment_insurance["个人缴"].astype(
float).sum()
# 失业
df_unenployement_insurance = pd.read_sql_query(
"select 个人缴 from t{}UnemployementInsuranceDetail".format(
self.date), self.conn)
float_unemployement_insurance_2 = df_unenployement_insurance[
"个人缴"].astype(float).sum()
# 医疗
df_medical_insurance = pd.read_sql_query(
"select 个人缴 from t{}MedicalInsuranceDetail".format(self.date),
self.conn)
float_medical_insurance_2 = df_medical_insurance["个人缴"].astype(
float).sum()
# 公积金
df_hourse_insurance = pd.read_sql_query(
"select 个人月缴额 from t{}HouseInsuranceDetail".format(self.date),
self.conn)
float_hourse_insurance_2 = df_hourse_insurance["个人月缴额"].astype(
float).sum()
# 各类补缴
df_extra_insurance = pd.read_sql_query(
"select 金额 from t{}InsuranceExtraPayment".format(self.date),
self.conn)
float_extra_insurance = df_extra_insurance["金额"].astype(float).sum()
list_all_wage_lower_then_0 = df_wage.loc[df_wage["应发工资"] < 0,
"员工编号"].values
list_in_hand_wage_lower_then_0 = df_wage.loc[df_wage["实发工资"] < 0,
"员工编号"].values
# todo 应发工资小于0会造成实发工资小于0，进而造成工资汇总表上应发-各项扣缴！=实发工资，需要进行检查处理
if len(list_all_wage_lower_then_0) > 0:
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，以下员工{}的应发工资小于0，请检查后重新生成工资明细。".format(
list_all_wage_lower_then_0))
# todo 实发工资不能小于0，应该为错误，进行检查
if len(list_in_hand_wage_lower_then_0) > 0:
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，以下员工{}的实发工资小于0，请检查后重新生成工资明细以避免汇总表数据与明细表不一致。"
.format(list_in_hand_wage_lower_then_0))
df_wage_extra_insurance = (float_endowment_insurance_extra +
float_unemployement_insurance_extra +
float_medical_insurance_extra +
float_hourse_insurance_extra).round(2)
if df_wage_extra_insurance != float_extra_insurance:
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，工资明细表中的保险补缴额{}与保险补缴导入文件中的金额{}不一致，请查明原因。".
format(df_wage_extra_insurance, float_extra_insurance))
if float_endowment_insurance.round(
) != float_endowment_insurance_2.round():
print("养老", float_endowment_insurance.round(),
float_endowment_insurance_2.round())
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，工资明细表中的养老保险缴纳数为{}与养老保险缴纳明细表中的金额{}不一致，请查明原因。"
.format(float_endowment_insurance,
float_endowment_insurance_2))
elif float_unemployement_insurance.round(
) != float_unemployement_insurance_2.round():
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，工资明细表中的失业保险缴纳数{}与失业保险缴纳明细表中的金额{}不一致，请查明原因。"
.format(float_unemployement_insurance,
float_unemployement_insurance_2))
elif float_medical_insurance.round(
) != float_medical_insurance_2.round():
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，工资明细表中的医疗保险缴纳数{}与医疗保险缴纳明细表中的金额{}不一致，请查明原因。"
.format(float_medical_insurance, float_medical_insurance_2))
elif float_hourse_insurance.round() != float_hourse_insurance_2.round(
):
raise DataFormatError(
"你可以继续生成明细表，汇总表。但请注意，工资明细表中的公积金缴纳数{}与公积金缴纳明细表中的金额{}不一致，请查明原因。".
format(float_hourse_insurance, float_hourse_insurance_2))
def dfGetDataFromSql(self):
# 第一步 检查数据库中各项表的数据是否合乎要求
# 标准表导入，检查
try:
# 导入标准表
df_standrad = pd.read_sql_query(
r"select * from t{}StandradTable".format(self.date), self.conn)
# 导入工资补发补扣
df_wage_extra_workincome = \
pd.read_sql_query(r"select * from t{}ExtraWorkincome".format(self.date), self.conn) \
[["员工编号", "其他补发", "其他补扣"]]
# 导入各类保险,并把名称统一
df_endowment_insurance_roster = \
pd.read_sql_query(r"select * from t{}EndowmentInsuranceRoster".format(self.date), self.conn) \
[["个人编号", "证件号码"]]
df_endowment_insurance_roster.rename(columns={
"证件号码": "身份证号码",
"个人编号": "社保个人编号"
},
inplace=True)
df_endowment_insurance_detail = \
pd.read_sql_query(r"select * from t{}EndowmentInsuranceDetail".format(self.date), self.conn) \
[["个人编号", "个人缴"]]
df_endowment_insurance_detail.rename(columns={
"个人缴": "养老保险员工实缴",
"个人编号": "社保个人编号"
},
inplace=True)
df_unemployement_detail = \
pd.read_sql_query(r"select * from t{}UnemployementInsuranceDetail".format(self.date), self.conn) \
[["个人编号", "个人缴"]]
df_unemployement_detail.rename(columns={
"个人缴": "失业保险员工实缴",
"个人编号": "社保个人编号"
},
inplace=True)
df_medical_insurance_roster = \
pd.read_sql_query(r"select * from t{}MedicalInsuranceRoster".format(self.date), self.conn) \
[["个人编号", "证件号码"]]
df_medical_insurance_roster.rename(columns={
"证件号码": "身份证号码",
"个人编号": "医保个人编号"
},
inplace=True)
df_medical_insurance_detail = \
pd.read_sql_query(r"select * from t{}MedicalInsuranceDetail".format(self.date), self.conn) \
[["个人编号", "个人缴"]]
df_medical_insurance_detail.rename(columns={
"个人缴": "医疗保险员工实缴",
"个人编号": "医保个人编号"
},
inplace=True)
df_house_insurance_Detail = \
pd.read_sql_query(r"select * from t{}HouseInsuranceDetail".format(self.date), self.conn) \
[["证件号码", "个人月缴额"]]
df_house_insurance_Detail.rename(columns={
"证件号码": "身份证号码",
"个人月缴额": "住房公积金员工实缴"
},
inplace=True)
df_civil_servants_insurance_detail = \
pd.read_sql_query(r"select * from t{}CivilServantsInsuranceDetail".format(self.date), self.conn) \
[["个人编号", "险种类型"]]
df_civil_servants_insurance_detail.rename(columns={"个人编号": "医保个人编号", "险种类型": "是否享受公务员医疗待遇"}, \
inplace=True)
# 保险补缴信息
df_insurance_extra_payment = pd.read_sql_query(
r"select * from t{}InsuranceExtraPayment".format(self.date),
self.conn)
df_endowment_insurance_extra_payment = \
df_insurance_extra_payment[df_insurance_extra_payment["工资项"] == "4102"][["员工编号", "金额"]]
df_endowment_insurance_extra_payment.rename(
columns={"金额": "养老保险-个人补缴"}, inplace=True)
df_unemployement_insurance_extra_payment = \
df_insurance_extra_payment[df_insurance_extra_payment["工资项"] == "4122"][["员工编号", "金额"]]
df_unemployement_insurance_extra_payment.rename(
columns={"金额": "失业保险-个人补缴"}, inplace=True)
df_medical_insurance_extra_payment = \
df_insurance_extra_payment[df_insurance_extra_payment["工资项"] == "4112"][["员工编号", "金额"]]
df_medical_insurance_extra_payment.rename(
columns={"金额": "医疗保险-个人补缴"}, inplace=True)
df_house_insurance_extra_payment = \
df_insurance_extra_payment[df_insurance_extra_payment["工资项"] == "4092"][["员工编号", "金额"]]
df_house_insurance_extra_payment.rename(
columns={"金额": "住房公积金-个人补缴"}, inplace=True)
# 财务计税附加
"""因为计税附加可能含有某一个职工多个工资项均有计数，所以需要把金额累计起来，因为数据库内各列格式均为str
所以先把金额转换格式，然后按员工编号分组求和"""
df_tex_extra = pd.read_sql_query(
r"select * from t{}TaxExtra".format(self.date),
self.conn)[["员工编号", "工资项", "金额"]]
df_tex_extra.rename(columns={"金额": "财务计税附加"}, inplace=True)
df_tex_extra["财务计税附加"] = df_tex_extra["财务计税附加"].astype(float)
df_tex_extra = df_tex_extra.groupby("员工编号").sum()
# 上月缴税明细信息导入
df_last_month_tax_detail = \
pd.read_sql_query(r"select * from t{}LastMonthTaxDetail".format(self.date), self.conn) \
[["证照号码", "累计收入额", "累计减除费用", "累计专项扣除", "累计应补(退)税额", "累计已预缴税额"]]
df_last_month_tax_detail.rename(columns={"证照号码": "身份证号码"},
inplace=True)
# 本月的个人附加扣除信息
df_tex_remove = pd.read_sql_query(r"select * from t{}TaxRemove".format(self.date), self.conn) \
[["证照号码", "累计子女教育", "累计赡养老人", "累计继续教育", "累计住房贷款利息", "累计住房租金"]]
df_tex_remove.rename(columns={"证照号码": "身份证号码"}, inplace=True)
# 出勤信息导入
df_sap_workday = pd.read_sql_query(r"select * from t{}SapWorkday".format(self.date), self.conn) \
[["人员编号", "年假天数", "事假天数", "病假天数", "工伤假天数", "探亲假天数", "婚假天数", "丧假天数", "产假天数",
"育儿假天数", "节育假天数", "旷工天数", "高温出勤", "有毒有害出勤", "工作补贴出勤", "节假日加班", "加班天数", "中班天数", "夜班天数"]]
df_sap_workday.rename(columns={"人员编号": "员工编号"}, inplace=True)
df_people_info = pd.read_sql_query(r"select * from t{}PeopleInfo".format(self.date), self.conn) \
[["员工编号", "部室", "员工子组名称", "性别", "合同起始时间"]]
# 导入上月工资明细
df_last_month_wage_detail = \
pd.read_sql_query(r"select * from t{}LastMonthWageDetail".format(self.date), self.conn) \
[["员工编号", "岗位工资系数"]]
df_last_month_wage_detail.rename(columns={"岗位工资系数": "上月岗位工资系数"},
inplace=True)
except pd.io.sql.DatabaseError as e:
raise DataFormatError(
r"没有在数据库中找到所需的数据表，请确认是否已经导入。具体错误信息如下：{}".format(str(e)))
except ValueError as e:
raise DataFormatError(
r"你所导入的文件当中，应该为数字的某一列含有字母、字符等无法进行计算的内容，请核实。具体错误信息如下：{}".format(
str(e)))
else:
# 各种表依次整合，首先是标准表和补发补扣
df_return = pd.merge(df_standrad,
df_wage_extra_workincome,
on="员工编号",
how="left")
# 其次是养老保险花名册
df_return = pd.merge(df_return,
df_endowment_insurance_roster,
on="身份证号码",
how="left")
# 养老保险明细
df_return = pd.merge(df_return,
df_endowment_insurance_detail,
on="社保个人编号",
how="left")
# 失业保险明细
df_return = pd.merge(df_return,
df_unemployement_detail,
on="社保个人编号",
how="left")
# 医疗保险花名册
df_return = pd.merge(df_return,
df_medical_insurance_roster,
on="身份证号码",
how="left")
# 医疗保险明细
df_return = pd.merge(df_return,
df_medical_insurance_detail,
on="医保个人编号",
how="left")
# 公积金明细
df_return = pd.merge(df_return,
df_house_insurance_Detail,
on="身份证号码",
how="left")
# 公务员明细
df_return = pd.merge(df_return,
df_civil_servants_insurance_detail,
on="医保个人编号",
how="left")
# 养老保险补缴
df_return = pd.merge(df_return,
df_endowment_insurance_extra_payment,
on="员工编号",
how="left")
# 失业保险补缴
df_return = pd.merge(df_return,
df_unemployement_insurance_extra_payment,
on="员工编号",
how="left")
# 医疗保险补缴
df_return = pd.merge(df_return,
df_medical_insurance_extra_payment,
on="员工编号",
how="left")
# 住房公积金补缴
df_return = pd.merge(df_return,
df_house_insurance_extra_payment,
on="员工编号",
how="left")
# 财务计税附加
df_return = pd.merge(df_return,
df_tex_extra,
on="员工编号",
how="left")
# 上月计税信息（累计收入额、累计减除、累计专项扣除）
df_return = pd.merge(df_return,
df_last_month_tax_detail,
on="身份证号码",
how="left")
# 本月个人附加信息
df_return = pd.merge(df_return,
df_tex_remove,
on="身份证号码",
how="left")
# sap出勤
df_return = pd.merge(df_return,
df_sap_workday,
on="员工编号",
how="left")
# 人员信息
df_return = pd.merge(df_return,
df_people_info,
on="员工编号",
how="left")
# 导入上月的工资系数
df_return = pd.merge(df_return,
df_last_month_wage_detail,
on="员工编号",
how="left")
return df_return
def excelWageSummary(self, str_file_name):
df_wage_detail = pd.read_sql_query(
r"select * from wage{}sql".format(self.date), self.conn)
dict_people_count = df_wage_detail["员工子组名称"].value_counts().to_dict()
int_high_position_count = dict_people_count["经营管理-高层"]
int_middle_position_count = dict_people_count["经营管理-中层"]
int_other_position_count = dict_people_count["经营管理-其他"]
int_technology_people_count = dict_people_count["专业技术人员"]
int_produce_people_count = dict_people_count["生产操作人员"]
int_extra_people_count = dict_people_count["0"]
# 岗位工资
float_JingYingGuanLi_GWGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["预支年薪", "岗位工资", "技能保留工资", "年功保留工资"]].sum().sum()
float_ZHuanYeJiSHu_GWGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员",
["预支年薪", "岗位工资", "技能保留工资", "年功保留工资"]].sum().sum()
float_ShengChanCaoZuo_GWGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员",
["预支年薪", "岗位工资", "技能保留工资", "年功保留工资"]].sum().sum()
float_QiTaRenYuan_GWGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0",
["预支年薪", "岗位工资", "技能保留工资", "年功保留工资"]].sum().sum()
# 加班工资
float_JingYingGuanLi_JBGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
"加班工资"].sum().sum()
float_ZHuanYeJiSHu_JBGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", "加班工资"].sum().sum()
float_ShengChanCaoZuo_JBGZ = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", "加班工资"].sum().sum()
float_QiTaRenYuan_JBGZ = df_wage_detail.loc[df_wage_detail["员工子组名称"] ==
"0", "加班工资"].sum().sum()
# 岗位补贴
float_JingYingGuanLi_GWBT = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["竞业津贴", "技术津贴", "回民补贴", "女工洗理费", "住房补贴", "中班津贴", "夜班津贴"]].sum(
).sum()
float_ZHuanYeJiSHu_GWBT = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员",
["竞业津贴", "技术津贴", "回民补贴", "女工洗理费", "住房补贴", "中班津贴", "夜班津贴"]].sum(
).sum()
float_ShengChanCaoZuo_GWBT = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员",
["竞业津贴", "技术津贴", "回民补贴", "女工洗理费", "住房补贴", "中班津贴", "夜班津贴"]].sum(
).sum()
float_QiTaRenYuan_GWBT = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0",
["竞业津贴", "技术津贴", "回民补贴", "女工洗理费", "住房补贴", "中班津贴", "夜班津贴"]].sum(
).sum()
# 补发
float_JingYingGuanLi_BuFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
"其他补发"].sum().sum()
float_ZHuanYeJiSHu_BuFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", "其他补发"].sum().sum()
float_ShengChanCaoZuo_BuFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", "其他补发"].sum().sum().round(2)
float_QiTaRenYuan_BuFa = df_wage_detail.loc[df_wage_detail["员工子组名称"] ==
"0", "其他补发"].sum().sum()
# 补扣
float_JingYingGuanLi_BuKou = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
"其他补扣"].sum().sum()
float_ZHuanYeJiSHu_BuKou = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", "其他补扣"].sum().sum()
float_ShengChanCaoZuo_BuKou = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", "其他补扣"].sum().sum()
float_QiTaRenYuan_BuKou = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", "其他补扣"].sum().sum()
# 班中餐补贴
float_JingYingGuanLi_BanZHongCan = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["班中餐补贴", "误餐补贴"]].sum().sum()
float_ZHuanYeJiSHu_BanZHongCan = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员",
["班中餐补贴", "误餐补贴"]].sum().sum()
float_ShengChanCaoZuo_BanZHongCan = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员",
["班中餐补贴", "误餐补贴"]].sum().sum()
float_QiTaRenYuan_BanZHongCan = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", ["班中餐补贴", "误餐补贴"]].sum().sum()
# 交通补贴
float_JingYingGuanLi_JiaoTong = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["交通补贴"]].sum().sum()
float_ZHuanYeJiSHu_JiaoTong = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", ["交通补贴"]].sum().sum()
float_ShengChanCaoZuo_JiaoTong = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", ["交通补贴"]].sum().sum()
float_QiTaRenYuan_JiaoTong = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", ["交通补贴"]].sum().sum()
# 异地津贴
float_JingYingGuanLi_YiDiJinTie = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["异地津贴", "工作补贴"]].sum().sum()
float_ZHuanYeJiSHu_YiDiJinTie = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员",
["异地津贴", "工作补贴"]].sum().sum()
float_ShengChanCaoZuo_YiDiJinTie = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员",
["异地津贴", "工作补贴"]].sum().sum()
float_QiTaRenYuan_YiDiJinTie = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", ["异地津贴", "工作补贴"]].sum().sum()
# 异地差旅费
float_JingYingGuanLi_YiDiCHaiLueFei = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["异地差旅费"]].sum().sum()
float_ZHuanYeJiSHu_YiDiCHaiLueFei = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", ["异地差旅费"]].sum().sum()
float_ShengChanCaoZuo_YiDiCHaiLueFei = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", ["异地差旅费"]].sum().sum()
float_QiTaRenYuan_YiDiCHaiLueFei = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", ["异地差旅费"]].sum().sum()
# 外租房
float_JingYingGuanLi_WaiZuFang = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["外租房津贴"]].sum().sum()
float_ZHuanYeJiSHu_WaiZuFang = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", ["外租房津贴"]].sum().sum()
float_ShengChanCaoZuo_WaiZuFang = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", ["外租房津贴"]].sum().sum()
float_QiTaRenYuan_WaiZuFang = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", ["外租房津贴"]].sum().sum()
# 应发工资
float_JingYingGuanLi_YingFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"].isin(["经营管理-高层", "经营管理-中层", "经营管理-其他"]),
["应发工资"]].sum().sum()
float_ZHuanYeJiSHu_YingFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "专业技术人员", ["应发工资"]].sum().sum()
float_ShengChanCaoZuo_YingFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "生产操作人员", ["应发工资"]].sum().sum()
float_QiTaRenYuan_YingFa = df_wage_detail.loc[
df_wage_detail["员工子组名称"] == "0", ["应发工资"]].sum().sum()
# 各类保险
float_endowment_insurance = df_wage_detail["养老保险员工实缴"].sum()
float_unemployement_insurance = df_wage_detail["失业保险员工实缴"].sum()
float_medical_insurance = df_wage_detail["医疗保险员工实缴"].sum()
float_hourse_insurance = df_wage_detail["住房公积金员工实缴"].sum()
float_big_ill_insurance = df_wage_detail["大额保险-个人"].sum()
float_tex = df_wage_detail["个人所得税"].sum()
float_endowment_insurance_extra = df_wage_detail["养老保险-个人补缴"].sum()
float_unemployement_insurance_extra = df_wage_detail["失业保险-个人补缴"].sum()
float_medical_insurance_extra = df_wage_detail["医疗保险-个人补缴"].sum()
float_hourse_insurance_extra = df_wage_detail["住房公积金-个人补缴"].sum()
float_in_hand_money = df_wage_detail["实发工资"].sum()
workbook = openpyxl.load_workbook(str_file_name, data_only=True)
worksheet = workbook.active
worksheet["a1"] = "河钢乐亭钢铁有限公司{}工资汇总表".format(self.date[:4] + "年" +
self.date[4:] + "月")
# 各类人数
worksheet[
"c6"] = int_high_position_count + int_middle_position_count + int_other_position_count
worksheet["c7"] = int_technology_people_count
worksheet["c8"] = int_produce_people_count
worksheet["c18"] = int_extra_people_count
worksheet["c5"] = worksheet["c6"].value + worksheet[
"c7"].value + worksheet["c8"].value + worksheet["c18"].value
# 基础工资
worksheet["d6"] = float_JingYingGuanLi_GWGZ
worksheet["d7"] = float_ZHuanYeJiSHu_GWGZ
worksheet["d8"] = float_ShengChanCaoZuo_GWGZ
worksheet["d18"] = float_QiTaRenYuan_GWGZ
worksheet["d5"] = worksheet["d6"].value + worksheet[
"d7"].value + worksheet["d8"].value + worksheet["d18"].value
# 加班工资
worksheet["e6"] = float_JingYingGuanLi_JBGZ
worksheet["e7"] = float_ZHuanYeJiSHu_JBGZ
worksheet["e8"] = float_ShengChanCaoZuo_JBGZ
worksheet["e18"] = float_QiTaRenYuan_JBGZ
worksheet["e5"] = worksheet["e6"].value + worksheet[
"e7"].value + worksheet["e8"].value + worksheet["e18"].value
# 岗位补贴
worksheet["f6"] = float_JingYingGuanLi_GWBT
worksheet["f7"] = float_ZHuanYeJiSHu_GWBT
worksheet["f8"] = float_ShengChanCaoZuo_GWBT
worksheet["f18"] = float_QiTaRenYuan_GWBT
worksheet["f5"] = worksheet["f6"].value + worksheet[
"f7"].value + worksheet["f8"].value + worksheet["f18"].value
# 补发工资
worksheet["g6"] = float_JingYingGuanLi_BuFa
worksheet["g7"] = float_ZHuanYeJiSHu_BuFa
worksheet["g8"] = float_ShengChanCaoZuo_BuFa
worksheet["g18"] = float_QiTaRenYuan_BuFa
worksheet["g5"] = worksheet["g6"].value + worksheet[
"g7"].value + worksheet["g8"].value + worksheet["g18"].value
# 补扣
worksheet["h6"] = float_JingYingGuanLi_BuKou
worksheet["h7"] = float_ZHuanYeJiSHu_BuKou
worksheet["h8"] = float_ShengChanCaoZuo_BuKou
worksheet["h18"] = float_QiTaRenYuan_BuKou
worksheet["h5"] = worksheet["h6"].value + worksheet[
"h7"].value + worksheet["h8"].value + worksheet["h18"].value
# 班中餐，含误餐
worksheet["i6"] = float_JingYingGuanLi_BanZHongCan
worksheet["i7"] = float_ZHuanYeJiSHu_BanZHongCan
worksheet["i8"] = float_ShengChanCaoZuo_BanZHongCan
worksheet["i18"] = float_QiTaRenYuan_BanZHongCan
worksheet["i5"] = worksheet["i6"].value + worksheet[
"i7"].value + worksheet["i8"].value + worksheet["i18"].value
# 交通补贴
worksheet["j6"] = float_JingYingGuanLi_JiaoTong
worksheet["j7"] = float_ZHuanYeJiSHu_JiaoTong
worksheet["j8"] = float_ShengChanCaoZuo_JiaoTong
worksheet["j18"] = float_QiTaRenYuan_JiaoTong
worksheet["j5"] = worksheet["j6"].value + worksheet[
"j7"].value + worksheet["j8"].value + worksheet["j18"].value
# 异地津补贴（含工作补贴）
worksheet["k6"] = float_JingYingGuanLi_YiDiJinTie
worksheet["k7"] = float_ZHuanYeJiSHu_YiDiJinTie
worksheet["k8"] = float_ShengChanCaoZuo_YiDiJinTie
worksheet["k18"] = float_QiTaRenYuan_YiDiJinTie
worksheet["k5"] = worksheet["k6"].value + worksheet[
"k7"].value + worksheet["k8"].value + worksheet["k18"].value
# 异地差旅费
worksheet["l6"] = float_JingYingGuanLi_YiDiCHaiLueFei
worksheet["l7"] = float_ZHuanYeJiSHu_YiDiCHaiLueFei
worksheet["l8"] = float_ShengChanCaoZuo_YiDiCHaiLueFei
worksheet["l18"] = float_QiTaRenYuan_YiDiCHaiLueFei
worksheet["l5"] = worksheet["l6"].value + worksheet[
"l7"].value + worksheet["l8"].value + worksheet["l18"].value
# 外租房
worksheet["m6"] = float_JingYingGuanLi_WaiZuFang
worksheet["m7"] = float_ZHuanYeJiSHu_WaiZuFang
worksheet["m8"] = float_ShengChanCaoZuo_WaiZuFang
worksheet["m18"] = float_QiTaRenYuan_WaiZuFang
worksheet["m5"] = worksheet["m6"].value + worksheet[
"m7"].value + worksheet["m8"].value + worksheet["m18"].value
# 应发工资
worksheet["n6"] = float_JingYingGuanLi_YingFa
worksheet["n7"] = float_ZHuanYeJiSHu_YingFa
worksheet["n8"] = float_ShengChanCaoZuo_YingFa
worksheet["n18"] = float_QiTaRenYuan_YingFa
worksheet["n5"] = worksheet["n6"].value + worksheet[
"n7"].value + worksheet["n8"].value + worksheet["n18"].value
# 各类保险
worksheet["p5"] = float_endowment_insurance
worksheet["p6"] = float_unemployement_insurance
worksheet["p7"] = float_medical_insurance
worksheet["p10"] = float_hourse_insurance
worksheet["p8"] = float_big_ill_insurance
worksheet["p9"] = float_hourse_insurance_extra
worksheet["p11"] = float_endowment_insurance_extra
worksheet["p12"] = float_unemployement_insurance_extra
worksheet["p13"] = float_medical_insurance_extra
worksheet["p14"] = float_tex
worksheet["p18"] = float_endowment_insurance + float_unemployement_insurance + float_medical_insurance \
+ float_hourse_insurance + float_big_ill_insurance + float_hourse_insurance_extra \
+ float_endowment_insurance_extra + float_unemployement_insurance_extra \
+ float_medical_insurance_extra + float_tex
# 实发工资
worksheet["n19"] = worksheet["n5"].value - worksheet["p18"].value
worksheet["c19"] = strCHangeMoneyFormat(worksheet["n5"].value -
worksheet["p18"].value)
# 关闭文件并保存
workbook.close()
workbook.save(str_file_name)
# 做进一步核实
if worksheet["n19"].value != float_in_hand_money:
print(worksheet["n19"].value, float_in_hand_money)
raise DataFormatError(
"工资明细表中实发工资{0}与工资汇总表中实发工资金额{1}不一致,推测为工资明细表中有人实发工资小于0，请检查后重新生成汇总表。"
.format(float_in_hand_money, worksheet["n19"].value))