Subversion Repositories SmartDukaan

'''

Created on Mar 10, 2015

'''

from datetime import date, datetime, timedelta

from dtr.storage.Mysql import getOrdersAfterDate, \

    getOrdersByTag

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from pymongo.mongo_client import MongoClient

from xlrd import open_workbook

from xlutils.copy import copy

from xlwt.Workbook import Workbook

import MySQLdb

import smtplib

import time

import xlwt

import pymongo

from shop2020.utils.Utils import to_py_date, to_java_date

from datetime import datetime

from elixir import *

from dtr.storage import DataService

from dtr.storage.DataService import Orders, Users, CallHistory

from sqlalchemy.sql.expression import func

from operator import or_

DB_HOST = "localhost"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "dtr"

TMP_FILE = "User_Activity_Report.xls"  

con = None

dateWiseOrderMap = {}

weekWiseOrderMap = {}

monthWiseOrderMap = {}

orderIds = []

cutOff = 1425839400

#cutOff = 1425234600

oneDay = 86400

monthCutOff = 1425148200

weekCutOff = 1425839400

#weekCutOff = 1425234600

DataService.initialize()

# KEY NAMES

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "User Activity Report for" + date.today().isoformat()

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

DATE_QUERY="""

SELECT date(d.visited) from daily_visitors d 

join users u where u.id=d.user_id AND 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(d.visited) > '2015-03-08' group by visited ;

"""

MONTH_QUERY="""

SELECT month(d.visited) from daily_visitors d 

join users u on u.id=d.user_id where 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

group by month(d.visited);

"""

WEEK_QUERY="""

SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week 

FROM  order_view o

JOIN users u ON u.id = o.user_id WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(o.created) > '2015-03-08' 

GROUP BY WEEK(date(o.created))

ORDER BY WEEK(date(o.created))

"""

DNRU_QUERY="""

SELECT count(*) 

FROM users u WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(u.created) > '2015-03-08'

group by date(u.created) 

order by date(u.created)

"""

DAU_QUERY="""

SELECT count(distinct d.user_id) 

FROM daily_visitors d join users u WHERE u.id=d.user_id 

AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(d.visited) > '2015-03-08' group by visited ;

"""

DAB_QUERY="""

SELECT COUNT(DISTINCT o.user_id ) 

FROM  order_view o

JOIN users u ON u.id = o.user_id WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')

AND date(o.created) > '2015-03-08' 

group by date(o.created)

order by date(o.created)

"""

DTO_QUERY="""

SELECT COUNT( *) 

FROM  order_view o

JOIN users u ON u.id = o.user_id WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')

AND date(o.created) > '2015-03-08' 

GROUP BY DATE(o.created)

order by date(o.created) 

"""

MNRU_QUERY="""

SELECT count(*) FROM users u WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(u.created) > '2015-03-08' 

group by month(u.created)

order by month(u.created) 

"""

MAU_QUERY="""

SELECT count(distinct d.user_id) 

FROM daily_visitors d join users u where u.id=d.user_id AND 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(d.visited) > '2015-03-08' 

group by month(d.visited)

order by month(d.visited); 

"""

MAB_QUERY="""

SELECT COUNT(DISTINCT o.user_id ) 

FROM  order_view o

JOIN users u ON u.id = o.user_id where

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')

AND date(o.created) > '2015-03-08' 

group by month(o.created)

order by month(o.created); 

"""

MTO_QUERY="""

SELECT COUNT( *) 

FROM  order_view o

JOIN users u ON u.id = o.user_id where

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')

AND date(o.created) > '2015-03-08' 

GROUP BY MONTH(o.created)

order by month(o.created); 

"""

WNRU_QUERY="""

SELECT COUNT(*)

FROM users u WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(u.created) > '2015-03-08' 

GROUP BY WEEK(date(u.created))

ORDER BY WEEK(date(u.created))

"""

WAU_QUERY="""

SELECT  COUNT(distinct d.user_id) AS total

FROM  daily_visitors d join users u where u.id=d.user_id AND 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND date(d.visited) > '2015-03-08' 

GROUP BY WEEK(d.visited)

ORDER BY WEEK(d.visited)

"""

WAB_QUERY="""

SELECT COUNT( DISTINCT o.user_id ) 

FROM  order_view o

JOIN users u ON u.id = o.user_id WHERE

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')

AND date(o.created) > '2015-03-08'  

GROUP BY WEEK(date(o.created))

ORDER BY WEEK(date(o.created))

"""

WTO_QUERY="""

SELECT COUNT(*)

FROM  order_view o

JOIN users u ON u.id = o.user_id WHERE 

(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1

AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')

AND date(o.created) > '2015-03-08' 

GROUP BY WEEK(date(o.created))

ORDER BY WEEK(date(o.created))

"""

date_format = xlwt.XFStyle()

date_format.num_format_str = 'yyyy/mm/dd'

datetime_format = xlwt.XFStyle()

datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'

default_format = xlwt.XFStyle()

def getDbConnection():

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

def generateDailyReport():

    datesql= DATE_QUERY

    dnruSql = DNRU_QUERY

    dauSql = DAU_QUERY

    dabSql = DAB_QUERY

    dtoSql = DTO_QUERY

    conn = getDbConnection()

    # prepare a cursor object using cursor() method

    cursor = conn.cursor()

    # Execute the SQL command

    # Fetch source id.

    cursor.execute(datesql)

    result = cursor.fetchall()

    global workbook

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("User")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    sumdata=17

    global z

    worksheet.write(row, 0, 'Date', boldStyle)

    worksheet.write(row, 1, 'TRU', boldStyle)

    worksheet.write(row, 2, 'NRU', boldStyle)

    worksheet.write(row, 3, 'DAU', boldStyle)

    worksheet.write(row, 4, 'DAB', boldStyle)

    worksheet.write(row, 5, 'DTO', boldStyle)

    worksheet.write(row, 6, 'DSO', boldStyle)   

    worksheet.write(row, 7, 'DTV', boldStyle)

    for r in result:

        row += 1

        column = 0

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0

    cursor.execute(dnruSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 1

        for data in r :

            print "Data" + str(data)

            sumdata=sumdata+data

            print "Sum Data" + str(sumdata)

            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)

            column += 1

    row = 0

    cursor.execute(dnruSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 2

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1        

    row = 0

    cursor.execute(dauSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 3

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0

    cursor.execute(dabSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 4

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0            

    cursor.execute(dtoSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 5

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row=0

    z=0    

    for x in sorted(dateWiseOrderMap):

        row += 1

        column = 6

        worksheet.write(row,column,dateWiseOrderMap.get(x).count)

        column += 1

    row =0

    for x in sorted(dateWiseOrderMap):

        row += 1

        column = 7

        worksheet.write(row,column,dateWiseOrderMap.get(x).value)

        column += 1    

def generateMonthlyReport():

    monthSql = MONTH_QUERY

    mnruSql = MNRU_QUERY

    mauSql = MAU_QUERY

    mabSql = MAB_QUERY

    mtoSql = MTO_QUERY

    conn = getDbConnection()

    cursor = conn.cursor()

    cursor.execute(monthSql)

    result = cursor.fetchall()

    rb = open_workbook(TMP_FILE)

    wb = copy(rb)

    worksheet = workbook.add_sheet("Monthly")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    sumdata=17

    worksheet.write(row, 0, 'Month', boldStyle)

    worksheet.write(row, 1, 'MTRU', boldStyle)

    worksheet.write(row, 2, 'MNRU', boldStyle)

    worksheet.write(row, 3, 'MAU', boldStyle)

    worksheet.write(row, 4, 'MAB', boldStyle)

    worksheet.write(row, 5, 'MTO', boldStyle)

    for r in result:

        row += 1

        column = 0

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0

    cursor.execute(mnruSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 1

        for data in r :

            print "Data" + str(data)

            sumdata=sumdata+data

            print "Sum Data" + str(sumdata)

            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)

            column += 1

    row = 0

    cursor.execute(mnruSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 2

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1        

    row = 0

    cursor.execute(mauSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 3

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0

    cursor.execute(mabSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 4

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0            

    cursor.execute(mtoSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 5

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    workbook.save(TMP_FILE)

    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)

    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)

def generateWeeklyReport():

    weekSql = WEEK_QUERY

    wnruSql = WNRU_QUERY

    wauSql = WAU_QUERY

    wabSql = WAB_QUERY

    wtoSql = WTO_QUERY

    conn = getDbConnection()

    cursor = conn.cursor()

    cursor.execute(weekSql)

    result = cursor.fetchall()

    rb = open_workbook(TMP_FILE)

    wb = copy(rb)

    worksheet = workbook.add_sheet("Weekly")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    sumdata=17

    worksheet.write(row, 0, 'Week', boldStyle)

    worksheet.write(row, 1, 'WTRU', boldStyle)

    worksheet.write(row, 2, 'WNRU', boldStyle)

    worksheet.write(row, 3, 'WAU', boldStyle)

    worksheet.write(row, 4, 'WAB', boldStyle)

    worksheet.write(row, 5, 'WTO', boldStyle)

    worksheet.write(row, 6, 'WTS', boldStyle)

    worksheet.write(row, 7, 'WTV', boldStyle)

    for r in result:

        row += 1

        column = 0

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0

    cursor.execute(wnruSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 1

        for data in r :

            print "Data" + str(data)

            sumdata=sumdata+data

            print "Sum Data" + str(sumdata)

            worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)

            column += 1

    row = 0

    cursor.execute(wnruSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 2

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1        

    row = 0

    cursor.execute(wauSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 3

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0

    cursor.execute(wabSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 4

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0            

    cursor.execute(wtoSql)

    result = cursor.fetchall()

    for r in result:

        row += 1

        column = 5

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)

            column += 1

    row = 0        

    for x in sorted(weekWiseOrderMap):

        row += 1

        column = 6

        worksheet.write(row,column,weekWiseOrderMap.get(x).count)

        column+=1

    row = 0

    for x in sorted(weekWiseOrderMap):

        row += 1

        column = 7

        worksheet.write(row,column,weekWiseOrderMap.get(x).value)

        column+=1    

def sendmail(email, message, fileName, title):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    fileMsg = MIMEBase('application', 'vnd.ms-excel')

    fileMsg.set_payload(file(TMP_FILE).read())

    encoders.encode_base64(fileMsg)

    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)

    msg.attach(fileMsg)

    MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com']

    #MAILTO = ['manas.kapoor@saholic.com']

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

class __Order:

    def __init__(self, count, value):

        self.count = count

        self.value = value

def get_mongo_connection(host='localhost', port=27017):

    global con

    if con is None:

        print "Establishing connection %s host and port %d" %(host,port)

        try:

            con = pymongo.MongoClient(host, port)

        except Exception, e:

            print e

            return None

    return con

def populateOrderMap():

    global dateWiseOrderMap

    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff}}).sort([('createdOnInt',pymongo.ASCENDING)])

    for orders in allOrders:

        if orders.get('orderId') not in order_ids:

            continue

        cdate = ((to_py_date(orders.get('createdOnInt') * 1000)))

        millisec = to_java_date(datetime(cdate.year, cdate.month, cdate.day))

        if dateWiseOrderMap.has_key(millisec):

            orderObj = dateWiseOrderMap.get(millisec)

            q, c = getSubOrderQuantity(orders.get('subOrders'))

            orderObj.count += q

            orderObj.value += c

        else:

            orderObj = __Order(None, None)

            q, c = getSubOrderQuantity(orders.get('subOrders'))

            orderObj.count = q

            orderObj.value = c 

            dateWiseOrderMap[millisec] = orderObj

def getSubOrderQuantity(subOrders):

    q = 0

    c = 0

    if subOrders is None:

        return q, c

    for subOrder in subOrders:

        q = q + int(subOrder.get('quantity'))

        try:

            c = c + float(subOrder.get('amountPaid').encode('utf-8'))

        except AttributeError:

            c = c + float(subOrder.get('amountPaid'))

    return q, c

def populateWeekWiseMap():

    global weekCutOff

    while(True):

        quantity, amount = 0 , 0

        for i in xrange(weekCutOff , (weekCutOff * 7)+1):

            orderObj = dateWiseOrderMap.get(i*1000)

            quantity += orderObj.count

            amount += orderObj.value

            i = i + oneDay

        weekWiseOrderMap[weekCutOff * 1000] = __Order(quantity, amount)

        weekCutOff = weekCutOff * 1000 * 7 + oneDay

        if weekCutOff >= to_py_date(datetime.now()):

            break

def populateWeekWiseMap1():

    global weekCutOff

    while(True):

        #print weekCutOff *1000

        #print to_java_date(datetime.now())

        #print "**********************"

        if weekCutOff *1000 >= to_java_date(datetime.now()):

            #print "Breaking outer while"

            break

        init = weekCutOff

        breakPoint = weekCutOff + (6 * oneDay)

        quantity, amount = 0 , 0

        while(True):

            #print to_py_date(weekCutOff*1000)

            #print "weekCutOff ",weekCutOff

            #print "breakPoint ",breakPoint

            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)

            if orderObj is not None:

                quantity += orderObj.count

                amount += orderObj.value

            weekCutOff = weekCutOff + oneDay

            if weekCutOff > breakPoint:

                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)

                #print "Breaking inner while"

                break 

def populateMonthWiseMap():

    global monthCutOff

    while(True):

        quantity, amount = 0 , 0

        for i in xrange(monthCutOff , (monthCutOff * 30)+1):

            orderObj = dateWiseOrderMap.get(i*1000)

            quantity += orderObj.count

            amount += orderObj.value

            i = i + oneDay

        weekWiseOrderMap[monthCutOff * 1000] = __Order(quantity, amount)

        monthCutOff = monthCutOff * 1000 * 30 + oneDay

        if monthCutOff >= to_py_date(datetime.now()):

            break

def populateMonthWiseMap1():

    global weekCutOff

    while(True):

        print weekCutOff *1000

        print to_java_date(datetime.now())

        print "**********************"

        if weekCutOff *1000 >= to_java_date(datetime.now()):

            print "Breaking outer while"

            break

        init = weekCutOff

        breakPoint = weekCutOff + (6 * oneDay)

        quantity, amount = 0 , 0

        while(True):

            print to_py_date(weekCutOff*1000)

            print "weekCutOff ",weekCutOff

            print "breakPoint ",breakPoint

            orderObj = dateWiseOrderMap.get(weekCutOff * 1000)

            if orderObj is None:

                print "None for ", to_py_date(weekCutOff * 1000)

            if orderObj is not None:

                quantity += orderObj.count

                amount += orderObj.value

            weekCutOff = weekCutOff + oneDay

            if weekCutOff > breakPoint:

                weekWiseOrderMap[init * 1000] = __Order(quantity, amount)

                print "Breaking inner while"

                break 

def populateValidOrders():

    global order_ids

    #allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(~(func.lower(Users.referrer)).like('emp%')).all()

    allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).all()

    order_ids = list(zip(*allOrders)[0])

def main():

    #date = raw_input('Enter a date name: ')

    populateValidOrders()

    populateOrderMap()

    populateWeekWiseMap1()

    generateDailyReport()

    generateWeeklyReport()

    generateMonthlyReport()

if __name__ == '__main__':

    main()