前端设计与开发实验项目2

一、项目内容

1.用户可注册登录网站，非注册用户不可登录查看数据
2.用户注册、登录、查询等操作计入数据库中的日志
3.实现查询词支持布尔表达式
4.爬虫数据查询结果列表支持分页和排序
5.用Echarts实现3个及以上的数据分析图表，展示在网站中

二、登录注册

1.登录

index.js文件中login部分

router.route('/login')
    .get(function(req, res) {
        if(req.session.islogin){
            res.locals.islogin=req.session.islogin;
        }

        if(req.cookies.islogin){
            req.session.islogin=req.cookies.islogin;
        }
        res.render('login', { title: '用户登录' ,test:res.locals.islogin});
    })
    .post(function(req, res) {
        client=usr.connect();
        result=null;
        usr.selectFun(client,req.body.username, function (result) {
            if(result[0]===undefined){
                res.send('没有该用户');
            }else{
                if(result[0].password===req.body.password){
                    req.session.islogin=req.body.username;
                    res.locals.islogin=req.session.islogin;
                    res.cookie('islogin',res.locals.islogin,{maxAge:60000});
                    res.redirect('/home');
                }else
                {
                    res.redirect('/login');
                }
               }
        });
    });

login.ejs文件部分：网页前端部分，只有登录后才能进行搜索和查看图表

<%- include header %>
<div class="container">
  <form class="col-sm-offset-4 col-sm-4 form-horizontal" role="form" method="post">
      <fieldset>
      <% if(locals.islogin) { %>
         用户：<h3> <%= test %></h3>已经登陆。<br>
         <a class="btn" href="/logout">登出</a>
          <a class="btn" href="/search">按关键词搜索</a>
          <a class="btn" href="/search">查看Echarts统计图表</a>
          <% } else{ %>
          <div class="form-group">
              <label class="col-sm-3 control-label" for="username">用户名</label>
              <div class="col-sm-9">
                  <input type="text" class="form-control" id="username" name="username" placeholder="用户名" required>
              </div>
          </div>
          <div class="form-group">
              <label class="col-sm-3 control-label" for="password">密码</label>
              <div class="col-sm-9">
                  <input type="password" class="form-control" id="password" name="password" placeholder="密码" required>
              </div>
          </div>
          <div class="form-group">
              <div class="col-sm-offset-3 col-sm-9">
                  <button type="submit" class="btn btn-primary">登录</button>
              </div>
          </div>
          <% } %>
      </fieldset>
  </form>
</div>
<%- include footer %>

对应function

function connectServer(){

  var client=mysql.createConnection({
      host:'localhost',
      user:'root',
      password:'123456',
      database:'crawler_data'
  })

  return client;
}


function  selectFun(client,username,callback){
  //client为一个mysql连接对象
  client.query('select password from users where username="'+username+'"',function(err,results,fields){
      if(err) throw err;

      callback(results);
  });
}

2.注册

js文件注册中部分

router.route('/reg')
  .get(function(req,res){
      res.render('reg',{title:'注册'});
  })
  .post(function(req,res) {
      client = usr.connect();

      usr.insertFun(client,req.body.username ,req.body.password2, function (err) {
            if(err)throw err;
            res.send('注册成功');
      });
  });

ejs文件部分

<%- include header %>
<div class="container">
    <form class=" col-sm-offset-4 col-sm-4 form-horizontal" role="form" method="post">
        <fieldset>

            <div class="form-group">
                <label class="col-sm-3 control-label" for="username">用户名</label>
                <div class="col-sm-9">
                    <input type="text" class="form-control" id="username" name="username" placeholder="用户名" required>
                </div>
            </div>
            <div class="form-group">
                <label class="col-sm-3 control-label" for="password2">密码</label>
                <div class="col-sm-9">
                    <input type="password" class="form-control" id="password2" name="password2" placeholder="密码" required>
                </div>
            </div>
            <div class="form-group">
                <div class="col-sm-offset-3 col-sm-9">
                    <button type="submit" class="btn btn-primary">注册</button>
                </div>
            </div>
        </fieldset>
    </form>
</div>
<%- include footer %>

对应function

function insertFun(client , username , password,callback){
  client.query('insert into users value(?,?)', [username, password], function(err,result){
      if( err ){
          console.log( "error:" + err.message);
          return err;
      }
        callback(err);
  });
}

3.登出

在登录后才能看到登出按钮

router.get('/logout', function(req, res) {
    res.clearCookie('islogin');
    req.session.destroy();
    res.redirect('/');
});

三、记录注册、登录、查询

增加log的function,并且修改index.js中的对应部分

function LoginLogFun(client , username ,callback){
    client.query('insert into Loginlog values(?,now())', [username], function(err,result){
        if( err ){
            console.log( "error:" + err.message);
            return err;
        }
          callback(err);
    });
}
function RegLogFun(client , username ,callback){
    client.query('insert into Reglog values(?,now())', [username], function(err,result){
        if( err ){
            console.log( "error:" + err.message);
            return err;
        }
          callback(err);
    });
}
function SearchLogFun(client , username ,searchword, callback){
    client.query('insert into Searchlog values(?,?,now())', [username,searchword], function(err,result){
        if( err ){
            console.log( "error:" + err.message);
            return err;
        }
          callback(err);
    });
}

以Loginlog为例，可在数据库中查到记录

四、实现查询词支持布尔表达式

对接收到的关键词进行判断，看其中是否包括and or，如果包括，就拆出关键词，执行不同的sql语句
关键词1and关键词2

select count(*) from news where title like '%keyword1%' and title like  '%keyword2%';

关键词1or关键词2

select count(*) from news where title like '%keyword1%' or title like  '%keyword2%';

五、实现查询结果的分页和排序

在index.js中实现

let query = {
        name: userName,
        pageIndex: parseInt(req.query.page) || 1,
        pageSize: 4
      }
      Post.getPage(query, (err, posts, sumResults) => {
        if (err) {
          req.flash('error', err);
          return res.redirect('/');
        }
        posts.forEach((post) => {
          post.reprint_info = JSON.parse( post.reprint_info);
        });
        let total = sumResults[0].sum || 0;
        res.render('user', {
          title: users[0].user_name,
          posts: posts,
          user: req.session.user,
          pageIndex: query.pageIndex,
          isFirstPage: (query.pageIndex - 1) == 0,
          isLastPage: ((query.pageIndex - 1) * query.pageSize + posts.length) == total,
          success: req.flash('success').toString(),
          error: req.flash('error').toString()
        });

六、Echarts数据分析图表

根据实验课代码略加修改