Subversion Repositories SmartDukaan

Rev

Rev 14427 | Rev 14440 | 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
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 datetime
import smtplib
import xlwt



DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "dtr"
xstr = lambda s: '' if s is None else str(s)
connection = None
def getDbConnection():
    connection = 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_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])[:90] +"</a></td><td>" + xstr(row[4]) + "</td><td>" + row[5][:15]+ "</td><td>" + row[6].strftime("%Y-%m-%d %H:%M:%S") + "</td></tr>")
        
        return template%(total_count, "".join(tbodyarr))    
    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]
    finally:
        conn.close()

def main():
    print getOrders()


if __name__ == '__main__':
    main()