Subversion Repositories SmartDukaan

'''

Created on 10-Apr-2012

@author: anupam

'''

#!/usr/bin/python

"""

 This Script polls transaction database to see if a new order was placed since last time.

"""

import MySQLdb

import sys

import smtplib

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

from datetime import datetime, timedelta

DB_HOST = "192.168.190.114"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "transaction"

FILENAME = "/root/lastMaxOrderId"

MAILTO = ['kshitij.sood@shop2020.in', 'manish.sharma@shop2020.in', 'rajveer.singh@shop2020.in']

SENDER = "help@shop2020.in"

PASSWORD = "5h0p2o2o"

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

def getDbConnection():

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

def closeConnection(conn):

    conn.close()

def getOrderTableStatus(lastSavedOrderId):

    maxOrderId = lastSavedOrderId

    codFlag = False

    prepaidFlag = False

    paymentOK = False

    orderStatusFlag = False

    # Prepare SQL query.

    selectSql = "select o.id, o.cod, o.status, p.status from transaction.`order` o join payment.payment p on (o.transaction_id = p.merchantTxnId) where o.id > " + lastSavedOrderId

    conn = getDbConnection()

    try:

        # prepare a cursor object using cursor() method

        cursor = conn.cursor()

        # Fetch.

        cursor.execute(selectSql)

        result = cursor.fetchall()

        for r in result :

            orderId = r[0]

            isCod = r[1]

            orderStatus = r[2]

            paymentStatus = r[3]

            if orderId > int(maxOrderId) :

                maxOrderId = str(orderId)

            if isCod :

                codFlag = True

            else :

                prepaidFlag = True

            if paymentStatus in [0,1,2,4] :

                paymentOK = True

            if orderStatus > 1 :

                orderStatusFlag = True

    except Exception as e:

            print "Error: unable to fetch data"

            print e

    return maxOrderId, codFlag, prepaidFlag, paymentOK, orderStatusFlag

def sendmail(subject):

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = subject + ' - ' + date.today().isoformat()

    msg['From'] = "hadb2@saholic.com"

    msg['To'] = 'recipients@saholic.com'

    msg.preamble = subject + ' - ' + date.today().isoformat() 

    mailServer.login(SENDER, PASSWORD)

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

def main():

    try :

        f = open(FILENAME, "r")

        orderIdinFile = f.read()

        f.close()

    except Exception as e :

        subject = 'File not found'

        sendmail(subject)

        exit(1)

    newOrderId, codFlag, prepaidFlag, paymentOK, orderStatusFlag = getOrderTableStatus(orderIdinFile)

    print 'NewOrderId: ' + str(newOrderId) + ' CODFLAG: ' + str(codFlag) + ' PREPAIDFLAG: ' + str(prepaidFlag) + ' PAYMENTOK : ' + str(paymentOK) + ' ORDERSTATUSFLAG: ' + str(orderStatusFlag)

    sendmailFlag = False

    subject = ''

    timeNow = datetime.now()

    halfHourBack = timeNow - timedelta(minutes=30)

    timeString = str(halfHourBack.time().strftime('%H:%M')) + ' and ' + str(timeNow.time().strftime('%H:%M'))

    if not prepaidFlag :

        subject = 'No prepaid orders were placed between ' + timeString

        sendmailFlag = True

    if not codFlag :

        subject = 'No COD orders were placed between ' + timeString

        sendmailFlag = True

    if not paymentOK :

        subject = 'There were no successful payments between ' + timeString

        sendmailFlag = True

    if not orderStatusFlag :

        subject = 'There were no orders placed between ' + timeString + ' whose status was other than PAYMENT_PENDING and PAYMENT_FAILED'

        sendmailFlag = True

    if newOrderId == orderIdinFile:

        subject = 'WARNING : No orders were placed between ' + timeString

        sendmailFlag = True

    else :

        try :

            f = open(FILENAME, "w")

            f.write(str(newOrderId))

            f.close()

        except Exception as e :

            print e

    print str(timeNow.time().strftime('%H:%M')) + ' SUBJECT : ' + subject

    if sendmailFlag :

        sendmail(subject)

if __name__ == '__main__':

    main()