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
+from shop2020.model.v1.catalog.impl.DataService import SnapdealItem, MarketplaceItems, Item, Category
 from shop2020.thriftpy.model.v1.order.ttypes import OrderSource
 import mechanize
 import sys
 import cookielib
 from time import sleep
 import json
 import smtplib
+import xlwt
 from datetime import datetime
 from shop2020.utils import EmailAttachmentSender
 from shop2020.utils.EmailAttachmentSender import get_attachment_part
 from email.mime.text import MIMEText
 import email
 config_client = ConfigClient()
 host = config_client.get_property('staging_hostname')
 DataService.initialize(db_hostname=host)
 class __SnapdealInfo:
-    def __init__(self, sellingPrice, weight, transferPrice, commission, commissionPercentage, courierCost, sellingPriceSnapdeal, transferPriceSnapdeal, fixedMargin, fixedMarginPercentage, collectionCharges, logisticCostSnapdeal, weightSnapdeal, supc, itemId):
+    def __init__(self, sellingPrice, weight, transferPrice, commission, commissionPercentage, courierCost, sellingPriceSnapdeal, transferPriceSnapdeal, fixedMargin, \
+                 fixedMarginPercentage, collectionCharges, logisticCostSnapdeal, weightSnapdeal, supc, itemId, parentCategory, productGroup ,brand, modelName, modelNumber, \
+                 color):
         self.sellingPrice = sellingPrice
         self.weight = weight
         self.transferPrice = transferPrice
         self.commission = commission
         self.collectionCharges = collectionCharges
         self.logisticCostSnapdeal = logisticCostSnapdeal
         self.weightSnapdeal = weightSnapdeal
         self.supc = supc
         self.itemId = itemId
+        self.parentCategory = parentCategory
+        self.productGroup = productGroup
+        self.brand = brand
+        self.modelName = modelName
+        self.modelNumber = modelNumber
+        self.color = color
 def getBrowserObject():
     br = mechanize.Browser(factory=mechanize.RobustFactory())
     cj = cookielib.LWPCookieJar()
     br.set_cookiejar(cj)
     return br
 def populateStuff(br):
     exceptionList = []
     fetchedItems = []
-    items = session.query(SnapdealItem,MarketplaceItems).join((MarketplaceItems,SnapdealItem.item_id==MarketplaceItems.itemId)).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(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()
+        parent_category = Category.query.filter_by(id=category.parent_category_id).first()
         try:
             print "Inside try for fetch data"
             snapdealInfo = fetchData(br,snapdealItem.supc)
         except Exception as e:
             exceptionList.append(item)
         snapdealInfo.courierCost = snapdealItem.courierCost
         snapdealInfo.transferPrice = snapdealItem.transferPrice
         snapdealInfo.commissionPercentage = marketplaceItem.commission
         snapdealInfo.commission = snapdealItem.commission
         snapdealInfo.itemId = snapdealItem.item_id
+        snapdealInfo.parentCategory = parent_category.display_name
+        snapdealInfo.productGroup = ds_item.product_group
+        snapdealInfo.brand = ds_item.brand
+        snapdealInfo.modelName = ds_item.model_name
+        snapdealInfo.modelNumber = ds_item.model_number
+        snapdealInfo.color = ds_item.color
+        snapdealInfo.weight = ds_item.weight
         fetchedItems.append(snapdealInfo)
     return exceptionList, fetchedItems
 def fetchData(br,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)
     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)
+    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)
     return snapdealInfo
 def filterData(fetchedItems):
+    filterList = []
     for data in fetchedItems:
+        print data.itemId
+        print data.transferPrice
+        print data.transferPriceSnapdeal
         if ( data.transferPrice - data.transferPriceSnapdeal >= -3 ) and (data.transferPrice - data.transferPriceSnapdeal <= 3):
-            print "Removing itemId ",data.itemId
+            continue
-            fetchedItems.remove(data)
+        filterList.append(data)
-    return fetchedItems
+    return filterList
 def sendMail(filteredData,exceptionList):
     xstr = lambda s: s or ""
     message="""<html>
             <body>
             <th>Snapdeal Courier Charges</th>
             </tr></thead>
             <tbody>"""
     for data in filteredData:
         if data.transferPriceSnapdeal < data.transferPrice:
-            it = Item.query.filter_by(id=data.itemId).one()
             message+="""<tr>
             <td style="text-align:center">"""+str(data.itemId)+"""</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">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>
             <td style="text-align:center">"""+str(data.sellingPrice)+"""</td>
             <td style="text-align:center">"""+str(data.sellingPriceSnapdeal)+"""</td>
             <td style="text-align:center">"""+str(data.transferPrice)+"""</td>
             <td style="text-align:center">"""+str(data.transferPriceSnapdeal)+"""</td>
-            <td style="text-align:center">"""+str(data.commission*1.1236)+"""</td>
+            <td style="text-align:center">"""+str(round(data.commission*1.1236,2))+"""</td>
             <td style="text-align:center">"""+str(round(float(data.fixedMargin)+float(data.collectionCharges),2))+"""</td>
             <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(it.weight*100)+" gms"+"""</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(data.courierCost*1.1236)+"""</td>
+            <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>
-            <td style="text-align:center">"""+str(data.logisticCostSnapdeal)+" gms"+"""</td>
+            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2))+"""</td>
             </tr>"""
     message+="""</tbody></table>"""
     message+="""
             <h3>High TP On Snapdeal</h3>
             <table border="1" style="width:100%;">
             <th>Snapdeal Courier Charges</th>
             </tr></thead>
             <tbody>"""
     for data in filteredData:
         if data.transferPriceSnapdeal >= data.transferPrice:
-            it = Item.query.filter_by(id=data.itemId).one()
             message+="""<tr>
             <td style="text-align:center">"""+str(data.itemId)+"""</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">"""+xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color)+"""</td>
             <td style="text-align:center">"""+str(data.sellingPrice)+"""</td>
             <td style="text-align:center">"""+str(data.sellingPriceSnapdeal)+"""</td>
             <td style="text-align:center">"""+str(data.transferPrice)+"""</td>
             <td style="text-align:center">"""+str(data.transferPriceSnapdeal)+"""</td>
-            <td style="text-align:center">"""+str(data.commission*1.1236)+"""</td>
+            <td style="text-align:center">"""+str(round(data.commission*1.1236,2))+"""</td>
             <td style="text-align:center">"""+str(round(float(data.fixedMargin)+float(data.collectionCharges),2))+"""</td>
             <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(it.weight*100)+" gms"+"""</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(data.courierCost*1.1236)+"""</td>
+            <td style="text-align:center">"""+str(round(data.courierCost*1.1236,2))+"""</td>
-            <td style="text-align:center">"""+str(data.logisticCostSnapdeal)+"""</td>
+            <td style="text-align:center">"""+str(round(data.logisticCostSnapdeal,2))+"""</td>
             </tr>"""
     message+="""</tbody></table>"""
     message+="""
             <h3>Unable To Fetch Items</h3>
             <table border="1" style="width:100%;">
             </tr></thead>
             <tbody>"""
     for data in exceptionList:
         snapdealItem = data[0]
         marketplaceItem = data[1]
-        it = Item.query.filter_by(id=data.itemId).one()
+        ds_item = data[2]
         message+="""<tr>
-            <td style="text-align:center">"""+str(data.itemId)+"""</td>
+            <td style="text-align:center">"""+str(ds_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">"""+xstr(ds_item.brand)+" "+xstr(ds_item.model_name)+" "+xstr(ds_item.model_number)+" "+xstr(ds_item.color)+"""</td>
             <td style="text-align:center">"""+str(snapdealItem.sellingPrice)+"""</td>
             <td style="text-align:center">"""+str(snapdealItem.transferPrice)+"""</td>
             <td style="text-align:center">"""+str(snapdealItem.commission*1.1236)+"""</td>
-            <td style="text-align:center">"""+str(marketplaceItem.commissionPercentage)+"%"+"""</td>
+            <td style="text-align:center">"""+str(marketplaceItem.commission)+"%"+"""</td>
-            <td style="text-align:center">"""+str(it.weight*100)+" gms"+"""</td>
+            <td style="text-align:center">"""+str(ds_item.weight*1000)+" gms"+"""</td>
             <td style="text-align:center">"""+str(snapdealItem.courierCost*1.1236)+"""</td>
             </tr>"""
     message+="""</tbody></table></body></html>"""
     print message
     mailServer = smtplib.SMTP("smtp.gmail.com", 587)
         smtpServer.sendmail(sender, recipients, msg.as_string())
         print "Successfully sent email"
     except:
         print "Error: unable to send email."
+def write_report(filteredData,exceptionList):
+    wbk = xlwt.Workbook()
+    sheet = wbk.add_sheet('Low TP SD')
+    xstr = lambda s: s or ""
+    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+    excel_integer_format = '0'
+    integer_style = xlwt.XFStyle()
+    integer_style.num_format_str = excel_integer_format
+    sheet.write(0, 0, "Item ID", heading_xf)
+    sheet.write(0, 1, "Category", heading_xf)
+    sheet.write(0, 2, "Product Group.", heading_xf)
+    sheet.write(0, 3, "SUPC", heading_xf)
+    sheet.write(0, 4, "Brand", heading_xf)
+    sheet.write(0, 5, "Product Name", heading_xf)
+    sheet.write(0, 6, "Our System SP", heading_xf)
+    sheet.write(0, 7, "Snapdeal SP", heading_xf)
+    sheet.write(0, 8, "Our TP", heading_xf)
+    sheet.write(0, 9, "Snapdeal TP", heading_xf)
+    sheet.write(0, 10, "Our System Commission", heading_xf)
+    sheet.write(0, 11, "Snapdeal Commission", heading_xf)
+    sheet.write(0, 12, "Our System Commission %", heading_xf)
+    sheet.write(0, 13, "Snapdeal Commission %", heading_xf)
+    sheet.write(0, 14, "Our System Weight (gms)", heading_xf)
+    sheet.write(0, 15, "Snapdeal Weight", heading_xf)
+    sheet.write(0, 16, "Our Courier Cost", heading_xf)
+    sheet.write(0, 17, "Snapdeal Courier Cost", heading_xf)
+    sheet_iterator=1
+    for data in filteredData:
+        if data.transferPriceSnapdeal < data.transferPrice:
+            sheet.write(sheet_iterator, 0, data.itemId)
+            sheet.write(sheet_iterator, 1, data.parentCategory)
+            sheet.write(sheet_iterator, 2, data.supc)
+            sheet.write(sheet_iterator, 3, data.brand)
+            sheet.write(sheet_iterator, 4, data.brand)
+            sheet.write(sheet_iterator, 5, xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color))
+            sheet.write(sheet_iterator, 6, data.sellingPrice)
+            sheet.write(sheet_iterator, 7, data.sellingPriceSnapdeal)
+            sheet.write(sheet_iterator, 8, data.transferPrice)
+            sheet.write(sheet_iterator, 9, data.transferPriceSnapdeal)
+            sheet.write(sheet_iterator, 10, round(data.commission*1.1236,2))
+            sheet.write(sheet_iterator, 11, round(float(data.fixedMargin)+float(data.collectionCharges),2))
+            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_iterator+=1
+    sheet = wbk.add_sheet('High TP SD')
+    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+    excel_integer_format = '0'
+    integer_style = xlwt.XFStyle()
+    integer_style.num_format_str = excel_integer_format
+    xstr = lambda s: s or ""
+    sheet.write(0, 0, "Item ID", heading_xf)
+    sheet.write(0, 1, "Category", heading_xf)
+    sheet.write(0, 2, "Product Group.", heading_xf)
+    sheet.write(0, 3, "SUPC", heading_xf)
+    sheet.write(0, 4, "Brand", heading_xf)
+    sheet.write(0, 5, "Product Name", heading_xf)
+    sheet.write(0, 6, "Our System SP", heading_xf)
+    sheet.write(0, 7, "Snapdeal SP", heading_xf)
+    sheet.write(0, 8, "Our TP", heading_xf)
+    sheet.write(0, 9, "Snapdeal TP", heading_xf)
+    sheet.write(0, 10, "Our System Commission", heading_xf)
+    sheet.write(0, 11, "Snapdeal Commission", heading_xf)
+    sheet.write(0, 12, "Our System Commission %", heading_xf)
+    sheet.write(0, 13, "Snapdeal Commission %", heading_xf)
+    sheet.write(0, 14, "Our System Weight (gms)", heading_xf)
+    sheet.write(0, 15, "Snapdeal Weight", heading_xf)
+    sheet.write(0, 16, "Our Courier Cost", heading_xf)
+    sheet.write(0, 17, "Snapdeal Courier Cost", heading_xf)
+    sheet_iterator=1
+    for data in filteredData:
+        if data.transferPriceSnapdeal > data.transferPrice:
+            sheet.write(sheet_iterator, 0, data.itemId)
+            sheet.write(sheet_iterator, 1, data.parentCategory)
+            sheet.write(sheet_iterator, 2, data.supc)
+            sheet.write(sheet_iterator, 3, data.brand)
+            sheet.write(sheet_iterator, 4, data.brand)
+            sheet.write(sheet_iterator, 5, xstr(data.brand)+" "+xstr(data.modelName)+" "+xstr(data.modelNumber)+" "+xstr(data.color))
+            sheet.write(sheet_iterator, 6, data.sellingPrice)
+            sheet.write(sheet_iterator, 7, data.sellingPriceSnapdeal)
+            sheet.write(sheet_iterator, 8, data.transferPrice)
+            sheet.write(sheet_iterator, 9, data.transferPriceSnapdeal)
+            sheet.write(sheet_iterator, 10, round(data.commission*1.1236,2))
+            sheet.write(sheet_iterator, 11, round(float(data.fixedMargin)+float(data.collectionCharges),2))
+            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_iterator+=1
+    sheet = wbk.add_sheet('Exceptions')
+    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
+    excel_integer_format = '0'
+    integer_style = xlwt.XFStyle()
+    integer_style.num_format_str = excel_integer_format
+    xstr = lambda s: s or ""
+    sheet.write(0, 0, "Item ID", heading_xf)
+    sheet.write(0, 1, "SUPC", heading_xf)
+    sheet.write(0, 2, "Brand", heading_xf)
+    sheet.write(0, 3, "Product Name", heading_xf)
+    sheet.write(0, 4, "Our System SP", heading_xf)
+    sheet.write(0, 5, "Our TP", heading_xf)
+    sheet.write(0, 6, "Our System Commission", heading_xf)
+    sheet.write(0, 7, "Our System Commission %", heading_xf)
+    sheet.write(0, 8, "Our System Weight (gms)", heading_xf)
+    sheet.write(0, 9, "Our Courier Cost", heading_xf)
+    sheet_iterator=1
+    for data in exceptionList:
+        snapdealItem = data[0]
+        marketplaceItem = data[1]
+        ds_item = data[2]
+        sheet.write(sheet_iterator, 0, ds_item.id)
+        sheet.write(sheet_iterator, 1, snapdealItem.supc)
+        sheet.write(sheet_iterator, 2, ds_item.brand)
+        sheet.write(sheet_iterator, 3, xstr(ds_item.brand)+" "+xstr(ds_item.model_name)+" "+xstr(ds_item.model_number)+" "+xstr(data.color))
+        sheet.write(sheet_iterator, 4, snapdealItem.sellingPrice)
+        sheet.write(sheet_iterator, 5, snapdealItem.transferPrice)
+        sheet.write(sheet_iterator, 6, round(snapdealItem.commission*1.1236,2))
+        sheet.write(sheet_iterator, 7, marketplaceItem.commission)
+        sheet.write(sheet_iterator, 8, (ds_item.weight*1000))
+        sheet.write(sheet_iterator, 9, round(data.courierCost*1.1236,2))
+    filename = "/tmp/snapdeal-tp-reconciliation-" + str(datetime.now()) + ".xls"
+    wbk.save(filename)
+    try:
+        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Snapdeal TP Reconciliation "+ str(datetime.now()), "", [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 TP Reconciliation "+ str(datetime.now()), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","rajveer.singh@saholic.com","vikram.raghav@saholic.com","kshitij.sood@saholic.com","chaitnaya.vats@saholic.com","khushal.bhatia@saholic.com"], [])
+    except Exception as e:
+        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']
+        msg = MIMEMultipart()
+        msg['Subject'] = "Snapdeal TP Reconciliation" + ' - ' + str(datetime.now())
+        msg['From'] = sender
+        #recipients = ['rajneesh.arora@saholic.com','rajveer.singh@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['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.xls')
+        msg.attach(fileMsg)
+        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)
 if __name__ == "__main__":
     main()

Subversion Repositories SmartDukaan

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