| 13480 |
kshitij.so |
1 |
from elixir import *
|
|
|
2 |
from shop2020.config.client.ConfigClient import ConfigClient
|
|
|
3 |
from shop2020.model.v1.catalog.impl import DataService
|
|
|
4 |
from shop2020.model.v1.catalog.impl.DataService import AmazonPromotion, Item
|
|
|
5 |
from shop2020.model.v1.order.script import MongoService
|
|
|
6 |
from shop2020.thriftpy.model.v1.catalog.ttypes import AmazonPromotionType
|
|
|
7 |
from shop2020.thriftpy.model.v1.order.ttypes import AmazonFCWarehouseLocation
|
|
|
8 |
from shop2020.utils.Utils import to_py_date
|
|
|
9 |
from datetime import datetime, timedelta
|
|
|
10 |
import xlwt
|
|
|
11 |
|
|
|
12 |
DataService.initialize(db_hostname='localhost')
|
|
|
13 |
config_client = ConfigClient()
|
|
|
14 |
host = config_client.get_property('staging_hostname')
|
|
|
15 |
|
|
|
16 |
startDate = datetime.strptime('01-11-2014 00:00:00', '%d-%m-%Y %H:%M:%S')
|
|
|
17 |
endDate = datetime.strptime('01-12-2014 00:00:00', '%d-%m-%Y %H:%M:%S')
|
|
|
18 |
|
|
|
19 |
wpiPromoMap = {}
|
|
|
20 |
dodPromoMap = {}
|
|
|
21 |
itemInfoMap = {}
|
|
|
22 |
|
|
|
23 |
prefix = {0:'FBA',1:'FBB',2:'FBG'}
|
|
|
24 |
|
|
|
25 |
offset, limit, sheet_iterator = 0, 1000, 1
|
|
|
26 |
|
|
|
27 |
class __ItemInfo:
|
|
|
28 |
|
|
|
29 |
def __init__(self, brand, model_name, model_number, color, productGroup):
|
|
|
30 |
self.brand = brand
|
|
|
31 |
self.model_name = model_name
|
|
|
32 |
self.model_number = model_number
|
|
|
33 |
self.color = color
|
|
|
34 |
self.productGroup = productGroup
|
|
|
35 |
|
|
|
36 |
|
|
|
37 |
def populateItemInfo():
|
|
|
38 |
global itemInfoMap
|
|
|
39 |
item_ids = MongoService.getDistinctItemsFromOrderData(startDate, endDate)
|
|
|
40 |
for item_id in item_ids:
|
|
|
41 |
it = Item.get_by(id=item_id)
|
|
|
42 |
if it is None:
|
|
|
43 |
itemInfoMap[item_id] = __ItemInfo(' ', ' ', ' ', ' ', ' ')
|
|
|
44 |
continue
|
|
|
45 |
itemInfoMap[item_id] = __ItemInfo(it.brand, it.model_name, it.model_number, it.color, it.product_group)
|
|
|
46 |
|
|
|
47 |
def populateSubsidyInfo():
|
|
|
48 |
promotions = AmazonPromotion.query.filter(AmazonPromotion.endDate>=startDate - timedelta(days=7)).filter(AmazonPromotion.endDate<=endDate + timedelta(days=7)).all()
|
|
|
49 |
for promo in promotions:
|
|
|
50 |
if not promo.promotionActive:
|
|
|
51 |
continue
|
|
|
52 |
temp = []
|
|
|
53 |
if promo.promotionType == AmazonPromotionType.LONGTERM:
|
|
|
54 |
if wpiPromoMap.has_key(promo.sku):
|
|
|
55 |
val = wpiPromoMap.get(promo.sku)
|
|
|
56 |
for l in val:
|
|
|
57 |
temp.append(l)
|
|
|
58 |
temp.append(promo)
|
|
|
59 |
wpiPromoMap[promo.sku] = temp
|
|
|
60 |
else:
|
|
|
61 |
temp.append(promo)
|
|
|
62 |
wpiPromoMap[promo.sku] = temp
|
|
|
63 |
else:
|
|
|
64 |
if dodPromoMap.has_key(promo.sku):
|
|
|
65 |
val = dodPromoMap.get(promo.sku)
|
|
|
66 |
for l in val:
|
|
|
67 |
temp.append(l)
|
|
|
68 |
temp.append(promo)
|
|
|
69 |
dodPromoMap[promo.sku] = temp
|
|
|
70 |
else:
|
|
|
71 |
temp.append(promo)
|
|
|
72 |
dodPromoMap[promo.sku] = temp
|
|
|
73 |
|
|
|
74 |
def writeReport():
|
|
|
75 |
global sheet_iterator
|
|
|
76 |
global offset
|
|
|
77 |
wbk = xlwt.Workbook(encoding="UTF-8")
|
|
|
78 |
sheet = wbk.add_sheet('AmazonSubsidy')
|
|
|
79 |
xstr = lambda s: s or ""
|
|
|
80 |
heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
|
|
|
81 |
|
|
|
82 |
excel_integer_format = '0'
|
|
|
83 |
integer_style = xlwt.XFStyle()
|
|
|
84 |
integer_style.num_format_str = excel_integer_format
|
|
|
85 |
writeHead(sheet,heading_xf)
|
|
|
86 |
while(True):
|
|
|
87 |
orders = MongoService.getAmazonOrderData(startDate, endDate, offset, limit)
|
|
|
88 |
if len(orders) == 0:
|
|
|
89 |
break
|
|
|
90 |
for order in orders:
|
|
|
91 |
sheet.write(sheet_iterator, 0, order.amazonOrderId)
|
|
|
92 |
sheet.write(sheet_iterator, 1, order.item_id)
|
|
|
93 |
sheet.write(sheet_iterator, 2, prefix.get(AmazonFCWarehouseLocation._NAMES_TO_VALUES.get(order.fcLocation))+str(order.item_id))
|
|
|
94 |
sheet.write(sheet_iterator, 3, order.fcLocation)
|
|
|
95 |
sheet.write(sheet_iterator, 4, getItemInfo(order.item_id).productGroup)
|
|
|
96 |
sheet.write(sheet_iterator, 5, getItemInfo(order.item_id).brand)
|
|
|
97 |
sheet.write(sheet_iterator, 6, xstr(getItemInfo(order.item_id).brand)+" "+xstr(getItemInfo(order.item_id).model_name)+" "+xstr(getItemInfo(order.item_id).model_number)+" "+xstr(getItemInfo(order.item_id).color))
|
|
|
98 |
sheet.write(sheet_iterator, 7, str(to_py_date(order.purchaseDate)))
|
|
|
99 |
sheet.write(sheet_iterator, 8, order.totalAmount)
|
|
|
100 |
sheet.write(sheet_iterator, 9, order.quantity)
|
|
|
101 |
sheet.write(sheet_iterator, 10, order.promotionDiscount)
|
|
|
102 |
subsidy, maxSalePrice, promoStartDate, promoEndDate, promoType, toleranceAmount = calculateSubsidy(order.item_id, AmazonFCWarehouseLocation._NAMES_TO_VALUES.get(order.fcLocation), to_py_date(order.purchaseDate),order.totalAmount, order.amazonOrderId, order.promotionDiscount)
|
|
|
103 |
if subsidy > 0:
|
|
|
104 |
sheet.write(sheet_iterator, 11, (order.quantity)*(subsidy))
|
|
|
105 |
sheet.write(sheet_iterator, 12, toleranceAmount)
|
|
|
106 |
sheet.write(sheet_iterator, 13, str(promoStartDate))
|
|
|
107 |
sheet.write(sheet_iterator, 14, str(promoEndDate))
|
|
|
108 |
sheet.write(sheet_iterator, 15, maxSalePrice)
|
|
|
109 |
sheet.write(sheet_iterator, 16, AmazonPromotionType._VALUES_TO_NAMES.get(promoType))
|
|
|
110 |
sheet_iterator+=1
|
|
|
111 |
offset = offset + 1000
|
|
|
112 |
file_subsidy = "/tmp/subsidy"+" " + str(datetime.now()) + ".xls"
|
|
|
113 |
wbk.save(file_subsidy)
|
|
|
114 |
|
|
|
115 |
def calculateSubsidy(item_id, fcLocation, purchaseDate, sellingPrice, amazonOrderId, promotionDiscount):
|
|
|
116 |
validPromosWpi = []
|
|
|
117 |
validPromosDod = []
|
|
|
118 |
sku = prefix.get(fcLocation)+str(item_id)
|
|
|
119 |
if wpiPromoMap.has_key(sku):
|
|
|
120 |
promos = wpiPromoMap.get(sku)
|
|
|
121 |
for promo in promos:
|
|
|
122 |
if promo.startDate <= purchaseDate and promo.endDate >= purchaseDate:
|
|
|
123 |
validPromosWpi.append(promo)
|
|
|
124 |
if dodPromoMap.has_key(sku):
|
|
|
125 |
promos = dodPromoMap.get(sku)
|
|
|
126 |
for promo in promos:
|
|
|
127 |
if promo.startDate <= purchaseDate and promo.endDate >= purchaseDate:
|
|
|
128 |
validPromosDod.append(promo)
|
|
|
129 |
return figureOutBestPossibleSubsidy(validPromosWpi, validPromosDod, sellingPrice, purchaseDate, amazonOrderId, promotionDiscount, sku)
|
|
|
130 |
|
|
|
131 |
#def figureOutBestPossibleSubsidy(validPromosWpi, validPromosDod , sellingPrice, purchaseDate,amazonOrderId):
|
|
|
132 |
# if len(validPromos) > 0:
|
|
|
133 |
# print "No of valid promo for sku ", (validPromos[0]).sku ,"is ", len(validPromos), "and purchase date ", purchaseDate, "amazon order id ",amazonOrderId
|
|
|
134 |
# subsidy, maxSalePrice, promoStartDate, promoEndDate, promoType = 0.0, 0.0, None, None, None
|
|
|
135 |
# toleranceAmount = 50 if sellingPrice <=1000 else 100
|
|
|
136 |
# for validPromo in validPromos:
|
|
|
137 |
# if validPromo.promotionType == AmazonPromotionType.LONGTERM:
|
|
|
138 |
# if validPromo.salePrice >= sellingPrice:
|
|
|
139 |
# if validPromo.subsidy > subsidy:
|
|
|
140 |
# subsidy = validPromo.subsidy
|
|
|
141 |
# maxSalePrice = validPromo.salePrice
|
|
|
142 |
# promoStartDate = validPromo.startDate
|
|
|
143 |
# promoEndDate = validPromo.endDate
|
|
|
144 |
# promoType = AmazonPromotionType.LONGTERM
|
|
|
145 |
# elif validPromo.salePrice >= sellingPrice - toleranceAmount:
|
|
|
146 |
# if validPromo.subsidy > subsidy:
|
|
|
147 |
# subsidy = validPromo.subsidy
|
|
|
148 |
# maxSalePrice = validPromo.salePrice
|
|
|
149 |
# promoStartDate = validPromo.startDate
|
|
|
150 |
# promoEndDate = validPromo.endDate
|
|
|
151 |
# promoType = AmazonPromotionType.LONGTERM
|
|
|
152 |
# elif validPromo.promotionType == AmazonPromotionType.SHORTTERM:
|
|
|
153 |
# if validPromo.salePrice >= sellingPrice:
|
|
|
154 |
# if validPromo.subsidy > subsidy:
|
|
|
155 |
# subsidy = validPromo.subsidy
|
|
|
156 |
# maxSalePrice = validPromo.salePrice
|
|
|
157 |
# promoStartDate = validPromo.startDate
|
|
|
158 |
# promoEndDate = validPromo.endDate
|
|
|
159 |
# promoType = AmazonPromotionType.SHORTTERM
|
|
|
160 |
# elif validPromo.salePrice >= sellingPrice - toleranceAmount:
|
|
|
161 |
# if validPromo.subsidy > subsidy:
|
|
|
162 |
# subsidy = validPromo.subsidy
|
|
|
163 |
# maxSalePrice = validPromo.salePrice
|
|
|
164 |
# promoStartDate = validPromo.startDate
|
|
|
165 |
# promoEndDate = validPromo.endDate
|
|
|
166 |
# promoType = AmazonPromotionType.SHORTTERM
|
|
|
167 |
# return subsidy, maxSalePrice, promoStartDate, promoEndDate, promoType
|
|
|
168 |
|
|
|
169 |
def figureOutBestPossibleSubsidy(validPromosWpi, validPromosDod, sellingPrice, purchaseDate, amazonOrderId, promotionDiscount, sku):
|
|
|
170 |
toleranceAmount = 50 if sellingPrice <=1000 else 100
|
|
|
171 |
subsidy, maxSalePrice, tolerance, startDate, endDate = 0.0, 0.0, 0.0, None, None
|
|
|
172 |
if len(validPromosDod)==0:
|
|
|
173 |
"""No info about deals , check wpiPromo or add buffer to dod promos"""
|
|
|
174 |
dodPromos = dodPromoMap.get(sku)
|
|
|
175 |
if dodPromos is not None:
|
|
|
176 |
for dodPromo in dodPromos:
|
|
|
177 |
if dodPromo.salePrice >=(sellingPrice -toleranceAmount) and purchaseDate >= dodPromo.startDate and purchaseDate <= dodPromo.endDate + timedelta(days=2) and dodPromo.subsidy > subsidy:
|
|
|
178 |
subsidy = dodPromo.subsidy
|
|
|
179 |
maxSalePrice = dodPromo.salePrice
|
|
|
180 |
tolerance = toleranceAmount
|
|
|
181 |
startDate = dodPromo.startDate
|
|
|
182 |
endDate = dodPromo.endDate
|
|
|
183 |
if subsidy == 0.0:
|
|
|
184 |
"""Still no information after adding buffer, checking wpi now"""
|
|
|
185 |
return getInformationForWpi(validPromosWpi, sellingPrice, purchaseDate, amazonOrderId, sku, toleranceAmount)
|
|
|
186 |
return subsidy, maxSalePrice, startDate, endDate, AmazonPromotionType.SHORTTERM, tolerance
|
|
|
187 |
elif len(validPromosDod)==1:
|
|
|
188 |
"""Single deal found"""
|
|
|
189 |
if validPromosDod[0].salePrice >= sellingPrice:
|
|
|
190 |
return validPromosDod[0].subsidy, validPromosDod[0].salePrice, validPromosDod[0].startDate, validPromosDod[0].endDate, AmazonPromotionType.SHORTTERM, 0.0
|
|
|
191 |
elif validPromosDod[0].salePrice >= sellingPrice - toleranceAmount:
|
|
|
192 |
return validPromosDod[0].subsidy, validPromosDod[0].salePrice, validPromosDod[0].startDate, validPromosDod[0].endDate, AmazonPromotionType.SHORTTERM, toleranceAmount
|
|
|
193 |
else:
|
|
|
194 |
return getInformationForWpi(validPromosWpi, sellingPrice, purchaseDate, amazonOrderId, sku, toleranceAmount)
|
|
|
195 |
else:
|
|
|
196 |
"""Mutiple validPromosDod exist for order.Selecting best possible"""
|
|
|
197 |
for promo in validPromosDod:
|
|
|
198 |
if promo.salePrice >=sellingPrice and promo.subsidy > subsidy:
|
|
|
199 |
subsidy = promo.subsidy
|
|
|
200 |
maxSalePrice = promo.salePrice
|
|
|
201 |
tolerance = 0.0
|
|
|
202 |
startDate = promo.startDate
|
|
|
203 |
endDate = promo.endDate
|
|
|
204 |
if subsidy == 0.0:
|
|
|
205 |
"""Adding tolerance amount"""
|
|
|
206 |
if promo.salePrice >=(sellingPrice-toleranceAmount) and promo.subsidy > subsidy:
|
|
|
207 |
subsidy = promo.subsidy
|
|
|
208 |
maxSalePrice = promo.salePrice
|
|
|
209 |
tolerance = toleranceAmount
|
|
|
210 |
startDate = promo.startDate
|
|
|
211 |
endDate = promo.endDate
|
|
|
212 |
return subsidy, maxSalePrice, startDate, endDate, AmazonPromotionType.SHORTTERM, tolerance
|
|
|
213 |
|
|
|
214 |
def getInformationForWpi(validPromosWpi, sellingPrice, purchaseDate, amazonOrderId, sku, toleranceAmount):
|
|
|
215 |
subsidy, maxSalePrice, tolerance, startDate, endDate, addedOnDate = 0.0, 0.0, 0.0, None, None, None
|
|
|
216 |
if len(validPromosWpi) == 0:
|
|
|
217 |
return subsidy, maxSalePrice, tolerance, startDate, endDate, 0.0
|
|
|
218 |
|
|
|
219 |
elif len(validPromosWpi) == 1:
|
|
|
220 |
if validPromosWpi[0].salePrice >= sellingPrice:
|
|
|
221 |
return validPromosWpi[0].subsidy, validPromosWpi[0].salePrice, validPromosWpi[0].startDate, validPromosWpi[0].endDate, AmazonPromotionType.LONGTERM, 0.0
|
|
|
222 |
|
|
|
223 |
elif validPromosWpi[0].salePrice >= sellingPrice - toleranceAmount:
|
|
|
224 |
return validPromosWpi[0].subsidy, validPromosWpi[0].salePrice, validPromosWpi[0].startDate, validPromosWpi[0].endDate, AmazonPromotionType.LONGTERM, toleranceAmount
|
|
|
225 |
|
|
|
226 |
else:
|
|
|
227 |
return subsidy, validPromosWpi[0].salePrice, validPromosWpi[0].startDate, validPromosWpi[0].endDate, AmazonPromotionType.LONGTERM, toleranceAmount
|
|
|
228 |
else:
|
|
|
229 |
"""Mutiple WPI"""
|
|
|
230 |
for wpiPromo in validPromosWpi:
|
|
|
231 |
if wpiPromo.salePrice >= sellingPrice and addedOnDate is None:
|
|
|
232 |
subsidy = wpiPromo.subsidy
|
|
|
233 |
maxSalePrice = wpiPromo.salePrice
|
|
|
234 |
startDate = wpiPromo.startDate
|
|
|
235 |
endDate = wpiPromo.endDate
|
|
|
236 |
tolerance = 0.0
|
|
|
237 |
addedOnDate = wpiPromo.addedOn
|
|
|
238 |
elif wpiPromo.salePrice >= (sellingPrice -toleranceAmount) and addedOnDate is None:
|
|
|
239 |
subsidy = wpiPromo.subsidy
|
|
|
240 |
maxSalePrice = wpiPromo.salePrice
|
|
|
241 |
startDate = wpiPromo.startDate
|
|
|
242 |
endDate = wpiPromo.endDate
|
|
|
243 |
tolerance = toleranceAmount
|
|
|
244 |
addedOnDate = wpiPromo.addedOn
|
|
|
245 |
elif wpiPromo.salePrice >= sellingPrice and wpiPromo.subsidy > subsidy :
|
|
|
246 |
subsidy = wpiPromo.subsidy
|
|
|
247 |
maxSalePrice = wpiPromo.salePrice
|
|
|
248 |
startDate = wpiPromo.startDate
|
|
|
249 |
endDate = wpiPromo.endDate
|
|
|
250 |
tolerance = 0.0
|
|
|
251 |
addedOnDate = wpiPromo.addedOn
|
|
|
252 |
elif wpiPromo.salePrice >= (sellingPrice- toleranceAmount) and wpiPromo.subsidy > subsidy:
|
|
|
253 |
subsidy = wpiPromo.subsidy
|
|
|
254 |
maxSalePrice = wpiPromo.salePrice
|
|
|
255 |
startDate = wpiPromo.startDate
|
|
|
256 |
endDate = wpiPromo.endDate
|
|
|
257 |
tolerance = toleranceAmount
|
|
|
258 |
addedOnDate = wpiPromo.addedOn
|
|
|
259 |
else:
|
|
|
260 |
pass
|
|
|
261 |
return subsidy, maxSalePrice, startDate, endDate, AmazonPromotionType.LONGTERM, tolerance
|
|
|
262 |
|
|
|
263 |
|
|
|
264 |
def writeHead(sheet,heading_xf):
|
|
|
265 |
sheet.write(0, 0, "Amazon Order Id", heading_xf)
|
|
|
266 |
sheet.write(0, 1, "Item Id", heading_xf)
|
|
|
267 |
sheet.write(0, 2, "Amazon Sku", heading_xf)
|
|
|
268 |
sheet.write(0, 3, "Location", heading_xf)
|
|
|
269 |
sheet.write(0, 4, "Product Group", heading_xf)
|
|
|
270 |
sheet.write(0, 5, "Brand", heading_xf)
|
|
|
271 |
sheet.write(0, 6, "Product Name", heading_xf)
|
|
|
272 |
sheet.write(0, 7, "Purchase Date", heading_xf)
|
|
|
273 |
sheet.write(0, 8, "Selling Price", heading_xf)
|
|
|
274 |
sheet.write(0, 9, "Quantity", heading_xf)
|
|
|
275 |
sheet.write(0, 10, "Item Promotion Discount", heading_xf)
|
|
|
276 |
sheet.write(0, 11, "Subsidy", heading_xf)
|
|
|
277 |
sheet.write(0, 12, "Tolerance Amount", heading_xf)
|
|
|
278 |
sheet.write(0, 13, "Promo Start Date", heading_xf)
|
|
|
279 |
sheet.write(0, 14, "Promo End Date", heading_xf)
|
|
|
280 |
sheet.write(0, 15, "MSP", heading_xf)
|
|
|
281 |
sheet.write(0, 16, "Promo Type", heading_xf)
|
|
|
282 |
|
|
|
283 |
def getItemInfo(item_id):
|
|
|
284 |
return itemInfoMap.get(item_id)
|
|
|
285 |
|
|
|
286 |
def main():
|
|
|
287 |
populateItemInfo()
|
|
|
288 |
populateSubsidyInfo()
|
|
|
289 |
writeReport()
|
|
|
290 |
|
|
|
291 |
if __name__=='__main__':
|
|
|
292 |
main()
|