def __init__(self, houseIdOrCode):
houseId = Mysql().getOne(
"select house_id from house where (house_id='%s' or house_code like '%s%s') "
"and deleted=0 " % (houseIdOrCode, houseIdOrCode, '%'))
if 1 != len(houseId):
raise ValueError(
'the house_code is not one but: %s! search for:%s' %
(len(houseId), houseIdOrCode))
sql = "select * from house where house_id='%s' and deleted=0" % houseId[
0]
self.house_info = Mysql().query(sql)[0]
self.house_id = self.house_info['house_id']
self.residential_id = self.house_info['residential_id']
self.building_id = self.house_info['building_id']
self.city_code = self.house_info['city_code']
self.area_code = self.house_info['area_code']
self.house_code = self.house_info['house_code']
self.unit_id = self.house_info['unit_id']
self.unit = self.house_info['unit']
self.floor_id = self.house_info['floor_id']
self.floor = self.house_info['floor']
self.house_no_id = self.house_info['house_no_id']
self.house_no = self.house_info['house_no']
self.property_name = self.house_info['property_name']
self.right_num = self.house_info['right_num'] # 产权证号
self.build_area = self.house_info['build_area']
self.orientation = self.house_info['orientation'] # 朝向
self.property_type = self.house_info['property_type'] # 物业类型
self.property_use = self.house_info['property_use'] # 物业用途
self.property_right = self.house_info['property_right'] # 物业熟悉
def __init__(self, apartmentIdOrCode):
apartmentId = Mysql().getAll(
"select apartment_id,house_id from apartment where (apartment_id='%s' or apartment_code "
"like '%s%s') and deleted=0 and is_active='Y'" %
(apartmentIdOrCode, apartmentIdOrCode, '%'))
if 1 != len(apartmentId):
raise ValueError(
'apartment_id num is not one but: %s! search for:%s' %
(len(apartmentId), apartmentIdOrCode))
super(ApartmentInfo, self).__init__(apartmentId[0][1])
sql = "select * from apartment where apartment_id='%s' and deleted=0" % apartmentId[
0][0]
self.apartment_info = sqlbase.query(sql)[0]
self.apartment_id = self.apartment_info['apartment_id']
self.room_id = self.apartment_info['room_id']
self.apartment_code = self.apartment_info['apartment_code']
self.apartment_type = self.apartment_info['apartment_type']
self.rent_type = self.apartment_info['rent_type']
self.rent_status = self.apartment_info['rent_status']
self.fitment_cost = self.apartment_info['fitment_cost']
self.apartment_cost = self.apartment_info['apartment_cost']
self.is_active = self.apartment_info['is_active']
self.rent_price = self.apartment_info['rent_price']
self.fitment_type = self.apartment_info['fitment_type']
self.house_contract_id = self.apartment_info['house_contract_id']
self.zone_id = self.apartment_info['zone_id']
self.set_delivery_date = self.apartment_info['set_delivery_date']
self.total_cost = self.apartment_info['total_cost']
self.fitment_status = self.apartment_info['fitment_status']
self.reform_way = self.apartment_info['reform_way']
def __init__(self, contract_id):
super(ApartmentContractEndInfo, self).__init__(contract_id)
sql = "select * from apartment_contract_end where contract_id='%s' and deleted=0" % contract_id
self.apartment_contract_end_info = Mysql().query(sql)[0]
self.end_id = self.apartment_contract_end_info['end_id']
self.end_contract_num = self.apartment_contract_end_info[
'end_contract_num']
self.end_date = self.apartment_contract_end_info['end_date']
self.end_type = self.apartment_contract_end_info['end_type']
self.payer_bank_no = self.apartment_contract_end_info['payer_bank_no']
self.payer_bank_location = self.apartment_contract_end_info[
'payer_bank_location']
self.receivable_total = self.apartment_contract_end_info[
'receivable_total']
self.receivable_date = self.apartment_contract_end_info[
'receivable_date']
self.payable_totle = self.apartment_contract_end_info['payable_totle']
self.payable_date = self.apartment_contract_end_info['payable_date']
self.end_reason_type = self.apartment_contract_end_info[
'end_reason_type']
self.contract_end_type = self.apartment_contract_end_info[
'contract_end_type'] # payment_type
self.bank = self.apartment_contract_end_info['bank']
self.pay_object = self.apartment_contract_end_info['pay_object']
self.liquidated_receivable = self.apartment_contract_end_info[
'liquidated_receivable']
self.end_type_detail = self.apartment_contract_end_info[
'end_type_detail']
self.payer = self.apartment_contract_end_info['payer']
self.end_audit_status = self.apartment_contract_end_info[
'audit_status']
def __init__(self, contractIdOrNum):
super(HouseContractEndInfo, self).__init__(contractIdOrNum)
sql = "select * from house_contract_end where contract_id='%s' and deleted=0" % self.house_contract_id
self.house_contract_end_info = Mysql().query(sql, nullThrow=False)[0]
if not self.house_contract_end_info:
consoleLog(
"there is no house_contract_end,contract_id: %s" %
self.house_contract_id, 'e')
return
self.end_id = self.house_contract_end_info['end_id']
self.end_date = self.house_contract_end_info['end_date']
self.end_type = self.house_contract_end_info['end_type']
self.pay_owner = self.house_contract_end_info['pay_owner']
self.pay_owner_bank_no = self.house_contract_end_info[
'pay_owner_bank_no']
self.pay_owner_bank_location = self.house_contract_end_info[
'pay_owner_bank_location']
self.receivable_total = self.house_contract_end_info[
'receivable_total']
self.payable_totle = self.house_contract_end_info['payable_totle']
self.payable_date = self.house_contract_end_info['payable_date']
self.receivable_date = self.house_contract_end_info['receivable_date']
self.end_audit_status = self.house_contract_end_info['audit_status']
self.__end_audit_status = self.house_contract_end_info['audit_status']
self.pay_object = self.house_contract_end_info['pay_object']
self.bank = self.house_contract_end_info['bank']
def get_apartment_detail(apartment_id, apartment_type):
"""获取房源属性"""
if apartment_type == 'SHARE':
shareSql = "SELECT hr.room_orientation, a.rooms, hr.room_area, h.floor, ( SELECT rbf.floor_name FROM residential_building_floor rbf LEFT JOIN residential_building_unit rbu ON rbu.unit_id = rbf.unit_id WHERE rbu.building_id = rb.building_id ORDER BY rbf.floor_name + 0 DESC LIMIT 1 ) total_floor, a.bathroom_property, a.fitment_level, a.balcony_property FROM apartment a INNER JOIN house h ON a.house_id = h.house_id LEFT JOIN residential r ON r.residential_id = h.residential_id LEFT JOIN residential_building rb ON rb.building_id = h.building_id INNER JOIN house_room hr ON a.room_id = hr.room_id WHERE a.deleted = 0 AND a.apartment_id = '{}'".format(
apartment_id)
return Mysql().query(shareSql)
else:
entireSql = "SELECT h.orientation, a.rooms, h.build_area, a.side_suite, h.floor, " \
"(SELECT rbf.floor_name FROM isz_erp.residential_building_floor rbf " \
"LEFT JOIN isz_erp.residential_building_unit rbu ON rbu.unit_id = rbf.unit_id " \
"WHERE rbu.building_id = rb.building_id ORDER BY rbf.floor_name + 0 DESC LIMIT 1 ) totle_floors, a.fitment_level " \
"FROM isz_erp.apartment a INNER JOIN isz_erp.house h ON a.house_id = h.house_id " \
"LEFT JOIN isz_erp.residential r ON r.residential_id = h.residential_id " \
"LEFT JOIN isz_erp.residential_building rb ON rb.building_id = h.building_id " \
"WHERE a.deleted = 0 AND a.is_active = 'Y' AND a.apartment_id = '{}'".format(apartment_id)
return Mysql().query(entireSql)
def step_status(self, step):
"""委托合同分步审核状态"""
step_status = Mysql().getOne(
"select status from house_contract_step_audit_status where contract_id = '%s' and "
"step = '%s' and deleted=0 order by step_id desc limit 1" %
(self.house_contract_id, step))[0]
return step_status
def room_no(self):
"""房屋编号"""
if self.rent_type == 'SHARE':
sql = "select (select dict_value from sys_dict_item where room_no=dict_e_value and deleted=0 limit 1) room_no " \
"from house_room where room_id='%s'" % self.room_id
return Mysql().getOne(sql)[0]
else:
return None
def config_suppliers(self):
"""订单所有物品供应商id"""
sql = "select distinct supplier_id from %s.new_stuff_list where project_id='%s' and deleted=0" % (
get_conf('db', 'decoration_db'), self.project_id)
supplier_ids = Mysql().getAll(sql)
if not supplier_ids:
consoleLog(u'订单配置供应商不存在')
return supplier_ids
def __get_list():
if not ContractBase.FileType._type_id_dict:
sql = "select file_type, file_type_id from contract_file_type where deleted=0"
dicts = Mysql().query(sql)
for dict in dicts:
ContractBase.FileType._type_id_dict[
dict['file_type']] = dict['file_type_id']
return ContractBase.FileType._type_id_dict
def searchByProjectId(project_id):
"""根据project_id查询工程订单
:param project_id
:return 工程订单对象
"""
sql = "select info_id from %s.new_decoration_project where project_id='%s' and deleted=0" % (
get_conf('db', 'decoration_db'), project_id)
info_id = Mysql().getOne(sql)[0]
return DecorationProjectInfo.searchByInfoId(info_id)
def receivables(self):
"""出租合同对应所有未删除的应收"""
sql = "select * from apartment_contract_receivable where contract_id='%s' and deleted=0" % self.apartment_contract_id
receivables = Mysql().query(sql)
receivablesVo = []
for receivable in receivables:
receivableVo = Receivable(receivable['receivable_id'])
receivablesVo.append(receivableVo)
return receivablesVo
def contract_field(contractNumOrId, field):
"""查询合同字段"""
fieldRetrun = Mysql().getOne(
"select %s from apartment_contract where (contract_num='%s' or contract_id='%s') and deleted=0"
% (field, contractNumOrId, contractNumOrId))
if fieldRetrun:
return fieldRetrun[0]
else:
consoleLog(u'合同：%s 对应字段：%s 查询结果为空' % (contractNumOrId, field))
def searchByInfoId(info_id):
"""根据info_id查询工程订单
:param info_id
:return 工程订单对象
"""
sql = "select contract_id from %s.decoration_house_info where info_id='%s' and deleted=0" % (
get_conf('db', 'decoration_db'), info_id)
contract_id = Mysql().getOne(sql)[0]
return DecorationProjectInfo(contract_id)
def apartment_contract(self):
"""房源对应出租合同"""
if 'RENTED' == self.rent_status:
contract_id = Mysql().getOne(
"select a.contract_id from apartment_contract a inner join apartment_contract_relation b on a.contract_id=b.contract_id "
"inner join apartment c on b.apartment_id=c.apartment_id where c.apartment_id='%s' and a.deleted=0 and a.is_active='Y'"
% self.apartment_id)[0]
return ApartmentContractInfo(contract_id)
else:
return None
def __init__(self, user_id):
sql = "select * from sys_user a inner join sys_department b on a.dep_id=b.dep_id " \
"where a.user_id='%s' and a.user_status='INCUMBENCY'" % user_id
self.user_info = Mysql().query(sql)[0]
self.user_name = self.user_info['user_name']
self.user_id = self.user_info['user_id']
self.user_phone = self.user_info['user_phone']
self.user_status = self.user_info['user_status']
self.dep_id = self.user_info['dep_id']
self.dep_name = self.user_info['dep_name']
def zones(self):
sql = "select zone_id from %s.funcation_zone where info_id='%s' and deleted=0" % (
get_conf('db', 'decoration_db'), self.info_id)
zone_ids = Mysql().getAll(sql)
zones = []
if zone_ids:
for zone_id in zone_ids:
zones.append(self.Zone(zone_id))
else:
consoleLog(u'房屋未分割')
return zones
def __init__(self, person_id):
sql = "select * from customer_person where person_id='%s'" % person_id
self.customer_info = Mysql().query(sql)[0]
self.person_id = person_id
self.customer_num = self.customer_info['customer_num']
self.address = self.customer_info['address']
self.gender = self.customer_info['gender']
self.customer_name = self.customer_info['customer_name']
self.phone = self.customer_info['phone']
self.card_type = self.customer_info['card_type']
self.id_card = self.customer_info['id_card']
class User(Enum):
"""登录用户信息"""
exhouseSql = Mysql().getAll(
"select a.user_name,a.user_id,a.dep_id,b.dep_name from sys_user a inner join sys_department b on a.dep_id=b.dep_id "
"where a.user_phone='%s' and a.user_status='INCUMBENCY'" %
get_conf('sysUser', 'userphone'))[0]
UID = exhouseSql[1]
DID = exhouseSql[2]
NAME = exhouseSql[0]
D_NAME = exhouseSql[3]
def get_template_ratio(template_type):
"""获取模板系数"""
ratioTypeSQl = "SELECT a.ratio_type,a.type_id from standard_price_ratio_type a inner JOIN standard_price_ratio b on a.config_id=b.config_id " \
"where b.ratio_status='VALID' and b.template_type='%s'" % template_type
ratioTypesList = Mysql().query(ratioTypeSQl)
ratioTypes = {}
for type in ratioTypesList:
ratioDetailSQl = "SELECT ratio_key,ratio_key_name,ratio_value,rule from standard_price_ratio_detail where type_id='%s'" % type.get(
'type_id')
ratioDetailList = Mysql().query(ratioDetailSQl)
ratioDetails = {}
for detail in ratioDetailList:
if type.get('ratio_type') == 'ORIENTATION':
ratioDetails[detail.get('ratio_key')] = detail.get(
'ratio_value')
elif type.get('ratio_type') in ('USABLE_AREA', 'AREA'): # 面积
ratioDetails[detail.get('rule').replace('#x#', 'area').replace(
'&&', '&')] = detail.get('ratio_value')
elif type.get('ratio_type') in ('FLOOR_RATIO', 'FLOOR',
'HOUSE_TYPE'): # 楼层户型
ratioDetails[detail.get('rule').replace(
'#x#',
'floor').replace('&&', '&').replace('||', 'or').replace(
'#y#', 'total_floor')] = detail.get('ratio_value')
elif type.get('ratio_type') in ('FITMENT', ): # 装修档次
ratioDetails[detail.get('rule').replace(
"'#x#'",
'fitment_level').replace('&&',
'&')] = detail.get('ratio_value')
elif type.get('ratio_type') in ('SEASONALITY', ): # 淡旺季
ratioDetails[detail.get('ratio_key_name')] = detail.get(
'ratio_value')
elif type.get('ratio_type') in ('HOUSE_TYPE',
'REMOULD_HOUSE_TYPE'): # 户型
ratioDetails[detail.get('rule').replace(
'#x#', 'rooms')] = detail.get('ratio_value')
else:
ratioDetails[get_dict_e_value(
detail.get('ratio_key_name'))] = detail.get('ratio_value')
ratioTypes[type.get('ratio_type')] = ratioDetails
return ratioTypes
def test():
"""名称描述"""
searchSQl = "select verify_id from isz_erp_finance.house_charge_account_verify where account_identify='UNKNOW' and deleted=0"
result = Mysql().getAll(searchSQl)
url = "http://rsm.ishangzu.com/isz_balanceofaccount/HouseChargeAccountController/account/officialAddress/{}"
for index, item in enumerate(result):
try:
newUrl = url.format(item)
myRequest(newUrl, method='put')
consoleLog("{}:verify_id:{}已调浦发接口查询".format(index + 1, item))
time.sleep(1)
except Exception as e:
consoleLog(e.message)
def getUserInfo():
"""获取登录用户信息"""
sql = "select a.user_name,a.user_id,a.dep_id,b.dep_name from sys_user a inner join sys_department b " \
"on a.dep_id=b.dep_id where a.user_phone='%s' and a.user_status='INCUMBENCY'" % get_conf('sysUser',
'userphone')
exhouseSql = Mysql().getAll(sql)[0]
userInfo = {
'user_name': exhouseSql[0],
'uid': exhouseSql[1],
'dep_name': exhouseSql[3],
'did': exhouseSql[2],
}
return userInfo
def payables(self, audit_status='NOTAUDIT', money_type='RENT'):
""" 委托合同应付，默认读取未审核的应付租金
:return 应付ID的集合
"""
payablesVo = []
sql = "select * from house_contract_payable where contract_id='%s' and deleted=0" % self.house_contract_id
payables = Mysql().query(sql, resarch=True)
for payable in payables:
payableVo = Payable(payable['payable_id'])
if not audit_status or audit_status == payableVo.audit_status:
if not money_type or money_type == payableVo.money_type:
payablesVo.append(payableVo)
return payablesVo
def __init__(self, receivable_id):
sql = "select * from apartment_contract_receivable where receivable_id='%s' and deleted=0" % receivable_id
receivable = Mysql().query(sql)[0]
self.receivable_id = receivable['receivable_id']
self.contract_id = receivable['contract_id']
self.finance_num = receivable['finance_num']
self.money_type = receivable['money_type']
self.start_date = receivable['start_date']
self.end_date = receivable['end_date']
self.receivable_date = receivable['receivable_date']
self.receivable_money = receivable['receivable_money']
self.__end_status = receivable['end_status']
self.end_status = receivable['end_status']
self.receivable_from = receivable['receivable_from']
self.end_type = receivable['end_type']
def __init__(self, contract_id):
sql = "select * from house_contract_landlord where contract_id='%s' and deleted=0" % contract_id
self.landlord_info = Mysql().query(sql)[0]
self.contract_landlord_id = self.landlord_info['contract_landlord_id']
self.phone = self.landlord_info['phone']
self.other_contact = self.landlord_info['other_contact']
self.card_type = self.landlord_info['card_type']
self.landlord_name = self.landlord_info['landlord_name']
self.id_card = self.landlord_info['id_card']
self.landlord_type = self.landlord_info['landlord_type']
self.mailing_address = self.landlord_info['mailing_address']
self.is_leaser = self.landlord_info['is_leaser']
self.email = self.landlord_info['email']
self.emergency_name = self.landlord_info['emergency_name']
self.emergency_phone = self.landlord_info['emergency_phone']
def __init__(self, zone_id):
sql = "select * from %s.funcation_zone where zone_id='%s' and deleted=0" % (
get_conf('db', 'decoration_db'), zone_id)
self.zone_id = zone_id
self.zone_info = Mysql().query(sql[0])
self.zone_type = self.zone_info['zone_type']
self.room_no = self.zone_info['room_no']
self.usearea = self.zone_info['usearea']
self.zone_orientation = self.zone_info['zone_orientation']
self.have_toilet = self.zone_info['have_toilet']
self.toilet_area = self.zone_info['toilet_area']
self.have_balcony = self.zone_info['have_balcony']
self.balcony_area = self.zone_info['balcony_area']
self.window_area = self.zone_info['window_area']
self.window_type = self.zone_info['window_type']
self.zone_status = self.zone_info['zone_status']
self.is_fictitious_room = self.zone_info['is_fictitious_room']
def update(self):
"""更新订单部分字段"""
sql = "select * from %s.new_decoration_project where info_id='%s' " % (
get_conf('db', 'decoration_db'), self.info_id)
newInfo = Mysql().query(sql)[0]
self.config_order_no = newInfo['config_order_no']
self.one_level_nodes = newInfo['one_level_nodes']
self.place_order_date = newInfo['place_order_date']
self.config_submit_uid = newInfo['config_submit_uid']
self.config_submit_uname = newInfo['config_submit_uname']
self.construct_uid = newInfo['construct_uid']
self.construct_uname = newInfo['construct_uname']
self.closed_water_test_result = newInfo['closed_water_test_result']
self.complete_two_nodes = newInfo['complete_two_nodes']
self.config_list_status = newInfo['config_list_status']
self.config_progress = newInfo['config_progress']
self.current_one_node = newInfo['current_one_node']
def __init__(self, payable_id):
self.payable_id = payable_id
sql = "select * from house_contract_payable where payable_id='%s' and deleted=0" % self.payable_id
payable = Mysql().query(sql)[0]
self.contract_id = payable['contract_id']
self.finance_num = payable['finance_num']
self.money_type = payable['money_type']
self.rent_start_date = payable['rent_start_date']
self.rent_end_date = payable['rent_end_date']
self.payable_date = payable['payable_date']
self.payable_amount = payable['payable_amount'] # 应付总额
self.end_status = payable['end_status'] # 完结状态
self.__end_status = payable['end_status'] # 完结状态
self.end_time = payable['end_time'] # 完结时间
self.audit_status = payable['audit_status'] # 租金审核状态
self.__audit_status = payable['audit_status'] # 租金审核状态
self.end_type = payable['end_type'] # 终止状态
self.deduction_amount = payable['deduction_amount'] # 扣款金额
def __init__(self, orderNumOrId):
sql = "select * from %s.repairs_order where (order_no='%s' or order_id='%s') and deleted=0" % (
get_conf('db', 'rsm_db'), orderNumOrId, orderNumOrId)
self.repairs_order_info = Mysql().query(sql)[0]
self.order_id = self.repairs_order_info['order_id']
self.order_no = self.repairs_order_info['order_no']
self.apartment_id = self.repairs_order_info['apartment_id']
self.apartment_type = self.repairs_order_info['apartment_type']
self.rent_status = self.repairs_order_info['rent_status']
self.rent_type = self.repairs_order_info['rent_type']
self.house_id = self.repairs_order_info['house_id']
self.room_id = self.repairs_order_info['room_id']
self.apartment_contract_id = self.repairs_order_info[
'apartment_contract_id']
self.apartment_contract_num = self.repairs_order_info[
'apartment_contract_num']
self.customer_id = self.repairs_order_info['customer_id']
self.house_contract_id = self.repairs_order_info['house_contract_id']
self.house_address = self.repairs_order_info['house_address']
self.customer_phone = self.repairs_order_info['customer_phone']
def __init__(self, contractNumOrId):
super(DecorationProjectInfo, self).__init__(contractNumOrId)
sql = "select * from %s.new_decoration_project where info_id='%s' " % (
get_conf('db', 'decoration_db'), self.info_id)
self.project_info = Mysql().query(sql, resarch=True)[0]
self.project_id = self.project_info['project_id']
self.project_no = self.project_info['project_no']
self.config_order_no = self.project_info['config_order_no']
self.one_level_nodes = self.project_info['one_level_nodes']
self.place_order_date = self.project_info['place_order_date']
self.config_submit_uid = self.project_info['config_submit_uid']
self.config_submit_uname = self.project_info['config_submit_uname']
self.construct_uid = self.project_info['construct_uid']
self.construct_uname = self.project_info['construct_uname']
self.closed_water_test_result = self.project_info[
'closed_water_test_result']
self.complete_two_nodes = self.project_info['complete_two_nodes']
self.config_list_status = self.project_info['config_list_status']
self.config_progress = self.project_info['config_progress']
self.current_one_node = self.project_info['current_one_node']
def __init__(self, contractNumOrId):
sql = "select * from %s.decoration_house_info where (contract_num='%s' or contract_id='%s') and deleted=0" % (
get_conf('db', 'decoration_db'), contractNumOrId, contractNumOrId)
self.decoration_house_info = Mysql().query(sql, resarch=True)[0]
self.info_id = self.decoration_house_info['info_id']
self.house_code = self.decoration_house_info['house_code']
self.house_id = self.decoration_house_info['house_id']
self.build_area = self.decoration_house_info['build_area']
self.entrust_type = self.decoration_house_info['entrust_type']
self.address = self.decoration_house_info['address']
self.contract_id = self.decoration_house_info['contract_id']
self.contract_num = self.decoration_house_info['contract_num']
self.contract_type = self.decoration_house_info['contract_type']
self.create_time = self.decoration_house_info['create_time']
self.create_uid = self.decoration_house_info['create_uid']
self.entrust_end_date = self.decoration_house_info['entrust_end_date']
self.entrust_start_date = self.decoration_house_info[
'entrust_start_date']
self.housekeep_mange_uid = self.decoration_house_info[
'housekeep_mange_uid']
self.city_code = self.decoration_house_info['city_code']
self.city_name = self.decoration_house_info['city_name']