Subversion Repositories SmartDukaan

Rev

Rev 5134 | Rev 5911 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
4998 anupam.sin 1 
'''
2 
Created on 10-Apr-2012
3 
 
4 
@author: anupam
5 
'''
6 
 
7 
#!/usr/bin/python
8 
 
9 
"""
10 
 This Script polls transaction database to see if a new order was placed since last time.
11 
"""
12 
 
13 
import MySQLdb
14 
import sys
15 
import smtplib
16 
 
17 
from email import encoders
18 
from email.mime.text import MIMEText
19 
from email.mime.base import MIMEBase
20 
from email.mime.multipart import MIMEMultipart
21 
from datetime import date
5011 anupam.sin 22 
from datetime import datetime, timedelta
4998 anupam.sin 23 
 
24 
 
5134 anupam.sin 25 
DB_HOST = "192.168.190.114"
4998 anupam.sin 26 
DB_USER = "root"
27 
DB_PASSWORD = "shop2020"
28 
DB_NAME = "transaction"
29 
 
30 
FILENAME = "/tmp/lastMaxOrderId"
5704 rajveer 31 
MAILTO = ['anupam.singh@shop2020.in', 'amar.kumar@shop2020.in', 'pankaj.kankar@shop2020.in']
4998 anupam.sin 32 
SENDER = "help@shop2020.in"
33 
PASSWORD = "5h0p2o2o"
34 
SMTP_SERVER = "smtp.gmail.com"
35 
SMTP_PORT = 587
36 
 
37 
def getDbConnection():
38 
    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
39 
 
40 
def closeConnection(conn):
41 
    conn.close()
42 
 
43 
def getOrderTableStatus(lastSavedOrderId):
44 
    maxOrderId = lastSavedOrderId
45 
    codFlag = False
46 
    prepaidFlag = False
47 
    paymentOK = False
5006 anupam.sin 48 
    orderStatusFlag = False
4998 anupam.sin 49 
    # Prepare SQL query.
5006 anupam.sin 50 
    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
4998 anupam.sin 51 
    conn = getDbConnection()
52 
    try:
53 
        # prepare a cursor object using cursor() method
54 
        cursor = conn.cursor()
55 
 
56 
        # Fetch.
57 
        cursor.execute(selectSql)
58 
        result = cursor.fetchall()
59 
        for r in result :
60 
            orderId = r[0]
61 
            isCod = r[1]
5006 anupam.sin 62 
            orderStatus = r[2]
63 
            paymentStatus = r[3]
4998 anupam.sin 64 
            if orderId > int(maxOrderId) :
65 
                maxOrderId = str(orderId)
66 
            if isCod :
67 
                codFlag = True
68 
            else :
69 
                prepaidFlag = True
5006 anupam.sin 70 
            if paymentStatus in [0,1,2,4] :
4998 anupam.sin 71 
                paymentOK = True
5006 anupam.sin 72 
            if orderStatus > 1 :
73 
                orderStatusFlag = True
4998 anupam.sin 74 
    except Exception as e:
75 
            print "Error: unable to fetch data"
76 
            print e
77 
 
5006 anupam.sin 78 
    return maxOrderId, codFlag, prepaidFlag, paymentOK, orderStatusFlag
4998 anupam.sin 79 
 
80 
def sendmail(subject):
81 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
82 
    mailServer.ehlo()
83 
    mailServer.starttls()
84 
    mailServer.ehlo()
85 
 
86 
    # Create the container (outer) email message.
87 
    msg = MIMEMultipart()
88 
    msg['Subject'] = subject + ' - ' + date.today().isoformat()
89 
    msg['From'] = "hadb2@saholic.com"
90 
    msg['To'] = 'recipients@saholic.com'
91 
    msg.preamble = subject + ' - ' + date.today().isoformat()
92 
 
93 
    mailServer.login(SENDER, PASSWORD)
94 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
95 
 
96 
def main():
97 
    try :
98 
        f = open(FILENAME, "r")
99 
        orderIdinFile = f.read()
100 
        f.close()
101 
    except Exception as e :
102 
        subject = 'File not found'
103 
        sendmail(subject)
104 
        exit(1)
105 
 
5006 anupam.sin 106 
    newOrderId, codFlag, prepaidFlag, paymentOK, orderStatusFlag = getOrderTableStatus(orderIdinFile)
5014 anupam.sin 107 
    print 'NewOrderId: ' + str(newOrderId) + ' CODFLAG: ' + str(codFlag) + ' PREPAIDFLAG: ' + str(prepaidFlag) + ' PAYMENTOK : ' + str(paymentOK) + ' ORDERSTATUSFLAG: ' + str(orderStatusFlag)
4998 anupam.sin 108 
    sendmailFlag = False
5015 anupam.sin 109 
    subject = ''
5011 anupam.sin 110 
    timeNow = datetime.now()
111 
    halfHourBack = timeNow - timedelta(minutes=30)
5013 anupam.sin 112 
    timeString = str(halfHourBack.time().strftime('%H:%M')) + ' and ' + str(timeNow.time().strftime('%H:%M'))
5011 anupam.sin 113 
 
4998 anupam.sin 114 
    if not prepaidFlag :
5011 anupam.sin 115 
        subject = 'No prepaid orders were placed between ' + timeString
4998 anupam.sin 116 
        sendmailFlag = True
117 
    if not codFlag :
5011 anupam.sin 118 
        subject = 'No COD orders were placed between ' + timeString
4998 anupam.sin 119 
        sendmailFlag = True
120 
    if not paymentOK :
5011 anupam.sin 121 
        subject = 'There were no successful payments between ' + timeString
4998 anupam.sin 122 
        sendmailFlag = True
5006 anupam.sin 123 
    if not orderStatusFlag :
5011 anupam.sin 124 
        subject = 'There were no orders placed between ' + timeString + ' whose status was other than PAYMENT_PENDING and PAYMENT_FAILED'
5006 anupam.sin 125 
        sendmailFlag = True
4998 anupam.sin 126 
    if newOrderId == orderIdinFile:
5011 anupam.sin 127 
        subject = 'WARNING : No orders were placed between ' + timeString
4998 anupam.sin 128 
        sendmailFlag = True
129 
    else :
130 
        try :
131 
            f = open(FILENAME, "w")
132 
            f.write(str(newOrderId))
133 
            f.close()
134 
        except Exception as e :
135 
            print e
5013 anupam.sin 136 
    print str(timeNow.time().strftime('%H:%M')) + ' SUBJECT : ' + subject
4998 anupam.sin 137 
    if sendmailFlag :
138 
        sendmail(subject)
139 
 
140 
if __name__ == '__main__':
141 
    main()