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

        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.dump

        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 = utils.statusMap.get(subOrder.get("db"))

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

        book = xlwt.Workbook()

        sheet1 = book.add_sheet("data_by_brand")

        sheet2 = book.add_sheet("data_by_store")

        sheet1.write(0,0,"Brand")

        sheet1.write(0,1,"Amount")

        sheet1.write(0,2,"Quantity")

        sheet1.write(0,3,"No. of order")

        sheet2.write(0,0,"Store")

        sheet2.write(0,1,"Amount")

        sheet2.write(0,2,"Quantity")

        sheet2.write(0,3,"No. of order")

        sheet2.write(0,4,"MTD Amount")

        sheet2.write(0,5,"MTD Quantity")

        sheet2.write(0,6,"MTD No. of order")

            # ----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 group by brand order by count(brand) desc limit 10;")

        rows = cur.fetchall()

        row = 1

        alldata =''

        for data in rows:

            sheet1.write(row,0,data[0])

            sheet1.write(row,1,data[1])

            sheet1.write(row,2,data[2])

            sheet1.write(row,3,data[3])

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

        print alldata

        cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where brand not in("+alldata+");")

        row_other = cur.fetchall()

        print row_other

        for data in row_other:

            sheet1.write(row,0,'Other')

            sheet1.write(row,1,data[0])

            sheet1.write(row,2,data[1])

            sheet1.write(row,3,data[2])

            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 month(`created_on`) = 4 group by brand order by count(brand) desc limit 10;")

        rows = cur.fetchall()

        row += 2

        alldata =''

        sheet1.write(row,0,"Brands")

        sheet1.write(row,1,"MTD Amount")

        sheet1.write(row,2,"MTD Quantity")

        sheet1.write(row,3,"MTD No. of order")

        row +=1

        for data in rows:

            sheet1.write(row,0,data[0])

            sheet1.write(row,1,data[1])

            sheet1.write(row,2,data[2])

            sheet1.write(row,3,data[3])

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

        print alldata

        cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where month(`created_on`) = 4 AND brand not in("+alldata+");")

        mtd_row_other = cur.fetchall()

        print row_other

        for data in mtd_row_other:

            sheet1.write(row,0,'Other')

            sheet1.write(row,1,data[0])

            sheet1.write(row,2,data[1])

            sheet1.write(row,3,data[2])

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

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

#         sendmail(["naman.kumar@shop2020.in"] ,message_by_brand, "Summary by brand","")

        # 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 x.*,a.store_id mtd_store,sum(a.amount_paid) mtd_amount,count(a.store_id) mtd_quantity, count(distinct a.order_id) mtd_num_of_order from allorder a left join (select store_id store,sum(amount_paid) amount,count(store_id) quantity,count(distinct order_id) num_of_orders from allorder where month(created_on) = 4 group by store_id ) as x on a.store_id=x.store  group by a.store_id order by store_id ASC;")

        rows = cur.fetchall()

        new_row = 1

        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:

            print data[0]

            sheet2.write(new_row,0,data[4])

            sheet2.write(new_row,1,data[1])

            sheet2.write(new_row,2,data[2])

            sheet2.write(new_row,3,data[3])

            sheet2.write(new_row,4,data[5])

            sheet2.write(new_row,5,data[6])

            sheet2.write(new_row,6,data[7])

            new_row +=1

            tbody.append(rowtemplate.format(data[4],data[1],data[2],data[3],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">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>

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