/// <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);
}
