Example #1
0
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)
Example #2
0
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)
Example #3
0
    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]
Example #5
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
Example #7
0
    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