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

 from datetime import datetime
 from elixir import *
 from dtr.storage import DataService
 from dtr.storage.DataService import Orders, Users, CallHistory
 from sqlalchemy.sql.expression import func
+from operator import or_
 DB_HOST = "localhost"
 DB_USER = "root"
 DB_PASSWORD = "shop2020"
 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
-join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(d.visited) > '2015-03-08' group by visited ;
 """
 MONTH_QUERY="""
 SELECT month(d.visited) from daily_visitors d
+join users u on u.id=d.user_id where
-join users u on u.id=d.user_id where LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 group by month(d.visited);
 """
 WEEK_QUERY="""
 SELECT CONCAT(date(o.created), ' - ', date(o.created) + INTERVAL 6 DAY) AS week
 FROM  order_view o
-JOIN users u ON u.id = o.user_id
+JOIN users u ON u.id = o.user_id WHERE
-WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(o.created) > '2015-03-08'
 GROUP BY WEEK(date(o.created))
 ORDER BY WEEK(date(o.created))
 """
 DNRU_QUERY="""
 SELECT count(*)
+FROM users u WHERE
-FROM users u WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(u.created) > '2015-03-08'
 group by date(u.created)
 order by date(u.created)
 """
 DAU_QUERY="""
 SELECT count(distinct d.user_id)
-FROM daily_visitors d join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+FROM daily_visitors d join users u WHERE u.id=d.user_id
+AND (LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(d.visited) > '2015-03-08' group by visited ;
 """
 DAB_QUERY="""
 SELECT COUNT(DISTINCT o.user_id )
 FROM  order_view o
-JOIN users u ON u.id = o.user_id
+JOIN users u ON u.id = o.user_id WHERE
-WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-AND o.STATUS =  'ORDER_CREATED'
+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)
 """
 DTO_QUERY="""
 SELECT COUNT( *)
 FROM  order_view o
-JOIN users u ON u.id = o.user_id
+JOIN users u ON u.id = o.user_id WHERE
-WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-AND o.STATUS =  'ORDER_CREATED'
+AND (o.STATUS =  'ORDER_CREATED' OR o.STATUS ='DETAIL_CREATED')
 AND date(o.created) > '2015-03-08'
 GROUP BY DATE(o.created)
 order by date(o.created)
 """
 MNRU_QUERY="""
-SELECT count(*)
+SELECT count(*) FROM users u WHERE
-FROM users u WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(u.created) > '2015-03-08'
 group by month(u.created)
 order by month(u.created)
 """
 MAU_QUERY="""
 SELECT count(distinct d.user_id)
-FROM daily_visitors d join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+FROM daily_visitors d join users u where u.id=d.user_id AND
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(d.visited) > '2015-03-08'
 group by month(d.visited)
 order by month(d.visited);
 """
 MAB_QUERY="""
 SELECT COUNT(DISTINCT o.user_id )
 FROM  order_view o
 JOIN users u ON u.id = o.user_id where
-LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-AND o.STATUS =  'ORDER_CREATED'
+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%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-AND o.STATUS =  'ORDER_CREATED'
+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);
 """
 WNRU_QUERY="""
 SELECT COUNT(*)
 FROM users u WHERE
-LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(u.created) > '2015-03-08'
 GROUP BY WEEK(date(u.created))
 ORDER BY WEEK(date(u.created))
 """
 WAU_QUERY="""
 SELECT  COUNT(distinct d.user_id) AS total
-FROM  daily_visitors d join users u where u.id=d.user_id AND LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+FROM  daily_visitors d join users u where u.id=d.user_id AND
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
 AND date(d.visited) > '2015-03-08'
 GROUP BY WEEK(d.visited)
 ORDER BY WEEK(d.visited)
 """
 WAB_QUERY="""
 SELECT COUNT( DISTINCT o.user_id )
 FROM  order_view o
-JOIN users u ON u.id = o.user_id
+JOIN users u ON u.id = o.user_id WHERE
-WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-AND o.STATUS =  'ORDER_CREATED'
+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
+JOIN users u ON u.id = o.user_id WHERE
-WHERE LOWER(u.referrer) NOT LIKE  'emp%' AND u.activated =1
+(LOWER(u.referrer) NOT LIKE  'emp%' OR u.utm_campaign is not NULL) AND u.activated =1
-AND o.STATUS =  'ORDER_CREATED'
+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))
 """
                 print "Breaking inner while"
                 break
 def populateValidOrders():
     global order_ids
-    allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(~(func.lower(Users.referrer)).like('emp%')).all()
+    #allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(~(func.lower(Users.referrer)).like('emp%')).all()
+    allOrders = session.query(Orders.id).join((Users,Orders.user_id==Users.id)).filter(or_(~(func.lower(Users.referrer)).like('emp%'),Users.utm_campaign != None)).all()
     order_ids = list(zip(*allOrders)[0])
 def main():
     #date = raw_input('Enter a date name: ')
     populateValidOrders()
     populateOrderMap()
     populateWeekWiseMap1()

Subversion Repositories SmartDukaan

(root)/trunk/PyDTR/src/scripts/ActivityReport.py – Rev 15231 → 15244