Subversion Repositories SmartDukaan

'''
Created on Mar 27, 2015

@author: amit
'''
from datetime import datetime, timedelta
from dtr.storage.Mysql import getOrdersAfterDate
from pymongo.mongo_client import MongoClient
import time
import xlwt

client = MongoClient('mongodb://localhost:27017/')
def generateAmazonReco(fileName):
    global i
    db = client.Dtr
    notReconciled = {}
    matchedList = []
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet("Orders Reconciled")
    worksheet1 = workbook.add_sheet("Orders Reconciled All")
    worksheet2 = workbook.add_sheet("Orders Not Reconciled")
    worksheet3 = workbook.add_sheet("Affiliate Not Reconciled")
    addHeaders(worksheet, worksheet1, worksheet2, worksheet3)
    
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 1)
    placedOnSince = int(time.mktime((datetime.now() - timedelta(days=30)).timetuple()))
    row = 0
    row1 = 0
    row2 = 0
    row3= 0
    
    for ro in curs:
        orderId = ro[0]
        order = db.merchantOrder.find_one({"orderId":orderId})
        if order is None:
            continue
        
        subOrders = order.get("subOrders")
        if subOrders is None:
            continue
        placedOnTime = int(time.mktime(datetime.strptime(order.get("placedOn"), "%d %B %Y").timetuple()))
        aff = db.amazonAffiliateInfo.find_one({"subTagId":order["subTagId"], "time": placedOnTime})
        for subOrder in subOrders:
            row1 += 1
            i =-1
            worksheet1.write(row1, inc(), orderId)
            worksheet1.write(row1, inc(), ro[1])
            worksheet1.write(row1, inc(), ro[-1])
            worksheet1.write(row1, inc(), ro[-3])
            worksheet1.write(row1, inc(), ro[-2])
            worksheet1.write(row1, inc(), order['merchantOrderId'])
            worksheet1.write(row1, inc(), subOrder['productTitle'])
            worksheet1.write(row1, inc(), subOrder['amountPaid'])
            worksheet1.write(row1, inc(), subOrder['quantity'])
            worksheet1.write(row1, inc(), subOrder['status'])
            worksheet1.write(row1, inc(), subOrder['detailedStatus'])
            worksheet1.write(row1, inc(), subOrder['cashBackStatus'])
            worksheet1.write(row1, inc(), subOrder['cashBackAmount'])
            worksheet1.write(row1, inc(), subOrder['placedOn'])
            worksheet1.write(row1, inc(), order['subTagId'])
            if aff is not None:
                row += 1
                i =-1
                worksheet.write(row, inc(), orderId)
                worksheet.write(row, inc(), ro[1])
                worksheet.write(row, inc(), ro[-1])
                worksheet.write(row, inc(), ro[-3])
                worksheet.write(row, inc(), ro[-2])
                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'])
                worksheet.write(row, inc(),"Yes")
                worksheet1.write(row1, i,"Yes")
                db.amazonAffiliateInfo.update({"subTagId":order["subTagId"], "time": placedOnTime}, 
                                              {"$set":{"reconciled":True}})
                db.merchantOrder.update({"merchantOrderId":order.get("merchantOrderId")},{"$set":{"reconciled":True}})
            else:
                row2 +=1
                i =-1
                worksheet2.write(row2, inc(), orderId)
                worksheet2.write(row2, inc(), ro[1])
                worksheet2.write(row2, inc(), ro[-1])
                worksheet2.write(row2, inc(), ro[-3])
                worksheet2.write(row2, inc(), ro[-2])
                worksheet2.write(row2, inc(), order['merchantOrderId'])
                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['detailedStatus'])
                worksheet2.write(row2, inc(), subOrder['cashBackStatus'])
                worksheet2.write(row2, inc(), subOrder['cashBackAmount'])
                worksheet2.write(row2, inc(), subOrder['placedOn'])
                worksheet2.write(row2, inc(), order['subTagId'])
                worksheet1.write(row1, inc(), "No")
     
    for aff in db.amazonAffiliateInfo.find({"reconciled":{"$exists":False}, "time":{"$gt":placedOnSince}}):
        row3 += 1
        i=-1
        worksheet3.write(row3, inc(), aff.get("linkType"))
        worksheet3.write(row3, inc(), aff.get("totalOrderedQuantity"))
        worksheet3.write(row3, inc(), aff.get("dateOrdered"))
        worksheet3.write(row3, inc(), aff.get("directOrderedQuantity"))
        worksheet3.write(row3, inc(), aff.get("indirectOrderedQuantity"))
        worksheet3.write(row3, inc(), aff.get("directClicks"))
        worksheet3.write(row3, inc(), aff.get("trackingId"))
        worksheet3.write(row3, inc(), aff.get("subTagId"))
        worksheet3.write(row3, inc(), aff.get("productLine"))
    
        
    workbook.save(fileName)
            
            
    
def addHeaders(worksheet, worksheet1, worksheet2, worksheet3):
    global i
    i=-1
    boldStyle = xlwt.XFStyle()
    f = xlwt.Font()
    f.bold = True
    boldStyle.font = f
    worksheet1.write(0, inc(), 'Order Id', boldStyle)
    worksheet.write(0, i, 'Order Id', boldStyle)
    worksheet2.write(0, i, 'Order Id', boldStyle)
    worksheet.write(0, inc(), 'User Id', boldStyle)
    worksheet1.write(0, i, 'User Id', boldStyle)
    worksheet2.write(0, i, 'User Id', boldStyle)
    worksheet.write(0, inc(), 'Username', boldStyle)
    worksheet1.write(0, i, 'Username', boldStyle)
    worksheet2.write(0, i, 'Username', boldStyle)
    worksheet.write(0, inc(), 'Device', boldStyle)
    worksheet1.write(0, i, 'Device', boldStyle)
    worksheet2.write(0, i, 'Device', boldStyle)
    worksheet.write(0, inc(), 'Version Code', boldStyle)
    worksheet1.write(0, i, 'Version Code', boldStyle)
    worksheet2.write(0, i, 'Version Code', boldStyle)
    worksheet.write(0, inc(), 'Merchant Order Id', boldStyle)
    worksheet1.write(0, i, 'Merchant Order Id', boldStyle)
    worksheet2.write(0, i, 'Merchant Order Id', boldStyle)
    worksheet.write(0, inc(), 'Product Title', boldStyle)
    worksheet1.write(0, i, 'Product Title', boldStyle)
    worksheet2.write(0, i, 'Product Title', boldStyle)
    worksheet.write(0, inc(), 'Price', boldStyle)
    worksheet1.write(0, i, 'Price', boldStyle)
    worksheet2.write(0, i, 'Price', boldStyle)
    worksheet.write(0, inc(), 'Quantity', boldStyle)
    worksheet1.write(0, i, 'Quantity', boldStyle)
    worksheet2.write(0, i, 'Quantity', boldStyle)
    worksheet.write(0, inc(), 'Status', boldStyle)
    worksheet1.write(0, i, 'Status', boldStyle)
    worksheet2.write(0, i, 'Status', boldStyle)
    worksheet.write(0, inc(), 'Detailed Status', boldStyle)
    worksheet1.write(0, i, 'Detailed Status', boldStyle)
    worksheet2.write(0, i, 'Detailed Status', boldStyle)
    worksheet.write(0, inc(), 'Cashback Status', boldStyle)
    worksheet1.write(0, i, 'Cashback Status', boldStyle)
    worksheet2.write(0, i, 'Cashback Status', boldStyle)
    worksheet.write(0, inc(), 'CashBack', boldStyle)
    worksheet1.write(0, i, 'CashBack', boldStyle)
    worksheet2.write(0, i, 'CashBack', boldStyle)
    worksheet.write(0, inc(), 'SubtagId', boldStyle)
    worksheet1.write(0, i, 'SubtagId', boldStyle)
    worksheet2.write(0, i, 'SubtagId', boldStyle)
    worksheet.write(0, inc(), 'Sale Date', boldStyle)
    worksheet1.write(0, i, 'Sale Date', boldStyle)
    worksheet2.write(0, i, 'Sale Date', boldStyle)
    worksheet.write(0, inc(), 'Reconciled', boldStyle)
    worksheet1.write(0, i, 'Reconciled', boldStyle)
    i=-1
    worksheet3.write(0, inc(), 'linkType',boldStyle)
    worksheet3.write(0, inc(), 'totalOrderedQuantity',boldStyle)
    worksheet3.write(0, inc(), 'dateOrdered',boldStyle)
    worksheet3.write(0, inc(), 'directOrderedQuantity',boldStyle)
    worksheet3.write(0, inc(), 'indirectOrderedQuantity',boldStyle)
    worksheet3.write(0, inc(), 'directClicks',boldStyle)
    worksheet3.write(0, inc(), 'trackingId',boldStyle)
    worksheet3.write(0, inc(), 'subTagId',boldStyle)
    worksheet3.write(0, inc(), 'productLine',boldStyle)
    
def main():
    generateAmazonReco("amazon.xls")

    
def inc():
    global i
    i+=1
    return i


if __name__ == '__main__':
    main()