The behaviour of account percentage module

I guess we must address this question to Tom Hallman.
He wrote the module.

Are you there, Tom

/Joe

I've sent him a private email, so hopefully he will be here in a short while.

/Joe

Hi again - I found a couple minutes here so I thought I'd take a look.

The tool *should* be able to be run on any date range whatsoever, so what e-prast is doing should be fine.

e-prast, could you turn on SQL debugging to see what SQL is being executed when you run the Account Percentages Tool?  That would be helpful in determining why data is not being returned.  (As a bonus step, you could try running that same SQL in phpMyAdmin to see if there IS any data or if something is wrong with the tool itself.)

Let me know what you find and I'll take a look as soon as I can.

Tom

here is the sql_dump when I run the modules
sorry if I put it here. the attachment seems don't work for me so here it is:
-------------------------------------------------------------------------------------------

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `frontaccount.tes`
--

-- --------------------------------------------------------

--
-- Table structure for table `6_sql_trail`
--

CREATE TABLE IF NOT EXISTS `6_sql_trail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sql` text NOT NULL,
  `result` tinyint(1) NOT NULL,
  `msg` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=86 ;

--
-- Dumping data for table `6_sql_trail`
--

INSERT INTO `6_sql_trail` (`id`, `sql`, `result`, `msg`) VALUES
(45, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(44, 'SELECT * FROM 6_chart_master WHERE account_code='61901900'', 1, 'could not get gl account'),
(42, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(43, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(41, 'SELECT * FROM 6_chart_master WHERE account_code='21103900'', 1, 'could not get gl account'),
(40, 'SELECT * FROM 6_chart_master WHERE account_code='41001000'', 1, 'could not get gl account'),
(39, 'SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source', 1, 'could not get account percentage entries'),
(38, 'DELETE FROM 6_acct_percent_tool WHERE id = 2', 1, 'could not delete account percentage entry'),
(37, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(36, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(35, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(33, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(34, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(32, 'SELECT * FROM 6_chart_master WHERE account_code='61901900'', 1, 'could not get gl account'),
(31, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(29, 'SELECT * FROM 6_chart_master WHERE account_code='21103900'', 1, 'could not get gl account'),
(30, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(27, 'SELECT * FROM 6_chart_master WHERE account_code='11111990'', 1, 'could not get gl account'),
(28, 'SELECT * FROM 6_chart_master WHERE account_code='41001000'', 1, 'could not get gl account'),
(26, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(25, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(23, 'SELECT * FROM 6_chart_master WHERE account_code='11111990'', 1, 'could not get gl account'),
(24, 'SELECT * FROM 6_chart_master WHERE account_code='11111990'', 1, 'could not get gl account'),
(22, 'SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source', 1, 'could not get account percentage entries'),
(46, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(47, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(48, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(49, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(50, 'SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source', 1, 'could not get account percentage entries'),
(51, 'SELECT * FROM 6_chart_master WHERE account_code='41001000'', 1, 'could not get gl account'),
(52, 'SELECT * FROM 6_chart_master WHERE account_code='21103900'', 1, 'could not get gl account'),
(53, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(54, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(55, 'SELECT * FROM 6_chart_master WHERE account_code='61901900'', 1, 'could not get gl account'),
(56, 'SELECT * FROM 6_acct_percent_tool WHERE id = 1', 1, 'could not get account percentage entry'),
(57, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(58, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(59, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(60, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(61, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(62, 'UPDATE 6_acct_percent_tool SET account_code_source=41001000, \r\n        account_code_credit=21103900, dimension1_credit=0, \r\n        dimension2_credit=0, account_code_debit=61901900, \r\n        percent='2.5' WHERE id = 1', 1, 'could not update account percentage entry'),
(63, 'SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source', 1, 'could not get account percentage entries'),
(64, 'SELECT * FROM 6_chart_master WHERE account_code='41001000'', 1, 'could not get gl account'),
(65, 'SELECT * FROM 6_chart_master WHERE account_code='21103900'', 1, 'could not get gl account'),
(66, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(67, 'SELECT * FROM 6_dimensions    WHERE id='0'', 1, 'The dimension could not be retrieved'),
(68, 'SELECT * FROM 6_chart_master WHERE account_code='61901900'', 1, 'could not get gl account'),
(69, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(70, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(71, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(72, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(73, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(74, 'SELECT next_reference FROM 6_sys_types WHERE type_id = '0'', 1, 'The last transaction ref for 0 could not be retreived'),
(75, 'SELECT * FROM 6_company WHERE coy_code=1', 1, 'The company preferences could not be retrieved'),
(76, 'SELECT * FROM 6_fiscal_year WHERE id='3'', 1, 'could not get current fiscal year'),
(77, 'SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source', 1, 'could not get account percentage entries'),
(78, 'SELECT * FROM 6_dimensions WHERE type_ = 1 ORDER BY reference ASC', 1, 'The dimensions could not be retrieved'),
(79, 'SELECT * FROM 6_dimensions WHERE type_ = 2 ORDER BY reference ASC', 1, 'The dimensions could not be retrieved'),
(80, 'SELECT SUM(amount) FROM 6_gl_trans\r\n        WHERE account='41001000' AND tran_date >= '2010-07-01' AND tran_date <= '2010-07-31' AND dimension_id = '1' AND dimension2_id = '0'', 1, 'Transactions for account 41001000 could not be calculated'),
(81, 'SELECT SUM(amount) FROM 6_gl_trans\r\n        WHERE account='41001000' AND tran_date >= '2010-07-01' AND tran_date <= '2010-07-31' AND dimension_id = '2' AND dimension2_id = '0'', 1, 'Transactions for account 41001000 could not be calculated'),
(82, 'SELECT id FROM 6_quick_entries WHERE type='3'', 1, 'could not retreive quick entries'),
(83, 'SELECT * FROM 6_company WHERE coy_code=1', 1, 'The company preferences could not be retrieved'),
(84, 'SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id\n            FROM 6_chart_master chart,6_chart_types type\n            WHERE chart.account_type=type.id ORDER BY type.id,account_code', 1, ''),
(85, 'SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0 AND type_=1) ORDER BY reference', 1, '');

Tom

Using the sqltrail for myself, I can say that what is reported is incorrect.

I had a lot of  errors incorrectly reported, many  being the same as the ones reported in this thread.

An explanation on the process behing the report would be very usefull.

As you, I think that "show_sql"  would be more usefull.

Gaston

Hello all,

  Sorry for the delay - just got back from vacation.

  Briefly, the Account Percentages Run tool does the following when run:
  * For each account percentage entry (as specified in the APT Editor)
    * For each combination of dimensions 1 & 2 (including the "no dimension")
      * Sum all journal entries to the source account over the specified time period, broken up by dimensions
      * Get the specified percentage for that sum
      * Add a credit journal entry to the specified credit account/dimensions with the given percentage
      * Add a debit journal entry to the specified debit account (and source dimensions) with the given percentage
  * Redirect to a journal entry page with all these journal entries already loaded in

  e-prast, to see the SQL that otherwise disappears too fast after running the tool, you can open this file:
  <FA root>/modules/acct_percent_tool/acct_percent_run.php and comment out the line that says "meta_forward(...)", about line 161.  Then see what SQL is there.

  Joe & Janusz, this module has only been tested against a code base from January 25.  (We have it in mind to get caught up soon!)  Have there been changes to the code that handles the retrieving of dimensions or accounts since then?  I wonder if that could be part of the problem...?

Tom

Hello e-prast,

  Okay, so if I understand this correctly, you have two "type 1" dimensions and no type 2 dimensions.  Is that right?

  What happens if you run those last two SELECT statements directly in phpMyAdmin?  Does anything show up?

Tom

e-prast,

  Hey, I think you might be right!  I just skimmed over the code, and I think there may indeed be a bug if there's no dim to credit.  I'll delve into that more soon to see what can be done and I'll submit a patch.

  In the meantime, I'm glad you found a workaround!  Thanks for asking about the bug!

Tom

e-prast,

  Sure.  Would you like it to say something like, "5% from transactions to: 41001000"?  If so, you can just edit line 149 of the run tool to look more like the commented out line 148.  For example:

  $memo = "$percent% from transactions to: $acct_source_code";

  That might be a quick fix for you!

Tom

OK I've edited so it looks like this:
$memo = "$percent% from transactions of account: $acct_source_code for period $start_date to $end_date";

and the output is:
2.5% from transactions of account: 41001000 for period 01/07/2010 to 31/07/2010

but my desired output is :
2.5% from transactions of account: 41001000 Sales Item-1 for period 01/07/2010 to 31/07/2010

how do I can get the account name not just the account code?

thx