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

 from elixir import *
 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
+from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, Category, \
+MarketPlaceUpdateHistory
 from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
 import mechanize
 import sys
 import cookielib
 from time import sleep
 from shop2020.utils.EmailAttachmentSender import get_attachment_part
 from email.mime.text import MIMEText
 import email
 from email.mime.multipart import MIMEMultipart
 import email.encoders
+import copy
 config_client = ConfigClient()
 host = config_client.get_property('staging_hostname')
 DataService.initialize(db_hostname=host)
+courierCostToSync = []
+oldPricing = []
 class __SnapdealInfo:
     def __init__(self, sellingPrice, weight, transferPrice, commission, commissionPercentage, courierCost, sellingPriceSnapdeal, transferPriceSnapdeal, fixedMargin, \
                  fixedMarginPercentage, collectionCharges, logisticCostSnapdeal, weightSnapdeal, supc, itemId, parentCategory, productGroup ,brand, modelName, modelNumber, \
-                 color,reason):
+                 color,reason,woodenPackagingCost):
         self.sellingPrice = sellingPrice
         self.weight = weight
         self.transferPrice = transferPrice
         self.commission = commission
         self.brand = brand
         self.modelName = modelName
         self.modelNumber = modelNumber
         self.color = color
         self.reason = reason
+        self.woodenPackagingCost = woodenPackagingCost
 def getBrowserObject():
     br = mechanize.Browser(factory=mechanize.RobustFactory())
     cj = cookielib.LWPCookieJar()
 def populateStuff(br):
     exceptionList = []
     fetchedItems = []
     items = session.query(SnapdealItem,MarketplaceItems,Item).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).join((Item,SnapdealItem.item_id==Item.id)).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
+    #items = session.query(SnapdealItem,MarketplaceItems,Item).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).join((Item,SnapdealItem.item_id==Item.id)).filter(SnapdealItem.item_id==12613).filter(MarketplaceItems.source==OrderSource.SNAPDEAL).all()
     for item in items:
         snapdealItem = item[0]
         marketplaceItem = item[1]
         ds_item = item[2]
         category = Category.query.filter_by(id=ds_item.category).one()
         snapdealInfo.weight = ds_item.weight
         fetchedItems.append(snapdealInfo)
     return exceptionList, fetchedItems
 def fetchData(br,supc):
+    print "Fetching data for ",supc
     url="http://seller.snapdeal.com/pricing/search?searchType=SUPC&searchValue=%s&gridType=normal&_search=false&nd=1396007375971&rows=30&page=1&sidx=&sord=asc"%(supc)
     sleep(1)
     response = br.open(url)
     dataform = str(response.read()).strip("'<>() ").replace('\'', '\"')
     struct = json.loads(dataform)
     sdObj = struct['rows'][0]
     print sdObj
     snapdealInfo = __SnapdealInfo(None,None,None,None,None,None,sdObj['sellingPrice'],sdObj['netSellerPayable'],sdObj['fixedMarginAmount'],sdObj['fixedMarginPercent'],sdObj['collectionCharges'],sdObj['logisticCost'],sdObj['deadWeight'],supc,None, \
-                    None, None, None, None, None, None, None)
+                    None, None, None, None, None, None, None,sdObj['woodenPackagingCost'])
     return snapdealInfo
 def filterData(fetchedItems):
     filterList = []
     for data in fetchedItems:
         if ( data.transferPrice - data.transferPriceSnapdeal >= -3 ) and (data.transferPrice - data.transferPriceSnapdeal <= 3):
+            print "continue for",data.itemId
             continue
         filterList.append(data)
     return filterList
 def sendMail(filteredData,exceptionList):
             <td style="text-align:center">"""+str(data.commissionPercentage)+"%"+"""</td>
             <td style="text-align:center">"""+str(round(float(data.fixedMarginPercentage)/1.1236,2))+"%"+"""</td>
             <td style="text-align:center">"""+str(data.weight*1000)+" gms"+"""</td>
             <td style="text-align:center">"""+str(data.weightSnapdeal)+" gms"+"""</td>
             <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>
-            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2))+"""</td>
+            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))+"""</td>
             <td style="text-align:center">"""+getReason(data)+"""</td>
             </tr>"""
     message+="""</tbody></table>"""
     message+="""
             <h3>High TP On Snapdeal</h3>
             <td style="text-align:center">"""+str(data.commissionPercentage)+"%"+"""</td>
             <td style="text-align:center">"""+str(round(float(data.fixedMarginPercentage)/1.1236,2))+"%"+"""</td>
             <td style="text-align:center">"""+str(data.weight*1000)+" gms"+"""</td>
             <td style="text-align:center">"""+str(data.weightSnapdeal)+" gms"+"""</td>
             <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>
-            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2))+"""</td>
+            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))+"""</td>
             <td style="text-align:center">"""+getReason(data)+"""</td>
             </tr>"""
     message+="""</tbody></table>"""
     message+="""
             <h3>Unable To Fetch Items</h3>
             sheet.write(sheet_iterator, 12, data.commissionPercentage)
             sheet.write(sheet_iterator, 13, round(float(data.fixedMarginPercentage)/1.1236,2))
             sheet.write(sheet_iterator, 14, data.weight*1000)
             sheet.write(sheet_iterator, 15, data.weightSnapdeal)
             sheet.write(sheet_iterator, 16, round(data.courierCost*1.1236,2))
-            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2))
+            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))
-            sheet.write(sheet_iterator, 18, getReason(data))
+            sheet.write(sheet_iterator, 18, getReasonSheet(data))
             sheet_iterator+=1
     sheet = wbk.add_sheet('High TP SD')
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
             sheet.write(sheet_iterator, 12, data.commissionPercentage)
             sheet.write(sheet_iterator, 13, round(float(data.fixedMarginPercentage)/1.1236,2))
             sheet.write(sheet_iterator, 14, data.weight*1000)
             sheet.write(sheet_iterator, 15, data.weightSnapdeal)
             sheet.write(sheet_iterator, 16, round(data.courierCost*1.1236,2))
-            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2))
+            sheet.write(sheet_iterator, 17, round(data.logisticCostSnapdeal,2)+round(data.woodenPackagingCost,2))
-            sheet.write(sheet_iterator, 18, getReason(data))
+            sheet.write(sheet_iterator, 18, getReasonSheet(data))
             sheet_iterator+=1
     sheet = wbk.add_sheet('Exceptions')
     heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
         print e
         print "Unable to send report.Trying with local SMTP"
         smtpServer = smtplib.SMTP('localhost')
         smtpServer.set_debuglevel(1)
         sender = 'support@shop2020.in'
-        #recipients = ['kshitij.sood@saholic.com']
+        recipients = ['kshitij.sood@saholic.com']
         msg = MIMEMultipart()
         msg['Subject'] = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())
         msg['From'] = sender
-        recipients = ['rajneesh.arora@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','sandeep.sachdeva@saholic.com']
+        #recipients = ['rajneesh.arora@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','sandeep.sachdeva@saholic.com']
         msg['To'] = ",".join(recipients)
         fileMsg = email.mime.base.MIMEBase('application','vnd.ms-excel')
         fileMsg.set_payload(file(filename).read())
         email.encoders.encode_base64(fileMsg)
         fileMsg.add_header('Content-Disposition','attachment;filename=snapdeal_tp_recon.xls')
             print "Successfully sent email"
         except:
             print "Error: unable to send email."
 def getReason(data):
+    global courierCostToSync
     reason=""
     if data.sellingPrice!=data.sellingPriceSnapdeal:
         reason+="Selling Price is different."
     if data.commissionPercentage!= round(float(data.fixedMarginPercentage)/1.1236,2):
         reason+="Commission is different."
-    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal):
+    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal+data.woodenPackagingCost):
         reason+="Courier Cost is different-Check Weight."
+        courierCostToSync.append(data)
     return reason
+def getReasonSheet(data):
+    reason=""
+    if data.sellingPrice!=data.sellingPriceSnapdeal:
+        reason+="Selling Price is different."
+    if data.commissionPercentage!= round(float(data.fixedMarginPercentage)/1.1236,2):
+        reason+="Commission is different."
+    if round(data.courierCost*1.1236)!=round(data.logisticCostSnapdeal+data.woodenPackagingCost):
+        reason+="Courier Cost is different-Check Weight."
+    return reason
+def syncCourierCost(courierCostToSync):
+    global oldPricing
+    for item in courierCostToSync:
+        sdItem = SnapdealItem.get_by(item_id=item.itemId)
+        mpItem = MarketplaceItems.get_by(itemId=item.itemId, source=OrderSource.SNAPDEAL)
+        oldMpItem = copy.deepcopy(mpItem)
+        temp = []
+        temp.append(item)
+        temp.append(oldMpItem)
+        addHistory(sdItem)
+        sdItem.courierCostMarketplace = round((item.logisticCostSnapdeal+item.woodenPackagingCost)/1.1236)
+        sdItem.transferPrice = getNewTp(mpItem,item)
+        sdItem.serviceTax = getNewServiceTax(mpItem,sdItem,item)
+        mpItem.courierCostMarketplace = round((item.logisticCostSnapdeal+item.woodenPackagingCost)/1.1236)
+        mpItem.currentTp = getNewTp(mpItem,item)
+        mpItem.minimumPossibleSp = getNewLowestPossibleSp(mpItem,sdItem,item)
+        temp.append(mpItem)
+        oldPricing.append(temp)
+    session.commit()
+def addHistory(item):
+    itemHistory = MarketPlaceUpdateHistory()
+    itemHistory.item_id = item.item_id
+    itemHistory.source = OrderSource.SNAPDEAL
+    itemHistory.exceptionPrice = item.exceptionPrice
+    itemHistory.warehouseId = item.warehouseId
+    itemHistory.isListedOnSource = 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.skuAtSource = item.skuAtSnapdeal
+    itemHistory.marketPlaceSerialNumber = item.supc
+    itemHistory.priceUpdatedBy = item.priceUpdatedBy
+    itemHistory.courierCostMarketplace = item.courierCostMarketplace
+def getNewTp(mpItem,data):
+    ourTp = mpItem.currentSp- mpItem.currentSp*(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))-((data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236+mpItem.closingFee)*(1+(mpItem.serviceTax/100))-(max(20,(mpItem.pgFee/100)*mpItem.currentSp)*(1+(mpItem.serviceTax/100)));
+    return round(ourTp,2)
+def getNewServiceTax(mpItem,sdItem,data):
+    return round(mpItem.serviceTax/100*(sdItem.commission+(data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236),2)
+def getNewLowestPossibleSp(mpItem,sdItem,data):
+    if (mpItem.pgFee/100)*mpItem.currentSp>=20:
+        lowestPossibleSp = (sdItem.maxNlc+(((data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236)+mpItem.closingFee)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat/100))+(15+mpItem.otherCost)*(1+(mpItem.vat)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100+mpItem.pgFee/100)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat)/100)-(mpItem.returnProvision/100)*(1+(mpItem.vat)/100));
+    else:
+        lowestPossibleSp = (sdItem.maxNlc+(((data.logisticCostSnapdeal+data.woodenPackagingCost)/1.1236)+mpItem.closingFee+20)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat/100))+(15+mpItem.otherCost)*(1+(mpItem.vat)/100))/(1-(mpItem.commission/100+mpItem.emiFee/100)*(1+(mpItem.serviceTax/100))*(1+(mpItem.vat)/100)-(mpItem.returnProvision/100)*(1+(mpItem.vat)/100));
+    return round(lowestPossibleSp,2)
+def sendCCSyncMail():
+    if len(courierCostToSync) == 0:
+        return
+    xstr = lambda s: s or ""
+    message="""<html>
+            <body>
+            <h3>Courier cost synced</h3>
+            <table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Product Name</th>
+            <th>Selling Price</th>
+            <th>Old Transfer Price</th>
+            <th>New Transfer Price</th>
+            <th>Old Courier Cost</th>
+            <th>New Courier Cost</th>
+            <th>Old Margin</th>
+            <th>New Margin</th>
+            <th>Old Margin %</th>
+            <th>New Margin %</th>
+            </tr></thead>
+            <tbody>"""
+    for value in oldPricing:
+        data = value[0]
+        mpItemOld = value[1]
+        mpItemNew = value[2]
+        message+="""<tr>
+        <td style="text-align:center">"""+str(data.itemId)+"""</td>
+        <td style="text-align:center">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>
+        <td style="text-align:center">"""+str(mpItemOld.currentSp)+"""</td>
+        <td style="text-align:center">"""+str(mpItemOld.currentTp)+"""</td>
+        <td style="text-align:center">"""+str(mpItemNew.currentTp)+"""</td>
+        <td style="text-align:center">"""+str(mpItemOld.courierCostMarketplace)+"""</td>
+        <td style="text-align:center">"""+str(mpItemNew.courierCostMarketplace)+"""</td>
+        <td style="text-align:center">"""+str(round(mpItemOld.currentTp-mpItemOld.minimumPossibleTp))+"""</td>
+        <td style="text-align:center">"""+str(round(mpItemNew.currentTp-mpItemNew.minimumPossibleTp))+"""</td>
+        <td style="text-align:center">"""+str(round(((mpItemOld.currentTp-mpItemOld.minimumPossibleTp)/mpItemOld.currentSp)*100,2))+"""</td>
+        <td style="text-align:center">"""+str(round(((mpItemNew.currentTp-mpItemNew.minimumPossibleTp)/mpItemNew.currentSp)*100,2))+"""</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','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','sandeep.sachdeva@saholic.com']
+    msg = MIMEMultipart()
+    msg['Subject'] = "Snapdeal Courier Cost Synced" + ' - ' + str(datetime.now())
+    msg['From'] = ""
+    msg['To'] = ",".join(recipients)
+    msg.preamble = "Snapdeal Courier Cost Synced" + ' - ' + 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 Snapdeal Courier Cost mail.Lets try with 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():
     print "Opening snapdeal seller login page"
     br = login("http://selleraccounts.snapdeal.com/")
     exceptionList, fetchedItems = populateStuff(br)
     filteredData = filterData(fetchedItems)
     sendMail(filteredData,exceptionList)
     write_report(filteredData,exceptionList)
+    syncCourierCost(courierCostToSync)
+    sendCCSyncMail()
 if __name__ == "__main__":
     main()

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/catalog/script/SnapdealReconciliation.py – Rev 10434 → 11098