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()