SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mixed results for accounting query

    I'm doing a bit of accountancy software and i have a function to get some search results and hand it to an object dedicated for holding account search data.

    My query will return key information about the accounts, as well as a a balance for each of the three ledgers (client ledger, office ledger, bill ledger). All it's doing really is pulling any transaction in that ledger that is confirmed and is not reversed.

    The issue i'm having is it will return the correct client balance for "Mr Kieran Peat (no delete)", but not for anyone else :S
    Actuallly, just figured if there's confirmed transactions from other accounts, it'll add the amount onto the client ledger for "Mr Kieran Peat (no delete)"

    Well confused :S any suggestions ?

    Code MySQL:
    SELECT account.aid
    	 , account.manager_id
    	 , manager.prefix				as manager_prefix
    	 , manager.forename			as manager_forename
    	 , manager.surname			as manager_surname
    	 , account.type
    	 , price.amount
    	 , price.vat
    	 , account.name
    	 , account.prefix
    	 , account.forename
    	 , account.surname
    	 , account.created
    	 , account.updated
    	 , account.closed
    	 , client_ledger.amount		as client_amount
    	 , client_ledger.vat		as client_vat
    	 , office_ledger.amount		as office_amount
    	 , office_ledger.vat		as office_vat
    	 , bill_ledger.amount		as bill_amount
    	 , bill_ledger.vat			as bill_vat
    FROM accounts as account
    LEFT OUTER
      JOIN users as manager
    	ON manager.id = account.manager_id
    LEFT OUTER
      JOIN account_monitors as monitors
    	ON monitors.aid = account.aid
    LEFT OUTER
      JOIN account_price as price
    	ON price.price = account.price
    LEFT OUTER JOIN
    (
    	SELECT c_ledger.aid
    		 , c_ledger.ledger
    		 , c_ledger.confirmed_by
    		 , SUM(c_ledger.amount) as amount
    		 , SUM(c_ledger.vat) as vat
    	FROM account_transactions as c_ledger
    	LEFT OUTER
    	  JOIN account_transaction_reverse as c_reverse
    		ON c_reverse.tid = c_ledger.tid
    	WHERE c_reverse.uid IS NULL AND c_ledger.confirmed_by > 0
    ) AS client_ledger
      ON client_ledger.aid = account.aid AND client_ledger.ledger = 1
    LEFT OUTER JOIN
    (
    	SELECT o_ledger.aid
    		 , o_ledger.ledger
    		 , o_ledger.confirmed_by
    		 , SUM(o_ledger.amount) as amount
    		 , SUM(o_ledger.vat) as vat
    	FROM account_transactions as o_ledger
    	LEFT OUTER
    	  JOIN account_transaction_reverse as o_reverse
    		ON o_reverse.tid = o_ledger.tid
    	WHERE o_reverse.uid IS NULL AND o_ledger.confirmed_by > 0
    ) AS office_ledger
      ON office_ledger.aid = account.aid AND office_ledger.ledger = 2
    LEFT OUTER JOIN
    (
    	SELECT b_ledger.aid
    		 , b_ledger.ledger
    		 , b_ledger.confirmed_by
    		 , SUM(b_ledger.amount) as amount
    		 , SUM(b_ledger.vat) as vat
    	FROM account_transactions as b_ledger
    	LEFT OUTER
    	  JOIN account_transaction_reverse as b_reverse
    		ON b_reverse.tid = b_ledger.tid
    	WHERE b_reverse.uid IS NULL AND b_ledger.confirmed_by > 0
    ) AS bill_ledger
      ON bill_ledger.aid = account.aid AND office_ledger.ledger = 3
     
    GROUP BY account.aid
    ORDER BY account.created DESC
    LIMIT 0, 10

    Test data

    Code MySQL:
    -- phpMyAdmin SQL Dump
    -- version 3.2.0.1
    -- [url]http://www.phpmyadmin.net[/url]
    --
    -- Host: localhost
    -- Generation Time: Oct 21, 2009 at 09:03 AM
    -- Server version: 5.1.36
    -- PHP Version: 5.3.0
     
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
     
    --
    -- Database: `wolstenholmes`
    --
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `accounts`
    --
     
    CREATE TABLE IF NOT EXISTS `accounts` (
      `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `manager_id` smallint(5) unsigned DEFAULT '0',
      `stream_id` int(11) unsigned DEFAULT '0',
      `old_aid` varchar(25) DEFAULT '',
      `old_fileid` varchar(25) DEFAULT '',
      `old_cid` varchar(25) DEFAULT '',
      `old_ref` varchar(25) DEFAULT '',
      `type` tinyint(2) DEFAULT '0',
      `price` int(15) unsigned DEFAULT '0',
      `name` varchar(50) DEFAULT '',
      `prefix` varchar(5) DEFAULT '',
      `forename` varchar(50) DEFAULT '',
      `middlename` varchar(200) DEFAULT '',
      `surname` varchar(50) DEFAULT '',
      `email` varchar(320) DEFAULT '',
      `main_contact` varchar(15) DEFAULT '',
      `street` varchar(255) DEFAULT '',
      `locality` varchar(50) DEFAULT '',
      `region` varchar(50) DEFAULT '',
      `postcode` varchar(10) DEFAULT '',
      `created` int(10) unsigned DEFAULT '0',
      `updated` int(10) unsigned DEFAULT '0',
      `closed` int(10) unsigned DEFAULT '0',
      PRIMARY KEY (`aid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
     
    --
    -- Dumping data for table `accounts`
    --
     
    INSERT INTO `accounts` (`aid`, `manager_id`, `stream_id`, `old_aid`, `old_fileid`, `old_cid`, `old_ref`, `type`, `price`, `name`, `prefix`, `forename`, `middlename`, `surname`, `email`, `main_contact`, `street`, `locality`, `region`, `postcode`, `created`, `updated`, `closed`) VALUES
    (1, 0, 87, '', '', '', '', 1, 1, 'Mr Kieran  Test (no Delete)', 'Mr', 'Kieran', '', 'Test (no Delete)', 'kieran.peat@wolstenholmes.co.uk', '07960535525', '119 Rutherford Drive', 'Over Hulton', 'Bolton', 'BL5 1DW', 1256029580, 1256029580, 0),
    (2, 0, 93, '', '', '', '', 5, 2, 'Mr Kieran  Another Test', 'Mr', 'Kieran', '', 'Another Test', 'kieran.peat@wolstenholmes.co.uk', '07960535525', 'Erger', 'Ergr', 'Ergre', 'BL5 1DW', 1256033535, 1256033535, 0) ;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `account_monitors`
    --
     
    CREATE TABLE IF NOT EXISTS `account_monitors` (
      `aid` int(10) unsigned NOT NULL DEFAULT '0',
      `user_id` smallint(5) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`aid`,`user_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
    --
    -- Dumping data for table `account_monitors`
    --
     
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `account_price`
    --
     
    CREATE TABLE IF NOT EXISTS `account_price` (
      `price` int(15) unsigned NOT NULL AUTO_INCREMENT,
      `amount` decimal(10,2) DEFAULT '0.00',
      `vat` decimal(8,2) DEFAULT '0.00',
      PRIMARY KEY (`price`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
     
    --
    -- Dumping data for table `account_price`
    --
     
    INSERT INTO `account_price` (`price`, `amount`, `vat`) VALUES
    (1, '562.53', '84.00'),
    (2, '265.00', '39.75') ;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `account_price_breakdown`
    --
     
    CREATE TABLE IF NOT EXISTS `account_price_breakdown` (
      `price` int(15) unsigned DEFAULT NULL,
      `name` varchar(50) DEFAULT '',
      `amount` decimal(10,2) DEFAULT '0.00',
      `vat` decimal(6,2) DEFAULT '0.00',
      KEY `price` (`price`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
    --
    -- Dumping data for table `account_price_breakdown`
    --
     
    INSERT INTO `account_price_breakdown` (`price`, `name`, `amount`, `vat`) VALUES
    (1, 'Sale', '235.00', '35.25'),
    (1, 'Client Identification Verification', '6.00', '0.90'),
    (1, 'Telegraphic Transfer (TT) Fee', '39.00', '5.85'),
    (1, 'Mortgage Fee', '55.00', '8.25'),
    (1, 'HIPs', '225.00', '33.75'),
    (1, 'Preparation of bank transfer', '2.53', '0.00'),
    (2, 'HIPs', '225.00', '33.75'),
    (2, 'Leasehold Surcharge', '40.00', '6.00'),
    (3, 'Sale', '159.00', '23.85'),
    (3, 'Client Identification Verification', '6.00', '0.90'),
    (3, 'Telegraphic Transfer (TT) Fee', '39.00', '5.85'),
    (3, 'Mortgage Fee', '55.00', '8.25'),
    (3, 'Preparation of bank transfer', '2.53', '0.00');
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `account_transactions`
    --
     
    CREATE TABLE IF NOT EXISTS `account_transactions` (
      `tid` int(15) unsigned NOT NULL AUTO_INCREMENT,
      `aid` int(10) unsigned DEFAULT '0',
      `issued_by` smallint(5) unsigned DEFAULT '0',
      `type` tinyint(1) DEFAULT '1',
      `ledger` tinyint(1) DEFAULT '1',
      `method` tinyint(1) DEFAULT '1',
      `amount` decimal(10,2) DEFAULT '0.00',
      `vat` decimal(6,2) DEFAULT '0.00',
      `time` int(10) unsigned DEFAULT '0',
      `contact` varchar(50) DEFAULT '',
      `ref` varchar(30) DEFAULT '',
      `description` text,
      `confirmed_by` smallint(5) unsigned DEFAULT '0',
      `confirmed_time` int(10) unsigned DEFAULT '0',
      PRIMARY KEY (`tid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
     
    --
    -- Dumping data for table `account_transactions`
    --
     
    INSERT INTO `account_transactions` (`tid`, `aid`, `issued_by`, `type`, `ledger`, `method`, `amount`, `vat`, `time`, `contact`, `ref`, `description`, `confirmed_by`, `confirmed_time`) VALUES
    (1, 1, 101, 1, 1, 1, '200.00', '0.00', 1256029580, 'Mr Kieran  Test (no Delete)', '', 'Deposit', 101, 1256029629),
    (2, 1, 101, 1, 1, 6, '500000.00', '0.00', 1256030533, 'Mortgage lender', '', 'Money from the mortgage lender', 101, 1256030533),
    (4, 2, 101, 1, 1, 1, '265.00', '39.75', 1256033535, 'Mr Kieran  Another Test', '', 'Deposit', 0, 0) ;
     
    -- --------------------------------------------------------
     
    --
    -- Table structure for table `account_transaction_reverse`
    --
     
    CREATE TABLE IF NOT EXISTS `account_transaction_reverse` (
      `tid` int(15) unsigned NOT NULL DEFAULT '0',
      `uid` smallint(5) unsigned DEFAULT '0',
      `time` int(10) unsigned DEFAULT '0',
      `reason` text,
      PRIMARY KEY (`tid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
    --
    -- Dumping data for table `account_transaction_reverse`
    --
     
    INSERT INTO `account_transaction_reverse` (`tid`, `uid`, `time`, `reason`) VALUES
    (1, 101, 1256115097, 'tyjy');

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried afew more variations, but got no where, I'm afraid to say this is above me ;/ Any one got any suggestions on what to try ?

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well there's such an amazinly short dead line on this software that i couldn't wait, i had to change it to having static fields with the balance and just update them on any changes.

    For knowledge sake i would like to know what was wrong though if anyone has any pointers ?

    Regards,
    Kieran

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I simplified the problem a bite to see if this is what your after. You can tell me if I'm on the right track or not.

    Code SQL:
    CREATE TABLE accounts(
    	aid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,PRIMARY KEY(aid)
    ) ENGINE=MyISAM;
     
    CREATE TABLE account_transactions (
        tid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,aid MEDIUMINT UNSIGNED NOT NULL
    	,ledger TINYINT UNSIGNED NOT NULL
    	,amount DECIMAL(20,2) NOT NULL DEFAULT '0.00'
    	,PRIMARY KEY(tid)
    	,INDEX(aid)
    	,INDEX(ledger)
    ) ENGINE=MyISAM;
     
    CREATE TABLE account_transaction_reverse (
    	rid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,tid MEDIUMINT UNSIGNED NOT NULL
    	,PRIMARY KEY(rid)
    	,INDEX(tid)
    ) ENGINE=MyISAM;
     
    CREATE TABLE ledger_types (
    	lid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,name VARCHAR(24) NOT NULL
    	,PRIMARY KEY(lid)
    	,INDEX(lid)
    ) ENGINE=MyISAM;

    On major change I mad is added a ledger types tables that will hold all the ledger types. This way you can cross join on that table for every account.

    Given the below schema I used this query:

    Code SQL:
    SELECT
         acct.aid
         ,COALESCE(SUM(trans.amount),0) AS ledger_total
         ,GROUP_CONCAT(CONCAT(ledger.name,':',COALESCE(trans.amount,0))) AS ledgers
      FROM
          accounts acct
     CROSS
      JOIN
         ledger_types ledger
      LEFT
      JOIN
          (SELECT
                trans.aid
                ,trans.ledger
                ,SUM(trans.amount) AS amount
            FROM
               account_transactions trans
            LEFT
            JOIN 
                account_transaction_reverse rev
              ON
                trans.tid = rev.tid
           WHERE
                rev.rid IS NULL
           GROUP
              BY
               trans.aid
               ,trans.ledger) trans
       ON
         acct.aid = trans.aid 
      AND
         ledger.lid = trans.ledger
    GROUP
       BY
         acct.aid

    This an example of the results:

    is that what your looking for?
    Attached Images Attached Images


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •