如果我通过后端实现的话如何const express = require('express');
const sql = require('mssql');
const cors = require('cors');
const multer = require('multer');
const app = express();
// 配置 SQL Server 连接，使用 SQL 用户验证
const config = {
user: 'admin',
password: 'admin1',
server: 'localhost',
database: 'PYZK_KQ',
options: {
encrypt: false,
trustServerCertificate: true
}
};
// 使用 cors 中间件
app.use(cors());
// 增加请求体大小限制
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ limit: '50mb', extended: true }));
// 增加字段值长度限制
const upload = multer({
limits: {
fieldSize: 1024 * 1024 * 50 // 设置字段值最大长度为 50MB
}
});
app.use(express.static('public'));
// 插入数据函数
async function insertData(data, tableName, columns) {
let pool;
try {
if (!Array.isArray(data) || data.length === 0) {
throw new Error('传入的数据不是有效的数组');
}
pool = await sql.connect(config);
const transaction = new sql.Transaction(pool);
await transaction.begin();
for (const row of data) {
const request = new sql.Request(transaction);
columns.forEach((column) => {
let value = row[column.name];
if (column.name === 'LeaveApplication') {
if (value === '--' || value === null || value === 'NaN') {
value = '无'; // 设置默认值
} else {
value = String(value).slice(0, 100); // 确保长度不超过 100 个字符
}
} else if (column.name === 'WorkingHours') {
if (typeof value !== 'string') {
value = String(value);
}
value = value.slice(0, 20); // 确保 WorkingHours 长度不超过 20 个字符
} else if (column.name === 'state') {
value = value || null; // 如果 state 为空，则设置为 null
} else {
value = value === '--' ? null : value;
}
request.input(column.name, column.type, value);
});
const columnNames = columns.map((col) => col.name).join(', ');
const paramNames = columns.map((col) => `@${col.name}`).join(', ');
await request.query(`INSERT INTO ${tableName} (${columnNames}) VALUES (${paramNames})`);
}
await transaction.commit();
return { message: `数据已成功插入 ${tableName} 表` };
} catch (error) {
console.error(`插入数据到 ${tableName} 表时出错:`, error);
if (transaction) await transaction.rollback();
throw error;
} finally {
if (pool) pool.close();
}
}
// 插入正常数据
app.post('/insertNormal', async (req, res) => {
const tableName = 'NormalAttendance';
const columns = [
{ name: 'Name', type: sql.NVarChar(100) },
{ name: 'WorkingHours', type: sql.NVarChar(20) },
{ name: 'Date', type: sql.Date },
{ name: 'Shift', type: sql.NVarChar(50) },
{ name: 'flag', type: sql.Int },
{ name: 'dk1', type: sql.NVarChar(20) },
{ name: 'dk2', type: sql.NVarChar(20) },
{ name: 'dk3', type: sql.NVarChar(20) },
{ name: 'dk4', type: sql.NVarChar(20) },
{ name: 'dk5', type: sql.NVarChar(20) },
{ name: 'dk6', type: sql.NVarChar(20) },
{ name: 'dk7', type: sql.NVarChar(20) },
{ name: 'dk8', type: sql.NVarChar(20) }
];
try {
if (!Array.isArray(req.body)) {
return res.status(400).json({ message: '传入的数据不是有效的数组' });
}
const result = await insertData(req.body, tableName, columns);
res.json(result);
} catch (error) {
res.status(500).json({ message: `插入正常数据时出错: ${error.message}` });
}
});
// 插入异常数据
// 插入异常数据
app.post('/insertAbnormal', async (req, res) => {
console.log('接收到插入异常数据的请求');
let pool;
let transaction;
try {
const data = req.body; // 获取前端传来的数据
if (!Array.isArray(data) || data.length === 0) {
return res.status(400).json({ message: '传入的数据不是有效的数组' });
}
pool = await sql.connect(config);
transaction = new sql.Transaction(pool);
await transaction.begin();
const columns = [
{ name: 'Name', type: sql.NVarChar(100) },
{ name: 'Date', type: sql.Date },
{ name: 'WorkingHours', type: sql.NVarChar(20) },
{ name: 'Shift', type: sql.NVarChar(50) },
{ name: 'AdjustedCheckInTime', type: sql.NVarChar(20) },
{ name: 'LeaveApplication', type: sql.NVarChar(sql.MAX) },
{ name: 'ActualCheckInTime', type: sql.NVarChar(20) },
{ name: 'CheckInDate', type: sql.Date },
{ name: 'flag', type: sql.Int },
{ name: 'state', type: sql.NVarChar(50) }
];
for (const row of data) {
const request = new sql.Request(transaction);
columns.forEach((column) => {
let value = row[column.name];
if (column.name === 'LeaveApplication' && (value === '--' || value === null || value === 'NaN')) {
value = '无'; // 设置默认值
} else if (column.name === 'WorkingHours') {
if (typeof value !== 'string') {
value = String(value);
}
value = value.slice(0, 20); // 确保 WorkingHours 长度不超过 20 个字符
if (!value || /^\s*$/.test(value)) {
value = '-/-';
}
} else if (column.name === 'state') {
value = value || '默认状态'; // 设置默认值
} else if (column.name === 'Date' || column.name === 'CheckInDate') {
// 特别处理日期类型
if (value === '--' || value === null || value === 'NaN') {
value = null;
} else {
// 尝试将日期转换为正确的格式
try {
const date = new Date(value);
if (isNaN(date.getTime())) {
value = null;
} else {
// 确保日期格式正确 (YYYY-MM-DD)
value = date.toISOString().split('T')[0];
}
} catch (e) {
console.warn(`日期转换错误: ${value}`, e);
value = null;
}
}
} else {
value = value === '--' ? null : value;
}
request.input(column.name, column.type, value);
});
const columnNames = columns.map((col) => col.name).join(', ');
const paramNames = columns.map((col) => `@${col.name}`).join(', ');
const insertQuery = `INSERT INTO [PYZK_KQ].[dbo].[YCSJ] (${columnNames}) VALUES (${paramNames})`;
await request.query(insertQuery);
}
// 删除重复数据的 SQL 查询语句
const deleteQuery = `
WITH CTE AS (
SELECT [ID],
[Name],
[Date],
[WorkingHours],
[Shift],
[AdjustedCheckInTime],
[LeaveApplication],
[ActualCheckInTime],
[CheckInDate],
[flag],
[state],
[Timestamp],
ROW_NUMBER() OVER (
PARTITION BY [Name], [Date], [WorkingHours], [Shift], [AdjustedCheckInTime], [LeaveApplication], [ActualCheckInTime], [CheckInDate]
ORDER BY CASE WHEN [state] <> '默认状态' THEN 0 ELSE 1 END, [ID] DESC
) AS RowNum
FROM [PYZK_KQ].[dbo].[YCSJ]
)
DELETE FROM CTE WHERE RowNum > 1;
`;
await new sql.Request(transaction).query(deleteQuery);
// 获取去重后的数据
const result = await new sql.Request(transaction).query('SELECT [ID], [Name], [Date], [WorkingHours], [Shift], [AdjustedCheckInTime], [LeaveApplication], [ActualCheckInTime], [CheckInDate], [flag], [state], [Timestamp] FROM [PYZK_KQ].[dbo].[YCSJ]');
console.log('插入数据成功，返回去重后的数据');
await transaction.commit();
res.json(result.recordset);
} catch (error) {
console.error('插入异常数据时出错:', error);
if (transaction) await transaction.rollback();
res.status(500).json({ message: `插入异常数据时出错: ${error.message}`, error: error.toString() });
} finally {
if (pool) pool.close();
}
});
// 获取打卡数据前端展示
app.get('/getAttendanceData', async (req, res) => {
console.log(11111);
let pool;
try {
pool = await sql.connect(config);
const result = await pool.request().query('SELECT * FROM NormalAttendance');
// console.log(result);
res.json(result.recordset);
} catch (error) {
res.status(500).json({ message: `获取数据时出错: ${error.message}` });
} finally {
if (pool) pool.close();
}
});
app.get('/getAttendanceData1', async (req, res) => {
console.log(11111);
let pool;
try {
pool = await sql.connect(config);
const result = await pool.request().query(`SELECT * FROM YCSJ
WHERE state <> '1'`);
console.log(result);
res.json(result.recordset);
} catch (error) {
res.status(500).json({ message: `获取数据时出错: ${error.message}` });
} finally {
if (pool) pool.close();
}
});
// 上传数据
app.post('/upload', upload.none(), async (req, res) => {
let pool;
let transaction;
try {
const data = JSON.parse(req.body.data);
if (!Array.isArray(data) || data.length === 0) {
return res.status(400).json({ message: '传入的数据不是有效的数组' });
}
// 连接到 SQL Server
pool = await sql.connect(config);
transaction = new sql.Transaction(pool);
await transaction.begin();
const tableName = 'PYZK_KQ_TEST';
// 获取数据库表的列名
const tableColumnsResult = await new sql.Request(transaction).query(`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '${tableName}'`);
const tableColumns = tableColumnsResult.recordset.map(row => row.COLUMN_NAME);
for (const row of data) {
const rowColumns = Object.keys(row);
const validColumns = rowColumns.filter(column => tableColumns.includes(column));
if (validColumns.length === 0) {
continue;
}
const columnNames = validColumns.join(', ');
const paramNames = validColumns.map((col, index) => `@param${index}`).join(', ');
const insertQuery = `INSERT INTO ${tableName} (${columnNames}) VALUES (${paramNames})`;
const request = new sql.Request(transaction);
validColumns.forEach((column, index) => {
const value = row[column] === '--' ? null : row[column];
request.input(`param${index}`, value);
});
await request.query(insertQuery);
}
// 删除完全重复的行
const deleteQuery = `
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY ${tableColumns.join(', ')}
ORDER BY (SELECT NULL)
) AS RowNum
FROM ${tableName}
)
DELETE FROM CTE
WHERE RowNum > 1;
`;
await new sql.Request(transaction).query(deleteQuery);
await transaction.commit();
res.json({ message: '数据已成功插入 SQL Server 数据库，且已删除完全重复的行' });
} catch (error) {
console.error('处理文件时出错:', error);
if (transaction) await transaction.rollback();
res.status(500).json({ message: '处理文件时出错，请稍后重试' });
} finally {
if (pool) pool.close();
}
});
// 获取按人名排序后的数据，并转换日期和时间列
app.get('/getSortedData', async (req, res) => {
let pool;
try {
pool = await sql.connect(config);
const tableName = 'PYZK_KQ_TEST';
// 获取数据库表的列名
const tableColumnsResult = await new sql.Request(pool).query(`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '${tableName}'`);
const tableColumns = tableColumnsResult.recordset.map(row => row.COLUMN_NAME);
// 构建查询语句
const query = `
SELECT
姓名,
TRY_CONVERT(VARCHAR(10), TRY_CONVERT(DATE, LEFT(日期, 10)), 120) AS 日期,
${tableColumns.filter(col => col !== '日期' && col !== '姓名').join(', ')}
FROM ${tableName}
ORDER BY 姓名
`;
const result = await new sql.Request(pool).query(query);
res.json(result.recordset);
} catch (error) {
console.error('获取排序数据时出错:', error);
res.status(500).json({ message: '获取排序数据时出错，请稍后重试' });
} finally {
if (pool) pool.close();
}
});
// 更新 YCSJ 表并插入 NormalAttendance 表
app.post('/updateData', async (req, res) => {
let pool;
try {
// 从请求体中获取所有参数，包括flag
const { name, date, shift, workingHours, dk1, dk2, dk3, dk4, dk5, dk6, dk7, dk8, flag } = req.body;
console.log('接收到的数据:', req.body);
if (!name || !date || !shift || !workingHours) {
return res.status(400).json({ message: '缺少必要参数' });
}
pool = await sql.connect(config);
const transaction = new sql.Transaction(pool);
await transaction.begin();
try {
// 更新 YCSJ 表
const updateYCSJQuery = `
UPDATE YCSJ
SET state = '1' -- 确保 state 是字符串类型
WHERE Name = @name AND Date = @date AND Shift = @shift
`;
const request1 = new sql.Request(transaction);
request1.input('name', sql.NVarChar(100), name);
request1.input('date', sql.Date, date);
request1.input('shift', sql.NVarChar(50), shift);
await request1.query(updateYCSJQuery);
// 插入数据到 NormalAttendance 表
const insertNormalQuery = `
INSERT INTO NormalAttendance (Name, WorkingHours, Date, Shift, flag, dk1, dk2, dk3, dk4, dk5, dk6, dk7, dk8)
VALUES (@name, @workingHours, @date, @shift, @flag, @dk1, @dk2, @dk3, @dk4, @dk5, @dk6, @dk7, @dk8)
`;
const request2 = new sql.Request(transaction);
request2.input('name', sql.NVarChar(100), name);
request2.input('workingHours', sql.NVarChar(20), workingHours);
request2.input('date', sql.Date, date);
request2.input('shift', sql.NVarChar(50), shift);
request2.input('flag', sql.Int, flag); // 使用从请求中获取的flag值
request2.input('dk1', sql.NVarChar(20), dk1 || '--');
request2.input('dk2', sql.NVarChar(20), dk2 || '--');
request2.input('dk3', sql.NVarChar(20), dk3 || '--');
request2.input('dk4', sql.NVarChar(20), dk4 || '--');
request2.input('dk5', sql.NVarChar(20), dk5 || '--');
request2.input('dk6', sql.NVarChar(20), dk6 || '--');
request2.input('dk7', sql.NVarChar(20), dk7 || '--');
request2.input('dk8', sql.NVarChar(20), dk8 || '--');
await request2.query(insertNormalQuery);
await transaction.commit();
res.json({ message: '数据更新和插入成功' });
} catch (error) {
await transaction.rollback();
throw error;
}
} catch (error) {
console.error('更新 YCSJ 表并插入 NormalAttendance 表时出错:', error);
res.status(500).json({ message: `更新和插入数据时出错: ${error.message}` });
} finally {
if (pool) pool.close();
}
});
// 辅助函数：根据 CheckInDate 分组签到时间
function groupCheckInTimesByDate(checkInTimes) {
const groupedData = {};
checkInTimes.forEach(([time, date]) => {
if (!groupedData[date]) {
groupedData[date] = [];
}
groupedData[date].push(time);
});
return groupedData;
}
const port = 3000;
app.listen(port, () => console.log(`服务器运行在端口 ${port}`));
本文介绍了如何利用微信小程序创建活动打卡功能，包括设置地点、时间及趣味性元素，如AR签到、刷脸签到等。用户可以扫描二维码参与活动并在指定范围内打卡，打卡数据可用于排行榜，激发竞争。此外，还提到了周期性打卡活动的应用，如读书节，并举例说明了打卡活动在连锁店、工会活动等场景中的实际应用。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
