Subversion Repositories SmartDukaan

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 = "127.0.0.1"
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 + 10)%12
cutOffMonth = 12 if cutOffMonth==0 else cutOffMonth 
cutOffYear = curDate.year - (0 if curDate.month-2 > 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 year(d.visited),month(d.visited);
"""

WEEK_QUERY="""
SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week,WEEK(o.created) 
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 year(date(o.created)),WEEK(date(o.created))
"""

DNRU_QUERY="""
SELECT date(u.activation_time),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);
"""

DVOL_QUERY="""
select sum(quantity), sum(value) from  (SELECT date(o.created_on) as dateid, 
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 date(o.created_on) UNION  
select date(o.created) as dateid,sum(quantity) as bquantity,sum(price) 
from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)  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 date(o.created)) a group by dateid;
"""
DOWNO_QUERY="""
SELECT date(created_on),count(distinct merchant_order_id)  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 date(o.created_on)
order by date(o.created_on);
"""
DOWN_QUERY="""
SELECT date(created_on),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 date(o.created_on); 
"""

DACCSB_QUERY="""
select date(created_on),count(distinct user_id) from allorder 
where (category='Accessories' or category='Accs') and 
date(created_on)>=%s and store_id='spice' group by date(created_on) order by date(created_on);
"""

DACCSTO_QUERY="""
SELECT date(created_on),count(distinct merchant_order_id)  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' 
and (category='Accs' or category='Accessories') GROUP BY date(o.created_on) 
order by date(o.created_on);
"""

DACCSO_QUERY="""
select date(created_on),sum(quantity),sum(amount_paid) from allorder 
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
group by date(created_on) order by date(created_on);
"""

# 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 year(u.activation_time),month(u.activation_time)
order by year(u.activation_time),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 year(d.visited),month(d.visited)
order by year(d.visited),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 year(s.created),month(s.created)
order by year(s.created),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 year(s.created),month(s.created)
order by year(s.created),month(s.created);
"""

MVOL_QUERY="""
select sum(quantity), sum(value) from  (SELECT year(date(o.created_on)) as yearid,month(date(o.created_on)) as monthid, 
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 year(date(o.created_on)),month(date(o.created_on)) UNION  
select year(date(o.created)) as yearid,month(date(o.created)) as monthid,sum(quantity) as bquantity,sum(price) 
from flipkartorders o JOIN users u ON u.id = ifnull(o.user_id,139)  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 year(date(o.created)),month(date(o.created))) a group by yearid,monthid;
"""

MOWNO_QUERY="""
SELECT month(created_on),count(distinct merchant_order_id)  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 year(o.created_on),month(o.created_on)
order by year(o.created_on),month(o.created_on);
"""

MOWN_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 year(date(o.created_on)),month(date(o.created_on)); 
"""
MACCSB_QUERY="""
select month(created_on),count(distinct user_id) from allorder 
where (category='Accessories' or category='Accs') and 
date(created_on)>=%s and store_id='spice' group by 
year(created_on),month(created_on) order by year(created_on),month(created_on);
"""

MACCSTO_QUERY="""
SELECT month(created_on),count(distinct merchant_order_id)  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' 
and (category='Accs' or category='Accessories') GROUP BY year(o.created_on),month(o.created_on) 
order by year(o.created_on),month(o.created_on);
"""

MACCSO_QUERY="""
select month(created_on),sum(quantity),sum(amount_paid) from allorder 
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
group by year(created_on),month(created_on) order by year(created_on),month(created_on);
"""

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 year(date(u.activation_time)),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 year(date(d.visited)),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 year(date(s.created)),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 year(date(s.created)),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 = ifnull(o.user_id,139) 
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;"""

WOWNO_QUERY="""
SELECT week(created_on),count(distinct merchant_order_id)  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(o.created_on)
order by week(o.created_on);
"""

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)); 
"""

WACCSB_QUERY="""
select WEEK(created_on) AS week, count(distinct user_id) from allorder
where (category='Accessories' or category='Accs') 
and  date(created_on)>=%s and store_id='spice'
group by week(created_on) 
order by WEEK(created_on);
"""

WACCSTO_QUERY="""
SELECT week(created_on),count(distinct merchant_order_id)  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' 
and (category='Accs' or category='Accessories') GROUP BY week(o.created_on) 
order by week(o.created_on);
"""

WACCSO_QUERY="""
select WEEK(created_on),sum(quantity),sum(amount_paid) from allorder 
where (category='Accessories' or category='Accs') and date(created_on)>=%s and store_id='spice'
group by WEEK(created_on)
order by WEEK(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 = '192.168.158.89'
DTR_DATA_HOST = '192.168.158.89'


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
    dvolSql=DVOL_QUERY
    downvolSql=DOWN_QUERY
    daccsbSql=DACCSB_QUERY
    daccsoSql = DACCSO_QUERY
    downoSql=DOWNO_QUERY
    daccstoSql=DACCSTO_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, 'DOWNORD', boldStyle)
    worksheet.write(row, 9, 'DOWNVOL', boldStyle)
    worksheet.write(row, 10, 'DOWNVAL', boldStyle)
    worksheet.write(row, 11, 'DACCSU', boldStyle)
    worksheet.write(row, 12, 'DACCSCART', boldStyle)
    worksheet.write(row, 13, 'DACCSB', boldStyle)
    worksheet.write(row, 14, 'DACCSORD', boldStyle)
    worksheet.write(row, 15, 'DACCSOVOL', boldStyle)
    worksheet.write(row, 16, 'DACCSOVAL', 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=dateMap.get(str(r[0]))
        sumDa=sumDa+r[1]
        column = 1
        worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)

    row = 0
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
    result = cursor.fetchall()
    for r in result:
        row=dateMap.get(str(r[0]))
        data=r[1]
        column = 2
        worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)
    
    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            
    cursor.execute(dvolSql,(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(downoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 8
        row=dateMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            
    row = 0            
    cursor.execute(downvolSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 9
        row=dateMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            column += 1
            worksheet.write(row, column, r[2])
            
    row = 1
    column=11
    breakDate = to_java_date(datetime.now())
    currentDay = cutOff*1000
    while True:
        currentDay = currentDay + 86400000
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-86400000}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
        worksheet.write(row, column, len(result))
        row=row+1
        if currentDay>breakDate:
            break
        
    row = 1
    column=12
    breakDate = to_java_date(datetime.now())
    currentDay = cutOff*1000
    while True:
        currentDay = currentDay + 86400000
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-86400000}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/cartdetails"}}]}).distinct('user_id')
        worksheet.write(row, column, len(result))
        row=row+1
        if currentDay>breakDate:
            break    
        
    row = 0            
    cursor.execute(daccsbSql,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        column = 13
        row=dateMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
    
    row = 0            
    cursor.execute(daccstoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 14
        row=dateMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            
    row = 0            
    cursor.execute(daccsoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 15
        row=dateMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            column += 1
            worksheet.write(row, column, r[2])                                    
    
def generateMonthlyReport():
    monthSql = MONTH_QUERY
    mnruSql = MNRU_QUERY
    mauSql = MAU_QUERY
    mabSql = MAB_QUERY
    mtoSql = MTO_QUERY
    mvolSql=MVOL_QUERY
    mownvolSql=MOWN_QUERY
    maccsbSql= MACCSB_QUERY
    maccsoSql= MACCSO_QUERY
    mownoSql= MOWNO_QUERY
    maccstoSql= MACCSTO_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, 'MOWNORD', boldStyle)
    worksheet.write(row, 9, 'MOWNVOL', boldStyle)
    worksheet.write(row, 10, 'MOWNVAL', boldStyle)
    worksheet.write(row, 11, 'MACCSU', boldStyle)
    worksheet.write(row, 12, 'MACCSCART', boldStyle)
    worksheet.write(row, 13, 'MACCSB', boldStyle)
    worksheet.write(row, 14, 'MACCSORD', boldStyle)
    worksheet.write(row, 15, 'MACCSOVOL', boldStyle)
    worksheet.write(row, 16, 'MACCSOVAL', boldStyle)

    monthMapDb = {}
    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)
            monthMapDb[str(data)]=row
            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
    cursor.execute(mvolSql,(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(mownoSql,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        column = 8
        row=monthMapDb.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
        
    row = 0            
    cursor.execute(mownvolSql,(cutOffDate))
    result = cursor.fetchall()
    
    for r in result:
        row += 1
        column = 9
        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=1
    column=11
    breakDate = to_java_date(datetime.now())
    currentDay = cutOff*1000
    while True:
        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay- (monthMap.get(month_get(currentDay))*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
        currentDay = currentDay+ 86400000
        worksheet.write(row,column,len(result))
        row=row+1
        if currentDay>breakDate:
            break
    
    row=1
    column=12
    breakDate = to_java_date(datetime.now())
    currentDay = cutOff*1000
    while True:
        currentDay = currentDay + (monthMap.get(month_get(currentDay))*86400000)
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay- (monthMap.get(month_get(currentDay))*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/cartdetails"}}]}).distinct('user_id')
        currentDay = currentDay+ 86400000
        worksheet.write(row,column,len(result))
        row=row+1
        if currentDay>breakDate:
            break
    
    row = 0            
    cursor.execute(maccsbSql,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        column = 13
        row=monthMapDb.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])

    row = 0            
    cursor.execute(maccstoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 14
        row=monthMapDb.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
    
    row = 0            
    cursor.execute(maccsoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 15
        row=monthMapDb.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            column += 1
            worksheet.write(row, column, r[2])                                    

    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
    waccsbSql = WACCSB_QUERY
    waccsoSql = WACCSO_QUERY
    wownoSql=WOWNO_QUERY
    waccstoSql=WACCSTO_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, 'WOWNORD', boldStyle)
    worksheet.write(row, 9, 'WOWNVOL', boldStyle)
    worksheet.write(row, 10, 'WOWNVAL', boldStyle)
    worksheet.write(row, 11, 'WACCSU', boldStyle)
    worksheet.write(row, 12, 'WACCSCART', boldStyle)
    worksheet.write(row, 13, 'WACCSB', boldStyle)
    worksheet.write(row, 14, 'WACCSORD', boldStyle)
    worksheet.write(row, 15, 'WACCSVOL', boldStyle)
    worksheet.write(row, 16, 'WACCSVAL', boldStyle)
    
    weekMap={}
    
    for r in result:
        row += 1
        column = 0
        worksheet.write(row, column, int(r[0]) if type(r[0]) is float else r[0], date_format if type(r[0]) is date else default_format)
        weekMap[str(r[1])]=row
        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(wownoSql,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        column = 8
        row=weekMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            
    row = 0            
    cursor.execute(wownSql,(cutOffDate))
    result = cursor.fetchall()
    
    for r in result:
        row += 1
        column = 9
        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=1
    column=11       
    breakDate = to_java_date(datetime.now())
    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
    while True:
        currentDay = currentDay + (7*86400000)
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-(6*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/category/6"}}]}).distinct('user_id')
        worksheet.write(row,column,len(result))
        row=row+1
        if currentDay>breakDate:
            break
    
    
    row=1
    column=12       
    breakDate = to_java_date(datetime.now())
    currentDay = to_java_date(last_day(cutOffDate, 'sunday'))
    while True:
        currentDay = currentDay + (7*86400000)
        result = get_mongo_connection_new().User.browsinghistories.find({"$and":[{'created':{"$gte":currentDay-(6*86400000)}},{'created':{"$lte":currentDay}},{"url":{"$regex" : "http://api.profittill.com/cartdetails"}}]}).distinct('user_id')
        worksheet.write(row,column,len(result))
        row=row+1
        if currentDay>breakDate:
            break
            
    row = 0            
    cursor.execute(waccsbSql,(cutOffDate))
    result = cursor.fetchall()
    for r in result:
        column = 13
        row=weekMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])

    row = 0            
    cursor.execute(waccstoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 14
        row=weekMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            
    row = 0            
    cursor.execute(waccsoSql,(cutOffDate))
    result = cursor.fetchall()

    for r in result:
        column = 15
        row=weekMap.get(str(r[0]))
        if row:
            worksheet.write(row, column, r[1])
            column += 1
            worksheet.write(row, column, r[2])
            
        
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 = ['rajender.singh@saholic.com','rajneesh.arora@saholic.com', 'khushal.bhatia@saholic.com', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']
    #MAILTO = ['rajender.singh@saholic.com']
    mailServer.login(SENDER, PASSWORD)
    mailServer.sendmail(SENDER, 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 get_mongo_connection_new(host='192.168.158.89', 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 main():
    populateYesterdayActiveUsers(24)
    #populateValidOrders()
    #populateOrderMap()
    #populateWeekWiseMap1()
    #populateMonthWiseMap1()
    #populateSaholicOrderMap()
    #populateSaholicWeekWiseMap1()
    #populateSaholicMonthWiseMap1()
    generateDailyReport()
    generateWeeklyReport()
    generateMonthlyReport()
    sendmail(["rajender.singh@shop2020.in","rajneesh.arora@saholic.com", "khushal.bhatia@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)
    #sendmail(["rajender.singh@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')
    
def month_get(java_timestamp):
    try:
        date = datetime.fromtimestamp(java_timestamp / 1e3)       
    except:
        return None
    return date.strftime('%m')
    
if __name__ == '__main__':
    if len(sys.argv)==1:
        main()
    else:
        populateYesterdayActiveUsers(int(sys.argv[1]))