Subversion Repositories SmartDukaan

'''

Created on 01-Dec-2015

@author: manas

'''

import MySQLdb

import xlwt

from datetime import date

import smtplib

from email import encoders

from email.mime.base import MIMEBase

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

DB_HOST = "localhost"

DB_USER = "root"

DB_PASSWORD = "shop2020"

DB_NAME = "dtr"

TMP_FILE = "/tmp/Approved_App_Transactions.xls"  

con = None

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "Approved App Transactions Related Data "

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587  

APPROVED_TRANSACTIONS="""

select approved.transaction_id, approved.transaction_time, approved.app_id,

offers.affiliate_offer_id, offers.app_name,  approved.offer_price, approved.final_user_payout 

from approved_app_transactions approved join app_offers offers 

on approved.app_id=offers.id where retailer_id >0 

and date(approved.transaction_time) > %s and 

date(approved.transaction_time)  <= %s 

order by date(approved.transaction_time)"""

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 getDbConnection():

    return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)

def generateApprovedTransactionReport():

    conn = getDbConnection()

    cursor = conn.cursor()

    cursor.execute(APPROVED_TRANSACTIONS,('2015-08-01','2015-11-30'))

    result = cursor.fetchall()

    global workbook

    workbook = xlwt.Workbook()

    worksheet = workbook.add_sheet("User")

    boldStyle = xlwt.XFStyle()

    f = xlwt.Font()

    f.bold = True

    boldStyle.font = f

    column = 0

    row = 0

    worksheet.write(0,0,'Transaction Id')

    worksheet.write(0,1,'Transaction Time Stamp')

    worksheet.write(0,2,'App Id')

    worksheet.write(0,3,'App Offer Id')

    worksheet.write(0,4,'App Name')

    worksheet.write(0,5,'Offer Price')

    worksheet.write(0,6,'User Payout Amount')

    for r in result:

        row += 1

        column = 0

        for data in r :

            if column==1:

                worksheet.write(row, column, str(data))

            else:

                worksheet.write(row, column, data)

            column += 1

    workbook.save(TMP_FILE)

def sendmail(email, message, fileName, title):

    if email == "":

        return

    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)

    mailServer.ehlo()

    mailServer.starttls()

    mailServer.ehlo()

    msg = MIMEMultipart()

    msg['Subject'] = title

    msg.preamble = title

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

    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','khushal.bhatia@saholic.com']

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

    mailServer.login(SENDER, PASSWORD)

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

def main():

    generateApprovedTransactionReport()

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

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

if __name__ == '__main__':

        main()