• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Decrement a value in a database.. via string grid... on an onclick.. in delphi!

Hi there

For a bit of background, my project is a sort of EPOS/Stock management system.

Everything is now all good, except I haven't been able to figure out how to decrement the stock values as they go through the transaction process.

There are two tables in the database - Product and Takings.. All the stock is stored in Product, obviously. I need to decrement the quantityinstock field when items are sold, of course.

It works by displaying all stock in a dbgrid. There is a query component which allows the user to find an item of stock. The user selects the record in question, then an onclick adds it to a strgrid, which keeps track of items in the current transaction. The code for this onclick is:

procedure TFrmEPOS.BtnEPOSAddClick(Sender: TObject);
begin
  BtnEPOSAdd.Enabled := false;
  { Write current record fieldvalues to string grid }
  StrGridTransaction.Cells[0,Strrow]:=EPOSQuery.FieldValues['ProductName'];
  StrGridTransaction.Cells[1,Strrow]:=EPOSQuery.FieldValues['ProductCost'];
  { Strrow points to next row to modify - is incremented as additions made }
  Strrow := Strrow + 1;
  { If statement disables user's ability to add to grid if full - delete button
  is re-enabled. }
  if Strrow = 17 then
    begin
      BtnEPOSAdd.Enabled := false;
    end;
  BtnEPOSdelete.Enabled := true;
end;


When all items desired are added to the strgrid, the user clicks another button to total the cost, then a final button to register cash tender and calculate change. This final onclick also posts the transaction date and takings to the takings table. The code for that is here:

procedure TFrmEPOS.BtnTenderClick(Sender: TObject);
var
  tender, change : real;
  count: integer;
begin
  tender := StrToFloat(inputbox('Tender','Please enter cash tendered.', ''));
  if tender >= total then
    { allows user to enter cash tendered and calculates change }
    begin
      change := Tender - total;
      LblEPOSChange.Caption := '£'+FloatToStr(change);
      { appending takings table }
      FrmTakings.TakingsQuery.Close;
      FrmTakings.ADOTakings.Append;
      FrmTakings.ADOTakings.Fieldvalues['TransactionDate']:=date;
      FrmTakings.ADOTakings.FieldValues['TransactionTakings']:=total;
      FrmTakings.ADOTakings.Post;
      FrmTakings.TakingsQuery.Open;
      { Clears the string grid of all values }
      Strrow := Strrow +1;
      for count := 0 to Strrow do
      begin
        StrGridTransaction.Cells[1,Count]:='';
        StrGridTransaction.Cells[0,Count]:='';
      end;
        { re enables transaction controls for new transaction }
        MmoTransactionTotal.Text:='';
        BtnEposAdd.Enabled := true;
        BtnEposDelete.Enabled := true;
        BtnEposQuantity.Enabled := true;
        BtnEposTotal.Enabled := true;
        Strrow := 0;
    end
  else
    begin
      ShowMessage('Tender cannot be less than transaction cost!');
    end;
end;


At this point don't mind if the solution is quite clunky, just that it works!

Cheers in advance!!
0
TTRMW
Asked:
TTRMW
1 Solution
 
8080_DiverCommented:
Of at least as much intrest as your procedures would be the table definition of the Product and takings table.  Also, where are the detailsof the transaction, i.e. the recorded list of products involved in the Takings?  Finally, what DBMS are you using?  (The DBMS may offer some solutions.)
It sounds like you are maintaining aQOH in the Product table.  As you are posting the individual inserts of the Product lines in vovled in the Taking, you need to use that same information to post a change to the Product table (sort of likeUPDATE PRODUCT SET QOH = QOH - AmtTaken; ).
 
If you are using a DBMS that allows triggers, you can do that with an INSERT or UPDATE trigger on the Takings table.
0
 
reynaldioCommented:
so far you only mention procedures and tables to decrease stock. do you have a table and procedure to increase stock. if so, you can create a stored procedure to calculate QOH stock. something like below. Run that stored procedure after you updated table TAKINGS and ADDEDSTOCK.
//you can also set product_id as a given parameter to increase performance
 
FOR SELECT PRODUCT_ID FROM PRODUCT INTO :PRODUCT_ID DO
BEGIN
   UPDATE PRODUCT SET QOH=(SELECT SUM(QTY) FROM ADDEDSTOCK WHERE PRODUCT_ID=:PRODUCT_ID) - (SELECT SUM(QTY) FROM TAKINGS WHERE PRODUCT_ID=:PRODUCT_ID) WHERE PRODUCT_ID=:PRODUCT_ID;
end;

Open in new window

0
 
TTRMWAuthor Commented:
helpful, cheers!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now