package com.clubminibar.cmbroom;

import android.content.Context;
import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class SQLiteData extends SQLiteOpenHelper {

	//DB
	private static final String CMB_DB = "CMBLocal";
	private static final int DB_VER = 24;
	
	//Tables
	private static final String ROOM_LIST = "Room";
	private static final String ROOM_ACTION = "RoomAction";
	private static final String ROOM_PRIORDND = "RoomPriorDND";
	private static final String ITEM_LIST = "Item";
	private static final String LOCAL_ITEM_LIST = "LocalItem";
	private static final String LOCAL_RESTOCK = "LocalRestock";
	private static final String LOCAL_ACTION = "LocalAction";
	private static final String LOCAL_REVERSAL = "LocalReversal";
	private static final String LOCAL_SALE = "LocalSale";
	private static final String LOCAL_EXPIRED = "LocalExpired";
	private static final String LOCAL_DAMAGED = "LocalDamaged";
	//Columns
	private static final String ROOM_ID = "RoomId";
	private static final String ROOM_NUMBER = "RoomNumber";
	private static final String ROOM_FLOOR = "RoomFloor";
	private static final String ROOM_STATUS = "RoomStatus";
	private static final String ROOM_CHECK_TIME = "RoomCheckTime";
	
	private static final String ITEM_LOCAL_ID = "ItemLocalId";
	private static final String ITEM_ACTION_ID = "ItemActionId";
	private static final String ITEM_RESTOCK_ID = "ItemRestockId";
	private static final String ITEM_ID = "ItemId";
	private static final String ITEM_DESC = "ItemDescription";
	private static final String ITEM_BEVERAGE = "ItemBeverage";
	private static final String ITEM_SORT = "ItemSort";
	private static final String ITEM_QTY = "ItemQty";
	private static final String ITEM_ACTION= "ItemAction";
	private static final String ROW_SENT = "RowSent";
	private static final String ROW_RECEIVED = "RowReceived";
	private static final String ROW_CREATED = "RowCreated";
	private static final String NO_RESTOCK = "NoRestock";
	
	
	SQLiteData(Context context){
		super(context,CMB_DB,null,DB_VER);
		
	}
	@Override
	public void onCreate(SQLiteDatabase db){
		db.execSQL("DROP TABLE IF EXISTS "+ROOM_LIST);
		db.execSQL("DROP TABLE IF EXISTS "+ROOM_ACTION);
		db.execSQL("DROP TABLE IF EXISTS "+ROOM_PRIORDND);
		db.execSQL("DROP TABLE IF EXISTS "+ITEM_LIST);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_ITEM_LIST);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_RESTOCK);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_ACTION);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_SALE);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_EXPIRED);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_DAMAGED);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_REVERSAL);
		
		db.execSQL("CREATE TABLE "+ROOM_LIST+"("+ROOM_ID+" INTEGER PRIMARY KEY,"+ROOM_NUMBER+" VARCHAR(6),"+ROOM_FLOOR+" INTEGER);");
		db.execSQL("CREATE TABLE "+ROOM_ACTION+"("+ROOM_ID+" INTEGER PRIMARY KEY,"+ROOM_STATUS+" INTEGER,"+ROOM_CHECK_TIME+" INTEGER,"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+" INTEGER);");
		db.execSQL("CREATE TABLE "+ROOM_PRIORDND+"("+ROOM_ID+" INTEGER PRIMARY KEY);");
		db.execSQL("CREATE TABLE "+ITEM_LIST+"("+ITEM_ID+" INTEGER PRIMARY KEY,"+ITEM_DESC+" VARCHAR(24),"+ITEM_BEVERAGE+" INTEGER,"+ITEM_SORT+" INTEGER,"+ITEM_QTY+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_ITEM_LIST+"("+ITEM_LOCAL_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+ITEM_ID+" INTEGER,"+ITEM_SORT+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_RESTOCK+"("+ITEM_RESTOCK_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+ITEM_LOCAL_ID+" INTEGER,"+ROOM_ID+" INTEGER,"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+"INTEGER,"+ROW_CREATED+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_ACTION+"("+ITEM_ACTION_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+ITEM_LOCAL_ID+" INTEGER,"+ROOM_ID+" INTEGER,"+ITEM_ACTION+" INTEGER,"+NO_RESTOCK+" INTEGER"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+" INTEGER,"+ROW_CREATED+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_REVERSAL+"("+ITEM_ACTION_ID+" INTEGER PRIMARY KEY,"+ITEM_LOCAL_ID+" INTEGER,"+ROOM_ID+" INTEGER,"+ITEM_ACTION+" INTEGER,"+NO_RESTOCK+" INTEGER"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+" INTEGER,"+ROW_CREATED+" INTEGER);");
		
	}
	@Override
	public void onUpgrade(SQLiteDatabase db,int oldver,int newver){
		db.execSQL("DROP TABLE IF EXISTS "+ROOM_LIST);
		db.execSQL("DROP TABLE IF EXISTS "+ROOM_ACTION);
		db.execSQL("DROP TABLE IF EXISTS "+ROOM_PRIORDND);
		db.execSQL("DROP TABLE IF EXISTS "+ITEM_LIST);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_ITEM_LIST);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_RESTOCK);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_ACTION);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_SALE);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_EXPIRED);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_DAMAGED);
		db.execSQL("DROP TABLE IF EXISTS "+LOCAL_REVERSAL);
		
		db.execSQL("CREATE TABLE "+ROOM_LIST+"("+ROOM_ID+" INTEGER PRIMARY KEY,"+ROOM_NUMBER+" VARCHAR(6),"+ROOM_FLOOR+" INTEGER);");
		db.execSQL("CREATE TABLE "+ROOM_ACTION+"("+ROOM_ID+" INTEGER PRIMARY KEY,"+ROOM_STATUS+" INTEGER,"+ROOM_CHECK_TIME+" INTEGER,"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+" INTEGER);");
		db.execSQL("CREATE TABLE "+ROOM_PRIORDND+"("+ROOM_ID+" INTEGER PRIMARY KEY);");
		db.execSQL("CREATE TABLE "+ITEM_LIST+"("+ITEM_ID+" INTEGER PRIMARY KEY,"+ITEM_DESC+" VARCHAR(24),"+ITEM_BEVERAGE+" INTEGER,"+ITEM_SORT+" INTEGER,"+ITEM_QTY+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_ITEM_LIST+"("+ITEM_LOCAL_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+ITEM_ID+" INTEGER,"+ITEM_SORT+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_RESTOCK+"("+ITEM_RESTOCK_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+ITEM_LOCAL_ID+" INTEGER,"+ROOM_ID+" INTEGER,"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+"INTEGER,"+ROW_CREATED+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_ACTION+"("+ITEM_ACTION_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+ITEM_LOCAL_ID+" INTEGER,"+ROOM_ID+" INTEGER,"+ITEM_ACTION+" INTEGER,"+NO_RESTOCK+" INTEGER"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+" INTEGER,"+ROW_CREATED+" INTEGER);");
		db.execSQL("CREATE TABLE "+LOCAL_REVERSAL+"("+ITEM_ACTION_ID+" INTEGER PRIMARY KEY,"+ITEM_LOCAL_ID+" INTEGER,"+ROOM_ID+" INTEGER,"+ITEM_ACTION+" INTEGER,"+NO_RESTOCK+" INTEGER"+ROW_SENT+" INTEGER,"+ROW_RECEIVED+" INTEGER,"+ROW_CREATED+" INTEGER);");
		
		
	}
	
	public void delRooms(){
		final SQLiteDatabase db = getWritableDatabase(); 
		db.execSQL("DELETE FROM "+ROOM_LIST+";");
		db.execSQL("DELETE FROM "+ROOM_ACTION+";");
		
	}
	public void AddRoom(final Integer id,final String rmnum,final Integer floornum){
		final SQLiteDatabase db = getWritableDatabase(); 
		db.execSQL("INSERT INTO "+ROOM_LIST+"("+ROOM_ID+","+ROOM_NUMBER+","+ROOM_FLOOR+") VALUES("+id.toString()+","+rmnum+","+floornum.toString()+");");
		
	}	
	public void AddRoomFromPrefs(final String id,final String rmnum,final String floornum){
		final SQLiteDatabase db = getWritableDatabase(); 
		db.execSQL("INSERT INTO "+ROOM_LIST+"("+ROOM_ID+","+ROOM_NUMBER+","+ROOM_FLOOR+") VALUES("+id.toString()+",\'"+rmnum.toString()+"\',"+floornum.toString()+");");
		
	}	

	public void delProducts(){

		final SQLiteDatabase db = getWritableDatabase(); 
		db.execSQL("DELETE FROM "+LOCAL_ITEM_LIST+";");
		db.execSQL("DELETE FROM "+ITEM_LIST+";");
		db.execSQL("DELETE FROM "+LOCAL_RESTOCK+";");
		db.execSQL("DELETE FROM "+LOCAL_ACTION+";");
		
	}
	
	public void AddProduct(final Integer id,final String itemdesc,final Integer isbev,final Integer isort,final Integer qty){
		final SQLiteDatabase db = getWritableDatabase(); 
		db.execSQL("INSERT INTO "+ITEM_LIST+"("+ITEM_ID+","+ITEM_DESC+","+ITEM_BEVERAGE+","+ITEM_SORT+","+ITEM_QTY+") VALUES("+id.toString()+","+isbev.toString()+","+isort.toString()+","+qty.toString()+");");
		db.execSQL("INSERT INTO "+LOCAL_ITEM_LIST+"("+ITEM_ID+","+ITEM_SORT+") VALUES("+id.toString()+","+isort.toString()+");");
		if (qty>1){
			for (int i=0;i<(qty-1);i++)
				db.execSQL("INSERT INTO "+LOCAL_ITEM_LIST+"("+ITEM_ID+","+ITEM_SORT+") VALUES("+id.toString()+","+isort.toString()+");");
		}
		
	}
	public void AddProductFromPrefs(final String id,final String itemdesc,final String isbev,final String isort,final String qty){
		final SQLiteDatabase db = getWritableDatabase(); 
		db.execSQL("INSERT INTO "+ITEM_LIST+"("+ITEM_ID+","+ITEM_DESC+","+ITEM_BEVERAGE+","+ITEM_SORT+","+ITEM_QTY+") VALUES("+id.toString()+",\'"+itemdesc.toString()+"\',"+isbev.toString()+","+isort.toString()+","+qty.toString()+");");
		db.execSQL("INSERT INTO "+LOCAL_ITEM_LIST+"("+ITEM_ID+","+ITEM_SORT+") VALUES("+id.toString()+","+isort.toString()+");");
		if (Integer.parseInt(qty)>1){
			int j=Integer.parseInt(qty)-1;
			for (int i=0;i<j;i++)
				db.execSQL("INSERT INTO "+LOCAL_ITEM_LIST+"("+ITEM_ID+","+ITEM_SORT+") VALUES("+id.toString()+","+isort.toString()+");");
		}
		
	}	
	public int getProductCount(){
		final SQLiteDatabase db = getReadableDatabase();
		Cursor c;
		int cnt;
		c = db.rawQuery("select count(*) from "+LOCAL_ITEM_LIST,null);
		if (c.getCount()>0){
			if(c.moveToFirst()){
				cnt=c.getInt(0);
				c.close();
			}
			else
				cnt=-1;
		}
		else
			cnt=0;
		return(cnt);
	}
	public int getRoomCount(){
		final SQLiteDatabase db = getReadableDatabase();
		Cursor c;
		int cnt;
		c = db.rawQuery("select count(*) from "+ROOM_LIST,null);
		
		if (c.getCount()>0){
			if(c.moveToFirst()){
				cnt=c.getInt(0);
				c.close();
			}
			else
				cnt=-1;
		}
		else
			cnt=0;
		return(cnt);
	}

	public Cursor getProducts(){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select "+ITEM_LOCAL_ID+","+ITEM_DESC+","+ITEM_BEVERAGE+" from "+LOCAL_ITEM_LIST+" natural join "+ITEM_LIST, null);
		
		return(c);
	}
	public Cursor getProduct(Integer localid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select "+ITEM_LOCAL_ID+","+ITEM_DESC+","+ITEM_BEVERAGE+" from "+LOCAL_ITEM_LIST+" natural join "+ITEM_LIST+" where "+ITEM_LOCAL_ID+"="+localid.toString(), null);
		
		return(c);
	}

	public int getProductRestock(Integer localid,Integer roomid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select 1 from "+LOCAL_RESTOCK+" where "+ITEM_LOCAL_ID+"="+localid.toString()+" and "+ROOM_ID+"="+roomid.toString(), null);
		if (c.getCount()==0){
			
			return(0);
		}
		else{
			
			return(1);}
		
	}
	public Integer getProductAction(Integer localid,Integer roomid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select "+ITEM_ACTION+" from "+LOCAL_ACTION+" where "+ITEM_LOCAL_ID+"="+localid.toString()+" and "+ROOM_ID+"="+roomid.toString()+" ORDER BY "+ROOM_ID+","+ITEM_LOCAL_ID+" LIMIT 1,1", null);
		if (c.getCount()==0){
			
			return(0);}
		else{
			
			return((Integer)c.getInt(0));}
	}	
	public int getProductSale(Integer localid,Integer roomid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select 1 from "+LOCAL_ACTION+" where "+ITEM_LOCAL_ID+"="+localid.toString()+" and "+ROOM_ID+"="+roomid.toString()+" AND "+ITEM_ACTION+"=1", null);
		if (c.getCount()==0){
			
			return(0);}
		else{
			
			return(1);}
	}	
	public int getProductDamaged(Integer localid,Integer roomid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select 1 from "+LOCAL_ACTION+" where "+ITEM_LOCAL_ID+"="+localid.toString()+" and "+ROOM_ID+"="+roomid.toString()+" AND "+ITEM_ACTION+"=3", null);
		if (c.getCount()==0){
			
			return(0);}
		else{
			
			return(1);}
	}		
	public int getProductExpired(Integer localid,Integer roomid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select 1 from "+LOCAL_ACTION+" where "+ITEM_LOCAL_ID+"="+localid.toString()+" and "+ROOM_ID+"="+roomid.toString()+" AND "+ITEM_ACTION+"=2", null);
		if (c.getCount()==0){
			
			return(0);}
		else{
			
			return(1);}
	}	
	public Cursor getLowestUnsentItem(){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select "+ITEM_ACTION_ID+","+ITEM_LOCAL_ID+","+ITEM_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_CREATED+" from "+LOCAL_ACTION+" natural join "+LOCAL_ITEM_LIST+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null order by "+ITEM_ACTION_ID+" limit 1,1", null);
		
		return(c);
	
	}
	
	public void setActionRowSent(Integer actionid,Long now){
		final SQLiteDatabase db = getWritableDatabase(); 
		
		db.execSQL("UPDATE "+LOCAL_ACTION+" SET "+ROW_SENT+"="+now.toString()+" where "+ITEM_ACTION_ID+"="+actionid.toString());
		
	}
	public Cursor getLowestUnsentRestock(){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select "+ITEM_RESTOCK_ID+","+ITEM_ID+","+ROOM_ID+","+ROW_CREATED+" from "+LOCAL_RESTOCK+" natural join "+LOCAL_ITEM_LIST+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null order by "+ITEM_RESTOCK_ID+" limit 1,1", null);
		
		return(c);
	
	}
	
	public void setRestockRowSent(Integer restockid,Long now){
		final SQLiteDatabase db = getWritableDatabase(); 
		
		db.execSQL("UPDATE "+LOCAL_RESTOCK+" SET "+ROW_SENT+"="+now.toString()+" where "+ITEM_RESTOCK_ID+"="+restockid.toString());
		
	}
	public Cursor getLowestUnsentRoom(){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select "+ROOM_ID+","+ROOM_CHECK_TIME+","+ROOM_STATUS+" from "+ROOM_ACTION+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null order by "+ROOM_ID+" limit 1,1", null);
		
		return(c);
	
	}
	
	public void setRoomRowSent(Integer roomid,Long now){
		final SQLiteDatabase db = getWritableDatabase(); 
		
		db.execSQL("UPDATE "+ROOM_ACTION+" SET "+ROW_SENT+"="+now.toString()+" where "+ROOM_ID+"="+roomid.toString());
		
	}
	public int getFirstRoom(boolean DND){
		
		final SQLiteDatabase db = getReadableDatabase();
		int roomid=0;
		Cursor c;
		if(DND)
			c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" where "+ROOM_STATUS+"!=0 ORDER BY "+ROOM_FLOOR+","+ROOM_NUMBER+" LIMIT 1,1",null);
		else
			c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" ORDER BY "+ROOM_FLOOR+","+ROOM_NUMBER+" LIMIT 1,1",null);
		if (c.getCount()>0)
			if(c.moveToFirst())
				roomid=c.getInt(0);
		c.close();
		return(roomid);
		
		
	}
	public int getNextRoom(Integer roomid,boolean DND){
		
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		int nextroomid=roomid;
		if(DND)
			c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" where "+ROOM_STATUS+"<5 AND "+ROOM_ID+">"+roomid.toString()+" ORDER BY "+ROOM_FLOOR+","+ROOM_NUMBER+" LIMIT 1,1",null);
		else
			c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" where "+ROOM_ID+">"+roomid.toString()+" ORDER BY "+ROOM_FLOOR+","+ROOM_NUMBER+" LIMIT 1,1",null);
		

		if (c.getCount()>0)
			if(c.moveToFirst())
				nextroomid=c.getInt(0);
		
		c.close();
		return(nextroomid);
		
		
	}
	public int getPreviousRoom(Integer roomid,boolean DND){
		
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		int prevroomid=roomid;
		if(DND)
			c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" where "+ROOM_STATUS+"<5 AND "+ROOM_ID+"<"+roomid.toString()+" ORDER BY "+ROOM_FLOOR+","+ROOM_NUMBER+" LIMIT 1,1",null);
		else
			c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" where "+ROOM_ID+"<"+roomid.toString()+" ORDER BY "+ROOM_FLOOR+","+ROOM_NUMBER+" LIMIT 1,1",null);
		
		
		if (c.getCount()>0)
			if(c.moveToFirst())
				prevroomid=c.getInt(0);

		c.close();
		return(prevroomid);
		
		
	}

	public String getRoomNumber(Integer roomid){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		String roomnum="";
		c = db.rawQuery("select "+ROOM_NUMBER+" from "+ROOM_LIST+" where "+ROOM_ID+"="+roomid.toString(),null);
		
		if (c.getCount()>0)
			if(c.moveToFirst())
				roomnum=c.getString(0);
		
		c.close();
		return(roomnum);
		
	}
	public Integer getRoomId(String roomnum){
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		Integer roomid=0;
		c = db.rawQuery("select "+ROOM_ID+" from "+ROOM_LIST+" where "+ROOM_NUMBER+"=\'"+roomnum.toString()+"\'",null);
		
		if (c.getCount()>0)
			if(c.moveToFirst())
				roomid=c.getInt(0);

		
		c.close();
		return(roomid);
		
	}

	public Integer getPendingTransactionCount(){
		Integer total=0;
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select count(*) from "+LOCAL_ACTION+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null",null);	
		if(c.moveToFirst())
			total=(Integer)c.getInt(0);
		c.close();
		c = db.rawQuery("select count(*) from "+LOCAL_RESTOCK+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null",null);		
		if(c.moveToFirst())
			total+=(Integer)c.getInt(0);
		c.close();
		c = db.rawQuery("select count(*) from "+ROOM_ACTION+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null",null);		
		if(c.moveToFirst())
			total+=(Integer)c.getInt(0);
		c.close();
		return(total);
	}
	public Integer getRoomTransactionCount(){
		Integer total=0;
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select count(*) from "+ROOM_ACTION+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null",null);		
		if(c.moveToFirst())
			total=(Integer)c.getInt(0);
		c.close();
		return(total);
	}	
	public Integer getActionTransactionCount(){
		Integer total=0;
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select count(*) from "+LOCAL_ACTION+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null",null);		
		if(c.moveToFirst())	
			total=(Integer)c.getInt(0);
		
		c.close();
		return(total);
	}	
	public Integer getRestockTransactionCount(){
		Integer total=0;
		final SQLiteDatabase db = getReadableDatabase(); 
		Cursor c;
		c = db.rawQuery("select count(*) from "+LOCAL_RESTOCK+" where "+ROW_SENT+" is null and "+ROW_RECEIVED+" is null",null);		
		if(c.moveToFirst())
			total=(Integer)c.getInt(0);
		
		c.close();
		return(total);
	}		
	public void putRoomAction(Integer roomid,Integer action,Long now){
		final SQLiteDatabase db = getWritableDatabase(); 
		
		db.execSQL("INSERT INTO "+ROOM_ACTION+"("+ROOM_ID+","+ROOM_STATUS+","+ROOM_CHECK_TIME+") VALUES ("+roomid.toString()+","+action.toString()+","+now.toString()+");");
		
	}
	public boolean putItemNone(Integer localitemid,Integer roomid,Long now){
		final SQLiteDatabase db = getWritableDatabase(); 
		 
		Cursor c = db.rawQuery("select "+ITEM_ACTION_ID+","+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_SENT+","+ROW_RECEIVED+" from "+LOCAL_ACTION+" where "+ITEM_LOCAL_ID+"="+localitemid.toString()+" and "+ROOM_ID+"="+roomid.toString(),null);
		if (c.getCount()>0){
			if(c.moveToFirst()){
				if(!c.isNull(6)){
					c.close();
					
					return(false);
				}else{
					if(!c.isNull(5)){
						Integer iactid = (Integer)c.getInt(0);
						Integer act =(Integer)c.getInt(3);
						Integer nr =(Integer)c.getInt(4);
						db.execSQL("INSERT INTO "+LOCAL_REVERSAL+"("+ITEM_ACTION_ID+","+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+") VALUES ("+iactid.toString()+","+localitemid.toString()+","+roomid.toString()+","+act.toString()+","+nr.toString()+")");
						db.execSQL("DELETE FROM "+LOCAL_ACTION+" WHERE "+ITEM_ACTION_ID+"="+iactid.toString());
						c.close();
						
						return(true);
					}else{
						Integer iactid = (Integer)c.getInt(0);
						db.execSQL("DELETE FROM "+LOCAL_ACTION+" WHERE "+ITEM_ACTION_ID+"="+iactid.toString());
						c.close();
						return(true);
					}
				}
			}
		}else{
			db.execSQL("DELETE FROM "+LOCAL_REVERSAL+" WHERE "+ITEM_LOCAL_ID+"="+localitemid.toString()+" AND "+ROOM_ID+"="+roomid.toString()+" AND "+ROW_SENT);
			c.close();
			
			return(true);			
		}
		return(false);
	}
	public boolean putItemAction(Integer localitemid,Integer roomid,Integer action,Integer nr,Long now){
		final SQLiteDatabase db = getWritableDatabase(); 
		 
		Cursor c = db.rawQuery("select "+ITEM_ACTION_ID+","+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_SENT+","+ROW_RECEIVED+" from "+LOCAL_ACTION+" where "+ITEM_LOCAL_ID+"="+localitemid.toString()+" and "+ROOM_ID+"="+roomid.toString(),null);
		if (c.getCount()>0){
			if(c.moveToFirst()){
				if(!c.isNull(6)){
					c.close();
					return(false);
				}else{
					if(!c.isNull(5)){
						Integer iactid = (Integer)c.getInt(0);
						Integer act =(Integer)c.getInt(3);
						Integer prevnr =(Integer)c.getInt(4);
						db.execSQL("DELETE FROM "+LOCAL_REVERSAL+" WHERE "+ITEM_ACTION_ID+"="+iactid.toString()+" AND "+ROW_SENT+" IS NOT NULL");
						db.execSQL("INSERT INTO "+LOCAL_REVERSAL+"("+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_CREATED+") VALUES ("+localitemid.toString()+","+roomid.toString()+","+act.toString()+","+prevnr.toString()+","+now.toString()+")");
						db.execSQL("DELETE FROM "+LOCAL_ACTION+" WHERE "+ITEM_ACTION_ID+"="+iactid.toString());
						db.execSQL("INSERT INTO "+LOCAL_ACTION+"("+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_CREATED+") VALUES ("+localitemid.toString()+","+roomid.toString()+","+action.toString()+","+nr.toString()+","+now.toString()+")");
						c.close();
						
						return(true);
					}else{
						Integer iactid = (Integer)c.getInt(0);
						db.execSQL("DELETE FROM "+LOCAL_ACTION+" WHERE "+ITEM_ACTION_ID+"="+iactid.toString());
						db.execSQL("INSERT INTO "+LOCAL_ACTION+"("+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_CREATED+") VALUES ("+localitemid.toString()+","+roomid.toString()+","+action.toString()+","+nr.toString()+","+now.toString()+")");
						c.close();
						
						return(true);
					}
				}
			}
		}else{
			db.execSQL("INSERT INTO "+LOCAL_ACTION+"("+ITEM_LOCAL_ID+","+ROOM_ID+","+ITEM_ACTION+","+NO_RESTOCK+","+ROW_CREATED+") VALUES ("+localitemid.toString()+","+roomid.toString()+","+action.toString()+","+nr.toString()+","+now.toString()+")");
			c.close();
			
			return(true);			
		}
		return(false);
	}

	
}
