Subversion Repositories SmartDukaan

Rev

Rev 14454 | Rev 14462 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 14454 Rev 14460
Line 2... Line 2...
2 
Created on Mar 13, 2015
 2 
Created on Mar 13, 2015
3 
 
 3 
 
4 
@author: amit
 4 
@author: amit
5 
'''
 5 
'''
6 
from datetime import date, datetime, timedelta
 6 
from datetime import date, datetime, timedelta
- 
 
 7 
from docutils.nodes import row
- 
 
 8 
from docutils.parsers.rst.directives import encoding
7 
from dtr.storage.Mysql import getOrdersAfterDate
 9 
from dtr.storage.Mysql import getOrdersAfterDate
8 
from email import encoders
 10 
from email import encoders
9 
from email.mime.base import MIMEBase
 11 
from email.mime.base import MIMEBase
10 
from email.mime.multipart import MIMEMultipart
 12 
from email.mime.multipart import MIMEMultipart
11 
from email.mime.text import MIMEText
 13 
from email.mime.text import MIMEText
12 
from pymongo.mongo_client import MongoClient
 14 
from pymongo.mongo_client import MongoClient
- 
 
 15 
from xlrd import open_workbook
- 
 
 16 
from xlwt.Workbook import Workbook
13 
import MySQLdb
 17 
import MySQLdb
- 
 
 18 
from xlutils.copy import copy
14 
import smtplib
 19 
import smtplib
15 
import time
 20 
import time
16 
import xlwt
 21 
import xlwt
- 
 
 22 
#from xlwt import
17 
 
 23 
 
18 
client = MongoClient('mongodb://localhost:27017/')
 24 
client = MongoClient('mongodb://localhost:27017/')
19 
 
 25 
 
20 
SENDER = "cnc.center@shop2020.in"
 26 
SENDER = "cnc.center@shop2020.in"
21 
PASSWORD = "5h0p2o2o"
 27 
PASSWORD = "5h0p2o2o"
22 
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
 28 
SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()
23 
SMTP_SERVER = "smtp.gmail.com"
 29 
SMTP_SERVER = "smtp.gmail.com"
24 
SMTP_PORT = 587
30 
SMTP_PORT = 587
25 
 
 31 
 
- 
 
 32 
XLS_FILENAME = "dtrsourcesreco.xls"
- 
 
 33 
boldStyle = xlwt.XFStyle()
- 
 
 34 
f = xlwt.Font()
- 
 
 35 
f.bold = True
- 
 
 36 
boldStyle.font = f
- 
 
 37 
i = -1
26 
def generateFlipkartReco():
 38 
def generateFlipkartReco():
- 
 
 39 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)
- 
 
 40 
    db = client.Dtr
- 
 
 41 
    rb = open_workbook(XLS_FILENAME)
- 
 
 42 
    wb = copy(rb)
- 
 
 43 
    #wb = xlwt.Workbook()
- 
 
 44 
    row = 0
- 
 
 45 
    worksheet = wb.add_sheet("Flipkart")
- 
 
 46 
    worksheet.write(row, inc(), 'Order Id', boldStyle)
- 
 
 47 
    worksheet.write(row, inc(), 'User Id', boldStyle)
- 
 
 48 
    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)
- 
 
 49 
    worksheet.write(row, inc(), 'Product Title', boldStyle)
- 
 
 50 
    worksheet.write(row, inc(), 'Price', boldStyle)
- 
 
 51 
    worksheet.write(row, inc(), 'Quantity', boldStyle)
- 
 
 52 
    worksheet.write(row, inc(), 'Status', boldStyle)
- 
 
 53 
    worksheet.write(row, inc(), 'Detailed Status', boldStyle)
- 
 
 54 
    worksheet.write(row, inc(), 'Cashback Status', boldStyle)
- 
 
 55 
    worksheet.write(row, inc(), 'Our CashBack', boldStyle)
- 
 
 56 
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
- 
 
 57 
    worksheet.write(row, inc(), 'SubtagId', boldStyle)
- 
 
 58 
    worksheet.write(row, inc(), 'Category', boldStyle)
- 
 
 59 
    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)
- 
 
 60 
    worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)
- 
 
 61 
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
- 
 
 62 
    for row1 in curs:
- 
 
 63 
        orderId = row1[0]
- 
 
 64 
        order = db.merchantOrder.find_one({"orderId":orderId})
- 
 
 65 
        if order is None:
- 
 
 66 
            continue
- 
 
 67 
        print order["subTagId"]
- 
 
 68 
        print order
- 
 
 69 
        saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))
- 
 
 70 
        #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))
- 
 
 71 
        affs = list(db.flipkartOrderAffiliateInfo.find({"subTagId":order["subTagId"], "saleDateInt":saleTime}))
27 
    pass
 72
- 
 
 73 
        for subOrder in order['subOrders']:
- 
 
 74 
            row += 1
- 
 
 75 
            global i
- 
 
 76 
            i=-1
- 
 
 77 
            worksheet.write(row, inc(), orderId)
- 
 
 78 
            worksheet.write(row, inc(), row1[1])
- 
 
 79 
            worksheet.write(row, inc(), order['merchantOrderId'])
- 
 
 80 
            worksheet.write(row, inc(), subOrder['productTitle'])
- 
 
 81 
            worksheet.write(row, inc(), subOrder['amountPaid'])
- 
 
 82 
            worksheet.write(row, inc(), subOrder['quantity'])
- 
 
 83 
            worksheet.write(row, inc(), subOrder['status'])
- 
 
 84 
            worksheet.write(row, inc(), subOrder['detailedStatus'])
- 
 
 85 
            worksheet.write(row, inc(), subOrder['cashBackStatus'])
- 
 
 86 
            worksheet.write(row, inc(), subOrder['cashBackAmount'])
- 
 
 87 
            worksheet.write(row, inc(), subOrder['placedOn'])
- 
 
 88 
            worksheet.write(row, inc(), order['subTagId'])
- 
 
 89 
            for aff in affs:
- 
 
 90 
                if aff['productCode']== subOrder['productCode']:
- 
 
 91 
                    worksheet.write(row, inc(), aff['category'])
- 
 
 92 
                    worksheet.write(row, inc(), aff['payOut'])
- 
 
 93 
                    worksheet.write(row, inc(), aff['saleAmount'])
- 
 
 94 
                    worksheet.write(row, inc(), aff['saleDate'])
- 
 
 95 
                    break
- 
 
 96
- 
 
 97 
 
- 
 
 98 
        wb.save(XLS_FILENAME)
28 
def generateSnapDealReco():
 99 
def generateSnapDealReco():
29 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=10), 3)
 100 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
30 
    db = client.Dtr
 101 
    db = client.Dtr
31
102
32 
    workbook = xlwt.Workbook()
 103 
    workbook = xlwt.Workbook()
33 
    worksheet = workbook.add_sheet("Snapdeal Reco")
 104 
    worksheet = workbook.add_sheet("Snapdeal")
34 
    boldStyle = xlwt.XFStyle()
 - 
 
35 
    f = xlwt.Font()
 - 
 
36 
    f.bold = True
 - 
 
37 
    boldStyle.font = f
 - 
 
38 
    column = 0
 105 
    column = 0
39 
    row = 0
 106 
    row = 0
40
107
41 
    worksheet.write(row, 0, 'Order Id', boldStyle)
 108 
    worksheet.write(row, 0, 'Order Id', boldStyle)
42 
    worksheet.write(row, 1, 'User Id', boldStyle)
 109 
    worksheet.write(row, 1, 'User Id', boldStyle)
Line 83... Line 150...
83 
            if len(aff)>0:
 150 
            if len(aff)>0:
84 
                worksheet.write(row, 12, aff[0]['adId'])
 151 
                worksheet.write(row, 12, aff[0]['adId'])
85 
                worksheet.write(row, 13, aff[0]['payOut'])
 152 
                worksheet.write(row, 13, aff[0]['payOut'])
86 
                worksheet.write(row, 14, aff[0]['saleAmount'])
 153 
                worksheet.write(row, 14, aff[0]['saleAmount'])
87 
                worksheet.write(row, 15, aff[0]['saleDate'])
 154 
                worksheet.write(row, 15, aff[0]['saleDate'])
88 
        workbook.save("snapdealreco.xls")
 155 
        workbook.save(XLS_FILENAME)
89
156
90 
def sendmail(email, message, title):
 157 
def sendmail(email, message, title):
91 
    if email == "":
 158 
    if email == "":
92 
        return
 159 
        return
93 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 160 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
Line 102... Line 169...
102 
    html_msg = MIMEText(message, 'html')
 169 
    html_msg = MIMEText(message, 'html')
103 
    msg.attach(html_msg)
 170 
    msg.attach(html_msg)
104
171
105 
    #snapdeal more to be added here
 172 
    #snapdeal more to be added here
106 
    snapdeal = MIMEBase('application', 'vnd.ms-excel')
 173 
    snapdeal = MIMEBase('application', 'vnd.ms-excel')
107 
    snapdeal.set_payload(file("snapdealreco.xls").read())
 174 
    snapdeal.set_payload(file(XLS_FILENAME).read())
108 
    encoders.encode_base64(snapdeal)
 175 
    encoders.encode_base64(snapdeal)
109 
    snapdeal.add_header('Content-Disposition', 'attachment;filename=snapdealreco.xls')
 176 
    snapdeal.add_header('Content-Disposition', 'attachment;filename=' + XLS_FILENAME)
110 
    msg.attach(snapdeal)
 177 
    msg.attach(snapdeal)
111 
 
 178 
 
112 
 
 179 
 
113 
    email.append('amit.gupta@shop2020.in')
 180 
    email.append('amit.gupta@shop2020.in')
114 
    MAILTO = email
181 
    MAILTO = email
115 
    mailServer.login(SENDER, PASSWORD)
 182 
    mailServer.login(SENDER, PASSWORD)
116 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
 183 
    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())
117 
 
 184 
 
118
185
- 
 
 186
- 
 
 187 
def inc():
- 
 
 188 
    global i
- 
 
 189 
    i+=1
- 
 
 190 
    return i
- 
 
 191
119 
def main():
 192 
def main():
120 
    generateSnapDealReco()
 193 
    generateSnapDealReco()
- 
 
 194 
    generateFlipkartReco()
121 
    sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")
 195 
    sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")
122 
 
 196 
 
123 
if __name__ == '__main__':
 197 
if __name__ == '__main__':
124 
    main()
 - 
 
125 

            198
            
    main()

          

          

            -
            
 

            199
            
    

          

          

            -
            
 

            200
            

          

          

            126
            

            201