· 5 years ago · Mar 03, 2020, 09:56 AM
1func updateCustomFields() {
2 do {
3 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)
4
5
6 try database.executeUpdate("delete from IssuesCustomFields where issueId='\(ModifiedIssueEntity.shared.id)'", values: [])
7
8 for field in ModifiedIssueEntity.shared.customFields {
9 let fieldType = field.fieldType
10 var label = ""
11 var value = ""
12
13 var query = ""
14
15 switch fieldType {
16 case CustomFieldsType.text.rawValue:
17 let fieldID = field.fieldId
18 label = field.label
19 value = (field as! FieldTextEntity).value
20
21 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(ModifiedIssueEntity.shared.workspaceId)');"
22 break
23
24 case CustomFieldsType.date.rawValue:
25 let fieldID = field.fieldId
26 label = field.label
27 value = (field as! FieldDateEntity).value
28
29 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(ModifiedIssueEntity.shared.workspaceId)');"
30 break
31
32 case CustomFieldsType.cost.rawValue:
33 let currency = field.currency
34 let fieldID = field.fieldId
35 label = field.label
36 value = (field as! FieldCostEntity).value
37
38 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '\(currency)', '', '\(ModifiedIssueEntity.shared.workspaceId)');"
39 break
40
41 case CustomFieldsType.list.rawValue:
42 let listLevel = (field as! FieldListEntity).selectedID
43 let fieldID = field.fieldId
44 label = field.label
45 value = (field as! FieldListEntity).value
46
47 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(ModifiedIssueEntity.shared.id)', '\(fieldID)', '\(fieldType)', ?, ?, '', '\(listLevel)', '\(ModifiedIssueEntity.shared.workspaceId)');"
48 break
49
50 default:
51 break
52 }
53
54 do {
55 try database.executeUpdate(query, values: [label, value])
56 } catch {
57 }
58 }
59 } catch {
60 }
61 }
62
63
64 func getIssuesIdsForSync() -> [String] {
65 var ids: [String] = [String]()
66 if self.openDatabase() {
67
68 let query = "select * from ModifiedIssues where editingType='1' OR editingType='2' OR editingType='0'"
69
70 do {
71 let results = try database.executeQuery(query, values: nil)
72
73 while results.next() {
74 ids.append(results.string(forColumn: "id"))
75 }
76 } catch {
77
78 }
79
80 return ids
81 } else {
82 return ids
83 }
84 }
85
86 func getIssuesIdsForCreate() -> [String] {
87 var ids: [String] = [String]()
88 if self.openDatabase() {
89
90 let query = "select * from ModifiedIssues where editingType='3'"
91
92 do {
93 let results = try database.executeQuery(query, values: nil)
94
95 while results.next() {
96 ids.append(results.string(forColumn: "id"))
97 }
98 } catch {
99
100 }
101
102 return ids
103 } else {
104 return ids
105 }
106 }
107
108 func updateCommentIssueId(from oldIssueId: String, to issueId: String, closure: @escaping (Bool) -> Void) {
109 if self.openDatabase() {
110 do {
111 try database.executeUpdate("UPDATE IssueComments SET issueId='\(issueId)' WHERE issueId='\(oldIssueId)'", values: [])
112
113 closure(true)
114 } catch {
115 closure(false)
116 }
117 } else {
118 closure(false)
119 }
120 }
121
122 func removeIssues(from ids: [String]) {
123 if self.openDatabase() {
124 for issueId in ids {
125 do {
126 try database.executeUpdate("delete from Issues where id='\(issueId)'", values: [])
127 try database.executeUpdate("delete from ModifiedIssues where id='\(issueId)'", values: [])
128 } catch {
129 }
130 }
131 }
132 }
133
134 func updateCustomFieldForNewCretedIssue(from fields: [[String: AnyObject]], and workspaseId: String, and newIssueId: String, closure: @escaping (Bool) -> Void) {
135 if self.openDatabase() {
136
137 do {
138 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)
139
140 for customField in fields {
141 let value = customField["value"] as? String ?? ""
142 let fieldID = customField["customFieldTemplateId"] as? String ?? ""
143 var currency = ""
144 var label = ""
145 var fieldType = ""
146
147 try database.executeUpdate("delete from IssuesCustomFields where id='\(fieldID)' AND issueId='\(newIssueId)'", values: [])
148
149 var query = "select * from AllCustomFields where workspaceId='\(workspaseId)' AND id='\(fieldID)'"
150
151 do {
152 let results = try database.executeQuery(query, values: nil)
153
154 while results.next() {
155 currency = results.string(forColumn: "currency")!
156 label = results.string(forColumn: "label")!
157 fieldType = results.string(forColumn: "type")!
158 }
159 } catch {
160 }
161
162 switch fieldType {
163 case CustomFieldsType.text.rawValue:
164 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(workspaseId)');"
165 break
166
167 case CustomFieldsType.date.rawValue:
168 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(workspaseId)');"
169 break
170
171 case CustomFieldsType.cost.rawValue:
172 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '\(currency)', '', '\(workspaseId)');"
173 break
174
175 case CustomFieldsType.list.rawValue:
176 query = "insert into IssuesCustomFields (issueId, id, fieldType, label, value, currency, listLevel, workspaseId) values ('\(newIssueId)', '\(fieldID)', '\(fieldType)', ?, ?, '', '', '\(workspaseId)');"
177 break
178
179 default:
180 break
181 }
182
183 do {
184 try database.executeUpdate(query, values: [label, value])
185 } catch {
186 }
187 }
188 closure(true)
189 } catch {
190 closure(false)
191 }
192 } else {
193 closure(false)
194 }
195 }
196
197 func editCustomFields(from fields: [[String: AnyObject]], and workspaseId: String) {
198 if self.openDatabase() {
199 for customField in fields {
200 let fieldType = customField["type"] as? String ?? ""
201 let fieldId = "\(customField["id"] as? Int ?? -1)"
202 let currency = customField["currency"] as? String ?? ""
203 let label = customField["label"] as? String ?? ""
204
205 do {
206 try database.executeUpdate("create table if not exists AllCustomFields(id text, currency text, label text, type text, workspaceId text)", values: nil)
207 try database.executeUpdate("UPDATE AllCustomFields SET label=?, currency=? WHERE id='\(fieldId)' AND workspaceId='\(workspaseId)'", values: [label, currency])
208 try database.executeUpdate("UPDATE IssuesCustomFields SET label=?, currency=? WHERE id='\(fieldId)' AND workspaseId='\(workspaseId)'", values: [label, currency])
209
210 } catch {
211 }
212
213 switch fieldType {
214 case CustomFieldsType.list.rawValue:
215 do {
216 try database.executeUpdate("delete from SubList where superId=? AND workspaceId=?", values: [fieldId, workspaseId])
217
218 if let subList = customField["subList"] as? [[String: AnyObject]] {
219 if subList.count > 0 {
220 self.saveSubListToDB(parentId: fieldId, list: subList, superId: fieldId, workspaceId: workspaseId)
221 }
222 }
223 } catch {
224 }
225 break
226 default:
227 break
228 }
229 }
230 }
231 }
232
233 func addNewCustomFields(rom fields: [[String: AnyObject]], and workspaseId: String) {
234 if self.openDatabase() {
235 for customField in fields {
236 let fieldType = customField["type"] as? String ?? ""
237 let fieldId = "\(customField["id"] as? Int ?? -1)"
238 let currency = customField["currency"] as? String ?? ""
239 let label = customField["label"] as? String ?? ""
240
241 do {
242 try database.executeUpdate("create table if not exists AllCustomFields(id text, currency text, label text, type text, workspaceId text)", values: nil)
243
244 try database.executeUpdate("delete from AllCustomFields where id='\(fieldId)' AND workspaceId='\(workspaseId)'", values: [])
245
246 //Save custom field in db
247 let query = "insert into AllCustomFields (id, currency, label, type, workspaceId) values (?, ?, ?, ?, ?);"
248 try database.executeUpdate(query, values: [fieldId, currency, label, fieldType, workspaseId])
249
250 //Save list to db
251 if fieldType == CustomFieldsType.list.rawValue {
252 if let subList = customField["subList"] as? [[String: AnyObject]] {
253 if subList.count > 0 {
254 self.saveSubListToDB(parentId: fieldId, list: subList, superId: fieldId, workspaceId: workspaseId)
255 }
256 }
257 }
258 } catch {
259 }
260 }
261 }
262 }
263
264 func addNewTags(from tags: [String], and workspaseId: String) {
265 if self.openDatabase() {
266 let siteQuery = "select * from Sites where workspaceId='\(workspaseId)'"
267
268 do {
269 let siteResults = try database.executeQuery(siteQuery, values: nil)
270 while siteResults.next() {
271 let tagsStr = siteResults.string(forColumn: "tags")!
272
273 var tagsArray = tagsStr.components(separatedBy: "|")
274
275 for addedTag in tags {
276 tagsArray.append(addedTag)
277 }
278 var newTagsStr = ""
279 for (index, tag) in tagsArray.enumerated() {
280 if index == tagsArray.count - 1 {
281 newTagsStr = "\(newTagsStr)\(tag)"
282 } else {
283 newTagsStr = "\(newTagsStr)\(tag)|"
284 }
285 }
286
287 try database.executeUpdate("UPDATE Sites SET tags=? WHERE workspaceId='\(workspaseId)'", values: [newTagsStr])
288 }
289 } catch {
290
291 }
292 }
293 }
294
295 func removeTags(from tags: [String], and workspaseId: String) {
296 if self.openDatabase() {
297 let siteQuery = "select * from Sites where workspaceId='\(workspaseId)'"
298 do {
299 let siteResults = try database.executeQuery(siteQuery, values: nil)
300 while siteResults.next() {
301 let tagsStr = siteResults.string(forColumn: "tags")!
302
303 var tagsArray = tagsStr.components(separatedBy: "|")
304
305 for removedTag in tags {
306 var index = -1
307
308 for (tempIndex, tag) in tagsArray.enumerated() {
309 if tag == removedTag {
310 index = tempIndex
311 break
312 }
313 }
314
315 if index != -1 && index < tagsArray.count {
316 tagsArray.remove(at: index)
317 }
318 }
319
320 let newTagsStr = tagsArray.joined(separator: "|")
321 try database.executeUpdate("UPDATE Sites SET tags=? WHERE workspaceId='\(workspaseId)'", values: [newTagsStr])
322
323 for removedTag in tags {
324 let issuesQuery = "select * from Issues where workspaceId='\(workspaseId)' AND tags like '%?%'"
325 do {
326 let issuesResults = try database.executeQuery(issuesQuery, values: [removedTag])
327 while issuesResults.next() {
328 let tagsStr = issuesResults.string(forColumn: "tags")!
329 let issueId = issuesResults.string(forColumn: "id")!
330
331 var tagsArray = tagsStr.components(separatedBy: ",")
332
333 var index = -1
334
335 for (tempIndex, tag) in tagsArray.enumerated() {
336 if tag == removedTag {
337 index = tempIndex
338 break
339 }
340 }
341
342 if index != -1 && index < tagsArray.count {
343 tagsArray.remove(at: index)
344 }
345
346 let newTagsStr = tagsArray.joined(separator: ",")
347 try database.executeUpdate("UPDATE Issues SET tags=? WHERE id='\(issueId)'", values: [newTagsStr])
348
349 }
350
351
352 let issuesQuery = "select * from ModifiedIssues where workspaceId='\(workspaseId)' AND tags like '%?%'"
353 do {
354 let issuesResults = try database.executeQuery(issuesQuery, values: [removedTag])
355 while issuesResults.next() {
356 let tagsStr = issuesResults.string(forColumn: "tags")!
357 let issueId = issuesResults.string(forColumn: "id")!
358
359 var tagsArray = tagsStr.components(separatedBy: ",")
360
361 var index = -1
362
363 for (tempIndex, tag) in tagsArray.enumerated() {
364 if tag == removedTag {
365 index = tempIndex
366 break
367 }
368 }
369
370 if index != -1 && index < tagsArray.count {
371 tagsArray.remove(at: index)
372 }
373
374 let newTagsStr = tagsArray.joined(separator: ",")
375 try database.executeUpdate("UPDATE ModifiedIssues SET tags=? WHERE id='\(issueId)'", values: [newTagsStr])
376
377 }
378 } catch {
379
380 }
381
382
383 } catch {
384
385 }
386 }
387
388 }
389 } catch {
390 }
391 }
392 }
393
394 func removeCustomFields(from ids: [Int], and workspaseId: String) {
395 if self.openDatabase() {
396 for id in ids {
397
398 if workspaseId == ModifiedIssueEntity.shared.workspaceId {
399 var ind = -1
400 for (index, cf) in ModifiedIssueEntity.shared.customFields.enumerated() {
401 if cf.fieldId == "\(id)" {
402 ind = index
403 }
404 }
405
406 if ind != -1 {
407 ModifiedIssueEntity.shared.customFields.remove(at: ind)
408 ind = -1
409 }
410 }
411
412 let query = "select * from AllCustomFields where id=? AND workspaceId='\(workspaseId)'"
413
414 do {
415 let results = try database.executeQuery(query, values: [id])
416
417 while results.next() {
418 let fieldType = results.string(forColumn: "type")!
419 if fieldType == CustomFieldsType.list.rawValue {
420 try database.executeUpdate("delete from SubList where superId=? AND workspaceId=?", values: [id, workspaseId])
421 }
422 }
423
424 try database.executeUpdate("delete from AllCustomFields where id=? AND workspaceId=?", values: [id, workspaseId])
425 try database.executeUpdate("delete from IssuesCustomFields where id=? AND workspaseId=?", values: [id, workspaseId])
426 } catch {
427 }
428 }
429 }
430 }
431
432
433
434 func clearDB(closure: @escaping (Bool) -> Void) {
435 if self.openDatabase() {
436 do {
437 try database.executeUpdate("delete from User", values: [])
438 try database.executeUpdate("delete from Shares", values: [])
439 try database.executeUpdate("delete from Workspaces", values: [])
440 try database.executeUpdate("delete from UnSavedSites", values: [])
441 try database.executeUpdate("delete from Accounts", values: [])
442 try database.executeUpdate("delete from DownloadedSitePlans", values: [])
443 try database.executeUpdate("delete from Sites", values: [])
444 try database.executeUpdate("delete from SitePlanTiles", values: [])
445
446 try database.executeUpdate("delete from AllCustomFields", values: [])
447 try database.executeUpdate("delete from SubList", values: [])
448 try database.executeUpdate("delete from Maps", values: [])
449 try database.executeUpdate("delete from IssuesCustomFields", values: [])
450 try database.executeUpdate("delete from UnSavedIssueVideos", values: [])
451 try database.executeUpdate("delete from UnSavedIssueImages", values: [])
452 try database.executeUpdate("delete from CustomFieldsPermission", values: [])
453
454// try database.executeUpdate("delete from Issues", values: [])
455// try database.executeUpdate("delete from DownloadedImages", values: [])
456// try database.executeUpdate("delete from IssueImages", values: [])
457// try database.executeUpdate("delete from IssueVideos", values: [])
458// try database.executeUpdate("delete from IssueComments", values: [])
459//
460// try database.executeUpdate("delete from IssueDocuments", values: [])
461// try database.executeUpdate("delete from ModifiedIssues", values: [])
462
463 UserDefaults.standard.removePersistentDomain(forName: Bundle.main.bundleIdentifier!)
464 UserDefaults.standard.synchronize()
465
466 closure(true)
467 }
468 catch {
469 closure(false)
470 }
471 } else {
472 closure(false)
473 }
474 }
475
476 //MARK: - Check if need create cf
477 func getCustomFieldsCount(for workspaceId: String) -> Int {
478 if (UserDefaults.standard.value(forKey: "cf_\(workspaceId)") != nil) {
479 return 100
480 } else {
481 return 0
482 }
483 }
484
485 func getSite(for workspaceId: String) -> SiteEntity? {
486 if self.openDatabase() {
487 let siteQuery = "select * from Sites where workspaceId='\(workspaceId)'"
488 do {
489 let siteResults = try database.executeQuery(siteQuery, values: nil)
490 while siteResults.next() {
491 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"))
492 }
493 } catch {
494 }
495 return nil
496 } else {
497 return nil
498 }
499 }