It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Posts by apmuthu
Addendum to your commit:
In rep102.php, $sign is computed and then used in the sql so it is okay.
In rep107.php $sign is assigned 1 and used and hence it can be fully eliminated unless there are some CAST issues.
In rep113.php, $sign is assigned -1 and used and may be removed likewise and the sign made inline.
In rep109.php, rep111.php and rep209.php just as in rep110.php here, $sign is not assigned anywhere in the script and hence can be removed as done herein.
This is a quirk of some set of PHP 5.6.x versions as listed here and here - specifically the PHP 5.6 bug.
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.
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?
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_flagshould now be changed to:
item.units, item.mb_flag, item.long_descriptionto 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.
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.
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?
Sales Transactions Permissions:
Sales orders edition
Sales deliveries edition
Sales invoices edition@trafficpest: If the Balance is 0 (zero) then it is best suppressed.
@joe: Can it be an option in the existing one?.
You will need to incorporate this fix as well.
Attachment has it done.
@joe: Want to include it in the core?
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?
After a Customer Payment is made, it needs to be Allocated to one or more invoices.
@itronics: Thanks. Committed.
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.
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.
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.
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.
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.
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.
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.
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.
@itronics committed fix.
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.
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;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.
FrontAccounting forum → Posts by apmuthu
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.