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


DB_HOST = "192.168.190.113"
DB_USER = "root"
DB_PASSWORD = "shop2020"
DB_NAME = "transaction"

FILENAME = "/tmp/lastMaxOrderId"
MAILTO = ['anupam.singh@shop2020.in', 'amar.kumar@shop2020.in', 'pankaj.kankar@shop2020.in', 'varun.gupta@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
    # Prepare SQL query.
    selectSql = "select o.id, o.cod, 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]
            paymentStatus = r[2]
            if orderId > int(maxOrderId) :
                maxOrderId = str(orderId)
            if isCod :
                codFlag = True
            else :
                prepaidFlag = True
            if paymentStatus in [1,2] :
                paymentOK = True
            print maxOrderId, isCod, paymentStatus
    except Exception as e:
            print "Error: unable to fetch data"
            print e
    
    return maxOrderId, codFlag, prepaidFlag, paymentOK
    
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 = getOrderTableStatus(orderIdinFile)
    sendmailFlag = False
    if not prepaidFlag :
        subject = 'No prepaid orders were placed since last check'
        sendmailFlag = True
    if not codFlag :
        subject = 'No COD orders were placed since last check'
        sendmailFlag = True
    if not paymentOK :
        subject = 'There were no successful payments since last check'
        sendmailFlag = True
    if newOrderId == orderIdinFile:
        subject = 'WARNING : No orders were placed since last check'
        sendmailFlag = True
    else :
        try :
            f = open(FILENAME, "w")
            f.write(str(newOrderId))
            f.close()
        except Exception as e :
            print e
    if sendmailFlag :
        sendmail(subject)

if __name__ == '__main__':
    main()