Subversion Repositories SmartDukaan

Rev

Rev 18340 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

'''
Created on Mar 11, 2015

@author: amit
'''

from datetime import date, datetime, timedelta
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import MySQLdb
import smtplib
import traceback
import xlwt



DB_HOST = "127.0.0.1"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
xstr = lambda s: '' if s is None else str(s)

def getDbConnection():
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)


ORDERS_QUERY='''
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
'''
ORDERS_DATE_QUERY = '''
select o.*, dev.imeinumber, dev.versioncode, u.username from order_view o left join (
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) 
group by ov.id, d.id having ov.created-d.created>=0 order by ov.created-d.created) dev group by dev.oid
) 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 
'''

ORDERS_DATE_QUERY1 = '''
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 
'''

ORDERS_COUNT_QUERY='''
select count(*) from order_view o join users u on u.id=o.user_id %s 
'''

ORDER_HTML_QUERY = '''
select rawhtml from orders where id = %s
'''




def getOrders(page=1, window=50, status=None, user=None, source=None):
    try:
        search = []
        if status is not None:
            search.append("o.status='%s'"%(status))
        if user is not  None:
            search.append("o.user_id=%s"%(user))
        if source is not  None:
            search.append("o.store_id=%s"%(source))
        if len(search) > 0:
            search = " where " +  " and ".join(search)
        else:
            search = " "
        conn = getDbConnection()
        if page==None:
            page = 1
        if window==None:
            window = 50
        skip = (page-1)*window
        # prepare a cursor object using cursor() method
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(ORDERS_COUNT_QUERY%(search))
        result = cursor.fetchall()
        total_count = result[0][0]
        pages = total_count/window + (0 if total_count%window==0 else 1)
        
        query = ORDERS_QUERY%(search,window,skip)
        cursor.execute(query)
        result = cursor.fetchall()
        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>"
        tbodyarr = []
        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>"
        tbodyarr.append(colhead)
        for row in result:
            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>")
        
        return template%(total_count, "".join(tbodyarr))
    except:
        traceback.print_exc()
    finally:
        conn.close()
def getOrderHtml(orderId):
    try:
        conn = getDbConnection()
        cursor = conn.cursor()
        # Execute the SQL command
        # Fetch source id.
        cursor.execute(ORDER_HTML_QUERY%(orderId))
        result = cursor.fetchall()
        return result[0][0]
    except:
        traceback.print_exc()
    finally:
        conn.close()
        
def getOrdersAfterDate(afterDate, storeId):
    try:
        conn = getDbConnection()
        cursor = conn.cursor()
        afterDate = datetime.strftime(afterDate,"%Y-%m-%d")
        beforeDate = datetime.strftime(datetime.now(),"%Y-%m-%d %H")
        cursor.execute(ORDERS_DATE_QUERY%(afterDate, beforeDate, storeId))
        result = cursor.fetchall()
        return result
    finally:
        conn.close() 

def getOrdersAfterDate1(afterDate, storeId):
    try:
        conn = getDbConnection()
        cursor = conn.cursor()
        afterDate = datetime.strftime(afterDate,"\'%Y-%m-%d\'")
        cursor.execute(ORDERS_DATE_QUERY1%(afterDate, storeId))
        result = cursor.fetchall()
        return result
    finally:
        conn.close() 

def getOrdersByTag(tagName, storeId):
    try:
        conn = getDbConnection()
        cursor = conn.cursor()
        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"
        cursor.execute(query%(tagName, storeId))
        result = cursor.fetchall()
        return result
    finally:
        conn.close()

def main():
    print getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)

def fetchResult(query, *params):
    try:
        conn = getDbConnection()
        cursor = conn.cursor()
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        result = cursor.fetchall()
        return result
    finally:
        conn.close() 

if __name__ == '__main__':
    main()