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

 from elixir import *
-from sqlalchemy.sql import or_ ,func
+from sqlalchemy.sql import or_ ,func, asc
 from shop2020.config.client.ConfigClient import ConfigClient
 from shop2020.model.v1.catalog.impl import DataService
 from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, \
-Category, SourcePercentageMaster, MarketPlaceHistory
+Category, SourcePercentageMaster, MarketPlaceHistory, SnapdealItemUpdateHistory, MarketPlaceItemPrice
 from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
 from shop2020.thriftpy.model.v1.catalog.ttypes import CompetitionCategory, CompetitionBasis, SalesPotential,\
 Decision, RunType
 from shop2020.clients.CatalogClient import CatalogClient
 from shop2020.clients.InventoryClient import InventoryClient
             markReasonForMpItem(autoIncrementItem,'Proposed SP is greater than 10,000 and current sp is less than 10,000',Decision.AUTO_INCREMENT_FAILED)
             continue
         if getLastDaySale(autoIncrementItem.item_id)<=2:
             markReasonForMpItem(autoIncrementItem,'Last day sale is less than 3',Decision.AUTO_INCREMENT_FAILED)
             continue
+        antecedentPrice = session.query(MarketPlaceHistory.ourSellingPrice).filter(MarketPlaceHistory.item_id==autoIncrementItem.item_id).filter(MarketPlaceHistory.source==OrderSource.SNAPDEAL).filter(MarketPlaceHistory.timestamp>time-timedelta(days=1)).order_by(asc(MarketPlaceHistory.timestamp)).first()
+        if antecedentPrice is not None:
+            if float(math.ceil(autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice))-math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0])))/math.ceil(antecedentPrice[0]+max(10,.01*antecedentPrice[0]))>.02:
+                markReasonForMpItem(autoIncrementItem,'Maximum price increase in last 24 hours should be 2%',Decision.AUTO_INCREMENT_FAILED)
+                continue
+        mpItem = MarketplaceItems.get_by(itemId=autoIncrementItem.item_id,source=OrderSource.SNAPDEAL)
+        if mpItem.maximumSellingPrice is not None and mpItem.maximumSellingPrice > 0:
+            if autoIncrementItem.ourSellingPrice+max(10,.01*autoIncrementItem.ourSellingPrice) > mpItem.maximumSellingPrice:
+                markReasonForMpItem(autoIncrementItem,'Price cannot exceed Maximum Selling Price',Decision.AUTO_INCREMENT_FAILED)
+                continue
         #oosStatus = inventory_client.getOosStatusesForXDaysForItem(autoIncrementItem.item_id,0,3)
         #count,sale,daysOfStock = 0,0,0
         #for obj in oosStatus:
         #    if not obj.is_oos:
         #        count+=1
             sheet.write(sheet_iterator, 6, math.ceil(mpHistory.ourSellingPrice+max(10,.01*mpHistory.ourSellingPrice)))
         sheet_iterator+=1
     filename = "/tmp/snapdeal-report-"+runType+" " + str(timestamp) + ".xls"
     wbk.save(filename)
+    #EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
-    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["rajneesh.arora@saholic.com","rajveer.singh@saholic.com","kshitij.sood@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], [""], [])
+    EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["chandan.kumar@saholic.com","manoj.kumar@saholic.com","yukti.jain@saholic.com","ankush.dhingra@saholic.com","manoj.pal@saholic.com"], " Snapdeal Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
-def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
-    xstr = lambda s: s or ""
-    catalog_client = CatalogClient().get_client()
-    inventory_client = InventoryClient().get_client()
-    message="""<html>
-            <body>
-            <h3>Auto Decrease Items</h3>
-            <table border="1" style="width:100%;">
-            <thead>
-            <tr><th>Item Id</th>
-            <th>Product Name</th>
-            <th>Old Price</th>
-            <th>New Price</th>
-            <th>Old Margin %</th>
-            <th>New Margin %</th>
-            <th>Snapdeal Inventory</th>
-            </tr></thead>
-            <tbody>"""
-    for item in successfulAutoDecrease:
-        it = Item.query.filter_by(id=item.item_id).one()
-        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
-        sdItem = SnapdealItem.get_by(item_id=item.item_id)
-        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
-        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
-        newMargin = round((getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice)),1)
-        message+="""<tr>
-                <td style="text-align:center">"""+str(item.item_id)+"""</td>
-                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
-                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
-                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
-                <td style="text-align:center">"""+str(round(item.margin/item.ourSellingPrice,2))+"""</td>
-                <td style="text-align:center">"""+str(round(newMargin/item.proposedSellingPrice,2))+"""</td>
-                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
-                </tr>"""
-    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
-            <thead>
-            <tr><th>Item Id</th>
-            <th>Product Name</th>
-            <th>Old Price</th>
-            <th>New Price</th>
-            <th>Old Margin %</th>
-            <th>New Margin %</th>
-            <th>Snapdeal Inventory</th>
-            </tr></thead>
-            <tbody>"""
-    for item in successfulAutoIncrease:
-        it = Item.query.filter_by(id=item.item_id).one()
-        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
-        sdItem = SnapdealItem.get_by(item_id=item.item_id)
-        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
-        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
-        newMargin = round((getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice))),1)
-        message+="""<tr>
-                <td style="text-align:center">"""+str(item.item_id)+"""</td>
-                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
-                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
-                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
-                <td style="text-align:center">"""+str(round(item.margin/item.ourSellingPrice,2))+"""</td>
-                <td style="text-align:center">"""+str(round(newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)),2))+"""</td>
-                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
-                </tr>"""
-    message+="""</tbody></table></body></html>"""
-    print message
-    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
-    mailServer.ehlo()
-    mailServer.starttls()
-    mailServer.ehlo()
-    # Create the container (outer) email message.
-    msg = MIMEMultipart()
-    msg['Subject'] = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
-    msg['From'] = ""
-    msg['To'] = 'sku-recipients@saholic.com'
-    msg.preamble = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
-    html_msg = MIMEText(message, 'html')
-    msg.attach(html_msg)
-    mailServer.login("build@shop2020.in", "cafe@nes")
-    mailServer.sendmail("cafe@nes", ['rajneesh.arora@saholic.com','rajveer.singh@saholic.com','kshitij.sood@saholic.com'], msg.as_string())
 def commitExceptionList(exceptionList,timestamp):
     exceptionItems=[]
     for item in exceptionList:
         mpHistory = MarketPlaceHistory()
         mpHistory.timestamp = timestamp
         mpHistory.run = RunType._NAMES_TO_VALUES.get(snapdealItemInfo.runType)
         buyBoxList.append(mpHistory)
     session.commit()
     return buyBoxList
+def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
+    xstr = lambda s: s or ""
+    catalog_client = CatalogClient().get_client()
+    inventory_client = InventoryClient().get_client()
+    message="""<html>
+            <body>
+            <h3>Auto Decrease Items</h3>
+            <table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Product Name</th>
+            <th>Old Price</th>
+            <th>New Price</th>
+            <th>Old Margin</th>
+            <th>New Margin</th>
+            <th>Snapdeal Inventory</th>
+            </tr></thead>
+            <tbody>"""
+    for item in successfulAutoDecrease:
+        it = Item.query.filter_by(id=item.item_id).one()
+        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
+        sdItem = SnapdealItem.get_by(item_id=item.item_id)
+        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
+        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
+        newMargin = round(getNewOurTp(mpItem,item.proposedSellingPrice) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice))
+        message+="""<tr>
+                <td style="text-align:center">"""+str(item.item_id)+"""</td>
+                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
+                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
+                <td style="text-align:center">"""+str(math.ceil(item.proposedSellingPrice))+"""</td>
+                <td style="text-align:center">"""+str(round(item.margin))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
+                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/item.proposedSellingPrice)*100,1))+"%)"+"""</td>
+                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
+                </tr>"""
+    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Product Name</th>
+            <th>Old Price</th>
+            <th>New Price</th>
+            <th>Old Margin</th>
+            <th>New Margin</th>
+            <th>Snapdeal Inventory</th>
+            </tr></thead>
+            <tbody>"""
+    for item in successfulAutoIncrease:
+        it = Item.query.filter_by(id=item.item_id).one()
+        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
+        sdItem = SnapdealItem.get_by(item_id=item.item_id)
+        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
+        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
+        newMargin = round(getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
+        message+="""<tr>
+                <td style="text-align:center">"""+str(item.item_id)+"""</td>
+                <td style="text-align:center">"""+xstr(it.brand)+" "+xstr(it.model_name)+" "+xstr(it.model_number)+" "+xstr(it.color)+"""</td>
+                <td style="text-align:center">"""+str(item.ourSellingPrice)+"""</td>
+                <td style="text-align:center">"""+str(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))+"""</td>
+                <td style="text-align:center">"""+str(round((item.margin),1))+" ("+str(round((item.margin/item.ourSellingPrice)*100,1))+"%)"+"""</td>
+                <td style="text-align:center">"""+str(newMargin)+" ("+str(round((newMargin/(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))*100,1))+"%)"+"""</td>
+                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
+                </tr>"""
+    message+="""</tbody></table></body></html>"""
+    print message
+    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
+    mailServer.ehlo()
+    mailServer.starttls()
+    mailServer.ehlo()
+    # Create the container (outer) email message.
+    msg = MIMEMultipart()
+    msg['Subject'] = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
+    msg['From'] = ""
+    msg['To'] = 'sku-recipients@saholic.com'
+    msg.preamble = "Snapdeal Auto Pricing" + ' - ' + str(datetime.now())
+    html_msg = MIMEText(message, 'html')
+    msg.attach(html_msg)
+    mailServer.login("build@shop2020.in", "cafe@nes")
+    #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
+    mailServer.sendmail("cafe@nes", ['rajneesh.arora@saholic.com','rajveer.singh@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.as_string())
+def commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp):
+    catalog_client = CatalogClient().get_client()
+    inventory_client = InventoryClient().get_client()
+    for item in successfulAutoDecrease:
+        it = Item.query.filter_by(id=item.item_id).one()
+        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
+        sdItem = SnapdealItem.get_by(item_id=item.item_id)
+        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
+        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, item.proposedSellingPrice)
+        addHistory(sdItem)
+        sdItem.transferPrice = getNewOurTp(mpItem,item.proposedSellingPrice)
+        sdItem.sellingPrice = math.ceil(item.proposedSellingPrice)
+        sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)
+        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCost),2)
+        sdItem.updatedOn = timestamp
+        sdItem.priceUpdatedBy = 'SYSTEM'
+        mpItem.currentSp = sdItem.sellingPrice
+        mpItem.currentTp = sdItem.transferPrice
+        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,item.proposedSellingPrice)
+        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
+        markStatusForMarketplaceItems(sdItem,mpItem)
+    session.commit()
+    for item in successfulAutoIncrease:
+        it = Item.query.filter_by(id=item.item_id).one()
+        mpItem = MarketplaceItems.get_by(itemId=item.item_id,source=OrderSource.SNAPDEAL)
+        sdItem = SnapdealItem.get_by(item_id=item.item_id)
+        warehouse = inventory_client.getWarehouse(sdItem.warehouseId)
+        vatRate = catalog_client.getVatPercentageForItem(item.item_id, warehouse.stateId, math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
+        addHistory(sdItem)
+        sdItem.transferPrice = getNewOurTp(mpItem,item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
+        sdItem.sellingPrice = math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice))
+        sdItem.commission = round((mpItem.commission/100)*(sdItem.sellingPrice),2)
+        sdItem.serviceTax = round((mpItem.serviceTax/100)*(sdItem.commission+sdItem.courierCost),2)
+        sdItem.updatedOn = timestamp
+        sdItem.priceUpdatedBy = 'SYSTEM'
+        mpItem.currentSp = sdItem.sellingPrice
+        mpItem.currentTp = sdItem.transferPrice
+        mpItem.minimumPossibleTp = getNewLowestPossibleTp(mpItem,item.ourNlc,vatRate,sdItem.sellingPrice)
+        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,item.ourNlc,vatRate)
+        markStatusForMarketplaceItems(sdItem,mpItem)
+    session.commit()
+def addHistory(item):
+    itemHistory = SnapdealItemUpdateHistory()
+    itemHistory.item_id = item.item_id
+    itemHistory.exceptionPrice = item.exceptionPrice
+    itemHistory.warehouseId = item.warehouseId
+    itemHistory.isListedOnSnapdeal = item.isListedOnSnapdeal
+    itemHistory.transferPrice = item.transferPrice
+    itemHistory.sellingPrice = item.sellingPrice
+    itemHistory.courierCost = item.courierCost
+    itemHistory.commission = item.commission
+    itemHistory.serviceTax = item.serviceTax
+    itemHistory.suppressPriceFeed = item.suppressPriceFeed
+    itemHistory.suppressInventoryFeed = item.suppressInventoryFeed
+    itemHistory.updatedOn = item.updatedOn
+    itemHistory.maxNlc = item.maxNlc
+    itemHistory.skuAtSnapdeal = item.skuAtSnapdeal
+    itemHistory.supc = item.supc
+    itemHistory.priceUpdatedBy = item.priceUpdatedBy
+def markStatusForMarketplaceItems(snapdealItem,marketplaceItem):
+    markUpdatedItem = MarketPlaceItemPrice.query.filter(MarketPlaceItemPrice.item_id==snapdealItem.item_id).filter(MarketPlaceItemPrice.source==marketplaceItem.source).first()
+    if markUpdatedItem is None:
+        marketPlaceItemPrice = MarketPlaceItemPrice()
+        marketPlaceItemPrice.item_id = snapdealItem.item_id
+        marketPlaceItemPrice.source = marketplaceItem.source
+        marketPlaceItemPrice.lastUpdatedOn = snapdealItem.updatedOn
+        marketPlaceItemPrice.sellingPrice = snapdealItem.sellingPrice
+        marketPlaceItemPrice.suppressPriceFeed = snapdealItem.suppressPriceFeed
+        marketPlaceItemPrice.isListedOnSource = snapdealItem.isListedOnSnapdeal
+    else:
+        if (markUpdatedItem.sellingPrice!=snapdealItem.sellingPrice or markUpdatedItem.suppressPriceFeed!=snapdealItem.suppressPriceFeed or markUpdatedItem.isListedOnSource!=snapdealItem.isListedOnSnapdeal):
+            markUpdatedItem.lastUpdatedOn = snapdealItem.updatedOn
+        markUpdatedItem.sellingPrice = snapdealItem.sellingPrice
+        markUpdatedItem.suppressPriceFeed = snapdealItem.suppressPriceFeed
+        markUpdatedItem.isListedOnSource = snapdealItem.isListedOnSnapdeal
 def getOtherTp(snapdealDetails,val,spm):
     if val.parent_category==10011:
         commissionPercentage = spm.competitorCommissionAccessory
     return round(lowest_possible_tp,2)
 def getNewOurTp(mpItem,proposedSellingPrice):
     ourTp = proposedSellingPrice- proposedSellingPrice*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100));
     return round(ourTp,2)
+def getNewLowestPossibleSp(mpItem,nlc,vatRate):
+    lowestPossibleSp = (nlc+(mpItem.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(vatRate/100))+(15+mpItem.otherCost)*(1+(vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(vatRate)/100)-(mpItem.returnProvision/100)*(1+(vatRate)/100));
+    return round(lowestPossibleSp,2)
 def getLowestPossibleSp(snapdealDetails,val,spm,mpItem):
     if snapdealDetails.rank==0:
         return mpItem.minimumPossibleSp
     lowestPossibleSp = (val.nlc+(val.courierCost+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate/100))+(15+mpItem.otherCost)*(1+(val.vatRate)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(val.vatRate)/100)-(mpItem.returnProvision/100)*(1+(val.vatRate)/100));
         previousAutoFav, nowAutoFav = markAutoFavourite()
     if options.runType=='FULL':
         writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, previousAutoFav, nowAutoFav,timestamp, options.runType)
     else:
         writeReport(cantCompete, buyBoxItems, competitive, competitiveNoInventory, exceptionList, negativeMargin, None, None, timestamp, options.runType)
+    commitPricing(successfulAutoDecrease,successfulAutoIncrease,timestamp)
     sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
 if __name__ == '__main__':
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/SnapdealHistoryPopulator.py – Rev 9981 → 9990