mysql

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