· 9 years ago · Feb 04, 2017, 05:32 PM
1var mysql = require("mysql");
2var md5 = require('md5');
3var open=require('open');
4path = require('path');
5const URL_SERVER="http://localhost:8081";
6var xlsx = require('node-xlsx');
7
8const getContent = function (url) {
9 // return new pending promise
10 return new Promise((resolve, reject) => {
11 // select http or https module, depending on reqested url
12 const lib = url.startsWith('https') ? require('https') : require('http');
13 const request = lib.get(url, (response) => {
14 // handle http errors
15 if (response.statusCode < 200 || response.statusCode > 299
16 ) {
17 reject(new Error('Failed to load page, status code: ' + response.statusCode));
18 }
19 // temporary data holder
20 const body = [];
21 // on every content chunk, push it to the data array
22 response.on('data', (chunk) => body.push(chunk)
23 )
24 ;
25 // we are done, resolve promise with those joined chunks
26 response.on('end', () => resolve(body.join(''))
27 )
28 ;
29 })
30 ;
31 // handle connection errors of the request
32 request.on('error', (err) => reject(err)
33 )
34 })
35};
36
37function REST_ROUTER(router, connection) {
38 var self = this;
39 self.handleRoutes(router, connection);
40}
41
42REST_ROUTER.prototype.handleRoutes = function (router, connection) {
43
44 router.get("/", function (req, res) {
45 res.json({"Message": "Hello World Welcome To API!"});
46 });
47
48
49 // -- -----------------------------------------------------
50 // -- Table `bio_beam_register`.`user_login`
51 // -- -----------------------------------------------------
52
53 router.post("/users", function (req, res) {
54 var query = "INSERT INTO ??(??,??) VALUES (?,?)";
55 var table = ["user_login", "user_name", "user_password"
56 , req.body.user_name, md5(req.body.password)];
57 query = mysql.format(query, table);
58 connection.query(query, function (err, rows) {
59 if (err) {
60 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
61 } else {
62 res.json({"Error": false, "Message": "User Added !"});
63 }
64 });
65 });
66
67 router.get("/users", function (req, res) {
68 var query = "SELECT * FROM ??";
69 var table = ["user_login"];
70 query = mysql.format(query, table);
71 connection.query(query, function (err, rows) {
72 if (err) {
73 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
74 } else {
75 res.json({"Error": false, "Message": "Success", "Users": rows});
76 }
77 });
78 });
79
80 router.get("/users/:id", function (req, res) {
81 var query = "SELECT * FROM ?? WHERE ??=?";
82 var table = ["user_login", "id", req.params.id];
83 query = mysql.format(query, table);
84 connection.query(query, function (err, rows) {
85 if (err) {
86 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
87 } else {
88 res.json({"Error": false, "Message": "Success", "Users": rows});
89 }
90 });
91 });
92
93 router.put("/users", function (req, res) {
94 var query = "UPDATE ??UPDATE ?? SET ?? = ? WHERE ?? = ?";
95 var table = ["user_login", "user_password", md5(req.body.password), "user_name", req.body.user_name];
96 query = mysql.format(query, table);
97 connection.query(query, function (err, rows) {
98 if (err) {
99 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
100 } else {
101 res.json({"Error": false, "Message": "Updated the password for user_name " + req.body.user_name});
102 }
103 });
104 });
105
106 router.delete("/users/:user_name", function (req, res) {
107 var query = "DELETE from ?? WHERE ??=?";
108 var table = ["user_login", "user_name", req.params.user_name];
109 query = mysql.format(query, table);
110 connection.query(query, function (err, result) {
111 if (err) {
112 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
113 } else {
114 res.json({"Error": false, "Message": "Deleted the user with user_name " + result});
115 }
116 });
117 });
118
119 // -- -----------------------------------------------------
120 // -- Table `bio_beam_register`.`banking_information`
121 // -- -----------------------------------------------------
122
123 // SEARCH
124
125 router.get("/BankInfo", function (req, res) {
126
127 // Logic to create regex for telSearch
128 var telRegex = '';
129 if(req.query.tel != null){
130 var tel = req.query.tel.replace('-', '');
131 for(var t = 0; t < tel.length; t++){
132 if(t == tel.length - 1){
133 telRegex = telRegex + tel[t];
134 }
135 else{
136 telRegex = telRegex + tel[t] + '[() -]*';
137 }
138 }
139 }
140 else{
141 telRegex = '[0-9]{1,3}[() -]*[0-9]{1,3}[() -]*[0-9]{1,4}';
142 }
143
144
145 var table = ["banking_information", "course",
146 req.query.course, req.query.course, req.query.course,
147 req.query.year, req.query.year, req.query.year,
148 req.query.semester, req.query.semester, req.query.semester,
149 telRegex, req.query.tel, req.query.tel,
150 "%" + req.query.name + "%", req.query.name, req.query.name,
151 req.query.is_print, req.query.is_print, req.query.is_print
152 ];
153
154 var query = "SELECT " +
155 "temp1.*," +
156 "temp2.description," +
157 "temp2.capacity," +
158 "temp2.price," +
159 "temp2.start_date," +
160 "temp2.end_date," +
161 "temp2.start_time," +
162 "temp2.end_time," +
163 "temp2.short_description," +
164 "temp2.branch," +
165 "temp2.type " +
166 "FROM ?? temp1 " +
167 "LEFT JOIN ?? temp2 ON " +
168 "temp1.year = temp2.year AND " +
169 "temp1.semester = temp2.semester AND " +
170 "temp1.course = temp2.course " +
171 "WHERE " +
172 "(temp1.course = ? OR ? IS NULL OR ? = '') AND " +
173 "(temp1.year = ? OR ? IS NULL OR ? = '') AND " +
174 "(temp1.semester = ? OR ? IS NULL OR ? = '') AND " +
175 "(temp1.tel REGEXP ? OR ? IS NULL OR ? = '') AND " +
176 "(temp1.name LIKE ? OR ? IS NULL OR ? = '') AND " +
177 "(temp1.is_print = ? OR ? IS NULL OR ? = '') AND " +
178 "temp1.is_deleted = 0" +
179 ";";
180
181 query = mysql.format(query, table);
182 connection.query(query, function (err, rows) {
183 if (err) {
184 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
185 } else {
186 res.json({"Error": false, "Message": "Success", "Count": rows.length, "bankingInformations": rows});
187 }
188 });
189 });
190
191 router.get("/BankInfo/:id", function (req, res) {
192 var query = "SELECT * FROM ?? WHERE ??=?";
193 var table = ["banking_information", "id", req.params.id];
194 query = mysql.format(query, table);
195 connection.query(query, function (err, row) {
196 if (err) {
197 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
198 } else {
199 res.json({"Error": false, "Message": "Success", "bankingInformation": row});
200 }
201 });
202 });
203
204 router.get("/softDelete/:id", function (req, res) {
205 var query = "UPDATE ?? SET is_delete=1 WHERE ??=?";
206 var table = ["banking_information", "id", req.params.id];
207 query = mysql.format(query, table);
208 connection.query(query, function (err, row) {
209 if (err) {
210 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
211 } else {
212 res.json({"Error": false, "Message": "Success"});
213 }
214 });
215 });
216
217 router.get("/retrieveFromDeleted/:id", function (req, res) {
218 var query = "UPDATE ?? SET is_delete=0 WHERE ??=?";
219 var table = ["banking_information", "id", req.params.id];
220 query = mysql.format(query, table);
221 connection.query(query, function (err, row) {
222 if (err) {
223 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
224 } else {
225 res.json({"Error": false, "Message": "Success"});
226 }
227 });
228 });
229
230 router.post("/BankInfo", function (req, res) {
231 var query = "INSERT INTO ?? SET ?";
232 var bankingInformation = req.body.bankingInformation;
233 var table = ["banking_information", bankingInformation];
234 query = mysql.format(query, table);
235 connection.query(query, function (err, result) {
236 if (err) {
237 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
238 } else {
239 res.json({"Error": false, "Message": "Success"});
240 }
241 });
242 });
243
244 router.put("/BankInfo/:id", function (req, res) {
245 var query = "UPDATE ?? SET ? WHERE ??=?";
246 var bankingInformation = req.body.bankingInformation;
247 var table = ["banking_information", bankingInformation, "id", req.params.id];
248 query = mysql.format(query, table);
249 connection.query(query, function (err, result) {
250 if (err) {
251 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
252 } else {
253 res.json({"Error": false, "Message": "Success"});
254 }
255 });
256 });
257
258 router.delete("/BankInfo/:id", function (req, res) {
259 var query = "DELETE from ?? WHERE ??=?";
260 var table = ["banking_information", "id", req.params.id];
261 query = mysql.format(query, table);
262 connection.query(query, function (err, result) {
263 if (err) {
264 res.json({"Error": true, "Message": "Error executing MySQL query", "Code": err.code});
265 } else {
266 res.json({"Error": false});
267 }
268 });
269 });
270
271 // -- -----------------------------------------------------
272 // -- Table `bio_beam_register`.`course`
273 // -- -----------------------------------------------------
274
275 // SEARCH
276
277
278 router.get("/courses", function (req, res) {
279 var query = "SELECT * FROM ?? " +
280 "WHERE " +
281 "(year = ? OR ? IS NULL OR ? = '') AND " +
282 "(semester = ? OR ? IS NULL OR ? = '') AND " +
283 "(course LIKE ? OR ? IS NULL OR ? = '')" +
284 ";";
285 var table = ["course",
286 req.query.year, req.query.year, req.query.year,
287 req.query.semester, req.query.semester, req.query.semester,
288 "%" + req.query.course + "%", req.query.course, req.query.course
289 ];
290 query = mysql.format(query, table);
291 connection.query(query, function (err, rows) {
292 if (err) {
293 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
294 } else {
295 res.json({"Error": false, "Message": "Success", "Count": rows.length, "courses": rows});
296 }
297 });
298 });
299
300
301 router.get("/courses/:id", function (req, res) {
302 var query = "SELECT * FROM ?? WHERE ??=?";
303 var table = ["course", "id", req.params.id];
304 query = mysql.format(query, table);
305 connection.query(query, function (err, row) {
306 if (err) {
307 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
308 } else {
309 res.json({"Error": false, "Message": "Success", "course": row});
310 }
311 });
312 });
313
314 router.post("/courses", function (req, res) {
315 var query = "INSERT INTO ?? SET ?";
316 var course = req.body.course;
317 var table = ["course", course];
318 query = mysql.format(query, table);
319 connection.query(query, function (err, result) {
320 if (err) {
321 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
322 } else {
323 res.json({"Error": false, "Message": "Success"});
324 }
325 });
326 });
327
328 router.put("/courses/:id", function (req, res) {
329 var query = "UPDATE ?? SET ? WHERE ??=?";
330 var course = req.body.course;
331 var table = ["course", course, "id", req.params.id];
332 query = mysql.format(query, table);
333 connection.query(query, function (err, result) {
334 if (err) {
335 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
336 } else {
337 res.json({"Error": false, "Message": "Success"});
338 }
339 });
340 });
341
342 router.delete("/courses/:id", function (req, res) {
343 var query = "DELETE from ?? WHERE ??=?";
344 var table = ["course", "id", req.params.id];
345 query = mysql.format(query, table);
346 connection.query(query, function (err, result) {
347 if (err) {
348 res.json({"Error": true, "Message": "Error executing MySQL query", "Code": err.code});
349 } else {
350 res.json({"Error": false});
351 }
352 });
353 });
354
355 // -- -----------------------------------------------------
356 // -- Table `bio_beam_register`.`room`
357 // -- -----------------------------------------------------
358
359 router.get("/seats", function (req, res) {
360 var query = "SELECT temp1.year, temp1.semester, temp1.course, temp1.room, temp2.floor, temp2.seat FROM ?? temp1 " +
361 "LEFT JOIN ?? temp2 ON " +
362 "temp1.year = temp2.year AND " +
363 "temp1.semester = temp2.semester AND " +
364 "temp1.room = temp2.room " +
365 "WHERE " +
366 "(temp1.year = ? OR ? IS NULL OR ? = '') AND " +
367 "(temp1.semester = ? OR ? IS NULL OR ? = '') AND " +
368 "(temp1.course = ? OR ? IS NULL OR ? = '') AND " +
369 "(temp1.room = ? OR ? IS NULL OR ? = '') AND " +
370 "(temp2.floor = ? OR ? IS NULL OR ? = '') " +
371 ";";
372 var table = ["room_course", "room_plan",
373 req.query.year, req.query.year, req.query.year,
374 req.query.semester, req.query.semester, req.query.semester,
375 req.query.course, req.query.course, req.query.course,
376 req.query.room, req.query.room, req.query.room,
377 req.query.floor, req.query.floor, req.query.floor,
378 ];
379 query = mysql.format(query, table);
380 connection.query(query, function (err, rows) {
381 if (err) {
382 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
383 } else {
384 res.json({"Error": false, "Message": "Success", "Count": rows.length, "seats": rows});
385 }
386 });
387 });
388
389
390 // -- -----------------------------------------------------
391 // -- Table `bio_beam_register`.`room_course`
392 // -- -----------------------------------------------------
393
394 // SEARCH
395
396
397 router.get("/roomCourses", function (req, res) {
398 var query = "SELECT * FROM ?? " +
399 "WHERE " +
400 "(year = ? OR ? IS NULL OR ? = '') AND " +
401 "(semester = ? OR ? IS NULL OR ? = '') AND " +
402 "(course = ? OR ? IS NULL OR ? = '') AND " +
403 "(room = ? OR ? IS NULL OR ? = '')" +
404 ";";
405 var table = ["room_course",
406 req.query.year, req.query.year, req.query.year,
407 req.query.semester, req.query.semester, req.query.semester,
408 req.query.course, req.query.course, req.query.course,
409 req.query.room, req.query.room, req.query.room,
410 ];
411 query = mysql.format(query, table);
412 connection.query(query, function (err, rows) {
413 if (err) {
414 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
415 } else {
416 res.json({"Error": false, "Message": "Success", "Count": rows.length, "roomCourses": rows});
417 }
418 });
419 });
420
421 router.get("/roomCourses/:id", function (req, res) {
422 var query = "SELECT * FROM ?? " +
423 "WHERE ??=?";
424 var table = ["room_course", "id", req.params.id];
425 query = mysql.format(query, table);
426 connection.query(query, function (err, row) {
427 if (err) {
428 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
429 } else {
430 res.json({"Error": false, "Message": "Success", "roomCourse": row});
431 }
432 });
433 });
434
435 router.post("/roomCourses", function (req, res) {
436 var query = "INSERT INTO ?? SET ?";
437 var roomCourse = req.body.roomCourse;
438 console.log(roomCourse)
439 var table = ["room_course", roomCourse];
440 query = mysql.format(query, table);
441 connection.query(query, function (err, result) {
442 if (err) {
443 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
444 } else {
445 res.json({"Error": false, "Message": "Success"});
446 }
447 });
448 });
449
450 router.put("/roomCourses/:id", function (req, res) {
451 var query = "UPDATE ?? SET ? WHERE ??=?";
452 var roomCourse = req.body.roomCourse;
453 var table = ["room_course", roomCourse, "id", req.params.id];
454 query = mysql.format(query, table);
455 connection.query(query, function (err, result) {
456 if (err) {
457 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
458 } else {
459 res.json({"Error": false, "Message": "Success"});
460 }
461 });
462 });
463
464 router.delete("/roomCourses/:id", function (req, res) {
465 var query = "DELETE from ?? WHERE ??=?";
466 var table = ["room_course", "id", req.params.id];
467 query = mysql.format(query, table);
468 connection.query(query, function (err, result) {
469 if (err) {
470 res.json({"Error": true, "Message": "Error executing MySQL query", "Code": err.code});
471 } else {
472 res.json({"Error": false});
473 }
474 });
475 });
476
477
478 // -- -----------------------------------------------------
479 // -- Table `bio_beam_register`.`room_plan`
480 // -- -----------------------------------------------------
481
482 // SEARCH
483
484
485 router.get("/roomPlans", function (req, res) {
486 var query = "SELECT * FROM ?? " +
487 "WHERE " +
488 "(year = ? OR ? IS NULL OR ? = '') AND " +
489 "(semester = ? OR ? IS NULL OR ? = '') AND " +
490 "(room = ? OR ? IS NULL OR ? = '') AND " +
491 "(floor = ? OR ? IS NULL OR ? = '') AND " +
492 "(seat = ? OR ? IS NULL OR ? = '') " +
493 ";";
494 var table = ["room_plan",
495 req.query.year, req.query.year, req.query.year,
496 req.query.semester, req.query.semester, req.query.semester,
497 req.query.room, req.query.room, req.query.room,
498 req.query.floor, req.query.floor, req.query.floor,
499 req.query.seat, req.query.seat, req.query.seat,
500 ];
501 query = mysql.format(query, table);
502 connection.query(query, function (err, rows) {
503 if (err) {
504 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
505 } else {
506 res.json({"Error": false, "Message": "Success", "Count": rows.length, "roomPlans": rows});
507 }
508 });
509 });
510
511 router.get("/roomPlans/:id", function (req, res) {
512 var query = "SELECT * FROM ?? " +
513 "WHERE ??=?";
514 var table = ["room_plan", "id", req.params.id];
515 query = mysql.format(query, table);
516 connection.query(query, function (err, row) {
517 if (err) {
518 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
519 } else {
520 res.json({"Error": false, "Message": "Success", "roomPlan": row});
521 }
522 });
523 });
524
525 router.post("/roomPlans", function (req, res) {
526 var query = "INSERT INTO ?? SET ?";
527 var roomPlan = req.body.roomPlan;
528 console.log(roomPlan)
529 var table = ["room_plan", roomPlan];
530 query = mysql.format(query, table);
531 connection.query(query, function (err, result) {
532 if (err) {
533 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
534 } else {
535 res.json({"Error": false, "Message": "Success"});
536 }
537 });
538 });
539
540 router.put("/roomPlans/:id", function (req, res) {
541 var query = "UPDATE ?? SET ? WHERE ??=?";
542 var roomPlan = req.body.roomPlan;
543 var table = ["room_plan", roomPlan, "id", req.params.id];
544 query = mysql.format(query, table);
545 connection.query(query, function (err, result) {
546 if (err) {
547 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
548 } else {
549 res.json({"Error": false, "Message": "Success"});
550 }
551 });
552 });
553
554 router.delete("/roomPlans/:id", function (req, res) {
555 var query = "DELETE from ?? WHERE ??=?";
556 var table = ["room_plan", "id", req.params.id];
557 query = mysql.format(query, table);
558 connection.query(query, function (err, result) {
559 if (err) {
560 res.json({"Error": true, "Message": "Error executing MySQL query", "Code": err.code});
561 } else {
562 res.json({"Error": false});
563 }
564 });
565 });
566
567 // -- -----------------------------------------------------
568 // -- Table `bio_beam_register`.`year_semester`
569 // -- -----------------------------------------------------
570
571 router.get("/yearSemesters", function (req, res) {
572 var query = "SELECT * FROM ?? " +
573 "WHERE " +
574 "(year = ? OR ? IS NULL OR ? = '') AND " +
575 "(semester = ? OR ? IS NULL OR ? = '') " +
576 "order by year desc, semester desc"
577 ";";
578 var table = ["year_semester",
579 req.query.year, req.query.year, req.query.year,
580 req.query.semester, req.query.semester, req.query.semester
581 ];
582 query = mysql.format(query, table);
583 connection.query(query, function (err, rows) {
584 if (err) {
585 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
586 } else {
587 res.json({"Error": false, "Message": "Success", "Count": rows.length, "yearSemesters": rows});
588 }
589 });
590 });
591
592
593 router.get("/yearSemesters/:id", function (req, res) {
594 var query = "SELECT * FROM ?? WHERE ??=?";
595 var table = ["year_semester", "id", req.params.id];
596 query = mysql.format(query, table);
597 connection.query(query, function (err, row) {
598 if (err) {
599 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
600 } else {
601 res.json({"Error": false, "Message": "Success", "yearSemester": row});
602 }
603 });
604 });
605
606 router.post("/yearSemesters", function (req, res) {
607 var query = "INSERT INTO ?? SET ?";
608 var yearSemester = req.body.yearSemester;
609 var table = ["year_semester", yearSemester];
610 query = mysql.format(query, table);
611 connection.query(query, function (err, result) {
612 if (err) {
613 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
614 } else {
615 res.json({"Error": false, "Message": "Success"});
616 }
617 });
618 });
619
620 router.put("/yearSemesters/:id", function (req, res) {
621 var query = "UPDATE ?? SET ? WHERE ??=?";
622 var yearSemester = req.body.yearSemester;
623 var table = ["year_semester", yearSemester, "id", req.params.id];
624 query = mysql.format(query, table);
625 connection.query(query, function (err, result) {
626 if (err) {
627 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
628 } else {
629 res.json({"Error": false, "Message": "Success"});
630 }
631 });
632 });
633
634 router.delete("/yearSemesters/:id", function (req, res) {
635 var query = "DELETE from ?? WHERE ??=?";
636 var table = ["year_semester", "id", req.params.id];
637 query = mysql.format(query, table);
638 connection.query(query, function (err, result) {
639 if (err) {
640 res.json({"Error": true, "Message": "Error executing MySQL query", "Code": err.code});
641 } else {
642 res.json({"Error": false});
643 }
644 });
645 });
646
647 /// RECAL SEAT
648
649 router.get("/RecalculateSeat", function (req, res) {
650
651 var bankingInformations;
652 console.log("WTF")
653
654 function setBankInfoValue(html) {
655 body = JSON.parse(html)
656 if (body.Error === true) {
657 res.json({"Error": true, "Message": body.Message, "ErrorDetails": body.ErrorDetails});
658 }
659 if (body.bankingInformations.length === 0) {
660 res.json({"Error": true, "Message": "Bank Info not found"});
661 }
662 bankingInformations = body.bankingInformations;
663 }
664
665 function setSeatValue(html) {
666 body = JSON.parse(html)
667 if (body.Error === true) {
668 res.json({"Error": true, "Message": body.Message, "ErrorDetails": body.ErrorDetails});
669 }
670 if (body.seats.length === 0) {
671 res.json({"Error": true, "Message": "Seat not found"});
672 }
673 seats = body.seats;
674 }
675
676 function recalculateSeat(bankingInformations, seats) {
677
678 if (seats.length < bankingInformations.length) {
679 res.json({"Error": true, "Message": "Students capacity more than seat capacity"});
680 }
681
682 bankingInformations.sort((a, b) => {
683 return a.id < b.id
684 })
685
686
687 for (let i = 0; i < bankingInformations.length; i++) {
688 bankingInformations[i].seat = seats[i].seat;
689 bankingInformations[i].room = seats[i].room;
690 bankingInformations[i].floor = seats[i].floor;
691 bankingInformations[i].is_print = 0;
692 }
693
694 let queries = "";
695 bankingInformations.forEach(function (item) {
696 queries += mysql.format("UPDATE banking_information SET seat = ?, room = ?, floor = ?, is_print = ? WHERE id = ?; ", [item.seat, item.room, item.floor, item.is_print, item.id]);
697 });
698
699 connection.query(queries, function (err, result) {
700 if (err) {
701 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
702 } else {
703 res.json({"Error": false, "Message": "Success"});
704 }
705 });
706 }
707
708
709 if (!req.query.course) {
710 res.json({"Error": true, "Message": "Please specify course"});
711 }
712 if (!req.query.year) {
713 res.json({"Error": true, "Message": "Please specify year"});
714 }
715 if (!req.query.semester) {
716 res.json({"Error": true, "Message": "Please specify semester"});
717 }
718
719 getContent(URL_SERVER+'/api/BankInfo?course=' + req.query.course + '&year=' + req.query.year + '&semester=' + req.query.semester)
720 .then((html) => {
721 setBankInfoValue(html)
722 getContent(URL_SERVER+'/api/seats?course=' + req.query.course + '&year=' + req.query.year + '&semester=' + req.query.semester)
723 .then((html) => {
724 setSeatValue(html)
725 recalculateSeat(bankingInformations, seats);
726 })
727 .catch((err) => res.json({"Error": true, "Message": "API Error", "ErrorDetails": err}));
728 })
729 .catch((err) => res.json({"Error": true, "Message": "API Error", "ErrorDetails": err}));
730 });
731
732
733 /// Continue SEAT
734
735 router.get("/ContinueCalculateSeat", function (req, res) {
736
737 var bankingInformations;
738
739 function setBankInfoValue(html) {
740 body = JSON.parse(html)
741 if (body.Error === true) {
742 res.json({"Error": true, "Message": body.Message, "ErrorDetails": body.ErrorDetails});
743 }
744 if (body.bankingInformations.length === 0) {
745 res.json({"Error": true, "Message": "Bank Info not found"});
746 }
747 bankingInformations = body.bankingInformations;
748 }
749
750 function setSeatValue(html) {
751 body = JSON.parse(html)
752 if (body.Error === true) {
753 res.json({"Error": true, "Message": body.Message, "ErrorDetails": body.ErrorDetails});
754 }
755 if (body.seats.length === 0) {
756 res.json({"Error": true, "Message": "Seat not found"});
757 }
758 seats = body.seats;
759 }
760
761 function is_in_bankInfo(seat, room, bankingInformations) {
762 let result = bankingInformations.filter((item) => {
763 return seat === item.seat
764 && room === item.room
765 });
766 if (result.length > 0) {
767 return true;
768 }
769 return false;
770 }
771
772 function continueCalculateSeat(bankingInformations, seats) {
773
774 if (seats.length < bankingInformations.length) {
775 res.json({"Error": true, "Message": "Students capacity more than seat capacity"});
776 }
777
778 let availableSeats = seats.filter((item) => !is_in_bankInfo(item.seat, item.room, bankingInformations));
779
780 let j = 0;
781
782 let noSeatBankingInfo = bankingInformations.filter((item) => {
783 return item.seat === null || item.seat == '';
784 });
785
786 if (noSeatBankingInfo.length === 0) {
787 res.json({"Error": true, "Message": "All seats are assigned"});
788 return;
789 }
790
791 if (availableSeats.length === 0) {
792 res.json({"Error": true, "Message": "No available seats"});
793 return;
794 }
795
796 if (availableSeats.length < noSeatBankingInfo.length) {
797 res.json({"Error": true, "Message": "Students capacity more than seat capacity"});
798 return;
799 }
800
801 noSeatBankingInfo.sort((a, b) => {
802 return a.id < b.id
803 })
804
805 for (let i = 0; i < noSeatBankingInfo.length; i++) {
806 noSeatBankingInfo[i].seat = availableSeats[i].seat;
807 noSeatBankingInfo[i].room = availableSeats[i].room;
808 noSeatBankingInfo[i].floor = availableSeats[i].floor;
809 noSeatBankingInfo[i].is_print = 0;
810 }
811
812 let queries = "";
813
814 noSeatBankingInfo.forEach(function (item) {
815 queries += mysql.format("UPDATE banking_information SET seat = ?, room = ?, floor = ?, is_print = ? WHERE id = ?; ", [item.seat, item.room, item.floor, item.is_print, item.id]);
816 });
817
818 connection.query(queries, function (err, result) {
819 if (err) {
820 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
821 } else {
822 res.json({"Error": false, "Message": "Success"});
823 }
824 });
825 }
826
827 if (!req.query.course) {
828 res.json({"Error": true, "Message": "Please specify course"});
829 }
830 if (!req.query.year) {
831 res.json({"Error": true, "Message": "Please specify year"});
832 }
833 if (!req.query.semester) {
834 res.json({"Error": true, "Message": "Please specify semester"});
835 }
836
837 getContent('http://127.0.0.1:8081/api/BankInfo?course=' + req.query.course + '&year=' + req.query.year + '&semester=' + req.query.semester)
838 .then((html) => {
839 setBankInfoValue(html)
840 getContent('http://127.0.0.1:8081/api/seats?course=' + req.query.course + '&year=' + req.query.year + '&semester=' + req.query.semester)
841 .then((html) => {
842 setSeatValue(html)
843 continueCalculateSeat(bankingInformations, seats);
844 })
845 .catch((err) => res.json({"Error": true, "Message": "API Error", "ErrorDetails": err}));
846 })
847 .catch((err) => res.json({"Error": true, "Message": "API Error", "ErrorDetails": err}));
848 });
849 // ******************************generate csv ****************************************************
850 function convertExcelToCsvBankInfo(){
851 var absPath = path.join(__dirname, "../../");
852 var filePath=absPath+'uploads/uploadBankInformation.xls';
853 var name = filePath;
854 var rows = [];
855 var writeStr = "";
856 var obj = xlsx.parse(name);
857 var fs = require('fs');
858 //looping through all sheetss
859 for(var i = 0; i < obj.length; i++)
860 {
861 var sheet = obj[i];
862 //loop through all rows in the sheet
863 for(var j = 0; j < sheet['data'].length; j++)
864 {
865 //add the row to the rows array
866 rows.push(sheet['data'][j]); //sheet name
867
868 }
869 }
870 //creates the csv string to write it to a file
871 for(var i = 0; i < rows.length; i++)
872 {
873 writeStr += rows[i].join(",") + "\n";
874 //res.json({"Error": rows[i]});
875 }
876 //console.log(writeStr);
877
878 fs.writeFile(absPath+'uploads/uploadBankInformation.csv', writeStr, function(err) {
879 if(err) {
880 return console.log(err);
881 }
882 console.log("uploadBankInformation.csv was saved in the current directory!");
883});
884}
885 function convertExcelToCsvCourse(){
886 var absPath = path.join(__dirname, "../../");
887 var filePath=absPath+'uploads/course.xls';
888 var name = filePath;
889 var rows = [];
890 var writeStr = "";
891 var obj = xlsx.parse(name);
892 var fs = require('fs');
893 //looping through all sheetss
894 for(var i = 0; i < obj.length; i++)
895 {
896 var sheet = obj[i];
897 //loop through all rows in the sheet
898 for(var j = 0; j < sheet['data'].length; j++)
899 {
900 //add the row to the rows array
901 rows.push(sheet['data'][j]); //sheet name
902
903 }
904 }
905 //creates the csv string to write it to a file
906 for(var i = 0; i < rows.length; i++)
907 {
908 writeStr += rows[i].join(",") + "\n";
909 //res.json({"Error": rows[i]});
910 }
911 //console.log(writeStr);
912
913 fs.writeFile(absPath+'uploads/course.csv', writeStr, function(err) {
914 if(err) {
915 return console.log(err);
916 }
917 console.log("course.csv was saved in the current directory!");
918});
919}
920 function convertExcelToCsvRoomCourse(){
921 var absPath = path.join(__dirname, "../../");
922 var filePath=absPath+'uploads/roomCourse.xls';
923 var name = filePath;
924 var rows = [];
925 var writeStr = "";
926 var obj = xlsx.parse(name);
927 var fs = require('fs');
928 //looping through all sheetss
929 for(var i = 0; i < obj.length; i++)
930 {
931 var sheet = obj[i];
932 //loop through all rows in the sheet
933 for(var j = 0; j < sheet['data'].length; j++)
934 {
935 //add the row to the rows array
936 rows.push(sheet['data'][j]); //sheet name
937
938 }
939 }
940 //creates the csv string to write it to a file
941 for(var i = 0; i < rows.length; i++)
942 {
943 writeStr += rows[i].join(",") + "\n";
944 //res.json({"Error": rows[i]});
945 }
946 //console.log(writeStr);
947
948 fs.writeFile(absPath+'uploads/roomCourse.csv', writeStr, function(err) {
949 if(err) {
950 return console.log(err);
951 }
952 console.log("roomCourse.csv was saved in the current directory!");
953});
954}
955 function convertExcelToCsvRoomPlan(){
956 var absPath = path.join(__dirname, "../../");
957 var filePath=absPath+'uploads/roomPlan.xls';
958 var name = filePath;
959 var rows = [];
960 var writeStr = "";
961 var obj = xlsx.parse(name);
962 var fs = require('fs');
963 //looping through all sheetss
964 for(var i = 0; i < obj.length; i++)
965 {
966 var sheet = obj[i];
967 //loop through all rows in the sheet
968 for(var j = 0; j < sheet['data'].length; j++)
969 {
970 //add the row to the rows array
971 rows.push(sheet['data'][j]); //sheet name
972
973 }
974 }
975 //creates the csv string to write it to a file
976 for(var i = 0; i < rows.length; i++)
977 {
978 writeStr += rows[i].join(",") + "\n";
979 //res.json({"Error": rows[i]});
980 }
981 //console.log(writeStr);
982
983 fs.writeFile(absPath+'uploads/roomPlan.csv', writeStr, function(err) {
984 if(err) {
985 return console.log(err);
986 }
987 console.log("roomPlan.csv was saved in the current directory!");
988});
989}
990//*************************************generate csv end ******************************************
991 router.post("/importFromFile/BankInfo", function (req, res) {
992 convertExcelToCsvBankInfo();
993 var query = "" +
994 "DROP TEMPORARY TABLE IF EXISTS tempBankInformation; "+
995 "set @year= ? ; "+
996 "set @semester= ? ;"+
997 "DELETE FROM `banking_information` WHERE year = @year AND semester= @semester;"+
998 "CREATE TEMPORARY TABLE IF NOT EXISTS tempBankInformation ( " +
999 "`tel` VARCHAR(50) NOT NULL, " +
1000 "`price` DOUBLE NOT NULL, " +
1001 "`name` VARCHAR(100) NOT NULL, " +
1002 "`time_of_interval` VARCHAR(200) NOT NULL, " +
1003 "`course` VARCHAR(100) NOT NULL, " +
1004 "`account` VARCHAR(100) DEFAULT NULL" +
1005 "); " +
1006 // "TRUNCATE TABLE `banking_information`; " +
1007 "TRUNCATE TABLE tempBankInformation; " +
1008 " LOAD DATA LOCAL INFILE ? INTO TABLE tempBankInformation FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES; " +
1009 " ALTER TABLE `tempBankInformation` ADD COLUMN `year` VARCHAR(16) DEFAULT NULL AFTER `account`, ADD COLUMN `semester` INT(16) DEFAULT NULL AFTER `year` ; " +
1010 "UPDATE tempBankInformation SET course =TRIM(TRAILING '\r' FROM course); "+
1011 "UPDATE tempBankInformation SET course =TRIM(TRAILING '\n' FROM course); "+
1012
1013 " delete from `banking_information` where year = @year and semester = @semester;" +
1014 "INSERT INTO `banking_information` (year, source, semester, tel, name, price, time_of_interval, course, account) " +
1015 "SELECT " +
1016 "@year, " +
1017 "'CSV', " +
1018 "@semester, " +
1019 "temp1.tel, " +
1020 "temp1.name, " +
1021 "temp2.price, " +
1022 // "temp1.time_of_interval, " +
1023 " now(), "+
1024 "temp1.course, " +
1025 "temp1.account " +
1026 "FROM `tempBankInformation` temp1 " +
1027 "LEFT JOIN `course` temp2 " +
1028 "ON temp1.course = temp2.course AND temp1.year = temp2.year AND temp1.semester = temp2.semester ;" +
1029 "DROP TEMPORARY TABLE IF EXISTS tempBankInformation; ";
1030 //var filePath = req.body.filePath;
1031 var absPath = path.join(__dirname, "../../");
1032 var filePath=absPath+'uploads/uploadBankInformation.csv';
1033
1034 var year = req.body.year;
1035 var semester = req.body.semester;
1036
1037
1038 var table = [year, semester, filePath ];
1039 query = mysql.format(query, table);
1040 connection.query(query, function (err, result) {
1041 if (err) {
1042 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
1043 } else {
1044 res.json({"Error": false, "Message": "Success", "bank": result});
1045 }
1046 });
1047 });
1048router.post("/importFromFile/BankInfoContinue", function (req, res) {
1049 convertExcelToCsvBankInfo();
1050 var query = "" +
1051 "DROP TEMPORARY TABLE IF EXISTS tempBankInformation; "+
1052 "set @year= ? ; "+
1053 "set @semester= ? ;"+
1054 "CREATE TEMPORARY TABLE IF NOT EXISTS tempBankInformation ( " +
1055 "`tel` VARCHAR(50) NOT NULL, " +
1056 "`price` DOUBLE NOT NULL, " +
1057 "`name` VARCHAR(100) NOT NULL, " +
1058 "`time_of_interval` VARCHAR(200) NOT NULL, " +
1059 "`course` VARCHAR(100) NOT NULL, " +
1060 "`account` VARCHAR(100) DEFAULT NULL" +
1061 "); " +
1062 // "TRUNCATE TABLE `banking_information`; " +
1063 "TRUNCATE TABLE tempBankInformation; " +
1064 " LOAD DATA LOCAL INFILE ? INTO TABLE tempBankInformation FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES; " +
1065 " ALTER TABLE `tempBankInformation` ADD COLUMN `year` VARCHAR(16) DEFAULT NULL AFTER `account`, ADD COLUMN `semester` INT(16) DEFAULT NULL AFTER `year` ; " +
1066 "UPDATE tempBankInformation SET course =TRIM(TRAILING '\r' FROM course); "+
1067 "UPDATE tempBankInformation SET course =TRIM(TRAILING '\n' FROM course); "+
1068
1069 "INSERT INTO `banking_information` (year, source, semester, tel, name, price, time_of_interval, course, account) " +
1070 "SELECT " +
1071 "@year, " +
1072 "'CSV', " +
1073 "@semester, " +
1074 "temp1.tel, " +
1075 "temp1.name, " +
1076 "temp2.price, " +
1077 // "temp1.time_of_interval, " +
1078 " now(), "+
1079 "temp1.course, " +
1080 "temp1.account " +
1081 "FROM `tempBankInformation` temp1 " +
1082 "LEFT JOIN `course` temp2 " +
1083 "ON temp1.course = temp2.course AND temp1.year = temp2.year AND temp1.semester = temp2.semester ;" +
1084 "DROP TEMPORARY TABLE IF EXISTS tempBankInformation; ";
1085 //var filePath = req.body.filePath;
1086 var absPath = path.join(__dirname, "../../");
1087 var filePath=absPath+'uploads/uploadBankInformation.csv';
1088
1089 var year = req.body.year;
1090 var semester = req.body.semester;
1091
1092 var table = [year, semester, filePath ];
1093 query = mysql.format(query, table);
1094 connection.query(query, function (err, result) {
1095 if (err) {
1096 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
1097 } else {
1098 res.json({"Error": false, "Message": "Success", "bank": result});
1099 }
1100 });
1101 });
1102
1103 router.post("/importFromFile/course", function (req, res) {
1104 convertExcelToCsvCourse();
1105 var query = "" +
1106 " SET SQL_SAFE_UPDATES = 0; " +
1107 "DROP TEMPORARY TABLE IF EXISTS tempCourse; " +
1108 "set @year=?; "+
1109 "set @semester=?;"+
1110 "CREATE TEMPORARY TABLE IF NOT EXISTS tempCourse ( " +
1111 "`no` INT(11) NOT NULL, " +
1112 "`company_account` VARCHAR(20) NOT NULL, " +
1113 "`course` VARCHAR(10) NOT NULL, " +
1114 "`description` VARCHAR(100) DEFAULT NULL, " +
1115 "`capacity` INT(11) NOT NULL, " +
1116 "`total_balance` DOUBLE DEFAULT NULL, " +
1117 "`price` DOUBLE DEFAULT NULL, " +
1118 "`start_date` VARCHAR(100) DEFAULT NULL, " +
1119 "`end_date` VARCHAR(100) DEFAULT NULL, " +
1120 "`start_time` VARCHAR(100) DEFAULT NULL, " +
1121 "`ledger_bal` VARCHAR(100) DEFAULT NULL, " +
1122 "`type` VARCHAR(100) DEFAULT NULL " +
1123 "); " +
1124 "DELETE FROM `course` WHERE year= @year AND semester=@semester ; " +
1125 "TRUNCATE TABLE tempCourse; " +
1126 "LOAD DATA LOCAL INFILE ? INTO TABLE tempCourse " +
1127 "FIELDS TERMINATED BY ',' " +
1128 "ENCLOSED BY '\"' " +
1129 "LINES TERMINATED BY '\n' " +
1130 "IGNORE 1 LINES; " +
1131 "INSERT INTO `bio_beam_register`.`course` (`company_account`, `year`, `semester`, `source`, `course`, `description`, `capacity`, `total_balance`, `price`, `start_date`, `end_date`, `start_time`, `branch`) " +
1132 "SELECT " +
1133 "temp.company_account, " +
1134 "@year, " +
1135 "@semester, " +
1136 "'BANK', " +
1137 "temp.course, " +
1138 "temp.description, " +
1139 "temp.capacity, " +
1140 "temp.total_balance, " +
1141 "temp.price, " +
1142 "temp.start_date, " +
1143 "temp.end_date, " +
1144 "temp.start_time, " +
1145 "'PAYATHAI' " +
1146 "FROM tempCourse temp ;" +
1147 "DROP TEMPORARY TABLE IF EXISTS tempCourse; ";
1148 //var filePath = req.body.filePath;
1149 var absPath = path.join(__dirname, "../../");
1150 var filePath=absPath+'uploads/course.csv';
1151 var year = req.body.year;
1152 var semester = req.body.semester;
1153 var table = [year, semester,filePath];
1154 query = mysql.format(query, table);
1155 connection.query(query, function (err, result) {
1156 if (err) {
1157 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
1158 } else {
1159 res.json({"Error": false, "Message": "Success", "bank": result});
1160 }
1161 });
1162 });
1163
1164 router.post("/importFromFile/roomPlan", function (req, res) {
1165 convertExcelToCsvRoomPlan();
1166 var query =""+
1167 "DROP TEMPORARY TABLE IF EXISTS tempRoom; " +
1168 "set @year = ?;"+
1169 "set @semester = ?;"+
1170 " CREATE TEMPORARY TABLE IF NOT EXISTS tempRoom ( "+
1171 "`v1` VARCHAR(10) NOT NULL,"+
1172 "`v2` VARCHAR(10) NOT NULL,"+
1173 "`v3` VARCHAR(10) NOT NULL,"+
1174 "`v4` VARCHAR(10) NOT NULL,"+
1175 "`v5` VARCHAR(10) NOT NULL,"+
1176 "`v6` VARCHAR(10) NOT NULL,"+
1177 "`v7` VARCHAR(10) NOT NULL,"+
1178 "`v8` VARCHAR(10) NOT NULL,"+
1179 "`v9` VARCHAR(10) NOT NULL,"+
1180 "`v10` VARCHAR(10) NOT NULL,"+
1181 "`v11` VARCHAR(10) NOT NULL,"+
1182 "`v12` VARCHAR(10) NOT NULL,"+
1183 "`v13` VARCHAR(10) NOT NULL,"+
1184 "`v14` VARCHAR(10) NOT NULL,"+
1185 "`v15` VARCHAR(10) NOT NULL,"+
1186 "`v16` VARCHAR(10) NOT NULL"+
1187 ");"+
1188 "DELETE FROM `room_plan` WHERE year=@year AND semester=@semester;"+
1189 "TRUNCATE TABLE tempRoom; "+
1190 "LOAD DATA LOCAL INFILE ? INTO TABLE tempRoom FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES; "+
1191 "UPDATE tempRoom SET v16 =TRIM(TRAILING '\r' FROM v16); "+
1192 "UPDATE tempRoom SET v16 =TRIM(TRAILING '\n' FROM v16); "+
1193 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)"+
1194 "SELECT @year, @semester, 'V1','12A',temp.v1 FROM tempRoom temp where temp.v1 != '' ;"+
1195 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1196 "SELECT @year, @semester, 'V2','12A',temp.v2 FROM tempRoom temp where temp.v2 != '' ;"+
1197 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1198 "SELECT @year, @semester, 'V3','12A',temp.v3 FROM tempRoom temp where temp.v3 != '' ;"+
1199 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`)"+
1200 "SELECT @year, @semester, 'V4','12A',temp.v4 FROM tempRoom temp where temp.v4 != '' ;"+
1201 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1202 "SELECT @year, @semester, 'V5','12A',temp.v5 FROM tempRoom temp where temp.v5 != '' ;"+
1203 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1204 "SELECT @year, @semester, 'V6','12A',temp.v6 FROM tempRoom temp where temp.v6 != '' ;"+
1205 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1206 "SELECT @year, @semester, 'V7','12A',temp.v7 FROM tempRoom temp where temp.v7 != '' ;"+
1207 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1208 "SELECT @year, @semester, 'V8','12A',temp.v8 FROM tempRoom temp where temp.v8 != '' ;"+
1209 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1210 "SELECT @year, @semester, 'V9','12A',temp.v9 FROM tempRoom temp where temp.v9 != '' ;"+
1211 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1212 "SELECT @year, @semester, 'V10','12A',temp.v10 FROM tempRoom temp where temp.v10 != '' ;"+
1213 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1214 "SELECT @year, @semester, 'V11','12A',temp.v11 FROM tempRoom temp where temp.v11 != '' ;"+
1215 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1216 "SELECT @year, @semester, 'V12','12A',temp.v12 FROM tempRoom temp where temp.v12 != '' ;"+
1217 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1218 "SELECT @year, @semester, 'V13','12A',temp.v13 FROM tempRoom temp where temp.v13 != '' ;"+
1219 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1220 "SELECT @year, @semester, 'V14',3,temp.v14 FROM tempRoom temp where temp.v14 != '' ;"+
1221 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1222 "SELECT @year, @semester, 'V15',3,temp.v15 FROM tempRoom temp where temp.v15 != '' ;"+
1223 "INSERT INTO room_plan (`year`, `semester`, `room`,`floor`, `seat`) "+
1224 "SELECT @year, @semester, 'V16',3, temp.v16 FROM tempRoom temp where temp.v16 != '';"+
1225 "UPDATE room_plan SET room =TRIM(TRAILING '\r' FROM room);"+
1226 "UPDATE room_plan SET room =TRIM(TRAILING '\n' FROM room);"+
1227 "UPDATE room_plan SET seat =TRIM(TRAILING '\r' FROM seat);"+
1228 "UPDATE room_plan SET seat =TRIM(TRAILING '\n' FROM seat);"+
1229 "DROP TEMPORARY TABLE IF EXISTS tempRoom;"
1230 // var filePath = req.body.filePath;
1231 // var year = req.body.year;
1232 // var semester = req.body.semester;
1233 var absPath = path.join(__dirname, "../../");
1234 var filePath=absPath+'uploads/roomPlan.csv';
1235
1236 var year = req.body.year;
1237 var semester = req.body.semester;
1238
1239 var table = [year, semester,filePath];
1240 query = mysql.format(query, table);
1241 connection.query(query, function (err, result) {
1242 if (err) {
1243 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
1244 } else {
1245 res.json({"Error": false, "Message": "Success", "bank": result});
1246 }
1247 });
1248 });
1249
1250 router.post("/importFromFile/roomCourse", function (req, res) {
1251 convertExcelToCsvRoomCourse();
1252 var query = "" +
1253 "DROP TEMPORARY TABLE IF EXISTS tempRoom; " +
1254 "set @year=?; "+
1255 "set @semester=?;"+
1256 "DELETE FROM `room_course` WHERE year=@year AND semester=@semester;"+
1257 "CREATE TEMPORARY TABLE IF NOT EXISTS tempRoom ( " +
1258 "`course` VARCHAR(10) NOT NULL, " +
1259 "`room` VARCHAR(10) NOT NULL " +
1260 ");" +
1261 "TRUNCATE TABLE room_course; " +
1262 "TRUNCATE TABLE tempRoom; " +
1263 "LOAD DATA LOCAL INFILE ? INTO TABLE tempRoom " +
1264 "FIELDS TERMINATED BY ',' " +
1265 "ENCLOSED BY '\"' " +
1266 "LINES TERMINATED BY '\n' " +
1267 "IGNORE 1 LINES; " +
1268 "INSERT INTO room_course (`year`, `semester`,`course`, `room`) " +
1269 "SELECT " +
1270 "@year, " +
1271 "@semester, " +
1272 "temp.course, " +
1273 "temp.room " +
1274 "FROM tempRoom temp;" +
1275 "UPDATE room_course SET room =TRIM(TRAILING '\r' FROM room);"+
1276 "UPDATE room_course SET room =TRIM(TRAILING '\n' FROM room);"+
1277 "UPDATE room_course SET course =TRIM(TRAILING '\r' FROM course);"+
1278 "UPDATE room_course SET course =TRIM(TRAILING '\n' FROM course);"+
1279 "DROP TEMPORARY TABLE IF EXISTS tempRoom; ";
1280 // var filePath = req.body.filePath;
1281 var year = req.body.year;
1282 var semester = req.body.semester;
1283 var absPath = path.join(__dirname, "../../");
1284 var filePath=absPath+'uploads/roomCourse.csv';
1285 var year = req.body.year;
1286 var semester = req.body.semester;
1287 var table = [year, semester,filePath];
1288 query = mysql.format(query, table);
1289 connection.query(query, function (err, result) {
1290 if (err) {
1291 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
1292 } else {
1293 res.json({"Error": false, "Message": "Success", "roomCourse": result});
1294 }
1295 });
1296 });
1297
1298 // router.post("/importFromFile/yearSemester", function (req, res) {
1299 // var query = "INSERT INTO ?? SET ?";
1300 // var year = req.body.year;
1301 // var semester = req.body.semester;
1302 // var table = ["year_semester", {"year": year, "semester": semester}];
1303 // query = mysql.format(query, table);
1304 // connection.query(query, function (err, result) {
1305 // if (err) {
1306 // res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err});
1307 // } else {
1308 // res.json({"Error": false, "Message": "Success", "bank": result});
1309 // }
1310 // });
1311 // });
1312
1313
1314 function printPDFPerpage(bankingInformation, doc) {
1315 console.log(bankingInformation.name)
1316 console.log(bankingInformation.description)
1317 var date=new Date(new Date().getTime()).toLocaleDateString();
1318 if(bankingInformation.description){
1319 var description_temp =bankingInformation.description.split(" ");
1320 }
1321 else{
1322 var description_temp=' ';
1323 var start_date=' ';
1324 var time=' ';
1325 }
1326 var subject='';
1327 for(var i=0;i<description_temp.length;i++){
1328 if(i==description_temp.length-1)
1329 var time = description_temp[i];
1330 else if(i==description_temp.length-2)
1331 var start_date = description_temp[i];
1332 else
1333 subject+=description_temp[i]+" ";
1334 }
1335
1336
1337
1338 var leftMarginX = 72 * 0.1;
1339 var leftMarginY = 72 * 0.55;
1340 var rightMarginX = [72 * 4.3, 72 * 4.45, 72 * 6.2];
1341 var rightMarginY = 72 * 1.24;
1342 var spaceLeft = 72 * 0.42;
1343 var spaceRight = 72 * 0.27;
1344
1345 var subjectMarginY = 72 * 2.7;
1346
1347 doc.font(
1348 path.resolve(path.dirname(''), './assets', './fonts', './THSarabunNew.ttf')
1349 )
1350 .fontSize(16)
1351 .text(bankingInformation.name, leftMarginX, leftMarginY)
1352 .text(bankingInformation.course, leftMarginX, leftMarginY + spaceLeft * 1)
1353 .text(bankingInformation.seat + bankingInformation.room + " ชั้น " + bankingInformation.floor, leftMarginX, leftMarginY + spaceLeft * 2)
1354 .text(bankingInformation.branch, leftMarginX, leftMarginY + spaceLeft * 3)
1355 .text(date, leftMarginX, leftMarginY + spaceLeft * 4)
1356 .text(bankingInformation.price, leftMarginX, leftMarginY + spaceLeft * 5)
1357 .text(bankingInformation.name, rightMarginX[0], rightMarginY)
1358 .text(bankingInformation.course, rightMarginX[2], rightMarginY + spaceRight * 1)
1359 .text(" ", rightMarginX[1], rightMarginY + spaceRight * 1) //School
1360 .text(time, rightMarginX[2], rightMarginY + spaceRight * 2)
1361 .text(start_date, rightMarginX[1], rightMarginY + spaceRight * 2)
1362 .text(bankingInformation.seat + bankingInformation.room + " ชั้น " + bankingInformation.floor, rightMarginX[1], rightMarginY + spaceRight * 3)
1363 .text(bankingInformation.branch, rightMarginX[2], rightMarginY + spaceRight * 3)
1364 .text(date, rightMarginX[2], rightMarginY + spaceRight * 4)
1365 .text(bankingInformation.price, rightMarginX[1], rightMarginY + spaceRight * 4)
1366 .text(subject, rightMarginX[0], subjectMarginY)
1367
1368 }
1369
1370 function genPdf(bankingInformations, res) {
1371
1372
1373 let width = 3;
1374 let height = 8;
1375
1376 var fs = require('fs');
1377 var PDFDocument = require('pdfkit');
1378 var doc = new PDFDocument({
1379 layout: 'landscape',
1380 size: [72 * width, 72 * height],
1381 margins: { // by default, all are 72
1382 top: 0,
1383 bottom: 0,
1384 left: 0,
1385 right: 0
1386 },
1387 info: {
1388 Title: 'Print-Bio-Beam',
1389 Author: 'author', // the name of the author
1390 Subject: '', // the subject of the document
1391 Keywords: 'pdf;javascript', // keywords associated with the document
1392 CreationDate: 'DD/MM/YYYY', // the date the document was created (added automatically by PDFKit)
1393 }
1394 });
1395 let d = new Date();
1396 let n = d.getTime();
1397 let fileName="output"+n+".pdf";
1398 //doc.pipe(res);
1399 doc.pipe(fs.createWriteStream(fileName))
1400
1401 bankingInformations.forEach((item,i) => {
1402 if(i!==0){
1403 doc.addPage()
1404 }
1405
1406 printPDFPerpage(item, doc)
1407 }
1408 );
1409 doc.end();
1410 res.write(fileName);
1411 res.end();
1412 }
1413
1414 function updatePrintFlag(ids){
1415 // let ids = [1, 2, 3];
1416 var params = [];
1417 for (var i = 1; i <= ids.length; i++) {
1418 params.push('?');
1419 }
1420
1421 var updateQuery="UPDATE banking_information SET is_print=1 WHERE id IN ("+params+")";
1422 var query = mysql.format(updateQuery, ids);
1423 connection.query(query, function (err, result) {
1424 if (err) {
1425 console.log({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err,"Params":params.join(',')});
1426 } else {
1427 console.log("Update Complete");
1428 }
1429 });
1430 }
1431
1432
1433 router.get("/getPdf",function(req,res){
1434 // res.setHeader("Content-Type", "application/pdf");
1435 // res.setHeader("Content-disposition","attachment; filename=test.pdf");
1436 // console.log(req.query.file)
1437 var fs = require('fs');
1438 var absPath = path.join(__dirname, "../../");
1439
1440 let fileName=req.query.file;
1441 fs.readFile(absPath + fileName , function (err,data){
1442 res.contentType("application/pdf");
1443 res.send(data);
1444 });
1445 });
1446
1447 router.post("/genPdf", function (req, res) {
1448 //res.setHeader("Content-Type", "application/pdf");
1449 //res.setHeader("Content-disposition","attachment; filename=test.pdf");
1450 // let ids = [1, 2, 3];
1451 let ids = req.body.ids;
1452 var params = [];
1453 for (var i = 1; i <= ids.length; i++) {
1454 params.push('?');
1455 }
1456
1457
1458 var queryText = "SELECT "+
1459 "temp1.*," +
1460 "temp2.description," +
1461 "temp2.capacity," +
1462 "temp2.price," +
1463 "temp2.start_date," +
1464 "temp2.end_date," +
1465 "temp2.start_time," +
1466 "temp2.end_time," +
1467 "temp2.short_description," +
1468 "temp2.branch," +
1469 "temp2.type " +
1470 "FROM banking_information temp1 " +
1471 "LEFT JOIN course temp2 ON " +
1472 "temp1.year = temp2.year AND " +
1473 "temp1.semester = temp2.semester AND " +
1474 "temp1.course = temp2.course " +
1475 "WHERE temp1.id IN (" + params.join(',') + ")"+
1476 ";";
1477 var query = mysql.format(queryText, ids);
1478 connection.query(query, function (err, result) {
1479 if (err) {
1480 res.json({"Error": true, "Message": "Error executing MySQL query", "ErrorDetails": err,"Params":params.join(',')});
1481 } else {
1482 genPdf(result, res)
1483 updatePrintFlag(ids)
1484 }
1485 });
1486
1487 });
1488
1489}
1490
1491
1492module.exports = REST_ROUTER;