Home

cics

Apr 26

Inventory Allocation Count Total Served Stored Procedure

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

Apr 26

Inventory Allocation Sub-System

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 »

Apr 26

Transaction Max Batch Number Stored Procedure

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

Apr 26

Transaction Unit Balance Stored Procedure

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

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 Open Batches Stored Procedure

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 »

Apr 26

Transactioon Max Unit Number Stored Procedure

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

Apr 26

Transaction Count Batch Stored Procedure

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

Apr 26

Transaction Batch Balance Stored Procedure

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 »

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

12next ›last »