def con_str(): user_id = utility.getUserId(utility.getUserName()) db = database_connection() cursor = db.cursor() string2="" sql_role = 'select role_id from mapping where user_id = %s' %user_id cursor.execute(sql_role) results_role=cursor.fetchall() for row in results_role: role_id = row[0] sql_keyword = 'select keywords,emails,names from mapping where role_id = %s' %role_id cursor.execute(sql_keyword) results_keyword=cursor.fetchall() for row1 in results_keyword: keyword = row1[0] if keyword: string2 = keyword+";"+string2 emails = row1[1] if emails: string2 =emails+";"+string2 names = row1[2] if names: string2 =names+";"+string2 sql_user = '******' %user_id cursor.execute(sql_user) results_user=cursor.fetchall() for rows in results_user: user_name = rows[0] return (string2,user_name)
def getLocal(): user_id = utility.getUserId(utility.getUserName()) db = database_connection() cursor = db.cursor() sql = "select gmail_login,gmail_pwd,login_id from user where user_id='%s'" %user_id cursor.execute(sql) results = cursor.fetchall() for row in results: local = row[2] return local
def getGmailAcct(): user_id = utility.getUserId(utility.getUserName()) db = database_connection() cursor = db.cursor() sql = "select gmail_login,gmail_pwd,login_id from user where user_id='%s'" %user_id #%(getUserName()) #print sql cursor.execute(sql) results = cursor.fetchall() for row in results: username= row[0] password = row[1] local = row[2] #print username return (username,password,local)
def main():
form = cgi.FieldStorage()
if(form.has_key("action")):
customer_id = utility.getUserId(utility.getUserName())
if form["action"].value == "display":
if "keyword" not in form:
keywords= ""
else:
keywords = form["keyword"].value
if "email" not in form:
emails= ""
else:
emails = form["email"].value
if "name" not in form:
names= ""
else:
names = form["name"].value
insert_roles(customer_id,form["role_name"].value,keywords,emails,names)
else:
generate_form(0)
#!c:/Python26/python.exe import cgi import header import cgitb import utility cgitb.enable() from db import database_connection print "Content-type: text/html; charset=iso-8859-1\n\n" customer_id = utility.getUserId(utility.getUserName()) def generate_deleteform(): db = database_connection() cursor = db.cursor() sql_role = "select a.role_id,a.role_name from roles a, mapping b where a.role_id = b.role_id and b.user_id = '%s'"%customer_id cursor.execute(sql_role) results=cursor.fetchall() #print header.printHeader() #print leftNav.printLeftNav() print header.printHeader() print '''<div class="left-side" style="width:745px;" >''' print '''<div class="formbox" align="center">''' print '''<form name="form_delete" action="delete_roles.py"><table border='1' class="formboxcontent"> <tr><td colspan="5"><b>Click on the respective Roles to Delete</b></td></tr> <tr><td colspan='1'> </td><td><b>Role Name</b></td></tr>''' for row in results: role_id = row[0] role_name = row[1]
def insert_data():
user_id = utility.getUserId(utility.getUserName())
#print user_id
#data = [('googlecalendar', '\t\t\tName: [email protected]\n\t\t\temail: [email protected]', '\t\t\t2010-12-05\n\t\t\t15:00:00\n', '\ttest attachment 1\n', '\tthis is a test1', "[u'NULL']", ['ritu', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test']), ('googlecalendar', '\t\t\tName: [email protected]\n\t\t\temail: [email protected]', '\t\t\t2010-12-05\n\t\t\t15:00:00\n', '\ttest event 2\n', '\tthis is a test2', "[u'NULL']", ['deepali', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test']), ('googlecalendar','\t\t\tName: [email protected]\n\t\t\temail: [email protected]', '\t\t\t2010-12-05\n\t\t\t15:00:00\n', '\ttest event\n', '\tthis is a test', "[u'NULL']", ['johann'])]
wordList,local = con_str.con_str()
data = runner.xmlReadandAnalyze(wordList,local)
#print "Data:%s"%data
#[('gmail', 'Deepali Agarwal <*****@*****.**>', '2010-12-05 17:45:09', 'urgent', ' Hi this is an urgent message.\n\n-- \nThanks and Regards!\nDeepali\n', "[u'NULL']", ['urgent'])]
#data = [('Brandeis Now', ' Brandeis Now', ' 2010-12-03 17:06:00', ' Town hall on alcohol and drug policy', ' ', "[u'http://www.brandeis.edu/now/2010/december/townhall-dec6.html']", []), ('Brandeis Now', ' Brandeis Now', ' 2010-11-30 14:28:00', ' Schusterman Center unveils Israeli art collection', " At the entrance to the new home of the Schusterman Center for Israel Studies, on the top level of the Mandel Center for the Humanities, visitors encounter innovative video works by prominent Israeli artists. These works by Sigalit Landau, Yael Bartana, Doron Solomons and Guy Ben-Ner are on display here after having been screened at major museums, among them the Israel Museum in Jerusalem, the Museum of Modern Art in New York, Tate Modern in London, and the Massachusetts Museum of Contemporary Art. They are part of a new collection of contemporary Israeli photography, prints and video art unveiled Nov. 10 at the inauguration of the Schusterman Center's new space. Also on display in the center are several large and powerful photographs of the Israeli landscape, including a nostalgic vista of the Yarkon River by Yossi Breger and a nocturnal image of the ruins of a Palestinian home by Dor Guez.", "[u'http://www.brandeis.edu/now/2010/december/schustermanfeature.html']", []), ('Brandeis Now', ' Brandeis Now', ' 2010-11-29 21:07:00', ' Annual Messiah Sing', ' ', "[u'http://www.brandeis.edu/arts/concerts/concerts.html']", []), ('Brandeis Now', ' Brandeis Now', ' 2010-11-29 17:48:00', " 'Girldrive' author says her cross-country trip shaped her view of feminism", ' In 2007, journalist and cultural critic Nona Willis Aronowitz and photographer Emma Bee Bernstein, both then 22 years old, hit the road for a trip across the United States to discover how their generation of young women related to feminism and ideas about gender justice. Some who they met didn\'t know the word "feminist," but they were "living the legacy" of feminism, she says.', "[u'http://www.brandeis.edu/now/2010/november/girldrive.html']", []), ('Brandeis Now', ' Brandeis Now', ' 2010-11-29 17:33:00', ' Ambassador will speak on South Sudan referendum', ' The self-determination vote gives South Sudan the opportunity to vote for independence from the North. The vote was provided for in the Comprehensive Peace Agreement that ended the Sudanese civil war in 2005. Top officials on both sides have committed to holding the referendum on time and to respecting the outcome. But many in the South and the international community worry that the government in Khartoum, in North Sudan, will not go along if the South votes for independence. ', "[u'http://www.brandeis.edu/now/2010/november/sudanese.html']", []), ('Brandeis Now', ' Brandeis Now', ' 2010-11-29 16:28:00', ' Staff awards surprise recipients', ' ', "[u'http://www.brandeis.edu/now/2010/november/staffawards.html']", [])]
keywords = []
counter = 0
for item in data:
counter +=1
#print "counter"
#print counter
source = item[0]
email_from = item[1]
email_date = item[2]
subject = item[3]
message = item[4]
attachment = item[5]
keywords =item[6]
temp = attachment.split("[")
#print temp
for temp1 in temp:
temp2 = temp1.split("u")
for temp3 in temp2:
temp4 = temp3.split("]")
att = temp4[0]
#print temp4[0]
if source == 'googlecalendar':
parts = email_from.split("Name:")
for value in parts:
email_from1 = value.split("email:")
for some in email_from1:
email_from2= some.split("\t")
email_final = email_from2[0]
if source == 'gmail':
email_final = email_from
if source == 'Brandeis Now':
email_final = "Brandeis Now"
message = "Null"
subject = "Null"
att = "Null"
email_date = '2010-12-06 11:29:00'
keywords1 = list(set(keywords))
count1 =0
count2=0
count3 =0
for child in keywords1:
temp = "%"
child1 = temp+child+temp
sql_role = "SELECT count(role_id) FROM oneportal.mydata where mydata like '%s' and user_id = '%s'" %(child1,user_id)
#print sql_role
cursor.execute(sql_role)
results=cursor.fetchall()
for row in results:
count = row[0]
if count > 1:
count1 = 1
if count == 1:
count2 =1
data = child1
child2 = child1
if ((count2!=1) and (count1==1)):
sql_insert = 'insert into email (role_id,user_id,message,date_email,from_email,datasource,subject,attachment) values ("2","%s","%s","%s","%s","%s","%s","%s")' %(user_id,message ,email_date,email_final,source,subject,att)
#print sql_insert
cursor.execute(sql_insert)
db.commit()
if (count2==1):
sql_fetch = "select role_id from oneportal.mydata where mydata like '%s' and user_id = '%s'" %(data,user_id)
cursor.execute(sql_fetch)
results1=cursor.fetchall()
for row1 in results1:
role_id = row1[0]
sql_insert = 'insert into email (role_id,user_id,message,date_email,from_email,datasource,subject,attachment) values ("%s","%s","%s","%s","%s","%s","%s","%s")' %(role_id,user_id,message ,email_date,email_final,source,subject ,att )
#print sql_insert
cursor.execute(sql_insert)
db.commit()
if ((count2 ==0) and (count1 ==0)):
sql_insert = 'insert into email (role_id,user_id,message,date_email,from_email,datasource,subject,attachment) values ("3","%s","%s","%s","%s","%s","%s","%s")'%(user_id,message,email_date,email_final,source,subject ,att )
#print sql_insert
cursor.execute(sql_insert)
db.commit()
def run():
http_headers()
customer_id = utility.getUserId(utility.getUserName())
#print 'id:%s'%customer_id
print '''
<html>'''
print header.printHeader()
todo = datetime.date.today()
print '''<head>
<script type="text/javascript" src="/others/simpletreemenu.js"></script>
<link rel="stylesheet" type="text/css" href="/others/jquery-ui-1.8.6.custom.css" /></link>
<script type="text/javascript" src="/others/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="/others/jquery-ui-1.8.6.custom.min.js"></script>
<link rel="stylesheet" type="text/css" href="/others/simpletree.css" /></link>
<link rel="stylesheet" type="text/css" href="/others/styles.css" /></link>
</head>
<title>SwiftMail</title>
<body>
<table align='center' width='800' height='500' border='1' class='pagecontent'>
<tr><td colspan="2">
<div style="height: 135px;width: 1000px;;overflow:auto;">
<table class='pagecontent' border='0' width='800' align='center'>
<tr width = "100%"><td colspan='2' width = "100%">'''
print '''<h3 class="ui-widget-header">This is the to-do list for '%s'</h3>'''%todo
print '''</td></tr>'''
print '''<tr><td><b>From</b></td>
<td><b>Subject</b></td></tr>'''
sql_todo = "select message_id,message,date_email,from_email,datasource,subject,attachment from email where user_id = %s and date_email >= current_date()"%customer_id
cursor.execute(sql_todo)
results_todo=cursor.fetchall()
for row in results_todo:
message_id = row[0]
message = row[1]
date_email = row[2]
from_email = row[3]
datasource = row[4]
subject =row[5]
attachment = row[6]
print '<tr><td>%s</td> '%from_email
print '<td><a href=home.py?message_id=%s>%s</a></td></tr>'%(message_id,subject)
print '''</table>
</div></td>
</tr>'''
print '''<tr valign="top">
<td rowspan='2'>
<script>
$(function() {
$( "#resizable2" ).resizable();
});
</script>
<div id="resizable2" class="ui-widget-content">
<a href="javascript:ddtreemenu.flatten('treemenu1', 'expand')">Expand All</a> | <a href="javascript:ddtreemenu.flatten('treemenu1', 'contact')">Contract All</a>
<div style="height:500px;width:206px;" style='table-layout:fixed'>
<ul id="treemenu1" class="treeview">'''
for row_root in results_root:
print '<li>'
role_id = row_root[0]
role_id1 = row_root[0]
role_name= row_root[1]
print '<a href="home.py?role_id=%d">%s</a>'%(role_id,role_name)
print '<ul>'
sql_subroot = "select message_id,subject,from_email,date_email,datasource,attachment,role_id from email where user_id =%s and role_id = %s"%(customer_id,role_id)
cursor.execute(sql_subroot)
results_subroot=cursor.fetchall()
if results_subroot:
for row in results_subroot:
message_id = row[0]
message_id1 = row[0]
subject = row[1]
print '<li>'
print '<a href="home.py?message_id=%d&role_id=%s">%s</a>'%(message_id,role_id,subject)
print '</li>'
else:
message_id1 = 0
print '</ul>'
print '<li>'
print '''</ul></div>
<script type="text/javascript">
ddtreemenu.createTree("treemenu1", false)
</script>
'''
print '''
<script>
$(function() {
$( "#resizable" ).resizable();
});
</script>'''
print '''</div>
</td>
<td>
<div id="resizable" class="ui-widget-content"; style="overflow:auto;">
<h3 class="ui-widget-header">Inbox</h3>
<table align="left" border='1' style='table-layout:fixed' class='pagecontent' width='100%'>
<tr><td><b>From</b></td><td><b>Subject</b></td><td><b>Date</b></td><td><b>DataSource</b></td><td><b>Attachment</b></td></tr>
'''
form = cgi.FieldStorage()
try:
role_id=form["role_id"].value
except:
#print "Select a appropriate folder"
role_id = role_id1
#print "role_id:%s"%role_id
sql_subroot = "select message_id,subject,from_email,date_email,datasource,attachment,role_id from email where user_id ='%s' and role_id = '%s'"%(customer_id,role_id)
#print sql_subroot
cursor.execute(sql_subroot)
results_subroot=cursor.fetchall()
for row in results_subroot:
message_id = row[0]
subject = row[1]
from_email = row[2]
date_email = row[3]
datasource = row[4]
attachment = row[5]
role_id = row[6]
print '<tr><td>%s</td>'%from_email
print ' <td>'
print '<a href="home.py?message_id=%d&role_id=%s">%s</a>'%(message_id,role_id,subject)
print '</td>'
print '<td>%s</td>'%date_email
print '<td>%s</td>'%datasource
print '<td>%s</td></tr>'%attachment
print '<form><input type="hidden" name="role_id" value="%s"></form>'%role_id
print'''</table>
</div>
</td>
</tr>
<tr>
<td valign='top'>
<script>
$(function() {
$( "#resizable1" ).resizable();
});
</script>
<div id="resizable1" class="ui-widget-content">
<h3 class="ui-widget-header">Full Email</h3>'''
print '''<table border='0' style='table-layout:fixed' class='pagecontent' width='100%'>'''
form = cgi.FieldStorage()
try:
message_id=form["message_id"].value
except:
message_id = message_id1
sql_message = 'select message,from_email,subject from email where message_id = %s'%(message_id)
cursor.execute(sql_message)
results_message=cursor.fetchall()
for row in results_message:
print '''<tr><td>'''
from_email = row[1]
print 'From : %s'%from_email
print '''</td></tr>'''
print '''<tr><td>'''
subject = row[2]
print 'Subject : %s'%subject
print '''</td></tr>'''
print '''<tr><td>'''
print ' '
print '''</td></tr>'''
print '''<tr><td>'''
message = row[0]
print '%s'%message
print '''</td></tr>'''
print '''</table></div>
