1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
import express from 'express';
import { MongoClient } from 'mongodb';
import { google } from 'googleapis';
import crypto from 'crypto';
import dotenv from 'dotenv'; // For loading environment variables
import fs from 'fs'; // For reading the Google credentials JSON file
dotenv.config(); // Load .env variables
const app = express();
const port = 3000;
app.use(express.json());
// Mongo URI from .env
const uri = process.env.MONGO_URI;
const client = new MongoClient(uri);
const dbName = 'inventory';
const collectionName = 'items';
// Load Google credentials from the key.json file
const googleCredentialsPath = process.env.GOOGLE_CREDENTIALS_PATH;
const googleApisKey = JSON.parse(fs.readFileSync(googleCredentialsPath, 'utf8'));
// Initialize Google Sheets API authentication
const auth = new google.auth.JWT(
googleApisKey.client_email,
null,
googleApisKey.private_key,
['https://www.googleapis.com/auth/spreadsheets']
);
const spreadsheetId = process.env.SPREADSHEET_ID;
const sheets = google.sheets({ version: 'v4', auth });
const range = 'Sheet1!A:H';
// Connect to MongoDB
async function connectDB() {
try {
await client.connect();
console.log('Connected to MongoDB!');
} catch (err) {
console.error('Error connecting to MongoDB:', err);
process.exit(1);
}
}
connectDB();
// Generate Serial Number
function generateSerialNumber() {
return `Jadupc${crypto.randomInt(100000, 999999)}`;
}
// Route to upload device details
app.post('/upload', async (req, res) => {
const { Verient, mac, os, status, customerAddress, customerNumber, customerName } = req.body;
if (!Verient || !mac || !os) {
return res.status(400).send('Missing required fields: Verient, mac, os, or status');
}
// Check if device already exists in database
try {
const db = client.db(dbName);
const collection = db.collection(collectionName);
const existingDevice = await collection.findOne({ mac });
if (existingDevice) {
return res.status(400).send('Device with the same MAC address is already registered');
}
// Generate serial number
const serialNumber = generateSerialNumber();
const newItem = {
serialNumber,
Verient,
mac,
os,
status: status || 'registered',
customerAddress,
customerNumber,
customerName,
};
await collection.insertOne(newItem);
console.log('Item successfully added to MongoDB');
try {
// Append data to Google Sheets
const response = await sheets.spreadsheets.values.append({
spreadsheetId,
range,
valueInputOption: 'USER_ENTERED',
requestBody: {
values: [[serialNumber, Verient, mac, os, status, customerAddress, customerNumber, customerName]],
},
});
console.log('Data successfully appended to Google Sheets');
res.status(200).send('Device uploaded and added to MongoDB and Google Sheets. Serial Number: ' + serialNumber);
} catch (err) {
console.error('Error appending to Google Sheets:', err);
res.status(500).send('Error uploading to Google Sheets');
}
} catch (err) {
console.error('Error inserting item into MongoDB:', err);
res.status(500).send('Error uploading item to MongoDB');
}
});
// Route to check device status
app.get('/check-status', async (req, res) => {
const { mac, serialNumber } = req.query;
if (!mac && !serialNumber) {
return res.status(400).send('MAC or Serial Number required');
}
try {
const db = client.db(dbName);
const collection = db.collection(collectionName);
const device = await collection.findOne(mac ? { mac } : { serialNumber });
if (!device) {
return res.status(200).json({
serialNumber: null,
status: 'new' // Default status if not found
});
}
res.status(200).json({
serialNumber: device.serialNumber,
status: device.status // Default status if missing
});
} catch (err) {
res.status(500).send('Error checking device status');
}
});
// Start the server
app.listen(port, () => console.log(`Server running on http://localhost:${port}`));
|
