Subversion Repositories SmartDukaan

Rev

Rev 17012 | Rev 17014 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 17012 Rev 17013
Line 41... Line 41...
41 
XLS_FILENAME = "snapdealaffiliatereco.xls"
 41 
XLS_FILENAME = "snapdealaffiliatereco.xls"
42 
XLS_O_FILENAME = "allorders.xls"
 42 
XLS_O_FILENAME = "allorders.xls"
43 
XLS_F_FILENAME = "flipkartaffiliatereco.xls"
 43 
XLS_F_FILENAME = "flipkartaffiliatereco.xls"
44 
XLS_A_FILENAME = "amazonaffiliatereco.xls"
 44 
XLS_A_FILENAME = "amazonaffiliatereco.xls"
45 
XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"
 45 
XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"
- 
 
 46 
XLS_HS_FILENAME = "homeshopaffiliatereco.xls"
46 
boldStyle = xlwt.XFStyle()
 47 
boldStyle = xlwt.XFStyle()
47 
f = xlwt.Font()
 48 
f = xlwt.Font()
48 
f.bold = True
 49 
f.bold = True
49 
boldStyle.font = f
 50 
boldStyle.font = f
50 
i = -1
 51 
i = -1
Line 448... Line 449...
448 
        if total ==0:
 449 
        if total ==0:
449 
            continue
 450 
            continue
450 
        tbody.append(rowtemplate.format(offer.get("_id"), offer.get("paidAmount"), approved, int((approved*100/total)), rejected, int((rejected*100/total))))
 451 
        tbody.append(rowtemplate.format(offer.get("_id"), offer.get("paidAmount"), approved, int((approved*100/total)), rejected, int((rejected*100/total))))
451 
    message = mailBodyTemplate.format("".join(tbody))
 452 
    message = mailBodyTemplate.format("".join(tbody))
452 
    workbook.save(XLS_FILENAME)
 453 
    workbook.save(XLS_FILENAME)
- 
 
 454
- 
 
 455 
def generateHomeShopReco():
- 
 
 456 
    global i
- 
 
 457 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 7)
- 
 
 458 
    db = client.Dtr
- 
 
 459 
    notReconciled = {}
- 
 
 460 
    matchedList = []
- 
 
 461 
    workbook = xlwt.Workbook()
- 
 
 462 
    worksheet = workbook.add_sheet("HomeShop18 Reconciled")
- 
 
 463 
    worksheet1 = workbook.add_sheet("HomeShop18 Reconciled All")
- 
 
 464 
    worksheet2 = workbook.add_sheet("HomeShop18 Orders not reconciled")
- 
 
 465 
    affNotReconciledSheet = workbook.add_sheet("HomeShop18 Aff not reconciled")
- 
 
 466 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
- 
 
 467 
    row = 0
- 
 
 468 
    anotherrow = 0
- 
 
 469 
    row2 = 0
- 
 
 470 
    row5=0
- 
 
 471
- 
 
 472 
    for row1 in curs:
- 
 
 473 
        orderId = row1[0]
- 
 
 474 
        order = db.merchantOrder.find_one({"orderId":orderId})
- 
 
 475 
        if order is None:
- 
 
 476 
            continue
- 
 
 477 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
- 
 
 478 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"merchantOrderId":order["merchantOrderId"]}))
- 
 
 479 
        anotherrow += 1
- 
 
 480 
        try:
- 
 
 481 
            formattedTimestamp = order['placedOn']
- 
 
 482 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
- 
 
 483 
        except:
- 
 
 484 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
- 
 
 485 
            formattedTimestamp = datetime.strftime(utils.fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
- 
 
 486
- 
 
 487 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
- 
 
 488 
            matchedList.append(order["subTagId"])
- 
 
 489 
            db.shopcluesOrderAffiliateInfo.update({"merchantOrderId":aff[0].get("merchantOrderId")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
- 
 
 490 
            order['reconciled'] = True
- 
 
 491 
            order['adId'] = aff[0].get("uniqueKey")
- 
 
 492 
            db.merchantOrder.save(order)
- 
 
 493 
            row += 1
- 
 
 494 
            i=-1
- 
 
 495
- 
 
 496 
            worksheet.write(row, inc(), orderId)
- 
 
 497 
            worksheet1.write(anotherrow, i, orderId)
- 
 
 498 
            worksheet.write(row, inc(), row1[1])
- 
 
 499 
            worksheet1.write(anotherrow, i, row1[1])
- 
 
 500 
            worksheet.write(row, inc(), row1[-1])
- 
 
 501 
            worksheet1.write(anotherrow, i, row1[-1])
- 
 
 502 
            worksheet.write(row, inc(), row1[-2])
- 
 
 503 
            worksheet1.write(anotherrow, i, row1[-2])
- 
 
 504 
            worksheet.write(row, inc(), row1[-3])
- 
 
 505 
            worksheet1.write(anotherrow, i, row1[-3])
- 
 
 506 
            worksheet.write(row, inc(), order['merchantOrderId'])
- 
 
 507 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
- 
 
 508 
            worksheet.write(row, inc(), order['subTagId'])
- 
 
 509 
            worksheet1.write(anotherrow, i, order['subTagId'])
- 
 
 510 
            worksheet.write(row, inc(), order['placedOn'])
- 
 
 511 
            worksheet1.write(anotherrow, i, order['placedOn'])
- 
 
 512 
            worksheet.write(row, inc(), formattedTimestamp)
- 
 
 513 
            worksheet1.write(anotherrow, i, formattedTimestamp)
- 
 
 514 
            worksheet.write(row, inc(), int(order['paidAmount']))
- 
 
 515 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
- 
 
 516 
            worksheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
- 
 
 517 
            worksheet1.write(anotherrow, i, datetime.strftime(utils.fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
- 
 
 518 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
- 
 
 519 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
- 
 
 520 
            worksheet.write(row, inc(), aff[0]['payOut'])
- 
 
 521 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
- 
 
 522 
            worksheet.write(row, inc(), "NA")
- 
 
 523 
            worksheet1.write(anotherrow, i, "NA")
- 
 
 524 
            k = i
- 
 
 525 
            row -= 1
- 
 
 526 
            anotherrow -= 1
- 
 
 527 
            for subOrder in order['subOrders']:
- 
 
 528 
                i = k
- 
 
 529 
                row += 1
- 
 
 530 
                anotherrow += 1
- 
 
 531 
                worksheet.write(row, inc(), subOrder['productTitle'])
- 
 
 532 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
- 
 
 533 
                worksheet.write(row, inc(), subOrder['amountPaid'])
- 
 
 534 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
- 
 
 535 
                worksheet.write(row, inc(), subOrder['quantity'])
- 
 
 536 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
- 
 
 537 
                worksheet.write(row, inc(), subOrder['status'])
- 
 
 538 
                worksheet1.write(anotherrow, i, subOrder['status'])
- 
 
 539 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
- 
 
 540 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
- 
 
 541 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
- 
 
 542 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
- 
 
 543
- 
 
 544 
        else:
- 
 
 545 
            i=-1
- 
 
 546 
            worksheet1.write(anotherrow, inc(), orderId)
- 
 
 547 
            worksheet1.write(anotherrow, inc(), row1[1])
- 
 
 548 
            worksheet1.write(anotherrow, inc(), row1[-1])
- 
 
 549 
            worksheet1.write(anotherrow, inc(), row1[-2])
- 
 
 550 
            worksheet1.write(anotherrow, inc(), row1[-3])
- 
 
 551 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
- 
 
 552 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
- 
 
 553 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
- 
 
 554 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
- 
 
 555 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
- 
 
 556
- 
 
 557 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
- 
 
 558 
            inc()
- 
 
 559 
            inc()
- 
 
 560 
            inc()
- 
 
 561 
            inc()
- 
 
 562 
            k = i
- 
 
 563 
            anotherrow -= 1
- 
 
 564 
            for subOrder in order['subOrders']:
- 
 
 565 
                anotherrow += 1
- 
 
 566 
                i = k
- 
 
 567 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
- 
 
 568 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
- 
 
 569 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
- 
 
 570 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
- 
 
 571 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
- 
 
 572 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
- 
 
 573 
            row5 += 1
- 
 
 574 
            i=-1
- 
 
 575 
            worksheet2.write(row5, inc(), order["orderId"])
- 
 
 576 
            worksheet2.write(row5, inc(), row1[1])
- 
 
 577 
            worksheet2.write(row5, inc(), row1[-1])
- 
 
 578 
            worksheet2.write(row5, inc(), row1[-2])
- 
 
 579 
            worksheet2.write(row5, inc(), row1[-3])
- 
 
 580 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
- 
 
 581 
            worksheet2.write(row5, inc(), order['subTagId'])
- 
 
 582 
            worksheet2.write(row5, inc(), order['placedOn'])
- 
 
 583 
            worksheet2.write(row5, inc(), formattedTimestamp)
- 
 
 584 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
- 
 
 585 
            row5 -=1
- 
 
 586 
            k=i
- 
 
 587 
            for subOrder in order['subOrders']:
- 
 
 588 
                i = k
- 
 
 589 
                row5 += 1
- 
 
 590 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
- 
 
 591 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
- 
 
 592 
                worksheet2.write(row5, inc(), subOrder['quantity'])
- 
 
 593 
                worksheet2.write(row5, inc(), subOrder['status'])
- 
 
 594 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
- 
 
 595 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
- 
 
 596 
    last30Days =  date.today() - timedelta(days=30)
- 
 
 597 
    int(time.mktime(last30Days.timetuple()))
- 
 
 598 
    row = 0
- 
 
 599 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
- 
 
 600 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
- 
 
 601 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
- 
 
 602 
        subTagId = offer.get("subTagId")
- 
 
 603 
        saleTime = offer.get("transactionTime")
- 
 
 604 
        orders = getOrdersByTag(subTagId, 7)
- 
 
 605 
        for order in orders:
- 
 
 606 
            if notReconciled.has_key(order[0]):
- 
 
 607 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
- 
 
 608 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
- 
 
 609 
                    i = column
- 
 
 610 
                    worksheet1.write(roww, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
- 
 
 611 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
- 
 
 612 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
- 
 
 613 
                    del notReconciled[order[0]]
- 
 
 614 
                    print "found match for user", order[1]
- 
 
 615 
                    db.homeshopOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
- 
 
 616 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
- 
 
 617 
                    break
- 
 
 618
- 
 
 619 
    unreconciledOrders = notReconciled.keys()
- 
 
 620
- 
 
 621 
    for offer in db.homeshopOrderAffiliateInfo.find({"reconciled":{"$exists":False},
- 
 
 622 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
- 
 
 623 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
- 
 
 624 
        row += 1
- 
 
 625 
        i=-1
- 
 
 626 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
- 
 
 627 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
- 
 
 628 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(utils.fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
- 
 
 629 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
- 
 
 630 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
- 
 
 631 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
- 
 
 632 
        affNotReconciledSheet.write(row, inc(), 'NA')
- 
 
 633 
 
- 
 
 634 
    workbook.save(XLS_HS_FILENAME)
- 
 
 635
453 
    return message
 636 
    return message
454
637
455
638
456 
def generateShopcluesReco():
 639 
def generateShopcluesReco():
457 
    global i
 640 
    global i