def get(self):
student_id = self.request.get('student_id')
student_id = int(student_id)
regiscourse_id = self.request.get('regiscourse_id')
regiscourse_id = int(regiscourse_id)
section_id = self.request.get('section_id')
section_id = int(section_id)
enroll = self.request.get('enroll')
enroll = int(enroll)
enroll = enroll - 1
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "DELETE from registeredcourse where student_id='%d' and regiscourse_id='%d'" % (
student_id, regiscourse_id)
cursor.execute(sql)
conn.commit()
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "UPDATE section set enroll='%d' where section_id='%d'" % (
enroll, section_id)
cursor2.execute(sql2)
conn2.commit()
conn.close()
conn2.close()
self.redirect('/DeleteCalendar?student_id=' + str(student_id) +
'®iscourse_id=' + str(regiscourse_id) +
'&course_code=' + str(course_code) + '§ion_id=' +
str(section_id))
def get(self):
course_id = self.request.get('course_id')
section_id = self.request.get('section_id')
section_id = int(section_id)
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "DELETE FROM section WHERE section_id='%d'" % (section_id)
cursor.execute(sql)
conn.commit()
conn.close()
utc = pytz.utc
date_object = datetime.today()
utc_dt = utc.localize(date_object)
bkk_tz = timezone("Asia/Bangkok")
bkk_dt = bkk_tz.normalize(utc_dt.astimezone(bkk_tz))
time_insert = bkk_dt.strftime("%H:%M:%S")
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "INSERT INTO log (staff_id,course_id,day,time,type)\
VALUES(3,(SELECT course_id FROM course WHERE course_code = '%s'),CURDATE(),'%s',6)" % (
course_id, time_insert)
cursor2.execute(sql2)
conn2.commit()
conn2.close()
self.redirect("/ModifyCourse?course_id=" + course_id)
def get(self):
student_id = self.request.get('student_id')
student_id = int(student_id)
regiscourse_id = self.request.get('regiscourse_id')
regiscourse_id = int(regiscourse_id)
section_id = self.request.get('section_id')
section_id = int(section_id)
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "DELETE FROM followcourse WHERE student_id='%d' AND regiscourse_id='%d'" % (
student_id, regiscourse_id)
cursor.execute(sql)
conn.commit()
conn.close()
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "INSERT INTO followcourse (student_id,regiscourse_id,section_id) VALUES ('%d','%d','%d')" % (
student_id, regiscourse_id, section_id)
cursor2.execute(sql2)
conn2.commit()
conn2.close()
self.redirect('/?student_id=' + str(student_id))
def get(self):
course_id=self.request.get('course_id')
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="DELETE FROM regiscourse WHERE course_id=(SELECT course_id FROM course WHERE course_code='%s')"%(course_id)
cursor.execute(sql);
conn.commit();
conn.close();
conn3 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor3 = conn3.cursor()
sql3="DELETE FROM course WHERE course_code='%s'"%(course_id)
cursor3.execute(sql3);
conn3.commit();
conn3.close();
utc = pytz.utc
date_object = datetime.today()
utc_dt = utc.localize(date_object);
bkk_tz = timezone("Asia/Bangkok");
bkk_dt = bkk_tz.normalize(utc_dt.astimezone(bkk_tz))
time_insert = bkk_dt.strftime("%H:%M:%S")
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="INSERT INTO log (staff_id,course_id,day,time,type)\
VALUES(3,(SELECT course_id FROM course WHERE course_code = '%s'),CURDATE(),'%s',7)"%(course_id,time_insert)
cursor2.execute(sql2)
conn2.commit()
conn2.close();
self.redirect("http://prinya-ailada.appspot.com/");
def get(self):
student_id = self.request.get('student_id')
student_id = int(student_id)
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT department_name from department"
cursor.execute(sql)
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "SELECT title from faculity"
cursor2.execute(sql2)
templates = {
'student_id': student_id,
'department': cursor.fetchall(),
'faculity': cursor2.fetchall(),
}
template = JINJA_ENVIRONMENT.get_template('course_active.html')
self.response.write(template.render(templates))
def get(self):
# student_id = self.request.get('student_id');
student_id = "2"
student_id = int(student_id)
price = 0
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT course_code,credit_lecture+(credit_lab/3) AS credit,price \
FROM registeredcourse reg JOIN course cou \
ON reg.regiscourse_id=cou.course_id WHERE student_id='%d'" % (student_id)
cursor.execute(sql)
# cursor.fetchall():
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "SELECT SUM(price) FROM registeredcourse reg JOIN course cou \
ON reg.regiscourse_id=cou.course_id WHERE student_id='%d'" % (student_id)
cursor2.execute(sql)
for row in cursor2.fetchall():
price = row[0]
conn.commit()
conn.close()
conn2.commit()
conn2.close()
self.redirect('/?student_id=' + str(student_id))
def get(self):
course_id=self.request.get('course_id')
sectime_id=self.request.get('sectime_id')
sectime_id=int(sectime_id)
section_id=self.request.get('section_id')
section_id=int(section_id)
section_number=self.request.get('section_number');
section_number=int(section_number)
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="DELETE FROM section_time WHERE sectime_id='%d'"%(sectime_id)
cursor.execute(sql);
conn.commit();
conn.close();
utc = pytz.utc
date_object = datetime.today()
utc_dt = utc.localize(date_object);
bkk_tz = timezone("Asia/Bangkok");
bkk_dt = bkk_tz.normalize(utc_dt.astimezone(bkk_tz))
time_insert = bkk_dt.strftime("%H:%M:%S")
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="INSERT INTO log (staff_id,course_id,day,time,type)\
VALUES(3,(SELECT course_id FROM course WHERE course_code = '%s'),CURDATE(),'%s',7)"%(course_id,time_insert)
cursor2.execute(sql2)
conn2.commit()
conn2.close();
self.redirect("/ModifySection?course_id="+course_id+"§ion_id="+str(section_id)+"§ion_number="+str(section_number));
def get(self):
course_id = self.request.get('course_code');
# course_id = "BIS-101"
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="SELECT * FROM course WHERE course_code = '%s'"%(course_id)
cursor.execute(sql);
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="SELECT co.course_code FROM course co,prerequsite_course pre\
WHERE prerequsite_id=co.course_id AND pre.course_id=\
(SELECT course_id FROM course WHERE course_code='%s')"%(course_id)
cursor2.execute(sql2);
pre_code=""
for row in cursor2.fetchall():
pre_code=row[0]
templates = {
'course' : cursor.fetchall(),
'prerequisite_code' : pre_code,
}
get_template = JINJA_ENVIRONMENT.get_template('course_detail.html')
self.response.write(get_template.render(templates));
conn.close();
conn2.close();
def get(self):
course_id = self.request.get('course_code')
# course_id = "BIS-101"
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT * FROM course WHERE course_code = '%s'" % (course_id)
cursor.execute(sql)
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "SELECT co.course_code FROM course co,prerequsite_course pre\
WHERE prerequsite_id=co.course_id AND pre.course_id=\
(SELECT course_id FROM course WHERE course_code='%s')" % (
course_id)
cursor2.execute(sql2)
pre_code = ""
for row in cursor2.fetchall():
pre_code = row[0]
templates = {
'course': cursor.fetchall(),
'prerequisite_code': pre_code,
}
get_template = JINJA_ENVIRONMENT.get_template('course_detail.html')
self.response.write(get_template.render(templates))
conn.close()
conn2.close()
def get(self):
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
cursor.execute(
'SELECT course_id,course_code,course_name,credit_lecture,credit_lab,credit_learning,status,regiscourse_id FROM course natural join regiscourse'
)
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
cursor2.execute(
'SELECT sum(capacity),sum(enroll),regiscourse_id FROM section group by regiscourse_id'
)
# conn3=rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
# cursor3 = conn3.cursor()
# cursor3.execute('SELECT course_id,status FROM regiscourse')
templates = {
'course': cursor.fetchall(),
'enroll': cursor2.fetchall(),
# 'status' : cursor3.fetchall(),
}
template = JINJA_ENVIRONMENT.get_template('course.html')
self.response.write(template.render(templates))
def get(self):
course_id = self.request.get('course_id')
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT * FROM course WHERE course_code = '%s'" % (course_id)
cursor.execute(sql)
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "SELECT course_id,course_code from course where course_code not like '%s'" % (
course_id)
cursor2.execute(sql2)
conn3 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor3 = conn3.cursor()
sql3 = "SELECT section_id,section_number,UPPER(CONCAT(CONCAT(firstname,' '),lastname)),enroll,capacity\
FROM section sec JOIN staff st ON teacher_id=staff_id\
WHERE regiscourse_id=(SELECT regiscourse_id FROM regiscourse WHERE course_id=\
(SELECT course_id from course where course_code='%s')) ORDER BY section_number" % (
course_id)
cursor3.execute(sql3)
conn4 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor4 = conn4.cursor()
sql4 = "SELECT co.course_id,co.course_code FROM course co,prerequsite_course pre\
WHERE prerequsite_id=co.course_id AND pre.course_id=\
(SELECT course_id FROM course WHERE course_code='%s')" % (
course_id)
# sql4="SELECT prerequisite , CASE prerequisite WHEN '0' THEN '- NONE - ' \
# ELSE (SELECT course_code FROM course WHERE course_id=\
# (SELECT prerequisite FROM course WHERE course_code='%s'))\
# END FROM course WHERE course_code='%s'"%(course_id,course_id)
cursor4.execute(sql4)
pre_id = ""
pre_code = ""
for row in cursor4.fetchall():
pre_id = row[0]
pre_code = row[1]
templates = {
'course': cursor.fetchall(),
'course2': cursor2.fetchall(),
'course3': cursor3.fetchall(),
'course_id': course_id,
'prerequisite_id': pre_id,
'prerequisite_code': pre_code,
}
get_template = JINJA_ENVIRONMENT.get_template('course_modify.html')
self.response.write(get_template.render(templates))
conn.close()
conn2.close()
conn3.close()
conn4.close()
def get(self):
student_id = self.request.get('student_id')
student_id = int(student_id)
enroll = self.request.get('enroll')
section_id = self.request.get('section_id')
regiscourse_id = self.request.get('regiscourse_id')
sec_id = self.request.get('sec_id')
section_id = int(section_id)
regiscourse_id = int(regiscourse_id)
sec_id = int(sec_id)
enroll = int(enroll)
enroll = enroll + 1
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "UPDATE registeredcourse set section_id ='%d' WHERE student_id = '%d' and regiscourse_id='%d'" % (
section_id, student_id, regiscourse_id)
cursor2.execute(sql2)
conn2.commit()
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "UPDATE section set enroll='%d' WHERE section_id = '%d' " % (
enroll, section_id)
cursor.execute(sql)
conn.commit()
conn3 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor3 = conn3.cursor()
sql3 = "SELECT enroll from section where section_id ='%d'" % (sec_id)
cursor3.execute(sql3)
conn3.commit()
roll = ""
for row in cursor3.fetchall():
roll = row[0]
roll = int(roll)
roll = roll - 1
self.response.write(roll)
conn4 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor4 = conn4.cursor()
sql4 = "UPDATE section set enroll='%d' WHERE section_id = '%d'" % (
roll, sec_id)
cursor4.execute(sql4)
conn4.commit()
conn.close()
conn2.close()
conn3.close()
conn4.close()
self.redirect('/SendMail?student_id=' + str(student_id) +
'®iscourse_id=' + str(regiscourse_id))
def get(self):
course_code = self.request.get('course_code');
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="SELECT round(sum(credit_lecture+(credit_lab/3)),'0')\
from registeredcourse rec join course cou \
ON cou.course_id=rec.regiscourse_id \
join student stu \
on stu.student_id=rec.student_id where rec.student_id='2'"
cursor.execute(sql);
credit=0
for row in cursor.fetchall():
credit=row[0]
conn4 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor4 = conn4.cursor()
sql4='SELECT maxcredit_per_semester from faculity f join student s on f.faculity_id=s.faculity_id where student_id=2'
cursor4.execute(sql4)
credit_total=0
for row2 in cursor4.fetchall():
credit_total=row2[0]
enroll = self.request.get('enroll');
enroll=int(enroll)
capacity = self.request.get('capacity');
capacity=int(capacity)
regiscourse_id = self.request.get('regiscourse_id');
regiscourse_id=int(regiscourse_id)
section_id = self.request.get('section_id');
section_id=int(section_id)
if credit>=credit_total:
self.redirect('/ErrorCredit')
elif enroll>=capacity:
self.redirect('/Error')
else :
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="INSERT INTO registeredcourse(student_id,regiscourse_id,section_id) values(2,'%d','%d')"%(regiscourse_id,section_id)
cursor2.execute(sql2);
conn2.commit()
enroll=enroll+1
conn3 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor3 = conn3.cursor()
sql3="UPDATE section SET enroll='%d' WHERE section_id='%d' and regiscourse_id='%d'"%(enroll,section_id,regiscourse_id)
cursor3.execute(sql3);
conn3.commit()
conn2.close()
conn3.close()
self.redirect('http://crs-ice.appspot.com?course_code='+course_code)
conn.close()
def __init__(self, instance=google_cloud_instance, database=google_cloud_database, user=local_mysql_user, password=local_mysql_password):
self.logger = logging.getLogger(__name__)
try:
self.conn = rdbms.connect(instance=google_cloud_instance, database=google_cloud_database)
except rdbms.OperationalError:
try:
self.logger.error("Trying local MySQL db")
self.conn = rdbms.connect(instance=local_mysql_instance, database=local_mysql_database,user=local_mysql_user,password=local_mysql_password)
except:
self.logger.error("Either not on Google AppEngine or local MySQL db not set-up")
def get_connection():
if os.environ['SERVER_SOFTWARE'].startswith('Development/'):
return rdbms.connect(instance=CLOUDSQL_INSTANCE,
database=DATABASE_NAME)
else:
return rdbms.connect(instance=CLOUDSQL_INSTANCE,
database=DATABASE_NAME,
user=USER_NAME,
password=PASSWORD,
charset='utf8')
def post(self):
Core.login(self)
session = get_current_session()
http = decorator.http()
course_id=self.request.get('course_id')
course_name=self.request.get('course_name')
section_number=self.request.get('section_number')
section_number=int(section_number)
teacher=self.request.get('teacher')
capacity=self.request.get('capacity')
capacity=int(capacity)
display_name = "prinya-course-" + course_id + "-" + str(section_number)
params = {
'displayName' : display_name
}
result = service_plus.circles().insert(userId="me",body=params).execute(http=http)
circle_url = "https://plus.google.com/u/0/circles/" + display_name + "-p" + result['id']
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="INSERT INTO section (regiscourse_id,section_number,teacher_id,capacity,enroll, circle_id, circle_url) \
VALUES ((SELECT regiscourse_id FROM regiscourse WHERE course_id=(SELECT course_id FROM course where course_code = '%s')),'%d',\
(SELECT staff_id FROM staff WHERE type=2 AND firstname='%s' AND university_id='%s'),'%d','0', '%s', '%s')"%(course_id,section_number,teacher,session['university_id'],capacity, str(result['id']), circle_url)
cursor.execute(sql);
conn.commit();
conn.close();
params = {
'email': "prinya-course-" + course_id + "-" + str(section_number) + session['domain'],
'name' : course_id + "-" + str(section_number)
}
result = service.groups().insert(body=params).execute(http=http)
utc = pytz.utc
date_object = datetime.today()
utc_dt = utc.localize(date_object);
bkk_tz = timezone("Asia/Bangkok");
bkk_dt = bkk_tz.normalize(utc_dt.astimezone(bkk_tz))
time_insert = bkk_dt.strftime("%H:%M:%S")
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="INSERT INTO log (staff_id,course_code,day,time,type, university_id)\
VALUES((select staff_id from staff where type=2 AND email='%s' AND university_id='%s'),'%s',CURDATE(),'%s',2, '%d')"%(session['email'], session['university_id'],course_id,time_insert, session['university_id'])
cursor2.execute(sql2)
conn2.commit()
conn2.close();
#self.redirect("/ModifyCourse?course_id="+course_id)
self.redirect("/CreateSecSites?course_id="+course_id+"§ion_number="+str(section_number)+"&course_name="+course_name+"&teacher="+teacher)
def get(self):
Core.login(self)
session = get_current_session()
course_id = self.request.get('course_code');
capacity=""
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="SELECT * FROM course WHERE course_code = '%s'"%(course_id)
cursor.execute(sql);
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="SELECT co.course_code FROM course co,prerequisite_course pre\
WHERE prerequisite_id=co.course_id AND pre.course_id=\
(SELECT course_id FROM course WHERE course_code='%s')"%(course_id)
cursor2.execute(sql2);
pre_code=""
for row in cursor2.fetchall():
pre_code=row[0]
conn3 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor3 = conn3.cursor()
sql3="SELECT sum(capacity) FROM section se JOIN regiscourse re\
ON se.regiscourse_id=re.regiscourse_id\
join course co\
ON co.course_id=re.course_id\
WHERE course_code='%s'"%(course_id)
cursor3.execute(sql3);
for capa in cursor3.fetchall():
if capa[0]!="":
capacity=capa[0]
else:
capacity=0
conn3.close();
templates = {
'email' : session['email'],
'domain' : session['domain'],
'course' : cursor.fetchall(),
'capacity' : capacity,
'prerequisite_code' : pre_code,
}
get_template = JINJA_ENVIRONMENT.get_template('course_detail.html')
self.response.write(get_template.render(templates));
conn.close();
conn2.close();
def post(self):
if self.request.get("toDo") == "delete_candidate":
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Veebivalimised')
cursor = conn.cursor()
cursor.execute("""
DELETE FROM
kandidaat
WHERE
isik_ID = %s
""", (self.current_user['id']))
conn.commit()
conn.close()
self.notify_all()
if self.request.get("toDo") == "delete_vote":
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Veebivalimised')
cursor = conn.cursor()
cursor.execute("""
DELETE FROM
vote
WHERE
isik_ID = %s
""", (self.current_user['id']))
conn.commit()
conn.close()
self.notify_all()
if self.request.get("toDo") == "set_candidate":
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Veebivalimised')
cursor = conn.cursor()
cursor.execute("""
INSERT INTO
kandidaat (partei_ID, piirkond_ID, isik_ID)
VALUES
(%s, %s, %s)
""" % (self.request.get("Party"), self.request.get("Area"), self.current_user['id']))
conn.commit()
conn.close()
self.notify_all()
if self.request.get("toDo") == "make_vote":
conn=rdbms.connect(instance=_INSTANCE_NAME, database='Veebivalimised')
cursor = conn.cursor()
cursor.execute("""
INSERT INTO
vote (kandidaat_ID, isik_ID)
VALUES
(%s, %s)
""" % (self.request.get("selected_candidate"), self.current_user['id']))
conn.commit()
conn.close()
self.notify_all()
def get(self):
course_id = self.request.get('course_id');
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql="SELECT * FROM course WHERE course_code = '%s'"%(course_id)
cursor.execute(sql);
conn2 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor2 = conn2.cursor()
sql2="SELECT course_id,course_code from course where course_code not like '%s'"%(course_id)
cursor2.execute(sql2);
conn3 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor3 = conn3.cursor()
sql3="SELECT section_id,section_number,UPPER(CONCAT(CONCAT(firstname,' '),lastname)),enroll,capacity\
FROM section sec JOIN staff st ON teacher_id=staff_id\
WHERE regiscourse_id=(SELECT regiscourse_id FROM regiscourse WHERE course_id=\
(SELECT course_id from course where course_code='%s')) ORDER BY section_number"%(course_id)
cursor3.execute(sql3);
conn4 = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor4 = conn4.cursor()
sql4="SELECT co.course_id,co.course_code FROM course co,prerequsite_course pre\
WHERE prerequsite_id=co.course_id AND pre.course_id=\
(SELECT course_id FROM course WHERE course_code='%s')"%(course_id)
# sql4="SELECT prerequisite , CASE prerequisite WHEN '0' THEN '- NONE - ' \
# ELSE (SELECT course_code FROM course WHERE course_id=\
# (SELECT prerequisite FROM course WHERE course_code='%s'))\
# END FROM course WHERE course_code='%s'"%(course_id,course_id)
cursor4.execute(sql4);
pre_id=""
pre_code=""
for row in cursor4.fetchall():
pre_id=row[0]
pre_code=row[1]
templates = {
'course' : cursor.fetchall(),
'course2' : cursor2.fetchall(),
'course3' : cursor3.fetchall(),
'course_id' : course_id,
'prerequisite_id' : pre_id,
'prerequisite_code' : pre_code,
}
get_template = JINJA_ENVIRONMENT.get_template('course_modify.html')
self.response.write(get_template.render(templates));
conn.close();
conn2.close();
conn3.close();
conn4.close();
def post(self):
Core.login(self)
session = get_current_session()
site_sec_username = self.request.get('site_sec_username')
site_sec_password = self.request.get('site_sec_password')
data_code = self.request.get('course_id')
section_number = self.request.get('section_number')
course_name = self.request.get('course_name')
teacher_name = self.request.get('teacher_name')
try:
teach_conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
teach_cursor = teach_conn.cursor()
teach_cursor.execute("SELECT email FROM staff WHERE firstname = '%s'"%(teacher_name))
teach_fetch = teach_cursor.fetchall()
teacher_email = teach_fetch[0]
site_name = course_name+"-1-2556"
client = gdata.sites.client.SitesClient(source=SOURCE_APP_NAME, site = site_name)
client.ClientLogin(site_sec_username, site_sec_password, client.source)
client.domain = session['domain'].replace("@","")
site_sec_name = course_name + "-sec" + section_number + "-1-2556"
entry = client.CreateSite(site_sec_name, description='Testing of Prinya course site', theme='slate')
site_sec_url = entry.GetAlternateLink().href
"""
scope = gdata.acl.data.AclScope(value=teacher_email, type='user')
role = gdata.acl.data.AclRole(value='writer')
acl = gdata.sites.data.AclEntry(scope=scope, role=role)
acl_entry = client.Post(acl, client.MakeAclFeedUri())
"""
site_sec_conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
site_sec_cursor = site_sec_conn.cursor()
site_sec_cursor.execute("UPDATE section SET site_url = '%s' WHERE regiscourse_id = \
(SELECT regiscourse_id FROM regiscourse WHERE course_id = \
(SELECT course_id FROM course WHERE course_code = '%s')) AND section_number = '%d'"%(site_sec_url, data_code, int(section_number)))
site_sec_conn.commit()
self.redirect("/ModifyCourse?course_id="+data_code+"&course_name="+course_name)
except DeadlineExceededError:
self.response.clear()
self.response.headers['Location'] = response.geturl()
self.response.set_status(302)
def get(self):
user = users.get_current_user()
if user is None:
login_url = users.create_login_url(self.request.path)
self.redirect(login_url)
return
else:
student_id = self.request.get('student_id')
student_id = int(student_id)
regiscourse_id = self.request.get('regiscourse_id')
regiscourse_id = int(regiscourse_id)
email = ""
course_code = ""
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT email,course_code FROM followcourse fwc JOIN student stu\
ON fwc.student_id=stu.student_id\
JOIN course cou\
ON fwc.regiscourse_id=cou.course_id WHERE regiscourse_id='%d'\
ORDER BY follow_id" % (regiscourse_id)
cursor.execute(sql)
for row in cursor.fetchall():
email = row[0]
course_code = row[1]
break
if email != "":
user = users.get_current_user()
message = mail.EmailMessage()
message.sender = user.email()
message.to = email
message.subject = "Subject " + course_code + " is avaliable NOW!!"
message.body = "You can enroll this course nowwww"
message.send()
self.redirect('/?student_id=' + str(student_id))
def get(self):
#Check for mobile browser, send them to the Play Store
#user_agent = self.request.headers['User-Agent']
#url_to_go = "market://details?id=org.rpi.rpinfo"
#b = reg_b.search(user_agent)
#v = reg_v.search(user_agent[0:4])
#if b or v:
# self.redirect(url_to_go)
#Check MemCache for number of people in website
memcache_key = "number_people"
cached_mem = memcache.get(memcache_key)
if cached_mem is not None:
number_people = cached_mem
else:
#If not, we query Cloud SQL
conn = rdbms.connect(instance=_INSTANCE_NAME, database='rpidirectory')
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM rpidirectory")
number_people = cursor.fetchall()[0][0]
#Commify number
r = []
for i, c in enumerate(str(number_people)[::-1]):
if i and (not (i % 3)):
r.insert(0, ',')
r.insert(0, c)
number_people = ''.join(r)
#Store it in MemCache
memcache.add(memcache_key, number_people, 43200)
template_values = {"number_people": number_people}
path = os.path.join(os.path.dirname(__file__), 'index.html')
self.response.out.write(template.render(path, template_values))
def get(self):
# Get all articles
conn = rdbms.connect(instance=INSTANCE_NAME, database=DATABASE_NAME)
cursor = conn.cursor()
SQL_GET_ARTICLES = 'SELECT * FROM articles a JOIN sources s WHERE a.source_id = s.id'
cursor.execute(SQL_GET_ARTICLES)
print cursor.rowcount
if cursor.rowcount == -1:
# There are no article.
return None
count = 0
recommended = []
for article in cursor.fetchall():
print article
# Run prediction to see if this use is interested in article
article_obj = {
'id': article[0],
'title': article[1],
'link': article[2],
'body': article[3],
'source_id': article[4],
'date': article[5],
'source_name': article[7]
}
# If we can't predict, simply serve.
article_obj['score'] = 0
recommended.append(article_obj)
count = count+1
print count
json_resp = json.dumps(recommended)
self.response.out.write(json_resp)
def post(self):
group_name = self.request.get('group_name')
faculity = self.request.get('faculity2')
department = self.request.get('department2')
flat_rate = self.request.get('flat_rate')
price_per_credit = self.request.get('price_per_credit')
tuition = self.request.get('tuition')
if tuition == "flat_rate":
tuition = flat_rate
else:
tuition = price_per_credit
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
cursor.execute(
"""insert into creditprice (faculity,department,creditprice.group,price)
values (%s,%s,%s,%s)""",
(faculity, department, group_name, tuition))
self.response.write(group_name)
self.response.write(faculity)
self.response.write(department)
self.response.write(flat_rate)
self.response.write(price_per_credit)
self.response.write(tuition)
conn.close()
def zemi_domejni(stranici):
"""Ги зима домејните од Марнет. stranici треба да е ажурна
листа на страници од главната страница на Марнет."""
#ako deneska sum sobral ne probuvaj pak
if gae:
conn = rdbms.connect(instance=_INSTANCE_NAME, database='domaininfo')
else:
pass
#conn = sqlite3.connect("db/domaininfo.sqlite3") #GAEquirk
c = conn.cursor()
c.execute('select count(*) from domaininfo where date=%s',(deneska,))
if c.fetchone()[0]<>0:
return []
domejni = []
for link in stranici:
logging.info(u"Sobiram %s" % link)
req = urllib2.Request(u'http://dns.marnet.net.mk/' + link)
res = urllib2.urlopen(req)
strana = res.read()
soup = BeautifulSoup(strana)
domejn_linkovi = soup.findChildren('a',{'class':'do'})
for domejn in domejn_linkovi:
if domejn['href'].find('dom=')<>-1:
domejni.append(domejn['href'].replace('registar.php?dom=',''))
return domejni
def __enter__(self):
self.conn = rdbms.connect(instance=settings.CLOUDSQL_INSTANCE,
database=settings.DATABASE_NAME,
user=settings.USER_NAME,
password=settings.PASSWORD,
charset='utf8')
return self.conn
def saveProfileData(self,post_data): conn = rdbms.connect(instance=_INSTANCE_NAME, database=dbname, user=usr, passwd=pss, charset='utf8') cursor = conn.cursor() sqlcmd = "update user set f_name = %s , l_name = %s , primary_phone = %s, address = %s, line_1 = %s , city = %s ,state = %s, country = %s where user_id = %s " cursor.execute(sqlcmd,(post_data['f_name'],post_data['l_name'],post_data['primary_phone'],post_data['address'],post_data['line_1'],post_data['city'],post_data['state'],post_data['country'],post_data['user_id'],)) conn.commit() conn.close()
def get(self):
# Get all articles
conn = rdbms.connect(instance=INSTANCE_NAME, database=DATABASE_NAME)
cursor = conn.cursor()
SQL_GET_ARTICLES = 'SELECT * FROM articles a JOIN sources s WHERE a.source_id = s.id'
cursor.execute(SQL_GET_ARTICLES)
print cursor.rowcount
if cursor.rowcount == -1:
# There are no article.
return None
count = 0
recommended = []
for article in cursor.fetchall():
print article
# Run prediction to see if this use is interested in article
article_obj = {
'id': article[0],
'title': article[1],
'link': article[2],
'body': article[3],
'source_id': article[4],
'date': article[5],
'source_name': article[7]
}
# If we can't predict, simply serve.
article_obj['score'] = 0
recommended.append(article_obj)
count = count + 1
print count
json_resp = json.dumps(recommended)
self.response.out.write(json_resp)
def getProjectId(self, user_id): conn = rdbms.connect(instance=_INSTANCE_NAME, database=dbname, user=usr, passwd=pss) cursor = conn.cursor() sqlcmd = "SELECT project_id FROM project_user where user_id = %s" # print sqlcmd cursor.execute(sqlcmd,(user_id,)) return cursor.fetchall()[0][0]
def post(self):
group_name = self.request.get('group_name');
faculity = self.request.get('faculity2');
department = self.request.get('department2');
flat_rate = self.request.get('flat_rate');
price_per_credit = self.request.get('price_per_credit');
tuition = self.request.get('tuition');
if tuition=="flat_rate":
tuition = flat_rate
else:
tuition = price_per_credit
# self.response.write(group_name)
# self.response.write(faculity)
# self.response.write(department)
# self.response.write(flat_rate)
# self.response.write(price_per_credit)
# self.response.write(tuition)
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
cursor.execute("""INSERT into creditprice (faculity,department,creditprice.group,price)
values (%s,%s,%s,%s)""",(faculity,department,group_name,tuition))
conn.close();
def delDeSuMap(self, de_id, su_id): conn = rdbms.connect(instance= _INSTANCE_NAME, database= dbname, user=usr, passwd= pss) cursor = conn.cursor() sqlcmd = "delete from de_surveyor where de_id = %s and surveyor_id = %s" cursor.execute(sqlcmd,(de_id,su_id)) conn.commit() conn.close()
def checkLogin():
session = get_current_session()
con = rdbms.connect(instance=INSTANCE_NAME, database=DATABASE)
cursor = con.cursor()
sql = "select * from User where email='%s'" % (
users.get_current_user().email().lower())
cursor.execute(sql)
row = cursor.fetchall()
if not row:
file_result = insert_file(service, "Easy Quiz", "Test Create Folder",
"", MIME_FOLDER, "Easy Quiz")
sql = "insert into User (email, name, drive_url) values ('%s', '%s', '%s')" % (
users.get_current_user().email().lower(),
users.get_current_user().nickname(), file_result['alternateLink'])
cursor.execute(sql)
con.commit()
#result = insert_file(self, service, "Easy Quiz", "EQ Description", "", MIME_FOLDER, "")
#file = service.files().insert(body=body).execute(http=decorator.http)
#self.response.write("Self Link: " + result['selfLink'])
#self.response.write("WebContent Link: " + result['webContentLink'])
#self.response.write("WebView Link: " + result['webViewLink'])
#self.response.write("Alternate Link: " + result['alternateLink'])
#else:
#self.response.write("Registered")
sql = "select id from User where email='%s' limit 1" % (
users.get_current_user().email().lower())
cursor.execute(sql)
session['user_id'] = cursor.fetchall()[0][0]
def post(self):
checkLogin()
session = get_current_session()
con = rdbms.connect(instance=INSTANCE_NAME, database=DATABASE)
cursor = con.cursor()
val = self.request.get('ans')
data = json.loads(val)
correct = 0
incorrect = 0
quiz_id = -1
for ans in data:
ans['question'] = ans['question'].replace('question', '')
sql = "select q.quiz_id, a.id, a.is_true from Question q, Answer a where q.id='%s' and a.question_id='%s' and a.id='%s'" % (
ans['question'], ans['question'], ans['ans'])
cursor.execute(sql)
row = cursor.fetchall()[0]
sql = "insert into AnsDB (quiz_id, question_id, ans_id, user_id, istrue) values ('%s', '%s', '%s', '%s', '%s')" % (
row[0], ans['question'], row[1], str(
session['user_id']), row[2])
quiz_id = row[0]
cursor.execute(sql)
con.commit()
if row[2] == True:
correct += 1
else:
incorrect += 1
self.response.write("y=" + str(correct) + "&n=" + str(incorrect) +
"&id=" + str(quiz_id))
def post(self):
checkLogin()
# zid = self.request.get('id')
# title = self.request.get('title')
# description = self.request.get('description')
# start_date = self.request.get('start')
# end_date = self.request.get('end')
# location = self.request.get('location')
data = json.loads(self.request.get('data'))
zid = data['id']
title = data['title']
description = data['description']
start_date = data['startdate']
end_date = data['enddate']
location = data['location']
con = rdbms.connect(instance=INSTANCE_NAME, database=DATABASE)
cursor = con.cursor()
sql = "update Quiz set title='%s', description='%s', start='%s', end='%s', address='%s' where id='%s'" % (
title, description, start_date, end_date, location, zid)
cursor.execute(sql)
con.commit()
con.close()
result = {'status': 'OK', 'id': str(zid)}
self.response.write(json.dumps(result))
def changePwd(self,u_id,pwd): conn = rdbms.connect(instance=_INSTANCE_NAME, database=dbname, user=usr, passwd=pss, charset='utf8') cursor = conn.cursor() sqlcmd = "update user set login_password = %s where user_id = %s" cursor.execute(sqlcmd,(pwd,u_id,)) conn.commit() conn.close()
def getCorrectionsSur(self, surveyor_id, last_index):
conn = rdbms.connect(instance = _INSTANCE_NAME, database= dbname, user= usr, passwd= pss)
cursor = conn.cursor()
sqlcmd = """SELECT * FROM survey_data
where SUBSTR(part_id,length(survey_id)+1,length(%s))=%s and timestamp>%s
and status_flag=1"""
cursor.execute(sqlcmd,(surveyor_id,surveyor_id,last_index))
rows = cursor.fetchall()
surveys=[]
# final_rows = {}
for row in rows:
final_rows = {}
final_rows['counter']=row[13]
final_rows['ID']=row[0]
final_rows['survey_id']=row[1]
final_rows['part_id']=row[2]
final_rows['sect_id']=row[3]
final_rows['ques_no']=row[4]
final_rows['op_value']=row[5]
final_rows['ans']=row[6]
final_rows['view_type']=row[7]
final_rows['language']=row[10]
final_rows['created_at']=str(row[8])
surveys.append(final_rows)
conn.close()
return surveys
def Query(self, query):
"""Issues a query to CloudSQL.
Args:
query (string): The query to be issued.
Returns:
(dict) Dictionary of results, split among 'fields' which describe
the columns of the result and 'data' which contains rows of result
data.
"""
# Issue the query.
conn = rdbms.connect(instance=self._instance, database=self._database)
cursor = conn.cursor()
cursor.execute(query)
# Parse the response data into a more convenient dict, with members
# 'fields' for row names and 'data' for row data.
if cursor.description is None: # Probably not a SELECT.
result = None
else:
result = { 'fields': tuple(d[0] for d in cursor.description),
'data': cursor.fetchall() }
conn.commit()
conn.close()
return result
def post(self):
session = get_current_session()
key = self.request.get('keyword')
department = self.request.get('department')
faculty = self.request.get('faculty')
status = self.request.get('status').lower()
if ("active".startswith(status) or status == "active"):
status = "1"
elif ("disable".startswith(status) or status == "disable"):
status = "0"
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT course_id,course_code,course_name,credit_lecture,credit_lab,credit_learning,status,regiscourse_id, department, faculty FROM course c natural join regiscourse rc WHERE c.university_id='" + str(session['university_id']) + "' AND (c.course_code LIKE '%" + key + "%' OR c.course_name LIKE '%" + key + "%') AND c.department LIKE '%" + department + "%' AND c.faculty LIKE '%" + faculty + "%' AND rc.status='" + status + "'"
cursor.execute(sql)
course = cursor.fetchall()
templates = {
'course' : course,
}
conn.close()
template = JINJA_ENVIRONMENT.get_template('course_ajax.html')
self.response.write(template.render(templates))
def get(self):
conn = rdbms.connect(instance=_INSTANCE_NAME, database=_DATABASE_NAME)
#total number of users
cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM ' + _TABLE_NAME)
total_cnt = 0
for row in cursor.fetchall():
total_cnt = row
self.response.out.write(
'<blockquote>%d users are stored</blockquote>' % total_cnt)
#number of female/male users
cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM ' + _TABLE_NAME +
' WHERE gender="f"')
female_cnt = 0
for row in cursor.fetchall():
female_cnt = row
self.response.out.write(
'<blockquote>%d users are female</blockquote>' % female_cnt)
cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM ' + _TABLE_NAME +
' WHERE gender="m"')
male_cnt = 0
for row in cursor.fetchall():
male_cnt = row
self.response.out.write(
'<blockquote>%d users are male</blockquote>' % male_cnt)
conn.close()
def get(self):
value = self.request.get('course_id')
value = int(value)
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql1 = "SELECT status FROM regiscourse WHERE course_id= '%d'" % value
cursor.execute(sql1)
result = cursor.fetchall()
for row in result:
if row[0] == 1:
sql2 = "UPDATE regiscourse set status=0 where course_id='%d'" % value
cursor.execute(sql2)
else:
sql3 = "UPDATE regiscourse set status=1 where course_id='%d'" % value
cursor.execute(sql3)
conn.commit()
conn.close()
self.redirect("/")
def getUnassSurveyor(self, pm_id):
# To return a directory of userId : userName(First Name + Last Name) of all Unassigned Surveyors for a given Project Manager's Id.
conn = rdbms.connect(instance=_INSTANCE_NAME, database=dbname, user=usr, passwd=pss)
cursor = conn.cursor()
sqlcmd = """select pu.user_id, CONCAT(u.f_name, ' ', u.l_name) from project_user pu, user u
where pu.user_id = u.user_id
and pu.project_id = (select pu1.project_id from project_user pu1 where pu1.user_id = '%s')
and pu.user_id like 'SU%%'
and pu.user_id not in (select ds.surveyor_id
from de_surveyor ds
where ds.p_id = (select pu1.project_id
from project_user pu1
where pu1.user_id = '%s'))""" %(pm_id,pm_id)
# print sqlcmd
cursor.execute(sqlcmd)
unass_surveyors_list = []
unass_surveyors_dict = {} # Dictionary to hold User Id and Name Mapping
for row in cursor.fetchall():
unass_surveyors_dict['name'] = row[1]
unass_surveyors_dict['ID'] = row[0]
unass_surveyors_dict_temp = {}
for k in unass_surveyors_dict.keys():
unass_surveyors_dict_temp[k] = unass_surveyors_dict[k]
unass_surveyors_list.append(unass_surveyors_dict_temp)
return unass_surveyors_list
def get(self):
self.response.headers['Content-Type'] = 'text/plain'
# Get Weather Underground data
wunderground_json_file = urllib2.urlopen('http://api.wunderground.com/api/'+WUNDERGROUND_API_KEY+'/geolookup/conditions/q/'+ZIP_CODE+'.json')
json_string = wunderground_json_file.read()
parsed_json = json.loads(json_string)
condition = parsed_json['current_observation']['weather']
temp_f = str(parsed_json['current_observation']['temp_f'])
feelslike_f = str(parsed_json['current_observation']['feelslike_f'])
relative_humidity = str(parsed_json['current_observation']['relative_humidity'])
wind_mph = str(parsed_json['current_observation']['wind_mph'])
wind_dir = parsed_json['current_observation']['wind_dir']
pressure_mb = str(parsed_json['current_observation']['pressure_mb'])
uv = str(parsed_json['current_observation']['UV'])
solarradiation = str(parsed_json['current_observation']['solarradiation'])
precip_1hr_in = str(parsed_json['current_observation']['precip_1hr_in'])
precip_today_in = str(parsed_json['current_observation']['precip_today_in'])
time_stamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
conn = rdbms.connect(instance=CLOUDSQL_INSTANCE, database=DATABASE_NAME)
cursor = conn.cursor()
cursor.execute("INSERT INTO grapher_weatherdata VALUES (null, '" + time_stamp + "', '" + condition + "', " + temp_f + ", " + feelslike_f + ", '" + relative_humidity[:-1] + "', " + wind_mph + ", '" + wind_dir + "', " + pressure_mb + ", " + uv + ", " + solarradiation + ", " + precip_1hr_in + ", " + precip_today_in + ")")
conn.commit()
conn.close()
self.response.write('Weather updated at ' + time_stamp)
def post(self):
pay_type = self.request.get('rate')
faculty = self.request.get('faculty')
department = self.request.get('department')
price = self.request.get('price')
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
if pay_type == "spec":
sql = "update university set payment_type=1"
cursor.execute(sql)
conn.commit();
elif pay_type == "flat":
sql = "update university set payment_type=2"
cursor.execute(sql)
conn.commit();
sql = "update department set fee='%s' where department_name='%s' and faculty_id='%s'"%(price, department, faculty)
self.response.write(sql)
cursor.execute(sql)
conn.commit()
conn.close()
self.redirect('/')
def deletePMmap(self, pm_id): conn = rdbms.connect(instance= _INSTANCE_NAME, database= dbname, user=usr, passwd= pss) cursor = conn.cursor() sqlcmd = "delete from project_user where user_id = %s" cursor.execute(sqlcmd,(pm_id,)) conn.commit() conn.close()
def addProjectUser(self, project_id, user_id, role): conn = rdbms.connect(instance= _INSTANCE_NAME, database= dbname, user=usr, passwd= pss) cursor = conn.cursor() sqlcmd = "insert into project_user values (%s,%s,%s)" cursor.execute(sqlcmd,(project_id, user_id, role)) conn.commit() conn.close()
def getDeSurveys(self,user_id):
conn = rdbms.connect(instance=_INSTANCE_NAME, database=dbname, user=usr, passwd=pss, charset='utf8')
cursor = conn.cursor()
sqlcmd = """SELECT sda.survey_id, sde.survey_name, CONCAT(u.f_name,' ',u.l_name) as surveyor_name, sda.part_id, u.company, CONCAT(u.city,',',u.state) as location, sda.timestamp, ds.de_id from survey_data sda,de_surveyor ds, survey_details sde,user u where
ds.surveyor_id=SUBSTR(sda.part_id,length(sda.survey_id)+1,length(ds.surveyor_id))
and sde.survey_id=sda.survey_id
and u.user_id=SUBSTR(sda.part_id,length(sda.survey_id)+1,length(ds.surveyor_id))
and ds.de_id= %s
and sda.status_flag=0
group by sda.part_id
"""
cursor.execute(sqlcmd,(user_id,))
info = []
user=[]
for row in cursor.fetchall():
info={
'ID': row[0],
'name': row[1],
'surveyorName': row[2],
'pID': row[3],
'company': row[4],
'location': row[5],
'date': str(row[6]),
'deID': row[7]
}
user.append(info)
# print user
conn.close()
return user
def getProfile(self,user_id):
conn = rdbms.connect(instance=_INSTANCE_NAME, database=dbname, user=usr, passwd=pss, charset='utf8')
cursor = conn.cursor()
sqlcmd = """select user_id as ID, f_name as fname, l_name as lname, email, primary_phone as phone, secondary_phone as altphone,
r.role_desc as accounttype, address, line_1 as street, city, state, country, DOB
from user left join role r on r.role_id=user.role where user_id= %s"""
cursor.execute(sqlcmd,(user_id,))
user=[]
for row in cursor.fetchall():
user={
'ID': row[0],
'fName': row[1],
'lName': row[2],
'Email': row[3],
'Phone': row[4],
'altphone': row[5],
'accountType': row[6],
'HouseNumber': row[7],
'Street': row[8],
'City': row[9],
'State': row[10],
'Country': row[11],
'DOB': row[12]
}
conn.close()
return user
def get(self):
Core.login(self)
session = get_current_session()
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
cursor.execute("select * from course")
course = cursor.fetchall()
cursor.execute("SELECT * FROM faculty WHERE university_id=" + str(session['university_id']))
faculty = cursor.fetchall()
cursor.execute("select payment_type from university where university_id=%d"%(session['university_id']))
pay_type = cursor.fetchall()[0][0]
templates = {
'email' : session['email'],
'course' : course,
'pay_type' : pay_type,
'faculty' : faculty,
}
get_template = JINJA_ENVIRONMENT.get_template('course_create.html')
self.response.write(get_template.render(templates));
def get(self):
value=self.request.get('course_id');
value=int(value)
conn = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
cursor = conn.cursor()
sql1="SELECT status FROM regiscourse WHERE course_id= '%d'"%value
cursor.execute(sql1);
result=cursor.fetchall()
for row in result:
if row[0]==1:
sql2="UPDATE regiscourse set status=0 where course_id='%d'"%value
cursor.execute(sql2);
else:
sql3="UPDATE regiscourse set status=1 where course_id='%d'"%value
cursor.execute(sql3);
conn.commit()
conn.close()
self.redirect("/")
def post(self):
course_id = self.request.get('course_id')
section_number = self.request.get('section_number')
section_number = int(section_number)
teacher = self.request.get('teacher')
capacity = self.request.get('capacity')
capacity = int(capacity)
# conncheck = rdbms.connect(instance=_INSTANCE_NAME, database='Prinya_Project')
# cursorcheck = conncheck.cursor()
# sqlcheck="INSERT INTO section (regiscourse_id,section_number,teacher_id,capacity,enroll) \
# VALUES ((SELECT course_id FROM course where course_code = '%s'),'%d',\
# (SELECT staff_id FROM staff WHERE firstname = '%s'),'%d','0')"%(course_id,section_number,teacher,capacity)
# cursorcheck.execute(sqlcheck);
# conncheck.commit();
# conncheck.close();
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "INSERT INTO section (regiscourse_id,section_number,teacher_id,capacity,enroll) \
VALUES ((SELECT course_id FROM course where course_code = '%s'),'%d',\
(SELECT staff_id FROM staff WHERE firstname = '%s'),'%d','0')" % (
course_id, section_number, teacher, capacity)
cursor.execute(sql)
conn.commit()
conn.close()
utc = pytz.utc
date_object = datetime.today()
utc_dt = utc.localize(date_object)
bkk_tz = timezone("Asia/Bangkok")
bkk_dt = bkk_tz.normalize(utc_dt.astimezone(bkk_tz))
time_insert = bkk_dt.strftime("%H:%M:%S")
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "INSERT INTO log (staff_id,course_id,day,time,type)\
VALUES(3,(SELECT course_id FROM course WHERE course_code = '%s'),CURDATE(),'%s',2)" % (
course_id, time_insert)
cursor2.execute(sql2)
conn2.commit()
conn2.close()
self.redirect("/ModifyCourse?course_id=" + course_id)
def get_connection():
"""
Create a connection to database
The connection needs to be closed afterwards.
"""
return rdbms.connect(instance=replication_config.SQL_INSTANCE_NAME,
database=replication_config.SQL_DATABASE_NAME)
def get(self):
if self.user:
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='socialibrary')
cursor = conn.cursor()
##TODO: Add a condition in templates for no books
"""Getting list of Books
"""
cursor.execute(
'SELECT * FROM owner_book_mapping WHERE user_id = %s',
(self.user.user_id))
book_rows = cursor.fetchall()
books = []
for book in book_rows:
book_item_id = book[1]
cursor.execute('SELECT title FROM books WHERE entryID = %s',
book_item_id)
b = cursor.fetchone()
books.append(b[0])
"""Getting list of Movies
"""
cursor.execute(
'SELECT * FROM owner_movie_mapping WHERE user_id = %s',
(self.user.user_id))
movie_rows = cursor.fetchall()
movies = []
for movie in movie_rows:
movie_item_id = movie[2]
cursor.execute('SELECT title FROM movies WHERE entryID = %s',
movie_item_id)
m = cursor.fetchone()
movies.append(m[0])
"""Getting list of Games
"""
cursor.execute(
'SELECT * FROM owner_game_mapping WHERE user_id = %s',
(self.user.user_id))
game_rows = cursor.fetchall()
games = []
for game in game_rows:
game_item_id = game[2]
cursor.execute('SELECT title FROM games WHERE entryID = %s',
game_item_id)
g = cursor.fetchone()
games.append(g[0])
conn.commit()
conn.close()
if self.request.get("ref") == "bookmarks":
self.render(u'bookmark_landing')
else:
self.render(u'userstart',
books=books,
movies=movies,
games=games)
else:
self.render(u'welcome')
def get_user_articles(self, user_id):
# Get all articles
conn = rdbms.connect(instance=INSTANCE_NAME, database=DATABASE_NAME)
cursor = conn.cursor()
SQL_GET_ARTICLES = 'SELECT * FROM articles a INNER JOIN sources s'
cursor.execute(SQL_GET_ARTICLES)
if cursor.rowcount == -1:
# There are no article.
return None
recommended = []
for article in cursor.fetchall():
# Run prediction to see if this use is interested in article
print article
article_id = article[0]
# get tags of the article
SQL_GET_ARTICLE_TAGS = 'SELECT tag FROM tags WHERE article_id = %s'
cursor.execute(SQL_GET_ARTICLE_TAGS, (article_id))
article_obj = {
'id': article[0],
'title': article[1],
'link': article[2],
'body': article[3],
'source_id': article[4],
'date': article[5],
'source_name': article[7]
}
for tag in cursor.fetchall():
tag = tag[0]
result = p.predict(user_id, [tag])
print 'tag = ' + tag
if result != None:
if result.outputLabel == '1':
# TODO:for now, If one tag passes, we pass.
# keep score
for r in result['outputMulti']:
if r['label'] == '1':
article_obj['score'] = score
# Give this article to the user, in order of scores
for i in range(0, recommended.len):
if float(recommended[i].score) < float(score):
recommended.insert(i, article_obj)
break
else:
# If we can't predict, simply serve.
logging.debug('could not predict for tag=' + tag)
article_obj['score'] = 0
recommended.append(article_obj)
break
print recommended
return recommended
def get(self):
if self.user:
""" web response to search query """
category = int(self.request.get("category"))
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='socialibrary')
cursor = conn.cursor()
if category == 1:
author = self.request.get("author")
title = self.request.get("title")
rating = int(self.request.get("rating"))
lastupdated = datetime.datetime.now()
created_by = self.user.user_id
cursor.execute(
'INSERT INTO books (title, author, rating, created_by) VALUES (%s, %s, %s, %s)',
(title, author, rating, created_by))
cursor.execute(
'INSERT INTO owner_book_mapping(user_id, book_entry_id) VALUES (%s, %s)',
(created_by, cursor.lastrowid))
elif category == 2:
actor = self.request.get("actor")
#director = self.request.get("director")
title = self.request.get("title")
genre = self.request.get("genre")
rating = int(self.request.get("rating"))
lastupdated = datetime.datetime.now()
created_by = self.user.user_id
cursor.execute(
'INSERT INTO movies (title, actor, rating, genre, created_by) VALUES (%s, %s, %s, %s, %s)',
(title, actor, rating, genre, created_by))
cursor.execute(
'INSERT INTO owner_movie_mapping(user_id, movie_entry_id) VALUES (%s, %s)',
(created_by, cursor.lastrowid))
elif category == 3:
title = self.request.get("title")
platform = self.request.get("platform")
rating = int(self.request.get("rating"))
created_by = self.user.user_id
lastupdated = datetime.datetime.now()
cursor.execute(
'INSERT INTO games (title, platform, rating, created_by) VALUES (%s, %s, %s, %s)',
(title, platform, rating, created_by))
cursor.execute(
'INSERT INTO owner_game_mapping(user_id, game_entry_id) VALUES (%s, %s)',
(created_by, cursor.lastrowid))
conn.commit()
conn.close()
template_values = {'titletext': self.request.get("title")}
self.response.out.write(
template.render(
os.path.join(os.path.dirname(__file__), 'templates',
'addeditem.html'), template_values))
else:
self.render(u'welcome')
def post(self):
course_id = self.request.get('course_id')
section_id = self.request.get('section_id')
section_id = int(section_id)
section_number = self.request.get('section_number')
section_number = int(section_number)
day = self.request.get('day')
day = int(day)
start_time = self.request.get('start_time')
end_time = self.request.get('end_time')
room = self.request.get('roomid')
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "INSERT INTO section_time (day,start_time,end_time,room,section_id)\
VALUES ('%d','%s','%s','%s','%d')" % (day, start_time, end_time,
room, section_id)
cursor.execute(sql)
conn.commit()
conn.close()
utc = pytz.utc
date_object = datetime.today()
utc_dt = utc.localize(date_object)
bkk_tz = timezone("Asia/Bangkok")
bkk_dt = bkk_tz.normalize(utc_dt.astimezone(bkk_tz))
time_insert = bkk_dt.strftime("%H:%M:%S")
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "INSERT INTO log (staff_id,course_id,day,time,type)\
VALUES(3,(SELECT course_id FROM course WHERE course_code = '%s'),CURDATE(),'%s',3)" % (
course_id, time_insert)
cursor2.execute(sql2)
conn2.commit()
conn2.close()
self.redirect("/ModifySection?course_id=" + str(course_id) +
"§ion_id=" + str(section_id) + "§ion_number=" +
str(section_number))
def get(self):
course_id = self.request.get('course_id')
section_id = self.request.get('section_id')
section_id = int(section_id)
section_number = self.request.get('section_number')
section_number = int(section_number)
conn = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor = conn.cursor()
sql = "SELECT section_number,firstname,capacity\
FROM section sec JOIN staff st ON teacher_id=staff_id\
WHERE section_id='%d' AND section_number='%d'" % (section_id,
section_number)
cursor.execute(sql)
conn2 = rdbms.connect(instance=_INSTANCE_NAME,
database='Prinya_Project')
cursor2 = conn2.cursor()
sql2 = "SELECT sectime_id, CASE day WHEN '1' THEN 'Sunday'\
WHEN '2' THEN 'Monday'\
WHEN '3' THEN 'Tuesday'\
WHEN '4' THEN 'Wednesday'\
WHEN '5' THEN 'Thursday'\
WHEN '6' THEN 'Friday'\
WHEN '7' THEN 'Saturday'\
ELSE 'ERROR' END,CONCAT(CONCAT(start_time,'-'),end_time),room FROM section_time WHERE section_id='%d'" % (
section_id)
cursor2.execute(sql2)
templates = {
'section': cursor.fetchall(),
'time': cursor2.fetchall(),
'course_id': course_id,
'section_id': section_id,
'section_number': section_number,
}
get_template = JINJA_ENVIRONMENT.get_template('secdetail.html')
self.response.write(get_template.render(templates))
conn.close()
conn2.close()
def get(self):
conn = rdbms.connect(instance=_INSTANCE_NAME, database=_DATABASE_NAME)
cursor = conn.cursor()
cursor.execute('SELECT * FROM ' + _TABLE_NAME +
' ORDER by RAND() LIMIT 20')
self.response.out.write('<blockquote>')
for row in cursor.fetchall():
self.response.out.write('@%s, ' % row[1])
self.response.out.write('</blockquote>')
conn.close()
