API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 28th, 2017
75
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 58.79 KB | None | 0 0
raw download clone embed print report
  1. var exports = module.exports = {};
  2. var sqlite3 = require('sqlite3').verbose();
  3. const path = require('path');
  4. const dbPath = path.resolve(__dirname, '../mainDB/iMSN_Database.db');
  5. var DateDiff = require('date-diff');
  6. var crypto = require('crypto');
  7. var fs = require('fs');
  8.  
  9. openConnection = function () {
  10. return new sqlite3.Database(dbPath);
  11. }
  12.  
  13. exports.login = function (user, pass, callback) {
  14. var db = openConnection();
  15.  
  16. pass = getSHA1(pass);
  17.  
  18. sql = "SELECT u.ID as ID, u.PaymentType, at.Title as Type, u.ActivationDate as Datum from User as u join AccountType as at on u.AccountTypeID=at.ID where u.Username=? and u.Password=?";
  19. db.all(sql,[user.toLowerCase(),pass], function (err, rows) {
  20. console.log(err);
  21. console.log(rows);
  22. if (err) {
  23. callback(false, false);
  24. }
  25. else {
  26. if (rows.length == 0) {
  27. sql = "SELECT ID from PendingOwner where Username=? and Password=?";
  28. db.all(sql,[user.toLowerCase(),pass], function (err1, rows1) {
  29. destroy(db);
  30. if (err1) {
  31. callback(false, false)
  32. }
  33. else {
  34. if (rows1.length == 0)
  35. callback(false, false);
  36. else {
  37. callback(false, true);
  38. }
  39. }
  40. });
  41. }
  42. else {
  43. destroy(db);
  44. var now = new Date();
  45. var datum = new Date(rows[0].Datum);
  46. console.log(now);
  47. console.log(datum);
  48. if (((new DateDiff(now, datum)).months()) - 12 >= 0)
  49. callback(rows[0].ID, rows[0].Type, false);
  50. else
  51. callback(rows[0].ID, rows[0].Type, true);
  52. }
  53. }
  54. });
  55. }
  56.  
  57. exports.usersForValidation = function (callback) {
  58. var db = openConnection();
  59.  
  60. sql = "SELECT Fname, Lname, ID, URL, `E-mail` as email, Username, Phone, PaymentType FROM PendingOwner WHERE URL IS NOT NULL";
  61.  
  62. db.all(sql, function (err, rows) {
  63. destroy(db);
  64.  
  65. if (err) {
  66. callback(false);
  67. }
  68. else {
  69. callback(JSON.stringify(rows));
  70. }
  71. });
  72. }
  73.  
  74. exports.moveUser = function (ID) {
  75. var db = openConnection();
  76.  
  77. sql = "SELECT Fname, Lname, Username, Password, `E-mail` as email, Phone, URL, PaymentType FROM PendingOwner WHERE ID=?";
  78.  
  79. db.all(sql,[ID], function (err, rows) {
  80. var row = rows[0];
  81. fs.unlink(__dirname + "/../public" + row.URL, function () { });
  82. sql = "select * from User where Username=?";
  83.  
  84. db.all(sql,[row.Username.toLowerCase()], function (err, rows1) {
  85. if (rows1.length > 0) {
  86. var paymentType = row.PaymentType<10?row.PaymentType:row.PaymentType%10;
  87. console.log("udji ovde");
  88. var datum = new Date(Date.now());
  89. sql = "Update User SET ActivationDate=?, PaymentType = ? where Username=?";
  90. db.run(sql,[datum, paymentType, row.Username.toLowerCase()], function () {
  91. sql = "DELETE FROM PendingOwner WHERE ID=?";
  92. db.run(sql,[ID], function () {
  93. destroy(db);
  94. });
  95. });
  96. }
  97. else {
  98. sql = "INSERT INTO User(Fname,Lname,Username,Password,`E-mail` ,Phone, AccountTypeID,ActivationDate, PaymentType) VALUES (?,?,?,?,?,?,3,'" + new Date() + "',?)";
  99. db.run(sql,[row.Fname,row.Lname,row.Username.toLowerCase(),row.Password,row.email,row.Phone,row.PaymentType], function () {
  100. sql = "DELETE FROM PendingOwner WHERE ID=?";
  101. db.run(sql,[ID], function () {
  102. destroy(db);
  103. });
  104. });
  105. }
  106. });
  107. });
  108. }
  109.  
  110.  
  111. exports.deleteUser = function (ID) {
  112. var db = openConnection();
  113. sql = "SELECT Fname, Lname, Username, Password, `E-mail` as email, Phone, URL FROM PendingOwner WHERE ID=?";
  114.  
  115. db.all(sql,[ID], function (err, rows) {
  116. fs.unlink(__dirname + "/../public" + rows[0].URL, function () { });
  117.  
  118. sql = "DELETE FROM PendingOwner WHERE ID=?";
  119. db.run(sql,[ID], function () {
  120. destroy(db);
  121. });
  122. });
  123.  
  124. }
  125. exports.getUser = function (ID, callback) {
  126. var db = openConnection();
  127. sql = "SELECT *, `E-mail` as email FROM User WHERE ID=?";
  128.  
  129. db.all(sql,[ID], function (err, rows) {
  130. destroy(db);
  131. callback(rows[0]);
  132. });
  133.  
  134. }
  135. exports.changeUser = function (user, callback) {
  136. var db = openConnection();
  137. sql = "SELECT ID from User where Username=? and ID!=? UNION Select ID from PendingOwner where Username=?";
  138. db.all(sql,[user.username.toLowerCase(),user.ID,user.username], function (err, rows) {
  139. if (!err) {
  140. if (rows.length > 0) {
  141. destroy(db);
  142. console.log("dada");
  143. callback(false);
  144. }
  145. else {
  146. var arr = [];
  147. if (user.password == "") {
  148. sql = "Update User SET Username=?, Fname=?, Lname=?, Phone=?, `E-mail`=? WHERE ID=?";
  149. arr.push(user.username.toLowerCase());
  150. arr.push(user.fname);
  151. arr.push(user.lname);
  152. arr.push(user.phone);
  153. arr.push(user.email);
  154. arr.push(user.ID);
  155. }
  156. else {
  157. var pass = getSHA1(user.password);
  158. sql = "Update User SET Password=? WHERE ID=?";
  159. arr.push(pass);
  160. arr.push(user.ID);
  161. }
  162. db.run(sql,arr, function (err) {
  163. console.log(err);
  164. destroy(db);
  165. callback(true);
  166. });
  167. }
  168. }
  169. });
  170. }
  171.  
  172. exports.insertRegisteredUser = function (user, callback) {
  173. var db = openConnection();
  174. console.log("USER:", user);
  175. if (user.paid && user.accType == undefined) {
  176. sql = "SELECT ID from User where Username=? ";
  177. db.all(sql,[user.username.toLowerCase()], function (err, rows) {
  178.  
  179. if (!err) {
  180.  
  181. if (rows.length > 0) {
  182. destroy(db);
  183. callback("username");
  184. }
  185. else {
  186. sql = "SELECT ID from User where `E-mail`=?";
  187. db.all(sql,[user.email], function (err, rows) {
  188.  
  189. if (!err) {
  190. if (rows.length > 0) {
  191. destroy(db);
  192. callback("email");
  193. }
  194. else{
  195. sql = "SELECT ID from PendingOwner where Username=? ";
  196. db.all(sql,[user.username.toLowerCase()], function (err1, rows1) {
  197. if (!err1) {
  198. if (rows1.length > 0) {
  199. destroy(db);
  200. callback("username");
  201. }
  202. else {
  203. sql = "SELECT ID from PendingOwner where `E-mail`=? ";
  204. db.all(sql,[user.email], function (err1, rows1) {
  205. if (!err1) {
  206. if (rows1.length > 0) {
  207. destroy(db);
  208. callback("email");
  209. }
  210. else {
  211. sql = "INSERT INTO PendingOwner(Fname,Lname,Username,Password,`E-mail` ,Phone, PaymentType) VALUES (?,?,?,?,?,?,?)";
  212. db.run(sql,[user.fname,user.lname,user.username.toLowerCase(),getSHA1(user.password),user.email,user.phone,user.PaymentType], function () {
  213. destroy(db);
  214. callback("true");
  215. });
  216.  
  217. }
  218. }
  219. });
  220. }
  221. }
  222. });
  223. }
  224. }
  225.  
  226. });
  227. }
  228. }
  229. });
  230. }
  231. else {
  232. sql = "SELECT ID from User where Username=? ";
  233. db.all(sql,[user.username.toLowerCase()], function (err, rows) {
  234. if (rows.length > 0) {
  235. destroy(db);
  236. callback("username");
  237. }
  238. else {
  239. sql = "SELECT ID from User where `E-mail`=?";
  240. db.all(sql,[user.email], function (err, rows) {
  241. if (rows.length > 0) {
  242. destroy(db);
  243. callback("email");
  244. }
  245. else {
  246. sql = "INSERT INTO User(Fname,Lname,Username,Password,`E-mail` ,Phone, AccountTypeID, ActivationDate, PaymentType) VALUES (?,?,?,?,?,?,?,'" + new Date(Date.now()) + "',null)";
  247. db.run(sql,[user.fname,user.lname,user.username.toLowerCase(),getSHA1(user.password),user.email,user.phone,user.accType], function () {
  248. destroy(db);
  249. callback("true");
  250. });
  251. }
  252. });
  253. }
  254. });
  255. }
  256. }
  257.  
  258.  
  259. exports.getAllTickets = function (callback) {
  260. var db = openConnection();
  261.  
  262. sql = 'select u.Fname || " " || u.Lname as fullName, u.PaymentType,s.ID,S.Date as date,s.Text as text from SupportTicket s join User u on u.ID=s.UserID';
  263.  
  264. db.all(sql, function (err, rows) {
  265. destroy(db);
  266.  
  267. if (err) {
  268. callback(false);
  269. }
  270. else {
  271. callback(JSON.stringify(rows));
  272. }
  273. });
  274. }
  275.  
  276. exports.deleteSupportTicket = function (ID) {
  277. var db = openConnection();
  278. sql = "DELETE FROM SupportTicket WHERE ID=?";
  279. db.run(sql,[ID], function () {
  280. destroy(db);
  281. });
  282. }
  283.  
  284. exports.addMobileNotifications = function(all,callback){
  285. var db = openConnection();
  286.  
  287. recAddMobileNotification(db,[],all,0,0,callback);
  288. }
  289.  
  290. function recAddMobileNotification(db,ret,all,i,j,callback){
  291. if(all.length==i) {
  292. destroy(db);
  293. callback(ret);
  294. }
  295. else
  296. {
  297. if(all[i].parcels.length==j) recAddMobileNotification(db,ret,all,i+1,0,callback);
  298. else
  299. {
  300. sql = "SELECT ID FROM MobileNotifications WHERE userID =? AND Message = ?";
  301. db.all(sql,[all[i].ID,'Obavestenje: '+all[i].parcels[j].Message+', Plantaza: '+all[i].parcels[j].Title],function(err,rows){
  302. console.log(err,rows);
  303. if(rows.length!=0) recAddMobileNotification(db,ret,all,i,j+1,callback);
  304. else
  305. {
  306. var tmp = ret.find(el=>el.ID==all[i].ID);
  307.  
  308. if(tmp!=null){
  309. tmp.parcels.push(all[i].parcels[j]);
  310. }
  311. else
  312. {
  313. ret.push({
  314. ID:all[i].ID,
  315. email:all[i].email,
  316. parcels:[all[i].parcels[j]]
  317. });
  318. }
  319.  
  320. sql = "INSERT INTO MobileNotifications VALUES (null,?,?,'"+new Date(Date.now())+"','FALSE',1)";
  321.  
  322. db.run(sql,['Obavestenje: '+all[i].parcels[j].Message+', Plantaza: '+all[i].parcels[j].Title,all[i].ID],function(err){
  323. console.log("---",err);
  324. recAddMobileNotification(db,ret,all,i,j+1,callback);
  325. });
  326. }
  327. });
  328. }
  329. }
  330. }
  331.  
  332. exports.getAllParcelsWithViewers = function(callback){
  333. var db = openConnection();
  334.  
  335. sql = "SELECT u.*,p.ID as ParcelID, p.Title as Title FROM User u JOIN (SELECT p.ID, p.Title, p.UserID as User1, per.UserID as User2 FROM Parcel p LEFT JOIN Permissions per ON p.ID = per.ParcelID) p ON u.ID = p.User1 OR u.ID = p.User2";
  336.  
  337. db.all(sql,function(err,data){
  338. var ret = [];
  339. data.forEach(el=>{
  340. var tmp = ret.find(e=>e.ParcelID == el.ParcelID);
  341.  
  342. if(tmp!=null){
  343. tmp.users.push(el);
  344. }
  345. else{
  346. ret.push({
  347. ParcelID: el.ParcelID,
  348. Title: el.Title,
  349. users:[el]
  350. })
  351. }
  352. });
  353.  
  354. callback(ret);
  355. })
  356. };
  357.  
  358. exports.getMail = function (ID, callback) {
  359. var db = openConnection();
  360. sql = "select `E-Mail` as email from User where ID=?";
  361. db.all(sql,[ID], function (err, rows) {
  362. console.log(err);
  363. console.log(rows);
  364. destroy(db);
  365.  
  366. callback(rows[0].email);
  367. });
  368. }
  369.  
  370. exports.getEmail = function (user, temp, callback) {
  371. var db = openConnection();
  372. sql = "select ID,`E-Mail` as email from User where Username=?";
  373.  
  374. db.all(sql,[user.toLowerCase()], function (err, rows) {
  375.  
  376. if (err) {
  377. destroy(db);
  378. callback(false)
  379. }
  380. else {
  381. if (rows.length > 0) {
  382. var pass = getSHA1(temp);
  383. sql = "Update User set Password=? where ID=?";
  384. db.run(sql,[pass,rows[0].ID], function () {
  385. destroy(db);
  386. callback(rows[0].email);
  387. });
  388.  
  389.  
  390. }
  391.  
  392. else {
  393. destroy(db);
  394. callback(false);
  395. }
  396. }
  397. });
  398. }
  399. exports.getEmailFromSupportTicket = function (ID, callback) {
  400. var db = openConnection();
  401. sql = 'select u.`E-Mail` as email from SupportTicket s join User u on u.ID=s.UserID where s.ID=?';
  402.  
  403. db.all(sql,[ID], function (err, rows) {
  404. destroy(db);
  405. callback(rows[0].email);
  406. });
  407. }
  408.  
  409. exports.addSupportTicket = function (userID, Text, date) {
  410. var db = openConnection();
  411. sql = "INSERT INTO SupportTicket(ID,UserID,Text,Date) VALUES (null,?,?,?)";
  412.  
  413. db.run(sql,[userID,Text,date], function () {
  414. destroy(db);
  415. });
  416. }
  417. exports.deleteOldNotif = function (ID, callback) {
  418. days = "(";
  419. var br = 0;
  420. var db = openConnection();
  421. var now = new Date();
  422. //console.log(now);
  423. sql = "select * from Notification where UserID=?";
  424. db.all(sql,[ID], function (err, rows) {
  425. if (rows.length > 0) {
  426. rows.forEach(el => {
  427. var datum = new Date(el.Date);
  428.  
  429. if (((new DateDiff(now, datum)).days()) > 3) {
  430.  
  431. if (br == 0) {
  432. br++;
  433. days += "" + el.ID;
  434. }
  435. else {
  436. days += "," + el.ID;
  437. }
  438. }
  439.  
  440. });
  441. days += ")";
  442. //console.log(days);
  443. sql = "Delete from Notification where ID in ?";
  444. db.run(sql,[days], function () {
  445. callback(true);
  446. });
  447. }
  448. else {
  449. callback(true);
  450.  
  451. }
  452. });
  453.  
  454. }
  455.  
  456. exports.updateDate = function (ID, type, callback) {
  457. sql = "UPDATE User SET PaymentType = ? AND ActivationDate = '" + new Date(Date.now()) + "' WHERE ID = ?";
  458.  
  459. var db = openConnection();
  460.  
  461. db.run(sql,[type,ID], function () {
  462. callback();
  463. });
  464. }
  465.  
  466. exports.getToDo = function (ID, callback) {
  467. sql = "Select * from Notification where ParcelID=?";
  468.  
  469. var db = openConnection();
  470.  
  471. db.all(sql,[ID], function (err, rows) {
  472. callback(rows);
  473. });
  474. }
  475.  
  476. exports.doIt = function (ID, check, user) {
  477. sql = "Select * from Notification where ID=?";
  478.  
  479. var db = openConnection();
  480.  
  481. db.all(sql,[ID], function (err, rows) {
  482. if (rows.length > 0) {
  483. sql = "Update Notification set ToDo=? where ID=?";
  484. db.run(sql,[check,ID]);
  485. if (check == 0) {
  486. sql = "Delete from ToDo where UserID=? and Title=? and Date=? and ParcelID=?";
  487. db.run(sql,[user,rows[0].Title,rows[0].Date,rows[0].ParcelID]);
  488. }
  489. else {
  490. sql = "INSERT into ToDo(Title,UserID,ParcelID,Date) VALUES(?,?,?,?)"
  491. db.run(sql,[rows[0].Title,user,rows[0].ParcelID,rows[0].Date]);
  492. }
  493. }
  494. });
  495. }
  496. exports.getNumberSupportTicket = function (callback) {
  497. var db = openConnection();
  498. sql = "Select count(ID) as number from SupportTicket";
  499.  
  500. db.all(sql, function (err, data) {
  501. destroy(db);
  502. callback(data[0].number);
  503. });
  504. }
  505.  
  506. exports.getNumberPendingOwner = function (callback) {
  507. var db = openConnection();
  508. sql = "Select count(*) as numb From PendingOwner WHERE URL IS NOT NULL";
  509.  
  510. db.all(sql, function (err, data) {
  511. destroy(db);
  512. callback(data[0].numb);
  513. });
  514. }
  515.  
  516.  
  517. exports.getCrops = function (callback) {
  518. var db = openConnection();
  519. sql = 'select * from Crops';
  520.  
  521. db.all(sql, function (err, rows) {
  522. destroy(db);
  523. callback(rows);
  524. });
  525. }
  526. exports.getUserSubCrops = function (crop, username, callback) {
  527. var db = openConnection();
  528. sql = "SELECT ID from User where Username=?";
  529. db.all(sql,[username.toLowerCase()], function (err1, rows1) {
  530. rows1.forEach(function (row1) {
  531. sql = 'select ID,Title from CustomSubCrop where CropID=? and OwnerID=?';
  532. db.all(sql,[crop,row1.ID], function (err, rows) {
  533. destroy(db);
  534. callback(rows, row1.ID);
  535. });
  536. });
  537. });
  538. }
  539. exports.getOwnerSubCrops = function (crop, ID, callback) {
  540.  
  541. var db = openConnection();
  542. sql = "SELECT c.ID, c.Title from OwnerWithUser as p join CustomSubCrop as c on c.OwnerID=p.OwnerID where p.UserID=? and c.CropID=?";
  543. db.all(sql,[ID,crop], function (err, rows) {
  544. destroy(db);
  545. callback(rows);
  546. });
  547. }
  548. exports.getFreeUsersSubCrops = function (crop, ID, callback) {
  549.  
  550. var db = openConnection();
  551. sql = "SELECT c.ID, c.Title from OwnerWithUser as p join CustomSubCrop as c on c.OwnerID=p.UserID where p.OwnerID=? and c.CropID=?";
  552. db.all(sql,[ID,crop], function (err, rows) {
  553. destroy(db);
  554. callback(rows);
  555. });
  556. }
  557. exports.getSubCrops = function (crop, callback) {
  558. var db = openConnection();
  559.  
  560. sql = 'select ID,Title from SubCrop where CropsID=?';
  561. db.all(sql,[crop], function (err, rows) {
  562. destroy(db);
  563.  
  564. callback(rows);
  565. });
  566. }
  567. exports.insertSubCrop = function (ID, crop, username, callback) {
  568. var db = openConnection();
  569. sql = "SELECT ID from User where Username=?";
  570. db.all(sql,[username.toLowerCase()], function (err1, rows1) {
  571. rows1.forEach(function (row1) {
  572. sql = "select ID from CustomSubCrop where Title=? and OwnerID=?";
  573. db.all(sql,[crop,row1.ID], function (err, rows) {
  574. if (rows.length > 0) {
  575. destroy(db);
  576. callback(false);
  577. }
  578. else {
  579.  
  580. sql = "select ID from SubCrop where Title=?";
  581. db.all(sql,[crop], function (err, rows2) {
  582. if (rows2.length > 0) {
  583. destroy(db);
  584. callback(false);
  585. }
  586. else {
  587. sql = "INSERT INTO CustomSubCrop(OwnerID,Title,CropID) VALUES (?,?,?)";
  588. db.run(sql,[row1.ID,crop,ID], function () {
  589.  
  590. destroy(db);
  591. callback(true);
  592. });
  593. }
  594. });
  595. }
  596. });
  597. });
  598. });
  599. }
  600. exports.getManufactures = function (callback) {
  601. var db = openConnection();
  602. sql = "select * from Manufacturer"
  603. db.all(sql, function (err, rows) {
  604. destroy(db);
  605. callback(rows);
  606. });
  607. }
  608.  
  609. exports.updateSubCrop = function (ID, crop, callback) {
  610. var db = openConnection();
  611. sql = "select ID from CustomSubCrop where Title=?";
  612. db.all(sql,[crop], function (err, rows) {
  613. if (rows.length > 0) {
  614. destroy(db);
  615. callback(false);
  616. }
  617. else {
  618.  
  619. sql = "select ID from SubCrop where Title=?";
  620. db.all(sql,[crop], function (err, rows2) {
  621. if (rows2.length > 0) {
  622. destroy(db);
  623. callback(false);
  624. }
  625. else {
  626. sql = "Update CustomSubCrop Set Title=? WHERE ID=?";
  627. db.run(sql,[crop,ID], function () {
  628. destroy(db);
  629. callback(true);
  630. });
  631.  
  632. }
  633. });
  634. }
  635. });
  636.  
  637. }
  638.  
  639. exports.deleteSubCrop = function (IDsub, callback) {
  640. var db = openConnection();
  641. sql = "select * from SubCropOnParcel as p join CustomSubCrop as c on p.CustomCropID=c.ID where c.ID=?";
  642. db.all(sql,[IDsub], function (err, rows2) {
  643. if (rows2.length > 0) {
  644. destroy(db);
  645. callback(false);
  646. }
  647. else {
  648. sql = "DELETE FROM CustomSubCrop WHERE ID=?";
  649. db.run(sql,[IDsub], function () {
  650.  
  651. destroy(db);
  652. callback(true);
  653. });
  654. }
  655. });
  656. }
  657. exports.insertCrops = function (crop, callback) {
  658. var db = openConnection();
  659. sql = "select ID from Crops where Title=?";
  660. db.all(sql,[crop], function (err, rows2) {
  661. if (rows2.length > 0) {
  662. destroy(db);
  663. callback(false);
  664. }
  665. else {
  666. sql = "INSERT INTO Crops(Title) VALUES (?)";
  667. db.run(sql,[crop], function () {
  668.  
  669. destroy(db);
  670. callback(true);
  671. });
  672. }
  673. });
  674. }
  675. exports.insertGeneralSubCrop = function (IDcrop, title, manuf, callback) {
  676. var db = openConnection();
  677. sql = "select ID from SubCrop where Title=? and CropsID=?";
  678. db.all(sql,[title,IDcrop], function (err, rows2) {
  679. if (rows2.length > 0) {
  680. destroy(db);
  681. callback(false);
  682. }
  683. else {
  684. sql = "INSERT INTO SubCrop(CropsID,Title) VALUES (?,?)";
  685. db.run(sql,[IDcrop,title], function () {
  686. sql = "select* from SubCrop where Title=? and CropsID=?";
  687. db.all(sql,[title,IDcrop], function (err, rows2) {
  688. sql = "select * from Manufacturer where Title=?";
  689. db.all(sql,[manuf], function (err, rows) {
  690. if (rows.length > 0) {
  691. sql = "INSERT INTO SubCropOfManufacturer(SubCropID,ManufacturerID) VALUES(?,?)"
  692. db.run(sql,[rows2[0].ID,rows[0].ID], function () {
  693. destroy(db);
  694. callback(true);
  695. });
  696. }
  697. else {
  698. sql = "INSERT INTO Manufacturer(Title) VALUES(?)";
  699. db.run(sql,[manuf], function () {
  700. sql = "select * from Manufacturer where Title=?";
  701. db.all(sql,[manuf], function (err, rows) {
  702. sql = "INSERT INTO SubCropOfManufacturer(SubCropID,ManufacturerID) VALUES(?,?)"
  703. db.run(sql,[rows2[0].ID,rows[0].ID], function () {
  704. destroy(db);
  705. callback(true);
  706. });
  707. });
  708. });
  709. }
  710. });
  711. });
  712. });
  713. }
  714. });
  715. }
  716. exports.deleteCrops = function (ID, callback) {
  717. var db = openConnection();
  718. sql = "select s.ID from SubCropOnParcel as p join SubCropOfManufacturer as m on p.ManufacturersCropID=m.ID join SubCrop as s on m.SubCropID=s.ID where s.CropsID=?";
  719. db.all(sql,[ID], function (err2, rows2) {
  720. if (rows2.length > 0) {
  721. //console.log(rows2);
  722. destroy(db);
  723. callback(false);
  724. }
  725. else {
  726. sql = "select c.ID from SubCropOnParcel as p join CustomSubCrop as c on p.CustomCropID=c.ID where c.CropID=?";
  727. db.all(sql,[ID], function (err, rows) {
  728. if (rows.length > 0) {
  729. destroy(db);
  730.  
  731.  
  732. callback(false);
  733. }
  734. else {
  735. sql = "DELETE FROM SubCrop WHERE CropsID=?";
  736. db.run(sql,[ID], function () {
  737. sql = "DELETE FROM CustomSubCrop WHERE CropID=?";
  738. db.run(sql,[ID], function () {
  739.  
  740. sql = "DELETE FROM Crops WHERE ID=?";
  741. db.run(sql,[ID], function () {
  742.  
  743. destroy(db);
  744. callback(true);
  745. });
  746. });
  747.  
  748. });
  749.  
  750. }
  751. });
  752. }
  753. });
  754. }
  755. exports.deleteGeneralSubCrop = function (IDsub, callback) {
  756. var db = openConnection();
  757. sql = "select * from SubCropOnParcel as p join SubCropOfManufacturer as m on p.ManufacturersCropID=m.ID join SubCrop as s on m.SubCropID=s.ID where s.ID=?";
  758. db.all(sql,[IDsub], function (err, rows2) {
  759. if (rows2.length > 0) {
  760. destroy(db);
  761. callback(false);
  762. }
  763. else {
  764. sql = "DELETE FROM SubCrop WHERE ID=?";
  765. db.run(sql,[IDsub], function () {
  766. sql = "DELETE from SubCropOfManufacturer where SubCropID=" + IDsub;
  767. db.run(sql,[IDsub], function () {
  768. destroy(db);
  769. callback(true);
  770. });
  771. });
  772. }
  773. });
  774. }
  775. exports.updateCrops = function (crop, ID, callback) {
  776. var db = openConnection();
  777. sql = "select ID from Crops where Title=?";
  778. db.all(sql,[crop], function (err, rows2) {
  779. if (rows2.length > 0) {
  780. destroy(db);
  781. callback(false);
  782. }
  783. else {
  784. sql = "Update Crops Set Title=? WHERE ID=?";
  785. db.run(sql,[crop,ID], function () {
  786.  
  787. destroy(db);
  788. callback(true);
  789. });
  790. }
  791. });
  792. }
  793. exports.updateGeneralSubCrop = function (IDcrop, IDsub, title, callback) {
  794. var db = openConnection();
  795. sql = "select ID from SubCrop where Title=? and CropsID=?";
  796. db.all(sql,[title,IDcrop], function (err, rows2) {
  797. if (rows2.length > 0) {
  798. destroy(db);
  799. callback(false);
  800. }
  801. else {
  802. sql = "Update SubCrop Set Title=? WHERE ID=?";
  803. db.run(sql,[title,IDsub], function () {
  804.  
  805. destroy(db);
  806. callback(true);
  807. });
  808. }
  809. });
  810. }
  811. exports.getUsersForEdit = function (callback) {
  812. var db = openConnection();
  813. sql = 'SELECT *, `E-Mail` as email FROM User ORDER BY Fname || \' \' || Lname DESC';
  814.  
  815. db.all(sql, function (err, rows) {
  816. destroy(db);
  817.  
  818. if (err) {
  819. callback(false);
  820. }
  821. else {
  822. callback(JSON.stringify(rows));
  823. }
  824. });
  825. }
  826.  
  827. exports.saveUserDataEdit = function (json, callback) {
  828. var db = openConnection();
  829.  
  830. var user = JSON.parse(json);
  831.  
  832.  
  833. sql = "SELECT ID from User where Username=? and ID!=? UNION Select ID from PendingOwner where Username=?";
  834. db.all(sql,[user.Username.toLowerCase(),user.ID,user.Username.toLowerCase()], function (err, rows) {
  835. if (!err) {
  836. if (rows.length > 0) {
  837. destroy(db);
  838. callback(false);
  839. }
  840. else {
  841. var arr = [];
  842.  
  843. sql = 'UPDATE User SET Fname=?, Lname=?, Username=?, `E-Mail`=?';
  844. arr.push(user.Fname);
  845. arr.push(user.Lname);
  846. arr.push(user.Username.toLowerCase());
  847. arr.push(user.email);
  848. if (user.Phone != '') { sql += ', phone=?'; arr.push(user.Phone); }
  849. if (user.Password != "********") { sql += " ,Password=?"; arr.push(getSHA1(user.Password)); }
  850. sql += ' WHERE ID=?';
  851. arr.push(user.ID);
  852.  
  853.  
  854. db.run(sql,arr, function () {
  855. destroy(db);
  856. callback(true);
  857. });
  858. }
  859. }
  860. });
  861. }
  862.  
  863. exports.insertAgronomist = function (user, userType, callback) {
  864. var db = openConnection();
  865.  
  866. if(user.phone==undefined) user.phone = '';
  867.  
  868. sql = "SELECT ID from User where Username=?";
  869. db.all(sql,[user.Username.toLowerCase()], function (err, rows) {
  870. if (!err) {
  871. if (rows.length > 0) {
  872. destroy(db);
  873. callback(false);
  874. }
  875. else {
  876. sql = "SELECT ID from PendingOwner where Username=?";
  877. db.all(sql,[user.Username.toLowerCase()], function (err1, rows1) {
  878. if (!err1) {
  879. if (rows1.length > 0) {
  880. destroy(db);
  881. callback(false);
  882. }
  883. else {
  884. console.log(user);
  885. console.log(userType);
  886. sql = "INSERT INTO User(Fname,Lname,Username,Password,`E-Mail`,Phone, AccountTypeID,ActivationDate, PaymentType) VALUES (?,?,?,?,?,?,?,'" + new Date() + "',-1)";
  887. db.run(sql,[user.fname,user.lname,user.username.toLowerCase(),getSHA1(user.password),user.email,user.phone,userType], function (err) {
  888. console.log(sql);
  889. console.log(err);
  890. destroy(db);
  891. callback(true);
  892. });
  893. }
  894. }
  895. });
  896. }
  897. }
  898. });
  899. }
  900.  
  901. exports.getPermissions = function (ID, callback) {
  902. var perms = {
  903. view: false,
  904. edit: false,
  905. rules: false
  906. }
  907.  
  908. var db = openConnection();
  909.  
  910. sql = "SELECT * FROM Permissions WHERE UserID=?";
  911.  
  912. db.all(sql,[ID], function (err, rows) {
  913. destroy(db);
  914.  
  915. if (err) callback(perms)
  916. else {
  917. rows.forEach(row => {
  918. perms.view = perms.view || row.View;
  919. perms.edit = perms.edit || row.Edit;
  920. perms.rules = perms.rules || row.Rules;
  921. });
  922.  
  923. callback(perms);
  924. }
  925. });
  926. }
  927.  
  928. exports.getPermissionsOfOwner = function (ID, callback) {
  929. console.log(ID);
  930. var db = openConnection();
  931. sql = " SELECT User.Fname, User.Lname, User.Username,Permissions.*, Parcel.Title,User.`E-Mail`as email, User.ID as accID FROM Permissions JOIN User ON Permissions.UserID=User.ID JOIN Parcel ON Permissions.ParcelID = Parcel.ID WHERE Parcel.UserID = ?" +
  932. " UNION " +
  933. "SELECT User.Fname, User.Lname, User.Username,null as ID, 0 as Rules, 0 as View, 0 as Edit, User.ID as UserID, Parcel.ID as ParcelID, Parcel.Title ,User.`E-Mail`as email, User.ID as accID FROM User JOIN OwnerWithUser ON User.ID = OwnerWithUser.UserID JOIN Parcel on OwnerID = Parcel.UserID WHERE OwnerID =?";
  934. db.all(sql,[ID,ID], function (err, rows) {
  935. destroy(db);
  936.  
  937. if (err) callback(false);
  938. else {
  939. callback(rows);
  940. }
  941. });
  942. }
  943.  
  944. exports.savePermissions = function (rows) {
  945. var db = openConnection();
  946.  
  947. rows.forEach(perms => {
  948. perms.Edit = perms.Edit ? 1 : 0;
  949. perms.View = perms.View ? 1 : 0;
  950. perms.Rules = perms.Rules ? 1 : 0;
  951. });
  952. if (rows.length != 0) {
  953. sql = "DELETE FROM Permissions WHERE (UserID = " + rows[0].accID + " AND ParcelID = " + rows[0].ParcelID + ")";
  954.  
  955. for (var i = 1; i < rows.length; i++)
  956. sql += " OR (UserID = " + rows[i].accID + " AND ParcelID = " + rows[i].ParcelID + ")"
  957.  
  958. db.run(sql, function () {
  959. sql = "INSERT INTO Permissions VALUES(null," + rows[0].Rules + "," + rows[0].View + "," + rows[0].Edit + "," + rows[0].accID + "," + rows[0].ParcelID + ")";
  960.  
  961. for (var i = 1; i < rows.length; i++)
  962. sql += ",(null," + rows[i].Rules + "," + rows[i].View + "," + rows[i].Edit + "," + rows[i].accID + "," + rows[i].ParcelID + ")";
  963.  
  964. db.run(sql, function () {
  965. destroy(db);
  966. })
  967.  
  968. });
  969. }
  970. }
  971.  
  972. exports.getOwnerID = function (parcelID, callback) {
  973. db = openConnection();
  974. sql = "SELECT UserID FROM Parcel WHERE ID = ?";
  975.  
  976. db.all(sql,[parcelID], function (err, data) {
  977. callback(data[0].UserID);
  978. });
  979. }
  980.  
  981. exports.getEditPermissions = function (ID, callback) {
  982. db = openConnection();
  983. sql = "SELECT Edit FROM Permissions WHERE UserID = ?";
  984.  
  985. db.all(sql,[ID], function (err, data) {
  986. console.log(data);
  987. var count = 0;
  988. data.forEach(el => {
  989. count += el.Edit;
  990. });
  991. destroy(db);
  992. callback(count != 0);
  993. });
  994. }
  995.  
  996. exports.getAllWorks = function (ID, callback) {
  997. db = openConnection();
  998.  
  999. sql = "SELECT u.ID as OwnerID, u.Fname || ' ' || u.Lname as Name FROM User u JOIN OwnerWithUser o ON u.ID = o.OwnerID WHERE o.UserID = ?";
  1000.  
  1001. db.all(sql,[ID], function (err, data) {
  1002. console.log(err);
  1003. destroy(db);
  1004. callback(data);
  1005. });
  1006. }
  1007.  
  1008. exports.deleteJob = function (ID, ownerID) {
  1009. db = openConnection();
  1010.  
  1011. console.log(ID,ownerID);
  1012.  
  1013. sql = "DELETE FROM Permissions WHERE UserID = ? AND ParcelID IN (SELECT ID FROM Parcel WHERE UserID = ?)";
  1014.  
  1015. db.run(sql,[ID,ownerID], function (err) {
  1016. console.log(err);
  1017. sql = "DELETE FROM OwnerWithUser WHERE UserID =? AND OwnerID=?";
  1018.  
  1019. db.run(sql,[ID,ownerID],function(err){
  1020. console.log(err);
  1021. destroy(db);
  1022. });
  1023. });
  1024. }
  1025.  
  1026. exports.getParcels = function (ID, callback) {
  1027. var db = openConnection();
  1028. sql = "SELECT p.UserID, p.ID, p.Title, null as Owner, p.Title as parcelWithOwner, No, MiddleLongitude, MiddleLatitude, 1 as View, 1 as Edit, 1 as Rules FROM User u JOIN Parcel p ON u.ID = p.UserID WHERE p.UserID = ?" +
  1029. " UNION " +
  1030. "SELECT p2.UserID, p2.ID, p2.Title, u2.Fname || ' ' || u2.Lname as Owner, p2.Title as parcelWithOwner, No, MiddleLongitude, MiddleLatitude, View, Edit, Rules FROM User u JOIN Permissions p ON u.ID = p.UserID JOIN Parcel p2 ON p.ParcelID = p2.ID JOIN User u2 ON p2.UserID = u2.ID WHERE u.ID = ?" + " AND View = 1";
  1031. //console.log(sql);
  1032. db.all(sql,[ID,ID], function (err, data) {
  1033. destroy(db);
  1034. if (err) callback([]);
  1035. else callback(data);
  1036. });
  1037. }
  1038.  
  1039. exports.getOwners = function (ID, callback) {
  1040. var db = openConnection();
  1041. sql = "SELECT OwnerID as UserID FROM OwnerWithUser WHERE UserID = ?";
  1042. db.all(sql,[ID], function (err, data) {
  1043. destroy(db);
  1044. //console.log(data);
  1045. if (err) callback([]);
  1046. else callback(data);
  1047. });
  1048. }
  1049.  
  1050. exports.deleteParcel = function (ID, callback) {
  1051. var db = openConnection();
  1052. sql = "DELETE FROM Coordinates WHERE ParcelID = ?";
  1053.  
  1054. db.run(sql,[ID], function () {
  1055. sql = "DELETE FROM SubCropOnParcel WHERE ParcelID = ?";
  1056.  
  1057. db.run(sql,[ID], function () {
  1058. sql = "DELETE FROM Permissions WHERE ParcelID = ?";
  1059.  
  1060. db.run(sql,[ID], function () {
  1061. sql = "DELETE FROM Parcel WHERE ID = ?";
  1062.  
  1063. db.run(sql,[ID], function () {
  1064. callback();
  1065. });
  1066. });
  1067. });
  1068. });
  1069. }
  1070.  
  1071. exports.getCropsForParcel = function (ID, callback) {
  1072. var db = openConnection();
  1073. sql = "SELECT 'm' || p.ManufacturersCropID as ID, sc.Title || ' - ' || m.Title as Name " +
  1074. "FROM SubCropOnParcel p JOIN SubCrop s ON p.ManufacturersCropID = s.CropsID JOIN SubCropOfManufacturer sm ON s.CropsID = sm.ID JOIN SubCrop sc ON sm.SubCropID = sc.ID JOIN Manufacturer m ON sm.ManufacturerID = m.ID " +
  1075. "WHERE p.ParcelID = " + ID +
  1076. " UNION " +
  1077. "SELECT 'c' || p.customCropID as ID, c.Title as Name " +
  1078. "FROM SubCropOnParcel p JOIN CustomSubCrop c ON p.CustomCropID = c.ID " +
  1079. "WHERE p.ParcelID = ?";
  1080. db.all(sql,[ID], function (err, data) {
  1081. destroy(db);
  1082. if (err || data.length == 0) callback(null);
  1083. else callback(JSON.stringify(data));
  1084. });
  1085. }
  1086.  
  1087. exports.getCoords = function (ID, callback) {
  1088. var db = openConnection();
  1089.  
  1090. sql = "SELECT * FROM Coordinates WHERE ParcelID = ? ORDER BY Coordinates.`Order` ASC"
  1091. db.all(sql,[ID], function (err, data) {
  1092. destroy(db);
  1093. callback(data);
  1094. });
  1095. }
  1096.  
  1097. exports.getAllCoords = function (ID, callback) {
  1098. var db = openConnection();
  1099.  
  1100. sql = "SELECT * FROM (SELECT p.ID, Longitude, Latitude, `Order` FROM User u JOIN Parcel p ON u.ID = p.UserID JOIN Coordinates c ON p.ID = c.ParcelID WHERE p.UserID = ? UNION SELECT p2.ID, Longitude, Latitude, `Order` FROM User u JOIN Permissions p ON u.ID = p.UserID JOIN Parcel p2 ON p.ParcelID = p2.ID JOIN Coordinates c ON p.ID = c.ParcelID WHERE u.ID = ? AND View = 1) ORDER BY `Order` ASC";
  1101.  
  1102.  
  1103. db.all(sql,[ID,ID], function (err, data) {
  1104. destroy(db);
  1105.  
  1106. var ret = [];
  1107. data.forEach(row => {
  1108. var temp = ret.find(el => el.ID == row.ID);
  1109. if (temp != null) {
  1110. temp.coords.push({
  1111. lng: row.Longitude,
  1112. lat: row.Latitude,
  1113. order: row.Order
  1114. });
  1115. }
  1116. else {
  1117. ret.push({
  1118. ID: row.ID,
  1119. coords: [{
  1120. lng: row.Longitude,
  1121. lat: row.Latitude,
  1122. order: row.Order
  1123. }]
  1124. });
  1125. }
  1126. })
  1127. callback(ret);
  1128. });
  1129. }
  1130.  
  1131. exports.saveParcelEdit = function (data, callback) {
  1132. var db = openConnection();
  1133. ////console.log(data);
  1134.  
  1135. var west = +data.Coords[0].lng;
  1136. var east = +data.Coords[0].lng;
  1137. var north = +data.Coords[0].lat;
  1138. var south = +data.Coords[0].lat;
  1139.  
  1140. data.Coords.forEach(dev => {
  1141. if (west > +dev.lng) {
  1142. west = +dev.lng;
  1143. }
  1144. if (east < +dev.lng) {
  1145. east = +dev.lng;
  1146. }
  1147. if (north < +dev.lat) {
  1148. north = +dev.lat;
  1149. }
  1150. if (south > +dev.lat) {
  1151. south = +dev.lat;
  1152. }
  1153. });
  1154.  
  1155. var midLat = (north + south) / 2;
  1156. var midLng = (west + east) / 2;
  1157.  
  1158. sql = "UPDATE Parcel SET Title = ?, MiddleLatitude = ?, MiddleLongitude = ? WHERE ID = ?";
  1159.  
  1160. db.run(sql,[data.Title,midLat,midLng,data.ID], function () {
  1161. sql = "DELETE FROM Coordinates WHERE ParcelID =?";
  1162. //
  1163. db.run(sql,[data.ID], function () {
  1164. sql = "INSERT INTO Coordinates(Latitude, Longitude, `Order`, ParcelID) VALUES ";
  1165. var count = 0;
  1166. var arr=[];
  1167. //
  1168. data.Coords.forEach(coord => {
  1169. count++;
  1170. if (count != 1) sql += ",";
  1171. arr.push(coord.lat);
  1172. arr.push(coord.lng);
  1173. arr.push(count);
  1174. arr.push(data.ID);
  1175. sql += "(?,?,?,?)";
  1176. });
  1177. ////console.log(sql);
  1178. db.run(sql,arr, function () {
  1179. //
  1180. sql = "DELETE FROM SubCropOnParcel WHERE ParcelID =?";
  1181.  
  1182. db.run(sql,[data.ID], function () {
  1183. sql = "INSERT INTO SubCropOnParcel(ManufacturersCropID,CustomCropID,ParcelID) VALUES";
  1184. var arr2=[];
  1185. count = 0;
  1186. //
  1187. data.Crops.forEach(crop => {
  1188. count++;
  1189. if (count != 1) sql += ",";
  1190.  
  1191. if (crop.ID[0] == "c") {
  1192. sql += "(null,?,?)";
  1193. arr2.push(crop.ID.substring(1));
  1194. arr2.push(data.ID);
  1195. }
  1196. else {
  1197. sql += "(?,null,?)";
  1198. arr2.push(crop.ID.substring(1));
  1199. arr2.push(data.ID);
  1200. }
  1201. });
  1202. ////console.log(sql);
  1203. if (data.Crops.length == 0) sql = "UPDATE Parcel SET ID = 0 WHERE ID = 0";
  1204.  
  1205. db.run(sql,arr2, function () {
  1206. //
  1207. destroy(db);
  1208. callback();
  1209. });
  1210. })
  1211. });
  1212. });
  1213. });
  1214. }
  1215.  
  1216.  
  1217. exports.saveNewParcel = function (data, ID, callback) {
  1218. var db = openConnection();
  1219. ////console.log(data);
  1220. var arr = [];
  1221. if(data.Owner==-1) {
  1222. sql="select * from Parcel where UserID=? and Title=?";
  1223. arr.push(ID);
  1224. arr.push(data.Title);
  1225. }
  1226. else {
  1227. sql="select * from Parcel where UserID=? and Title=?";
  1228. arr.push(data.Owner);
  1229. arr.push(data.Title);
  1230. }
  1231. db.all(sql,arr,function(err,rows){
  1232. console.log(sql);
  1233. console.log(rows);
  1234. console.log(err);
  1235. if(rows.length!=0)
  1236. {
  1237. destroy(db)
  1238. callback(false);
  1239.  
  1240. }
  1241. else{
  1242.  
  1243. var west = +data.Coords[0].lng;
  1244. var east = +data.Coords[0].lng;
  1245. var north = +data.Coords[0].lat;
  1246. var south = +data.Coords[0].lat;
  1247.  
  1248. data.Coords.forEach(dev => {
  1249. if (west > +dev.lng) {
  1250. west = +dev.lng;
  1251. }
  1252. if (east < +dev.lng) {
  1253. east = +dev.lng;
  1254. }
  1255. if (north < +dev.lat) {
  1256. north = +dev.lat;
  1257. }
  1258. if (south > +dev.lat) {
  1259. south = +dev.lat;
  1260. }
  1261. });
  1262.  
  1263. var midLat = (north + south) / 2;
  1264. var midLng = (west + east) / 2;
  1265. var arr2 = [];
  1266.  
  1267. if (data.Owner == -1) {
  1268. sql = "INSERT INTO Parcel VALUES(null,?,?,?,?,?)";
  1269. arr2.push(data.Title);
  1270. arr2.push(midLng);
  1271. arr2.push(midLat);
  1272. arr2.push(ID);
  1273. arr2.push(data.No);
  1274. }
  1275. else {
  1276. sql = "INSERT INTO Parcel VALUES(null,'" + data.Title + "'," + midLng + "," + midLat + "," + data.Owner + ",'" + data.No + "')";
  1277. arr2.push(data.Title);
  1278. arr2.push(midLng);
  1279. arr2.push(midLat);
  1280. arr2.push(data.Owner);
  1281. arr2.push(data.No);
  1282. }
  1283.  
  1284. db.run(sql,arr2, function () {
  1285. var arr3 = [];
  1286.  
  1287. if (data.Owner == -1) {
  1288. sql = "SELECT * FROM Parcel WHERE UserID = " + ID + " AND Title LIKE '" + data.Title + "' AND MiddleLatitude = " + midLat + " AND MiddleLongitude = " + midLng;
  1289. arr3.push(ID);
  1290. arr3.push(data.Title);
  1291. arr3.push(midLat);
  1292. arr3.push(midLng);
  1293. }
  1294. else {
  1295. sql = "SELECT * FROM Parcel WHERE UserID = " + data.Owner + " AND Title LIKE '" + data.Title + "' AND MiddleLatitude = " + midLat + " AND MiddleLongitude = " + midLng;
  1296. arr3.push(data.Owner);
  1297. arr3.push(data.Title);
  1298. arr3.push(midLat);
  1299. arr3.push(midLng);
  1300.  
  1301. }
  1302. //
  1303. db.all(sql,arr3, function (err, parcels) {
  1304. //console.log(err);
  1305. //console.log(parcels);
  1306. //console.log(sql);
  1307. sql = "INSERT INTO Coordinates(Latitude, Longitude, `Order`, ParcelID) VALUES ";
  1308. var count = 0;
  1309. var arr4 =[];
  1310. //
  1311. data.Coords.forEach(coord => {
  1312. count++;
  1313. if (count != 1) sql += ",";
  1314. sql += "(?,?,?,?)";
  1315. arr4.push(coord.lat);
  1316. arr4.push(coord.lng);
  1317. arr4.push(count);
  1318. arr4.push(parcels[0].ID);
  1319.  
  1320. });
  1321. ////console.log(sql);
  1322. db.run(sql,arr4, function () {
  1323. //
  1324. sql = "INSERT INTO SubCropOnParcel(ManufacturersCropID,CustomCropID,ParcelID) VALUES";
  1325. count = 0;
  1326. var arr5 =[];
  1327. //
  1328. data.Crops.forEach(crop => {
  1329. count++;
  1330. if (count != 1) sql += ",";
  1331.  
  1332. if (crop.ID[0] == "c") {
  1333. sql += "(null,?,?)";
  1334. arr5.push(crop.ID.substring(1));
  1335. arr5.push(parcels[0].ID);
  1336. }
  1337. else {
  1338. sql += "(?,null,?)";
  1339. arr5.push(crop.ID.substring(1));
  1340. arr5.push(parcels[0].ID);
  1341. }
  1342. });
  1343. ////console.log(sql);
  1344. if (data.Crops.length == 0) sql = "UPDATE Parcel SET ID = 0 WHERE ID = 0";
  1345.  
  1346. db.run(sql,arr5, function () {
  1347. //
  1348. if (data.Owner == -1) {
  1349. destroy(db);
  1350. callback(parcels[0]);
  1351. }
  1352. else {
  1353. sql = "INSERT INTO Permissions VALUES(null,0,1,0,?,?)";
  1354.  
  1355. db.run(sql,[ID,parcels[0].ID], function () {
  1356. destroy(db);
  1357. callback(parcels[0]);
  1358. });
  1359. }
  1360. });
  1361. });
  1362. });
  1363. });
  1364. }
  1365. });
  1366. }
  1367.  
  1368.  
  1369. exports.deletePermissions = function (ID, myID) {
  1370. var db = openConnection();
  1371. sql = "DELETE FROM OwnerWithUser WHERE UserID =? AND OwnerID=?";
  1372.  
  1373. db.run(sql,[ID,myID], function (err) {
  1374. sql = "DELETE FROM Permissions WHERE UserID = ? AND ParcelID IN " +
  1375. "( SELECT Parcel.ID FROM Parcel JOIN User ON Parcel.UserID = User.ID WHERE User.ID = ? )";
  1376.  
  1377. db.run(sql,[ID,myID], function (err) {
  1378. exports.getUser(myID,function(user){
  1379. exports.insertInNotification(ID,null,"Niste vise angazovani od strane "+user.Fname+" "+user.Lname+".",3,function(flg){
  1380. exports.insertInMobileNotification(ID,"Niste vise angazovani od strane "+user.Fname+" "+user.Lname+".",new Date());
  1381.  
  1382. destroy(db);
  1383. });
  1384. });
  1385. });
  1386. });
  1387. }
  1388.  
  1389. exports.addWorker = function (perms, ID, parcels, callback) {
  1390. var db = openConnection();
  1391.  
  1392. sql = "SELECT * , `E-mail` as email from User WHERE (username=? OR `E-mail`=?) AND AccountTypeID != 1 AND AccountTypeID != 2 ";
  1393.  
  1394.  
  1395. db.all(sql,[perms.workerText.toLowerCase(),perms.workerText], function (err, rows) {
  1396. if (rows.length == 0)
  1397. callback(false, false,null,null);
  1398. else {
  1399. rows.forEach(function (row) {
  1400. sql2 = "SELECT * from OwnerWithUser WHERE OwnerID=? AND UserID=?";
  1401. db.all(sql2,[ID,row.ID], function (err1, rows1) {
  1402. if (rows1.length != 0)
  1403. callback(false, true,null,null);
  1404. else {
  1405. //
  1406. exports.getUser(ID,function(user){
  1407.  
  1408.  
  1409. par="";
  1410. parcels.forEach(x=>{if(x.View || x.Rules || x.Edit) par+=x.Title+", "});
  1411.  
  1412. //
  1413. sql1 = "INSERT INTO OwnerWithUser VALUES (" + ID + "," + row.ID + ");"
  1414. db.run(sql1, function (err2, rows2) {
  1415. if (parcels.length == 0) {
  1416. text= "Angazovani ste od strane "+user.Fname+" "+user.Lname+".";
  1417. exports.insertInNotification(row.ID,null,text,3,function(flg){
  1418. exports.insertInMobileNotification(row.ID,text,new Date());
  1419.  
  1420. //console.log(parcels);
  1421. destroy(db);
  1422. callback(true, true,row.email,text);
  1423. });
  1424. }
  1425. else {
  1426. text= "Angazovani ste na plantazama: "+par+" od strane "+user.Fname+" "+user.Lname+".";
  1427. exports.insertInNotification(row.ID,null,text,3,function(flg){
  1428. exports.insertInMobileNotification(row.ID,text,new Date());
  1429.  
  1430. sql = "INSERT INTO Permissions VALUES ";
  1431. var c = 0;
  1432. var arr = [];
  1433. parcels.forEach(parcel => {
  1434. if (c++ > 0) sql += ", ";
  1435.  
  1436. parcel.View = parcel.View ? 1 : 0;
  1437. parcel.Rules = parcel.Rules ? 1 : 0;
  1438. parcel.Edit = parcel.Edit ? 1 : 0;
  1439.  
  1440. sql += "(null,?,?,?,?,?)";
  1441. arr.push(parcel.Rules);
  1442. arr.push(parcel.View);
  1443. arr.push(parcel.Edit);
  1444. arr.push(row.ID);
  1445. arr.push(parcel.ID);
  1446.  
  1447. });
  1448. //console.log(sql);
  1449. db.run(sql,arr, function () {
  1450. destroy(db);
  1451.  
  1452. callback(true, true,row.email,text);
  1453. });
  1454.  
  1455. });
  1456. }
  1457. });
  1458. });
  1459. }
  1460. })
  1461.  
  1462.  
  1463. })
  1464. }
  1465.  
  1466.  
  1467. //destroy(db);
  1468. });
  1469.  
  1470. }
  1471.  
  1472. destroy = function (db) {
  1473. db.close();
  1474. }
  1475.  
  1476. getSHA1 = function (input) {
  1477. try {
  1478. return crypto.createHash('sha1').update(input).digest('hex');
  1479. }
  1480. catch (err) { return "" };
  1481. }
  1482.  
  1483. exports.addUrl = function (username, URL) {
  1484.  
  1485. var db = openConnection();
  1486. sql = "select * from PendingOwner where Username=?";
  1487. db.all(sql,[username.toLowerCase()], function (err, rows) {
  1488. if (rows.length > 0) {
  1489. sql = "Update PendingOwner set URL=? where Username=?";
  1490. db.run(sql,[URL,username.toLowerCase()], function () {
  1491. destroy(db);
  1492. });
  1493. }
  1494. else {
  1495. sql = "select *,`E-mail`as email from User where Username=?";
  1496. db.all(sql,[username.toLowerCase()], function (err1, rows1) {
  1497. sql = "INSERT INTO PendingOwner(Fname,Lname,Username,Password,`E-mail` ,Phone, PaymentType,URL) VALUES (?,?,?,?,?,?,?,?)";
  1498. db.run(sql,[rows1[0].Fname,rows1[0].Lname,rows1[0].Username.toLowerCase(),rows1[0].Password,rows1[0].email,rows1[0].Phone,rows1[0].PaymentType,URL], function () {
  1499. destroy(db);
  1500. });
  1501. });
  1502. }
  1503. });
  1504.  
  1505. }
  1506.  
  1507. exports.allParcels = function (callback) {
  1508.  
  1509. var db = openConnection();
  1510. sql = "SELECT ID,Title FROM Parcel";
  1511.  
  1512. db.all(sql, function (err, data) {
  1513. destroy(db);
  1514.  
  1515. callback(data);
  1516. })
  1517. }
  1518. exports.getAllParcels = function (ID, callback) {
  1519.  
  1520. var db = openConnection();
  1521. sql = "SELECT Parcel.ID, Parcel.Title FROM Parcel WHERE UserID =?";
  1522.  
  1523. db.all(sql,[ID], function (err, data) {
  1524. destroy(db);
  1525.  
  1526. callback(data);
  1527. })
  1528. }
  1529.  
  1530. exports.getAllCrops = function (ID, parcelID, callback) {
  1531.  
  1532. var db = openConnection();
  1533.  
  1534. sql = "SELECT ID,UserID FROM Parcel WHERE ID = ?";
  1535. db.all(sql,[parcelID], function (err, parcel) {
  1536. sql = "SELECT c.ID,c.Title, 'm'||scm.ID as scmID, sc.Title || ' - ' || m.Title as scmTitle FROM Crops c JOIN SubCrop sc ON c.ID = sc.CropsID JOIN SubCropOfManufacturer scm ON sc.ID = scm.SubCropID JOIN Manufacturer m ON scm.ManufacturerID = m.ID" +
  1537. " UNION " +
  1538. "SELECT c.ID, c.Title, 'c'||cc.ID as scmID, cc.Title as scmTitle FROM Crops c JOIN CustomSubCrop cc ON c.ID == cc.CropID WHERE cc.OwnerID = ?";
  1539.  
  1540. db.all(sql,[ID], function (err, mainCrops) {
  1541. var arr = [];
  1542. if (parcel[0].UserID == ID) {
  1543. sql = "SELECT c.ID, c.Title, 'c'||cc.ID as scmID, cc.Title as scmTitle FROM Crops c JOIN CustomSubCrop cc ON c.ID == cc.CropID WHERE cc.OwnerID IN (SELECT UserID FROM OwnerWithUser WHERE OwnerID = ?)";
  1544. arr.push(ID);
  1545. }
  1546. else {
  1547. sql = "SELECT c.ID, c.Title, 'c'||cc.ID as scmID, cc.Title as scmTitle FROM Crops c JOIN CustomSubCrop cc ON c.ID == cc.CropID WHERE cc.OwnerID = ?";
  1548. arr.push(parcel[0].UserID);
  1549. }
  1550.  
  1551. db.all(sql,arr, function (err, otherCrops) {
  1552.  
  1553. var ret = [];
  1554. //console.log(mainCrops);
  1555. //console.log(otherCrops);
  1556.  
  1557. mainCrops.forEach(crop => {
  1558. var tmp = ret.find(el => el.ID == crop.ID);
  1559.  
  1560. if (tmp) {
  1561. tmp.subcrops.push({
  1562. ID: crop.scmID,
  1563. title: crop.scmTitle
  1564. })
  1565. }
  1566. else {
  1567. ret.push({
  1568. ID: crop.ID,
  1569. title: crop.Title,
  1570. subcrops: [{
  1571. ID: crop.scmID,
  1572. title: crop.scmTitle
  1573. }]
  1574. })
  1575. }
  1576. });
  1577.  
  1578. otherCrops.forEach(crop => {
  1579. var tmp = ret.find(el => el.ID == crop.ID);
  1580.  
  1581. if (tmp) {
  1582. tmp.subcrops.push({
  1583. ID: crop.scmID,
  1584. title: crop.scmTitle
  1585. })
  1586. }
  1587. else {
  1588. ret.push({
  1589. ID: crop.ID,
  1590. title: crop.Title,
  1591. subcrops: [{
  1592. ID: crop.scmID,
  1593. title: crop.scmTitle
  1594. }]
  1595. })
  1596. }
  1597. });
  1598.  
  1599. destroy(db);
  1600.  
  1601. callback(ret);
  1602. });
  1603. });
  1604. });
  1605.  
  1606. }
  1607.  
  1608.  
  1609. exports.getAllCropsForNew = function (ID, callback) {
  1610.  
  1611. var db = openConnection();
  1612.  
  1613. sql = "SELECT c.ID,c.Title, 'm'||scm.ID as scmID, sc.Title || ' - ' || m.Title as scmTitle FROM Crops c JOIN SubCrop sc ON c.ID = sc.CropsID JOIN SubCropOfManufacturer scm ON sc.ID = scm.SubCropID JOIN Manufacturer m ON scm.ManufacturerID = m.ID" +
  1614. " UNION " +
  1615. "SELECT c.ID, c.Title, 'c'||cc.ID as scmID, cc.Title as scmTitle FROM Crops c JOIN CustomSubCrop cc ON c.ID == cc.CropID WHERE cc.OwnerID = ?";
  1616.  
  1617. db.all(sql,[ID], function (err, mainCrops) {
  1618. sql = "SELECT c.ID, c.Title, 'c'||cc.ID as scmID, cc.Title as scmTitle FROM Crops c JOIN CustomSubCrop cc ON c.ID == cc.CropID WHERE cc.OwnerID IN (SELECT UserID FROM OwnerWithUser WHERE OwnerID = ?)";
  1619.  
  1620. db.all(sql,[ID], function (err, otherCrops) {
  1621.  
  1622. var ret = [];
  1623. ////console.log(sql);
  1624.  
  1625. mainCrops.forEach(crop => {
  1626. var tmp = ret.find(el => el.ID = crop.ID);
  1627.  
  1628. if (tmp) {
  1629. tmp.subcrops.push({
  1630. ID: crop.scmID,
  1631. title: crop.scmTitle
  1632. })
  1633. }
  1634. else {
  1635. ret.push({
  1636. ID: crop.ID,
  1637. title: crop.Title,
  1638. subcrops: [{
  1639. ID: crop.scmID,
  1640. title: crop.scmTitle
  1641. }]
  1642. })
  1643. }
  1644. });
  1645.  
  1646. otherCrops.forEach(crop => {
  1647. var tmp = ret.find(el => el.ID = crop.ID);
  1648.  
  1649. if (tmp) {
  1650. tmp.subcrops.push({
  1651. ID: crop.scmID,
  1652. title: crop.scmTitle
  1653. })
  1654. }
  1655. else {
  1656. ret.push({
  1657. ID: crop.ID,
  1658. title: crop.Title,
  1659. subcrops: [{
  1660. ID: crop.scmID,
  1661. title: crop.scmTitle
  1662. }]
  1663. })
  1664. }
  1665. });
  1666.  
  1667. destroy(db);
  1668.  
  1669. callback(ret);
  1670. });
  1671. });
  1672. }
  1673.  
  1674. exports.getPossibleOwners = function (ID, callback) {
  1675.  
  1676. var db = openConnection();
  1677.  
  1678. sql = "SELECT ID, Fname || ' ' || Lname as Name FROM User u JOIN OwnerWithUser o ON u.ID = o.OwnerID WHERE (u.PaymentType = 3 OR (SELECT COUNT(*) FROM Parcel p2 WHERE p2.UserID = o.OwnerID)<u.PaymentType) AND o.UserID =?";
  1679.  
  1680. db.all(sql,[ID], function (err, rows) {
  1681. destroy(db);
  1682. callback(rows);
  1683. });
  1684. }
  1685.  
  1686. exports.getPossibleOwnersForSensors = function (ID, callback) {
  1687.  
  1688. var db = openConnection();
  1689.  
  1690. sql = "SELECT DISTINCT u.ID, Fname || ' ' || Lname as Name FROM User u JOIN Parcel p ON u.ID = p.UserID JOIN Permissions per ON p.ID = per.ParcelID WHERE per.Edit = 1 AND per.UserID =?";
  1691.  
  1692. db.all(sql,[ID], function (err, rows) {
  1693. // //console.log("\n-\n-\n-\n")
  1694. // //console.log(err);
  1695. // //console.log(rows);
  1696.  
  1697. destroy(db);
  1698. callback(rows);
  1699. });
  1700. }
  1701.  
  1702. exports.getParcelsForSensors = function (ID, callback) {
  1703.  
  1704. var db = openConnection();
  1705.  
  1706. sql = "SELECT ID, Title FROM Parcel WHERE UserID = ?";
  1707.  
  1708. db.all(sql,[ID], function (err, rows) {
  1709. destroy(db);
  1710. callback(rows);
  1711. });
  1712. }
  1713.  
  1714. exports.getOwnersParcels = function (ID, callback) {
  1715.  
  1716. var db = openConnection();
  1717.  
  1718. sql = "SELECT Parcel.Title,Parcel.ID FROM User JOIN Parcel ON User.ID = Parcel.UserID WHERE User.ID = ?" +
  1719. " UNION " +
  1720. "SELECT p.title,p.ID FROM Parcel p JOIN OwnerWithUser o ON p.UserID = o.OwnerID JOIN Permissions pm ON o.UserID = pm.UserID AND p.ID = pm.ParcelID WHERE pm.Rules = 1 AND o.UserID = ?";
  1721. db.all(sql,[ID,ID], function (err, rows) {
  1722.  
  1723. destroy(db);
  1724. callback(rows);
  1725. });
  1726. }
  1727.  
  1728. exports.getToDos = function (ID, callback) {
  1729.  
  1730. var db = openConnection();
  1731.  
  1732. sql = "SELECT td.*, u.Fname || ' ' || u.Lname as Name FROM ToDo td JOIN Parcel p ON td.ParcelID = p.ID JOIN User u ON td.UserID = u.ID WHERE p.UserID = ?";
  1733. db.all(sql,[ID], function (err, rows) {
  1734. destroy(db);
  1735. callback(rows);
  1736. });
  1737. }
  1738.  
  1739. exports.getAllParcelsForExpert = function (callback) {
  1740.  
  1741. var db = openConnection();
  1742.  
  1743. sql = "SELECT Parcel.ID FROM Parcel";
  1744. db.all(sql, function (err, rows) {
  1745.  
  1746. destroy(db);
  1747. callback(rows);
  1748. });
  1749. }
  1750.  
  1751. exports.checkExistance = function (username, callback) {
  1752.  
  1753. var db = openConnection();
  1754.  
  1755. sql = "SELECT ID FROM User WHERE Username = ? " +
  1756. "UNION " +
  1757. "SELECT ID FROM PendingOwner WHERE Username = ?";
  1758. db.all(sql,[username.toLowerCase(),username.toLowerCase()], function (err, rows) {
  1759.  
  1760. destroy(db);
  1761. callback(rows.length == 0);
  1762. });
  1763. }
  1764.  
  1765. exports.getWorkers = function (ID, callback) {
  1766.  
  1767. var db = openConnection();
  1768.  
  1769. sql = "SELECT Count(*) as num FROM OwnerWithUser WHERE OwnerID =?";
  1770. db.all(sql,[ID], function (err, rows) {
  1771. destroy(db);
  1772. callback(rows[0].num);
  1773. });
  1774. }
  1775. exports.getNotifNum = function (ID, callback) {
  1776.  
  1777. var db = openConnection();
  1778.  
  1779. sql = "SELECT Count(*) as num FROM Notification WHERE UserID =? and Seen=0";
  1780. db.all(sql,[ID], function (err, rows) {
  1781. //console.log(err);
  1782. destroy(db);
  1783. callback(rows[0].num);
  1784. });
  1785. }
  1786.  
  1787. exports.getShowCorny = function (ID, callback) {
  1788.  
  1789. var db = openConnection();
  1790.  
  1791. sql = "SELECT ShowCorny FROM User WHERE ID =?";
  1792. db.all(sql,[ID], function (err, rows) {
  1793.  
  1794. destroy(db);
  1795. callback(rows[0].ShowCorny);
  1796. });
  1797. }
  1798.  
  1799. exports.setShowCorny = function (ID, flag, callback) {
  1800.  
  1801. var db = openConnection();
  1802.  
  1803.  
  1804. if (flag) sql = "UPDATE User SET ShowCorny = 1 WHERE ID =?";
  1805. else sql = "UPDATE User SET ShowCorny = 0 WHERE ID =?";
  1806. db.run(sql,[ID], function (err) {
  1807. //console.log(err)
  1808. destroy(db);
  1809. callback();
  1810. });
  1811. }
  1812.  
  1813. exports.getParcelsCount = function (ID, callback) {
  1814.  
  1815. var db = openConnection();
  1816.  
  1817. sql = "SELECT Count(*) as num FROM Parcel WHERE UserID =?";
  1818. db.all(sql,[ID], function (err, rows) {
  1819. destroy(db);
  1820. callback(rows[0].num);
  1821. });
  1822. }
  1823. exports.getCalendar = function (ID, callback) {
  1824.  
  1825. var db = openConnection();
  1826.  
  1827. sql = "SELECT * FROM Calendar WHERE UserID =?";
  1828. db.all(sql,[ID], function (err, rows) {
  1829. destroy(db);
  1830. callback(rows);
  1831. });
  1832. }
  1833.  
  1834. exports.checkCalendar = function (ID, niz, callback) {
  1835. var now = new Date();
  1836. var db = openConnection();
  1837. sql = "SELECT * FROM Calendar WHERE UserID =?";
  1838. db.all(sql,[ID], function (err, rows) {
  1839. if (rows.length > 0) {
  1840. var nizID = "(";
  1841. var br = 0;
  1842. rows.forEach(function (element) {
  1843. var datum = new Date(element.Date);
  1844. if (((new DateDiff(now, datum)).days()) < 1 && ((new DateDiff(now, datum)).days()) > 0 && element.Seen == 0) {
  1845. niz.push("Danas je: " + datum.getDate() + "/" + datum.getMonth() + "/" + datum.getFullYear() + ", obavestenje: " + element.Title);
  1846. if (br == 0) {
  1847. br++;
  1848. nizID += "" + element.ID;
  1849. }
  1850. else {
  1851. nizID += "," + element.ID;
  1852. }
  1853. }
  1854. }, this);
  1855. nizID += ")";
  1856. sql = "update Calendar set Seen=1 where ID in ?";
  1857. db.run(sql,[nizID], function () {
  1858. destroy(db);
  1859. callback();
  1860. });
  1861. }
  1862. else {
  1863. destroy(db);
  1864. callback();
  1865. }
  1866.  
  1867. });
  1868.  
  1869. }
  1870.  
  1871. exports.getNotification = function (ID, callback) {
  1872.  
  1873. var db = openConnection();
  1874. sql = "Update Notification set Seen=1 where UserID=?";
  1875. db.run(sql,[ID], function () {
  1876. sql = "SELECT * FROM Notification WHERE UserID =?";
  1877. db.all(sql,[ID], function (err, rows) {
  1878. destroy(db);
  1879. callback(rows);
  1880. });
  1881. });
  1882. }
  1883.  
  1884. exports.insertInMobileNotification = function (userID, message, date) {
  1885. var db = openConnection();
  1886.  
  1887. sql = "INSERT INTO MobileNotifications(Message, UserID, Date, Type) VALUES (?,?,?,2)";
  1888.  
  1889. db.run(sql,[message,userID,date], function () {
  1890. destroy(db);
  1891. });
  1892. }
  1893.  
  1894. exports.insertInNotification = function (ID, parcelID, title, priority, callback) {
  1895.  
  1896. //console.log(callback);
  1897.  
  1898. if (priority == null) priority = 3;
  1899.  
  1900. var db = openConnection();
  1901. sql = "select * from Notification where Title=? and UserID=?";
  1902. db.all(sql,[title,ID], function (err, rows) {
  1903. if (rows.length == 0) {
  1904. sql = "Insert into Notification(Title,UserID,Priority,Date,ParcelID) VALUES(?,?,?,'" + new Date() + "',?)";
  1905. db.run(sql,[title,ID,priority,parcelID], function (err) {
  1906. destroy(db);
  1907. callback(true);
  1908. });
  1909. }
  1910. else {
  1911. sql = "Update Notification set Priority=? where ID=?";
  1912. db.run(sql,[priority,rows[0].ID], function () {
  1913. destroy(db);
  1914. callback(false);
  1915. });
  1916. }
  1917. });
  1918. }
  1919.  
  1920. exports.insertInNotificationForAll = function (ID, parcelID, title, priority, callback) {
  1921.  
  1922. //console.log(callback);
  1923.  
  1924. if (priority == null) priority = 3;
  1925.  
  1926. var db = openConnection();
  1927.  
  1928. sql = "SELECT UserID FROM Permissions WHERE ParcelID = ? UNION SELECT UserID FROM Parcel WHERE ID = ?";
  1929.  
  1930. db.all(sql,[parcelID,parcelID], function (err, data) {
  1931. var all = "(";
  1932. var i = 0;
  1933. data.forEach(el => {
  1934. if (i != 0) all += ",";
  1935. i++;
  1936.  
  1937. all += el.UserID;
  1938. });
  1939. all += ")";
  1940.  
  1941. sql = "select * from Notification where Title=? and UserID IN " + all;
  1942. db.all(sql,[title], function (err, rows) {
  1943. console.log(err);
  1944. console.log(rows);
  1945. recInsertNotif(db, title, ID, priority, parcelID, rows, data, 0, false, callback);
  1946. });
  1947. });
  1948. }
  1949.  
  1950. function recInsertNotif(db, title, ID, priority, parcelID, rows, data, i, flag, callback) {
  1951. //console.log("Call",i);
  1952. if (i == data.length) {
  1953. destroy(db);
  1954. callback(flag);
  1955. }
  1956. else {
  1957. //console.log(rows);
  1958. var tmp = rows.find(e => e.UserID == data[i].UserID);
  1959.  
  1960. if (tmp) {
  1961. sql = "Update Notification set Priority=? where ID=?";
  1962. db.run(sql,[priority,tmp.ID], function () {
  1963. recInsertNotif(db, title, ID, priority, parcelID, rows, data, i + 1, flag, callback);
  1964. });
  1965. }
  1966. else {
  1967. if (data[i].UserID == ID) flag = true;
  1968.  
  1969. sql = "Insert into Notification(Title,UserID,Priority,Date,ParcelID) VALUES(?,?,?,'" + new Date() + "',?)";
  1970. db.run(sql,[title,data[i].UserID,priority,parcelID], function (err) {
  1971. sql = "Insert into MobileNotifications(Message, UserID, Date) VALUES(?,?,'" + new Date() + "')";
  1972. db.run(sql,[title,data[i].UserID], function (err) {
  1973. recInsertNotif(db, title, ID, priority, parcelID, rows, data, i + 1, flag, callback);
  1974. });
  1975. });
  1976. }
  1977. }
  1978. }
  1979.  
  1980. exports.getNotificationsForMobile = function (ID, callback) {
  1981.  
  1982. var db = openConnection();
  1983.  
  1984. sql = "SELECT ID, Message as Title, Date, Type FROM MobileNotifications WHERE userID = ? AND Seen = 'FALSE'";
  1985.  
  1986. db.all(sql,[ID], function (err, data) {
  1987. var date = new Date(Date.now());
  1988.  
  1989. var filtered = data.filter(el => {
  1990. var tmp = new Date(el.Date);
  1991. return (el.Type == 1 || (tmp.getDate() == date.getDate() && tmp.getMonth() == date.getMonth()));
  1992. });
  1993.  
  1994. callback(filtered);
  1995.  
  1996. sql = "SELECT * FROM MobileNotifications WHERE Seen='TRUE'";
  1997. db.all(sql, function (err, res) {
  1998. if (res.length > 0) {
  1999. var all = "(";
  2000. var i = 0;
  2001.  
  2002. res.forEach(el => {
  2003. var tmp = new Date(el.Date);
  2004.  
  2005. if (new DateDiff(date, tmp).days() > 3) {
  2006. if (i != 0) all += ",";
  2007. i++;
  2008.  
  2009. all += "el.ID";
  2010. }
  2011. });
  2012.  
  2013. all += ")";
  2014.  
  2015. if (i != 0) {
  2016. sql = "DELETE FROM MobileNotifications WHERE ID IN " + all;
  2017.  
  2018. db.run(sql, function () {
  2019. if (filtered.length != 0) {
  2020. all = "(";
  2021. i = 0;
  2022. filtered.forEach(el => {
  2023. if (i != 0) all += ",";
  2024. i++;
  2025.  
  2026. all += el.ID;
  2027. });
  2028. all += ")"
  2029.  
  2030. sql = "UPDATE MobileNotifications SET Seen = 'TRUE' WHERE ID IN " + all;
  2031. db.run(sql, function () {
  2032. destroy(db);
  2033. });
  2034. }
  2035. else destroy(db);
  2036. });
  2037. }
  2038. else {
  2039. if (filtered.length != 0) {
  2040. all = "(";
  2041. i = 0;
  2042. filtered.forEach(el => {
  2043. if (i != 0) all += ",";
  2044. i++;
  2045.  
  2046. all += el.ID;
  2047. });
  2048. all += ")"
  2049.  
  2050. sql = "UPDATE MobileNotifications SET Seen = 'TRUE' WHERE ID IN " + all;
  2051. db.run(sql, function () {
  2052. destroy(db);
  2053. });
  2054. }
  2055. else destroy(db);
  2056. }
  2057. }
  2058. else {
  2059. if (filtered.length != 0) {
  2060. all = "(";
  2061. i = 0;
  2062. filtered.forEach(el => {
  2063. if (i != 0) all += ",";
  2064. i++;
  2065.  
  2066. all += el.ID;
  2067. });
  2068. all += ")"
  2069.  
  2070. sql = "UPDATE MobileNotifications SET Seen = 'TRUE' WHERE ID IN " + all;
  2071. db.run(sql, function () {
  2072. destroy(db);
  2073. });
  2074. }
  2075. else destroy(db);
  2076. }
  2077. });
  2078. });
  2079. };
  2080.  
  2081. exports.deleteFromNotification = function (ID, title, date) {
  2082.  
  2083. var db = openConnection();
  2084.  
  2085. sql = "Delete from Notification where UserID=? and Title like ?";
  2086. db.run(sql,[ID,title], function () {
  2087. destroy(db);
  2088. });
  2089. }
  2090.  
  2091. exports.deleteNotification = function (ID) {
  2092.  
  2093. var db = openConnection();
  2094.  
  2095. sql = "Delete from Notification where ID=?";
  2096.  
  2097. db.run(sql,[ID], function (err) {
  2098. destroy(db);
  2099. });
  2100. }
  2101. exports.insertInCalendar = function (ID, title, date, priority) {
  2102.  
  2103. var db = openConnection();
  2104.  
  2105. sql = "Insert into Calendar(Title,UserID,Date,Priority) VALUES(?,?,?,?)";
  2106. db.run(sql,[title,ID,date,priority], function () {
  2107. destroy(db);
  2108. });
  2109. }
  2110. exports.deleteFromCalendar = function (ID, title, date) {
  2111. console.log(date);
  2112. var db = openConnection();
  2113.  
  2114. sql = "Delete from Calendar where UserID=? and Title like ? and Date like ?";
  2115. db.run(sql,[ID,title,date], function () {
  2116. sql = "Delete from MobileNotifications where userID=? and Message like ? and Date like ?";
  2117. db.run(sql,[ID,title,date], function (err) {
  2118. console.log(err);
  2119. destroy(db);
  2120. });
  2121. });
  2122. }
  2123.  
  2124. exports.getAllParcelsForRules = function (ID, callback) {
  2125.  
  2126. var db = openConnection();
  2127.  
  2128. sql = "SELECT DISTINCT p.ID, p.MiddleLatitude as lat, p.MiddleLongitude as lng,CropID " +
  2129. "FROM (SELECT p.ID,p.MiddleLatitude, p.MiddleLongitude FROM Parcel p WHERE p.UserID = ?" + " " +
  2130. "UNION " +
  2131. "SELECT p.ID,p.MiddleLatitude, p.MiddleLongitude FROM Parcel p JOIN Permissions pm ON p.ID = pm.ParcelID WHERE pm.View = 1 AND pm.UserID = ?" + ") p " +
  2132. "JOIN SubCropOnParcel sc ON p.ID = sc.ParcelID " +
  2133. "JOIN CustomSubCrop csp ON sc.CustomCropID = csp.CropID " +
  2134. "UNION " +
  2135. "SELECT DISTINCT p.ID, p.MiddleLatitude as lat, p.MiddleLongitude as lng,CropsID " +
  2136. "FROM (SELECT p.ID,p.MiddleLatitude, p.MiddleLongitude FROM Parcel p WHERE p.UserID = ?" + " " +
  2137. "UNION " +
  2138. "SELECT p.ID,p.MiddleLatitude, p.MiddleLongitude FROM Parcel p JOIN Permissions pm ON p.ID = pm.ParcelID WHERE pm.View = 1 AND pm.UserID = ?"+ ") p " +
  2139. "JOIN SubCropOnParcel sc ON p.ID = sc.ParcelID " +
  2140. "JOIN SubCropOfManufacturer scm ON sc.ManufacturersCropID = scm.ID " +
  2141. "JOIN SubCrop sub ON scm.SubCropID = sub.ID";
  2142.  
  2143. db.all(sql,[ID,ID,ID,ID], function (err, rows) {
  2144. //console.log(sql);
  2145. //console.log(err);
  2146. //console.log(rows);
  2147.  
  2148. if (err) callback([]);
  2149. else callback(rows);
  2150. });
  2151. }
  2152.  
  2153. exports.getAllParcelsForIntervalRules = function (callback) {
  2154.  
  2155. var db = openConnection();
  2156.  
  2157. sql = "SELECT DISTINCT p.ID, p.MiddleLatitude as lat, p.MiddleLongitude as lng,CropID " +
  2158. "FROM (SELECT p.ID,p.MiddleLatitude, p.MiddleLongitude FROM Parcel p ) p " +
  2159. "JOIN SubCropOnParcel sc ON p.ID = sc.ParcelID " +
  2160. "JOIN CustomSubCrop csp ON sc.CustomCropID = csp.CropID " +
  2161. "UNION " +
  2162. "SELECT DISTINCT p.ID, p.MiddleLatitude as lat, p.MiddleLongitude as lng,CropsID " +
  2163. "FROM (SELECT p.ID,p.MiddleLatitude, p.MiddleLongitude FROM Parcel p ) p " +
  2164. "JOIN SubCropOnParcel sc ON p.ID = sc.ParcelID " +
  2165. "JOIN SubCropOfManufacturer scm ON sc.ManufacturersCropID = scm.ID " +
  2166. "JOIN SubCrop sub ON scm.SubCropID = sub.ID";
  2167.  
  2168. db.all(sql, function (err, rows) {
  2169. //console.log(sql);
  2170. //console.log(err);
  2171. //console.log(rows);
  2172.  
  2173. if (err) callback([]);
  2174. else callback(rows);
  2175. });
  2176. }
  2177.  
  2178. exports.getParcelIDs = function (ID, callback) {
  2179. sql = "SELECT ID FROM Parcel WHERE UserID = ?";
  2180.  
  2181. var db = openConnection();
  2182.  
  2183. db.all(sql,[ID], function (err, data) {
  2184. destroy(db);
  2185.  
  2186. callback(data);
  2187. });
  2188. }
  2189.  
  2190. exports.getPaymentType = function (ID, callback) {
  2191. sql = "SELECT PaymentType as type FROM User WHERE ID = ?";
  2192.  
  2193. var db = openConnection();
  2194.  
  2195. db.all(sql,[ID], function (err, data) {
  2196. destroy(db);
  2197.  
  2198. callback(data[0].type);
  2199. });
  2200. }
  2201.  
  2202. exports.addUrlForChange = function(ID,URL,type){
  2203. sql = "SELECT *, `E-Mail` as email FROM User WHERE ID = ?";
  2204.  
  2205. var db = openConnection();
  2206.  
  2207. db.all(sql,[ID],function(err,rows){
  2208. console.log(err);
  2209.  
  2210. sql = "INSERT INTO PendingOwner VALUES(null,?,?,?,?,?,?,?,?)";
  2211.  
  2212. db.run(sql,[rows[0].Username,rows[0].Password,rows[0].Fname,rows[0].Lname,rows[0].Phone,rows[0].email,URL,type],function(err){
  2213. console.log(err);
  2214. destroy(db);
  2215. });
  2216. });
  2217. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!