1ALdqrVs

1// Dynamic Function to create table
2function createTable(tableName, columns) {
3    myDB.transaction(function(transaction) {
4        // Create column arrays
5        var columnNames = [];
6        for (var i=0; i<columns.length; i++){
7            columnNames[i] = columns[i];
8        }
9        // Produce create query
10        var columnNamesQuery = "";
11        for (var i=0; i<columnNames.length; i++){
12            if(i==0){
13                columnNamesQuery = columnNamesQuery + columnNames[i];
14            } else{
15                columnNamesQuery = columnNamesQuery + ", " + columnNames[i];
16            }
17        }
18        // Create query
19        var query = 'CREATE TABLE IF NOT EXISTS ' + tableName + ' (' + columnNamesQuery + ')';
20        transaction.executeSql(query, [],
21        function(tx, result) {
22            //navAlert("Table " + tableName + " created successfully");
23        },
24        function(error) {
25             navAlert("Error occurred while creating table:" + JSON.stringify(error));
26        });
27    });
28}
29
30// Function Lists
31function createTableProjects() {
32    var columnNames = ["projectUID text PRIMARY KEY","projectName text","projectType text","projectLocation text","projectDescription text","projectStartDate text","projectStopDate text","optionalQ1 text","optionalQ2 text","optionalQ3 text","optionalQ4 text","optionalQ5 text","optionalQ6 text"];
33    createTable("tblprojects",columnNames);
34}
35	
36function insertIntoTable(table, columns, data) {
37    // Column Name Query String
38    var columnNamesQuery = "(";
39    for (var i=0; i<columns.length; i++){
40        if(i==0){
41            columnNamesQuery = columnNamesQuery + columns[i];
42        }else{
43            columnNamesQuery = columnNamesQuery + ", " + columns[i];
44        }
45    }
46    columnNamesQuery = columnNamesQuery + ")";
47
48    // Column Name Value String
49    var query = "";
50    myDB.transaction(function(transaction) {
51        var primary_id = "";
52        for(var i=0; i<data.length; i++){
53            var row = data[i];
54            // Create insert query
55            var columnValuesArray = [];
56            var columnValuesQuery = "(";
57            for(var j=0; j<columns.length; j++){
58                var val = row[columns[j]];
59                if(j==0){
60                    primary_id = val;
61                }
62                if(val === undefined || val == null){
63                    val = "";
64                }
65                columnValuesArray[j] = val;
66                if(j<columns.length-1){
67                    columnValuesQuery = columnValuesQuery + "?,";
68                } else if(j==columns.length-1){
69                    columnValuesQuery = columnValuesQuery + "?)";
70                }
71            }
72
73            // Insert query
74            var project_query = 'INSERT OR REPLACE INTO ' + table + columnNamesQuery + ' VALUES ' + columnValuesQuery + ";";
75            transaction.executeSql(project_query, columnValuesArray,
76                function(tx,result){
77                    //navAlert("Data inserted/replaced in table: " + table);
78                },
79                function(error) {
80                    navAlert("Error: " + return_val + " " + project_query);
81                }
82            );
83        }
84    });
85}
86
87// Inserted in tablet on download from server
88function insertTblProjects(dataArray){
89    var columns = ["projectUID","projectName","projectType","projectLocation","projectDescription","projectStartDate","projectStopDate","optionalQ1","optionalQ2","optionalQ3","optionalQ4","optionalQ5","optionalQ6"];
90    insertIntoTable("tblprojects",columns, dataArray);
91}