from admin.dboper import *

# 查询所有研究课题(前台显示)
def loadResearchTopic_Top6():
    sql = "select * from ResearchTopic"
    results = mySQL_Query(sql)
    return results

# 根据研究课题编号查询研究课题信息(前台projects页面)
def loadAllResearchTopic_ForWeb ():
    sql = "Select RTID, ra.RAID, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent From ResearchTopic rt, ResearchArea ra where rt.RAID = ra.RAID and rt.RTStatus=1 order by rt.RTOrder"
    results = mySQL_Query(sql)
    results2 = []
    for row in results:
        row = list(row)
        row[4] = row[4][0:62] + "..."
        row[5] = row[5][0:125] + "..."
        results2.append(row)
    return results2


# 根据研究课题编号查询研究课题信息(前台projectinfo页面)
def loadResearchTopicByID_ForWeb (RTid):
    sql = "Select RTID, RANameCN, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent From ResearchTopic rt, ResearchArea ra where rt.RAID = ra.RAID and RTID=" + RTid
    results = mySQL_Query(sql)
    if len(results) > 0: results = results[0]
    return results


# 查询所有研究课题信息(前台paperinfo页面)
def loadAllResearchTopic_Show ():
    sql = "Select RTID, ra.RAID, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent From ResearchTopic rt, ResearchArea ra where rt.RAID = ra.RAID and rt.RTStatus=1 order by rt.RTOrder"
    results = mySQL_Query(sql)
    return results


# 查询所有研究课题(后台管理页面)
def loadAllResearchTopic_ForMang ():
    sql = "select RTID, ra.RANameCN, RTNameCN, RTNameEN, RTImage, RTOrder, RTStatus from ResearchTopic rt, ResearchArea ra where rt.RAID = ra.RAID order by rt.RTOrder"
    results = mySQL_Query(sql)
    return results

# 根据研究课题编号查询研究课题信息(前台projects页面)
def loadAllResearchTopic ():
    sql = "Select RTID, ra.RAID, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent From ResearchTopic rt, ResearchArea ra where rt.RAID = ra.RAID and rt.RTStatus=1 order by rt.RTOrder"
    results = mySQL_Query(sql)
    results2 = []
    for row in results:
        row = list(row)
        row[4] = row[4][0:62] + "..."
        row[5] = row[5][0:125] + "..."
        results2.append(row)
    return results2


# 根据研究课题编号查询研究课题信息(后台查看页面)
def loadResearchTopicByID_ForView (RTid):
    sql = "Select RTID, RANameCN, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent, RTOrder, RTStatus From ResearchTopic rt, ResearchArea ra where rt.RAID = ra.RAID and RTID=" + RTid
    results = mySQL_Query(sql)
    return results[0]


# 查询研究课题信息
def loadResearchTopic ():
    sql = "Select * From ResearchTopic "  # where RTStatus=1
    results = mySQL_Query(sql)
    return results


# 查询一个研究课题
def loadResearchTopicByID (RTid):
    sql = "select * from ResearchTopic where RTID=%s" % RTid
    results = mySQL_Query(sql)
    if len(results) > 0: results = results[0]
    return results

# 添加一个研究课题
# RTID, RAID, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent, RTOrder, RTStatus
def add_ResearchTopic (data):
    sql = "insert into ResearchTopic values (NULL, %s, '%s', '%s', '%s', '%s', '%s', '%s', %s, %s) " % (
    data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8])
    i, msg = mySQL_Execute(sql)
    return i, msg


# 更新一个研究课题
## RTID, RAID, RTNameCN, RTNameEN, RTAbstractCN, RTAbstractEN, RTImage, RTContent, RTOrder, RTStatus
def update_ResearchTopic (data):
    sql = "update ResearchTopic set RAID=%s, RTNameCN='%s', RTNameEN='%s', RTAbstractCN='%s', RTAbstractEN='%s', RTImage='%s', RTContent='%s', RTOrder=%s, RTStatus=%s where RTID=%s" % (
    data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9])
    i, msg = mySQL_Execute(sql)
    return i, msg



# 删除一个研究课题
def del_ResearchTopic (RTid):
    sql = "delete from ResearchTopic where RTID =%s" % RTid
    i, msg = mySQL_Execute(sql)
    return i, msg


# 查询前6个项目信息(显示在主页)
def loadResearchTopic_Top6 ():
    sql = "select rt.RTID, ra.RANameCN, ra.RANameEN, rt.RTNameCN, rt.RTNameEN,  rt.RTImage  from ResearchTopic rt, ResearchArea ra where ra.RAID = rt.RAID limit 0,6;"
    results = mySQL_Query(sql)
    return results