| 13884 |
kshitij.so |
1 |
import pymongo
|
|
|
2 |
import xlwt
|
|
|
3 |
from datetime import datetime
|
|
|
4 |
from shop2020.utils import EmailAttachmentSender
|
|
|
5 |
from shop2020.utils.EmailAttachmentSender import get_attachment_part
|
|
|
6 |
from email.mime.text import MIMEText
|
|
|
7 |
import email
|
|
|
8 |
from email.mime.multipart import MIMEMultipart
|
|
|
9 |
import email.encoders
|
|
|
10 |
import smtplib
|
|
|
11 |
|
|
|
12 |
con = None
|
|
|
13 |
|
|
|
14 |
class __SkuInfo:
|
|
|
15 |
|
|
|
16 |
def __init__(self, _id, skuBundleId, category_id, mrp, available_price, source_id, rank, maxNlc, minNlc, schemeAmount, minDiscount, \
|
| 13906 |
kshitij.so |
17 |
maxDiscount, discountType, dp, nlcPoints, bestSellerPoints, totalPoints,in_stock,maxprice,showDeal):
|
| 13884 |
kshitij.so |
18 |
self._id = _id
|
|
|
19 |
self.skuBundleId = skuBundleId
|
|
|
20 |
self.category_id = category_id
|
|
|
21 |
self.mrp = mrp
|
|
|
22 |
self.available_price = available_price
|
|
|
23 |
self.source_id = source_id
|
|
|
24 |
self.rank = rank
|
|
|
25 |
self.maxNlc = maxNlc
|
|
|
26 |
self.minNlc = minNlc
|
|
|
27 |
self.schemeAmount = schemeAmount
|
|
|
28 |
self.minDiscount = minDiscount
|
|
|
29 |
self.maxDiscount = maxDiscount
|
|
|
30 |
self.discountType = discountType
|
|
|
31 |
self.dp = dp
|
|
|
32 |
self.nlcPoints = nlcPoints
|
|
|
33 |
self.bestSellerPoints = bestSellerPoints
|
|
|
34 |
self.totalPoints = totalPoints
|
|
|
35 |
self.in_stock = in_stock
|
| 13906 |
kshitij.so |
36 |
self.maxprice = maxprice
|
| 13884 |
kshitij.so |
37 |
self.showDeal = showDeal
|
|
|
38 |
|
|
|
39 |
def get_mongo_connection(host='localhost', port=27017):
|
|
|
40 |
global con
|
|
|
41 |
if con is None:
|
|
|
42 |
print "Establishing connection %s host and port %d" %(host,port)
|
|
|
43 |
try:
|
|
|
44 |
con = pymongo.MongoClient(host, port)
|
|
|
45 |
except Exception, e:
|
|
|
46 |
print e
|
|
|
47 |
return None
|
|
|
48 |
return con
|
|
|
49 |
|
| 13904 |
kshitij.so |
50 |
def writeSheet():
|
|
|
51 |
p = []
|
| 13884 |
kshitij.so |
52 |
data = get_mongo_connection().Catalog.Deals.find().sort([('totalPoints',pymongo.DESCENDING)])
|
|
|
53 |
print data.count()
|
|
|
54 |
for x in data:
|
|
|
55 |
s_info = __SkuInfo(x['_id'], x['skuBundleId'], x['category_id'],x['mrp'],x['available_price'],x['source_id'],x['rank'],x['maxNlc'], \
|
|
|
56 |
x['minNlc'], x['schemeAmount'],x['minDiscount'],x['maxDiscount'],x['discountType'],x['dp'],x['nlcPoints'],x['bestSellerPoints'], \
|
| 13906 |
kshitij.so |
57 |
x['totalPoints'],x['in_stock'],x['maxprice'],x['showDeal'])
|
| 13884 |
kshitij.so |
58 |
p.append(s_info)
|
| 13904 |
kshitij.so |
59 |
|
| 13884 |
kshitij.so |
60 |
wbk = xlwt.Workbook()
|
|
|
61 |
sheet = wbk.add_sheet('Deals')
|
|
|
62 |
xstr = lambda s: s or ""
|
|
|
63 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
64 |
|
|
|
65 |
excel_integer_format = '0'
|
|
|
66 |
integer_style = xlwt.XFStyle()
|
|
|
67 |
integer_style.num_format_str = excel_integer_format
|
|
|
68 |
|
|
|
69 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
70 |
sheet.write(0, 1, "Catalog Item Id", heading_xf)
|
|
|
71 |
sheet.write(0, 2, "Category", heading_xf)
|
|
|
72 |
sheet.write(0, 3, "Brand", heading_xf)
|
|
|
73 |
sheet.write(0, 4, "Product Name", heading_xf)
|
|
|
74 |
sheet.write(0, 5, "Item Status", heading_xf)
|
|
|
75 |
sheet.write(0, 6, "Stock Status", heading_xf)
|
|
|
76 |
sheet.write(0, 7, "MarketPlace", heading_xf)
|
|
|
77 |
sheet.write(0, 8, "MarketPlace Identifier", heading_xf)
|
|
|
78 |
sheet.write(0, 9, "MarketPlace Secondary Identifier", heading_xf)
|
|
|
79 |
sheet.write(0, 10, "Product Name (Marketplace)", heading_xf)
|
|
|
80 |
sheet.write(0, 11, "Url", heading_xf)
|
|
|
81 |
sheet.write(0, 12, "Price", heading_xf)
|
|
|
82 |
sheet.write(0, 13, "Mrp", heading_xf)
|
|
|
83 |
sheet.write(0, 14, "DP", heading_xf)
|
|
|
84 |
sheet.write(0, 15, "Scheme Amount", heading_xf)
|
|
|
85 |
sheet.write(0, 16, "Discount Type", heading_xf)
|
|
|
86 |
sheet.write(0, 17, "Min Discount", heading_xf)
|
|
|
87 |
sheet.write(0, 18, "Max Discount", heading_xf)
|
|
|
88 |
sheet.write(0, 19, "Max Nlc", heading_xf)
|
|
|
89 |
sheet.write(0, 20, "Min Nlc", heading_xf)
|
|
|
90 |
sheet.write(0, 21, "Max Price", heading_xf)
|
|
|
91 |
sheet.write(0, 22, "Rank", heading_xf)
|
|
|
92 |
sheet.write(0, 23, "Nlc Points", heading_xf)
|
|
|
93 |
sheet.write(0, 24, "Best Seller Points", heading_xf)
|
|
|
94 |
sheet.write(0, 25, "Total Points", heading_xf)
|
|
|
95 |
sheet.write(0, 26, "Show Deals", heading_xf)
|
|
|
96 |
|
|
|
97 |
it = 1
|
|
|
98 |
for x in p:
|
|
|
99 |
y = list(get_mongo_connection().Catalog.MasterData.find({'_id':x._id}))
|
|
|
100 |
sheet.write(it, 0, x._id)
|
|
|
101 |
sheet.write(it, 1, x.skuBundleId)
|
|
|
102 |
if x.category_id == 3:
|
|
|
103 |
category = 'Mobiles'
|
|
|
104 |
else:
|
|
|
105 |
category = 'Tablets'
|
|
|
106 |
sheet.write(it, 2, category)
|
|
|
107 |
sheet.write(it, 3, y[0]['brand'])
|
|
|
108 |
sheet.write(it, 4, y[0]['product_name'])
|
|
|
109 |
if y[0]['status'] ==1:
|
|
|
110 |
sheet.write(it, 5, 'Active')
|
|
|
111 |
else:
|
|
|
112 |
sheet.write(it, 5, 'EOL')
|
|
|
113 |
if y[0]['in_stock'] ==1:
|
|
|
114 |
sheet.write(it, 6, 'In Stock')
|
|
|
115 |
else:
|
|
|
116 |
sheet.write(it, 6, 'Out Of Stock')
|
|
|
117 |
sheet.write(it, 7, y[0]['source'])
|
|
|
118 |
sheet.write(it, 8, y[0]['identifier'])
|
|
|
119 |
sheet.write(it, 9, y[0]['secondaryIdentifier'])
|
|
|
120 |
sheet.write(it, 10, y[0]['source_product_name'])
|
|
|
121 |
sheet.write(it, 11, y[0]['url'])
|
|
|
122 |
sheet.write(it, 12, y[0]['available_price'])
|
|
|
123 |
sheet.write(it, 13, x.mrp)
|
|
|
124 |
sheet.write(it, 14, x.dp)
|
|
|
125 |
sheet.write(it, 15, x.schemeAmount)
|
|
|
126 |
sheet.write(it, 16, x.discountType)
|
|
|
127 |
sheet.write(it, 17, x.minDiscount)
|
|
|
128 |
sheet.write(it, 18, x.maxDiscount)
|
|
|
129 |
sheet.write(it, 19, x.maxNlc)
|
|
|
130 |
sheet.write(it, 20, x.minNlc)
|
| 13906 |
kshitij.so |
131 |
sheet.write(it, 21, x.maxprice)
|
| 13884 |
kshitij.so |
132 |
sheet.write(it, 22, x.rank)
|
|
|
133 |
sheet.write(it, 23, x.nlcPoints)
|
|
|
134 |
sheet.write(it, 24, x.bestSellerPoints)
|
|
|
135 |
sheet.write(it, 25, x.totalPoints)
|
|
|
136 |
sheet.write(it, 26, x.showDeal)
|
|
|
137 |
it+=1
|
|
|
138 |
filename = "/tmp/deal-data"+str(datetime.now())+".xls"
|
|
|
139 |
wbk.save(filename)
|
|
|
140 |
smtpServer = smtplib.SMTP('localhost')
|
|
|
141 |
smtpServer.set_debuglevel(1)
|
|
|
142 |
sender = 'dtr@shop2020.in'
|
| 13896 |
kshitij.so |
143 |
#recipients = ['kshitij.sood@saholic.com']
|
| 13884 |
kshitij.so |
144 |
msg = MIMEMultipart()
|
|
|
145 |
msg['Subject'] = "DTR Deals data" + ' - ' + str(datetime.now())
|
|
|
146 |
msg['From'] = sender
|
| 13896 |
kshitij.so |
147 |
recipients = ['rajneesh.arora@saholic.com','kshitij.sood@saholic.com','chaitnaya.vats@saholic.com','manoj.kumar@saholic.com']
|
| 13884 |
kshitij.so |
148 |
msg['To'] = ",".join(recipients)
|
|
|
149 |
fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
|
|
|
150 |
fileMsg.set_payload(file(filename).read())
|
|
|
151 |
email.encoders.encode_base64(fileMsg)
|
| 13898 |
kshitij.so |
152 |
fileMsg.add_header('Content-Disposition','attachment;filename=Deal_Sheet_Dtr.xls')
|
| 13884 |
kshitij.so |
153 |
msg.attach(fileMsg)
|
|
|
154 |
try:
|
|
|
155 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
|
|
156 |
print "Successfully sent email"
|
|
|
157 |
except:
|
|
|
158 |
print "Error: unable to send email."
|
|
|
159 |
|
| 13893 |
kshitij.so |
160 |
def sendMail():
|
| 13884 |
kshitij.so |
161 |
writeSheet()
|
|
|
162 |
|
| 13893 |
kshitij.so |
163 |
def main():
|
|
|
164 |
sendMail()
|
|
|
165 |
|
| 13884 |
kshitij.so |
166 |
if __name__=='__main__':
|
|
|
167 |
main()
|