samedi 30 janvier 2016

Search Query and Update method bug in sqlite

So, here's my simple app. Whenever someone comes to my restaurant, they will enter their number on a tab set on the booth, if they are new here then it will be a new entry in the database, if they ate here before, then it will get the point from the database using the username and add the new points with the prev points and update the customer's points. Somehow it's not working when an existing username is searched. The keeps foreclosing

Here's my code in MainActivity

   //When the submit button is clicked
public void onButtonClick(View v) {

    if (v.getId() == R.id.bSubmit) {
        EditText uNum = (EditText) findViewById(R.id.number);
        String numstr = uNum.getText().toString();
        EditText uPoints = (EditText) findViewById(R.id.pnts);
        String pontstr = uPoints.getText().toString();
        int i = Integer.parseInt(pontstr);
        //takes in the customer's number and the purchase amount

        //search for the customer's number in database
        String user = helper.searchUname(numstr);

        if (numstr.equals(user)) {
            //if found then add the amount as new points
            int row = helper.searchRow(user);
            int point = helper.searchPoint(user);
            int pnt =  point + i;
            helper.updatePoint(pnt,user);

        }

        else {
            //create a new customer and add the info to database
            customer c = new customer();
            c.setuNum(numstr);
            c.setPoint(i);
            helper.insertcustomer(c);
        }

    }
}

And here's my database codes

public class DatabaseHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "customers.db";
private static final String TABLE_NAME = "customers";
private static final String COLUMN_ID = "id";
private static final String COLUMN_UNUM = "uNum";
private static final String COLUMN_POINT = "Point";
SQLiteDatabase db;
private static final String TABLE_CREATE = "create table customers (id integer primary key not null , " +
        "uNum text not null , Point integer not null );";

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_CREATE);
    this.db = db;
}

public void insertcustomer(customer c) {
    db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    String query = "select * from customers";
    Cursor cursor = db.rawQuery(query , null);
    int count = cursor.getCount();

    values.put(COLUMN_ID , count);
    values.put(COLUMN_UNUM, c.getuNum());
    values.put(COLUMN_POINT, c.getPoint());
    db.insert(TABLE_NAME, null, values);
    db.close();
}

public String searchUname (String numstr){

    db = this.getReadableDatabase();
    String query = "select uNum from "+TABLE_NAME;
    Cursor c = db.rawQuery(query , null);
    String a = "not found";
    String b;

    c.moveToFirst();

    do{
        b = c.getString(0);
        if (b.equals(numstr)){
            a = b;

            break;
        }

    }
    while (c.moveToNext());

    return a;
}

public void updatePoint (int pnt, String user)
{
    db= this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(COLUMN_POINT, pnt);
    String [] whereargs = {user};

    db.update(TABLE_NAME, values, COLUMN_UNUM+" =?", whereargs);
}

public void deleteUser (String user){
    db = this.getWritableDatabase();
    String whereArgs [] = new String[1];
    whereArgs [0]= user;
    db.delete(TABLE_NAME, COLUMN_UNUM+"=?", whereArgs);
}


public int searchRow (String user){

    db = this.getReadableDatabase();
    String query = "select uNum, id from "+TABLE_NAME;
    Cursor c = db.rawQuery(query , null);
    int a = 0;
    String b;

    c.moveToFirst();

    do{
        b = c.getString(0);
        if(b.equals(user))
        {
            a = c.getInt(1);
            break;
        }

    }
    while (c.moveToNext());

    return a;
}






public int searchPoint(String uNum)
{
    db = this.getReadableDatabase();
    String query = "select uNum, Point from "+TABLE_NAME;
    Cursor cursor = db.rawQuery(query , null);
    String a;
    int b = 0;
    if(cursor.moveToFirst())
    {
        do{
            a = cursor.getString(0);

            if(a.equals(uNum))
            {
                b = cursor.getInt(1);
                break;
            }
        }
        while(cursor.moveToNext());
    }

    return b;
}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String query = "DROP TABLE IF EXISTS "+TABLE_NAME;
    db.execSQL(query);
    this.onCreate(db);
}

}

Aucun commentaire:

Enregistrer un commentaire