Subversion Repositories SmartDukaan

'''
Created on Mar 10, 2015

'''
from datetime import date, datetime, timedelta, datetime
from dtr.storage import DataService, Mongo
from dtr.storage.DataService import Orders, Users, CallHistory
from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag
from dtr.utils import utils
from dtr.utils.utils import toTimeStamp
from elixir import *
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from operator import or_
from pymongo.mongo_client import MongoClient
from shop2020.utils.Utils import to_py_date, to_java_date
from sqlalchemy.sql.expression import func
from time import strftime
from xlrd import open_workbook
from xlutils.copy import copy
from xlwt.Workbook import Workbook
import MySQLdb
import pymongo
import smtplib
import sys
import time
import xlwt


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.activation_time) > '2015-03-08'
group by date(u.activation_time) 
order by date(u.activation_time)
"""

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.activation_time) > '2015-03-08' 
group by month(u.activation_time)
order by month(u.activation_time) 
"""

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.activation_time) > '2015-03-08' 
GROUP BY WEEK(date(u.activation_time))
ORDER BY WEEK(date(u.activation_time))

"""
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()
#DTR_DATA_HOST = '45.33.50.227'
DTR_DATA_HOST = '192.168.161.154'


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)
    
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 populateYesterdayActiveUsers(hours=4):
    con = Mongo.get_mongo_connection(DTR_DATA_HOST)
    db = con.User
    curtime = datetime.now()
    fourhrsprev = curtime - timedelta(hours=hours)
    pipeline = [{"$match":{"created":{"$lt":toTimeStamp(curtime)*1000, "$gt":toTimeStamp(fourhrsprev)*1000}}},
                {"$group":{"_id":{"user_id":"$user_id","created":{"$subtract":["$created",{"$mod":["$created",86400000]}]}}}}]
    alluserdates=db.browsinghistories.aggregate(pipeline)['result']
    insertq = "insert ignore into daily_visitors values (%s, %s)"
    conn = getDbConnection()
    try:
        for a in alluserdates:
            cursor = conn.cursor()
            # Execute the SQL command
            # Fetch source id.
            cursor.execute(insertq,(a['_id']['user_id'], utils.fromTimeStamp(int(a['_id']['created']/1000))))
            conn.commit()
    finally:
        conn.close()

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():
    populateYesterdayActiveUsers(24)
    populateValidOrders()
    populateOrderMap()
    populateWeekWiseMap1()
    populateMonthWiseMap1()
    populateSaholicOrderMap()
    populateSaholicWeekWiseMap1()
    populateSaholicMonthWiseMap1()
    generateDailyReport()
    generateWeeklyReport()
    generateMonthlyReport()
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com"], "", TMP_FILE, SUBJECT)
    #sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)
    
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__':
    if not sys.argv[0]:
        main()
    else:
        populateYesterdayActiveUsers(int(sys.argv[0]))