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_
from time import strftime
from dtr.utils import utils


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

con = None
dateWiseOrderMap = {}
weekWiseOrderMap = {}
monthWiseOrderMap = {}
dateWiseSaholicOrderMap = {}
weekWiseSaholicOrderMap = {}
monthWiseSaholicOrderMap = {}

orderIds = []
cutOff = 1425839400
#cutOff = 1425234600
oneDay = 86400
monthCutOff = 1425148200
weekCutOff = 1425839400
newWeekCutOff = 1425839400
newMonthCutOff = 1425148200
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()

    cursor = conn.cursor()
    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)
    worksheet.write(row, 8, 'DOWNVOL', boldStyle)
    worksheet.write(row, 9, 'DOWNVAL', 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 :
            sumdata=sumdata+data
            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
        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    
        
    #For Saholic
    row=0
    cursor = conn.cursor()
    cursor.execute(datesql)
    result = cursor.fetchall()
    for x in sorted(dateWiseSaholicOrderMap):
        row=0
        for r in result:
            for data in r :
                row += 1
                z= to_x_date(x)
                if str(data).strip() == str(z).strip():
                    column = 8
                    worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
                    column += 1
                    
    for x in sorted(dateWiseSaholicOrderMap):
        row=0
        for r in result:
            for data in r :
                row += 1
                z= to_x_date(x)
                if str(data).strip() == str(z).strip():
                    column = 9
                    worksheet.write(row,column,dateWiseSaholicOrderMap.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)
    worksheet.write(row, 6, 'MVOL', boldStyle)
    worksheet.write(row, 7, 'MTV', boldStyle)
    worksheet.write(row, 8, 'MOWNVOL', boldStyle)
    worksheet.write(row, 9, 'MOWNVAL', 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 :
            sumdata=sumdata+data
            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
        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
        
    row =0    
    for x in sorted(monthWiseSaholicOrderMap):
        row += 1
        column = 8
        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).count)
        column += 1
    row =0    
    for x in sorted(monthWiseSaholicOrderMap):
        row += 1
        column = 9
        worksheet.write(row,column,monthWiseSaholicOrderMap.get(x).value)
        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)
    worksheet.write(row, 8, 'WOWNVOL', boldStyle)
    worksheet.write(row, 9, 'WOWNVAL', 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 :
            sumdata=sumdata+data
            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
        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    
    
    row=0
    for x in sorted(weekWiseSaholicOrderMap):
        row += 1
        column = 8
        worksheet.write(row,column,weekWiseSaholicOrderMap.get(x).count)
        column+=1
    
    row=0
    for x in sorted(weekWiseSaholicOrderMap):
        row += 1
        column = 9
        worksheet.write(row,column,weekWiseSaholicOrderMap.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},'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 populateSaholicOrderMap():
    global dateWiseSaholicOrderMap
    allOrders = get_mongo_connection().Dtr.merchantOrder.find({'createdOnInt':{"$gte":cutOff},'storeId':{"$in":[4]}}).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 dateWiseSaholicOrderMap.has_key(millisec):
            orderObj = dateWiseSaholicOrderMap.get(millisec)
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
            orderObj.count += q
            orderObj.value += c
        else:
            orderObj = __Order(None, None)
            q, c = getSaholicSubOrderQuantity(orders.get('subOrders'))
            orderObj.count = q
            orderObj.value = c 
            dateWiseSaholicOrderMap[millisec] = orderObj
            
    
            
def getSaholicSubOrderQuantity(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 populateSaholicWeekWiseMap1():
    global newWeekCutOff
    while(True):
        if newWeekCutOff *1000 >= to_java_date(datetime.now()):
            break
        init = newWeekCutOff
        breakPoint = newWeekCutOff + (6 * oneDay)
        quantity, amount = 0 , 0
        while(True):
            orderObj = dateWiseSaholicOrderMap.get(newWeekCutOff * 1000)
            if orderObj is not None:
                quantity += orderObj.count
                amount += orderObj.value
            else:
                quantity +=0
                amount +=0
            newWeekCutOff = newWeekCutOff + oneDay
            if newWeekCutOff > breakPoint:
                weekWiseSaholicOrderMap[init * 1000] = __Order(quantity, amount)
                break 

def populateSaholicMonthWiseMap1():
    global newMonthCutOff
    while(True):
        
        print newMonthCutOff *1000
        a=str(to_py_date(newMonthCutOff*1000))
        print 'Now ' + str(to_java_date(datetime.now()))
        print "**********************"
        if newMonthCutOff *1000 >= to_java_date(datetime.now()):
            print "Breaking outer while"
            break
        init = newMonthCutOff
        breakPoint = newMonthCutOff + (monthMap.get(a[5:7]) * oneDay)
        print breakPoint
        quantity, amount = 0 , 0
        while(True):
            print to_py_date(newMonthCutOff*1000)
            print "weekCutOff ",newMonthCutOff
            print "breakPoint ",breakPoint
            orderObj = dateWiseSaholicOrderMap.get(newMonthCutOff * 1000)
            if orderObj is None:
                print "None for ", to_py_date(newMonthCutOff * 1000)
                quantity += 0
                amount += 0
            if orderObj is not None:
                quantity += orderObj.count
                amount += orderObj.value
            newMonthCutOff = newMonthCutOff + oneDay
            counter=0
            print counter+1
            if newMonthCutOff > breakPoint:
                monthWiseSaholicOrderMap[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()
    populateSaholicOrderMap()
    populateSaholicWeekWiseMap1()
    populateSaholicMonthWiseMap1()
    generateDailyReport()
    generateWeeklyReport()
    generateMonthlyReport()
    
def to_x_date(java_timestamp):
    try:
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
    except:
        return None
    return date.strftime('%Y-%m-%d')
    
if __name__ == '__main__':
    main()