1.使用powerdesigner软件设计数据库字段
1)powerdesigner软件的General是编写表中英文名的,想清楚要设计服务状态表需要的字段,在Columns中编写字段;
2)设计子服务状态表需要的字段(同上),并且两者需要有关联,比如这两个表是通过ServiceId这个字段来进行联系;
3)在Preview下方可以获取服务状态表的SQL语句,可以直接拿到SQL Server中建表; 4)获取子服务状态表的SQL语句(同上);
5)两个表建成以后的样子;
2. 在数据库中执行,创建表
1)将Preview下获取服务状态表的SQL语句复制到SQLServe数据库中,创建服务状态表;
drop table if exists s_servicestatustable;
/*==============================================================*/
/* Table: s_servicestatustable */
/*==============================================================*/
create table s_servicestatustable
(
ServiceId varchar(36) not null,
ServiceName varchar(50),
ServiceStatus int,
ServiceOperation varchar(50),
ServiceProcessTime varchar(20),
ServiceRefreshTime varchar(20),
primary key (ServiceId)
);
2)选中建好的表,右键---设计,编写服务器状态表内容;
3)创建子服务状态表(获取人员表d_futuresaccount中的一些内容,并使用as修改列名,条件是status为1的);
SELECT
'03358964-b6e9-42a6-b807-27b5b7846f2c' as ServiceId ,
NewID() as SonServeId ,
(investorid+'-'+brokerid) as SonServeName,
0 as SonServeStatus,
'0' as SonServeOperation,
'2022-11-08 14:02:10' as SonServeProcessTime,
'2022-11-08 09:02:10' as SonServeRefreshTime,
investorid as accountnumber,
brokerid as brokerid
INTO [dbo].[s_sonservicestatustable]
FROM [d_futuresaccount] where [status] = 1;
4)或者创建子表也可以使用另一种方式:
先创建子服务状态表
drop table if exists s_sonservicestatustable;
/*==============================================================*/
/* Table: s_sonservicestatustable */
/*==============================================================*/
create table s_sonservicestatustable
(
ServiceId varchar(36),
SonServeId int not null,
SonServeName varchar(50),
SonServeStatus int,
SonServeOperation varchar(50),
SonServeProcessTime varchar(20),
SonServeRefreshTime varchar(20),
accountnumber varchar(20),
brokerid varchar(20),
primary key (SonServeId)
);
在往子服务状态表里插入信息;
insert into [dbo].[s_sonservicestatustable]([ServiceId],[SonServeId],[SonServeName],[SonServeStatus],[SonServeOperation],[SonServeProcessTime],[SonServeRefreshTime],[accountnumber],[brokerid])
(select '03355564-b6e9-42a6-b807-27b5b7846f2c',
NEWID(),
(select [dictname]+'-'+[investorid] from [dbo].[s_dictionary] where [dictcode]=[brokerid]),
0,
0,
'20221109 9:48:57',
'20221109 14:24:25',
[investorid],
[brokerid]
from [dbo].[d_futuresaccount])
将sa建立权限给mark,右键属性---权限--搜索--浏览---选择mark
选择权限,除了接管所有权和控制,其他都勾上;
3.使用视图对子服务表进行关联,d_futuresaccount为主表,
将b_employees表中的worknumber与d_futuresaccount表中的worknumber进行关联;并显示b_employees表中的name,d_futuresaccount表中的worknumber;
将d_futuresaccount表中的investorid与s_sonservicestatustable表中的accountnumber进行关联;
将d_futuresaccount表中的brokerid与s_sonservicestatustable表中的brokerid进行关联;
并显示s_sonservicestatustable表中的所有信息;
d_futuresaccount表中的信息来自s_sonservicestatustable表;
将s_dictionary表中的dictcode与s_sonservicestatustable表中的brokerid进行关联;并显示dictname;
s_dictionary表中的信息来自s_sonservicestatustable表;
将dicttype筛选器设置为=期货公司;
视图关联后形成的SQL语句;
SELECT dbo.s_sonservicestatustable.ServiceId, dbo.s_sonservicestatustable.SonServeId,
dbo.s_sonservicestatustable.SonServeName, dbo.s_sonservicestatustable.SonServeStatus,
dbo.s_sonservicestatustable.SonServeOperation, dbo.s_sonservicestatustable.SonServeProcessTime,
dbo.s_sonservicestatustable.SonServeRefreshTime, dbo.s_sonservicestatustable.accountnumber,
dbo.s_sonservicestatustable.brokerid, dbo.d_futuresaccount.worknumber, dbo.b_employees.name,
dbo.s_dictionary.dictname
FROM dbo.s_dictionary RIGHT OUTER JOIN
dbo.s_sonservicestatustable ON dbo.s_dictionary.dictcode = dbo.s_sonservicestatustable.brokerid LEFT OUTER JOIN
dbo.b_employees INNER JOIN
dbo.d_futuresaccount ON dbo.b_employees.worknumber = dbo.d_futuresaccount.worknumber ON
dbo.s_sonservicestatustable.accountnumber = dbo.d_futuresaccount.investorid AND
dbo.s_sonservicestatustable.brokerid = dbo.d_futuresaccount.brokerid
WHERE (dbo.s_dictionary.dicttype = '期货公司')
整体格式如下:
到此数据库设计完成;
将sa的视图权限给mark,右键属性---权限--搜索--浏览---选择mark后,勾选如下选项:查看定义、选择、引用;
5.使用VisualStudio软件进行如下操作,
1)IYHLHService.cs文件中声明一个方法;
/// <summary>
/// 获取服务器所有信息
/// </summary>
/// <returns></returns>
[OperationContract]
DataTable AccountStatus();
/// <summary>
/// 获取子服务器所有信息
/// </summary>
/// <returns></returns>
[OperationContract]
DataTable SonAccountStatus();
2)在YHLHService.cs中使用,获取服务器所有信息,需要右键--生成网站进行编译;
/ <summary>
/ 获取服务器所有信息
/ 声明一个ds实体集,用于存放SQL语句查询到的数据
/ dh声明 引用DataHelper中的方法
/ dt声明 因为是DataTable类型的所以需要声明一下
/ 对ds获取到的信息进行非空判断
/ </summary>
/ <returns></returns>
public DataTable AccountStatus()
{
DataSet ds = new DataSet();
DataHelper dh = new DataHelper();
DataTable dt = new DataTable();
string sqlstring = @"SELECT * FROM [dbo].[s_servicestatustable]";
ds = dh.GetDataSet(sqlstring);
if (ds != null && ds.Tables != null)
{
dt = ds.Tables[0];
}
return dt;
}
/ <summary>
/ 获取子服务器所有信息
/ </summary>
/ <returns></returns>
public DataTable SonAccountStatus()
{
DataSet ds = new DataSet();
DataHelper dh = new DataHelper();
DataTable dt = new DataTable();
string sqlstring = @"select * from [dbo].[s_sonservicestatustable]";
ds = dh.GetDataSet(sqlstring);
if (ds != null && ds.Tables != null)
{
dt = ds.Tables[0];
}
return dt;
}
3)在Entity.cs中声明变量名,
/// <summary>
/// 服务器信息
/// </summary>
public class ServeAccount
{
public string ServiceId;
public string ServiceName;
public int ServiceStatus;
public string ServiceOperation;
public string ServiceProcessTime;
public string ServiceRefreshTime;
}
/// <summary>
/// 子服务器信息
/// </summary>
public class SonServeAccount
{
public string ServiceId;
public string SonServeId;
public string SonServeName;
public int SonServeStatus;
public string SonServeOperation;
public string SonServeProcessTime;
public string SonServeRefreshTime;
public string accountnumber;
public string brokerid;
}
对该文件进行更新服务引用;
4) 在LocalService中获取变量,进行操作,做完之后,进行右键--发布网站编译;
/// <summary>
/// 服务器所有信息
/// </summary>
/// <returns></returns>
[WebMethod]
public string AccountStatus()
{
DataTable dt = service.AccountStatus();
List<ServeAccount> accountstatuslist = new List<ServeAccount>();
if (dt != null && dt.Rows.Count > 0)
{
for(int i = 0; i < dt.Rows.Count; i++)
{
ServeAccount accountstatus = new ServeAccount();
accountstatus.ServiceId = dt.Rows[i]["ServiceId"].ToString();
accountstatus.ServiceName = dt.Rows[i]["ServiceName"].ToString();
accountstatus.ServiceStatus = int.Parse(dt.Rows[i]["ServiceStatus"].ToString());
accountstatus.ServiceOperation = dt.Rows[i]["ServiceOperation"].ToString();
accountstatus.ServiceProcessTime = dt.Rows[i]["ServiceProcessTime"].ToString();
accountstatus.ServiceRefreshTime = dt.Rows[i]["ServiceRefreshTime"].ToString();
accountstatuslist.Add(accountstatus);
}
}
string strjson = JsonConvert.SerializeObject(accountstatuslist);
return strjson;
}
/// <summary>
/// 子服务器所有信息
/// </summary>
/// <returns></returns>
[WebMethod]
public string SonAccountStatus()
{
DataTable dt = service.SonAccountStatus();
List<SonServeAccount> sonaccountstatuslist = new List<SonServeAccount>();
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SonServeAccount sonaccountstatus = new SonServeAccount();
sonaccountstatus.ServiceId = dt.Rows[i]["ServiceId"].ToString();
sonaccountstatus.SonServeId = dt.Rows[i]["SonServeId"].ToString();
sonaccountstatus.SonServeName = dt.Rows[i]["SonServeName"].ToString();
sonaccountstatus.SonServeStatus = int.Parse(dt.Rows[i]["SonServeStatus"].ToString());
sonaccountstatus.SonServeOperation = dt.Rows[i]["SonServeOperation"].ToString();
sonaccountstatus.SonServeProcessTime = dt.Rows[i]["SonServeProcessTime"].ToString();
sonaccountstatus.SonServeRefreshTime = dt.Rows[i]["SonServeRefreshTime"].ToString();
sonaccountstatus.accountnumber = dt.Rows[i]["accountnumber"].ToString();
sonaccountstatus.brokerid = dt.Rows[i]["brokerid"].ToString();
sonaccountstatuslist.Add(sonaccountstatus);
}
}
string strjson = JsonConvert.SerializeObject(sonaccountstatuslist);
return strjson;
}
5)html编写页面
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title></title>
<link href="../../css/basic.css" rel="stylesheet" />
<link rel="stylesheet" href="../../lib/bootstrap/css/bootstrap.css" media="all" />
<script type="text/javascript" src="../../js/jquery-1.8.3.js" charset="utf-8"></script>
<script type="text/javascript" src="../../js/jquery-jtemplates.js" charset="utf-8"></script>
<script type="text/javascript" src="../../js/jquery.min.js" charset="utf-8"></script>
<link href="../../lib/layui-v2.6.8/layui/css/layui.css" rel="stylesheet" />
<script src="../../lib/layui-v2.6.8/layui/layui.js"></script>
<script src="../../js/sysinfo.js"></script>
<link href="../../css/AccountStatus/AccountStatus.css" rel="stylesheet" />
<script src="../../js/AccountStatus/AccountStatus.js"></script>
</head>
<body>
<div id="mangerdiv">
<h3>我管理的账户</h3>
<hr />
<div id="accout">
<ul>
<li><h3>服务器状态</h3></li>
<li><h3 class="layui-btn" id="servestatus">在线</h3></li>
<li><h3 class="layui-btn layui-btn-primary layui-border-black">离线</h3></li>
<li><h3 class="layui-btn layui-btn-primary layui-border-black">重启</h3></li>
</ul>
<ul>
<li>所有账户</li>
<li>在线账户数</li>
<li>离线账户数</li>
<li>添加新账户</li>
</ul>
<ul>
<li><h2>10</h2></li>
<li><h2>8</h2></li>
<li><h2>2</h2></li>
<li>
<div class="layui-btn-group">
<button type="button" class="layui-btn layui-btn-primary layui-btn-sm">
<i class="layui-icon"></i>
</button>
</div>
</li>
</ul>
</div>
</div>
<div id="tablediv">
<table class="layui-table col-sm-4">
<colgroup>
<col width="150">
<col width="200">
<col>
</colgroup>
<thead id="thead">
<tr>
<th>账户id</th>
<th>姓名</th>
<th>状态</th>
</tr>
</thead>
<tbody>
<tr>
<td>123456</td>
<td>郑</td>
<td id="status">
<ul>
<li><button class="layui-btn layui-btn-radius">在线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">离线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">重新连接</button></li>
</ul>
</td>
</tr>
<tr>
<td>123</td>
<td>钱1</td>
<td id="status">
<ul>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">在线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-danger">离线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-normal">重新连接</button></li>
</ul>
</td>
</tr>
<tr>
<td>12323</td>
<td>钱2</td>
<td id="status">
<ul>
<li><button class="layui-btn layui-btn-radius">在线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">离线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">重新连接</button></li>
</ul>
</td>
</tr>
<tr>
<td>12367</td>
<td>钱3</td>
<td id="status">
<ul>
<li><button class="layui-btn layui-btn-radius">在线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">离线</button></li>
<li><button class="layui-btn layui-btn-radius layui-btn-primary">重新连接</button></li>
</ul>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
6)css中编写样式
#thead tr th {
vertical-align: middle;
text-align: center;
}
#tablediv {
width: 50%;
text-align: center;
}
#mangerdiv {
width: 50%;
text-align: center;
}
#mangerdiv h3 {
margin-top: 10px;
}
#accout ul {
margin: 10px;
display: flex;
}
#accout ul li {
float: left;
flex: 1;
}
#accout ul:nth-child(2) {
margin-top: 10px;
}
#status ul {
display: flex;
}
#status ul li {
float: left;
flex: 1;
/*text-align: center;*/
}
#status li:first-child {
color: green;
}
#status li:nth-child(2) {
color: #ccc;
}
#status li:nth-child(3) {
color: red;
}
7)在js使用ajax进行接收文件;
jQuery.extend({
serveload: function () {
var jsonObj;//接收数据
$.ajax({
type: 'POST',
dataType: 'JSON',
contentType: 'application/json; charset=utf-8',
async: false,
cache: false,
url: urlstr + "View/LocalService/LocalService.asmx/AccountStatus",
//传递的参数
data: JSON.stringify({
}),
success: function (data) {
jsonObj = JSON.parse(data.d);
console.log(jsonObj);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert("一般性网络问题,请求数据错误。");
//alert(XMLHttpRequest.status);
//alert(XMLHttpRequest.readyState);
//alert(textStatus);
}
});
},
sonserveload: function () {
var jsonObj;//接收数据
$.ajax({
type: 'POST',
dataType: 'JSON',
contentType: 'application/json; charset=utf-8',
async: false,
cache: false,
url: urlstr + "View/LocalService/LocalService.asmx/SonAccountStatus",
//传递的参数
data: JSON.stringify({
}),
success: function (data) {
jsonObj = JSON.parse(data.d);
console.log("子服务器信息:",jsonObj);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert("一般性网络问题,请求数据错误。");
//alert(XMLHttpRequest.status);
//alert(XMLHttpRequest.readyState);
//alert(textStatus);
}
});
}
});
//事件
$(document).ready(function () {
})
$(function () {
$.serveload();
$.sonserveload();
});
就可以获取到数据了
最终展示效果