Eve-Project/app-node/case-psql/queryuser-account-group.js

// /**
//  * 安装pg-promise库，并引入
//  * npm init
//  * npm i -S pg-promise
//  */
//
// const http = require('http');
//
// const initOptions = {/* initialization options */};
// const pgp = require('pg-promise')(initOptions);
// const db = pgp('postgres://web3:666@dbo.fmode.cn:5432/dev');
//
// async function analysisStudentAgeByGroup(account, password) {
//   account = account.toString();
//   let sql = `SELECT *
//              FROM "LjUser"
//              WHERE "account" = $1
//                AND "password" = $2;`;
//
//   let result = await db.any(sql, [account, password]);
//   return result;
// }
//
// const server = http.createServer(async (req, res) => {
//   if (req.url === '/user/login/get') {
//     try {
//       if (req.method === 'POST') {
//         let body = '';
//         req.on('data', (chunk) => {
//           body += chunk.toString();
//         });
//
//         req.on('end', async () => {
//           const {account, password} = JSON.parse(body);
//           const result = await analysisStudentAgeByGroup(account, password);
//           res.writeHead(200, {'Content-Type': 'application/json'});
//           res.end(JSON.stringify(result));
//         });
//       } else {
//         res.writeHead(400, {'Content-Type': 'text/plain'});
//         res.end('Bad Request');
//       }
//     } catch (error) {
//       res.writeHead(500, {'Content-Type': 'text/plain'});
//       res.end('Internal Server Error');
//     }
//   } else {
//     res.writeHead(404, {'Content-Type': 'text/plain'});
//     res.end('Not Found');
//   }
// });
//
// server.listen(3000, () => {
//   console.log('Server is running on port 3000');
// });

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const {Pool} = require('pg');
const {max} = require("pg/lib/defaults.js");

const pool = new Pool({
  user: 'web3',
  password: '666',
  host: 'dbo.fmode.cn',
  port: 5432,
  database: 'dev'
});


//解决跨域的问题
app.use((req, res, next) => {
  res.setHeader('Access-Control-Allow-Origin', '*'); // 允许所有地址访问
  res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');
  res.setHeader('Access-Control-Allow-Headers', 'Content-Type, Authorization');
  res.setHeader('Access-Control-Allow-Credentials', 'true');
  next();
});

// 跨域请求
const cors = require('cors');
app.use(cors({
  origin: '*'
}));

app.use(bodyParser.json());


let userList = []

var sessionMap = {}
app.get('/user/login', async (req, res) => {
  console.log(req.query);
  if (!req.query || !req.query.account || !req.query.password) {
    res.status(400).send('Invalid request query parameters');
    return;
  }
  const {account, password} = req.query;

  try {
    const client = await pool.connect();
    const result = await client.query('SELECT account,password,gender,skills,birthday,"isFirstRegister",stuno FROM "LjUser" WHERE account = $1 AND password = $2', [account, password]);
    console.log(result)

    // client.release();

    userList = result.rows.map(row => {
      return {
        user: row.account,
        password: row.password,
        gender: row.gender,
        skills: row.skills,
        age: row.age,
        isFirstRegister: row.isFirstRegister,
        stuno: row.stuno,
      };
    });

    console.log(userList[0].stuno)

    if (result.rows.length > 0) {
      // 登录成功
      console.log(userList[0].stuno)
      let isToken = await createToken(userList[0].stuno);
      console.log(isToken)
      if (isToken) {
        res.json(true); // 返回布尔类型的值 true
      } else {
        res.json(isToken.toString())
      }
    } else {
      // 登录失败
      res.json("账号或者密码错误"); // 返回布尔类型的值 false
    }
  } catch (error) {
    console.log('Failed to query the database:', error);
    res.status(500).send('Failed to query the database.');
  }
});


//注册接口
// app.post('/user/insert', async (req, res) => {
//   try {
//     const client = await pool.connect();
//     const {value1, value2} = req.body; // 从请求体中获取要插入的值
//
//     // 执行插入操作
//     await client.query('INSERT INTO your_table (column1, column2) VALUES ($1, $2)', [value1, value2]);
//     if ()
//
//       res.status(200).json({message: 'Insert successful'});
//   } catch (error) {
//     console.error('Error inserting data:', error);
//     res.status(500).json({error: 'An error occurred'});
//   }
// })
app.get('/user/register', async (req, res) => {
  if (!req.query.account) {
    res.json(false)
  }
  const {account} = req.query;

  try {
    const client = await pool.connect();
    const result = await client.query('SELECT account FROM "LjUser" WHERE account = $1 ;', [account]);
    if (result.rows[0] !== undefined) {
      res.json(false);
    } else {
      res.json(true)
    }
  } catch (error) {
    console.log('Failed to query the database:', error);
    res.status(500).send('Failed to query the database.');
  }
})

app.get('/user/stuno', async (req, res) => {
  try {
    const client = await pool.connect();

    // 执行查询操作获取最大的 stuno
    const query = 'SELECT MAX(CAST(stuno AS INT)) as max_stuno FROM "LjUser"';
    const result = await client.query(query);
    const maxStuno = parseInt(result.rows[0].max_stuno);
    console.log(maxStuno)

    // 计算新的 stuno
    const newStuno = (maxStuno ? maxStuno + 1 : 1).toString().padStart(9, '0');
    console.log(newStuno)


    // 返回新的 stuno
    res.json({stuno: newStuno});
  } catch (error) {
    console.log('Failed to fetch max stuno from the database:', error);
    res.status(500).send('Failed to fetch max stuno from the database.');
  }
});

app.put('/user/register', async (req, res) => {
  console.log(req.body.stuno);
  console.log(req.body.email);
  console.log(req.body.objectId);
  const {account, email, password, birthday, skills, availableBalance, stuno, objectId} = req.body;

  try {
    const client = await pool.connect();
    // 执行插入操作
    const query = 'INSERT INTO "LjUser" (account, email, password, birthday, skills, "availableBalance", stuno, "objectId") VALUES ($1, $2, $3, $4, $5, $6, $7, $8)';
    const values = [account, email, password, birthday, skills, availableBalance, stuno, objectId];
    await client.query(query, values);

    // 返回成功消息
    res.json({success: true});
  } catch (error) {
    console.log('Failed to insert data into the database:', error);
    res.status(500).send('Failed to insert data into the database.');
  }
});


async function createToken(stuno) {
  console.log(stuno)

  const client = await pool.connect();
  try {
    // 处理查询结果
    return client.query('SELECT * FROM "LjUserToken" WHERE userno = $1', [stuno]);
  } catch (error) {
    console.error('数据库查询错误:', error);
    return false;
  }

  console.log(result)
  if (result.rows[0].stuno && result.rows[0].token) {
    console.log(1)
    if (result.rows[0].token - (new Date().getTime() < 0)) {
      userList[0].token = btoa(new Date().getTime())
      await client.query('UPDATE "LjUserToken" SET userno = $1, token = $2;', [stuno.toString(), userList[0].token])
    }
    userList[0].token = result.rows[0].token;
    return true
  } else {
    userList[0].token = btoa(new Date().getTime())
    console.log(userList[0].token)
    try {
      console.log(userList[0].token)
      await client.query('INSERT INTO "LjUserToken" (userno, token) VALUES ($1, $2);', [stuno, userList[0].token])
    } catch (e) {
      console.log(e)
    }
    userList[0].expirationTime = new Date().setTime(new Date().getTime() + 1000 * 100);
    sessionMap[userList[0].user] = userList[0];
    console.log(userList[0], sessionMap)
    return true;
  }
}

app.post('/user/login', async (req, res) => {
  console.log(req.body)
  if (!req.body || !req.body.account || !req.body.password) {
    res.status(400).send('Invalid request body');
    return;
  }
  const {account, password} = req.body;
  // try {
  //   const client = await pool.connect();
  //   const isToken = await client.query('SELECT token FROM "LjUserToken" WHERE userno = $1', [account]);
  //   if (isToken) {
  //     const user = await client.query('SELECT account,gender,name,email,skills,isFirstRegister FROM "LjUser" WHERE account = $1', [account]);
  //   } else {
  //     let token = createToken();
  //   }
  //
  //
  // } catch (e) {
  //
  // }


  try {
    const client = await pool.connect();
    const result = await client.query('SELECT account,gender,name,email,skills,"isFirstRegister" FROM "LjUser" WHERE account = $1 AND password = $2', [account, password]);
    console.log(result)

    if (result.rows[0] !== undefined) {
      console.log(result.rows[0])
      res.json(result.rows[0]); // 返回第一行数据

    } else {
      // 登录失败
      res.status(401).send('Invalid account or password');
    }
  } catch (error) {
    console.log('Failed to query the database:', error);
    res.status(500).send('Failed to query the database.');
  }
});

app.listen(23000, () => {
  console.log('Express server is running on port 23000');
});