WebSVN – SmartDukaan – //trunk/PyDTR/src/dtr/utils/DealSheet.py

import pymongo
import xlwt
from datetime import datetime
from email.mime.multipart import MIMEMultipart
import email.encoders
import smtplib
from dtr.utils.utils import getCashBack
from dtr.storage.MemCache import MemCache


con = None

statusMap = {1:'Active',2:'EOL',3:'In Process',4:'Exclusive'}
mc = MemCache("127.0.0.1")

class __SkuInfo:
    
    def __init__(self, _id, skuBundleId, category_id, mrp, available_price, source_id, rank, maxNlc, minNlc, schemeAmount, minDiscount, \
                 maxDiscount, discountType, dp, nlcPoints, bestSellerPoints, totalPoints,in_stock,maxprice,showDeal, catalogBestSellerPoints, \
                 showDealOld):
        self._id = _id
        self.skuBundleId = skuBundleId
        self.category_id = category_id
        self.mrp = mrp
        self.available_price = available_price
        self.source_id = source_id
        self.rank = rank
        self.maxNlc = maxNlc
        self.minNlc = minNlc
        self.schemeAmount = schemeAmount
        self.minDiscount = minDiscount
        self.maxDiscount = maxDiscount
        self.discountType = discountType
        self.dp = dp
        self.nlcPoints = nlcPoints
        self.bestSellerPoints = bestSellerPoints
        self.totalPoints = totalPoints
        self.in_stock = in_stock
        self.maxprice = maxprice
        self.showDeal = showDeal
        self.catalogBestSellerPoints = catalogBestSellerPoints
        self.showDealOld = showDealOld  

def get_mongo_connection(host='localhost', port=27017):
    global con
    if con is None:
        print "Establishing connection %s host and port %d" %(host,port)
        try:
            con = pymongo.MongoClient(host, port)
        except Exception, e:
            print e
            return None
    return con

def writeSheet():
    p = []
    data = get_mongo_connection().Catalog.Deals.find().sort([('totalPoints',pymongo.DESCENDING)])
    print data.count()
    for x in data:
        s_info = __SkuInfo(x['_id'], x['skuBundleId'], x['category_id'],x['mrp'],x['available_price'],x['source_id'],x['rank'],x['maxNlc'], \
                           x['minNlc'], x['schemeAmount'],x['minDiscount'],x['maxDiscount'],x['discountType'],x['dp'],x['nlcPoints'],x['bestSellerPoints'], \
                           x['totalPoints'],x['in_stock'],x['maxprice'],x['showDeal'],x['catalogBestSellerPoints'],x['showDealOld'])
        p.append(s_info)
    
    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('Deals')
    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, "Catalog Item Id", heading_xf)
    sheet.write(0, 2, "Category", heading_xf)
    sheet.write(0, 3, "Brand", heading_xf)
    sheet.write(0, 4, "Product Name", heading_xf)
    sheet.write(0, 5, "Item Status", heading_xf)
    sheet.write(0, 6, "Stock Status", heading_xf)
    sheet.write(0, 7, "MarketPlace", heading_xf)
    sheet.write(0, 8, "MarketPlace Identifier", heading_xf)
    sheet.write(0, 9, "MarketPlace Secondary Identifier", heading_xf)
    sheet.write(0, 10, "Product Name (Marketplace)", heading_xf)
    sheet.write(0, 11, "Url", heading_xf)
    sheet.write(0, 12, "Price", heading_xf)
    sheet.write(0, 13, "After Cashback", heading_xf)
    sheet.write(0, 14, "Mrp", heading_xf)
    sheet.write(0, 15, "DP", heading_xf)
    sheet.write(0, 16, "Scheme Amount", heading_xf)
    sheet.write(0, 17, "Discount Type", heading_xf)
    sheet.write(0, 18, "Min Discount", heading_xf)
    sheet.write(0, 19, "Max Discount", heading_xf)
    sheet.write(0, 20, "Max Nlc", heading_xf)
    sheet.write(0, 21, "Min Nlc", heading_xf)
    sheet.write(0, 22, "Max Price", heading_xf)
    sheet.write(0, 23, "Rank", heading_xf)
    sheet.write(0, 24, "Nlc Points", heading_xf)
    sheet.write(0, 25, "Best Seller Points", heading_xf)
    sheet.write(0, 26, "Catalog Best Seller Points", heading_xf)
    sheet.write(0, 27, "Total Points", heading_xf)
    sheet.write(0, 28, "Show Deals", heading_xf)
    sheet.write(0, 29, "Show Deals Old", heading_xf)
    
    it = 1
    for x in p:
        y = list(get_mongo_connection().Catalog.MasterData.find({'_id':x._id}))
        sheet.write(it, 0, x._id)
        sheet.write(it, 1, x.skuBundleId)
        if x.category_id == 3:
            category = 'Mobiles'
        else:
            category = 'Tablets'
        sheet.write(it, 2, category)
        sheet.write(it, 3, y[0]['brand'])
        sheet.write(it, 4, y[0]['product_name'])
        sheet.write(it, 5, statusMap.get(y[0]['status']))
        if y[0]['in_stock'] ==1:
            sheet.write(it, 6, 'In Stock')
        else:
            sheet.write(it, 6, 'Out Of Stock')
        sheet.write(it, 7, y[0]['source'])
        sheet.write(it, 8, y[0]['identifier'])
        sheet.write(it, 9, y[0]['secondaryIdentifier'])
        sheet.write(it, 10, y[0]['source_product_name'])
        sheet.write(it, 11, y[0]['url'])
        sheet.write(it, 12, y[0]['available_price'])
        cashBack = getCashBack(x._id, x.source_id, x.category_id, mc, 'localhost')
        if not cashBack or cashBack.get('cash_back_status')!=1:
            pass
        else:
            if cashBack['cash_back_type'] ==1:
                y[0]['available_price'] = y[0]['available_price'] - y[0]['available_price'] * float(cashBack['cash_back'])/100
            elif cashBack['cash_back_type'] ==2:
                y[0]['available_price'] = y[0]['available_price'] - float(cashBack['cash_back'])
            else:
                pass
        sheet.write(it, 13, y[0]['available_price'])
        sheet.write(it, 14, x.mrp)
        sheet.write(it, 15, x.dp)
        sheet.write(it, 16, x.schemeAmount)
        sheet.write(it, 17, x.discountType)
        sheet.write(it, 18, x.minDiscount)
        sheet.write(it, 19, x.maxDiscount)
        sheet.write(it, 20, x.maxNlc)
        sheet.write(it, 21, x.minNlc)
        sheet.write(it, 22, x.maxprice)
        sheet.write(it, 23, x.rank)
        sheet.write(it, 24, x.nlcPoints)
        sheet.write(it, 25, x.bestSellerPoints)
        sheet.write(it, 26, x.catalogBestSellerPoints)
        sheet.write(it, 27, x.totalPoints)
        sheet.write(it, 28, x.showDeal)
        sheet.write(it, 29, x.showDealOld)
        it+=1
    filename = "/tmp/deal-data"+str(datetime.now())+".xls" 
    wbk.save(filename)
    smtpServer = smtplib.SMTP('localhost')
    #smtpServer.set_debuglevel(1)
    sender = 'dtr@shop2020.in'
    #recipients = ['kshitij.sood@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "DTR Deals data" + ' - ' + str(datetime.now())
    msg['From'] = sender
    recipients = ['rajneesh.arora@saholic.com','kshitij.sood@saholic.com','chaitnaya.vats@saholic.com','manoj.kumar@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=Deal_Sheet_Dtr.xls')
    msg.attach(fileMsg)
    try:
        smtpServer.sendmail(sender, recipients, msg.as_string())
        print "Successfully sent email"
    except:
        print "Error: unable to send email."

def sendMail():
    writeSheet()

def main():
    sendMail()

if __name__=='__main__':
    main()
Subversion Repositories SmartDukaan

(root)//trunk/PyDTR/src/dtr/utils/DealSheet.py – Rev 14750
