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) NOT LIKE  'emp%' 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%' 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%' 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%' 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%' 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%' AND u.activated =1

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) NOT LIKE  'emp%' AND u.activated =1

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) NOT LIKE  'emp%' 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%' 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%' AND u.activated =1

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) NOT LIKE  'emp%' AND u.activated =1

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) NOT LIKE  'emp%' 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%' 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%' AND u.activated =1

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) NOT LIKE  'emp%' AND u.activated =1

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","rajneesh.arora@saholic.com"], "", 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)

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

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