I've received a lot of interest from my previous post about MySQL Stored Procedures and PHP 5. So, I decided to set up an example archive of the stored procedures I've written for a Inventory Management project I'm currently working on. I've got 13 examples up so far, with more to come. Check it out.
After an Inventory Allocation has been created, Partners need to be chosen. Thus, we cannot fill in the value of Total Served in the Inventory Allocation table until we've chosen all our partners. The value is calculated and stored because it is used frequently in the distribution algorithm.
<?php
--
-- Inventory Allocation Count Total Served Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Ia_CountTotalServed;
DELIMITER |
CREATE PROCEDURE Ia_CountTotalServed (IN ia_id INT)
BEGIN
DECLARE total_served INT;
SELECT
SUM(`net_served`) INTO total_served
FROM
`ia_partner`
WHERE
`ia_partner`.`ia_id` = ia_id;
SELECT total_served;
END;
|
DELIMITER ;
?>The Inventory Allocation functionality in my Inventory Management system is designed to distribute units from inventory to a number of partners. There's way to many business rules associated with this piece to describe here, but I wanted to at least give a high-level picture.
<?php
--
-- Table structure for table `ia`
--
CREATE TABLE `ia` (
`ia_id` int(10) unsigned NOT NULL auto_increment,
`date` date NOT NULL default '0000-00-00',
`closed` tinyint(1) NOT NULL default '0',
`locked` tinyint(1) NOT NULL default '0',
`total_weight` int(10) default '0',
`total_served` int(10) default '0',
PRIMARY KEY (`ia_id`),
KEY `IDX_ia_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Table structure for table `ia_unit`
--
CREATE TABLE `ia_unit` (
`ia_unit_id` int(10) unsigned NOT NULL auto_increment,
`ia_id` int(10) unsigned NOT NULL default '0',
`category_id` int(10) unsigned NOT NULL default '0', read more »
This is somewhat simplistic. The reason I implemented a simple MAX() query is to move towards centralizing all of my custom SQL into stored procedures.
<?php
--
-- Transaction Maximum Batch Number Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_MaxBatchNumber;
DELIMITER |
CREATE PROCEDURE Transaction_MaxBatchNumber ()
BEGIN
DECLARE batch_number INT;
SELECT
MAX(`transaction`.`batch_number`) INTO batch_number
FROM
`transaction`;
SELECT batch_number;
END;
|
DELIMITER ;
?><?php
--
-- Transaction Unit Balance Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_UnitBalance;
DELIMITER |
CREATE PROCEDURE Transaction_UnitBalance (IN unit_number INT, IN unit_account_id INT)
BEGIN
DECLARE unit_balance, unit_credit, unit_debit INT;
SELECT
SUM(t.amount) INTO unit_credit
FROM
transaction AS t
WHERE
t.unit_number = unit_number
AND
t.credit_account_id = unit_account_id;
SELECT
SUM(t.amount) INTO unit_debit
FROM
transaction AS t
WHERE
t.unit_number = unit_number
AND
t.debit_account_id = unit_account_id;
SET unit_debit = IFNULL(unit_debit,0);
SET unit_credit = IFNULL(unit_credit,0);
SET unit_balance = unit_debit - unit_credit;
SELECT unit_balance;
END;
|
DELIMITER ;
?>This function was easy to write after writing the Transaction Open Batches Stored Procedure.
<?php
--
-- Transaction Open Units Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_OpenUnits;
DELIMITER |
CREATE PROCEDURE Transaction_OpenUnits (IN unit_account_id INT)
BEGIN
SELECT
`transaction`.`unit_number` AS unit_no,
`category`.`category_id` AS category_id,
`category`.`calc_name` AS category,
IFNULL(
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`
WHERE `unit_number` = `unit_no` AND `credit_account_id` = unit_account_id
),0)
-
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`
WHERE `unit_number` = `unit_no` AND `debit_account_id` = unit_account_id
),0)
,0) AS unit_balance
FROM
`transaction`
LEFT JOIN `category` ON `transaction`.`category_id` = `category`.`category_id`
WHERE
`transaction`.`unit_number` > 0
GROUP BY
`transaction`.`unit_number`
HAVING
unit_balance > 0;
END
|
DELIMITER ;
?>This was a fun procedure to write. I originally found the IFNULL function for my Account Balance Store Procedure. This query, when directly implemented in PHP was MUCH more complicated because of the possibilities of returning a NULL value. Now, it's all done in one statement.
<?php
--
-- Transaction Open Batches Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_OpenBatches;
DELIMITER |
CREATE PROCEDURE Transaction_OpenBatches (IN batch_account_id INT)
BEGIN
SELECT
`transaction`.`batch_number` AS batch_no,
IFNULL(
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction`
WHERE `batch_number` = `batch_no` AND `credit_account_id` = batch_account_id
),0)
-
IFNULL((
SELECT SUM(`transaction`.`amount`) FROM `transaction` read more »
<?php
--
-- Transaction Maximum Unit Number Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_MaxUnitNumber;
DELIMITER |
CREATE PROCEDURE Transaction_MaxUnitNumber ()
BEGIN
DECLARE unit_number INT;
SELECT
MAX(`transaction`.`unit_number`) INTO unit_number
FROM
`transaction`;
SELECT unit_number;
END;
|
DELIMITER ;
?><?php
--
-- Transaction Count Batch Number Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_CountBatchNumber;
DELIMITER |
CREATE PROCEDURE Transaction_CountBatchNumber (IN batch_number INT)
BEGIN
DECLARE batch_count INT;
SELECT
COUNT(`transaction`.`batch_number`) INTO batch_count
FROM
`transaction`
WHERE
`transaction`.`batch_number` = batch_number;
SELECT batch_count;
END;
|
DELIMITER ;
?>This is a procedure that simply gets the balance of a batch. A batch is a business level object that is reflected at this level. This procedure is a direct descendant of the Account Balance Stored Procedure.
<?php
--
-- Transaction Batch Balance Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_BatchBalance;
DELIMITER |
CREATE PROCEDURE Transaction_BatchBalance (IN batch_number INT, IN batch_account_id INT)
BEGIN
DECLARE batch_balance, batch_credit, batch_debit INT;
SELECT
SUM(t.amount) INTO batch_credit
FROM
transaction AS t
WHERE
t.batch_number = batch_number
AND
t.credit_account_id = batch_account_id;
SELECT
SUM(t.amount) INTO batch_debit
FROM
transaction AS t
WHERE
t.batch_number = batch_number
AND
t.debit_account_id = batch_account_id;
SET batch_debit = IFNULL(batch_debit,0);
SET batch_credit = IFNULL(batch_credit,0); read more »
