该项目主要用于为来访人员和客户展示会议室预定情况,用的Adminlte框架。
1.页面效果
1、登录页
2、预定页面
3、会议室管理界面
4、液晶屏展示界面
2.代码部分
1、引入adminlte框架,注意js和css 的引用,css中icon和fonts的关联内容的引用。
2、预定部分的代码如下:
HTML代码
@{
ViewBag.Title = "会议室预定";
}
<!-- Date Picker -->
<link rel="stylesheet" href="@Url.Content("~/assets/plugins/datepicker/datepicker3.css")">
<!-- datepicker -->
<script src="@Url.Content("~/assets/plugins/datepicker/bootstrap-datepicker.js")"></script>
<link rel="stylesheet" type="text/css" href="@Url.Content("~/Content/dist/book_alertform.css")" />
<link rel="stylesheet" type="text/css" href="@Url.Content("~/Content/dist/calendar.css")">
<link rel="stylesheet" type="text/css" href="@Url.Content("~/Content/dist/book_index.css")" />
<script src="@Url.Content("~/Scripts/dist/book_index.js")"></script>
<script src="@Url.Content("~/Scripts/dist/calendar.js")"></script>
<div class="change-type">
<div class="type-left" class="showType == true ? 'showListType':''">
<ul>
<li><a href="@Url.Content("/book/room")">会议室管理</a><span></span></li>
<li><a href="@Url.Content("/book/index")">预定</a><span></span></li>
<li><a href="@Url.Content("/book/show")">展示</a><span></span></li>
</ul>
</div>
<div class="type-right">
<p>
<i class="el-icon-menu"></i>
菜单
</p>
</div>
</div>
<div class="box">
<!-- /.box-header -->
<div class="box-body">
<div style="width:100%;height:30px;">
<div class="box-header with-border" style="float:left;display:inline;">
@*<div style="float:left;padding:7px 10px;margin-right:20px;border:1px solid #72afd2;">
<a href="@Url.Content("/book/room")">会议室管理</a>
</div>*@
<div style="float:left;margin-right:30px;border:1px solid red;"></div>
<div style="padding:7px 20px;float:left;" class="is-booked-0">内部使用</div>
<div style="padding:7px 20px;float:left;" class="is-booked-1">客户使用</div>
<div style="padding:7px 20px;float:left;" class="is-booked-2">研发使用</div>
</div>
<span style="font-size:20px;margin-left:23%;line-height:60px; font-weight:bold;">会议室预定系统</span>
<div class="box-header with-border" style="float:right;display:inline;">
<div id="t_calendar" style="float:left;width:150px;">
日期:
<input id="user_date" style="width:100px;padding:7px 10px;border:1px solid #ccc;" readonly />
</div>
<div style="float:left;margin-left:20px;">
<button type="button" class="btn btn-block btn-primary" style="width:80px;" id="btn_book">预约</button>
</div>
</div>
</div>
<div id="disappare" style="display:none;">
<p>预定时间小于当前时间,不可预定~</p>
</div>
<table class="table table-bordered table-hover" id="table_1"></table>
</div>
<!-- /.box-body -->
</div>
<!-- /.box --><!--初始预定 弹窗开始-->
<div class="tan" id="book_do">
<div class="box">
<div class="form">
<span class="close" id="close_doBook">✖</span>
<h3>会议室预定</h3>
<form action="#" method="post" name="form_submit" id="form_submit">
<table class="tb-111">
<tbody>
<tr>
<td>
<div class="control-group">
<label>会议主题: </label>
<input type="text" name="b_topic" id="b_topic" placeholder="请输入会议主题" required />
</div>
</td>
<td>
<div class="control-group">
<label>使用类别: </label>
<select name="b_sort" id="b_sort" class="form-control" style="width:150px;height:30px;padding:0 10px;margin-bottom:10px;">
<option value="0">内部使用</option>
<option value="1">客户使用</option>
<option value="2">研发使用</option>
</select>
</div>
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>预订人: </label>
<input type="text" name="b_name" id="b_name" placeholder="请输入部门&姓名" required />
</div>
</td>
<td>
<div class="control-group">
<label>联系电话: </label>
<input type="text" name="b_phone" id="b_phone" placeholder="请输入电话" required />
</div>
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>会议室: </label>
<input type="text" class="form-control" id="show_room_name" readonly="readonly" />
</div>
</td>
<td>
@*<div class="control-group">
<label>预约日期: </label>
<input type="text" name="b_book_date" id="b_book_date" readonly="readonly" />
</div>*@
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>开始日期: </label>
<input type="text" name="b_book_date_1" id="b_book_date_1" readonly="readonly" />
</div>
</td>
<td>
<div class="control-group">
<label>结束日期: </label>
<input type="text" name="b_book_date_2" id="b_book_date_2" readonly="readonly" />
</div>
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>开始时间: </label>
<input type="text" class="form-control" name="b_time_1" id="show_s_time" readonly="readonly" />
</div>
</td>
<td>
<div class="control-group">
<label>结束时间: </label>
<input type="text" class="form-control" name="b_time_2" id="show_e_time" readonly="readonly" />
</div>
</td>
</tr>
</tbody>
</table>
<input type="hidden" name="b_room_id" id="b_room_id" value="" />
<input type="hidden" name="b_time_id" id="b_time_id" value="" />
@*<input type="text" name="name" id="name" placeholder="请输入邮箱地址" />
<input type="password" name="password" id="passw" placeholder="请输入密码" />*@
</form>
<div style="float:right;">
<button type="button" class="btn btn-block btn-primary" style="width:80px;" id="btn_submit">提交</button>
</div>
</div>
</div>
</div>
<!--初始预定 弹窗结束-->
<!--修改预订 弹窗开始-->
<div class="tan" id="bookInfo_edit">
<div class="box">
<div class="form">
<span class="close" id="close_edit">✖</span>
<h3>修改预订信息</h3>
<form action="#" method="post" name="form_edit_submit" id="form_edit_submit">
<table class="tb-111">
<tbody>
<tr>
<td>
<div class="control-group">
<label>会议主题: </label>
<input type="text" name="b_edit_topic" id="b_edit_topic" placeholder="请输入会议主题" required />
</div>
</td>
<td>
<div class="control-group">
<label>使用类别: </label>
<select name="b_edit_sort" id="b_edit_sort" class="form-control" style="width:150px;height:30px;padding:0 10px;margin-bottom:10px;">
<option value="0">内部使用</option>
<option value="1">客户使用</option>
<option value="2">研发使用</option>
</select>
</div>
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>预订人: </label>
<input type="text" name="b_edit_name" id="b_edit_name" placeholder="请输入部门&姓名" required />
</div>
</td>
<td>
<div class="control-group">
<label>联系电话: </label>
<input type="text" name="b_edit_phone" id="b_edit_phone" placeholder="请输入电话" required />
</div>
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>会议室: </label>
<input type="text" class="form-control" id="b_edit_room_name" readonly="readonly" />
</div>
</td>
<td>
@*<div class="control-group">
<label>预约日期: </label>
<input type="text" name="b_edit_book_date" id="b_edit_book_date" readonly="readonly" />
</div>*@
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>开始日期: </label>
<input type="text" class="form-control" name="b_edit_book_date_1" id="b_edit_book_date_1" readonly="readonly" />
</div>
</td>
<td>
<div class="control-group">
<label>结束日期: </label>
<input type="text" name="b_edit_book_date_2" id="b_edit_book_date_2" />
</div>
</td>
</tr>
<tr>
<td>
<div class="control-group">
<label>开始时间: </label>
@*<input type="text" class="form-control" name="b_edit_time_1" id="b_edit_time_1" />*@
<select name="b_edit_time_1" id="b_edit_time_1" class="form-control" style="width:150px;height:30px;padding:0 10px;margin-bottom:10px;">
<option value="08:00">08:00</option>
<option value="08:30">08:30</option>
<option value="09:00">09:00</option>
<option value="09:30">09:30</option>
<option value="10:00">10:00</option>
<option value="10:30">10:30</option>
<option value="11:00">11:00</option>
<option value="11:30">11:30</option>
<option value="12:00">12:00</option>
<option value="12:30">12:30</option>
<option value="13:00">13:00</option>
<option value="13:30">13:30</option>
<option value="14:00">14:00</option>
<option value="14:30">14:30</option>
<option value="15:00">15:00</option>
<option value="15:30">15:30</option>
<option value="16:00">16:00</option>
<option value="16:30">16:30</option>
<option value="17:00">17:00</option>
<option value="17:30">17:30</option>
<option value="18:00">18:00</option>
<option value="18:30">18:30</option>
<option value="19:00">19:00</option>
</select>
</div>
</td>
<td>
<div class="control-group">
<label>结束时间: </label>
@*<input type="text" class="form-control" name="b_edit_time_2" id="b_edit_time_2" />*@
<select name="b_edit_time_2" id="b_edit_time_2" class="form-control" style="width:150px;height:30px;padding:0 10px;margin-bottom:10px;">
<option value="08:00">08:00</option>
<option value="08:30">08:30</option>
<option value="09:00">09:00</option>
<option value="09:30">09:30</option>
<option value="10:00">10:00</option>
<option value="10:30">10:30</option>
<option value="11:00">11:00</option>
<option value="11:30">11:30</option>
<option value="12:00">12:00</option>
<option value="12:30">12:30</option>
<option value="13:00">13:00</option>
<option value="13:30">13:30</option>
<option value="14:00">14:00</option>
<option value="14:30">14:30</option>
<option value="15:00">15:00</option>
<option value="15:30">15:30</option>
<option value="16:00">16:00</option>
<option value="16:30">16:30</option>
<option value="17:00">17:00</option>
<option value="17:30">17:30</option>
<option value="18:00">18:00</option>
<option value="18:30">18:30</option>
<option value="19:00">19:00</option>
</select>
</div>
</td>
</tr>
</tbody>
</table>
<input type="hidden" name="b_edit_room_id" id="b_edit_room_id" value="" />
@*<input type="hidden" name="b_edit_time_id" id="b_edit_time_id" value="" />*@
<input type="hidden" name="b_edit_book_id" id="b_edit_book_id" value="" />
</form>
<div style="float:right;">
<button type="button" class="btn btn-block btn-primary" style="width:80px;" id="btn_edit_submit">确定修改</button>
</div>
</div>
</div>
</div>
<!--修改预订 弹窗结束-->
<!-- 模态框(Modal) -->
<button class="btn btn-primary btn-lg" id="motaikuang" data-toggle="modal" data-target="#myModal" style="display:none;">模态框</button>
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="false">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"
aria-hidden="true">
×
</button>
<h4 class="modal-title" id="myModalLabel">
会议预定详情
</h4>
</div>
<div class="modal-body">
<div class="modal-my-con">
<span class="modal-my-con-left">会议室:</span>
<span class="modal-my-con-right" id="modal_room"></span>
</div>
<div class="modal-my-con">
<span class="modal-my-con-left">预定日期:</span>
<span class="modal-my-con-right" id="modal_date"></span>
</div>
<div class="modal-my-con">
<span class="modal-my-con-left">起止时间:</span>
<span class="modal-my-con-right" id="modal_time"></span>
</div>
<div class="modal-my-con">
<span class="modal-my-con-left">会议主题:</span>
<span class="modal-my-con-right" id="modal_topic"></span>
</div>
<div class="modal-my-con">
<span class="modal-my-con-left">预订人:</span>
<span class="modal-my-con-right" id="modal_name"></span>
</div>
<div class="modal-my-con">
<span class="modal-my-con-left">联系电话:</span>
<span class="modal-my-con-right" id="modal_phone"></span>
</div>
<div class="modal-my-con">
<span class="modal-my-con-left">预约时间:</span>
<span class="modal-my-con-right" id="modal_book_time"></span>
</div>
</div>
<div class="modal-footer" id="modal_footer" style="display:flex;justify-content:center;">
<button type="button" id="modal_hide" class="btn btn-primary" data-dismiss="modal" style="display:none;"></button>
<button type="button" id="modal_edit" modal_book_id="" class="btn btn-primary">
修改预订
</button>
<button type="button" id="modal_cancel" modal_book_id="" class="btn btn-danger">
取消预定
</button>
</div>
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->
css代码:
.table-bordered > thead > tr > th, .table-bordered > thead > tr > td {
text-align: center;
vertical-align: middle;
word-break: keep-all;
white-space: nowrap;
}
.table-bordered > thead > tr > th, .table-bordered > tbody > tr > th, .table-bordered > tfoot > tr > th, .table-bordered > thead > tr > td, .table-bordered > tbody > tr > td, .table-bordered > tfoot > tr > td {
text-align: center;
vertical-align: middle;
width: 250px;
height: 30px;
overflow: hidden;
/*text-overflow: ellipsis;
white-space: nowrap;*/
}
.table > tbody > tr > td, .table > tbody > tr > th, .table > tfoot > tr > td, .table > tfoot > tr > th, .table > thead > tr > td, .table > thead > tr > th {
padding: 3px;
}
/*设置table中鼠标滑过的背景色*/
.table-hover > tbody > tr:hover > td,
.table-hover > tbody > tr:hover > th {
/*background-color: #f5f5f5;*/
}
/*设置table表到浏览器边的距离*/
.box-body {
padding: 15px;
}
/*奇数=odd,偶数=even*/
/*table样式 - start*/
.test-color {
color: #f5bcdc;
}
.td-selected { /*td被选中时*/
background-color: #ffaf00;
}
.is-booked-0 { /*内部使用*/
font-size: 14px;
background-color: #00FFFF;
}
.is-booked-1 { /*客户使用*/
font-size: 14px;
background-color: #81AFF5;
}
.is-booked-2 { /*研发使用*/
font-size: 14px;
background-color: lightgray;
}
.is-booked-other { /*其他情况用,漂亮的紫色*/
color: #fff;
font-size: 14px;
background-color: #d200ff;
}
.is-booked-00 { /*蓝色*/
color: #fff;
font-size: 14px;
background-color: #33A1C9;
}
.is-booked-01 { /*绿色 */
color: #fff;
font-size: 14px;
background-color: #00C78C;
}
.is-booked-02 { /*浅粉色*/
color: #fff;
font-size: 14px;
background-color: #DEA3A5;
}
.is-booked-4 { /*维修中 - 橘黄色*/
color: #fff;
font-size: 16px;
background-color: #ffaf00;
}
.is-booked-5 { /*深绿色*/
color: #fff;
font-size: 16px;
background-color: #2E8B57;
}
.is-booked-6 { /*深紫色*/
color: #fff;
font-size: 16px;
background-color: #872657;
}
.is-booked-7 { /*深蓝色*/
color: #fff;
font-size: 16px;
background-color: #3D59AB;
}
.is-booked-8 { /*橘红色*/
color: #fff;
font-size: 16px;
background-color: #FF6347;
}
/*table样式 - end*/
#disappare {
border-radius: 5px;
background: gray;
font-size: 16px;
position: fixed;
top: 45%;
left: 42%;
}
#disappare p {
padding: 5px;
margin: 7px 18px;
font-size: 15px;
color: white;
}
.modal-content {
width: 66%;
margin: 20% 20%;
}
.modal-my-con {
padding: 8px 0;
}
.modal-my-con-left {
float: left;
display: inline-block;
font-weight: bold;
}
.modal-my-con-right {
margin-left: 6px;
}
.box-header.with-border {
border: 0px solid red;
}
/*会议预定详情弹窗标题居中*/
.modal-title {
text-align: center;
}
@charset "utf-8";
/** {
padding: 0;
margin: 0;
box-sizing: border-box;
font-family: "微软雅黑";
}*/
/*a {
text-decoration: none;
}*/
/*弹窗*/
.tan {
position: fixed;
top: 0;
left: 0;
width: 100%;
height: 100%;
background: rgba(0,0,0,.6);
display: none;
}
.tan .box {
width: 580px;
height: auto;
overflow: hidden;
background-color: #fff;
border-radius: 3px;
position: absolute;
top: 50%;
left: 50%;
transform: translate(-50%,-50%);
}
.tan .box .form {
position: relative;
width: 100%;
height: 100%;
padding: 40px 50px;
padding-top: 15px;
padding-bottom: 30px;
text-align: center;
overflow: hidden;
}
.tan .box .form span.close {
position: absolute;
right: 16px;
top: 5px;
font-size: 14px;
display: block;
cursor: pointer;
height: 8px;
width: 8px;
color: #b0b8bf;
}
.tan .box .form span:hover {
color: #808492;
}
.tan .box .form form input {
display: block;
/*width: 50%;*/
width: 150px;
outline-style: none;
height: 30px;
margin-bottom: 10px;
border-radius: 3px;
border: 1px solid #ccc;
padding: 0 10px;
}
.tan .box .form h3 {
font-size: 18px;
color: #333437;
font-weight: normal;
margin-bottom: 8px;
}
.tan .box .form p.desc {
color: #0074fa;
font-size: 12px;
margin-bottom: 18px;
}
.tan .box .form form input[name=valicode] {
padding-right: 80px;
}
.tan .box .form form input[type=submit] {
background-color: #1278f6;
color: #fff;
border: 0;
height: 40px;
cursor: pointer;
}
.tan .box .form form input[type=submit]:hover {
background-color: #3382FF;
}
.tan .box .form form input.red {
border-color: red;
}
.tan .box .form form .valicode {
position: relative;
}
.tan .box .form form .valicode img {
position: absolute;
right: 8px;
top: 2px;
height: 35px;
}
.tan .box .form p.reg {
margin-top: 20px;
}
.tan .box .form p.reg a {
color: #333;
font-size: 12px;
float: right;
}
.tan .box .form p.reg a:first-child {
float: left;
}
.tan .box .form form .error {
height: 30px;
line-height: 30px;
text-align: left;
color: red;
font-size: 12px;
display: none;
}
.control-group {
padding-top: 8px;
padding-bottom: 5px;
margin-bottom: 8px;
border-bottom: 1px dotted #dddddd;
/*border: 1px solid green;*/
}
.tb-111 {
width: 100%;
height: 100%;
}
.tb-111 td {
/*border: 1px solid red;*/
}
label {
float: left;
white-space: nowrap;
display: inline-block;
margin: 5px 6px;
width: 70px;
text-align: left;
}
JS代码:
var today_date, now_time, global_user_date;
$(function () {
today_date = cur_date(); //获取当前系统date(2019-06-06)
$("#user_date").val(today_date);
init(today_date);//JQ初始化方法
now_time = cur_dateTime();//获取系统时间(年月日时分秒)
global_user_date = today_date; //用户选择日期
//预约 - Date picker
$('#b_book_date_1').datepicker({
todayHighlight: true,
autoclose: true,
format: 'yyyy-mm-dd',
startDate: today_date
});
$('#b_book_date_2').datepicker({
todayHighlight: true,
autoclose: true,
format: 'yyyy-mm-dd',
startDate: today_date
});
// 修改预约信息 - Date picker
$('#b_edit_book_date_2').datepicker({
todayHighlight: true,
autoclose: true,
format: 'yyyy-mm-dd',
startDate: today_date
});
//var aaa = $("#b_book_date_1").datepicker("getDate").toLocaleString();//获取
//console.log(aaa)
//日期控件
$("#user_date").calendar({
// controlId: "tt", // 弹出的日期控件ID,默认: $(this).attr("id") + "Calendar"
speed: 200, // 三种预定速度之一的字符串("slow", "normal", or "fast")或表示动画时长的毫秒数值(如:1000),默认:200
complement: true, // 是否显示日期或年空白处的前后月的补充,默认:true
readonly: true, // 目标对象是否设为只读,默认:true
//upperLimit: new Date(addDate(today_date, 7)), // 日期上限,默认:NaN(不限制)
lowerLimit: new Date("2019/06/01"), // 日期下限,默认:NaN(不限制)
callback: function () { // 点击选择日期后的回调函数
global_user_date = $("#user_date").val();
reload_tbl(global_user_date); //用户选择日期重构表格
}
});
//点击预约
$("#btn_book").click(function () {
if (global_r_id == "" || global_arr_t_id.length == 0) {
alert("未选择会议室和时间!");
return;
}
//设置预约默认时间
$("#b_book_date_1").datepicker('setDate', global_user_date);
$("#b_book_date_2").datepicker('setDate', global_user_date);
$('#book_do').show(); //show表单
var t_begin_idx = global_arr_t_id[0];
var t_end_idx = global_arr_t_id[(global_arr_t_id.length) - 1];
var t_begin = $("#t_" + t_begin_idx).text().split('-')[0];
var t_end = $("#t_" + t_end_idx).text().split('-')[1];
var r_name = $("#r_" + global_r_id).text();
//var time_begin = global_user_date + " " + t_begin;
//var time_end = global_user_date + " " + t_end;
//给表单赋值
$("#b_room_id").val(global_r_id);
$("#b_time_id").val(global_arr_t_id);
$("#b_book_date").val(global_user_date);
$("#show_room_name").val(r_name);
$("#show_s_time").val(t_begin);
$("#show_e_time").val(t_end);
});
$("#close_doBook").click(function () {
$('#book_do').hide(); //hide表单
document.getElementById("form_submit").reset(); //清空表单数据
});
$("#close_edit").click(function () {
$('#bookInfo_edit').hide(); //hide表单
document.getElementById("form_edit_submit").reset(); //清空表单数据
});
//提交预约
$("#btn_submit").click(function () {
if ($("#b_topic").val() == "" || $("#b_name").val() == "" || $("#b_phone").val() == "" || $("#b_book_date_1").val() == "" || $("#b_book_date_2").val() == "") {
alert("请填写完整信息!")
return;
}
var bool_t = checkEndTime($("#b_book_date_1").val(), $("#b_book_date_2").val());
if (!bool_t) {
alert("日期区间有误!");
return;
}
if ($("#b_phone").val().length != 11) {
alert("联系电话不合法!")
return;
}
var data = $("#form_submit").serialize();
var url = '/book/doBook';
$.ajax({
type: 'POST',
cache: false,
url: url,
dataType: 'json',
data: data,
error: function (res) {
console.log(res);
alert("error!");
},
success: function (res) {
var ret = JSON.parse(res.Data);
if (ret.code == "0") {
alert(ret.desc);
$('#book_do').hide(); //hide表单
document.getElementById("form_submit").reset(); //清空表单数据
reload_tbl(global_user_date);//重构表格
} else {
alert(ret.desc)
}
}
})
});
//提交修改预约信息
$("#btn_edit_submit").click(function () {
if ($("#b_edit_topic").val() == "" || $("#b_edit_sort").val() == "" || $("#b_edit_name").val() == "" || $("#b_edit_phone").val() == "" || $("#b_edit_book_date_1").val() == "" || $("#b_edit_book_date_2").val() == "" || $("#b_edit_time_1").val() == "" || $("#b_edit_time_2").val() == "" || $("#b_edit_room_id").val() == "" || $("#b_edit_book_id").val() == "") {
alert("请填写完整信息!")
return;
}
var bool_d = checkEndTime($("#b_edit_book_date_1").val(), $("#b_edit_book_date_2").val());
if (!bool_d) {
alert("日期区间有误!");
return;
}
var bool_t = checkEndTime("2019-01-01 " + $("#b_edit_time_1").val(), "2019-01-01 " + $("#b_edit_time_2").val());
if (!bool_t || $("#b_edit_time_1").val() == $("#b_edit_time_2").val()) {
alert("时间区间有误!");
return;
}
if ($("#b_edit_phone").val().length != 11) {
alert("联系电话不合法!")
return;
}
//判断时间格式是否正确
//var regTime1 = /^(0\d{1}|1\d{1}|2[0-3]):([0-5]\d{1})$/; //hh:mm
//var regTime2 = /^(0\d{1}|1\d{1}|2[0-3]):[0-5]\d{1}:([0-5]\d{1})$/; //hh:mm:ss
var data = $("#form_edit_submit").serialize();
var url = '/book/editBook';
$.ajax({
type: 'POST',
cache: false,
url: url,
dataType: 'json',
data: data,
error: function (res) {
console.log(res);
alert("error!");
},
success: function (res) {
var ret = JSON.parse(res.Data);
if (ret.code == "0") {
alert(ret.desc);
$('#bookInfo_edit').hide(); //hide表单
document.getElementById("form_edit_submit").reset(); //清空表单数据
reload_tbl(global_user_date);//重构表格
} else {
alert(ret.desc)
}
}
})
});
//点击修改预约
$("#modal_edit").click(function () {
var book_id = $("#modal_cancel").attr("modal_book_id");
$('#modal_hide').click(); //hide弹窗
$("#bookInfo_edit").show(); //show表单
$.ajax({
type: 'POST',
cache: false,
url: '/book/queryBookInfoById',
dataType: 'json',
data: { 'book_id': book_id },
error: function (res) {
console.log(res)
},
success: function (res) {
//console.log(res)
if (res.code == 0) {
var data = res.data[0];
//var t = data.time_id.substring(0, data.time_id.length - 1).split(",");
//var t_begin_idx = t[0];
//var t_end_idx = t[(t.length) - 1];
//var t_begin = $("#t_" + t_begin_idx).text().split('-')[0];
//var t_end = $("#t_" + t_end_idx).text().split('-')[1];
var r_name = $("#r_" + data.room_id).text();
var meeting_date_1 = data.meeting_date_1.split('T')[0];
var meeting_date_2 = data.meeting_date_2.split('T')[0];
//var book_time = data.book_time.replace(/T/g, " ");
//给表单赋值
$("#b_edit_topic").val(data.topic);
$("#b_edit_sort").val(data.sort);
$("#b_edit_name").val(data.applicant);
$("#b_edit_phone").val(data.phone);
$("#b_edit_room_name").val(r_name);
$("#b_edit_book_date_1").val(meeting_date_1);
$("#b_edit_book_date_2").datepicker('setDate', meeting_date_2);
$("#b_edit_time_1").val(data.time_1.substr(0, 5));
$("#b_edit_time_2").val(data.time_2.substr(0, 5));
$("#b_edit_room_id").val(data.room_id);
$("#b_edit_book_id").val(data.id);
//$("#modal_date").text("[ " + meeting_date_1 + " ~ " + meeting_date_2 + " ]");
//$("#modal_time").text("[ " + t_begin + " ~ " + t_end + " ]");
//$("#modal_cancel").attr("modal_book_id", data.id);
} else {
console.log(res)
alert(res.desc)
}
}
})
});
//取消预约
$("#modal_cancel").click(function () {
var book_id = $("#modal_cancel").attr("modal_book_id");
var r = confirm("亲,确定删除该预约信息吗?");
if (r == true) {
var url = '/book/cancelBook';
$.ajax({
type: 'POST',
cache: false,
url: url,
dataType: 'json',
data: { 'id': book_id },
error: function (res) {
console.log(res);
alert("error!");
},
success: function (res) {
var ret = JSON.parse(res.Data);
$('#modal_hide').click(); //hide弹窗
if (ret.code == "0") {
reload_tbl(global_user_date);//重构表格
} else {
alert(ret.desc);
}
}
})
}
});
//定时刷新
//var refresh_time = 0;
setInterval(function () {
//refresh_time++;
//console.log("refresh [ " + refresh_time + " ] 次");
init(global_user_date);//JQ初始化方法
}, 30000);//1秒=1000毫秒 1分钟=60*1000毫秒
//JQ初始化调用方法,获取所有信息
function init(query_date) {
$.ajax({
type: 'POST',
cache: false,
url: '/book/queryCurInfo',
dataType: 'json',
data: { 'query_date': query_date },
error: function (res) {
console.log(res);
alert("error!");
},
success: function (res) {
html_handle(res);
}
});
} //end - init()
//用户选择日期,重构表格,获取所有信息
function reload_tbl(query_date) {
//先清空,再赋值
for (var i = 0; i < global_arr_t_id.length; i++) {
$("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-selected");
$("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-none");
}
global_r_id = "";
global_arr_t_id = [];
$.ajax({
type: 'POST',
cache: false,
url: '/book/queryCurInfo',
dataType: 'json',
data: { 'query_date': query_date },
error: function (res) {
console.log(res);
alert("error!");
},
success: function (res) {
html_handle(res);
}
});
} //end - reload_tbl()
//html处理拼接
function html_handle(res) {
var room_list = res.data_0;
var time_list_old = res.data_1;
var time_list = new Array();
var book_list = res.data_2;
//处理展示时间格式
var time_begin, time_end, time_all;
for (var i = 0; i < time_list_old.length - 1; i++) {
time_begin = time_list_old[i].time_name.substr(0, time_list_old[i].time_name.length - 3);
time_end = time_list_old[i + 1].time_name.substr(0, time_list_old[i + 1].time_name.length - 3);
time_all = time_begin + "-" + time_end;//时间格式处理
time_list_old[i].time_name = trim(time_all);
time_list[i] = time_list_old[i];
}
//构建标签
var htmls = "";
//表头 - 会议室名称
htmls += "<thead>";
htmls += "<th style=\"width:110px;\"></th>";
for (var j = 0; j < room_list.length; j++) {
htmls += "<th id=\"r_" + room_list[j].room_id + "\" style=\"font-size:16px;\">" + room_list[j].room_name + "</th>";
}
htmls += "</thead>";
//会议室设备
htmls += "<tr><td style=\"width:110px;\">会议室设备</td>";
for (var j = 0; j < room_list.length; j++) {
htmls += "<td>" + room_list[j].media + "</td>";
}
htmls += "</tr>";
//会议室属性
htmls += "<tr><td style=\"width:110px;\">会议室属性</td>";
for (var j = 0; j < room_list.length; j++) {
htmls += "<td>" + "[ " + room_list[j].person_number + " ]人" + "</td>";
}
htmls += "</tr>";
//内容 - 左第一列是时间
//htmls += "<tbody>";
for (var i = 0; i < time_list.length; i++) {
htmls += "<tr>";
htmls += "<td id=\"t_" + time_list[i].id + "\" style=\"width:110px;\">" + time_list[i].time_name + "</td>";
for (var k = 0; k < room_list.length; k++) {
//id = (time_id-room_id), time_id = 时间ID, room_id = 会议室ID, book_id = 预定信息ID值
htmls += "<td class=\"td-none\" id =\"" + time_list[i].id + "-" + room_list[k].room_id + "\" time_id=\"" + time_list[i].id + "\" room_id=\"" + room_list[k].room_id + "\" book_id=\"\" onclick=\"td_click(this)\"></td>";
}
htmls += "</tr>";
}
//htmls += "</tbody>";
$("#table_1").html(htmls);
//预定信息处理
book_handle(book_list);
} //end - html_handle()
//预定信息处理
function book_handle(book_list) {
if (book_list.length > 0) { //判断有对应查询日期的预订记录
for (var i = 0; i < book_list.length; i++) {
var time_id = book_list[i].time_id; //格式 = 3,4,5,6,
var room_id = book_list[i].room_id;
var arr_time_id = time_id.substring(0, time_id.length - 1).split(",");
var time_id_half_index = Math.ceil(arr_time_id.length / 2) - 1; //取timeID的下标半值用于显示预订人信息
//var time_id_half = arr_time_id[time_id_half_index]; //需要显示预定信息的time_id值
var time_id_half = arr_time_id[0]; //需要显示预定信息的time_id值
var td_id_half = time_id_half + "-" + room_id;
var topic = book_list[i].topic;
var applicant = book_list[i].applicant;
var phone = book_list[i].phone;
var all_info = "";
if (arr_time_id.length > 1) {
//all_info = topic + " (" + applicant + " " + phone + ")";
//all_info = topic + "( " + applicant + " )";
all_info = topic + "( " + applicant + " )";
} else {
all_info = applicant;
}
$("#" + td_id_half).html(all_info);//赋值会议主题
for (var j = 0; j < arr_time_id.length; j++) {
var td_id = arr_time_id[j] + "-" + room_id;
if (j == 0) {
$("#" + td_id).removeClass("td-none");//移除class="td-none"的空样式
switch (book_list[i].sort) {
case 0:
$("#" + td_id).addClass("is-booked-0")//为ID为time_id_half的对象追加样式is-booked
break;
case 1:
$("#" + td_id).addClass("is-booked-1")//为ID为time_id_half的对象追加样式is-booked
break;
case 2:
$("#" + td_id).addClass("is-booked-2")//为ID为time_id_half的对象追加样式is-booked
break;
default:
$("#" + td_id).addClass("is-booked-other")//为ID为time_id_half的对象追加样式is-booked
break;
}
//此处给每个td设置对应的book_id = book_list[i].id(预定信息表的ID值)
$("#" + td_id).attr("book_id", book_list[i].id);
$("#" + td_id).attr("rowspan", arr_time_id.length);
} else {
$("#" + td_id).remove();
}
}
}
}
//设置样式
for (var i = 0; i < global_arr_t_id.length; i++) {
$("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-none");
$("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-selected");
}
} //end - book_handle()
//$(document).on('click', ':not(.tb-none)', function () {
// console.log("waibu click")
// return;
//})
//$(".td-none").click(function (event) {
// event.stopPropagation();
//});
//点击table外部,清空用户选中
//$(document).bind("click", function (e) {
// var target = $(e.target);
// if (target.closest("td").length == 0) { //点击的不是td
// //先清空,再赋值
// for (var i = 0; i < global_arr_t_id.length; i++) {
// console.log("++ " + global_arr_t_id[i])
// $("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-selected");
// $("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-none");
// }
// global_r_id = "";
// global_arr_t_id = [];
// }
//})
}); //end - Jquery
//table-td点击事件
function td_click(obj) {
var td_class = $(obj).attr("class");
if (td_class == "td-none" || td_class == "td-selected") {
var time_id = $(obj).attr("time_id");
var room_id = $(obj).attr("room_id");
//判断预定时间是否合法
var t_begin = $("#t_" + time_id).text().split("-")[1];
var time_begin = global_user_date + " " + t_begin + ":00";
var bool_time = checkEndTime(time_begin, now_time);
if (bool_time) {
$("#disappare").show().delay(1500).hide(300);
return;
}
get_click_td(time_id, room_id);
} else {
//先清空,再赋值
for (var i = 0; i < global_arr_t_id.length; i++) {
$("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-selected");
$("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-none");
}
global_r_id = "";
global_arr_t_id = [];
//已预订
var book_id = $(obj).attr("book_id");//预约记录表的ID值
query_book_by_id(book_id); //通过预定ID查询预约信息
}
//else if (td_class == "td-selected") {
// var time_id = $(obj).attr("time_id");
// var room_id = $(obj).attr("room_id");
// remove_click_td(time_id, room_id);
// }
}
var global_r_id;
var global_arr_t_id = new Array();
//获取用户点击的td
function get_click_td(t_id, r_id) {
if (global_r_id == "") { //首次点击
global_r_id = r_id;
global_arr_t_id.push(t_id);
$("#" + t_id + "-" + r_id).removeClass("td-none");
$("#" + t_id + "-" + r_id).addClass("td-selected");
} else { //非首次点击
if (r_id != global_r_id) { //会议室变了,相当于首次点击
//先清空,再赋值
for (var i = 0; i < global_arr_t_id.length; i++) {
$("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-selected");
$("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-none");
}
global_arr_t_id = [];
global_r_id = r_id;
global_arr_t_id.push(t_id);
$("#" + t_id + "-" + r_id).removeClass("td-none");
$("#" + t_id + "-" + r_id).addClass("td-selected");
} else { //真正的非首次点击
//排序
global_arr_t_id = arr_sort(global_arr_t_id);
var arr_max = parseInt(global_arr_t_id[(global_arr_t_id.length) - 1]);
var arr_min = parseInt(global_arr_t_id[0]);
var int_t_id = parseInt(t_id); //用户新选择的id'
var new_arr_t = [];
if (int_t_id < arr_min) {
for (int_t_id; int_t_id <= arr_max; int_t_id++) {
new_arr_t.push(int_t_id.toString());
}
} else {
for (arr_min; arr_min <= int_t_id; arr_min++) {
new_arr_t.push(arr_min.toString());
}
}
//取消样式
for (var i = 0; i < global_arr_t_id.length; i++) {
$("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-selected");
$("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-none");
}
//最新赋值
global_arr_t_id = new_arr_t;
//设置样式
for (var i = 0; i < global_arr_t_id.length; i++) {
$("#" + global_arr_t_id[i] + "-" + global_r_id).removeClass("td-none");
$("#" + global_arr_t_id[i] + "-" + global_r_id).addClass("td-selected");
}
}
}
}
//移除用户选中的td
function remove_click_td(t_id, r_id) {
$("#" + t_id + "-" + r_id).removeClass("td-selected");
$("#" + t_id + "-" + r_id).addClass("td-none");
if (global_arr_t_id.length == 1) { //只选中一个的情况,清空操作
global_r_id = "";
global_arr_t_id = [];
} else { //选中多个的情况,移除操作
//global_r_id 不进行操作
global_arr_t_id.remove(t_id);
}
}
//通过预定ID查询预约信息
function query_book_by_id(book_id) {
$.ajax({
type: 'POST',
cache: false,
url: '/book/queryBookInfoById',
dataType: 'json',
data: { 'book_id': book_id },
error: function (res) {
console.log(res)
},
success: function (res) {
if (res.code == 0) {
var data = res.data[0];
var t = data.time_id.substring(0, data.time_id.length - 1).split(",");
var t_begin_idx = t[0];
var t_end_idx = t[(t.length) - 1];
var t_begin = $("#t_" + t_begin_idx).text().split('-')[0];
var t_end = $("#t_" + t_end_idx).text().split('-')[1];
var r_name = $("#r_" + data.room_id).text();
var meeting_date_1 = data.meeting_date_1.split('T')[0];
var meeting_date_2 = data.meeting_date_2.split('T')[0];
var book_time = data.book_time.replace(/T/g, " ");
var check_date_time_1 = meeting_date_1 + " " + t_begin + ":00";
var check_date_time_2 = meeting_date_2 + " " + t_end + ":00";
var check_today = now_time.substr(0, 10) + " " + "00:00:00"; //今日零点
//var check_today = now_time.substr(0, 10) + " " + "23:59:59";
//var bool_time_1 = checkEndTime(check_date_time_1, check_today_end);
//var bool_time_2 = checkEndTime(check_date_time_2, check_today_end);
var bool_1 = checkEndTime(check_date_time_2, check_today); //前<后 == true
var bool_2 = checkEndTime(check_date_time_1, check_today); //前<后 == true
$("#modal_cancel").show(); //先默认打开
if (bool_1) { //会议结束日期 < 今日零点
$("#modal_footer").hide();
} else { //会议开始日期和当前日期不同
$("#modal_footer").show();
if (bool_2) { //会议开始日期 < 今日零点
$("#modal_cancel").hide();
}
}
//给表单赋值
$("#modal_room").text(r_name);
$("#modal_date").text("[ " + meeting_date_1 + " ~ " + meeting_date_2 + " ]");
$("#modal_time").text("[ " + t_begin + " ~ " + t_end + " ]");
$("#modal_topic").text(data.topic);
$("#modal_name").text(data.applicant);
$("#modal_phone").text(data.phone);
$("#modal_book_time").text(book_time);
$("#modal_cancel").attr("modal_book_id", data.id);
$("#motaikuang").click();
} else {
console.log(res)
}
}
})
}
//校验时间格式是否是00:00:00
function test(s) {
var regu = /^([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$/;
var re = new RegExp(regu);
if (re.test(s)) {
return true;
} else {
return false;
}
}
//获取当前系统日期(年-月-日)
function cur_date() {
var d = new Date(), str = '';
str += d.getFullYear(); //获取当前年份
var month = d.getMonth() + 1;
month = (month < 10 ? "0" + month : month);
str += "-" + month; //获取当前月份(0——11)
var date = d.getDate();
date = (date < 10 ? "0" + date : date);
str += "-" + date;
return str;
}
//获取当前系统time(时分秒)
function cur_time() {
var d = new Date(), str = '';
var hour = d.getHours();
hour = (hour < 10 ? "0" + hour : hour);
str += hour;
var minute = d.getMinutes();
minute = (minute < 10 ? "0" + minute : minute);
str += ":" + minute;
var second = d.getSeconds();
second = (second < 10 ? "0" + second : second);
str += ":" + second;
return str;
}
//获取当前系统时间(年月日时分秒)
function cur_dateTime() {
var d = new Date(), str = '';
//str += d.getFullYear() + '年'; //获取当前年份
//str += d.getMonth() + 1 + '月'; //获取当前月份(0——11)
//str += d.getDate() + '日';
//str += d.getHours() + '时';
//str += d.getMinutes() + '分';
//str += d.getSeconds() + '秒';
str += d.getFullYear(); //获取当前年份
var month = d.getMonth() + 1;
month = (month < 10 ? "0" + month : month);
str += "-" + month; //获取当前月份(0——11)
var date = d.getDate();
date = (date < 10 ? "0" + date : date);
str += "-" + date;
var hour = d.getHours();
hour = (hour < 10 ? "0" + hour : hour);
str += " " + hour;
var minute = d.getMinutes();
minute = (minute < 10 ? "0" + minute : minute);
str += ":" + minute;
var second = d.getSeconds();
second = (second < 10 ? "0" + second : second);
str += ":" + second;
return str;
}
//去左空格;
function ltrim(s) {
return s.replace(/(^\s*)/g, "");
}
//去右空格;
function rtrim(s) {
return s.replace(/(\s*$)/g, "");
}
//去左右空格;
function trim(s) {
return s.replace(/(^\s*)|(\s*$)/g, "");
}
//删除js数组指定元素arr.remove(val)
Array.prototype.remove = function (val) {
var index = this.indexOf(val);
if (index > -1) {
this.splice(index, 1);
}
};
//js数组去重
function uniqueArray(arr) {
const newArr = [];
arr.sort();
for (let i = 0; i < arr.length; i++) {
if (arr[i] !== arr[i + 1]) {
newArr.push(arr[i])
}
}
return newArr;
}
//js字符串数组排序-从小到大
function arr_sort(arr) {
for (var i = 0; i < arr.length; i++) {
arr[i] = parseInt(arr[i]);
}
var min;
for (var i = 0; i < arr.length; i++) {
for (var j = i; j < arr.length; j++) {
if (arr[i] > arr[j]) {
min = arr[j];
arr[j] = arr[i];
arr[i] = min;
}
}
}
for (var i = 0; i < arr.length; i++) {
arr[i] = arr[i].toString();
}
return arr;
}
//日期
function addDate(date, days) {
var d = new Date(date);
d.setDate(d.getDate() + days);
var month = d.getMonth() + 1;
var day = d.getDate();
if (month < 10) {
month = "0" + month;
}
if (day < 10) {
day = "0" + day;
}
var val = d.getFullYear() + "/" + month + "/" + day;
return val;
}
//比较时间大小,bb<aa(false) ;aa>=bb(true)
function checkEndTime(aa, bb) {
var startTime = aa;
var start = new Date(startTime.replace("-", "/").replace("-", "/"));
var endTime = bb;
var end = new Date(endTime.replace("-", "/").replace("-", "/"));
if (end < start) {
return false;
}
return true;
}
后台代码:
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
namespace board.Controllers
{
public class BookController : Controller
{
#region 全局变量
//数据库表名
static string tbl_room = "room_info";
static string tbl_time = "time_info";
static string tbl_booking = "booking_info";
static string tbl_userInfo = "user_info";
//全局变量值
public DataTable global_dt_room = queryRoomInfo();
public DataTable global_dt_time = queryTimeInfo();
#endregion 全局变量
// GET: Book
public ActionResult Login()
{
return View();
}
public ActionResult Index()
{
if (Session["Login"] != null && Session["Login"].ToString() == "OK")
{
return View();
}
else
{
return Redirect("/Book/login");
}
}
public ActionResult Show()
{
return View();
}
public ActionResult Room()
{
if (Session["Login"] != null && Session["Login"].ToString() == "OK")
{
return View();
}
else
{
return Redirect("/Book/login");
}
}
//页面初始化获取所有信息
public string queryCurInfo()
{
string query_date = Request.Form["query_date"]; //获取ajax提交值
//string query_date = "2019/05/09";
StringBuilder json = new StringBuilder();
DataTable dt_room = global_dt_room;
DataTable dt_time = global_dt_time;
DataTable dt_book = queryBookInfo(query_date);
var mylist = new List<DataTable>();
mylist.Add(dt_room); //添加第1个数值
mylist.Add(dt_time); //添加第2个数值
mylist.Add(dt_book); //添加第3个数值
string j = List2Json(mylist);
json.Append(j); //将room_info,time_info,book_info填入json中
return json.ToString();
}
//通过book_id查询预定信息
public string queryBookInfoById()
{
string book_id = Request.Form["book_id"]; //获取ajax提交值
DataTable dt = null;
string res = string.Empty;
try
{
//预定表
string sql = $@"SELECT * FROM {tbl_booking} WHERE id = '{book_id}' ";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
string JsonString = string.Empty, jsonStr = string.Empty;
JsonString = JsonConvert.SerializeObject(dt);
res = "{\"code\":0,\"data\":" + JsonString + "}";
}
catch (Exception ex)
{
res = "{\"code\":-1,\"data\":\"\"}";
log.logs.WriteLogC("[Book/queryBookInfoById] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 登录
/// </summary>
/// <returns></returns>
public JsonResult doLogin()
{
string res = "{\"code\":\"-1\",\"desc\":\"不OK\"}"; // 最终返回结果:0成功,-1失败
string userName = Request.Form["userName"];
string passWord = Request.Form["passWord"];
int result = 0;
try
{
//预定表
string sql = $@"SELECT NAME FROM {tbl_userInfo} WHERE NAME='{userName}' AND PASSWORD='{passWord}';";
var ds = DBHelper.SelectData(sql);
result = ds.Tables[0].Rows.Count;
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"登录成功\"}";
Session["Login"] = "OK";
}
else
{
res = "{\"code\":-1,\"desc\": \"登陆失败,用户名或密码错误\"}";
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"oops,数据库异常!请刷新重试。\"}";
log.logs.WriteLogC("[Book/doLogin] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return Json(new JsonResult()
{
Data = res
}, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 预定
/// </summary>
/// <returns></returns>
public JsonResult doBook()
{
string res = "{\"code\":\"-1\",\"desc\":\"不OK\"}"; // 最终返回结果:0成功,-1失败
string topic = Request.Form["b_topic"];
string applicant = Request.Form["b_name"];
string phone = Request.Form["b_phone"];
string room_id = Request.Form["b_room_id"];
string time_id = Request.Form["b_time_id"] + ",";
string meeting_date_1 = Request.Form["b_book_date_1"]; //会议日期
string meeting_date_2 = Request.Form["b_book_date_2"]; //会议日期
string time_1 = Request.Form["b_time_1"].ToString().Trim() + ":00";
string time_2 = Request.Form["b_time_2"].ToString().Trim() + ":00";
string sort = Request.Form["b_sort"]; //分类(客户or内部)
int result = 0;
try
{
//先查询该时间段是否已有预定记录
string qry_sql = $@"SELECT * FROM {tbl_booking} WHERE 1 = 1 AND room_id = '{room_id}' AND ((( meeting_date_1 BETWEEN '{meeting_date_1}' AND '{meeting_date_2}')AND (meeting_date_2 BETWEEN '{meeting_date_1}' AND '{meeting_date_2}'))OR ('{meeting_date_1}' BETWEEN meeting_date_1 AND meeting_date_2 )OR ('{meeting_date_2}' BETWEEN meeting_date_1 AND meeting_date_2 ))AND (( ( time_1 BETWEEN '{time_1}' AND '{time_2}')AND (time_2 BETWEEN '{time_1}' AND '{time_2}' ))OR (( '{time_1}' BETWEEN time_1 AND time_2 )AND ('{time_2}' BETWEEN time_1 AND time_2 ))OR ( ( '{time_1}' > time_1 AND '{time_1}' < time_2 ) OR ( '{time_2}' > time_1 AND '{time_2}' < time_2 ) ) )";
DataSet ds_qry = DBHelper.SelectData(qry_sql);
int qry_num;
if (ds_qry != null)
{
qry_num = ds_qry.Tables[0].Rows.Count;
if (qry_num > 0) //该区间已有记录
{
res = "{\"code\":-1,\"desc\": \"该时间区间内已有预约记录,无法重复预约!\"}";
}
else
{
//预定表
string sql = $@"INSERT INTO {tbl_booking}
(room_id,time_id,applicant,phone,topic,meeting_date_1,meeting_date_2,time_1,time_2,sort)
VALUES ('{room_id}','{time_id}','{applicant}','{phone}','{topic}','{meeting_date_1}','{meeting_date_2}','{time_1}','{time_2}','{sort}');";
result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"恭喜,预约成功~\"}";
}
else
{
res = "{\"code\":-1,\"desc\": \"oops,预约失败!请刷新重试。\"}";
}
}
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"oops,数据库操作失败!请刷新重试。\"}";
log.logs.WriteLogC("[Book/doBook] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return Json(new JsonResult()
{
Data = res
}, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 修改预订信息
/// </summary>
/// <returns></returns>
public JsonResult editBook()
{
string res = "{\"code\":\"-1\",\"desc\":\"不OK\"}"; // 最终返回结果:0成功,-1失败
string topic = Request.Form["b_edit_topic"];
string sort = Request.Form["b_edit_sort"]; //分类(客户or内部)
string applicant = Request.Form["b_edit_name"];
string phone = Request.Form["b_edit_phone"];
string room_id = Request.Form["b_edit_room_id"];
string meeting_date_1 = Request.Form["b_edit_book_date_1"]; //会议日期
string meeting_date_2 = Request.Form["b_edit_book_date_2"]; //会议日期
string time_1 = Request.Form["b_edit_time_1"].ToString().Trim();
string time_2 = Request.Form["b_edit_time_2"].ToString().Trim();
string book_id = Request.Form["b_edit_book_id"]; //预订信息的ID值
int result = 0;
try
{
//先查询该时间段是否已有预定记录
string qry_sql = $@"SELECT * FROM {tbl_booking} WHERE 1 = 1 AND id<>'{book_id}' AND room_id = '{room_id}' AND ((( meeting_date_1 BETWEEN '{meeting_date_1}' AND '{meeting_date_2}')AND (meeting_date_2 BETWEEN '{meeting_date_1}' AND '{meeting_date_2}'))OR ('{meeting_date_1}' BETWEEN meeting_date_1 AND meeting_date_2 )OR ('{meeting_date_2}' BETWEEN meeting_date_1 AND meeting_date_2 ))AND (( ( time_1 BETWEEN '{time_1}' AND '{time_2}')AND (time_2 BETWEEN '{time_1}' AND '{time_2}' ))OR (( '{time_1}' BETWEEN time_1 AND time_2 )AND ('{time_2}' BETWEEN time_1 AND time_2 ))OR ( ( '{time_1}' > time_1 AND '{time_1}' < time_2 ) OR ( '{time_2}' > time_1 AND '{time_2}' < time_2 ) ) )";
DataSet ds_qry = DBHelper.SelectData(qry_sql);
int qry_num;
if (ds_qry != null)
{
qry_num = ds_qry.Tables[0].Rows.Count;
if (qry_num > 0) //该区间已有记录
{
res = "{\"code\":-1,\"desc\": \"该时间区间内已有预约记录,无法重复预约!\"}";
}
else
{
//查询并拼接time_id
try
{
string qry_sql_timeId = $@"SELECT id FROM time_info WHERE time_name < '{time_2}' AND time_name >= '{time_1}' ORDER BY id";
DataSet ds_qry_timeId = DBHelper.SelectData(qry_sql_timeId);
DataTable dt_timeId = ds_qry_timeId.Tables[0];
string time_id = "";
for (int i = 0; i < dt_timeId.Rows.Count; i++)
{
time_id += dt_timeId.Rows[i]["id"].ToString() + ",";
}
//预定表
string sql = $@"UPDATE {tbl_booking} SET topic='{topic}',sort='{sort}',applicant='{applicant}',phone='{phone}',room_id='{room_id}',meeting_date_1='{meeting_date_1}',meeting_date_2='{meeting_date_2}',time_1='{time_1}',time_2='{time_2}',time_id='{time_id}' WHERE id='{book_id}'";
result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"修改成功!\"}";
}
else
{
res = "{\"code\":-1,\"desc\": \"oops,修改失败!请刷新重试。\"}";
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"oops,数据库操作失败!请刷新重试。\"}";
log.logs.WriteLogC("[Book/doBook 获取并拼接time_id出错 ] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
}
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"oops,数据库操作失败!请刷新重试。\"}";
log.logs.WriteLogC("[Book/doBook] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return Json(new JsonResult()
{
Data = res
}, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 取消预约
/// </summary>
/// <returns></returns>
public JsonResult cancelBook()
{
string res = "{\"code\":\"-1\",\"desc\":\"不OK\"}"; // 最终返回结果:0成功,-1失败
string id = Request.Form["id"].ToString();
int result = 0;
try
{
//预定表
string sql = $@"DELETE FROM {tbl_booking} WHERE id = '{id}';";
result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"操作成功哦~\"}";
}
else
{
res = "{\"code\":-1,\"desc\": \"oops,操作失败!请刷新重试。\"}";
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"oops , 数据库操作失败!请刷新重试。\"}";
log.logs.WriteLogC("[Book/cancelBook] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return Json(new JsonResult()
{
Data = res
}, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 查询指定日期的预定信息
/// </summary>
/// <param name="time"></param>
/// <returns></returns>
public DataTable queryBookInfo(string book_time)
{
DataTable dt = null;
//DateTime date = Convert.ToDateTime(time);
try
{
//预定表
string sql = $@"SELECT * FROM {tbl_booking} WHERE '{book_time}' between meeting_date_1 and meeting_date_2 ORDER BY room_id,time_id";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
}
catch (Exception ex)
{
log.logs.WriteLogC("[Book/queryBookInfo] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return dt;
}
/// <summary>
/// 查询会议室名称
/// </summary>
/// <returns></returns>
public static DataTable queryRoomInfo()
{
DataTable dt = null;
try
{
//会议室表
string sql = $"SELECT * FROM {tbl_room}";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
}
catch (Exception ex)
{
log.logs.WriteLogC("[Book/queryRoomInfo] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return dt;
}
/// <summary>
/// 查询会议时间节点
/// </summary>
/// <returns></returns>
public static DataTable queryTimeInfo()
{
DataTable dt = null;
try
{
//时间表
string sql = $"SELECT * FROM {tbl_time}";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
}
catch (Exception ex)
{
log.logs.WriteLogC("[Book/queryTimeInfo]: " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return dt;
}
/// <summary>
/// 查询会议室名称 - jsonString
/// </summary>
/// <returns></returns>
public string queryRoomInfo_json()
{
DataTable dt = null;
string res = string.Empty;
try
{
//会议室表
string sql = $"SELECT * FROM {tbl_room}";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
string JsonString = string.Empty, jsonStr = string.Empty;
JsonString = JsonConvert.SerializeObject(dt);
res = "{\"code\":0,\"desc\":\"OK\",\"data\":" + JsonString + "}";
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"查询异常!请退出重试~\",\"data\":\"\"}";
log.logs.WriteLogC("[Book/queryRoomInfo] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 查询会议时间节点 - jsonString
/// </summary>
/// <returns></returns>
public string queryTimeInfo_json()
{
DataTable dt = null;
string res = string.Empty;
try
{
//时间表
string sql = $"SELECT * FROM {tbl_time}";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
string JsonString = string.Empty, jsonStr = string.Empty;
JsonString = JsonConvert.SerializeObject(dt);
res = "{\"code\":1,\"data\":" + JsonString + "}";
}
catch (Exception ex)
{
res = "{\"code\":0,\"data\":\"\"}";
log.logs.WriteLogC("[Book/queryTimeInfo]: " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 通过room_id查询会议室信息 - jsonString
/// </summary>
/// <returns></returns>
public string queryRoomInfoById()
{
string room_id = Request.Form["r_id"];
DataTable dt = null;
string res = string.Empty;
try
{
//会议室表
string sql = $"SELECT * FROM {tbl_room} where room_id = '{room_id}'";
DataSet ds = DBHelper.SelectData(sql);
if (ds != null)
{
dt = ds.Tables[0];
}
string JsonString = string.Empty, jsonStr = string.Empty;
JsonString = JsonConvert.SerializeObject(dt);
res = "{\"code\":0,\"desc\":\"OK\",\"data\":" + JsonString + "}";
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"查询异常!请退出重试~\",\"data\":\"\"}";
log.logs.WriteLogC("[Book/queryRoomInfoById] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 编辑会议室信息 - jsonString
/// </summary>
/// <returns></returns>
public string roomEdit()
{
string room_id = Request.Form["edit_room_id"].ToString().Trim();
string room_name = Request.Form["edit_room_name"].ToString().Trim();
string person_number = Request.Form["edit_person_number"].ToString().Trim();
string media = Request.Form["edit_media"].ToString().Trim();
string res = string.Empty;
try
{
//会议室表
string sql = $"UPDATE {tbl_room} SET room_name = '{room_name}',person_number='{person_number}',media='{media}' WHERE room_id={room_id};";
int result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"操作成功~\"}";
}
else
{
res = "{\"code\":-1,\"desc\": \"oops,操作失败!请刷新重试。\"}";
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"查询异常!请退出重试~\",\"data\":\"\"}";
log.logs.WriteLogC("[Book/roomEdit] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 新增会议室信息 - jsonString
/// </summary>
/// <returns></returns>
public string roomAdd()
{
string room_name = Request.Form["add_room_name"].ToString().Trim();
string person_number = Request.Form["add_person_number"].ToString().Trim();
string media = Request.Form["add_media"].ToString().Trim();
string res = string.Empty;
try
{
//会议室表
string sql = $"INSERT INTO {tbl_room} (room_name,person_number,media) VALUES ('{room_name}','{person_number}','{media}')";
int result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"操作成功~\"}";
}
else
{
res = "{\"code\":-1,\"desc\": \"oops,操作失败!请刷新重试。\"}";
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"查询异常!请退出重试~\",\"data\":\"\"}";
log.logs.WriteLogC("[Book/roomAdd] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 删除会议室信息 - jsonString
/// </summary>
/// <returns></returns>
public string roomDel()
{
string id = Request.Form["room_id"].ToString().Trim();
string res = string.Empty;
try
{
//会议室表
string sql = $@"DELETE FROM {tbl_room} WHERE room_id = '{id}';";
int result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
res = "{\"code\":0,\"desc\": \"操作成功~\"}";
}
else
{
res = "{\"code\":-1,\"desc\": \"oops,操作失败!请刷新重试。\"}";
}
}
catch (Exception ex)
{
res = "{\"code\":-1,\"desc\":\"查询异常!请退出重试~\",\"data\":\"\"}";
log.logs.WriteLogC("[Book/roomDel] " + ex.ToString());
throw new Exception("查询异常,请退出重试~");
}
return res;
}
/// <summary>
/// 将多个DataTable转为一个Json格式中
/// </summary>
/// <param name="dt_name">DataTable表名</param>
/// <param name="dt">DataTable表值</param>
/// <returns></returns>
public static string List2Json(List<DataTable> mylist)
{
StringBuilder json = new StringBuilder();
json.Append("{");
for (int i = 0; i < mylist.Count; i++)
{
json.Append("\"");
json.Append("data_" + i);
json.Append("\":");
json.Append(JsonConvert.SerializeObject(mylist[i]));
if (i != mylist.Count - 1)
{
json.Append(",");
}
}
json.Append("}");
return json.ToString();
}
}
}
其中数据库用的MySQL,增删改查封装如下:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
//using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Text;
using Newtonsoft.Json;
using System.Collections;
using MySql.Data.MySqlClient;
namespace board
{
public static class DBHelper
{
private static readonly string connString = ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString;
#region 增删改操作 ExecuteNonQuery
public static int ExecuteNonQuery(string sql, params MySqlParameter[] pms)
{
using (MySqlConnection con = new MySqlConnection(connString))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
#endregion
#region 查询,返回单个值 ExecuteScalar
public static object ExecuteScalar(string sql, params MySqlParameter[] pms)
{
using (MySqlConnection con = new MySqlConnection(connString))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
#endregion
#region 查询,返回多行多列 ExecuteReader
public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] pms)
{
using (MySqlConnection con = new MySqlConnection(connString))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
try
{
con.Open();
// System.Data.CommandBehavior.CloseConnection此枚举参数,表示将来使用完毕MySqlDataReader之后,在关闭reader的同时,在MySqlDataReader内部会将关联的connection对象也关闭掉。
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch // 如果查询发生异常,也保证con连接能够关闭
{
con.Close();
con.Dispose();
throw;
}
}
}
}
#endregion
#region 查询,返回DataSet
public static DataSet SelectData(string sql)
{
DataSet ds = new DataSet();
try
{
using (MySqlConnection conn = new MySqlConnection(connString))
{
//conn.Open();
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(ds);
return ds;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return ds;
}
#endregion
#region 查询,返回hashtable
public static string GetHashTable(string sql)
{
Hashtable ht = new Hashtable();
try
{
//string sql_gettotal = "select COUNT(*) from WQY_PRODUCT";
DataTable dt_data = DBHelper.SelectData(sql).Tables[0];
//DataTable dt_total = DBHelper.SelectData(sql_gettotal).Tables[0];
//int total = int.Parse(dt_total.Rows[0][0].ToString());
//ht.Add("total", total);
ht.Add("rows", dt_data);
return JsonConvert.SerializeObject(ht);
}
catch (Exception ex)
{
//ht.Add("total", 0);
ht.Add("rows", null);
Console.Write(ex);
string a = JsonConvert.SerializeObject(ht);
return JsonConvert.SerializeObject(ht);
json字符串转为json对象
//JObject jo = (JObject)JsonConvert.DeserializeObject(myInfo);
}
}
#endregion
}
}
日志封装如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Web;
// log.logs.WriteLogC("写入日志的内容");
namespace log
{
static class logs
{
/** <summary>
/// 写入日志文件
/// </summary>
/// <param name="input"></param>
public static void WriteLogC(string input)
{
/**/
///指定日志文件的目录
//string fname = Directory.GetCurrentDirectory() + "\\LogFile.txt";
//文件对应的文件夹路径,当前路径的上一级目录新建一个logs文件夹
//string furl = String.Format(@"{0}\..\logs", Directory.GetCurrentDirectory()); //winform程序
string furl = HttpContext.Current.Server.MapPath("\\logs\\"); //web项目
//文件完整路径
string fname = $@"{furl}LogFile.txt";
/**/
///定义文件信息对象
//Directory.Delete(furl, true);//删除logs文件夹以及文件夹中的子目录,文件
// 文件夹
if (Directory.Exists(furl) == false)//如果不存在就创建logs文件夹
{
Directory.CreateDirectory(furl);
}
// 文件
FileInfo finfo = new FileInfo(fname);
if (!finfo.Exists)// 如果不存在就创建LogFile.txt文件
{
FileStream fs;
fs = File.Create(fname);
fs.Close();
finfo = new FileInfo(fname);
}
/**/
///判断文件是否存在以及是否大于2K
if (finfo.Length > 1024 * 1024 * 10)
{
/**/
///文件超过10MB则重命名
File.Move(fname, $@"{furl}LogFile_{DateTime.Now.TimeOfDay}.txt");
/**/
///删除该文件
//finfo.Delete();
}
//finfo.AppendText();
/**/
///创建只写文件流
using (FileStream fs = finfo.OpenWrite())
{
/**/
///根据上面创建的文件流创建写数据流
StreamWriter w = new StreamWriter(fs);
/**/
///设置写数据流的起始位置为文件流的末尾
w.BaseStream.Seek(0, SeekOrigin.End);
/**/
///写入“Log Entry : ”
w.Write("\n\rLog Entry : ");
/**/
///写入当前系统时间并换行
w.Write("{0} {1} \r\n", DateTime.Now.ToLongTimeString(),
DateTime.Now.ToLongDateString());
/**/
///写入日志内容并换行
w.Write(input + "\r\n");
/**/
///写入------------------------------------“并换行
w.Write("------------------------------------\r\n");
/**/
///清空缓冲区内容,并把缓冲区内容写入基础流
w.Flush();
/**/
///关闭写数据流
w.Close();
}
}
}
}
3.数据库
采用MySQL数据库,用sqlyog进行访问。
1、数据表结构
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 8.0.15 : Database - meetingroom
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`meetingroom` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `meetingroom`;
/*Table structure for table `booking_info` */
DROP TABLE IF EXISTS `booking_info`;
CREATE TABLE `booking_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`room_id` int(4) NOT NULL COMMENT '预定会议室的room_id',
`time_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`applicant` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '申请人',
`topic` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '会议主题,会议标题',
`meeting_date_1` date NOT NULL COMMENT '会议开始日期',
`meeting_date_2` date NOT NULL COMMENT '会议结束日期',
`time_1` time NOT NULL,
`time_2` time NOT NULL,
`phone` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '申请人联系方式',
`book_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
`sort` int(1) DEFAULT '1' COMMENT '0:内部;1:客户;2:研发;',
UNIQUE KEY `room_id` (`room_id`,`meeting_date_1`,`time_id`) COMMENT 'room_id,time_id,meeting_date,3字段联合唯一,防止预定重合',
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8;
/*Table structure for table `room_info` */
DROP TABLE IF EXISTS `room_info`;
CREATE TABLE `room_info` (
`room_id` int(13) NOT NULL AUTO_INCREMENT COMMENT '会议室ID',
`room_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '会议室名称',
`person_number` tinyint(4) DEFAULT NULL COMMENT '可容纳人数',
`media` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '多媒体设备(投影仪、电视等)',
`useable` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '1' COMMENT '会议室是否可用(0不可用,1可用)',
KEY `room_id` (`room_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
/*Table structure for table `test` */
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(15) NOT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Table structure for table `time_info` */
DROP TABLE IF EXISTS `time_info`;
CREATE TABLE `time_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time_name` time NOT NULL COMMENT '时间值',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
/*Table structure for table `user_info` */
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`power` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户权限,1-用户权限,2-管理员权限,3-超级权限',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2、时间段的数据
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 8.0.15 : Database - meetingroom
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`meetingroom` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `meetingroom`;
/*Table structure for table `time_info` */
DROP TABLE IF EXISTS `time_info`;
CREATE TABLE `time_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time_name` time NOT NULL COMMENT '时间值',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
/*Data for the table `time_info` */
insert into `time_info`(`id`,`time_name`) values
(1,'08:00:00'),
(2,'08:30:00'),
(3,'09:00:00'),
(4,'09:30:00'),
(5,'10:00:00'),
(6,'10:30:00'),
(7,'11:00:00'),
(8,'11:30:00'),
(9,'12:00:00'),
(10,'12:30:00'),
(11,'13:00:00'),
(12,'13:30:00'),
(13,'14:00:00'),
(14,'14:30:00'),
(15,'15:00:00'),
(16,'15:30:00'),
(17,'16:00:00'),
(18,'16:30:00'),
(19,'17:00:00'),
(20,'17:30:00'),
(21,'18:00:00'),
(22,'18:30:00'),
(23,'19:00:00');
/*Table structure for table `user_info` */
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`power` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户权限,1-用户权限,2-管理员权限,3-超级权限',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `user_info` */
insert into `user_info`(`id`,`name`,`password`,`addTime`,`power`) values
(1,'admin','admin123','2019-04-26 14:18:28','3'),
(2,'hale','hale123','2019-04-26 14:18:57','1');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
3、其他测试数据