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()