risadmin_prod
/
prod124njac
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
prod124njac/prod124njac-back-b/authsec_node/Backend/api/cns.controller.js

2759 lines
76 KiB
JavaScript
Raw Permalink Normal View History

base_project
2024-09-18 04:14:45 +00:00
const { genSaltSync, hashSync, compareSync } = require("bcrypt")
const { sign } = require("jsonwebtoken")
const otpGenerator = require("otp-generator")
const nodemailer = require("nodemailer")
const bcrypt = require("bcrypt")
const { pool, db } = require("../config/database")
const fs = require("fs")
const path = require("path")
const generateRandomHash = require("../Functions/generateRandomHash")
const { execArgv, env } = require("process")
const {
buildExtensionByFormCode,
} = require("../Functions/buildExtensionByFormCode")
const { exec } = require("child_process")
const { v4: uuidv4 } = require("uuid")
const multer = require("multer")
const { promisify } = require("util")
const readFileAsync = promisify(require("fs").readFile)
const transporter = nodemailer.createTransport({
service: "gmail", // e.g., 'Gmail'
host: process.env.NODEMAILER_HOST,
port: process.env.NODEMAILER_PORT,
secure: false,
auth: {
user: process.env.NODEMAILER_USER,
pass: process.env.NODEMAILER_PASSWORD,
},
})
async function dateAndTime() {
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
return dateTime
}
async function executeQuery(sql, values) {
const connection = await db.getConnection()
// console.log("connection..",connection);
try {
const [rows] = await connection.execute(sql, values)
return rows
} finally {
connection.release()
}
}
const login = async (req, res) => {
const body = req.body
pool.query(
"SELECT * FROM sec_users WHERE email = ?",
[body.email],
(err, results) => {
if (err) {
console.error(err)
return res.json({
success: 0,
data: "Error during login",
})
}
if (!results || results.length === 0) {
return res.json({
success: 0,
data: "Invalid email or password",
})
}
const isValidPassword = compareSync(body.password, results[0].user_passw)
if (isValidPassword) {
// console.log(results[0].usr_grp)
const jsontoken = sign(results[0], "secret", {
algorithm: "HS256", // Use a shorter signing algorithm
expiresIn: "5h", // Set a shorter expiration time if appropriate
})
return res.json({
operationStatus: "SUCCESS",
operationMessage: "Login Success",
item: {
token: jsontoken,
userId: results[0].user_id,
fullname: results[0].full_name,
username: results[0].user_name,
email: results[0].email,
firstName: results[0].full_name,
roles: ["ProjectManager", "Developer", "ROLE ADMIN"],
},
})
} else {
res.json({
success: 0,
data: "Invalid email or password",
})
}
}
)
}
// logout
const logout = (req, res) => {
res.json({
success: 1,
message: "Logout successful",
})
}
// reset password
const sendEmailForResetPassword = async (req, res) => {
const userEmail = req.body.email
// console.log(userEmail)
try {
const [userRows] = await executeQuery(
"SELECT * FROM sec_users WHERE email = ?",
[userEmail]
)
// console.log(userRows.email)
const user = userRows
if (user) {
const email = user.email
const subject = "Password Reset"
const hash = generateRandomHash()
const link =
"http://" +
process.env.LOCAL_HOST +
":" +
process.env.FRONTEND_PORT_9191 +
"/#/forgotresetpassword/" +
hash
// Store the reset token in the database
const resetTokenData = {
expiry_date: calculateExpiryDate(), // You need to implement a function to calculate the expiry date
token: hash,
user_user_id: user.user_id,
}
// Assuming you have a database model for password_reset_token
await createPasswordResetToken(resetTokenData)
// req.user.resetHash = hash
const mailOptions = {
from: process.env.NODEMAILER_USER,
to: userEmail,
subject: subject,
text: `${link}`,
}
transporter.sendMail(mailOptions, (error) => {
if (error) {
console.log(error)
return res.status(500).json({ message: "Error sending email" })
}
return res.status(200).json({ message: "Email sent successfully" })
})
} else {
return res.status(400).json({ message: "User not found" })
}
} catch (error) {
console.error(error)
return res
.status(500)
.json({ message: "Error sending email for password reset" })
}
}
const createPasswordResetToken = async (resetTokenData) => {
try {
// Obtain the maximum ID from the password_reset_token table
const sql = `SELECT MAX(id) AS maxId FROM password_reset_token`
const [record] = await executeQuery(sql)
// console.log(record)
// Increment the maxId by 1 to get the new primary key value
const newId = record.maxId + 1
// Insert the record into the password_reset_token table with the new ID
const insertSql = `
INSERT INTO password_reset_token (id, expiry_date, token, user_user_id)
VALUES (?, ?, ?, ?)
`
const results = await executeQuery(insertSql, [
newId,
resetTokenData.expiry_date,
resetTokenData.token,
resetTokenData.user_user_id,
])
return results
} catch (error) {
console.error(error)
throw error
}
}
// Function to calculate expiry date (you may customize this as per your requirements)
const calculateExpiryDate = () => {
const expiryDate = new Date()
expiryDate.setDate(expiryDate.getDate() + 1) // Set expiry date to 1 day from now
return expiryDate
}
const savePassword = async (req, res) => {
const token = req.params.token
// console.log(token)
const { newPassword, confirmPassword } = req.body
try {
// Fetch user by token from the database
const [rows] = await executeQuery(
"SELECT * FROM password_reset_token WHERE token = ?",
[token]
)
if (rows.length === 0) {
return res.status(400).json({ message: "Token expired" })
}
// console.log(rows)
const user_id = rows.user_user_id
// Fetch user by email from the database
const [userRows] = await executeQuery(
"SELECT * FROM sec_users WHERE user_id = ?",
[user_id]
)
// console.log(userRows)
if (userRows.length === 0) {
return res.status(400).json({ message: "User not found" })
}
const a = userRows
if (newPassword === confirmPassword) {
// Update user password
const hashedPassword = await bcrypt.hash(newPassword, 10)
a.user_passw = hashedPassword
a.pwd_changed_cnt = a.pwd_changed_cnt ? a.pwd_changed_cnt + 1 : 1
a.last_pwd_changed_date = new Date()
a.change_passw = newPassword
// Fetch the maximum id from password_reset_token
// const sql = `SELECT MAX(id) AS maxId FROM password_reset_token`
// const [record] = await executeQuery(sql)
// // console.log(record)
// // Increment the maxId by 1 to get the new primary key value
// const newId = record.maxId + 1
// Save the updated user details to the database with the new id
await executeQuery(
"UPDATE sec_users SET user_passw = ?, pwd_changed_cnt = ?, last_pwd_changed_date = ?, change_passw = ? WHERE user_id = ?",
[
a.user_passw,
a.pwd_changed_cnt,
a.last_pwd_changed_date,
a.change_passw,
a.user_id,
]
)
// Delete the password reset token
await executeQuery("DELETE FROM password_reset_token WHERE token = ?", [
token,
])
return res.status(200).json(a)
}
return res
.status(400)
.json({ message: "Password and confirm password do not match" })
} catch (error) {
console.error(error)
return res.status(500).json({ message: "Internal Server Error" })
}
}
const resetPassword = async (req, res) => {
try {
const { oldPassword, newPassword, confirmPassword } = req.body
console.log(oldPassword, newPassword, confirmPassword)
// Check if newPassword and confirmPassword match
if (newPassword !== confirmPassword) {
return res
.status(400)
.json({ message: "New password and confirm password do not match" })
}
const user = req.user
// console.log(user)
if (!user) {
return res.status(400).json({ message: "User not found" })
}
// Validate old password
const isValidOldPassword = compareSync(oldPassword, user.user_passw)
if (!isValidOldPassword) {
return res.status(400).json({ message: "Invalid old password" })
}
// Update the password in the database
await updatePasswordAndUserData(user.user_id, newPassword)
// Optionally, remove the hash from your local storage or mark it as used
// removeHashFromStorage(hash);
return res.status(200).json({ message: "Password reset successful" })
} catch (error) {
console.error(error)
return res.status(500).json({ message: "Error resetting password" })
}
}
// Update Password Controller
const updatePasswordAndUserData = async (userId, newPassword) => {
return new Promise(async (resolve, reject) => {
try {
// Retrieve user details
const [userRows] = await executeQuery(
"SELECT * FROM sec_users WHERE user_id = ?",
[userId]
)
if (userRows.length === 0) {
reject({ message: "User not found" })
return
}
// Hash the new password
const hashedPassword = await bcrypt.hash(newPassword, 10)
// Update user details
const updatedUser = {
user_passw: hashedPassword,
pwd_changed_cnt: userRows.pwd_changed_cnt
? userRows.pwd_changed_cnt + 1
: 1,
last_pwd_changed_date: new Date(),
change_passw: newPassword,
user_id: userId,
}
// Update password and user data in a single query
await executeQuery(
"UPDATE sec_users SET user_passw = ?, pwd_changed_cnt = ?, last_pwd_changed_date = ?, change_passw = ? WHERE user_id = ?",
[
updatedUser.user_passw,
updatedUser.pwd_changed_cnt,
updatedUser.last_pwd_changed_date,
updatedUser.change_passw,
updatedUser.user_id,
]
)
resolve({ message: "Password updated successfully" })
} catch (error) {
console.error(error)
reject({ message: "Internal Server Error" })
}
})
}
// Controllers for User grp maintenance
const getUsersUserGrpMaintenance = (req, res) => {
// pool.query(`SELECT * FROM sec_user_group`, [], (error, results, fields) => {
// if (error) {
// console.log(error)
// return res.json({
// success: 0,
// message: "Table is empty",
// })
// }
// return res.status(200).json(results)
// })
// }
pool.query(
`SELECT * FROM sec_user_group`,
[],
(error, results, fields) => {
if (error) {
console.log(error);
return res.json({
success: 0,
message: "Table is empty",
});
}
// Map the results to the desired format
const formattedResults = results.map((item) => {
// Convert updatedate to a timestamp in milliseconds and a human-readable format
const updatedateTimestamp = item.updatedate ? new Date(item.updatedate).getTime() : null;
const updateDateFormated = item.updatedate
? new Date(item.updatedate).toLocaleString("en-GB", {
day: "2-digit",
month: "short",
year: "numeric",
hour: "2-digit",
minute: "2-digit",
second: "2-digit",
})
: null;
// Re-map the fields
return {
usrGrp: item.usr_grp,
groupName: item.group_name,
groupDesc: item.group_desc,
createby: item.createby,
createdate: item.createdate,
updatedate: updatedateTimestamp, // Converted to timestamp in milliseconds
updateby: item.updateby,
status: item.status,
groupLevel: item.group_level,
createDateFormated: item.createdate, // Keeping it as null or format if needed
updateDateFormated: updateDateFormated, // Human-readable date format
};
});
// Return the newly formatted result
return res.status(200).json(formattedResults);
}
);
};
const getUsersByUserIdUserGrpMaintenance = (req, res) => {
const id = req.params.id
pool.query(
`SELECT * FROM sec_user_group WHERE usr_grp = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
if (!results) {
return res.json({
success: 0,
message: "Record not found",
})
}
return res.status(200).json(results)
}
)
}
const updateUsersUserGrpMaintenance = (req, res) => {
const body = req.body
const id = req.params
pool.query(
`UPDATE INTO sec_user_group SET createby = ?, createdate = ?, group_desc = ?, group_level = ?, group_name = ?, status = ?, updateby = ? WHERE usr_grp = ?`,
[
body.createby,
body.createdate,
body.group_desc,
body.group_level,
body.password,
body.group_name,
body.status,
body.updateby,
id,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to update user",
})
}
return res.json({
success: 1,
data: "updated succesfully",
})
}
)
}
const createUsersUserGrpMaintenance = (req, res) => {
const body = req.body
pool.query(
`INSERT sec_user_group SET createby = ?,createdate = ?,group_desc = ?,group_level = ?,group_name = ?, status = ?, updateby = ? usr_grp = ?`,
[
body.createby,
body.createdate,
body.groupDesc,
body.group_level,
body.password,
body.groupName,
body.status,
body.updateby,
body.usrGrp,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to update user",
})
}
return res.json({
success: 1,
data: "updated succesfully",
})
}
)
}
const deleteUsersUserGrpMaintenance = (req, res) => {
const id = req.params.id
pool.query(
`delete from registration where id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
return res.json({
success: 1,
data: "user deleted succesfully",
})
}
)
}
// Controllers for Menu maintainence
const getUsersMenuMaintainence = (req, res) => {
pool.query(`SELECT * FROM sec_menu_det`, [], (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
})
}
const getUsersByUserIdMenuMaintainence = (req, res) => {
const id = req.params.id;
pool.query(
`SELECT * FROM sec_grp_menu_access WHERE usr_grp = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error);
return res.json({
success: 0,
message: "Table is empty",
});
}
if (!results || results.length === 0) {
return res.json({
success: 0,
message: "Record not found",
});
}
// Map the results to the desired format
const formattedResults = results.map((item) => {
// Convert created_at and updated_at to timestamps in milliseconds
const createdAtTimestamp = item.created_at ? new Date(item.created_at).getTime() : null;
const updatedAtTimestamp = item.updated_at ? new Date(item.updated_at).getTime() : null;
return {
usrGrp: item.usr_grp, // Adding usrGrp
menuItemId: item.menu_item_id,
mexport: item.mexport,
menuId: item.menu_id,
createby: item.createby,
createdAt: createdAtTimestamp, // Convert to timestamp
updateby: item.updateby,
updatedAt: updatedAtTimestamp, // Convert to timestamp
isdisable: item.isdisable,
itemSeq: item.item_seq,
menuItemDesc: item.menu_item_desc,
status: item.status,
moduleName: item.module_name,
main_menu_action_name: item.main_menu_action_name,
main_menu_icon_name: item.main_menu_icon_name,
subMenus: [], // Empty array as requested
grpid: null, // Placeholder for grpid, assuming no equivalent field in the database
gmenuid: null, // Placeholder for gmenuid, assuming no equivalent field in the database
mcreate: item.m_create,
mdelete: item.m_delete,
mquery: item.m_query,
mvisible: item.m_visible,
medit: item.m_edit,
};
});
return res.status(200).json(formattedResults);
}
);
};
const updateUsersMenuMaintainence = (req, res) => {
const body = req.body
const { id } = req.params
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
pool.query(
`UPDATE sec_menu_det SET item_seq = ?, main_menu_action_name = ?, main_menu_icon_name = ?, menu_id = ?, menu_item_desc = ?, module_name = ?, status = ?, updated_at = ? WHERE menu_item_id = ?`,
[
body.itemSeq,
body.main_menu_action_name,
body.main_menu_icon_name,
body.menuId,
body.menuItemDesc,
body.moduleName,
body.status,
dateTime,
id,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to update user",
})
}
return res.json({
success: 1,
data: "updated succesfully",
})
}
)
}
const createUsersMenuMaintainence = (req, res) => {
const body = req.body
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
pool.query(
`INSERT sec_menu_det SET menu_item_id = ?, item_seq = ?, main_menu_action_name = ?, main_menu_icon_name = ?, menu_id = ?, menu_item_desc = ?, module_name = ?, status = ?, created_at = ?, updated_at = ?`,
[
body.menuItemId,
body.itemSeq,
body.main_menu_action_name,
body.main_menu_icon_name,
body.menuId,
body.menuItemDesc,
body.moduleName,
body.status,
body.createdAt,
dateTime,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to create user",
})
}
return res.json({
success: 1,
data: "created succesfully",
})
}
)
}
const deleteUsersMenuMaintainence = (req, res) => {
const id = req.params.id
pool.query(
`DELETE FROM sec_menu_det WHERE menu_item_id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
return res.json({
success: 1,
data: "user deleted succesfully",
})
}
)
}
// Controller for Sub Menu maintainence
const getUsersSubMenuMaintainence = (req, res) => { }
// by rajeev
// const getUsersByIdSubMenuMaintainence = (req, res) => {
// const id = req.params.id
// pool.query(
// `SELECT * FROM sec_menu_det WHERE menu_id = ? ORDER BY item_seq ASC`,
// [id],
// (error, results, fields) => {
// if (error) {
// console.log(error)
// return res.json({
// success: 0,
// message: "Table is empty",
// })
// }
// if (!results) {
// return res.json({
// success: 0,
// message: "Record not found",
// })
// }
// // console.log(results)
// return res.status(200).json(results)
// }
// )
// }
// by Gk
const getUsersByIdSubMenuMaintainence = (req, res) => {
const id = req.params.id;
pool.query(
`SELECT * FROM sec_menu_det WHERE menu_id = ? ORDER BY item_seq ASC`,
[id],
(error, results) => {
if (error) {
console.log(error);
return res.json({
success: 0,
message: "Table is empty",
});
}
if (!results || results.length === 0) {
return res.json({
success: 0,
message: "Record not found",
});
}
// Map the results to the desired format
const formattedResults = results.map((item) => {
return {
createdAt: new Date(item.created_at).getTime(),
updatedAt: new Date(item.updated_at).getTime(),
menuItemId: item.menu_item_id,
itemSeq: item.item_seq,
menuItemDesc: item.menu_item_desc,
status: item.status,
menuId: item.menu_id,
moduleName: item.module_name,
main_menu_action_name: item.main_menu_action_name,
main_menu_icon_name: item.main_menu_icon_name,
subMenus: [], // Empty array as required
};
});
return res.status(200).json(formattedResults);
}
);
};
const updateUsersSubMenuMaintainence = (req, res) => { }
const createUsersSubMenuMaintainence = (req, res) => { }
const deleteUsersSubMenuMaintainence = (req, res) => { }
// Controller for MENU ACCESS CONTROL
const getUsersMenuAccessControl = (req, res) => {
pool.query(
`SELECT * FROM sec_grp_menu_access`,
[],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const getUsersByMenuAccessControl = (req, res) => { }
const createUsersMenuAccessControl = async (req, res) => {
const { id } = req.params
const sql = `SELECT * FROM sec_menu_det WHERE menu_item_id = ?`
const values = [id]
try {
const [record] = await executeQuery(sql, values)
if (!record) {
return res.status(404).json({ message: "Record not found" })
}
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
const sqlIn = `INSERT INTO sec_grp_menu_access SET menu_item_id = ?, usr_grp = 41, created_at = ?, main_menu_action_name = ?, main_menu_icon_name = ?, menu_id = ?, menu_item_desc = ?, module_name = ?, status = ?, item_seq = ?, updated_at = ?, isdisable = 'true', m_create = 'true', m_delete = 'true', m_edit = 'true' , m_query = 'true', m_visible = 'true'`
const valuesIn = [
record.menu_item_id,
record.created_at,
record.main_menu_action_name,
record.main_menu_icon_name,
record.menu_id,
record.menu_item_desc,
record.module_name,
record.status,
record.item_seq,
dateTime,
]
try {
const [recordIn] = await executeQuery(sqlIn, valuesIn)
} catch (errorIn) {
console.error("inside", errorIn)
}
} catch (error) {
console.error(error)
res.status(500).json({ message: "error fetching record Outside" })
}
return res.json({
success: 1,
data: "created succesfully",
})
}
// Controller for User maintenance
const getUserMaintainence = (req, res) => {
pool.query(`SELECT * FROM sec_users`, [], (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
})
}
const getByIdUserMaintainence = (req, res) => {
const { id } = req.params
pool.query(
`SELECT * FROM sec_users WHERE user_id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const getLoggedInUser = async (req, res) => {
const user = req.user
res.json(user)
}
const updateUserMaintainence = async (req, res) => {
try {
const updatedFields = {
full_name: req.body.fullName || null,
pronounce: req.body.pronounce || null,
// role: req.body.role || null,
// department: req.body.department || null,
about: req.body.about || null,
email: req.body.email || null, // Add email field
// Add other fields to update as needed
}
// console.log(updatedFields)
const user = req.user // Assuming req.user contains the user details, adjust accordingly
// Filter out fields with null values
const filteredFields = Object.fromEntries(
Object.entries(updatedFields).filter(([_, value]) => value !== null)
)
// If no fields are provided for update, return a response
if (Object.keys(filteredFields).length === 0) {
return res.json({ success: 1, message: "No fields to update" })
}
// Construct the SQL query dynamically based on the provided filteredFields
const updateFieldsSql = Object.keys(filteredFields)
.map((key) => `${key} = ?`)
.join(", ")
// Add the user_id to the filteredFields
filteredFields.user_id = user.user_id
// Construct the parameters array for the query
const queryParams = Object.values(filteredFields)
// Update the specified non-null fields, including the updateDate, in sec_users
await executeQuery(
`UPDATE sec_users SET ${updateFieldsSql} WHERE user_id = ?`,
queryParams
)
res.json({ success: 1, message: "User profile updated successfully" })
} catch (error) {
console.error(error)
res.status(500).json({ success: 0, message: "Internal Server Error" })
}
}
const uploadPath = path.join(process.cwd(), "uploaded-picture")
// Create the 'uploaded-picture' directory if it doesn't exist
if (!fs.existsSync(uploadPath)) {
fs.mkdirSync(uploadPath)
}
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, uploadPath)
},
filename: (req, file, cb) => {
const userId = req.user.user_id
const ext = path.extname(file.originalname)
const fileName = `profile-pic-${userId}-${uuidv4()}${ext}`
cb(null, fileName)
},
})
const upload = multer({ storage: storage })
const uploadProfilePhoto = (req, res) => {
try {
const userId = req.user.user_id
const usernameFolder = req.user.user_name
const userFolderPath = path.join(
process.cwd(),
"uploaded-picture",
usernameFolder
)
if (!fs.existsSync(userFolderPath)) {
fs.mkdirSync(userFolderPath)
}
const currentDate = new Date()
.toISOString()
.replace(/:/g, "-")
.substring(0, 19)
const fileNewName = `profile-pic-${userId}-${currentDate}.png`
const destinationPath = path.join(
userFolderPath,
fileNewName.replace(/ /g, "_")
)
const updateQuery = "UPDATE sec_users SET photo_name = ? WHERE user_id = ?"
pool.query(updateQuery, [fileNewName, userId], (error, results) => {
if (error) {
console.error(error)
return res
.status(500)
.json({ success: false, message: "Internal Server Error" })
}
// Use req.file.path to get the temporary path of the uploaded file
// console.log(req)
// console.log("Request body:", req.body) // Log the entire request body
// console.log("Request file:", req.file.path) // Log the uploaded file information
// fs.renameSync(req.file.path, destinationPath)
fs.writeFileSync(destinationPath, req.file.buffer)
res.json({
success: true,
message: "Profile picture uploaded successfully",
})
})
} catch (error) {
console.error(error)
res.status(500).json({ success: false, message: "Internal Server Error" })
}
}
const retrieveProfilePhoto = async (req, res) => {
try {
const userId = req.user.user_id.toString()
const usernameFolder = req.user.user_name
// Retrieve the file name from the database
const selectQuery = "SELECT photo_name FROM sec_users WHERE user_id = ?"
const [results] = await executeQuery(selectQuery, [userId])
// console.log(results)
if (results.length === 0 || !results.photo_name) {
return res.status(404).json({
success: false,
message: "Profile picture not found. First try uploading one...!!",
})
}
const fileName = results.photo_name
const filePath = path.join(
process.cwd(),
"uploaded-picture",
usernameFolder,
fileName
)
const imageBuffer = await readFileAsync(filePath)
// Convert the image buffer to a base64-encoded string
const encodeBase64 = imageBuffer.toString("base64")
const ext = path.extname(filePath).substring(1)
// Construct the data URL
const dataURL = `data:image/${ext};base64,${encodeBase64}`
// Send the data URL as a JSON response
res.json({
success: true,
message: "Profile picture retrieved successfully",
image: dataURL,
})
} catch (error) {
console.error(error)
res.status(500).json({ success: false, message: "Internal Server Error" })
}
}
const createUserMaintainence = (req, res) => {
const body = req.body
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
pool.query(
`INSERT sec_users SET user_id = ?, change_passw = ?, email = ?, full_name = ?, is_blocked = ?, mob_no = ?, user_passw = ?, user_name = ?, usr_grp_id = ?, account_id = ?, usr_grp = ?, createdate = ?`,
[
body.user_id,
body.is_complete,
body.change_passw,
body.email,
body.full_name,
body.is_blocked,
body.mob_no,
body.user_passw,
body.user_name,
body.usr_grp_id,
body.account_id,
body.usr_grp,
dateTime,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to create user",
})
}
return res.json({
success: 1,
data: "created succesfully",
})
}
)
}
const deleteUserMaintainence = (req, res) => {
const id = req.params.id
pool.query(
`DELETE FROM sec_menu_det WHERE menu_item_id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
return res.json({
success: 1,
data: "user deleted succesfully",
})
}
)
}
// By Rajeev
// const loadMenuByUser = async (req, res) => {
// try {
// // Simulate getting the logged-in user's group (usrGrp)
// const usrgrp = req.user.usr_grp // Replace this with your actual logic to get the user's group
// // Find root menu items where menu_id is 0
// let sql = `SELECT * FROM sec_grp_menu_access WHERE menu_id = 0 AND status= 'Enable' AND usr_grp = ${usrgrp} ORDER BY item_seq`
// const root = await executeQuery(sql)
// const renameFields = (obj) => {
// const newObj = {}
// for (const key in obj) {
// switch (key) {
// case "usr_grp":
// newObj["usrGrp"] = obj[key]
// break
// case "menu_item_id":
// newObj["menuItemId"] = obj[key]
// break
// case "item_seq":
// newObj["itemSec"] = obj[key]
// break
// case "menu_id":
// newObj["menuId"] = obj[key]
// break
// case "menu_item_desc":
// newObj["menuItemDesc"] = obj[key]
// break
// case "m_create":
// newObj["mcreate"] = obj[key]
// break
// case "m_delete":
// newObj["mdelete"] = obj[key]
// break
// case "m_edit":
// newObj["medit"] = obj[key]
// break
// case "m_query":
// newObj["mquery"] = obj[key]
// break
// case "m_visible":
// newObj["mvisible"] = obj[key]
// break
// default:
// newObj[key] = obj[key]
// }
// }
// return newObj
// }
// const renameSubmenuFields = (submenu) => {
// const newSubmenu = {}
// for (const key in submenu) {
// switch (key) {
// case "usr_grp":
// newSubmenu["usrGrp"] = submenu[key]
// break
// case "menu_item_id":
// newSubmenu["menuItemId"] = submenu[key]
// break
// case "item_seq":
// newSubmenu["itemSec"] = submenu[key]
// break
// case "menu_id":
// newSubmenu["menuId"] = submenu[key]
// break
// case "menu_item_desc":
// newSubmenu["menuItemDesc"] = submenu[key]
// break
// case "m_create":
// newSubmenu["mcreate"] = submenu[key]
// break
// case "m_delete":
// newSubmenu["mdelete"] = submenu[key]
// break
// case "m_edit":
// newSubmenu["medit"] = submenu[key]
// break
// case "m_query":
// newSubmenu["mquery"] = submenu[key]
// break
// case "m_visible":
// newSubmenu["mvisible"] = submenu[key]
// break
// default:
// newSubmenu[key] = submenu[key]
// }
// }
// return newSubmenu
// }
// // for (let i = 0; i < root.length; i++) {
// // const rootMenuItem = root[i]
// // // Find menu items by menu_id (rootMenuItemId) and usrGrp
// // let sql1 = `SELECT * FROM sec_grp_menu_access a WHERE a.usr_grp =${usrgrp} and a.menu_item_id=${rootMenuItem.menu_item_id}`
// // const menu = await executeQuery(sql1)
// for (const menuItem of root) {
// // Find all submenu items with status
// let sql2 = `SELECT * FROM sec_grp_menu_access a where a.status= 'Enable' and a.menu_id=${menuItem.menu_item_id} and a.usr_grp =${usrgrp} ORDER BY item_seq`
// const allSubmenu = await executeQuery(sql2)
// for (const subMenus of allSubmenu) {
// let sqlNew = `SELECT * FROM sec_user_group where usr_grp=${usrgrp}`
// const sqlNewResult = await executeQuery(sqlNew)
// let sqlMenuItemId = `SELECT * FROM sec_menu_det where menu_item_id=${menuItem.menu_item_id}`
// const sqlMenuItemIdResult = await executeQuery(sqlMenuItemId)
// subMenus.usrGrp = sqlNewResult[0]
// subMenus.menuItemId = sqlMenuItemIdResult[0]
// }
// let sqlNew = `SELECT * FROM sec_user_group where usr_grp=${usrgrp}`
// const sqlNewResult = await executeQuery(sqlNew)
// let sqlMenuItemId = `SELECT * FROM sec_menu_det where menu_item_id=${menuItem.menu_item_id}`
// const sqlMenuItemIdResult = await executeQuery(sqlMenuItemId)
// menuItem.subMenus = allSubmenu.map(renameSubmenuFields)
// menuItem.usrGrp = sqlNewResult[0]
// // updatedUsrGrp = menuItem.usrGrp
// // menuItem.usrGrp = updatedUsrGrp.map(renameSubmenuFields)
// menuItem.menuItemId = sqlMenuItemIdResult[0]
// }
// // rootMenuItem.subMenus = menu
// // }
// for (let i = 0; i < root.length; i++) {
// const rootMenuItem = root[i]
// root[i] = renameFields(rootMenuItem)
// }
// res.status(200).json(root)
// console.log(root);
// } catch (error) {
// console.error(error)
// res.status(500).json({ message: "Internal server error" })
// }
// }
// By Gk
const loadMenuByUser = async (req, res) => {
try {
const usrgrp = req.user.usr_grp; // Get the logged-in user's group
// Batch query to fetch root menu items along with the necessary details
const rootQuery = `
SELECT
a.*,
b.group_name,
c.menu_item_desc AS menu_item_desc_det,
c.main_menu_icon_name,
c.main_menu_action_name
FROM
sec_grp_menu_access a
LEFT JOIN
sec_user_group b ON a.usr_grp = b.usr_grp
LEFT JOIN
sec_menu_det c ON a.menu_item_id = c.menu_item_id
WHERE
a.menu_id = 0 AND a.status = 'Enable' AND a.usr_grp = ?
ORDER BY
a.item_seq;
`;
const root = await executeQuery(rootQuery, [usrgrp]);
// Prepare a map of root menu items to fetch submenus in a single query
const menuItemIds = root.map(item => item.menu_item_id);
if (menuItemIds.length > 0) {
const submenuQuery = `
SELECT
a.*,
b.group_name,
c.menu_item_desc AS menu_item_desc_det,
c.main_menu_icon_name,
c.main_menu_action_name
FROM
sec_grp_menu_access a
LEFT JOIN
sec_user_group b ON a.usr_grp = b.usr_grp
LEFT JOIN
sec_menu_det c ON a.menu_item_id = c.menu_item_id
WHERE
a.status = 'Enable'
AND a.menu_id IN (${menuItemIds.join(',')})
AND a.usr_grp = ?
ORDER BY
a.item_seq;
`;
const submenus = await executeQuery(submenuQuery, [usrgrp]);
// Organize submenus under their respective root menu items
const submenuMap = {};
submenus.forEach(submenu => {
if (!submenuMap[submenu.menu_id]) {
submenuMap[submenu.menu_id] = [];
}
submenuMap[submenu.menu_id].push(renameSubmenuFields(submenu));
});
// Assign submenus to their corresponding root menu items
root.forEach(item => {
item.subMenus = submenuMap[item.menu_item_id] || [];
});
} else {
// If no root items, set subMenus to empty arrays
root.forEach(item => {
item.subMenus = [];
});
}
// Rename fields for root menu items
const formattedRoot = root.map(renameFields);
res.status(200).json(formattedRoot);
console.log(formattedRoot);
} catch (error) {
console.error(error);
res.status(500).json({ message: "Internal server error" });
}
};
const renameFields = (obj) => {
return {
usrGrp: obj.usr_grp,
menuItemId: obj.menu_item_id,
itemSec: obj.item_seq,
menuId: obj.menu_id,
menuItemDesc: obj.menu_item_desc,
mcreate: obj.m_create,
mdelete: obj.m_delete,
medit: obj.m_edit,
mquery: obj.m_query,
mvisible: obj.m_visible,
main_menu_action_name: obj.main_menu_action_name,
main_menu_icon_name: obj.main_menu_icon_name, // Include the main_menu_icon_name
subMenus: obj.subMenus || []
};
};
const renameSubmenuFields = (submenu) => {
return {
usrGrp: submenu.usr_grp,
menuItemId: submenu.menu_item_id,
itemSec: submenu.item_seq,
menuId: submenu.menu_id,
menuItemDesc: submenu.menu_item_desc,
mcreate: submenu.m_create,
mdelete: submenu.m_delete,
medit: submenu.m_edit,
mquery: submenu.m_query,
mvisible: submenu.m_visible,
main_menu_action_name: submenu.main_menu_action_name,
main_menu_icon_name: submenu.main_menu_icon_name // Include the main_menu_icon_name
};
};
// Report
const createReport = async (req, res) => {
const body = req.body
try {
const params = [body.reportName,
body.description,
body.active,
body.isSql
]
// Replace undefined with null in parameters
const sanitizedParams = params.map((param) =>
param !== undefined ? param : null
)
await executeQuery(
`
INSERT INTO rpt_builder2_t (report_name, description, active,is_sql)
VALUES (?, ?, ?,?);
`,
sanitizedParams
);
return res.json({
success: 1,
content: "Report created successfully",
})
} catch (error) {
console.error(error)
return res.status(500).json({
success: 0,
message: "Error creating report",
error: error.message,
})
}
}
const getAllReportBuilder = async (req, res) => {
pool.query(
`SELECT
r.id as reportId,
r.report_name as reportName,
r.description,
r.active,
l.id as lineId,
l.header_id,
l.model
FROM rpt_builder2_t r
LEFT JOIN rpt_builder2_lines_t l ON r.id = l.rpt_builder2_t_id`,
[],
(error, results, fields) => {
if (error) {
console.error("Error executing SQL query:", error)
return res.status(500).json({
success: 0,
message: "Internal Server Error",
})
}
if (results.length === 0) {
return res.json({
success: 0,
message: "No data found",
})
}
// console.log("Data retrieved successfully:", results)
// Group results by report ID
const groupedResults = results.reduce((acc, result) => {
const existingReport = acc.find((item) => item.id === result.reportId)
if (existingReport) {
existingReport.rpt_builder2_lines.push({
id: result.lineId,
header_id: result.header_id,
model: result.model,
})
} else {
acc.push({
id: result.reportId,
reportName: result.reportName,
description: result.description,
active: result.active,
rpt_builder2_lines: [
{
id: result.lineId,
header_id: result.header_id,
model: result.model,
},
],
})
}
return acc
}, [])
return res.status(200).json(groupedResults)
}
)
}
const getAllReportBuilderById = async (req, res) => {
const id = req.params.id
pool.query(
`SELECT
r.id as reportId,
r.report_name as reportName,
r.description,
r.active,
l.id as lineId,
l.header_id,
l.model
FROM rpt_builder2_t r
LEFT JOIN rpt_builder2_lines_t l ON r.id = l.rpt_builder2_t_id
WHERE r.id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.error("Error executing SQL query:", error)
return res.status(500).json({
success: 0,
message: "Internal Server Error",
})
}
if (results.length === 0) {
return res.status(404).json({
success: 0,
message: "No data found for the given ID",
})
}
// Group results by report ID
const groupedResults = results.reduce((acc, result) => {
const existingReport = acc.find((item) => item.id === result.reportId)
if (existingReport) {
existingReport.rpt_builder2_lines.push({
id: result.lineId,
header_id: result.header_id,
model: result.model,
})
} else {
acc.push({
id: result.reportId,
reportName: result.reportName,
description: result.description,
active: result.active,
rpt_builder2_lines: [
{
id: result.lineId,
header_id: result.header_id,
model: result.model,
},
],
})
}
return acc
}, [])
return res.status(200).json(groupedResults)
}
)
}
const getAllReportBuilderByQuery = async (req, res) => {
const query = req.params.query
// console.log(query);
pool.query(query, (error, results, fields) => {
if (error) {
console.log(error)
return res.status(500).json({
success: 0,
message: "Error fetching data from the database",
})
}
if (results.length === 0) {
return res.status(404).json({
success: 0,
message: "No data found",
})
}
return res.status(200).json(results)
})
}
const updateReportBuilder = async (req, res) => {
const id = req.params.id
const body = req.body
pool.query(
`UPDATE rpt_builder2_t SET
report_name = ?,
description = ?,
active = ?,
is_sql = ?
WHERE id = ?`,
[body.report_name, body.description, body.active, body.is_sql, id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.status(500).json({
success: 0,
message: "Error updating record in the database",
})
}
if (results.affectedRows === 0) {
return res.status(404).json({
success: 0,
message: "Record not found",
})
}
return res.status(200).json({
success: 1,
message: "Record updated successfully",
})
}
)
}
const deleteReportBuilder = async (req, res) => {
const id = req.params.id
pool.query(
`DELETE FROM rpt_builder2_t WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.status(500).json({
success: 0,
message: "Error deleting record from the database",
})
}
if (results.affectedRows === 0) {
return res.status(404).json({
success: 0,
message: "Record not found",
})
}
return res.status(200).json({
success: 1,
message: "Record deleted successfully",
})
}
)
}
// Report Line
const getReportLine = async (req, res) => {
pool.query(
`SELECT * FROM rpt_builder2_lines_t `,
[],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const getReportLineById = async (req, res) => {
const id = req.params.id
pool.query(
`SELECT * FROM rpt_builder2_lines_t WHERE rpt_builder2_t_id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const updateReportLine = async (req, res) => {
const id = req.params.id
const body = req.body
pool.query(
`UPDATE rpt_builder2_lines_t SET header_id = ?, model = ? WHERE rpt_builder2_t_id = ?`,
[body.header_id, body.model, id],
(error, results, fields) => {
if (error) {
console.log(error)
}
if (!results) {
return res.status(400).json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json({
success: 1,
message: "Record updated succesfully",
})
}
)
}
// Dashboard
const getAllDashboard = async (req, res) => {
pool.query(
`SELECT
h.id,
h.account_id,
h.created_at AS createdAt,
h.created_by AS createdBy,
h.updated_at AS updatedAt,
h.updated_by AS updatedBy,
CAST(h.is_build AS UNSIGNED) AS build,
CAST(h.testing AS UNSIGNED) AS testing,
h.dashboard_name,
h.module_id,
h.description,
h.menu_name,
h.object_type,
h.secuirity_profile,
h.sub_object_type,
h.tech_stack,
h.is_updated,
l.id AS lineId,
l.created_at AS lineCreatedAt,
l.created_by AS lineCreatedBy,
l.updated_at AS lineUpdatedAt,
l.updated_by AS lineUpdatedBy,
l.account_id AS lineAccountId,
l.header_id,
l.model
FROM dashbord_header h
LEFT JOIN dashbord1_line l ON h.id = l.dashbord_header_id
`,
async (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Error fetching data from the database",
})
}
// If there are no results, return an empty array
if (results.length === 0) {
return res.status(200).json([])
}
// Organize the data into the desired structure
const dashboards = results.map((dashboardHeader) => {
return {
createdAt: dashboardHeader.createdAt,
createdBy: dashboardHeader.createdBy,
updatedBy: dashboardHeader.updatedBy,
updatedAt: dashboardHeader.updatedAt,
accountId: dashboardHeader.account_id,
id: dashboardHeader.id,
menuName: dashboardHeader.menu_name,
updated: dashboardHeader.is_updated === 1,
techStack: dashboardHeader.tech_stack,
object_type: dashboardHeader.object_type,
sub_object_type: dashboardHeader.sub_object_type,
build: !!dashboardHeader.build, // Convert to boolean
testing: !!dashboardHeader.testing, // Convert to boolean
dashboard_name: dashboardHeader.dashboard_name,
module_id: dashboardHeader.module_id,
description: dashboardHeader.description,
secuirity_profile: dashboardHeader.secuirity_profile,
dashbord1_Line: dashboardHeader.lineId
? [
{
createdAt: dashboardHeader.lineCreatedAt,
createdBy: dashboardHeader.lineCreatedBy,
updatedBy: dashboardHeader.lineUpdatedBy,
updatedAt: dashboardHeader.lineUpdatedAt,
accountId: dashboardHeader.lineAccountId,
id: dashboardHeader.lineId,
header_id: dashboardHeader.header_id,
model: dashboardHeader.model, // Parse the JSON model
},
]
: [],
}
})
return res.status(200).json(dashboards)
}
)
}
const getDashboardByModuleId = async (req, res) => {
const module_id = req.query.module_id
pool.query(
`SELECT
h.id,
h.account_id,
h.created_at AS createdAt,
h.created_by AS createdBy,
h.updated_at AS updatedAt,
h.updated_by AS updatedBy,
CAST(h.is_build AS UNSIGNED) AS build,
CAST(h.testing AS UNSIGNED) AS testing,
h.dashboard_name,
h.module_id,
h.description,
h.menu_name,
h.object_type,
h.secuirity_profile,
h.sub_object_type,
h.tech_stack,
h.is_updated,
l.id AS lineId,
l.created_at AS lineCreatedAt,
l.created_by AS lineCreatedBy,
l.updated_at AS lineUpdatedAt,
l.updated_by AS lineUpdatedBy,
l.account_id AS lineAccountId,
l.header_id,
l.model
FROM dashbord_header h
LEFT JOIN dashbord1_line l ON h.id = l.dashbord_header_id
WHERE h.module_id = ?`,
[module_id],
async (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Error fetching data from the database",
})
}
// If there are no results, return an empty array
if (results.length === 0) {
return res.status(200).json([])
}
// Organize the data into the desired structure
const dashboards = results.map((dashboardHeader) => {
return {
createdAt: dashboardHeader.createdAt,
createdBy: dashboardHeader.createdBy,
updatedBy: dashboardHeader.updatedBy,
updatedAt: dashboardHeader.updatedAt,
accountId: dashboardHeader.account_id,
id: dashboardHeader.id,
menuName: dashboardHeader.menu_name,
updated: dashboardHeader.is_updated === 1,
techStack: dashboardHeader.tech_stack,
object_type: dashboardHeader.object_type,
sub_object_type: dashboardHeader.sub_object_type,
build: !!dashboardHeader.build, // Convert to boolean
testing: !!dashboardHeader.testing, // Convert to boolean
dashboard_name: dashboardHeader.dashboard_name,
module_id: dashboardHeader.module_id,
description: dashboardHeader.description,
secuirity_profile: dashboardHeader.secuirity_profile,
dashbord1_Line: dashboardHeader.lineId
? [
{
createdAt: dashboardHeader.lineCreatedAt,
createdBy: dashboardHeader.lineCreatedBy,
updatedBy: dashboardHeader.lineUpdatedBy,
updatedAt: dashboardHeader.lineUpdatedAt,
accountId: dashboardHeader.lineAccountId,
id: dashboardHeader.lineId,
header_id: dashboardHeader.header_id,
model: dashboardHeader.model, // Parse the JSON model
},
]
: [],
}
})
return res.status(200).json(dashboards)
}
)
}
const getAllDashboardHeaderById = async (req, res) => {
const dashboardId = req.params.id
const moduleId = req.query.module_id
pool.query(
`SELECT
h.id,
h.account_id,
h.created_at AS createdAt,
h.created_by AS createdBy,
h.updated_at AS updatedAt,
h.updated_by AS updatedBy,
CAST(h.is_build AS UNSIGNED) AS build,
CAST(h.testing AS UNSIGNED) AS testing,
h.dashboard_name,
h.module_id,
h.description,
h.menu_name,
h.object_type,
h.secuirity_profile,
h.sub_object_type,
h.tech_stack,
h.is_updated,
l.id AS lineId,
l.created_at AS lineCreatedAt,
l.created_by AS lineCreatedBy,
l.updated_at AS lineUpdatedAt,
l.updated_by AS lineUpdatedBy,
l.account_id AS lineAccountId,
l.header_id,
l.model
FROM dashbord_header h
LEFT JOIN dashbord1_line l ON h.id = l.dashbord_header_id
WHERE h.id = ?`,
[dashboardId],
async (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Error fetching data from the database",
})
}
// If there are no results, return a 404 response
if (results.length === 0) {
return res.status(404).json({
success: 0,
message: "No data found for the given ID and module ID",
})
}
// Organize the data into the desired structure
const dashboardHeader = results[0] // Assuming there's only one record
const dashboard = {
createdAt: dashboardHeader.createdAt,
createdBy: dashboardHeader.createdBy,
updatedBy: dashboardHeader.updatedBy,
updatedAt: dashboardHeader.updatedAt,
accountId: dashboardHeader.account_id,
id: dashboardHeader.id,
menuName: dashboardHeader.menu_name,
updated: dashboardHeader.is_updated === 1,
techStack: dashboardHeader.tech_stack,
object_type: dashboardHeader.object_type,
sub_object_type: dashboardHeader.sub_object_type,
build: !!dashboardHeader.build, // Convert to boolean
testing: !!dashboardHeader.testing, // Convert to boolean
dashboard_name: dashboardHeader.dashboard_name,
module_id: dashboardHeader.module_id,
description: dashboardHeader.description,
secuirity_profile: dashboardHeader.secuirity_profile,
dashbord1_Line: dashboardHeader.lineId
? [
{
createdAt: dashboardHeader.lineCreatedAt,
createdBy: dashboardHeader.lineCreatedBy,
updatedBy: dashboardHeader.lineUpdatedBy,
updatedAt: dashboardHeader.lineUpdatedAt,
accountId: dashboardHeader.lineAccountId,
id: dashboardHeader.lineId,
header_id: dashboardHeader.header_id,
model: dashboardHeader.model, // Parse the JSON model
},
]
: [],
}
return res.status(200).json(dashboard)
}
)
}
const createDashboard = (req, res) => {
const body = req.body
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
// Insert into dashbord_header
pool.query(
`INSERT dashbord_header SET
created_at = ?,
updated_at = ?,
is_build = ?,
dashboard_name = ?,
description = ?,
menu_name = ?,
module_id = ?,
secuirity_profile = ?,
tech_stack = ?,
is_updated = ?,
object_type = ?,
sub_object_type = ?,
testing = ?`,
[
dateTime,
dateTime,
body.is_build || null,
body.dashboard_name || null,
body.description || null,
body.menu_name || null,
body.module_id || 0, // Provide a default value for module_id
body.secuirity_profile || null,
body.tech_stack || null,
body.is_updated || null,
body.object_type || null,
body.sub_object_type || null,
body.testing || false,
],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Failed to create dashboard",
})
}
if (!results.insertId) {
return res.json({
success: 0,
message: "Failed to create dashboard",
})
}
const dashboardId = results.insertId
// Insert into dashbord1_line
pool.query(
`INSERT dashbord1_line SET
created_at = ?,
updated_at = ?,
model = ?,
header_id = ?,
dashbord_header_id = ?`,
[
dateTime,
dateTime,
body.model || null, // Assuming you have 'model' in your request body
body.header_id || null, // Assuming you have 'header_id' in your request body
dashboardId,
],
(lineError, lineResults, lineFields) => {
if (lineError) {
console.log(lineError)
// Handle error, you might want to rollback the dashboard_header insertion
return res.json({
success: 0,
message: "Failed to create dashboard and line",
})
}
return res.json({
success: 1,
data: "created successfully",
})
}
)
}
)
}
const updateDashboard = (req, res) => {
const { id } = req.params
const body = req.body
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
pool.query(
`UPDATE dashbord_header SET
updated_at = ?,
is_build = ?,
dashboard_name = ?,
description = ?,
menu_name = ?,
module_id = ?,
secuirity_profile = ?,
tech_stack = ?,
is_updated = ?,
object_type = ?,
sub_object_type = ?,
testing = ?
WHERE id = ?`,
[
dateTime,
body.is_build || null,
body.dashboard_name || null,
body.description || null,
body.menu_name || null,
body.module_id || null,
body.secuirity_profile || null,
body.tech_stack || null,
body.is_updated || null,
body.object_type || null,
body.sub_object_type || null,
body.testing || null,
id,
],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Failed to update dashboard",
})
}
if (results.affectedRows === 0) {
return res.json({
success: 0,
message: "No records found for the given ID",
})
}
return res.json({
success: 1,
data: "updated successfully",
})
}
)
}
const deleteDashboard = async (req, res) => {
const id = req.params.id
pool.query(
`DELETE FROM dashbord_header WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
return res.json({
success: 1,
data: "Deleted succesfully",
})
}
)
}
//// Dashboard Line
const getDashboardLine = async (req, res) => {
const id = req.params.id
pool.query(`SELECT * FROM dashbord1_line`, [id], (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
})
}
const getDashboardLineById = async (req, res) => {
const id = req.params.id
pool.query(
`SELECT * FROM dashbord1_line WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const updateDashboardLineById = async (req, res) => {
const id = req.params.id
const body = req.body
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
pool.query(
`UPDATE dashbord1_line SET updated_at = ?, created_by = ?, updated_by = ?, model = ? WHERE id = ${id}`,
[dateTime, body.created_by, body.updated_by, body.model, id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to update dashboard",
})
}
return res.json({
success: 1,
data: "Updated succesfully",
})
}
)
}
//// System Parameter
const getSystemParameter = async (req, res) => {
pool.query(`SELECT * FROM system_paramaters`, (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
})
}
const getSystemParameterById = async (req, res) => {
var id = req.params.id
if (id == null) {
id = 1
}
pool.query(
`SELECT * FROM system_paramaters WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const createSystemParameter = async (req, res) => {
const body = req.body
pool.query(
`INSERT system_paramaters SET edi_mae_scheduler_onoff = ?, edi_scheduler_onoff = ?, base_price_list = ?, billing_department_code = ?, cn_billing_frequency = ?, default_department = ?, default_position = ?, first_dayof_the_week = ?, hour_per_shift = ?, lease_tax_code = ?, link_to_display = ?, lov_link_to_display = ?, lov_row_to_display = ?, noncontainer_serviceorder_auto_appdepcode = ?, oid_admin_user = ?, oid_base = ?, oid_server_port = ?, oid_server_name = ?, row_to_add = ?, row_to_display = ?, scheduler_time = ?, single_charge = ?, user_default_group = ?, vessel_conf_processlimit = ?, company_display_name = ?, upload_logo = ?, upload_logo_name = ?, upload_logo_path = ?`,
[
body.edi_mae_scheduler_onoff,
body.edi_scheduler_onoff,
body.base_price_list,
body.billing_department_code,
body.cn_billing_frequency,
body.default_department,
body.default_position,
body.first_dayof_the_week,
body.hour_per_shift,
body.lease_tax_code,
body.link_to_display,
body.lov_link_to_display,
body.lov_row_to_display,
body.noncontainer_serviceorder_auto_appdepcode,
body.oid_admin_user,
body.oid_base,
body.oid_server_port,
body.oid_server_name,
body.row_to_add,
body.row_to_display,
body.scheduler_time,
body.single_charge,
body.user_default_group,
body.vessel_conf_processlimit,
body.company_display_name,
body.upload_logo,
body.upload_logo_name,
body.upload_logo_path,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to create dashboard",
})
}
return res.json({
success: 1,
data: "created succesfully",
})
}
)
}
const updateSystemParameter = async (req, res) => {
const id = req.params.id
const body = req.body
pool.query(
`UPDATE system_paramaters SET edi_mae_scheduler_onoff = ?, edi_scheduler_onoff = ?, base_price_list = ?, billing_department_code = ?, cn_billing_frequency = ?, default_department = ?, default_position = ?, first_dayof_the_week = ?, hour_per_shift = ?, lease_tax_code = ?, link_to_display = ?, lov_link_to_display = ?, lov_row_to_display = ?, noncontainer_serviceorder_auto_appdepcode = ?, oid_admin_user = ?, oid_base = ?, oid_server_port = ?, oid_server_name = ?, row_to_add = ?, row_to_display = ?, scheduler_time = ?, single_charge = ?, user_default_group = ?, vessel_conf_processlimit = ?, company_display_name = ?, upload_logo = ?, upload_logo_name = ?, upload_logo_path = ? where id = ${id}`,
[
body.edi_mae_scheduler_onoff,
body.edi_scheduler_onoff,
body.base_price_list,
body.billing_department_code,
body.cn_billing_frequency,
body.default_department,
body.default_position,
body.first_dayof_the_week,
body.hour_per_shift,
body.lease_tax_code,
body.link_to_display,
body.lov_link_to_display,
body.lov_row_to_display,
body.noncontainer_serviceorder_auto_appdepcode,
body.oid_admin_user,
body.oid_base,
body.oid_server_port,
body.oid_server_name,
body.row_to_add,
body.row_to_display,
body.scheduler_time,
body.single_charge,
body.user_default_group,
body.vessel_conf_processlimit,
body.company_display_name,
body.upload_logo,
body.upload_logo_name,
body.upload_logo_path,
],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
if (!results) {
return res.json({
success: 0,
message: "Failed to update Parameter",
})
}
return res.json({
success: 1,
data: "Updated succesfully",
})
}
)
}
const deleteSystemParameter = async (req, res) => {
const id = req.params.id
pool.query(
`DELETE FROM system_paramaters WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
return res.json({
success: 1,
data: "Deleted succesfully",
})
}
)
}
// const test = async (req, res) => {
// try {
// // Simulate getting the logged-in user's group (usrGrp)
// const usrgrp = 1 // Replace this with your actual logic to get the user's group
// // Find root menu items where menu_id is 0
// let sql = `SELECT * FROM sec_grp_menu_access WHERE menu_id = 0 AND status= 'Enable' AND usr_grp = ${usrgrp} ORDER BY item_seq`
// const root = await executeQuery(sql)
// for (let i = 0; i < root.length; i++) {
// const rootMenuItemId = root[i]
// // Find menu items by menu_id (rootMenuItemId) and usrGrp
// let sql1 = `SELECT * FROM sec_grp_menu_access a WHERE a.usr_grp = ${usrgrp} and a.menu_item_id= ${rootMenuItemId.menu_item_id}`
// const menu = await executeQuery(sql1)
// for (const menuItem of menu) {
// // Find all submenu items with status
// let sql2 = `SELECT * FROM sec_grp_menu_access a where a.status= 'Enable' and a.menu_id=${menuItem.menu_item_id} and a.usr_grp =${usrgrp} ORDER BY item_seq`
// const allSubmenu = await executeQuery(sql2)
// menuItem.subMenus = allSubmenu
// }
// }
// res.status(200).json(root)
// } catch (error) {
// console.error(error)
// res.status(500).json({ message: "Internal server error" })
// }
// }
const getLogs = (req, res) => {
const { userName } = req.params // Adjust this to your specific user identification method
// Define the directory where the user's logs are stored
const logDir = path.join(__dirname, "../logs", userName)
try {
// Read log files in the user's directory
const logFiles = fs.readdirSync(logDir)
// Read the content of each log file
const logs = logFiles.map((file) => {
const logFilePath = path.join(logDir, file)
const logData = fs.readFileSync(logFilePath, "utf-8")
return { date: file, data: logData }
})
res.status(200).json({ logs })
} catch (err) {
console.error(`Error while reading logs: ${err}`)
res.status(500).json({ error: "Internal Server Error" })
}
}
const addColumns = (req, res) => {
const { columnName, dataType } = req.body
if (!Array.isArray(columnName) || !Array.isArray(dataType)) {
return res.send("Please provide multiple column names and data types.")
}
if (columnName.length !== dataType.length) {
return res.send(
"Number of column names must match the number of data types."
)
}
const columnsToAdd = []
for (let i = 0; i < columnName.length; i++) {
columnsToAdd.push(`${columnName[i]} ${dataType[i]}`)
}
// Construct the SQL query
const sql = `ALTER TABLE your_table_name ADD COLUMN ${columnsToAdd.join(
", "
)}`
connection.query(sql, (err) => {
if (err) {
res.send(`Error: ${err.message}`)
} else {
res.send("Columns added successfully.")
}
})
}
//// Form Extension controller
const getAllFormExtension = (req, res) => {
pool.query(`SELECT * FROM rn_ext_field_t`, (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
})
}
const getByIdFormExtension = (req, res) => {
const id = req.params.id
pool.query(
`SELECT * FROM rn_ext_field_t WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
const createFormExtension = async (req, res) => {
const body = req.body
// const currentDate = new Date().toISOString().slice(0, 10) // Get YYYY-MM-DD format
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
const user = req.user
console.log(req.user.user_name)
const email = req.user.email
const sql = `SELECT account_id FROM sec_users WHERE email = ?`
const account_id = await executeQuery(sql, [email])
// console.log(account_id[0].account_id)
pool.query(
`INSERT INTO rn_ext_field_t SET created_at = ?, created_by = ?, account_id = ?, data_type = ?, field_name = ?, form_code = ?, is_active = ?, mapping = ?, type = ?`,
[
dateTime,
req.user.user_name,
account_id[0].account_id,
body.data_type,
body.field_name,
body.form_code,
body.is_active,
body.mapping,
body.type,
],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Some error occured",
})
}
return res.json({
success: 1,
data: "created succesfully",
})
}
)
// console.log(account_id)
}
const updateFormExtension = async (req, res) => {
const id = req.params.id
const body = req.body
// const currentDate = new Date().toISOString().slice(0, 10) // Get YYYY-MM-DD format
var today = new Date()
var date =
today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate()
var time =
today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds()
var dateTime = date + " " + time
const email = req.user.email
const sql = `SELECT account_id FROM sec_users WHERE email = ?`
const account_id = await executeQuery(sql, [email])
pool.query(
`UPDATE rn_ext_field_t SET updated_at = ?, updated_at = ?, account_id = ?, data_type = '${body.data_type}', field_name = '${body.field_name}', form_code = '${body.form_code}', is_active = '${body.is_active}', mapping = '${body.mapping}', type = '${body.type}' WHERE id = '${id}'`,
[dateTime, req.user.user_name, account_id[0].account_id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Some error occured",
})
}
return res.json({
success: 1,
data: "created succesfully",
})
}
)
}
const deleteFormExtension = async (req, res) => {
const id = req.params.id
const body = req.body
pool.query(
`DELETE FROM rn_ext_field_t WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return
}
return res.json({
success: 1,
data: "Deleted succesfully",
})
}
)
}
const buildExtension = async (req, res) => {
const email = req.user.email
const sql = `SELECT account_id FROM sec_users WHERE email = ?`
const result = await executeQuery(sql, [email])
const account_id = result[0].account_id
const formId = req.params.formId
const sql2 = `SELECT field_name FROM rn_ext_field_t WHERE id = ?`
const field_name = await executeQuery(sql, [formId])
console.log(field_name)
const formCode = await buildExtensionByFormCode(account_id, formId)
if (!formCode) {
res.status(500).json({ success: 0, message: "Some error occured!" })
}
const mailOptions = {
from: "example.com",
to: email,
subject: "Email for informing about form Extension",
text: `Your form with form id ${formId} is extended with FieldName ${field_name}`,
}
transporter.sendMail(mailOptions, (error) => {
if (error) {
console.log(error)
console.log({ message: "Error sending Email" })
}
console.log({ message: "Email sent successfully" })
})
console.log(formCode)
res.status(200).json(formCode[0])
}
const userMenu = (req, res) => {
pool.query(`SELECT * FROM accesstype`, (error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
})
}
const test = (req, res) => {
const id = req.params.id
pool.query(
`SELECT field_name FROM rn_ext_field_t WHERE id = ?`,
[id],
(error, results, fields) => {
if (error) {
console.log(error)
return res.json({
success: 0,
message: "Table is empty",
})
}
return res.status(200).json(results)
}
)
}
module.exports = {
getUsersUserGrpMaintenance,
getUsersByUserIdUserGrpMaintenance,
updateUsersUserGrpMaintenance,
createUsersUserGrpMaintenance,
deleteUsersUserGrpMaintenance,
getUsersMenuMaintainence,
getUsersByUserIdMenuMaintainence,
createUsersMenuMaintainence,
updateUsersMenuMaintainence,
deleteUsersMenuMaintainence,
getUsersByIdSubMenuMaintainence,
createUsersMenuAccessControl,
getUsersMenuAccessControl,
getUserMaintainence,
getByIdUserMaintainence,
createUserMaintainence,
updateUserMaintainence,
loadMenuByUser,
userMenu,
createReport,
getAllReportBuilder,
getAllReportBuilderById,
getAllReportBuilderByQuery,
updateReportBuilder,
deleteReportBuilder,
getReportLine,
getReportLineById,
updateReportLine,
getAllDashboard,
getDashboardByModuleId,
getAllDashboardHeaderById,
createDashboard,
updateDashboard,
deleteDashboard,
getDashboardLine,
getDashboardLineById,
updateDashboardLineById,
getSystemParameter,
getSystemParameterById,
createSystemParameter,
updateSystemParameter,
deleteSystemParameter,
getLogs,
getAllFormExtension,
getByIdFormExtension,
createFormExtension,
updateFormExtension,
deleteFormExtension,
buildExtension,
login,
logout,
sendEmailForResetPassword,
resetPassword,
savePassword,
getLoggedInUser,
uploadProfilePhoto,
retrieveProfilePhoto,
test,
}