Subversion Repositories SmartDukaan

Rev

Rev 5006 | Rev 5013 | 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 
 
25 
DB_HOST = "192.168.190.113"
26 
DB_USER = "root"
27 
DB_PASSWORD = "shop2020"
28 
DB_NAME = "transaction"
29 
 
30 
FILENAME = "/tmp/lastMaxOrderId"
31 
MAILTO = ['anupam.singh@shop2020.in', 'amar.kumar@shop2020.in', 'pankaj.kankar@shop2020.in', 'varun.gupta@shop2020.in']
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)
4998 anupam.sin 107 
    sendmailFlag = False
5011 anupam.sin 108 
 
109 
    timeNow = datetime.now()
110 
    halfHourBack = timeNow - timedelta(minutes=30)
111 
    timeString = str(timeNow.time().strftime('%H:%M')) + ' and ' + str(halfHourBack.time().strftime('%H:%M'))
112 
 
4998 anupam.sin 113 
    if not prepaidFlag :
5011 anupam.sin 114 
        subject = 'No prepaid orders were placed between ' + timeString
4998 anupam.sin 115 
        sendmailFlag = True
116 
    if not codFlag :
5011 anupam.sin 117 
        subject = 'No COD orders were placed between ' + timeString
4998 anupam.sin 118 
        sendmailFlag = True
119 
    if not paymentOK :
5011 anupam.sin 120 
        subject = 'There were no successful payments between ' + timeString
4998 anupam.sin 121 
        sendmailFlag = True
5006 anupam.sin 122 
    if not orderStatusFlag :
5011 anupam.sin 123 
        subject = 'There were no orders placed between ' + timeString + ' whose status was other than PAYMENT_PENDING and PAYMENT_FAILED'
5006 anupam.sin 124 
        sendmailFlag = True
4998 anupam.sin 125 
    if newOrderId == orderIdinFile:
5011 anupam.sin 126 
        subject = 'WARNING : No orders were placed between ' + timeString
4998 anupam.sin 127 
        sendmailFlag = True
128 
    else :
129 
        try :
130 
            f = open(FILENAME, "w")
131 
            f.write(str(newOrderId))
132 
            f.close()
133 
        except Exception as e :
134 
            print e
135 
    if sendmailFlag :
136 
        sendmail(subject)
137 
 
138 
if __name__ == '__main__':
139 
    main()