WebSVN – SmartDukaan – Diff – /trunk/PyDTR/src/scripts/ActivityReport.py



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"  

orderIds = []
 
curDate = date.today()
cutOffMonth = (datetime.now().month + 10)%12
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,

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

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

# 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="""

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

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

            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
 
    row = 0
    cursor.execute(dnruSql,(cutOffDate,todaysDate()))
    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,(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 :

                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:

  
    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)

        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

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

        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,(last_day(cutOffDate, 'sunday')))
    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,(last_day(cutOffDate, 'sunday')))
    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,(last_day(cutOffDate, 'sunday')))
    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,(last_day(cutOffDate, 'sunday')))
    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,(last_day(cutOffDate, 'sunday')))
    result = cursor.fetchall()
    
    for r in result:
        row += 1
        column = 5

    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:
    

                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)       

Rev 17209	Rev 17217
Line 26...	Line 26...
26	`import smtplib`	26	`import smtplib`
27	`import sys`	27	`import sys`
28	`import time`	28	`import time`
29	`import xlwt`	29	`import xlwt`
30		30
31		-
32	`DB_HOST = "localhost"`	31	`DB_HOST = "localhost"`
33	`DB_USER = "root"`	32	`DB_USER = "root"`
34	`DB_PASSWORD = "shop2020"`	33	`DB_PASSWORD = "shop2020"`
35	`DB_NAME = "dtr"`	34	`DB_NAME = "dtr"`
36	`TMP_FILE = "User_Activity_Report.xls"`	35	`TMP_FILE = "User_Activity_Report.xls"`
Line 46...	Line 45...
46	`orderIds = []`	45	`orderIds = []`
47		46
48	`curDate = date.today()`	47	`curDate = date.today()`
49	`cutOffMonth = (datetime.now().month + 10)%12`	48	`cutOffMonth = (datetime.now().month + 10)%12`
50	`cutOffYear = curDate.year - (0 if curDate.month-2 > 0 else 1)`	49	`cutOffYear = curDate.year - (0 if curDate.month-2 > 0 else 1)`
-		50	`cutOff = to_java_date(datetime(cutOffYear, cutOffMonth, 1))/1000`
51	`cutOff = datetime(cutOffYear, cutOffMonth, 1)`	51	`cutOffDate=datetime(cutOffYear, cutOffMonth, 1)`
52	`#cutOff = 1425234600`	52	`#cutOff = 1425234600`
53	`oneDay = 86400`	53	`oneDay = 86400`
54	`monthCutOff = 1425148200`	54	`#monthCutOff = 1425148200`
55	`weekCutOff = 1425839400`	55	`#weekCutOff = 1425839400`
56	`newWeekCutOff = 1425839400`	56	`#newWeekCutOff = 1425839400`
57	`newMonthCutOff = 1425148200`	57	`#newMonthCutOff = 1425148200`
-		58	`weekCutOff=cutOff`
-		59	`newWeekCutOff = cutOff`
-		60	`monthCutOff=cutOff`
-		61	`newMonthCutOff=cutOff`
58	`DataService.initialize()`	62	`DataService.initialize()`
59		63
60	`monthMap = { '01':30,`	64	`monthMap = { '01':30,`
61	`'02':28,`	65	`'02':28,`
62	`'03':30,`	66	`'03':30,`
Line 79...	Line 83...
79	`SMTP_PORT = 587`	83	`SMTP_PORT = 587`
80		84
81	`DATE_QUERY="""`	85	`DATE_QUERY="""`
82	`SELECT date(d.visited) from daily_visitors d`	86	`SELECT date(d.visited) from daily_visitors d`
83	`join users u where u.id=d.user_id AND`	87	`join users u where u.id=d.user_id AND`
84	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	88	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
85	`AND date(d.visited) > '2015-03-08' group by visited ;`	89	`AND date(d.visited) BETWEEN %s AND %s group by visited ;`
86	`"""`	90	`"""`
87		91
88	`MONTH_QUERY="""`	92	`MONTH_QUERY="""`
89	`SELECT month(d.visited) from daily_visitors d`	93	`SELECT month(d.visited) from daily_visitors d`
90	`join users u on u.id=d.user_id where`	94	`join users u on u.id=d.user_id where`
91	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	95	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
-		96	`AND date(d.visited) BETWEEN %s AND %s`
92	`group by month(d.visited);`	97	`group by month(d.visited);`
93	`"""`	98	`"""`
94		99
95	`WEEK_QUERY="""`	100	`WEEK_QUERY="""`
96	`SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week`	101	`SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week`
97	`FROM order_view o`	102	`FROM order_view o`
98	`JOIN users u ON u.id = o.user_id WHERE`	103	`JOIN users u ON u.id = o.user_id WHERE`
99	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	104	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
100	`AND date(o.created) > '2015-03-08'`	105	`AND date(o.created) >= %s`
101	`GROUP BY WEEK(date(o.created))`	106	`GROUP BY WEEK(date(o.created))`
102	`ORDER BY WEEK(date(o.created))`	107	`ORDER BY WEEK(date(o.created))`
103	`"""`	108	`"""`
104		109
105	`DNRU_QUERY="""`	110	`DNRU_QUERY="""`
106	`SELECT count(*)`	111	`SELECT count(*)`
107	`FROM users u WHERE`	112	`FROM users u WHERE`
108	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	113	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
109	`AND date(u.activation_time) > '2015-03-08'`	114	`AND date(u.activation_time) BETWEEN %s AND %s`
110	`group by date(u.activation_time)`	115	`group by date(u.activation_time)`
111	`order by date(u.activation_time)`	116	`order by date(u.activation_time)`
112	`"""`	117	`"""`
113		118
114	`DAU_QUERY="""`	119	`DAU_QUERY="""`
115	`SELECT count(distinct d.user_id)`	120	`SELECT count(distinct d.user_id)`
116	`FROM daily_visitors d join users u WHERE u.id=d.user_id`	121	`FROM daily_visitors d join users u WHERE u.id=d.user_id`
117	`AND (LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	122	`AND (LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
118	`AND date(d.visited) > '2015-03-08' group by visited ;`	123	`AND date(d.visited) BETWEEN %s AND %s group by visited ;`
119	`"""`	124	`"""`
120		125
121	`# DAB_QUERY="""`	126	`# DAB_QUERY="""`
122	`# SELECT COUNT(DISTINCT o.user_id )`	127	`# SELECT COUNT(DISTINCT o.user_id )`
123	`# FROM order_view o`	128	`# FROM order_view o`
Line 131...	Line 136...
131	`DAB_QUERY="""`	136	`DAB_QUERY="""`
132	`select count(DISTINCT s.user_id) from`	137	`select count(DISTINCT s.user_id) from`
133	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	138	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
134	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`	139	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`
135	`JOIN users u ON u.id = s.user_id WHERE`	140	`JOIN users u ON u.id = s.user_id WHERE`
136	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1`	141	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1`
137	`AND date(s.created) > '2015-03-08'`	142	`AND date(s.created) BETWEEN %s AND %s`
138	`GROUP by date(s.created)`	143	`GROUP by date(s.created)`
139	`ORDER by date(s.created);`	144	`ORDER by date(s.created);`
140	`"""`	145	`"""`
141		146
142	`DTO_QUERY="""`	147	`DTO_QUERY="""`
143	`select count(*) from`	148	`select count(*) from`
144	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	149	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
145	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`	150	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`
146	`JOIN users u ON u.id = s.user_id WHERE`	151	`JOIN users u ON u.id = s.user_id WHERE`
147	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated = 1`	152	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated = 1`
148	`AND date(s.created) > '2015-03-08'`	153	`AND date(s.created) BETWEEN %s AND %s`
149	`GROUP by date(s.created)`	154	`GROUP by date(s.created)`
150	`ORDER by date(s.created);`	155	`ORDER by date(s.created);`
151	`"""`	156	`"""`
152	`# DTO_QUERY="""`	157	`# DTO_QUERY="""`
153	`# SELECT date(o.created),COUNT(*)`	158	`# SELECT date(o.created),COUNT(*)`
Line 157...	Line 162...
157	`# AND (o.STATUS = 'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')`	162	`# AND (o.STATUS = 'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')`
158	`# AND date(o.created) > '2015-03-08'`	163	`# AND date(o.created) > '2015-03-08'`
159	`# GROUP BY DATE(o.created)`	164	`# GROUP BY DATE(o.created)`
160	`# order by date(o.created)`	165	`# order by date(o.created)`
161	`#"""`	166	`#"""`
-		167	`MTRU_QUERY="""`
-		168	`SELECT count(*) FROM users u WHERE (LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL)`
-		169	`AND u.activated =1`
-		170	`AND date(u.activation_time) < %s;`
-		171	`"""`
162		172
163	`MNRU_QUERY="""`	173	`MNRU_QUERY="""`
164	`SELECT count(*) FROM users u WHERE`	174	`SELECT count(*) FROM users u WHERE`
165	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	175	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
166	`AND date(u.activation_time) > '2015-03-08'`	176	`AND date(u.activation_time) BETWEEN %s AND %s`
167	`group by month(u.activation_time)`	177	`group by month(u.activation_time)`
168	`order by month(u.activation_time)`	178	`order by month(u.activation_time)`
169	`"""`	179	`"""`
170		180
171	`MAU_QUERY="""`	181	`MAU_QUERY="""`
172	`SELECT count(distinct d.user_id)`	182	`SELECT count(distinct d.user_id)`
173	`FROM daily_visitors d join users u where u.id=d.user_id AND`	183	`FROM daily_visitors d join users u where u.id=d.user_id AND`
174	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	184	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
175	`AND date(d.visited) > '2015-03-08'`	185	`AND date(d.visited) BETWEEN %s AND %s`
176	`group by month(d.visited)`	186	`group by month(d.visited)`
177	`order by month(d.visited);`	187	`order by month(d.visited);`
178	`"""`	188	`"""`
179		189
180	`# MAB_QUERY="""`	190	`# MAB_QUERY="""`
Line 201...	Line 211...
201		211
202	`MAB_QUERY="""`	212	`MAB_QUERY="""`
203	`select count(DISTINCT s.user_id) from`	213	`select count(DISTINCT s.user_id) from`
204	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	214	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
205	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`	215	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`
206	`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`	216	`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`
207	`AND date(s.created) > '2015-03-08'`	217	`AND date(s.created) BETWEEN %s AND %s`
208	`GROUP by month(s.created)`	218	`GROUP by month(s.created)`
209	`order by month(s.created);`	219	`order by month(s.created);`
210	`"""`	220	`"""`
211	`MTO_QUERY="""`	221	`MTO_QUERY="""`
212	`select count(*) from`	222	`select count(*) from`
213	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	223	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
214	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`	224	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`
215	`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`	225	`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`
216	`AND date(s.created) > '2015-03-08'`	226	`AND date(s.created) BETWEEN %s AND %s`
217	`GROUP by month(s.created)`	227	`GROUP by month(s.created)`
218	`order by month(s.created);`	228	`order by month(s.created);`
219	`"""`	229	`"""`
220		230
221	`WNRU_QUERY="""`	231	`WNRU_QUERY="""`
222	`SELECT COUNT(*)`	232	`SELECT COUNT(*)`
223	`FROM users u WHERE`	233	`FROM users u WHERE`
224	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	234	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
225	`AND date(u.activation_time) > '2015-03-08'`	235	`AND date(u.activation_time) >=%s`
226	`GROUP BY WEEK(date(u.activation_time))`	236	`GROUP BY WEEK(date(u.activation_time))`
227	`ORDER BY WEEK(date(u.activation_time))`	237	`ORDER BY WEEK(date(u.activation_time))`
228		238
229	`"""`	239	`"""`
230	`WAU_QUERY="""`	240	`WAU_QUERY="""`
231	`SELECT COUNT(distinct d.user_id) AS total`	241	`SELECT COUNT(distinct d.user_id) AS total`
232	`FROM daily_visitors d join users u where u.id=d.user_id AND`	242	`FROM daily_visitors d join users u where u.id=d.user_id AND`
233	`(LOWER(u.referrer) NOT LIKE 'emp%' OR u.utm_campaign is not NULL) AND u.activated =1`	243	`(LOWER(u.referrer) NOT LIKE 'emp%%' OR u.utm_campaign is not NULL) AND u.activated =1`
234	`AND date(d.visited) > '2015-03-08'`	244	`AND date(d.visited) >= %s`
235	`GROUP BY WEEK(d.visited)`	245	`GROUP BY WEEK(d.visited)`
236	`ORDER BY WEEK(d.visited)`	246	`ORDER BY WEEK(d.visited)`
237	`"""`	247	`"""`
238		248
239		249
Line 261...	Line 271...
261		271
262	`WAB_QUERY="""`	272	`WAB_QUERY="""`
263	`select count(distinct user_id) from`	273	`select count(distinct user_id) from`
264	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	274	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
265	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`	275	`union SELECT created, id,user_id FROM flipkartorders where date(created)>'2015-03-22') s`
266	`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`	276	`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`
267	`AND date(s.created) > '2015-03-08'`	277	`AND date(s.created) >= %s`
268	`GROUP by week(s.created)`	278	`GROUP by week(s.created)`
269	`order by week(s.created);`	279	`order by week(s.created);`
270	`"""`	280	`"""`
271		281
272	`WTO_QUERY="""`	282	`WTO_QUERY="""`
273	`select count(*) from`	283	`select count(*) from`
274	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`	284	`(SELECT created, id,user_id FROM order_view where STATUS = 'ORDER_CREATED' OR STATUS ='DETAIL_CREATED'`
275	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`	285	`union SELECT created, id,ifnull(user_id,139) FROM flipkartorders where date(created)>'2015-03-22') s`
276	`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`	286	`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`
277	`AND date(s.created) > '2015-03-08'`	287	`AND date(s.created) >=%s`
278	`GROUP by week(s.created)`	288	`GROUP by week(s.created)`
279	`order by week(s.created);`	289	`order by week(s.created);`
280	`"""`	290	`"""`
281		291
282	`DQ_QUERY="""`	292	`DQ_QUERY="""`
Line 304...	Line 314...
304		314
305		315
306	`def getDbConnection():`	316	`def getDbConnection():`
307	`return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)`	317	`return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)`
308		318
-		319	`def initialDate(cutOff):`
-		320	`return cutOff.strftime('%Y-%m-%d')`
309		321
-		322	`def todaysDate():`
-		323	`dateToday = utils.toTimeStamp(date.today())`
-		324	`date1 = datetime.fromtimestamp(dateToday)`
-		325	`return date1.strftime('%Y-%m-%d')`
-		326
310	`def generateDailyReport():`	327	`def generateDailyReport():`
311	`datesql= DATE_QUERY`	328	`datesql= DATE_QUERY`
312	`dnruSql = DNRU_QUERY`	329	`dnruSql = DNRU_QUERY`
313	`dauSql = DAU_QUERY`	330	`dauSql = DAU_QUERY`
314	`dabSql = DAB_QUERY`	331	`dabSql = DAB_QUERY`
315	`dtoSql = DTO_QUERY`	332	`dtoSql = DTO_QUERY`
316	`conn = getDbConnection()`	333	`conn = getDbConnection()`
317		334
318	`cursor = conn.cursor()`	335	`cursor = conn.cursor()`
319	`cursor.execute(datesql)`	336	`cursor.execute(datesql,(cutOffDate,todaysDate()))`
320	`result = cursor.fetchall()`	337	`result = cursor.fetchall()`
321	`global workbook`	338	`global workbook`
322	`workbook = xlwt.Workbook()`	339	`workbook = xlwt.Workbook()`
323	`worksheet = workbook.add_sheet("User")`	340	`worksheet = workbook.add_sheet("User")`
324	`boldStyle = xlwt.XFStyle()`	341	`boldStyle = xlwt.XFStyle()`
Line 347...	Line 364...
347	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	364	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
348	`dateMap[str(data)]=row`	365	`dateMap[str(data)]=row`
349	`column += 1`	366	`column += 1`
350		367
351	`row = 0`	368	`row = 0`
352	`cursor.execute(dnruSql)`	369	`cursor.execute(dnruSql,(cutOffDate,todaysDate()))`
353	`result = cursor.fetchall()`	370	`result = cursor.fetchall()`
354	`for r in result:`	371	`for r in result:`
355	`row += 1`	372	`row += 1`
356	`column = 1`	373	`column = 1`
357	`for data in r :`	374	`for data in r :`
358	`sumdata=sumdata+data`	375	`sumdata=sumdata+data`
359	`worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)`	376	`worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)`
360	`column += 1`	377	`column += 1`
361		378
362	`row = 0`	379	`row = 0`
363	`cursor.execute(dnruSql)`	380	`cursor.execute(dnruSql,(cutOffDate,todaysDate()))`
364	`result = cursor.fetchall()`	381	`result = cursor.fetchall()`
365	`for r in result:`	382	`for r in result:`
366	`row += 1`	383	`row += 1`
367	`column = 2`	384	`column = 2`
368	`for data in r :`	385	`for data in r :`
369	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	386	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
370	`column += 1`	387	`column += 1`
371		388
372	`row = 0`	389	`row = 0`
373	`cursor.execute(dauSql)`	390	`cursor.execute(dauSql,(cutOffDate,todaysDate()))`
374	`result = cursor.fetchall()`	391	`result = cursor.fetchall()`
375		392
376	`for r in result:`	393	`for r in result:`
377	`row += 1`	394	`row += 1`
378	`column = 3`	395	`column = 3`
379	`for data in r :`	396	`for data in r :`
380	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	397	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
381	`column += 1`	398	`column += 1`
382		399
383	`row = 0`	400	`row = 0`
384	`cursor.execute(dabSql)`	401	`cursor.execute(dabSql,(cutOffDate,todaysDate()))`
385	`result = cursor.fetchall()`	402	`result = cursor.fetchall()`
386		403
387	`for r in result:`	404	`for r in result:`
388	`row += 1`	405	`row += 1`
389	`column = 4`	406	`column = 4`
390	`for data in r :`	407	`for data in r :`
391	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	408	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
392	`column += 1`	409	`column += 1`
393		410
394	`row = 0`	411	`row = 0`
395	`cursor.execute(dtoSql)`	412	`cursor.execute(dtoSql,(cutOffDate,todaysDate()))`
396	`result = cursor.fetchall()`	413	`result = cursor.fetchall()`
397	`for r in result:`	414	`for r in result:`
398	`row += 1`	415	`row += 1`
399	`column = 5`	416	`column = 5`
400	`for data in r :`	417	`for data in r :`
Line 432...	Line 449...
432	`quantity=0`	449	`quantity=0`
433	`worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))`	450	`worksheet.write(row,column,(dateWiseOrderMap.get(x).value)+float(quantity))`
434		451
435	`#For Saholic`	452	`#For Saholic`
436	`cursor = conn.cursor()`	453	`cursor = conn.cursor()`
437	`cursor.execute(datesql)`	454	`cursor.execute(datesql,(cutOffDate,todaysDate()))`
438	`result = cursor.fetchall()`	455	`result = cursor.fetchall()`
439	`for x in sorted(dateWiseSaholicOrderMap):`	456	`for x in sorted(dateWiseSaholicOrderMap):`
440	`z= to_x_date(x)`	457	`z= to_x_date(x)`
441	`row = dateMap.get(z)`	458	`row = dateMap.get(z)`
442	`if row:`	459	`if row:`
Line 460...	Line 477...
460		477
461	`conn = getDbConnection()`	478	`conn = getDbConnection()`
462		479
463	`cursor = conn.cursor()`	480	`cursor = conn.cursor()`
464		481
465	`cursor.execute(monthSql)`	482	`cursor.execute(monthSql,(cutOffDate,todaysDate()))`
466	`result = cursor.fetchall()`	483	`result = cursor.fetchall()`
467	`rb = open_workbook(TMP_FILE)`	484	`rb = open_workbook(TMP_FILE)`
468	`wb = copy(rb)`	485	`wb = copy(rb)`
469	`worksheet = workbook.add_sheet("Monthly")`	486	`worksheet = workbook.add_sheet("Monthly")`
470	`boldStyle = xlwt.XFStyle()`	487	`boldStyle = xlwt.XFStyle()`
471	`f = xlwt.Font()`	488	`f = xlwt.Font()`
472	`f.bold = True`	489	`f.bold = True`
473	`boldStyle.font = f`	490	`boldStyle.font = f`
474	`column = 0`	491	`column = 0`
475	`row = 0`	492	`row = 0`
476	`sumdata=17`	-
477	`worksheet.write(row, 0, 'Month', boldStyle)`	493	`worksheet.write(row, 0, 'Month', boldStyle)`
478	`worksheet.write(row, 1, 'MTRU', boldStyle)`	494	`worksheet.write(row, 1, 'MTRU', boldStyle)`
479	`worksheet.write(row, 2, 'MNRU', boldStyle)`	495	`worksheet.write(row, 2, 'MNRU', boldStyle)`
480	`worksheet.write(row, 3, 'MAU', boldStyle)`	496	`worksheet.write(row, 3, 'MAU', boldStyle)`
481	`worksheet.write(row, 4, 'MAB', boldStyle)`	497	`worksheet.write(row, 4, 'MAB', boldStyle)`
Line 490...	Line 506...
490	`column = 0`	506	`column = 0`
491	`for data in r :`	507	`for data in r :`
492	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	508	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
493	`column += 1`	509	`column += 1`
494		510
-		511	`cursor.execute(MTRU_QUERY,(cutOffDate))`
-		512	`result = cursor.fetchall()`
-		513	`for r in result:`
-		514	`sumDa=r[0]`
-		515
495	`row = 0`	516	`row = 0`
496	`cursor.execute(mnruSql)`	517	`cursor.execute(mnruSql,(cutOffDate,todaysDate()))`
497	`result = cursor.fetchall()`	518	`result = cursor.fetchall()`
498	`for r in result:`	519	`for r in result:`
499	`row += 1`	520	`row += 1`
500	`column = 1`	521	`column = 1`
501	`for data in r :`	522	`for data in r :`
502	`sumdata=sumdata+data`	523	`sumDa=sumDa+data`
503	`worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)`	524	`worksheet.write(row, column, int(sumDa) if type(sumDa) is float else sumDa, date_format if type(sumDa) is date else default_format)`
504	`column += 1`	525	`column += 1`
505		526
506	`row = 0`	527	`row = 0`
507	`cursor.execute(mnruSql)`	528	`cursor.execute(mnruSql,(cutOffDate,todaysDate()))`
508	`result = cursor.fetchall()`	529	`result = cursor.fetchall()`
509	`for r in result:`	530	`for r in result:`
510	`row += 1`	531	`row += 1`
511	`column = 2`	532	`column = 2`
512	`for data in r :`	533	`for data in r :`
513	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	534	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
514	`column += 1`	535	`column += 1`
515		536
516	`row = 0`	537	`row = 0`
517	`cursor.execute(mauSql)`	538	`cursor.execute(mauSql,(cutOffDate,todaysDate()))`
518	`result = cursor.fetchall()`	539	`result = cursor.fetchall()`
519		540
520	`for r in result:`	541	`for r in result:`
521	`row += 1`	542	`row += 1`
522	`column = 3`	543	`column = 3`
523	`for data in r :`	544	`for data in r :`
524	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	545	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
525	`column += 1`	546	`column += 1`
526		547
527	`row = 0`	548	`row = 0`
528	`cursor.execute(mabSql)`	549	`cursor.execute(mabSql,(cutOffDate,todaysDate()))`
529	`result = cursor.fetchall()`	550	`result = cursor.fetchall()`
530		551
531	`for r in result:`	552	`for r in result:`
532	`row += 1`	553	`row += 1`
533	`column = 4`	554	`column = 4`
534	`for data in r :`	555	`for data in r :`
535	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	556	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
536	`column += 1`	557	`column += 1`
537		558
538	`row = 0`	559	`row = 0`
539	`cursor.execute(mtoSql)`	560	`cursor.execute(mtoSql,(cutOffDate,todaysDate()))`
540	`result = cursor.fetchall()`	561	`result = cursor.fetchall()`
541		562
542	`for r in result:`	563	`for r in result:`
543	`row += 1`	564	`row += 1`
544	`column = 5`	565	`column = 5`
Line 606...	Line 627...
606		627
607	`conn = getDbConnection()`	628	`conn = getDbConnection()`
608		629
609	`cursor = conn.cursor()`	630	`cursor = conn.cursor()`
610		631
611	`cursor.execute(weekSql)`	632	`cursor.execute(weekSql,(last_day(cutOffDate, 'sunday')))`
612	`result = cursor.fetchall()`	633	`result = cursor.fetchall()`
613	`rb = open_workbook(TMP_FILE)`	634	`rb = open_workbook(TMP_FILE)`
614	`wb = copy(rb)`	635	`wb = copy(rb)`
615	`worksheet = workbook.add_sheet("Weekly")`	636	`worksheet = workbook.add_sheet("Weekly")`
616	`boldStyle = xlwt.XFStyle()`	637	`boldStyle = xlwt.XFStyle()`
Line 637...	Line 658...
637	`for data in r :`	658	`for data in r :`
638	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	659	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
639	`column += 1`	660	`column += 1`
640		661
641	`row = 0`	662	`row = 0`
642	`cursor.execute(wnruSql)`	663	`cursor.execute(wnruSql,(last_day(cutOffDate, 'sunday')))`
643	`result = cursor.fetchall()`	664	`result = cursor.fetchall()`
644	`for r in result:`	665	`for r in result:`
645	`row += 1`	666	`row += 1`
646	`column = 1`	667	`column = 1`
647	`for data in r :`	668	`for data in r :`
648	`sumdata=sumdata+data`	669	`sumdata=sumdata+data`
649	`worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)`	670	`worksheet.write(row, column, int(sumdata) if type(sumdata) is float else sumdata, date_format if type(sumdata) is date else default_format)`
650	`column += 1`	671	`column += 1`
651		672
652	`row = 0`	673	`row = 0`
653	`cursor.execute(wnruSql)`	674	`cursor.execute(wnruSql,(last_day(cutOffDate, 'sunday')))`
654	`result = cursor.fetchall()`	675	`result = cursor.fetchall()`
655	`for r in result:`	676	`for r in result:`
656	`row += 1`	677	`row += 1`
657	`column = 2`	678	`column = 2`
658	`for data in r :`	679	`for data in r :`
659	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	680	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
660	`column += 1`	681	`column += 1`
661		682
662	`row = 0`	683	`row = 0`
663	`cursor.execute(wauSql)`	684	`cursor.execute(wauSql,(last_day(cutOffDate, 'sunday')))`
664	`result = cursor.fetchall()`	685	`result = cursor.fetchall()`
665		686
666	`for r in result:`	687	`for r in result:`
667	`row += 1`	688	`row += 1`
668	`column = 3`	689	`column = 3`
669	`for data in r :`	690	`for data in r :`
670	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	691	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
671	`column += 1`	692	`column += 1`
672		693
673	`row = 0`	694	`row = 0`
674	`cursor.execute(wabSql)`	695	`cursor.execute(wabSql,(last_day(cutOffDate, 'sunday')))`
675	`result = cursor.fetchall()`	696	`result = cursor.fetchall()`
676		697
677	`for r in result:`	698	`for r in result:`
678	`row += 1`	699	`row += 1`
679	`column = 4`	700	`column = 4`
680	`for data in r :`	701	`for data in r :`
681	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`	702	`worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is date else default_format)`
682	`column += 1`	703	`column += 1`
683		704
684	`row = 0`	705	`row = 0`
685	`cursor.execute(wtoSql)`	706	`cursor.execute(wtoSql,(last_day(cutOffDate, 'sunday')))`
686	`result = cursor.fetchall()`	707	`result = cursor.fetchall()`
687		708
688	`for r in result:`	709	`for r in result:`
689	`row += 1`	710	`row += 1`
690	`column = 5`	711	`column = 5`
Line 758...	Line 779...
758	`fileMsg.set_payload(file(TMP_FILE).read())`	779	`fileMsg.set_payload(file(TMP_FILE).read())`
759	`encoders.encode_base64(fileMsg)`	780	`encoders.encode_base64(fileMsg)`
760	`fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)`	781	`fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)`
761	`msg.attach(fileMsg)`	782	`msg.attach(fileMsg)`
762		783
763	`MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com']`	784	`MAILTO = ['manas.kapoor@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com','chaitnaya.vats@shop2020.in']`
764	`#MAILTO = ['amit.gupta@saholic.com']`	785	`#MAILTO = ['manas.kapoor@saholic.com']`
765	`mailServer.login(SENDER, PASSWORD)`	786	`mailServer.login(SENDER, PASSWORD)`
766	`mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())`	787	`mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())`
767		788
768	`class __Order:`	789	`class __Order:`
769		790
Line 1008...	Line 1029...
1008	`break`	1029	`break`
1009		1030
1010		1031
1011	`def populateValidOrders():`	1032	`def populateValidOrders():`
1012	`global order_ids`	1033	`global order_ids`
1013	`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>=cutOff)).all()`	1034	`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()`
1014	`order_ids = list(zip(*allOrders)[0])`	1035	`order_ids = list(zip(*allOrders)[0])`
-		1036
-		1037	`def last_day(d, day_name):`
-		1038	`days_of_week = ['sunday','monday','tuesday','wednesday',`
-		1039	`'thursday','friday','saturday']`
-		1040	`target_day = days_of_week.index(day_name.lower())`
-		1041	`delta_day = target_day - (d.isoweekday()%7)`
-		1042	`return d + timedelta(days=delta_day)`
1015		1043
1016	`def main():`	1044	`def main():`
1017	`#populateYesterdayActiveUsers(24)`	1045	`populateYesterdayActiveUsers(24)`
1018	`populateValidOrders()`	1046	`populateValidOrders()`
1019	`populateOrderMap()`	1047	`populateOrderMap()`
1020	`populateWeekWiseMap1()`	1048	`populateWeekWiseMap1()`
1021	`populateMonthWiseMap1()`	1049	`populateMonthWiseMap1()`
1022	`populateSaholicOrderMap()`	1050	`populateSaholicOrderMap()`
1023	`populateSaholicWeekWiseMap1()`	1051	`populateSaholicWeekWiseMap1()`
1024	`populateSaholicMonthWiseMap1()`	1052	`populateSaholicMonthWiseMap1()`
1025	`generateDailyReport()`	1053	`generateDailyReport()`
1026	`generateWeeklyReport()`	1054	`generateWeeklyReport()`
1027	`generateMonthlyReport()`	1055	`generateMonthlyReport()`
1028	`sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in"], "", TMP_FILE, SUBJECT)`	1056	`sendmail(["manas.kapoor@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in","chaitnaya.vats@shop2020.in"], "", TMP_FILE, SUBJECT)`
1029	`#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`	1057	`#sendmail(["manas.kapoor@shop2020.in"], "", TMP_FILE, SUBJECT)`
1030		1058
1031	`def to_x_date(java_timestamp):`	1059	`def to_x_date(java_timestamp):`
1032	`try:`	1060	`try:`
1033	`date = datetime.fromtimestamp(java_timestamp / 1e3)`	1061	`date = datetime.fromtimestamp(java_timestamp / 1e3)`

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 17209 → 17217