Simple stok management

[sergio]

Simple stock management [SOLVED]

Hi all,
Should I make a simple stock management (simple add and remove products with automatic update of the quantity in stok)
well....
I've 2 tables:
1. called:Magazzino
id_prod | descripiton | cod | quantity
2. called Movimenti
id_mov | date | id_prod (joined with the id_prod of the first tb) | pieces (added o removed)

Now, when I make a "movement" in table 2, the quantity inserted in the colums "pieces" (i.e. -2) should go to remove (in this case) the quantity of the column "quantity" oh the first table!

I've tried a lot of solutions.... but I'm still in deadlock...

can you suggest me something.....

tnxxxxxx

Sergio

 

[sergio]

I don't have specified that I use fabrik 1.0.6 with the last svn and joomla 1.0.15

 

[cheesegrits]

You'll need to use a form submit script (bottom left of Form admin page), probably on the 'After Fabrik has processed the form (simple eval)' hook. Something like this:

global $database;
$database->setQuery("UPDATE magazzino SET quantity = quantity + '" . $aData['movimenti___pieces'] . "' WHERE fabrik_internal_id = '" . $aData['movimenti___id_prod'] . "'");
$database->query();

Make sure the element names match those on your form (view source of your form to double check the element names). Also may need to change fabrik_internal_id to the PK name for your table, if it wasn't created with Fabrik.

-- hugh

 

[sergio]

Hugh you're the numember 1

Perfect, all works!

only a small thing if is possible...
now when I insert the record in "movimenti" it remove the quantity in "magazzino" --> ok, but if I delete the record in "movimenti" is it possible that the quantity of this record deleted to be added to "magazzino" in order to restore the "original" quantity....

Sorry for my english.....

tnxxxxxxxxxxxx

 

[cheesegrits]

Not in Fabrik 1.0. That was one of the improvements we've made in 2.0, being able to add plugins that take action when deleting rows.

But of course Fabrik 2.0 only runs on Joomla 1.5.x.

You might be able to achieve this using MySQL triggers:

http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

I'm pretty sure triggers would work in conjunction with Fabrik. Never tested 'em, but shouldn't be hard to set it up and test it on your server. I'd imagine the trigger would look like this:

CREATE TRIGGER update_stock_trigger BEFORE DELETE ON movimenti
FOR EACH ROW SET magazzino.quantity = magazzino.quantity - OLD.pieces WHERE magazzino.fabrik_internal_id = OLD.id_prod;

Enter that in your favorite MySQL client (phpMyAdmin, mysql command line, whatever). Then just delete a movement record via Fabrik and see if the magazzino quantity updates automatically.

NOTE - triggers only work in MySQL 5.0.2 and above.

-- hugh

 

[sergio]

Hi

I tried and the "first version" of the CREATE TRIGGER don't works;
I've read the mysql documentation and I've find the correct syntax:

CREATE TRIGGER stocktrigger BEFORE DELETE ON tb_movimenti
FOR EACH ROW UPDATE tb_magazzino SET qt = qt + OLD.num WHERE id_mag = OLD.id_mag;

now it works perfect!

tnxxxx a lot