Rev 19893 | Blame | Compare with Previous | Last modification | View Log | RSS feed
'''Created on 24-Sep-2015@author: manish'''from datetime import date, datetime, timedeltafrom dtr.main import sourceMapfrom dtr.storage import Mysqlfrom dtr.utils import utilsfrom email.mime.multipart import MIMEMultipartfrom email.mime.text import MIMETextfrom pymongo import MongoClientimport MySQLdb as mdbimport _mysqlimport smtplibimport sysimport reimport traceback# 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('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 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 created >= %s and created < %s and 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 %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_titlevalues = (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()continueexcept _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 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 allorderwhere created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day and created_on < CURDATE() group by store_id with rollup) bleft join(select ifnull(store_id, 'total') as store_id, sum(amount_paid) amount, count(*) quantity, count(distinct order_id) orders from allorderwhere date(created_on)=curdate()-interval 1 day group by store_id with rollup) aon 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_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(2017,7,1))#summaryByBrandAndStore()pass