class PaymentSub: def __init__(self): self.dao = DaoUtils(**{'dbType': 'MySQL', 'config': MySQLConfig.localhost()}) self.sql = 'INSERT ignore INTO itl_payment_sub' \ '(created_time, modified_time, payment_id, product_type_code, bill_id, actual_amount) VALUES (now(),now(),{p}, "{c}", {b}, "{a}");' def handle(self): payment_list = self.dao.get_all('SELECT id, type, bill_ids, pay_amount FROM payment WHERE status = "PAID" AND type IS NOT NULL;') bill_list = self.dao.get_all('SELECT id, payment_id, real_amount FROM bill;') bill_map = dict() for bill in bill_list: bill_map[bill['id']] = bill['real_amount'] with open('out_PaymentSub.sql', 'a') as f: f.write(Constant.SQL_BEGIN) for payment in payment_list: if payment['bill_ids'] is None or payment['bill_ids'] == '': f.write( self.sql.format(p=payment['id'], c=payment['type'], b='null', a=payment['pay_amount']) + '\n' ) else: bill_id_list = str(payment['bill_ids']).split(',') for bill_id in bill_id_list: if int(bill_id) in bill_map: f.write( self.sql.format(p=payment['id'], c=payment['type'], b=bill_id, a=bill_map[int(bill_id)]) + '\n' ) f.write(Constant.SQL_COMMIT)
class UserZone: _BASE_SQL = 'INSERT INTO itl_user_zone_relation (created_time, modified_time, user_id, zone_id) VALUES (now(), now(), "{userId}", "{zoneId}");' def __init__(self): self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) def handle(self): data = self.dao.get_all(""" SELECT user.id, user.zone_id, user.zone_ids FROM user WHERE user.zone_id > 0 AND user.id NOT IN (SELECT user_id FROM user_role_relation WHERE role_code IN ('物业公司管理员' , '后台项目管理员', '小二')); """) print(len(data)) print(Constant.SQL_BEGIN) for temp in data: zone_ids = str(temp['zone_ids']).split(',') for zone_id in zone_ids: print( UserZone._BASE_SQL.format(userId=temp['id'], zoneId=zone_id)) print(Constant.SQL_COMMIT)
class JjyParkingFee20171021: def __init__(self): self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) self.field_index = { 'parking_name': 0, 'end_time': 1, } self.field_index_special = { 'parking_name': 0, 'end_time': 1, 'need_pad': 8, } self.select_sql = 'SELECT itl_parking_bill.bill_id AS bill_id, itl_parking.name AS pn, itl_parking.house_info_id AS house, bill.title AS bt' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}" AND CAST(date_format(bill.gmt_start, \'%Y%m\') AS SIGNED) = {day_data}' \ ' AND payment_ids =\'\';' self.update_sql = 'UPDATE bill SET ' \ ' real_amount = ought_amount, gmt_modify = now(), gmt_pay = now(),' \ ' remark = \'[第3批系统处理]车位收费上线之前,已线下收费[当前特殊月处理],没有支付流水\', STATUS = \'PAID\', financial_income = 0.00' \ ' WHERE id = {bill_id};' def handle(self, money_file): bill_ids = list() data = ReadUtil.read_file(money_file, self.field_index_special) for row in data: end_time = str(row['end_time']) if end_time is not None and end_time != '' and round( float(row['need_pad']), 2) == 0: day_split = str(row['end_time']).split('/') sql = self.select_sql.format(parking_name=row['parking_name'], day_data=day_split[0].zfill(4) + day_split[1].zfill(2)) row_data = self.dao.get_all(sql) for temp in row_data: bill_ids.append({ 'billId': temp['bill_id'], 'parkingName': temp['pn'], 'house_id': temp['house'], 'bill_name': temp['bt'], }) return bill_ids
class JjyParkingFee20171022: def __init__(self): self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) self.field_index = { 'parking_name': 0, 'end_time': 1, } self.field_index_special = { 'parking_name': 0, 'end_time': 1, 'need_pad': 8, } self.select_sql = 'SELECT itl_parking_bill.bill_id AS bill_id, bill.house_info_id AS house_info_id, itl_parking.name AS parking_name, bill.remark AS remark, bill.gmt_start AS start' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}"' \ ' AND payment_ids !=\'\';' def handle(self, money_file): bill_ids = list() data = ReadUtil.read_file(money_file, self.field_index) for row in data: end_time = str(row['end_time']) if end_time is not None and end_time != '': day_split = str(row['end_time']).split('/') sql = self.select_sql.format(parking_name=row['parking_name'], day_data=day_split[0].zfill(4) + day_split[1].zfill(2)) row_data = self.dao.get_all(sql) for temp in row_data: bill_ids.append({ 'id': temp['bill_id'], 'house_info_id': temp['house_info_id'], 'parking_name': temp['parking_name'], 'remark': temp['remark'], 'start': temp['start'], }) print(len(bill_ids)) for row in bill_ids: print(row)
class WycHistoricalFee: def __init__(self): self.dao = DaoUtils(**{'dbType': 'MySQL', 'config': MySQLConfig.localhost()}) self.delete_bill_sql = 'DELETE FROM bill WHERE zone_id = 24 AND product_type_code = "propertyFee" AND house_info_id = {house_info_id} AND id != 10792 AND id != 10829;' self.delete_sub_enter_sql = 'DELETE FROM subscription_enter WHERE zone_id = 24 AND product_type_code = "propertyFee" AND house_info_id = {house_info_id};' self.update_sql = 'UPDATE bill SET title="{title}", ought_amount={ought_amount}, gmt_modify=now(), remark="{remark}", is_checked=1,' \ ' financial_income="{financial_income}" WHERE zone_id = 24 AND product_type_code = "propertyFee" AND house_info_id = {house_info_id};' def handle(self, file, field_index): db_house_dict = dict() # key-房id,value-房code file_house_codes = set() # 待处理文件中已存在的房号code base = ReadUtil.read_file(file, field_index) # 待处理的数据 houses = self.dao.get_all('SELECT id, code FROM house_info WHERE zone_id = 24;') # 万源城逸郡所有的房号 for house in houses: db_house_dict[str(house['code'])] = house['id'] for b in base: file_house_codes.add(str(b['house_code'])) with open('out_wyc.sql', 'a') as f: f.write(Constant.SQL_BEGIN + '\n') for key in db_house_dict.keys(): if key not in file_house_codes: f.write(self.delete_bill_sql.format(house_info_id=db_house_dict[key]) + '\n') f.write(self.delete_sub_enter_sql.format(house_info_id=db_house_dict[key]) + '\n') with open('out_wyc.sql', 'a') as f: for row in base: f.write( self.update_sql.format( title=row['bill_tile'], ought_amount=row['money'], remark='历史遗留账单处理', financial_income=row['money'], house_info_id=db_house_dict[row['house_code']] ) + '\n' ) f.write(Constant.SQL_COMMIT + '\n')
class WycBill: def __init__(self): self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) self.day_title = { '2018-01-01': '物业费+车位管理费2018年01月-03月', '2018-04-01': '物业费+车位管理费2018年04月-06月', '2018-07-01': '物业费+车位管理费2018年07月-09月', '2018-10-01': '物业费+车位管理费2018年10月-12月', } self.sql = 'SELECT bill.id, CONCAT( house_info.house, "-", house_info.building, "-", CAST(house_info.door AS SIGNED)) AS k, gmt_start AS s' \ ' FROM bill LEFT JOIN house_info ON house_info.id = bill.house_info_id' \ ' WHERE bill.zone_id = 24 AND product_type_id = 7 AND gmt_start IN ("2018-01-01", "2018-04-01", "2018-07-01", "2018-10-01");' self.sql_update = 'UPDATE bill SET title = "{title}", ought_amount = {money}, gmt_modify = now(), remark = "手动更改", financial_income = {money} WHERE id = {id};' def handle(self, file, field_index): need_update = list() excel_data = ReadUtil.read_file(file, field_index) bill_db = self.dao.get_all(self.sql) for ed in excel_data: key = ed['house'] + '-' + ed['building'] + '-' + ed['door'] for bill in bill_db: if key == bill['k']: need_update.append( self.sql_update.format(title=self.day_title[str( bill['s'])], money=ed[str(bill['s'])], id=bill['id'])) with open('out.sql', 'a') as f: f.write(Constant.SQL_BEGIN + '\n') for nu in need_update: f.write(nu + '\n') f.write(Constant.SQL_COMMIT + '\n')
class Patrol: def __init__(self): self.dao = DaoUtils(**{'dbType': 'MySQL', 'config': MySQLConfig.localhost()}) # self.delete_zone_task_sql = 'DELETE FROM itl_zone_task WHERE type = "1E" AND zone_id = {zone_id} AND id = {id};' self.delete_user_task = 'DELETE FROM itl_user_task WHERE zone_id = {zone_id} AND id = {id};' self.insert_group = 'INSERT INTO itl_patrol_group' \ ' (created_time, modified_time, zone_id, group_type, name, route_ids, user_ids, zone_task_ids, day_shift_count, night_shift_count, operator_id)' \ ' VALUES (now(), now(), {zone_id}, 2, "ALL_RANDOM", "", "{user_ids}", "{zone_task_ids}", {day_shift_count}, {night_shift_count}, 431);' self.insert_user_task = 'INSERT INTO itl_user_task (created_time, modified_time, zone_task_id, user_id)' \ ' VALUES (now(), now(), {zone_task_id}, {user_id});' self.insert_group_user = '******' \ ' VALUES (now(), now(), {zone_id}, {patrol_group_id}, {patrol_user_id}, 431);' def handle(self): data_db = self.dao.get_all(""" SELECT itl_zone_task.id AS zt_id, itl_zone_task.overdue_time, itl_zone_task.start_time, itl_zone_task.zone_id, itl_user_task.id AS ut_id, itl_user_task.user_id FROM itl_zone_task LEFT JOIN itl_user_task ON itl_user_task.zone_task_id = itl_zone_task.id WHERE type = '1E' AND is_valid = 1 ORDER BY itl_zone_task.zone_id; """) task_cfg = dict() # delete_zone_task = set() delete_user_task = set() for row in data_db: # delete_zone_task.add(self.delete_zone_task_sql.format(zone_id=row['zone_id'], id=row['zt_id'])) delete_user_task.add(self.delete_user_task.format(zone_id=row['zone_id'], id=row['ut_id'])) if row['zone_id'] in task_cfg: task_cfg[row['zone_id']]['zt_ids'].add(row['zt_id']) task_cfg[row['zone_id']]['user_ids'].add(row['user_id']) if row['overdue_time'] == 1: task_cfg[row['zone_id']]['day_shift'] += 1 elif row['overdue_time'] == -1: task_cfg[row['zone_id']]['night_shift'] += 1 else: day_shift = 0 night_shift = 0 user_ids = set() user_ids.add(row['user_id']) zt_ids = set() zt_ids.add(row['zt_id']) if row['overdue_time'] == 1: day_shift = 1 elif row['overdue_time'] == -1: night_shift = 1 task_cfg[row['zone_id']] = { 'zt_ids': zt_ids, 'user_ids': user_ids, 'day_shift': day_shift, 'night_shift': night_shift } data = dict() for row in task_cfg: print(row) requests.post("http://httpbin.org/post", data=data)
class PatrolSimple: def __init__(self): self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) def handle(self): data_db = self.dao.get_all(""" SELECT itl_zone_task.id AS zt_id, itl_zone_task.overdue_time, itl_zone_task.start_time, itl_zone_task.zone_id, itl_user_task.id AS ut_id, itl_user_task.user_id FROM itl_zone_task LEFT JOIN itl_user_task ON itl_user_task.zone_task_id = itl_zone_task.id WHERE type = '1E' AND is_valid = 1 ORDER BY itl_zone_task.zone_id; """) task_cfg = dict() delete_zone_task_ids = set() delete_user_task_ids = set() for row in data_db: delete_zone_task_ids.add(row['zt_id']) if row['ut_id'] is not None: delete_user_task_ids.add(row['ut_id']) if row['zone_id'] in task_cfg: task_cfg[row['zone_id']]['user_ids'].add(row['user_id']) if row['zt_id'] in task_cfg[row['zone_id']]['zt_ids']: if row['overdue_time'] == 1: task_cfg[row['zone_id']]['day_shift'] += 1 elif row['overdue_time'] == -1: task_cfg[row['zone_id']]['night_shift'] += 1 else: task_cfg[row['zone_id']]['zt_ids'].add(row['zt_id']) else: day_shift = 0 night_shift = 0 user_ids = set() user_ids.add(row['user_id']) zt_ids = set() zt_ids.add(row['zt_id']) if row['overdue_time'] == 1: day_shift = 1 elif row['overdue_time'] == -1: night_shift = 1 task_cfg[row['zone_id']] = { 'zt_ids': zt_ids, 'user_ids': user_ids, 'day_shift': day_shift, 'night_shift': night_shift } param = { 'delZtIds': list(delete_zone_task_ids), 'delUtIds': list(delete_user_task_ids) } param_sub = list() for key, value in task_cfg.items(): pgf_item = { 'zoneId': key, 'pgc': [{ 'groupName': 'ALL_RANDOM', 'routeIds': [], 'staffIds': list(value['user_ids']), 'dayShiftCount': value['day_shift'], 'nightShiftCount': value['night_shift'], }] } param_sub.append(pgf_item) param['add'] = param_sub print(json.dumps(param)) headers = {'content-type': 'application/json'} # requests.post("http://premanager.itianluo.cn/patrol/oldDataHandle?token=092ef9338413437abdf58e9560a0842a", headers=headers, data=json.dumps(param)) requests.post( "http://manager.itianluo.cn/patrol/oldDataHandle?token=092ef9338413437abdf58e9560a0842a", headers=headers, data=json.dumps(param))
class DbUtil: _BASE_PATH = './sql/' _DIRECT_EXE_ON_DB = False def __init__(self): self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.stable_sky() }) self.time = datetime.now().strftime('%Y%m%d%H%M%S') self.users = self.get_all_user() self.admin_emp = self.get_all_admin_emp() self.where = '' if not os.path.exists(DbUtil._BASE_PATH): os.mkdir(DbUtil._BASE_PATH) def out_sql(self, data, remark=''): with open(DbUtil._BASE_PATH + 'out' + self.time + '.sql', 'a') as f: f.write('# ' + remark + Constant.NEW_LINE) # f.write(Constant.SQL_BEGIN + Constant.NEW_LINE) for row in data: f.write(row + Constant.NEW_LINE) # f.write(Constant.SQL_COMMIT + Constant.NEW_LINE) f.write(Constant.NEW_LINE) def exe_on_db(self, data): if DbUtil._DIRECT_EXE_ON_DB: for row in data: self.dao.dao.execute(row) def get_user_id(self, admin_id): for emp in self.admin_emp: if emp['id'] == admin_id: for row in self.users: if emp['mobile'] in row['account']: return row['id'] return 0 def get_user(self, admin_id): for emp in self.admin_emp: if emp['id'] == admin_id: for row in self.users: if emp['mobile'] in row['account']: return {'id': row['id'], 'name': row['name']} return {'id': 0, 'name': ''} def get_all_role(self): return self.dao.get_all('SELECT code FROM role ORDER BY id;') def get_all_user(self): return self.dao.get_all( 'SELECT id, account, name FROM user ORDER BY id;') def get_all_admin_emp(self): return self.dao.get_all( 'SELECT id, mobile FROM admin_employee ORDER BY id;') def get_all_subscription_enter(self): return self.dao.get_all( 'SELECT id, admin_employee_id FROM subscription_enter ' + self.where + ';') def get_all_repair(self): return self.dao.get_all('SELECT id, guid, employee_id FROM task;') def get_all_task_map(self): return self.dao.get_all('SELECT id, uid FROM task_map;') def get_all_appraisal(self): return self.dao.get_all('SELECT id, eid FROM appraisal;') def get_all_appraisal_emp(self): return self.dao.get_all('SELECT id, eid FROM appraisal_emp;') def get_all_appraisal_progress(self): return self.dao.get_all('SELECT id, eid FROM appraisal_progress;') def get_all_appraisal_adjust(self): return self.dao.get_all('SELECT id, eid FROM appraisal_adjust;') def get_all_appraisal_assignee(self): return self.dao.get_all('SELECT id, eid FROM appraisal_assignee;') def get_all_summary_plan(self): return self.dao.get_all('SELECT id, employee_id FROM summary_plan;') def get_all_bug_report(self): return self.dao.get_all('SELECT id, uid FROM bug_report;') def get_all_chat(self): return self.dao.get_all('SELECT id, reply_id FROM chat;') def get_about_appraisal(self): return [ { 'table': 'appraisal', 'data': self.get_all_appraisal() }, { 'table': 'appraisal_emp', 'data': self.get_all_appraisal_emp() }, { 'table': 'appraisal_progress', 'data': self.get_all_appraisal_progress() }, { 'table': 'appraisal_adjust', 'data': self.get_all_appraisal_adjust() }, { 'table': 'appraisal_assignee', 'data': self.get_all_appraisal_assignee() }, ] # START TEMP def temp(self): return self.dao.get_all( 'SELECT user_id FROM user_role_relation WHERE role_code = \'customerService\';' ) def role_permission_relation(self): return self.dao.get_all( 'SELECT role_code, permission_code FROM role_permission_relation WHERE role_code IN (\'propertyManager\', \'xiaoer\');' ) def get_all_zones(self): return self.dao.get_all( 'SELECT id FROM user_role_relation WHERE role_code = \'customerService\';' ) def get_wh_role_per_r(self): return self.dao.get_all( 'SELECT role_code, permission_code FROM role_permission_relation WHERE id >= 3432 AND id <= 4029;' )
class JjyParkingFee: def __init__(self): # bill itl_parking_bill itl_parking self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) self.field_index = { 'parking_name': 0, 'end_time': 1, } self.field_index_special = { 'parking_name': 0, 'end_time': 1, 'need_pad': 4, } self.select_sql = 'SELECT itl_parking_bill.bill_id AS bill_id' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}" AND CAST(date_format(bill.gmt_start, \'%Y%m\') AS SIGNED) <= {day_data};' self.update_sql = 'UPDATE bill SET' \ ' real_amount = financial_income, gmt_modify = now(), gmt_pay = now(), remark = \'车位收费上线之前,已线下收费,没有支付流水\',' \ ' status = \'PAID\', financial_income = 0.00 WHERE id = {bill_id};' self.select_sql_special = 'SELECT itl_parking_bill.bill_id AS bill_id' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}" AND CAST(date_format(bill.gmt_start, \'%Y%m\') AS SIGNED) = {day_data};' self.update_sql_special = 'UPDATE bill SET ' \ 'real_amount = ought_amount - {need_pay}, gmt_modify = now(), gmt_pay = now(),' \ ' remark = \'车位收费上线之前,已线下收费[当前特殊月处理],没有支付流水\', status = \'NO_PAY\', financial_income = {need_pay}' \ ' WHERE id = {bill_id};' def handle(self, money_file): bill_ids = list() data = ReadUtil.read_file(money_file, self.field_index) for row in data: end_time = str(row['end_time']) if end_time is not None and end_time != '': day_split = str(row['end_time']).split('/') sql = self.select_sql.format(parking_name=row['parking_name'], day_data=day_split[0].zfill(4) + day_split[1].zfill(2)) row_data = self.dao.get_all(sql) for temp in row_data: bill_ids.append(temp['bill_id']) else: pass with open('out.sql', 'a') as f: for row in bill_ids: f.write(self.update_sql.format(bill_id=row)) f.write('\n') def handle_special(self, money_file): bill_ids = list() with open(money_file, 'r') as f: for line in f.readlines(): line_split = line.split(' ') if line_split[2] != '' and line_split[2] is not None and round( float(line_split[7]), 2) > 0: row_data = self.dao.get_all( self.select_sql_special.format( parking_name=line_split[0], day_data=line_split[2].zfill(4) + line_split[3].zfill(2))) for temp in row_data: bill_ids.append({ 'bill_id': temp['bill_id'], 'need_pad': round(float(line_split[7]), 2), }) with open('out_special.sql', 'a') as f: for row in bill_ids: f.write( self.update_sql_special.format(need_pay=row['need_pad'], bill_id=row['bill_id'])) f.write('\n')
class JjyParkingFee20171022: def __init__(self): # bill itl_parking_bill itl_parking self.dao = DaoUtils(**{ 'dbType': 'MySQL', 'config': MySQLConfig.localhost() }) self.data = dict() self.field_index = { 'parking_name': 0, 'end_time': 1, } self.field_index_special = { 'parking_name': 0, 'end_time': 1, 'need_pad': 8, } self.select_sql = 'SELECT itl_parking_bill.bill_id AS bill_id' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}" AND CAST(date_format(bill.gmt_start, \'%Y%m\') AS SIGNED) < {day_data}' \ ' AND payment_ids =\'\';' self.update_sql = 'UPDATE bill SET' \ ' real_amount = ought_amount, gmt_modify = now(), gmt_pay = now(), remark = \'[第二批系统处理]车位收费上线之前,已线下收费,没有支付流水\',' \ ' status = \'PAID\', financial_income = 0.00 WHERE id = {bill_id};' self.select_sql_special = 'SELECT itl_parking_bill.bill_id AS bill_id' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}" AND CAST(date_format(bill.gmt_start, \'%Y%m\') AS SIGNED) = {day_data}' \ ' AND payment_ids =\'\';' self.update_sql_special = 'UPDATE bill SET ' \ ' real_amount = ought_amount - {need_pay}, gmt_modify = now(), gmt_pay = now(), is_part_paid = 1,' \ ' remark = \'[第二批系统处理]车位收费上线之前,已线下收费[当前特殊月处理],没有支付流水\', STATUS = \'NO_PAY\', financial_income = {need_pay}' \ ' WHERE id = {bill_id};' self.select_dy_sql = 'SELECT itl_parking_bill.bill_id AS bill_id' \ ' FROM itl_parking_bill' \ ' LEFT JOIN itl_parking ON itl_parking.id = itl_parking_bill.parking_id' \ ' LEFT JOIN bill ON bill.id = itl_parking_bill.bill_id' \ ' WHERE itl_parking.zone_id = 2 AND itl_parking.name = "{parking_name}" AND CAST(date_format(bill.gmt_start, \'%Y%m\') AS SIGNED) > {day_data}' \ ' AND payment_ids =\'\' AND status != \'NO_PAY\';' self.update_dy_sql = 'UPDATE bill SET' \ ' real_amount = NULL , gmt_modify = now(), gmt_pay = NULL , financial_income = ought_amount, ' \ ' remark = \'[第二批系统处理]车位收费上线之前,没有付款\',' \ ' status = \'NO_PAY\' WHERE id = {bill_id};' def handle(self, money_file): bill_ids = list() data = ReadUtil.read_file(money_file, self.field_index) for row in data: end_time = str(row['end_time']) if end_time is not None and end_time != '': day_split = str(row['end_time']).split('/') sql = self.select_sql.format(parking_name=row['parking_name'], day_data=day_split[0].zfill(4) + day_split[1].zfill(2)) row_data = self.dao.get_all(sql) for temp in row_data: bill_ids.append(temp['bill_id']) self.data[row['parking_name']] = ( self.data[row['parking_name']] + len(row_data) ) if row['parking_name'] in self.data else len(row_data) else: pass with open('out_第二批.sql', 'a') as f: for row in bill_ids: f.write(self.update_sql.format(bill_id=row)) f.write('\n') def handle_special(self, money_file): bill_ids = list() data = ReadUtil.read_file(money_file, self.field_index_special) for row in data: end_time = str(row['end_time']) if end_time is not None and end_time != '' and round( float(row['need_pad']), 2) > 0: day_split = str(row['end_time']).split('/') sql = self.select_sql_special.format( parking_name=row['parking_name'], day_data=day_split[0].zfill(4) + day_split[1].zfill(2)) row_data = self.dao.get_all(sql) for temp in row_data: bill_ids.append({ 'bill_id': temp['bill_id'], 'need_pad': round(float(row['need_pad']), 2), }) self.data[row['parking_name']] = ( self.data[row['parking_name']] + len(row_data) ) if row['parking_name'] in self.data else len(row_data) with open('out_special_第二批.sql', 'a') as f: for row in bill_ids: f.write( self.update_sql_special.format(need_pay=row['need_pad'], bill_id=row['bill_id'])) f.write('\n') def handle_dy(self, money_file): bill_ids = list() data = ReadUtil.read_file(money_file, self.field_index) for row in data: end_time = str(row['end_time']) if end_time is not None and end_time != '': day_split = str(row['end_time']).split('/') sql = self.select_dy_sql.format( parking_name=row['parking_name'], day_data=day_split[0].zfill(4) + day_split[1].zfill(2)) row_data = self.dao.get_all(sql) for temp in row_data: bill_ids.append(temp['bill_id']) self.data[row['parking_name']] = ( self.data[row['parking_name']] + len(row_data) ) if row['parking_name'] in self.data else len(row_data) with open('out_第二批_dy.sql', 'a') as f: for row in bill_ids: f.write(self.update_dy_sql.format(bill_id=row)) f.write('\n')