· 5 years ago · Mar 02, 2020, 09:42 AM
1//
2// FMDBManager.swift
3// fourthMate
4//
5// Created by Volpis on 10.05.17.
6// Copyright © 2017 ComBC. All rights reserved.
7//
8
9import UIKit
10
11class FMDBManager: NSObject {
12
13 enum CustomFieldsType: String {
14 case cost = "COST"
15 case text = "TEXT"
16 case date = "DATE"
17 case list = "LIST"
18 }
19
20 static let shared: FMDBManager = FMDBManager()
21
22 let databaseFileName = "fourthmate_1.sqlite"
23
24 var database: FMDatabase!
25
26
27 override init() {
28 super.init()
29 }
30
31
32 func getPath() -> String {
33 let documentsURL = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)[0] as NSURL
34 let fileURL = documentsURL.appendingPathComponent(self.databaseFileName)
35 return fileURL!.path
36 }
37
38 func copyFile() {
39 let dbPath: String = getPath()
40 let fileManager = FileManager.default
41
42 if !fileManager.fileExists(atPath: dbPath) {
43 let documentsURL = Bundle.main.resourceURL
44 let fromPath = documentsURL?.appendingPathComponent(self.databaseFileName)
45
46 do {
47 try fileManager.copyItem(atPath: fromPath!.path, toPath: dbPath)
48 } catch let error as NSError {
49 print("Error copy file => \(error.localizedDescription)")
50 }
51 }
52 }
53
54 func openDatabase() -> Bool {
55 if self.database == nil {
56 let path = self.getPath()
57 self.database = FMDatabase(path: path)
58 }
59 if self.database.open() {
60 return true
61 }
62 return false
63 }
64
65 func closeDatabase() {
66 if self.database != nil {
67 //self.database.close()
68 }
69 }
70
71
72 // MARK: - Save user after login
73 func saveUser(with pass: String, and authToken: String, and authLoggedUser: AuthLoggedUser, closure: @escaping (Bool) -> Void) {
74 if self.openDatabase() {
75 var email = ""
76 var name = ""
77 var userId = ""
78 var rev = ""
79 var enabled = false
80 var portraitId = ""
81 var portraitCaption = ""
82 var verified = false
83 var type = ""
84 let imagePath = ""
85
86 if let tempId = authLoggedUser._id {
87 userId = tempId
88 }
89
90 if let tempRev = authLoggedUser._rev {
91 rev = tempRev
92 }
93
94 if let tempEmail = authLoggedUser.email {
95 email = tempEmail
96 }
97
98 if let tempName = authLoggedUser.name {
99 name = tempName
100 }
101
102 if let tempEnabled = authLoggedUser.enabled {
103 enabled = tempEnabled
104 }
105
106 if let tempVerified = authLoggedUser.verified {
107 verified = tempVerified
108 }
109
110 if let tempType = authLoggedUser.type {
111 type = tempType
112 }
113
114 if let images = authLoggedUser.images, images.count > 0 {
115 if let tempPortraitId = images[0].id {
116 portraitId = tempPortraitId
117 }
118 if let tempPortraitCaption = images[0].caption {
119 portraitCaption = tempPortraitCaption
120 }
121 }
122
123 let query = "insert into User (email, password, authToken, name, userId, rev, enabled, portraitId, portraitCaption, verified, type, imagePath) values ('\(email)','\(pass)','\(authToken)','\(name)','\(userId)','\(rev)','\(enabled)','\(portraitId)','\(portraitCaption)','\(verified)','\(type)','\(imagePath)');"
124
125 do {
126 try database.executeUpdate("delete from User", values: [])
127 if !database.executeStatements(query) {
128 closure(false)
129 } else {
130 var domain = ""
131 if let domainUrl = UserDefaults.standard.value(forKey: "DomainUrlKey") as? String {
132 domain = domainUrl
133 }
134
135 //Save cookies
136 let cookieProperties = [
137 HTTPCookiePropertyKey.name : "wave-auth",
138 HTTPCookiePropertyKey.value : authToken + ":" + email,
139 HTTPCookiePropertyKey.domain : domain,
140 HTTPCookiePropertyKey.path : "/"
141 ]
142 UserDefaults.standard.set(cookieProperties, forKey: "cookies")
143 closure(true)
144 }
145 } catch let error as NSError {
146 print("Error deleting user from database => \(error.localizedDescription)")
147 closure(false)
148 }
149 } else {
150 closure(false)
151 }
152 }
153
154 func updateUser(with name: String, path: String, imageId: String, imageCaption: String, closure: @escaping (Bool) -> Void) {
155 if self.openDatabase() {
156 do {
157 try database.executeUpdate("UPDATE User SET imagePath='\(path)', name='\(name)', portraitId='\(imageId)', portraitCaption='\(imageCaption)' ", values: [])
158 closure(true)
159 } catch let error as NSError {
160 print("Error updating user info in database => \(error.localizedDescription)")
161 closure(false)
162 }
163 } else {
164 closure(false)
165 }
166 }
167
168
169 // MARK: - Get current user
170 func getUser() -> CurrentUser? {
171 if self.openDatabase() {
172 let query = "select * from User"
173
174 if database.inTransaction() {
175 return nil
176 } else {
177 do {
178 let results = try database.executeQuery(query, values: nil)
179
180 var authToken = ""
181 var email = ""
182 var pass = ""
183 var name = ""
184 var userId = ""
185 var rev = ""
186 var enabled = false
187 var portraitId = ""
188 var portraitCaption = ""
189 var verified = false
190 var type = ""
191 var imagePath = ""
192
193 while results.next() {
194 authToken = results.string(forColumn: "authToken")
195 email = results.string(forColumn: "email")
196 pass = results.string(forColumn: "password")
197 name = results.string(forColumn: "name")
198 userId = results.string(forColumn: "userId")
199 rev = results.string(forColumn: "rev")
200 enabled = results.bool(forColumn: "enabled")
201 portraitId = results.string(forColumn: "portraitId")
202 portraitCaption = results.string(forColumn: "portraitCaption")
203 verified = results.bool(forColumn: "verified")
204 type = results.string(forColumn: "type")
205 imagePath = results.string(forColumn: "imagePath")
206 }
207 return CurrentUser(userId: userId, name: name, email: email, authToken: authToken, password: pass, rev: rev, enabled: enabled, portraitId: portraitId, portraitCaption: portraitCaption, verified: verified, type: type, imagePath: imagePath)
208
209 } catch let error as NSError {
210 print("Error getting current user => \(error.localizedDescription)")
211 return nil
212 }
213 }
214 }
215 return nil
216 }
217
218 // MARK: - Save all shares
219 func checkIfExistUpdates(with data: [[String: AnyObject]]) {
220 if self.openDatabase() {
221 let fMDBManagerHelper = FMDBManagerHelper()
222
223 for item in data {
224 var label = ""
225
226 if let tempLabel = item["label"] as? String {
227 label = tempLabel
228 }
229
230 var defectsTags = ""
231 if let dTags = item["defectTags"] as? [String] {
232 defectsTags = fMDBManagerHelper.convertDefectTagsToString(defectTags: dTags)
233 }
234
235 let existingDefectsStr = getDefectTagsString(for: label)
236
237 let shareEntity = getShareEntity(for: label)
238
239 var permissionsDefectCreate = false
240 var permissionsDefectEdit = false
241 var permissionsDefectRead = false
242
243 var permissionCommentsEdit = false
244 var permissionCommentsRead = false
245
246 var permissionsTagsRead = false
247 var permissionsTagsEdit = false
248
249 var shareOption = ""
250
251 if let permissions = item["permissions"] as? [String: AnyObject] {
252 if let defectPermissions = permissions["defect"] as? [String: AnyObject] {
253 if let tempPermissionsDefectCreate = defectPermissions["create"] as? Bool {
254 permissionsDefectCreate = tempPermissionsDefectCreate
255 }
256 if let tempPermissionsDefectEdit = defectPermissions["edit"] as? Bool {
257 permissionsDefectEdit = tempPermissionsDefectEdit
258 }
259 if let tempPermissionsDefectRead = defectPermissions["read"] as? Bool {
260 permissionsDefectRead = tempPermissionsDefectRead
261 }
262 }
263 }
264
265 if let advancedAccessLevels = item["advancedAccessLevels"] as? [String: AnyObject] {
266 let (pCommentsEdit, pCommentsRead) = fMDBManagerHelper.convert(advancedAccessLevelsDict: advancedAccessLevels)
267 permissionCommentsEdit = pCommentsEdit
268 permissionCommentsRead = pCommentsRead
269
270 if let tagsPermission = fMDBManagerHelper.convertTagsPermission(advancedAccessLevelsDict: advancedAccessLevels) {
271 permissionsTagsRead = tagsPermission.read
272 permissionsTagsEdit = tagsPermission.edit
273 }
274 } else if let permissions = item["permissions"] as? [String: AnyObject] {
275 if let deffect = permissions["defect"] as? [String: AnyObject] {
276 permissionsTagsEdit = deffect["edit"] as? Bool ?? true
277 permissionsTagsRead = deffect["edit"] as? Bool ?? true
278 permissionCommentsEdit = deffect["edit"] as? Bool ?? true
279 permissionCommentsRead = deffect["edit"] as? Bool ?? true
280 }
281 }
282
283 if let tempShareOption = item["shareOption"] as? String {
284 shareOption = tempShareOption
285 }
286
287
288 let canAddPoint = permissionsDefectCreate == shareEntity.canAddPoint
289 let canReadPoint = permissionsDefectRead == shareEntity.canReadPoint
290 let canEditPoint = permissionsDefectEdit == shareEntity.canEditPoint
291
292 let canCommentsEdit = permissionCommentsEdit == shareEntity.permissionCommentsEdit
293 let canCommentsRead = permissionCommentsRead == shareEntity.permissionCommentsRead
294
295 let canTagsEdit = permissionsTagsEdit == shareEntity.permissionsTagsEdit
296 let canTagsRead = permissionsTagsRead == shareEntity.permissionsTagsRead
297
298 let shOption = shareOption == shareEntity.shareOption
299
300 let defTags = defectsTags == existingDefectsStr
301
302
303 if (!(canAddPoint && canReadPoint && canEditPoint && canCommentsEdit && canCommentsRead && canTagsEdit && canTagsRead && shOption && defTags)) {
304 UserDefaults.standard.set("", forKey: "last_sync_time_\(label)")
305
306 let issues = self.getIssues(for: label)
307
308 for issue in issues {
309
310 let detailIssue = self.getDetailIssue(with: issue.id)
311
312 for img in detailIssue?.images ?? [] {
313 do {
314 try database.executeUpdate("delete from DownloadedImages where imageId='\(img.imageId)'", values: [])
315 try database.executeUpdate("delete from IssueImages where imageId='\(img.imageId)'", values: [])
316 } catch let error as NSError {
317 print("Error deleting issue images from database => \(error.localizedDescription)")
318 }
319 }
320
321 for vid in detailIssue?.videos ?? [] {
322 do {
323 try database.executeUpdate("delete from IssueVideos where videoId='\(vid.videoId)'", values: [])
324 } catch let error as NSError {
325 print("Error deleting issue videos from database => \(error.localizedDescription)")
326 }
327 }
328
329 for comment in detailIssue?.comments ?? [] {
330 do {
331 try database.executeUpdate("delete from IssueComments where commentId='\(comment.commentId)'", values: [])
332 } catch let error as NSError {
333 print("Error deleting issue comments from database => \(error.localizedDescription)")
334 }
335 }
336
337 for doc in detailIssue?.documents ?? [] {
338 do {
339 try database.executeUpdate("delete from IssueDocuments where documentId='\(doc.documentId)'", values: [])
340 } catch let error as NSError {
341 print("Error deleting issue documents from database => \(error.localizedDescription)")
342 }
343 }
344
345 do {
346 try database.executeUpdate("delete from Issues where id='\(issue.id)'", values: [])
347 } catch let error as NSError {
348 print("Error deleting issue from database => \(error.localizedDescription)")
349 }
350 }
351 }
352 }
353 }
354 }
355
356
357 func saveAllShares(with data: [[String: AnyObject]], closure: @escaping (Bool) -> Void) {
358 if self.openDatabase() {
359 do {
360 //Clear Shares db table
361 try database.executeUpdate("delete from Shares", values: [])
362
363 do {
364 try? database.executeUpdate("ALTER TABLE Shares ADD COLUMN permissionCommentsEdit text", values: nil)
365 try? database.executeUpdate("ALTER TABLE Shares ADD COLUMN permissionCommentsRead text", values: nil)
366 try? database.executeUpdate("ALTER TABLE Shares ADD COLUMN permissionsTagsRead VARCHAR", values: nil)
367 try? database.executeUpdate("ALTER TABLE Shares ADD COLUMN permissionsTagsEdit VARCHAR", values: nil)
368 try? database.executeUpdate("ALTER TABLE Shares ADD COLUMN defectTags text", values: nil)
369 }
370
371 do {
372 try? database.executeUpdate("CREATE TABLE IF NOT EXISTS CustomFieldsPermission(templateId INTEGER, read BOOLEAN, edit BOOLEAN)", values: nil)
373 try? database.executeUpdate("delete from CustomFieldsPermission", values: [])
374 }
375
376 var savedCount = 0
377
378 let fMDBManagerHelper = FMDBManagerHelper()
379
380 for item in data {
381
382 var label = ""
383 var shareOption = ""
384 var shareId = ""
385 var shareRev = ""
386 var permissionsSiteShare = false
387 var permissionsSiteRead = false
388 var permissionsFullVisibility = false
389 var permissionsDefectContribute = false
390 var permissionsDefectDelete = false
391 var permissionsDefectEdit = false
392 var permissionsDefectRead = false
393 var permissionsDefectCreate = false
394 var userRefId = ""
395 var userRefType = ""
396 var userRefCaption = ""
397 var permissionCommentsEdit = false
398 var permissionCommentsRead = false
399 var permissionsTagsRead = false
400 var permissionsTagsEdit = false
401 var defectTags = ""
402
403 if let advancedAccessLevels = item["advancedAccessLevels"] as? [String: AnyObject] {
404 let (pCommentsEdit, pCommentsRead) = fMDBManagerHelper.convert(advancedAccessLevelsDict: advancedAccessLevels)
405 permissionCommentsEdit = pCommentsEdit
406 permissionCommentsRead = pCommentsRead
407
408 let customFieldsPermission = fMDBManagerHelper.convertCustomFieldPermission(from: advancedAccessLevels)
409 for fpermissoin in customFieldsPermission {
410 do {
411 try? database.executeUpdate("insert into CustomFieldsPermission (templateId, read, edit) values ('\(fpermissoin.templateId)', '\(fpermissoin.read)', '\(fpermissoin.edit)');", values: nil)
412 }
413 }
414
415 if let tagsPermission = fMDBManagerHelper.convertTagsPermission(advancedAccessLevelsDict: advancedAccessLevels) {
416 permissionsTagsRead = tagsPermission.read
417 permissionsTagsEdit = tagsPermission.edit
418 }
419 } else if let permissions = item["permissions"] as? [String: AnyObject] {
420 if let deffect = permissions["defect"] as? [String: AnyObject] {
421 permissionsTagsEdit = deffect["edit"] as? Bool ?? true
422 permissionsTagsRead = deffect["edit"] as? Bool ?? true
423 permissionCommentsEdit = deffect["edit"] as? Bool ?? true
424 permissionCommentsRead = deffect["edit"] as? Bool ?? true
425
426 }
427 }
428
429 if let dTags = item["defectTags"] as? [String] {
430 defectTags = fMDBManagerHelper.convertDefectTagsToString(defectTags: dTags)
431 }
432
433 if let tempLabel = item["label"] as? String {
434 label = tempLabel
435 }
436
437 if let tempShareOption = item["shareOption"] as? String {
438 shareOption = tempShareOption
439 }
440
441 if let tempShareId = item["_id"] as? String {
442 shareId = tempShareId
443 }
444
445 if let tempShareRev = item["_rev"] as? String {
446 shareRev = tempShareRev
447 }
448
449 if let userRef = item["userRef"] as? [String: AnyObject] {
450 if let tempUserRefId = userRef["id"] as? String {
451 userRefId = tempUserRefId
452 }
453
454 if let tempUserRefType = userRef["type"] as? String {
455 userRefType = tempUserRefType
456 }
457
458 if let tempUserRefCaption = userRef["caption"] as? String {
459 userRefCaption = tempUserRefCaption
460 }
461 }
462
463 if let permissions = item["permissions"] as? [String: AnyObject] {
464 if let sitePermissions = permissions["site"] as? [String: AnyObject] {
465 if let tempPermissionsSiteShare = sitePermissions["share"] as? Bool {
466 permissionsSiteShare = tempPermissionsSiteShare
467 }
468 if let tempPermissionsSiteRead = sitePermissions["read"] as? Bool {
469 permissionsSiteRead = tempPermissionsSiteRead
470 }
471 }
472
473 if let tempPpermissionsFullVisibility = permissions["fullVisibility"] as? Bool {
474 permissionsFullVisibility = tempPpermissionsFullVisibility
475 }
476
477 if let defectPermissions = permissions["defect"] as? [String: AnyObject] {
478 if let tempPermissionsDefectContribute = defectPermissions["contribute"] as? Bool {
479 permissionsDefectContribute = tempPermissionsDefectContribute
480 }
481 if let tempPermissionsDefectDelete = defectPermissions["delete"] as? Bool {
482 permissionsDefectDelete = tempPermissionsDefectDelete
483 }
484 if let tempPermissionsDefectEdit = defectPermissions["edit"] as? Bool {
485 permissionsDefectEdit = tempPermissionsDefectEdit
486 }
487 if let tempPermissionsDefectRead = defectPermissions["read"] as? Bool {
488 permissionsDefectRead = tempPermissionsDefectRead
489 }
490 if let tempPermissionsDefectCreate = defectPermissions["create"] as? Bool {
491 permissionsDefectCreate = tempPermissionsDefectCreate
492 }
493 }
494 }
495
496 let query = "insert into Shares (label, shareOption, shareId, shareRev, permissionsSiteShare, permissionsSiteRead, permissionsFullVisibility, permissionsDefectContribute, permissionsDefectDelete, permissionsDefectEdit, permissionsDefectRead, permissionsDefectCreate, userRefId, userRefType, userRefCaption, permissionCommentsEdit, permissionCommentsRead, permissionsTagsRead, permissionsTagsEdit, defectTags) values ('\(label)','\(shareOption)','\(shareId)','\(shareRev)','\(permissionsSiteShare)','\(permissionsSiteRead)','\(permissionsFullVisibility)','\(permissionsDefectContribute)','\(permissionsDefectDelete)','\(permissionsDefectEdit)','\(permissionsDefectRead)','\(permissionsDefectCreate)','\(userRefId)','\(userRefType)','\(userRefCaption)', '\(permissionCommentsEdit)', '\(permissionCommentsRead)', '\(permissionsTagsRead)', '\(permissionsTagsEdit)','\(defectTags)');"
497
498 if !database.executeStatements(query) {
499 } else {
500 savedCount += 1
501 }
502
503 }
504
505 if savedCount == data.count {
506 closure(true)
507 } else {
508 closure(false)
509 }
510 } catch let error as NSError {
511 print("Error deleting shares from database => \(error.localizedDescription)")
512 closure(false)
513 }
514 } else {
515 closure(false)
516 }
517 }
518
519 func getShare(for worksapce: String) -> [String : AnyObject]? {
520 if self.openDatabase() {
521 let query = "select * from Shares where label='\(worksapce)'"
522
523 do {
524 let results = try database.executeQuery(query, values: nil)
525
526 while results.next() {
527 return ["id" : results.string(forColumn: "userRefId")! as AnyObject, "type" : results.string(forColumn: "userRefType")! as AnyObject, "caption" : results.string(forColumn: "userRefCaption")! as AnyObject]
528 }
529
530 return nil
531 } catch let error as NSError {
532 print("Error getting shares from database => \(error.localizedDescription)")
533 return nil
534 }
535 } else {
536 return nil
537 }
538 }
539
540 func getShareEntity(for workspace: String) -> ShareEntity {
541 var share = ShareEntity()
542
543 if !self.openDatabase() {
544 return share
545 }
546
547 let query = "select * from Shares where label='\(workspace)'"
548
549 do {
550 let results = try database.executeQuery(query, values: nil)
551
552 while results.next() {
553 let canEdit = results.string(forColumn: "permissionsDefectEdit")
554 let canCreate = results.string(forColumn: "permissionsDefectCreate")
555 let canRead = results.string(forColumn: "permissionsDefectRead")
556
557 let permissionCommentsEdit = results.string(forColumn: "permissionCommentsEdit")
558 let permissionCommentsRead = results.string(forColumn: "permissionCommentsRead")
559
560 let permissionsTagsRead = results.string(forColumn: "permissionsTagsRead")
561 let permissionsTagsEdit = results.string(forColumn: "permissionsTagsEdit")
562
563 share.canEditPoint = (canEdit == "true") ? true : false ;
564 share.canAddPoint = (canCreate == "true") ? true : false ;
565 share.canReadPoint = (canRead == "true") ? true : false;
566
567 share.permissionCommentsEdit = (permissionCommentsEdit == "true") ? true : false ;
568 share.permissionCommentsRead = (permissionCommentsRead == "true") ? true : false ;
569
570 share.permissionsTagsRead = (permissionsTagsRead == "true") ? true : false
571 share.permissionsTagsEdit = (permissionsTagsEdit == "true") ? true : false
572
573 share.shareOption = results.string(forColumn: "shareOption") ?? ""
574
575 return share
576 }
577 print("Shares is empty for workspace id => \(workspace)")
578 return share
579 } catch let error as NSError {
580 print("Error getting share entity from database => \(error.localizedDescription)")
581 return share
582 }
583 }
584
585 func getWorkspaceAcc(for workspace: String) -> [String] {
586 if !self.openDatabase() {
587 return ["", ""]
588 }
589
590 let query = "select * from Workspaces where id='\(workspace)'"
591
592 do {
593 let results = try database.executeQuery(query, values: nil)
594
595 while results.next() {
596 let siteCaption = results.string(forColumn: "siteCaption")
597 let accountId = results.string(forColumn: "accountId")
598
599 let query1 = "select * from Accounts where id='\(accountId!)'"
600 do {
601 let results1 = try database.executeQuery(query1, values: nil)
602
603 while results1.next() {
604 let name = results1.string(forColumn: "name")
605
606 return [siteCaption!, name!]
607 }
608 } catch let error as NSError {
609 print("Error get account from database for id '\(accountId!)' => \(error.localizedDescription)")
610 }
611 }
612 } catch let error as NSError {
613 print("Error get workspace from database for id '\(workspace)' => \(error.localizedDescription)")
614 }
615 return ["", ""]
616 }
617
618 // MARK: - Save/Get all accounts and workspaces
619 func checkNewSites(with data: [[String: AnyObject]], closure: @escaping (Bool) -> Void) {
620 if !self.openDatabase() {
621 closure(false)
622 }
623
624 for item in data {
625 let name = item["name"] as? String ?? ""
626 let accountId = item["id"] as? String ?? ""
627 var workspaceIds = ""
628
629 if let tempWorkspaces = item["workspaces"] as? [[String: AnyObject]] {
630 for (index, tempWorkspace) in tempWorkspaces.enumerated() {
631 let siteCaption = tempWorkspace["siteName"] as? String ?? ""
632 let workspaceId = tempWorkspace["_id"] as? String ?? ""
633
634 var siteId = ""
635 if let siteRef = tempWorkspace["siteRef"] as? [String: AnyObject] {
636 if let tempSiteId = siteRef["id"] as? String {
637 siteId = tempSiteId
638 }
639 }
640
641 if index == tempWorkspaces.count - 1 {
642 workspaceIds = "\(workspaceIds)\(workspaceId)"
643 } else {
644 workspaceIds = "\(workspaceIds)\(workspaceId),"
645 }
646
647 //Save workspace in db
648 var count = 0
649 var query = "select count(*) as count from Workspaces where id='\(workspaceId)'"
650 do {
651 let results = try database.executeQuery(query, values: nil)
652 while results.next() {
653 count = Int(results.int(forColumn: "count"))
654 }
655 } catch let error as NSError {
656 print("Error get workspaces count from database for id '\(workspaceId)' => \(error.localizedDescription)")
657 }
658
659 if count == 0 {
660 let query1 = "insert into Workspaces (siteCaption, siteId, accountId, id) values ('\(siteCaption)','\(siteId)','\(accountId)','\(workspaceId)');"
661 if !database.executeStatements(query1) {
662 print("Error insert into workspaces for workspace id \(workspaceId)")
663 }
664 }
665
666 count = 0
667 query = "select count(*) as count from Sites where id='\(siteId)'"
668 do {
669 let results = try database.executeQuery(query, values: nil)
670 while results.next() {
671 count = Int(results.int(forColumn: "count"))
672 }
673 } catch let error as NSError {
674 print("Error get sites count from database for id '\(siteId)' => \(error.localizedDescription)")
675 }
676
677 if count == 0 {
678 //Save site which need download again in db
679 let query2 = "insert into UnSavedSites (id) values ('\(siteId)');"
680 if !database.executeStatements(query2) {
681 print("Error insert into UnSavedSites for site id \(siteId)")
682 }
683 }
684 }
685 }
686 var count = 0
687 let query = "select count(*) as count from Accounts where id='\(accountId)'"
688 do {
689 let results = try database.executeQuery(query, values: nil)
690 while results.next() {
691 count = Int(results.int(forColumn: "count"))
692 }
693 } catch let error as NSError {
694 print("Error select account count from database for id '\(accountId)' => \(error.localizedDescription)")
695 }
696
697 if count == 0 {
698 //Save account in db
699 let query = "insert into Accounts (name, id, workspaces) values ('\(name)', '\(accountId)', '\(workspaceIds)');"
700 if !database.executeStatements(query) {
701 print("Error insert into accounts for account id \(accountId)")
702 }
703 } else {
704 do {
705 try database.executeUpdate("UPDATE Accounts SET workspaces=? WHERE id=?", values: [workspaceIds, accountId])
706 } catch let error as NSError {
707 print("Error update account in database for id '\(accountId)' => \(error.localizedDescription)")
708 }
709 }
710 }
711 closure(true)
712 }
713
714 func updateAllAccounts(with data: [[String: AnyObject]], closure: @escaping (Bool) -> Void) {
715 if self.openDatabase() {
716 closure(false)
717 }
718
719 for item in data {
720 let name = item["name"] as? String ?? ""
721 let accountId = item["id"] as? String ?? ""
722 var workspaceIds = ""
723
724 if let tempWorkspaces = item["workspaces"] as? [[String: AnyObject]] {
725 for (index, tempWorkspace) in tempWorkspaces.enumerated() {
726 let siteCaption = tempWorkspace["siteName"] as? String ?? ""
727 let workspaceId = tempWorkspace["_id"] as? String ?? ""
728 let workspaceAccountId = accountId
729
730 var siteId = ""
731 if let siteRef = tempWorkspace["siteRef"] as? [String: AnyObject] {
732 if let tempSiteId = siteRef["id"] as? String {
733 siteId = tempSiteId
734 }
735 }
736
737 if index == tempWorkspaces.count - 1 {
738 workspaceIds = "\(workspaceIds)\(workspaceId)"
739 } else {
740 workspaceIds = "\(workspaceIds)\(workspaceId),"
741 }
742
743 var count = 0
744 let query = "select count(*) as count from Workspaces where siteId='\(siteId)' AND id='\(workspaceId)'"
745 do {
746 let results = try database.executeQuery(query, values: nil)
747 while results.next() {
748 count = Int(results.int(forColumn: "count"))
749 }
750 } catch let error as NSError {
751 print("Error get workspace count from database for id '\(workspaceId)' => \(error.localizedDescription)")
752 }
753
754 if count == 0 {
755 //Save workspace in db
756 let query1 = "insert into Workspaces (siteCaption, siteId, accountId, id) values ('\(siteCaption)','\(siteId)','\(workspaceAccountId)','\(workspaceId)');"
757 if !database.executeStatements(query1) {
758 print("Error insert into Workspaces for id '\(workspaceId)')")
759 }
760
761 //Save site which need download again in db
762 let query2 = "insert into UnSavedSites (id) values ('\(siteId)');"
763 if !database.executeStatements(query2) {
764 print("Error insert into UnSavedSites for site id '\(siteId)')")
765 }
766 } else {
767 do {
768 try database.executeUpdate("UPDATE Workspaces SET siteCaption=? WHERE siteId=? AND id=?", values: [siteCaption, siteId, workspaceId])
769 } catch let error as NSError {
770 print("Error update workspace in database for id '\(workspaceId)' => \(error.localizedDescription)")
771 closure(false)
772 }
773 }
774 }
775 }
776
777 var count = 0
778 let query = "select count(*) as count from Accounts where id='\(accountId)'"
779 do {
780 let results = try database.executeQuery(query, values: nil)
781 while results.next() {
782 count = Int(results.int(forColumn: "count"))
783 }
784 } catch let error as NSError {
785 print("Error get account count from database for id '\(accountId)' => \(error.localizedDescription)")
786 }
787 if count == 0 {
788 //Save account in db
789 let query3 = "insert into Accounts (name, id, workspaces) values ('\(name)', '\(accountId)', '\(workspaceIds)');"
790 if !database.executeStatements(query3) {
791 print("Error insert into accounts to database for id '\(accountId)'")
792 }
793 } else {
794 do {
795 try database.executeUpdate("UPDATE Accounts SET name=?, workspaces=? WHERE id=?", values: [name, workspaceIds, accountId])
796 } catch let error as NSError {
797 print("Error update accounts in database for id '\(accountId)' => \(error.localizedDescription)")
798 closure(false)
799 }
800 }
801 }
802 closure(true)
803 }
804
805
806 func saveAllAccounts(with data: [[String: AnyObject]], closure: @escaping (Bool) -> Void) {
807 if self.openDatabase() {
808 do {
809 //Clear Accounts, Workspaces, UnSavedSites db tables
810 try database.executeUpdate("delete from Accounts", values: [])
811 try database.executeUpdate("delete from Workspaces", values: [])
812 try database.executeUpdate("delete from UnSavedSites", values: [])
813
814 for item in data {
815 var name = ""
816 var accountId = ""
817 var workspaceIds = ""
818
819 if let tempName = item["name"] as? String {
820 name = tempName
821 }
822
823 if let tempAccountId = item["id"] as? String {
824 accountId = tempAccountId
825 }
826
827 if let tempWorkspaces = item["workspaces"] as? [[String: AnyObject]] {
828 for (index, tempWorkspace) in tempWorkspaces.enumerated() {
829 var siteCaption = ""
830 var siteId = ""
831 var workspaceId = ""
832 let workspaceAccountId = accountId
833
834 if let tempWorkspaceId = tempWorkspace["_id"] as? String {
835 workspaceId = tempWorkspaceId
836 }
837
838 siteCaption = tempWorkspace["siteName"] as? String ?? ""
839
840 if let siteRef = tempWorkspace["siteRef"] as? [String: AnyObject] {
841 if let tempSiteId = siteRef["id"] as? String {
842 siteId = tempSiteId
843 }
844 }
845
846 if index == tempWorkspaces.count - 1 {
847 workspaceIds = "\(workspaceIds)\(workspaceId)"
848 } else {
849 workspaceIds = "\(workspaceIds)\(workspaceId),"
850 }
851
852 //Save workspace in db
853 let query1 = "insert into Workspaces (siteCaption, siteId, accountId, id) values (?,'\(siteId)','\(workspaceAccountId)','\(workspaceId)');"
854 try database.executeUpdate(query1, values: [siteCaption])
855
856 //Save site which need download again in db
857 let query2 = "insert into UnSavedSites (id) values ('\(siteId)');"
858 if !database.executeStatements(query2) {
859 } else {
860 }
861 }
862 }
863
864 //Save account in db
865 let query = "insert into Accounts (name, id, workspaces) values (?, '\(accountId)', '\(workspaceIds)');"
866 try database.executeUpdate(query, values: [name])
867 }
868
869 closure(true)
870 } catch {
871 closure(false)
872 }
873 } else {
874 closure(false)
875 }
876 }
877
878 func getAllAccountsandWorkspaces() -> [AccountEntity] {
879 var accounts: [AccountEntity] = [AccountEntity]()
880 if self.openDatabase() {
881 let query = "select * from Accounts"
882
883 do {
884 let results = try database.executeQuery(query, values: nil)
885
886 while results.next() {
887 let id = results.string(forColumn: "id")
888 let name = results.string(forColumn: "name")
889
890 var workspaces: [WorkspaceEntity] = [WorkspaceEntity]()
891
892 let workspacesIdsStr = results.string(forColumn: "workspaces")
893
894 let workspacesIdsArr = workspacesIdsStr?.components(separatedBy: ",")
895
896 for workspacesId in workspacesIdsArr! {
897 let query = "select * from Workspaces where id='\(workspacesId)'"
898 do {
899 let workspacesResults = try database.executeQuery(query, values: nil)
900
901 while workspacesResults.next() {
902 let id = workspacesResults.string(forColumn: "id")
903 let accountId = workspacesResults.string(forColumn: "accountId")
904 let siteId = workspacesResults.string(forColumn: "siteId")
905 let siteCaption = workspacesResults.string(forColumn: "siteCaption")
906
907 workspaces.append(WorkspaceEntity(id: id!, accountId: accountId!, siteId: siteId!, siteCaption: siteCaption!))
908 }
909 } catch {
910 }
911 }
912
913 workspaces = workspaces.sorted(by: { (wsp1, wsp2) -> Bool in
914 return wsp1.siteCaption < wsp2.siteCaption
915 })
916
917 accounts.append(AccountEntity(name: name!, id: id!, workspaces: workspaces))
918 }
919 return accounts
920 } catch {
921 return accounts
922 }
923 } else {
924 return accounts
925 }
926 }
927
928 // MARK: - Get unsaved sites
929 func getUnsavedSites(closure: @escaping ([String]) -> Void) {
930 var ids: [String] = [String]()
931
932 if self.openDatabase() {
933 let query = "select * from UnSavedSites"
934
935 do {
936 let results = try database.executeQuery(query, values: nil)
937
938 while results.next() {
939 ids.append(results.string(forColumn: "id"))
940 }
941
942 closure(ids)
943
944 } catch {
945 closure(ids)
946 }
947 } else {
948 closure(ids)
949 }
950
951 }
952
953 func isSiteSaved(siteID: String) -> Bool {
954 var ids: [String] = [String]()
955
956 if self.openDatabase() {
957 let query = "select * from UnSavedSites where id='\(siteID)'"
958
959 do {
960 let results = try database.executeQuery(query, values: nil)
961
962 while results.next() {
963 ids.append(results.string(forColumn: "id"))
964 }
965
966 if ids.count > 0 {
967 return false
968 } else {
969 return true
970 }
971
972 } catch {
973 return false
974 }
975 }
976
977 return false
978 }
979
980 func removeSavedSiteFromUnsaved(with siteID: String) {
981 if self.openDatabase() {
982 do {
983 try database.executeUpdate("delete from UnSavedSites where id='\(siteID)'", values: [])
984 } catch {
985
986 }
987 }
988 }
989
990 // MARK: - Save site
991 func markSiteAsDownloaded(siteID: String, tiles: String, largeImage: String, limit: String) -> Bool {
992 if self.openDatabase() {
993 do {
994 try database.executeUpdate("create table if not exists DownloadedSitePlans(siteId text, tiles text, largeImage text, lastLimit text)", values: nil)
995 try database.executeUpdate("delete from DownloadedSitePlans where siteId='\(siteID)'", values: [])
996 let query = "insert into DownloadedSitePlans (siteId, tiles, largeImage, lastLimit) values ('\(siteID)', '\(tiles)', '\(largeImage)', '\(limit)');"
997 try database.executeUpdate(query, values: [])
998
999 return true
1000 } catch {
1001 return false
1002 }
1003 } else {
1004 return false
1005 }
1006 }
1007
1008 func isSiteDownloaded(siteID: String) -> Bool {
1009 if self.openDatabase() {
1010 let query = "select * from DownloadedSitePlans where siteId='\(siteID)'"
1011
1012 do {
1013 let results = try database.executeQuery(query, values: nil)
1014
1015 while results.next() {
1016 return true
1017 }
1018
1019 return false
1020 } catch {
1021 return false
1022 }
1023 }
1024 return false
1025 }
1026
1027 func isFullSiteDownloaded(siteID: String) -> Bool {
1028 if self.openDatabase() {
1029 let query = "select * from DownloadedSitePlans where siteId='\(siteID)'"
1030
1031 do {
1032 let results = try database.executeQuery(query, values: nil)
1033
1034 while results.next() {
1035 let lastLimit = results.string(forColumn: "lastLimit")
1036 if lastLimit == "-1" {
1037 return true
1038 } else {
1039 return false
1040 }
1041
1042 }
1043 return false
1044 } catch {
1045 return false
1046 }
1047 }
1048 return false
1049 }
1050
1051 func saveSite(with data: [String: AnyObject], closure: @escaping (String, String, Bool) -> Void) {
1052 if self.openDatabase() {
1053
1054 do {
1055 try self.database.executeUpdate("ALTER TABLE Sites ADD COLUMN assignees TEXT", values: nil)
1056 }
1057 catch {
1058 }
1059
1060 var siteId = ""
1061 var rev = ""
1062 var workspaceId = ""
1063 var name = ""
1064 var tags = ""
1065 var imageId = ""
1066
1067 if let tempSiteId = data["_id"] as? String {
1068 siteId = tempSiteId
1069 }
1070
1071 if let tempRev = data["_rev"] as? String {
1072 rev = tempRev
1073 }
1074
1075 if let workspaceRef = data["workspaceRef"] as? [String: AnyObject] {
1076 if let tempWorkspaceId = workspaceRef["id"] as? String {
1077 workspaceId = tempWorkspaceId
1078 }
1079 }
1080
1081 if let tempName = data["name"] as? String {
1082 name = tempName
1083 }
1084
1085 if let siteImageRef = data["siteImageRef"] as? [String: AnyObject] {
1086 if let tempImageId = siteImageRef["id"] as? String {
1087 imageId = tempImageId
1088 }
1089 }
1090
1091 if let tempTags = data["tags"] as? [String] {
1092 for (index, tag) in tempTags.enumerated() {
1093 if index == tempTags.count - 1 {
1094 tags = "\(tags)\(tag)"
1095 } else {
1096 tags = "\(tags)\(tag)|"
1097 }
1098 }
1099 }
1100
1101 RequestService().getAssigneesForWorkspace(workspaceId: workspaceId, closure: { (response, error) in
1102 if error == nil {
1103 do {
1104 try self.database.executeUpdate("create table if not exists IssueAssignees(id text primary key, caption text, primaryImageId text, type text)", values: nil)
1105
1106 if let assigneeList = response?["entity"] as? [[String: AnyObject]] {
1107
1108 var assigneeIds = "";
1109 for (index, assignee) in assigneeList.enumerated() {
1110 let assigneeId = assignee["id"] as? String ?? ""
1111
1112 do {
1113 if index == assigneeList.count - 1 {
1114 assigneeIds = "\(assigneeIds)\(assigneeId)"
1115 } else {
1116 assigneeIds = "\(assigneeIds)\(assigneeId),"
1117 }
1118
1119 let caption = assignee["caption"] as? String ?? ""
1120 let primaryImageId = assignee["primaryImageId"] as? String ?? ""
1121 let type = assignee["type"] as? String ?? ""
1122
1123 let query = "insert into IssueAssignees (id, caption, primaryImageId, type) values ('\(assigneeId)', '\(caption)', '\(primaryImageId)', '\(type)');"
1124 do {
1125 try self.database.executeUpdate("delete from IssueAssignees where id='\(assigneeId)'", values: [])
1126 try self.database.executeUpdate(query, values: [])
1127 } catch {
1128 }
1129 }
1130 }
1131 do {
1132 if (assigneeIds != "") {
1133 let queryUpdate = "update Sites set assignees='\(assigneeIds)' where workspaceId='\(workspaceId)'"
1134 try self.database.executeUpdate(queryUpdate, values: nil)
1135 }
1136 } catch {
1137 }
1138 }
1139 } catch {
1140 }
1141 } else {
1142 print("ERROR->\(String(describing: error))")
1143 }
1144 })
1145
1146 do {
1147 //Remove from site table if exist with same ID
1148 try database.executeUpdate("delete from Sites where id='\(siteId)'", values: [])
1149
1150 //Save site in db
1151 let query = "insert into Sites (id, workspaceId, name, tags, imageId) values ('\(siteId)', '\(workspaceId)', ?, ?, '\(imageId)');"
1152 try database.executeUpdate(query, values: [name, tags])
1153
1154 try self.database.executeUpdate("UPDATE Workspaces SET siteCaption=? WHERE siteId=?", values: [name, siteId])
1155
1156 //------------
1157 //Save site tiles in db
1158
1159 if let sitePlan = data["sitePlan"] as? [String: AnyObject] {
1160
1161 var extents = ""
1162 var maxZoom = -1
1163 var minZoom = -1
1164 var pixelSize = ""
1165 var resolutions = ""
1166 var sitePlanURL = ""
1167 var version = ""
1168
1169 if let tempVersion = sitePlan["version"] as? Int {
1170 version = "\(tempVersion)"
1171 }
1172
1173 if let tempSitePlanURL = sitePlan["sitePlanURL"] as? String {
1174 sitePlanURL = tempSitePlanURL
1175 }
1176
1177 if let tempPixelSize = sitePlan["pixelSize"] as? Int {
1178 pixelSize = "\(tempPixelSize)"
1179 }
1180
1181 if let tempMaxZoom = sitePlan["maxZoom"] as? Int {
1182 maxZoom = tempMaxZoom
1183 }
1184
1185 if let tempMinZoom = sitePlan["minZoom"] as? Int {
1186 minZoom = tempMinZoom
1187 }
1188
1189 if let tempExtents = sitePlan["extent"] as? [Int] {
1190 for (index, extent) in tempExtents.enumerated() {
1191 if index == tempExtents.count - 1 {
1192 extents = "\(extents)\(extent)"
1193 } else {
1194 extents = "\(extents)\(extent)|"
1195 }
1196 }
1197 }
1198
1199 if let tempResolutions = sitePlan["resolutions"] as? [Int] {
1200 for (index, resolution) in tempResolutions.enumerated() {
1201 if index == tempResolutions.count - 1 {
1202 resolutions = "\(resolutions)\(resolution)"
1203 } else {
1204 resolutions = "\(resolutions)\(resolution)|"
1205 }
1206 }
1207 }
1208
1209 do {
1210 try database.executeUpdate("create table if not exists SitePlanTiles(id text, rev text, workspaceId text, version text, sitePlanURL text, maxZoom text, minZoom text, pixelSize text, resolutions text, extent text)", values: nil)
1211
1212 try database.executeUpdate("delete from SitePlanTiles where id='\(siteId)' AND workspaceId='\(workspaceId)'", values: [])
1213
1214 //Save site plan in db
1215 let query = "insert into SitePlanTiles (id, rev, workspaceId, version, sitePlanURL, maxZoom, minZoom, pixelSize, resolutions, extent) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
1216 try database.executeUpdate(query, values: [siteId, rev, workspaceId, version, sitePlanURL, "\(maxZoom)", "\(minZoom)", pixelSize, resolutions, extents])
1217 } catch {
1218 print("Something wrong during saving to db")
1219 }
1220
1221 }
1222 //------------
1223
1224 if let fields = data["customFields"] as? [[String: AnyObject]] {
1225 for customField in fields {
1226 let fieldType = customField["type"] as? String ?? ""
1227 let fieldId = "\(customField["id"] as? Int ?? -1)"
1228 let currency = customField["currency"] as? String ?? ""
1229 let label = customField["label"] as? String ?? ""
1230
1231 do {
1232 try database.executeUpdate("create table if not exists AllCustomFields(id text, currency text, label text, type text, workspaceId text)", values: nil)
1233
1234 try database.executeUpdate("delete from AllCustomFields where id='\(fieldId)' AND workspaceId='\(workspaceId)'", values: [])
1235
1236 //Save custom field in db
1237 let query = "insert into AllCustomFields (id, currency, label, type, workspaceId) values (?, ?, ?, ?, ?);"
1238 try database.executeUpdate(query, values: [fieldId, currency, label, fieldType, workspaceId])
1239
1240
1241 //Save list to db
1242 if fieldType == CustomFieldsType.list.rawValue {
1243 if let subList = customField["subList"] as? [[String: AnyObject]] {
1244 if subList.count > 0 {
1245 self.saveSubListToDB(parentId: fieldId, list: subList, superId: fieldId, workspaceId: workspaceId)
1246 }
1247 }
1248 }
1249
1250 } catch {
1251
1252 }
1253 }
1254 }
1255
1256 closure(siteId, imageId, true)
1257 } catch {
1258 closure("", "", false)
1259 }
1260 } else {
1261 closure("", "", false)
1262 }
1263 }
1264
1265 func getCustomFieldTemplateID(with label: String, and workspaceId: String) -> String {
1266 var customFieldTemplateID = ""
1267 if self.openDatabase() {
1268 let query = "select * from AllCustomFields where label='\(label)' AND workspaceId='\(workspaceId)'"
1269
1270 do {
1271 let results = try database.executeQuery(query, values: nil)
1272
1273 while results.next() {
1274 customFieldTemplateID = results.string(forColumn: "id")!
1275
1276 return customFieldTemplateID
1277 }
1278 } catch {
1279 }
1280 }
1281 return customFieldTemplateID
1282 }
1283
1284 func continueDownloadTiles(for siteId: String) {
1285 if self.openDatabase() {
1286 let query = "select * from Tiles where siteId='\(siteId)' AND localUrl=''"
1287
1288
1289 do {
1290
1291 let results = try database.executeQuery(query, values: nil)
1292
1293 while results.next() {
1294 let tempUrl = results.string(forColumn: "tileUrl")!
1295 let workspaceID = results.string(forColumn: "workspaceId")!
1296 let tempSiteId = results.string(forColumn: "siteId")!
1297
1298 RequestService().downloadTileImage(siteID: tempSiteId, workspaceID: workspaceID, imageUrl: tempUrl, closure: { (closureІiteId, closureWorkspaceId, closureImageUrl, closureLocalPath) in
1299 do {
1300 //Check if tile was downloaded
1301 if (closureLocalPath != nil) {
1302 try self.database.executeUpdate("UPDATE Tiles SET localUrl=? WHERE tileUrl=?", values: [closureLocalPath!, closureImageUrl])
1303 } else {
1304 }
1305 print("Saved tile to db")
1306 } catch {
1307 print("Something wrong during saving tile to db")
1308 }
1309 })
1310 }
1311 } catch {
1312
1313 }
1314 }
1315 }
1316
1317 func getStatusOfTilesDownloading(for siteId: String) -> String {
1318 let statusStr: String = ""
1319 var countOfAllTiles: Int = 0
1320 var countOfAllTilesNotDownloaded: Int = 0
1321
1322 if self.openDatabase() {
1323 var query = "select count(*) as count from Tiles where siteId='\(siteId)'"
1324 do {
1325 var results = try database.executeQuery(query, values: nil)
1326
1327 while results.next() {
1328 countOfAllTiles = Int(results.int(forColumn: "count"))
1329 }
1330
1331 query = "select count(*) as count from Tiles where siteId='\(siteId)' AND localUrl=''"
1332
1333 results = try database.executeQuery(query, values: nil)
1334
1335 while results.next() {
1336 countOfAllTilesNotDownloaded = Int(results.int(forColumn: "count"))
1337 }
1338
1339 if countOfAllTilesNotDownloaded > 0 && countOfAllTiles > 0 {
1340 return "\(countOfAllTiles - countOfAllTilesNotDownloaded) of \(countOfAllTiles) tiles"
1341 }
1342
1343 } catch {
1344
1345 }
1346 }
1347
1348 return statusStr
1349 }
1350
1351 func getSitePlanTileEntity(for siteID: String) -> SitePlanEntity? {
1352 if self.openDatabase() {
1353 let query = "select * from SitePlanTiles where id='\(siteID)'"
1354
1355 do {
1356 let results = try database.executeQuery(query, values: nil)
1357
1358 while results.next() {
1359 let id = results.string(forColumn: "id")!
1360 let rev = results.string(forColumn: "rev")!
1361 let workspaceId = results.string(forColumn: "workspaceId")!
1362 let version = results.string(forColumn: "version")!
1363 let sitePlanURL = results.string(forColumn: "sitePlanURL")!
1364 let maxZoom = results.string(forColumn: "maxZoom")!
1365 let minZoom = results.string(forColumn: "minZoom")!
1366 let pixelSize = results.string(forColumn: "pixelSize")!
1367 let resolutions = results.string(forColumn: "resolutions")!
1368 let extent = results.string(forColumn: "extent")!
1369
1370
1371 return SitePlanEntity(id: id, rev: rev, workspaceId: workspaceId, version: version, sitePlanURL: sitePlanURL, maxZoom: maxZoom, minZoom: minZoom, pixelSize: pixelSize, resolutions: resolutions, extent: extent)
1372 }
1373
1374 return nil
1375 } catch {
1376 return nil
1377 }
1378 } else {
1379 return nil
1380 }
1381 }
1382
1383
1384 func saveSubListToDB(parentId: String, list: [[String: AnyObject]], superId: String, workspaceId: String) {
1385 for item in list {
1386 let newId = "\(item["id"] as? Int ?? -1)"
1387 let newLabel = item["label"] as? String ?? ""
1388
1389 do {
1390 try database.executeUpdate("create table if not exists SubList(id text, parentId text, label text, superId text, workspaceId text)", values: nil)
1391
1392 try database.executeUpdate("delete from SubList where id='\(newId)' AND label=? AND workspaceId='\(workspaceId)'", values: [newLabel])
1393
1394 //Save custom field in db
1395 let query = "insert into SubList (id, parentId, label, superId, workspaceId) values (?, ?, ?, ?, ?);"
1396 try database.executeUpdate(query, values: [newId, parentId, newLabel, superId, workspaceId])
1397
1398 if let subList = item["subList"] as? [[String: AnyObject]] {
1399 self.saveSubListToDB(parentId: newId, list: subList, superId: superId, workspaceId: workspaceId)
1400 } else {
1401 }
1402
1403 } catch {
1404 print("List saving error")
1405 }
1406 }
1407 }
1408
1409 func getSubListItemID(with label: String, and workspaceId: String, and superId: String) -> String {
1410 var subListItemID = ""
1411 if self.openDatabase() {
1412 let query = "select * from SubList where label='\(label)' AND workspaceId='\(workspaceId)' AND superId='\(superId)'"
1413
1414 do {
1415 let results = try database.executeQuery(query, values: nil)
1416
1417 while results.next() {
1418 subListItemID = results.string(forColumn: "id")!
1419
1420 return subListItemID
1421 }
1422 } catch {
1423 }
1424 }
1425 return subListItemID
1426 }
1427
1428 func getSubListItems(for parentId: String, and workspaceId: String, and superID: String) -> [SubListItem] {
1429 var items: [SubListItem] = [SubListItem]()
1430
1431 if self.openDatabase() {
1432
1433 let query = "select * from SubList where parentId='\(parentId)' AND workspaceId='\(workspaceId)' AND superId='\(superID)'"
1434
1435 do {
1436 let results = try database.executeQuery(query, values: nil)
1437
1438 while results.next() {
1439 items.append(SubListItem(itemId: results.string(forColumn: "id")!, parentId: results.string(forColumn: "parentId")!, label: results.string(forColumn: "label")!))
1440 }
1441
1442 } catch {
1443
1444 }
1445
1446 return items
1447 } else {
1448 return items
1449 }
1450 }
1451
1452
1453 func getAllCustomFields(for workspaceId: String) -> [CustomFieldEntity] {
1454 var allFields: [CustomFieldEntity] = [CustomFieldEntity]()
1455 if self.openDatabase() {
1456
1457 let query = "select * from AllCustomFields where workspaceId='\(workspaceId)'"
1458
1459 do {
1460 let results = try database.executeQuery(query, values: nil)
1461
1462 while results.next() {
1463 let fieldId = results.string(forColumn: "id")!
1464 let currency = results.string(forColumn: "currency")!
1465 let label = results.string(forColumn: "label")!
1466 let type = results.string(forColumn: "type")!
1467
1468 let cf = CustomFieldEntity()
1469 cf.fieldId = fieldId
1470 cf.fieldType = type
1471 cf.currency = currency
1472 cf.label = label
1473
1474 allFields.append(cf)
1475
1476 }
1477
1478 return allFields
1479 } catch {
1480 return allFields
1481 }
1482 } else {
1483 return allFields
1484 }
1485 }
1486
1487 // MARK: - Save map
1488 func saveMap(with data: [String: AnyObject], closure: @escaping (Bool) -> Void) {
1489 if self.openDatabase() {
1490
1491 var mapId = ""
1492 var workspaceId = ""
1493 var fileName = ""
1494 var sizeX = 0
1495 var sizeY = 0
1496
1497 if let tempMapId = data["_id"] as? String {
1498 mapId = tempMapId
1499 }
1500
1501 if let tempFileName = data["fileName"] as? String {
1502 fileName = tempFileName
1503 }
1504
1505 if let tempSizeX = data["sizeX"] as? Int {
1506 sizeX = tempSizeX
1507 }
1508
1509 if let tempSizeY = data["sizeY"] as? Int {
1510 sizeY = tempSizeY
1511 }
1512
1513 if let workspaceRef = data["workspaceRef"] as? [String: AnyObject] {
1514 if let tempWorkspaceId = workspaceRef["id"] as? String {
1515 workspaceId = tempWorkspaceId
1516 }
1517 }
1518
1519 do {
1520 //Remove from site table if exist with same ID
1521 try database.executeUpdate("delete from Maps where id='\(mapId)'", values: [])
1522
1523 //Save site in db
1524 let query = "insert into Maps (id, fileName, workspaceId, sizeX, sizeY) values ('\(mapId)', '\(fileName)', '\(workspaceId)', '\(sizeX)', '\(sizeY)');"
1525 if !database.executeStatements(query) {
1526 closure(false)
1527 } else {
1528 closure(true)
1529 }
1530 } catch {
1531 closure(false)
1532 }
1533
1534 } else {
1535 closure(false)
1536 }
1537 }
1538
1539
1540 // MARK: - Save/get issues
1541 func saveIssues(with data: [[String: AnyObject]], comments: [String: AnyObject], closure: @escaping (Bool) -> Void) {
1542 if self.openDatabase() {
1543 var savedCount = 0
1544
1545 do {
1546 try? database.executeUpdate("ALTER TABLE Issues ADD COLUMN createdBy TEXT", values: nil)
1547 try? database.executeUpdate("ALTER TABLE Issues ADD COLUMN assignees TEXT", values: nil)
1548 }
1549
1550 //Save issues
1551 for item in data {
1552 var description = ""
1553 var createdBy = ""
1554 var sequenceNumber = -1
1555 var priority = ""
1556 var type = ""
1557 var title = ""
1558 var geoLon = 0.0
1559 var geoLat = 0.0
1560 var workspaceId = ""
1561 var id = ""
1562 var pinX = 0.0
1563 var pinY = 0.0
1564 var status = ""
1565 var videos = ""
1566 var images = ""
1567 var documents = ""
1568 var tags = ""
1569 var assignees = ""
1570 var updatedEpochMillis = ""
1571 var createdEpochMillis = ""
1572 var rev = ""
1573
1574
1575 if let tempId = item["_id"] as? String {
1576 id = tempId
1577 }
1578
1579 if let workspaceRef = item["workspaceRef"] as? [String: AnyObject] {
1580 if let tempWorkspaceId = workspaceRef["id"] as? String {
1581 workspaceId = tempWorkspaceId
1582 }
1583 }
1584
1585 do {
1586 try database.executeUpdate("create table if not exists IssueAssignees(id text primary key, caption text, primaryImageId text, type text)", values: nil)
1587
1588 if let assigneeList = item["assignees"] as? [[String: AnyObject]] {
1589
1590 for assignee in assigneeList {
1591 let assigneeId = assignee["id"] as? String ?? ""
1592
1593 do {
1594 try database.executeUpdate("delete from IssueAssignees where id='\(assigneeId)'", values: [])
1595
1596 let caption = assignee["caption"] as? String ?? ""
1597 let primaryImageId = assignee["primaryImageId"] as? String ?? ""
1598 let type = assignee["type"] as? String ?? ""
1599
1600 let query = "insert into IssueAssignees (id, caption, primaryImageId, type) values ('\(assigneeId)', '\(caption)', '\(primaryImageId)', '\(type)');"
1601
1602 do {
1603 try database.executeUpdate(query, values: [])
1604 } catch {
1605 }
1606 } catch {
1607 }
1608 }
1609 }
1610 } catch {
1611 }
1612
1613
1614 do {
1615 try database.executeUpdate("create table if not exists IssuesCustomFields(issueId text, id text, fieldType text, label text, value text, currency text, listLevel text, workspaseId text)", values: nil)
1616
1617 do {
1618 try database.executeUpdate("delete from IssuesCustomFields where issueId='\(id)'", values: [])
1619
1620 if let customFieldList = item["customFieldSimplyList"] as? [[String: AnyObject]] {
1621 for field in customFieldList {
1622 let fieldType = field["type"] as? String ?? ""
1623 let fieldID = field["customFieldTemplateId"] as? String ?? ""
1624 let label = field["label"] as? String ?? ""
1625 let value = field["value"] as? String ?? ""
1626 let currency = field["currency"] as? String ?? ""
1627 let listLevel = field["listLevel"] as? String ?? ""
1628
1629 let query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(id)', '\(fieldID)', '\(fieldType)', ?, ?, '\(currency)', '\(listLevel)', '\(workspaceId)');"
1630
1631 do {
1632 try database.executeUpdate(query, values: [label, value])
1633 } catch {
1634 }
1635 }
1636 }
1637 } catch {
1638 }
1639 } catch {
1640 print("failed: \(error.localizedDescription)")
1641 }
1642
1643
1644 if let header = item["header"] as? [String: AnyObject] {
1645 if let tempCreatedBy = header["createdBy"] as? [String: AnyObject] {
1646 if let tempCaption = tempCreatedBy["id"] as? String {
1647 createdBy = "\(tempCaption)"
1648 }
1649 }
1650 if let tempUpdatedEpochMillis = header["updatedEpochMillis"] as? Int64 {
1651 updatedEpochMillis = "\(tempUpdatedEpochMillis)"
1652 }
1653 if let tempCreatedEpochMillis = header["createdEpochMillis"] as? Int64 {
1654 createdEpochMillis = "\(tempCreatedEpochMillis)"
1655 }
1656 }
1657
1658 if let tempDescription = item["description"] as? String {
1659 description = tempDescription
1660 }
1661
1662 if let tempRev = item["_rev"] as? String {
1663 rev = tempRev
1664 }
1665
1666 if let tempSequenceNumber = item["sequenceNumber"] as? Int {
1667 sequenceNumber = tempSequenceNumber
1668 }
1669
1670 if let tempPriority = item["priority"] as? String {
1671 priority = tempPriority
1672 }
1673
1674 if let tempType = item["type"] as? String {
1675 type = tempType
1676 }
1677
1678 if let tempTitle = item["title"] as? String {
1679 title = tempTitle
1680 }
1681
1682 if let geo = item["geo"] as? [String: AnyObject] {
1683 if let tempGeoLon = geo["lon"] as? Double {
1684 geoLon = tempGeoLon
1685 }
1686 if let tempGeoLat = geo["lat"] as? Double {
1687 geoLat = tempGeoLat
1688 }
1689 }
1690
1691 if let pin = item["pin"] as? [String: AnyObject] {
1692 if let tempPinX = pin["x"] as? Double {
1693 pinX = tempPinX
1694 }
1695 if let tempPinY = pin["y"] as? Double {
1696 pinY = tempPinY
1697 }
1698 }
1699
1700 if let tempStatus = item["status"] as? String {
1701 status = tempStatus
1702 }
1703
1704 if let tempAssignees = item["assignees"] as? [String] {
1705 for (index, assigneeItem) in tempAssignees.enumerated() {
1706 if index == tempAssignees.count - 1 {
1707 assignees = "\(assignees)\(assigneeItem)"
1708 } else {
1709 assignees = "\(assignees)\(assigneeItem),"
1710 }
1711 }
1712 }
1713
1714 if let tempTags = item["tags"] as? [String] {
1715 for (index, tagItem) in tempTags.enumerated() {
1716 if index == tempTags.count - 1 {
1717 tags = "\(tags)\(tagItem)"
1718 } else {
1719 tags = "\(tags)\(tagItem),"
1720 }
1721 }
1722 }
1723
1724 if let tempVideos = item["videos"] as? [[String: AnyObject]] {
1725 for (index, videoItem) in tempVideos.enumerated() {
1726 if let tempVideoId = videoItem["id"] as? String {
1727 if index == tempVideos.count - 1 {
1728 videos = "\(videos)\(tempVideoId)"
1729 } else {
1730 videos = "\(videos)\(tempVideoId),"
1731 }
1732 }
1733 }
1734
1735 if tempVideos.count > 0 {
1736 //Remove from UnSavedIssueVideos table if exist with same ID
1737 do {
1738 try database.executeUpdate("delete from UnSavedIssueVideos where issueId='\(id)'", values: [])
1739 let query = "insert into UnSavedIssueVideos (issueId, workspaceId) values ('\(id)', '\(workspaceId)');"
1740
1741 if !database.executeStatements(query) {
1742 } else {
1743 }
1744 } catch {
1745 }
1746 }
1747 }
1748
1749 if let tempImages = item["images"] as? [[String: AnyObject]] {
1750 for (index, imageItem) in tempImages.enumerated() {
1751 if let tempImageId = imageItem["id"] as? String {
1752 if index == tempImages.count - 1 {
1753 images = "\(images)\(tempImageId)"
1754 } else {
1755 images = "\(images)\(tempImageId),"
1756 }
1757
1758 if !self.isImageInfoAvailable(imageID: tempImageId) {
1759 //Remove from UnSavedIssueImages table if exist with same ID
1760 do {
1761 try database.executeUpdate("delete from UnSavedIssueImages where imageId='\(tempImageId)'", values: [])
1762 let query = "insert into UnSavedIssueImages (issueId, imageId, workspaceId) values ('\(id)', '\(tempImageId)', '\(workspaceId)');"
1763
1764 if !database.executeStatements(query) {
1765 } else {
1766 }
1767 } catch {
1768 }
1769 }
1770 }
1771 }
1772 }
1773
1774 if let tempDocuments = item["documents"] as? [[String: AnyObject]] {
1775 for (index, documentItem) in tempDocuments.enumerated() {
1776 if let tempDocumentId = documentItem["id"] as? String {
1777 if index == tempDocuments.count - 1 {
1778 documents = "\(documents)\(tempDocumentId)"
1779 } else {
1780 documents = "\(documents)\(tempDocumentId),"
1781 }
1782 }
1783 }
1784
1785 if tempDocuments.count > 0 {
1786 //Remove from UnSavedIssueDocuments table if exist with same ID
1787 do {
1788 try database.executeUpdate("delete from UnSavedIssueDocuments where issueId='\(id)'", values: [])
1789 let query = "insert into UnSavedIssueDocuments (issueId, workspaceId) values ('\(id)', '\(workspaceId)');"
1790
1791 if !database.executeStatements(query) {
1792 } else {
1793 }
1794 } catch {
1795 }
1796 }
1797 }
1798
1799 //Remove from Issues table if exist with same ID
1800 do {
1801 try database.executeUpdate("delete from Issues where id='\(id)'", values: [])
1802
1803 let query = "insert into Issues (description, createdBy, assignees, sequenceNumber, priority, type, title, geoLon, geoLat, workspaceId, id, pinX, pinY, status, documents, images, videos, tags, updatedEpochMillis, createdEpochMillis, editingType, rev) values (?, '\(createdBy)', '\(assignees)', '\(sequenceNumber)', '\(priority)', '\(type)', ?, '\(geoLon)', '\(geoLat)', '\(workspaceId)', '\(id)', '\(pinX)', '\(pinY)', '\(status)', '\(documents)', '\(images)', '\(videos)', ?, '\(updatedEpochMillis)', '\(createdEpochMillis)', '-1', '\(rev)');"
1804
1805 do {
1806 try database.executeUpdate(query, values: [description, title, tags])
1807 savedCount += 1
1808 } catch {
1809
1810 }
1811 } catch {
1812 }
1813 }
1814
1815 for key in comments.keys {
1816 if let commentsForIssue = comments[key] as? [[String: AnyObject]] {
1817 for comment in commentsForIssue {
1818 self.saveIssueComments(with: comment, closure: { success in
1819
1820 })
1821 }
1822 }
1823 }
1824
1825 if savedCount == data.count {
1826 closure(true)
1827 } else {
1828 closure(false)
1829 }
1830
1831 } else {
1832 closure(false)
1833 }
1834 }
1835
1836 func isNeedToDownloadImagesForIssue(issueId: String) -> Bool {
1837 if self.openDatabase() {
1838 let query = "select * from UnSavedIssueImages where issueId='\(issueId)'"
1839
1840 do {
1841 let results = try database.executeQuery(query, values: nil)
1842
1843 while results.next() {
1844 return true
1845 }
1846
1847 return false
1848 } catch {
1849 return false
1850 }
1851 }
1852 return false
1853 }
1854
1855 func getIssues(for workspaceId: String) -> [IssueEntity] {
1856 var issues: [IssueEntity] = [IssueEntity]()
1857
1858 if self.openDatabase() {
1859 let query = "select * from Issues where workspaceId='\(workspaceId)'"
1860
1861 do {
1862 let results = try database.executeQuery(query, values: nil)
1863
1864 while results.next() {
1865 let description = results.string(forColumn: "description")
1866 let sequenceNumber = results.int(forColumn: "sequenceNumber")
1867 let priority = results.string(forColumn: "priority")
1868 let type = results.string(forColumn: "type")
1869 let title = results.string(forColumn: "title")
1870 let geoLon = results.double(forColumn: "geoLon")
1871 let geoLat = results.double(forColumn: "geoLat")
1872 let workspaceId = results.string(forColumn: "workspaceId")
1873 let id = results.string(forColumn: "id")
1874 let pinX = results.double(forColumn: "pinX")
1875 let pinY = results.double(forColumn: "pinY")
1876 let status = results.string(forColumn: "status")
1877 let updatedEpochMillis = results.string(forColumn: "updatedEpochMillis")
1878 let createdEpochMillis = results.string(forColumn: "createdEpochMillis")
1879 let editingType = results.string(forColumn: "editingType")
1880 let rev = results.string(forColumn: "rev")
1881 let tags = results.string(forColumn: "tags")
1882 let createdById = results.string(forColumn: "createdBy") ?? ""
1883 let assignees = results.string(forColumn: "assignees") ?? ""
1884
1885 var createdBy = "";
1886 do {
1887 let user = try database.executeQuery("SELECT * FROM IssueAssignees WHERE id='\(createdById)'", values: nil)
1888 createdBy = user.string(forColumn: "caption") ?? ""
1889 }
1890
1891 // TODO: Need to get videos/images/documents
1892
1893 issues.append(IssueEntity(assignees: assignees, tags: tags!, description: description!, createdBy: createdBy, sequenceNumber: Int(sequenceNumber), priority: priority!, type: type!, title: title!, geoLon: geoLon, geoLat: geoLat, workspaceId: workspaceId!, id: id!, pinX: pinX, pinY: pinY, status: status!, updatedEpochMillis: updatedEpochMillis!, createdEpochMillis: createdEpochMillis!, editingType: editingType!, rev: rev!))
1894 }
1895
1896 return issues
1897 } catch {
1898 return issues
1899 }
1900 } else {
1901 return issues
1902 }
1903 }
1904
1905
1906 // self.needToDeleteDocuments = [DocumentEntity]()
1907 // self.needToDeleteImages = [ImageEntity]()
1908 // self.needToDeleteVideos = [VideoEntity]()
1909 // self.needToAddImages = [ImageEntity]()
1910 // self.needToAddVideos = [VideoEntity]()
1911 // self.needToAddComments = [CommentEntity]()
1912
1913 func getModifiedIssue(with issueId: String) {
1914 if self.openDatabase() {
1915 let query = "select * from ModifiedIssues where id='\(issueId)'"
1916
1917 do {
1918 let results = try database.executeQuery(query, values: nil)
1919
1920 while results.next() {
1921 let issue = self.getDetailIssue(with: issueId)
1922 ModifiedIssueEntity.shared.didInitAllFields(from: issue!)
1923 ModifiedIssueEntity.shared.editingType = results.string(forColumn: "editingType")
1924
1925
1926
1927 ModifiedIssueEntity.shared.title = results.string(forColumn: "title")
1928 ModifiedIssueEntity.shared.issueDescription = results.string(forColumn: "description")
1929 ModifiedIssueEntity.shared.createdBy = results.string(forColumn: "createdBy") ?? ""
1930 ModifiedIssueEntity.shared.pinX = results.double(forColumn: "pinX")
1931 ModifiedIssueEntity.shared.pinY = results.double(forColumn: "pinY")
1932
1933 ModifiedIssueEntity.shared.status = results.string(forColumn: "status")
1934 ModifiedIssueEntity.shared.priority = results.string(forColumn: "priority")
1935
1936 let needToDeleteDocumentsStr = results.string(forColumn: "needToDeleteDocuments")
1937 let needToDeleteImagesStr = results.string(forColumn: "needToDeleteImages")
1938 let needToDeleteVideosStr = results.string(forColumn: "needToDeleteVideos")
1939 let needToAddImagesStr = results.string(forColumn: "needToAddImages")
1940 let needToAddVideosStr = results.string(forColumn: "needToAddVideos")
1941 let needToAddCommentsStr = results.string(forColumn: "needToAddComments")
1942
1943 let addedImagesStr = results.string(forColumn: "addedImages")
1944 let addedVideosStr = results.string(forColumn: "addedVideos")
1945
1946 let addedImagesArr = addedImagesStr?.components(separatedBy: ",")
1947 for addedImageStr in addedImagesArr! {
1948 ModifiedIssueEntity.shared.addedImages.append(addedImageStr)
1949 }
1950
1951 let addedVideosArr = addedVideosStr?.components(separatedBy: ",")
1952 for addedVideoStr in addedVideosArr! {
1953 ModifiedIssueEntity.shared.addedVideos.append(addedVideoStr)
1954 }
1955
1956 let documentsArr = needToDeleteDocumentsStr?.components(separatedBy: ",")
1957 for documentStr in documentsArr! {
1958 let documentsQuery = "select * from IssueDocuments where documentId='\(documentStr)'"
1959 do {
1960 let documentsResults = try database.executeQuery(documentsQuery, values: nil)
1961 while documentsResults.next() {
1962 ModifiedIssueEntity.shared.needToDeleteDocuments.append(DocumentEntity(documentId: documentsResults.string(forColumn: "documentId"), fileName: documentsResults.string(forColumn: "fileName"), type: documentsResults.string(forColumn: "type"), workspaceId: documentsResults.string(forColumn: "workspaceId"), publicUrl: documentsResults.string(forColumn: "publicUrl"), editingType: documentsResults.string(forColumn: "editingType")))
1963 }
1964 } catch {
1965 }
1966 }
1967
1968 let needToAddCommentsArr = needToAddCommentsStr?.components(separatedBy: ",")
1969 for needToAddComment in needToAddCommentsArr! {
1970 let commentsQuery = "select * from IssueComments where commentId='\(needToAddComment)'"
1971 do {
1972 let commentsResults = try database.executeQuery(commentsQuery, values: nil)
1973 while commentsResults.next() {
1974 ModifiedIssueEntity.shared.needToAddComments.append(CommentEntity(commentId: commentsResults.string(forColumn: "commentId"), text: commentsResults.string(forColumn: "text"), authorName: commentsResults.string(forColumn: "authorName"), updatedEpochMillis: commentsResults.string(forColumn: "updatedEpochMillis"), workspaceId: commentsResults.string(forColumn: "workspaceId"), issueId: commentsResults.string(forColumn: "issueId"), editingType: commentsResults.string(forColumn: "editingType")))
1975 }
1976 } catch {
1977 }
1978 }
1979
1980 var videosArr = needToDeleteVideosStr?.components(separatedBy: ",")
1981 for videoStr in videosArr! {
1982 let videosQuery = "select * from IssueVideos where videoId='\(videoStr)'"
1983 do {
1984 let videosResults = try database.executeQuery(videosQuery, values: nil)
1985 while videosResults.next() {
1986
1987 let filePath = videosResults.string(forColumn: "filePath")
1988 var url: URL? = nil
1989 if filePath != "" {
1990 url = URL(fileURLWithPath: filePath!)
1991 }
1992
1993
1994 ModifiedIssueEntity.shared.needToDeleteVideos.append(VideoEntity(videoId: videosResults.string(forColumn: "videoId"), fileName: videosResults.string(forColumn: "fileName"), type: videosResults.string(forColumn: "type"), workspaceId: videosResults.string(forColumn: "workspaceId"), key: videosResults.string(forColumn: "key"), filePath: url, editingType: videosResults.string(forColumn: "editingType")))
1995 }
1996 } catch {
1997 }
1998 }
1999
2000 videosArr = needToAddVideosStr?.components(separatedBy: ",")
2001 for videoStr in videosArr! {
2002 let videosQuery = "select * from IssueVideos where videoId='\(videoStr)'"
2003 do {
2004 let videosResults = try database.executeQuery(videosQuery, values: nil)
2005 while videosResults.next() {
2006
2007 let filePath = videosResults.string(forColumn: "filePath")
2008 var url: URL? = nil
2009 if filePath != "" {
2010 url = URL(fileURLWithPath: filePath!)
2011 }
2012
2013
2014 ModifiedIssueEntity.shared.needToAddVideos.append(VideoEntity(videoId: videosResults.string(forColumn: "videoId"), fileName: videosResults.string(forColumn: "fileName"), type: videosResults.string(forColumn: "type"), workspaceId: videosResults.string(forColumn: "workspaceId"), key: videosResults.string(forColumn: "key"), filePath: url, editingType: videosResults.string(forColumn: "editingType")))
2015 }
2016 } catch {
2017 }
2018 }
2019
2020 var imagesArr = needToAddImagesStr?.components(separatedBy: ",")
2021 for imageStr in imagesArr! {
2022 if imageStr.count > 0 {
2023
2024
2025 let imagesQuery = "select * from IssueImages where imageId='\(imageStr)'"
2026 do {
2027 let imagesResults = try database.executeQuery(imagesQuery, values: nil)
2028 while imagesResults.next() {
2029 let imageId = imagesResults.string(forColumn: "imageId")
2030 let editingType = imagesResults.string(forColumn: "editingType")
2031 let filePath = imagesResults.string(forColumn: "filePath")
2032 var url: URL? = nil
2033
2034 if filePath == "" {
2035 url = self.getFileURL(with: "images/issues/\(SyncDataService.ImageSize.square200.rawValue)_\(imageId!).png")
2036 } else {
2037 url = URL(fileURLWithPath: filePath!)
2038 }
2039 ModifiedIssueEntity.shared.needToAddImages.append(ImageEntity(imageId: imageId!, filePath: url, editingType: editingType!, filePathSmall: nil))
2040 }
2041 } catch {
2042 }
2043 }
2044 }
2045
2046 imagesArr = needToDeleteImagesStr?.components(separatedBy: ",")
2047 for imageStr in imagesArr! {
2048 if imageStr.count > 0 {
2049
2050
2051 let imagesQuery = "select * from IssueImages where imageId='\(imageStr)'"
2052 do {
2053 let imagesResults = try database.executeQuery(imagesQuery, values: nil)
2054 while imagesResults.next() {
2055 let imageId = imagesResults.string(forColumn: "imageId")
2056 let editingType = imagesResults.string(forColumn: "editingType")
2057 let filePath = imagesResults.string(forColumn: "filePath")
2058 var url: URL? = nil
2059
2060 if filePath == "" {
2061 url = self.getFileURL(with: "images/issues/\(SyncDataService.ImageSize.square200.rawValue)_\(imageId!).png")
2062 } else {
2063 url = URL(fileURLWithPath: filePath!)
2064 }
2065 ModifiedIssueEntity.shared.needToDeleteImages.append(ImageEntity(imageId: imageId!, filePath: url, editingType: editingType!, filePathSmall: nil))
2066 }
2067 } catch {
2068 }
2069 }
2070 }
2071 }
2072 } catch {
2073
2074 }
2075 }
2076 }
2077
2078 func getDetailIssue(with issueId: String) -> DetailIssueEntity? {
2079 if self.openDatabase() {
2080 let query = "select * from Issues where id='\(issueId)'"
2081
2082 do {
2083 let results = try database.executeQuery(query, values: [])
2084
2085 while results.next() {
2086 let description = results.string(forColumn: "description")
2087 let sequenceNumber = results.int(forColumn: "sequenceNumber")
2088 let priority = results.string(forColumn: "priority")
2089 let type = results.string(forColumn: "type")
2090 let title = results.string(forColumn: "title")
2091 let geoLon = results.double(forColumn: "geoLon")
2092 let geoLat = results.double(forColumn: "geoLat")
2093 let workspaceId = results.string(forColumn: "workspaceId")
2094 let id = results.string(forColumn: "id")
2095 let pinX = results.double(forColumn: "pinX")
2096 let pinY = results.double(forColumn: "pinY")
2097 let status = results.string(forColumn: "status")
2098 let assigneesStr = results.string(forColumn: "assignees")
2099 let tagsStr = results.string(forColumn: "tags")
2100 let videosStr = results.string(forColumn: "videos")
2101 let documentsStr = results.string(forColumn: "documents")
2102 let imagesStr = results.string(forColumn: "images")
2103 let updatedEpochMillis = results.string(forColumn: "updatedEpochMillis")
2104 let createdEpochMillis = results.string(forColumn: "createdEpochMillis")
2105 let editingType = results.string(forColumn: "editingType")
2106 let rev = results.string(forColumn: "rev")
2107 let createdById = results.string(forColumn: "createdBy") ?? ""
2108
2109 var assignees: [AssigneeEntity] = [AssigneeEntity]()
2110 var tags: [TagEntity] = [TagEntity]()
2111 var videos: [VideoEntity] = [VideoEntity]()
2112 var images: [ImageEntity] = [ImageEntity]()
2113 var comments: [CommentEntity] = [CommentEntity]()
2114 var documents: [DocumentEntity] = [DocumentEntity]()
2115 var customFields: [CustomFieldEntity] = [CustomFieldEntity]()
2116
2117 //Get custom fields
2118 let customFieldsQuery = "select * from IssuesCustomFields where issueId='\(id!)'"
2119 do {
2120 let customFieldsResults = try database.executeQuery(customFieldsQuery, values: nil)
2121 while customFieldsResults.next() {
2122
2123 let fieldType = customFieldsResults.string(forColumn: "fieldType")!
2124 let fieldId = customFieldsResults.string(forColumn: "id")!
2125 let currency = customFieldsResults.string(forColumn: "currency")!
2126 let label = customFieldsResults.string(forColumn: "label")!
2127 let type = customFieldsResults.string(forColumn: "fieldType")!
2128 let value = customFieldsResults.string(forColumn: "value")!
2129 let listLevel = customFieldsResults.string(forColumn: "listLevel")!
2130
2131 switch fieldType {
2132 case CustomFieldsType.cost.rawValue:
2133 customFields.append(FieldCostEntity(currency: currency, label: label, type: type, value: value, fieldId: fieldId, fieldType: fieldType))
2134 break
2135
2136 case CustomFieldsType.text.rawValue:
2137 customFields.append(FieldTextEntity(label: label, type: type, value: value, fieldId: fieldId, fieldType: fieldType))
2138 break
2139
2140 case CustomFieldsType.date.rawValue:
2141 customFields.append(FieldDateEntity(label: label, type: type, value: value, fieldId: fieldId, fieldType: fieldType))
2142 break
2143
2144 case CustomFieldsType.list.rawValue:
2145 customFields.append(FieldListEntity(label: label, listLevel: listLevel, type: type, value: value, fieldId: fieldId, fieldType: fieldType, selectedID: listLevel))
2146 break
2147
2148 default:
2149 break
2150 }
2151 }
2152 } catch {
2153 }
2154
2155 var createdBy = "";
2156 do {
2157 let user = try database.executeQuery("select * from IssueAssignees WHERE id='\(createdById)'", values: nil)
2158 while user.next() {
2159 createdBy = user.string(forColumn: "caption")
2160 }
2161 }
2162
2163 if let assigneesArr = assigneesStr?.components(separatedBy: ",") {
2164 for assigneeStr in assigneesArr {
2165 if assigneeStr.count > 0 {
2166 let assigneesQuery = "select * from IssueAssignees where id='\(assigneeStr)'"
2167 do {
2168 let assigneesResults = try database.executeQuery(assigneesQuery, values: nil)
2169 while assigneesResults.next() {
2170 let id = assigneesResults.string(forColumn: "id")
2171 let caption = assigneesResults.string(forColumn: "caption")
2172 let primaryImageId = assigneesResults.string(forColumn: "primaryImageId")
2173 let type = assigneesResults.string(forColumn: "type")
2174
2175 assignees.append(AssigneeEntity(id: id!, caption: caption!, primaryImageId: primaryImageId!, type: type!))
2176 }
2177 } catch {
2178 }
2179 }
2180 }
2181 }
2182 let tagsArr = tagsStr?.components(separatedBy: ",")
2183 for tagStr in tagsArr! {
2184 if tagStr.count > 0 {
2185 tags.append(TagEntity(text: tagStr))
2186 }
2187
2188 }
2189
2190 //TODO: - EditingType
2191 let imagesArr = imagesStr?.components(separatedBy: ",")
2192 for imageStr in imagesArr! {
2193 if imageStr.count > 0 {
2194
2195 let imagesQuery = "select * from IssueImages where imageId='\(imageStr)'"
2196 do {
2197 let imagesResults = try database.executeQuery(imagesQuery, values: nil)
2198 while imagesResults.next() {
2199 let imageId = imagesResults.string(forColumn: "imageId")
2200 let editingType = imagesResults.string(forColumn: "editingType")
2201 let filePath = imagesResults.string(forColumn: "filePath")
2202 var url: URL? = nil
2203
2204 if filePath == "" {
2205 url = self.getFileURL(with: "images/issues/\(SyncDataService.ImageSize.square200.rawValue)_\(imageId!).png")
2206 } else {
2207 url = URL(fileURLWithPath: filePath!)
2208 }
2209 images.append(ImageEntity(imageId: imageId!, filePath: url, editingType: editingType!, filePathSmall: nil))
2210 }
2211 } catch {
2212 }
2213 }
2214 }
2215
2216 var site = SiteEntity()
2217 let siteQuery = "select * from Sites where workspaceId='\(workspaceId!)'"
2218 do {
2219 let siteResults = try database.executeQuery(siteQuery, values: nil)
2220 while siteResults.next() {
2221 site = SiteEntity(id: siteResults.string(forColumn: "id"), workspaceId: siteResults.string(forColumn: "workspaceId"), name: siteResults.string(forColumn: "name"), tags: siteResults.string(forColumn: "tags"), imageId: siteResults.string(forColumn: "imageId"))
2222 }
2223 } catch {
2224 }
2225
2226 let commentsQuery = "select * from IssueComments where issueId='\(issueId)'"
2227 do {
2228 let commentsResults = try database.executeQuery(commentsQuery, values: nil)
2229 while commentsResults.next() {
2230 comments.append(CommentEntity(commentId: commentsResults.string(forColumn: "commentId"), text: commentsResults.string(forColumn: "text"), authorName: commentsResults.string(forColumn: "authorName"), updatedEpochMillis: commentsResults.string(forColumn: "updatedEpochMillis"), workspaceId: commentsResults.string(forColumn: "workspaceId"), issueId: commentsResults.string(forColumn: "issueId"), editingType: commentsResults.string(forColumn: "editingType")))
2231 }
2232 } catch {
2233 }
2234
2235 let videosArr = videosStr?.components(separatedBy: ",")
2236 for videoStr in videosArr! {
2237 let videosQuery = "select * from IssueVideos where videoId='\(videoStr)'"
2238 do {
2239 let videosResults = try database.executeQuery(videosQuery, values: nil)
2240 while videosResults.next() {
2241
2242 let filePath = videosResults.string(forColumn: "filePath")
2243 var url: URL? = nil
2244 if filePath != "" {
2245 url = URL(fileURLWithPath: filePath!)
2246 }
2247
2248
2249 videos.append(VideoEntity(videoId: videosResults.string(forColumn: "videoId"), fileName: videosResults.string(forColumn: "fileName"), type: videosResults.string(forColumn: "type"), workspaceId: videosResults.string(forColumn: "workspaceId"), key: videosResults.string(forColumn: "key"), filePath: url, editingType: videosResults.string(forColumn: "editingType")))
2250 }
2251 } catch {
2252 }
2253 }
2254
2255 let documentsArr = documentsStr?.components(separatedBy: ",")
2256 for documentStr in documentsArr! {
2257 let documentsQuery = "select * from IssueDocuments where documentId='\(documentStr)'"
2258 do {
2259 let documentsResults = try database.executeQuery(documentsQuery, values: nil)
2260 while documentsResults.next() {
2261 documents.append(DocumentEntity(documentId: documentsResults.string(forColumn: "documentId"), fileName: documentsResults.string(forColumn: "fileName"), type: documentsResults.string(forColumn: "type"), workspaceId: documentsResults.string(forColumn: "workspaceId"), publicUrl: documentsResults.string(forColumn: "publicUrl"), editingType: documentsResults.string(forColumn: "editingType")))
2262 }
2263 } catch {
2264 }
2265 }
2266
2267 return DetailIssueEntity(description: description!, createdBy: createdBy, sequenceNumber: Int(sequenceNumber), priority: priority!, type: type!, title: title!, geoLon: geoLon, geoLat: geoLat, workspaceId: workspaceId!, id: id!, pinX: pinX, pinY: pinY, status: status!, site: site, assignees: assignees, tags: tags, documents: documents, comments: comments, images: images, videos: videos, updatedEpochMillis: updatedEpochMillis!, createdEpochMillis: createdEpochMillis!, editingType: editingType!, rev: rev!, customFields: customFields)
2268
2269 }
2270 return nil
2271 } catch {
2272 return nil
2273 }
2274 } else {
2275 return nil
2276 }
2277 }
2278
2279 func getFileURL(with prefix: String) -> URL {
2280 let documentsURL = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)[0]
2281 let fileURL = documentsURL.appendingPathComponent("\(prefix)")
2282
2283 return fileURL
2284 }
2285
2286
2287 // MARK: - Get unsaved elements for workspace
2288 func getUnSavedIssueImages(for workspaceId: String) -> [String] {
2289 var imagesIds: [String] = [String]()
2290 if self.openDatabase() {
2291 let query = "select * from UnSavedIssueImages where workspaceId='\(workspaceId)'"
2292
2293 do {
2294 let results = try database.executeQuery(query, values: nil)
2295
2296 while results.next() {
2297 imagesIds.append(results.string(forColumn: "imageId"))
2298 }
2299 } catch {
2300 }
2301 }
2302 return imagesIds
2303 }
2304
2305 func getUnsavedVideosCountForIssue(issueID: String) -> Int {
2306 var videoCount: Int = 0
2307 if self.openDatabase() {
2308 let query = "select count(*) as count from UnSavedIssueVideos where issueId='\(issueID)'"
2309 do {
2310 let results = try database.executeQuery(query, values: nil)
2311
2312 while results.next() {
2313 videoCount = Int(results.int(forColumn: "count"))
2314 }
2315 } catch {
2316 }
2317 }
2318 return videoCount
2319 }
2320
2321 func getUnSavedVideos(for workspaceId: String) -> [String] {
2322 var issuesIds: [String] = [String]()
2323 if self.openDatabase() {
2324 let query = "select * from UnSavedIssueVideos where workspaceId='\(workspaceId)'"
2325
2326 do {
2327 let results = try database.executeQuery(query, values: nil)
2328
2329 while results.next() {
2330 issuesIds.append(results.string(forColumn: "issueId"))
2331 }
2332 } catch {
2333 }
2334 }
2335 return issuesIds
2336 }
2337
2338 func getUnsavedDocumentsCountForIssue(issueID: String) -> Int {
2339 var documentsCount: Int = 0
2340 if self.openDatabase() {
2341 let query = "select count(*) as count from UnSavedIssueDocuments where issueId='\(issueID)'"
2342 do {
2343 let results = try database.executeQuery(query, values: nil)
2344
2345 while results.next() {
2346 documentsCount = Int(results.int(forColumn: "count"))
2347 }
2348 } catch {
2349 }
2350 }
2351 return documentsCount
2352 }
2353
2354 func getUnSavedDocuments(for workspaceId: String) -> [String] {
2355 var issuesIds: [String] = [String]()
2356 if self.openDatabase() {
2357 let query = "select * from UnSavedIssueDocuments where workspaceId='\(workspaceId)'"
2358
2359 do {
2360 let results = try database.executeQuery(query, values: nil)
2361
2362 while results.next() {
2363 issuesIds.append(results.string(forColumn: "issueId"))
2364 }
2365 } catch {
2366 }
2367 }
2368 return issuesIds
2369 }
2370
2371 func getUnSavedComments(for workspaceId: String) -> [String] {
2372 var issuesIds: [String] = [String]()
2373 if self.openDatabase() {
2374 let query = "select * from UnSavedIssueComments where workspaceId='\(workspaceId)'"
2375
2376 do {
2377 let results = try database.executeQuery(query, values: nil)
2378
2379 while results.next() {
2380 issuesIds.append(results.string(forColumn: "issueId"))
2381 }
2382 } catch {
2383 }
2384 }
2385 return issuesIds
2386 }
2387
2388
2389 // MARK: - Save issue images info
2390 func saveImage(imageId: String, imagePath: String, imageSize: String, closure: @escaping (Bool) -> Void) {
2391 if self.openDatabase() {
2392 do {
2393 try database.executeUpdate("create table if not exists DownloadedImages(imageId text, imagePath text, imageSize text)", values: nil)
2394 try database.executeUpdate("delete from DownloadedImages where imageId='\(imageId)'", values: [])
2395 let query = "insert into DownloadedImages (imageId, imagePath, imageSize) values ('\(imageId)', '\(imagePath)', '\(imageSize)');"
2396 if !database.executeStatements(query) {
2397 closure(false)
2398 } else {
2399 closure(true)
2400 }
2401 } catch {
2402 closure(false)
2403 }
2404 }
2405 closure(false)
2406 }
2407
2408 func isImageAlreadyDownloaded(imageId: String) -> String {
2409 if self.openDatabase() {
2410 do {
2411 try database.executeUpdate("create table if not exists DownloadedImages(imageId text, imagePath text, imageSize text)", values: nil)
2412
2413 let query = "select * from DownloadedImages where imageId='\(imageId)'"
2414 let results = try database.executeQuery(query, values: nil)
2415
2416 while results.next() {
2417 return results.string(forColumn: "imagePath")
2418 }
2419
2420 return ""
2421 } catch {
2422 return ""
2423 }
2424 }
2425 return ""
2426 }
2427
2428 func saveIssueImage(with data: [String: AnyObject], closure: @escaping (Bool, String) -> Void) {
2429 if self.openDatabase() {
2430 let imageId = data["id"]
2431 let workspaceId = data["workspaceId"]
2432 let editingType = data["editingType"]
2433 let filePath = data["filePath"]
2434
2435 do {
2436 try database.executeUpdate("delete from IssueImages where imageId='\(imageId!)'", values: [])
2437 let query = "insert into IssueImages (imageId, workspaceId, editingType, filePath) values ('\(imageId!)', '\(workspaceId!)', '\(editingType!)', '\(filePath!)');"
2438
2439 if !database.executeStatements(query) {
2440 closure(false, "")
2441 } else {
2442 closure(true, imageId as! String)
2443 }
2444 } catch {
2445 closure(false, "")
2446 }
2447
2448
2449
2450 } else {
2451 closure(false, "")
2452 }
2453 }
2454
2455 func isImageInfoAvailable(imageID: String) -> Bool {
2456 if self.openDatabase() {
2457 let query = "select * from IssueImages where imageId='\(imageID)'"
2458
2459 do {
2460 let results = try database.executeQuery(query, values: nil)
2461
2462 while results.next() {
2463 return true
2464 }
2465
2466 return false
2467 } catch {
2468 return false
2469 }
2470 }
2471 return false
2472 }
2473
2474 // MARK: - Save issue videos info
2475 func saveIssueVideo(with data: [String: AnyObject], closure: @escaping (Bool, String, String) -> Void) {
2476 if self.openDatabase() {
2477 var videoId = ""
2478 var fileName = ""
2479 var type = ""
2480 var workspaceId = ""
2481 var key = ""
2482
2483
2484 if let tempVideoId = data["_id"] as? String {
2485 videoId = tempVideoId
2486 }
2487
2488 if let tempFileName = data["fileName"] as? String {
2489 fileName = tempFileName
2490 }
2491
2492 if let tempType = data["type"] as? String {
2493 type = tempType
2494 }
2495
2496 if let workspaceRef = data["workspaceRef"] as? [String: AnyObject] {
2497 if let tempWorkspaceId = workspaceRef["id"] as? String {
2498 workspaceId = tempWorkspaceId
2499 }
2500 }
2501
2502 if let blobs = data["blobs"] as? [[String: AnyObject]] {
2503 if let metaData = blobs[0]["metaData"] as? [String: AnyObject] {
2504 if let tempKey = metaData["key"] as? String {
2505 key = tempKey
2506 }
2507 }
2508 }
2509
2510 //Remove from IssueVideos table if exist with same ID
2511 do {
2512 try database.executeUpdate("delete from IssueVideos where videoId='\(videoId)'", values: [])
2513 let query = "insert into IssueVideos (videoId, fileName, type, workspaceId, key, editingType, filePath) values ('\(videoId)', '\(fileName)', '\(type)', '\(workspaceId)', '\(key)', '-1', '');"
2514
2515
2516
2517 if !database.executeStatements(query) {
2518 closure(false, "", "")
2519 } else {
2520 closure(true, videoId, fileName)
2521 }
2522 } catch {
2523 closure(false, "", "")
2524 }
2525
2526
2527 } else {
2528 closure(false, "", "")
2529 }
2530 }
2531
2532 // MARK: - Save issue comments info
2533 func saveIssueComments(with data: [String: AnyObject], closure: @escaping (Bool) -> Void) {
2534 if self.openDatabase() {
2535 var commentId = ""
2536 var text = ""
2537 var authorName = ""
2538 var updatedEpochMillis = ""
2539 var workspaceId = ""
2540 var issueId = ""
2541
2542 if let tempCommentId = data["_id"] as? String {
2543 commentId = tempCommentId
2544 }
2545
2546 if let tempText = data["comment"] as? String {
2547 text = tempText
2548 }
2549
2550 if let workspaceRef = data["workspaceRef"] as? [String: AnyObject] {
2551 if let tempWorkspaceId = workspaceRef["id"] as? String {
2552 workspaceId = tempWorkspaceId
2553 }
2554 }
2555
2556 if let header = data["header"] as? [String: AnyObject] {
2557 if let tempUpdatedEpochMillis = header["updatedEpochMillis"] as? Int64 {
2558 updatedEpochMillis = "\(tempUpdatedEpochMillis)"
2559 }
2560
2561 if let updatedBy = header["updatedBy"] as? [String: AnyObject] {
2562 if let tempAuthorName = updatedBy["caption"] as? String {
2563 authorName = tempAuthorName
2564 }
2565 }
2566 }
2567
2568 if let defectRef = data["defectRef"] as? [String: AnyObject] {
2569 if let tempIssueId = defectRef["id"] as? String {
2570 issueId = tempIssueId
2571 }
2572 }
2573
2574 //Remove from IssueComments table if exist with same ID
2575 do {
2576 try database.executeUpdate("delete from IssueComments where commentId='\(commentId)'", values: [])
2577 let query = "insert into IssueComments (commentId, text, authorName, updatedEpochMillis, workspaceId, issueId, editingType) values ('\(commentId)', ?, ?, '\(updatedEpochMillis)', '\(workspaceId)', '\(issueId)', '-1');"
2578
2579 do {
2580 try database.executeUpdate(query, values: [text, authorName])
2581 closure(true)
2582 } catch {
2583 closure(false)
2584 }
2585 } catch {
2586 closure(false)
2587 }
2588 } else {
2589 closure(false)
2590 }
2591 }
2592
2593 // MARK: - Save issue documents info
2594 func saveIssueDocuments(with data: [String: AnyObject], closure: @escaping (Bool) -> Void) {
2595 if self.openDatabase() {
2596 var documentId = ""
2597 var fileName = ""
2598 var type = ""
2599 var workspaceId = ""
2600 var publicUrl = ""
2601
2602 if let tempDocumentId = data["_id"] as? String {
2603 documentId = tempDocumentId
2604 }
2605
2606 if let tempFileName = data["fileName"] as? String {
2607 fileName = tempFileName
2608 }
2609
2610 if let tempType = data["type"] as? String {
2611 type = tempType
2612 }
2613
2614 if let workspaceRef = data["workspaceRef"] as? [String: AnyObject] {
2615 if let tempWorkspaceId = workspaceRef["id"] as? String {
2616 workspaceId = tempWorkspaceId
2617 }
2618 }
2619
2620 if let blobs = data["blobs"] as? [[String: AnyObject]] {
2621 if blobs.count > 0 {
2622 if let metaData = blobs[0]["metaData"] as? [String: AnyObject] {
2623 if let tempPublicUrl = metaData["publicUrl"] as? String {
2624 publicUrl = tempPublicUrl
2625 }
2626 }
2627 }
2628 }
2629
2630 //Remove from IssueVideos table if exist with same ID
2631 do {
2632 try database.executeUpdate("delete from IssueDocuments where documentId='\(documentId)'", values: [])
2633 let query = "insert into IssueDocuments (documentId, fileName, type, workspaceId, publicUrl, editingType) values ('\(documentId)', '\(fileName)', '\(type)', '\(workspaceId)', '\(publicUrl)', '-1');"
2634
2635 if !database.executeStatements(query) {
2636 closure(false)
2637 } else {
2638 closure(true)
2639 }
2640 } catch {
2641 closure(false)
2642 }
2643
2644 } else {
2645 closure(false)
2646 }
2647 }
2648
2649 // MARK: - Remove saved elements from unsaved tables in db
2650 func deleteImageFromUnSaved(with imageId: String, closure: @escaping (Bool) -> Void) {
2651 if self.openDatabase() {
2652 do {
2653 try database.executeUpdate("delete from UnSavedIssueImages where imageId='\(imageId)'", values: [])
2654 closure(true)
2655 } catch {
2656 closure(false)
2657 }
2658
2659 } else {
2660 closure(false)
2661 }
2662 }
2663
2664 func deleteVideosFromUnSaved(with issueId: String, closure: @escaping (Bool) -> Void) {
2665 if self.openDatabase() {
2666 do {
2667 try database.executeUpdate("delete from UnSavedIssueVideos where issueId='\(issueId)'", values: [])
2668 closure(true)
2669 } catch {
2670 closure(false)
2671 }
2672 } else {
2673 closure(false)
2674 }
2675 }
2676
2677 func deleteDocumentsFromUnSaved(with issueId: String, closure: @escaping (Bool) -> Void) {
2678 if self.openDatabase() {
2679 do {
2680 try database.executeUpdate("delete from UnSavedIssueDocuments where issueId='\(issueId)'", values: [])
2681 closure(true)
2682 } catch {
2683 closure(false)
2684 }
2685 } else {
2686 closure(false)
2687 }
2688 }
2689
2690 func deleteCommentsFromUnSaved(with issueId: String, closure: @escaping (Bool) -> Void) {
2691 if self.openDatabase() {
2692 do {
2693 try database.executeUpdate("delete from UnSavedIssueComments where issueId='\(issueId)'", values: [])
2694 closure(true)
2695 } catch {
2696 closure(false)
2697 }
2698 } else {
2699 closure(false)
2700 }
2701 }
2702
2703
2704 //MARK: - Updating issue
2705 func didUpdateIssueImages(with imageId: String, and issueId: String, closure: @escaping (Bool) -> Void) {
2706 if self.openDatabase() {
2707 let query = "select * from Issues where id='\(issueId)'"
2708
2709 do {
2710 let results = try database.executeQuery(query, values: nil)
2711 var imagesStr = ""
2712 while results.next() {
2713 imagesStr = results.string(forColumn: "images")
2714 }
2715
2716 if imagesStr.count > 0 {
2717 imagesStr = "\(imagesStr),\(imageId)"
2718 } else {
2719 imagesStr = imageId
2720 }
2721 //
2722 do {
2723 try database.executeUpdate("UPDATE Issues SET images=?, updatedEpochMillis=? WHERE id=?", values: [imagesStr, "\(Int64(Date().timeIntervalSince1970 * 1000))", issueId])
2724 closure(true)
2725 } catch {
2726 closure(false)
2727 }
2728
2729 } catch {
2730 closure(false)
2731 }
2732 } else {
2733 closure(false)
2734 }
2735 }
2736
2737 func didUpdateIssueTime(with issueId: String, closure: @escaping (Bool) -> Void) {
2738 if self.openDatabase() {
2739 do {
2740 try database.executeUpdate("UPDATE Issues SET updatedEpochMillis=? WHERE id=?", values: ["\(Int64(Date().timeIntervalSince1970 * 1000))", issueId])
2741 closure(true)
2742 } catch {
2743 closure(false)
2744 }
2745 } else {
2746 closure(false)
2747 }
2748 }
2749
2750 func didUpdateIssueVideos(with videoId: String, and issueId: String, closure: @escaping (Bool) -> Void) {
2751 if self.openDatabase() {
2752 let query = "select * from Issues where id='\(issueId)'"
2753
2754 do {
2755 let results = try database.executeQuery(query, values: nil)
2756 var videosStr = ""
2757 while results.next() {
2758 videosStr = results.string(forColumn: "videos")
2759 }
2760
2761 if videosStr.count > 0 {
2762 videosStr = "\(videosStr),\(videoId)"
2763 } else {
2764 videosStr = videoId
2765 }
2766
2767 do {
2768 try database.executeUpdate("UPDATE Issues SET videos=?, updatedEpochMillis=? WHERE id=?", values: [videosStr, "\(Int64(Date().timeIntervalSince1970 * 1000))", issueId])
2769 closure(true)
2770 } catch {
2771 closure(false)
2772 }
2773
2774 } catch {
2775 closure(false)
2776 }
2777 } else {
2778 closure(false)
2779 }
2780 }
2781
2782
2783 // MARK: - Get assignees for workspace
2784 func getAssignees(for workspaceId: String) -> [AssigneeEntity] {
2785 var results: [AssigneeEntity] = [AssigneeEntity]()
2786 if self.openDatabase() {
2787 let siteQuery = "select * from Sites where workspaceId='\(workspaceId)'"
2788 do {
2789 let siteResults = try database.executeQuery(siteQuery, values: nil)
2790 while siteResults.next() {
2791 let assigneesStr = siteResults.string(forColumn: "assignees")
2792 guard let assigneesArr = assigneesStr?.components(separatedBy: ",") else { return results }
2793 for item in assigneesArr {
2794 let assigneeQuery = "select * from IssueAssignees where id='\(item)'"
2795 do {
2796 let assigneeResult = try database.executeQuery(assigneeQuery, values: nil)
2797 while assigneeResult.next() {
2798 let assigneeCaption = assigneeResult.string(forColumn: "caption") ?? ""
2799 let assigneePrimaryImageId = assigneeResult.string(forColumn: "primaryImageId") ?? ""
2800 let assigneeType = assigneeResult.string(forColumn: "type") ?? ""
2801 results.append(AssigneeEntity(id: item, caption: assigneeCaption, primaryImageId: assigneePrimaryImageId, type: assigneeType))
2802 }
2803 }
2804 catch {
2805 }
2806 }
2807 }
2808 } catch {
2809 }
2810 return results
2811 } else {
2812 return results
2813 }
2814 }
2815
2816 // MARK: - Get tags for workspace
2817 func getTags(for workspaceId: String) -> [TagEntity] {
2818 var results: [TagEntity] = [TagEntity]()
2819 if self.openDatabase() {
2820 let siteQuery = "select * from Sites where workspaceId='\(workspaceId)'"
2821 do {
2822 let siteResults = try database.executeQuery(siteQuery, values: nil)
2823 while siteResults.next() {
2824 let tagsStr = siteResults.string(forColumn: "tags")
2825 let tagsArr = tagsStr?.components(separatedBy: "|")
2826
2827 for item in tagsArr! {
2828 if item.count > 0 {
2829 results.append(TagEntity(text: item))
2830 }
2831 }
2832 }
2833 } catch {
2834 }
2835 return results
2836 } else {
2837 return results
2838 }
2839 }
2840
2841 func getDefectAssignees(for defectId: String) -> [AssigneeEntity] {
2842 var results: [AssigneeEntity] = [AssigneeEntity]()
2843 if self.openDatabase() {
2844 let siteQuery = "select * from Issues where id='\(defectId)'"
2845 do {
2846 let siteResults = try database.executeQuery(siteQuery, values: nil)
2847 while siteResults.next() {
2848 let assigneesStr = siteResults.string(forColumn: "assignees")
2849 guard let assigneesArr = assigneesStr?.components(separatedBy: ",") else { return results }
2850 for item in assigneesArr {
2851 let assigneeQuery = "select * from IssueAssignees where id='\(item)'"
2852 do {
2853 let assigneeResult = try database.executeQuery(assigneeQuery, values: nil)
2854 while assigneeResult.next() {
2855 let assigneeCaption = assigneeResult.string(forColumn: "caption") ?? ""
2856 let assigneePrimaryImageId = assigneeResult.string(forColumn: "primaryImageId") ?? ""
2857 let assigneeType = assigneeResult.string(forColumn: "type") ?? ""
2858 results.append(AssigneeEntity(id: item, caption: assigneeCaption, primaryImageId: assigneePrimaryImageId, type: assigneeType))
2859 }
2860 }
2861 catch {
2862 }
2863 }
2864 }
2865 } catch {
2866 }
2867 return results
2868 } else {
2869 return results
2870 }
2871 }
2872
2873 // MARK: - Get tags for workspace
2874 func getDefectTagsString(for workspaceId: String) -> String {
2875 if self.openDatabase() {
2876 let siteQuery = "select * from Shares where label='\(workspaceId)'"
2877 do {
2878 let siteResults = try database.executeQuery(siteQuery, values: nil)
2879 while siteResults.next() {
2880 let tagsStr = siteResults.string(forColumn: "defectTags")
2881 return tagsStr ?? ""
2882 }
2883 } catch {
2884 }
2885 }
2886 return ""
2887 }
2888
2889 func getDefectTags(for workspaceId: String) -> [TagEntity] {
2890 var results: [TagEntity] = [TagEntity]()
2891 if self.openDatabase() {
2892 let siteQuery = "select * from Shares where label='\(workspaceId)'"
2893 do {
2894 let siteResults = try database.executeQuery(siteQuery, values: nil)
2895 while siteResults.next() {
2896 let tagsStr = siteResults.string(forColumn: "defectTags")
2897 guard let tagsArr = tagsStr?.components(separatedBy: "|") else { return results }
2898 for item in tagsArr {
2899 if item.count > 0 {
2900 results.append(TagEntity(text: item))
2901 }
2902 }
2903 }
2904 } catch {
2905 }
2906 return results
2907 } else {
2908 return results
2909 }
2910 }
2911
2912 // MARK: - Get site image id for workspace
2913 func getSiteImageId(for workspaceId: String) -> String? {
2914 if self.openDatabase() {
2915 let siteQuery = "select * from Sites where workspaceId='\(workspaceId)'"
2916 do {
2917 let siteResults = try database.executeQuery(siteQuery, values: nil)
2918 while siteResults.next() {
2919 let imageId = siteResults.string(forColumn: "imageId")
2920 return imageId
2921 }
2922 return nil
2923 } catch {
2924 return nil
2925 }
2926 } else {
2927 return nil
2928 }
2929 }
2930
2931 //MARK: - Saving in offline mode
2932 func didSaveFromModifiedIssue(closure: @escaping (Bool) -> Void) {
2933 if self.openDatabase() {
2934
2935 do {
2936 try? database.executeUpdate("ALTER TABLE ModifiedIssues ADD COLUMN createdBy TEXT", values: nil)
2937 try? database.executeUpdate("ALTER TABLE ModifiedIssues ADD COLUMN assignees TEXT", values: nil)
2938 }
2939
2940 do {
2941 try database.executeUpdate("delete from ModifiedIssues where id='\(ModifiedIssueEntity.shared.id)'", values: [])
2942
2943 var images = ""
2944 for (index, image) in ModifiedIssueEntity.shared.images.enumerated() {
2945 if index == ModifiedIssueEntity.shared.images.count - 1 {
2946 images = "\(images)\(image.imageId)"
2947 } else {
2948 images = "\(images)\(image.imageId),"
2949 }
2950 }
2951
2952 var docs = ""
2953 for (index, doc) in ModifiedIssueEntity.shared.documents.enumerated() {
2954 if index == ModifiedIssueEntity.shared.documents.count - 1 {
2955 docs = "\(docs)\(doc.documentId)"
2956 } else {
2957 docs = "\(docs)\(doc.documentId),"
2958 }
2959 }
2960
2961 var videos = ""
2962 for (index, video) in ModifiedIssueEntity.shared.videos.enumerated() {
2963 if index == ModifiedIssueEntity.shared.videos.count - 1 {
2964 videos = "\(videos)\(video.videoId)"
2965 } else {
2966 videos = "\(videos)\(video.videoId),"
2967 }
2968 }
2969
2970 var assignees = ""
2971 for (index, assignee) in ModifiedIssueEntity.shared.assignees.enumerated() {
2972 if index == ModifiedIssueEntity.shared.assignees.count - 1 {
2973 assignees = "\(assignees)\(assignee.id)"
2974 } else {
2975 assignees = "\(assignees)\(assignee.id),"
2976 }
2977 }
2978
2979 var tags = ""
2980 for (index, tag) in ModifiedIssueEntity.shared.tags.enumerated() {
2981 if index == ModifiedIssueEntity.shared.tags.count - 1 {
2982 tags = "\(tags)\(tag.text)"
2983 } else {
2984 tags = "\(tags)\(tag.text),"
2985 }
2986 }
2987
2988 var deletedImages = ""
2989 for (index, deletedImage) in ModifiedIssueEntity.shared.needToDeleteImages.enumerated() {
2990 if index == ModifiedIssueEntity.shared.needToDeleteImages.count - 1 {
2991 deletedImages = "\(deletedImages)\(deletedImage.imageId)"
2992 } else {
2993 deletedImages = "\(deletedImages)\(deletedImage.imageId),"
2994 }
2995 }
2996
2997 var deletedVideos = ""
2998 for (index, deletedVideo) in ModifiedIssueEntity.shared.needToDeleteVideos.enumerated() {
2999 if index == ModifiedIssueEntity.shared.needToDeleteVideos.count - 1 {
3000 deletedVideos = "\(deletedVideos)\(deletedVideo.videoId)"
3001 } else {
3002 deletedVideos = "\(deletedVideos)\(deletedVideo.videoId),"
3003 }
3004 }
3005
3006 var deletedDocs = ""
3007 for (index, deletedDoc) in ModifiedIssueEntity.shared.needToDeleteDocuments.enumerated() {
3008 if index == ModifiedIssueEntity.shared.needToDeleteDocuments.count - 1 {
3009 deletedDocs = "\(deletedDocs)\(deletedDoc.documentId)"
3010 } else {
3011 deletedDocs = "\(deletedDocs)\(deletedDoc.documentId),"
3012 }
3013 }
3014
3015 var needToAddImages = ""
3016 for (index, needToAddImage) in ModifiedIssueEntity.shared.needToAddImages.enumerated() {
3017
3018 let imageId = needToAddImage.imageId
3019 let workspaceId = ModifiedIssueEntity.shared.workspaceId
3020 let editingType = needToAddImage.editingType
3021 let filePath = needToAddImage.filePath?.path
3022
3023 //Remove from IssueImages table if exist with same ID
3024 do {
3025 try database.executeUpdate("delete from IssueImages where imageId='\(imageId)'", values: [])
3026 let query = "insert into IssueImages (imageId, workspaceId, editingType, filePath) values ('\(imageId)', '\(workspaceId)', '\(editingType)', '\(filePath!)');"
3027
3028 if !database.executeStatements(query) {
3029 } else {
3030 }
3031 } catch {
3032 }
3033
3034
3035 if index == ModifiedIssueEntity.shared.needToAddImages.count - 1 {
3036 needToAddImages = "\(needToAddImages)\(needToAddImage.imageId)"
3037 } else {
3038 needToAddImages = "\(needToAddImages)\(needToAddImage.imageId),"
3039 }
3040 }
3041
3042 var needToAddVideos = ""
3043 for (index, needToAddVideo) in ModifiedIssueEntity.shared.needToAddVideos.enumerated() {
3044
3045 //Remove from IssueVideos table if exist with same ID
3046 do {
3047 try database.executeUpdate("delete from IssueVideos where videoId='\(needToAddVideo.videoId)'", values: [])
3048 let query = "insert into IssueVideos (videoId, fileName, type, workspaceId, key, editingType, filePath) values ('\(needToAddVideo.videoId)', '\(needToAddVideo.fileName)', '\(needToAddVideo.type)', '\(needToAddVideo.workspaceId)', '\(needToAddVideo.key)', '\(needToAddVideo.editingType)', '\((needToAddVideo.filePath?.path)!)');"
3049
3050 if !database.executeStatements(query) {
3051 } else {
3052 }
3053 } catch {
3054 }
3055
3056 if index == ModifiedIssueEntity.shared.needToAddVideos.count - 1 {
3057 needToAddVideos = "\(needToAddVideos)\(needToAddVideo.videoId)"
3058 } else {
3059 needToAddVideos = "\(needToAddVideos)\(needToAddVideo.videoId),"
3060 }
3061 }
3062
3063 var needToAddComments = ""
3064 for (index, needToAddComment) in ModifiedIssueEntity.shared.needToAddComments.enumerated() {
3065
3066
3067 //Remove from IssueComments table if exist with same ID
3068 do {
3069 try database.executeUpdate("delete from IssueComments where commentId='\(needToAddComment.commentId)'", values: [])
3070 let query = "insert into IssueComments (commentId, text, authorName, updatedEpochMillis, workspaceId, issueId, editingType) values ('\(needToAddComment.commentId)', ?, ?, '\(needToAddComment.updatedEpochMillis)', '\(needToAddComment.workspaceId)', '\(needToAddComment.issueId)', '\(needToAddComment.editingType)');"
3071
3072 do {
3073 try database.executeUpdate(query, values: [needToAddComment.text, needToAddComment.authorName])
3074 } catch {
3075 }
3076 } catch {
3077 }
3078
3079
3080 if index == ModifiedIssueEntity.shared.needToAddComments.count - 1 {
3081 needToAddComments = "\(needToAddComments)\(needToAddComment.commentId)"
3082 } else {
3083 needToAddComments = "\(needToAddComments)\(needToAddComment.commentId),"
3084 }
3085 }
3086
3087
3088 var addedImages = ""
3089 for (index, addedImage) in ModifiedIssueEntity.shared.addedImages.enumerated() {
3090
3091 if index == ModifiedIssueEntity.shared.addedImages.count - 1 {
3092 addedImages = "\(addedImages)\(addedImage)"
3093 } else {
3094 addedImages = "\(addedImages)\(addedImage),"
3095 }
3096 }
3097
3098 var addedVideos = ""
3099 for (index, addedVideo) in ModifiedIssueEntity.shared.addedVideos.enumerated() {
3100
3101 if index == ModifiedIssueEntity.shared.addedVideos.count - 1 {
3102 addedVideos = "\(addedVideos)\(addedVideo)"
3103 } else {
3104 addedVideos = "\(addedVideos)\(addedVideo),"
3105 }
3106 }
3107
3108 let query = "insert into ModifiedIssues (description, sequenceNumber, priority, type, title, geoLon, geoLat, workspaceId, id, pinX, pinY, status, documents, images, videos, assignees, tags, updatedEpochMillis, createdEpochMillis, editingType, isNew, needToDeleteDocuments, needToDeleteImages, needToDeleteVideos, needToAddImages, needToAddVideos, needToAddComments, addedImages, addedVideos, rev) values (?, '\(ModifiedIssueEntity.shared.sequenceNumber)', '\(ModifiedIssueEntity.shared.priority)', '\(ModifiedIssueEntity.shared.type)', ?, '\(ModifiedIssueEntity.shared.geoLon)', '\(ModifiedIssueEntity.shared.geoLat)', '\(ModifiedIssueEntity.shared.workspaceId)', '\(ModifiedIssueEntity.shared.id)', '\(ModifiedIssueEntity.shared.pinX)', '\(ModifiedIssueEntity.shared.pinY)', '\(ModifiedIssueEntity.shared.status)', '\(docs)', '\(images)', '\(videos)', ?, ?, '\(ModifiedIssueEntity.shared.updatedEpochMillis)', '\(ModifiedIssueEntity.shared.createdEpochMillis)', '\(ModifiedIssueEntity.shared.editingType)', '\(ModifiedIssueEntity.shared.isNew)', '\(deletedDocs)', '\(deletedImages)', '\(deletedVideos)', '\(needToAddImages)', '\(needToAddVideos)', '\(needToAddComments)', '\(addedImages)', '\(addedVideos)', '\(ModifiedIssueEntity.shared.rev)');"
3109
3110 do {
3111 try database.executeUpdate(query, values: [ModifiedIssueEntity.shared.issueDescription, ModifiedIssueEntity.shared.title, assignees, tags])
3112 do {
3113 try database.executeUpdate("UPDATE Issues SET description=?, priority=?, title=?, status=?, updatedEpochMillis=?, editingType=?, pinX='\(ModifiedIssueEntity.shared.pinX)', pinY='\(ModifiedIssueEntity.shared.pinY)', documents='\(docs)', images='\(images)', videos='\(videos)', assignees='\(assignees)', tags='\(tags)' WHERE id=?", values: [ModifiedIssueEntity.shared.issueDescription, ModifiedIssueEntity.shared.priority, ModifiedIssueEntity.shared.title, ModifiedIssueEntity.shared.status, ModifiedIssueEntity.shared.updatedEpochMillis, ModifiedIssueEntity.shared.editingType, ModifiedIssueEntity.shared.id])
3114 self.updateCustomFields()
3115 closure(true)
3116 } catch {
3117 closure(false)
3118 }
3119 } catch {
3120 closure(false)
3121 }
3122 } catch {
3123 closure(false)
3124 }
3125 } else {
3126 closure(false)
3127 }
3128 }
3129
3130 func didUpdateFromModifiedIssue(closure: @escaping (Bool) -> Void) {
3131 if self.openDatabase() {
3132 do {
3133 try database.executeUpdate("delete from ModifiedIssues where id='\(ModifiedIssueEntity.shared.id)'", values: [])
3134
3135 var images = ""
3136 for (index, image) in ModifiedIssueEntity.shared.images.enumerated() {
3137 if index == ModifiedIssueEntity.shared.images.count - 1 {
3138 images = "\(images)\(image.imageId)"
3139 } else {
3140 images = "\(images)\(image.imageId),"
3141 }
3142 }
3143
3144 var docs = ""
3145 for (index, doc) in ModifiedIssueEntity.shared.documents.enumerated() {
3146 if index == ModifiedIssueEntity.shared.documents.count - 1 {
3147 docs = "\(docs)\(doc.documentId)"
3148 } else {
3149 docs = "\(docs)\(doc.documentId),"
3150 }
3151 }
3152
3153 var videos = ""
3154 for (index, video) in ModifiedIssueEntity.shared.videos.enumerated() {
3155 if index == ModifiedIssueEntity.shared.videos.count - 1 {
3156 videos = "\(videos)\(video.videoId)"
3157 } else {
3158 videos = "\(videos)\(video.videoId),"
3159 }
3160 }
3161
3162 var tags = ""
3163 for (index, tag) in ModifiedIssueEntity.shared.tags.enumerated() {
3164 if index == ModifiedIssueEntity.shared.tags.count - 1 {
3165 tags = "\(tags)\(tag.text)"
3166 } else {
3167 tags = "\(tags)\(tag.text),"
3168 }
3169 }
3170
3171 do {
3172 try database.executeUpdate("UPDATE Issues SET description=?, priority=?, title=?, status=?, updatedEpochMillis=?, editingType=?, pinX='\(ModifiedIssueEntity.shared.pinX)', pinY='\(ModifiedIssueEntity.shared.pinY)', documents='\(docs)', images='\(images)', videos='\(videos)', tags='\(tags)' WHERE id=?", values: [ModifiedIssueEntity.shared.issueDescription, ModifiedIssueEntity.shared.priority, ModifiedIssueEntity.shared.title, ModifiedIssueEntity.shared.status, ModifiedIssueEntity.shared.updatedEpochMillis, "-1", ModifiedIssueEntity.shared.id])
3173 self.updateCustomFields()
3174 closure(true)
3175 } catch {
3176 closure(false)
3177 }
3178
3179 } catch {
3180 closure(false)
3181 }
3182 } else {
3183 closure(false)
3184 }
3185 }
3186
3187 func didCreateOfflineIssue(closure: @escaping (Bool) -> Void) {
3188 if self.openDatabase() {
3189
3190 do {
3191 try? database.executeUpdate("ALTER TABLE Issues ADD COLUMN createdBy TEXT", values: nil)
3192 try? database.executeUpdate("ALTER TABLE Issues ADD COLUMN assignees TEXT", values: nil)
3193 try? database.executeUpdate("ALTER TABLE ModifiedIssues ADD COLUMN createdBy TEXT", values: nil)
3194 try? database.executeUpdate("ALTER TABLE ModifiedIssues ADD COLUMN assignees TEXT", values: nil)
3195 }
3196
3197 var isCreated = false
3198
3199 if ModifiedIssueEntity.shared.id.count > 0 {
3200 isCreated = true
3201 } else {
3202 ModifiedIssueEntity.shared.createdEpochMillis = "\(CUnsignedLongLong(Date().timeIntervalSince1970 * 1000))"
3203 ModifiedIssueEntity.shared.id = FileManagerService().getRandomString(length: 15)
3204 ModifiedIssueEntity.shared.rev = FileManagerService().getRandomString(length: 15)
3205 }
3206
3207 var images = ""
3208 for (index, image) in ModifiedIssueEntity.shared.images.enumerated() {
3209 if index == ModifiedIssueEntity.shared.images.count - 1 {
3210 images = "\(images)\(image.imageId)"
3211 } else {
3212 images = "\(images)\(image.imageId),"
3213 }
3214 }
3215
3216 var docs = ""
3217 for (index, doc) in ModifiedIssueEntity.shared.documents.enumerated() {
3218 if index == ModifiedIssueEntity.shared.documents.count - 1 {
3219 docs = "\(docs)\(doc.documentId)"
3220 } else {
3221 docs = "\(docs)\(doc.documentId),"
3222 }
3223 }
3224
3225 var videos = ""
3226 for (index, video) in ModifiedIssueEntity.shared.videos.enumerated() {
3227 if index == ModifiedIssueEntity.shared.videos.count - 1 {
3228 videos = "\(videos)\(video.videoId)"
3229 } else {
3230 videos = "\(videos)\(video.videoId),"
3231 }
3232 }
3233
3234 var assignees = ""
3235 for (index, assignee) in ModifiedIssueEntity.shared.assignees.enumerated() {
3236 if index == ModifiedIssueEntity.shared.assignees.count - 1 {
3237 assignees = "\(assignees)\(assignee.id)"
3238 } else {
3239 assignees = "\(assignees)\(assignee.id),"
3240 }
3241 }
3242
3243 var tags = ""
3244 for (index, tag) in ModifiedIssueEntity.shared.tags.enumerated() {
3245 if index == ModifiedIssueEntity.shared.tags.count - 1 {
3246 tags = "\(tags)\(tag.text)"
3247 } else {
3248 tags = "\(tags)\(tag.text),"
3249 }
3250 }
3251
3252 var deletedImages = ""
3253 for (index, deletedImage) in ModifiedIssueEntity.shared.needToDeleteImages.enumerated() {
3254 if index == ModifiedIssueEntity.shared.needToDeleteImages.count - 1 {
3255 deletedImages = "\(deletedImages)\(deletedImage.imageId)"
3256 } else {
3257 deletedImages = "\(deletedImages)\(deletedImage.imageId),"
3258 }
3259 }
3260
3261 var deletedVideos = ""
3262 for (index, deletedVideo) in ModifiedIssueEntity.shared.needToDeleteVideos.enumerated() {
3263 if index == ModifiedIssueEntity.shared.needToDeleteVideos.count - 1 {
3264 deletedVideos = "\(deletedVideos)\(deletedVideo.videoId)"
3265 } else {
3266 deletedVideos = "\(deletedVideos)\(deletedVideo.videoId),"
3267 }
3268 }
3269
3270 var deletedDocs = ""
3271 for (index, deletedDoc) in ModifiedIssueEntity.shared.needToDeleteDocuments.enumerated() {
3272 if index == ModifiedIssueEntity.shared.needToDeleteDocuments.count - 1 {
3273 deletedDocs = "\(deletedDocs)\(deletedDoc.documentId)"
3274 } else {
3275 deletedDocs = "\(deletedDocs)\(deletedDoc.documentId),"
3276 }
3277 }
3278
3279 var needToAddImages = ""
3280 for (index, needToAddImage) in ModifiedIssueEntity.shared.needToAddImages.enumerated() {
3281
3282 let imageId = needToAddImage.imageId
3283 let workspaceId = ModifiedIssueEntity.shared.workspaceId
3284 let editingType = needToAddImage.editingType
3285 let filePath = needToAddImage.filePath?.path
3286
3287 //Remove from IssueImages table if exist with same ID
3288 do {
3289 try database.executeUpdate("delete from IssueImages where imageId='\(imageId)'", values: [])
3290 let query = "insert into IssueImages (imageId, workspaceId, editingType, filePath) values ('\(imageId)', '\(workspaceId)', '\(editingType)', '\(filePath!)');"
3291
3292 if !database.executeStatements(query) {
3293 } else {
3294 }
3295 } catch {
3296 }
3297
3298
3299 if index == ModifiedIssueEntity.shared.needToAddImages.count - 1 {
3300 needToAddImages = "\(needToAddImages)\(needToAddImage.imageId)"
3301 } else {
3302 needToAddImages = "\(needToAddImages)\(needToAddImage.imageId),"
3303 }
3304 }
3305
3306 var needToAddVideos = ""
3307 for (index, needToAddVideo) in ModifiedIssueEntity.shared.needToAddVideos.enumerated() {
3308
3309 //Remove from IssueVideos table if exist with same ID
3310 do {
3311 try database.executeUpdate("delete from IssueVideos where videoId='\(needToAddVideo.videoId)'", values: [])
3312 let query = "insert into IssueVideos (videoId, fileName, type, workspaceId, key, editingType, filePath) values ('\(needToAddVideo.videoId)', '\(needToAddVideo.fileName)', '\(needToAddVideo.type)', '\(needToAddVideo.workspaceId)', '\(needToAddVideo.key)', '\(needToAddVideo.editingType)', '\((needToAddVideo.filePath?.path)!)');"
3313
3314 if !database.executeStatements(query) {
3315 } else {
3316 }
3317 } catch {
3318 }
3319
3320 if index == ModifiedIssueEntity.shared.needToAddVideos.count - 1 {
3321 needToAddVideos = "\(needToAddVideos)\(needToAddVideo.videoId)"
3322 } else {
3323 needToAddVideos = "\(needToAddVideos)\(needToAddVideo.videoId),"
3324 }
3325 }
3326
3327 var needToAddComments = ""
3328 for (index, needToAddComment) in ModifiedIssueEntity.shared.needToAddComments.enumerated() {
3329
3330
3331 //Remove from IssueComments table if exist with same ID
3332 do {
3333 try database.executeUpdate("delete from IssueComments where commentId='\(needToAddComment.commentId)'", values: [])
3334 let query = "insert into IssueComments (commentId, text, authorName, updatedEpochMillis, workspaceId, issueId, editingType) values ('\(needToAddComment.commentId)', ?, ?, '\(needToAddComment.updatedEpochMillis)', '\(needToAddComment.workspaceId)', '\(needToAddComment.issueId)', '\(needToAddComment.editingType)');"
3335
3336 do {
3337 try database.executeUpdate(query, values: [needToAddComment.text, needToAddComment.authorName])
3338 } catch {
3339 }
3340 } catch {
3341 }
3342
3343
3344 if index == ModifiedIssueEntity.shared.needToAddComments.count - 1 {
3345 needToAddComments = "\(needToAddComments)\(needToAddComment.commentId)"
3346 } else {
3347 needToAddComments = "\(needToAddComments)\(needToAddComment.commentId),"
3348 }
3349 }
3350
3351
3352 var addedImages = ""
3353 for (index, addedImage) in ModifiedIssueEntity.shared.addedImages.enumerated() {
3354
3355 if index == ModifiedIssueEntity.shared.addedImages.count - 1 {
3356 addedImages = "\(addedImages)\(addedImage)"
3357 } else {
3358 addedImages = "\(addedImages)\(addedImage),"
3359 }
3360 }
3361
3362 var addedVideos = ""
3363 for (index, addedVideo) in ModifiedIssueEntity.shared.addedVideos.enumerated() {
3364
3365 if index == ModifiedIssueEntity.shared.addedVideos.count - 1 {
3366 addedVideos = "\(addedVideos)\(addedVideo)"
3367 } else {
3368 addedVideos = "\(addedVideos)\(addedVideo),"
3369 }
3370 }
3371
3372
3373
3374
3375 if isCreated {
3376 self.didSaveFromModifiedIssue(closure: { (success) in
3377 if success {
3378 closure(true)
3379 } else {
3380 closure(false)
3381 }
3382 })
3383 } else {
3384 let query = "insert into ModifiedIssues (description, sequenceNumber, createdBy, priority, type, title, geoLon, geoLat, workspaceId, id, pinX, pinY, status, documents, images, videos, assignees, tags, updatedEpochMillis, createdEpochMillis, editingType, isNew, needToDeleteDocuments, needToDeleteImages, needToDeleteVideos, needToAddImages, needToAddVideos, needToAddComments, addedImages, addedVideos, rev) values (?, '\(ModifiedIssueEntity.shared.sequenceNumber)', '\(ModifiedIssueEntity.shared.createdBy)', '\(ModifiedIssueEntity.shared.priority)', '\(ModifiedIssueEntity.shared.type)', ?, '\(ModifiedIssueEntity.shared.geoLon)', '\(ModifiedIssueEntity.shared.geoLat)', '\(ModifiedIssueEntity.shared.workspaceId)', '\(ModifiedIssueEntity.shared.id)', '\(ModifiedIssueEntity.shared.pinX)', '\(ModifiedIssueEntity.shared.pinY)', '\(ModifiedIssueEntity.shared.status)', '\(docs)', '\(images)', '\(videos)', ?, ?, '\(ModifiedIssueEntity.shared.updatedEpochMillis)', '\(ModifiedIssueEntity.shared.createdEpochMillis)', '\(ModifiedIssueEntity.shared.editingType)', '\(ModifiedIssueEntity.shared.isNew)', '\(deletedDocs)', '\(deletedImages)', '\(deletedVideos)', '\(needToAddImages)', '\(needToAddVideos)', '\(needToAddComments)', '\(addedImages)', '\(addedVideos)', '\(ModifiedIssueEntity.shared.rev)');"
3385
3386 do {
3387 try database.executeUpdate(query, values: [ModifiedIssueEntity.shared.issueDescription, ModifiedIssueEntity.shared.title, assignees, tags])
3388
3389 //try database.executeUpdate("delete from Issues where id='\(ModifiedIssueEntity.shared.id)'", values: [])
3390 let query1 = "insert into Issues (description, sequenceNumber, createdBy, priority, type, title, geoLon, geoLat, workspaceId, id, pinX, pinY, status, documents, images, videos, assignees, tags, updatedEpochMillis, createdEpochMillis, editingType, rev) values (?, '\(ModifiedIssueEntity.shared.sequenceNumber)', '\(ModifiedIssueEntity.shared.createdBy)', '\(ModifiedIssueEntity.shared.priority)', '\(ModifiedIssueEntity.shared.type)', ?, '\(ModifiedIssueEntity.shared.geoLon)', '\(ModifiedIssueEntity.shared.geoLat)', '\(ModifiedIssueEntity.shared.workspaceId)', '\(ModifiedIssueEntity.shared.id)', '\(ModifiedIssueEntity.shared.pinX)', '\(ModifiedIssueEntity.shared.pinY)', '\(ModifiedIssueEntity.shared.status)', '\(docs)', '\(images)', '\(videos)', ?, ?, '\(ModifiedIssueEntity.shared.updatedEpochMillis)', '\(ModifiedIssueEntity.shared.createdEpochMillis)', '\(ModifiedIssueEntity.shared.editingType)', '\(ModifiedIssueEntity.shared.rev)');"
3391
3392 do {
3393 try database.executeUpdate(query1, values: [ModifiedIssueEntity.shared.issueDescription, ModifiedIssueEntity.shared.title, assignees, tags])
3394 self.updateCustomFields()
3395 closure(true)
3396 } catch {
3397 closure(false)
3398 }
3399 } catch {
3400 closure(false)
3401 }
3402 }
3403 } else {
3404 closure(false)
3405 }
3406 }
3407
3408 func didUpdateFromModifiedCreatedIssue(item: [String: AnyObject], closure: @escaping (Bool) -> Void) {
3409 if self.openDatabase() {
3410 do {
3411 try database.executeUpdate("delete from ModifiedIssues where id='\(ModifiedIssueEntity.shared.id)'", values: [])
3412
3413 var sequenceNumber = -1
3414 var id = ""
3415 var createdBy = ""
3416 var updatedEpochMillis = ""
3417 var rev = ""
3418 var geoLon = 0.0
3419 var geoLat = 0.0
3420
3421 if let geo = item["geo"] as? [String: AnyObject] {
3422 if let tempGeoLon = geo["lon"] as? Double {
3423 geoLon = tempGeoLon
3424 }
3425 if let tempGeoLat = geo["lat"] as? Double {
3426 geoLat = tempGeoLat
3427 }
3428 }
3429
3430 if let header = item["header"] as? [String: AnyObject] {
3431 if let tempCreatedBy = header["createdBy"] as? [String: AnyObject] {
3432 if let tempCaption = tempCreatedBy["caption"] as? String {
3433 createdBy = tempCaption
3434 }
3435 }
3436 if let tempUpdatedEpochMillis = header["updatedEpochMillis"] as? Int64 {
3437 updatedEpochMillis = "\(tempUpdatedEpochMillis)"
3438 }
3439 }
3440 if let tempRev = item["_rev"] as? String {
3441 rev = tempRev
3442 }
3443
3444 if let tempSequenceNumber = item["sequenceNumber"] as? Int {
3445 sequenceNumber = tempSequenceNumber
3446 }
3447 if let tempId = item["_id"] as? String {
3448 id = tempId
3449 }
3450
3451 var images = ""
3452 for (index, image) in ModifiedIssueEntity.shared.images.enumerated() {
3453 if index == ModifiedIssueEntity.shared.images.count - 1 {
3454 images = "\(images)\(image.imageId)"
3455 } else {
3456 images = "\(images)\(image.imageId),"
3457 }
3458 }
3459
3460 var docs = ""
3461 for (index, doc) in ModifiedIssueEntity.shared.documents.enumerated() {
3462 if index == ModifiedIssueEntity.shared.documents.count - 1 {
3463 docs = "\(docs)\(doc.documentId)"
3464 } else {
3465 docs = "\(docs)\(doc.documentId),"
3466 }
3467 }
3468
3469 var videos = ""
3470 for (index, video) in ModifiedIssueEntity.shared.videos.enumerated() {
3471 if index == ModifiedIssueEntity.shared.videos.count - 1 {
3472 videos = "\(videos)\(video.videoId)"
3473 } else {
3474 videos = "\(videos)\(video.videoId),"
3475 }
3476 }
3477
3478 var tags = ""
3479 for (index, tag) in ModifiedIssueEntity.shared.tags.enumerated() {
3480 if index == ModifiedIssueEntity.shared.tags.count - 1 {
3481 tags = "\(tags)\(tag.text)"
3482 } else {
3483 tags = "\(tags)\(tag.text),"
3484 }
3485 }
3486
3487 do {
3488 try database.executeUpdate("UPDATE Issues SET createdBy='\(createdBy)', description=?, priority=?, title=?, status=?, updatedEpochMillis=?, editingType=?, pinX='\(ModifiedIssueEntity.shared.pinX)', pinY='\(ModifiedIssueEntity.shared.pinY)', documents='\(docs)', images='\(images)', videos='\(videos)', tags='\(tags)', id='\(id)', rev='\(rev)', sequenceNumber='\(sequenceNumber)', updatedEpochMillis='\(updatedEpochMillis)', geoLon='\(geoLon)', geoLat='\(geoLat)' WHERE id=?", values: [ModifiedIssueEntity.shared.issueDescription, ModifiedIssueEntity.shared.priority, ModifiedIssueEntity.shared.title, ModifiedIssueEntity.shared.status, ModifiedIssueEntity.shared.updatedEpochMillis, "-1", ModifiedIssueEntity.shared.id])
3489 closure(true)
3490 } catch {
3491 closure(false)
3492 }
3493
3494 } catch {
3495 closure(false)
3496 }
3497 } else {
3498 closure(false)
3499 }
3500 }
3501
3502 func updateCustomFields() {
3503 do {
3504 try database.executeUpdate("create table if not exists IssuesCustomFields(issueId text, id text, fieldType text, label text, value text, currency text, listLevel text, workspaseId text)", values: nil)
3505
3506
3507 try database.executeUpdate("delete from IssuesCustomFields where issueId='\(ModifiedIssueEntity.shared.id)'", values: [])
3508
3509 for field in ModifiedIssueEntity.shared.customFields {
3510 let fieldType = field.fieldType
3511 var label = ""
3512 var value = ""
3513
3514 var query = ""
3515
3516 switch fieldType {
3517 case CustomFieldsType.text.rawValue:
3518 let fieldID = field.fieldId
3519 label = field.label
3520 value = (field as! FieldTextEntity).value
3521
3522 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(ModifiedIssueEntity.shared.workspaceId)');"
3523 break
3524
3525 case CustomFieldsType.date.rawValue:
3526 let fieldID = field.fieldId
3527 label = field.label
3528 value = (field as! FieldDateEntity).value
3529
3530 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(ModifiedIssueEntity.shared.workspaceId)');"
3531 break
3532
3533 case CustomFieldsType.cost.rawValue:
3534 let currency = field.currency
3535 let fieldID = field.fieldId
3536 label = field.label
3537 value = (field as! FieldCostEntity).value
3538
3539 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '\(currency)', '', '\(ModifiedIssueEntity.shared.workspaceId)');"
3540 break
3541
3542 case CustomFieldsType.list.rawValue:
3543 let listLevel = (field as! FieldListEntity).selectedID
3544 let fieldID = field.fieldId
3545 label = field.label
3546 value = (field as! FieldListEntity).value
3547
3548 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '', '\(listLevel)', '\(ModifiedIssueEntity.shared.workspaceId)');"
3549 break
3550
3551 default:
3552 break
3553 }
3554
3555 do {
3556 try database.executeUpdate(query, values: [label, value])
3557 } catch {
3558 }
3559 }
3560 } catch {
3561 }
3562 }
3563
3564
3565 func getIssuesIdsForSync() -> [String] {
3566 var ids: [String] = [String]()
3567 if self.openDatabase() {
3568
3569 let query = "select * from ModifiedIssues where editingType='1' OR editingType='2' OR editingType='0'"
3570
3571 do {
3572 let results = try database.executeQuery(query, values: nil)
3573
3574 while results.next() {
3575 ids.append(results.string(forColumn: "id"))
3576 }
3577 } catch {
3578
3579 }
3580
3581 return ids
3582 } else {
3583 return ids
3584 }
3585 }
3586
3587 func getIssuesIdsForCreate() -> [String] {
3588 var ids: [String] = [String]()
3589 if self.openDatabase() {
3590
3591 let query = "select * from ModifiedIssues where editingType='3'"
3592
3593 do {
3594 let results = try database.executeQuery(query, values: nil)
3595
3596 while results.next() {
3597 ids.append(results.string(forColumn: "id"))
3598 }
3599 } catch {
3600
3601 }
3602
3603 return ids
3604 } else {
3605 return ids
3606 }
3607 }
3608
3609 func updateCommentIssueId(from oldIssueId: String, to issueId: String, closure: @escaping (Bool) -> Void) {
3610 if self.openDatabase() {
3611 do {
3612 try database.executeUpdate("UPDATE IssueComments SET issueId='\(issueId)' WHERE issueId='\(oldIssueId)'", values: [])
3613
3614 closure(true)
3615 } catch {
3616 closure(false)
3617 }
3618 } else {
3619 closure(false)
3620 }
3621 }
3622
3623 func removeIssues(from ids: [String]) {
3624 if self.openDatabase() {
3625 for issueId in ids {
3626 do {
3627 try database.executeUpdate("delete from Issues where id='\(issueId)'", values: [])
3628 try database.executeUpdate("delete from ModifiedIssues where id='\(issueId)'", values: [])
3629 } catch {
3630 }
3631 }
3632 }
3633 }
3634
3635 func updateCustomFieldForNewCretedIssue(from fields: [[String: AnyObject]], and workspaseId: String, and newIssueId: String, closure: @escaping (Bool) -> Void) {
3636 if self.openDatabase() {
3637
3638 do {
3639 try database.executeUpdate("create table if not exists IssuesCustomFields(issueId text, id text, fieldType text, label text, value text, currency text, listLevel text, workspaseId text)", values: nil)
3640
3641 for customField in fields {
3642 let value = customField["value"] as? String ?? ""
3643 let fieldID = customField["customFieldTemplateId"] as? String ?? ""
3644 var currency = ""
3645 var label = ""
3646 var fieldType = ""
3647
3648 try database.executeUpdate("delete from IssuesCustomFields where id='\(fieldID)' AND issueId='\(newIssueId)'", values: [])
3649
3650 var query = "select * from AllCustomFields where workspaceId='\(workspaseId)' AND id='\(fieldID)'"
3651
3652 do {
3653 let results = try database.executeQuery(query, values: nil)
3654
3655 while results.next() {
3656 currency = results.string(forColumn: "currency")!
3657 label = results.string(forColumn: "label")!
3658 fieldType = results.string(forColumn: "type")!
3659 }
3660 } catch {
3661 }
3662
3663 switch fieldType {
3664 case CustomFieldsType.text.rawValue:
3665 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(workspaseId)');"
3666 break
3667
3668 case CustomFieldsType.date.rawValue:
3669 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(workspaseId)');"
3670 break
3671
3672 case CustomFieldsType.cost.rawValue:
3673 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '\(currency)', '', '\(workspaseId)');"
3674 break
3675
3676 case CustomFieldsType.list.rawValue:
3677 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(workspaseId)');"
3678 break
3679
3680 default:
3681 break
3682 }
3683
3684 do {
3685 try database.executeUpdate(query, values: [label, value])
3686 } catch {
3687 }
3688 }
3689 closure(true)
3690 } catch {
3691 closure(false)
3692 }
3693 } else {
3694 closure(false)
3695 }
3696 }
3697
3698 func editCustomFields(from fields: [[String: AnyObject]], and workspaseId: String) {
3699 if self.openDatabase() {
3700 for customField in fields {
3701 let fieldType = customField["type"] as? String ?? ""
3702 let fieldId = "\(customField["id"] as? Int ?? -1)"
3703 let currency = customField["currency"] as? String ?? ""
3704 let label = customField["label"] as? String ?? ""
3705
3706 do {
3707 try database.executeUpdate("create table if not exists AllCustomFields(id text, currency text, label text, type text, workspaceId text)", values: nil)
3708 try database.executeUpdate("UPDATE AllCustomFields SET label=?, currency=? WHERE id='\(fieldId)' AND workspaceId='\(workspaseId)'", values: [label, currency])
3709 try database.executeUpdate("UPDATE IssuesCustomFields SET label=?, currency=? WHERE id='\(fieldId)' AND workspaseId='\(workspaseId)'", values: [label, currency])
3710
3711 } catch {
3712 }
3713
3714 switch fieldType {
3715 case CustomFieldsType.list.rawValue:
3716 do {
3717 try database.executeUpdate("delete from SubList where superId=? AND workspaceId=?", values: [fieldId, workspaseId])
3718
3719 if let subList = customField["subList"] as? [[String: AnyObject]] {
3720 if subList.count > 0 {
3721 self.saveSubListToDB(parentId: fieldId, list: subList, superId: fieldId, workspaceId: workspaseId)
3722 }
3723 }
3724 } catch {
3725 }
3726 break
3727 default:
3728 break
3729 }
3730 }
3731 }
3732 }
3733
3734 func addNewCustomFields(rom fields: [[String: AnyObject]], and workspaseId: String) {
3735 if self.openDatabase() {
3736 for customField in fields {
3737 let fieldType = customField["type"] as? String ?? ""
3738 let fieldId = "\(customField["id"] as? Int ?? -1)"
3739 let currency = customField["currency"] as? String ?? ""
3740 let label = customField["label"] as? String ?? ""
3741
3742 do {
3743 try database.executeUpdate("create table if not exists AllCustomFields(id text, currency text, label text, type text, workspaceId text)", values: nil)
3744
3745 try database.executeUpdate("delete from AllCustomFields where id='\(fieldId)' AND workspaceId='\(workspaseId)'", values: [])
3746
3747 //Save custom field in db
3748 let query = "insert into AllCustomFields (id, currency, label, type, workspaceId) values (?, ?, ?, ?, ?);"
3749 try database.executeUpdate(query, values: [fieldId, currency, label, fieldType, workspaseId])
3750
3751 //Save list to db
3752 if fieldType == CustomFieldsType.list.rawValue {
3753 if let subList = customField["subList"] as? [[String: AnyObject]] {
3754 if subList.count > 0 {
3755 self.saveSubListToDB(parentId: fieldId, list: subList, superId: fieldId, workspaceId: workspaseId)
3756 }
3757 }
3758 }
3759 } catch {
3760 }
3761 }
3762 }
3763 }
3764
3765 func addNewTags(from tags: [String], and workspaseId: String) {
3766 if self.openDatabase() {
3767 let siteQuery = "select * from Sites where workspaceId='\(workspaseId)'"
3768
3769 do {
3770 let siteResults = try database.executeQuery(siteQuery, values: nil)
3771 while siteResults.next() {
3772 let tagsStr = siteResults.string(forColumn: "tags")!
3773
3774 var tagsArray = tagsStr.components(separatedBy: "|")
3775
3776 for addedTag in tags {
3777 tagsArray.append(addedTag)
3778 }
3779 var newTagsStr = ""
3780 for (index, tag) in tagsArray.enumerated() {
3781 if index == tagsArray.count - 1 {
3782 newTagsStr = "\(newTagsStr)\(tag)"
3783 } else {
3784 newTagsStr = "\(newTagsStr)\(tag)|"
3785 }
3786 }
3787
3788 try database.executeUpdate("UPDATE Sites SET tags=? WHERE workspaceId='\(workspaseId)'", values: [newTagsStr])
3789 }
3790 } catch {
3791
3792 }
3793 }
3794 }
3795
3796 func removeTags(from tags: [String], and workspaseId: String) {
3797 if self.openDatabase() {
3798 let siteQuery = "select * from Sites where workspaceId='\(workspaseId)'"
3799 do {
3800 let siteResults = try database.executeQuery(siteQuery, values: nil)
3801 while siteResults.next() {
3802 let tagsStr = siteResults.string(forColumn: "tags")!
3803
3804 var tagsArray = tagsStr.components(separatedBy: "|")
3805
3806 for removedTag in tags {
3807 var index = -1
3808
3809 for (tempIndex, tag) in tagsArray.enumerated() {
3810 if tag == removedTag {
3811 index = tempIndex
3812 break
3813 }
3814 }
3815
3816 if index != -1 && index < tagsArray.count {
3817 tagsArray.remove(at: index)
3818 }
3819 }
3820
3821 let newTagsStr = tagsArray.joined(separator: "|")
3822 try database.executeUpdate("UPDATE Sites SET tags=? WHERE workspaceId='\(workspaseId)'", values: [newTagsStr])
3823
3824 for removedTag in tags {
3825 let issuesQuery = "select * from Issues where workspaceId='\(workspaseId)' AND tags like '%?%'"
3826 do {
3827 let issuesResults = try database.executeQuery(issuesQuery, values: [removedTag])
3828 while issuesResults.next() {
3829 let tagsStr = issuesResults.string(forColumn: "tags")!
3830 let issueId = issuesResults.string(forColumn: "id")!
3831
3832 var tagsArray = tagsStr.components(separatedBy: ",")
3833
3834 var index = -1
3835
3836 for (tempIndex, tag) in tagsArray.enumerated() {
3837 if tag == removedTag {
3838 index = tempIndex
3839 break
3840 }
3841 }
3842
3843 if index != -1 && index < tagsArray.count {
3844 tagsArray.remove(at: index)
3845 }
3846
3847 let newTagsStr = tagsArray.joined(separator: ",")
3848 try database.executeUpdate("UPDATE Issues SET tags=? WHERE id='\(issueId)'", values: [newTagsStr])
3849
3850 }
3851
3852
3853 let issuesQuery = "select * from ModifiedIssues where workspaceId='\(workspaseId)' AND tags like '%?%'"
3854 do {
3855 let issuesResults = try database.executeQuery(issuesQuery, values: [removedTag])
3856 while issuesResults.next() {
3857 let tagsStr = issuesResults.string(forColumn: "tags")!
3858 let issueId = issuesResults.string(forColumn: "id")!
3859
3860 var tagsArray = tagsStr.components(separatedBy: ",")
3861
3862 var index = -1
3863
3864 for (tempIndex, tag) in tagsArray.enumerated() {
3865 if tag == removedTag {
3866 index = tempIndex
3867 break
3868 }
3869 }
3870
3871 if index != -1 && index < tagsArray.count {
3872 tagsArray.remove(at: index)
3873 }
3874
3875 let newTagsStr = tagsArray.joined(separator: ",")
3876 try database.executeUpdate("UPDATE ModifiedIssues SET tags=? WHERE id='\(issueId)'", values: [newTagsStr])
3877
3878 }
3879 } catch {
3880
3881 }
3882
3883
3884 } catch {
3885
3886 }
3887 }
3888
3889 }
3890 } catch {
3891 }
3892 }
3893 }
3894
3895 func removeCustomFields(from ids: [Int], and workspaseId: String) {
3896 if self.openDatabase() {
3897 for id in ids {
3898
3899 if workspaseId == ModifiedIssueEntity.shared.workspaceId {
3900 var ind = -1
3901 for (index, cf) in ModifiedIssueEntity.shared.customFields.enumerated() {
3902 if cf.fieldId == "\(id)" {
3903 ind = index
3904 }
3905 }
3906
3907 if ind != -1 {
3908 ModifiedIssueEntity.shared.customFields.remove(at: ind)
3909 ind = -1
3910 }
3911 }
3912
3913 let query = "select * from AllCustomFields where id=? AND workspaceId='\(workspaseId)'"
3914
3915 do {
3916 let results = try database.executeQuery(query, values: [id])
3917
3918 while results.next() {
3919 let fieldType = results.string(forColumn: "type")!
3920 if fieldType == CustomFieldsType.list.rawValue {
3921 try database.executeUpdate("delete from SubList where superId=? AND workspaceId=?", values: [id, workspaseId])
3922 }
3923 }
3924
3925 try database.executeUpdate("delete from AllCustomFields where id=? AND workspaceId=?", values: [id, workspaseId])
3926 try database.executeUpdate("delete from IssuesCustomFields where id=? AND workspaseId=?", values: [id, workspaseId])
3927 } catch {
3928 }
3929 }
3930 }
3931 }
3932
3933
3934
3935 func clearDB(closure: @escaping (Bool) -> Void) {
3936 if self.openDatabase() {
3937 do {
3938 try database.executeUpdate("delete from User", values: [])
3939 try database.executeUpdate("delete from Shares", values: [])
3940 try database.executeUpdate("delete from Workspaces", values: [])
3941 try database.executeUpdate("delete from UnSavedSites", values: [])
3942 try database.executeUpdate("delete from Accounts", values: [])
3943 try database.executeUpdate("delete from DownloadedSitePlans", values: [])
3944 try database.executeUpdate("delete from Sites", values: [])
3945 try database.executeUpdate("delete from SitePlanTiles", values: [])
3946
3947 try database.executeUpdate("delete from AllCustomFields", values: [])
3948 try database.executeUpdate("delete from SubList", values: [])
3949 try database.executeUpdate("delete from Maps", values: [])
3950 try database.executeUpdate("delete from IssuesCustomFields", values: [])
3951 try database.executeUpdate("delete from UnSavedIssueVideos", values: [])
3952 try database.executeUpdate("delete from UnSavedIssueImages", values: [])
3953 try database.executeUpdate("delete from CustomFieldsPermission", values: [])
3954
3955// try database.executeUpdate("delete from Issues", values: [])
3956// try database.executeUpdate("delete from DownloadedImages", values: [])
3957// try database.executeUpdate("delete from IssueImages", values: [])
3958// try database.executeUpdate("delete from IssueVideos", values: [])
3959// try database.executeUpdate("delete from IssueComments", values: [])
3960//
3961// try database.executeUpdate("delete from IssueDocuments", values: [])
3962// try database.executeUpdate("delete from ModifiedIssues", values: [])
3963
3964 UserDefaults.standard.removePersistentDomain(forName: Bundle.main.bundleIdentifier!)
3965 UserDefaults.standard.synchronize()
3966
3967 closure(true)
3968 }
3969 catch {
3970 closure(false)
3971 }
3972 } else {
3973 closure(false)
3974 }
3975 }
3976
3977
3978
3979 //MARK: - Check if need create cf
3980 func getCustomFieldsCount(for workspaceId: String) -> Int {
3981 if (UserDefaults.standard.value(forKey: "cf_\(workspaceId)") != nil) {
3982 return 100
3983 } else {
3984 return 0
3985 }
3986 }
3987
3988 func getSite(for workspaceId: String) -> SiteEntity? {
3989 if self.openDatabase() {
3990 let siteQuery = "select * from Sites where workspaceId='\(workspaceId)'"
3991 do {
3992 let siteResults = try database.executeQuery(siteQuery, values: nil)
3993 while siteResults.next() {
3994 return SiteEntity(id: siteResults.string(forColumn: "id"), workspaceId: siteResults.string(forColumn: "workspaceId"), name: siteResults.string(forColumn: "name"), tags: siteResults.string(forColumn: "tags"), imageId: siteResults.string(forColumn: "imageId"))
3995 }
3996 } catch {
3997 }
3998 return nil
3999 } else {
4000 return nil
4001 }
4002 }
4003
4004}