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()