def handle(self):
print('SET SQL_SAFE_UPDATES = 0;')
print('START TRANSACTION;')
wy = ReadUtil.read_file(Account._BASE_PATH + '物业后台账号-物业公司管理员.xlsx', {
'company': 0,
'account': 2,
'pwd': 3
})
for row in wy:
name = row['company'] + '-管理员'
print(
Account._INSERT_USER.format(
account=row['account'],
password=hashlib.md5(
row['pwd'].encode('utf-8')).hexdigest(),
name=name,
nickname=name,
company_name=row['company'],
zone_id='',
zone_ids=''))
print(
Account._UPDATE_COMPANY.format(account=row['account'],
company_name=row['company']))
print(
Account._INSERT_R.format(account=row['account'],
role_code='物业公司管理员'))
print()
print()
print()
xm = ReadUtil.read_file(Account._BASE_PATH + '物业后台账号-项目管理员拆分版.xlsx', {
'company': 0,
'zone': 1,
'account': 2,
'pwd': 3
})
for row in xm:
name = row['zone'] + '-管理员'
# print(Account._INSERT_USER_2222.format(account=row['account'], password=hashlib.md5(row['pwd'].encode('utf-8')).hexdigest(),
# name=name, nickname=name, company_name=row['company'],
# zone_name=row['zone']))
# print(Account._UPDATE_ZONE.format(account=row['account'], zone_name=row['zone']))
print(
Account._INSERT_R.format(account=row['account'],
role_code='小二'))
# print(Account._INSERT_CATEGORY.format(zone_name=row['zone']))
print('COMMIT;')
def h(self):
wm = dict()
water = ReadUtil.read_file('南城景园住宅水费2018.2.1-2018.6.3（新）_data.xlsx', {
'house': 0,
'base': 1,
'next': 2
})
for row in water:
wm[row['house']] = row
with open('南城5月份水费能耗数据.txt', 'r') as f:
print(Constant.SQL_BEGIN)
for row in f.readlines():
row = row.replace('\n', '')
array = row.split(',')
key = str(int(array[1])) + '-' + str(int(
array[2])) + '-' + str(int(array[3]))
if key in wm:
# if float(wm[key]['next']) - float(wm[key]['base']) == 0:
# print('房号:', key, '底数:', wm[key]['base'], '本期读数:', wm[key]['next'])
print(
self.sql.format(last=float(wm[key]['base']),
last_date='2018-04-30',
curr=float(wm[key]['next']),
curr_date='2018-05-30',
actual=float(wm[key]['next']) -
float(wm[key]['base']),
id=array[0]).replace('(', '{').replace(
')', '}'))
else:
pass
print(Constant.SQL_COMMIT)
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 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')
def handle(self, file_name):
result_list = ReadUtil.read_file(file_name, self.field_index, True)
print('len: ' + str(len(result_list)))
print(Constant.SQL_BEGIN)
for row in result_list:
print(
self.base_sql.format(name=row['name1'] + '-' + row['name2'],
owner_type=row['type'],
coordinate=row['point1'] + ';' +
row['point2'] + ';' + row['point3'] +
';' + row['point4'] + ''))
print(Constant.SQL_BEGIN)
print()
print(Constant.SQL_BEGIN)
for row in result_list:
print(
self.base_update_sql.format(
coordinate=row['point1'] + ';' + row['point2'] + ';' +
row['point3'] + ';' + row['point4'] + '',
zone_id=1,
name=row['name1'] + '-' + row['name2']))
print(Constant.SQL_BEGIN)
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(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(self):
field_index = {
'house': 0,
'building': 1,
'door': 2,
'area': 3,
'name': 4,
'mobile': 5,
}
rows = ReadUtil.read_file('浙地面积修改.xlsx', field_index)
dbs = dict()
with open('zd.txt', 'r') as f:
for line in f.readlines():
arr = line.replace('\n', '').split(',')
key = str(int(arr[1])) + '-' + str(int(arr[2])) + '-' + str(int(arr[3]))
dbs[key] = str(int(arr[0]))
result = list()
for row in rows:
key = row['house'] + '-' + row['building'] + '-' + row['door']
if key in dbs:
result.append(self.sql.format(area=row['area'], un=row['name'], m=row['mobile'], id=dbs[key]))
else:
print('not in dbs key: ', key)
with open('out.sql', 'a') as f:
f.write(Constant.SQL_BEGIN + '\n')
for row in result:
f.write(row + '\n')
f.write(Constant.SQL_COMMIT + '\n')
def handle(self, file_name):
field_index = {
'role': 0,
'type': 1,
'content': 2,
'standard': 3,
'rate': 4,
'startTime': 5,
'endTime': 6
}
self.write_file(ReadUtil.read_file(file_name, field_index), file_name)
def handle(self):
sql = 'INSERT INTO bill(zone_id, house_info_id, title, product_type_id, product_type_code, product_id,' \
'sub_enter_id, billing_period, ought_amount, real_amount, gmt_start, gmt_end, gmt_create,' \
'gmt_modify, remark, status, is_checked, financial_income)' \
'VALUES("{zone_id}", "{house_info_id}", "{title}", "{product_type_id}", "{product_type_code}", "{product_id}",' \
'NULL , "{billing_period}", "{ought_amount}", "{real_amount}", "{gmt_start}",' \
'"{gmt_end}", now(), now(), "{remark}", "{status}", "{is_checked}", "{financial_income}");'
field_index = {
'house_info': 0,
'period': 5,
'period_name': 6,
'ought_amount': 7
}
data = ReadUtil.read_file('田螺财务收费系统生成账单核对20171009.xlsx',
field_index=field_index)
house_W = dict()
with open('houW.txt', 'r') as f:
for line in f.readlines():
line_split = line[:-1].split(',')
house_W[line_split[1]] = line_split[0]
for row in data:
p_s = str(row['period']).split('月-')
ps_s = p_s[0].split('年')
print(
sql.format(zone_id=24,
house_info_id=house_W[row['house_info']],
title=row['period_name'],
product_type_id=7,
product_type_code='propertyFee',
product_id=24,
billing_period=row['period_name'],
ought_amount='%.2f' % float(row['ought_amount']),
real_amount=0.00,
gmt_start=ps_s[0] + '-' + ps_s[1] + '-01',
gmt_end=ps_s[0] + '-' + p_s[1][:-1] + '-01',
remark='手动生成的历史遗留数据',
status='NO_PAY',
is_checked=1,
financial_income='%.2f' %
float(row['ought_amount'])))
def handle(self, file, field_index):
need_update = list()
excel_data = ReadUtil.read_file(file, field_index, include_first=True)
for ed in excel_data:
coordinate = ReadNcCar.handle_point(
ed['a']) + ';' + ReadNcCar.handle_point(
ed['b']) + ';' + ReadNcCar.handle_point(
ed['c']) + ';' + ReadNcCar.handle_point(ed['d'])
need_update.append(
self.update_sql.format(coordinate=coordinate,
id=int(float(ed['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 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
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')
' remark = "{remark}",' \
' has_refund = "{has_refund}",' \
' is_garbage = "{is_garbage}",' \
' number_condition = "{number_condition}"' \
' WHERE id ={id};'
data = ReadUtil.read_file(
'file/要调账的数据（小罗要的格式）_190517.xlsx', {
'id': 0,
'gmt_create': 1,
'gmt_modified': 2,
'zone_id': 3,
'amount': 4,
'pay_time': 5,
'pay_title': 6,
'payment_channel': 7,
'uuid': 8,
'trade_no': 9,
'payer_owner_id': 10,
'receiver_id': 11,
'charge_object_type': 12,
'charge_object_value': 13,
'contact_number': 14,
'remark': 15,
'has_refund': 16,
'is_garbage': 17,
'number_condition': 18,
})
print()
print()
print()
for row in data:
status,
title,
zone_id,
is_checked,
financial_income,
sub_period_id)
VALUES
({b_id}, '{p_name}', now(), now(), "{start}"," {end}", {house_info_id}, {ought_amount}, 43, 'propertyFee', 7, NULL, 'NO_PAY', '{title}', 76, {is_checked}, {financial_income}, {p_id});
"""
data = ReadUtil.read_file(
'物业用房账单明细_data.xls',
{
'id': 0,
'house': 1,
'building': 2,
'door': 3,
'wyf': 8,
'ggf': 9,
'nhf': 10,
}
)
sp = list()
with open('wyf_p.txt', 'r') as f:
for line in f.readlines():
d = line.replace('\n', '').split(',')
sp.append(d)
b_id = 483778
for row in data:
for p in sp:
select id, house, building, door from house_info where zone_id = 24; select id, period_start from subscription_period where zone_id = 24 and product_id = 24 and id > 161; scp -P 22 /Users/luoyanjie/PycharmProjects/thor_handle/handle/itl8/wyc/toExe.sql [email protected]:/root/zSky """ from utils.file.excel.readUtil import ReadUtil # 改造战斗感金额的sql sql = 'UPDATE bill ' \ 'SET title = "{title}", ought_amount = {ought_amount}, gmt_modify = now(), remark="手动处理", financial_income = {financial_income} ' \ 'WHERE zone_id = 24 AND sub_period_id > 161 AND sub_period_id = {sub_period_id} AND house_info_id = {house_info_id};' # 原始数据 data = ReadUtil.read_file('万源城逸郡2019年账单导入.xlsx', {'title': 2, 'house_name': 3, 'ought_amount': 4, 'financial_income': 5}) # 万源城的房号数据 house_info = dict() with open('wycHouse.txt', 'r') as f: for row in f.readlines(): hd = row.replace('\n', '').split(',') key = hd[1] + '幢' + hd[2] + '单元' + str(int(hd[3])) + '室' house_info[key] = hd[0] # 账单对应账期 bill_period = { '01/01/2019': '物业费+车位管理费2019年1月-3月', '04/01/2019': '物业费+车位管理费2019年4月-6月', '07/01/2019': '物业费+车位管理费2019年7月-9月', '10/01/2019': '物业费+车位管理费2019年10月-12月'
from utils.file.excel.readUtil import ReadUtil
sql_update = 'UPDATE house_info SET name = "{name}", price_type_name = "{price_type_name}" WHERE zone_id = 135 AND id ={id};'
data = ReadUtil.read_file('file/蓝庭房号数据订正.xlsx', {
'id': 0,
'name': 1,
'price_type_name': 2,
})
for row in data:
print(
sql_update.format(id=row['id'],
name=row['name'],
price_type_name=row['price_type_name']))
data = row.split(',')
shop[int(data[0])] = str(data[1])
field_index = {
'kind': 0,
'created_time': 1,
'zone': 2,
'name': 3,
'type': 4,
'house': 5,
'en': 6,
'base': 7,
}
file_name = '数据订正.xlsx'
data = ReadUtil.read_file(file_name, field_index)
with open('out.sql', 'w') as f:
f.write(Constant.SQL_BEGIN + '\n')
for row in data:
print(row)
name = ''
energy_table_kind = kind[row['kind']]
if row['name'] == '根据房号id取房号名':
name = house[int(float(row['house']))]
elif row['name'] == '根据id取店铺名':
name = shop[int(float(row['house']))]
else:
name = row['name']
f.write(
sql.format(zone_id=row['zone'],
from utils.file.excel.readUtil import ReadUtil
file_name = 'file/绿都商铺数据调整.xlsx'
data = ReadUtil.read_file(file_name, {'id': 2, 'amount': 5})
sql = 'UPDATE itl_finance_bill SET ought_amount = {ought_amount}, owe_amount = {owe_amount} WHERE id = {id};'
with open(file_name + '.txt', 'a') as f:
for row in data:
f.write(
sql.format(ought_amount=row['amount'], owe_amount=row['amount'], id=row['id']) + '\n'
)
from utils.file.excel.readUtil import ReadUtil
file_name = 'file/itl_store表修改.xlsx'
data = ReadUtil.read_file(file_name, {'id': 0, 'price': 2})
sql = 'UPDATE itl_store SET price_type_name = "{price_type_name}" WHERE id = {id};'
with open(file_name + '.txt', 'a') as f:
for row in data:
f.write(sql.format(price_type_name=row['price'], id=row['id']) + '\n')
from utils.file.excel.readUtil import ReadUtil
sql_house = 'UPDATE house_info SET price_type_name = "{price_type_name}" WHERE zone_id = {zone_id} AND id = {id};'
sql_store = 'UPDATE itl_store SET price_type_name = "{price_type_name}" WHERE zone_id = {zone_id} AND id = {id};'
sql_parking = 'UPDATE itl_parking SET price_type_name = "{price_type_name}" WHERE zone_id = {zone_id} AND id = {id};'
file_name = 'file/住宅单价类型订正.csv'
data = ReadUtil.read_file(file_name, {'id': 0, 'zone_id': 1, 'price_type': 2})
print('数据总量：', len(data))
need = list()
for row in data:
need.append(
sql_house.format(price_type_name=row['price_type'],
zone_id=row['zone_id'],
id=row['id']))
with open(file_name.replace('.', '_') + '_out.txt', 'a') as f:
for row in need:
f.write(row + '\n')
from utils.file.excel.readUtil import ReadUtil
sql_update = 'UPDATE house_info SET name = "{name}" WHERE id ={id};'
data = ReadUtil.read_file('file/house_info.name.xlsx', {
'id': 0,
'name': 1,
})
for row in data:
print(sql_update.format(id=row['id'], name=row['name']))
from utils.file.excel.readUtil import ReadUtil
file_name = 'file/需要订正的账单.xlsx'
data = ReadUtil.read_file(
file_name, {
'id': 0,
'ought_amount': 8,
'real_amount': 9,
'discount_amount': 10,
'round_amount': 11,
'owe_amount': 12
})
sql = 'UPDATE itl_finance_bill ' \
'SET ought_amount = {ought_amount}, real_amount = {real_amount}, discount_amount = {discount_amount}, round_amount = {round_amount}, owe_amount = {owe_amount} ' \
'WHERE id = {id};'
with open(file_name + '.txt', 'a') as f:
for row in data:
f.write(
sql.format(ought_amount=row['ought_amount'],
real_amount=row['real_amount'],
discount_amount=row['discount_amount'],
round_amount=row['round_amount'],
owe_amount=row['owe_amount'],
id=row['id']) + '\n')
import requests
from utils.file.excel.readUtil import ReadUtil
def len_sort(elem):
return len(elem) == 11
url = 'http://127.0.0.1:8001/external/moreBatchSendSmsForCommon'
data = ReadUtil.read_file('发短信电话.xls', {
'z': 0,
'm': 1,
's': 2,
'name': 3,
'mobile': 4
}) # 原始数据
need_send_mobile = list() # 需要发送的手机号
need_send = list() # 可以发送
error = list() # 无法发送的错误的手机号
for row in data:
if len(row['mobile']) == 11:
need_send_mobile.append(row['mobile'])
need_send.append(row)
else:
if len(row['mobile']) < 11:
error.append(row)
else:
ms_array = str(row['mobile']).replace(' /', '/').replace(
' ', '/').replace('、', '/').replace(' ', '/').split('/')
from utils.file.excel.readUtil import ReadUtil
delete_sql = 'DELETE FROM itl_zone_task WHERE zone_id = "{zone_id}";'
de = ReadUtil.read_file('任务库涉及到的小区_20181016.xls', {'id': 0, 'de': 5})
zone_ids = list()
for row in de:
if row['de'] == '√':
zone_ids.append(str(int(float(row['id']))))
print(",".join(zone_ids))