Subversion Repositories SmartDukaan

'''

Created on 08-Jul-2013

@author: anupam

'''

import MySQLdb

import datetime

import smtplib

import xlwt

import traceback

from email import encoders

from email.mime.text import MIMEText

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from datetime import date

# Initialize db connection settings.

DB_HOST = "192.168.190.114"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "transaction"

# KEY NAMES

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "Recharge report for week ending on " + date.today().isoformat()

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

TMP_FILE="/tmp/dtrshipped_report.xls"

report_map = {}

SHIPPED_ORDER_GROUP_SQL="""     select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),sum(o.total_amount) as amount 

                                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  

                                join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id  

                                where o.status in (9,10,16,17,40) and o.total_amount > 1 group by c.name, prodname  with rollup

                        """

SHIPPED_ORDER_SQL=      """

                          select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, date(o.shipping_timestamp) as shiipedon, os.statusName , date(o.expected_delivery_time), 

                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o 

                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id 

                          join inventory.warehouse w on w.id = o.fulfilmentWarehouseId join orderstatus os on o.status = os.status 

                          where o.status in (9,10,16,17,40) and o.total_amount > 1 order by code, prodname   

                        """

SOLD_OUT_GROUP_SQL="""

                    select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),  sum(o.total_amount) as total_sale 

                        from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id 

                        join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id 

                        join transaction.lineitem li on li.order_id=o.id  where date(mar.activationTime) = curdate()-1 group by c.name, prodname  with rollup;

                   """

SOLD_OUT_SQL="""

                          select c.code,c.name, o.id, date(o.created_timestamp) as orderedon, date(delivery_timestamp), mar.activationTime, 

                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o 

                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id 

                          join mobileactivationrecord mar on mar.orderId = o.id where date(mar.activationTime) = curdate()-1 order by code, prodname

             """

PENDING_ORDER_GROUP_SQL="""     select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),sum(o.total_amount) as amount 

                                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  

                                join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id  

                                where o.status in (3,4,5,7) and o.total_amount > 1 group by c.name, prodname  with rollup

                        """

PENDING_ORDER_SQL= """

                          select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, date(o.shipping_timestamp) as shiipedon, os.statusName , date(o.expected_delivery_time), 

                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o 

                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id 

                          join inventory.warehouse w on w.id = o.fulfilmentWarehouseId join orderstatus os on o.status = os.status 

                          where o.status in (3,4,5,7) and o.total_amount > 1 order by code, prodname   

                        """

STOCK_REPORT_GROUP_SQL= """

                        select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  count(*) as delivered,

                            sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is not null, 0, 1)) as instock,  

                            sum(if(mar.activationTime is not null, 0,o.total_amount)) as valueinstock 

                        from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id 

                            left join mobileactivationrecord mar on mar.orderId = o.id  join user.counter c on c.id = pd.counter_id 

                            join transaction.lineitem li on li.order_id=o.id 

                        where o.status=12 and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product  with rollup

                        """

STOCK_REPORT_SQL=       """

                        select c.code, c.name, o.id, date(o.created_timestamp) createddate, date(o.delivery_timestamp) delivereddate, os.statusName, 

                        concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  li.quantity, o.total_amount, 

                        datediff(curdate(),date(o.delivery_timestamp)) as ageing  from transaction.order o join user.privatedealuser 

                        pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id  

                        join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id join orderstatus os on o.status = os.status

                        where o.status=12 and o.total_amount > 0 and mar.activationTime is null and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable)

                        """

MIS_REPORT_GROUP_SQL="""

                        select c.name, sum(if(date(o.created_timestamp)=curdate()-1, 1, 0)) as lastDayQty, 

                            sum(if(date(o.created_timestamp)=curdate()-1, o.total_amount , 0)) lastDayValue, 

                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o 

                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id 

                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40) 

                            and o.total_amount > 1 and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE() 

                            group by c.name with rollup;

                     """

MIS_REPORT_GROUP_PROD_SQL="""

                        select concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(date(o.created_timestamp)=curdate()-1, 1, 0)) as lastDayQty, 

                            sum(if(date(o.created_timestamp)=curdate()-1, o.total_amount , 0)) lastDayValue, 

                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o 

                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id 

                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40) 

                            and o.total_amount > 1 and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE() 

                            group by product with rollup;

                     """

MIS_REPORT_SQL="""

                select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, os.statusName , date(mar.activationTime) activatedon, 

                concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount 

                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id 

                join transaction.lineitem li on li.order_id=o.id join inventory.warehouse w on w.id = o.fulfilmentWarehouseId 

                join orderstatus os on o.status = os.status left join mobileactivationrecord mar on mar.orderId = o.id 

                where o.status in (2,3,4,5,7,9,10,12,16,17,40) and o.total_amount > 1 

                and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE() order by orderedon desc, c.code 

                """

date_format = xlwt.XFStyle()

date_format.num_format_str = 'dd/mm/yyyy'

datetime_format = xlwt.XFStyle()

datetime_format.num_format_str = 'dd/mm/yyyy HH:MM AM/PM'

number_format = xlwt.XFStyle()

number_format.num_format_str = '#,##0'

valueformat = "{0:,.0f}"

default_format = xlwt.XFStyle()

shipped_headers=['Counter', 'Product', 'Quantity', 'Value']

tertiary_headers=shipped_headers

pending_headers=shipped_headers

ageing_headers=['Counter','Product','QtyDelivered','QtySold','QtyInStock','ValueInStock']

mis_headers=['Counter','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']

mis_headers_prod=['Product','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']

report_map['shipped'] = {'title':'Shipped Orders Report', 'query' : SHIPPED_ORDER_GROUP_SQL, 'group':2, 'headers':shipped_headers}

report_map['tertiary'] = {'title':'Tertiary Report', 'query' : SOLD_OUT_GROUP_SQL, 'group':2, 'headers':tertiary_headers}

report_map['pending'] = {'title':'Pending Orders Report', 'query' : PENDING_ORDER_GROUP_SQL, 'group':2, 'headers':pending_headers}

report_map['ageing'] = {'title':'Spice Stock Ageing Report', 'query' : STOCK_REPORT_GROUP_SQL, 'group':2, 'headers':ageing_headers}

report_map['mis'] = {'title':'Daily Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_SQL, 'group':1, 'headers':mis_headers}

report_map['mis_prod'] = {'title':'Daily Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_PROD_SQL, 'group':1, 'headers':mis_headers_prod}

#recipients = []

recipients = ['rajneesh.arora@saholic.com', 'lakshman.g@smobility.in', 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'khushal.bhatia@saholic.com', 'chaitnaya.vats@saholic.com']

def getDbConnection():

    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

def closeConnection(conn):

    conn.close()

def sendmail(email, message, fileName, title):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    fileMsg = MIMEBase('application','vnd.ms-excel')

    fileMsg.set_payload(file(TMP_FILE).read())

    encoders.encode_base64(fileMsg)

    fileMsg.add_header('Content-Disposition','attachment;filename=' + fileName)

    msg.attach(fileMsg)

    email.append('amit.gupta@shop2020.in')

    MAILTO = email 

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def getMailAttachmentShipped():

    selectSql = SHIPPED_ORDER_SQL

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        createXlsReport(result)

    except Exception:

        traceback.print_exc()

        print "Could not execute query"

def createXlsReport(result):

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Shipped Orders")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(row, 0, 'Counter Code', boldStyle)

    worksheet.write(row, 1, 'Counter Name', boldStyle)

    worksheet.write(row, 2, 'Warehouse', boldStyle)

    worksheet.write(row, 3, 'Order Id', boldStyle)

    worksheet.write(row, 4, 'Order Date', boldStyle)

    worksheet.write(row, 5, 'Shipped Date', boldStyle)

    worksheet.write(row, 6, 'Order Status', boldStyle)

    worksheet.write(row, 7, 'Expected Delivery Date', boldStyle)

    worksheet.write(row, 8, 'Product', boldStyle)

    worksheet.write(row, 9, 'Qty', boldStyle)

    worksheet.write(row, 10, 'Value', boldStyle)

    for r in result:

        row += 1

        column = 0

        for data in r :

            worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)

            column += 1

    workbook.save(TMP_FILE)

def getMailAttachmentTertiary():

    selectSql = SOLD_OUT_SQL

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        workbook = xlwt.Workbook()

        worksheet = workbook.add_sheet("Tertiary Report")

        boldStyle = xlwt.XFStyle()

        f = xlwt.Font()

        f.bold = True

        boldStyle.font = f

        column = 0

        row = 0

        worksheet.write(row, 0, 'Counter Code', boldStyle)

        worksheet.write(row, 1, 'Counter Name', boldStyle)

        worksheet.write(row, 2, 'Order Id', boldStyle)

        worksheet.write(row, 3, 'Order Date', boldStyle)

        worksheet.write(row, 4, 'Delivered Date', boldStyle)

        worksheet.write(row, 5, 'Activated Timestamp', boldStyle)

        worksheet.write(row, 6, 'Product', boldStyle)

        worksheet.write(row, 7, 'Qty', boldStyle)

        worksheet.write(row, 8, 'Value', boldStyle)

        for r in result:

            row += 1

            column = 0

            for data in r :

                worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else datetime_format if type(data) is datetime.datetime else default_format)

                column += 1

        workbook.save(TMP_FILE)

    except Exception:

        traceback.print_exc()

        print "Could not execute query"

def getMailAttachmentPending():

    selectSql = PENDING_ORDER_SQL

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        workbook = xlwt.Workbook()

        worksheet = workbook.add_sheet("Pending Orders")

        boldStyle = xlwt.XFStyle()

        f = xlwt.Font()

        f.bold = True

        boldStyle.font = f

        column = 0

        row = 0

        worksheet.write(row, 0, 'Counter Code', boldStyle)

        worksheet.write(row, 1, 'Counter Name', boldStyle)

        worksheet.write(row, 2, 'Warehouse', boldStyle)

        worksheet.write(row, 3, 'Order Id', boldStyle)

        worksheet.write(row, 4, 'Order Date', boldStyle)

        worksheet.write(row, 5, 'Shipped Date', boldStyle)

        worksheet.write(row, 6, 'Order Status', boldStyle)

        worksheet.write(row, 7, 'Expected Delivery Date', boldStyle)

        worksheet.write(row, 8, 'Product', boldStyle)

        worksheet.write(row, 9, 'Qty', boldStyle)

        worksheet.write(row, 10, 'Value', boldStyle)

        for r in result:

            row += 1

            column = 0

            for data in r :

                worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)

                column += 1

        workbook.save(TMP_FILE)

    except Exception:

            traceback.print_exc()

            print "Could not execute query"

def getMailAttachmenAgeing():

    selectSql = STOCK_REPORT_SQL

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        workbook = xlwt.Workbook()

        worksheet = workbook.add_sheet("Stock Aeging Orders")

        boldStyle = xlwt.XFStyle()

        f = xlwt.Font()

        f.bold = True

        boldStyle.font = f

        column = 0

        row = 0

        worksheet.write(row, 0, 'Counter Code', boldStyle)

        worksheet.write(row, 1, 'Counter Name', boldStyle)

        worksheet.write(row, 2, 'Order Id', boldStyle)

        worksheet.write(row, 3, 'Order Date', boldStyle)

        worksheet.write(row, 4, 'Delivered Date', boldStyle)

        worksheet.write(row, 5, 'Order Status', boldStyle)

        worksheet.write(row, 6, 'Product', boldStyle)

        worksheet.write(row, 7, 'Qty', boldStyle)

        worksheet.write(row, 8, 'Value', boldStyle)

        worksheet.write(row, 9, 'Ageing(Days)', boldStyle)

        for r in result:

            row += 1

            column = 0

            for data in r :

                worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)

                column += 1

        workbook.save(TMP_FILE)

    except Exception:

        traceback.print_exc()

        print "Could not execute query"

def sendShippedReport():

    mailbody = getMailBody(report_map['shipped'])

    getMailAttachmentShipped() 

    sendmail(recipients, mailbody, 'dtrshipped_report.xls', 'DTR Shipped Product Report')

def sendTertiaryReport():

    mailbody = getMailBody(report_map['tertiary'])

    getMailAttachmentTertiary()

    sendmail(recipients, mailbody, 'dtrtertiary_report.xls', 'DTR Tertiary Report')

def sendPendingReport():

    mailbody = getMailBody(report_map['pending'])

    getMailAttachmentPending()

    sendmail(recipients, mailbody, 'dtrpending_report.xls', 'DTR Pending Orders Report')

def getSpiceStockAgeingReport():

    mailbody = getMailBody(report_map['ageing'])

    getMailAttachmenAgeing()

    sendmail(recipients, mailbody, 'dtrstockageing_report.xls', 'DTR Spice Stock Ageing Report')

def getMisReport():

    mailbody = getMailBody(report_map['mis']) + "<br>" + "<br>" + getMailBody(report_map['mis_prod'])

    getMailAttachmentMis()

    sendmail(recipients, mailbody, 'dtrcountersale_report.xls', 'DTR Counterwise Sales Report')

def getMailBody(reportmap):

    conn = getDbConnection()

    mailBodyTemplate="""

    <html>

        <body>

        <table cellspacing="0" border="1" style="text-align:right">

            <thead>

                <tr>

                    <th colspan="{4}" style="text-align:center">{2}</th>

                    <th colspan="{5}" style="text-align:center">Total Amount - {0}</th>

                </tr>

                <tr>

                    {3}

                </tr>

            </thead>

            <tbody>

                {1}

            </tbody>

        </table>

        </body>

    </html>

    """

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(reportmap['query'])

        result = cursor.fetchall()

        grandTotal = 0

        tbody=[]

        headerLen = len(reportmap['headers'])

        for rowindex in range(-1, len(result)-1):

            row = result[rowindex]

            nullColumn = -1

            tag="<td>"

            closeTag="</td>"

            for grColumn in range(0, reportmap['group']):

                if row[grColumn] is None:

                    tag="""<th text-align="right">"""

                    closeTag="</th>"

                    nullColumn = grColumn

                    break

            tbody.append("<tr>")

            for column in range(0, headerLen):      

                tbody.append(tag)

                if nullColumn==column:

                    if nullColumn==0:

                        tbody.append('Grand Total')

                        grandTotal = row[headerLen-1]

                    else:

                        tbody.append('Sub Total')

                elif row[column] is None:

                        tbody.append('')

                else:

                    data = row[column]

                    tbody.append(intWithCommas(int(data)) if type(data) is float else (data if data!=0 else '-'))

                tbody.append(closeTag)

            tbody.append("</tr>")

        tblbody = ''.join([str(x) for x in tbody])

        theader = ''.join(["<th>%s</th>"%header for header in reportmap['headers']])

        return mailBodyTemplate.format(intWithCommas(int(grandTotal)), tblbody, reportmap['title'], theader, (headerLen)/2, (headerLen+1)/2)

    except:

        traceback.print_exc()

        print "Could not execute query"

    return ""

def getMailAttachmentMis():

    selectSql = MIS_REPORT_SQL

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Execute the SQL command

        # Fetch source id.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        workbook = xlwt.Workbook()

        worksheet = workbook.add_sheet("Last Day Sale Report")

        worksheet1 = workbook.add_sheet("MTD Sale Report")

        boldStyle = xlwt.XFStyle()

        f = xlwt.Font()

        f.bold = True

        boldStyle.font = f

        column = 0

        row = 0

        worksheet.write(row, 0, 'Counter Code', boldStyle)

        worksheet.write(row, 1, 'Counter Name', boldStyle)

        worksheet.write(row, 2, 'Warehouse', boldStyle)

        worksheet.write(row, 3, 'Order Id', boldStyle)

        worksheet.write(row, 4, 'Order Date', boldStyle)

        worksheet.write(row, 5, 'Order Status', boldStyle)

        worksheet.write(row, 6, 'Activation Date', boldStyle)

        worksheet.write(row, 7, 'Product', boldStyle)

        worksheet.write(row, 8, 'Qty', boldStyle)

        worksheet.write(row, 9, 'Value', boldStyle)

        worksheet1.write(row, 0, 'Counter Code', boldStyle)

        worksheet1.write(row, 1, 'Counter Name', boldStyle)

        worksheet1.write(row, 2, 'Warehouse', boldStyle)

        worksheet1.write(row, 3, 'Order Id', boldStyle)

        worksheet1.write(row, 4, 'Order Date', boldStyle)

        worksheet1.write(row, 5, 'Order Status', boldStyle)

        worksheet1.write(row, 6, 'Activation Date', boldStyle)

        worksheet1.write(row, 7, 'Product', boldStyle)

        worksheet1.write(row, 8, 'Qty', boldStyle)

        worksheet1.write(row, 9, 'Value', boldStyle)

        yesterday = datetime.date.fromordinal(datetime.date.today().toordinal()-1)

        for r in result:

            row += 1

            column = 0

            for data in r :

                if data is not None:

                    if r[4] == yesterday:

                        worksheet.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)

                    worksheet1.write(row, column, int(data) if type(data) is float else data, date_format if type(data) is datetime.date else default_format)

                column += 1

        workbook.save(TMP_FILE)

    except Exception:

        traceback.print_exc()

        print "Could not execute query"

def main():

    sendShippedReport()

    #print getMailBody()

    sendTertiaryReport()

    sendPendingReport()

    getSpiceStockAgeingReport()

    getMisReport()

def intWithCommas(x):

    if type(x) not in [type(0), type(0L)]:

        raise TypeError("Parameter must be an integer.")

    if x==0:

        return '-'

    if x < 0:

        return '-' + intWithCommas(-x)

    result = ''

    while x >= 1000:

        x, r = divmod(x, 1000)

        result = ",%03d%s" % (r, result)

    return "%d%s" % (x, result)

if __name__ == '__main__':

    main()