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 __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, *args, **kw):
# 设置是否打印日志
self.log = kw['log'] if 'log' in kw else True
if 'dbType' in kw:
if 'MySQL' == kw['dbType']:
self.dao = MySQLUtil(**kw['config'])
else:
log.error('dbType error')
else:
self.dao = MySQLUtil(**MySQLConfig.localhost())
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)
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 __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 __init__(self):
self.dao = DaoUtils(**{
'dbType': 'MySQL',
'config': MySQLConfig.localhost()
})
sql += ','.join(value_list)
self.handle_sql_log(sql)
return self.dao.execute(sql)
# 更新数据
def customizable_modify(self, table, db_dict, where_dict, time=True):
if time:
db_dict['gmt_modify'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# 更新的内容
value_list = list()
for key, value in db_dict.items():
value_list.append('%s = "%s"' % (key, str(value)))
# 判断内容
where_list = list()
where_list.append('1 = 1')
for key, value in where_dict.items():
where_list.append('%s = "%s"' % (key, str(value)))
# 更新的条件
sql = 'update %s set %s ' % (table, ','.join(value_list))
sql += 'where %s ' % (' and '.join(where_list))
print(sql)
return self.dao.execute(sql)
if __name__ == '__main__':
dao_config = {'dbType': 'MySQL', 'config': MySQLConfig.localhost()}
dao = DaoUtils(**dao_config)
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}");'