def fetch_test1(match_number): crawl_url_1 = 'http://211.151.108.43/soccer/match/' crawl_url_2 = '/ah/handicap/27/' webpage = crawl_url_1 + str(match_number) + crawl_url_2 entry = data_class_test.odds_trend() entry = crawl_engine.capture_matchesdata(webpage) database_conn.db_conn(entry)
def fetch_byterm(seed): crawl_url_1 = 'http://211.151.108.43/soccer/match/' crawl_url_2 = '/ah/handicap/27/' mlist = crawl_engine.get_zucai(seed) for i in mlist: webpage = crawl_url_1 + i + crawl_url_2 entry = data_class_test.odds_trend() entry = crawl_engine.capture_matchesdata(webpage) database_conn.db_conn(entry)
def booking_details(self):
conn, cursor = db_conn('bs4')
cursor.execute("""
SELECT
bt.id
, bt.boat_type
, bt.boat_category
, bt.length
, b.price_with_service_fee_cents
- b.sales_tax_cents
- b.service_fee_cents
, p.bareboat
, b.trip_type
, a.state
, a.latitude
, a.longitude
FROM bookings b
JOIN packages p
ON p.id = b.package_id
JOIN boats bt
ON bt.id = p.boat_id
JOIN addresses a
ON a.addressable_type = 'Boat'
AND a.addressable_id = bt.id
AND a.deleted_at IS NULL
WHERE b.id = %s
AND b.trip_type IN ('half_day', 'full_day')
AND (a.latitude IS NOT NULL AND a.longitude IS NOT NULL)
""" % self.booking_id)
bk_details = cursor.fetchall()
conn.close()
return bk_details[0]
def renter_info(booking_id):
conn, cursor = db_conn('bs4')
cursor.execute(
"""
SELECT u.first_name, u.email
FROM bookings b
JOIN users u
ON u.id = b.renter_id
WHERE b.id = %s
""" % booking_id
)
info = cursor.fetchall()
conn.close()
return info[0]
def min_package_price(self, bt_public_id):
conn, cursor = db_conn('bs4')
cursor.execute("""
SELECT
CASE WHEN MIN(p.half_day_cents) > 0 THEN
ROUND(MIN(p.half_day_cents)/100::DECIMAL,2)
ELSE ROUND(MIN(p.all_day_cents)/100::DECIMAL,2)
END
FROM packages p
JOIN boats bt
ON bt.id = p.boat_id
WHERE bt.public_id = '%s'
AND p.available = True
""" % bt_public_id)
min_price = cursor.fetchall()[0][0]
conn.close()
return min_price
def live_boats(self):
'''
Produces a list of live boat owned by the owner passed to the owner_id
parameter of the class.
'''
conn, cursor = db_conn('bs4')
cursor.execute(
"""
SELECT bt.id, bt.public_id, f.boat_image_large
FROM boats bt
JOIN fleets f
ON f.boat_id = bt.id
WHERE f.searchable = True
AND bt.primary_manager_id = %s
""" % self.owner_id
)
boats = [i for i in cursor.fetchall()]
conn.close()
return boats
def boat_suggestions(self):
bk_details = self.booking_details()
home_coors = self.geo_coors()
conn, cursor = db_conn('bs4')
cursor.execute("""
SELECT
bt.public_id
, f.boat_image_large
, bt.boat_type
, bt.boat_category
, bt.length
, CASE
WHEN p.include_captain_price = True
AND bt.length <= 29 THEN p.half_day_cents + 22000
WHEN p.include_captain_price = True
AND bt.length >= 30
AND bt.length <= 45 THEN p.half_day_cents + 28000
WHEN p.include_captain_price = True
AND bt.length >= 46 THEN p.half_day_cents + 39000
ELSE half_day_cents
END as half_day_cents
, CASE
WHEN p.include_captain_price = True
AND bt.length <= 29 THEN p.all_day_cents + 33000
WHEN p.include_captain_price = True
AND bt.length >= 30
AND bt.length <= 45 THEN p.all_day_cents + 40000
WHEN p.include_captain_price = True
AND bt.length >= 46 THEN p.all_day_cents + 67000
ELSE all_day_cents
END as all_day_cents
, a.latitude
, a.longitude
, concat(bt.length::TEXT, 'ft '::TEXT, bt.make)
, a.city
, a.state
FROM boats bt
JOIN packages p
ON p.boat_id = bt.id
JOIN fleets f
ON f.boat_id = bt.id
JOIN addresses a
ON a.addressable_type = 'Boat'
AND a.addressable_id = bt.id
AND a.deleted_at IS NULL
WHERE bt.id != %s
AND p.bareboat = %s
AND f.searchable = True
AND p.available = True
AND (a.latitude IS NOT NULL AND a.longitude IS NOT NULL)
""" % (bk_details[0], bk_details[5]))
boat_list = cursor.fetchall()
conn.close()
def distance_filter(boat_list, max_distance):
return filter(
lambda x: distance.distance(
(x[7], x[8]), home_coors).miles < max_distance, boat_list)
if len(distance_filter(boat_list, max_distance=10)) > 10:
boat_list = distance_filter(boat_list, max_distance=10)
elif len(distance_filter(boat_list, max_distance=30)) > 3:
boat_list = distance_filter(boat_list, max_distance=30)
elif len(distance_filter(boat_list, max_distance=50)) > 3:
boat_list = distance_filter(boat_list, max_distance=50)
else:
boat_list = None
def score(boat_type, boat_category, boat_length, half_day_cents,
all_day_cents):
score = 0
if bk_details[1] == boat_type:
score += 100
if bk_details[2] == boat_category:
score += 100
score -= (100 * abs(
(boat_length - bk_details[3]) / float(bk_details[3])))
if bk_details[6] == 'half_day':
score -= (100 * abs(
(half_day_cents - bk_details[4]) / float(bk_details[4])))
elif bk_details[6] == 'full_day':
score -= (100 * abs(
(all_day_cents - bk_details[4]) / float(bk_details[4])))
return score
scored_boats = []
if boat_list is not None:
for boat in boat_list:
location = boat[10].capitalize() + ', ' + boat[11]
scored_boats.append(
(boat[0], boat[1],
score(boat[2], boat[3], boat[4],
boat[5], boat[6]), boat[9],
self.min_package_price(boat[0]), location))
sorted_boats = sorted(scored_boats,
key=lambda x: x[2],
reverse=True)[:3]
return [(i[0], i[1], i[3], i[4], i[5]) for i in sorted_boats]
else:
return None
def get_nearby_boats(self, boat_id):
'''
Returns a list of boats within the distance defined by radius_miles
variable from the boat being evaluated
'''
radius_miles = 20
def haversine(lat1, lon1, lat2, lon2):
R = 3959.87433
dLat = radians(lat2 - lat1)
dLon = radians(lon2 - lon1)
lat1 = radians(lat1)
lat2 = radians(lat2)
a = sin(dLat/2)**2 + cos(lat1)*cos(lat2)*sin(dLon/2)**2
c = 2*asin(sqrt(a))
return R * c
conn, cursor = db_conn('bs4')
cursor.execute(
"""
SELECT
latitude, longitude, state
FROM addresses
WHERE addressable_type = 'Boat'
AND addressable_id = %s
AND deleted_at IS NULL
""" % boat_id
)
coords = cursor.fetchall()[0]
cursor.execute(
"""
SELECT f.boat_id, latitude, longitude, bt.primary_manager_id
FROM fleets f
JOIN boats bt
ON bt.id = f.boat_id
JOIN addresses a
ON a.addressable_type = 'Boat'
AND a.addressable_id = bt.id
AND a.deleted_at IS NULL
WHERE bt.id != %s
AND f.searchable = True
AND latitude IS NOT NULL
AND longitude IS NOT NULL
AND a.state = '%s'
""" % (boat_id, coords[2])
)
live_boats = cursor.fetchall()
conn.close()
return [[i[0], i[3]] for i in live_boats
if haversine(coords[0], coords[1], i[1], i[2]) < radius_miles]
def performance_stats(self, boat_id):
'''
Returns a dictionary with the performance stats
used in the search score email.
'''
stats = {}
conn, cursor = db_conn('redshift')
# Boat pageviews during time period
cursor.execute(
"""
SELECT COUNT(DISTINCT v.id)
FROM bs4.boats bt
JOIN astronomer_production.viewed_boat_listing v
ON SPLIT_PART(v.context_page_path, '/',3) = bt.public_id
AND v.received_at::DATE BETWEEN '%s' AND '%s'
WHERE bt.id = %s
""" % (self.lower_date, self.upper_date, boat_id)
)
stats['views'] = int(cursor.fetchall()[0][0])
conn.close()
conn, cursor = db_conn('bs4')
# Ops, Rentals, and Earnings for rentals during time period
cursor.execute(
"""
SELECT
COUNT(DISTINCT b.opportunity_id)
,COUNT(DISTINCT CASE
WHEN b.state IN
('concluded', 'disputed', 'aboard', 'ashore', 'approved')
THEN b.id END)
,CASE WHEN SUM(CASE WHEN b.state IN
('concluded', 'disputed', 'aboard', 'ashore', 'approved')
THEN b.owner_payout_cents END) IS NOT NULL
THEN CONCAT('$', ROUND(SUM(CASE WHEN b.state IN
('concluded', 'disputed', 'aboard', 'ashore', 'approved')
THEN b.owner_payout_cents END)/100::DECIMAL,2)::TEXT)
ELSE '$0.00' END
FROM bookings b
JOIN packages p
ON p.id = b.package_id
JOIN boats bt
ON bt.id = p.boat_id
WHERE bt.id = %s
AND b.trip_start::DATE BETWEEN '%s' AND '%s'
""" % (boat_id, self.lower_date, self.upper_date)
)
bookings = cursor.fetchall()[0]
stats['ops'] = int(bookings[0])
stats['rentals'] = int(bookings[1])
if bookings[2] is not None:
stats['earnings'] = bookings[2]
else:
stats['earnings'] = '$0.00'
# Earnings lost to other owners during time period
cursor.execute(
"""
WITH boat_ops as (
SELECT b.opportunity_id
FROM bookings b
JOIN packages p
ON p.id = b.package_id
WHERE p.boat_id = %s
AND b.trip_start BETWEEN '%s' AND '%s'
)
SELECT CONCAT('$',
CASE WHEN SUM((b.owner_payout_cents)/100::DECIMAL)::TEXT IS NULL
THEN 0.00::TEXT
ELSE ROUND(SUM((b.owner_payout_cents)/100::DECIMAL),2)::TEXT END)
FROM bookings b
JOIN packages p
ON p.id = b.package_id
JOIN boats bt
ON bt.id = p.boat_id
JOIN boat_ops bo
ON bo.opportunity_id = b.opportunity_id
WHERE bt.primary_manager_id != %s
AND b.state IN
('concluded', 'disputed', 'aboard', 'ashore', 'approved')
""" % (boat_id, self.lower_date, self.upper_date, self.owner_id)
)
earnings_lost = cursor.fetchall()[0][0]
if earnings_lost is not None:
stats['earnings_lost'] = earnings_lost
else:
stats['earnings_lost'] = '$0.00'
conn.close()
return stats
def search_score_components(self, boat_id):
'''
Returns a dictionary with the components of the search score
that are used in the search score email relative to the boat_id passed
to the boat_id parameter.
'''
components = {}
conn, cursor = db_conn('bs4')
cursor.execute(
"""
SELECT search_score_details
FROM fleets
WHERE boat_id = %s
""" % boat_id
)
score_details = cursor.fetchall()[0][0]
win_rate_last_60 = float(score_details.split(
'formula"=>"')[1].split(
'+', 10)[1].split('*', 10)[0])
components['win_rate'] = win_rate_last_60
cursor.execute(
"""
SELECT
COUNT(id)
FROM fleets
WHERE (captained_instant_book_enabled = True
OR bareboat_instant_book_enabled = True)
AND boat_id = %s
""" % boat_id
)
insta_packages = cursor.fetchall()[0][0]
if insta_packages > 0:
components['insta_enabled'] = True
else:
components['insta_enabled'] = False
cursor.execute(
"""
WITH owner_id AS (
SELECT bt.primary_manager_id
FROM boats bt
WHERE bt.id = %s
GROUP BY bt.primary_manager_id
)
SELECT
COUNT(DISTINCT be.booking_id)
,COUNT(distinct b.id)
FROM bookings b
JOIN packages p
ON p.id = b.package_id
JOIN boats bt
ON bt.id = p.boat_id
JOIN owner_id oid
ON bt.primary_manager_id = oid.primary_manager_id
LEFT JOIN booking_events be
ON be.booking_id = b.id
AND be.acting_user_type = 'owner'
""" % boat_id
)
response_vars = cursor.fetchall()[0]
if response_vars[1] > 0:
components['owner_response_rate'] = (
float(response_vars[0])/response_vars[1])
else:
components['owner_response_rate'] = 0
cursor.execute(
"""
SELECT COUNT(*)
FROM reviews
WHERE reviewable_type = 'Boat'
AND reviewable_id = %s
AND star_rating = 5
""" % boat_id
)
components['five_star_reviews'] = int(cursor.fetchall()[0][0])
conn.close()
return components
return info[0]
def render_template(template, **kwargs):
''' renders a Jinja template into HTML '''
# check if template exists
if not os.path.exists(template):
print('No template file present: %s' % template)
sys.exit()
templateLoader = jinja2.FileSystemLoader(searchpath="/")
templateEnv = jinja2.Environment(loader=templateLoader)
templ = templateEnv.get_template(template)
return templ.render(**kwargs)
conn, cursor = db_conn('redshift')
cursor.execute(
"""
SELECT
b.id
, CASE
WHEN renter_address.state IS NULL THEN bill.state
ELSE renter_address.state
END as home_state
, zip.zip_code
FROM bs4.bookings b
JOIN bs4.packages p
ON p.id = b.package_id
JOIN bs4.boats bt
ON bt.id = p.boat_id
JOIN bs4.addresses boat_address