Many thanks for the replies.
Regarding project cost tracking with dimensions, basically what would be needed is to be able to select a dimension for any revenue or expense transaction entered. It's less important for us to associate a dimension to an Item or Inventory, because costs on any given product or service won't always be incurred on the same project (dimension). Anyway, I'll look at it further to see, maybe everything will work for us as is.
Regarding the tax reports. I understand that the accumulation of the tax GL Lines are shown on the report but we need to be able to show the GST/PST breakdown, rather just one sum.
It seems all of the tax transactions are included in the "0_gl_trans" table in the database. I was able to write a script that parses the database for sales tax transactions and add up the taxes separately as GST & PST. I'd like to share it because it might help other Canadian companies out there. To use it you would need to enter your own tax accounts as per your chart of accounts on lines 33 and 34. If you upload it to a location other than the main FA directory, you'll have to change the path to the include files on lines 3 and 4 to match.
I'm not a programmer and I'm sure there's a better way of coding it. For example, the tax types from the "0_tax_types" database table could be automatically obtained by the script instead of hard-coding the tax GL codes. The tax codes could then be used to sum up the different tax transactions. Also, the script doesn't PDF export, it just displays the amounts on the screen.
The script works for us because we have the same tax account set for "Sales GL Account" and "Purchase GL Account" for each of the taxes. Eg. GST Sales and Purchase GL account is 2310. PST Sales and Purchase GL is 2320
If anyone has suggestions or comments on how to improve the script, maybe I'll take another look at it when I have time. Feel free to improve and share it yourself.
Save the code in a file with a .php extension and upload it to your FA directory. Here it is:
<?php
$page_security = 8;
include_once("includes/session.inc");
include_once("includes/ui.inc");
$startdate=$_POST['startdate'];
$enddate=$_POST['enddate'];
$js = "";
if ($use_popup_windows)
$js .= get_js_open_window(900, 500);
if ($use_date_picker)
$js .= get_js_date_picker();
page(_("GST/PST sales report"), false, false, "", $js);
If ($startdate==NULL Or $enddate==NULL)
{
start_form();
start_table($table_style);
table_section_title(_("Create a sales tax report"));
start_row();
date_cells(_("From:"), 'startdate', '', null, -30);
end_row();
start_row();
date_cells(_("To:"), 'enddate');
end_row();
submit_row('submit', 'Generate');
end_table();
end_form();
}
Else
{
//configuration area
$GSTaccount="2310"; //GST chart of accounts number
$PSTaccount="2320"; //PST chart of accounts number
//end of configuration area
$startdate=date2sql($startdate);
$enddate=date2sql($enddate);
$queryGST = "SELECT * FROM 0_gl_trans WHERE account='$GSTaccount' AND tran_date>='$startdate' AND tran_date<='$enddate'";
$resultGST = db_query($queryGST,"Error");
$GSTcollectible=0;
$GSTpayable=0;
$netGST=0;
while($rowGST = mysql_fetch_array($resultGST, MYSQL_ASSOC))
{
If ($rowGST['amount'] > 0)
{
$GSTcollectible = $GSTcollectible + $rowGST['amount'];
}
elseif ($rowGST['amount'] < 0)
{
$GSTpayable = $GSTpayable + $rowGST['amount'];
}
$netGST=$netGST + $rowGST['amount'];
}
$PSTcollectible=0;
$PSTpayable=0;
$netPST=0;
$queryPST = "SELECT * FROM 0_gl_trans WHERE account='$PSTaccount' AND tran_date>='$startdate' AND tran_date<='$enddate'";
$resultPST = mysql_query($queryPST);
while($rowPST = mysql_fetch_array($resultPST, MYSQL_ASSOC))
{
If ($rowPST['amount'] > 0)
{
$PSTcollectible = $PSTcollectible + $rowPST['amount'];
}
elseif ($rowPST['amount'] < 0)
{
$PSTpayable = $PSTpayable + $rowPST['amount'];
}
$netPST=$netPST + $rowPST['amount'];
}
$grandtotal=$netGST + $netPST;
$startdate=sql2date($startdate);
$enddate=sql2date($enddate);
start_table($table_style);
table_section_title(_("Tax report ($startdate to $enddate)"));
start_row();
label_cell("GST charged on sales (Output Tax):");
amount_cell($GSTpayable, $bold=false);
end_row();
start_row();
label_cell("GST paid on purchases (Input Tax):");
amount_cell($GSTcollectible, $bold=false);
end_row();
start_row();
label_cell("Net GST payable or collectible:");
amount_cell($netGST, $bold=true);
end_row();
start_row();
label_cell();
label_cell();
end_row();
start_row();
label_cell("PST paid on purchases (Input Tax):");
amount_cell($PSTcollectible, $bold=false);
end_row();
start_row();
label_cell("PST charged on sales (Output Tax):");
amount_cell($PSTpayable, $bold=false);
end_row();
start_row();
label_cell("Net PST payable or collectible:");
amount_cell($netPST, $bold=true);
end_row();
start_row();
label_cell();
label_cell();
end_row();
start_row();
label_cell("Total payable or refund:");
amount_cell($grandtotal, $bold=true);
end_row();
end_table();
}
end_page();
?>
Best regards.
