Subversion Repositories SmartDukaan

'''

Created on 24-Sep-2015

@author: manish

'''

from datetime import date, datetime, timedelta

from dtr.main import sourceMap

from dtr.storage import Mysql

from dtr.utils import utils

from email.mime.multipart import MIMEMultipart

from email.mime.text import MIMEText

from pymongo import MongoClient

import MySQLdb as mdb

import _mysql

import smtplib

import sys

import re

import traceback

# 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('127.0.0.1','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 created >= %s and created < %s and 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 %s or u.utm_campaign is not null) and u.activated = 1 and ow.status in ('ORDER_CREATED','DETAIL_CREATED') and ow.created >= %s and ow.created < %s; 

                ''', start_date, end_date, 'emp%', start_date, end_date, )

        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,quantity, reconciled, affstatus) 

                    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, %s, %s, %s)

                """        

        for result in results:

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

            if morder is not None:

                reconciled=morder.get("reconciled")

                subOrders = morder.get("subOrders")

                if subOrders is not None:

                    for  subOrder in subOrders:

                        try:

                            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]

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

                            quantity=int(subOrder.get("quantity"))

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

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

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

                            else:

                                skuData = db1.MasterData.find_one({'identifier':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'

                            affStatus = subOrder.get('affStatus')

                            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]

                            user_name = re.sub(r'[^\x00-\x7F]+',' ', user_name)

                            product_title = re.sub(r'[^\x00-\x7F]+',' ', product_title)

                            print user_id, user_name, product_title

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

                            cur.execute(query,values)     

                            con.commit()

                        except:

                            print traceback.print_exc()

                            continue

    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 b.store_id, a.amount, a.quantity, a.orders, b.mtdamount,b.mtdquantity, b.mtdorders from   

            (select ifnull(store_id, 'total') as 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 and created_on < CURDATE() group by store_id with rollup) b 

            left join  

            (select ifnull(store_id, 'total') as store_id, sum(amount_paid) amount, count(*) quantity,  count(distinct order_id) orders  from allorder 

                where date(created_on)=curdate()-interval 1 day group by store_id with rollup) a   

            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] if data[2] else "-",data[3] if data[3] else "-",data[4] if data[4] else "-",

                                            data[5] if data[5] else "-",data[6] if data[6] else "-",data[7] if data[7] else "-"))

        # 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(2017,7,1))

    #summaryByBrandAndStore()

    pass