def pull_platform_editions():
cur = conn.cursor()
cur.execute(
"""SELECT pe.id AS id, pe.name AS name, pe.official_color AS official_color, pe.has_matte AS has_matte, pe.has_transparency AS has_transparency, pe.has_gloss AS has_gloss, pe.note AS note, pe.image_url AS image_url, x.colors, p.id AS platform_id FROM platforms AS p JOIN platform_editions AS pe ON pe.platform_id = p.id JOIN (SELECT pe.id AS id, STRING_AGG(c.name,', ') AS colors FROM platform_editions AS pe JOIN colors_platform_editions AS cpe ON cpe.platform_edition_id = pe.id JOIN colors AS c ON c.id = cpe.color_id GROUP BY pe.id ORDER BY pe.id) AS x ON x.id = pe.id ORDER BY p.id, name, official_color;
""")
raw_platform_editions = cur.fetchall()
for raw_pe in raw_platform_editions:
pe = PlatformEdition(id=raw_pe[0],
name=raw_pe[1],
official_color=raw_pe[2],
has_matte=raw_pe[3],
has_transparency=raw_pe[4],
has_gloss=raw_pe[5],
note=raw_pe[6],
image_url=raw_pe[7])
for color in raw_pe[8].split(', '):
pe.colors.append(color)
# put edition to platform
p_id = raw_pe[9]
Platform.get_by_id(p_id).add_edition(pe)
pe.add_to_registry()
cur.close()
def get_edition_by_id(id):
conn = get_db_connection()
cur = conn.cursor()
cur.execute("""
SELECT
e.id,
e.name,
e.platform_id,
e.official_color,
e.has_matte,
e.has_transparency,
e.has_gloss,
e.note,
e.image_url
FROM platform_editions as e
WHERE e.id=%s LIMIT 1;
""", (id,))
e = cur.fetchone()
cur.close()
conn.close()
if e == None:
return None
return PlatformEdition(id=e[0], name=e[1], official_color=e[3], has_matte=e[4], has_transparency=e[5], has_gloss=e[6], note=e[7], image_url=e[8])
def get_editions_by_platform_id(id):
conn = get_db_connection()
cur = conn.cursor()
cur.execute("""
SELECT
pe.id AS id,
pe.name AS name,
pe.official_color AS official_color,
pe.has_matte AS has_matte,
pe.has_transparency AS has_transparency,
pe.has_gloss AS has_gloss,
pe.note AS note,
pe.image_url AS image_url,
x.colors,
p.id AS platform_id
-- p.name AS platform_name
FROM platforms AS p
JOIN platform_editions AS pe ON pe.platform_id = p.id
JOIN
(SELECT pe.id AS id, STRING_AGG(c.name,', ') AS colors
FROM platform_editions AS pe
JOIN colors_platform_editions AS cpe ON cpe.platform_edition_id = pe.id
JOIN colors AS c ON c.id = cpe.color_id
GROUP BY pe.id
ORDER BY pe.id)
AS x ON x.id = pe.id
WHERE p.id = %s
ORDER BY p.id, name, official_color, colors;
""", (id,))
editions = cur.fetchall()
all_platform_editions = []
for e in editions:
current = PlatformEdition(id=e[0], name=e[1], official_color=e[2], has_matte=e[3], has_transparency=e[4], has_gloss=e[5], note=e[6], image_url=e[7])
current.colors = e[8].split(', ')
all_platform_editions.append(current)
cur.close()
conn.close()
return all_platform_editions
def find_matches(cls):
"""
Find all matches inside yet unsearched listings
"""
# find product instances in listings
profiler = Profiler()
for listing in Listing.get_all():
for edition in PlatformEdition.get_all():
cls.search_for_matches(listing, edition)
for platform in Platform.get_all():
cls.search_for_matches(listing, platform)
def pull_platform_edition_presences():
"""
Returns product presences, organized in dict (keys are pe_id; values are listing_id)
"""
cur = conn.cursor()
cur.execute(
"""SELECT listing_id, platform_edition_id, is_matched_via_body_text, index_start, index_end, score FROM listings_platform_editions;"""
)
raw_pe_presences = cur.fetchall()
cur.close()
pe_presences = []
for presence in raw_pe_presences:
pe_presences.append({
'listing_id': presence[0],
'platform_edition_id': presence[1],
'is_matched_via_body_text': presence[2],
'index_start': presence[3],
'index_end': presence[4],
'score': presence[5]
})
del raw_pe_presences
pe_presences_per_pe = {}
for presence in pe_presences:
if presence['platform_edition_id'] not in pe_presences_per_pe:
pe_presences_per_pe[presence['platform_edition_id']] = list()
pe_presences_per_pe[presence['platform_edition_id']].append(
presence['listing_id'])
match = Match(
score=presence['score'],
is_matched_via_body_text=presence['is_matched_via_body_text'],
start=presence['index_start'],
end=presence['index_end'],
item=PlatformEdition.get_by_id(
presence['platform_edition_id']),
listing=Listing.get_by_id(presence['listing_id']))
match.add_to_registry()
print('inner Match')
print(Match)
del pe_presences
return pe_presences_per_pe
def generate_message_text(self):
message_text_matches = ''
message_text_matches = message_text_matches + 'PLATFORMS & EDITIONS\n\n'
pp.pprint(Listing.registry)
for platform in Platform.get_all():
if platform.id in self.platform_editions or (
self.platforms and platform.id in self.platforms):
message_text_per_platform = ''
# ----- Platform Name -----
message_text_per_platform = message_text_per_platform + '----- ' + platform.name + ' -----\n'
if self.platform_editions.get(platform.id):
for edition_id in self.platform_editions.get(platform.id):
edition = PlatformEdition.get_by_id(edition_id)
edition_referencial_name = edition.referencial_name()
message_text_per_platform = message_text_per_platform + edition_referencial_name + '\n'
if Mailer.pe_presences_per_pe.get(edition.id):
for listing_id in Mailer.pe_presences_per_pe.get(
edition.id):
listing = Listing.get_by_id(listing_id)
# listing title
message_text_per_platform = message_text_per_platform + '\t' + listing.title + '\n'
# listing price
if listing.price is None:
message_text_per_platform = message_text_per_platform + '\t' + '(price not listed)' + '\n'
else:
message_text_per_platform = message_text_per_platform + '\t' + listing.price + '\n'
# listing link
message_text_per_platform = message_text_per_platform + '\t' + listing.url + '\n'
# listing datetime
message_text_per_platform = message_text_per_platform + '\t' + str(
listing.date_posted) + '\n'
# blank line
message_text_per_platform = message_text_per_platform + '\t' + '\n'
message_text_per_platform = message_text_per_platform + '\n'
message_text_matches = message_text_matches + message_text_per_platform
return message_text_matches
def get_searched_editions(q):
conn = get_db_connection()
cur = conn.cursor()
like_q = '%' + q + '%'
data_dict = {
'q': q,
'like_q': like_q,
}
cur.execute("""
SELECT
e.id,
e.name,
e.platform_id,
e.official_color,
e.has_matte,
e.has_transparency,
e.has_gloss,
e.note,
e.image_url
FROM platform_editions as e
WHERE e.name ILIKE %(like_q)s OR e.official_color ILIKE %(like_q)s
ORDER BY e.name, e.official_color;
""", data_dict)
editions = cur.fetchall()
all_editions = []
for e in editions:
current = PlatformEdition(id=e[0], name=e[1], official_color=e[3], has_matte=e[4], has_transparency=e[5], has_gloss=e[6], note=e[7], image_url=e[8])
all_editions.append(current)
cur.close()
conn.close()
return all_editions
def generate_message_html(self):
message_text_matches = ''
products_matched_ct = 0
platforms_and_editions_category_title = '<h2>PLATFORMS & EDITIONS</h2>\n\n'
message_text_all_platforms = ''
for platform in Platform.get_all():
message_text_this_platform = ''
if platform.id in self.platform_editions or (
self.platforms and platform.id in self.platforms):
# Platform Name
this_platform_name = f'\n<h3>{platform.name}</h3>'
message_text_this_platform_general = ''
message_text_this_platform_list = ''
platform_general_list_start = '\n<ul style="padding: 0; list-style: none;">'
if self.platform_presences_per_platform.get(platform.id):
listing_ct = 0
for listing_id in self.platform_presences_per_platform.get(
platform.id):
listing = Listing.get_by_id(listing_id)
if listing is not None:
listing_ct += 1
match = Match.get_by_info(listing, platform)
message_text_this_platform_list = message_text_this_platform_list + Mailer.create_listing_html(
match, listing_ct)
platform_general_list_end = '\n</ul>'
if len(message_text_this_platform_list) > 0:
message_text_this_platform_general = message_text_this_platform_general + f"""{platform_general_list_start}\n{message_text_this_platform_list}\n{platform_general_list_end}"""
message_text_all_editions = ''
if self.platform_editions.get(platform.id):
for edition_id in self.platform_editions.get(platform.id):
edition = PlatformEdition.get_by_id(edition_id)
edition_referencial_name = edition.referencial_name()
message_text_this_edition = ''
this_edition_name = f'\n<h4>{edition_referencial_name}</h4>'
message_text_this_edition_listings = ''
if Mailer.pe_presences_per_pe.get(edition.id):
listing_ct = 0
editions_list_start = '\n<ul style="padding: 0; list-style: none;">'
for listing_id in Mailer.pe_presences_per_pe.get(
edition.id):
listing = Listing.get_by_id(listing_id)
if listing is not None:
listing_ct += 1
match = Match.get_by_info(listing, edition)
message_text_this_edition_listings = message_text_this_edition_listings + Mailer.create_listing_html(
match, listing_ct)
editions_list_end = '\n</ul>'
if len(message_text_this_edition_listings) > 0:
products_matched_ct += 1
# add edition title
message_text_this_edition = message_text_this_edition + this_edition_name
# add edition listings
message_text_this_edition = message_text_this_edition + editions_list_start + message_text_this_edition_listings + editions_list_end
if len(message_text_this_edition) > 0:
# add edition message text
message_text_all_editions = message_text_all_editions + message_text_this_edition
if len(message_text_all_editions) > 0 or len(
message_text_this_platform_general) > 0:
# add platform title
message_text_this_platform = message_text_this_platform + this_platform_name
if len(message_text_this_platform_general) > 0:
products_matched_ct += 1
general_platform_matches_title = f'\n<h4>General</h4>'
# add platform generic message text
message_text_this_platform = message_text_this_platform + general_platform_matches_title + message_text_this_platform_general
if len(message_text_all_editions) > 0:
# add platform editions message text
message_text_this_platform = message_text_this_platform + message_text_all_editions
if len(message_text_this_platform) > 0:
# add platform editions message text
message_text_all_platforms = message_text_all_platforms + message_text_this_platform
if len(message_text_all_platforms) > 0:
# add platform & edition category title to mail message
message_text_matches = message_text_matches + platforms_and_editions_category_title
# add platform & edition category content to mail message
message_text_matches = message_text_matches + message_text_all_platforms
return message_text_matches, products_matched_ct