Exemple #1
0
    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;')
Exemple #2
0
    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)
Exemple #4
0
    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)
Exemple #6
0
    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')
Exemple #7
0
    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')
Exemple #8
0
    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')
Exemple #9
0
    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'])))
Exemple #11
0
    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')
Exemple #12
0
    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
Exemple #13
0
    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')
Exemple #14
0
             ' 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:
Exemple #16
0
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月'
Exemple #17
0
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']))
Exemple #18
0
        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'],
Exemple #19
0
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'
        )
Exemple #20
0
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')
Exemple #21
0
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')
Exemple #22
0
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']))
Exemple #23
0
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')
Exemple #24
0
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('/')
Exemple #25
0
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))