Main menu

Subscribe to stored procedures

stored procedures

Transaction Open Units Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:53

This function was easy to write after writing the Transaction Open Batches Stored Procedure.

<?php
--
-- Transaction Open Units Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Transaction_OpenUnits;

DELIMITER |
CREATE PROCEDURE Transaction_OpenUnits (IN unit_account_id INT)
BEGIN
SELECT
        <code>transaction</code>.<code>unit_number</code> AS unit_no,
        <code>category</code>.<code>category_id</code> AS category_id,
       <code>category</code>.<code>calc_name</code> AS category,

      

IFNULL(
            IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>unit_number</code> = <code>unit_no</code> AND <code>credit_account_id</code> = unit_account_id
          ),0)
       -
          IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>unit_number</code> = <code>unit_no</code> AND <code>debit_account_id</code> = unit_account_id
           ),0)
       ,0) AS unit_balance
    FROM
       <code>transaction</code>

LEFT JOIN <code>category</code> ON <code>transaction</code>.<code>category_id</code> = <code>category</code>.<code>category_id</code>

   

WHERE
     <code>transaction</code>.<code>unit_number</code> > 0
GROUP BY
       <code>transaction</code>.<code>unit_number</code>
    HAVING
     unit_balance > 0;
END
|

DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
stored procedures
cics
mysqli

Transaction Table

Submitted by mikehostetler on Thu, 04/26/2007 - 08:38

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 <code>transaction</code>
--

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

Account Balance Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:32

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 ;
?>
Taxonomy upgrade extras: 
mysql
stored procedures
cics
mysqli

Account Table

Submitted by mikehostetler on Thu, 04/26/2007 - 08:31

<?php
--
-- Table structure for table <code>account</code>
--

Taxonomy upgrade extras: 
mysql
stored procedures
cics
mysqli

Inventory Management Project

Submitted by mikehostetler on Thu, 04/26/2007 - 08:29

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.

Taxonomy upgrade extras: 
PHP
mysql
stored procedures
cics
mysqli

MySQL 5 Stored Procedure Examples

Submitted by mikehostetler on Thu, 04/26/2007 - 08:26

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.

Taxonomy upgrade extras: 
PHP
mysql
database
stored procedures
transactions
examples