| Line 23... |
Line 23... |
| 23 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
23 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
| 24 |
|
24 |
|
| 25 |
def closeConnection(conn):
|
25 |
def closeConnection(conn):
|
| 26 |
conn.close()
|
26 |
conn.close()
|
| 27 |
|
27 |
|
| 28 |
def getSourceId(source):
|
28 |
def getSourceId(is_paid, source):
|
| 29 |
if sourceIdMap.has_key(source) :
|
29 |
if sourceIdMap.has_key((is_paid, source)) :
|
| 30 |
return sourceIdMap[source]
|
30 |
return sourceIdMap[(is_paid, source)]
|
| 31 |
|
31 |
|
| 32 |
# Prepare SQL query to insert new source into db.
|
32 |
# Prepare SQL query to insert new source into db.
|
| 33 |
insertSql = "insert into source(url) values (%s)"
|
33 |
insertSql = "insert into source(is_paid, url) values (%s, %s)"
|
| 34 |
selectSql = "select id from source where url = %s"
|
34 |
selectSql = "select id from source where is_paid = %s and url = %s"
|
| 35 |
conn = getDbConnection()
|
35 |
conn = getDbConnection()
|
| 36 |
try:
|
36 |
try:
|
| 37 |
# prepare a cursor object using cursor() method
|
37 |
# prepare a cursor object using cursor() method
|
| 38 |
cursor = conn.cursor()
|
38 |
cursor = conn.cursor()
|
| 39 |
# Execute the SQL command
|
39 |
# Execute the SQL command
|
| 40 |
cursor.execute(insertSql, (source))
|
40 |
cursor.execute(insertSql, (is_paid, source))
|
| 41 |
|
41 |
|
| 42 |
# Fetch source id.
|
42 |
# Fetch source id.
|
| 43 |
cursor.execute(selectSql, (source))
|
43 |
cursor.execute(selectSql, (is_paid, source))
|
| 44 |
result = cursor.fetchone()
|
44 |
result = cursor.fetchone()
|
| 45 |
sourceIdMap[source] = result[0]
|
45 |
sourceIdMap[(is_paid, source)] = result[0]
|
| 46 |
except Exception as e:
|
46 |
except Exception as e:
|
| 47 |
print "Error: unable to fetch data"
|
47 |
print "Error: unable to fetch data"
|
| 48 |
print e
|
48 |
print e
|
| 49 |
|
49 |
|
| 50 |
return sourceIdMap[source]
|
50 |
return sourceIdMap[(is_paid, source)]
|
| 51 |
|
51 |
|
| 52 |
def loadSourceIdMap():
|
52 |
def loadSourceIdMap():
|
| 53 |
conn = getDbConnection()
|
53 |
conn = getDbConnection()
|
| 54 |
|
54 |
|
| 55 |
# Prepare SQL query to load source mapping from db.
|
55 |
# Prepare SQL query to load source mapping from db.
|
| 56 |
sql = "select id, url from source"
|
56 |
sql = "select id, is_paid, url from source"
|
| 57 |
try:
|
57 |
try:
|
| 58 |
# prepare a cursor object using cursor() method
|
58 |
# prepare a cursor object using cursor() method
|
| 59 |
cursor = conn.cursor()
|
59 |
cursor = conn.cursor()
|
| 60 |
# Execute the SQL command
|
60 |
# Execute the SQL command
|
| 61 |
cursor.execute(sql)
|
61 |
cursor.execute(sql)
|
| 62 |
# Fetch all the rows in a list of lists.
|
62 |
# Fetch all the rows in a list of lists.
|
| 63 |
results = cursor.fetchall()
|
63 |
results = cursor.fetchall()
|
| 64 |
for row in results:
|
64 |
for row in results:
|
| 65 |
sourceIdMap[row[1]] = row[0]
|
65 |
sourceIdMap[(row[1], row[2])] = row[0]
|
| 66 |
cursor.close()
|
66 |
cursor.close()
|
| 67 |
except Exception as e:
|
67 |
except Exception as e:
|
| 68 |
print "Error: unable to fetch data"
|
68 |
print "Error: unable to fetch data"
|
| 69 |
print e
|
69 |
print e
|
| 70 |
|
70 |
|
| Line 81... |
Line 81... |
| 81 |
if len(results) > 0: cursor.execute("delete from productactivity where date_id = %s", results[0][0])
|
81 |
if len(results) > 0: cursor.execute("delete from productactivity where date_id = %s", results[0][0])
|
| 82 |
|
82 |
|
| 83 |
cursor.executemany (
|
83 |
cursor.executemany (
|
| 84 |
"""
|
84 |
"""
|
| 85 |
insert into productactivity (
|
85 |
insert into productactivity (
|
| 86 |
date_id, catalog_item_id, is_first_source_paid, first_source_id, is_session_source_paid, session_source_id,
|
86 |
date_id, catalog_item_id, first_source_id, session_source_id,
|
| 87 |
view, add_to_cart, add_to_research, delete_from_cart, delete_from_research, shipping_access,
|
87 |
view, add_to_cart, add_to_research, delete_from_cart, delete_from_research, shipping_access,
|
| 88 |
payment_success, order_creation, payment_failure, proceed_to_pay, unique_view, unique_add_to_cart,
|
88 |
payment_success, order_creation, payment_failure, proceed_to_pay, unique_view, unique_add_to_cart,
|
| 89 |
unique_add_to_research, unique_delete_from_cart, unique_delete_from_research, unique_shipping_access,
|
89 |
unique_add_to_research, unique_delete_from_cart, unique_delete_from_research, unique_shipping_access,
|
| 90 |
unique_payment_success, unique_order_creation, unique_payment_failure, unique_proceed_to_pay)
|
90 |
unique_payment_success, unique_order_creation, unique_payment_failure, unique_proceed_to_pay)
|
| 91 |
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
91 |
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
| 92 |
""", results)
|
92 |
""", results)
|
| 93 |
conn.commit()
|
93 |
conn.commit()
|
| 94 |
results = []
|
94 |
results = []
|
| 95 |
cursor.close()
|
95 |
cursor.close()
|
| 96 |
except Exception as e:
|
96 |
except Exception as e:
|
| Line 115... |
Line 115... |
| 115 |
isPaidFirstSource = 0 if not activity.has_key('isPaidFirstSource') else 1 if activity['isPaidFirstSource'] == 'true' else 0
|
115 |
isPaidFirstSource = 0 if not activity.has_key('isPaidFirstSource') else 1 if activity['isPaidFirstSource'] == 'true' else 0
|
| 116 |
firstSource = str(activity['firstSource']) if activity.has_key('firstSource') else ""
|
116 |
firstSource = str(activity['firstSource']) if activity.has_key('firstSource') else ""
|
| 117 |
isPaidSessionSource = 0 if not activity.has_key('isPaidSessionSource') else 1 if activity['isPaidSessionSource'] == 'true' else 0
|
117 |
isPaidSessionSource = 0 if not activity.has_key('isPaidSessionSource') else 1 if activity['isPaidSessionSource'] == 'true' else 0
|
| 118 |
sessionSource = str(activity['sessionSource']) if activity.has_key('sessionSource') else ""
|
118 |
sessionSource = str(activity['sessionSource']) if activity.has_key('sessionSource') else ""
|
| 119 |
|
119 |
|
| 120 |
firstSourceId = getSourceId(firstSource)
|
120 |
firstSourceId = getSourceId(isPaidFirstSource, firstSource)
|
| 121 |
sessionSourceId = getSourceId(sessionSource)
|
121 |
sessionSourceId = getSourceId(isPaidSessionSource, sessionSource)
|
| 122 |
|
122 |
|
| 123 |
catalogId = int(activity['catalogId'])
|
123 |
catalogId = int(activity['catalogId'])
|
| 124 |
|
124 |
|
| 125 |
view = int(activity['view']) if activity.has_key('view') else 0
|
125 |
view = int(activity['view']) if activity.has_key('view') else 0
|
| 126 |
addToCart = int(activity['addToCart']) if activity.has_key('addToCart') else 0
|
126 |
addToCart = int(activity['addToCart']) if activity.has_key('addToCart') else 0
|
| Line 142... |
Line 142... |
| 142 |
uniquePaymentSuccess = int(activity['uniquePaymentSuccess']) if activity.has_key('uniquePaymentSuccess') else 0
|
142 |
uniquePaymentSuccess = int(activity['uniquePaymentSuccess']) if activity.has_key('uniquePaymentSuccess') else 0
|
| 143 |
uniqueOrderCreation = int(activity['uniqueOrderCreation']) if activity.has_key('uniqueOrderCreation') else 0
|
143 |
uniqueOrderCreation = int(activity['uniqueOrderCreation']) if activity.has_key('uniqueOrderCreation') else 0
|
| 144 |
uniquePaymentFailure = int(activity['uniquePaymentFailure']) if activity.has_key('uniquePaymentFailure') else 0
|
144 |
uniquePaymentFailure = int(activity['uniquePaymentFailure']) if activity.has_key('uniquePaymentFailure') else 0
|
| 145 |
uniqueProceedToPay = int(activity['uniqueProceedToPay']) if activity.has_key('uniqueProceedToPay') else 0
|
145 |
uniqueProceedToPay = int(activity['uniqueProceedToPay']) if activity.has_key('uniqueProceedToPay') else 0
|
| 146 |
|
146 |
|
| 147 |
results.append((dateDelta, catalogId, isPaidFirstSource, firstSourceId, isPaidSessionSource, sessionSourceId, view, addToCart, addToResearch,
|
147 |
results.append((dateDelta, catalogId, firstSourceId, sessionSourceId, view, addToCart, addToResearch,
|
| 148 |
deleteFromCart, deleteFromResearch, shippingAccess, paymentSuccess, orderCreation, paymentFailure, proceedToPay,
|
148 |
deleteFromCart, deleteFromResearch, shippingAccess, paymentSuccess, orderCreation, paymentFailure, proceedToPay,
|
| 149 |
uniqueView, uniqueAddToCart, uniqueAddToResearch, uniqueDeleteFromCart, uniqueDeleteFromResearch,
|
149 |
uniqueView, uniqueAddToCart, uniqueAddToResearch, uniqueDeleteFromCart, uniqueDeleteFromResearch,
|
| 150 |
uniqueShippingAccess, uniquePaymentSuccess, uniqueOrderCreation, uniquePaymentFailure, uniqueProceedToPay))
|
150 |
uniqueShippingAccess, uniquePaymentSuccess, uniqueOrderCreation, uniquePaymentFailure, uniqueProceedToPay))
|
| 151 |
|
151 |
|
| 152 |
pushResultsToDb(results)
|
152 |
pushResultsToDb(results)
|