erpsuiteandroid/Apex/src/com/usai/util/dbUtil.java

package com.usai.util;

import com.usai.apex.ApexTrackingApplication;
import com.usai.apex.ResultActivity;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.net.Uri;
import android.util.Log;

public class dbUtil
{

	private static final String[] FIELD_ID = { "_id" };

	// public static final String ZONECODE
	// public static final int FILTER_TYPE_MSG = 0;
	// public static final int FILTER_TYPE_BOTH = 1;
	// public static final int FILTER_TYPE_CALL = 2;

	// public static final int RECORD_TYPE_CALL = 2;
	// public static final int RECORD_TYPE_MSG = 1;
	//
	// public static final int NUMBER_TYPE_NULL = 0;
	// public static final int NUMBER_TYPE_NORMAL = 1;
	// public static final int NUMBER_TYPE_BLACK = 2;
	// public static final int NUMBER_TYPE_WHITE = 3;
	// public static final int NUMBER_TYPE_GRAY = 4;
	// public static final int NUMBER_TYPE_UNKNOWN = 7;

	// public static final int NUMBER_TYPE_CONTACT = 5;
	// public static final int NUMBER_TYPE_SUSPECT = 6;

	// public static final int FILTER_SOURCE_BLACKLIST = 0;
	// public static final int FILTER_SOURCE_SINGLERING = 1;
	// public static final int FILTER_SOURCE_KEYWORDS = 2;
	// public static final int FILTER_SOURCE_NONUMBER = 3;

	// public static void AddNumberToBlacklist(Context context, String Number) {
	// SQLiteDatabase db;
	// db = dbUtil.OpenDB(context, null, true);
	// boolean bexist = isRecordExist(db, "user_numberlist", "number = '"
	// + Number + "'");
	// if (bexist == false) {
	// ContentValues cv = new ContentValues();
	// cv.put("number", Number);
	// cv.put("type", dbUtil.NUMBER_TYPE_BLACK);
	// cv.put("burntype", 3);
	// cv.put("cimm", PhoneNumberUtils.toCallerIDMinMatch(Number));
	// db.insert("user_numberlist", null, cv);
	// }
	// dbUtil.CloseDB(db);
	// }

	// public static int KeywordsFilter(Context context, String Msg) {
	// int ret = -1;
	// boolean bKeywordsFilter = context.getSharedPreferences("PhoneAsstPref",
	// 0).getBoolean("KeywordsFilterPref", false);
	//
	// // = context.getSharedPreferences("PhoneAsstPref", 0)
	// // .getBoolean("BlockUnknownNumber", false);
	// if (bKeywordsFilter == false)
	// return ret;
	// SQLiteDatabase db = dbUtil.OpenDB(context, null, true);
	//
	// Cursor cursor = null;
	// try {
	//
	// cursor = db.query("user_keywords", new String[] { "_id", "words" },
	// null, null, null, null, "level desc", null);
	//
	// while (cursor.moveToNext()) {
	// String keywords = cursor.getString(1);
	// String[] spliter = keywords.split(" ");
	//
	// if (spliter.length == 0)
	// continue;
	//
	// int ifound = spliter.length; // 鍏抽敭瀛椾釜鏁� // for (int idx = 0; idx <
	// spliter.length; idx++) {
	// // 鍏煎浠ュ墠鐗堟湰鍏抽敭瀛楁湁澶氱┖鏍兼儏鍐碉紱
	// if (spliter[idx].equals("")) {
	// ifound--; // 鎺掗櫎璇绠楃殑绌烘暟閲� // continue;
	// }
	//
	// if (Msg.indexOf(spliter[idx]) != -1) {
	// ifound--;
	// }
	// }
	// if (ifound == 0) {
	// int id = cursor.getInt(0);
	// db.execSQL("update " + "user_keywords"
	// + " set level = level+1 where _id=" + id);
	// ret = id;
	// break;// break while
	// }
	//
	// }
	// } catch (SQLiteException e) {
	// dbgUtil.Log(Log.ERROR, "sql_err", e.toString());
	// } finally {
	// dbUtil.CloseCursor(cursor);
	// dbUtil.CloseDB(db);
	// }
	// return ret;
	// }

	// public static final int NUMBER_TYPE_UNKNOWN = 5;//no number ,hide number
	// ect;
	public static boolean isContentExist(Context context, Uri uri, String where)
	{
		boolean ret;
		Cursor cursor = context.getContentResolver().query(uri, null, where,
				null, null);
		dbgUtil.dbgCursorInfo(cursor, context);
		if (cursor.moveToNext())
			ret = true;
		else
			ret = false;

		dbUtil.CloseCursor(cursor);
		return ret;
	}

	// public static int query_NumberType(SQLiteDatabase db, String number) {
	// String cimm = PhoneNumberUtils.toCallerIDMinMatch(number);
	// int ret = -1;
	// Cursor cursor = db.query("user_numberlist", new String[] { "type",
	// "number" }, "cimm = '" + cimm + "'", null, null, null, null);
	// while (cursor.moveToNext()) {
	// if (PhoneNumberUtils.compare(number, cursor.getString(1))) {
	// dbgUtil.Log(
	// Log.ERROR,
	// "QUERY FROM user_numberlist",
	// number + "=" + cursor.getString(1) + "type="
	// + cursor.getInt(0));
	// ret = cursor.getInt(0);
	// break;
	// }
	// }
	// CloseCursor(cursor);
	// return ret;
	// }

	// static boolean isContact(Context context, String number) {
	// // String cimm=PhoneNumberUtils.toCallerIDMinMatch(number);
	// return dbUtil.isContentExist(context,
	// // ContactsContract.CommonDataKinds.Phone.CONTENT_URI,
	// Uri.withAppendedPath(
	// ContactsContract.PhoneLookup.CONTENT_FILTER_URI,
	// Uri.encode(number)),
	// // ContactsContract.CommonDataKinds.Phone.NUMBER + "= '" +
	// // number+ "'");
	// /* "min_match = '" + cimm+ "'" */null);
	// }

	// static boolean isSuspect(SQLiteDatabase db, String number) {
	// return dbUtil.isRecordExist(db, "user_suspectlist", "number = '"
	// + number + "'");
	// }

	// public static int get_CallNumberType_old(Context context,
	// String incomingNumber)
	// {
	// int type = NUMBER_TYPE_NULL;
	// SQLiteDatabase db = dbUtil.OpenDB(context, null, true);
	//
	// boolean bBlockUnknown = context
	// .getSharedPreferences("PhoneAsstPref", 0).getBoolean(
	// "BlockUnknownNumber", false);
	// boolean bignorcontact = context
	// .getSharedPreferences("PhoneAsstPref", 0).getBoolean(
	// "IgnoreContact", false);

	// if ((incomingNumber == null || incomingNumber.equals("")))
	// if (bBlockUnknown)
	// type = NUMBER_TYPE_BLACK;
	// else
	// type = NUMBER_TYPE_NORMAL;
	// else
	// {
	// type = query_NumberType(db, incomingNumber);
	// if (type == NUMBER_TYPE_WHITE)
	// {
	//
	// }
	// else
	// if (bignorcontact && isContact(context, incomingNumber))
	// {
	// type = NUMBER_TYPE_CONTACT;
	// dbgUtil.Log(Log.ERROR,null, "number type contact");
	// }
	// else
	// if (type == NUMBER_TYPE_BLACK)
	// {
	// }
	// else
	// if (dbUtil.isRecordExist(db, "user_suspectlist",
	// "number = '" + incomingNumber + "'"))
	// {
	// type = NUMBER_TYPE_SUSPECT;
	// }
	// else
	// {
	// type = NUMBER_TYPE_NORMAL;
	//
	// }
	// }
	// dbUtil.CloseDB(db);
	//
	// dbgUtil.Log(Log.ERROR,"get_CallNumberType",
	// "number="+incomingNumber+" type="+type);
	// return type;
	//
	// }

	// public static NumberInfo get_NumberInfo(Context context,
	// String incomingNumber)
	// {
	// NumberInfo numberInfo = new NumberInfo();
	// numberInfo.incomingNumber = incomingNumber;
	// SQLiteDatabase db = dbUtil.OpenDB(context, null, true);
	//
	// // boolean bBlockUnknown = context
	// // .getSharedPreferences("PhoneAsstPref", 0).getBoolean(
	// // "BlockUnknownNumber", false);
	// // boolean bignorcontact = context
	// // .getSharedPreferences("PhoneAsstPref", 0).getBoolean(
	// // "IgnoreContact", false);
	//
	// // int type = NUMBER_TYPE_NULL;
	// if (incomingNumber == null || incomingNumber.length() < 3)
	// {
	// numberInfo.BaseType = NUMBER_TYPE_UNKNOWN;
	// }
	// else
	// {
	// numberInfo.BaseType = query_NumberType(db, incomingNumber);
	// if (numberInfo.BaseType == -1)// not in user_numberlist;
	// {
	// numberInfo.BaseType = NUMBER_TYPE_NORMAL;
	//
	// // else
	// // if (isSuspect(db, incomingNumber))
	// // {
	// // type = NUMBER_TYPE_SUSPECT;
	// //
	// // }
	// // else
	// // {
	// // type = NUMBER_TYPE_NORMAL;
	// // }
	// }
	// if (isContact(context, incomingNumber))
	// {
	// numberInfo.isContact = true;
	// // type = NUMBER_TYPE_CONTACT;
	// }
	// if (isSuspect(db, incomingNumber))
	// {
	// numberInfo.isSuspect = true;
	//
	// }
	//
	// // else
	// // if (type == NUMBER_TYPE_BLACK)
	// // {
	// // if (isContact(context, incomingNumber))
	// // {
	// // type = NUMBER_TYPE_CONTACT;
	// // }
	// // }
	// }
	// dbUtil.CloseDB(db);
	//
	// dbgUtil.Log(Log.ERROR, "get_NumberInfo", "number=" + incomingNumber
	// + " type=" + numberInfo.BaseType);
	// return numberInfo;
	//
	// }

	// public static int get_SmsNumberType(Context context, String
	// incomingNumber)
	// {
	// SQLiteDatabase db = dbUtil.OpenDB(context, null, false);
	//
	// boolean bBlockUnknown = context
	// .getSharedPreferences("PhoneAsstPref", 0).getBoolean(
	// "BlockUnknownNumber", false);
	// boolean bignorcontact = context
	// .getSharedPreferences("PhoneAsstPref", 0).getBoolean(
	// "IgnoreContact", false);
	//
	// int type = NUMBER_TYPE_NULL;
	// if ((incomingNumber == null || incomingNumber.equals("")))
	// if (bBlockUnknown)
	// type = NUMBER_TYPE_BLACK;
	// else
	// type = NUMBER_TYPE_NORMAL;
	// else
	// {
	// type = query_NumberType(db, incomingNumber);
	// if (type == NUMBER_TYPE_WHITE)
	// {
	//
	// }
	// else
	// if (bignorcontact && isContact(context, incomingNumber))
	// {
	// type = NUMBER_TYPE_CONTACT;
	// dbgUtil.Log(Log.ERROR, null, "number type contact");
	// }
	// else
	// if (type == NUMBER_TYPE_BLACK)
	// {
	// }
	// else
	// {
	// type = NUMBER_TYPE_NORMAL;
	//
	// }
	// }
	// dbUtil.CloseDB(db);
	//
	// return type;
	//
	// }

	public static void CloseDB(SQLiteDatabase db)
	{
		if (db == null)
			return;
		if (db.isOpen() == false)
			return;
		db.close();
		return;

	}

	public static void CloseCursor(Cursor c)
	{
		if (c == null)
			return;
		if (c.isClosed())
			return;
		c.close();
		return;

	}

	public static SQLiteDatabase OpenDB(Context context, CursorFactory factory,
			boolean writable)
	{

		SqlOpenHelper dbHelper = new SqlOpenHelper(context, "apex.db", factory,
				1);
		dbHelper.addInitSQL("create table users (" + "_id INTEGER PRIMARY KEY,"
				+ "name varchar(20)," + "pass varchar(20))");

		// dbHelper.addInitSQL("create table scan_records ("
		// + "_id INTEGER PRIMARY KEY," + "submit BOOLEAN DEFAULT (0),"
		// + "picker VARCHAR(20)," + "pid varchar(20))");

		/*
		 * 
		 * name : field name aname: alias name for display field_type:
		 * int,bool,varchar... function_name: Ocean Booking , Ocean B/L Info.,
		 * ... behavior: search / result priority: display order show: whether
		 * show this field abandon: true/false , uses for update auth_info user:
		 * record owner
		 */
		dbHelper.addInitSQL("CREATE TABLE fields_info ("
				+ "_id INTEGER PRIMARY KEY," + "name VARCHAR(20),"
				+ "aname VARCHAR(20)," + "field_type INTEGER,"
				+ "function_name VARCHAR(20)," + "behavior INTEGER,"
				+ "priority INTEGER," + "show BOOLEAN," + "abandon BOOLEAN,"
				+ "user VARCHAR(20))");

		dbHelper.addInitSQL("CREATE TABLE actions_info ("
				+ "_id INTEGER PRIMARY KEY," + "name VARCHAR(20),"
				+ "function_name VARCHAR(20)," + "priority INTEGER,"
				+ "abandon BOOLEAN," + "user VARCHAR(20))");
		dbHelper.addInitSQL("CREATE TABLE search_history ("
				+ "_id INTEGER PRIMARY KEY," + "h_val VARCHAR(20),"
				+ "h_field VARCHAR(20)," + "h_time timestamp,"
				+ "user VARCHAR(20))");
		// dbHelper.addInitSQL("create table pics (" +
		// "_id INTEGER PRIMARY KEY,"
		// + "picker varchar(20)," + "err_code INTEGER ,"
		// + "pid varchar(20)," + "local_path varchar(256))");

		SQLiteDatabase db = dbHelper.OpenDB(writable);
		return db;
	}

	public static void removeRecords(SQLiteDatabase db, String table,
			String whereclause)
	{
		db.execSQL("delete from " + table + " where " + whereclause);
	}

	public static long addRecordsFromCursor(SQLiteDatabase db,
			String tablename, Cursor source, String[] from, String[] to,
			boolean dumpPrimaryKey, boolean dumpOneRecord)
	{
		int ret = -1;
		ContentValues cv = new ContentValues();

		if (from == null)
			from = source.getColumnNames();

		if (to == null)
			to = source.getColumnNames();

		if (from.length != to.length)// 瀛楁鏁颁笉鍖归厤
			return ret;

		while (source.moveToNext())
		{

			for (int i = 0; i < from.length; i++)
			{
				if (from[i].equals("_id"))
				{
					if (dumpPrimaryKey)
						cv.put(to[i], source.getInt(i));
				}
				else
				{
					cv.put(to[i], source.getString(i));
				}

			}
			db.insert(tablename, null, cv);

			if (dumpOneRecord)
				break;
		}

		ret = 1;
		return ret;

	}

	// public static long addRecord(SQLiteDatabase db, String table,
	// ContentValues record)
	// {
	// long ret = -1;
	// try
	// {
	// ret = db.insert(table, null, record);
	// }
	// catch (SQLiteException e)
	// {
	// dbgUtil.Log(Log.ERROR,"sql_err", e.toString());
	//
	// }
	// return ret;
	// }

	public static boolean isTableEmpty(SQLiteDatabase db, String table)
	{
		boolean ret = true;

		Cursor cursor = null;
		try
		{

			cursor = db.query(table, FIELD_ID, null, null, null, null, null,
					"1");
			if (cursor.moveToNext())
			{
				// cursor.close();
				ret = false;
			}
			else
				ret = true;
		}
		catch (SQLiteException e)
		{
			Log.e("sql_err", e.toString());
		}
		finally
		{
			dbUtil.CloseCursor(cursor);
		}
		return ret;
	}

	public static boolean isRecordExist(SQLiteDatabase db, String table,
			String where)
	{
		boolean ret = false;
		Cursor cursor = null;
		try
		{

			cursor = db.query(table, FIELD_ID, where, null, null, null, null,
					null);
			if (cursor.moveToNext())
				ret = true;
			else
				ret = false;
		}
		catch (SQLiteException e)
		{
			Log.e("sql_err", e.toString());

		}
		finally
		{

			dbUtil.CloseCursor(cursor);
		}
		return ret;

	}

	public static String get_fields(String user, String module)
	{
		SQLiteDatabase db = OpenDB(ApexTrackingApplication.get_instance()
				.getApplicationContext(), null, false);
		Cursor cursor = db.query("fields_info", new String[] { "name" },
				"function_name='" + module + "' and user='" + user
						+ "' and behavior=" + Network.BEHAVIOR_RESULT, null,
				null, null, null, null);
		String ret = "";
		while (cursor.moveToNext())
		{
			ret += cursor.getString(0);
			ret += ",";

		}
		ret = ret.substring(0, ret.length() - 1);
		CloseCursor(cursor);
		CloseDB(db);
		return ret;
	}
	public static int get_count(SQLiteDatabase db,String table,String where)
	{
		int ret=0;
		Cursor cursor = db.query(table,
				new String[] { "count(*)" }, where,
				null, null, null, null, null);
		if (cursor.moveToNext()) {
			ret = cursor.getInt(0);
		}

		dbUtil.CloseCursor(cursor);
		return ret;
	}

	public static int get_recordid(SQLiteDatabase db, String table, String where )
	{
		return get_recordid(db,table,where,null);
	}
	public static int get_recordid(SQLiteDatabase db, String table, String where ,String Orderby)
	{
		int ret = -1;
		Cursor cursor = null;
		try
		{

			cursor = db.query(table, FIELD_ID, where, null, null, null, Orderby,
					null);
			if (cursor.moveToNext())
				ret = cursor.getInt(0);
			else
				ret = -1;
		}
		catch (SQLiteException e)
		{
			Log.e("sql_err", e.toString());

		}
		finally
		{

			dbUtil.CloseCursor(cursor);
		}
		return ret;

	}

	// if(isTableEmpty(db,"block_records"))

}