/// <summary> /// 获取线路 /// </summary> /// <returns></returns> public static DataTable Lines() { DataTable Result = new DataTable(); string _ConnStr = "server={0};database={1};uid={2};pwd={3};"; string Sql = @" SELECT ID as LineID, LineName, [Description], DataSourceAddress, UserName, PassWord, DataBaseName, '' as 'ConStr', LineTag FROM [SYGLDB_LIB].[dbo].[sys_line] where isbhz in (2,3) and isactive =1"; Result = LineDbHelperSQL.Query(Sql).Tables[0]; foreach (DataRow Dr in Result.Rows) { Dr["ConStr"] = string.Format(_ConnStr, Dr["DataSourceAddress"].ToString(), Dr["DataBaseName"].ToString(), Dr["UserName"].ToString(), Dr["PassWord"].ToString()); //Result.Add(string.Format(_ConnStr, Dr["DataSourceAddress"].ToString(), Dr["DataBaseName"].ToString(), Dr["UserName"].ToString(), Dr["PassWord"].ToString())); } return(Result); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { i = 0; if (Session["SysBaseLine"] != null) { sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line; Label1.Text = BasePage.CutString(sysBaseLine.LineName, 18); } if (Session["UserName"] != null) { string Line = "'" + ((ResultInfo)Session["UserInfo"]).Line.Replace(",", "','") + "'"; Line = Line == "''" ? ((ResultInfo)Session["UserInfo"]).LineID : Line; string strSql = @"select ID , LineName , Description , DataSourceAddress , UserName , PassWord , DataBaseName FROM sys_line WHERE ID in(" + Line + ")"; DataSet ds = LineDbHelperSQL.Query(strSql); this.rp_list.DataSource = ds; this.rp_list.DataBind(); } } }
/// <summary> /// 获取用户线路 /// </summary> public DataSet GetUserLines(string strWhere) { DataSet Result = new DataSet(); if (Session["UserName"] != null) { string strSql = @"SELECT ID as LineID , LineName , [Description] , DataSourceAddress , UserName as SaUserName , PassWord as SaPassWord , DataBaseName , IsActive,TestMapJson,LinesJson FROM dbo.sys_Line WHERE 1=1 "; if (!string.IsNullOrEmpty(strWhere)) { strSql += strWhere; } Result = LineDbHelperSQL.Query(strSql); } else { return(null); } return(Result); }
protected void rp_list_ItemCommand(object source, RepeaterCommandEventArgs e) { if (e.CommandName == "change") { string str = e.CommandArgument.ToString(); string strSql = @"SELECT ID , LineName , Description , DataSourceAddress , UserName , PassWord , DataBaseName,IsActive FROM dbo.sys_Line WHERE ID='" + str + "' "; DataSet ds = LineDbHelperSQL.Query(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { BLL_Login bll = new BLL_Login(ds.Tables[0].Rows[0]["ID"].ToString()); bll.CreateProjectModel_BaseSys(ds.Tables[0].Rows[0]["ID"].ToString()); Label1.Text = BasePage.CutString(ds.Tables[0].Rows[0]["LineName"].ToString(), 18); Session["leftTree"] = ""; Session["SelectedTestRoomCodes"] = ""; ResultInfo RI = (ResultInfo)Session["UserInfo"]; RI.LineID = ds.Tables[0].Rows[0]["ID"].ToString(); Session["UserInfo"] = RI; new BLL_Login("BaseSystem").CallService("SetUserLineID", "UserID=" + RI.ID + "&LineID=" + RI.LineID + "&Description=" + ds.Tables[0].Rows[0]["Description"].ToString()); } } }
private void UpDataUserLine() { Session["leftTree"] = ""; Session["SelectedTestRoomCodes"] = ""; string strSql = @"SELECT ID , LineName , Description , DataSourceAddress , UserName , PassWord , DataBaseName,IsActive FROM dbo.sys_Line WHERE ID='" + "LineID".RequestStr() + "' AND ISBHZ in (2,3) "; DataSet ds = LineDbHelperSQL.Query(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { BLL_Login bll = new BLL_Login(ds.Tables[0].Rows[0]["ID"].ToString()); bll.CreateProjectModel_BaseSys(ds.Tables[0].Rows[0]["ID"].ToString()); Session["leftTree"] = ""; Session["SelectedTestRoomCodes"] = ""; ResultInfo RI = (ResultInfo)Session["UserInfo"]; RI.LineID = ds.Tables[0].Rows[0]["ID"].ToString(); Session["UserInfo"] = RI; new BLL_Login("BaseSystem").CallService("SetUserLineID", "UserID=" + RI.ID + "&LineID=" + RI.LineID + "&Description=" + ds.Tables[0].Rows[0]["Description"].ToString()); } }
/// <summary> /// 获取用户线路 /// </summary> public DataSet GetUserLines(string strWhere) { DataSet Result = new DataSet(); if (Session["UserName"] != null) { ResultInfo RI = (ResultInfo)Session["UserInfo"]; string Line = "'" + ((ResultInfo)Session["UserInfo"]).Line.Replace(",", "','") + "'"; Line = Line == "''" ? ((ResultInfo)Session["UserInfo"]).LineID : Line; string strSql = @"SELECT ID as LineID , LineName , [Description] , DataSourceAddress , UserName as SaUserName , PassWord as SaPassWord , DataBaseName , IsActive,TestMapJson,LinesJson FROM dbo.sys_Line WHERE IsActive='1' AND ISBHZ in (2,3) AND Id in (" + Line + ")"; if (!string.IsNullOrEmpty(strWhere)) { strSql += strWhere; } Result = LineDbHelperSQL.Query(strSql); } else { Response.Redirect("~/login.aspx"); } return(Result); }
protected void Button1_Click(object sender, EventArgs e) { int n = LineDbHelperSQL.ExecuteSql("UPDATE sys_User SET Password='******' WHERE UserName='******'"); if (n > 0) { Label1.Text = "密码修改成功!"; } Bind(); }
public void SiteMap() { string DoMain = Session["DoMain"] == null? "sys":Session["DoMain"].ToString(); sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line; string Sql = " Select * from Domain_Line_Map where DoMain ='" + DoMain + "'"; DataSet Ds = LineDbHelperSQL.Query(Sql); Site = Newtonsoft.Json.JsonConvert.DeserializeObject <LineMapEnt>(Ds.Tables[0].Rows[0]["Map"].ToString()); }
private void Bind() { if (Session["UserName"] != null) { lbl_username.Text = Session["UserName"].ToString(); DataSet ds = LineDbHelperSQL.Query("SELECT * FROM sys_User WHERE UserName='******'"); if (ds != null && ds.Tables[0].Rows.Count > 0) { lbl_old.Text = EncryptSerivce.Dencrypt(ds.Tables[0].Rows[0]["Password"].ToString()); } } }
private String bsloginpop(string proc, string users) { int records = 0, pageCount = 0; sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line; DataTable dt = LineDbHelperSQL.GetDataTableFromProc(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), users, sysBaseLine.ID.ToString(), PageIndex, PageSize, "", "", out pageCount, out records); if (dt != null) { string Data = JsonConvert.SerializeObject(dt); return("{\"pageCount\":\"" + pageCount + "\",\"Data\":" + Data + "}"); } else { return(""); } }
private string DeleteUserManageList(string UserName) { sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line; try { string SQL = "delete from sys_bs_users where username='******';delete from sys_users_testroom where username='******'"; DbHelperSQL.ExecuteSql(SQL).ToString(); #region SQL = "DELETE dbo.sys_BaseLine_Users WHERE UserName='******' AND LineID='{1}'"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, UserName, sysBaseLine.ID)); SQL = "SELECT * FROM dbo.sys_BaseLine_Users WHERE UserName='******'"; DataSet ds = LineDbHelperSQL.Query(string.Format(SQL, UserName)); if (ds != null && ds.Tables[0].Rows.Count > 0) { SQL = "SELECT * FROM dbo.sys_BaseUsers WHERE UserName='******'"; DataSet ds1 = LineDbHelperSQL.Query(string.Format(SQL, UserName)); if (ds1 != null && ds1.Tables[0].Rows.Count > 0) { SQL = "SELECT * FROM dbo.sys_BaseLine_Users WHERE LineID='{0}' and UserName='******'"; DataSet ds2 = LineDbHelperSQL.Query(string.Format(SQL, ds1.Tables[0].Rows[0]["LineID"].ToString(), UserName)); if (ds2 != null && ds2.Tables[0].Rows.Count > 0) { } else { //此处修改用户表的默认登录库,因为一旦删除了线路表里面的数据,用户登录以后找不到这个线路会报错 SQL = "UPDATE dbo.sys_BaseUsers SET LineID='{0}' ,Descrption='{1}' WHERE UserName='******'"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, ds.Tables[0].Rows[0]["LineID"].ToString(), "", UserName)); } } } else { SQL = "DELETE dbo.sys_BaseUsers WHERE UserName='******'"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, UserName)); } #endregion return("1"); } catch { return("0"); } }
private string SaveSysBsUsers(string RID, string uname, string upwd, string truename, string active, string group, string temp) { sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line; //0:操作失败,1:操作成功,2:用户名重复. if (!string.IsNullOrEmpty(uname)) { string SQL = ""; #region 添加线路库的数据 SQL = "SELECT * FROM dbo.sys_BaseUsers WHERE UserName='******'"; DataSet sys_ds = LineDbHelperSQL.Query(string.Format(SQL, uname)); //判断大库里面的use表 SQL = "SELECT * FROM dbo.sys_BaseLine_Users WHERE UserName='******' AND LineID='{1}'"; DataSet sys_line_ds = LineDbHelperSQL.Query(string.Format(SQL, uname, sysBaseLine.ID)); //判断大库里面的Line权限 SQL = "SELECT id FROM sys_bs_users where UserName='******';"; DataSet ds = DbHelperSQL.Query(string.Format(SQL, uname)); if (ds != null && ds.Tables[0].Rows.Count == 0) { if (sys_ds != null && sys_ds.Tables[0].Rows.Count == 0) { #region SQL = @"INSERT dbo.sys_BaseUsers ( UserName , Password , IsActive , TrueName , LineID , Descrption , RoleName )VALUES ( '{0}' , '{1}' , '{2}' , '{3}' , '{4}' , '{5}' , '{6}' )"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, uname, EncryptSerivce.Encrypt(upwd), active, truename, sysBaseLine.ID, sysBaseLine.LineName, group)); SQL = @"INSERT dbo.sys_BaseLine_Users ( UserName, LineID) VALUES ( '{0}', '{1}' )"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, uname, sysBaseLine.ID)); #endregion } else { #region //if ((group == "S" && ((sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "S") || (sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "SS"))) || (group == "SS" && ((sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "S") || (sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "SS")))) if ((group == "S" || group == "SS" || group == "X" || group == "A") && ((sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "S") || (sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "SS") || (sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "X") || (sys_ds.Tables[0].Rows[0]["RoleName"].ToString() == "A"))) { SQL = @"INSERT dbo.sys_BaseLine_Users ( UserName, LineID ) VALUES ( '{0}', '{1}' )"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, uname, sysBaseLine.ID)); } else { return("2"); } #endregion } SQL = "INSERT INTO sys_bs_users(UserName,Password,IsActive,TrueName)VALUES('{0}','{1}','{2}','{3}');"; int userCount = DbHelperSQL.ExecuteSql(string.Format(SQL, uname, EncryptSerivce.Encrypt(upwd), active, truename)); if (userCount > 0) { StringBuilder str = new StringBuilder(); SQL = "INSERT INTO sys_users_testroom(username,testroomcode,segment)VALUES('{0}','{1}','{2}');"; string[] roomCode = temp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < roomCode.Length; i++) { str.Append(string.Format(SQL, uname, roomCode[i], group)); } DbHelperSQL.ExecuteSql(str.ToString()); return("1"); } return("0"); } else { SQL = "UPDATE sys_bs_users set Password='******',IsActive='{1}',TrueName='{2}' where UserName='******';"; int userCount = DbHelperSQL.ExecuteSql(string.Format(SQL, EncryptSerivce.Encrypt(upwd), active, truename, uname)); if (userCount > 0) { SQL = "delete from sys_users_testroom where username='******';"; DbHelperSQL.ExecuteSql(string.Format(SQL, uname)); StringBuilder str = new StringBuilder(); SQL = "INSERT INTO sys_users_testroom(username,testroomcode,segment)VALUES('{0}','{1}','{2}');"; string[] roomCode = temp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); for (int i = 0; i < roomCode.Length; i++) { str.Append(string.Format(SQL, uname, roomCode[i], group)); } DbHelperSQL.ExecuteSql(str.ToString()); #region 修改大库的密码 SQL = "SELECT * FROM dbo.sys_BaseUsers WHERE UserName='******' "; DataSet sys_ds_edit = LineDbHelperSQL.Query(string.Format(SQL, uname));//判断大库里面的use表 if (sys_ds_edit != null && sys_ds_edit.Tables.Count > 0) { SQL = @"UPDATE dbo.sys_BaseUsers SET Password='******' WHERE UserName='******'"; LineDbHelperSQL.ExecuteSql(string.Format(SQL, EncryptSerivce.Encrypt(upwd), uname)); } #endregion return("1"); } return("0"); } #endregion } else { return("0"); } }
public void List() { int RecordCount = 0; DataSet DS = LineDbHelperSQL.Query("SELECT [Account] FROM [Sys_Users]"); string Users = string.Empty; if (DS.Tables.Count > 0 && DS.Tables[0].Rows.Count > 0) { foreach (DataRow Dr in DS.Tables[0].Rows) { Users += Users.IsNullOrEmpty() ? "'" + Dr[0].ToString() + "'" : ",'" + Dr[0].ToString() + "'"; } } #region SQL string Sql = @" DECLARE @Page int DECLARE @PageSize int SET @Page = {1} SET @PageSize = {2} SET NOCOUNT ON DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(200)) INSERT INTO @TempTable ( _keyID ) SELECT ID FROM sys_operate_log WHERE 1=1 {0} Order by modifiedDate DESC SELECT sys_operate_log.ID, modifiedby as 'YH', t2.description as 'BD', t3.description as 'DW', t1.description as 'SYS', modifiedDate as 'CZRQ', optType as 'CZLX', sys_module.name as 'MB', DataName as 'BGRQ', BGBH as 'BGBH', modifyitem as 'XGRZ' FROM sys_operate_log left outer join sys_module on sys_module.id = sys_operate_log.moduleID left outer join sys_tree t1 on t1.nodecode = sys_operate_log.testroomcode left outer join sys_tree t2 on t2.nodecode = left(sys_operate_log.testroomcode,8) left outer join sys_tree t3 on t3.nodecode = left(sys_operate_log.testroomcode,12) INNER JOIN @TempTable t ON sys_operate_log.ID = t._keyID WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) {0} Order By modifiedDate Desc DECLARE @C int select @C= count(ID) from sys_operate_log where 1=1 {0} select @C "; #endregion string Where = " "; Where += string.IsNullOrEmpty("StartDate".RequestStr()) ? " " : " AND modifiedDate >='" + "StartDate".RequestStr() + "' "; Where += string.IsNullOrEmpty("EndDate".RequestStr()) ? " " : " AND modifiedDate <='" + DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToShortDateString() + "' "; Where += string.IsNullOrEmpty(SelectedTestRoomCodes) ? " " : " AND TestRoomCode in(" + SelectedTestRoomCodes + ") "; Where += string.IsNullOrEmpty("Person".RequestStr()) ? " " : " AND modifiedby like'%" + "Person".RequestStr() + "%' "; Where += Users.IsNullOrEmpty() ? "" : " AND modifiedby in (" + Users + ")"; Sql = string.Format(Sql, Where, "page".RequestStr(), "rows".RequestStr()); RecordCount = 0; BLL_Document BLL = new BLL_Document(); DataSet Ds = BLL.GetDataSet(Sql); RecordCount = int.Parse(Ds.Tables[1].Rows[0][0].ToString()); string Json = JsonConvert.SerializeObject(Ds.Tables[0]); Json = "{\"rows\":" + Json + ",\"total\":" + RecordCount + "}"; Response.Write(Json); Response.End(); }
/// <summary> /// 添加 /// </summary> public string Edit() { string Result = ""; try { string KMID = Request["KMID"].ToString(); string Content = Request["Content"].ToString(); string Person = UserName; //rolename string Sql = @" UPDATE [dbo].[sys_KeyModify] SET [YZUserName] = '{0}' ,[YZContent] = '{1}' ,[YZOPTime] = '{2}' ,Status={4} WHERE KMID = '{3}' "; Sql = string.Format(Sql, Person, Content, DateTime.Now.ToString(), KMID, "Type".RequestStr()); BLL_Document BLL = new BLL_Document(); Result = BLL.ExcuteCommand(Sql) > 0 ? "true" : "false"; try { #region 发短信 if ("Type".RequestStr() == "2" && Result == "true") { sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line; DataSet DsTemp = LineDbHelperSQL.Query("SELECT [SMSState] FROM [sys_line] where ID ='" + sysBaseLine.ID + "'"); if (DsTemp.Tables.Count > 0 && DsTemp.Tables[0].Rows.Count > 0 && DsTemp.Tables[0].Rows[0][0].ToString() == "1") { string SMSSql = @" SELECT sys_module.name,sys_KeyModify.BGBH,sys_KeyModify.ModifyItem,sys_KeyModify.YzUserName, t1.DESCRIPTION +' '+sys_tree.DESCRIPTION as DESCRIPTION,TestRoomCode FROM sys_KeyModify left outer join sys_tree on sys_KeyModify.testroomcode = sys_tree.nodecode left outer join sys_tree as t1 on Left(sys_KeyModify.testroomcode,8) = t1.nodecode left outer join sys_module on sys_module.id = sys_KeyModify.moduleid where sys_KeyModify.KMID = '" + KMID + "'"; DataSet Ds = BLL.GetDataSet(SMSSql); string SmsContent = "{0} {1} {2} {3} {4}拒绝"; if (Ds.Tables[0] != null && Ds.Tables[0].Rows.Count > 0) { DataRow Dr = Ds.Tables[0].Rows[0]; ModifyItem[] Temp = Newtonsoft.Json.JsonConvert.DeserializeObject <ModifyItem[]>(Dr["ModifyItem"].ToString()); SmsContent = string.Format(SmsContent , Dr["DESCRIPTION"].ToString() , Dr["name"].ToString() , Dr["BGBH"].ToString() , Temp[0].Description + Temp[0].CurrentValue + "修改为" + Temp[0].OriginalValue , Dr["YzUserName"].ToString() ); string CPS = @" SELECT CellPhone FROM [SYGLDB_ZhengXu].[dbo].[sys_sms_receiver] where TestRoomCode ='" + Ds.Tables[0].Rows[0]["TestRoomCode"].ToString() + "' and CellPhone <>'' ANd CellPhone is not null and IsActive=1"; string MS = ""; Ds = BLL.GetDataSet(CPS); if (Ds.Tables[0] != null && Ds.Tables[0].Rows.Count > 0) { foreach (DataRow Dr1 in Ds.Tables[0].Rows) { if (!Dr1[0].ToString().IsNullOrEmpty()) { MS += MS.IsNullOrEmpty() ? Dr1[0].ToString() : "," + Dr1[0].ToString(); } } } if (!MS.IsNullOrEmpty()) { string SMSResult = SendSMS("Mobile=" + MS + "&Content=" + SmsContent + "&Stime=" + DateTime.Now.ToString() + "&Extno=1"); } } } } #endregion } catch { } } catch { Result = "false"; } return(Result); }