//
//  DB.js
//  SugLisSystem
//
//  Created by MrRight on 2012-11-22.
//  Copyright 2012 MrRight. All rights reserved.
//

var DATABASE_NAME = 'sls';
var mExeSQL = require('untils/ExeSQL');
var PubFun = require('untils/PubFun');

function DB(tableName) {
	this.schema = new Object();
	var pk = [];
	var field = [];
	this.tableName = tableName;
	//获取该表下的所有主键
	var sql = "PRAGMA table_info('" + tableName + "');"
	var tableInfo = mExeSQL.execSQL(sql, mExeSQL.TYPE_OBJECT);
	for (var i = 0; i < tableInfo.length; i++) {
		if (tableInfo[i]['pk'] == '1') {
			pk.push(tableInfo[i]['name'].toString().toLowerCase());
		}
		field.push(tableInfo[i]['name'].toString().toLowerCase());
		this[tableInfo[i]['name'].toString().toLowerCase()] = null;
	}
	this.pk = pk;
	this.field = field;
}

/**
 *get&set方法
 * */
DB.prototype.setSchema = function(schema) {
	for (var i = 0; i < this.field.length; i++) {
		this[this.field[i]] = schema[this.field[i]];
	}
}
DB.prototype.getSchema = function() {
	var schema = new Object();
	for (var i = 0; i < this.field.length; i++) {
		schema[this.field[i]] = this[this.field[i]];
	}
	return schema;
}
DB.prototype.getPk = function() {
	return this.pk;
}
/**
 * 根据主键查询
 * 所有主键均有值的情况下才能执行
 * @return 是否执行成功
 * */
DB.prototype.getInfo = function() {
	//缺少的主键值
	var pk_lacked = [];
	//查询条件
	var condition = " WHERE";
	for (var i = 0; i < this.pk.length; i++) {
		if (PubFun.isNull(this[this.pk[i]])) {
			pk_lacked.push(this.pk[i]);
		} else {
			condition += (i == 0 ? " " + this.pk[i] + " = '" + this[this.pk[i]] + "'" : " AND " + this.pk[i] + " = '" + this[this.pk[i]] + "'");
		}
	}
	if (pk_lacked.length > 0) {
		Ti.API.error("[DB]表" + this.tableName + "执行getInfo缺少主键值:" + pk_lacked);
		return false;
	}
	var reslutSet = mExeSQL.execSQL("SELECT * FROM " + this.tableName + condition, mExeSQL.TYPE_OBJECT);
	if (reslutSet == null || reslutSet.length == 0) {
		return false;
	} else {
		this.setSchema(reslutSet[0]);
		return true;
	}
}
/**
 * 根据当前schema来查询类似的schema集合
 * @return schema集合
 * */
DB.prototype.query = function() {
	//根据schema生成wherePart
	var condition = "";
	var schema = this.getSchema();
	for (var pmts in schema) {
		var preposition = " AND ";
		if (!PubFun.isNull(schema[pmts]) && schema[pmts] != '') {
			condition = condition + preposition + pmts + " = '" + schema[pmts] + "'";
		}
	}
	condition = condition.replace('AND', 'WHERE');
	sql = "SELECT * FROM " + this.tableName + condition;
	var reslutSet = mExeSQL.execSQL(sql, mExeSQL.TYPE_OBJECT);
	if (reslutSet == null) {
		reslutSet = [];
	}

	var schemaSet = [];
	for (var i = 0; i < reslutSet.length; i++) {
		var mDB = new DB(this.tableName);
		mDB.setSchema(reslutSet[i]);
		schemaSet.push(mDB);
	}
	return schemaSet;
}
/**
 * 执行特定语句的查询
 * @param sql:需要执行的sql
 * @return DB结果集
 * */
DB.prototype.executeQuery = function(sql) {
	var reslutSet = mExeSQL.execSQL(sql, mExeSQL.TYPE_OBJECT);
	if (reslutSet == null) {
		reslutSet = [];
	}
	var schemaSet = [];
	for (var i = 0; i < reslutSet.length; i++) {
		var mDB = new DB(this.tableName);
		mDB.setSchema(reslutSet[i]);
		schemaSet.push(mDB);
	}
	return schemaSet;
}
/**
 * 将schema插入数据库
 * @return 是否插入成功
 * */
DB.prototype.insert = function() {
	var valueMark = [];
	var values = []
	var mSchema = this.getSchema();
	for (var pmts in mSchema) {
		if (!PubFun.isNull(mSchema[pmts])) {
			valueMark.push(pmts);
			values.push("'" + mSchema[pmts] + "'");
		}
	}
	var insertsql = "INSERT INTO " + this.tableName + "(" + valueMark + ") VALUES(" + values + ")";
	Ti.API.warn('[DB]执行SQL脚本插入:' + insertsql);
	return mExeSQL.execUpdateSQL(insertsql);
}
/**
 * 根据schema生成条件
 * 将数据从数据库删除
 * @return 是否删除成功
 * */
DB.prototype.remove = function() {
	var condition = "";
	var mSchema = this.getSchema();
	for (var pmts in mSchema) {
		var preposition = " AND ";
		if (!PubFun.isNull(mSchema[pmts]) && mSchema[pmts] != '') {
			condition = condition + preposition + pmts + " = '" + mSchema[pmts] + "'";
		}
	}
	var deletesql = "DELETE  FROM " + this.tableName + condition.replace('AND', 'WHERE');
	return mExeSQL.execUpdateSQL(deletesql);
}
/**
 * 清除DB中的所有数据
 * */
DB.prototype.clear = function() {
	for (var i = 0; i < this.field.length; i++) {
		this[this.field[i]] = null;
	}
}

module.exports = DB;
