Subversion Repositories SmartDukaan

Rev

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

Rev 15578 Rev 15791
Line 37... Line 37...
37 
 
 37 
 
38 
XLS_FILENAME = "snapdealaffiliatereco.xls"
 38 
XLS_FILENAME = "snapdealaffiliatereco.xls"
39 
XLS_O_FILENAME = "allorders.xls"
 39 
XLS_O_FILENAME = "allorders.xls"
40 
XLS_F_FILENAME = "flipkartaffiliatereco.xls"
 40 
XLS_F_FILENAME = "flipkartaffiliatereco.xls"
41 
XLS_A_FILENAME = "amazonaffiliatereco.xls"
 41 
XLS_A_FILENAME = "amazonaffiliatereco.xls"
- 
 
 42 
XLS_SC_FILENAME = "shopcluesaffiliatereco.xls"
42 
boldStyle = xlwt.XFStyle()
 43 
boldStyle = xlwt.XFStyle()
43 
f = xlwt.Font()
 44 
f = xlwt.Font()
44 
f.bold = True
 45 
f.bold = True
45 
boldStyle.font = f
 46 
boldStyle.font = f
46 
i = -1
 47 
i = -1
Line 403... Line 404...
403 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
404 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
404 
        affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))
 405 
        affNotReconciledSheet.write(row, inc(), offer.get("conversionStatus"))
405 
        affNotReconciledSheet.write(row, inc(), offer.get("ip"))
 406 
        affNotReconciledSheet.write(row, inc(), offer.get("ip"))
406 
 
 407 
 
407 
    workbook.save(XLS_FILENAME)
 408 
    workbook.save(XLS_FILENAME)
- 
 
 409
- 
 
 410 
def generateShopcluesReco():
- 
 
 411 
    global i
- 
 
 412 
    curs = getOrdersAfterDate(datetime.now() - timedelta(days=30), 3)
- 
 
 413 
    db = client.Dtr
- 
 
 414 
    notReconciled = {}
- 
 
 415 
    matchedList = []
- 
 
 416 
    workbook = xlwt.Workbook()
- 
 
 417 
    worksheet = workbook.add_sheet("Shopclues Reconciled")
- 
 
 418 
    worksheet1 = workbook.add_sheet("Shopclues Reconciled All")
- 
 
 419 
    worksheet2 = workbook.add_sheet("Shopclues Orders not reconciled")
- 
 
 420 
    affNotReconciledSheet = workbook.add_sheet("Shopclues Aff not reconciled")
- 
 
 421 
    setHeaders(worksheet, worksheet1, worksheet2, affNotReconciledSheet)
- 
 
 422 
    row = 0
- 
 
 423 
    anotherrow = 0
- 
 
 424 
    row2 = 0
- 
 
 425 
    row5=0
- 
 
 426
- 
 
 427 
    for row1 in curs:
- 
 
 428 
        orderId = row1[0]
- 
 
 429 
        order = db.merchantOrder.find_one({"orderId":orderId})
- 
 
 430 
        if order is None:
- 
 
 431 
            continue
- 
 
 432 
        #saleTime = int(time.mktime(datetime.strptime(order['placedOn'], "%b %d, %Y, %I:%M %p").timetuple()))  26 May 2015 02:12 PM
- 
 
 433 
        aff = list(db.shopcluesOrderAffiliateInfo.find({"orderRef":order["merchantOrderId"]}))
- 
 
 434 
        anotherrow += 1
- 
 
 435 
        try:
- 
 
 436 
            formattedTimestamp = order['placedOn']
- 
 
 437 
            timestamp1 = int(time.mktime(datetime.strptime(order['placedOn'], "%d %b %Y %I:%M %p").timetuple()))
- 
 
 438 
        except:
- 
 
 439 
            timestamp1 = int(time.mktime(row1[-4].timetuple()))
- 
 
 440 
            formattedTimestamp = datetime.strftime(fromTimeStamp(timestamp1), "%d %b %Y %I:%M %p")
- 
 
 441
- 
 
 442 
        if len(aff) > 0 and order["subTagId"] not in matchedList and long(order["paidAmount"])==long(float(aff[0]["transactionValue"])):
- 
 
 443 
            matchedList.append(order["subTagId"])
- 
 
 444 
            db.shopcluesOrderAffiliateInfo.update({"orderRef":aff[0].get("orderRef")}, {"$set":{"reconciled":True, "orderId":orderId}}, multi=True)
- 
 
 445 
            order['reconciled'] = True
- 
 
 446 
            order['adId'] = aff[0].get("uniqueKey")
- 
 
 447 
            db.merchantOrder.save(order)
- 
 
 448 
            row += 1
- 
 
 449 
            i=-1
- 
 
 450
- 
 
 451 
            worksheet.write(row, inc(), orderId)
- 
 
 452 
            worksheet1.write(anotherrow, i, orderId)
- 
 
 453 
            worksheet.write(row, inc(), row1[1])
- 
 
 454 
            worksheet1.write(anotherrow, i, row1[1])
- 
 
 455 
            worksheet.write(row, inc(), row1[-1])
- 
 
 456 
            worksheet1.write(anotherrow, i, row1[-1])
- 
 
 457 
            worksheet.write(row, inc(), row1[-2])
- 
 
 458 
            worksheet1.write(anotherrow, i, row1[-2])
- 
 
 459 
            worksheet.write(row, inc(), row1[-3])
- 
 
 460 
            worksheet1.write(anotherrow, i, row1[-3])
- 
 
 461 
            worksheet.write(row, inc(), order['merchantOrderId'])
- 
 
 462 
            worksheet1.write(anotherrow, i, order['merchantOrderId'])
- 
 
 463 
            worksheet.write(row, inc(), order['subTagId'])
- 
 
 464 
            worksheet1.write(anotherrow, i, order['subTagId'])
- 
 
 465 
            worksheet.write(row, inc(), order['placedOn'])
- 
 
 466 
            worksheet1.write(anotherrow, i, order['placedOn'])
- 
 
 467 
            worksheet.write(row, inc(), formattedTimestamp)
- 
 
 468 
            worksheet1.write(anotherrow, i, formattedTimestamp)
- 
 
 469 
            worksheet.write(row, inc(), int(order['paidAmount']))
- 
 
 470 
            worksheet1.write(anotherrow, i, int(order['paidAmount']))
- 
 
 471 
            worksheet.write(row, inc(), datetime.strftime(fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
- 
 
 472 
            worksheet1.write(anotherrow, i, datetime.strftime(fromTimeStamp(aff[0]['transactionTime']), "%d %b %Y %I:%M %p"))
- 
 
 473 
            worksheet.write(row, inc(), aff[0]['transactionValue'])
- 
 
 474 
            worksheet1.write(anotherrow, i, aff[0]['transactionValue'])
- 
 
 475 
            worksheet.write(row, inc(), aff[0]['payOut'])
- 
 
 476 
            worksheet1.write(anotherrow, i, aff[0]['payOut'])
- 
 
 477 
            worksheet.write(row, inc(), aff[0]['ip'])
- 
 
 478 
            worksheet1.write(anotherrow, i, aff[0]['ip'])
- 
 
 479 
            k = i
- 
 
 480 
            row -= 1
- 
 
 481 
            anotherrow -= 1
- 
 
 482 
            for subOrder in order['subOrders']:
- 
 
 483 
                i = k
- 
 
 484 
                row += 1
- 
 
 485 
                anotherrow += 1
- 
 
 486 
                worksheet.write(row, inc(), subOrder['productTitle'])
- 
 
 487 
                worksheet1.write(anotherrow, i, subOrder['productTitle'])
- 
 
 488 
                worksheet.write(row, inc(), subOrder['amountPaid'])
- 
 
 489 
                worksheet1.write(anotherrow, i, subOrder['amountPaid'])
- 
 
 490 
                worksheet.write(row, inc(), subOrder['quantity'])
- 
 
 491 
                worksheet1.write(anotherrow, i, subOrder['quantity'])
- 
 
 492 
                worksheet.write(row, inc(), subOrder['status'])
- 
 
 493 
                worksheet1.write(anotherrow, i, subOrder['status'])
- 
 
 494 
                worksheet.write(row, inc(), subOrder['cashBackStatus'])
- 
 
 495 
                worksheet1.write(anotherrow, i, subOrder['cashBackStatus'])
- 
 
 496 
                worksheet.write(row, inc(), subOrder['cashBackAmount'])
- 
 
 497 
                worksheet1.write(anotherrow, i, subOrder['cashBackAmount'])
- 
 
 498
- 
 
 499 
        else:
- 
 
 500 
            i=-1
- 
 
 501 
            worksheet1.write(anotherrow, inc(), orderId)
- 
 
 502 
            worksheet1.write(anotherrow, inc(), row1[1])
- 
 
 503 
            worksheet1.write(anotherrow, inc(), row1[-1])
- 
 
 504 
            worksheet1.write(anotherrow, inc(), row1[-2])
- 
 
 505 
            worksheet1.write(anotherrow, inc(), row1[-3])
- 
 
 506 
            worksheet1.write(anotherrow, inc(), order['merchantOrderId'])
- 
 
 507 
            worksheet1.write(anotherrow, inc(), order['subTagId'])
- 
 
 508 
            worksheet1.write(anotherrow, inc(), order['placedOn'])
- 
 
 509 
            worksheet1.write(anotherrow, inc(), formattedTimestamp)
- 
 
 510 
            worksheet1.write(anotherrow, inc(), int(order['paidAmount']))
- 
 
 511
- 
 
 512 
            notReconciled[orderId] = (anotherrow, i, int(order['paidAmount']), timestamp1, order["subTagId"])
- 
 
 513 
            inc()
- 
 
 514 
            inc()
- 
 
 515 
            inc()
- 
 
 516 
            inc()
- 
 
 517 
            k = i
- 
 
 518 
            anotherrow -= 1
- 
 
 519 
            for subOrder in order['subOrders']:
- 
 
 520 
                anotherrow += 1
- 
 
 521 
                i = k
- 
 
 522 
                worksheet1.write(anotherrow, inc(), subOrder['productTitle'])
- 
 
 523 
                worksheet1.write(anotherrow, inc(), subOrder['amountPaid'])
- 
 
 524 
                worksheet1.write(anotherrow, inc(), subOrder['quantity'])
- 
 
 525 
                worksheet1.write(anotherrow, inc(), subOrder['status'])
- 
 
 526 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackStatus'])
- 
 
 527 
                worksheet1.write(anotherrow, inc(), subOrder['cashBackAmount'])
- 
 
 528 
            row5 += 1
- 
 
 529 
            i=-1
- 
 
 530 
            worksheet2.write(row5, inc(), order["orderId"])
- 
 
 531 
            worksheet2.write(row5, inc(), row1[1])
- 
 
 532 
            worksheet2.write(row5, inc(), row1[-1])
- 
 
 533 
            worksheet2.write(row5, inc(), row1[-2])
- 
 
 534 
            worksheet2.write(row5, inc(), row1[-3])
- 
 
 535 
            worksheet2.write(row5, inc(), order['merchantOrderId'])
- 
 
 536 
            worksheet2.write(row5, inc(), order['subTagId'])
- 
 
 537 
            worksheet2.write(row5, inc(), order['placedOn'])
- 
 
 538 
            worksheet2.write(row5, inc(), formattedTimestamp)
- 
 
 539 
            worksheet2.write(row5, inc(), int(order['paidAmount']))
- 
 
 540 
            row5 -=1
- 
 
 541 
            k=i
- 
 
 542 
            for subOrder in order['subOrders']:
- 
 
 543 
                i = k
- 
 
 544 
                row5 += 1
- 
 
 545 
                worksheet2.write(row5, inc(), subOrder['productTitle'])
- 
 
 546 
                worksheet2.write(row5, inc(), subOrder['amountPaid'])
- 
 
 547 
                worksheet2.write(row5, inc(), subOrder['quantity'])
- 
 
 548 
                worksheet2.write(row5, inc(), subOrder['status'])
- 
 
 549 
                worksheet2.write(row5, inc(), subOrder['cashBackStatus'])
- 
 
 550 
                worksheet2.write(row5, inc(), subOrder['cashBackAmount'])
- 
 
 551
- 
 
 552 
    last30Days =  date.today() - timedelta(days=30)
- 
 
 553 
    int(time.mktime(last30Days.timetuple()))
- 
 
 554 
    row = 0
- 
 
 555 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
- 
 
 556 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
- 
 
 557 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
- 
 
 558 
        subTagId = offer.get("subTagId")
- 
 
 559 
        saleTime = offer.get("transactionTime")
- 
 
 560 
        orders = getOrdersByTag(subTagId, 7)
- 
 
 561 
        for order in orders:
- 
 
 562 
            if notReconciled.has_key(order[0]):
- 
 
 563 
                (roww, column, paidAmount, timestamp1, oSubTagId) = notReconciled[order[0]]
- 
 
 564 
                if paidAmount==int(offer.get("transactionValue")) and timestamp1 <= saleTime and timestamp1 + 120 >= saleTime:
- 
 
 565 
                    i = column
- 
 
 566 
                    worksheet1.write(roww, inc(), datetime.strftime(fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
- 
 
 567 
                    worksheet1.write(roww, inc(), offer.get("transactionValue"))
- 
 
 568 
                    worksheet1.write(roww, inc(), offer.get("payOut"))
- 
 
 569 
                    del notReconciled[order[0]]
- 
 
 570 
                    print "found match for user", order[1]
- 
 
 571 
                    db.shopcluesOrderAffiliateInfo.update({"transactionId":offer.get("transactionId")}, {"$set":{"reconciled":True, "orderId":order[0]}}, multi=True)
- 
 
 572 
                    db.merchantOrder.update({"orderId":order[0]}, {"$set":{"reconciled":True, "adId":offer.get("uniqueKey")}}, multi=True)
- 
 
 573 
                    break
- 
 
 574
- 
 
 575 
    unreconciledOrders = notReconciled.keys()
- 
 
 576
- 
 
 577 
    for offer in db.shopcluesOrderAffiliateInfo.find({"reconciled":{"$exists":False},
- 
 
 578 
                                                     "transactionTime":{"$gt":int(time.mktime(last30Days.timetuple())),
- 
 
 579 
                                                                 "$lt":int(time.mktime(date.today().timetuple()))}} ):
- 
 
 580 
        row += 1
- 
 
 581 
        i=-1
- 
 
 582 
        affNotReconciledSheet.write(row, inc(), offer.get("uniqueKey"))
- 
 
 583 
        affNotReconciledSheet.write(row, inc(), offer.get("subTagId"))
- 
 
 584 
        affNotReconciledSheet.write(row, inc(), datetime.strftime(fromTimeStamp(offer.get("transactionTime")), "%d %b %Y %I:%M %p"))
- 
 
 585 
        affNotReconciledSheet.write(row, inc(), offer.get("transactionValue"))
- 
 
 586 
        affNotReconciledSheet.write(row, inc(), offer.get("payOut"))
- 
 
 587 
        affNotReconciledSheet.write(row, inc(), offer.get("status"))
- 
 
 588 
        affNotReconciledSheet.write(row, inc(), 'NA')
- 
 
 589 
 
- 
 
 590 
    workbook.save(XLS_SC_FILENAME)
408
591
409 
def sendmail(email, message, title, *varargs):
 592 
def sendmail(email, message, title, *varargs):
410 
    if email == "":
 593 
    if email == "":
411 
        return
 594 
        return
412 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 595 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
Line 745... Line 928...
745 
    sheet.write(0, inc(), )
 928 
    sheet.write(0, inc(), )
746 
def main():
 929 
def main():
747
930
748 
    generateSnapDealReco()
 931 
    generateSnapDealReco()
749 
    generateFlipkartReco()
 932 
    generateFlipkartReco()
- 
 
 933 
    generateShopcluesReco()
750 
    generateAmazonReco(XLS_A_FILENAME)
 934 
    generateAmazonReco(XLS_A_FILENAME)
751 
    getUserOrders()
 935 
    getUserOrders()
752 
    sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME)
 936 
    sendmail(["amit.gupta@shop2020.in","rajneesh.arora@saholic.com"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME, XLS_SC_FILENAME)
753 
    #sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME)
 937 
    #sendmail(["amit.gupta@shop2020.in"], "", "Affiliate Reco", XLS_FILENAME, XLS_F_FILENAME, XLS_A_FILENAME)
754 
    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com"], "", "All DTR Orders", XLS_O_FILENAME)
 938 
    sendmail(["amit.gupta@shop2020.in", "rajneesh.arora@saholic.com", "chaitnaya.vats@saholic.com", "manoj.kumar@saholic.com"], "", "All DTR Orders", XLS_O_FILENAME)
755 
 
 939 
 
756 
if __name__ == '__main__':
 940 
if __name__ == '__main__':
757 
    #info = client.Dtr.flipkartOrderAffiliateInfo.find()
 941 
    #info = client.Dtr.flipkartOrderAffiliateInfo.find()