def get_person_level(department, person, conn): sql = "select `person`.`级别` AS `级别` from `person` where ((`person`.`部门` = '{department}') and (`person`.`姓名` = '{person}'));".format( department=department, person=person) cursor = mysql_lib.Cursor(conn) result = cursor.select(sql) if len(result) == 0: return '其他人员' return result[0][0]
def get_id_list(conn): sql = "SELECT 标识 as id from general_table;" cursor = mysql_lib.Cursor(conn) results = cursor.select(sql) id_list = () for result in results: id_list += result return id_list
def get_separator(conn): ''' @description: @param {type} @return: 分隔符,类型:元祖 ''' sql = "SELECT other.`内容` from other WHERE other.`类别` = '分隔符';" cursor = mysql_lib.Cursor(conn) results = cursor.select(sql) separator = () for result in results: separator += result return separator
def get_standard_list(conn): sql = "select `standard`.`人员级别` AS `人员级别`,`standard`.`地区类别` AS `地区类别`,`standard`.`标准` AS `标准` from `standard`;" cursor = mysql_lib.Cursor(conn) results = cursor.select(sql) return results
def get_ID_max(conn, table): sql = "select ifnull(max(`ID`),0) AS `ID_max` from `{table}`;".format( table=table) cursor = mysql_lib.Cursor(conn) ID_max = cursor.select(sql) return ID_max[0][0]
def run(name_oa, password_oa, host, user, password, db): conn = pymysql.connect(host=host, user=user, password=password, port=3306, db=db) # 获取分隔符 separator = get_separator(conn) login = get_login(name_oa, password_oa) data = login.getData() # 初始化统计数据 success_num = 0 faild_num = 0 exist_num = 0 other_num = 0 # 判断获取主数据是否成功,成功继续,失败返回统计数据 if data == 0: return success_num, faild_num, exist_num, other_num # 主表信息 table_general_table = 'general_table' keys_general_table = [ 'ID', '标识', '类别', '报销事由', '部门', '报销人', '电话', '报销金额', '发起时间', '审批时间', '费用', '客运增值税', '专票增值税', '状态', '费用大类', '费用分类', '费用名称' ] # 客运表信息 table_passenger_transport = 'passenger_transport' keys_passenger_transport = [ 'ID', '标识', '序号', '开车时间', '开车地点', '到站时间', '到站地点', '交通工具', '票价', '燃油附加', '其他', '税额', '不含税价' ] # 住宿表信息 table_accommodation = 'accommodation' keys_accommodation = [ 'ID', '标识', '序号', '发票类别', '发票号', '无税金额', '税率', '税额', '含税金额' ] # 发票表信息 table_invoice = 'invoice' keys_invoice = [ 'ID', '标识', '序号', '发票类别', '发票号', '无税金额', '税率', '税额', '含税金额' ] # 出租车表信息 table_taxi = 'taxi' keys_taxi = ['ID', '标识', '序号', '日期', '车号', '金额'] # 补助表信息 table_subsidy = 'subsidy' keys_subsidy = [ 'ID', '标识', '序号', '人员', '级别', '开始时间', '结束时间', '出差地', '住宿标准', '住宿天数', '住宿费', '住宿补助', '伙食补助天数', '伙食补助金额', '交通补助天数', '交通补助金额' ] standard_list = get_standard_list(conn) # 获取住宿标准 # 获取插入数据sql insert_sql = mysql_lib.Cursor(conn) sql_general_table = insert_sql.get_insert_sql(table_general_table, keys_general_table) sql_passenger_transport = insert_sql.get_insert_sql( table_passenger_transport, keys_passenger_transport) sql_accommodation = insert_sql.get_insert_sql(table_accommodation, keys_accommodation) sql_taxi = insert_sql.get_insert_sql(table_taxi, keys_taxi) sql_subsidy = insert_sql.get_insert_sql(table_subsidy, keys_subsidy) sql_invoice = insert_sql.get_insert_sql(table_invoice, keys_invoice) cursor = conn.cursor() # 获取单行数据,后期添加循环 for i in range(len(data['data'])): # 初始化数据 values_general_table = [] values_passenger_transport = [] values_accommodation = [] values_invoice = [] values_taxi = [] values_subsidy = [] #获取单行数据 data_row = data['data'][i] # 判断id是否已经存在,如果存在跳过本次循环 id_list = get_id_list(conn) if data_row['id'] in id_list: exist_num += 1 continue if data_row['bodyType'] == '20': # 判断是否是协同 id_category = get_id_category(data_row['subject']) if id_category == "差旅": # 差旅费 data_id = login.getData_id_travel(data_row['id'], separator) if data_id == 0: faild_num += 1 continue # 获取ID最大值 ID_max_passenger_transport = get_ID_max( conn, table_passenger_transport) ID_max_accommodation = get_ID_max(conn, table_accommodation) ID_max_invoice = get_ID_max(conn, table_invoice) ID_max_taxi = get_ID_max(conn, table_taxi) ID_max_subsidy = get_ID_max(conn, table_subsidy) ID_max_general_table = get_ID_max(conn, table_general_table) # 主表信息 if len(login.reason) == 0: reason = data_row['subject'] else: reason = login.reason[0] if len(login.tel) == 0: tel = '无' else: tel = login.tel[0] if len(login.amount_total) == 0: amount_total = '0' else: amount_total = login.amount_total[0] if len(login.big_category) == 0: big_category = '' else: big_category = login.big_category[0] if len(login.category) == 0: category = '' else: category = login.category[0] if len(login.name) == 0: name = '' else: name = login.name[0] createMemberName = data_row['createMemberName'] value_general_table = (str(ID_max_general_table + 1), data_row['id'], id_category, reason, login.department[0], createMemberName, tel, amount_total, data_row['createDate'].split(' ', 1)[0], data_row['completeTime'].split( ' ', 1)[0], amount_total, '0', '0', '待录入', big_category, category, name) values_general_table.append(value_general_table) # 客运表信息 # 城市间交通 start_time = ['2020-01-01'] end_time = ['2020-01-01'] destinations = [] for m in range(math.ceil(len(login.transport) / 8)): if m == 0: start_time = [] end_time = [] value_passenger_transport = ( str(ID_max_passenger_transport + m + 1), data_row['id'], str(m + 1), login.transport[m * 8], login.transport[m * 8 + 1], login.transport[m * 8 + 2], login.transport[m * 8 + 3], login.transport[m * 8 + 4], login.transport[m * 8 + 5], '0', '0', login.transport[m * 8 + 6], login.transport[m * 8 + 7]) values_passenger_transport.append( value_passenger_transport) start_time.append(login.transport[m * 8]) end_time.append(login.transport[m * 8 + 2]) destinations.append(login.transport[m * 8 + 3]) # 飞机 airplane = login.airplane if airplane[0][0] != '\xa0': airplane = ["0" if x == [] else x for x in airplane] for m in range(math.ceil(len(airplane) / 10)): value_passenger_transport = ( str(ID_max_passenger_transport + math.ceil(len(login.transport) / 8) + m + 1), data_row['id'], str(math.ceil(len(login.transport) / 8) + m + 1), airplane[m * 10][0], airplane[m * 10 + 1][0], airplane[m * 10 + 2][0], airplane[m * 10 + 3][0], '飞机', airplane[m * 10 + 4][0], airplane[m * 10 + 5][0], airplane[m * 10 + 6][0], airplane[m * 10 + 8][0], airplane[m * 10 + 9][0]) values_passenger_transport.append( value_passenger_transport) start_time.append(airplane[m * 10][0]) end_time.append(airplane[m * 10 + 2][0]) destinations.append(airplane[m * 10 + 3][0]) # 住宿 if len(login.Accommodation_amount) > 0: value_accommodation = (str(ID_max_accommodation + 1), data_row['id'], '1', '普票', '', login.Accommodation_amount[0], '0', '0', login.Accommodation_amount[0]) values_accommodation.append(value_accommodation) accommodation_day = login.Accommodation_day[0] accommodation_amount = login.Accommodation_amount[0] else: accommodation_day = '0' accommodation_amount = '0' # 出租 for m in range(math.ceil(len(login.taxi) / 6)): value_taxi = (str(ID_max_taxi + m + 1), data_row['id'], str(m + 1), login.taxi[m * 6], '', login.taxi[m * 6 + 5]) values_taxi.append(value_taxi) # 补助 persons = login.person if len(persons) == 0: persons = [createMemberName] accommodation_amount_onePerson = ( decimal.Decimal(accommodation_amount) / len(persons)).quantize(decimal.Decimal('0.0000')) for m in range(len(persons)): person = persons[m] department = login.department[0] person_level = get_person_level(department, person, conn) destination = get_destination(destinations) standard = get_standard(person_level, destination, standard_list) accommodation_subsidy = standard * decimal.Decimal( accommodation_day) - accommodation_amount_onePerson if accommodation_subsidy > 0: accommodation_subsidy = accommodation_subsidy / 2 strat_date = min(start_time) end_date = max(end_time) travel_day = caltime(strat_date, end_date) + 1 value_subsidy = (str(ID_max_subsidy + m + 1), data_row['id'], str(m + 1), person, person_level, strat_date, end_date, destination, standard, accommodation_day, accommodation_amount_onePerson, accommodation_subsidy, travel_day, travel_day * 60, travel_day, travel_day * 20) values_subsidy.append(value_subsidy) # 录入信息 try: cursor.executemany(sql_general_table, values_general_table) cursor.executemany(sql_passenger_transport, values_passenger_transport) cursor.executemany(sql_accommodation, values_accommodation) cursor.executemany(sql_taxi, values_taxi) cursor.executemany(sql_subsidy, values_subsidy) conn.commit() success_num += 1 except: conn.rollback() faild_num += 1 elif id_category == "款项": other_num += 1 continue else: # 其他费用 data_id = login.getData_id_other(data_row['id']) if data_id == 0: faild_num += 1 continue # 主表信息 ID_max_general_table = get_ID_max(conn, table_general_table) if len(login.reason) == 0: reason = data_row['subject'] else: reason = login.reason[0] if len(login.tel) == 0: tel = '无' else: tel = login.tel[0] if len(login.amount_total) == 0: amount_total = '0' else: amount_total = login.amount_total[0] if len(login.big_category) == 0: big_category = '' else: big_category = login.big_category[0] if len(login.category) == 0: category = '' else: category = login.category[0] if len(login.name) == 0: name = '' else: name = login.name[0] value_general_table = (str(ID_max_general_table + 1), data_row['id'], id_category, reason, login.department[0], data_row['createMemberName'], tel, amount_total, data_row['createDate'].split(' ', 1)[0], data_row['completeTime'].split( ' ', 1)[0], amount_total, '0', '0', '待录入', big_category, category, name) values_general_table.append(value_general_table) # 发票信息 ID_max_invoice = get_ID_max(conn, table_invoice) expense_detail = login.expense_detail expense_detail = [ "0" if x == [] else x for x in expense_detail ] for m in range(math.ceil(len(login.expense_detail) / 6)): value_invoice = (str(ID_max_invoice + m + 1), data_row['id'], login.expense_detail[0][0], '普票', '', login.expense_detail[4][0], '0', '0', login.expense_detail[4][0]) values_invoice.append(value_invoice) # 录入信息 try: cursor.executemany(sql_general_table, values_general_table) cursor.executemany(sql_invoice, values_invoice) conn.commit() success_num += 1 except: conn.rollback() faild_num += 1 else: other_num += 1 cursor.close() conn.close() return success_num, faild_num, exist_num, other_num