Subversion Repositories SmartDukaan

Rev

Rev 18340 | 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 
 
22506 amit.gupta 19 
DB_HOST = "127.0.0.1"
14427 amit.gupta 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='''
15731 amit.gupta 30 
select o.*, u.username from order_view o join users u on u.id=o.user_id %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 
'''
17052 amit.gupta 38 
 
14468 amit.gupta 39 
ORDERS_DATE_QUERY1 = '''
17085 amit.gupta 40 
select o.id,o.user_id,o.store_id,o.order_url,null, o.sub_tag, o.status,o.ip,o.imeinumber,o.wifi,o.created,o.modified from orders where created > %s and store_id = %s
14468 amit.gupta 41 
'''
14427 amit.gupta 42 
 
43 
ORDERS_COUNT_QUERY='''
15731 amit.gupta 44 
select count(*) from order_view o join users u on u.id=o.user_id %s
14427 amit.gupta 45 
'''
46 
 
47 
ORDER_HTML_QUERY = '''
48 
select rawhtml from orders where id = %s
49 
'''
50 
 
51 
 
14615 amit.gupta 52 
 
53 
 
15731 amit.gupta 54 
def getOrders(page=1, window=50, status=None, user=None, source=None):
14439 amit.gupta 55 
    try:
15731 amit.gupta 56 
        search = []
14725 amit.gupta 57 
        if status is not None:
15731 amit.gupta 58 
            search.append("o.status='%s'"%(status))
14725 amit.gupta 59 
        if user is not  None:
15731 amit.gupta 60 
            search.append("o.user_id=%s"%(user))
61 
        if source is not  None:
62 
            search.append("o.store_id=%s"%(source))
63 
        if len(search) > 0:
64 
            search = " where " +  " and ".join(search)
65 
        else:
66 
            search = " "
14439 amit.gupta 67 
        conn = getDbConnection()
68 
        if page==None:
69 
            page = 1
70 
        if window==None:
71 
            window = 50
72 
        skip = (page-1)*window
73 
        # prepare a cursor object using cursor() method
74 
        cursor = conn.cursor()
75 
        # Execute the SQL command
76 
        # Fetch source id.
14725 amit.gupta 77 
        cursor.execute(ORDERS_COUNT_QUERY%(search))
14439 amit.gupta 78 
        result = cursor.fetchall()
79 
        total_count = result[0][0]
80 
        pages = total_count/window + (0 if total_count%window==0 else 1)
81 
 
14725 amit.gupta 82 
        query = ORDERS_QUERY%(search,window,skip)
14439 amit.gupta 83 
        cursor.execute(query)
84 
        result = cursor.fetchall()
85 
        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>"
86 
        tbodyarr = []
14470 amit.gupta 87 
        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 88 
        tbodyarr.append(colhead)
89 
        for row in result:
18340 amit.gupta 90 
            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 91 
 
14440 amit.gupta 92 
        return template%(total_count, "".join(tbodyarr))
93 
    except:
94 
        traceback.print_exc()
14439 amit.gupta 95 
    finally:
96 
        conn.close()
14427 amit.gupta 97 
def getOrderHtml(orderId):
14439 amit.gupta 98 
    try:
99 
        conn = getDbConnection()
100 
        cursor = conn.cursor()
101 
        # Execute the SQL command
102 
        # Fetch source id.
103 
        cursor.execute(ORDER_HTML_QUERY%(orderId))
104 
        result = cursor.fetchall()
105 
        return result[0][0]
14440 amit.gupta 106 
    except:
107 
        traceback.print_exc()
14439 amit.gupta 108 
    finally:
109 
        conn.close()
14615 amit.gupta 110 
 
14451 amit.gupta 111 
def getOrdersAfterDate(afterDate, storeId):
14460 amit.gupta 112 
    try:
113 
        conn = getDbConnection()
114 
        cursor = conn.cursor()
14602 amit.gupta 115 
        afterDate = datetime.strftime(afterDate,"%Y-%m-%d")
116 
        beforeDate = datetime.strftime(datetime.now(),"%Y-%m-%d %H")
17085 amit.gupta 117 
        cursor.execute(ORDERS_DATE_QUERY%(afterDate, beforeDate, storeId))
14460 amit.gupta 118 
        result = cursor.fetchall()
119 
        return result
120 
    finally:
121 
        conn.close()
14451 amit.gupta 122 
 
14468 amit.gupta 123 
def getOrdersAfterDate1(afterDate, storeId):
124 
    try:
125 
        conn = getDbConnection()
126 
        cursor = conn.cursor()
127 
        afterDate = datetime.strftime(afterDate,"\'%Y-%m-%d\'")
128 
        cursor.execute(ORDERS_DATE_QUERY1%(afterDate, storeId))
129 
        result = cursor.fetchall()
130 
        return result
131 
    finally:
132 
        conn.close()
133 
 
14526 amit.gupta 134 
def getOrdersByTag(tagName, storeId):
135 
    try:
136 
        conn = getDbConnection()
137 
        cursor = conn.cursor()
138 
        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"
139 
        cursor.execute(query%(tagName, storeId))
140 
        result = cursor.fetchall()
141 
        return result
142 
    finally:
14615 amit.gupta 143 
        conn.close()
14526 amit.gupta 144 
 
14427 amit.gupta 145 
def main():
14460 amit.gupta 146 
    print getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)
14427 amit.gupta 147 
 
17144 amit.gupta 148 
def fetchResult(query, *params):
14615 amit.gupta 149 
    try:
150 
        conn = getDbConnection()
151 
        cursor = conn.cursor()
17147 amit.gupta 152 
        if params:
153 
            cursor.execute(query, params)
154 
        else:
155 
            cursor.execute(query)
14615 amit.gupta 156 
        result = cursor.fetchall()
157 
        return result
158 
    finally:
159 
        conn.close()
14427 amit.gupta 160 
 
161 
if __name__ == '__main__':
162 
    main()