Subversion Repositories SmartDukaan

Rev

Rev 14472 | Rev 14526 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
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='''
14470 amit.gupta 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
14427 amit.gupta 31 
'''
14451 amit.gupta 32 
ORDERS_DATE_QUERY = '''
14513 amit.gupta 33 
select o.*, u.username from order_view o join users u on u.id=o.user_id where o.created between %s and %s and store_id = %s
14451 amit.gupta 34 
'''
14468 amit.gupta 35 
ORDERS_DATE_QUERY1 = '''
36 
select * from orders where created > %s and store_id = %s
37 
'''
14427 amit.gupta 38 
 
39 
ORDERS_COUNT_QUERY='''
40 
select count(*) from order_view
41 
'''
42 
 
43 
ORDER_HTML_QUERY = '''
44 
select rawhtml from orders where id = %s
45 
'''
46 
 
47 
 
48 
def getOrders(page=1, window=50):
14439 amit.gupta 49 
    try:
50 
        conn = getDbConnection()
51 
        if page==None:
52 
            page = 1
53 
        if window==None:
54 
            window = 50
55 
        skip = (page-1)*window
56 
        # prepare a cursor object using cursor() method
57 
        cursor = conn.cursor()
58 
        # Execute the SQL command
59 
        # Fetch source id.
60 
        cursor.execute(ORDERS_COUNT_QUERY)
61 
        result = cursor.fetchall()
62 
        total_count = result[0][0]
63 
        pages = total_count/window + (0 if total_count%window==0 else 1)
64 
 
65 
        query = ORDERS_QUERY%(window,skip)
66 
        cursor.execute(query)
67 
        result = cursor.fetchall()
68 
        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>"
69 
        tbodyarr = []
14470 amit.gupta 70 
        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>"
14439 amit.gupta 71 
        tbodyarr.append(colhead)
72 
        for row in result:
14470 amit.gupta 73 
            tbodyarr.append("<tr><td>" + xstr(row[0]) + "</td><td>" + xstr(row[1]) +"</td><td>" + xstr(row[-1]) + "</td><td>" + xstr(row[2]) +"</td><td><a target='_blank' href='/rawhtml/" + xstr(row[0]) + "'>" + xstr(row[3])[:130] +"</a></td><td>" + xstr(row[4]) + "</td><td>" + row[5]+ "</td><td>" + row[6].strftime("%Y-%m-%d %H:%M:%S") + "</td></tr>")
14439 amit.gupta 74 
 
14440 amit.gupta 75 
        return template%(total_count, "".join(tbodyarr))
76 
    except:
77 
        traceback.print_exc()
14439 amit.gupta 78 
    finally:
79 
        conn.close()
14427 amit.gupta 80 
def getOrderHtml(orderId):
14439 amit.gupta 81 
    try:
82 
        conn = getDbConnection()
83 
        cursor = conn.cursor()
84 
        # Execute the SQL command
85 
        # Fetch source id.
86 
        cursor.execute(ORDER_HTML_QUERY%(orderId))
87 
        result = cursor.fetchall()
88 
        return result[0][0]
14440 amit.gupta 89 
    except:
90 
        traceback.print_exc()
14439 amit.gupta 91 
    finally:
92 
        conn.close()
14427 amit.gupta 93 
 
14451 amit.gupta 94 
def getOrdersAfterDate(afterDate, storeId):
14460 amit.gupta 95 
    try:
96 
        conn = getDbConnection()
97 
        cursor = conn.cursor()
98 
        afterDate = datetime.strftime(afterDate,"\'%Y-%m-%d\'")
14513 amit.gupta 99 
        beforeDate = datetime.strftime(datetime.now(),"\'%Y-%m-%d\'")
100 
        cursor.execute(ORDERS_DATE_QUERY%(afterDate, beforeDate, storeId))
14460 amit.gupta 101 
        result = cursor.fetchall()
102 
        return result
103 
    finally:
104 
        conn.close()
14451 amit.gupta 105 
 
14468 amit.gupta 106 
def getOrdersAfterDate1(afterDate, storeId):
107 
    try:
108 
        conn = getDbConnection()
109 
        cursor = conn.cursor()
110 
        afterDate = datetime.strftime(afterDate,"\'%Y-%m-%d\'")
111 
        cursor.execute(ORDERS_DATE_QUERY1%(afterDate, storeId))
112 
        result = cursor.fetchall()
113 
        return result
114 
    finally:
115 
        conn.close()
116 
 
14427 amit.gupta 117 
def main():
14460 amit.gupta 118 
    print getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)
14427 amit.gupta 119 
 
120 
 
121 
if __name__ == '__main__':
122 
    main()