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

monthMap = {  '01':30,

            '02':28,

            '03':30,

            '04':29,

            '05':30,

            '06':29,

            '07':30,

            '08':30,

            '09':29,

            '10':30,

            '11':29,

            '12':30

        }

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

# """

DAB_QUERY="""

select count(DISTINCT s.user_id) from 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 

union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 

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

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

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

GROUP by date(s.created)

ORDER by date(s.created);

"""

DTO_QUERY="""

select count(*) from 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 

union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 

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

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

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

GROUP by date(s.created)

ORDER by date(s.created);

"""

# DTO_QUERY="""

# SELECT date(o.created),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); 

# """

MAB_QUERY="""

select count(DISTINCT s.user_id) from 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 

union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 

JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1 

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

GROUP by month(s.created)

order by month(s.created);

"""

MTO_QUERY="""

select count(*) from 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 

union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 

JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1 

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

GROUP by month(s.created)

order by month(s.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))

# """

WAB_QUERY="""

select count(distinct user_id) from 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 

union  SELECT created, id,user_id FROM   flipkartorders where date(created)>'2015-03-22') s 

JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1 

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

GROUP by week(s.created)

order by week(s.created);

"""

WTO_QUERY="""

select count(*) from 

(SELECT created, id,user_id FROM  order_view where STATUS =  'ORDER_CREATED' OR STATUS ='DETAIL_CREATED' 

union  SELECT created, id,ifnull(user_id,139) FROM   flipkartorders where date(created)>'2015-03-22') s 

JOIN users u ON u.id = s.user_id WHERE   (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1 

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

GROUP by week(s.created)

order by week(s.created);

"""

DQ_QUERY="""

select sum(quantity) from flipkartorders where date(created)='%s';

"""

DV_QUERY="""

select sum(quantity*price) from flipkartorders where date(created)='%s';

"""

WQ_QUERY="""

select sum(quantity) from flipkartorders where date(created) between '%s' and '%s';

"""

WV_QUERY="""

select sum(quantity*price) from flipkartorders where date(created) between '%s' and '%s';

"""

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, 'DVOL', 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

        d = str(datetime.fromtimestamp(x/1000.0))

        dq=DQ_QUERY%d[:10]

        cursor.execute(dq)

        result = cursor.fetchall()

        for r in result:

            quantity= r[0]   

            if quantity is None:

                quantity=0

        print 'Total Sub order quantity', dateWiseOrderMap.get(x).count,' Flipkart Order', quantity

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

        column += 1

    row =0

    for x in sorted(dateWiseOrderMap):

        row += 1

        column = 7

        d = str(datetime.fromtimestamp(x/1000.0))

        dq=DV_QUERY%d[:10]

        cursor.execute(dq)

        result = cursor.fetchall()

        for r in result:

            quantity= r[0]   

            if quantity is None:

                quantity=0

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

        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)

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

    worksheet.write(row, 7, 'MTV', 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

    row=0

    z=0    

    for x in sorted(monthWiseOrderMap):

        d = str(datetime.fromtimestamp(x/1000.0))

        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000

        dnext = str(datetime.fromtimestamp(nextday/1000.0))

        row += 1

        column = 6

        dq=WQ_QUERY % (d[:10],dnext[:10])

        cursor.execute(dq)

        result = cursor.fetchall()

        for r in result:

            quantity= r[0]

            if quantity is None:

                quantity=0

        print 'Total Sub order quantity Monthly', dateWiseOrderMap.get(x).count,' Flipkart Order', quantity        

        worksheet.write(row,column,(monthWiseOrderMap.get(x).count)+quantity)

        column += 1

    row =0

    for x in sorted(monthWiseOrderMap):

        row += 1

        column = 7

        d = str(datetime.fromtimestamp(x/1000.0))

        nextday = (x/1000 + (monthMap.get(d[5:7]) * oneDay))*1000

        dnext = str(datetime.fromtimestamp(nextday/1000.0))

        dq=WV_QUERY % (d[:10],dnext[:10])

        cursor.execute(dq)

        result = cursor.fetchall()

        for r in result:

            quantity= r[0]

            if quantity is None:

                quantity=0

        worksheet.write(row,column,(monthWiseOrderMap.get(x).value)+float(quantity))

        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, 'WVOL', 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

        d = str(datetime.fromtimestamp(x/1000.0))

        nextday=oneDay*7*1000+x

        dnext = str(datetime.fromtimestamp(nextday/1000.0))

        dq=WQ_QUERY % (d[:10],dnext[:10])

        cursor.execute(dq)

        result = cursor.fetchall()

        for r in result:

            quantity= r[0]

            if quantity is None:

                quantity=0

        print 'Total Sub order quantity Weekly', dateWiseOrderMap.get(x).count,' Flipkart Order', quantity        

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

        column+=1

    row = 0

    for x in sorted(weekWiseOrderMap):

        row += 1

        column = 7

        d = str(datetime.fromtimestamp(x/1000.0))

        nextday=oneDay*7*1000+x

        dnext = str(datetime.fromtimestamp(nextday/1000.0))

        dq=WV_QUERY % (d[:10],dnext[:10])

        cursor.execute(dq)

        result = cursor.fetchall()

        for r in result:

            quantity= r[0]

            if quantity is None:

                quantity=0

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

        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},'storeId':{"$in":[1,3,4,5,6]}}).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):

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

            break

        init = weekCutOff

        breakPoint = weekCutOff + (6 * oneDay)

        quantity, amount = 0 , 0

        while(True):

            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)

                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 monthCutOff

    while(True):

        print monthCutOff *1000

        a=str(to_py_date(monthCutOff*1000))

        print 'Now ' + str(to_java_date(datetime.now()))

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

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

            print "Breaking outer while"

            break

        init = monthCutOff

        breakPoint = monthCutOff + (monthMap.get(a[5:7]) * oneDay)

        print breakPoint

        quantity, amount = 0 , 0

        while(True):

            print to_py_date(monthCutOff*1000)

            print "weekCutOff ",monthCutOff

            print "breakPoint ",breakPoint

            orderObj = dateWiseOrderMap.get(monthCutOff * 1000)

            if orderObj is None:

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

            if orderObj is not None:

                quantity += orderObj.count

                amount += orderObj.value

            monthCutOff = monthCutOff + oneDay

            counter=0

            print counter+1

            if monthCutOff > breakPoint:

                monthWiseOrderMap[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(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).all()

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

def main():

    populateValidOrders()

    populateOrderMap()

    populateWeekWiseMap1()

    populateMonthWiseMap1()

    generateDailyReport()

    generateWeeklyReport()

    generateMonthlyReport()

if __name__ == '__main__':

    main()