and I get the desired output.
thx

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

Sorry again for the slow reply.  Try using get_gl_account_name($code).

That is:

$acct_name = get_gl_account_name($acct_source_code);
$memo = "$percent% from transactions of account: $acct_name for period $start_date to $end_date";

I haven't tested that, but I think that'll do what you want.

Tom

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

although in my mind it's more like this

2,5% from transaction 41001000 Sales Item-1 period 01/07/2010 - 15/07/2010

41001000 sales item-1 is the account number, in case there's more than 1 sales account or if the percentage different between sales account (ex: 41001000 sales item-1, 41002000 sales item-2, 42001000 service),

and period is for tracking, so we reduce the risk to double post the percentage in case we run it multiple times in one month

thx

  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

thx

  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

After I put "dimension 1 to credit" entry on the editor and run it, it seems works, the journal entry is filled the credit entries on the journal is set to dimension I choose on the seeting.

But how about if I don't want my account percentage calculation and journalling related to certain  dimension.
I mean if I let the dimension 1 or 2 blank on the editor, can I still use the modules? or is it really have to use dimension?

As for workaround on the journal entry form after running the tools I can edit the dimension back to blank (or another dimension) if it necessary anyway.

But it still feel nice knowing that the modules works for me.

thx

  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

SQL..

SELECT next_reference FROM 6_sys_types WHERE type_id = '0'

SQL..

SELECT * FROM 6_company WHERE coy_code=1

SQL..

SELECT * FROM 6_fiscal_year WHERE id='3'

SQL..

SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source

SQL..

SELECT * FROM 6_dimensions WHERE type_ = 1 ORDER BY reference ASC

SQL..

SELECT * FROM 6_dimensions WHERE type_ = 2 ORDER BY reference ASC

SQL..

SELECT SUM(amount) FROM 6_gl_trans
        WHERE account='41001000' AND tran_date >= '2010-07-01' AND tran_date <= '2010-07-31' AND dimension_id = '1' AND dimension2_id = '0'

SQL..

SELECT SUM(amount) FROM 6_gl_trans
        WHERE account='41001000' AND tran_date >= '2010-07-01' AND tran_date <= '2010-07-31' AND dimension_id = '2' AND dimension2_id = '0'

  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,

Thanks for sending that.  I was thinking more of the config option "show_sql" rather than the sql trail.  That way you'll see the SQL right on the account percentages module page.

Still, if I'm reading the sql trail correctly, it looks like there's a number of errors with the SQL queries.  Lots of "can't update and can't retrieve" stuff.  (See entries 61-68 and 74-83)

When you use the Account Percentages Editor (not the "Run" part), does it save your changes between page loads?  That is, save the values in the editor, leave the page, and then come back to the editor.

Joe/Janusz, do those errors make sense to you?  Any thoughts?  It doesn't seem to be limited to the account percentages tool to me...

Tom

when I'm using the editor my settings is saved, I've checked the database too and here is the sql dump:
--------------------------------------------------------------------------------------------------------------------------------------------------------

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_acct_percent_tool`
--

CREATE TABLE IF NOT EXISTS `6_acct_percent_tool` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_code_source` varchar(11) NOT NULL DEFAULT '',
  `account_code_credit` varchar(11) NOT NULL DEFAULT '',
  `dimension1_credit` int(11) NOT NULL DEFAULT '0',
  `dimension2_credit` int(11) NOT NULL DEFAULT '0',
  `account_code_debit` varchar(11) NOT NULL DEFAULT '',
  `percent` double DEFAULT NULL,
  `inactive` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `6_acct_percent_tool`
--

INSERT INTO `6_acct_percent_tool` (`id`, `account_code_source`, `account_code_credit`, `dimension1_credit`, `dimension2_credit`, `account_code_debit`, `percent`, `inactive`) VALUES
(1, '41001000', '21103900', 0, 0, '61901900', 2.5, 0);
----------------------------------------------------------------------------------------------------------------------------------------------------------

when using the editor here is the show_sql in view mode:

SELECT * FROM 6_acct_percent_tool WHERE !inactive ORDER BY account_code_source

SQL..

SELECT * FROM 6_chart_master WHERE account_code='41001000'

SQL..

SELECT * FROM 6_chart_master WHERE account_code='21103900'

SQL..

SELECT * FROM 6_dimensions    WHERE id='0'

SQL..

SELECT * FROM 6_dimensions    WHERE id='0'

SQL..

SELECT * FROM 6_chart_master WHERE account_code='61901900'
---------

and this the show_sql when I try to edit the settings in editor:

Source Account:SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
            FROM 6_chart_master chart,6_chart_types type
            WHERE chart.account_type=type.id ORDER BY type.id,account_code
Account to credit:SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
            FROM 6_chart_master chart,6_chart_types type
            WHERE chart.account_type=type.id ORDER BY type.id,account_code
Dimension 1 to credit:SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference
Dimension 2 to credit:SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference
Account to debit:SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
            FROM 6_chart_master chart,6_chart_types type
            WHERE chart.account_type=type.id ORDER BY type.id,account_code

when I click run it seems there's sql  printed on screen but it's so fast so I can't put it here and then the journal entry show's with blank entries and here is the sql:

SELECT id FROM 6_quick_entries WHERE type='3'
SELECT * FROM 6_company WHERE coy_code=1
SELECT chart.account_code, chart.account_name, type.name, chart.inactive, type.id
            FROM 6_chart_master chart,6_chart_types type
            WHERE chart.account_type=type.id ORDER BY type.id,account_code

SELECT id, CONCAT(reference,'  ',name) as ref FROM 6_dimensions WHERE (closed=0 AND type_=1) ORDER BY reference

is there something that I miss?

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

Thanks for sending that.  I was thinking more of the config option "show_sql" rather than the sql trail.  That way you'll see the SQL right on the account percentages module page.

Still, if I'm reading the sql trail correctly, it looks like there's a number of errors with the SQL queries.  Lots of "can't update and can't retrieve" stuff.  (See entries 61-68 and 74-83)

When you use the Account Percentages Editor (not the "Run" part), does it save your changes between page loads?  That is, save the values in the editor, leave the page, and then come back to the editor.

Joe/Janusz, do those errors make sense to you?  Any thoughts?  It doesn't seem to be limited to the account percentages tool to me...

Tom

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=&#039;61901900&#039;', 1, 'could not get gl account'),
(42, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(43, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(41, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;21103900&#039;', 1, 'could not get gl account'),
(40, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;41001000&#039;', 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,&#039;  &#039;,name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(35, 'SELECT id, CONCAT(reference,&#039;  &#039;,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=&#039;61901900&#039;', 1, 'could not get gl account'),
(31, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(29, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;21103900&#039;', 1, 'could not get gl account'),
(30, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(27, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;11111990&#039;', 1, 'could not get gl account'),
(28, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;41001000&#039;', 1, 'could not get gl account'),
(26, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(25, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(23, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;11111990&#039;', 1, 'could not get gl account'),
(24, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;11111990&#039;', 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,&#039;  &#039;,name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(48, 'SELECT id, CONCAT(reference,&#039;  &#039;,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=&#039;41001000&#039;', 1, 'could not get gl account'),
(52, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;21103900&#039;', 1, 'could not get gl account'),
(53, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(54, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(55, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;61901900&#039;', 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,&#039;  &#039;,name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(60, 'SELECT id, CONCAT(reference,&#039;  &#039;,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=&#039;2.5&#039; 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=&#039;41001000&#039;', 1, 'could not get gl account'),
(65, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;21103900&#039;', 1, 'could not get gl account'),
(66, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(67, 'SELECT * FROM 6_dimensions    WHERE id=&#039;0&#039;', 1, 'The dimension could not be retrieved'),
(68, 'SELECT * FROM 6_chart_master WHERE account_code=&#039;61901900&#039;', 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,&#039;  &#039;,name) as ref FROM 6_dimensions WHERE (closed=0) ORDER BY reference', 1, ''),
(72, 'SELECT id, CONCAT(reference,&#039;  &#039;,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 = &#039;0&#039;', 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=&#039;3&#039;', 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=&#039;41001000&#039; AND tran_date &gt;= &#039;2010-07-01&#039; AND tran_date &lt;= &#039;2010-07-31&#039; AND dimension_id = &#039;1&#039; AND dimension2_id = &#039;0&#039;', 1, 'Transactions for account 41001000 could not be calculated'),
(81, 'SELECT SUM(amount) FROM 6_gl_trans\r\n        WHERE account=&#039;41001000&#039; AND tran_date &gt;= &#039;2010-07-01&#039; AND tran_date &lt;= &#039;2010-07-31&#039; AND dimension_id = &#039;2&#039; AND dimension2_id = &#039;0&#039;', 1, 'Transactions for account 41001000 could not be calculated'),
(82, 'SELECT id FROM 6_quick_entries WHERE type=&#039;3&#039;', 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,&#039;  &#039;,name) as ref FROM 6_dimensions WHERE (closed=0 AND type_=1) ORDER BY reference', 1, '');