1 Reply by advocaat.pollet

(3 replies, posted in Banking and General Ledger)

Hi gj6n68,

I made a taxreport for Belgian taxes.  You can find it here : https://cloud.pollet.legal/index.php/s/GFSYo7A9zzBTFRg
If you study it very thoroughly, you will understand what to do for your european country.
I use the taxnames to get the taxes in the right grids of the Belgian VAT-declaration.  In the names you will find the grid-numbers.  The taxnames are also important to calculate the deductable part of taxes in purchases.
The taxreport makes the difference between invoices and creditnotes.
I have a lot of taxtypes, also for inter european sales/purchases (0-taxes for sales, 'negative' taxes for european purchases (tax shifted (reverse charged)))

BELGIAN TAXTYPES (VAT, 'BTW' in Dutch) :

### Structure of table `11_tax_types` ###

DROP TABLE IF EXISTS `11_tax_types`;

CREATE TABLE `11_tax_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rate` double NOT NULL DEFAULT '0',
  `sales_gl_code` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `purchasing_gl_code` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `name` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `inactive` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

### Data of table `11_tax_types` ###

INSERT INTO `11_tax_types` VALUES
('11', '21', '45104', '8005', 'BTW031.0.210.1.00.BED&GBM+M03VT54.M49AT64|__', '0'),
('12', '12', '45105', '8004', 'BTW030.0.120.1.00.BED&GBM+M02VT54.M49AT64|__', '0'),
('13', '6', '45106', '8003', 'BTW029.0.060.1.00.BED&GBM+M01VT54.M49AT64|__', '0'),
('25', '0', '8122', '8009', 'BTW120.0.000.1.00.EUG/HG/BM+M46__.M48__|__', '0'),
('26', '21', '45100', '41120', 'BTW004.0.210.1.00.BEHGM03VT54M49AT64M81AT59M85VT63', '0'),
('27', '21', '8103', '41125', 'BTW022.0.210.1.00.BEBM+_|M83AT59.M85VT63', '0'),
('28', '10.5', '8160', '64025', 'BTW393.BE|EU+BTW NAF [$]', '0'),
('29', '10.5', '8113', '41130', 'BTW044.0.210.0.50.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('32', '3', '8157', '64022', 'BTW371.BE|EU+BTW NAF [$]', '0'),
('36', '6', '8150', '41113', 'BTW174.0.060.1.00.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('37', '12', '8152', '41112', 'BTW176.0.120.1.00.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('38', '21', '8155', '41111', 'BTW179.0.210.1.00.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('42', '6', '8112', '41133', 'BTW041.0.120.0.50.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('43', '3', '8111', '41132', 'BTW036.0.060.0.50.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('47', '-6', '8119', '45132', 'BTW081.0.060.1.00.BEWOG-_|M87VT56.M85AT62+(N)AF[%]', '0'),
('49', '-21', '8121', '45130', 'BTW082.0.210.1.00.BEWOG-_|M87VT56.M85AT62+(N)AF[%]', '0'),
('53', '-6', '8131', '45107', 'BTW144.0.060.1.00.EUG-_|M86VT55.M84AT62+(N)AF[%]', '0'),
('54', '-12', '8132', '45108', 'BTW145.0.120.1.00.EUG-_|M86VT55.M84AT62+(N)AF[%]', '0'),
('55', '-21', '8133', '45109', 'BTW146.0.210.1.00.EUG-_|M86VT55.M84AT62+(N)AF[%]', '0'),
('56', '3', '8139', '41114', 'BTW172.0.060.0.50.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('58', '10.5', '8151', '41115', 'BTW175.0.210.0.50.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('59', '12', '45102', '41121', 'BTW003.0.120.1.00.BEHGM02VT54M49AT64M81AT59M85VT63', '0'),
('60', '6', '45103', '41122', 'BTW002.0.060.1.00.BEHGM01VT54M49AT64M81AT59M85VT63', '0'),
('68', '0', '8117', '8007', 'BTW068.0.060.1.00!BED&G+_|M82__.M85__+NAF', '0'),
('79', '0', '8118', '8008', 'BTW080.0.000.1.00.BEWOG+M45__.M49__|__', '0'),
('80', '6', '8108', '41131', 'BTW039.0.060.1.00.BED&G+_|M82AT59.M85VT63', '0'),
('81', '12', '8109', '41134', 'BTW054.0.120.1.00.BED&G+_|M82AT59.M85VT63', '0'),
('82', '21', '8110', '41135', 'BTW060.0.210.1.00.BED&G+_|M82AT59.M85VT63', '0'),
('87', '0', '8106', '8002', 'BTW028.0.000.1.00.BED&GBM+M01__.M49__|__', '0'),
('88', '0', '8101', '8001', 'BTW001.0.000.1.00.BEHG+M01__.M49__|M81__.M85__', '0'),
('90', '6', '8159', '64024', 'BTW390.BE|EU+BTW NAF [$]', '0'),
('91', '12', '8161', '64026', 'BTW394.BE|EU+BTW NAF [$]', '0'),
('92', '21', '8163', '64028', 'BTW400.BE|EU+BTW NAF [$]', '0'),
('93', '0', '8123', '8010', 'BTW149.0.000.1.00.EUD+M44__.M48__|__', '0'),
('94', '0', '8107', '8006', 'BTW033.0.000.1.00.BED&G+_|M82__.M85__', '0'),
('97', '0', '8130', '8012', 'BTW140.0.000.1.00.EUG+_|M82M86_.M84_', '0'),
('98', '0', '8124', '8011', 'BTW130.0.000.1.00.EUHG+_|M81M86_.M84_', '0'),
('99', '-6', '8125', '45110', 'BTW131.0.060.1.00.EUHG-_|M86VT55.M84AT62+AF[%]', '0'),
('101', '-12', '8126', '45111', 'BTW132.0.120.1.00.EUHG-_|M86VT55.M84AT62+AF[%]', '0'),
('102', '-21', '8127', '45112', 'BTW133.0.210.1.00.EUHG-_|M86VT55.M84AT62+AF[%]', '0'),
('103', '0', '8134', '8013', 'BTW151.0.000.1.00.EUD+_|M82M88_.M84_', '0'),
('107', '-6', '8135', '45122', 'BTW154.0.060.1.00.EUD-_|M88VT55.M84AT62+(N)AF[%]', '0'),
('109', '-12', '8136', '45121', 'BTW155.0.120.1.00.EUD-_|M88VT55.M84AT62+(N)AF[%]', '0'),
('111', '-21', '8137', '45120', 'BTW156.0.210.1.00.EUD-_|M88VT55.M84AT62+(N)AF[%]', '0'),
('113', '-21', '8129', '45115', 'BTW138.0.210.1.00.EUBM-_|M86VT55.M84AT62+AF[%]', '0'),
('114', '6', '8102', '41126', 'BTW012.0.060.1.00.BEBM+_|M83AT59.M85VT63', '0'),
('115', '12', '8104', '41127', 'BTW016.0.120.1.00.BEBM+_|M83AT59.M85VT63', '0'),
('116', '10.5', '8105', '41128', 'BTW015.0.210.0.50.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('117', '-6', '8128', '45116', 'BTW136.0.060.1.00.EUBM-_|M86VT55.M84AT62+AF[%]', '0'),
('118', '18.9', '8162', '64027', 'BTW399.BE|EU+BTW NAF [$]', '0'),
('119', '5.25', '8158', '64023', 'BTW380.BE|EU+BTW NAF [$]', '0'),
('120', '2.1', '8114', '41136', 'BTW035.0.210.0.10.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('121', '15.75', '8115', '41137', 'BTW057.0.210.0.75.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('122', '0', '8170', '8014', 'BTW999._|_ !! MAAK EEN BTW-REGEL BIJ !! [$]', '0'),
('124', '18.9', '8154', '41117', 'BTW178.0.210.0.90.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('125', '5.25', '8140', '41118', 'BTW173.0.210.0.25.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('126', '18.9', '8116', '41138', 'BTW059.0.210.0.90.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('127', '2.1', '8138', '41119', 'BTW171.0.210.0.10.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('128', '2.1', '8156', '64021', 'BTW370.BE|EU+BTW NAF [$]', '0'),
('129', '15.75', '8153', '41116', 'BTW177.0.210.0.75.EUHG+_|M81AT59.*VT61 AF[^]', '0'),
('130', '2.1', '8171', '41149', 'BTW187.0.210.0.10.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('131', '3', '8172', '41144', 'BTW189.0.060.0.50.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('132', '5.25', '8173', '41148', 'BTW191.0.210.0.25.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('133', '6', '8174', '41143', 'BTW192.0.060.1.00.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('134', '10.5', '8175', '41145', 'BTW196.0.210.0.50.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('136', '15.75', '8177', '41146', 'BTW201.0.210.0.75.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('137', '18.9', '8178', '41147', 'BTW204.0.210.0.90.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('138', '21', '8179', '41141', 'BTW205.0.210.1.00.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('139', '21', '8180', '41151', 'BTW232.0.210.1.00.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('140', '18.9', '8181', '41157', 'BTW231.0.210.0.90.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('141', '15.75', '8182', '41156', 'BTW229.0.210.0.75.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('142', '12', '8183', '41152', 'BTW226.0.120.1.00.EUBM+_|M83AT59.*VT61 AF[^]    ', '0'),
('143', '10.5', '8184', '41155', 'BTW215.0.210.0.50.EUBM+_|M83AT59.*VT61 AF[^]    ', '0'),
('144', '6', '8185', '41153', 'BTW212.0.060.1.00.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('145', '5.25', '8186', '41158', 'BTW211.0.210.0.25.EUBM+_|M83AT59.*VT61 AF[^]    ', '0'),
('146', '3', '8187', '41154', 'BTW209.0.060.0.50.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('147', '2.1', '8188', '41159', 'BTW208.0.210.0.10.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('148', '4.2', '8199', '41160', 'BTW210.0.210.0.20.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('149', '6.3', '8200', '41171', 'BTW213.0.210.0.30.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('150', '8.4', '8201', '41172', 'BTW214.0.210.0.40.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('151', '12.6', '8202', '41173', 'BTW227.0.210.0.60.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('152', '14.7', '8203', '41174', 'BTW228.0.210.0.70.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('153', '16.8', '8204', '41175', 'BTW230.0.210.0.80.EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('154', '3', '8205', '41176', 'BTW009.0.060.0.50.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('155', '2.1', '8198', '41170', 'BTW008.0.210.0.10.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('156', '4.2', '8197', '41169', 'BTW010.0.210.0.20.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('157', '5.25', '8196', '41168', 'BTW011.0.210.0.25.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('158', '6.3', '8195', '41167', 'BTW013.0.210.0.30.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('159', '8.4', '8194', '41166', 'BTW014.0.210.0.40.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('160', '12.6', '8193', '41165', 'BTW017.0.210.0.60.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('161', '14.7', '8192', '41164', 'BTW018.0.210.0.70.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('162', '16.8', '8190', '41162', 'BTW020.0.210.0.80.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('163', '15.75', '8191', '41163', 'BTW019.0.210.0.75.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('164', '18.9', '8189', '41161', 'BTW021.0.210.0.90.BEBM+_|M83AT59.M85VT63+NAF', '0'),
('165', '16.8', '8207', '41178', 'BTW058.0.210.0.80.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('166', '12.6', '8208', '41179', 'BTW055.0.210.0.60.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('167', '8.4', '8209', '41180', 'BTW043.0.210.0.40.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('168', '6.3', '8210', '41181', 'BTW042.0.210.0.30.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('169', '5.25', '8211', '41182', 'BTW038.0.210.0.25.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('170', '4.2', '8212', '41183', 'BTW037.0.210.0.20.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('171', '14.7', '8213', '41184', 'BTW056.0.210.0.70.BED&G+_|M82AT59.M85VT63+NAF', '0'),
('172', '4.2', '8214', '64029', 'BTW377.BE|EU+BTW NAF [$]', '0'),
('173', '6.3', '8215', '64030', 'BTW391.BE|EU+BTW NAF [$]', '0'),
('174', '8.4', '8216', '64031', 'BTW392.BE|EU+BTW NAF [$]', '0'),
('175', '12.6', '8217', '64032', 'BTW395.BE|EU+BTW NAF [$]', '0'),
('176', '14.7', '8218', '64033', 'BTW396.BE|EU+BTW NAF [$]', '0'),
('177', '15.75', '8219', '64034', 'BTW397.BE|EU+BTW NAF [$]', '0'),
('178', '16.8', '8220', '64035', 'BTW397.BE|EU+BTW NAF [$]', '0'),
('179', '10.5', '8221', '41185', 'BTW045.0.210.0.80!BED&G+_|M82AT59.M85VT63+NAF', '0'),
('180', '4.2', '8222', '41186', 'BTW190.0.210.0.20.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('181', '6.3', '8223', '41187', 'BTW194.0.210.0.30.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('182', '8.4', '8224', '41188', 'BTW195.0.210.0.40.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('183', '12.6', '8225', '41189', 'BTW199.0.210.0.60.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('184', '14.7', '8226', '41190', 'BTW200.0.210.0.70.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('185', '16.8', '8227', '41191', 'BTW202.0.210.0.80.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('187', '0', '8230', '8015', 'BTW500.BE|EU+GMVH', '0'),
('188', '10.5', '8231', '41192', 'BTW023.0.210.0.80!BEBM+_|M83AT59.M85VT63+NAF', '0'),
('189', '10.5', '8233', '41193', 'BTW216.0.210.0.80!EUBM+_|M83AT59.*VT61 AF[^]', '0'),
('190', '10.5', '8232', '41194', 'BTW197.0.210.0.80!EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('191', '0', '8235', '8016', 'BTW070.0.210.1.00!BED&G+_|M82__.M85__+NAF', '0'),
('192', '0', '8236', '8017', 'BTW182.0.210.1.00!EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('193', '0.6', '8238', '41195', 'BTW183.0.060.0.10.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('194', '1.2', '8239', '41196', 'BTW184.0.060.0.20.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('195', '1.5', '8240', '41197', 'BTW185.0.060.0.25.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('196', '1.8', '8241', '41198', 'BTW186.0.060.0.30.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('197', '2.4', '8242', '41199', 'BTW188.0.060.0.40.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('198', '5.4', '8243', '64036', 'BTW389.BE|EU+BTW NAF [$]', '0'),
('199', '4.8', '8244', '64037', 'BTW379.BE|EU+BTW NAF [$]', '0'),
('200', '4.5', '8245', '64038', 'BTW378.BE|EU+BTW NAF [$]', '0'),
('201', '3.6', '8246', '64039', 'BTW376.BE|EU+BTW NAF [$]', '0'),
('202', '6', '8247', '41177', 'BTW193.0.120.0.50.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('203', '12', '8248', '41150', 'BTW198.0.120.1.00.EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('204', '16.8', '8249', '64040', 'BTW398.BE|EU+BTW NAF [$]', '0'),
('205', '0', '8250', '8018', 'BTW069.0.120.1.00!BED&G+_|M82__.M85__+NAF', '0'),
('206', '0', '8251', '8019', 'BTW180.0.060.1.00!EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('207', '0', '8252', '8020', 'BTW181.0.120.1.00!EUD&G+_|M82AT59.VT61 AF[^]', '0'),
('208', '0.6', '8253', '41101', 'BTW083.0.060.0.10.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('209', '1.2', '8254', '41102', 'BTW084.0.060.0.20.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('210', '1.5', '8255', '41103', 'BTW085.0.060.0.25.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('211', '1.8', '8256', '41104', 'BTW086.0.060.0.30.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('212', '2.1', '8257', '41105', 'BTW087.0.210.0.10.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('213', '2.4', '8258', '41106', 'BTW088.0.060.0.40.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('214', '3', '8259', '41107', 'BTW089.0.060.0.50.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('215', '4.2', '8260', '41108', 'BTW090.0.210.0.20.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('216', '5.25', '8261', '41109', 'BTW091.0.210.0.25.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('217', '6', '8262', '41110', 'BTW092.0.060.1.00.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('218', '6.3', '8263', '41124', 'BTW094.0.210.0.30.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('219', '8.4', '8264', '41139', 'BTW095.0.210.0.40.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('220', '10.5', '8265', '41123', 'BTW096.0.210.0.50.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('221', '10.5', '8266', '41001', 'BTW097.0.210.0.80!BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('222', '12.6', '8267', '41002', 'BTW099.0.210.0.60.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('223', '14.7', '8268', '41003', 'BTW100.0.210.0.70.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('224', '15.75', '8269', '41004', 'BTW101.0.210.0.75.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('225', '16.8', '8270', '41005', 'BTW102.0.210.0.80.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('226', '18.9', '8271', '41006', 'BTW104.0.210.0.90.BEWOG+_|M82AT59.VT61 AF[^]    ', '0'),
('227', '21', '8272', '41007', 'BTW105.0.210.1.00.BEWOG+_|M82AT59.VT61 AF[^]', '0'),
('228', '0', '8273', '8021', 'BTW006.0.000.0.00.BEBM+_|__.__', '0'),
('229', '0', '8274', '8022', 'BTW032.0.000.0.00.BED&G+_|__.__', '0'),
('230', '0', '8275', '8023', 'BTW139.0.000.0.00.EUG+_|__.__', '0'),
('231', '0', '8276', '8024', 'BTW150.0.000.0.00.EUD+_|__.__', '0'),
('232', '0', '8277', '8025', 'BTW007.0.000.1.00.BEBM+_|M83__.M85__', '0'),
('233', '0', '8278', '8026', 'BTW134.0.000.0.00.EUBM+_|__.__', '0'),
('234', '0', '8279', '8027', 'BTW135.0.000.1.00.EUBM+_|M83M86__.M84__', '0');

Here you can find my tax_calc.inc : https://cloud.pollet.legal/index.php/s/eMLsMXm5TCD96tX
Here you can find my tax_types.php : https://cloud.pollet.legal/index.php/s/4LcZpsEKLDS3nsk

You can find in attachement an example of a resulting Belgian VAT-declaration (with 0-taxes and an inter-european transaction (purchase - reverse charged -> negative taxrate)).

F.

Go to forum: Banking and General Ledger

2 Topic by advocaat.pollet

(1 replies, posted in FA Modifications)

Hi,

I want the 'delivery date' for each item in salesinvoice ((new) column 'Date' ('tran_date' of delivery)).  I know I have to change the function 'get_customer_trans_details' in '/sales/includes/db/cust_trans_details_db.inc', but how to select the 'tran_date' of the delivery in the sql-syntax ?

This is the function :

function get_customer_trans_details($debtor_trans_type, $debtor_trans_no)
{
if (!is_array($debtor_trans_no))
    $debtor_trans_no = array( 0=>$debtor_trans_no );

    $sql = "SELECT line.*,
        line.unit_price+line.unit_tax AS FullUnitPrice,
           line.unit_price AS UnitPriceExTax, 
        line.description AS StockDescription,
        item.long_description AS StockLongDescription,
        item.units, item.mb_flag
        FROM "
            .TB_PREF."debtor_trans_details line,"
            .TB_PREF."stock_master item
        WHERE (";

    $tr=array();
    foreach ($debtor_trans_no as $trans_no)
        $tr[] = 'debtor_trans_no='.db_escape($trans_no);

    $sql .= implode(' OR ', $tr);


    $sql.=    ") AND debtor_trans_type=".db_escape($debtor_trans_type)."
        AND item.stock_id=line.stock_id
        ORDER BY id";
    return db_query($sql, "The debtor transaction detail could not be queried");
}

Thanks,

F.

Go to forum: FA Modifications

3 Topic by advocaat.pollet

(0 replies, posted in Report Bugs here)

Hi,

purchasing/supplier_invoice.php (Enter Supplier Invoice) doesn't have F-keys.

F2 for making/selecting a (new) supplier
F? for making/selecting a (new) Quick Entry

Thanks,
F.

Go to forum: Report Bugs here

4 Reply by advocaat.pollet

(2 replies, posted in Reporting)

Forgot to mention, the report needs PHP 8 and didn't test with quick entries yet.

Go to forum: Reporting

5 Reply by advocaat.pollet

(2 replies, posted in Reporting)

Hi,

I helped myself. Attachted you can find the rep709-file. Rename it to rep709.php and save it under your '/reporting/'-directory.

The report contains a VAT-declaration for Belgium, but you can adapt it for other countries (symply rename the fieldnumbers of the VAT-declaration).

You need to add the fieldnames of the VAT-declaration in the taxname, ex. 'BTW BE D&G-21/100-M03VT54M49AT64|M82AT59M85VT63'.

When the 'taxname' is used for a transaction, the fields of the VAT-declaration will be populated with the correct values and sums.

So, in this ex., if you make a salesinvoice, the fields M03 en VT54 will be popultated with the salesamount and the taxamount.
If you make a purchase, the fields M82 and AT59 will be populated with the purchase-amount and the 100%-deductable taxamount.
salescreditnote : fields M49 and AT64
purchase-creditnote : fields M85 and VT63

Ofcourse this is not a pretty name of the taxname in invoices/credit notes/deliveries.  In these reports, I changed the variable '$tax_type_name' to :

$tax_type_name = substr($tax_item['tax_type_name'],0,3);

So, only the first 3 letters of the taxname are used in invoices/credit notes/deliveries, in above ex. : 'BTW', which is the dutch translation of 'VAT'.

It also works for VAT that is only partialy deductable (ex. 50% deductability of the tax).  Read the code.  Make a first taxname for the deductable part of the tax (as explained above), and a second taxname for the non-deductable part of the tax, put '_' in front of the taxname for the non-deductable part (ex. _BTW21/50, i.e. 10,5 %, i.e. 1/2e of 21) and FA and the report will do the rest (escaping the non-deductable part of taxamounts in purchases from being part of the VAT-declaration).
 
I hope this helps someone.

Kind regards,
F.

Go to forum: Reporting

6 Topic by advocaat.pollet

(2 replies, posted in Reporting)

Hi,

In Belgium, the creditnotes in VAT-declaration need to be mentioned seperated from invoices (sales and purchases).

Now, FA makes sums of invoices AND creditnotes in the taxreport-summary.

How can I seperate the creditnotes from invoices in the taxreport-summary ?

Kind regards,
F.

Go to forum: Reporting

7 Reply by advocaat.pollet

(4 replies, posted in Wish List)

Hi mappolon,

I'am using Nextcloud as a customerportal where customers can view there invoices, creditnotes, ...

Added two fields in db :
nextcloudfolder (the nextcloudfolder where invoices, ... need to be saved/are saved)
nextcloudlink (the URL-sharelink to view the invoices, ... in a browser)

Added these fields in FA-applicationfiles where needed.

You can do the same for articles.

Kind regards,
F.

Go to forum: Wish List

8 Reply by advocaat.pollet

(8 replies, posted in Reporting)

Hi kvvaradha,

I am no PHP-programmer, so please provide the code / instructions (dummy-proof ;-)).

It seems that the fpdi-files need an update for later PHP-versions.

I rewrote the function, and it works very well :

function TermsAndConditions()
    {
        $tmpl_pdf = find_custom_file("/reporting/forms/TermsAndConditions.pdf");
            if ($tmpl_pdf) {
            $this->tmplSize = $this->setSourceFile($tmpl_pdf);
            }
        for ($i = 1; $i <= $this->tmplSize; $i++) {
            $id = $this->importPage($i);
            $this->AddPage();
            $this->useTemplate($id);
        }
    }

Ofcourse one can define and set a separate header too, calling the TermsAndConditions-file. The problem for me was that 'importPage()' only included 1 page, where the TermsAndConditions-file had 2 (or more) pages.

Kind regards,
F.

Go to forum: Reporting

9 Reply by advocaat.pollet

(8 replies, posted in Reporting)

Oh, PHP Version 8.0.23

Go to forum: Reporting

10 Reply by advocaat.pollet

(8 replies, posted in Reporting)

Changed it like this :

function _getPageRotation ($obj) { // $obj = /Page
        $obj = $this->pdf_resolve_object($this->c, $obj);
        if (isset ($obj[1][1]['/Rotate'])) {
            $res = $this->pdf_resolve_object($this->c, $obj[1][1]['/Rotate']);
//DEV:
            if ($res[0] ?? 'default value' == PDF_TYPE_OBJECT)
//:DEV
                return $res[1];
            return $res;
        } else {
            if (!isset ($obj[1][1]['/Parent'])) {
                return false;
            } else {
                $res = $this->_getPageRotation($obj[1][1]['/Parent']);
//DEV:
                if ($res[0] ?? 'default value' == PDF_TYPE_OBJECT)
//:DEV
                    return $res[1];
                return $res;
            }
        }
    }

Solved the warning.

Go to forum: Reporting

11 Reply by advocaat.pollet

(8 replies, posted in Reporting)

Thanks apmuthu !

This is what I have done :

1/ Created my Terms and Conditions (2 pages), saved them in a pdf-file, named 'TermsAndConditions.pdf'.
2/ Placed this file in /reporting/forms.
3/ Made a new function 'TermAndConditions' in /reporting/includes/pdf_report.inc :

function TermsAndConditions()
    {
        if ($this->pageNumber > 1)
        {
        $tmpl_pdf = find_custom_file("/reporting/forms/TermsAndConditions.pdf");
            if ($tmpl_pdf) {
            $this->tmplSize = $this->setSourceFile($tmpl_pdf);
            }
        }

        if ($this->tmplSize)
        {
            for ($i = 1; $i <= $this->tmplSize; $i++) {
                parent::newPage();
                $id = $this->importPage(min($i, $this->tmplSize));
                $this->useTemplate($id);
            }

        }
    }

4/ In the reports where I need my Terms and Conditions (ex. 'rep107.php' (invoices)), before 'if ($email == 1) ...', added :

// START my modification
    $rep->TermsAndConditions();
    // END my modification

This works as expected (the report with Terms and Conditions is created as it must be), but I get a php-warning in Frontaccouting :

Trying to access array offset on value of type bool in file: /.../reporting/includes/fpdi/fpdi_pdf_parser.php op regel 336

What should be done ?

F.

Go to forum: Reporting

12 Topic by advocaat.pollet

(8 replies, posted in Reporting)

Hi,

In an older thread (https://frontaccounting.com/punbb/viewtopic.php?id=6629) I found :

"You can have a generic PDF included into the invoice. Alternatively, you can have a link in the Invoice pointing to a PDF file hosted at your site. Use the TCPDF page number variables at will in the report."

But how can I include a PDF into an invoice ?

Thanks !

F.

Go to forum: Reporting

13 Topic by advocaat.pollet

(0 replies, posted in FA Modifications)

Hi,

The backup-functionality only makes a backup of the database-data.

Is it possible to download the (renamed) attachments of a company from the FA-application, perhaps in a zip-file called [date]_[companynumber]_backup_attachments.zip ?  Can it be made possible ?

Thanks,

F.

Go to forum: FA Modifications

14 Reply by advocaat.pollet

(6 replies, posted in Report Bugs here)

@apmuthu

A daily cronjob for automatic backup in the wiki should also be great :

#!/bin/bash
url="https://[host].[domain].[tld]/"
cd "$(dirname "$1")"

curl -s -c my_session -F 'user_name_entry_field=admin' \
                      -F 'password=[password for admin]' \
                      -F 'company_login_name=[companynumber]' ${url} >/dev/null

curl -s -b my_session -F 'creat=Create Backup' ${url}admin/backups.php

echo
rm my_session

Go to forum: Report Bugs here

15 Reply by advocaat.pollet

(6 replies, posted in Report Bugs here)

Hi,

I think 'Protect security settings' was enabled when restoring.

Also table #_security_roles wasn't restored.

Are there other tables that are protected when 'Protect security settings' is enabled ?

Kind regards,

F.

Go to forum: Report Bugs here

16 Topic by advocaat.pollet

(6 replies, posted in Report Bugs here)

Hi,

As the title indicates : I wanted to restore the backup of a company but it seems that the users-table isn't restored.



Kind regards,

F.

Go to forum: Report Bugs here

17 Reply by advocaat.pollet

(3 replies, posted in Modules Add-on's)

Hi,

When installing this module I get a SQL-error :

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF NOT EXISTS `google_secret_code` VARCHAR(100) NOT NULL DEFAULT '' AFTER `passw' at line 1

Kind regards,
F.

Go to forum: Modules Add-on's

18 Reply by advocaat.pollet

(8 replies, posted in Report Bugs here)

Hi Joe,

'Journal Entries' is not in empty.po-file ...

F.

Go to forum: Report Bugs here

19 Reply by advocaat.pollet

(8 replies, posted in Report Bugs here)

Hi Joe,

The new files solved the problems in sales/inquiry/customer_allocation_inquiry.php?
Was my own fault, sorry.

F.

Go to forum: Report Bugs here

20 Reply by advocaat.pollet

(8 replies, posted in Report Bugs here)

Hi Joe,

Do you mean that 'Unsettled transactions' is also used as 'Overdue transactions' in other places ?  Do you mean not to change the wording, or not to change the sql-query to get due + overdue transactions ?

Problems in sales/inquiry/customer_allocation_inquiry.php? :

1/ Select Sales Invoices
I get 1 record (the first invoice in the system ?), allocated but there should be more than 1 unallocated.

2/ Select Unsettled transactions
I get 1 record (the first overdue invoice in the system ?), not allocated, but there should be more.
This selectionoption should not even exist because 'sales/inquiry/customer_allocation_inquiry.php?' already gives me overdue transactions

3/ Select Payments
DB-error
I get 1 sales invoice (the first invoice in the system ?), allocated, but no payments
Select 'show settled:' gives the same result.

4/ Select Creditnotes
I get 1 record (the first creditnote in the system ?) that is allocated.

5/ Select Delivery Notes
DB-error
I get 1 record, unallocated but is an invoice.
Select 'show settled:' gives the same result.

Kind regards,
F.

Go to forum: Report Bugs here

21 Reply by advocaat.pollet

(8 replies, posted in Report Bugs here)

Hello Joe,

Unsettled means 'due' but not 'overdue'. I expect to get due transactions (not allocated) when selecting 'Unsettled transactions', not only 'overdue' transactions.

If Frontaccounting only gives overdue transactions, and it is created to be that way, the selection on Salestype should be called 'Overdue transactions', not 'Unsettled transactions'.

Another problem : selection on 'Journal Entry' isn't possible, but Journal entries are there in the list.

***

I also see that there are several problems when making selections on 'Salestype' in sales/inquiry/customer_allocation_inquiry.php?.
The results are not correct.

F.

Go to forum: Report Bugs here

22 Topic by advocaat.pollet

(8 replies, posted in Report Bugs here)

Hi,

Sales - customer_inquiry.php

When searching for unsettled transactions, only the transactions that are overdue are shown.
Transactions that are unsettled but not overdue, aren't.

Kind regards,
F.

Go to forum: Report Bugs here

23 Topic by advocaat.pollet

(1 replies, posted in Report Bugs here)

Hi,

When workorders are made, automatic GL-booking are made (WIP-account/inventory-account), but without the dimensions.
It is even impossible to select a dimension on creation of a workorder.
So, workorders should have dimensions for the automatic GL-bookings.

See also : https://frontaccounting.com/punbb/viewtopic.php?id=10064

F.

Go to forum: Report Bugs here

24 Reply by advocaat.pollet

(17 replies, posted in Setup)

Hi,

Is this code available ?  Where could I find it ?

Kind regards,
F.

Go to forum: Setup

25 Reply by advocaat.pollet

(2 replies, posted in Report Bugs here)

Hi Joe,

Yes, I know, but I can't select the right one to print, nor the right range of orders, because there are several 'auto'-orders for the same customer, all called the same 'auto-customername' ....  Impossible to select the right one(s).

F.

Go to forum: Report Bugs here