vendredi 1 avril 2016

How to multiply SQL columns from 2 tables and insert into a seperate column?

I am trying to write an SQL Android application that takes the quantity of an inventory from one table to multiply it by the price of the item in another table to get the total to store into another column. Based on what I have read it seems pretty simple, but isn't working for me. I have tried multiple solutions, but at this point what I have done is put the SQL code in a separate method called totalPrice() to be called on a button click in another method.

public static final String TABLE_INVOICE = "Invoice";
public static final String INVOICE_ID = "Invoice_ID";
public static final String INVOICE_DATE = "Date";

public static final String TABLE_INVENTORY = "Inventory";
public static final String INVENTORY_ID = "Inventory_ID";
public static final String INVENTORY_ITEM = "Item";
public static final String INVENTORY_XS = "extraSmall";
public static final String INVENTORY_S = "Small";
public static final String INVENTORY_M = "Medium";
public static final String INVENTORY_L = "Large";
public static final String INVENTORY_XL = "extraLarge";
public static final String INVENTORY_XXL = "extraExtraLarge";
public static final String INVENTORY_PRICE = "Price";

public static final String TABLE_ORDERED = "Ordered";
public static final String ORDERED_ID = "Ordered_ID";
public static final String ORDERED_ITEM = "Item";
public static final String ORDERED_SIZE = "Size";
public static final String ORDERED_QUANTITY = "Quantity";
public static final String ORDERED_TOTAL = "Total";

public DbHelper(Context context){
    super(context, DB_NAME, null, DB_VERSION);
}

public void onCreate(SQLiteDatabase db) {
    String CREATE_CUSTOMER_TABLE = "CREATE TABLE " + TABLE_CUSTOMER + " ("
            + C_CUSTOMER_ID + " INTEGER PRIMARY KEY, "
            + C_CUSTOMER_NAME + " TEXT, "
            + C_EMAIL + " TEXT, "
            + C_STREET_ADDRESS + " TEXT, "
            + C_CITY + " TEXT, "
            + C_STATE + " TEXT, "
            + C_ZIP + " TEXT, "
            + C_PHONE + " TEXT "
            + ")";

    String CREATE_INVENTORY_TABLE = "CREATE TABLE " + TABLE_INVENTORY + " ("
            + INVENTORY_ID + " INTEGER PRIMARY KEY, "
            + INVENTORY_ITEM + " TEXT, "
            + INVENTORY_XS + " TEXT, "
            + INVENTORY_S + " TEXT, "
            + INVENTORY_M + " TEXT, "
            + INVENTORY_L + " TEXT, "
            + INVENTORY_XL + " TEXT, "
            + INVENTORY_XXL + " TEXT, "
            + INVENTORY_PRICE + " REAL "
            + ")";

    String CREATE_INVOICE_TABLE = "CREATE TABLE " + TABLE_INVOICE + " ("
            + INVOICE_ID + " INTEGER PRIMARY KEY, "
            + INVOICE_DATE + " DATETIME, "
            + C_CUSTOMER_ID + " INTEGER, "
            + "FOREIGN KEY(" + C_CUSTOMER_ID + ") REFERENCES " + TABLE_CUSTOMER + " (" + C_CUSTOMER_ID + "));";

    String CREATE_ORDERED_TABLE = "CREATE TABLE " + TABLE_ORDERED + " ("
            + ORDERED_ID + " INTEGER PRIMARY KEY, "
            + ORDERED_ITEM + " TEXT, "
            + ORDERED_SIZE + " TEXT, "
            + ORDERED_QUANTITY + " INTEGER, "
            + ORDERED_TOTAL + " REAL, "
            + INVENTORY_ID + " INTEGER, "
            + "FOREIGN KEY(" + INVENTORY_ID + ") REFERENCES " + TABLE_INVENTORY + " (" + INVENTORY_ID + "));";

    db.execSQL(CREATE_CUSTOMER_TABLE);
    db.execSQL(CREATE_INVENTORY_TABLE);
    db.execSQL(CREATE_INVOICE_TABLE);
    db.execSQL(CREATE_ORDERED_TABLE);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CUSTOMER);

    //create tables again
    this.onCreate(db);
}

public Cursor totalPrice()
{
    //open database
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor res = db.rawQuery("SELECT " + ORDERED_QUANTITY + ", " + INVENTORY_PRICE + ", " +
            ORDERED_QUANTITY + " * " + INVENTORY_PRICE + " AS " + ORDERED_TOTAL +
            " FROM " + TABLE_ORDERED + " INNER JOIN " + TABLE_INVENTORY +
            " ON Ordered." + INVENTORY_ID + " = Inventory." + INVENTORY_ID, null);
    return res;
}

I have another class that calls the method within a button click.

public class AddInvoice extends AppCompatActivity {
Button addInvoice;
DbHelper db;
EditText date, cid, item, size, invID, quantity;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_add_invoice);
    Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
    setSupportActionBar(toolbar);
    db = new DbHelper(this);

    addInvoice = (Button) findViewById(R.id.btnAddToInvoice);
    date = (EditText) findViewById(R.id.etDate);
    cid = (EditText) findViewById(R.id.etCID);
    item = (EditText) findViewById(R.id.ItemName);
    size = (EditText) findViewById(R.id.etSize);
    invID = (EditText) findViewById(R.id.etAddInventoryID);
    quantity = (EditText) findViewById(R.id.etQuantity);

    addInvoice.setOnClickListener(
            new View.OnClickListener() {
                @Override
                public void onClick(View v) {
              AddInvoice();
              AddOrderedItem();
              db.totalPrice();
              date.setText("");
              cid.setText("");
              item.setText("");
              size.setText("");
              invID.setText("");
              quantity.setText("");
                }
            }
    );
              getSupportActionBar().setDisplayHomeAsUpEnabled(true);
          }

public void AddOrderedItem() {

    boolean isInserted = db.addOrderedItem(item.getText().toString(), size.getText().toString(), Integer.parseInt(invID.getText().toString()),
            Integer.parseInt(quantity.getText().toString()));
    db.totalPrice();
        if (isInserted == true)
            Toast.makeText(AddInvoice.this, "Item Inserted", Toast.LENGTH_SHORT).show();
        else
            Toast.makeText(AddInvoice.this, "Item not Inserted", Toast.LENGTH_SHORT).show();
        }

public void AddInvoice(){
    boolean isInserted = db.addInvoice(date.getText().toString(), cid.getText().toString());
        if (isInserted == true)
            Toast.makeText(AddInvoice.this, "Invoice Inserted", Toast.LENGTH_SHORT).show();
        else
            Toast.makeText(AddInvoice.this, "Invoice not Inserted", Toast.LENGTH_SHORT).show();
}

}

Now when I run the code the value that is stored in the Total Price column is null. Thanks for any help.

Aucun commentaire:

Enregistrer un commentaire