def getTeacgerByXueKe(self, param):
sql_num = "SELECT count(*) as num from (select id from teacher_dis_code where discipline_code=%s) a left join teacher b on a.id=b.id"
sql = "SELECT a.*,b.age,b.email,b.theme from (select id,name,school,institution,selected,time from teacher_dis_code where discipline_code=%s) a left join teacher b on a.id=b.id order by a.selected desc,a.time desc limit %s,%s"
s = dbs.getDics(sql, (param['field'], param['pPageNum'] *
(param['page'] - 1), param['pPageNum']))
n = dbs.getDics(sql_num, (param['field'], ))
return {"result": s, "num": n[0]["num"]}
def get_theme(self, params):
# 从计算后的theme_year_new表中获取数据
sql_0 = "select * from theme_year_cr where author_id=%s"
sql_2 = "SELECT theme FROM `theme_year_cr` WHERE author_id=%s GROUP BY theme"
data_result = dbs.getDics(sql_0, params)
themelist = dbs.getDics(sql_2, params)
return data_result, themelist
def getMessage(self, params):
sql_num = 'SELECT count(*) as num FROM `messages` a LEFT JOIN user b on a.user=b.account'
sql = 'SELECT a.*,b.account,b.username FROM `messages` a LEFT JOIN user b on a.user=b.account order by time desc limit %s,%s'
param = (params['pPageNum'] * (params['page'] - 1), params['pPageNum'])
s = dbs.getDics(sql, param)
n = dbs.getDics(sql_num)
return {"result": s, "num": n[0]["num"]}
def getUsers(self, params):
sql_num = 'SELECT count(*) as num from (select a.account,a.username,b.lastlogin from user a left join (SELECT value,max(time) as lastlogin FROM `records` where type="登陆" GROUP BY value) b on a.account=b.value) c join (SELECT value,time FROM `records` where type="注册" ) d on c.account=d.value'
sql = 'SELECT c.*,d.time as register from (select a.account,a.username,b.lastlogin from user a left join (SELECT value,max(time) as lastlogin FROM `records` where type="登陆" GROUP BY value) b on a.account=b.value) c join (SELECT value,time FROM `records` where type="注册" ) d on c.account=d.value limit %s,%s'
param = (params['pPageNum'] * (params['page'] - 1), params['pPageNum'])
s = dbs.getDics(sql, param)
n = dbs.getDics(sql_num)
return {"result": s, "num": n[0]["num"]}
def get_theme(self, params):
# 从计算后的theme_year_new表中获取数据
sql_0 = "select year, theme, paper_num from theme_year_new where author_id=%s"
sql_2 = "SELECT DISTINCT theme " \
"FROM theme_year " \
"WHERE author_id=%s"
data_result = dbs.getDics(sql_0, params)
theme_result = dbs.getDics(sql_2, params)
return theme_result, data_result
def get_discipline(self, id):
d_sql = "SELECT xueke1, xueke2, level FROM discipline where school=%s"
if not id.isdigit():
sql = "SELECT id FROM school_info where name=%s"
info_result = dbs.getDics(sql, id)
if not info_result:
return None
id = info_result[0]["id"]
result = dbs.getDics(d_sql, id)
return result
def get_important_discipline(self, id):
d_sql = "SELECT code, name FROM discipline_school where school_id=%s"
if not id.isdigit():
sql = "SELECT id FROM school_info where name=%s"
info_result = dbs.getDics(sql, id)
if not info_result:
return None
id = info_result[0]["id"]
result = dbs.getDics(d_sql, id)
return result
def getNeedTopicByTopic(self, params):
params = ('%' + params[0] + '%', )
sql = "SELECT * FROM need_topic where topic like %s"
radar_result = dbs.getDics(sql, params)
if len(radar_result) == 0:
return []
return radar_result
def get_info(self, params):
if params.isdigit():
sql = "SELECT * FROM school_info where id=%s"
else:
sql = "SELECT * FROM school_info where name=%s"
info_result = dbs.getDics(sql, params)
return info_result
def get_teacher(self, params):
if params is list:
params = str(params)
sql = "select id, name, theme, title, institution, pic from teacher where (name, school) in %s"
else:
sql = "select id, name, theme, title, institution, pic from teacher where school = %s limit 6"
data_result = dbs.getDics(sql, params)
return data_result
def get_single_axis(self, params):
# 从计算后的theme_year_new表中获取数据
sql_0 = "select * from theme_single_axis where author_id=%s"
data_result = dbs.getDics(sql_0, params)
return data_result
def get_paper(self, params):
sql = "SELECT `name`,author,org,`year`,cited_num,abstract FROM `paper_expertpage` WHERE author_id=%s;"
info_result = dbs.getDics(sql, params)
return info_result
def getField(self):
sql = 'SELECT a.*,b.`name`,b.selected from (SELECT discipline_code ,count(*) as num FROM `teacher_dis_code` GROUP BY discipline_code) a join discipline_new b on a.discipline_code=b.`code` ORDER BY time desc'
s = dbs.getDics(sql)
return s
def getHotSearch(self,param):
sql = "select value,sum(num) as sum from statistics where type=%s GROUP BY value order by sum desc limit %s,%s"
r=dbs.getDics(sql,(param["type"],param['pPageNum']*(param['page']-1),param["pPageNum"]))
return r
def getSearchResult(self, sql, params):
re = dbs.getDics(sql)
return self.getDateSet(re, params)
def getRecord(self):
sql="SELECT count(*) as num,type,value FROM `records` where TO_DAYS(time) = TO_DAYS(NOW())-1 GROUP BY type,value "
re= dbs.getDics(sql)
return re
def get_infosByIds(self, params):
sql = "SELECT * FROM teacher where id in " + params
info_result = dbs.getDics(sql)
return info_result
def get_ego(self, params):
sql = "SELECT * FROM ego_network where author_id=%s order by w desc limit 0,50"
ego_result = dbs.getDics(sql, params)
return ego_result
def getFieldTeacher(self):
sql = "SELECT c.*,d.name,d.school,d.institution,d.theme,d.title from (select a.codeName,a.time,b.id from (SELECT code,time,name as codeName FROM `discipline_new` where selected=1 ORDER BY time desc) a LEFT JOIN teacher_dis_code b on b.selected=1 and a.`code`=b.discipline_code ORDER BY a.time desc,b.time desc) c LEFT JOIN teacher d on c.id=d.id"
s = dbs.getDics(sql)
return s
def getUserByAccount(self, params):
sql = "SELECT * FROM user where account=%s"
s = dbs.getDics(sql, params)
return s
def get_school_info(self, params):
sql = "SELECT * FROM school_info where characteristic=%s limit 4"
info_result = dbs.getDics(sql, params)
return info_result
def get_Record(self, params):
sql = "SELECT * FROM `records` WHERE user=%s AND type='登陆' ORDER BY time DESC LIMIT 0,5"
info_result = dbs.getDics(sql, params)
return info_result
def get_topics(self, params):
# 画词云用
sql_0 = "select topics from teacher where id=%s"
data_result = dbs.getDics(sql_0, params)
return data_result
def selectByTypeAndDate(self,param):
sql="select value,sum(num) as sum from statistics where type=%s and date between %s and %s GROUP BY value"
params=(param["type"],param['startDate'],param['endDate'])
r=dbs.getDics(sql,params)
return r
def get_paperbar(self, params):
# 画历年成果数用
sql_0 = "select * from paper_bar where id=%s"
data_result = dbs.getDics(sql_0, params)
return data_result
def selectByTypeAndDateOrderByDate(self,param):
sql="select * from statistics where type=%s and date between %s and %s order BY date"
params=(param["type"],param['startDate'],param['endDate'])
r=dbs.getDics(sql,params)
return r
def get_info(self, params):
sql = "SELECT * FROM teacher where id=%s"
info_result = dbs.getDics(sql, params)
return info_result
def selectIPwhereLocationisNull(self):
sql = "select id,ip from records where location='' or location is Null"
r = dbs.getDics(sql)
return r
def get_radar(self, params):
sql = "SELECT * FROM radar where author_id=%s"
radar_result = dbs.getDics(sql, params)
return radar_result[0]
def get_infosByIds(self, params):
sql = "SELECT * FROM platform where id = " + str(params)
info_result = dbs.getDics(sql)
return info_result
