| 14427 |
amit.gupta |
1 |
'''
|
|
|
2 |
Created on Mar 11, 2015
|
|
|
3 |
|
|
|
4 |
@author: amit
|
|
|
5 |
'''
|
|
|
6 |
|
| 14451 |
amit.gupta |
7 |
from datetime import date, datetime, timedelta
|
| 14427 |
amit.gupta |
8 |
from email import encoders
|
|
|
9 |
from email.mime.base import MIMEBase
|
|
|
10 |
from email.mime.multipart import MIMEMultipart
|
|
|
11 |
from email.mime.text import MIMEText
|
|
|
12 |
import MySQLdb
|
|
|
13 |
import smtplib
|
| 14440 |
amit.gupta |
14 |
import traceback
|
| 14427 |
amit.gupta |
15 |
import xlwt
|
|
|
16 |
|
|
|
17 |
|
|
|
18 |
|
|
|
19 |
DB_HOST = "localhost"
|
|
|
20 |
DB_USER = "root"
|
|
|
21 |
DB_PASSWORD = "shop2020"
|
|
|
22 |
DB_NAME = "dtr"
|
|
|
23 |
xstr = lambda s: '' if s is None else str(s)
|
| 14440 |
amit.gupta |
24 |
|
| 14427 |
amit.gupta |
25 |
def getDbConnection():
|
| 14440 |
amit.gupta |
26 |
return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
|
| 14427 |
amit.gupta |
27 |
|
|
|
28 |
|
|
|
29 |
ORDERS_QUERY='''
|
|
|
30 |
select * from order_view order by id desc limit %s offset %s
|
|
|
31 |
'''
|
| 14451 |
amit.gupta |
32 |
ORDERS_DATE_QUERY = '''
|
|
|
33 |
select * from order_view where created > %s and store_id = %s
|
|
|
34 |
'''
|
| 14427 |
amit.gupta |
35 |
|
|
|
36 |
ORDERS_COUNT_QUERY='''
|
|
|
37 |
select count(*) from order_view
|
|
|
38 |
'''
|
|
|
39 |
|
|
|
40 |
ORDER_HTML_QUERY = '''
|
|
|
41 |
select rawhtml from orders where id = %s
|
|
|
42 |
'''
|
|
|
43 |
|
|
|
44 |
|
|
|
45 |
def getOrders(page=1, window=50):
|
| 14439 |
amit.gupta |
46 |
try:
|
|
|
47 |
conn = getDbConnection()
|
|
|
48 |
if page==None:
|
|
|
49 |
page = 1
|
|
|
50 |
if window==None:
|
|
|
51 |
window = 50
|
|
|
52 |
skip = (page-1)*window
|
|
|
53 |
# prepare a cursor object using cursor() method
|
|
|
54 |
cursor = conn.cursor()
|
|
|
55 |
# Execute the SQL command
|
|
|
56 |
# Fetch source id.
|
|
|
57 |
cursor.execute(ORDERS_COUNT_QUERY)
|
|
|
58 |
result = cursor.fetchall()
|
|
|
59 |
total_count = result[0][0]
|
|
|
60 |
pages = total_count/window + (0 if total_count%window==0 else 1)
|
|
|
61 |
|
|
|
62 |
query = ORDERS_QUERY%(window,skip)
|
|
|
63 |
cursor.execute(query)
|
|
|
64 |
result = cursor.fetchall()
|
|
|
65 |
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>"
|
|
|
66 |
tbodyarr = []
|
|
|
67 |
colhead="<tr><th>Id</th><th>User Id</th><th>Store Id</th><th>Order Url</th><th>Subtag</th><th>Statu</th><th>Created on</th></tr>"
|
|
|
68 |
tbodyarr.append(colhead)
|
|
|
69 |
for row in result:
|
|
|
70 |
tbodyarr.append("<tr><td>" + xstr(row[0]) + "</td><td>" + xstr(row[1]) +"</td><td>" + xstr(row[2]) +"</td><td><a target='_blank' href='/rawhtml/" + xstr(row[0]) + "'>" + xstr(row[3])[:90] +"</a></td><td>" + xstr(row[4]) + "</td><td>" + row[5][:15]+ "</td><td>" + row[6].strftime("%Y-%m-%d %H:%M:%S") + "</td></tr>")
|
|
|
71 |
|
| 14440 |
amit.gupta |
72 |
return template%(total_count, "".join(tbodyarr))
|
|
|
73 |
except:
|
|
|
74 |
traceback.print_exc()
|
| 14439 |
amit.gupta |
75 |
finally:
|
|
|
76 |
conn.close()
|
| 14427 |
amit.gupta |
77 |
def getOrderHtml(orderId):
|
| 14439 |
amit.gupta |
78 |
try:
|
|
|
79 |
conn = getDbConnection()
|
|
|
80 |
cursor = conn.cursor()
|
|
|
81 |
# Execute the SQL command
|
|
|
82 |
# Fetch source id.
|
|
|
83 |
cursor.execute(ORDER_HTML_QUERY%(orderId))
|
|
|
84 |
result = cursor.fetchall()
|
|
|
85 |
return result[0][0]
|
| 14440 |
amit.gupta |
86 |
except:
|
|
|
87 |
traceback.print_exc()
|
| 14439 |
amit.gupta |
88 |
finally:
|
|
|
89 |
conn.close()
|
| 14427 |
amit.gupta |
90 |
|
| 14451 |
amit.gupta |
91 |
def getOrdersAfterDate(afterDate, storeId):
|
|
|
92 |
conn = getDbConnection()
|
|
|
93 |
cursor = conn.cursor()
|
|
|
94 |
afterDate = datetime.strftime(afterDate,"\'%Y-%m-%d\'")
|
|
|
95 |
cursor.execute(ORDERS_DATE_QUERY%(afterDate, storeId))
|
|
|
96 |
result = cursor.fetchall()
|
|
|
97 |
return result
|
|
|
98 |
|
| 14427 |
amit.gupta |
99 |
def main():
|
| 14451 |
amit.gupta |
100 |
getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)
|
| 14427 |
amit.gupta |
101 |
|
|
|
102 |
|
|
|
103 |
if __name__ == '__main__':
|
|
|
104 |
main()
|