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']
    #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"], "", TMP_FILE, SUBJECT)
    
if __name__ == '__main__':
    main()