def sql_upload_slice(tno, uno, submit_time):
db = connect_db()
cursor = db.cursor()
tno = int(tno)
uno = int(uno)
sql = "SELECT FNO FROM FILE F WHERE F.TNO = %s " % tno
cursor.execute(sql)
fno = int(cursor.fetchone()[0])
sql = "UPDATE PARTICIPATION_TASK SET STATUS='pending',SUBMIT_TIME='%s' WHERE FNO=%d AND UNO=%d" % (
submit_time, fno, uno)
#print sql
cursor.execute(sql)
sql = "SELECT FSNO FROM PARTICIPATION_TASK WHERE FNO=%d AND UNO=%d" % (fno,
uno)
cursor.execute(sql)
res = cursor.fetchall()
for i in res:
sql = "UPDATE FILE_SLICE SET RECEIVE=RECEIVE+1 WHERE FNO=%d AND FSNO=%d" % (
fno, i[0])
#print sql
cursor.execute(sql)
db.commit()
db.close()
def load_taskinfo(tno):
db = connect_db()
cursor = db.cursor()
json = {}
sql = "SELECT UNO,TITLE,DESCRIPTION,PAYMENT,DEADLINE,EXAMPLE_NAME FROM TASK T, SCHOLAR_OWN_TASK S,FILE F WHERE T.TNO = S.TNO AND T.TNO = F.TNO AND S.TNO = %s" % tno
cursor.execute(sql)
res = cursor.fetchone()
json['uno'] = res[0]
json['title'] = res[1]
json['description'] = res[2]
json['payment'] = res[3]
json['deadline'] = res[4]
json['example_name'] = res[5]
sql = "SELECT COUNT(FSNO) FROM FILE F, FILE_SLICE S WHERE F.FNO = S.FNO AND F.TNO = %s GROUP BY F.FNO" % tno
#print sql
cursor.execute(sql)
res = cursor.fetchone()
json['num'] = res[0]
db.close()
return json
def add_task_to_db(task=None):
db = connect_db()
cursor = db.cursor()
#print task.payment
sql = "INSERT INTO TASK(TITLE,DESCRIPTION,OPENTIME,DEADLINE,PAYMENT,TYPE,STAGE) VALUES\
('%s','%s','%s','%s','%s','TASK','OPEN')" % \
(task.title,task.description,task.opentime,task.deadline,task.payment)
#print "sql:",sql
cursor.execute(sql)
cursor.execute("SELECT MAX(TNO) FROM TASK")
tno = cursor.fetchone()[0]
#print tno
cursor.execute("SELECT UNO FROM USERINFO WHERE UNAME = '%s'" % task.owner)
uno = cursor.fetchone()[0]
#print uno
sql = "INSERT INTO SCHOLAR_OWN_TASK(UNO,TNO,ACCESS) VALUES(%d,%d,'owner')" % (uno,tno)
cursor.execute(sql)
db.commit()
db.close()
return tno
def check_legibility(sno,uno):
# whether volunteer meets requirements
sno = int(sno)
db = connect_db()
cursor = db.cursor()
cursor.execute("SELECT MINAGE,MAXAGE,SURVEY_RESTRICT,GENDER_RESTRICT FROM SURVEY WHERE SNO = %d" % sno)
tup = cursor.fetchone()
min_age,max_age,sr,gr = tup[0:5]
cursor.execute("SELECT AGE,GENDER,USERTYPE FROM USERINFO WHERE UNO = %d" % uno)
age,gender,usertype = cursor.fetchone()[0:4]
cursor.execute("SELECT SNO FROM PARTICIPATION WHERE UNO = %d AND SNO = %d" % (uno,sno))
has_dup = cursor.fetchone()
errtext = ""
if has_dup:
errtext = "你已经参与过调研"
return False,errtext
if age < min_age or age > max_age:
errtext += "年龄不符合要求;"
if (gr == u'仅女性' and gender == u'Male') or (gr == u'仅男性' and gender == u'Female'):
errtext += "性别不符合要求;"
if sr == u'仅学者' and usertype != u'Scholar':
errtext += "身份不符合要求;"
db.close()
if errtext != "":
return False,errtext
else:
return True,""
def grant_scholar(uno, pwd, cursor, db):
cursor.execute("CREATE USER scholar_%d IDENTIFIED by '%s'" % (uno, pwd))
cursor.execute(
"GRANT SELECT,DELETE,UPDATE,INSERT ON ScholarDB.* TO scholar_%d" % uno)
cursor.execute("CREATE DATABASE WorkSpace_%d" % uno)
cursor.execute(
"GRANT SELECT,DELETE,UPDATE,INSERT ON WorkSpace_%d.* to scholar_%d" %
(uno, uno))
db.commit()
db2 = connect_db('root', 'dbpjdbpj', 'WorkSpace_%d' % uno)
cursor2 = db2.cursor()
cursor2.execute("create table PRIV_QUESTION(\
QNO INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,\
TITLE VARCHAR(200) NOT NULL,\
SUPPLEMENT VARCHAR(200),\
SUPPLEMENT_TYPE VARCHAR(45),\
TYPE VARCHAR(45) NOT NULL,\
INPUTTYPE VARCHAR(45) NOT NULL\
)")
cursor2.execute("create table PRIV_CHOICE(\
CNO INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,\
PLACE INTEGER NOT NULL,\
QNO INTEGER NOT NULL,\
CONTENT VARCHAR(200)\
)")
db2.commit()
db2.close()
def search(uno=None,
subject=None,
datatype=None,
type=None,
pattern=None,
order=None,
isDesc=None,
user='******',
pwd=123456):
db = connect_db()
cursor = db.cursor()
get_scholar_list_from_db(uno, subject, datatype, type, order, user, pwd)
sql = "SELECT TYPE,NO,TITLE,DESCRIPTION,PAYMENT,OPENTIME FROM LIST WHERE TITLE LIKE '%" + pattern + "%'"
#print "isDesc:",isDesc
if isDesc == '1':
sql += "OR DESCRIPTION LIKE '%" + pattern + "%'"
sql += "ORDER BY " + order
#print sql
cursor.execute(sql)
list_res = cursor.fetchall()
res = load_json(list_res)
db.commit()
db.close()
return res
def load_from_db(self,sno):
db = connect_db()
cursor = db.cursor()
cursor.execute('SELECT TITLE,DESCRIPTION,OPENTIME,PAYMENT FROM SURVEY WHERE SNO = %d' % sno)
self.title,self.description,self.opentime,self.payment = cursor.fetchone()[0:4]
cursor.execute('SELECT PUBLICITY FROM PUBLICITY_SURVEY WHERE SNO = %d' % sno)
self.publicity = cursor.fetchone()[0]
db.close()
def get_money(UNO):
uno = int(UNO)
db = connect_db()
cursor = db.cursor()
sql = 'SELECT MONEY FROM USERINFO WHERE UNO = %d' % uno
cursor.execute(sql)
money = cursor.fetchone()[0]
db.close()
return money
def load_brief_summary(sno):
sno = int(sno)
db = connect_db()
cursor = db.cursor()
cursor.execute("SELECT TITLE,DESCRIPTION,PAYMENT,INST FROM SURVEY,SCHOLAR_OWN_SURVEY,SCHOLAR WHERE "
"SURVEY.SNO = SCHOLAR_OWN_SURVEY.SNO AND SCHOLAR_OWN_SURVEY.UNO = SCHOLAR.UNO AND ACCESS = 'owner' "
"AND SURVEY.SNO = %d" %sno)
l = cursor.fetchone()
return {'title':l[0],'description':l[1],'payment':l[2],'inst':l[3]}
def get_scholar_list_from_db(uno=None,
subject=None,
datatype=None,
publicity=None,
type=None,
order=None,
user='******',
pwd='123456',
onlyforme=True):
db = connect_db()
cursor = db.cursor()
cursor.execute(
"SELECT COUNT(*) FROM information_schema.views WHERE table_schema = 'ScholarDB' AND table_name = 'LIST' "
)
l = cursor.fetchall()
#print l
if (l[0][0] == 1):
cursor.execute("DROP VIEW LIST")
sql = """CREATE VIEW LIST (NO, TYPE, TITLE, DESCRIPTION, PAYMENT, OPENTIME)
AS
"""
if type == 'SURVEY':
sql += add_survey_to_scholar_list(uno, subject, user, pwd, onlyforme,
publicity)
if type == 'TASK':
sql += add_task_to_scholar_list(uno, datatype, user, pwd, onlyforme,
publicity)
if type == "BOTH":
sql += add_survey_to_scholar_list(
uno, subject, user, pwd, onlyforme,
publicity) + " UNION " + add_task_to_scholar_list(
uno, datatype, user, pwd, onlyforme, publicity)
#print sql
cursor.execute(sql)
sql = "SELECT TYPE,NO,TITLE,DESCRIPTION,PAYMENT,OPENTIME FROM LIST ORDER BY " + order
cursor.execute(sql)
list_res = cursor.fetchall()
#print list_res
res = load_json(list_res)
db.commit()
db.close()
return res
def inform_privacy(sno):
sno = int(sno)
db = connect_db()
cursor = db.cursor()
cursor.execute("SELECT WHAT FROM PRIVACY WHERE SNO = %d" % sno)
l = cursor.fetchall()
ret = []
for tup in l:
ret.append(tup[0])
db.close()
return ret
def login_mail(MAIL, PWD):
db = connect_db()
cursor = db.cursor()
sql = 'SELECT PWD FROM USERINFO WHERE MAIL = "%s"' % MAIL
cursor.execute(sql)
l = cursor.fetchall()
if not l or l[0][0] != PWD:
db.close()
return (False, '用户名或者密码不正确')
else:
db.close()
return (True, 'Success')
def get_basic_info(MAIL):
db = connect_db()
cursor = db.cursor()
sql = 'SELECT UNAME,UNO,USERTYPE FROM USERINFO WHERE MAIL = "%s"' % MAIL
cursor.execute(sql)
try:
username, uno, usertype = cursor.fetchall()[0][0:3]
db.close()
return username, uno, usertype
except KeyError:
db.close()
#print "Not found"
return ''
def register(UNAME,
PWD,
MAIL,
AGE,
GENDER,
NATION,
CITY,
INST,
TTYPE,
USERTYPE,
MONEY=0):
AGE = int(AGE)
db = connect_db()
cursor = db.cursor()
# 邮箱是否已注册？
sql = 'SELECT MAIL FROM USERINFO WHERE MAIL ="%s"' % MAIL
cursor.execute(sql)
if cursor.fetchall():
db.close()
return (False, "Mail already registered")
sql = 'SELECT MAX(UNO) FROM USERINFO'
cursor.execute(sql)
max_uno = cursor.fetchall()[0][0] # return ((1000L,),)
try:
new_uno = max_uno + 1
except TypeError:
new_uno = 1000
sql1 = "INSERT INTO USERINFO(UNO,UNAME,MAIL,AGE,USERTYPE,GENDER,NATION,CITY,MONEY,PWD)" \
"VALUES(%d,'%s','%s',%d,'%s','%s','%s','%s',%d,'%s')" % (
new_uno, UNAME, MAIL, AGE, USERTYPE, GENDER, NATION, CITY, 0, PWD)
if USERTYPE == 'Scholar':
sql2 = "INSERT INTO SCHOLAR(UNO,TYPE,INST,RATE) VALUES(%d,'%s','%s',%d)" % (
new_uno, TTYPE, INST, 1)
elif USERTYPE == 'Volunteer':
sql2 = "INSERT INTO VOLUNTEER(UNO,CRED) VALUES(%d,%d)" % (new_uno, 100)
else:
raise ValueError('Unknown usertype: %s' % USERTYPE)
#print sql1, sql2
cursor.execute(sql1)
#print 1
cursor.execute(sql2)
#print 2
# update
#if USERTYPE == 'Scholar':
# grant_scholar(new_uno,PWD,cursor,db)
db.commit()
db.close()
return True, 'Success'
def load_questions(sno):
sno = int(sno)
db = connect_db()
cursor = db.cursor()
sq = SurveyQuestions()
cursor.execute("SELECT QNO,TITLE,SUPPLEMENT,SUPPLEMENT_TYPE,TYPE FROM QUESTION WHERE SNO = %d ORDER BY SNO" % sno)
tups = cursor.fetchall()
if not tups:
return None
for tup in tups:
tup2 = []
if tup[4].startswith("qsc"):
cursor.execute("SELECT CONTENT FROM CHOICE WHERE QNO = %d ORDER BY PLACE" % tup[0])
tup2 = cursor.fetchall()
sq.append(tup,tup2)
return sq
def send_index(max_num, num, tno, uno):
db = connect_db()
cursor = db.cursor()
max_num = int(max_num)
num = int(num)
uno = int(uno)
sql = "SELECT SEND FROM FILE F, FILE_SLICE S WHERE F.FNO = S.FNO AND F.TNO = %s " % tno
cursor.execute(sql)
res = cursor.fetchall()
for i in range(0, max_num):
j = i
i = (i + 1) % max_num
if res[i][0] < res[j][0]:
break
index = []
for k in range(0, num):
index.append(i)
i = (i + 1) % max_num
sql = "SELECT FNO FROM FILE F WHERE F.TNO = %s " % tno
cursor.execute(sql)
fno = int(cursor.fetchone()[0])
for k in index:
sql = "UPDATE FILE_SLICE,FILE SET SEND = SEND + 1 WHERE FILE_SLICE.FNO = FILE.FNO AND FILE.TNO = %s AND FSNO = %d" % (
tno, k)
cursor.execute(sql)
sql = "INSERT INTO PARTICIPATION_TASK(FNO,FSNO,UNO,STATUS) VALUES (%d,%d,%d,'unload')" % (
fno, k, uno)
cursor.execute(sql)
#print "fno",fno
#print "tno",tno
db.commit()
db.close()
#print index
return index
def load_json(list_res):
db = connect_db()
cursor = db.cursor()
res = []
for tup in list_res:
try:
dict = {
"type": tup[0],
"no": tup[1],
"title": tup[2],
"description": tup[3],
"payment": tup[4],
"opentime": tup[5]
}
if tup[0] == 'SURVEY':
sql = "SELECT MINAGE,MAXAGE,GENDER_RESTRICT,SURVEY_RESTRICT FROM SURVEY WHERE SNO= %d" % tup[
1]
cursor.execute(sql)
l = cursor.fetchall()
dict["min_age"] = l[0][0]
dict["max_age"] = l[0][1]
dict["gender_restrict"] = l[0][2]
dict["survey_restrict"] = l[0][3]
sql = "SELECT WHAT FROM SURVEY_SUBJECT WHERE SNO = %d" % tup[1]
cursor.execute(sql)
l = cursor.fetchall()
dict["subject1"] = l[0][0] if len(l) >= 1 else ""
dict["subject2"] = l[1][0] if len(l) >= 2 else ""
dict["subject3"] = l[2][0] if len(l) >= 3 else ""
else:
sql = "SELECT DATATYPE FROM FILE WHERE TNO =%d" % tup[1]
cursor.execute(sql)
l = cursor.fetchall()
dict["datatype"] = l[0][0]
res.append(dict)
except Exception:
#print 'error at sql_list'
pass
db.commit()
db.close()
return res
def add_file_to_db(rawdata,example,tno,datatype,num):
db = connect_db()
cursor = db.cursor()
sql = "INSERT INTO FILE(FNAME,EXAMPLE_NAME,TNO,DATATYPE) VALUES" \
"('%s','%s',%d,'%s')" % (rawdata,example,tno,datatype)
print sql
cursor.execute(sql)
cursor.execute("SELECT MAX(FNO) FROM FILE")
fno = cursor.fetchone()[0]
for i in range(0,num):
sql = "INSERT INTO FILE_SLICE(FNO,FSNO,SEND,RECEIVE) VALUES" \
"(%d,%d,'0','0')" % (fno,i)
#print "test____",sql
cursor.execute(sql)
db.commit()
db.close()
def add_survey_to_db(title, detail, questions, user='******', pwd='dbpjdbpj'):
# args type - SurveyTitle, SurveyDetail, SurveyQuestion
db = connect_db()
cursor = db.cursor()
# title
sql = "INSERT INTO SURVEY(SNO,TITLE,DESCRIPTION,MINAGE,MAXAGE,GENDER_RESTRICT,SURVEY_RESTRICT,PAYMENT,STAGE,OPENTIME,TYPE,MAXNEED) VALUES" \
"(NULL,'%s','%s',%d,%d,'%s','%s',%d,'OPEN','%s','SURVEY',%d)" % (
title.title, title.description, detail.min_age, detail.max_age,
detail.gender_restrict, detail.survey_restrict, detail.payment, detail.opentime,detail.maxneed)
cursor.execute(sql)
cursor.execute("SELECT MAX(SNO) FROM SURVEY")
sno = cursor.fetchall()[0][0]
cursor.execute("SELECT UNO FROM USERINFO WHERE UNAME = '%s'" % detail.owner)
uno = cursor.fetchall()[0][0]
sql = "INSERT INTO SCHOLAR_OWN_SURVEY(UNO,SNO,ACCESS) VALUES(%d,%d,'owner')" % (uno, sno)
cursor.execute(sql)
for subject in title.subject:
sql = "INSERT INTO SURVEY_SUBJECT(SNO,WHAT) VALUES(%d,'%s')" % (sno, subject)
cursor.execute(sql)
for privacy in detail.privacy:
sql = "INSERT INTO PRIVACY(SNO,WHAT) VALUES (%d,'%s')" % (sno, privacy)
cursor.execute(sql)
for question in questions.question_list:
sql = "INSERT INTO QUESTION(QNO,SNO,TITLE,SUPPLEMENT,SUPPLEMENT_TYPE,TYPE) VALUES" \
"(NULL,%d,'%s','%s','%s','%s')" % (sno, question.title, question.supplement, question.supplement_type,
question.type + " " + question.input_type)
cursor.execute(sql)
if question.type == 'qsc':
cursor.execute("SELECT MAX(QNO) FROM QUESTION")
qno = cursor.fetchall()[0][0]
for i, choice in enumerate(question.choice):
sql = "INSERT INTO CHOICE(CNO,PLACE,QNO,CONTENT) VALUES(NULL,%d,%d,'%s')" % (i, qno, choice)
cursor.execute(sql)
db.commit()
db.close()
def load_from_db(self,tno):
db = connect_db()
cursor = db.cursor()
cursor.execute('SELECT TITLE,DESCRIPTION,OPENTIME,PAYMENT FROM TASK WHERE TNO = %d' % tno)
self.title, self.description, self.opentime, self.payment = cursor.fetchone()[0:4]
db.close()
def load_json(list_res):
db = connect_db()
cursor = db.cursor()
res = []
for tup in list_res:
dict = {
"type": tup[0],
"no": tup[1],
"title": tup[2],
"description": tup[3],
"payment": tup[4],
"opentime": tup[5]
}
if tup[0] == 'SURVEY':
sql = "SELECT MINAGE,MAXAGE,GENDER_RESTRICT,SURVEY_RESTRICT FROM SURVEY WHERE SNO= %d" % tup[
1]
cursor.execute(sql)
l = cursor.fetchall()
dict["min_age"] = l[0][0]
dict["max_age"] = l[0][1]
dict["gender_restrict"] = l[0][2]
dict["survey_restrict"] = l[0][3]
sql = "SELECT COUNT(*) FROM PARTICIPATION WHERE SNO = %d" % tup[1]
cursor.execute(sql)
dict["num"] = cursor.fetchone()[0]
#added by AuCson 0428: PUBLICITY:
sql = "SELECT PUBLICITY FROM PUBLICITY_SURVEY WHERE SNO = %d" % tup[
1]
cursor.execute(sql)
try:
dict["publicity"] = cursor.fetchone()[0]
except TypeError:
dict['publicity'] = ''
#end add
sql = "SELECT WHAT FROM SURVEY_SUBJECT WHERE SNO = %d" % tup[1]
cursor.execute(sql)
l = cursor.fetchall()
#print l
dict["subject1"] = l[0][0] if len(l) >= 1 else ""
dict["subject2"] = l[1][0] if len(l) >= 2 else ""
dict["subject3"] = l[2][0] if len(l) >= 3 else ""
else:
#added by Aucson 0428
sql = "SELECT PUBLICITY FROM PUBLICITY_TASK WHERE TNO = %d" % tup[1]
cursor.execute(sql)
try:
dict["publicity"] = cursor.fetchone()[0]
except TypeError:
dict['publicity'] = ''
#end add
sql = "SELECT DATATYPE,SUM(SEND),SUM(RECEIVE),COUNT(FSNO) FROM FILE F,FILE_SLICE S WHERE TNO =%d AND F.FNO=S.FNO GROUP BY F.FNO" % tup[
1]
#print sql
cursor.execute(sql)
l = cursor.fetchall()
if l:
dict["datatype"] = l[0][0]
dict["num"] = l[0][1]
dict["now"] = l[0][2]
dict["slice"] = l[0][3]
res.append(dict)
db.commit()
db.close()
return res