WebSVN – SmartDukaan – Diff – /trunk/PyProj/src/shop2020/model/v1/catalog/script/FlipkartHistoryPopulator.py

         mpHistory.prefferedSellerTp = flipkartPricing.prefSellerTp
         mpHistory.prefferedSellerShippingTime = ''
         mpHistory.prefferedSellerShippingTime= str(flipkartDetails.shippingTimeLowerLimitPrefSeller) if flipkartDetails.shippingTimeUpperLimitPrefSeller==0\
         else str(flipkartDetails.shippingTimeLowerLimitPrefSeller)+'-'+str(flipkartDetails.shippingTimeUpperLimitPrefSeller)
         mpHistory.margin = mpHistory.ourTp - mpHistory.lowestPossibleTp
-        proposed_sp = max(mpHistory.lowestSellingPrice - max((20, mpHistory.lowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
+        proposed_sp = max(flipkartDetails.lowestSellerSp - max((10, flipkartDetails.lowestSellerSp*0.001)), flipkartPricing.lowestPossibleSp)
         proposed_tp = getTargetTp(proposed_sp,mpItem)
         mpHistory.proposedSellingPrice = proposed_sp
         mpHistory.proposedTp = proposed_tp
         mpHistory.totalSeller = flipkartDetails.totalAvailableSeller
         mpHistory.avgSales = (itemSaleMap.get(flipkartItemInfo.item_id))[3]
     flipkartDetails = __FlipkartDetails(rank ,ourSp , secondLowestSellerSp, prefSellerSp, lowestSellerSp, lowestSellerScore, prefSellerScore, secondLowestSellerScore, ourScore, int(shippingTimeLowerLimitLowestSeller),int(shippingTimeUpperLimitLowestSeller), \
     int(shippingTimeLowerLimitPrefSeller), int(shippingTimeUpperLimitPrefSeller), int(shippingTimeLowerLimitOur), int(shippingTimeUpperLimitOur), int(shippingTimeLowerLimitSecondLowestSeller), int(shippingTimeUpperLimitSecondLowestSeller), len(sortedVendorsData), lowestSellerName, lowestSellerCode, secondLowestSellerName, secondLowestSellerCode, prefSellerName, prefSellerCode, lowestSellerBuyTrend, \
     ourBuyTrend, prefSellerBuyTrend, secondLowestSellerBuyTrend, ourCode)
     if flipkartDetails.ourBuyTrend == 'PrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.secondLowestSellerSp:
-        print "Under PrefCheap category.Swiching data for ",flipkartSerialNumber
+        print "Under PrefCheap category.Switching data for ",flipkartSerialNumber
         flipkartDetails.lowestSellerSp, flipkartDetails.secondLowestSellerSp = flipkartDetails.secondLowestSellerSp,flipkartDetails.lowestSellerSp
         flipkartDetails.lowestSellerScore, flipkartDetails.secondLowestSellerScore = flipkartDetails.secondLowestSellerScore,flipkartDetails.lowestSellerScore
         flipkartDetails.shippingTimeLowerLimitLowestSeller, flipkartDetails.shippingTimeLowerLimitSecondLowestSeller = flipkartDetails.shippingTimeLowerLimitSecondLowestSeller,flipkartDetails.shippingTimeLowerLimitLowestSeller
         flipkartDetails.shippingTimeUpperLimitLowestSeller, flipkartDetails.shippingTimeUpperLimitSecondLowestSeller = flipkartDetails.shippingTimeUpperLimitSecondLowestSeller,flipkartDetails.shippingTimeUpperLimitLowestSeller
         flipkartDetails.lowestSellerName, flipkartDetails.secondLowestSellerName = flipkartDetails.secondLowestSellerName,flipkartDetails.lowestSellerName
         flipkartDetails.lowestSellerCode, flipkartDetails.secondLowestSellerCode = flipkartDetails.secondLowestSellerCode,flipkartDetails.lowestSellerCode
         flipkartDetails.lowestSellerBuyTrend, flipkartDetails.secondLowestSellerBuyTrend = flipkartDetails.secondLowestSellerBuyTrend,flipkartDetails.lowestSellerBuyTrend
         flipkartDetails.rank=1
     if flipkartDetails.ourBuyTrend == 'NPrefCheap'and flipkartDetails.rank!=1 and flipkartDetails.ourSp==flipkartDetails.lowestSellerSp:
-        print "Under NPrefCheap category.Swiching data for ",flipkartSerialNumber
+        print "Under NPrefCheap category.Switching data for ",flipkartSerialNumber
         flipkartDetails.lowestSellerSp = flipkartDetails.ourSp
         flipkartDetails.lowestSellerScore = flipkartDetails.ourScore
         flipkartDetails.shippingTimeLowerLimitLowestSeller = flipkartDetails.shippingTimeLowerLimitOur
         flipkartDetails.shippingTimeUpperLimitLowestSeller = flipkartDetails.shippingTimeUpperLimitOur
         flipkartDetails.lowestSellerName = 'Saholic'
     sheet.write(0, 30, "Lowest Possible TP", heading_xf)
     sheet.write(0, 31, "Target SP", heading_xf)
     sheet.write(0, 32, "Target TP", heading_xf)
     sheet.write(0, 33, "Margin Increased Potential", heading_xf)
     sheet.write(0, 34, "Total Seller", heading_xf)
+    sheet.write(0, 35, "Auto Pricing Decision", heading_xf)
+    sheet.write(0, 36, "Reason", heading_xf)
+    sheet.write(0, 37, "Updated Price", heading_xf)
     sheet_iterator = 1
     buyBoxItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
     .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
     .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
         target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
         sheet.write(sheet_iterator, 31, proposed_sp)
         sheet.write(sheet_iterator, 32, proposed_tp)
         sheet.write(sheet_iterator, 33, proposed_tp -mpHistory.ourTp )
         sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
+        if mpHistory.decision is None:
+            sheet.write(sheet_iterator, 35, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 35, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
+        sheet.write(sheet_iterator, 36, mpHistory.reason)
+        sheet.write(sheet_iterator, 37, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
         sheet_iterator+=1
     buyBoxItems[:] = []
     sheet = wbk.add_sheet('Pref Not Cheap')
     sheet.write(0, 29, "Lowest Possible SP", heading_xf)
     sheet.write(0, 30, "Lowest Possible TP", heading_xf)
     sheet.write(0, 31, "Target SP", heading_xf)
     sheet.write(0, 32, "Target TP", heading_xf)
     sheet.write(0, 33, "Total Seller", heading_xf)
+    sheet.write(0, 34, "Auto Pricing Decision", heading_xf)
+    sheet.write(0, 35, "Reason", heading_xf)
+    sheet.write(0, 36, "Updated Price", heading_xf)
     sheet_iterator = 1
     prefNotCheapItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
     .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
     .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
         sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
         sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
         sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
         sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
         sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
-        proposed_sp = max(mpHistory.lowestSellingPrice - max((20, mpHistory.lowestSellingPrice*0.002)), mpHistory.lowestPossibleSp)
+        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
         proposed_tp = getTargetTp(proposed_sp,mpItem)
         target_nlc = proposed_tp -  mpHistory.lowestPossibleTp + mpHistory.ourNlc
         sheet.write(sheet_iterator, 31, proposed_sp)
         sheet.write(sheet_iterator, 32, proposed_tp)
         sheet.write(sheet_iterator, 33, mpHistory.totalSeller)
+        if mpHistory.decision is None:
+            sheet.write(sheet_iterator, 34, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 34, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
+        sheet.write(sheet_iterator, 35, mpHistory.reason)
+        sheet.write(sheet_iterator, 36, math.ceil(mpHistory.proposedSellingPrice))
         sheet_iterator+=1
     prefNotCheapItems[:] = []
     sheet = wbk.add_sheet('Cheap But Not Pref')
     sheet.write(0, 31, "Target SP", heading_xf)
     sheet.write(0, 32, "Target TP", heading_xf)
     sheet.write(0, 33, "Target NLC", heading_xf)
     sheet.write(0, 34, "Sales Potential", heading_xf)
     sheet.write(0, 35, "Total Seller", heading_xf)
+    sheet.write(0, 36, "Auto Pricing Decision", heading_xf)
+    sheet.write(0, 37, "Reason", heading_xf)
+    sheet.write(0, 38, "Updated Price", heading_xf)
     sheet_iterator = 1
     competitiveItems = session.query(MarketPlaceHistory,FlipkartItem,MarketplaceItems,Item)\
     .join((FlipkartItem,MarketPlaceHistory.item_id==FlipkartItem.item_id))\
     .join((MarketplaceItems,MarketPlaceHistory.item_id==MarketplaceItems.itemId))\
         sheet.write(sheet_iterator, 26, getOosString((itemSaleMap.get(mpHistory.item_id))[1]))
         sheet.write(sheet_iterator, 27, (itemSaleMap.get(mpHistory.item_id))[3])
         sheet.write(sheet_iterator, 28, mpHistory.ourNlc)
         sheet.write(sheet_iterator, 29, mpHistory.lowestPossibleSp)
         sheet.write(sheet_iterator, 30, mpHistory.lowestPossibleTp)
-        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestSellingPrice)
+        proposed_sp = max(mpHistory.lowestSellingPrice - max((10, mpHistory.lowestSellingPrice*0.001)), mpHistory.lowestPossibleSp)
         proposed_tp = getTargetTp(proposed_sp,mpItem)
         target_nlc = proposed_tp - mpHistory.lowestPossibleTp + mpHistory.ourNlc
         sheet.write(sheet_iterator, 31, proposed_sp)
         sheet.write(sheet_iterator, 32, proposed_tp)
         sheet.write(sheet_iterator, 33, target_nlc)
         sheet.write(sheet_iterator, 34, getSalesPotential(mpHistory.lowestSellingPrice,mpHistory.ourNlc))
         sheet.write(sheet_iterator, 35, mpHistory.totalSeller)
+        if mpHistory.decision is None:
+            sheet.write(sheet_iterator, 36, 'Auto Pricing Inactive')
+            sheet_iterator+=1
+            continue
+        sheet.write(sheet_iterator, 36, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
+        sheet.write(sheet_iterator, 37, mpHistory.reason)
+        sheet.write(sheet_iterator, 38, math.ceil(mpHistory.proposedSellingPrice))
         sheet_iterator+=1
     competitiveItems[:]=[]
     sheet = wbk.add_sheet('Negative Margin')
             sheet.write(sheet_iterator, 33, getSalesPotential(mpHistory.lowestPossibleSp,mpHistory.ourNlc))
             sheet.write(sheet_iterator, 34, mpHistory.totalSeller)
             sheet_iterator+=1
     competitiveNoInventory[:]=[]
-    autoPricingItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.decision.in_([1,2,3,4])).all()
+#    autoPricingItems = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.decision.in_([1,2,3,4])).all()
-    sheet = wbk.add_sheet('Auto Inc and Dec')
+#    sheet = wbk.add_sheet('Auto Inc and Dec')
+#
-    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+#    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+#
-    excel_integer_format = '0'
+#    excel_integer_format = '0'
-    integer_style = xlwt.XFStyle()
+#    integer_style = xlwt.XFStyle()
-    integer_style.num_format_str = excel_integer_format
+#    integer_style.num_format_str = excel_integer_format
-    xstr = lambda s: s or ""
+#    xstr = lambda s: s or ""
+#
-    sheet.write(0, 0, "Item ID", heading_xf)
+#    sheet.write(0, 0, "Item ID", heading_xf)
-    sheet.write(0, 1, "Brand", heading_xf)
+#    sheet.write(0, 1, "Brand", heading_xf)
-    sheet.write(0, 2, "Product Name", heading_xf)
+#    sheet.write(0, 2, "Product Name", heading_xf)
-    sheet.write(0, 3, "Decision", heading_xf)
+#    sheet.write(0, 3, "Decision", heading_xf)
-    sheet.write(0, 4, "Reason", heading_xf)
+#    sheet.write(0, 4, "Reason", heading_xf)
-    sheet.write(0, 5, "Old Selling Price", heading_xf)
+#    sheet.write(0, 5, "Old Selling Price", heading_xf)
-    sheet.write(0, 6, "Selling Price Updated",heading_xf)
+#    sheet.write(0, 6, "Selling Price Updated",heading_xf)
+#
-    sheet_iterator=1
+#    sheet_iterator=1
-    for autoPricingItem in autoPricingItems:
+#    for autoPricingItem in autoPricingItems:
-        mpHistory = autoPricingItem[0]
+#        mpHistory = autoPricingItem[0]
-        item = autoPricingItem[1]
+#        item = autoPricingItem[1]
-        it = Item.query.filter_by(id=item.id).one()
+#        it = Item.query.filter_by(id=item.id).one()
-        sheet.write(sheet_iterator, 0, item.id)
+#        sheet.write(sheet_iterator, 0, item.id)
-        sheet.write(sheet_iterator, 1, it.brand)
+#        sheet.write(sheet_iterator, 1, it.brand)
-        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
+#        sheet.write(sheet_iterator, 2, xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color))
-        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
+#        sheet.write(sheet_iterator, 3, Decision._VALUES_TO_NAMES.get(mpHistory.decision))
-        sheet.write(sheet_iterator, 4, mpHistory.reason)
+#        sheet.write(sheet_iterator, 4, mpHistory.reason)
-        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
+#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_DECREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
+#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
-            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
+#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.proposedSellingPrice))
-        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
+#        if Decision._VALUES_TO_NAMES.get(mpHistory.decision) == "AUTO_INCREMENT_SUCCESS":
-            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
+#            sheet.write(sheet_iterator, 5, mpHistory.ourSellingPrice)
-            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
+#            sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
-        sheet_iterator+=1
+#        sheet_iterator+=1
     filename = "/tmp/flipkart-report-"+runType+" " + str(timestamp) + ".xls"
     wbk.save(filename)
     try:
         EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Flipkart Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
         try:
             smtpServer.sendmail(sender, recipients, msg.as_string())
             print "Successfully sent email"
         except:
             print "Error: unable to send email."
+def sendAlertForNegativeMargins(timestamp):
+    xstr = lambda s: s or ""
+    negativeMargins = session.query(MarketPlaceHistory,Item).join((Item,MarketPlaceHistory.item_id==Item.id)).filter(MarketPlaceHistory.timestamp==timestamp).filter(MarketPlaceHistory.source==OrderSource.FLIPKART).filter(MarketPlaceHistory.competitiveCategory==CompetitionCategory.NEGATIVE_MARGIN).all()
+    if len(negativeMargins) == 0:
+        return
+    message="""<html>
+            <body>
+            <h3 style="color:red;font-weight:bold;">Flipkart Negative Margins</h3>
+            <table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Product Name</th>
+            <th>SP</th>
+            <th>TP</th>
+            <th>Lowest Possible SP</th>
+            <th>Lowest Possible TP</th>
+            <th>Margin</th>
+            <th>Margin %</th>
+            <th>Flipkart Inventory</th>
+            <th>Total Inventory</th>
+            <th>Sales History</th>
+            </tr></thead>
+            <tbody>"""
+    for item in negativeMargins:
+        mpHistory = item[0]
+        catItem = item[1]
+        netInventory=''
+        if not inventoryMap.has_key(item):
+            netInventory='Info Not Available'
+        else:
+            netInventory = str(getNetAvailability(inventoryMap.get(item)))
+        message+="""<tr>
+            <td style="text-align:center">"""+str(mpHistory.item_id)+"""</td>
+            <td style="text-align:center">"""+xstr(catItem.brand)+" "+xstr(catItem.model_name)+" "+xstr(catItem.model_number)+" "+xstr(catItem.color)+"""</td>
+            <td style="text-align:center">"""+str(mpHistory.ourSellingPrice)+"""</td>
+            <td style="text-align:center">"""+str(mpHistory.ourTp)+"""</td>
+            <td style="text-align:center">"""+str(mpHistory.lowestPossibleSp)+"""</td>
+            <td style="text-align:center">"""+str(mpHistory.lowestPossibleTp)+"""</td>
+            <td style="text-align:center">"""+str(mpHistory.margin)+"""</td>
+            <td style="text-align:center">"""+str(round(mpHistory.margin/mpHistory.ourSellingPrice,2))+"""</td>
+            <td style="text-align:center">"""+str(mpHistory.ourInventory)+"""</td>
+            <td style="text-align:center">"""+netInventory+"""</td>
+            <td style="text-align:center">"""+getOosString((itemSaleMap.get(item))[1])+"""</td>
+            </tr>"""
+    message+="""</tbody></table></body></html>"""
+    print message
+    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
+    mailServer.ehlo()
+    mailServer.starttls()
+    mailServer.ehlo()
+    recipients = ['kshitij.sood@saholic.com']
+    #recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','vikram.raghav@saholic.com','kshitij.sood@saholic.com','khushal.bhatia@saholic.com','chaitnaya.vats@saholic.com','chandan.kumar@saholic.com','manoj.kumar@saholic.com','yukti.jain@saholic.com','ankush.dhingra@saholic.com','manoj.pal@saholic.com']
+    msg = MIMEMultipart()
+    msg['Subject'] = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
+    msg['From'] = ""
+    msg['To'] = ",".join(recipients)
+    msg.preamble = "Flipkart Negative Margin" + ' - ' + str(datetime.now())
+    html_msg = MIMEText(message, 'html')
+    msg.attach(html_msg)
+    try:
+        mailServer.login("build@shop2020.in", "cafe@nes")
+        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
+        mailServer.sendmail("cafe@nes", recipients, msg.as_string())
+    except Exception as e:
+        print e
+        print "Unable to send Flipkart Negative margin mail.Lets try local SMTP"
+        smtpServer = smtplib.SMTP('localhost')
+        smtpServer.set_debuglevel(1)
+        sender = 'support@shop2020.in'
+        try:
+            smtpServer.sendmail(sender, recipients, msg.as_string())
+            print "Successfully sent email"
+        except:
+            print "Error: unable to send email."
 def main():
     parser = optparse.OptionParser()
     parser.add_option("-t", "--type", dest="runType",
     if previousProcessingTimestamp[0] is not None:
         processLostBuyBoxItems(previousProcessingTimestamp[0],timestamp)
     if options.runType=='FULL':
         cheapButNotPrefAlert(timestamp)
         sendPricingMismatch(timestamp)
+        sendAlertForNegativeMargins(timestamp)
 if __name__ == '__main__':
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/FlipkartHistoryPopulator.py – Rev 11774 → 11775