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

package com.usai.util;

import com.usai.apex.ApexTrackingApplication;
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 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 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,
				2);
		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))");

		// new table must create in onupgrade function too;

		dbHelper.addInitSQL("CREATE TABLE push_message ("
				+ "_id INTEGER PRIMARY KEY," + "s_id VARCHAR(20),"
				+ "e_id VARCHAR(20)," + "msgcount INTEGER,"
				+ "message VARCHAR(20)," + "h_time timestamp,"
				+ "create_time timestamp," + "user VARCHAR(20),"
				+ "read BOOLEAN)");

		dbHelper.addInitSQL("CREATE TABLE favorites ("
				+ "_id INTEGER PRIMARY KEY," + "name VARCHAR(20),"
				+ "params VARCHAR(20)," + "action VARCHAR(20),"
				+ "module_name VARCHAR(20)," + "detail_id VARCHAR(20),"
				+ "action_type VARCHAR(20)," + "create_time timestamp,"
				+ "user VARCHAR(20))");
		dbHelper.addInitSQL("CREATE TABLE history ("
				+ "_id INTEGER PRIMARY KEY," + "name VARCHAR(20),"
				+ "params VARCHAR(20)," + "action VARCHAR(20),"
				+ "module_name VARCHAR(20)," + "criterion VARCHAR(20),"
				+ "create_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"))

}