Subversion Repositories SmartDukaan

Rev

Rev 17269 | Rev 17443 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

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, to_java_date, to_py_date
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 sqlalchemy.sql.expression import func, and_
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 = []

curDate = date.today()
cutOffMonth = (datetime.now().month + 11)%12
cutOffYear = curDate.year - (0 if curDate.month-1 > 0 else 1)
cutOff = to_java_date(datetime(cutOffYear, cutOffMonth, 1))/1000
cutOffDate=datetime(cutOffYear, cutOffMonth, 1)
#cutOff = 1425234600
oneDay = 86400
#monthCutOff = 1425148200
#weekCutOff = 1425839400
#newWeekCutOff = 1425839400
#newMonthCutOff = 1425148200
weekCutOff=cutOff
newWeekCutOff = cutOff
monthCutOff=cutOff
newMonthCutOff=cutOff
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) BETWEEN %s AND %s 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
AND date(d.visited) BETWEEN %s AND %s
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) >= %s 
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) BETWEEN %s AND %s
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) BETWEEN %s AND %s 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) BETWEEN %s AND %s 
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) BETWEEN %s AND %s 
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) 
#"""
MTRU_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) < %s;
"""

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) BETWEEN %s AND %s 
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) BETWEEN %s AND %s 
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) BETWEEN %s AND %s
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) BETWEEN %s AND %s
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) >=%s 
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) >= %s 
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) >= %s 
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) >=%s 
GROUP by week(s.created)
order by week(s.created);
"""

WVOL_QUERY="""
select sum(quantity), sum(value) from 
(SELECT WEEK(date(o.created_on)) as weekid, sum(quantity) as quantity,sum(amount_paid) as value 
FROM  allorder 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_on) >= %s 
GROUP BY WEEK(date(o.created_on)) UNION 
select WEEK(date(o.created)) as weekid,sum(quantity) as bquantity,sum(price) 
from flipkartorders 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) >= %s GROUP BY WEEK(date(o.created))) a group by weekid;"""
WOWN_QUERY="""
SELECT sum(quantity),sum(amount_paid)  FROM  allorder 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_on) >= %s and store_id='spice' GROUP BY WEEK(date(o.created_on)); 
"""
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 initialDate(cutOff):
    return cutOff.strftime('%Y-%m-%d')

def todaysDate():
    dateToday = utils.toTimeStamp(date.today())
    date1 = datetime.fromtimestamp(dateToday)
    return date1.strftime('%Y-%m-%d')
    
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,(cutOffDate,todaysDate()))
    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)
    dateMap={}
    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)
            dateMap[str(data)]=row
            column += 1
    
    cursor.execute(MTRU_QUERY,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        sumDa=r[0]
        
    row = 0
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
    result = cursor.fetchall()
    for r in result:
        row += 1
        column = 1
        for data in r :
            sumDa=sumDa+data
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
            column += 1

    row = 0
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
    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,(cutOffDate,todaysDate()))
    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,(cutOffDate,todaysDate()))
    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,(cutOffDate,todaysDate()))
    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):
        column = 6
        d = datetime.fromtimestamp(x/1000.0)
        row = dateMap[datetime.strftime(d,'%Y-%m-%d')]
        dq=DQ_QUERY%str(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):
        column = 7
        d = datetime.fromtimestamp(x/1000.0)
        row = dateMap[datetime.strftime(d, '%Y-%m-%d')]
        dq=DV_QUERY%str(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))
        
    #For Saholic
    cursor = conn.cursor()
    cursor.execute(datesql,(cutOffDate,todaysDate()))
    result = cursor.fetchall()
    for x in sorted(dateWiseSaholicOrderMap):
        z= to_x_date(x)
        row = dateMap.get(z)
        if row:
            column = 8
            worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).count)
                    
    for x in sorted(dateWiseSaholicOrderMap):
        z= to_x_date(x)
        row=dateMap.get(z)
        if row:
                column = 9
                worksheet.write(row,column,dateWiseSaholicOrderMap.get(x).value)
                                        
    
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,(cutOffDate,todaysDate()))
    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
    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

    cursor.execute(MTRU_QUERY,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        sumDa=r[0]
    
    row = 0
    cursor.execute(mnruSql,(cutOffDate,todaysDate()))
    result = cursor.fetchall()
    for r in result:
        row += 1
        column = 1
        for data in r :
            sumDa=sumDa+data
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
            column += 1

    row = 0
    cursor.execute(mnruSql,(cutOffDate,todaysDate()))
    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,(cutOffDate,todaysDate()))
    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,(cutOffDate,todaysDate()))
    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,(cutOffDate,todaysDate()))
    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
    wownSql=WOWN_QUERY
    wvolSql=WVOL_QUERY
    conn = getDbConnection()
    
    cursor = conn.cursor()
  
    cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))
    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
    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

    cursor.execute(MTRU_QUERY,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        sumDa=r[0]

    row = 0
    cursor.execute(wnruSql,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        row += 1
        column = 1
        for data in r :
            sumDa=sumDa+data
            worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)
            column += 1

    row = 0
    cursor.execute(wnruSql,(cutOffDate))
    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,(cutOffDate))
    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,(cutOffDate))
    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,(cutOffDate))
    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            
    cursor.execute(wvolSql,(cutOffDate,cutOffDate))
    result = cursor.fetchall()
    
    for r in result:
        row += 1
        column = 6
        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(wownSql,(cutOffDate))
    result = cursor.fetchall()
    
    for r in result:
        row += 1
        column = 8
        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', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']
    #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]}}, {"orderId":1,"createdOnInt":1,"subOrders":1}).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)).filter(and_(Orders.created>=cutOffDate)).all()
    order_ids = list(zip(*allOrders)[0])
    
def last_day(d, day_name):
    days_of_week = ['sunday','monday','tuesday','wednesday',
                        'thursday','friday','saturday']
    target_day = days_of_week.index(day_name.lower())
    delta_day = target_day - (d.isoweekday()%7)
    return d + timedelta(days=delta_day)
          
def main():
    populateYesterdayActiveUsers(24)
    populateValidOrders()
    populateOrderMap()
    #populateWeekWiseMap1()
    populateMonthWiseMap1()
    populateSaholicOrderMap()
    #populateSaholicWeekWiseMap1()
    populateSaholicMonthWiseMap1()
    generateDailyReport()
    generateWeeklyReport()
    generateMonthlyReport()
    sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", 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 len(sys.argv)==1:
        main()
    else:
        populateYesterdayActiveUsers(int(sys.argv[1]))