Subversion Repositories SmartDukaan

Rev

Rev 14733 | Rev 15731 | 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='''
14725 amit.gupta 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
14427 amit.gupta 31 
'''
14451 amit.gupta 32 
ORDERS_DATE_QUERY = '''
14771 amit.gupta 33 
select o.*, dev.imeinumber, dev.versioncode, u.username from order_view o left join (
34 
select * from (select ov.id as oid, ov.created ocreated, d.* from order_view ov left join devices d on (ov.user_id= d.user_id)
35 
group by ov.id, d.id having ov.created-d.created>=0 order by ov.created-d.created) dev group by dev.oid
36 
) dev on dev.oid = o.id join users u on u.id=o.user_id where o.created between '%s' and '%s' and store_id = %s group by o.id
14451 amit.gupta 37 
'''
14468 amit.gupta 38 
ORDERS_DATE_QUERY1 = '''
39 
select * from orders where created > %s and store_id = %s
40 
'''
14427 amit.gupta 41 
 
42 
ORDERS_COUNT_QUERY='''
14725 amit.gupta 43 
select count(*) from order_view o join users u on u.id=o.user_id where %s
14427 amit.gupta 44 
'''
45 
 
46 
ORDER_HTML_QUERY = '''
47 
select rawhtml from orders where id = %s
48 
'''
49 
 
50 
 
14615 amit.gupta 51 
 
52 
 
14725 amit.gupta 53 
def getOrders(page=1, window=50, status=None, user=None):
14439 amit.gupta 54 
    try:
14725 amit.gupta 55 
        search = "1=1"
56 
        if status is not None:
57 
            search="o.status='%s'"%(status)
58 
        if user is not  None:
59 
            search = "o.user_id=%s"%(user)
14439 amit.gupta 60 
        conn = getDbConnection()
61 
        if page==None:
62 
            page = 1
63 
        if window==None:
64 
            window = 50
65 
        skip = (page-1)*window
66 
        # prepare a cursor object using cursor() method
67 
        cursor = conn.cursor()
68 
        # Execute the SQL command
69 
        # Fetch source id.
14725 amit.gupta 70 
        cursor.execute(ORDERS_COUNT_QUERY%(search))
14439 amit.gupta 71 
        result = cursor.fetchall()
72 
        total_count = result[0][0]
73 
        pages = total_count/window + (0 if total_count%window==0 else 1)
74 
 
14725 amit.gupta 75 
        query = ORDERS_QUERY%(search,window,skip)
14439 amit.gupta 76 
        cursor.execute(query)
77 
        result = cursor.fetchall()
78 
        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>"
79 
        tbodyarr = []
14470 amit.gupta 80 
        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 81 
        tbodyarr.append(colhead)
82 
        for row in result:
14733 amit.gupta 83 
            tbodyarr.append("<tr><td>" + xstr(row[0]) + "</td><td>" + xstr(row[1]) +"</td><td><a href=\"/transactions?user=" + xstr(row[1]) + "\">" + xstr(row[-1]) + "</a></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><a href=\"/transactions?status=" + xstr(row[5]) + "\">" + xstr(row[5]) + "</a></td><td>" + row[6].strftime("%Y-%m-%d %H:%M:%S") + "</td></tr>")
14439 amit.gupta 84 
 
14440 amit.gupta 85 
        return template%(total_count, "".join(tbodyarr))
86 
    except:
87 
        traceback.print_exc()
14439 amit.gupta 88 
    finally:
89 
        conn.close()
14427 amit.gupta 90 
def getOrderHtml(orderId):
14439 amit.gupta 91 
    try:
92 
        conn = getDbConnection()
93 
        cursor = conn.cursor()
94 
        # Execute the SQL command
95 
        # Fetch source id.
96 
        cursor.execute(ORDER_HTML_QUERY%(orderId))
97 
        result = cursor.fetchall()
98 
        return result[0][0]
14440 amit.gupta 99 
    except:
100 
        traceback.print_exc()
14439 amit.gupta 101 
    finally:
102 
        conn.close()
14615 amit.gupta 103 
 
14451 amit.gupta 104 
def getOrdersAfterDate(afterDate, storeId):
14460 amit.gupta 105 
    try:
106 
        conn = getDbConnection()
107 
        cursor = conn.cursor()
14602 amit.gupta 108 
        afterDate = datetime.strftime(afterDate,"%Y-%m-%d")
109 
        beforeDate = datetime.strftime(datetime.now(),"%Y-%m-%d %H")
14513 amit.gupta 110 
        cursor.execute(ORDERS_DATE_QUERY%(afterDate, beforeDate, storeId))
14460 amit.gupta 111 
        result = cursor.fetchall()
112 
        return result
113 
    finally:
114 
        conn.close()
14451 amit.gupta 115 
 
14468 amit.gupta 116 
def getOrdersAfterDate1(afterDate, storeId):
117 
    try:
118 
        conn = getDbConnection()
119 
        cursor = conn.cursor()
120 
        afterDate = datetime.strftime(afterDate,"\'%Y-%m-%d\'")
121 
        cursor.execute(ORDERS_DATE_QUERY1%(afterDate, storeId))
122 
        result = cursor.fetchall()
123 
        return result
124 
    finally:
125 
        conn.close()
126 
 
14526 amit.gupta 127 
def getOrdersByTag(tagName, storeId):
128 
    try:
129 
        conn = getDbConnection()
130 
        cursor = conn.cursor()
131 
        query = "select o.* from users u join order_view o on u.id = o.user_id join clicks c on c.user_id = u.id where c.tag = '%s' and o.status='ORDER_CREATED' and o.store_id=%s"
132 
        cursor.execute(query%(tagName, storeId))
133 
        result = cursor.fetchall()
134 
        return result
135 
    finally:
14615 amit.gupta 136 
        conn.close()
14526 amit.gupta 137 
 
14427 amit.gupta 138 
def main():
14460 amit.gupta 139 
    print getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)
14427 amit.gupta 140 
 
14615 amit.gupta 141 
def fetchResult(query, *params):
142 
    try:
143 
        conn = getDbConnection()
144 
        cursor = conn.cursor()
145 
        cursor.execute(query.format(params))
146 
        result = cursor.fetchall()
147 
        return result
148 
    finally:
149 
        conn.close()
14427 amit.gupta 150 
 
151 
if __name__ == '__main__':
152 
    main()