-
Notifications
You must be signed in to change notification settings - Fork 0
/
properties.py
203 lines (171 loc) · 10.6 KB
/
properties.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
import psycopg2
import json
from user_functions import VarIf, convertJSONDate, getKeyValue, QueryWithSingleValue, returnNextSerialID, cleanForSQL, checkRowExists
from database import cur, conn
from user_constants import *
from files import File
class Property:
def __init__( self, domain_property_id, landShape, onMarket, address_object, areaSize, numBedrooms, numBathrooms, features, rawJson, history = None, files = None, propertyID = None ):
self.domain_property_id = domain_property_id
self.landShape = landShape
self.onMarket = onMarket
self.address_object = address_object
self.areaSize = areaSize
self.numBedrooms = VarIf( numBedrooms is None, 0, numBedrooms )
self.numBathrooms = VarIf( numBathrooms is None, 0, numBathrooms )
self.features = features
self.rawJson = rawJson
self.history = history
self.files = files
@classmethod
def initFromObject( cls, propertyObject, propertyID = None ):
if 'addressCoordinate' in propertyObject:
longitude = propertyObject['addressCoordinate']['lon']
lattitude = propertyObject['addressCoordinate']['lat']
else:
longitude = None
lattitude = None
photos = []
#Create File Array from the Photos Object inside the propertyObject
for photo in propertyObject['photos']:
propertyImage = File( FILE_TYPE_Images, OBJECT_Property, propertyID, None, 'Property ' + photo['imageType'], None )
propertyImage.addImageDetails( photo['advertId'], photo['date'], photo['fullUrl'], None )
photos.append( propertyImage )
new_property = cls( propertyObject['id'], propertyObject.get( "cadastreType" ), VarIf( propertyObject['status'] == PROPERTY_MARKET_STATUS_ONMARKET, True, False ),
Address( propertyObject['address'], propertyObject['streetName'], propertyObject['suburb'], propertyObject['streetNumber'],
propertyObject.get( "zone" ), propertyObject.get( "lotNumber" ), longitude, lattitude, None ), propertyObject.get( "areaSize" ), propertyObject.get( 'numBedrooms'),
propertyObject.get( 'numBathrooms' ), propertyObject.get( 'features' ), json.dumps( propertyObject ), propertyObject.get( 'history' ), photos )
return new_property
def saveProperty( self, commit ):
#store the propertyObject inside a text file in case we want to debug afterwards.
debug = open( "debug.txt", "w")
debug.write( json.dumps( self.rawJson ))
try:
#Handle the address first, if required.
if self.address_object.address_id is None:
new_address_id = self.address_object.storeAddress()
if new_address_id is None:
return None
#Get the next property id
next_property_id = returnNextSerialID( 'properties', 'property_id' )
if not self.saveSalesHistory( next_property_id ):
return None
#Save the Files next.
for item in self.files:
item.keyval1 = next_property_id
item.storeFile( False )
#Store the Features Next
for feature in self.features:
storeFeatures( next_property_id, OBJECT_Property, feature )
#Now save the property.
cur.execute( """INSERT INTO properties( domain_prop_id, shape_of_land, on_market, address_id, area_size, num_bedrooms, num_bathrooms, entered_when, raw_json )
VALUES( %s, %s, %s, %s, %s, %s, %s, current_timestamp, %s )""",
( self.domain_property_id, self.landShape, self.onMarket, new_address_id, self.areaSize, self.numBedrooms, self.numBathrooms, self.rawJson ) )
if commit:
conn.commit()
return next_property_id
except(Exception, psycopg2.DatabaseError ) as error:
print( "Error in INSERTING Property with Domain Property ID " + str( self.domain_property_id ) + "\n" + error )
return None
def saveSalesHistory( self, new_property_id = None ):
#Need to test if we have a property id to save against.
if new_property_id is None:
raise RuntimeError( "Invalid Property ID for Property " + self.domain_property_id )
try:
if self.history is not None:
for history in self.history:
sales_history_insert_statement = f""" INSERT INTO property_sales_history( property_id, agency_id, sale_date, days_on_market, documented_as_sold, sale_price
reported_as_sold, details_suppressed, price_suppressed, sale_type, agency_name,
property_sales_type, listings_id )
VALUES( {new_property_id}, {self.history['apmAgencyId']}, to_timestamp( '{self.history['date']}', 'YYYY-MM-DD'), {self.history['daysOnMarket']},
{self.history['documentedAsSold']}, {self.history['price']}, {self.history['reportedAsSold']},
{self.history['suppressDetails']}, {self.history['suppressPrice']}, '{self.history['type']}', '{self.history['agency']}',
'{self.history['propertyType']}', {self.history['id']} ) """
cur.execute( sales_history_insert_statement, "")
return True
except(Exception, psycopg2.DatabaseError) as error:
print( "Error in INSERTING Sales History for Property with Property ID " + str( new_property_id ) + "\n" + "Error: " + error )
return False
### Data Storage Functions
class Address:
def __init__( self, full_address, street_name, suburb_name, street_number = None, zone = None, lot_number = None, long = None, lat = None, address_id = None, state = None, postCode = None ):
self.full_address = full_address
self.street_name = street_name
self.suburb_name = suburb_name
#Get the Suburb_ID here.
self.suburb_id = QueryWithSingleValue( "suburbs", "name", self.suburb_name, "suburb_id", True )
# If the Suburb ID
if self.suburb_id == None:
#We need to insert a new suburb into the Suburbs Table
self.suburb_id = self.storeSuburb( suburb_name, state, postCode )
self.street_number = street_number
self.zone = zone
self.lot_number = lot_number
self.long = long
self.lat = lat
self.address_id = address_id
def returnSuburbInformation( self ):
"""
This Function information about the suburb represented by self.suburb_id.
This returns both the state and postcode of the suburb in a tuple.
Index[0] = Postcode
Index[1] = State
Keyword arguements:
self -- Keyword Name.
"""
try:
cur.execute( f"SELECT s.postcode, s.state FROM suburbs s WHERE s.suburb_id = {self.suburb_id}", "" )
result = cur.fetchone()
suburb_tuple = ( result[0], result[1] )
return suburb_tuple
except( Exception, psycopg2.DatabaseError ) as error:
print (error)
def storeSuburb( self, suburbName, state, postCode ):
try:
new_suburb_id = returnNextSerialID( 'suburbs', 'suburb_id' )
cur.execute( """ INSERT INTO suburbs( name, state, is_completed, postcode )
VALUES( %s, %s, %s, %s ) """,
( suburbName, state, False, postCode ) )
return new_suburb_id
except( Exception, psycopg2.DatabaseError ) as error:
print( error )
def storeAddress( self ):
try:
address_id = returnNextSerialID( 'address', 'address_id' )
cur.execute( """INSERT INTO address( full_address, street_name, street_number, suburb_id, zone, lot_number, latlong )
VALUES( %s, %s, %s, %s, %s, %s, %s )""",
( self.full_address, self.street_name, self.street_number, self.suburb_id, self.zone, self.lot_number,
VarIf( self.long is None or self.lat is None, None, f"SRID=4326;Point({self.long} {self.lat})" ) ) )
return address_id
except(Exception, psycopg2.DatabaseError) as error:
print( "Error in INSERTING Address for Address ID " + "\n" + "Error: " + error )
return None
def storeFeatures( id, object_type, feature ):
"""
Stores a list of tuple of features inside the object_features table. For each feature inside the features tuple, it first queries the features_lkp table to check if that feature already exists inside the table.
If it does, it retrieves that feature's feature_id. Otherwise, it inserts that feature into the features_lkp table and returns the newly inserted feature's feature_id.
It then uses the feature's feature_id and the id of the object to create a linking row inside the object_features table
Keyword arguements:
id -- ID of the Object that the features are being linked against.
object_type -- Object Type of the Object that the features are being linked against.
features -- Tuple of features that are going to be linked against the object.
"""
try:
#Only insert if the feature doesn't exist.
check_feature_exists = f"SELECT feature_id FROM features_lkp WHERE UPPER( description ) = '{str.upper( feature )}'"
cur.execute( check_feature_exists, "" )
row = cur.fetchone()
if row == None:
feature_id = returnNextSerialID( 'object_features', 'feature_id' )
#We need to store the new feature inside the features table.
cur.execute( f""" INSERT INTO features_lkp( feature_id, description) VALUES( {feature_id}, '{cleanForSQL(feature)}' )""", "" )
else:
feature_id = row[0]
#Once we've acquired the feature_id...
object_features_insert_statement = f""" INSERT INTO object_features( id, object_type, feature_id, entered_when, entered_who )
VALUES( {id}, {object_type}, {feature_id}, current_timestamp, 1 )"""
cur.execute( object_features_insert_statement, "")
return True
except(Exception, psycopg2.DatabaseError) as error:
print( "Error in INSERTING Feature " + feature + "\n" + "Error: " + error )
return False