Rev 17152 | Rev 17154 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
'''Created on 24-Sep-2015@author: manish'''import _mysqlfrom datetime import date, datetime, timedeltafrom email.mime.multipart import MIMEMultipartfrom email.mime.text import MIMETextfrom pymongo import MongoClientimport smtplibimport sysimport xlwtimport MySQLdb as mdbfrom dtr.main import Store, sourceMapfrom dtr.storage import Mysqlfrom dtr.utils import utilsimport 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 = 587client = 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 skuDatadef sendmail(email, message, title, *varargs):if email == "":returnmailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)mailServer.ehlo()mailServer.starttls()mailServer.ehlo()# Create the container (outer) email message.msg = MIMEMultipart()msg['Subject'] = titlemsg.preamble = titlehtml_msg = MIMEText(message, 'html')msg.attach(html_msg)# email.append('amit.gupta@shop2020.in')MAILTO = emailmailServer.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.Dtrdb1 = client.Catalogcur.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 owleft join users u on u.id = ow.user_idleft 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_idleft join (select * from (select * from all_user_addresses order by source) s group by user_id)aua on aua.user_id=u.idwhere (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_idvalues = (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_ordersfrom 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 = 1alldata =''for data in rows:row +=1if 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 MTDcur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_ordersfrom allorder where created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day andcreated_on < CURDATE() group by brand order by count(brand) desc limit 10''')rows = cur.fetchall()row += 2alldata =''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[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_brandexcept _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()