| 12241 |
kshitij.so |
1 |
from elixir import *
|
|
|
2 |
from sqlalchemy.sql import func
|
|
|
3 |
from shop2020.model.v1.order.impl import DataService
|
|
|
4 |
from shop2020.model.v1.order.impl.DataService import Order, LineItem
|
|
|
5 |
from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
|
|
|
6 |
from datetime import datetime, timedelta
|
|
|
7 |
import MySQLdb
|
|
|
8 |
import xlwt
|
|
|
9 |
from operator import itemgetter
|
|
|
10 |
import smtplib
|
|
|
11 |
from email.mime.text import MIMEText
|
|
|
12 |
from email.mime.multipart import MIMEMultipart
|
|
|
13 |
from email import encoders
|
|
|
14 |
from email.mime.text import MIMEText
|
|
|
15 |
from email.mime.base import MIMEBase
|
|
|
16 |
from email.mime.multipart import MIMEMultipart
|
|
|
17 |
|
|
|
18 |
|
|
|
19 |
DataService.initialize(db_hostname='192.168.190.114')
|
|
|
20 |
SaleMap = {}
|
|
|
21 |
BrandCatMap = {}
|
|
|
22 |
UnUsableStatus = [25, 27, 30, 32, 47, 48, 68, 71, 72, 73, 74]
|
|
|
23 |
UsableStatus = [21, 28, 64, 67, 69, 70]
|
|
|
24 |
db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )
|
|
|
25 |
cursor = db.cursor()
|
|
|
26 |
|
|
|
27 |
class __SaleAndReturnInfo:
|
|
|
28 |
def __init__(self, websiteSale, amazonSale, ebaySale, snapdealSale, flipkartSale, productGroup, sellableCountWebsite, nonSellableCountWebsite, sellableCountAmazon, \
|
|
|
29 |
nonSellableCountAmazon,sellableCountEbay,nonSellableCountEbay,sellableCountSnapdeal,nonSellableCountSnapdeal,sellableCountFlipkart,nonSellableCountFlipkart, \
|
|
|
30 |
brand, model_name, model_number, color, sellableWebsitePercentage, nonSellableWebsitePercentage, sellableAmazonPercentage, nonSellableAmazonPercentage, \
|
|
|
31 |
sellableEbayPercentage, nonSellableEbayPercentage, sellableSnapdealPercentage, nonSellableSnapdealPercentage, sellableFlipkartPercentage, nonSellableFlipkartPercentage, \
|
|
|
32 |
sellableOverallPercentage, nonSellableOverallPercentage, item_id):
|
|
|
33 |
|
|
|
34 |
self.websiteSale = websiteSale
|
|
|
35 |
self.amazonSale = amazonSale
|
|
|
36 |
self.ebaySale = ebaySale
|
|
|
37 |
self.snapdealSale = snapdealSale
|
|
|
38 |
self.flipkartSale = flipkartSale
|
|
|
39 |
self.productGroup = productGroup
|
|
|
40 |
self.sellableCountWebsite = sellableCountWebsite
|
|
|
41 |
self.nonSellableCountWebsite = nonSellableCountWebsite
|
|
|
42 |
self.sellableCountAmazon = sellableCountAmazon
|
|
|
43 |
self.nonSellableCountAmazon = nonSellableCountAmazon
|
|
|
44 |
self.sellableCountEbay = sellableCountEbay
|
|
|
45 |
self.nonSellableCountEbay = nonSellableCountEbay
|
|
|
46 |
self.sellableCountSnapdeal = sellableCountSnapdeal
|
|
|
47 |
self.nonSellableCountSnapdeal = nonSellableCountSnapdeal
|
|
|
48 |
self.sellableCountFlipkart = sellableCountFlipkart
|
|
|
49 |
self.nonSellableCountFlipkart = nonSellableCountFlipkart
|
|
|
50 |
self.brand = brand
|
|
|
51 |
self.model_name = model_name
|
|
|
52 |
self.model_number = model_number
|
|
|
53 |
self.color = color
|
|
|
54 |
self.sellableWebsitePercentage = sellableWebsitePercentage
|
|
|
55 |
self.nonSellableWebsitePercentage = nonSellableWebsitePercentage
|
|
|
56 |
self.sellableAmazonPercentage = sellableAmazonPercentage
|
|
|
57 |
self.nonSellableAmazonPercentage = nonSellableAmazonPercentage
|
|
|
58 |
self.sellableEbayPercentage = sellableEbayPercentage
|
|
|
59 |
self.nonSellableEbayPercentage = nonSellableEbayPercentage
|
|
|
60 |
self.sellableSnapdealPercentage = sellableSnapdealPercentage
|
|
|
61 |
self.nonSellableSnapdealPercentage = nonSellableSnapdealPercentage
|
|
|
62 |
self.sellableFlipkartPercentage = sellableFlipkartPercentage
|
|
|
63 |
self.nonSellableFlipkartPercentage = nonSellableFlipkartPercentage
|
|
|
64 |
self.sellablOverallPercentage = sellableOverallPercentage
|
|
|
65 |
self.nonSellableOverallPercentage = nonSellableOverallPercentage
|
|
|
66 |
self.item_id = item_id
|
|
|
67 |
|
|
|
68 |
class __BrandCatInfo:
|
|
|
69 |
def __init__(self, totalSale, nonSellableReturn, nonSellableReturnPercentage, brand, productGroup):
|
|
|
70 |
self.totalSale = totalSale
|
|
|
71 |
self.nonSellableReturn = nonSellableReturn
|
|
|
72 |
self.nonSellableReturnPercentage = nonSellableReturnPercentage
|
|
|
73 |
self.brand = brand
|
|
|
74 |
self.productGroup = productGroup
|
|
|
75 |
|
|
|
76 |
|
|
|
77 |
|
|
|
78 |
def populateSaleInfo():
|
|
|
79 |
global SaleMap
|
|
|
80 |
fromDate = (datetime.now()-timedelta(days=45))
|
|
|
81 |
toDate = (fromDate + timedelta(days=15))
|
|
|
82 |
#allShippedOrders = Order.query.filter(Order.created_timestamp.between(fromDate.date(),toDate.date())).filter(Order.shipping_timestamp!=None).all()
|
|
|
83 |
|
|
|
84 |
allShippedOrders = session.query(LineItem.item_id,LineItem.productGroup,Order.source,Order.status,Order.received_return_timestamp, \
|
|
|
85 |
LineItem.quantity,Order.id,LineItem.brand,LineItem.model_name,LineItem.model_number,LineItem.color) \
|
|
|
86 |
.join((Order,LineItem.order_id==Order.id)).filter(Order.created_timestamp.between(fromDate.date(),toDate.date())) \
|
|
|
87 |
.filter(Order.shipping_timestamp!=None).filter(LineItem.productGroup.in_(('Handsets','Mobile Handset','Smart Phone','Mobile Phone','Tablets'))).all()
|
|
|
88 |
|
|
|
89 |
for shippedOrder in allShippedOrders:
|
|
|
90 |
if SaleMap.has_key(shippedOrder[0]):
|
|
|
91 |
val = SaleMap.get(shippedOrder[0])
|
|
|
92 |
if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):
|
|
|
93 |
val.websiteSale = val.websiteSale + shippedOrder[5]
|
|
|
94 |
elif shippedOrder[2]==OrderSource.AMAZON:
|
|
|
95 |
val.amazonSale = val.amazonSale + shippedOrder[5]
|
|
|
96 |
elif shippedOrder[2]==OrderSource.EBAY:
|
|
|
97 |
val.ebaySale = val.ebaySale + shippedOrder[5]
|
|
|
98 |
elif shippedOrder[2]==OrderSource.SNAPDEAL:
|
|
|
99 |
val.snapdealSale = val.snapdealSale + shippedOrder[5]
|
|
|
100 |
elif shippedOrder[2]==OrderSource.FLIPKART:
|
|
|
101 |
val.flipkartSale = val.flipkartSale + shippedOrder[5]
|
|
|
102 |
else:
|
|
|
103 |
print "Unknown source for orderId ",shippedOrder[6]
|
|
|
104 |
else:
|
|
|
105 |
saleInfo = __SaleAndReturnInfo(0,0,0,0,0,shippedOrder[1],0,0,0,0,0,0,0,0,0,0,shippedOrder[7],shippedOrder[8],shippedOrder[9],shippedOrder[10], \
|
|
|
106 |
0,0,0,0,0,0,0,0,0,0,0,0,shippedOrder[0])
|
|
|
107 |
if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):
|
|
|
108 |
saleInfo.websiteSale = saleInfo.websiteSale + shippedOrder[5]
|
|
|
109 |
elif shippedOrder[2]==OrderSource.AMAZON:
|
|
|
110 |
saleInfo.amazonSale = saleInfo.amazonSale + shippedOrder[5]
|
|
|
111 |
elif shippedOrder[2]==OrderSource.EBAY:
|
|
|
112 |
saleInfo.ebaySale = saleInfo.ebaySale + shippedOrder[5]
|
|
|
113 |
elif shippedOrder[2]==OrderSource.SNAPDEAL:
|
|
|
114 |
saleInfo.snapdealSale = saleInfo.snapdealSale + shippedOrder[5]
|
|
|
115 |
elif shippedOrder[2]==OrderSource.FLIPKART:
|
|
|
116 |
saleInfo.flipkartSale = saleInfo.flipkartSale + shippedOrder[5]
|
|
|
117 |
else:
|
|
|
118 |
print "Unknown source for orderId ",shippedOrder[6]
|
|
|
119 |
SaleMap[shippedOrder[0]]=saleInfo
|
|
|
120 |
|
|
|
121 |
#Checking returns
|
|
|
122 |
|
|
|
123 |
for shippedOrder in allShippedOrders:
|
|
|
124 |
if shippedOrder[4] is not None:
|
|
|
125 |
val = SaleMap.get(shippedOrder[0])
|
|
|
126 |
sql = "select quantity,type from scanNew where orderId=%d and type in ('SALE_RET','DOA_IN','SALE_RET_UNUSABLE')" %(shippedOrder[6])
|
|
|
127 |
cursor.execute(sql)
|
|
|
128 |
result = cursor.fetchone()
|
|
|
129 |
print result
|
|
|
130 |
if result is None:
|
|
|
131 |
continue
|
|
|
132 |
quantity = result[0]
|
|
|
133 |
returnType = result[1]
|
|
|
134 |
if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):
|
|
|
135 |
if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
|
|
|
136 |
val.nonSellableCountWebsite = val.nonSellableCountWebsite + quantity
|
|
|
137 |
else:
|
|
|
138 |
val.sellableCountWebsite = val.sellableCountWebsite + quantity
|
|
|
139 |
elif shippedOrder[2]==OrderSource.AMAZON:
|
|
|
140 |
if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
|
|
|
141 |
val.nonSellableCountAmazon = val.nonSellableCountAmazon + quantity
|
|
|
142 |
else:
|
|
|
143 |
val.sellableCountAmazon = val.sellableCountAmazon + quantity
|
|
|
144 |
elif shippedOrder[2]==OrderSource.EBAY:
|
|
|
145 |
if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
|
|
|
146 |
val.nonSellableCountEbay = val.nonSellableCountEbay + quantity
|
|
|
147 |
else:
|
|
|
148 |
val.sellableCountEbay = val.sellableCountEbay + quantity
|
|
|
149 |
elif shippedOrder[2]==OrderSource.SNAPDEAL:
|
|
|
150 |
if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
|
|
|
151 |
val.nonSellableCountSnapdeal = val.nonSellableCountSnapdeal + quantity
|
|
|
152 |
else:
|
|
|
153 |
val.sellableCountSnapdeal = val.sellableCountSnapdeal + quantity
|
|
|
154 |
elif shippedOrder[2]==OrderSource.FLIPKART:
|
|
|
155 |
if returnType in ('DOA_IN','SALE_RET_UNUSABLE'):
|
|
|
156 |
val.nonSellableCountFlipkart = val.nonSellableCountFlipkart + quantity
|
|
|
157 |
else:
|
|
|
158 |
val.sellableCountFlipkart = val.sellableCountFlipkart + quantity
|
|
|
159 |
else:
|
|
|
160 |
print "Not counting, unknown source"
|
|
|
161 |
|
|
|
162 |
allShippedOrders[:] = []
|
|
|
163 |
db.close()
|
|
|
164 |
|
|
|
165 |
def calculateReturnPercentage():
|
|
|
166 |
global SaleMap
|
|
|
167 |
for k, v in SaleMap.iteritems():
|
|
|
168 |
if v.websiteSale > 0:
|
|
|
169 |
v.sellableWebsitePercentage = float(v.sellableCountWebsite)/v.websiteSale
|
|
|
170 |
v.nonSellableWebsitePercentage = float(v.nonSellableCountWebsite)/v.websiteSale
|
|
|
171 |
if v.amazonSale > 0:
|
|
|
172 |
v.sellableAmazonPercentage = float(v.sellableCountAmazon)/v.amazonSale
|
|
|
173 |
v.nonSellableAmazonPercentage = float(v.nonSellableCountAmazon)/v.amazonSale
|
|
|
174 |
if v.ebaySale > 0:
|
|
|
175 |
v.sellableEbayPercentage = float(v.sellableCountEbay)/v.ebaySale
|
|
|
176 |
v.nonSellableEbayPercentage = float(v.nonSellableCountEbay)/v.ebaySale
|
|
|
177 |
if v.snapdealSale > 0:
|
|
|
178 |
v.sellableSnapdealPercentage = float(v.sellableCountSnapdeal)/v.snapdealSale
|
|
|
179 |
v.nonSellableSnapdealPercentage = float(v.nonSellableCountSnapdeal)/v.snapdealSale
|
|
|
180 |
if v.flipkartSale > 0:
|
|
|
181 |
v.sellableFlipkartPercentage = float(v.sellableCountFlipkart)/v.flipkartSale
|
|
|
182 |
v.nonSellableFlipkartPercentage = float(v.nonSellableCountFlipkart)/v.flipkartSale
|
|
|
183 |
if (v.websiteSale+v.amazonSale+v.ebaySale+v.snapdealSale+v.flipkartSale) > 0:
|
|
|
184 |
v.sellableOverallPercentage = float(v.sellableCountWebsite+v.sellableCountAmazon+v.sellableCountEbay+ \
|
|
|
185 |
v.sellableCountSnapdeal+v.sellableCountFlipkart)/(v.websiteSale+v.amazonSale+v.ebaySale+v.snapdealSale+v.flipkartSale)
|
|
|
186 |
v.nonSellableOverallPercentage = float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+ \
|
|
|
187 |
v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)/(v.websiteSale+v.amazonSale+v.ebaySale+v.snapdealSale+v.flipkartSale)
|
|
|
188 |
|
|
|
189 |
|
|
|
190 |
def exit():
|
|
|
191 |
import sys
|
|
|
192 |
sys.exit(1)
|
|
|
193 |
|
|
|
194 |
|
|
|
195 |
def sendEmail():
|
|
|
196 |
xstr = lambda s: s or ""
|
|
|
197 |
message="""<html>
|
|
|
198 |
<body>
|
|
|
199 |
<h3>Source Wise Return</h3>
|
|
|
200 |
<h4>Orders from """+str((datetime.now()-timedelta(days=45)).date())+"""
|
|
|
201 |
to """+str((datetime.now()-timedelta(days=30)).date())+"""</h4>
|
|
|
202 |
<table border="1" style="width:50%;">
|
|
|
203 |
<thead>
|
|
|
204 |
<tr><th>Source</th>
|
|
|
205 |
<th>Total Sale</th>
|
|
|
206 |
<th>Total Returns</th>
|
|
|
207 |
<th>Total Sellable Returns</th>
|
|
|
208 |
<th>Total Non Sellable Returns</th>
|
|
|
209 |
<th>Return %</th>
|
|
|
210 |
<th>Sellable Return %</th>
|
|
|
211 |
<th>Non Sellable Return %</th>
|
|
|
212 |
</tr></thead>
|
|
|
213 |
<tbody>"""
|
|
|
214 |
websiteSale, websiteSellableReturns, websiteNonSellableReturns, amazonSale, amazonSellableReturns, amazonNonSellableReturns, \
|
|
|
215 |
ebaySale, ebaySellableReturns, ebayNonSellableReturns, snapdealSale, snapdealSellableReturns, snapdealNonSellableReturns, \
|
|
|
216 |
flipkartSale, flipkartSellableReturns , flipkartNonSellableReturns, overallSale, overallSellableReturns, overallNonSellableReturns = (0,)*18
|
|
|
217 |
for k, v in SaleMap.iteritems():
|
|
|
218 |
websiteSale = websiteSale + v.websiteSale
|
|
|
219 |
websiteSellableReturns = websiteSellableReturns + v.sellableCountWebsite
|
|
|
220 |
websiteNonSellableReturns = websiteNonSellableReturns + v.nonSellableCountWebsite
|
|
|
221 |
|
|
|
222 |
amazonSale = amazonSale + v.amazonSale
|
|
|
223 |
amazonSellableReturns = amazonSellableReturns + v.sellableCountAmazon
|
|
|
224 |
amazonNonSellableReturns = amazonNonSellableReturns + v.nonSellableCountAmazon
|
|
|
225 |
|
|
|
226 |
ebaySale = ebaySale + v.ebaySale
|
|
|
227 |
ebaySellableReturns = ebaySellableReturns + v.sellableCountEbay
|
|
|
228 |
ebayNonSellableReturns = ebayNonSellableReturns + v.nonSellableCountEbay
|
|
|
229 |
|
|
|
230 |
snapdealSale = snapdealSale + v.snapdealSale
|
|
|
231 |
snapdealSellableReturns = snapdealSellableReturns + v.sellableCountSnapdeal
|
|
|
232 |
snapdealNonSellableReturns = snapdealNonSellableReturns + v.nonSellableCountSnapdeal
|
|
|
233 |
|
|
|
234 |
flipkartSale = flipkartSale + v.flipkartSale
|
|
|
235 |
flipkartSellableReturns = flipkartSellableReturns + v.sellableCountFlipkart
|
|
|
236 |
flipkartNonSellableReturns = flipkartNonSellableReturns + v.nonSellableCountFlipkart
|
|
|
237 |
|
|
|
238 |
overallSale = overallSale + websiteSale + amazonSale + ebaySale + snapdealSale + flipkartSale
|
|
|
239 |
overallSellableReturns = overallSellableReturns + websiteSellableReturns + amazonSellableReturns + ebaySellableReturns + snapdealSellableReturns + flipkartSellableReturns
|
|
|
240 |
overallNonSellableReturns = overallNonSellableReturns + websiteNonSellableReturns + amazonNonSellableReturns + ebayNonSellableReturns + snapdealNonSellableReturns + flipkartNonSellableReturns
|
|
|
241 |
message+="""<tr>
|
|
|
242 |
<td style="text-align:center">"""+"Website"+"""</td>
|
|
|
243 |
<td style="text-align:center">"""+str(int(websiteSale))+"""</td>
|
|
|
244 |
<td style="text-align:center">"""+str(websiteSellableReturns+websiteNonSellableReturns)+"""</td>
|
|
|
245 |
<td style="text-align:center">"""+str(websiteSellableReturns)+"""</td>
|
|
|
246 |
<td style="text-align:center">"""+str(websiteNonSellableReturns)+"""</td>
|
|
|
247 |
<td style="text-align:center">"""+str(round(float(websiteSellableReturns+websiteNonSellableReturns)*100/websiteSale,1))+" %"+"""</td>
|
|
|
248 |
<td style="text-align:center">"""+str(round(float(websiteSellableReturns)*100/websiteSale,1))+" %"+"""</td>
|
|
|
249 |
<td style="text-align:center">"""+str(round(float(websiteNonSellableReturns)*100/websiteSale,1))+" %"+"""</td>
|
|
|
250 |
</tr>
|
|
|
251 |
<tr>
|
|
|
252 |
<td style="text-align:center">"""+"Amazon MFN"+"""</td>
|
|
|
253 |
<td style="text-align:center">"""+str(int(amazonSale))+"""</td>
|
|
|
254 |
<td style="text-align:center">"""+str(amazonSellableReturns+amazonNonSellableReturns)+"""</td>
|
|
|
255 |
<td style="text-align:center">"""+str(amazonSellableReturns)+"""</td>
|
|
|
256 |
<td style="text-align:center">"""+str(amazonNonSellableReturns)+"""</td>
|
|
|
257 |
<td style="text-align:center">"""+str(round(float(amazonSellableReturns+amazonNonSellableReturns)*100/amazonSale,1))+" %"+"""</td>
|
|
|
258 |
<td style="text-align:center">"""+str(round(float(amazonSellableReturns)*100/amazonSale,1))+" %"+"""</td>
|
|
|
259 |
<td style="text-align:center">"""+str(round(float(amazonNonSellableReturns)*100/amazonSale,1))+" %"+"""</td>
|
|
|
260 |
</tr>
|
|
|
261 |
<tr>
|
|
|
262 |
<td style="text-align:center">"""+"Ebay"+"""</td>
|
|
|
263 |
<td style="text-align:center">"""+str(int(ebaySale))+"""</td>
|
|
|
264 |
<td style="text-align:center">"""+str(ebaySellableReturns+ebayNonSellableReturns)+"""</td>
|
|
|
265 |
<td style="text-align:center">"""+str(ebaySellableReturns)+"""</td>
|
|
|
266 |
<td style="text-align:center">"""+str(ebayNonSellableReturns)+"""</td>
|
|
|
267 |
<td style="text-align:center">"""+str(round(float(ebaySellableReturns+ebayNonSellableReturns)*100/ebaySale,1))+" %"+"""</td>
|
|
|
268 |
<td style="text-align:center">"""+str(round(float(ebaySellableReturns)*100/ebaySale,1))+" %"+"""</td>
|
|
|
269 |
<td style="text-align:center">"""+str(round(float(ebayNonSellableReturns)*100/ebaySale,1))+" %"+"""</td>
|
|
|
270 |
</tr>
|
|
|
271 |
<tr>
|
|
|
272 |
<td style="text-align:center">"""+"Snapdeal"+"""</td>
|
|
|
273 |
<td style="text-align:center">"""+str(int(snapdealSale))+"""</td>
|
|
|
274 |
<td style="text-align:center">"""+str(snapdealSellableReturns+snapdealNonSellableReturns)+"""</td>
|
|
|
275 |
<td style="text-align:center">"""+str(snapdealSellableReturns)+"""</td>
|
|
|
276 |
<td style="text-align:center">"""+str(snapdealNonSellableReturns)+"""</td>
|
|
|
277 |
<td style="text-align:center">"""+str(round(float(snapdealSellableReturns+snapdealNonSellableReturns)*100/snapdealSale,1))+" %"+"""</td>
|
|
|
278 |
<td style="text-align:center">"""+str(round(float(snapdealSellableReturns)*100/snapdealSale,1))+" %"+"""</td>
|
|
|
279 |
<td style="text-align:center">"""+str(round(float(snapdealNonSellableReturns)*100/snapdealSale,1))+" %"+"""</td>
|
|
|
280 |
</tr>
|
|
|
281 |
<tr>
|
|
|
282 |
<td style="text-align:center">"""+"Flipkart"+"""</td>
|
|
|
283 |
<td style="text-align:center">"""+str(int(flipkartSale))+"""</td>
|
|
|
284 |
<td style="text-align:center">"""+str(flipkartSellableReturns+flipkartNonSellableReturns)+"""</td>
|
|
|
285 |
<td style="text-align:center">"""+str(flipkartSellableReturns)+"""</td>
|
|
|
286 |
<td style="text-align:center">"""+str(flipkartNonSellableReturns)+"""</td>
|
|
|
287 |
<td style="text-align:center">"""+str(round(float(flipkartSellableReturns+flipkartNonSellableReturns)*100/flipkartSale,1))+" %"+"""</td>
|
|
|
288 |
<td style="text-align:center">"""+str(round(float(flipkartSellableReturns)*100/flipkartSale,1))+" %"+"""</td>
|
|
|
289 |
<td style="text-align:center">"""+str(round(float(flipkartNonSellableReturns)*100/flipkartSale,1))+" %"+"""</td>
|
|
|
290 |
</tr>
|
|
|
291 |
<tr>
|
|
|
292 |
<td style="text-align:center">"""+"Total"+"""</td>
|
|
|
293 |
<td style="text-align:center">"""+str(int(overallSale))+"""</td>
|
|
|
294 |
<td style="text-align:center">"""+str(overallSellableReturns+overallNonSellableReturns)+"""</td>
|
|
|
295 |
<td style="text-align:center">"""+str(overallSellableReturns)+"""</td>
|
|
|
296 |
<td style="text-align:center">"""+str(overallNonSellableReturns)+"""</td>
|
|
|
297 |
<td style="text-align:center">"""+str(round(float(overallSellableReturns+overallNonSellableReturns)*100/overallSale,1))+" %"+"""</td>
|
|
|
298 |
<td style="text-align:center">"""+str(round(float(overallSellableReturns)*100/overallSale,1))+" %"+"""</td>
|
|
|
299 |
<td style="text-align:center">"""+str(round(float(overallNonSellableReturns)*100/overallSale,1))+" %"+"""</td>
|
|
|
300 |
</tr></tbody></table>"""
|
|
|
301 |
|
|
|
302 |
topWebsite, topAmazon, topEbay, topSnapdeal, topFlipkart, topOverall = [],[],[],[],[],[]
|
|
|
303 |
|
|
|
304 |
for v in SaleMap.itervalues():
|
|
|
305 |
# if v.websiteSale >=10:
|
|
|
306 |
# topWebsite.append(v)
|
|
|
307 |
# if v.amazonSale >=10:
|
|
|
308 |
# topAmazon.append(v)
|
|
|
309 |
# if v.ebaySale >=10:
|
|
|
310 |
# topEbay.append(v)
|
|
|
311 |
# if v.snapdealSale >=10:
|
|
|
312 |
# topSnapdeal.append(v)
|
|
|
313 |
# if v.flipkartSale >=10:
|
|
|
314 |
# topFlipkart.append(v)
|
|
|
315 |
if (v.websiteSale + v.amazonSale + v.ebaySale + v.snapdealSale + v.flipkartSale) >=25:
|
|
|
316 |
topOverall.append(v)
|
|
|
317 |
|
|
|
318 |
sortedHighReturnRate = sorted(topOverall, key=lambda x: x.nonSellableOverallPercentage, reverse=True)
|
|
|
319 |
|
|
|
320 |
message+="""<h3>Top Sku's with high sale return</h3>
|
|
|
321 |
<table border="1" style="width:100%;">
|
|
|
322 |
<thead>
|
|
|
323 |
<tr><th>Item Id</th>
|
|
|
324 |
<th>Product Name</th>
|
|
|
325 |
<th>Website Sale</th>
|
|
|
326 |
<th>Website Sellable Returns</th>
|
|
|
327 |
<th>Website Non Sellable Returns</th>
|
|
|
328 |
<th>Website Sellable Returns %</th>
|
|
|
329 |
<th>Website Non Sellable Returns %</th>
|
|
|
330 |
<th>Amazon Sale</th>
|
|
|
331 |
<th>Amazon Sellable Returns</th>
|
|
|
332 |
<th>Amazon Non Sellable Returns</th>
|
|
|
333 |
<th>Amazon Sellable Returns %</th>
|
|
|
334 |
<th>Amazon Non Sellable Returns %</th>
|
|
|
335 |
<th>Ebay Sale</th>
|
|
|
336 |
<th>Ebay Sellable Returns</th>
|
|
|
337 |
<th>Ebay Non Sellable Returns</th>
|
|
|
338 |
<th>Ebay Sellable Returns %</th>
|
|
|
339 |
<th>Ebay Non Sellable Returns %</th>
|
|
|
340 |
<th>Snapdeal Sale</th>
|
|
|
341 |
<th>Snapdeal Sellable Returns</th>
|
|
|
342 |
<th>Snapdeal Non Sellable Returns</th>
|
|
|
343 |
<th>Snapdeal Sellable Returns %</th>
|
|
|
344 |
<th>Snapdeal Non Sellable Returns %</th>
|
|
|
345 |
<th>Flipkart Sale</th>
|
|
|
346 |
<th>Flipkart Sellable Returns</th>
|
|
|
347 |
<th>Flipkart Non Sellable Returns</th>
|
|
|
348 |
<th>Flipkart Sellable Returns %</th>
|
|
|
349 |
<th>Flipkart Non Sellable Returns %</th>
|
|
|
350 |
<th>Total Sale</th>
|
|
|
351 |
<th>Total Sellable Returns</th>
|
|
|
352 |
<th>Total Non Sellable Returns</th>
|
|
|
353 |
<th>Total Sellable Returns %</th>
|
|
|
354 |
<th>Total Non Sellable Returns %</th>
|
|
|
355 |
</tr></thead>
|
|
|
356 |
<tbody>"""
|
|
|
357 |
countItems = 0
|
|
|
358 |
for highReturnData in sortedHighReturnRate:
|
|
|
359 |
if countItems==11:
|
|
|
360 |
break
|
|
|
361 |
countItems+=1
|
|
|
362 |
message+="""<tr>
|
|
|
363 |
<td style="text-align:center">"""+str(highReturnData.item_id)+"""</td>
|
|
|
364 |
<td style="text-align:center">"""+xstr(highReturnData.brand)+" "+xstr(highReturnData.model_name)+" "+xstr(highReturnData.model_number)+" "+xstr(highReturnData.color)+"""</td>
|
|
|
365 |
<td style="text-align:center">"""+str(int(highReturnData.websiteSale))+"""</td>
|
|
|
366 |
<td style="text-align:center">"""+str(highReturnData.sellableCountWebsite)+"""</td>
|
|
|
367 |
<td style="text-align:center">"""+str(highReturnData.nonSellableCountWebsite)+"""</td>
|
|
|
368 |
<td style="text-align:center">"""+str(round(highReturnData.sellableWebsitePercentage*100,1))+"""</td>
|
|
|
369 |
<td style="text-align:center">"""+str(round(highReturnData.nonSellableWebsitePercentage*100,1))+"""</td>
|
|
|
370 |
<td style="text-align:center">"""+str(int(highReturnData.amazonSale))+"""</td>
|
|
|
371 |
<td style="text-align:center">"""+str(highReturnData.sellableCountAmazon)+"""</td>
|
|
|
372 |
<td style="text-align:center">"""+str(highReturnData.nonSellableCountAmazon)+"""</td>
|
|
|
373 |
<td style="text-align:center">"""+str(round(highReturnData.sellableAmazonPercentage*100,1))+"""</td>
|
|
|
374 |
<td style="text-align:center">"""+str(round(highReturnData.nonSellableAmazonPercentage*100,1))+"""</td>
|
|
|
375 |
<td style="text-align:center">"""+str(int(highReturnData.ebaySale))+"""</td>
|
|
|
376 |
<td style="text-align:center">"""+str(highReturnData.sellableCountEbay)+"""</td>
|
|
|
377 |
<td style="text-align:center">"""+str(highReturnData.nonSellableCountEbay)+"""</td>
|
|
|
378 |
<td style="text-align:center">"""+str(round(highReturnData.sellableEbayPercentage*100,1))+"""</td>
|
|
|
379 |
<td style="text-align:center">"""+str(round(highReturnData.nonSellableEbayPercentage*100,1))+"""</td>
|
|
|
380 |
<td style="text-align:center">"""+str(int(highReturnData.snapdealSale))+"""</td>
|
|
|
381 |
<td style="text-align:center">"""+str(highReturnData.sellableCountSnapdeal)+"""</td>
|
|
|
382 |
<td style="text-align:center">"""+str(highReturnData.nonSellableCountSnapdeal)+"""</td>
|
|
|
383 |
<td style="text-align:center">"""+str(round(highReturnData.sellableSnapdealPercentage*100,1))+"""</td>
|
|
|
384 |
<td style="text-align:center">"""+str(round(highReturnData.nonSellableSnapdealPercentage*100,1))+"""</td>
|
|
|
385 |
<td style="text-align:center">"""+str(int(highReturnData.flipkartSale))+"""</td>
|
|
|
386 |
<td style="text-align:center">"""+str(highReturnData.sellableCountFlipkart)+"""</td>
|
|
|
387 |
<td style="text-align:center">"""+str(highReturnData.nonSellableCountFlipkart)+"""</td>
|
|
|
388 |
<td style="text-align:center">"""+str(round(highReturnData.sellableFlipkartPercentage*100,1))+"""</td>
|
|
|
389 |
<td style="text-align:center">"""+str(round(highReturnData.nonSellableFlipkartPercentage*100,1))+"""</td>
|
|
|
390 |
<td style="text-align:center">"""+str(int(highReturnData.websiteSale+highReturnData.amazonSale+highReturnData.ebaySale+highReturnData.snapdealSale+highReturnData.flipkartSale))+"""</td>
|
|
|
391 |
<td style="text-align:center">"""+str(highReturnData.sellableCountWebsite+highReturnData.sellableCountAmazon+highReturnData.sellableCountEbay+highReturnData.sellableCountSnapdeal+highReturnData.sellableCountFlipkart)+"""</td>
|
|
|
392 |
<td style="text-align:center">"""+str(highReturnData.nonSellableCountWebsite+highReturnData.nonSellableCountAmazon+highReturnData.nonSellableCountEbay+highReturnData.nonSellableCountSnapdeal+highReturnData.nonSellableCountFlipkart)+"""</td>
|
|
|
393 |
<td style="text-align:center">"""+str(round(highReturnData.sellableOverallPercentage*100,1))+"""</td>
|
|
|
394 |
<td style="text-align:center">"""+str(round(highReturnData.nonSellableOverallPercentage*100,1))+"""</td>
|
|
|
395 |
</tr>"""
|
|
|
396 |
|
|
|
397 |
message+="""</tr></tbody></table>"""
|
|
|
398 |
|
|
|
399 |
topBrandCatReturns = []
|
|
|
400 |
|
|
|
401 |
for y in BrandCatMap.itervalues():
|
|
|
402 |
if y.totalSale > 25:
|
|
|
403 |
topBrandCatReturns.append(y)
|
|
|
404 |
|
|
|
405 |
sortedHighReturnRateBrandCat = sorted(topBrandCatReturns, key=lambda x: x.nonSellableReturnPercentage, reverse=True)
|
|
|
406 |
message+="""<h3>Brand Category Wise Non-Sellable Return</h3>
|
|
|
407 |
<table border="1" style="width:50%;">
|
|
|
408 |
<thead>
|
|
|
409 |
<tr><th>Brand</th>
|
|
|
410 |
<th>Category</th>
|
|
|
411 |
<th>Total Sale</th>
|
|
|
412 |
<th>Non Sellable Returns</th>
|
|
|
413 |
<th>Non Sellable Returns %</th>
|
|
|
414 |
</tr></thead>
|
|
|
415 |
<tbody>"""
|
|
|
416 |
for highReturnDataBrandCat in sortedHighReturnRateBrandCat:
|
|
|
417 |
message+="""<tr>
|
|
|
418 |
<td style="text-align:center">"""+str(highReturnDataBrandCat.brand)+"""</td>
|
|
|
419 |
<td style="text-align:center">"""+str(highReturnDataBrandCat.productGroup)+"""</td>
|
|
|
420 |
<td style="text-align:center">"""+str(int(highReturnDataBrandCat.totalSale))+"""</td>
|
|
|
421 |
<td style="text-align:center">"""+str(int(highReturnDataBrandCat.nonSellableReturn))+"""</td>
|
|
|
422 |
<td style="text-align:center">"""+str(round(highReturnDataBrandCat.nonSellableReturnPercentage*100,1))+"""</td>
|
|
|
423 |
</tr>"""
|
|
|
424 |
message+="""</tr></tbody></table></body></html>"""
|
|
|
425 |
|
|
|
426 |
wbk = xlwt.Workbook(encoding="UTF-8")
|
|
|
427 |
sheet = wbk.add_sheet('Source Wise Return')
|
|
|
428 |
xstr = lambda s: s or ""
|
|
|
429 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
430 |
|
|
|
431 |
excel_integer_format = '0'
|
|
|
432 |
integer_style = xlwt.XFStyle()
|
|
|
433 |
integer_style.num_format_str = excel_integer_format
|
|
|
434 |
|
|
|
435 |
sheet.write(0, 0, "Item ID", heading_xf)
|
|
|
436 |
sheet.write(0, 1, "Product Group", heading_xf)
|
|
|
437 |
sheet.write(0, 2, "Brand", heading_xf)
|
|
|
438 |
sheet.write(0, 3, "Product", heading_xf)
|
|
|
439 |
sheet.write(0, 4, "Total Sale", heading_xf)
|
|
|
440 |
sheet.write(0, 5, "Website Sale", heading_xf)
|
|
|
441 |
sheet.write(0, 6, "Amazon Sale", heading_xf)
|
|
|
442 |
sheet.write(0, 7, "Ebay Sale", heading_xf)
|
|
|
443 |
sheet.write(0, 8, "Snapdeal Sale", heading_xf)
|
|
|
444 |
sheet.write(0, 9, "Flipkart Sale", heading_xf)
|
|
|
445 |
sheet.write(0, 10, "Sellable Website", heading_xf)
|
|
|
446 |
sheet.write(0, 11, "Non Sellable Website", heading_xf)
|
|
|
447 |
sheet.write(0, 12, "Sellable Amazon", heading_xf)
|
|
|
448 |
sheet.write(0, 13, "Non Sellable Amazon", heading_xf)
|
|
|
449 |
sheet.write(0, 14, "Sellable Ebay", heading_xf)
|
|
|
450 |
sheet.write(0, 15, "Non Sellable Ebay", heading_xf)
|
|
|
451 |
sheet.write(0, 16, "Sellable Snapdeal", heading_xf)
|
|
|
452 |
sheet.write(0, 17, "Non Sellable Snapdeal", heading_xf)
|
|
|
453 |
sheet.write(0, 18, "Sellable Flipkart", heading_xf)
|
|
|
454 |
sheet.write(0, 19, "Non Sellable Flipkart", heading_xf)
|
|
|
455 |
sheet.write(0, 20, "Total Sellable Returns", heading_xf)
|
|
|
456 |
sheet.write(0, 21, "Sellable Returns %", heading_xf)
|
|
|
457 |
sheet.write(0, 22, "Total Non Sellable Returns", heading_xf)
|
|
|
458 |
sheet.write(0, 23, "Non Sellable Returns %", heading_xf)
|
|
|
459 |
|
|
|
460 |
sheet_iterator=1
|
|
|
461 |
for k, v in SaleMap.iteritems():
|
|
|
462 |
sheet.write(sheet_iterator,0,k)
|
|
|
463 |
sheet.write(sheet_iterator,1,v.productGroup)
|
|
|
464 |
sheet.write(sheet_iterator,2,v.brand)
|
|
|
465 |
sheet.write(sheet_iterator,3,xstr(v.brand)+" "+xstr(v.model_name)+" "+xstr(v.model_number)+" "+xstr(v.color))
|
|
|
466 |
sheet.write(sheet_iterator,4,v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale)
|
|
|
467 |
sheet.write(sheet_iterator,5,v.websiteSale)
|
|
|
468 |
sheet.write(sheet_iterator,6,v.amazonSale)
|
|
|
469 |
sheet.write(sheet_iterator,7,v.ebaySale)
|
|
|
470 |
sheet.write(sheet_iterator,8,v.snapdealSale)
|
|
|
471 |
sheet.write(sheet_iterator,9,v.flipkartSale)
|
|
|
472 |
sheet.write(sheet_iterator,10,v.sellableCountWebsite)
|
|
|
473 |
sheet.write(sheet_iterator,11,v.nonSellableCountWebsite)
|
|
|
474 |
sheet.write(sheet_iterator,12,v.sellableCountAmazon)
|
|
|
475 |
sheet.write(sheet_iterator,13,v.nonSellableCountAmazon)
|
|
|
476 |
sheet.write(sheet_iterator,14,v.sellableCountEbay)
|
|
|
477 |
sheet.write(sheet_iterator,15,v.nonSellableCountEbay)
|
|
|
478 |
sheet.write(sheet_iterator,16,v.sellableCountSnapdeal)
|
|
|
479 |
sheet.write(sheet_iterator,17,v.nonSellableCountSnapdeal)
|
|
|
480 |
sheet.write(sheet_iterator,18,v.sellableCountFlipkart)
|
|
|
481 |
sheet.write(sheet_iterator,19,v.nonSellableCountFlipkart)
|
|
|
482 |
sheet.write(sheet_iterator,20,v.sellableCountWebsite+v.sellableCountAmazon+v.sellableCountEbay+v.sellableCountSnapdeal+v.sellableCountFlipkart)
|
|
|
483 |
if (v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale) > 0:
|
|
|
484 |
sheet.write(sheet_iterator,21,round(float(v.sellableCountWebsite+v.sellableCountAmazon+v.sellableCountEbay+v.sellableCountSnapdeal+v.sellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))
|
|
|
485 |
else:
|
|
|
486 |
sheet.write(sheet_iterator,21,0)
|
|
|
487 |
sheet.write(sheet_iterator,22,v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)
|
|
|
488 |
if (v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart) > 0:
|
|
|
489 |
sheet.write(sheet_iterator,23,round(float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))
|
|
|
490 |
else:
|
|
|
491 |
sheet.write(sheet_iterator,23,0)
|
|
|
492 |
sheet_iterator+=1
|
|
|
493 |
filename = "/tmp/return-report-source-mob-tab.xls"
|
|
|
494 |
wbk.save(filename)
|
|
|
495 |
|
|
|
496 |
mailServer = smtplib.SMTP("smtp.gmail.com", 587)
|
|
|
497 |
mailServer.ehlo()
|
|
|
498 |
mailServer.starttls()
|
|
|
499 |
mailServer.ehlo()
|
|
|
500 |
|
| 20172 |
aman.kumar |
501 |
recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','amit.sirohi@saholic.com','khushal.bhatia@saholic.com']
|
| 12241 |
kshitij.so |
502 |
msg = MIMEMultipart()
|
|
|
503 |
msg['Subject'] = "Sale Return Source Wise Mobiles & Tablets" + ' - ' + str(datetime.now())
|
|
|
504 |
msg['From'] = ""
|
|
|
505 |
msg['To'] = ",".join(recipients)
|
|
|
506 |
msg.preamble = "Sale Return Source Wise Sale Return Source Wise Mobiles & Tablets" + ' - ' + str(datetime.now())
|
|
|
507 |
html_msg = MIMEText(message, 'html')
|
|
|
508 |
msg.attach(html_msg)
|
|
|
509 |
fileMsg = MIMEBase('application','vnd.ms-excel')
|
|
|
510 |
fileMsg.set_payload(file(filename).read())
|
|
|
511 |
encoders.encode_base64(fileMsg)
|
|
|
512 |
from datetime import date
|
|
|
513 |
fileMsg.add_header('Content-Disposition','attachment;filename=Return-Source-Wise-Mob-Tab' + ' - ' + date.today().isoformat() + '.xls')
|
|
|
514 |
msg.attach(fileMsg)
|
|
|
515 |
try:
|
|
|
516 |
mailServer.login("cnc.center@shop2020.in", "5h0p2o2o")
|
|
|
517 |
#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
|
|
|
518 |
mailServer.sendmail("5h0p2o2o", recipients, msg.as_string())
|
|
|
519 |
except Exception as e:
|
|
|
520 |
print e
|
|
|
521 |
print "Unable to send return .Lets try with local SMTP."
|
|
|
522 |
smtpServer = smtplib.SMTP('localhost')
|
|
|
523 |
smtpServer.set_debuglevel(1)
|
|
|
524 |
sender = 'cnc.center@shop2020.in'
|
|
|
525 |
try:
|
|
|
526 |
smtpServer.sendmail(sender, recipients, msg.as_string())
|
|
|
527 |
print "Successfully sent email"
|
|
|
528 |
except:
|
|
|
529 |
print "Error: unable to send email."
|
|
|
530 |
|
|
|
531 |
def populateBrandCategoryReturn():
|
|
|
532 |
global BrandCatMap
|
|
|
533 |
for v in SaleMap.itervalues():
|
|
|
534 |
if BrandCatMap.has_key(v.productGroup+" "+v.brand):
|
|
|
535 |
brandCatObj = BrandCatMap.get(v.productGroup+" "+v.brand)
|
|
|
536 |
totalSale = brandCatObj.totalSale + v.websiteSale + v.amazonSale + v.ebaySale + v.snapdealSale +v.flipkartSale
|
|
|
537 |
totalNonSellableReturn = brandCatObj.nonSellableReturn + v.nonSellableCountWebsite + v.nonSellableCountAmazon + v.nonSellableCountEbay + v.nonSellableCountSnapdeal + v.nonSellableCountFlipkart
|
|
|
538 |
nonSellableReturnPercentage = float(totalNonSellableReturn)/totalSale
|
|
|
539 |
brandCatObj.totalSale = totalSale
|
|
|
540 |
brandCatObj.nonSellableReturn = totalNonSellableReturn
|
|
|
541 |
brandCatObj.nonSellableReturnPercentage = nonSellableReturnPercentage
|
|
|
542 |
else:
|
|
|
543 |
totalSale = v.websiteSale + v.amazonSale + v.ebaySale + v.snapdealSale +v.flipkartSale
|
|
|
544 |
totalNonSellableReturn = v.nonSellableCountWebsite + v.nonSellableCountAmazon + v.nonSellableCountEbay + v.nonSellableCountSnapdeal + v.nonSellableCountFlipkart
|
|
|
545 |
nonSellableReturnPercentage = float(totalNonSellableReturn)/totalSale
|
|
|
546 |
brandCatObj = __BrandCatInfo(totalSale,totalNonSellableReturn,nonSellableReturnPercentage,v.brand,v.productGroup)
|
|
|
547 |
BrandCatMap[v.productGroup+" "+v.brand] = brandCatObj
|
|
|
548 |
|
|
|
549 |
|
|
|
550 |
def main():
|
|
|
551 |
populateSaleInfo()
|
|
|
552 |
calculateReturnPercentage()
|
|
|
553 |
populateBrandCategoryReturn()
|
|
|
554 |
sendEmail()
|
|
|
555 |
|
|
|
556 |
|
|
|
557 |
if __name__=='__main__':
|
|
|
558 |
main()
|