/// <summary>
/// 根据用户ID去修改其角色
/// 根据需求可改数据库以及参数介绍
/// </summary>
/// <param name="userid">用户的网站id</param>
/// <param name="roleid">
///0 admin 管理员
///1 staff 项目成员
///2 coordinator 协调员
///3 RA 协调助理
///4 partner 参赛者
///5 webGuest 网页用户
/// </param>
/// <returns>修改成功返回true</returns>
public static bool ChangeUserRoleByUserIDWithRoleEngName(string userid , string roleEngname)
{
string queryStr = string.Format(@"SELECT ID FROM WEB_USER_ROLE WHERE ROLEENGNAME = N'{0}'", roleEngname);
DatabaseAccess da = new DatabaseAccess();
string roleid = da.queryDatatable(queryStr).Rows[0][0].ToString();
return ChangeUserRoleByUserIDWithRoleID(userid, roleid);
}
public static string packJson(string countStr, string queryStr)
{
//string jsonHead = "{\"total\":";
//string jsonMid = ",\"rows\":";
//string jsonTail = "}";
//DatabaseAccess da = new DatabaseAccess();
//int count = da.queryCount(countStr);
//StringBuilder rJsonVal = new StringBuilder();
//rJsonVal.Append(jsonHead);
//rJsonVal.Append(count);
//rJsonVal.Append(jsonMid);
//DataTable dt = da.queryDatatable(queryStr);
//string mainJsonBody = JsonHelper.DataTableToJSON(dt);
//rJsonVal.Append(mainJsonBody);
//rJsonVal.Append(jsonTail);
//return rJsonVal.ToString();
//version2
DatabaseAccess da = new DatabaseAccess();
int count = da.queryCount(countStr);
DataTable dt = da.queryDatatable(queryStr);
return JsonHelper.ToEasyUIJson(dt, count);
}
private void _blindDataForProvince()
{
DatabaseAccess da = new DatabaseAccess();
DataTable dt = da.queryDatatable(@"SELECT * FROM WEB_PROVINCE ORDER BY PROVINCEID ");
Province.DataSource = dt;
Province.DataTextField = "provinceName";
Province.DataValueField = "provinceID";
Province.DataBind();
}
/// <summary>
/// 查询团队细节
/// </summary>
/// <param name="usercode">查询人的ID</param>
/// <returns>返回一个DATATABLE，【中文名称，电话号码，性别，email，团队角色，队伍ID，队伍名称】</returns>
public DataTable GetTeamUserDetail(string usercode)
{
string queryStr = string.Format(@"SELECT T2.USERNAME , T2.PHONE , T2.GENDER , T2.EMAIL , T1.ROLENAME , T1.TEAMID ,T3.TEAMNAME
FROM WEB_TEAM_MEMBER T1 LEFT JOIN WEB_USERPROFILE T2 ON T1.USERCODE = T2.USERID LEFT JOIN WEB_TEAM_PROFILE T3 ON T1.TEAMID = T3.TEAMID
WHERE T1.TEAMID IN (SELECT T3.TEAMID
FROM WEB_TEAM_MEMBER T3
WHERE T3.USERCODE = N'{0}') ORDER BY T1.TEAMID ", usercode);
DatabaseAccess da = new DatabaseAccess();
return da.queryDatatable(queryStr);
}
/// <summary>
/// 优化过的查询方式，返回easyUI的格式
/// </summary>
/// <param name="countStr">查询总数量语句,SELECT COUNT</param>
/// <param name="queryStr">查询语句</param>
/// <param name="pageNum">页数</param>
/// <param name="rows">每页容量</param>
/// <returns></returns>
public static string packJson(string countStr, string queryStr,int pageNum ,int rows)
{
DatabaseAccess da = new DatabaseAccess();
int count = da.queryCount(countStr);
string sqlStr = string.Format(@"select top {0} TABLE_TEMP.*
from (
select row_number() over(order by (select 0)) as rownumber,* from
(
{1}
)AS TEMP ) AS TABLE_TEMP
WHERE rownumber>={2}", rows.ToString(), queryStr, ((pageNum-1) * rows + 1).ToString());
DataTable dt = da.queryDatatable(sqlStr);
return JsonHelper.ToEasyUIJson(dt, count);
}
/// <summary>
/// 查询参加的团队名称
/// </summary>
/// <param name="userID">登录名</param>
/// <returns>一个表格正常情况下只取第一行，数据为[团队ID，团队名称，参与角色]</returns>
public DataTable GetTeamInfo(string userID)
{
DatabaseAccess da = new DatabaseAccess();
string queryStr = string.Format(@"SELECT T2.TEAMID,T2.TEAMNAME,T1.ROLENAME FROM WEB_TEAM_MEMBER T1 LEFT JOIN WEB_TEAM_PROFILE T2 ON T1.TEAMID = T2.TEAMID WHERE T1.USERCODE = N'{0}'", userID);
return da.queryDatatable(queryStr);
}
/// <summary>
/// 插入一条团队信息
/// </summary>
/// <param name="teamProfile">团队信息的内容</param>
/// <param name="userID">插入人的ID</param>
/// <returns>返回插入的ID号</returns>
public string SavaTeamInfo(Dictionary<string, string> teamProfile, string userID)
{
string sqlStr = string.Format(@"INSERT WEB_TEAM_PROFILE(PROVINCEID,TEAMNAME,LEADER)
VALUES(N'{0}' , N'{1}',N'{2}')", teamProfile["Province"], teamProfile["teamName"], userID);
DatabaseAccess da = new DatabaseAccess();
int vat = da.ExcuteSql(sqlStr);
if (vat == 1)
{
string queryStr = string.Format(@"SELECT TEAMID FROM WEB_TEAM_PROFILE WHERE TEAMNAME = N'{0}'", teamProfile["teamName"]);
DataTable dt = da.queryDatatable(queryStr);
string teamID = dt.Rows[0][0].ToString();
sqlStr = string.Format(@"INSERT WEB_TEAM_MEMBER(USERCODE,TEAMID,ROLENAME)
VALUES(N'{0}',N'{1}',N'队长')", userID, teamID);
vat = da.ExcuteSql(sqlStr);
RoleManage.ChangeUserRoleByUserIDWithRoleID(userID, "4");
if (vat == 1)
{
return teamID;
}
}
return @"数据库操作失败";
}
/// <summary>
/// 获取个人申请记录
/// </summary>
/// <param name="userID">申请人信息</param>
/// <returns>返回一个datatable row形式为【申请名称，是否通过[0,1,2]】</returns>
public DataTable GetApplication(string userID)
{
DatabaseAccess da = new DatabaseAccess();
string queryStr = string.Format(@"SELECT T2.WEB_APPLICATION_NAME ,T1.PASS,T1.FEEDBACK
FROM WEB_APPLY T1 LEFT JOIN WEB_APPLICATION T2 ON T1.WEB_APPLICATION_ID = T2.WEB_APPLICATION_ID
WHERE T1.USERID = N'{0}'", userID);
return da.queryDatatable(queryStr);
}