Subversion Repositories SmartDukaan

'''

Created on 24-Sep-2015

@author: manish

'''

import _mysql

from datetime import date, datetime, timedelta

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from pymongo import MongoClient

import smtplib

import sys

import xlwt

import MySQLdb as mdb

from dtr.main import Store, sourceMap

from dtr.storage import Mysql

from dtr.utils import utils

import dtr.utils

# DataService.initialize()

# client = MongoClient('mongodb://localhost:27017/')

SENDER = "cnc.center@shop2020.in"

PASSWORD = "5h0p2o2o"

SUBJECT = "Summary"

SMTP_SERVER = "smtp.gmail.com"

SMTP_PORT = 587

client = MongoClient()

con = mdb.connect('localhost','root','shop2020','dtr')

def getSkuData(storeId, identifier):

    if storeId in (1,2,4,5,6,7):

        skuData = client.Catalog.MasterData.find_one({'identifier':identifier, 'source_id':storeId})

    elif storeId == 3:

        skuData = client.Catalog.MasterData.find_one({'secondaryIdentifier':identifier, 'source_id':storeId})

    return skuData

def sendmail(email, message, title, *varargs):

    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

    html_msg = MIMEText(message, 'html')

    msg.attach(html_msg)

#     email.append('amit.gupta@shop2020.in')

    MAILTO = email 

    mailServer.login(SENDER, PASSWORD)

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

def addToAllOrders(start_date, end_date=None):

    if not end_date:

        end_date = datetime.now()

    else:

        end_date = end_date + timedelta(days=1) 

    try:

        cur = con.cursor()            

        db=client.Dtr

        db1 = client.Catalog

        cur.execute("delete from allorder where created_on >= %s and created_on < %s", (start_date, end_date))

        con.commit()

        results = Mysql.fetchResult('''

                select ow.*, u.username, crm1.order_count, u.referrer, aua.city,aua.state,aua.pincode from order_view ow 

                left join users u on u.id = ow.user_id 

                left join (select user_id, count(*) as order_count from order_view where status in ('ORDER_CREATED','DETAIL_CREATED')  group by user_id)as crm1 on ow.user_id = crm1.user_id

                left join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.id 

                where (lower(u.referrer) not like 'emp%' or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED'); 

                ''')

        query = """INSERT INTO allorder 

                    (user_id, user_name, order_id, created_on, store_id, merchant_order_id, status, detailed_status, product_title, referrer, amount_paid, catalog_id, brand, model,category, deal_rank, max_nlc, min_nlc, dp, item_status, city, state, pincode, merchant_suborder_id,cashback_status,cashback_amount) 

                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)

                """        

        for result in results:

            morder = db.merchantOrder.find_one({"orderId":result[0]})

            if morder is not None:

                subOrders = morder.get("subOrders")

                if subOrders is not None:

                    for  subOrder in subOrders:

                        user_id = result[1]

                        user_name= result[7]

                        order_id =  result[0]

                        created_on =result[6]

                        store_id = sourceMap.get(result[2])

                        merchant_order_id = morder.get("merchantOrderId")

                        status = subOrder.get("status")

                        detailed_status = subOrder.get("detailedStatus")

                        product_title= subOrder.get("productTitle")

                        referrer =  result[9]

                        if int(subOrder.get("quantity")) > 0:

                            amount_paid = int(subOrder.get("amountPaid"))/int(subOrder.get("quantity"))

                        skuData = getSkuData(morder.get("storeId"), subOrder.get("productCode"))

                        if morder.get("storeId") in (1,2,4,5):

                            skuData = db1.MasterData.find_one({'identifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})

                        elif morder.get("storeId") == 3:

                            skuData = db1.MasterData.find_one({'secondaryIdentifier':subOrder.get("productCode").strip(), 'source_id':morder.get("storeId")})

                        if skuData is not None:

                            catalog_id = skuData.get("skuBundleId")

                            brand= skuData.get("brand")

                            model = skuData.get("model_name")

                            category = skuData.get("category")

                            deal_rank = subOrder.get("dealRank")

                            max_nlc = subOrder.get("maxNlc")

                            min_nlc = subOrder.get("minNlc")

                            dp = subOrder.get("db")

                            item_status = utils.statusMap.get(subOrder.get("itemStatus"))

                        else:

                            catalog_id = 'None'

                            brand= 'None'

                            model = 'None'

                            category = 'None'

                            deal_rank = 'None'

                            max_nlc = 'None'

                            min_nlc = 'None'

                            dp = 'None'

                            item_status = 'None'

                        merchant_suborder_id = subOrder.get("merchantSubOrderId")

                        cashback_status = subOrder.get("cashBackStatus")

                        cashback_amount = subOrder.get("cashBackAmount")

                        city = result[10]

                        state = result[11]

                        pincode = result[12]

                        print user_id

                        values = (user_id, user_name, order_id, created_on, store_id, merchant_order_id, status, detailed_status, product_title, referrer, amount_paid, catalog_id, brand, model,category, deal_rank, max_nlc, min_nlc, dp, item_status, city, state, pincode,merchant_suborder_id,cashback_status,cashback_amount)

                        cur.execute(query,values)     

                        con.commit()

    except _mysql.Error, e:

        print "Error %d: %s" % (e.args[0], e.args[1])

        sys.exit(1)

    finally:

        if con:

            con.close()

def summaryByBrandAndStore():

    con = mdb.connect('localhost','root','shop2020','dtr')

    try:

        cur = con.cursor()            

        # ----Data by brand---

        tbody = []

        rowtemplate_brand="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>"

        mailBodyTemplate_brand="""

                <html>

                    <body>

                    <table cellspacing="0" border="1" style="text-align:right">

                        <thead>

                            <tr>

                                <th colspan='4' style="text-align:center">Summary by Brand</th>

                            </tr>

                            <tr>

                                <th style="text-align:center">Brand</th>

                                <th  style="text-align:center">Amount</th>

                                <th  style="text-align:center">Quantity</th>

                                <th  style="text-align:center">Number of Order</th>

                            </tr>

                        </thead>

                        <tbody>

                            {0}

                        </tbody>

                    </table><br><br>

                    </body>

                </html>

            """

        cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders  

                    from allorder where created_on >= CURDATE() -  interval 1 day and created_on < CURDATE() group by brand order by count(brand) desc limit 10

                    ''')

        rows = cur.fetchall()

        row = 1

        alldata =''

        for data in rows:

            row +=1

            if len(alldata)>0:

                alldata = alldata + ",'"+ data[0]+"'"

            else:

                alldata = "'"+data[0]+"'"

            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))

        cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where created_on >= CURDATE() -  interval 1 day and created_on < CURDATE() and brand not in("+alldata+")")

        row_other = cur.fetchall()

        for data in row_other:

            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))

        tbody.append(rowtemplate_brand.format('','','',''))

        tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))

        # Brand by MTD

        cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders  

                    from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day and 

                                        created_on < CURDATE() group by brand order by count(brand) desc limit 10''')

        rows = cur.fetchall()

        row += 2

        alldata =''

        for data in rows:

            if len(alldata)>0:

                alldata = alldata + ",'"+ data[0]+"'"

            else:

                alldata = "'"+data[0]+"'"

            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))

        cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day and created_on < CURDATE() AND brand not in("+alldata+")")

        mtd_row_other = cur.fetchall()

        for data in mtd_row_other:

            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))

        message_by_brand = mailBodyTemplate_brand.format("".join(tbody))

        # Data by Store

        # cur.execute("select store_id store,sum(amount_paid) amount,count(store_id) quantity ,count(distinct order_id) from allorder group by store_id order by store_id ASC;")

        cur.execute("select name, c.* from store left join (select a.*, b.mtdamount,b.mtdquantity, b.mtdorders from  (select store_id, sum(amount_paid), count(*),  count(distinct order_id)  from allorder where date(created_on)=curdate()-interval 1 day group by store_id) a join (select store_id, sum(amount_paid) mtdamount, count(*) mtdquantity,  count(distinct order_id) mtdorders  from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day group by store_id) b on a.store_id=b.store_id) as c on name = c.store_id")

        rows = cur.fetchall()

        tbody =[]

        rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td></tr>"

        for data in rows:

            tbody.append(rowtemplate.format(data[0],data[2],data[3],data[4],data[5],data[6],data[7]))

        # book.save("/home/manish/Desktop/Summary.xls")

        mailBodyTemplate="""

                <html>

                    <body>

                    <table cellspacing="0" border="1" style="text-align:right">

                        <thead>

                            <tr>

                                <th colspan='7' style="text-align:center">Summary by Store</th>

                            </tr>

                            <tr>

                                <th style="text-align:center">Store</th>

                                <th  style="text-align:center">Amount</th>

                                <th  style="text-align:center">Quantity</th>

                                <th  style="text-align:center">Number of Order</th>

                                <th  style="text-align:center">MTD Amount</th>

                                <th  style="text-align:center">MTD Quantity</th>

                                <th  style="text-align:center">MTD Number of Order</th>

                            </tr>

                        </thead>

                        <tbody>

                            {0}

                        </tbody>

                    </table><br><br>

                    </body>

                </html>

            """

        message = mailBodyTemplate.format("".join(tbody))

        return message+message_by_brand

    except _mysql.Error, e:

        print "Error %d: %s" % (e.args[0], e.args[1])

        sys.exit(1)

    finally:

        if con:

            con.close()               

if __name__ == '__main__':

    addToAllOrders(date(2015,1,1))

    #summaryByBrandAndStore()