Subversion Repositories SmartDukaan

from elixir import *

import pymongo

from dtr.storage.MemCache import MemCache

import xlwt

from dtr.storage import DataService

from dtr.storage.DataService import brand_preferences, user_actions, price_preferences

from email.mime.multipart import MIMEMultipart

import email.encoders

import smtplib

from datetime import datetime

mc = MemCache()

con =None

DataService.initialize(db_hostname='localhost')

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,persPoints, \

                 fav_weight,brand_weight,asp_weight):

        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.persPoints = persPoints

        self.fav_weight = fav_weight

        self.brand_weight = brand_weight

        self.asp_weight = asp_weight

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 generateSheet(user_id):

    "Generating user specific deals for ",user_id

    userId = int(user_id)

    outer_query = []

    outer_query.append({"showDeal":1})

    query = {}

    query['$gt'] = 0

    outer_query.append({'totalPoints':query})

    brandPrefMap = {}

    pricePrefMap = {}

    actionsMap = {}

    brand_p = session.query(price_preferences).filter_by(user_id=userId).all()

    for x in brand_p:

        pricePrefMap[x.category_id] = [x.min_price,x.max_price]

    for x in session.query(brand_preferences).filter_by(user_id=userId).all():

        temp_map = {}

        if brandPrefMap.has_key((x.brand).strip().upper()):

            val = brandPrefMap.get((x.brand).strip().upper())

            temp_map[x.category_id] = 1 if x.status == 'show' else 0

            val.append(temp_map)

        else:

            temp = []

            temp_map[x.category_id] = 1 if x.status == 'show' else 0

            temp.append(temp_map)

            brandPrefMap[(x.brand).strip().upper()] = temp

    for x in session.query(user_actions).filter_by(user_id=userId).all():

        actionsMap[x.store_product_id] = 1 if x.action == 'like' else 0

    all_deals = list(get_mongo_connection().Catalog.Deals.find({"$and":outer_query}).sort([('totalPoints',pymongo.DESCENDING),('bestSellerPoints',pymongo.DESCENDING),('nlcPoints',pymongo.DESCENDING),('rank',pymongo.DESCENDING)]))

    all_category_deals = []

    for deal in all_deals:

        if actionsMap.get(deal['_id']) == 0:

            fav_weight =.25

        elif actionsMap.get(deal['_id']) == 1:

            fav_weight = 1.5

        else:

            fav_weight = 1

        if brandPrefMap.get(deal['brand'].strip().upper()) is not None:

            brand_weight = 1

            for brandInfo in brandPrefMap.get(deal['brand'].strip().upper()):

                if brandInfo.get(deal['category_id']) is not None:

                    if brandInfo.get(deal['category_id']) == 1:

                        brand_weight = 2.0

        else:

            brand_weight = 1

        if pricePrefMap.get(deal['category_id']) is not None:

            if deal['available_price'] >= pricePrefMap.get(deal['category_id'])[0] and deal['available_price'] <= pricePrefMap.get(deal['category_id'])[1]:

                asp_weight = 1.5

            elif  deal['available_price'] >= pricePrefMap.get(deal['category_id'])[0] - 0.5 * pricePrefMap.get(deal['category_id'])[0] and deal['available_price'] <= pricePrefMap.get(deal['category_id'])[1] + 0.5 * pricePrefMap.get(deal['category_id'])[1]:

                asp_weight = 1.2

            else:

                asp_weight = 1

        else:

            asp_weight = 1

        persPoints = deal['totalPoints'] * fav_weight * brand_weight * asp_weight

        deal['fav_weight'] = fav_weight

        deal['brand_weight'] = brand_weight

        deal['asp_weight'] = asp_weight

        deal['persPoints'] = persPoints

        all_category_deals.append(deal)

    sorted_deals = sorted(all_category_deals, key = lambda x: (x['persPoints'],x['totalPoints'],x['bestSellerPoints'], x['nlcPoints'], x['rank']),reverse=True)

    p = []

    for x in sorted_deals:

        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['persPoints'],deal['fav_weight'],deal['brand_weight'],deal['asp_weight'])

        p.append(s_info)

    wbk = xlwt.Workbook()

    sheet = wbk.add_sheet('User 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, "Mrp", heading_xf)

    sheet.write(0, 14, "DP", heading_xf)

    sheet.write(0, 15, "Scheme Amount", heading_xf)

    sheet.write(0, 16, "Discount Type", heading_xf)

    sheet.write(0, 17, "Min Discount", heading_xf)

    sheet.write(0, 18, "Max Discount", heading_xf)

    sheet.write(0, 19, "Max Nlc", heading_xf)

    sheet.write(0, 20, "Min Nlc", heading_xf)

    sheet.write(0, 21, "Max Price", heading_xf)

    sheet.write(0, 22, "Rank", heading_xf)

    sheet.write(0, 23, "Nlc Points", heading_xf)

    sheet.write(0, 24, "Best Seller Points", heading_xf)

    sheet.write(0, 25, "Total Points", heading_xf)

    sheet.write(0, 26, "Pers Points", heading_xf)

    sheet.write(0, 27, "Show Deals", heading_xf)

    sheet.write(0, 28, "Fav Weight", heading_xf)

    sheet.write(0, 29, "Brand Weight", heading_xf)

    sheet.write(0, 30, "Asp Weight", 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'])

        if y[0]['status'] ==1:

            sheet.write(it, 5, 'Active')

        else:

            sheet.write(it, 5, 'EOL')

        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'])

        sheet.write(it, 13, x.mrp)

        sheet.write(it, 14, x.dp)

        sheet.write(it, 15, x.schemeAmount)

        sheet.write(it, 16, x.discountType)

        sheet.write(it, 17, x.minDiscount)

        sheet.write(it, 18, x.maxDiscount)

        sheet.write(it, 19, x.maxNlc)

        sheet.write(it, 20, x.minNlc)

        sheet.write(it, 21, x.maxprice)

        sheet.write(it, 22, x.rank)

        sheet.write(it, 23, x.nlcPoints)

        sheet.write(it, 24, x.bestSellerPoints)

        sheet.write(it, 25, x.totalPoints)

        sheet.write(it, 26, x.persPoints)

        sheet.write(it, 27, x.showDeal)

        sheet.write(it, 28, x.fav_weight)

        sheet.write(it, 29, x.brand_weight)

        sheet.write(it, 30, x.asp_weight)

        it+=1

    filename = "/tmp/deal-data-user-specific"+str(datetime.now())+".xls" 

    wbk.save(filename)

    smtpServer = smtplib.SMTP('localhost')

    #smtpServer.set_debuglevel(1)

    sender = 'dtr@shop2020.in'

    if userId==27:

        recipients = ['kshitij.sood@saholic.com']

    else:

        recipients = ['rajneesh.arora@saholic.com','kshitij.sood@saholic.com']

    msg = MIMEMultipart()

    msg['Subject'] = "DTR Deals data (UserOnly) UserId - "+str(userId)+"" + ' - ' + str(datetime.now())

    msg['From'] = sender

    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=User_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 main():

    generateSheet(1)

if __name__=='__main__':

    main()