Subversion Repositories SmartDukaan

'''

Created on Mar 13, 2015

@author: amit

'''

from datetime import date, datetime, timedelta

from dtr.storage.Mysql import getOrdersAfterDate

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from pymongo.mongo_client import MongoClient

from xlrd import open_workbook

from xlwt.Workbook import Workbook

import MySQLdb

from xlutils.copy import copy

import smtplib

import time

import xlwt

#from xlwt import 

client = MongoClient('mongodb://localhost:27017/')

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "DTR User Segmentation report for " + date.today().isoformat()

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

XLS_FILENAME = "dtrsourcesreco.xls"

boldStyle = xlwt.XFStyle()

f = xlwt.Font()

f.bold = True

boldStyle.font = f

i = -1

def generateFlipkartReco():

    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 2)

    db = client.Dtr

    rb = open_workbook(XLS_FILENAME)

    wb = copy(rb)

    #wb = xlwt.Workbook()

    row = 0

    worksheet = wb.add_sheet("Flipkart")

    worksheet.write(row, inc(), 'Order Id', boldStyle)

    worksheet.write(row, inc(), 'User Id', boldStyle)

    worksheet.write(row, inc(), 'Username', boldStyle)

    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)

    worksheet.write(row, inc(), 'Product Title', boldStyle)

    worksheet.write(row, inc(), 'Price', boldStyle)

    worksheet.write(row, inc(), 'Quantity', boldStyle)

    worksheet.write(row, inc(), 'Status', boldStyle)

    worksheet.write(row, inc(), 'Detailed Status', boldStyle)

    worksheet.write(row, inc(), 'Cashback Status', boldStyle)

    worksheet.write(row, inc(), 'Our CashBack', boldStyle)

    worksheet.write(row, inc(), 'Sale Date', boldStyle)

    worksheet.write(row, inc(), 'SubtagId', boldStyle)

    worksheet.write(row, inc(), 'Category', boldStyle)

    worksheet.write(row, inc(), 'Aff PayOut', boldStyle)

    worksheet.write(row, inc(), 'Aff Sale Amount', boldStyle)

    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)

    for row1 in curs:

        orderId = row1[0]

        order = db.merchantOrder.find_one({"orderId":orderId})

        if order is None:

            continue

        saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%d %B, %Y %I:%M %p").replace(hour=0, minute=0, second=0, microsecond=0).timetuple()))

        #saleTime1 = int(time.mktime((datetime.strptime(order['placedOn'], "%b %d, %Y %I:%M %p") + timedelta(seconds=-60)).timetuple()))

        for subOrder in order['subOrders']: 

            affs = list(db.flipkartOrderAffiliateInfo.find({"subOrders.productCode":subOrder.get("productCode"), "saleDateInt":saleTime}))

            row += 1

            global i

            i=-1

            worksheet.write(row, inc(), orderId)

            worksheet.write(row, inc(), row1[1])

            worksheet.write(row, inc(), row1[-1])

            worksheet.write(row, inc(), order['merchantOrderId'])

            worksheet.write(row, inc(), subOrder['productTitle'])

            worksheet.write(row, inc(), subOrder['amountPaid'])

            worksheet.write(row, inc(), subOrder['quantity'])

            worksheet.write(row, inc(), subOrder['status'])

            worksheet.write(row, inc(), subOrder['detailedStatus'])

            worksheet.write(row, inc(), subOrder['cashBackStatus'])

            worksheet.write(row, inc(), subOrder['cashBackAmount'])

            worksheet.write(row, inc(), subOrder['placedOn'])

            worksheet.write(row, inc(), order['subTagId'])

            for aff in affs:

                if aff['subTagId']== subOrder['subTagId']:

                    worksheet.write(row, inc(), aff['category'])

                    worksheet.write(row, inc(), aff['payOut'])

                    worksheet.write(row, inc(), aff['saleAmount'])

                    worksheet.write(row, inc(), aff['saleDate'])

                    break

        wb.save(XLS_FILENAME)

def generateSnapDealReco():

    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)

    db = client.Dtr

    matchedList = []

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Snapdeal")

    column = 0

    row = 0

    global i

    i=-1    

    worksheet.write(row, inc(), 'Order Id', boldStyle)

    worksheet.write(row, inc(), 'User Id', boldStyle)

    worksheet.write(row, inc(), 'Username', boldStyle)

    worksheet.write(row, inc(), 'Merchant Order Id', boldStyle)

    worksheet.write(row, inc(), 'Product Title', boldStyle)

    worksheet.write(row, inc(), 'Price', boldStyle)

    worksheet.write(row, inc(), 'Quantity', boldStyle)

    worksheet.write(row, inc(), 'Status', boldStyle)

    worksheet.write(row, inc(), 'Detailed Status', boldStyle)

    worksheet.write(row, inc(), 'Cashback Status', boldStyle)

    worksheet.write(row, inc(), 'Our CashBack', boldStyle)

    worksheet.write(row, inc(), 'Sale Date', boldStyle)

    worksheet.write(row, inc(), 'SubtagId', boldStyle)

    worksheet.write(row, inc(), 'Ad Id', boldStyle)

    worksheet.write(row, inc(), 'Affiliate PayOut', boldStyle)

    worksheet.write(row, inc(), 'Affiliate Sale Amount', boldStyle)

    worksheet.write(row, inc(), 'Affiliate Sale Date', boldStyle)

    for row1 in curs:

        orderId = row1[0]

        order = db.merchantOrder.find_one({"orderId":orderId})

        if order is None:

            continue

        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))

        aff = list(db.snapdealOrderAffiliateInfo.find({"subTagId":order["subTagId"]}))

        matchedSubTag = False

        if len(aff) > 0 and order["subTagId"] not in matchedList: 

            matchedSubTag = True

            matchedList.append(order["subTagId"])

        once = True        

        for subOrder in order['subOrders']:

            i=-1 

            row += 1

            worksheet.write(row, inc(), orderId)

            worksheet.write(row, inc(), row1[1])

            worksheet.write(row, inc(), row1[-1])

            worksheet.write(row, inc(), order['merchantOrderId'])

            worksheet.write(row, inc(), subOrder['productTitle'])

            worksheet.write(row, inc(), subOrder['amountPaid'])

            worksheet.write(row, inc(), subOrder['quantity'])

            worksheet.write(row, inc(), subOrder['status'])

            worksheet.write(row, inc(), subOrder['detailedStatus'])

            worksheet.write(row, inc(), subOrder['cashBackStatus'])

            worksheet.write(row, inc(), subOrder['cashBackAmount'])

            worksheet.write(row, inc(), subOrder['placedOn'])

            worksheet.write(row, inc(), order['subTagId'])

            if matchedSubTag:

                worksheet.write(row, inc(), aff[0]['adId'])

                worksheet.write(row, inc(), aff[0]['saleDate'])

                if once:

                    worksheet.write(row, inc(), aff[0]['payOut'])

                    worksheet.write(row, inc(), aff[0]['saleAmount'])

                    once = False

        workbook.save(XLS_FILENAME)

def sendmail(email, message, title):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    # Create the container (outer) email message.

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    #snapdeal more to be added here

    snapdeal = MIMEBase('application', 'vnd.ms-excel')

    snapdeal.set_payload(file(XLS_FILENAME).read())

    encoders.encode_base64(snapdeal)

    snapdeal.add_header('Content-Disposition', 'attachment;filename=' + XLS_FILENAME)

    msg.attach(snapdeal)

    email.append('amit.gupta@shop2020.in')

    MAILTO = email 

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(PASSWORD, MAILTO, msg.as_string())

def inc():

    global i

    i+=1

    return i

def main():

    generateSnapDealReco()

    generateFlipkartReco()

    sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")

if __name__ == '__main__':

    main()