import pyExcelerator, os, time, string, calendar, datetime import smtplib, cgiutils, xldate, DateTime, EmailRecips def ExcelToList(fname, worksheet=0, encoding='cp1251'): data = pyExcelerator.parse_xls(fname,encoding) sheet_name=data[worksheet][0].encode(encoding) values = data[worksheet][1] for each in sorted(data[0][1].items()): print str(each)[1:-1] vdict = {} row_idx_max = 0 col_idx_max = 0 for row_idx, col_idx in sorted(values.keys()): row_idx_max = max(row_idx,row_idx_max) col_idx_max = max(col_idx,col_idx_max) v = values[(row_idx, col_idx)] if isinstance(v, unicode): v = v.encode(encoding, 'backslashreplace') vdict[(row_idx,col_idx)] = v vlist = [] for row in range(row_idx_max+1): vlist.append([]) for col in range(col_idx_max+1): if (row,col) not in vdict: vdict[(row,col)]=None vlist[row].append(vdict[(row,col)]) return vlist def GetWellStatus(inSpudDt, inPrjTdDt): if len(inSpudDt) > 0 and len(inPrjTdDt) > 0: today1 = time.strftime('%m/%d/%Y',time.localtime()) today = (datetime.date(int(today1.split('/')[2]), int(today1.split('/')[0]), int(today1.split('/')[1]))) spudDt = (datetime.date(inSpudDt[0], inSpudDt[1],inSpudDt[2])) prjTdDt = datetime.date(inPrjTdDt[0],inPrjTdDt[1], inPrjTdDt[2]) if today > prjTdDt: status = 'Drilled' elif today >= spudDt and today <= prjTdDt: status = 'Drilling' elif today < spudDt: status = 'Proposed' else: status = 'Unknown' else: status = 'Undeterminable' return status def KillObject(toBeKilled): if os.path.exists(toBeKilled): os.remove(toBeKilled) def WriteOutput(file, text): writer = open(file, 'a') writer.write(text) def BuildLiveMapsUrl(wells, ds): urlBase = 'http://maps.live.com/default.aspx?style=r' wholeList = [] dict = {} for well in wells: eachList = [] name = (well[0].replace(' ','%20')).replace('#','%23') spudDateTuple = xldate.xldate_as_tuple(well[1],0) spudDate = str(spudDateTuple[0])+'/'+str(spudDateTuple[1])+'/'+str(spudDateTuple[2]) tdDateTuple = xldate.xldate_as_tuple(well[2],0) tdDate = str(tdDateTuple[0])+'/'+str(tdDateTuple[1])+'/'+str(tdDateTuple[2]) lat = well[3] long = well[4] rig = well[5].replace(' ','%20') status = GetWellStatus(spudDateTuple, tdDateTuple) if status == 'Drilling': eachList.append('an.' + str(lat) + '_' + str(long) + '_' + name + '_' + spudDate + '%20-%20' + rig + '%20-%20' + status.upper) else: eachList.append('an.' + str(lat) + '_' + str(long) + '_' + name + '_' + spudDate + '%20-%20' + rig) eachList.append(spudDate) wholeList.append(eachList) wholeList.sort(lambda x,y:cmp(x[1],y[1])) for whole in wholeList: print whole urlList = [] for each in wholeList: del each[1] wellString = str(each)[2:len(each)-3] urlList.append(wellString) urlStringWells = string.join(urlList,'~') masterUrl = urlBase + '&sp=' + urlStringWells + '~an.36.1164211465469_-94.143074863021_SEECO% 20Fayetteville%20Office~an.35.513123_-93.834189_SWN%20Ozark%20Office~an.35.0685432008022_ -92.4189197874021_SWN%20Conway%20Office~an.' f = 'C:/temp/DS_Html_Email.html' t = 'C:/temp/DS_Text_Email.txt' dsUpdated = str(time.strftime('%A, %m/%d/%Y, %I:%M %p', time.localtime(os.path.getmtime(ds)))) KillObject(f) KillObject(t) WriteOutput(f,'\n\n\n\n\n') WriteOutput(f,'\n\n\n') WriteOutput(f,'\n\n\n')\ WriteOutput(f,'\n\n\n') # empty row WriteOutput(f,'\n\n\n') WriteOutput(f,'\n\n\n') # empty row WriteOutput(f,'\n\n\n\n\n\n') WriteOutput(f,'\n

AAT Drill Schedule Mapper for '\ + time.strftime('%A, %m/%d/%Y',time.localtime()) + '

Drill schedule last updated: ' + dsUpdated + '

Click here for a map of the latest\ AAT drill schedule

*** THIS MESSAGE AUTO-GENERATED ***

\ *** PLEASE DO NOT REPLY TO THIS EMAIL ***

\n\n') WriteOutput(t,'AAT Drill Schedule Mapper for ' + str(time.strftime('%A, %m/%d/%Y'\ ,time.localtime()))) WriteOutput(t,'\n\nDrill schedule last updated: ' + dsUpdated) WriteOutput(t,'\n\nCopy and paste this address into your web broswer:\n\n' + masterUrl) WriteOutput(t,'\n\n** This message auto-generated - please do not respond to this email ***') def SendEmail(message, recipients): sender = 'AAT_Drill_Schedule_Mapper' server = smtplib.SMTP(ip_address) server.sendmail(sender, recipients, message) server.quit() if __name__ == '__main__': wells = ExcelToList('C:/temp/DS.xls') BuildLiveMapsUrl(wells, 'C:/temp/DS.xls') # Build our email, using both HTML and plain-text inputs fhtml = open('C:/temp/DS_Html_Email.html','r') ftxt = open('C:/temp/DS_Text_Email.txt','r') html = fhtml.read() text = ftxt.read() subject = 'AAT Drill Schedule Mapper - ' + str((DateTime.now() + DateTime.\ RelativeDateTime(days=+1)).strftime('%A, %m/%d/%Y')) # Create the message with both HTML and text formats message = cgiutils.createhtmlmail(subject, html, text) # Send the email out recips = ['chad_cooper@swn.com'] # for testing #recips = Dictionaries.aatEmails SendEmail(message,recips)