2,101 Reply by apmuthu

(3 replies, posted in Report Bugs here)

This is a quirk of some set of PHP 5.6.x versions as listed here and here - specifically the PHP 5.6 bug.

Go to forum: Report Bugs here

2,102 Reply by apmuthu

(3 replies, posted in Items and Inventory)

https://frontaccounting.com/punbb/viewtopic.php?id=1294
https://frontaccounting.com/punbb/viewtopic.php?id=4202
https://frontaccounting.com/punbb/viewtopic.php?id=1112

No standard extension in the Official PKG repo as yet.

Go to forum: Items and Inventory

2,103 Reply by apmuthu

(5 replies, posted in Report Bugs here)

Replace line 161 in reporting/rep110.php:

                    $DisplayFreight = number_format2($sign*$myrow["ov_freight"],$dec);

with:

                    $DisplayFreight = number_format2($myrow["ov_freight"],$dec);

The $sign is not defined anywhere in the report and may be present through some include when a sales order is being printed. The $sign was not present in FA 2.3.x.

@joe: Should this be committed?

Go to forum: Report Bugs here

2,104 Reply by apmuthu

(7 replies, posted in Items and Inventory)

In general the wiki article referred to in my post still applies but needs to be modified to suit FA 2.4.3. It was considered to be a workaround as the DB schema was frozen in FA 2.3.x then and hence we needed it to accommodate several data elements in the single description field and then parse them out when needed.

Line 22 in the same file in FA 2.4.3:

        item.units, item.mb_flag

should now be changed to:

        item.units, item.mb_flag, item.long_description

to use the workaround.

In FA 2.4.3, it was sought to be made native but the devs had not found enough time and sponsors to get it done and it possibly remains as an extension in some private repos only. The correct way to implement this would be to create a separate extension that would use a new table in a 1:1 relationship with the existing stock_master table used.

Go to forum: Items and Inventory

2,105 Topic by apmuthu

(2 replies, posted in Misc. Charts of Accounts)

EG, FR, GB, IN, SG, US, CA, AU English charts have been committed in synch upto this core commit with fixes till date.

@itronics: can update the relevant official pkg charts.

Go to forum: Misc. Charts of Accounts

2,106 Reply by apmuthu

(3 replies, posted in Translations)

Read this post.

Flesh out the function price_in_words() and translate the lang/new_language_template/locale.inc file and copy it over as lang/es_AR/locale.inc.

$locale path is defined having the filename locale.inc in line 63 and used in line 64 of includes/lang/language.inc file.

The local default function price_in_words() is defined in includes/ui/ui_view.inc and relies on the function _number_to_words() also defined in the same file.

@joe: what is the standard way of providing the number to words in each language?

Go to forum: Translations

2,107 Reply by apmuthu

(6 replies, posted in Setup)

Sales Transactions Permissions:

Sales orders edition
Sales deliveries edition
Sales invoices edition

Go to forum: Setup

2,108 Reply by apmuthu

(13 replies, posted in Reporting)

@trafficpest: If the Balance is 0 (zero) then it is best suppressed.
@joe: Can it be an option in the existing one?.

Go to forum: Reporting

2,109 Reply by apmuthu

(13 replies, posted in Reporting)

You will need to incorporate this fix as well.

Attachment has it done.

@joe: Want to include it in the core?

Go to forum: Reporting

2,110 Reply by apmuthu

(2 replies, posted in FA Modifications)

It actually looks nice in just 2 lines instead of 3 line header table.

@joe: Want to add it into the core or does it go against intuition (needing to be first seen) and / or familiarity hitherto?

Go to forum: FA Modifications

2,111 Reply by apmuthu

(3 replies, posted in Accounts Receivable)

After a Customer Payment is made, it needs to be Allocated to one or more invoices.

Go to forum: Accounts Receivable

2,112 Reply by apmuthu

(1 replies, posted in Misc. Charts of Accounts)

@itronics: Thanks. Committed.

Go to forum: Misc. Charts of Accounts

2,113 Topic by apmuthu

(1 replies, posted in Misc. Charts of Accounts)

The recent commit to the standard Charts missed out the Deferred Income Account in the chart_master table and left the corresponding SysPrefs entry blank. Correct it with (if not already done) substituting appropriate table prefix:

INSERT INTO `0_chart_master` (`account_code`,`account_code2`,`account_name`,`account_type`,`inactive`) VALUES ('2105', '', 'Deferred Income Account', '4', '0');

INSERT INTO `0_payment_terms` (`terms_indicator`,`terms`,`days_before_due`,`day_in_following_month`,`inactive`) VALUES ('5', 'Prepaid Sale', '-1', '0', '0');

UPDATE `0_sys_prefs` SET `value`='2105' WHERE `name`='deferred_income_act';

If the entry is not there in the sys_prefs table, add it in with:

INSERT INTO `0_sys_prefs` (`name`,`category`,`type`,`length`,`value`) VALUES ('deferred_income_act', 'glsetup.sales', 'varchar', '15', '2105');

Note: This is only for those who use the standard en_US-new.sql and en_US-demo.sql and their translations.

Go to forum: Misc. Charts of Accounts

2,114 Reply by apmuthu

(3 replies, posted in Translations)

Assuming you are using FA 2.4.3+ and the official es_AR translation, update your file at lang/es_AR/LC_MESSAGES/es_AR-2.4.1-2.mo.

Go to forum: Translations

2,115 Topic by apmuthu

(0 replies, posted in Accounts Receivable)

In the making of a New Invoice and receiving payment for it, the following SQLs by operation occur:

1. Make a Sales Order, effect a Customer Delivery and then raise an invoice for it (A Non Cash Direct Invoice):

-- Table Prefix _1
-- Order No. 8
-- Sales Delivery No. 5
-- Sales Invoice No. 5
-- debtor_no 4

-- ST_SALESORDER
INSERT INTO `1_sales_orders` VALUES
('8', '30', '1', '0', '1', '1', 'auto', '', NULL, '2018-01-07', '1', '1', 'N/A', NULL, NULL, 'Donald Easter LLC', '0', 'DEF', '2018-01-17', '3', '400', '0', '0');
INSERT INTO `1_sales_order_details` VALUES
(NULL, '8', '30', '103', 'iPhone Cover Case', '2', '50', '2', '0', '0'),
(NULL, '8', '30', '101', 'iPad Air 2 16GB', '1', '300', '1', '0', '0');
INSERT INTO `1_audit_trail` VALUES
(NULL, '30', '8', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');

-- ST_CUSTDELIVERY
INSERT INTO `1_stock_moves` VALUES
(NULL, '5', '103', '13', 'DEF', '2018-01-07', '50', 'auto', '-2', '10'),
(NULL, '5', '101', '13', 'DEF', '2018-01-07', '300', 'auto', '-1', '200');
INSERT INTO `1_debtor_trans` VALUES
('5', '13', '1', '1', '1', '2018-01-07', '2018-01-17', 'auto', '1', '8', '400', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '3', '1');
INSERT INTO `1_debtor_trans_details` VALUES
(NULL, '5', '13', '103', 'iPhone Cover Case', '50', '2.38', '2', '0', '10', '2', '11'),
(NULL, '5', '13', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '1', '12');
INSERT INTO `1_gl_trans` VALUES
(NULL, '13', '5', '2018-01-07', '5010', '', '20', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '1510', '', '-20', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '5010', '', '200', '0', '0', NULL, NULL),
(NULL, '13', '5', '2018-01-07', '1510', '', '-200', '0', '0', NULL, NULL);
INSERT INTO `1_trans_tax_details` VALUES (
(NULL, '13', '5', '2018-01-07', '1', '5', '1', '1', '380.95', '19.05', 'auto', NULL);
INSERT INTO `1_audit_trail` VALUES
(NULL, '13', '5', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');

ST_SALESINVOICE
INSERT INTO `1_refs` VALUES
('5', '10', '001/2018');
INSERT INTO `1_debtor_trans` VALUES
('5', '10', '0', '1', '1', '2018-01-07', '2018-01-17', '001/2018', '1', '8', '400', '0', '0', '0', '0', '0', '0', '1', '1', '0', '0', '3', '1'),
INSERT INTO `1_debtor_trans_details` VALUES
(NULL, '5', '10', '103', 'iPhone Cover Case', '50', '2.38', '2', '0', '10', '0', '13'),
(NULL, '5', '10', '101', 'iPad Air 2 16GB', '300', '14.29', '1', '0', '200', '0', '14');
INSERT INTO `1_gl_trans` VALUES
(NULL, '10', '5', '2018-01-07', '4010', '', '-95.24', '0', '0', NULL, NULL),
(NULL, '10', '5', '2018-01-07', '4010', '', '-285.71', '0', '0', NULL, NULL),
(NULL, '10', '5', '2018-01-07', '1200', '', '400', '0', '0', '2', '1'),
(NULL, '10', '5', '2018-01-07', '2150', '', '-19.05', '0', '0', NULL, NULL);
INSERT INTO `1_trans_tax_details` VALUES (
(NULL, '10', '5', '2018-01-07', '1', '5', '1', '1', '380.95', '19.05', '001/2018', '0');
INSERT INTO `1_audit_trail` VALUES
(NULL, '10', '5', '1', '2018-01-07 10:09:06', NULL, '3', '2018-01-07', '0');

2. Receive a payment for the customer and allocate it entireely to the above invoice:

SET @alloc=400;
SET @bankcharge=5;

INSERT INTO `1_refs` VALUES
('4', '12', '001/2018');
INSERT INTO `1_bank_trans` VALUES
(NULL, '12', '4', '1', '001/2018', '2018-01-07', @alloc-@bankcharge, '0', '0', '2', '1', NULL);
INSERT INTO `1_comments` VALUES
('12', '4', '2018-01-07', 'by outstation cheque');
INSERT INTO `1_cust_allocations` VALUES
(NULL, '1', @alloc, '0000-00-00', '4', '12', '5', '10');
INSERT INTO `1_debtor_trans` VALUES
('4', '12', '0', '1', '1', '2018-01-07', '0000-00-00', '001/2018', '0', '0', '400', '0', '0', '0', '0', @alloc, '0', '1', '0', '0', '0', NULL, '0'),
UPDATE `1_debtor_trans` SET `alloc`=`ov_amount`-@alloc WHERE `type`='10' AND `trans_no`='5' AND `debtor_no`='4'
INSERT INTO `1_gl_trans` VALUES
(NULL, '12', '4', '2018-01-07', '1060', '', @alloc-@bankcharge, '0', '0', NULL, NULL),
(NULL, '12', '4', '2018-01-07', '1200', '', -@alloc, '0', '0', '2', '1'),
(NULL, '12', '4', '2018-01-07', '5690', '', @bankcharge, '0', '0', NULL, NULL);
INSERT INTO `1_audit_trail` VALUES
(NULL, '12', '4', '1', '2018-01-07 10:13:49', NULL, '3', '2018-01-07', '0');

Note that the date of allocation is "0000-00-00" in 2 places above.
The actual date of payment is entered in the case of a Direct Invoice in Cash mode.
An audit trail entry is made after each transaction.

This should be useful for anyone trying to directly insert transactions..
All Account codes and rates are as per the demo chart of accounts  - en_US-demo.sql.

Go to forum: Accounts Receivable

2,116 Reply by apmuthu

(1 replies, posted in Setup)

The db functions have now been moved to includes/db/connect_db_mysql.inc and includes/db/connect_db_mysqli.inc where the latter is the default preferred choice.

Go to forum: Setup

2,117 Reply by apmuthu

(13 replies, posted in Setup)

There is no guarantee that the the $ref will have the $ref of the trans_no as the first part. It is better to rely on the pattern to extract it or rely on the trans_no directly. Consider the case of straddling fiscal years.

Go to forum: Setup

2,118 Reply by apmuthu

(10 replies, posted in Setup)

Try the SQLyog community edition for a start - all Windows GUI for the MySQL Database.
Direct import from CSV and table creation / altering too in the GUI.
The "History" tab will show the SQLs used.

https://github.com/webyog/sqlyog-commun … /Downloads

Everyone starts off not knowing MySQL and assuming FA not to be a good fit but in time you will find is as user friendly as possible as any web app can be. Scout around for anything more user friendly and let us know so that FA can catchup as well.

Go to forum: Setup

2,119 Reply by apmuthu

(10 replies, posted in Setup)

Making an Excel / Access table with the above data is trivial. It then will not need the BTrieve ODBC Driver and use the native MS Jet ODBC Driver for Excel / Access.

FICA (Employee and Employer Witholding Tax) - US - Federal Insurance Contributions Act
FUTA (Employer Only) - US - Federal Unemployment Tax Act
SICA (Employer Only) - US - State Unemployment Tax Act

So much for ease of doing business in the USA! No wonder they find it attractive to import from the rest of the world! All they need to do is print more money and run up trillions of dollars of debt.... Every country should follow suit - then there would be no one willing to export to anyone....in others currency! Then no more multi-currency in FA or no FA at all!

Wall Street = Legalised Casino.
Monetary Policy = Con Game.

The Code Book from The Bangladesh Mathematical Foundation.

smile

Go to forum: Setup

2,120 Reply by apmuthu

(23 replies, posted in FA Modifications)

FA has just 1 series for each Voucher Type. A default Prefix can be used. The same number of characters must be in the final invoice number and it is editable. It will be preferable to have the same running number but with different prefixes to identify the Client/Location pair. Since both Client and Location are available in the transaction detail, a well crafted sql will be able to report when grouped by accordingly so as to remain within FA's single numbering series for all sales.

Go to forum: FA Modifications

2,121 Reply by apmuthu

(5 replies, posted in Report Bugs here)

@itronics committed fix.

Go to forum: Report Bugs here

2,122 Reply by apmuthu

(3 replies, posted in Report Bugs here)

Ask someone else to view the page and see if they can see it or compare with their installs. We can all generate Arabic PDFs. Hope you have the necessary fonts installed in FA.

Go to forum: Report Bugs here

2,123 Reply by apmuthu

(5 replies, posted in Report Bugs here)

Follow @joe's instructions by choosing the smaller number in "From" and larger Number in "To".

If the problem still occurs in your installation, then troubleshoot as below.

The function get_sales_order_header() in sales/includes/db/sales_order_db.inc is where this error emanates.
It is possible that the database may be missing some records.
Try this set of SQLs (with your table prefixes - set as 1_ here in 9 places) for each of the error pairs of type and order number in phpMyAdmin or any MySQL client:

SET @typ = 30;
SET @ono = 28; -- test with 29, 38, 71, 82 and 462 where the error occurs as well

SELECT sorder.*,
      cust.name,
      cust.curr_code,
      cust.address,
      loc.location_name,
      cust.discount,
      stype.sales_type,
      stype.id AS sales_type_id,
      stype.tax_included,
      stype.factor,
       ship.shipper_name,
      tax_group.name AS tax_group_name,
      tax_group.id AS tax_group_id,
      cust.tax_id,
      sorder.alloc,
      IFNULL(allocs.ord_allocs, 0)+IFNULL(inv.inv_allocs ,0) AS sum_paid,
      sorder.prep_amount>0 AS prepaid
    FROM 1_sales_orders sorder
            LEFT JOIN (SELECT trans_no_to, SUM(amt) ord_allocs FROM 1_cust_allocations
                WHERE trans_type_to=30 AND trans_no_to=@ono GROUP BY trans_no_to)
                 allocs ON sorder.trans_type=30 AND allocs.trans_no_to=sorder.order_no
            LEFT JOIN (SELECT order_, SUM(alloc) inv_allocs FROM 1_debtor_trans 
                WHERE TYPE=10 AND order_=@ono  GROUP BY order_)
                 inv ON sorder.trans_type=30 AND inv.order_=sorder.order_no
            LEFT JOIN 1_shippers ship ON  ship.shipper_id = sorder.ship_via,
      1_debtors_master cust,
      1_sales_types stype, 
      1_tax_groups tax_group, 
      1_cust_branch branch,
      1_locations loc
    WHERE sorder.order_type=stype.id
        AND branch.branch_code = sorder.branch_code
        AND branch.tax_group_id = tax_group.id
        AND sorder.debtor_no = cust.debtor_no
        AND loc.loc_code = sorder.from_stk_loc
        AND sorder.trans_type = @typ
        AND sorder.order_no = @ono;

Go to forum: Report Bugs here

2,124 Reply by apmuthu

(13 replies, posted in Reporting)

The fix for the invoice range is not to rely on any pattern but to rely on the transaction number and type directly. Hence, replace lines 39-40 of reporting/rep107.php:

            ." AND trans.reference>=".db_escape(get_reference(ST_SALESINVOICE, $from))
            ." AND trans.reference<=".db_escape(get_reference(ST_SALESINVOICE, $to))

with:

            ." AND trans.trans_no BETWEEN ".db_escape($from)." AND ".db_escape($to)

This will result in the $sql in the get range function to be like:

SELECT trans.trans_no, trans.reference
FROM &TB_PREF&debtor_trans trans 
    LEFT JOIN &TB_PREF&voided voided ON trans.type=voided.type AND trans.trans_no=voided.id
WHERE trans.type=10 AND ISNULL(voided.id) 
    AND trans.trans_no BETWEEN '1' AND '5' 
ORDER BY trans.tran_date, trans.reference

@joe: see if fit to commit.

Go to forum: Reporting

2,125 Reply by apmuthu

(10 replies, posted in Setup)

Once you get the CSV of the customers and assuming you are using the latest FA v2.4.3 and the sql/en_US-new.sql Chart of Accounts, manually enter one typical ustomer and see the diffs in the before and after backups and construct your sqls accordingly. This will need to be carefully done in a cloned container and then migrated to the production install. The same can be done for vendors (suppliers), items (inventory), etc.

Try to revert to an earlier version of DacEasy before Sage took over. You will most probably find a non expiring version.....MBAs tend to enforce migration by such blackmail tactics - they probably cannot survive in a non-monopolistic environment - they will however wax eloquent on the virtues of their idea of a free "market economy".

Go to forum: Setup