def SearchAmazon(self, op):
"""Find Amazon listings based on given search terms. Add the results to a list.
Parameters: terms= A string containing search terms.
addtolist= A list name to add results to.
"""
params = op.params
r = self.mwsapi.ListMatchingProducts(
priority=op.priority,
MarketplaceId=self.mwsapi.api.market_id(),
Query=params['terms'])
if self.is_error_response(r, op):
return
parser = ListMatchingProductsParser(r.readAll().data().decode())
for product in parser.products:
# Update the product's info
amz_listing = dbhelpers.get_or_create(self.dbsession,
AmazonListing,
sku=product.asin)
product.update(amz_listing)
# Update the product's category
category = dbhelpers.get_or_create_category(
self.dbsession, product.product_category_id,
product.product_group)
amz_listing.category = category
# Schedule an update to fill in the rest of the product info
self.dbsession.add(
Operation.UpdateAmazonListing(listing=amz_listing,
priority=op.priority))
# Add to list
if 'addtolist' in params:
add_list = dbhelpers.get_or_create(self.dbsession,
List,
name=params['addtolist'],
is_amazon=True)
dbhelpers.get_or_create(self.dbsession,
ListMembership,
list=add_list,
listing=amz_listing)
op.complete = True
def UpdateAmazonListing(self, op):
"""Update pricing, salesrank, offers, and merchant info for listing, then add to product history.
Parameters: log= Add the new product data to the log
repeat= Repeat this operation after the given number of minutes.
testmargins: Create a TestMargins operation if the criteria are met.
salesrank= Sales Rank must be below the given value.
threshold= The minimum require profit margin to add to the list.
list= The name of the list to add matches to.
"""
amz_listing = op.listing
params = op.params
r = self.paapi.ItemLookup(
priority=op.priority,
ItemId=amz_listing.sku,
ResponseGroup='OfferFull,SalesRank,ItemAttributes')
if self.is_error_response(r, op):
return
parser = ItemLookupParser(r.readAll().data().decode())
product = parser.product
if product:
product.update(amz_listing)
else:
code = parser.xpath_get('.//Error/Code')
message = parser.xpath_get('.//Error/Message')
op.error = True
op.message = 'ItemLookup failed for ASIN %s: %s. %s' % (
amz_listing.sku, code, message)
return
parser.product.update(amz_listing)
# Update the merchant
merchant = dbhelpers.get_or_create(self.dbsession,
AmazonMerchant,
name=parser.product.merchant
or 'N/A')
amz_listing.merchant = merchant
# ItemLookup tells us the current buy box price, but not including shipping. Call GetLowestOffListings
# to get the lowest offer INCLUDING shipping. This is *probably* the buy box price
r = self.mwsapi.GetLowestOfferListingsForASIN(
priority=op.priority,
MarketplaceId=self.mwsapi.api.market_id(),
ASINList=[amz_listing.sku],
ItemCondition='New')
if self.is_error_response(r, op):
return
parser = GetLowestOfferListingsForASINParser(
r.readAll().data().decode())
result = next(parser.get_product_info())
if result['error']:
op.error = True
op.message = result['message']
return
else:
amz_listing.price = max(amz_listing.price or 0, result['price']
or 0) or None
# amz_listing.hasprime = result['prime']
# Test margins?
if 'testmargins' in params:
if 'salesrank' not in params['testmargins'] \
or (amz_listing.salesrank and amz_listing.salesrank <= params['testmargins']['salesrank']):
test_op = Operation.TestMargins(listing=amz_listing,
params=params['testmargins'],
priority=op.priority)
self.dbsession.add(test_op)
if 'log' in params and params['log'] == True:
self.dbsession.add(
AmzProductHistory(amz_listing_id=amz_listing.id,
salesrank=amz_listing.salesrank,
hasprime=amz_listing.hasprime,
price=amz_listing.price,
merchant_id=amz_listing.merchant_id,
offers=amz_listing.offers,
timestamp=func.now()))
op.message = None
if 'repeat' in params and params['repeat'] > 0:
op.scheduled = datetime.utcnow() + timedelta(
minutes=params['repeat'])
else:
op.complete = True
def FindAmazonMatches(self, op):
"""Query Amazon for products matching a given listing.
Parameters: linkif: create a link only if the conditions are met
conf= match confidence greater than or equal to the given value.
testmargins: Create a TestMargins operation if the conditions are met.
salesrank= Maximum sales rank
list= The name of the list given to TestMargins
threshold= The minimum margin threshold given to TestMargins
"""
vnd_listing = op.listing
params = op.params
title = str(vnd_listing.title).replace(vnd_listing.brand, '').replace(
vnd_listing.model, '').strip()
query = ' '.join([vnd_listing.brand, vnd_listing.model, title])
r = self.mwsapi.ListMatchingProducts(
priority=op.priority,
MarketplaceId=self.mwsapi.api.market_id(),
Query=query)
if self.is_error_response(r, op):
return
parser = ListMatchingProductsParser(r.readAll().data().decode())
for product in parser.products:
# Update the product info
amz_listing = dbhelpers.get_or_create(self.dbsession,
AmazonListing,
sku=product.asin)
product.update(amz_listing)
# Update the product category
category = dbhelpers.get_or_create_category(
self.dbsession, product.product_category_id,
product.product_group)
amz_listing.category = category
# Create a link between the two listings. If it doesn't meet the criteria, expunge() it below.
link = dbhelpers.link_products(self.dbsession,
amz=amz_listing,
vnd=vnd_listing)
# Link criteria - it meets the threshold, or no threshold was provided
add_cond_1 = 'linkif' in params \
and 'conf' in params['linkif'] \
and link.confidence >= float(params['linkif']['conf'])
add_cond_2 = 'linkif' not in params
if add_cond_1 or add_cond_2:
# Test margins?
if 'testmargins' in params:
if 'salesrank' not in params['testmargins'] \
or (product.salesrank and product.salesrank <= params['testmargins']['salesrank']):
update_op = Operation.UpdateAmazonListing(
listing=amz_listing,
params={'testmargins': params['testmargins']},
priority=op.priority)
self.dbsession.add(update_op)
else:
self.dbsession.expunge(link)
op.message = '%s links found.' % len(vnd_listing.amz_links)
op.complete = True
def TestMargins(self, op):
"""Look at the potential profit margin for a listing, based on available sources. Add the listing to a list
if the margin meets a minimum threshold.
Parameters: confidence= The minimum confidence level for sources to be considered.
threshold= The minimum profit margin to be added to the list.
list= The name of the list to add matches to.
"""
amz_listing = op.listing
params = op.params
if not amz_listing.price or not amz_listing.quantity:
op.complete = True
return
min_confidence = params.get('confidence', 0)
# Get the lowest vendor cost available
vnd_unit_cost = self.dbsession.query(func.min(VendorListing.unit_price * (1 + Vendor.tax_rate + Vendor.ship_rate))).\
join(LinkedProducts, LinkedProducts.vnd_listing_id == Listing.id).\
filter(LinkedProducts.amz_listing_id == amz_listing.id,
LinkedProducts.confidence >= min_confidence,
Vendor.id == Listing.vendor_id).\
scalar()
if vnd_unit_cost is None:
op.complete = True
return
# Test the margin based solely on cost
cost = vnd_unit_cost * amz_listing.quantity
profit = amz_listing.price - cost
if profit / cost < params['threshold']:
op.complete = True
return
# Now get fees
price_point = dbhelpers.get_or_create(self.dbsession,
AmzPriceAndFees,
amz_listing_id=amz_listing.id,
price=amz_listing.price)
if price_point.fba is None:
self.GetMyFeesEstimate(op)
fba = price_point.fba or amz_listing.price * .25
prep = price_point.prep or 0
ship = price_point.ship or 0
# Calculate the margin
cost = vnd_unit_cost * amz_listing.quantity + prep + ship
profit = amz_listing.price - cost - fba
if profit / cost < params['threshold']:
op.complete = True
return
# Add to the list
dbhelpers.add_ids_to_list(self.dbsession,
listing_ids=[amz_listing.id],
list_name=params['list'])
op.complete = True
def on_import_csv(self):
"""Opens the 'Import CSV' dialog, imports the contents into the database."""
# Show the dialog
dialog = ImportCSVDialog(self)
ok = dialog.exec()
if not ok:
return
file_name = dialog.filename
vendor_name = dialog.vendorname
start_row = dialog.startrow
end_row = dialog.endrow
vendor = dbhelpers.get_or_create(self.dbsession,
Vendor,
name=vendor_name)
self.dbsession.flush()
add_list = dbhelpers.get_or_create(
self.dbsession, List,
name=dialog.list_name) if dialog.list_name else None
with open(file_name) as file:
dialog = ProgressDialog(minimum=start_row,
maximum=end_row,
parent=self)
dialog.setModal(True)
dialog.show()
reader = csv.DictReader(file)
for row in reader:
if reader.line_num < start_row:
continue
elif reader.line_num > end_row:
break
if dialog.result() == QDialog.Rejected:
dialog.close()
self.dbsession.rollback()
return
dialog.progress_value = reader.line_num
dialog.status_text = 'Importing row {} of {}...'.format(
reader.line_num - start_row, end_row - start_row)
QCoreApplication.processEvents()
sku = row.get('sku')
product = dbhelpers.get_or_create(self.dbsession,
VendorListing,
vendor_id=vendor.id,
sku=sku)
product.title = row.get('title')
product.brand = row.get('brand')
product.model = row.get('model')
product.upc = row.get('upc')
product.quantity = row.get('quantity')
product.price = row.get('price')
product.url = row.get('url')
product.updated = func.now()
if add_list:
dbhelpers.get_or_create(self.dbsession,
ListMembership,
list=add_list,
listing=product)
self.dbsession.commit()
dialog.close()
self.populate_source_box()
self.sourceBox.setCurrentText(vendor_name)
self.sourceBox.activated.emit(0)