def get_top_selling_items(category_name):
urlparse.uses_netloc.append("postgres")
database_url = urlparse.urlparse(constants.database_url)
conn = psycopg2.connect( database=database_url.path[1:], user=database_url.username, password=database_url.password, host=database_url.hostname, port=database_url.port )
curr = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
id_list=[]
curr.execute("select id from categories where name ='"+category_name+"'")
row=curr.fetchone()
id_list.append(row['id'])
curr.execute("select id from categories where parent_id ="+str(id_list[0]))
rows=curr.fetchall()
for id in rows:
id_list.append(id['id'])
curr.execute("SELECT l.design_id FROM line_items l,categories_designs cd,categories c,designs d WHERE cd.design_id=l.design_id AND c.id=cd.category_id AND l.design_id = d.id AND lower(d.state) like 'in_stock' AND l.created_at > current_date - interval '90' day and c.id in ("+','.join(map(str, id_list))+") GROUP BY l.design_id,c.name ORDER BY count(l.id) DESC LIMIT 100")
design_ids = rows = list(set(list(itertools.chain.from_iterable(curr.fetchall()))))
top_selling_list=[]
top_selling_hash={}
for design_id in design_ids:
curr.execute('SELECT id,photo_file_name FROM images where design_id = '+str(design_id))
rows = curr.fetchone()
image_link = image_hash.get_image_link(rows['photo_file_name'],rows['id'],'_thumb')
curr.execute('SELECT discount_percent from designs where id='+str(design_id))
row = curr.fetchone()
internal_hash = {'image_link': image_link,'discount': row['discount_percent'],'design_id': design_id}
top_selling_list.append(internal_hash)
top_selling_hash['top_items']=top_selling_list
return top_selling_hash
def create_creative():
con = None
simple_list = []
times = int(
raw_input("Please enter the number of cards for carousel ads.\n"))
try:
con = header.create_connection()
cur = con.cursor()
for i in xrange(times):
design_id = raw_input("Please enter design id.\n")
cur.execute('SELECT id,title,price from designs where id=' +
str(design_id))
row = cur.fetchone()
cur.execute(
'SELECT id,photo_file_name FROM images where design_id = ' +
str(design_id))
rows = cur.fetchone()
image_link = image_hash.get_image_link()
product1 = AdCreativeLinkDataChildAttachment()
product1[AdCreativeLinkDataChildAttachment.Field.
link] = 'www.mirraw.com/d/' + str(row[0])
product1[AdCreativeLinkDataChildAttachment.Field.name] = str(
row[1])
product1[
AdCreativeLinkDataChildAttachment.Field.description] = row[2]
product1[AdCreativeLinkDataChildAttachment.Field.
image_hash] = image_hash.get_image_hash(
image_link, rows[1])
simple_list.append(product1)
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
def create_carousel_ad(caption, adset_id, ad_name, times, design_list,
account_id, land_on_design, url, campaign_tag):
logger = logging.getLogger('testlogger')
logger.debug("In create carousel")
conn = None
simple_list = []
account_medium_list = {
"act_940036526039709": "fb_ocpc",
"act_938286879548007": "acpm",
"act_1010404049002956": "acpm",
"act_1385041538425866": "acpm",
"act_1128744890502204": "jcpc",
"act_10152414205523137": "int",
"act_972844956092199": "exp"
}
utm_medium = account_medium_list[account_id]
try:
urlparse.uses_netloc.append("postgres")
database_url = urlparse.urlparse(constants.database_url)
conn = psycopg2.connect(database=database_url.path[1:],
user=database_url.username,
password=database_url.password,
host=database_url.hostname,
port=database_url.port)
curr = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
for i in xrange(times):
design_id = design_list[i]
curr.execute(
'SELECT discount_percent,designer_id from designs where id=' +
str(design_id))
row = curr.fetchone()
curr.execute(
'SELECT id,photo_file_name FROM images where design_id = ' +
str(design_id))
rows = curr.fetchone()
curr.execute(
'SELECT name FROM "categories" INNER JOIN "categories_designs" ON "categories"."id" = "categories_designs"."category_id" WHERE design_id ='
+ str(design_id))
category_name = curr.fetchone()
image_link = image_hash.get_image_link(rows['photo_file_name'],
rows['id'])
if row['discount_percent'] is None:
row['discount_percent'] = 0
product1 = AdCreativeLinkDataChildAttachment()
if land_on_design:
product1[
AdCreativeLinkDataChildAttachment.Field.
link] = 'www.mirraw.com/designers/' + str(
row['designer_id']
) + '/designs/' + str(
design_id
) + '?utm_source=facebook-auto&utm_medium=' + utm_medium + '&utm_campaign=' + campaign_tag
else:
product1[
AdCreativeLinkDataChildAttachment.Field.
link] = url + '?pid=' + str(
design_id
) + '&utm_source=facebook&utm_medium=' + utm_medium + '&utm_campaign=' + campaign_tag
product1[AdCreativeLinkDataChildAttachment.Field.
name] = category_name['name']
action = AdCreativeLinkDataCallToAction()
link_value = AdCreativeLinkDataCallToActionValue()
link_value[AdCreativeLinkDataCallToActionValue.Field.
link_title] = 'Discount ' + str(
row['discount_percent']) + '%'
action[AdCreativeLinkDataCallToAction.Field.
type] = AdCreativeLinkDataCallToAction.Type.shop_now
action[AdCreativeLinkDataCallToAction.Field.value] = link_value
product1[AdCreativeLinkDataChildAttachment.Field.
call_to_action] = action
product1[AdCreativeLinkDataChildAttachment.Field.
description] = 'Discount ' + str(
row['discount_percent']) + '%'
product1[AdCreativeLinkDataChildAttachment.Field.
image_hash] = image_hash.get_image_hash(
image_link, rows[1], account_id)
sleep(0.5)
simple_list.append(product1)
link = AdCreativeLinkData()
link[
link.Field.
link] = url + '&utm_source=facebook&utm_medium=' + utm_medium + '&utm_campaign=' + campaign_tag
link[link.Field.child_attachments] = simple_list
link[
link.Field.
caption] = url + '&utm_source=facebook&utm_medium=' + utm_medium + '&utm_campaign=' + campaign_tag
call_to_action_button = AdCreativeLinkDataCallToAction()
call_to_action_button[
AdCreativeLinkDataCallToAction.Field.
type] = AdCreativeLinkDataCallToAction.Type.shop_now
link[link.Field.call_to_action] = call_to_action_button
link[link.Field.message] = caption
logger.info(link)
story = AdCreativeObjectStorySpec()
story[story.Field.page_id] = constants.page_id
story[story.Field.link_data] = link
creative = AdCreative(parent_id=account_id)
creative[AdCreative.Field.name] = 'MPA Creative'
creative[AdCreative.Field.object_story_spec] = story
creative.remote_create()
creative = json.loads(str(creative).replace('<AdCreative> ', ''))
logger.info(creative)
ad = Ad(parent_id=account_id)
ad[Ad.Field.name] = ad_name
ad[Ad.Field.adset_id] = adset_id
ad[Ad.Field.status] = Campaign.Status.paused
ad[Ad.Field.creative] = {'creative_id': str(creative['id'])}
logger.info('Creating Ad')
ad.remote_create()
logger.info(ad)
except psycopg2.DatabaseError, e:
logger.error('Error %s' % e)
return False
def create_carousel_ad(caption, adset_id, ad_name, design_list, land_on_design,
url, campaign_tag, country_code):
FORMAT = '%(name)s:%(levelname)s:%(asctime)-15s:%(message)s'
logging.basicConfig(filename='%s-facebook-automated.log' % date.today(),
format=FORMAT,
level=logging.DEBUG)
logging.getLogger('create_carousel')
logging.info('In create carousel')
connection = None
simple_list = []
utm_medium = 'exp'
try:
connection = header.create_connection(
os.environ['FB_APP_DATABASE_URL'])
cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)
for design_id in design_list:
cursor.execute(
'SELECT designer_id,discount_price from designs where id=' +
str(design_id[0]))
row = cursor.fetchone()
cursor.execute(
'SELECT id,photo_file_name FROM images where design_id = ' +
str(design_id[0]))
rows = cursor.fetchone()
cursor.execute(
'SELECT name FROM "categories" INNER JOIN "categories_designs" ON "categories"."id" = "categories_designs"."category_id" WHERE design_id ='
+ str(design_id[0]))
category_name = cursor.fetchone()
cursor.execute(
"SELECT rate FROM currency_converts WHERE country_code = '" +
country_code + "'")
rate = cursor.fetchone()
image_link = image_hash.get_image_link(rows['photo_file_name'],
rows['id'])
product1 = AdCreativeLinkDataChildAttachment()
if land_on_design:
product1[
AdCreativeLinkDataChildAttachment.Field.
link] = 'www.mirraw.com/designers/' + str(
row['designer_id']
) + '/designs/' + str(
design_id[0]
) + '?utm_source=facebook-auto&utm_medium=' + utm_medium + '&utm_campaign=' + campaign_tag
else:
product1[
AdCreativeLinkDataChildAttachment.Field.
link] = url + '?pid=' + str(
design_id
) + '&utm_source=facebook&utm_medium=' + utm_medium + '&utm_campaign=' + campaign_tag
product1[AdCreativeLinkDataChildAttachment.Field.
name] = category_name['name']
price = (row['discount_price'] * 1.0 / rate['rate'])
product1[AdCreativeLinkDataChildAttachment.Field.
description] = 'Price: $' + str(round(price, 2))
logging.info(image_link)
logging.info(rows['photo_file_name'])
product1[AdCreativeLinkDataChildAttachment.Field.
image_hash] = image_hash.get_image_hash(
image_link, rows['photo_file_name'])
sleep(10)
simple_list.append(product1)
link = AdCreativeLinkData()
link[link.Field.link] = 'www.mirraw.com'
link[link.Field.child_attachments] = simple_list
link[link.Field.caption] = caption
story = AdCreativeObjectStorySpec()
story[story.Field.page_id] = header.page_id
story[story.Field.link_data] = link
creative = AdCreative(parent_id=header.my_account['id'])
creative[AdCreative.Field.name] = 'MPA Creative'
creative[AdCreative.Field.object_story_spec] = story
creative.remote_create()
creative = json.loads(str(creative).replace('<AdCreative> ', ''))
ad = Ad(parent_id=header.my_account['id'])
ad[Ad.Field.name] = ad_name
ad[Ad.Field.adset_id] = adset_id
ad[Ad.Field.status] = Campaign.Status.paused
ad[Ad.Field.creative] = {'creative_id': str(creative['id'])}
logging.info('Creating Ad')
ad.remote_create()
logging.info(ad)
except psycopg2.DatabaseError, e:
logging.error('Error %s' % e)
return False
import header
import json
import create_adset
import psycopg2
import image_hash
con = None
try:
con = header.create_connection()
cur = con.cursor()
design_id=raw_input("Please enter design id.\n")
cur.execute('SELECT id,title,price from designs where id='+str(design_id))
row=cur.fetchone()
cur.execute('SELECT id,photo_file_name FROM images where design_id = '+str(design_id))
rows=cur.fetchone()
image_link=image_hash.get_image_link(rows[1],rows[0])
except psycopg2.DatabaseError, e:
print 'Error %s' % e
sys.exit(1)
finally:
if con:
con.close()
caption = raw_input("Please enter caption for the ad")
link_data = AdCreativeLinkData()
link_data[AdCreativeLinkData.Field.message] = 'try it out'
link_data[AdCreativeLinkData.Field.link] = 'www.mirraw.com'
link_data[AdCreativeLinkData.Field.caption] = caption
link_data[AdCreativeLinkData.Field.image_hash] = image_hash.get_image_hash(image_link,rows[1])