def find_page_of_publishable_rows_fitting_criteria(self, data):
"""TODO."""
# log.debug(data)
query = SESSION.query(Cleaned).filter(
Cleaned.detail_publish.is_(True)
).filter(
(Cleaned.sellers.ilike('%%%s%%' % data['name_address']))
| (Cleaned.buyers.ilike('%%%s%%' % data['name_address']))
| (Cleaned.address.ilike('%%%s%%' % data['name_address']))
| (Cleaned.instrument_no.ilike('%%%s%%' % data['name_address']))
).filter(Cleaned.neighborhood.ilike(
'%%%s%%' % data['neighborhood'])).filter(
Cleaned.zip_code.ilike('%%%s%%' % data['zip_code'])).filter(
Cleaned.document_date >= '%s' % data['begin_date']).filter(
Cleaned.document_date <= '%s' %
data['end_date']).filter(
Cleaned.amount >= '%s' %
data['amount_low']).filter(
Cleaned.amount <= '%s' %
data['amount_high']).order_by(
desc(Cleaned.document_date)).offset(
'%d' % int(data['page_offset']
) # convert unicode to int
).limit('%d' %
int(data['page_length'])).all()
# log.debug(query)
SESSION.close()
return query
def find_all_publishable_rows_fitting_criteria(self, data):
"""TODO."""
# log.debug(data)
query = SESSION.query(Cleaned).filter(
Cleaned.detail_publish.is_(True)).filter(
(Cleaned.sellers.ilike('%%%s%%' % data['name_address']))
| (Cleaned.buyers.ilike('%%%s%%' % data['name_address']))
| (Cleaned.address.ilike('%%%s%%' % data['name_address']))
| (Cleaned.instrument_no.ilike('%%%s%%' % data['name_address'])
)).filter(
Cleaned.neighborhood.ilike(
'%%%s%%' % data['neighborhood'])).filter(
Cleaned.zip_code.ilike(
'%%%s%%' % data['zip_code'])).filter(
Cleaned.document_date >= '%s' %
data['begin_date']).filter(
Cleaned.document_date <= '%s' %
data['end_date']).filter(
Cleaned.amount >= '%s' %
data['amount_low']).filter(
Cleaned.amount <= '%s' %
data['amount_high']).all()
# log.debug(query)
SESSION.close()
return query
def get_existing_until_date(self):
"""TODO: Docstring."""
query_until_date = SESSION.query(
Detail.document_recorded
).order_by(
Detail.document_recorded.desc()
).limit(1).all()
# Check if any records at all
if len(query_until_date) == 0:
# Make it so initialized date range will start from beginning.
until_date = OPENING_DATE - timedelta(days=1)
# log.debug(until_date)
# log.debug(type(until_date))
else:
log.debug(len(query_until_date))
for row in query_until_date:
# TODO: will this fail w/o .one()?
until_date = row.document_recorded
# log.debug(until_date)
# log.debug(type(until_date))
# log.debug(until_date)
SESSION.close()
return until_date
def get_sale(self, instrument_no):
"""TODO."""
data = {}
data['update_date'] = self.get_last_updated_date()
query = SESSION.query(Cleaned).filter(
Cleaned.instrument_no == '%s' % (instrument_no)).filter(
Cleaned.detail_publish.is_(True) # Only publish trusted data
).all()
for row in query:
row.amount = get_num_with_curr_sign(row.amount)
row.document_date = ymd_to_full_date(
(row.document_date).strftime('%Y-%m-%d'), no_day=True)
# address = row.address
# location_info = row.location_info
data['assessor_publish'] = row.assessor_publish
# newrows = query
features = self.build_features_json(query)
jsdata = {"type": "FeatureCollection", "features": features}
SESSION.close()
if len(query) == 0:
return None, None, None
else:
return data, jsdata, query
def join_subqueries(self):
"""Run a JOIN on subqueries."""
log.debug('join_subqueries')
subq_vendees = self.get_vendees()
subq_vendors = self.get_vendors()
subq_location = self.get_locations()
log.debug('query...')
query = SESSION.query(
Detail.document_id, Detail.amount, Detail.document_date,
Detail.document_recorded, Detail.instrument_no,
Detail.detail_publish, Detail.permanent_flag,
subq_vendees.c.buyers, subq_vendors.c.sellers,
subq_location.c.location_publish, subq_location.c.address,
subq_location.c.location_info
# TODO: Once SQLAlchemy supports WITHIN GROUP, uncomment these.
# subq_location.c.zip_code,
# subq_location.c.latitude,
# subq_location.c.longitude,
# subq_location.c.neighborhood
).join(subq_vendees).join(subq_vendors).join(subq_location).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)).filter(
Detail.document_recorded <= '{}'.format(self.until_date)).all(
)
log.debug('len(query): %d', len(query))
SESSION.close()
return query
def get_locations(self):
"""Return SQL query of locations table for given date range."""
log.debug('get_locations')
subquery = SESSION.query(
Location.document_id,
func.bool_and(Location.location_publish).label('location_publish'),
func.string_agg(
cast(Location.street_number, Text) + ' ' +
cast(Location.address, Text), '; ').label('address'),
func.string_agg(
'Unit: ' + cast(Location.unit, Text) + ', ' + 'Condo: ' +
cast(Location.condo, Text) + ', ' + 'Weeks: ' +
cast(Location.weeks, Text) + ', ' + 'Subdivision: ' +
cast(Location.subdivision, Text) + ', ' + 'District: ' +
cast(Location.district, Text) + ', ' + 'Square: ' +
cast(Location.square, Text) + ', ' + 'Lot: ' +
cast(Location.lot, Text), '; ').label('location_info')
# todo: Once SQLAlchemy supports this, add these fields this way.
# 'mode() WITHIN GROUP (ORDER BY locations.zip_code) AS zip_code',
# 'mode() WITHIN GROUP (ORDER BY locations.latitude) AS latitude',
# 'mode() WITHIN GROUP (ORDER BY locations.longitude) ' +
# ' AS longitude',
# 'mode() WITHIN GROUP (ORDER BY locations.neighborhood) ' +
# 'AS neighborhood'
).group_by(Location.document_id).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def query_with_map_boundaries(self, data):
"""TODO."""
query = SESSION.query(Cleaned).filter(
Cleaned.detail_publish.is_(True)
).filter(
(Cleaned.sellers.ilike('%%%s%%' % data['name_address']))
| (Cleaned.buyers.ilike('%%%s%%' % data['name_address']))
| (Cleaned.address.ilike('%%%s%%' % data['name_address']))
| (Cleaned.instrument_no.ilike('%%%s%%' % data['name_address']))
).filter(Cleaned.neighborhood.ilike(
'%%%s%%' % data['neighborhood'])).filter(
Cleaned.zip_code.ilike('%%%s%%' % data['zip_code'])).filter(
Cleaned.document_date >= '%s' % data['begin_date']).filter(
Cleaned.document_date <= '%s' %
data['end_date']).filter(
Cleaned.amount >= '%s' %
data['amount_low']).filter(
Cleaned.amount <= '%s' % data['amount_high']
).filter((Cleaned.latitude <= data['bounds'][0])
& (Cleaned.latitude >= data['bounds'][2])
& (Cleaned.longitude <= data['bounds'][1])
& (Cleaned.longitude >= data['bounds'][3])
).order_by(desc(
Cleaned.document_date)).offset(
'%d' % data['page_offset']).limit(
'%d' %
data['page_length']).all()
SESSION.close()
return query
def get_rows_with_null_rating(self):
"""
Return query result for locations with rating IS NULL.
:returns: SQLAlchemy query result.
"""
query = SESSION.query(
Location.rating,
Location.document_id,
Location.street_number,
Location.address
).join(
Detail
).filter(
Location.rating.is_(None)
).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)
).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).all()
log.debug('Rows with rating is NULL: {}'.format(len(query)))
SESSION.close()
return query
def get_existing_until_date(self):
"""TODO: Docstring."""
query_until_date = SESSION.query(Detail.document_recorded).order_by(
Detail.document_recorded.desc()).limit(1).all()
# Check if any records at all
if len(query_until_date) == 0:
# Make it so initialized date range will start from beginning.
until_date = OPENING_DATE - timedelta(days=1)
# log.debug(until_date)
# log.debug(type(until_date))
else:
log.debug(len(query_until_date))
for row in query_until_date:
# TODO: will this fail w/o .one()?
until_date = row.document_recorded
# log.debug(until_date)
# log.debug(type(until_date))
# log.debug(until_date)
SESSION.close()
return until_date
def get_details(self):
"""Return SQL query of details table for given date range."""
subquery = SESSION.query(Detail).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).subquery()
log.debug(subquery)
SESSION.close()
return subquery
def latest_date_no_flag(self):
"""Finds the latest date_recorded without permanent_flag set."""
query = SESSION.query(
func.max(Detail.document_recorded).label('late_date')).filter(
Detail.permanent_flag.is_(None)).all()
for row in query:
latest_none_date = row.late_date
latest_none_datetime = datetime.combine(latest_none_date,
datetime.min.time())
SESSION.close()
return latest_none_datetime
def get_neighborhoods(self):
"""TODO."""
query = SESSION.query(Neighborhood.gnocdc_lab).all()
neighborhoods = []
for neighborhood in query:
neighborhoods.append(
(neighborhood.gnocdc_lab).title().replace('Mcd', 'McD'))
neighborhoods.sort()
SESSION.close()
return neighborhoods
def get_details(self):
"""Return SQL query of details table for given date range."""
subquery = SESSION.query(
Detail
).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)
).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).subquery()
log.debug(subquery)
SESSION.close()
return subquery
def earliest_date_no_flag(self):
"""Find the earliest date_recorded without permanent_flag set."""
query = SESSION.query(
func.min(Detail.document_recorded).label('early_date')).filter(
Detail.permanent_flag.is_(None) # To satisfy PEP8
).all()
for row in query:
earliest_none_date = row.early_date
earliest_none_datetime = datetime.combine(earliest_none_date,
datetime.min.time())
SESSION.close()
return earliest_none_datetime
def query_search_term_limit_3(self, table, term):
"""
Get the top three results for autocomplete dropdown.
:param table: string. The database to query.
:type table: string
:param term: string. The autocomplete term entered in search box.
:type term: string
:returns: A SQLAlchemy query result for three matches, at most.
"""
query = SESSION.query(getattr(Cleaned, table)).filter(
getattr(Cleaned, table).ilike(
'%%{}%%'.format(term))).distinct().limit(3).all()
SESSION.close()
return query
def get_vendees(self):
"""Return SQL query of vendees table for given date range."""
log.debug('get_vendees')
subquery = SESSION.query(
Vendee.document_id,
func.string_agg(
cast(Vendee.vendee_firstname, Text) + " " +
cast(Vendee.vendee_lastname, Text),
', ').label('buyers')).group_by(Vendee.document_id).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def latest_date_no_flag(self):
"""Finds the latest date_recorded without permanent_flag set."""
query = SESSION.query(
func.max(Detail.document_recorded).label('late_date')
).filter(
Detail.permanent_flag.is_(None)
).all()
for row in query:
latest_none_date = row.late_date
latest_none_datetime = datetime.combine(
latest_none_date, datetime.min.time())
SESSION.close()
return latest_none_datetime
def earliest_date_no_flag(self):
"""Find the earliest date_recorded without permanent_flag set."""
query = SESSION.query(
func.min(Detail.document_recorded).label('early_date')
).filter(
Detail.permanent_flag.is_(None) # To satisfy PEP8
).all()
for row in query:
earliest_none_date = row.early_date
earliest_none_datetime = datetime.combine(
earliest_none_date, datetime.min.time())
SESSION.close()
return earliest_none_datetime
def join_subqueries(self):
"""Run a JOIN on subqueries."""
log.debug('join_subqueries')
subq_vendees = self.get_vendees()
subq_vendors = self.get_vendors()
subq_location = self.get_locations()
log.debug('query...')
query = SESSION.query(
Detail.document_id,
Detail.amount,
Detail.document_date,
Detail.document_recorded,
Detail.instrument_no,
Detail.detail_publish,
Detail.permanent_flag,
subq_vendees.c.buyers,
subq_vendors.c.sellers,
subq_location.c.location_publish,
subq_location.c.address,
subq_location.c.location_info
# TODO: Once SQLAlchemy supports WITHIN GROUP, uncomment these.
# subq_location.c.zip_code,
# subq_location.c.latitude,
# subq_location.c.longitude,
# subq_location.c.neighborhood
).join(
subq_vendees
).join(
subq_vendors
).join(
subq_location
).filter(
Detail.document_recorded >= '{}'.format(self.initial_date)
).filter(
Detail.document_recorded <= '{}'.format(self.until_date)
).all()
log.debug('len(query): %d', len(query))
SESSION.close()
return query
def get_last_updated_date(self):
"""TODO."""
query = SESSION.query(Cleaned).filter(
Cleaned.detail_publish.is_(True)).order_by(
desc(Cleaned.document_recorded)).limit(1).all()
log.info(query)
updated_date = ''
for row in query:
updated_date = ymd_to_full_date(
(row.document_recorded).strftime('%Y-%m-%d'), no_day=True)
log.info(updated_date)
SESSION.close()
return updated_date
def get_vendors(self):
"""Return SQL query of vendors table for given date range."""
log.debug('get_vendors')
subquery = SESSION.query(
Vendor.document_id,
func.string_agg(
cast(Vendor.vendor_firstname, Text) + " " +
cast(Vendor.vendor_lastname, Text),
', '
).label('sellers')
).group_by(
Vendor.document_id
).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def earliest_date_temp_flag(self):
"""Find earliest date with permanent_flag = False."""
query = SESSION.query(
func.min(Detail.document_recorded).label('early_date')).filter(
Detail.permanent_flag.is_(False) # To satisfy PEP8
).all()
for row in query:
earliest_temp_date = row.early_date
if earliest_temp_date is not None:
earliest_temp_datetime = datetime.combine(earliest_temp_date,
datetime.min.time())
log.debug(earliest_temp_datetime)
SESSION.close()
return earliest_temp_datetime
else:
SESSION.close()
return None
def get_locations(self):
"""Return SQL query of locations table for given date range."""
log.debug('get_locations')
subquery = SESSION.query(
Location.document_id,
func.bool_and(Location.location_publish).label('location_publish'),
func.string_agg(
cast(Location.street_number, Text) + ' ' +
cast(Location.address, Text),
'; '
).label('address'),
func.string_agg(
'Unit: ' + cast(Location.unit, Text) + ', ' +
'Condo: ' + cast(Location.condo, Text) + ', ' +
'Weeks: ' + cast(Location.weeks, Text) + ', ' +
'Subdivision: ' + cast(Location.subdivision, Text) + ', ' +
'District: ' + cast(Location.district, Text) + ', ' +
'Square: ' + cast(Location.square, Text) + ', ' +
'Lot: ' + cast(Location.lot, Text),
'; '
).label('location_info')
# todo: Once SQLAlchemy supports this, add these fields this way.
# 'mode() WITHIN GROUP (ORDER BY locations.zip_code) AS zip_code',
# 'mode() WITHIN GROUP (ORDER BY locations.latitude) AS latitude',
# 'mode() WITHIN GROUP (ORDER BY locations.longitude) ' +
# ' AS longitude',
# 'mode() WITHIN GROUP (ORDER BY locations.neighborhood) ' +
# 'AS neighborhood'
).group_by(
Location.document_id
).subquery()
# log.debug(subquery)
SESSION.close()
return subquery
def earliest_date_temp_flag(self):
"""Find earliest date with permanent_flag = False."""
query = SESSION.query(
func.min(Detail.document_recorded).label('early_date')
).filter(
Detail.permanent_flag.is_(False) # To satisfy PEP8
).all()
for row in query:
earliest_temp_date = row.early_date
if earliest_temp_date is not None:
earliest_temp_datetime = datetime.combine(
earliest_temp_date, datetime.min.time())
log.debug(earliest_temp_datetime)
SESSION.close()
return earliest_temp_datetime
else:
SESSION.close()
return None
