Subversion Repositories SmartDukaan

Rev

Rev 14460 | Rev 14470 | Go to most recent revision | 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 = "localhost"
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 * from order_view order by id desc limit %s offset %s
'''
ORDERS_DATE_QUERY = '''
select * from order_view where created > %s and store_id = %s 
'''
ORDERS_DATE_QUERY1 = '''
select * from orders where created > %s and store_id = %s 
'''

ORDERS_COUNT_QUERY='''
select count(*) from order_view
'''

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


def getOrders(page=1, window=50):
    try:
        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)
        result = cursor.fetchall()
        total_count = result[0][0]
        pages = total_count/window + (0 if total_count%window==0 else 1)
        
        query = ORDERS_QUERY%(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>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>" + 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>")
        
        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\'")
        cursor.execute(ORDERS_DATE_QUERY%(afterDate, 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 main():
    print getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)


if __name__ == '__main__':
    main()