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};'
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)
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 __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 __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 __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)
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)
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};'
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)
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 !=\'\';'
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')
def __init__(self):
self.dao = DaoUtils(**{
'dbType': 'MySQL',
'config': MySQLConfig.localhost()
})
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')
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 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 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;'
)
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}");'