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



DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
TMP_FILE = "User_Activity_Report.xls"  

# 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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
AND o.STATUS =  'ORDER_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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
AND o.STATUS =  'ORDER_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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR lower(u.referrer) not like 'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
AND o.STATUS =  'ORDER_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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
AND o.STATUS =  'ORDER_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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
AND o.STATUS =  'ORDER_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) IS NULL AND u.id > 350 AND u.id NOT IN (387,399,418)) OR LOWER(u.referrer) NOT LIKE  'emp%'
AND o.STATUS =  'ORDER_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
    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)
   
    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


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"], "", 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)
   
   
    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


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)

    email.append('manas.kapoor@shop2020.in')
    MAILTO = email 
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def main():
    #date = raw_input('Enter a date name: ')
    generateDailyReport()
    generateWeeklyReport()
    generateMonthlyReport()

if __name__ == '__main__':
    main()