protected void Page_Load(object sender, EventArgs e)
{
DatabaseAccess da = new DatabaseAccess();
string action = Request["action"];
int id = Convert.ToInt32(Request["id"]);
string reason = Request["reason"];
//注意下面这个参数
string Role = Request["roletype"];
//Role 前台必须传和数据库一致的英文名称
string Userid = Request["userid"];
int pass = DEFAULT_PARA;
if (action.Equals(CONFIRM_REQUEST_STR))
{
pass = CONFIRM_REQUEST_PARA;
if(!RoleManage.ChangeUserRoleByUserIDWithRoleEngName(Userid, Role))
{
Response.Write(@"出现错误，请稍后重试");
return;
}
}
else if (action.Equals(REJECT_REQUEST_STR))
pass = REJECT_REQUEST_PARA;
ApplyManage am = new ApplyManage();
Response.Write(am.approvalApplication(id.ToString(), pass.ToString(), reason));
}
/// <summary>
/// 添加通知信息
/// </summary>
/// <param name="infoTitle">通知文头</param>
/// <param name="context">通知内容</param>
/// <param name="teamID">关联的teamID</param>
/// <returns>添加成功与否，成功返回true，否则返回false</returns>
public bool addInfo(string infoTitle , string context , string[] teamID ,string adduser , string type , string endtime)
{
infoTitle = infoTitle.Replace(@"'", "''");
context = context.Replace(@"'", "''");
bool isFinished = true;
DatabaseAccess da = new DatabaseAccess();
foreach(string teamid in teamID)
{
string sqlStr = string.Empty;
if (teamid == @"0")
{
sqlStr = string.Format(@"INSERT WEB_INFO(INFOTITLE , INFODETAIL , TEAMID , ADDUSER , TYPE , ENDTIME)
VALUES (N'{0}',N'{1}',NULL,N'{3}' , N'{4}' , N'{5}')", infoTitle, context, teamid, adduser,type ,endtime);
}
else
{
sqlStr = string.Format(@"INSERT WEB_INFO(INFOTITLE , INFODETAIL , TEAMID , ADDUSER , TYPE, ENDTIME)
VALUES (N'{0}',N'{1}',N'{2}',N'{3}', N'{4}', N'{5}')", infoTitle, context, teamid, adduser, type , endtime);
}
int vat = da.ExcuteSql(sqlStr);
if(vat != 1)
{
isFinished = false;
break;
}
}
return isFinished;
}
/// <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);
}
/// <summary>
/// 查询该人是否曾经加入到其它队伍
/// </summary>
/// <param name="userID">参与者的ID</param>
/// <returns>bool值，若找到相关记录则为真</returns>
public bool hasAlreadyJoined(string userID)
{
string queryStr = string.Format("SELECT COUNT(*) FROM WEB_TEAM_MEMBER WHERE usercode=N'{0}'", userID);
DatabaseAccess da = new DatabaseAccess();
return (da.queryCount(queryStr) > 0);
}
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>
/// 根据用户id删除团队加入信息
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public bool deleteTeamMemberByUserid(string userid)
{
string sqlStr = string.Format(@"DELETE FROM WEB_TEAM_MEMBER WHERE USERCODE = N'{0}'", userid);
DatabaseAccess da = new DatabaseAccess();
int vat = da.ExcuteSql(sqlStr);
if(vat >= 0)
{
return true;
}
return false;
}
/// <summary>
/// 根据团队创建人删除团队信息
/// 该函数存在安全隐患（数据库关联隐患，请谨慎使用）
/// </summary>
/// <param name="usercode">创建人ID</param>
/// <returns></returns>
public bool deleteTeamByTeamLeaderUsercode(string usercode)
{
string sqlStr = string.Format(@"DELETE FROM WEB_TEAM_PROFILE WHERE LEADER = N'{0}'",usercode);
DatabaseAccess da = new DatabaseAccess();
int vat = da.ExcuteSql(sqlStr);
if (vat >= 0)
{
return true;
}
return false;
}
public int saveFeedback(string userName, string type, string url, string teamID, string askey)
{
string strSql = string.Empty;
if (string.IsNullOrEmpty(teamID))
strSql = string.Format(@"INSERT INTO WEB_FEEDBACK (userName, feedbackType, url,ASKEY) VALUES(N'{0}', N'{1}', N'{2}',{3})",
userName, type, url, string.IsNullOrEmpty(askey)?@"NULL" : "N'"+askey+"'");
else
strSql = string.Format(@"INSERT INTO WEB_FEEDBACK (userName, feedbackType, url, teamID,ASKEY) VALUES(N'{0}', N'{1}', N'{2}', N'{3}',{4})",
userName, type, url, teamID, string.IsNullOrEmpty(askey) ? @"NULL" : "N'" + askey + "'");
DatabaseAccess da = new DatabaseAccess();
return (da.ExcuteSql(strSql));
}
/// <summary>
/// 根据userid删除该用户的所有申请
/// </summary>
/// <param name="userid">用户ID</param>
/// <returns>如果删除成功返回true；否则返回false</returns>
public bool DeleteApplyByUserID(string userid)
{
string sqlStr = string.Format(@"DELETE FROM WEB_APPLY WHERE USERID = N'{0}'", userid);
DatabaseAccess da = new DatabaseAccess();
int vat = da.ExcuteSql(sqlStr);
if(vat >= 0)
{
return true;
}
else
{
return false;
}
}
/// <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 ChangeUserRoleByUserIDWithRoleID(string userid, string roleid)
{
string sqlStr = string.Format(@"UPDATE WEB_USERPROFILE SET ROLEID = N'{0}' WHERE USERID = N'{1}'",roleid,userid);
DatabaseAccess DA = new DatabaseAccess();
int vat = DA.ExcuteSql(sqlStr);
if(vat == 1)
{
return true;
}
else
{
return false;
}
}
/// <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="applyID">申请项目的ID</param>
/// <param name="userID">申请人用户ID</param>
/// <param name="message">留言内容</param>
/// <returns>生成成功返回true，否则false</returns>
public bool AddApplication(string applyID , string userID,string message , string detail)
{
message = Util.commonTool.checkStrForSql(message);
if(!String.IsNullOrEmpty(detail))
{
detail = Util.commonTool.checkStrForSql(detail);
}
string sqlStr = string.Format(@"INSERT INTO WEB_APPLY(USERID , WEB_APPLICATION_ID,MESSAGE , DETAILPOSITION)
VALUES(N'{0}',N'{1}',N'{2}',{3})", userID, applyID, message, detail == null ? "NULL" : "N'" + detail + "'");
DatabaseAccess da = new DatabaseAccess();
int row = da.ExcuteSql(sqlStr);
if (row == 1)
return true;
else
return false;
}
public bool deleteInfo(string infoid)
{
string sqlStr = string.Format(@"DELETE FROM WEB_FEEDBACK WHERE ASKEY=N'{0}' ", infoid);
DatabaseAccess DA = new DatabaseAccess();
int vat = DA.ExcuteSql(sqlStr);
if (vat >= 0)
{
sqlStr = string.Format(@"DELETE FROM WEB_INFO WHERE SYSID = N'{0}'", infoid);
vat = DA.ExcuteSql(sqlStr);
if (vat == 1)
return true;
}
return false;
}
/// <summary>
/// 对用户申请进行审批
/// </summary>
/// <param name="ID"></param>
/// <param name="action"></param>
/// <param name="feedback"></param>
/// <returns></returns>
public string approvalApplication(string ID , string action , string feedback)
{
DatabaseAccess da = new DatabaseAccess();
string sqlStr = string.Format(@"UPDATE WEB_APPLY SET
PASS = N'{0}',
FEEDBACK = N'{1}'
WHERE ID = N'{2}'", action, feedback, ID);
int vat = da.ExcuteSql(sqlStr);
if(vat == 1)
{
return @"审批成功";
}
else
{
return @"后台出现逻辑错误，请联系管理员";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if(isAuth && !string.IsNullOrEmpty(user))
{
//只有在认证登录的情况下才能进行
DatabaseAccess da = new DatabaseAccess();
if (UserManage.isFullProfile(user))
{
//如果存在信息则直接加载
//先绑定Province数据
this._blindDataForProvince();
this._blindDataForForm();
}
else
{
//如果不存在则开始新建,绑定Province的数据集
this._blindDataForProvince();
}
}
}
public string queryTeamID(string teamName)
{
string queryStr = string.Format("SELECT TEAMID FROM WEB_TEAM_PROFILE WHERE TEAMNAME=N'{0}'", teamName);
DatabaseAccess da = new DatabaseAccess();
return da.queryCount(queryStr).ToString();
}
/// <summary>
/// 判断申请是否重复提交
/// </summary>
/// <param name="applyID">申请的ID</param>
/// <param name="userID">用户ID</param>
/// <returns>若重复则返回true ， 若不重复则返回false</returns>
public bool isRepeat(string applyID , string userID)
{
string sqlStr = string.Format(@"SELECT COUNT(*) FROM WEB_APPLY WHERE WEB_APPLICATION_ID = N'{0}' AND USERID = N'{1}'", applyID, userID);
DatabaseAccess da = new DatabaseAccess();
return da.isExist(sqlStr);
}
/// <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);
}
/// <summary>
/// 判断Team是否重名
/// </summary>
/// <param name="teamName"></param>
/// <returns>如果重名返回true， 否则返回false</returns>
public bool isTeamRepeat(string teamName, string userCode)
{
DatabaseAccess da = new DatabaseAccess();
string queryStr_1 = string.Format(@"SELECT COUNT(*) FROM WEB_TEAM_PROFILE WHERE TEAMNAME = N'{0}'", teamName);
string queryStr_2 = string.Format(@"SELECT COUNT(*) FROM WEB_TEAM_PROFILE WHERE LEADER = N'{0}'", userCode);
return (da.isExist(queryStr_1) || da.isExist(queryStr_2));
}
/// <summary>
/// 查询团队名称及（或）ID是否合法
/// </summary>
/// <param name="teamID">团队ID</param>
/// <param name="teamName">团队名称</param>
/// <returns>bool值，合法与否</returns>
public bool isTeamValid(string teamID, string teamName)
{
string queryStr = @"SELECT COUNT(*) FROM WEB_TEAM_PROFILE WHERE ";
string strID = string.Format("teamID=N'{0}'", teamID);
string strName = string.Format("teamName=N'{0}'", teamName);
if (!string.IsNullOrEmpty(teamID))
{
queryStr += strID;
if (!string.IsNullOrEmpty(teamName))
queryStr += " AND " + strName;
}
else
queryStr += strName;
DatabaseAccess da = new DatabaseAccess();
return (da.queryCount(queryStr) == 1);
}
/// <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);
}
public bool joinTeam(string teamID, string userID,string teamRole)
{
string queryStr = string.Empty;
if (!string.IsNullOrEmpty(teamID))
queryStr = string.Format("INSERT WEB_TEAM_MEMBER(usercode, teamID, roleName) VALUES(N'{0}', N'{1}', N'{2}')",
userID, teamID, teamRole);
else
queryStr = string.Format("INSERT WEB_TEAM_MEMBER(usercode, roleName) VALUES(N'{0}', N'{1}')",
userID, teamRole);
DatabaseAccess da = new DatabaseAccess();
return (da.ExcuteSql(queryStr) == 1);
}
public bool updateTeamMember(string sysid, string teamID)
{
string queryStr = string.Format("UPDATE WEB_TEAM_MEMBER SET TEAMID=N'{0}' WHERE SYSID=N'{1}'", teamID, sysid);
DatabaseAccess da = new DatabaseAccess();
return (da.ExcuteSql(queryStr) == 1);
}
/// <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 @"数据库操作失败";
}
