Subversion Repositories SmartDukaan

'''

Created on Mar 13, 2015

@author: amit

'''

from datetime import date, datetime, timedelta

from dtr.main import Store

from dtr.storage import Mysql

from dtr.storage.Mysql import getOrdersAfterDate, getOrdersByTag

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 xlutils.copy import copy

from xlwt.Workbook import Workbook

import MySQLdb

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

    global i

    i = -1

    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

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

    global i

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

    db = client.Dtr

    notReconciled = {}

    matchedList = []

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Snapdeal Reconciled")

    worksheet1 = workbook.add_sheet("Snapdeal Reconciled All")

    worksheet2 = workbook.add_sheet("Snapdeal Orders not reconciled")

    affNotReconciledSheet = workbook.add_sheet("Snapdeal Aff not reconciled")

    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)

    row = 0

    anotherrow = 0

    row2 = 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 and int(order["paidAmount"])==int(aff[0]["saleAmount"]): 

            matchedList.append(order["subTagId"])

            db.snapdealOrderAffiliateInfo.update({"adId":aff[0].get("adId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)

            order['reconciled'] = True

            order['adId'] = aff[0].get("adId")

            db.merchantOrder.save(order)

            row += 1

            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[-2])

            worksheet1.write(anotherrow, i, row1[-2])

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

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

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

            worksheet1.write(anotherrow, i, aff[0]['ip'])

            k = i

            row -= 1

            anotherrow -= 1

            for subOrder in order['subOrders']:

                i = k

                row += 1

                anotherrow += 1

                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:

            i=-1

            worksheet1.write(anotherrow, inc(), orderId)

            worksheet1.write(anotherrow, inc(), row1[1])

            worksheet1.write(anotherrow, inc(), row1[-2])

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

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

            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"]) 

            inc()

            inc()

            inc()

            k = i

            anotherrow -= 1

            for subOrder in order['subOrders']:

                anotherrow += 1

                i = k

                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])

                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])

                worksheet1.write(anotherrow, inc(), subOrder['quantity'])

                worksheet1.write(anotherrow, inc(), subOrder['status'])

                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])

                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])

    last30Days =  date.today() - timedelta(days=30)

    int(time.mktime(last30Days.timetuple()))

    row = 0

    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False}, 

                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),

                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):

        subTagId = offer.get("subTagId")

        saleTime = offer.get("saleTime")

        orders = getOrdersByTag(subTagId, 3)

        for order in orders:

            if notReconciled.has_key(order[0]):

                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]

                if paidAmount==int(offer.get("saleAmount")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:

                    i = column

                    worksheet1.write(roww, inc(), offer.get("saleDate"))        

                    worksheet1.write(roww, inc(), offer.get("saleAmount"))        

                    worksheet1.write(roww, inc(), offer.get("payOut"))

                    del notReconciled[order[0]]

                    print "found match for user", order[1]

                    db.snapdealOrderAffiliateInfo.update({"adId":offer.get("adId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)

                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("adId")}}, multi=True)

                    break

    unreconciledOrders = notReconciled.keys()

    row2=0

    for row1 in curs:

        if row1[0] in unreconciledOrders:

            for order in db.merchantOrder.find({"orderId":row1[0]}):

                row2 += 1

                i=-1

                worksheet2.write(row2, inc(), order["orderId"])

                worksheet2.write(row2, inc(), row1[1])

                worksheet2.write(row2, inc(), row1[-2])

                worksheet2.write(row2, inc(), row1[-1])

                worksheet2.write(row2, inc(), order['merchantOrderId'])

                worksheet2.write(row2, inc(), order['subTagId'])

                worksheet2.write(row2, inc(), order['placedOn'])

                worksheet2.write(row2, inc(), int(order['paidAmount']))

                row2 -=1

                k=i

                for subOrder in order['subOrders']:

                    i = k

                    row2 += 1

                    worksheet2.write(row2, inc(), subOrder['productTitle'])

                    worksheet2.write(row2, inc(), subOrder['amountPaid'])

                    worksheet2.write(row2, inc(), subOrder['quantity'])

                    worksheet2.write(row2, inc(), subOrder['status'])

                    worksheet2.write(row2, inc(), subOrder['cashBackStatus'])

                    worksheet2.write(row2, inc(), subOrder['cashBackAmount'])

    for offer in db.snapdealOrderAffiliateInfo.find({"reconciled":{"$exists":False}, 

                                                     "saleTime":{"$gt":int(time.mktime(last30Days.timetuple())),

                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):

        row += 1

        i=-1

        affNotReconciledSheet.write(row, inc(), offer.get("adId")) 

        affNotReconciledSheet.write(row, inc(), offer.get("subTagId")) 

        affNotReconciledSheet.write(row, inc(), offer.get("saleDate")) 

        affNotReconciledSheet.write(row, inc(), offer.get("saleAmount")) 

        affNotReconciledSheet.write(row, inc(), offer.get("payOut")) 

        affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))

        affNotReconciledSheet.write(row, inc(), offer.get("ip"))

        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, worksheet2, affNotReconciledSheet):

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    row = 0

    global i

    i=-1    

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

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

    worksheet.write(row, inc(), 'Version Code', 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(), 'IP', 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)

    i = -1

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

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

    worksheet1.write(row, inc(), 'Version Code', 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(), 'IP', 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) 

    i = -1

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

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

    worksheet2.write(row, inc(), 'Version Code', boldStyle)

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

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

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

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

    worksheet2.write(row, inc(), 'Sale Amount', boldStyle)

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

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

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

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

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

    worksheet2.write(row, inc(), 'CashBack', boldStyle)

    i =-1

    affNotReconciledSheet.write(row, inc(), 'AdId', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'subTagId', boldStyle) 

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

    affNotReconciledSheet.write(row, inc(), 'Sale Amount', boldStyle) 

    affNotReconciledSheet.write(row, inc(), 'Pay Out', boldStyle) 

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

    affNotReconciledSheet.write(row, inc(), 'IP', boldStyle)

def getUserAmountReconciled():

    pass

def getUserOrders():

    global i

    rb = open_workbook(XLS_FILENAME,formatting_info=True)

    wb = copy(rb)

    worksheet = wb.add_sheet("All Users")

    worksheet1 = wb.add_sheet("Suspected Users")

    db=client.Dtr

    results = Mysql.fetchResult('''

        select ow.*, u.username, crm1.order_count, u.referrer from order_view ow left join users u on u.id = ow.user_id left join (select user_id, count(*) as order_count from order_view where status = 'ORDER_CREATED'  group by user_id) as crm1 on ow.user_id = crm1.user_id where lower(u.referrer) not like 'emp%' 

        ''',)

    orders = []

    orders.append("")

    orderH = []

    row = 0

    i=-1

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

    worksheet1.write(row, i, 'User Id', boldStyle)

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

    worksheet1.write(row, i, 'User name', boldStyle)

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

    worksheet1.write(row, i, 'Order Id', boldStyle)

    worksheet.write(row, inc(), 'Created On', boldStyle)

    worksheet1.write(row, i, 'Created On', boldStyle)

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

    worksheet1.write(row, i, 'Store Id', boldStyle)

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

    worksheet1.write(row, i, 'Merchant Order Id', boldStyle)

    worksheet.write(row, inc(), 'Amount Paid', boldStyle)

    worksheet1.write(row, i, 'Amount Paid', boldStyle)

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

    worksheet1.write(row, i, 'Cashback', boldStyle)

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

    worksheet1.write(row, i, 'Order Status', boldStyle)

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

    worksheet1.write(row, i, 'Detailed Status', boldStyle)

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

    worksheet1.write(row, i, 'Cashback Status', boldStyle)

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

    worksheet1.write(row, i, 'Reconciled', boldStyle)

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

    worksheet1.write(row, i, 'IP', boldStyle)

    row=0

    row2=0

    for result in results:

        morder = db.merchantOrder.find_one({"orderId":result[0]})

        if morder is not None:

            subOrders = morder.get("subOrders")

            if subOrders is not None:

                for  subOrder in subOrders:

                    if subOrder.get("cashBackStatus") == Store.CB_APPROVED or subOrder.get("cashBackStatus") == Store.CB_PENDING:

                        row +=1

                        i=-1

                        worksheet.write(row, inc(), result[0])

                        worksheet.write(row, inc(), result[-3])

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

                        worksheet.write(row, inc(), result[-4])

                        worksheet.write(row, inc(), result[2])

                        worksheet.write(row, inc(), morder.get("merchantOrderId"))

                        #order.append(subOrder.get("merchantSubOrderId"))

                        worksheet.write(row, inc(), subOrder.get("amountPaid"))

                        worksheet.write(row, inc(), subOrder.get("cashBackAmount"))

                        worksheet.write(row, inc(), subOrder.get("status"))

                        worksheet.write(row, inc(), subOrder.get("detailedStatus"))

                        worksheet.write(row, inc(), subOrder.get("cashBackStatus"))

                        worksheet.write(row, inc(), False if morder.get("reconciled") is None else True)

                        if morder.get("reconciled") and morder.get("storeId")==3:

                            try:

                                worksheet.write(row, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))

                            except:

                                print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")

                        if result[-2] >= 10 and result[-1].lower().startswith("crm"):

                            row2 +=1 

                            i=-1

                            worksheet1.write(row2, inc(), result[0])

                            worksheet1.write(row2, inc(), result[-3])

                            worksheet1.write(row2, inc(), result[1])

                            worksheet1.write(row2, inc(), result[-4])

                            worksheet1.write(row2, inc(), result[2])

                            worksheet1.write(row2, inc(), morder.get("merchantOrderId"))

                            worksheet1.write(row2, inc(), subOrder.get("amountPaid"))

                            worksheet1.write(row2, inc(), subOrder.get("cashBackAmount"))

                            worksheet1.write(row2, inc(), subOrder.get("status"))

                            worksheet1.write(row2, inc(), subOrder.get("detailedStatus"))

                            worksheet1.write(row2, inc(), subOrder.get("cashBackStatus"))

                            worksheet1.write(row2, inc(), False if morder.get("reconciled") is None else True)

                            if morder.get("reconciled") and morder.get("storeId")==3:

                                try:

                                    worksheet1.write(row2, inc(), db.snapdealOrderAffiliateInfo.find_one({"adId":morder.get("adId"), "reconciled":True}).get("ip"))

                                except:

                                    print "Could not find", morder.get("adId"), "that is reconcired for order", morder.get("orderId")

    wb.save(XLS_FILENAME)

def inc():

    global i

    i+=1

    return i

def main():

    generateSnapDealReco()

    getUserOrders()

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

if __name__ == '__main__':

    main()