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

 import simplejson as json
 import xlwt
 import optparse
 import sys
 from operator import itemgetter
+from shop2020.utils import EmailAttachmentSender
+from shop2020.utils.EmailAttachmentSender import get_attachment_part
+import smtplib
+from multiprocessing import Process
+from email.mime.text import MIMEText
+import email
+from email.mime.multipart import MIMEMultipart
+import email.encoders
 config_client = ConfigClient()
 host = config_client.get_property('staging_hostname')
 syncPrice=config_client.get_property('sync_price_on_marketplace')
     global amazonLongTermActivePromotions
     global amazonShortTermActivePromotions
     itemInfo = []
     inventory_client = InventoryClient().get_client()
     fbaAvailableInventorySnapshot = inventory_client.getAllAvailableAmazonFbaItemInventory()
-    print len(fbaAvailableInventorySnapshot)
+    if runType=='FAVOURITE':
+        favourites = session.query(Amazonlisted.itemId).filter(or_(Amazonlisted.autoFavourite==True, Amazonlisted.manualFavourite==True)).all()
     for fbaInventoryItem in fbaAvailableInventorySnapshot:
+        if runType=='FAVOURITE':
+            if not (fbaInventoryItem.item_id in favourites):
+                continue
         d_amazon_listed = Amazonlisted.get_by(itemId=fbaInventoryItem.item_id)
         if d_amazon_listed is None:
             continue
         if d_amazon_listed.overrrideWanlc:
             wanlc = d_amazon_listed.exceptionalWanlc
         else:
             wanlc = inventory_client.getWanNlcForSource(fbaInventoryItem.item_id,OrderSource.AMAZON)
         it = Item.query.filter_by(id=fbaInventoryItem.item_id).one()
         category = Category.query.filter_by(id=it.category).one()
         parent_category = Category.query.filter_by(id=category.parent_category_id).first()
+        sourcePercentage = None
-        scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==it.category).filter(SourceCategoryPercentage.source==OrderSource.AMAZON).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()
+        sip = SourceItemPercentage.query.filter(SourceItemPercentage.item_id==it.id).filter(SourceItemPercentage.source==OrderSource.AMAZON).filter(SourceItemPercentage.startDate<=time).filter(SourceItemPercentage.expiryDate>=time).first()
-        if scp is not None:
+        if sip is not None:
-            sourcePercentage = scp
+            sourcePercentage = sip
         else:
-            spm = SourcePercentageMaster.get_by(source=OrderSource.AMAZON)
+            scp = SourceCategoryPercentage.query.filter(SourceCategoryPercentage.category_id==it.category).filter(SourceCategoryPercentage.source==OrderSource.AMAZON).filter(SourceCategoryPercentage.startDate<=time).filter(SourceCategoryPercentage.expiryDate>=time).first()
+            if scp is not None:
+                sourcePercentage = scp
+            else:
+                spm = SourcePercentageMaster.get_by(source=OrderSource.AMAZON)
-            sourcePercentage = spm
+                sourcePercentage = spm
         if fbaInventoryItem.location==0:
             sku = 'FBA'+str(fbaInventoryItem.item_id)
             state_id = 1
         elif fbaInventoryItem.location==1:
             sku = 'FBB'+str(fbaInventoryItem.item_id)
         else:
             subsidy = (amazonShortTermActivePromotions.get(val.sku)).subsidy
         lowestPossibleSp = lowestPossibleSp - subsidy
     return round(lowestPossibleSp,2)
+def getNewLowestPossibleSp(item,serviceTax,newVatRate):
+    lowestPossibleSp = (item.wanlc+(item.courierCost)*(1+(serviceTax/100))*(1+(newVatRate/100))+(15+item.otherCost)*(1+(newVatRate)/100))/(1-(item.commission/100)*(1+(serviceTax/100))*(1+(newVatRate)/100)-(item.returnProvision/100)*(1+(newVatRate)/100));
+    if item.isPromotion:
+        sku = ''
+        if item.warehouseLocation==1:
+            sku='FBA'+str(item.item_id)
+        else:
+            sku='FBB'+str(item.item_id)
+        if amazonLongTermActivePromotions.has_key(sku):
+            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
+        else:
+            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
+        lowestPossibleSp = lowestPossibleSp - subsidy
+    return round(lowestPossibleSp,2)
 def getTargetTp(targetSp,spm,val):
     targetTp = targetSp- targetSp*(spm.commission/100+spm.emiFee/100)*(1+(spm.serviceTax/100))-(val.courierCost)*(1+(spm.serviceTax/100))
     return round(targetTp,2)
 def commitExceptionList(exceptionList,timestamp,runType):
             sheet.write(sheet_iterator, 3, "False")
             sheet_iterator+=1
     filename = "/tmp/amazon-report-"+runType+" " + str(timestamp) + ".xls"
     wbk.save(filename)
+    try:
+        EmailAttachmentSender.mail("build@shop2020.in", "cafe@nes", ["kshitij.sood@saholic.com"], " Amazon 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"], " Amazon Auto Pricing "+runType+" " + str(timestamp), "", [get_attachment_part(filename)], ["rajneesh.arora@saholic.com","anikendra.das@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 = 'build@shop2020.in'
+        recipients = ["kshitij.sood@saholic.com"]
+        msg = MIMEMultipart()
+        msg['Subject'] = "Amazon Auto Pricing" + ' '+runType+' - ' + str(datetime.now())
+        msg['From'] = sender
+        #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['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=amazon-auto-pricing.xls')
+        msg.attach(fileMsg)
+        try:
+            smtpServer.sendmail(sender, recipients, msg.as_string())
+            print "Successfully sent email"
+        except:
+            print "Error: unable to send email."
+def getNewVatRate(item_id,state,price):
+    itemVatMaster = ItemVatMaster.query.filter(and_(ItemVatMaster.itemId==item_id, ItemVatMaster.stateId==state)).first()
+    if itemVatMaster is None:
+        d_item = Item.query.filter_by(id=item_id).first()
+        if d_item is None:
+            raise
+        else:
+            vatMaster = CategoryVatMaster.query.filter(and_(CategoryVatMaster.categoryId==d_item.category, CategoryVatMaster.minVal<=price,  CategoryVatMaster.maxVal>=price,  CategoryVatMaster.stateId == state)).first()
+        if vatMaster is None:
+            raise
+        else:
+            vatRate = vatMaster.vatPercent
+    else:
+        vatRate = itemVatMaster.vatPercentage
+    return vatRate
+def sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease):
+    if len(successfulAutoDecrease)==0 and len(successfulAutoIncrease)==0 :
+        return
+    xstr = lambda s: s or ""
+    message="""<html>
+            <body>
+            <h3>Auto Decrease Items</h3>
+            <table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Amazon SKU</th>
+            <th>Product Name</th>
+            <th>Old Price</th>
+            <th>New Price</th>
+            <th>Subsidy</th>
+            <th>Old Margin</th>
+            <th>New Margin</th>
+            <th>Commission %</th>
+            <th>Return Provision %</th>
+            <th>Inventory</th>
+            <th>Sales History</th>
+            <th>Category</th>
+            </tr></thead>
+            <tbody>"""
+    for item in successfulAutoDecrease:
+        it = Item.query.filter_by(id=item.item_id).one()
+        vatRate = getNewVatRate(item.item_id,item.warehouseLocation,item.proposedSp)
+        oldMargin = item.ourSellingPrice - item.lowestPossibleSp
+        newMargin = round(item.proposedSp - getNewLowestPossibleSp(item,12.36,vatRate))
+        sku = ''
+        if item.warehouseLocation==1:
+            sku='FBA'+str(item.item_id)
+        else:
+            sku='FBB'+str(item.item_id)
+        if amazonLongTermActivePromotions.has_key(sku):
+            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
+        elif amazonShortTermActivePromotions.has_key(sku):
+            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
+        else:
+            subsidy = 0
+        message+="""<tr>
+                <td style="text-align:center">"""+str(item.item_id)+"""</td>
+                <td style="text-align:center">"""+sku+"""</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.proposedSp))+"""</td>
+                <td style="text-align:center">"""+str(math.ceil(item.proposedSp))+"""</td>
+                <td style="text-align:center">"""+str(round(subsidy))+"""</td>
+                <td style="text-align:center">"""+str(round(oldMargin))+" ("+str(round((oldMargin/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.commission)+" %"+"""</td>
+                <td style="text-align:center">"""+str(item.returnProvision)+" %"+"""</td>
+                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
+                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
+                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</td>
+                </tr>"""
+    message+="""</tbody></table><h3>Auto Increase Items</h3><table border="1" style="width:100%;">
+            <thead>
+            <tr><th>Item Id</th>
+            <th>Amazon SKU</th>
+            <th>Product Name</th>
+            <th>Old Price</th>
+            <th>New Price</th>
+            <th>Subsidy</th>
+            <th>Old Margin</th>
+            <th>New Margin</th>
+            <th>Commission %</th>
+            <th>Return Provision %</th>
+            <th>Inventory</th>
+            <th>Sales History</th>
+            <th>Category</th>
+            </tr></thead>
+            <tbody>"""
+    for item in successfulAutoIncrease:
+        it = Item.query.filter_by(id=item.item_id).one()
+        vatRate = getNewVatRate(item.item_id,item.warehouseLocation,math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)))
+        oldMargin = item.ourSellingPrice - item.lowestPossibleSp
+        newMargin = round(math.ceil(item.ourSellingPrice+max(10,.01*item.ourSellingPrice)) - getNewLowestPossibleSp(item,12.36,vatRate))
+        sku = ''
+        if item.warehouseLocation==1:
+            sku='FBA'+str(item.item_id)
+        else:
+            sku='FBB'+str(item.item_id)
+        if amazonLongTermActivePromotions.has_key(sku):
+            subsidy = (amazonLongTermActivePromotions.get(sku)).subsidy
+        elif amazonShortTermActivePromotions.has_key(sku):
+            subsidy = (amazonShortTermActivePromotions.get(sku)).subsidy
+        else:
+            subsidy = 0
+        message+="""<tr>
+                <td style="text-align:center">"""+str(item.item_id)+"""</td>
+                <td style="text-align:center">"""+sku+"""</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(subsidy))+"""</td>
+                <td style="text-align:center">"""+str(round((oldMargin),1))+" ("+str(round((oldMargin/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.commission)+" %"+"""</td>
+                <td style="text-align:center">"""+str(item.returnProvision)+" %"+"""</td>
+                <td style="text-align:center">"""+str(item.ourInventory)+"""</td>
+                <td style="text-align:center">"""+getOosString(saleMap.get(sku))+"""</td>
+                <td style="text-align:center">"""+str(CompetitionCategory._VALUES_TO_NAMES.get(item.competitiveCategory))+"""</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'] = "Amazon Auto Pricing" + ' - ' + str(datetime.now())
+    msg['From'] = ""
+    msg['To'] = ",".join(recipients)
+    msg.preamble = "Amazon Auto Pricing" + ' - ' + 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 pricing mail.Lets try with local SMTP."
+        smtpServer = smtplib.SMTP('localhost')
+        smtpServer.set_debuglevel(1)
+        sender = 'build@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",
                    default="FULL", type="string",
         exceptionList[:], negativeMargin[:], cheapest[:], amongCheapestAndCanCompete[:], canCompete[:], almostCompete[:], cantCompete[:] =[],[],[],[],[],[],[]
     autoDecreaseItems = fetchItemsForAutoDecrease(timestamp)
     autoIncreaseItems = fetchItemsForAutoIncrease(timestamp)
     previousAutoFav, nowAutoFav = markAutoFavourites(timestamp)
     writeReport(timestamp,autoDecreaseItems,autoIncreaseItems,previousAutoFav,nowAutoFav,options.runType)
+    sendAutoPricingMail(successfulAutoDecrease,successfulAutoIncrease)
 if __name__=='__main__':
     main()

Subversion Repositories SmartDukaan

(root)//trunk/PyProj/src/shop2020/model/v1/catalog/script/AmazonHistoryPopulator.py – Rev 12484 → 12489