def price_per_m2(column_name, value, *args, **kwargs):
offer_record = kwargs['offer_record']
city = offer_record['city']
district = offer_record['district']
housing_type = offer_record['housing_type']
business_type = offer_record['business_type']
if district is None:
db_dict = db.get_custom(f"select price_per_m2 from offers where city = '{city}' and housing_type = '{housing_type}' and business_type = '{business_type}';")
else:
db_dict = db.get_custom(f"select price_per_m2 from offers where city = '{city}' and district = '{district}' and housing_type = '{housing_type}' and business_type = '{business_type}';")
prices = [dictionary['price_per_m2'] for dictionary in db_dict]
try:
mean_of_prices = mean(prices)
except TypeError:
mean_of_prices = None
if mean_of_prices:
if value < 0.5 * mean_of_prices:
return 1
elif value < 0.75 * mean_of_prices:
return 0.75
elif value < 1 * mean_of_prices:
return 0.5
elif value < 1.5 * mean_of_prices:
return 0.25
else:
return 0
else:
return None
def __init__(self, relevant_word = 'def', sql_column_name = 'offer_text', use_util = False):
self.use_util = use_util
self.relevant_word = relevant_word
self.sql_column_name = sql_column_name
if self.use_util:
self.sql_train_data = sql.get_custom(
"""select offer_url, offer_text, {0} from utility where {0} is not Null and offer_text like '%{1}%'""".format(sql_column_name, relevant_word))
else:
self.sql_train_data = sql.get_custom(
"""select offer_url, offer_text, {0} from offers where {0} is not Null and offer_text like '%{1}%'""".format(sql_column_name,relevant_word))
self.train_batch_size = len(self.sql_train_data)
self.sql_parse_data = sql.get_custom(
"""select offer_url, offer_text from offers where {0} is Null and offer_text like '%{1}%'""".format(sql_column_name,relevant_word))
self.parse_batch_size = len(self.sql_parse_data)
self.sql_parse_features_data = sql.get_custom("""select offer_url, offer_text from offers""")
def best_offer(user_obj=None, count=1):
try:
queries = db.get_all_queries(facebook_id=user_obj.facebook_id)
except AttributeError:
print('SUCH A USER HAS NOT BEEN FOUND')
query = 'select * from offers where True'
for field in queries:
comparator = query_scheme[field[0]]['comparator']
to_compare = query_scheme[field[0]]['to_compare']
if to_compare:
if 'int' in query_scheme[field[0]]['db'].lower():
query = query + f' and {field[0]} {comparator} {field[1]}'
elif 'char' in query_scheme[field[0]]['db'].lower():
query = query + f" and {field[0]} {comparator} '{field[1]}'"
elif 'bool' in query_scheme[field[0]]['db'].lower():
query = query + f' and {field[0]} {comparator} {field[1]}'
elif 'date' in query_scheme[field[0]]['db'].lower():
query = query + f' and ({field[0]} {comparator} "{field[1] + timedelta(days=5)}" or {field[0]} is null)'
else:
print('TODO')
print(query_scheme[field[0]]['db'])
if field[0] == 'city':
city = field[1]
print(query)
print(queries)
offers = db.get_custom(query)
try:
print(offers[0:3])
except:
pass
offers_count_city = db.get_custom(f"SELECT COUNT(IF(city = '{city}', 1, NULL)) '{city}' FROM offers;")
offers_count_city = offers_count_city[0][city]
return {'offers': offers[0:count], 'offers_count': len(offers), 'offers_count_city': offers_count_city}
def area(column_name, value, *args, **kwargs):
offer_record = kwargs['offer_record']
city = offer_record['city']
district = offer_record['district']
housing_type = offer_record['housing_type']
business_type = offer_record['business_type']
if district is None:
db_dict = db.get_custom(f"select price, area from offers where city = '{city}' and housing_type = '{housing_type}' and business_type = '{business_type}';")
else:
db_dict = db.get_custom(f"select price, area from offers where city = '{city}' and district = '{district}' and housing_type = '{housing_type}' and business_type = '{business_type}';")
prices = [dictionary['price'] for dictionary in db_dict]
areas = [dictionary['area'] for dictionary in db_dict]
try:
average_price_per_m2 = mean(prices) / mean(areas)
except TypeError:
average_price_per_m2 = None
try:
offer_price_per_m2 = offer_record['price'] / offer_record['area']
except TypeError:
offer_price_per_m2 = None
if average_price_per_m2 and offer_price_per_m2:
if offer_price_per_m2 < 0.5 * average_price_per_m2:
return 1
elif offer_price_per_m2 < 0.75 * average_price_per_m2:
return 0.75
elif offer_price_per_m2 < 1 * average_price_per_m2:
return 0.5
elif offer_price_per_m2 < 1.5 * average_price_per_m2:
return 0.25
else:
return 0
else:
return None
def child_locations(city):
cities = [
x['city']
for x in db.get_all(table_name='districts', fields_to_get='city')
]
if city in cities:
children = db.get_custom(
f"select district from districts where city = '{city}'")
# TODO show most popular first
# sorted(student_tuples, key=lambda student: student[2]) # sort by number of searches
children = [x['district'] for x in children]
else:
children = []
try:
place_id = recognize_location(location=city)["place_id"]
places = requests.get(
url=
f"https://nominatim.openstreetmap.org/details.php?osmtype=W&place_id={place_id}&format=json&hierarchy=1&pretty=1&addressdetails=1&keywords=1&linkedplaces=1&group_hierarchy=1&polygon_geojson=0"
)
for e in json.loads(places.text)["hierarchy"]["administrative"]:
if e["admin_level"] == 9:
place_id = e["place_id"]
district = requests.get(
url=
f"https://nominatim.openstreetmap.org/details.php?osmtype=W&place_id={place_id}&format=json&hierarchy=1&pretty=1&addressdetails=1&keywords=1&linkedplaces=1&group_hierarchy=1&polygon_geojson=0"
)
if json.loads(district.text)["importance"] > 0.1:
nazwa = json.loads(district.text)["localname"].replace(
"Osiedle ", "")
# TODO skróć nazwę (usuń po myślinku i zastąp skrótem z kropką lub Płd. Płn. itd
# if len(nazwa) > 20:
# nazwa
children.append(nazwa)
db.create_districts(city, children)
except (KeyError, TypeError,
requests.exceptions.ChunkedEncodingError) as e:
logging.info(f"Couldn't find locations children for: {city}")
if children:
# TODO powinno zwracać wraz z rankingiem searches
return children
else:
return False
from Databases import mysql_connection as db
import rater as r
import logging
from Key import rating_weights
# logging.basicConfig(level='DEBUG')
fields_to_ignore = [
'creation_time', 'modification_time', 'city', 'housing_type'
]
offer_records = db.get_custom(
'select offers.*, offer_features.* from offers inner join offer_features on offers.offer_url=offer_features.offer_url;'
)
for offer_record in offer_records:
offer_rating = {}
offer_rating['offer_url'] = offer_record['offer_url']
for column_name, value in offer_record.items():
rating = None
try:
function_result = None
for func in r.functions_to_apply[column_name]:
if function_result:
logging.debug('Applying funtion ' + func.__name__ +
' on the field name ' + str(column_name) +
' with the value of ' + str(function_result))
else:
logging.debug('Applying funtion ' + func.__name__ +
' on the field name ' + str(column_name) +
' with the value of ' + str(value))
function_result = func(column_name,