Subversion Repositories SmartDukaan

#!/usr/bin/python

"""

 This script gets product activities by source for a date from google app engine and dumps it in pentaho bi database.

"""

import json

import urllib2

import MySQLdb

import datetime

import sys

DB_HOST = "localhost"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "sales"

sourceIdMap = {}

def getDbConnection():

    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

def closeConnection(conn):

    conn.close()

def getSourceId(is_paid, source):

    if sourceIdMap.has_key((is_paid, source)) :

        return sourceIdMap[(is_paid, source)] 

    # Prepare SQL query to insert new source into db.

    insertSql = "insert into source(is_paid, url) values (%s, %s)"

    selectSql = "select id from source where is_paid = %s and url = %s"

    conn = getDbConnection()

    try:

      # prepare a cursor object using cursor() method

      cursor = conn.cursor()

      # Execute the SQL command

      cursor.execute(insertSql, (is_paid, source))

      # Fetch source id.

      cursor.execute(selectSql, (is_paid, source))

      result = cursor.fetchone()

      sourceIdMap[(is_paid, source)] = result[0]

    except Exception as e:

      print "Error: unable to fetch data"

      print e

    return sourceIdMap[(is_paid, source)]

def loadSourceIdMap():

    conn = getDbConnection()

    # Prepare SQL query to load source mapping from db.

    sql = "select id, is_paid, url from source"

    try:

      # prepare a cursor object using cursor() method

      cursor = conn.cursor()

      # Execute the SQL command

      cursor.execute(sql)

      # Fetch all the rows in a list of lists.

      results = cursor.fetchall()

      for row in results:

        sourceIdMap[(row[1], row[2])] = row[0]

      cursor.close()

    except Exception as e:

      print "Error: unable to fetch data"

      print e

    closeConnection(conn)

def pushResultsToDb(results):

    conn = getDbConnection()

    try:

      # prepare a cursor object using cursor() method

      cursor = conn.cursor()

      # Clean db for the date

      if len(results) > 0: cursor.execute("delete from productactivity where date_id = %s", results[0][0]) 

      cursor.executemany (

          """

              insert into productactivity (

              date_id, catalog_item_id, first_source_id, session_source_id, 

              view, add_to_cart, add_to_research, delete_from_cart, delete_from_research, shipping_access, 

              payment_success, order_creation, payment_failure, proceed_to_pay, unique_view, unique_add_to_cart, 

              unique_add_to_research, unique_delete_from_cart, unique_delete_from_research, unique_shipping_access,

              unique_payment_success, unique_order_creation, unique_payment_failure, unique_proceed_to_pay) 

              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)

          """, results)

      conn.commit()

      results = []

      cursor.close()

    except Exception as e:

      print "Error: unable to insert row"

      print e

    closeConnection(conn)

def downloadProductActivity(date):

    url = urllib2.urlopen('http://saholic-datastore.appspot.com/jsp/export-product-activity-with-source.jsp?date=' + date.strftime("%Y%m%d"))

    return url.read()

def start(date):

    jsonString = downloadProductActivity(date)

    loadSourceIdMap()

    productActivities = json.loads(jsonString)

    results = []

    dateDelta = (date - datetime.datetime(2010,12,31)).days

    for activity in productActivities:

        isPaidFirstSource = 0 if not activity.has_key('isPaidFirstSource') else 1 if activity['isPaidFirstSource'] == 'true' else 0

        firstSource = str(activity['firstSource']) if activity.has_key('firstSource') else ""

        isPaidSessionSource = 0 if not activity.has_key('isPaidSessionSource') else 1 if activity['isPaidSessionSource'] == 'true' else 0

        sessionSource = str(activity['sessionSource']) if activity.has_key('sessionSource') else ""

        firstSourceId = getSourceId(isPaidFirstSource, firstSource)

        sessionSourceId = getSourceId(isPaidSessionSource, sessionSource)

        catalogId = int(activity['catalogId'])

        view = int(activity['view']) if activity.has_key('view') else 0

        addToCart = int(activity['addToCart']) if activity.has_key('addToCart') else 0

        addToResearch = int(activity['addToResearch']) if activity.has_key('addToResearch') else 0

        deleteFromCart = int(activity['deleteFromCart']) if activity.has_key('deleteFromCart') else 0

        deleteFromResearch = int(activity['deleteFromResearch']) if activity.has_key('deleteFromResearch') else 0

        shippingAccess = int(activity['shippingAccess']) if activity.has_key('shippingAccess') else 0

        paymentSuccess = int(activity['paymentSuccess']) if activity.has_key('paymentSuccess') else 0

        orderCreation = int(activity['orderCreation']) if activity.has_key('orderCreation') else 0

        paymentFailure = int(activity['paymentFailure']) if activity.has_key('paymentFailure') else 0

        proceedToPay = int(activity['proceedToPay']) if activity.has_key('proceedToPay') else 0

        uniqueView = int(activity['uniqueView']) if activity.has_key('uniqueView') else 0

        uniqueAddToCart = int(activity['uniqueAddToCart']) if activity.has_key('uniqueAddToCart') else 0

        uniqueAddToResearch = int(activity['uniqueAddToResearch']) if activity.has_key('uniqueAddToResearch') else 0

        uniqueDeleteFromCart = int(activity['uniqueDeleteFromCart']) if activity.has_key('uniqueDeleteFromCart') else 0

        uniqueDeleteFromResearch = int(activity['uniqueDeleteFromResearch']) if activity.has_key('uniqueDeleteFromResearch') else 0

        uniqueShippingAccess = int(activity['uniqueShippingAccess']) if activity.has_key('uniqueShippingAccess') else 0

        uniquePaymentSuccess = int(activity['uniquePaymentSuccess']) if activity.has_key('uniquePaymentSuccess') else 0

        uniqueOrderCreation = int(activity['uniqueOrderCreation']) if activity.has_key('uniqueOrderCreation') else 0

        uniquePaymentFailure = int(activity['uniquePaymentFailure']) if activity.has_key('uniquePaymentFailure') else 0

        uniqueProceedToPay = int(activity['uniqueProceedToPay']) if activity.has_key('uniqueProceedToPay') else 0

        results.append((dateDelta, catalogId, firstSourceId, sessionSourceId, view, addToCart, addToResearch, 

                        deleteFromCart, deleteFromResearch, shippingAccess, paymentSuccess, orderCreation, paymentFailure, proceedToPay, 

                        uniqueView, uniqueAddToCart, uniqueAddToResearch, uniqueDeleteFromCart, uniqueDeleteFromResearch, 

                        uniqueShippingAccess, uniquePaymentSuccess, uniqueOrderCreation, uniquePaymentFailure, uniqueProceedToPay))

    pushResultsToDb(results)

def main():

  if len(sys.argv) > 2:

    print "Usage : ProductActivityLoader.py [date-yyyyMMdd]"

    return

  if len(sys.argv) > 1:

    datestr = sys.argv[1]

    date = datetime.datetime.strptime(datestr, '%Y%m%d')

  else:

    date = datetime.datetime.today() - datetime.timedelta(1)

  start(date)

if __name__ == '__main__':

    main()