var systemDB;

function cv_1_0_1_1(tx) {
	tx.executeSql('DROP TABLE IF EXISTS cri_norme;');
	tx.executeSql('DROP TABLE IF EXISTS cri_fields;');
	tx.executeSql('DROP TABLE IF EXISTS cri_extensions;');
	tx.executeSql('DROP TABLE IF EXISTS cri_sales;');
	tx.executeSql('DROP TABLE IF EXISTS cri_calls;');
}
function error_1_0_1_1(error) {
	alert('Erreur lors de la conversion de 1.0 -> 1.1 : '+error.message);
	return true; // treat all errors as fatal
}
function success_1_0_1_1() {
	//alert("Database mise à jour vers version 1.1");
	createTables(systemDB);
}
/* ! Initialize the systemDB global variable. */
function initDB() {

	try {
		if (!window.openDatabase) {
			alert('Local database not supported');
			return;
		} else {
			var shortName = 'mobile30';
			var displayName = 'MOBILE30 Local Database';
			var maxSize = 2000000; // in bytes
			var myDB = openDatabase(shortName, "", displayName, maxSize);
			systemDB = myDB;
			var version = myDB.version;
			if (myDB.changeVersion && version=="1.0") {
				try {
					myDB.changeVersion("1.0", "1.1", cv_1_0_1_1, error_1_0_1_1, success_1_0_1_1);
				} catch (e) {
					alert('changeversion 1.0 -> 1.1 echoué');
				}			
			}
		}
	} catch (e) {
		// Error handling code goes here.
		if (e.code == 11) {
			// Version number mismatch.
			alert("Invalid database version.");
		} else {
			alert("Unknown error " + e + ".");
		}
		return;
	}

	// alert("Database is: "+myDB);
	if (systemDB!=undefined && (systemDB.version=='' || systemDB.version=="1.1")) {
		createTables(systemDB);
		return true;
	} else return false;
}

/* ! This creates the database tables. */
function createTables(db) {

	/*
	 * To wipe out the table (if you are still experimenting with schemas, for
	 * example), enable this block.
	 */
	/*db.transaction(function(tx) {
		tx.executeSql('DROP TABLE cri_norme;');
		tx.executeSql('DROP TABLE cri_fields;');
		tx.executeSql('DROP TABLE cri_extensions;');
		tx.executeSql('DROP TABLE cri_sales;');
		tx.executeSql('DROP TABLE cri_calls;');
	});*/

	db.transaction(function(tx) {
		tx.executeSql("CREATE TABLE IF NOT EXISTS cri_norme (" +
				"id_inter INTEGER NOT NULL PRIMARY KEY," +
				"id_partenaire INTEGER NOT NULL default 0," +
				"id_type INTEGER NOT NULL default 0," +
				"jour TEXT DEFAULT CURRENT_DATE ," +
				"debut TEXT," +
				"fin TEXT," +
				"support_diag TEXT," +
				"support_hl2 TEXT," +
				"support_nok TEXT," +
				"status INTEGER NOT NULL default 0," +
				"synchro INTEGER NOT NULL default 0," +
				"DATE_CREATION TEXT DEFAULT CURRENT_TIMESTAMP)"
				,[]
				, function(tx, rs) {}
				, function(tx, error) {
					alert("Impossible de créer la table cri_norme : " + error.message + ". Merci de recharger la page.");
					return;
					}
		);
		tx.executeSql("CREATE TABLE IF NOT EXISTS cri_fields (" +
				"id_inter INTEGER NOT NULL," +
				"number INTEGER NOT NULL default 0," +
				"value TEXT," +
				"DATE_CREATION TEXT DEFAULT CURRENT_TIMESTAMP)"
				,[]
				, function(tx, rs) {}
				, function(tx, error) {
					alert("Impossible de créer la table cri_fields : " + error.message + ". Merci de recharger la page.");
					return;
					}
		);
		tx.executeSql("CREATE TABLE IF NOT EXISTS cri_extensions (" +
				"id_inter INTEGER NOT NULL," +
				"ext_type INTEGER NOT NULL default 0," +
				"number INTEGER NOT NULL default 0," +
				"value TEXT," +
				"DATE_CREATION TEXT DEFAULT CURRENT_TIMESTAMP)"
				,[]
				, function(tx, rs) {}
				, function(tx, error) {
					alert("Impossible de créer la table cri_extensions : " + error.message + ". Merci de recharger la page.");
					return;
					}
		);
		tx.executeSql("CREATE TABLE IF NOT EXISTS cri_sales (" +
				"inter_type INTEGER NOT NULL default 0," +
				"id_partenaire INTEGER NOT NULL default 0," +
				"sale_type INTEGER NOT NULL default 0," +
				"id INTEGER NOT NULL default 0," +
				"libelle TEXT NOT NULL," +
				"prix REAL NOT NULL DEFAULT 0," +
				"DATE_CREATION TEXT DEFAULT CURRENT_TIMESTAMP)"
				,[]
				, function(tx, rs) {}
				, function(tx, error) {
					alert("Impossible de créer la table cri_sales : " + error.message + ". Merci de recharger la page.");
					return;
					}
		);
		tx.executeSql("CREATE TABLE IF NOT EXISTS cri_calls (" +
				"id_inter INTEGER NOT NULL PRIMARY KEY," +
				"client TEXT," +
				"diag TEXT," +
				"hl2 TEXT," +
				"nok TEXT," +
				"DATE_CREATION TEXT DEFAULT CURRENT_TIMESTAMP)"
				,[]
				, function(tx, rs) {}
				, function(tx, error) {
					alert("Impossible de créer la table cri_calls : " + error.message + ". Merci de recharger la page.");
					return;
					}
		);
	});

}
function db_insertCalls(id_inter, client, diag, hl2, nok) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('INSERT OR REPLACE INTO cri_calls (id_inter, client, diag, hl2, nok) VALUES (?, ?, ?, ?, ?)', [ id_inter, client, diag, hl2, nok ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_insertCalls :"+error.message);
		});
	});
}

function db_insertInter(id_inter, id_partenaire, int_add, support_diag, support_hl2, support_nok) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT id_inter FROM cri_norme WHERE id_inter = ? LIMIT 1', [ id_inter ],
			function(tx, rs) {
				if (rs.rows.length==0) {
					tx.executeSql('INSERT INTO cri_norme (id_inter, id_partenaire, id_type, support_diag, support_hl2, support_nok) VALUES (?, ?, ?, ?, ?, ?)', [ id_inter, id_partenaire, int_add , support_diag, support_hl2, support_nok ]);
				}
			},
			function(tx, error) {
				alert("db_insertInter :"+error.message);
			}
		);
	});
}
function db_clearInters() {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT id_inter FROM cri_norme WHERE jour < ?', [getMoins7()],
		function(tx, rs) {
		    for (var i = 0; i < rs.rows.length; i++) {
		       var tmp=rs.rows.item(i)['id_inter'];
		       db_clearFields(tmp);
		       db_clearExtensions(tmp);
		       db_clearCalls(tmp);
		       tx.executeSql('DELETE FROM cri_norme WHERE id_inter = ?', [ tmp ]);
		    };
		},
		function(tx, error) {
			alert("db_clearInters :"+error.message);
		});
	});
}
function db_clearFields(id_inter) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('DELETE FROM cri_fields WHERE id_inter = ?', [ id_inter ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_clearFields :"+error.message);
		});
	});
}
function db_clearExtensions(id_inter, ext_type) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('DELETE FROM cri_extensions WHERE id_inter = ? AND ext_type = ?', [ id_inter, ext_type ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_clearExtensions :"+error.message);
		});
	});
}
function db_clearCalls(id_inter, ext_type) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('DELETE FROM cri_calls WHERE id_inter = ?', [ id_inter ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_clearCalls :"+error.message);
		});
	});
}
function db_insertField(id_inter, number, value) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('INSERT OR REPLACE INTO cri_fields (id_inter, number, value) VALUES (?, ?, ?)', [ id_inter, number, value ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_insertField :"+error.message);
		});
	});
}
function db_insertExtension(id_inter, ext_type, number, value) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('INSERT INTO cri_extensions (id_inter, ext_type, number, value) VALUES (?, ?, ?, ?)', [ id_inter, ext_type, number, value ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_insertExtension :"+error.message);
		});
	});
}
function db_clearSale(inter_type, id_partenaire, sale_type) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('DELETE FROM cri_sales WHERE inter_type = ? AND id_partenaire = ? AND  sale_type = ?', [ inter_type, id_partenaire, sale_type ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_clearSale :"+error.message);
		});
	});
}
function db_insertSale(inter_type, id_partenaire, sale_type, id, libelle, prix) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('INSERT INTO cri_sales (inter_type, id_partenaire, sale_type, id, libelle, prix) VALUES (?, ?, ?, ?, ?, ?)', [ inter_type, id_partenaire, sale_type, id, libelle, prix ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_insertSale :"+error.message);
		});
	});
}
function db_updateStatus(inter, value) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('UPDATE cri_norme SET status = ? WHERE id_inter = ? and (status IS NULL or status < ?)', [ value, inter, value ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_updateStatus :"+error.message);
		});
	});
}
function db_updateInter(inter, field, value) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('UPDATE cri_norme SET '+field+' = ? WHERE id_inter = ?', [ value, inter ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_updateInter :"+error.message);
		});
	});
}
function db_updateTable(table, inter, field, value) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('UPDATE '+table+' SET '+field+' = ? WHERE id_inter = ?', [ value, inter ],
				function(tx, rs) {
			// …
		},
		function(tx, error) {
			alert("db_updateTable :"+error.message);
		});
	});
}
function db_getCall(inter, calltype, callback) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT '+calltype+' FROM cri_calls WHERE id_inter = ?', [ inter ],
			function(tx, rs) {
				if (rs.rows.length) {
					var result=rs.rows.item(0)[calltype];
					if(result!=null) callback(result,calltype);
				}
			},
			function(tx, error) {
				alert("db_getCall :"+error.message);
			}
		);
	});
}
function db_getCalls(inter, callback) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT * FROM cri_calls WHERE id_inter = ?', [ inter ],
			function(tx, rs) {
				if (rs.rows.length) {
					var row=rs.rows.item(0);
					//alert('test='+row['client']);
					callback(row['client'],row['diag'],row['hl2'],row['nok']);
				}
			},
			function(tx, error) {
				alert("db_getCalls :"+error.message);
			}
		);
	});
}
function db_getInformations(inter, callback) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT * FROM cri_norme WHERE id_inter = ?', [ inter ],
			function(tx, rs) {
				if (rs.rows.length) {
					var row=rs.rows.item(0);
					//alert('test='+row['client']);
					callback(row);
				}
			},
			function(tx, error) {
				alert("db_getInformations :"+error.message);
			}
		);
	});
}
function db_getFields(inter, callback) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT * FROM cri_fields WHERE id_inter = ?', [ inter ],
			function(tx, rs) {
				if (rs.rows.length) {
					callback(rs.rows);
				}
			},
			function(tx, error) {
				alert("db_getFields :"+error.message);
			}
		);
	});
}
function db_getExtensions(inter, ext_type, callback) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT * FROM cri_extensions WHERE id_inter = ? and ext_type = ?', [ inter, ext_type ],
			function(tx, rs) {
				callback(rs.rows);
			},
			function(tx, error) {
				alert("db_getExtensions :"+error.message);
			}
		);
	});
}
function db_checkSynchro(callback) {
	if (systemDB==undefined) return;
	systemDB.transaction(function(tx) {
		tx.executeSql('SELECT * FROM cri_norme WHERE status > 1 AND synchro = 0', [],
			function(tx, rs) {
				if (rs.rows.length) {
					callback(rs.rows);
				}
			},
			function(tx, error) {
				alert("db_checkSynchro :"+error.message);
			}
		);
	});
}