from index import app,db,es,redis_store,limiter,home_cache,city_cache
from flask_cors import cross_origin
from ..models import IpQuery
from city_to_state import *
import datetime
import json
"Add target cities here"
cities = ["Berkeley"]
states = ["CA"]
def get_city_data(city, state):
city = city
state = state
engine_str = app.config['SQLALCHEMY_BINDS']['datawarehouse']
city_geoid = map_geoid(engine_str, city, state)
nb_stats = scoring_neighborhood(city)
real_estate_stats = get_city_sources(city)
census_result = query_census_detail(engine_str, city_geoid)
result = dict(nb_stats.items() + real_estate_stats.items() + census_result.items())
city_cache.set_key_value(name = city_geoid, value = json.dumps(result),expiration = 60 * 60 * 24)
if __name__ == '__main__':
with app.app_context():
for city, state in zip(cities, states):
get_city_data(city, state)
def job(neighbor):
neighbor_statistics = {}
if not neighbor.properties:
return
p = json.loads(neighbor.properties)['features'][0]
_body = {
"query": {
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']
}
}
},
"aggs": {
"median_rent_income_ratio": {
"percentiles": {
"field": "rental_income_ratio",
"percents": [50.0]
}
},
"median_increase_ratio": {
"percentiles": {
"field": "increase_ratio",
"percents": [50.0]
}
},
"median_sale_rent_ratio": {
"percentiles": {
"field": "sale_rent_ratio",
"percents": [50.0]
}
},
"median_house_price": {
"percentiles": {
"field": "house_price_dollar",
"percents": [50.0]
}
},
"median_rent_price": {
"percentiles": {
"field": "rent",
"percents": [50.0]
}
},
"score": {
"percentiles": {
"field": "score",
"percents": [50.0]
}
},
"median_airbnb_price": {
"percentiles": {
"field": "airbnb_rent",
"percents": [50.0]
}
},
"median_airbnb_rental_ratio": {
"percentiles": {
"field": "airbnb_rental_ratio",
"percents": [50.0]
}
}
}
}
try:
with app.app_context():
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=0)
p['properties']['rent_increase_ratio'] = res['aggregations'][
'median_rent_income_ratio']['values']['50.0']
p['properties']['increase_ratio'] = res['aggregations'][
'median_increase_ratio']['values']['50.0']
p['properties']['sale_rent_ratio'] = res['aggregations'][
'median_sale_rent_ratio']['values']['50.0']
p['properties']['house_price'] = res['aggregations'][
'median_house_price']['values']['50.0']
p['properties']['rent_median'] = res['aggregations'][
'median_rent_price']['values']['50.0']
p['properties']['airbnb_median'] = res['aggregations'][
'median_airbnb_price']['values']['50.0']
p['properties']['airbnb_rental_ratio'] = res['aggregations'][
'median_airbnb_rental_ratio']['values']['50.0']
p['properties']['score'] = res['aggregations']['score']['values'][
'50.0']
p['properties']['region_id'] = neighbor.region_id
airbnb_rental_ratio = None if p['properties'][
'airbnb_rental_ratio'] == u'NaN' else p['properties'][
'airbnb_rental_ratio']
rent_increase_ratio = None if p['properties'][
'rent_increase_ratio'] == u'NaN' else p['properties'][
'rent_increase_ratio']
increase_ratio = None if p['properties'][
'increase_ratio'] == u'NaN' else p['properties']['increase_ratio']
sale_rent_ratio = None if p['properties'][
'sale_rent_ratio'] == u'NaN' else p['properties']['sale_rent_ratio']
total_ratio = increase_ratio + rent_increase_ratio if increase_ratio and rent_increase_ratio else None
house_price = None if p['properties']['house_price'] == u'NaN' else p[
'properties']['house_price']
rent_median = None if p['properties']['rent_median'] == u'NaN' else p[
'properties']['rent_median']
airbnb_rent_median = None if p['properties'][
'airbnb_median'] == u'NaN' else p['properties']['airbnb_median']
score = None if p['properties']['score'] == u'NaN' else p[
'properties']['score']
neighbor_statistics[neighbor.region_id] = {
"increase_ratio": increase_ratio,
"rent_increase_ratio": rent_increase_ratio,
"sale_rent_ratio": sale_rent_ratio,
"house_price": house_price,
"rent_median": rent_median,
"airbnb_rent_median": airbnb_rent_median,
"score": score,
"total_ratio": total_ratio,
"airbnb_rental_ratio": airbnb_rental_ratio,
"total_ratio": total_ratio
}
more_fields_for_irr = {
"acquisition_cost": neighbor.acquisition_cost,
"cap_ex": neighbor.cap_ex,
"disposition_cost": neighbor.disposition_cost,
"insurance_cost": neighbor.insurance_cost,
"leasing_commission": neighbor.leasing_commission,
"property_management_fee": neighbor.property_management_fee,
"property_tax": neighbor.property_tax,
"repair": neighbor.repair,
"vaccancy_rate": neighbor.vaccancy_rate,
"rent_growth": neighbor.rent_growth
}
neighbor_statistics[neighbor.region_id].update(more_fields_for_irr)
redis_store.hmset(settings.NEIGHBORHOOD_STATISTICS,
neighbor_statistics)
except TransportError:
with app.app_context():
logger.warning("ES transport error")
def suite_level_send_mail(case_log_id_list, email, suite_id):
time.sleep(10)
Test_Name = []
Test_Description = []
Test_src_table = []
Test_target_table = []
Test_status = []
print(case_log_id_list, email)
while not check_status(case_log_id_list):
db.session.commit()
time.sleep(10)
suite = TestSuite.query.filter_by(test_suite_id=suite_id).first()
print(suite)
for each_test in suite.test_case:
Test_Name.append(each_test.test_name)
Test_Description.append(each_test.test_id)
Test_status.append(each_test.test_status)
import json
(src_table, dest_table), = json.loads(
each_test.test_case_detail)['table'].items()
Test_src_table.append(src_table)
Test_target_table.append(dest_table)
print(Test_status, Test_Name, Test_Description, Test_src_table,
Test_target_table)
render_list = {}
render_list['Test_status'] = Test_status
render_list['Test_Name'] = Test_Name
render_list['Test_Description'] = Test_Description
render_list['src_tables'] = Test_src_table
render_list['dest_tables'] = Test_target_table
# Null_display = []
# Dup_display = []
# for i in case_log_id_list:
# case_log_id = TestCaseLog.query.filter_by(test_case_log_id=i).first()
# case = TestCase.query.filter_by(test_case_id=case_log_id.test_case_id).first()
# if case.test_name == 'NullCheck' and case_log_id.des_execution_log is not None:
# Table = (list(json.loads(case.test_case_detail)['table'].values())[0])
# length = len(json.loads(case_log_id.des_execution_log))
# Null_display.append({"data":json.loads(case_log_id.des_execution_log)[:11],"records":length-1,"table":Table})
# elif case.test_name == 'DuplicateCheck' and case_log_id.des_execution_log is not None:
# print("do some logic")
# Table = (list(json.loads(case.test_case_detail)['table'].values())[0])
# Dup_display.append({"data":json.loads(case_log_id.des_execution_log)[:11], "table":Table})
# print(Dup_display)
payload = {}
payload = {"status": True, "message": "send Mail"}
print(payload)
msg = Message('Quality Suite Result',
sender=("Acciom", app.config.get('MAIL_USERNAME')),
recipients=[email])
current_time = datetime.datetime.now()
current_time.strftime("%c")
with app.app_context():
msg.html = render_template(
'email.html',
content=render_list,
zip_content=zip(Test_Name, Test_Description, Test_src_table,
Test_target_table, Test_status),
suite_name=suite.test_suite_name,
suite_id=suite.test_suite_id,
executed_at=str(current_time.strftime("%c")))
mail.send(msg)
#Null_display = Null_display, Dup_display=Dup_display
def start():
logging.info('开始更新新闻')
with app.app_context():
for name in types.keys():
spider_news(name)
logging.info('更新完成')
def job(area):
area_statistics = {}
if not area.properties:
return
p = area.properties['features'][0]
_body = {
"query": {
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']
}
}
},
"aggs":{
"median_rent_income_ratio":
{"percentiles":{"field":"rental_income_ratio","percents":[50.0]}},
"median_increase_ratio":
{"percentiles":{"field":"increase_ratio","percents":[50.0]}},
"median_sale_rent_ratio":
{"percentiles":{"field":"sale_rent_ratio","percents":[50.0]}},
"median_house_price":
{"percentiles":{"field":"house_price_dollar","percents":[50.0]}},
"median_rent_price":
{"percentiles":{"field":"rent","percents":[50.0]}},
"score":
{"percentiles":{"field":"score","percents":[50.0]}},
"median_airbnb_price":
{"percentiles":{"field":"airbnb_rent","percents":[50.0]}},
"median_airbnb_rental_ratio":
{"percentiles":{"field":"airbnb_rental_ratio","percents":[50.0]}}
}
}
with app.app_context():
res = es.search(index=HOME_INDEX,
doc_type=HOME_TYPE,
body=_body,
size=0
)
p['properties']['rent_increase_ratio'] = res['aggregations']['median_rent_income_ratio']['values']['50.0']
p['properties']['increase_ratio'] = res['aggregations']['median_increase_ratio']['values']['50.0']
p['properties']['sale_rent_ratio'] = res['aggregations']['median_sale_rent_ratio']['values']['50.0']
p['properties']['house_price'] = res['aggregations']['median_house_price']['values']['50.0']
p['properties']['rent_median'] = res['aggregations']['median_rent_price']['values']['50.0']
p['properties']['airbnb_median'] = res['aggregations']['median_airbnb_price']['values']['50.0']
p['properties']['airbnb_rental_ratio'] = res['aggregations']['median_airbnb_rental_ratio']['values']['50.0']
p['properties']['score'] = res['aggregations']['score']['values']['50.0']
p['properties']['region_id'] = area.geoid
# separate sale and sold status
with app.app_context():
status_sale = 2
_body_separate = {
"query": {
"bool":{
"must":[{"match":{"status":2}}],
"filter":{
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']}
}
}
}
},
"aggs":{
"median_house_price":
{"percentiles":{"field":"price","percents":[50.0]}}
}
}
res_sale = es.search(index=HOME_INDEX,doc_type=HOME_TYPE,body=_body_separate,size=0)
with app.app_context():
status_sold = 4
_body_separate = {
"query": {
"bool":{
"must":[{"match":{"status":4}}],
"filter":{
"geo_shape": {
"location": {
"relation": "within",
"shape": p['geometry']}
}
}
}
},
"aggs":{
"median_house_price":
{"percentiles":{"field":"price","percents":[50.0]}}
}
}
res_sold = es.search(index=HOME_INDEX,doc_type=HOME_TYPE,body=_body_separate,size=0)
p['properties']['listing_price'] = res_sale['aggregations']['median_house_price']['values']['50.0']
p['properties']['sold_price'] = res_sold['aggregations']['median_house_price']['values']['50.0']
airbnb_rental_ratio = None if p['properties']['airbnb_rental_ratio']==u'NaN' else p['properties']['airbnb_rental_ratio']
rent_increase_ratio = None if p['properties']['rent_increase_ratio']==u'NaN' else p['properties']['rent_increase_ratio']
increase_ratio = None if p['properties']['increase_ratio']==u'NaN' else p['properties']['increase_ratio']
sale_rent_ratio = None if p['properties']['sale_rent_ratio']==u'NaN' else p['properties']['sale_rent_ratio']
total_ratio = increase_ratio + rent_increase_ratio if increase_ratio and rent_increase_ratio else None
house_price = None if p['properties']['house_price']==u'NaN' else p['properties']['house_price']
rent_median = None if p['properties']['rent_median']==u'NaN' else p['properties']['rent_median']
airbnb_rent_median = None if p['properties']['airbnb_median']==u'NaN' else p['properties']['airbnb_median']
score = None if p['properties']['score']==u'NaN' else p['properties']['score']
sale_price = None if p['properties']['listing_price'] ==u'NaN' else p['properties']['listing_price']
sold_price = None if p['properties']['sold_price'] ==u'NaN' else p['properties']['sold_price']
area_statistics[area.geoid] = {"increase_ratio":increase_ratio,
"rent_increase_ratio":rent_increase_ratio,
"sale_rent_ratio":sale_rent_ratio,
"house_price":house_price,
"rent_median":rent_median,
"airbnb_rent_median":airbnb_rent_median,
"score":score,
"total_ratio":total_ratio,
"airbnb_rental_ratio":airbnb_rental_ratio,
"score":score,
"sale_price":sale_price,
"sold_price":sold_price}
more_fields_for_irr = {"acquisition_cost":area.acquisition_cost,
"cap_ex":area.cap_ex,
"disposition_cost":area.disposition_cost,
"insurance_cost":area.insurance_cost,
"leasing_commission":area.leasing_commission,
"property_management_fee":area.property_management_fee,
"property_tax":area.property_tax,
"repair":area.repair,
"vaccancy_rate":area.vaccancy_rate,
"rent_growth":area.rent_growth,
"down_payment":area.down_payment,
"expenses":area.expenses,
"loan_interest_rate":area.loan_interest_rate,
"closing_costs_misc":area.closing_costs_misc}
area_statistics[area.geoid].update(more_fields_for_irr)
redis_store.hmset(settings.AREA_STATISTICS,area_statistics)
def send_async_email(app, msg):
with app.app_context():
try:
mail.send(msg)
except ConnectionRefusedError:
raise InternalServerError("[MAIL SERVER] not working")