qnRrPAyc

1//
2//  db.swift
3//  pointOfSalesplswork
4//
5//  Created by Shahlin on 12/18/18.
6//  Copyright © 2018 student. All rights reserved.
7//
8
9import Foundation
10import SQLite3
11
12class DbHandler {
13    let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
14        .appendingPathComponent("Pos.sqlite")
15    
16    var db: OpaquePointer?
17    
18    init(){
19        if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
20            print("error opening database")
21        }
22        
23        createTables()
24    }
25    
26    func createTables(){
27        var products_table = "CREATE TABLE IF NOT EXISTS `products` (`product_id` INTEGER NOT NULL,`product_name` text,`product_desc` text,`product_manufacturer` text,`product_price` double,`product_stock` INTEGER,`product_barcode` text,`product_image` text,`created_date` text,`expiry_date` text)"
28        
29        var products_data = "INSERT INTO `products` (`product_id`, `product_name`,`product_desc`, `product_manufacturer`, `product_price`, `product_stock`, `product_barcode`, `product_image`, `created_date`, `expiry_date`) VALUES(1, 'Strepsils Orange', 'Soothing effective relief for sore throats', 'Reckitt Benckiser Healthcare International', 17.1, 44, '5000167019346', 'null', '2018-01-01 00:00:00', '2021-01-01 00:00:00'),(2, 'EarPods Headphone Plug', 'Apple EarPods are white in-ear headphones included with music players and smartphones designed and marketed by Apple Inc. They are designed to fit the ear while retaining a new design pushed by Apple alongside the iPod and iPod Touch products, with which they were sold together.', 'Apple', 579, 100, '190198107077', 'null', '2018-07-01 00:00:00', 'null'),(3, 'Snickers', 'Snickers is a brand name chocolate bar made by the American company Mars, Incorporated, consisting of nougat topped with caramel and peanuts that has been enrobed in milk chocolate.', 'Mars', 1.58, 186, '9780201379624', 'null', '2017-09-06 00:00:00', '2019-12-31 00:00:00'),(4, 'Lays MAX Mexican Chili', 'With flavors almost as rich as our history, we have a chip or crisp flavor guaranteed to bring a smile on your face. ', 'PepsiCo', 1.95, 197, '671860013624', 'null', '2018-08-01 00:00:00', '2019-08-01 00:00:00'),(5, 'iPhone XS Max', '6.5” Super Retina HD display\r\nDual 12MP wide-angle and telephoto cameras\r\n7MP TrueDepth camera\r\nA12 Bionic chip with next-generation Neural Engine', 'Apple', 4642, -3, '9501101530003', 'null', '2018-07-01 00:00:00', '2019-02-01 00:00:00'),(6, 'Ledger 10 x 8 inch', 'Manuscript book', 'Farook International Stationery', 10, 2, '6291019003654', 'null', '2018-11-13 00:00:00', 'null');"
30        
31        if sqlite3_exec(db, products_table, nil, nil, nil) != SQLITE_OK {
32            let errmsg = String(cString: sqlite3_errmsg(db)!)
33            print("error creating table: \(errmsg)")
34        }
35        
36        if sqlite3_exec(db, products_data, nil, nil, nil) != SQLITE_OK {
37            let errmsg = String(cString: sqlite3_errmsg(db)!)
38            print("error inserting to table: \(errmsg)")
39        }
40        
41    }
42    
43    func getAllProducts() -> [Product]{
44        var products = [Product]()
45        
46        //this is our select query
47        let queryString = "SELECT * FROM products"
48        
49        //statement pointer
50        var stmt:OpaquePointer?
51        
52        //preparing the query
53        if sqlite3_prepare(db, queryString, -1, &stmt, nil) != SQLITE_OK{
54            let errmsg = String(cString: sqlite3_errmsg(db)!)
55            print("error preparing insert: \(errmsg)")
56            
57            return [Product()]
58            
59        }
60        
61        //traversing through all the records
62        var i: Int = 0
63        while(sqlite3_step(stmt) == SQLITE_ROW){
64            if i == 6 {
65                return products
66            }
67            let product: Product = Product()
68            
69            product.product_id = Int(sqlite3_column_int(stmt, 0))
70            product.product_name = String(cString: sqlite3_column_text(stmt, 1))
71            product.product_desc = String(cString: sqlite3_column_text(stmt, 2))
72            product.product_manufacturer = String(cString: sqlite3_column_text(stmt, 3))
73            product.product_price = Double(sqlite3_column_double(stmt, 4))
74            product.product_stock = Int(sqlite3_column_int(stmt, 5))
75            product.product_barcode = String(cString: sqlite3_column_text(stmt, 6))
76            
77            //adding values to list
78            products.append(product)
79            
80            i += 1
81        }
82        
83        return products
84        
85    }
86    
87}