Subversion Repositories SmartDukaan

'''

Created on 18-Apr-2016

@author: manas

'''

from datetime import date, datetime, timedelta, datetime

from elixir import *

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from operator import or_

from pymongo.mongo_client import MongoClient

from sqlalchemy.sql.expression import func, and_

from time import strftime

from xlrd import open_workbook

from xlutils.copy import copy

from xlwt.Workbook import Workbook

import MySQLdb

import pymongo

import smtplib

import sys

import time

import xlwt

TMP_FILE = "WALLET_REFUND_REPORT.xls"  

con = None

orderIds = []

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "Wallet Refund Report for " + str(date.today() - timedelta(days=1))

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587    

date_format = xlwt.XFStyle()

date_format.num_format_str = 'yyyy/mm/dd'

datetime_format = xlwt.XFStyle()

datetime_format.num_format_str = 'yyyy/mm/dd HH:MM AM/PM'

default_format = xlwt.XFStyle()

def getMailBody():

    inputs = "Total Credited Amount " + str(totalCreditedSum) + "<br> Total Approved Amount " + str(totalApprovedSum)+ " <br> Total Cancelled Amount " + str(totalCancelledSum) + " <br> Total Pending Amount " + str(totalPendingSum)    

    return inputs

def sendmail(email, message, fileName, 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

    msg.attach(MIMEText(getMailBody(), 'html'))

    fileMsg = MIMEBase('application', 'vnd.ms-excel')

    fileMsg.set_payload(file(TMP_FILE).read())

    encoders.encode_base64(fileMsg)

    fileMsg.add_header('Content-Disposition', 'attachment;filename=' + fileName)

    msg.attach(fileMsg)

    MAILTO = ['rajender.singh@saholic.com','rajneesh.arora@saholic.com', 'amit.gupta@saholic.com','khushal.bhatia@saholic.com']

    #MAILTO = ['rajender.singh@saholic.com']

    mailServer.login(SENDER, PASSWORD)

    mailServer.sendmail(SENDER, MAILTO, msg.as_string())

def get_mongo_connection(host='localhost', port=27017):

    global con

    if con is None:

        print "Establishing connection %s host and port %d" %(host,port)

        try:

            con = pymongo.MongoClient(host, port)

        except Exception, e:

            print e

            return None

    return con

def getLastDate():

    lastDate = datetime.now() - timedelta(days=1)

    last =  lastDate.strftime('%Y-%m-%d')

    last_date_object = datetime.strptime(last+" 00:00:00","%Y-%m-%d %H:%M:%S")

    print 'Last Date',last_date_object

    return to_java_date(last_date_object)

def getCurrentDate():

    currentDate =  datetime.now()

    cur =  currentDate.strftime('%Y-%m-%d')

    cur_date_object = datetime.strptime(cur+" 00:00:00","%Y-%m-%d %H:%M:%S")

    print 'Current Date',cur_date_object

    return to_java_date(cur_date_object)

totalApprovedSum = 0     

totalCancelledSum = 0   

totalCreditedSum = 0

totalPendingSum = 0

def generatePendingWalletRefundReport():

    global workbook

    global totalPendingSum

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("Pending")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(row, 0, 'UserId', boldStyle)

    worksheet.write(row, 1, 'EmailId', boldStyle)

    worksheet.write(row, 2, 'Mobile Number', boldStyle)

    worksheet.write(row, 3, 'Status', boldStyle)

    worksheet.write(row, 4, 'Amount', boldStyle)

    worksheet.write(row, 5, 'Type', boldStyle)

    worksheet.write(row, 6, 'Store', boldStyle)   

    worksheet.write(row, 7, 'Reference Number', boldStyle)

    worksheet.write(row, 8, 'Reference Description', boldStyle)

    worksheet.write(row, 9, 'Approved By', boldStyle)

    worksheet.write(row, 10, 'Created Timestamp', boldStyle)

    worksheet.write(row, 11, 'Updated Timestamp', boldStyle)

    queryFilter = {"$and":

                   [

                    {'created_timestamp':{"$gte":getLastDate()}},

                    {'created_timestamp':{"$lte":getCurrentDate()}},

                    {'status':"Pending"}

                    ]

                   }

    result = get_mongo_connection().Dtr.crmrefundwallet.find(queryFilter)

    for r in result:

        row=row+1

        worksheet.write(row, 0, r.get('user_id'))

        worksheet.write(row, 1, r.get('email_id'))

        worksheet.write(row, 2, r.get('mobile'))

        worksheet.write(row, 3, r.get('status'))

        worksheet.write(row, 4, r.get('amount'))

        totalPendingSum = totalPendingSum + r.get('amount')

        worksheet.write(row, 5, r.get('type'))

        worksheet.write(row, 6, r.get('store'))   

        worksheet.write(row, 7, r.get('reference_no'))

        worksheet.write(row, 8, r.get('reference_desc'))

        worksheet.write(row, 9, r.get('approved_by'))

        worksheet.write(row, 10, to_py_date(r.get('created_timestamp')), datetime_format)

        worksheet.write(row, 11, to_py_date(r.get('update_timestamp')) , datetime_format)

    workbook.save(TMP_FILE)

def to_java_date(py_timestamp):

    try:

        java_date =  int(time.mktime(py_timestamp.timetuple())) * 1000 + py_timestamp.microsecond / 1000

        return java_date

    except:

        return None

def to_py_date(java_timestamp):

    try:

        date = datetime.fromtimestamp(java_timestamp/1e3)

    except:

        return None

    return date

def generateApprovedWalletRefundReport():

    rb = open_workbook(TMP_FILE,formatting_info=True)

    global totalApprovedSum

    workbook = copy(rb)

    worksheet = workbook.add_sheet("Approved")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(row, 0, 'UserId', boldStyle)

    worksheet.write(row, 1, 'EmailId', boldStyle)

    worksheet.write(row, 2, 'Mobile Number', boldStyle)

    worksheet.write(row, 3, 'Status', boldStyle)

    worksheet.write(row, 4, 'Amount', boldStyle)

    worksheet.write(row, 5, 'Type', boldStyle)

    worksheet.write(row, 6, 'Store', boldStyle)   

    worksheet.write(row, 7, 'Reference Number', boldStyle)

    worksheet.write(row, 8, 'Reference Description', boldStyle)

    worksheet.write(row, 9, 'Approved By', boldStyle)

    worksheet.write(row, 10, 'Created Timestamp', boldStyle)

    worksheet.write(row, 11, 'Updated Timestamp', boldStyle)

    queryFilter = {"$and":

                   [

                    {'created_timestamp':{"$gte":getLastDate()}},

                    {'created_timestamp':{"$lte":getCurrentDate()}},

                    {'status':"Approved"}

                    ]

                   }

    result = get_mongo_connection().Dtr.crmrefundwallet.find(queryFilter)

    for r in result:

        row=row+1

        worksheet.write(row, 0, r.get('user_id'))

        worksheet.write(row, 1, r.get('email_id'))

        worksheet.write(row, 2, r.get('mobile'))

        worksheet.write(row, 3, r.get('status'))

        worksheet.write(row, 4, r.get('amount'))

        totalApprovedSum = totalApprovedSum + r.get('amount')

        worksheet.write(row, 5, r.get('type'))

        worksheet.write(row, 6, r.get('store'))   

        worksheet.write(row, 7, r.get('reference_no'))

        worksheet.write(row, 8, r.get('reference_desc'))

        worksheet.write(row, 9, r.get('approved_by'))

        worksheet.write(row, 10, to_py_date(r.get('created_timestamp')), datetime_format)

        worksheet.write(row, 11, to_py_date(r.get('update_timestamp')) , datetime_format)

    workbook.save(TMP_FILE)

def generateCreditedWalletRefundReport():

    rb = open_workbook(TMP_FILE,formatting_info=True)

    global totalCreditedSum

    workbook = copy(rb)

    worksheet = workbook.add_sheet("Credited")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(row, 0, 'UserId', boldStyle)

    worksheet.write(row, 1, 'EmailId', boldStyle)

    worksheet.write(row, 2, 'Mobile Number', boldStyle)

    worksheet.write(row, 3, 'Status', boldStyle)

    worksheet.write(row, 4, 'Amount', boldStyle)

    worksheet.write(row, 5, 'Type', boldStyle)

    worksheet.write(row, 6, 'Store', boldStyle)   

    worksheet.write(row, 7, 'Reference Number', boldStyle)

    worksheet.write(row, 8, 'Reference Description', boldStyle)

    worksheet.write(row, 9, 'Approved By', boldStyle)

    worksheet.write(row, 10, 'Created Timestamp', boldStyle)

    worksheet.write(row, 11, 'Updated Timestamp', boldStyle)

    queryFilter = {"$and":

                   [

                    {'created_timestamp':{"$gte":getLastDate()}},

                    {'created_timestamp':{"$lte":getCurrentDate()}},

                    {'status':"Credited"}

                    ]

                   }

    result = get_mongo_connection().Dtr.crmrefundwallet.find(queryFilter)

    for r in result:

        row=row+1

        worksheet.write(row, 0, r.get('user_id'))

        worksheet.write(row, 1, r.get('email_id'))

        worksheet.write(row, 2, r.get('mobile'))

        worksheet.write(row, 3, r.get('status'))

        worksheet.write(row, 4, r.get('amount'))

        totalCreditedSum = totalCreditedSum + r.get('amount')

        worksheet.write(row, 5, r.get('type'))

        worksheet.write(row, 6, r.get('store'))   

        worksheet.write(row, 7, r.get('reference_no'))

        worksheet.write(row, 8, r.get('reference_desc'))

        worksheet.write(row, 9, r.get('approved_by'))

        worksheet.write(row, 10, to_py_date(r.get('created_timestamp')), datetime_format)

        worksheet.write(row, 11, to_py_date(r.get('update_timestamp')) , datetime_format)

    workbook.save(TMP_FILE)

def generateCancelledWalletRefundReport():

    rb = open_workbook(TMP_FILE,formatting_info=True)

    global totalCancelledSum

    workbook = copy(rb)

    worksheet = workbook.add_sheet("Cancelled")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(row, 0, 'UserId', boldStyle)

    worksheet.write(row, 1, 'EmailId', boldStyle)

    worksheet.write(row, 2, 'Mobile Number', boldStyle)

    worksheet.write(row, 3, 'Status', boldStyle)

    worksheet.write(row, 4, 'Amount', boldStyle)

    worksheet.write(row, 5, 'Type', boldStyle)

    worksheet.write(row, 6, 'Store', boldStyle)   

    worksheet.write(row, 7, 'Reference Number', boldStyle)

    worksheet.write(row, 8, 'Reference Description', boldStyle)

    worksheet.write(row, 9, 'Approved By', boldStyle)

    worksheet.write(row, 10, 'Created Timestamp', boldStyle)

    worksheet.write(row, 11, 'Updated Timestamp', boldStyle)

    queryFilter = {"$and":

                   [

                    {'created_timestamp':{"$gte":getLastDate()}},

                    {'created_timestamp':{"$lte":getCurrentDate()}},

                    {'status':"Cancelled"}

                    ]

                   }

    result = get_mongo_connection().Dtr.crmrefundwallet.find(queryFilter)

    for r in result:

        row=row+1

        worksheet.write(row, 0, r.get('user_id'))

        worksheet.write(row, 1, r.get('email_id'))

        worksheet.write(row, 2, r.get('mobile'))

        worksheet.write(row, 3, r.get('status'))

        worksheet.write(row, 4, r.get('amount'))

        totalCancelledSum = totalCancelledSum + r.get('amount')

        worksheet.write(row, 5, r.get('type'))

        worksheet.write(row, 6, r.get('store'))   

        worksheet.write(row, 7, r.get('reference_no'))

        worksheet.write(row, 8, r.get('reference_desc'))

        worksheet.write(row, 9, r.get('approved_by'))

        worksheet.write(row, 10, to_py_date(r.get('created_timestamp')), datetime_format)

        worksheet.write(row, 11, to_py_date(r.get('update_timestamp')) , datetime_format)

    workbook.save(TMP_FILE)

def main():

    generatePendingWalletRefundReport()

    generateApprovedWalletRefundReport()

    generateCreditedWalletRefundReport()

    generateCancelledWalletRefundReport()

    #sendmail(["rajender.singh@shop2020.in"], "", TMP_FILE, SUBJECT)

    sendmail(["rajender.singh@shop2020.in","rajneesh.arora@saholic.com", "amit.gupta@shop2020.in", "khushal.bhatia@saholic.com"], "", TMP_FILE, SUBJECT)

if __name__ == '__main__':

    main()