Try adding more columns - one for each envisaged payment and then you will find FA interesting....
5,151 04/03/2014 08:38:59 am
Re: What's the best online solution for this? (1 replies, posted in Accounts Receivable)
5,152 04/03/2014 08:37:14 am
Re: Back Button closes window (3 replies, posted in Report Bugs here)
Which Browser, Which version and What OS?
5,153 04/03/2014 08:36:11 am
Re: Activating Chart of Account (1 replies, posted in Banking and General Ledger)
Setup -> backup and then compare with any CoA you want, make changes, upload and restore.
5,154 04/03/2014 08:35:04 am
Re: how does FA perform with huge data? (1 replies, posted in Accounts Payable)
No problems
5,155 03/14/2014 01:08:04 pm
Re: Convert WHERE to LEFT JOIN in cust_trans_db SQL (2 replies, posted in Accounts Receivable)
Should we then use INNER JOINS for all joins here?
Thank you for the explanation of the differences.
5,156 03/14/2014 01:04:22 pm
Re: gl_db sql convert where to join (2 replies, posted in Banking and General Ledger)
Then INNER JOIN would be a good replacement for the last join as it mimics the WHERE very well and uses the indices when available.
5,157 03/13/2014 04:18:46 pm
Re: £ sign in currency (4 replies, posted in Report Bugs here)
The insert statement in many CoA sql sets including the default show "?" for "£". Try to use some hex editor or a special character aware text editor like Notepad++ to edit the sql files before use.
Also using a MySQL client like SQLyog (they have a free community edition) will get the job done.
5,158 03/13/2014 04:10:53 pm
Re: Newbie - In Need of Best Payroll Software (2 replies, posted in Accounts Payable)
Try to integrate OrangeHRM into FA. Download OrangeHRM v3.1.1 - 9.2 MB
5,159 03/13/2014 04:06:40 pm
Re: Release 2.3.20 (9 replies, posted in Announcements)
The attachment in my earlier post are the files that have changed since the previous version besides the SQLs that need to be executed for each company's db tables. These SQLs can be carried out first as they most probably will not have any dependency on your mods and certainly not on the rest of the standard code.
If you have the differences between FA v2.3.19 and you existing modded version, then you can incorporate the differences between the v2.3.19 and v2.3.20 into your modded version. Any extensions you have installed will make the issue a bit more involved just as any table schema changes in your modded version would entail as well.
5,160 03/13/2014 08:06:04 am
Re: Can't Delete a company (11 replies, posted in Report Bugs here)
Notes:
The php function rmdir can be used only if the directory is empty.
When safe mode is enabled, PHP checks whether the directory in which the script is operating has the same UID (owner) as the script that is being executed.
5,161 03/13/2014 07:58:45 am
Re: On going delivery (3 replies, posted in Accounts Payable)
Create a Goods in Transit account and use a journal voucher to move it to the customer when it reaches the destination.
5,162 03/13/2014 07:55:04 am
Re: Negative tax (3 replies, posted in Banking and General Ledger)
The PPH seems to be a tax rebate and can make the real VAT to be 10% - 2.1% = 7.9%.
Hence using PPH adjusted VAT:
Item Value = 10,000
7.9% VAT = 790
=======
Total = 10,7905,163 03/13/2014 07:48:41 am
Re: £ sign in currency (4 replies, posted in Report Bugs here)
What is the default collation and charset for your db / table / fields specifically the curr_symbol field?
INSERT INTO `0_currencies`(`currency`,`curr_abrev`,`curr_symbol`,`country`,`hundreds_name`,`auto_update`,`inactive`) VALUES ('Pounds','GBP','£','England','Pence',1,0);or
UPDATE `0_currencies SET currency='Pounds', curr_symbol='£', country='England', hundreds_name='Pence' WHERE curr_abbrev='GBP';The above code may appear same as yours but for the character set in question it may be binarily different. Hope you have utf-8 collation.
5,164 03/13/2014 07:32:16 am
Re: Release 2.3.20 (9 replies, posted in Announcements)
Diff from FA v2.3.19 to v2.3.20 with fixes from official repo till date along with 2 unreleased fixes as well listed in forum posts 4795 and 4796.
Needs the following SQLs to be executed for upgrades (alter the "0_" to suit your company table prefix):
ALTER TABLE `0_debtor_trans` ADD INDEX `order_` (`order_`);
ALTER TABLE `0_users` CHANGE `query_size` `query_size` TINYINT(1) UNSIGNED NOT NULL DEFAULT 10;The last sql referred to above is also an unreleased fix discussed at 4634 and is included in the sql files in this diff set and the CHANGELOG has been updated accordingly.
The above code is available in my unofficial FA GitHub Repo.
5,165 03/12/2014 04:01:16 pm
Topic: Convert WHERE to LEFT JOIN in cust_trans_db SQL (2 replies, posted in Accounts Receivable)
--- sales/includes/db/cust_trans_db.inc Mon Mar 10 16:17:12 2014
+++ sales/includes/db/cust_trans_db.inc Wed Dec 25 19:00:22 2013
@@ -377,19 +377,19 @@
Sum(line.quantity-line.qty_done) AS Outstanding,
Sum(line.qty_done) AS Done
FROM "
- .TB_PREF."sales_orders as sorder, "
- .TB_PREF."debtor_trans as trans, "
- .TB_PREF."debtor_trans_details as line, "
- .TB_PREF."debtors_master as debtor, "
- .TB_PREF."cust_branch as branch
- WHERE
- sorder.order_no = trans.order_ AND
- trans.debtor_no = debtor.debtor_no
- AND trans.type = ".ST_CUSTDELIVERY."
- AND line.debtor_trans_no = trans.trans_no
- AND line.debtor_trans_type = trans.type
- AND trans.branch_code = branch.branch_code
- AND trans.debtor_no = branch.debtor_no ";
+ .TB_PREF."sales_orders as sorder LEFT JOIN "
+ .TB_PREF."debtor_trans as trans
+ ON (sorder.order_no = trans.order_) LEFT JOIN "
+ .TB_PREF."debtor_trans_details as line
+ ON (line.debtor_trans_no = trans.trans_no
+ AND line.debtor_trans_type = trans.type) LEFT JOIN "
+ .TB_PREF."debtors_master as debtor
+ ON (trans.debtor_no = debtor.debtor_no) LEFT JOIN "
+ .TB_PREF."cust_branch as branch
+ ON (trans.branch_code = branch.branch_code
+ AND trans.debtor_no = branch.debtor_no)
+ WHERE
+ trans.type = ".ST_CUSTDELIVERY;
if ($_POST['OutstandingOnly'] == true) {
$sql .= " AND line.qty_done < line.quantity ";Joe, please include this patch in the core FA repo. It moves most of the WHERE statements to the LEFT JOINs.
5,166 03/12/2014 03:37:47 pm
Re: 403 Forbidden (3 replies, posted in Setup)
PM me the details - possibly time for an install from basics instead of thru the not so delicious simple scripts (quite outdated I guess).
5,167 03/12/2014 03:36:27 pm
Re: Size limit on restoring backup (14 replies, posted in Reporting)
FTP the sql file to the company/#/backups folder and then do a restore.
5,168 03/12/2014 03:33:05 pm
Re: Release 2.3.20 (9 replies, posted in Announcements)
Use WinMerge to get your diffs or install TortoiseGit and load in v2.3.19 and then v2.3.20 and possibly a post 2.3.20 snapshot. Easiest way like Joe said is to overwrite the files or do a fresh install and restore your db.
Will try to make a diff soon. Trying to get my version to sync with the official version first. Been away from the FA scene for a while now and need to check out the various changes done.
5,169 03/12/2014 03:30:02 pm
Re: 404 error for pdf reports after upgrade (14 replies, posted in Installation)
PHP 5.4 may have issues as FA v2.3.x is tested for a max PHP version of 5.3.x.
Also your db schema may have to be compared with the latest ones for anomalies.
Try a new install with noting from your backups and see if PDF works. Then migrate your data step by step.
5,170 03/12/2014 03:26:20 pm
Re: Restored hosting account problems with FA (3 replies, posted in Installation)
PM me the details and possibly the sql dump zipped. Will try a schema compare for what might be needed. Did you install any extensions?
5,171 03/12/2014 03:22:22 pm
Re: opening balance (5 replies, posted in Banking and General Ledger)
One Journal voucher addressing all opening balances would contra to a nett of zero currency value.
5,172 03/12/2014 03:20:22 pm
Re: Add New Tab on Top Menu (2 replies, posted in FA Modifications)
Create a module and install and activate it. See the FA Wiki.
5,173 03/12/2014 03:19:07 pm
Re: Item and Inventory (8 replies, posted in Items and Inventory)
It is "FROM" instead of "FORM" in the SQL.
5,174 03/12/2014 03:17:56 pm
Re: Cannot install new extensions—"could not open" (6 replies, posted in Modules Add-on's)
Take them from my GitHub Unofficial Repo - extension files.
5,175 03/12/2014 03:14:55 pm
Topic: gl_db sql convert where to join (2 replies, posted in Banking and General Ledger)
--- gl/includes/db/gl_db_bank_trans.inc Mon Mar 10 16:17:11 2014
+++ gl/includes/db/gl_db_bank_trans.inc Sat Jan 04 02:23:22 2014
@@ -66,17 +66,17 @@
function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id=null)
{
$sql = "SELECT bt.*, act.*,
- IFNULL(abs(dt.ov_amount), IFNULL(ABS(st.ov_amount), bt.amount)) settled_amount,
- IFNULL(abs(dt.ov_amount/bt.amount), IFNULL(ABS(st.ov_amount/bt.amount), 1)) settle_rate,
- IFNULL(debtor.curr_code, IFNULL(supplier.curr_code, act.bank_curr_code)) settle_curr
+ IFNULL(ABS(dt.ov_amount), IFNULL(ABS(st.ov_amount), bt.amount)) AS settled_amount,
+ IFNULL(ABS(dt.ov_amount/bt.amount), IFNULL(ABS(st.ov_amount/bt.amount), 1)) AS settle_rate,
+ IFNULL(debtor.curr_code, IFNULL(supplier.curr_code, act.bank_curr_code)) AS settle_curr
FROM ".TB_PREF."bank_trans bt
- LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=bt.type AND dt.trans_no=bt.trans_no
- LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = dt.debtor_no
- LEFT JOIN ".TB_PREF."supp_trans st ON st.type=bt.type AND st.trans_no=bt.trans_no
- LEFT JOIN ".TB_PREF."suppliers supplier ON supplier.supplier_id = st.supplier_id,
- ".TB_PREF."bank_accounts act
- WHERE act.id=bt.bank_act ";
+ LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=bt.type AND dt.trans_no=bt.trans_no
+ LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = dt.debtor_no
+ LEFT JOIN ".TB_PREF."supp_trans st ON st.type=bt.type AND st.trans_no=bt.trans_no
+ LEFT JOIN ".TB_PREF."suppliers supplier ON supplier.supplier_id = st.supplier_id
+ LEFT JOIN ".TB_PREF."bank_accounts act ON act.id = bt.bank_act
+ WHERE 1 ";
if ($type != null)
$sql .= " AND bt.type=".db_escape($type);
if ($trans_no != null)
@@ -85,7 +85,7 @@
$sql .= " AND bt.person_type_id = ".db_escape($person_type_id);
if ($person_id != null)
$sql .= " AND bt.person_id = ".db_escape($person_id);
- $sql .= " ORDER BY trans_date, bt.id";
+ $sql .= " ORDER BY bt.trans_date, bt.id";
return db_query($sql, "query for bank transaction");
}Converting the WHERE static clause to a LEFT JOIN would be elegant. Joe, can you add this please.