| Line 25... |
Line 25... |
| 25 |
def getDbConnection():
|
25 |
def getDbConnection():
|
| 26 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
26 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
| 27 |
|
27 |
|
| 28 |
|
28 |
|
| 29 |
ORDERS_QUERY='''
|
29 |
ORDERS_QUERY='''
|
| 30 |
select o.*, u.username from order_view o join users u on u.id=o.user_id order by id desc limit %s offset %s
|
30 |
select o.*, u.username from order_view o join users u on u.id=o.user_id where %s order by id desc limit %s offset %s
|
| 31 |
'''
|
31 |
'''
|
| 32 |
ORDERS_DATE_QUERY = '''
|
32 |
ORDERS_DATE_QUERY = '''
|
| 33 |
select o.*, max(d.versioncode), u.username from order_view o join users u on u.id=o.user_id left
|
33 |
select o.*, max(d.versioncode), u.username from order_view o join users u on u.id=o.user_id left
|
| 34 |
join devices d on (d.user_id=o.user_id and o.created >= d.created) where o.created between '%s' and '%s' and store_id = %s group by o.id
|
34 |
join devices d on (d.user_id=o.user_id and o.created >= d.created) where o.created between '%s' and '%s' and store_id = %s group by o.id
|
| 35 |
'''
|
35 |
'''
|
| 36 |
ORDERS_DATE_QUERY1 = '''
|
36 |
ORDERS_DATE_QUERY1 = '''
|
| 37 |
select * from orders where created > %s and store_id = %s
|
37 |
select * from orders where created > %s and store_id = %s
|
| 38 |
'''
|
38 |
'''
|
| 39 |
|
39 |
|
| 40 |
ORDERS_COUNT_QUERY='''
|
40 |
ORDERS_COUNT_QUERY='''
|
| 41 |
select count(*) from order_view
|
41 |
select count(*) from order_view o join users u on u.id=o.user_id where %s
|
| 42 |
'''
|
42 |
'''
|
| 43 |
|
43 |
|
| 44 |
ORDER_HTML_QUERY = '''
|
44 |
ORDER_HTML_QUERY = '''
|
| 45 |
select rawhtml from orders where id = %s
|
45 |
select rawhtml from orders where id = %s
|
| 46 |
'''
|
46 |
'''
|
| 47 |
|
47 |
|
| 48 |
|
48 |
|
| 49 |
|
49 |
|
| 50 |
|
50 |
|
| 51 |
def getOrders(page=1, window=50):
|
51 |
def getOrders(page=1, window=50, status=None, user=None):
|
| 52 |
try:
|
52 |
try:
|
| - |
|
53 |
search = "1=1"
|
| - |
|
54 |
if status is not None:
|
| - |
|
55 |
search="o.status='%s'"%(status)
|
| - |
|
56 |
if user is not None:
|
| - |
|
57 |
search = "o.user_id=%s"%(user)
|
| 53 |
conn = getDbConnection()
|
58 |
conn = getDbConnection()
|
| 54 |
if page==None:
|
59 |
if page==None:
|
| 55 |
page = 1
|
60 |
page = 1
|
| 56 |
if window==None:
|
61 |
if window==None:
|
| 57 |
window = 50
|
62 |
window = 50
|
| 58 |
skip = (page-1)*window
|
63 |
skip = (page-1)*window
|
| 59 |
# prepare a cursor object using cursor() method
|
64 |
# prepare a cursor object using cursor() method
|
| 60 |
cursor = conn.cursor()
|
65 |
cursor = conn.cursor()
|
| 61 |
# Execute the SQL command
|
66 |
# Execute the SQL command
|
| 62 |
# Fetch source id.
|
67 |
# Fetch source id.
|
| 63 |
cursor.execute(ORDERS_COUNT_QUERY)
|
68 |
cursor.execute(ORDERS_COUNT_QUERY%(search))
|
| 64 |
result = cursor.fetchall()
|
69 |
result = cursor.fetchall()
|
| 65 |
total_count = result[0][0]
|
70 |
total_count = result[0][0]
|
| 66 |
pages = total_count/window + (0 if total_count%window==0 else 1)
|
71 |
pages = total_count/window + (0 if total_count%window==0 else 1)
|
| 67 |
|
72 |
|
| 68 |
query = ORDERS_QUERY%(window,skip)
|
73 |
query = ORDERS_QUERY%(search,window,skip)
|
| 69 |
cursor.execute(query)
|
74 |
cursor.execute(query)
|
| 70 |
result = cursor.fetchall()
|
75 |
result = cursor.fetchall()
|
| 71 |
template = "<html><head><style>td,th{border:1px solid;padding:2px}</style></head><div>Total Orders: %s</div><br/><body><table style='font-size:12px;border:1px solid grey;border-spacing:0;cell-spacing:0;border-bottom:1px solid grey;border-bottom:1px solid transparent'>%s</table></body></html>"
|
76 |
template = "<html><head><style>td,th{border:1px solid;padding:2px}</style></head><div>Total Orders: %s</div><br/><body><table style='font-size:12px;border:1px solid grey;border-spacing:0;cell-spacing:0;border-bottom:1px solid grey;border-bottom:1px solid transparent'>%s</table></body></html>"
|
| 72 |
tbodyarr = []
|
77 |
tbodyarr = []
|
| 73 |
colhead="<tr><th>Id</th><th>User Id</th><th>Username</th><th>Store Id</th><th>Order Url</th><th>Subtag</th><th>Statu</th><th>Created on</th></tr>"
|
78 |
colhead="<tr><th>Id</th><th>User Id</th><th>Username</th><th>Store Id</th><th>Order Url</th><th>Subtag</th><th>Statu</th><th>Created on</th></tr>"
|