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 Reconciled")
    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")
    setHeaders(worksheet, worksheet1)
    row = 0
    anotherrow = 0
    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"]}))
        anotherrow += 1
        if len(aff) > 0 and order["subTagId"] not in matchedList: 
            matchedList.append(order["subTagId"])
            aff[0]['reconciled'] = True
            db.snapdealOrderAffiliateInfo.save(aff[0])
            order['reconciled'] = True
            db.merchantOrder.save(order)
            row += 1
            global i
            i=-1 
            worksheet.write(row, inc(), orderId)
            worksheet1.write(anotherrow, i, orderId)
            worksheet.write(row, inc(), row1[1])
            worksheet1.write(anotherrow, i, row1[1])
            worksheet.write(row, inc(), row1[-1])
            worksheet1.write(anotherrow, i, row1[-1])
            worksheet.write(row, inc(), order['merchantOrderId'])
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
            worksheet.write(row, inc(), order['subTagId'])
            worksheet1.write(anotherrow, i, order['subTagId'])
            worksheet.write(row, inc(), order['placedOn'])
            worksheet1.write(anotherrow, i, order['placedOn'])
            worksheet.write(row, inc(), int(order['paidAmount']))
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
            if int(order["paidAmount"]) == aff[0]["saleAmount"]:
                worksheet.write(row, inc(), aff[0]['saleDate'])
                worksheet1.write(anotherrow, i, aff[0]['saleDate'])
                worksheet.write(row, inc(), aff[0]['saleAmount'])
                worksheet1.write(anotherrow, i, aff[0]['saleAmount'])
                worksheet.write(row, inc(), aff[0]['payOut'])
                worksheet1.write(anotherrow, i, aff[0]['payOut'])
            k = i
            for subOrder in order['subOrders']:
                i = k
                worksheet.write(row, inc(), subOrder['productTitle'])
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
                worksheet.write(row, inc(), subOrder['amountPaid'])
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
                worksheet.write(row, inc(), subOrder['quantity'])
                worksheet1.write(anotherrow, i, subOrder['quantity'])
                worksheet.write(row, inc(), subOrder['status'])
                worksheet1.write(anotherrow, i, subOrder['status'])
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
        else:
            worksheet1.write(anotherrow, inc(), orderId)
            worksheet1.write(anotherrow, inc(), row1[1])
            worksheet1.write(anotherrow, inc(), row1[-1])
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
            worksheet1.write(anotherrow, inc(), order['subTagId'])
            worksheet1.write(anotherrow, inc(), order['placedOn'])
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
            worksheet1.write(anotherrow, inc(), "")
            worksheet1.write(anotherrow, inc(), "")
            worksheet1.write(anotherrow, inc(), "")
            worksheet1.write(anotherrow, inc(), 'Product Title', boldStyle)
            worksheet1.write(anotherrow, inc(), 'Price', boldStyle)
            worksheet1.write(anotherrow, inc(), 'Amoun Paid', boldStyle)
            worksheet1.write(anotherrow, inc(), 'Status', boldStyle)
            worksheet1.write(anotherrow, inc(), 'Cashback Status', boldStyle)
            worksheet1.write(anotherrow, inc(), 'CashBack', boldStyle)
            k = i
            for subOrder in order['subOrders']:
                i = k
                worksheet.write(anotherrow, inc(), subOrder['productTitle'])
                worksheet.write(anotherrow, inc(), subOrder['amountPaid'])
                worksheet.write(anotherrow, inc(), subOrder['quantity'])
                worksheet.write(anotherrow, inc(), subOrder['status'])
                worksheet.write(anotherrow, inc(), subOrder['cashBackStatus'])
                worksheet.write(anotherrow, inc(), subOrder['cashBackAmount'])
        
            
        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 setHeaders(worksheet, worksheet1):
    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(), 'SubtagId', boldStyle)
    worksheet.write(row, inc(), 'Sale Date', boldStyle)
    worksheet.write(row, inc(), 'Sale Amount', boldStyle)
    worksheet.write(row, inc(), 'Aff Sale Date', boldStyle)
    worksheet.write(row, inc(), 'Aff Sale Amt', boldStyle)
    worksheet.write(row, inc(), 'Aff PayOut', 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(), 'Cashback Status', boldStyle)
    worksheet.write(row, inc(), 'CashBack', boldStyle)

    worksheet1.write(row, inc(), 'Order Id', boldStyle)
    worksheet1.write(row, inc(), 'User Id', boldStyle)
    worksheet1.write(row, inc(), 'Username', boldStyle)
    worksheet1.write(row, inc(), 'Merchant Order Id', boldStyle)
    worksheet1.write(row, inc(), 'SubtagId', boldStyle)
    worksheet1.write(row, inc(), 'Sale Date', boldStyle)
    worksheet1.write(row, inc(), 'Sale Amount', boldStyle)
    worksheet1.write(row, inc(), 'Aff Sale Date', boldStyle)
    worksheet1.write(row, inc(), 'Aff Sale Amt', boldStyle)
    worksheet1.write(row, inc(), 'Aff PayOut', boldStyle)
    worksheet1.write(row, inc(), 'Product Title', boldStyle)
    worksheet1.write(row, inc(), 'Price', boldStyle)
    worksheet1.write(row, inc(), 'Quantity', boldStyle)
    worksheet1.write(row, inc(), 'Status', boldStyle)
    worksheet1.write(row, inc(), 'Cashback Status', boldStyle)
    worksheet1.write(row, inc(), 'CashBack', boldStyle) 
    
    
def inc():
    global i
    i+=1
    return i
    
def main():
    generateSnapDealReco()
    generateFlipkartReco()
    sendmail(["amit.gupta@shop2020.in"], "", "DTR Affiliate Reco")

if __name__ == '__main__':
    main()