유저리스트의 정보들을 보여주고, 그룹이름과 권한이름은 수정 가능한 리스트만 보여준후 수정 가능
패스워드 초기화는 아이콘 클릭시 비밀번호 초기화 가능
* VS CODE, CHAT GPT4.0, gibhub Copilot 활용
index.js
import { Pool } from 'pg';
import MybatisMapper from 'mybatis-mapper';
const pool = new Pool({
user: 'postgres',
host: 'xxx.xxx.xxx.xx',
database: 'xxxxxx',
password: 'xxxx',
port: xxxx,
});
console.log("index check");
MybatisMapper.createMapper(['src/pages/api/mybatisMapper.xml']);
export default async function handler(req, res) {
if (req.method === "GET") {
try {
const connection = await pool.connect();
const sql = MybatisMapper.getStatement("namespace1", "user_list");
const { rows } = await connection.query(sql);
console.log('user List:', rows);
connection.release();
return res.status(200).json(rows);
} catch (error) {
console.error('Error executing GET query:', error);
res.status(500).json({ error: 'Internal server error' });
}
} else if (req.method === "POST") {
try {
console.log("index.js POST");
const connection = await pool.connect();
const sql = MybatisMapper.getStatement("namespace1", "user_list");
const { rows } = await connection.query(sql);
const changes = req.body;
await Promise.all(
changes.map(async (change) => {
const params = {
user_id: change.key,
};
// console.log("value is ",changes);
Object.entries(change.data).forEach(([name, value]) => {
params[name] = value;
});
console.log("sql is ",params);
// console.log("sql is ",params);
let sql;
switch (change.type) {
case "update":
console.log("POST_UPDATE",params);
let auth_code_name = params?.auth_code_name;
let group_code_name = params?.group_code_name;
console.log("index check",auth_code_name,group_code_name);
if (auth_code_name != null || group_code_name != null) {
// Run the UPDATE statement
const sql = MybatisMapper.getStatement("namespace1", "updateServerInfo", params);
console.log(sql);
const result = await connection.query(sql);
} else {
// Skip the UPDATE statement, handle accordingly
console.log('Both auth_code_name and group_code_name are null, skipping update.');
}
break;
case "insert":
console.log("POST_INSERT",params);
sql = MybatisMapper.getStatement("namespace1", "insertServerInfo", params);
break;
case "remove":
console.log("POST_DEL",params);
sql = MybatisMapper.getStatement("namespace1", "deleteServerInfo", params);
break;
default:
break;
}
if (sql) {
await connection.query(sql); // client.query 대신 connection.query 사용
}
})
);
console.log('Changes applied:', changes);
connection.release();
return res.status(200).json(rows);
} catch (error) {
console.error('Error executing POST query:', error);
res.status(500).json({ error: 'Internal server error' });
}
} else {
return res.status(404).send("Not Found");
}
}
user_list/index.js
import { Pool } from 'pg';
import MybatisMapper from 'mybatis-mapper';
const pool = new Pool({
user: 'postgres',
host: '192.168.100.24',
database: 'bescon',
password: '1234',
port: 5432,
});
console.log("index check");
MybatisMapper.createMapper(['src/pages/api/mybatisMapper.xml']);
export default async function handler(req, res) {
if (req.method === "GET") {
try {
const connection = await pool.connect();
const sql = MybatisMapper.getStatement("namespace1", "user_list");
const { rows } = await connection.query(sql);
console.log('user List:', rows);
connection.release();
return res.status(200).json(rows);
} catch (error) {
console.error('Error executing GET query:', error);
res.status(500).json({ error: 'Internal server error' });
}
} else if (req.method === "POST") {
try {
console.log("index.js POST");
const connection = await pool.connect();
const sql = MybatisMapper.getStatement("namespace1", "user_list");
const { rows } = await connection.query(sql);
const changes = req.body;
await Promise.all(
changes.map(async (change) => {
const params = {
user_id: change.key,
};
// console.log("value is ",changes);
Object.entries(change.data).forEach(([name, value]) => {
params[name] = value;
});
console.log("sql is ",params);
// console.log("sql is ",params);
let sql;
switch (change.type) {
case "update":
console.log("POST_UPDATE",params);
let auth_code_name = params?.auth_code_name;
let group_code_name = params?.group_code_name;
console.log("index check",auth_code_name,group_code_name);
if (auth_code_name != null || group_code_name != null) {
// Run the UPDATE statement
const sql = MybatisMapper.getStatement("namespace1", "updateServerInfo", params);
console.log(sql);
const result = await connection.query(sql);
} else {
// Skip the UPDATE statement, handle accordingly
console.log('Both auth_code_name and group_code_name are null, skipping update.');
}
break;
case "insert":
console.log("POST_INSERT",params);
sql = MybatisMapper.getStatement("namespace1", "insertServerInfo", params);
break;
case "remove":
console.log("POST_DEL",params);
sql = MybatisMapper.getStatement("namespace1", "deleteServerInfo", params);
break;
default:
break;
}
if (sql) {
await connection.query(sql); // client.query 대신 connection.query 사용
}
})
);
console.log('Changes applied:', changes);
connection.release();
return res.status(200).json(rows);
} catch (error) {
console.error('Error executing POST query:', error);
res.status(500).json({ error: 'Internal server error' });
}
} else {
return res.status(404).send("Not Found");
}
}
udpate_password/index.js
import { Pool } from 'pg';
import MybatisMapper from 'mybatis-mapper';
const pool = new Pool({
//connectionString: "postgres://default:wjDaO1Gd0BhP@ep-holy-recipe-99329247-pooler.us-east-1.postgres.vercel-storage.com:5432/verceldb" + "?sslmode=require"
user: 'postgres', // 사용자 이름
host: 'xxx.xxx.xxx.xxx', // 호스트 주소
database: 'xxxxxx', // 데이터베이스 이름
password: 'xxxx', // 비밀번호
port: xxxx, // 포트 번호
});
MybatisMapper.createMapper(['src/pages/api/mybatisMapper.xml']);
export default async function handler(req, res) {
if (req.method === 'POST') {
try {
const { user_id, new_password } = req.body;
if (!user_id || !new_password) {
return res.status(400).json({ message: "Missing user_id or new_password" });
}
const connection = await pool.connect();
// console.log("user_id: " + user_id);
// Update the SQL statement call
const param = { user_id: user_id, new_password: new_password };
const sql = MybatisMapper.getStatement("namespace1", "password_list", param);
// Assuming the SQL is to update the password
const result = await connection.query(sql);
connection.release();
// Check result and send response accordingly
if (result.rowCount > 0) {
return res.status(200).json({ message: 'Password updated successfully, original password is "1234"' });
} else {
return res.status(404).json({ message: 'User not found' });
}
} catch (error) {
console.error('Error updating password:', error);
res.status(500).json({ message: 'Internal server error' });
}
} else {
console.log("user_id FAIL" + user_id);
// Handle any non-POST requests
res.setHeader('Allow', ['POST']);
res.status(405).end(`Method ${req.method} Not Allowed`);
}
}
Mybatismapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="namespace1">
<select id="agent_list">
SELECT agent_id as id,port,owner,active,java_version FROM bc_agent_info
</select>
<select id="login_check">
SELECT user_name FROM bc_user_info where user_id = #{user_id}
</select>
<select id="user_list">
SELECT a.user_id, a.user_name, b.code_name AS group_code_name, c.code_name AS auth_code_name,TO_CHAR(a.last_login_time, 'YYYY-MM-DD HH24:MI:SS') as last_login_time
FROM BC_USER_INFO a
JOIN BC_CODE_INFO b ON a.group_id = b.code_id AND b.code_type = 'GROUP'
JOIN BC_CODE_INFO c ON a.auth_id = c.code_id AND c.code_type = 'AUTH'
</select>
<select id="auth_code_list">
select distinct code_name as auth_code_name from BC_CODE_INFO where code_type = 'AUTH'
</select>
<select id="group_code_list">
select distinct code_name as group_code_name from BC_CODE_INFO where code_type = 'GROUP'
</select>
<select id="alarm_current">
select ROW_NUMBER() OVER (ORDER BY b.agent_id) AS ID,
d.code_name site_name,
e.code_name line_name,
c.host_name,
c.ip_address,
b.owner,
a.object_name,
a.event_name,
a.value ,
a.status ,
a.update_time
from bc_alarm_current a
left join
bc_agent_info b
on a.agent_id = b.agent_id
left join
bc_server_info c
on b.server_id = c.server_id
left join
(select * from bc_code_info where code_type = 'SITE') d
on c.site_id = d.code_id
left join
(select * from bc_code_info where code_type = 'LINE') e
on c.line_id = e.code_id
where c.host_name is not null
--and a.status in ('FAIL','WARNNING')
</select>
<select id="server_list">
SELECT server_name,event_name,warn_min,warn_max,fail_min,fail_max FROM bc_alarm_conf
</select>
<select id="server_list2">
select a.server_id as id,
a.host_name,
a.serial_number,
a.manager_user_id,
b.user_name as user,
a.cpu_clock
from bc_server_info a
left join bc_user_info b
on a.manager_user_id = b.user_id
</select>
<select id="server_list3">
select a.server_id as id,
a.host_name,
a.os_name,
a.memory,
a.disk,
a.ip_address,
a.manager_user_id,
b.user_name,
a.parent_id
from bc_server_info a
left join bc_user_info b
on a.manager_user_id = b.user_id
</select>
<update id="updateServerInfo">
UPDATE bc_user_info
<set>
<if test="group_code_name != null">
group_id = (SELECT max(code_id) FROM bc_code_info WHERE code_type = 'GROUP' AND code_name = #{group_code_name}),
</if>
<if test="auth_code_name != null">
auth_id = (SELECT max(code_id) FROM bc_code_info WHERE code_type = 'AUTH' AND code_name = #{auth_code_name}),
</if>
<if test="group_code_name == null and auth_code_name == null">
user_id = user_id,
</if>
</set>
WHERE user_id = #{user_id};
</update>
<update id="password_list">
update bc_user_info set password='1234' where user_id = #{user_id};
</update>
<insert id="insertServerInfo">
insert into bc_user_info (user_id, user_name, password, create_time, group_id, auth_id)
values (#{user_id}, #{user_name}, '1234', sysdate, #{group_code_name}, #{auth_code_name})
</insert>
<delete id="deleteServerInfo">
delete from bc_user_info where user_id = #{user_id};
</delete>
</mapper>
'개발' 카테고리의 다른 글
| react 잘 모르는것들 정리 (1) | 2024.01.22 |
|---|---|
| react 개발 위한 유용한 사이트 (0) | 2024.01.17 |
| json (0) | 2024.01.12 |
| 개발 개념 잡기 (0) | 2024.01.12 |
| docker기반의 nexus설치 (0) | 2024.01.10 |
