Home

stored procedures

Apr 26

Transaction Open Units Stored Procedure

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 ;
?>

Apr 26

Transaction Table

The transaction table is the core of the application. It records amounts of inventory moving from one area to another. It also implements a Single Table Inheritance pattern to capture data about different types of transactions.

<?php
--
-- Table structure for table `transaction`
--

CREATE TABLE `transaction` (
  `transaction_id` int(10) unsigned NOT NULL auto_increment,
  `debit_account_id` int(10) unsigned NOT NULL default '0',
  `credit_account_id` int(10) unsigned NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date` date NOT NULL default '0000-00-00',
  `category_id` int(10) unsigned NULL default '0',
  `amount` double(10,2) NOT NULL default '0.00',
  `memo` text,
  `batch_number` int(10) default '0',
  `unit_number` int(10) default '0',
  `ia_number` int(10) default '0',
  `fma_number` int(10) default '0',
  `cases` int(10) default '0',
  `html_tag` text,
  PRIMARY KEY  (`transaction_id`),
  KEY `IDX_transaction_debit_account_id` (`debit_account_id`),
  KEY `IDX_transaction_credit_account_id` (`credit_account_id`),
  KEY `IDX_transaction_category_id` (`category_id`),
  KEY `IDX_transaction_batch_number` (`batch_number`),
  KEY `IDX_transaction_unit_number` (`unit_number`),
  KEY `IDX_transaction_ia_number` (`ia_number`),
  KEY `IDX_transaction_fma_number` (`fma_number`),
  KEY `IDX_transaction_cases` (`cases`),
  KEY `IDX_transaction_date` (`date`),
  KEY `IDX_transaction_created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
?>

Apr 26

Account Balance Stored Procedure

This is one of the very first procedures I wrote. It simply makes two queries, adding up the debits and subtracting the credits.

<?php
--
-- Account Balance Stored Procedure
--

DROP PROCEDURE IF EXISTS Account_Balance;

DELIMITER |
CREATE PROCEDURE Account_Balance (IN account_id INT, IN before_date DATE)
BEGIN
    DECLARE acct_credit, acct_debit, balance INT;

    SELECT
        SUM(t.amount) INTO acct_credit
    FROM
        transaction AS t
    WHERE
        t.credit_account_id = account_id
      AND
        t.date <= before_date
    ORDER BY
        t.created DESC, t.transaction_id ASC;

    SELECT
        SUM(t.amount) INTO acct_debit
    FROM
        transaction AS t
    WHERE
        t.debit_account_id = account_id
      AND
        t.date <= before_date
    ORDER BY
        t.created DESC, t.transaction_id ASC;

    SET balance = acct_debit - acct_credit;
    SET balance = IFNULL(balance,0);

    SELECT balance;
END;
|
DELIMITER ;
?>

Apr 26

Account Table

<?php
--
-- Table structure for table `account`
--

CREATE TABLE `account` (
  `account_id` int(10) unsigned NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`account_id`),
  KEY `IDX_account_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
?>

Apr 26

Inventory Management Project

I'm currently working on an Inventory Management project. The core is based on a very simple double entry accounting system. I've slowly been moving most of the heavy lifting of my business logic into procedures and this archive is the result.

Apr 26

MySQL 5 Stored Procedure Examples

As I've been experimenting more with MySQL's stored procedures, I have found that there are very few examples online. Questions I had about the stored procedures were difficult to answer. So, here's an archive of stored procedure examples that I have written for a current project. Hopefully they will help someone.