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