Tutorial Extensions
Embedded Extensions are extensions written in JavaScript and saved directly in the Banana Accounting file. For more information visit:
Embedded extensions examples
The following pages contain code examples for most of the Javascript APIs:
Embedded Extensions examples Tutorial 1
'Hello world' report
Hello World
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialhelloworld
// @description = Tutorial: Hello world
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text
report.addParagraph('Hello World!!!');
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Creating Reports
Add several paragraphs
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialaddseveralparagraphs
// @description = Tutorial: Add several paragraphs
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add several paragraphs with some text
report.addParagraph('This');
report.addParagraph('is');
report.addParagraph('a');
report.addParagraph('text');
report.addParagraph(' '); //Empty paragraph
report.addParagraph('printed');
report.addParagraph('on');
report.addParagraph('several');
report.addParagraph('paragraphs.');
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Add the header
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialheaderstyles
// @description = Tutorial: Add the header with some styles
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add the header
report.getHeader().addClass('header');
report.getHeader().addText('This is the header text aligned to the right with a bottom border', 'header');
//Add some style
var stylesheet = Banana.Report.newStyleSheet();
//Header style
style = stylesheet.addStyle(".header");
style.setAttribute("text-align", "right");
style.setAttribute("border-bottom", "thin solid black");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Add the footer
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialfooterstyles
// @description = Tutorial: Add the footer with some styles
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add the footer with page numbers
report.getFooter().addClass('footer');
report.getFooter().addText('Banana Accounting, v. ' + Banana.document.info('Base', 'ProgramVersion'));
report.getFooter().addText(' - Page' + ' ');
report.getFooter().addFieldPageNr();
//Add some style
var stylesheet = Banana.Report.newStyleSheet();
//Footer style
style = stylesheet.addStyle(".footer");
style.setAttribute("text-align", "right");
style.setAttribute("font-size", "8px");
style.setAttribute("font-family", "Courier New");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Add an image
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialimage
// @description = Tutorial: Add an image
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add an image stored in the document tabel
report.addImage("documents:logo");
//Add a png image passing the content of the image
report.addImage("data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAYAAABXAvmHAAARqklEQVRogb2ZeVRT1/bHT9+TgiKTIiiCgAMig0wm997MzKKiOHR4WgdAUAhgRQWnIn1Pa21r+161Uu3wfG0d2tfqs1a7bJkHAyGQhJAACSQEUQZFxalWge/vj6AFoVZb+9trfde6f9yz92fvs885dyDkD1i87QTXLRMnLXnDbfLrh6d7f3TSa+aRr718P8+Z6rlv40SX9I3OzqIkm0l2fyTGMzURISPEjhNDd7tNffOIp09hgV9QmyyIgpJFQzVAilkUpIHs3ryZAReOePrk73Bx35vu5DLfnxDb3xPX3cHScXMc68cXhFMOEkKe+z0+nlvrMGHhN14z86RB7BtNNA8GhocGigs1m4GKRaOmXyoWjVoWDQ2bgZbiQk/zYKB5UASx757w9lVmT3LbHGpp6fg0wbdlLsmQ/i8ZcZEe3xFCnn8qcl9CJu+b5nVEw2Z6dRQXdRQHKhYNZb8egCsfIxWLRh2bg0aai0aaix/8ArTZLm5rRYRYPAnDgf0b/qEq3ILEeR650+2J1RPDz7a0jDwvCtdeilwAFZuGchb1EHig1P3VbqS5aKK50FJc1LGZYe9VsmhoKS50FBcHp3l+HWRuPuW3OJYtC32p7nwW1r/kK2FPtBr7RPDzRlotrIl56Xrr4mVQzKJQMwBe2Q+tp3lopLmQBrHvnZ0Z0Hp8hm/tMU8f1SkfP32J/6xb9RQHzQwfWor7cNYejK9lMbjA8HHa108VYWMT9DiWyZOdfasKttzLSmDVhgfYO/0mfAAZIahZvLTrwiuroQgIGgKv66/24ele8liH8Vm+FhZh5oR4EEKcCCHjCSHuzmZmlMDKNm67i9uxQv/AzhaGDw2bGdR2NSwaLQwf53wD6/2eH+39GKRRX3+WqHx/S4hR5D/e7bHwLoQ4nRGE1XQkp0MROBS+ieaiLIDVucjOIcPSBPubNs1sJLXdxe3LBjbTV8fmDFk7Fxg+jnh6l1oR8qvtsWNz5Edfvhd9zdyceDw22GbnSW91pWWiliOEMoiFGhYzCL7QL6jNz3zUvCcBf8TMl45z3K5hMz31FGfITFzk8JHuNOnNXxscM98n6btPF/WNtTZj/WoEJ0I8pXNi2o2Ll5laZ0AADZtB7SzqZ2qk9cu/A/6hxTlO2K2neVAPWOTK/l1KHsS+5W1m6T/cOH9/p3DJ6eVgeY195Vedrxo3PrNtyXLUMALUBLEHBWhheNjo7PIxIeQvfyQBQohNzrTpxQaGN2SHMjJ8ZE9yPTTcoJleE/g6aTLES733/5pjs8PefgXGsHmDtksli0YDxUV5AOuqi9ljpu8pjBo5ekkdi+lTPZKAjuLi+5l+Bu+RIyc9OkbAnbaorW49ju2bU0fIMGeBDSHuRYGsK3U0b0gCRoaHf03x+J4QMvJZJEAIsf3ay1eto7iD1kIdmwMNi7kXZmOzeODN0RFunvvfij6trUyCqiC+j/J1XDDEI9vKil89i+6pfaQqtSwGzQwPseMcdz4jeEIIITsmTf53C8MblEBtf7HiHJyy+28zz8pgZ6tLVrTpqxOhKotFY3ka/rlZcIY8Wsxo27ExahY9ZFo1bAZaNqcvwsp25bNMINZhfJZxmHXQwvCwzWXSvwkhZOlS1rLeG6/hWlMimhWJuKRJgL5yHerOxvUI/Ma8NMhhjM2YxWoW3adiD3ZYR3FQy2bu86xsFz7LBJY6OGwcdiFzeHjTbcqXhJC/fnUs/RRgQNeFAzBUx6NRKkaDJA2tFa8ia7X/UTJwQxFZW89Wsqi+GjYN5QBpKA7qKKY32MbmxWeZQMJ4p6xmDh8K1uB4Rg4Pu1wnHyOE2KgVnzQAt3DnaimaqlejUZqM+vOpuCDLwIdZYaVzfW1+eddwMzPzlwSxbteyGSgHSMVm0MTwsHiM/fpnmcAOV7dPjBw+FI/EM3L4SB/vtN/dw8Hvdnf+T333jbjZlYvGqtVokiWjoUwMoywT3+a8qHOfYOE60KfdCW8/tZbmDus023XyF+R3vkwMY6O+9vFV6hjeoFgqNoNmjgALLcds/Ft8yGr0yfHzrWp0d5yEVvZLAgbpJpQcjWsfa0U8B3nNdHH/2MgR9DvlPFQDzUOB/6wL4wiZ+izoeZbWs2vYnPs1A2Io2Ryo2Vxo2Jz7IgvLVZ9/ueNL9JbgTncRrl78HDp5KvSVSWgoE6OpYhOkJ5KuudqPChzkOGiUVYyGzelTU1wo2JxBMnD4SHNy3v0M+J87MNXztJ7hD4lRR/OQ7+PXGjXDLamp5bih59ZJ3L5yGh2GD9CkWAe9NAl1ZWI0VWSi6lRq9+Txo4YcrDaHPGYUGzlCyB9xXktxoWAxt/jW1pF/hH6Bnf0aLcPrqR2mSPU0D5+6T5FuS39pX1/Pib7blz9F96XP0Nb4NgyKZDRJk1BfJoa+YjMqT6Zec7U3CxwSgBlt80INi3tXQ/NNSVAmydkcNDAC5AeyjNzRtsG/B36ds+viilnUlbpHfD/wr5lF9+308DxdWv6mvPdODq61vo8u4z60N+5Cs3wtGqVi1Jcmw1C5HcXH1rQNWQP99tf1Tq6HWngiKCgOqtkm53K26VrLCFAaSF3Ndp2aHkGI5ZOAv2BnZyOeMHFr+Sz6Rj0jGOTzgZQUF2U+ATdz1sYU/nwnp+fGxSxcad6Fy4Z30K59DYaqNWisEENTnIKW6myczlmqtRtJhjwzPTCHfR5eeaYkuKimuJD3q5riQkPzUcfw8a1vYNWa8S4bY2zsgzImTnSOsbW1DSJkFJfYW71q7zohzmGi37qJLuu/nRlUreUIoGb4JvgB/h6ols3F2SBWp6Ziz5Wfr/0dl5vS0aHbgi7Du2hVr4e+ai105WJoilJxUb4TOVlRpbyB58CjZkOI+wfTZhQ284KhoHmoYnOHSMPw0cARopLN3DnpG6D5yNOn8AMPr7M5Hj4/Hvf2V0hYzI16jgAahj/s+Aeqprio8p+Fih1r7t/sfhtd+tXoaEhCh3Yruoz/hFGRCH1lMhokKVAXpaGtehe2rQ46Sgj562OnfhQhTttcJx+t5whRzxFCTvEgo3io6teD6xqaj3qOADqu6KHqOULU0PxB9w0rmofqIAoSYQQMVbvR3ZmMTm0cWmvjcfXCAXTotkFftRqNFWLUl6airiQdxrLtWCyatPlJ2pcQQswj7camnJwZ1NjIC4aOGwwFI4CM4j+xqujHiOKj0p9G+evxuHp1O7oMsbikiUW7dhu62w6jRRkPfZXY1D7FKWgsz4T8VOJtbxfLp9sN7QmZ/vK4ibuO+gbWKBkhGnnBqOeKUMMIUE0LUEUPBq6m+VAyAqgemywPVUE8lIWHQit7Bd2X4tDZEI+L6gTc6Pgv2rWZaJYnQl+ZgoayVKgLU2GUZuLIG+FqQshTfd17aCMJcQm0tF6W4ux28CNP38qzgezLEhanV0bzIWcEkPcnVM7m9p7xZ3UenxmglVK8niqaD9mjYvNREciD5MO56OqMR1fTalxUr8C11kO4fvEQjIo4GKpN1a8rSUV9aRqai5IhXuzxq6+VT2tj7QiZOdPScnaonf2yhfaOCYvsJ6yJsB27kra2i3I2M6N2T/U8V8MRDYWn+ZAF8FGQIERL8wpcNybiknoFLut34k7XWbTWrkVz9RroZWI0nDct3vrSNEiPLLo3ZbzF7zqHnspEhIx4Z5rXQQ03GJW0AJW08KGktBCyQCEKw3molbyI7rY1aK9fhQ7tJty9XoxO3WY0y+NgqBI/XLy1Ba+iMS8Fe5IDvieEmP+p8N6EPL9ryoycOl4oZIwIFbRwkCpZQpSweZAcj0ZXZwI6G1aio+FV3L1xHl3Ne2BUrIKhWmx6gSlLgbowFXWFG1D91d96/KdYxfyp8FGEmO+c5nVQxQuFjAlGOS38RYwQFZQQZYF8FO6JQPuleFzWLUenbgPudpfjWut+tChXolmejCZZMnQSMTRFaVDlvwpjcTr+vsbvNHl2HxaG2rIxY6zf8/Q9XMMNRQUjgoQWopwWmcSIUEGLIAkUIm9DCJr1K3ClcSWu6LNw72YVrl14Hy3KlTAqkqDvh68vTYUqPxW60q0o+njRdZdxI/jDxX0+lHIKf2sXd/2Bfy7J3LYxKlokmuqcnf10H7CW2kyy+8DL/ysVPxwVnGBIGNEgldMiSAJE+CFBBK3mZXQZYnHjUg7u3Vaiy7jHVPkH8OX98HliaIq3Qv9jKl4Mcc4eNrD7OAtB9alVt291bMLtLjHuXPtX76XG9zvPffvqt7v/sSDzhYX+4W9kz/HYtXXRhGVRlHVq1FTz7Gzv51OjpppHRDhaxsYGjIsMcPfbN93vnJIfDgk3BGWcYJzvVxknGOeZEJwPDMa5ZVzUyhaj+1IG7l7/EXe7S9GhzYRRsaofXgxdeTIaylJRWyCGumgLLkpewxtr/L4nw33MIoSQyY4WYdqyrXd/6vwPWlSJuNnxFnC/EOgrBHpOo+fGERgb97VJirOVZ05uyDt9OPZU7pH4kycOLf/f5x+/fO7w1nnSs8GhlxWiuShhglHCCUEJJ/ShyjihKA0S4OxSCmpZLH66/gXu36pCd9t/0KpOgFERb+r5SlPlG8rSoC4QQ120GZdku/HZ6wKNvRWZ/rjZN0+J5++53/YumipSoK9KwBXjFty+9jnu3c5H790yoCcP6DuDnzoO4IJsM4xVG9GqWQ/VwZdQvWABVC+vRSk/EiVMCEq4oQ9VyoSiOFCIH1Yvgl73IXruSXHr8gm0azP6+z0RhuoUNEqToS1P6e95MdTF29Au34tT70YZPV1Hcp6khR0/fndh8VXtdjTLE9CsiEVrbTwu6zfi9uW9uHfzY1wx7EWjJAN6+Qaoy1OQvz4SJaJ5UIq3oixyEYpoEUo4YabKc0NRQoeiMCgEuevXwWg8gTvXvkCnLgMtylg0K1ajuToJTTIxdBWmg0pTnAJVnhh15/+OdvlenHwnomWK44iQJ4EnhBBiY2Mxef9bL35/VZcNvTQZBnkimmXJ0FfFo/CbefjukzDkn4xG4fEY5C4NhmTOK1C/9ibK5r6AQkqIYl4EinkRKOJFoISJQD4VjLMZC1BfuwntDSloUayCUZGIZnky9LJkNEqToZOYqq4uSIYqfx0aZXvRLtuNT7fza2a4jaSfGP6XJGzs0pPn7rug3NXbpsqEMi8B/z0QhY9283DwHS4OpbHxrYAH2fI01GbtRknEAhRQIhTyI1HIj0ARPwJFdATO8cJwZvd8aGoS0KpeY3okqBKbwCvE0JanoKEsBZoisanqpdtxqeYA9AWbsCPO56S91W/8hfkN+0uoyH/l15/E6k7kROLT9wQ4uIePL1byURARA6V4G2RJ6cjnhyOPE4IC4WzkCyNRKJiNQjoc380Ox7mPlqBekYQWZQr0laYTVVeeAu15U8U1RWKo8sWoLcpAi/w9dCrexY8HF3fOZ8ZtIYSM/iPwD83amkybO9v17Q83cW+cig5BSfgSlC9PQlH0C8hlQpArjESuKAq5oigU8KOQx4nE/16ZjbxvlkNXnYImqQlYez4F9WVpqCtJhbpADFV+CjTFmdDL3ka7/B1UfbPmTsYyz/+6Ooxgngn4APvLionTXssVRPWeF0ajIGw+ckWz8QM3DLmiKPwoikK+cA4KuFE4I4zENxkLUPpDAhqk69BQmgZNSRo0RWlQF6ZBXbgO9SUb0FS+A63Vb6G1fCdKvlh567V4v69mTDSfS/6MhzMns1GBn7GFuh/4s2/mB8/plYRGQxo2H9Lw+SgPi4Y0dD4kYdHIXRGDwv/EoqFqA1qqMmEo3wR9+UYYJBthLM9AS/lmGCXboCvYgtLjq7tytgZX/i3c5Q2HMYQhhJg9c/ABZjbZ3Hqaj41NBGU7Lnaek3N2grvHZ1leAbmHAjmad3wp2eJprkfXLvM9dWBnhOTY3ui6E/sXNZ/+cOGFU/tjjEffnqM9mB1W+Y9U7vcJC70+DJk1LsV9wgg+ecyv1P8Pe86eECsLQlyJ6R+xmRshFlZWxN7CgriPHk1m2I0mPmNGE29zczKFEOLoRMioPxPo/wBFxyrdFyIbZQAAAABJRU5ErkJggg==");
//Add a svg image passing the content of image
report.addImage('data:image/svg;utf8,<svg version="1.2" baseProfile="tiny" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:a="http://ns.adobe.com/AdobeSVGViewerExtensions/3.0/" x="0px" y="0px" width="337px" height="82px" viewBox="0 0 337 82" xml:space="preserve"><defs></defs><path fill="#234571" d="M16.737,0c5.755,0,9.592,3.309,9.592,8.151c0,3.453-2.973,6.38-5.803,6.906c3.405,0.191,8.105,3.405,8.105,8.632c0,5.947-4.172,9.881-10.839,9.881H4.651V0H16.737z M16.209,14.1c4.412,0,7.337-2.35,7.337-5.851s-2.925-5.755-7.337-5.755H7.48v11.604L16.209,14.1L16.209,14.1z M17.648,31.078c4.556,0,8.104-2.734,8.104-7.242c0-4.221-3.836-7.29-8.393-7.29H7.48v14.532H17.648z"/><path fill="#234571" d="M59.56,24.076H39.514l-4.844,9.495h-3.069L48.77,0.001h1.535l17.169,33.57h-3.069L59.56,24.076z M58.218,21.438L49.873,4.989l-0.336-0.863l-0.335,0.863l-8.345,16.448L58.218,21.438L58.218,21.438z"/><path fill="#234571" d="M100.178,0v33.568H97.54L76.678,5.801l-0.671-1.007l0.096,1.391v27.387h-2.83V0.001h2.638l20.91,27.672l0.624,1.055l-0.096-1.438V0H100.178z"/><path fill="#234571" d="M132.833,24.076h-20.047l-4.844,9.495h-3.069l17.169-33.57h1.535l17.168,33.57h-3.068L132.833,24.076z M131.492,21.438L123.145,4.99l-0.336-0.863l-0.334,0.863l-8.346,16.448H131.492z"/><path fill="#234571" d="M173.452,0v33.568h-2.638L149.954,5.8l-0.672-1.007l0.096,1.391v27.387h-2.83V0.001h2.64l20.91,27.672l0.623,1.055l-0.097-1.438V0H173.452z"/><path fill="#234571" d="M206.105,24.076h-20.047l-4.844,9.495h-3.068l17.168-33.57h1.535l17.17,33.57h-3.067L206.105,24.076z M204.762,21.438L196.416,4.99l-0.336-0.863l-0.336,0.863L187.4,21.438H204.762z"/><path fill="#234571" d="M26.376,75.723H10.934l-1.966,4.316H0l15.826-33.57h5.659l15.826,33.57h-8.968L26.376,75.723z M22.972,68.193l-3.837-8.486l-0.479-1.344l-0.479,1.344l-3.837,8.486H22.972z"/><path fill="#234571" d="M61.334,56.875c-1.535-1.918-4.412-3.117-7.05-3.117c-5.563,0-9.016,3.98-9.016,9.496c0,5.803,3.789,9.398,9.016,9.398c3.213,0,6.283-1.633,7.673-3.934l8.153,2.109c-2.781,5.945-8.776,9.88-15.826,9.88c-9.64,0-17.409-7.338-17.409-17.457s7.769-17.457,17.409-17.457c6.618,0,12.277,3.55,15.251,8.92L61.334,56.875z"/><path fill="#234571" d="M97.012,56.875c-1.535-1.918-4.412-3.117-7.05-3.117c-5.563,0-9.016,3.98-9.016,9.496c0,5.803,3.789,9.398,9.016,9.398c3.213,0,6.283-1.633,7.673-3.934l8.153,2.109c-2.781,5.945-8.776,9.88-15.826,9.88c-9.64,0-17.409-7.338-17.409-17.457s7.769-17.457,17.409-17.457c6.618,0,12.277,3.55,15.251,8.92L97.012,56.875z"/><path fill="#234571" d="M125.64,45.797c9.642,0,17.457,7.338,17.457,17.457s-7.815,17.457-17.457,17.457c-9.64,0-17.409-7.338-17.409-17.457S116,45.797,125.64,45.797z M125.64,72.749c5.467,0,9.064-3.884,9.064-9.495c0-5.658-3.598-9.496-9.064-9.496c-5.418,0-9.016,3.838-9.016,9.496C116.623,68.865,120.22,72.749,125.64,72.749z"/><path fill="#234571" d="M178.102,46.469v18.655c0,10.069-6.232,15.587-15.104,15.587c-8.922,0-15.106-5.518-15.106-15.587V46.469h8.057v18.943c0,4.796,2.637,7.77,7.051,7.77c4.412,0,7.049-2.974,7.049-7.77V46.469H178.102z"/><path fill="#234571" d="M214.645,46.469v33.57h-7.58l-13.905-17.938l-0.671-1.248l0.097,1.248v17.938h-8.06v-33.57h7.576l13.908,17.696l0.672,1.247l-0.098-1.247V46.469H214.645z"/><path fill="#234571" d="M247.204,46.469v7.529h-10.218v26.041h-8.059v-26.04h-10.068v-7.53H247.204L247.204,46.469z"/><path fill="#234571" d="M251.418,80.04V46.469h8.061v33.57L251.418,80.04L251.418,80.04z"/><path fill="#234571" d="M297.077,46.469v33.57h-7.578L275.59,62.102l-0.67-1.248l0.096,1.248v17.938h-8.057v-33.57h7.576l13.908,17.696l0.672,1.247l-0.098-1.247V46.469H297.077z"/><path fill="#234571" d="M329.008,77.307c-1.965,2.109-5.317,3.404-9.106,3.404c-9.644,0-17.408-7.338-17.408-17.457s7.77-17.457,17.408-17.457c6.52,0,12.229,3.453,15.25,8.92l-8.199,2.158c-1.584-1.918-4.412-3.117-7.051-3.117c-5.562,0-9.021,3.98-9.021,9.496c0,5.896,3.98,9.592,9.496,9.592c5.371,0,7.725-2.781,8.684-5.273v-0.051h-8.823v-6.474h15.682v18.991h-6.906v-2.732H329.008L329.008,77.307z"/></svg>');
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
stylesheet.addStyle("img", "margin-bottom:4em");
Banana.Report.preview(report, stylesheet);
}
Add page break
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialpagebreak
// @description = Tutorial: Page break
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text
report.addParagraph('Hello');
//Add a page break
report.addPageBreak();
//Add an other paragraph
report.addParagraph('World!!!');
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Add attachments
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialattachments
// @description = Tutorial: Attachments
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report attachments');
//Add a paragraph with some text
report.addParagraph('Report with attachments');
//Attach text files created on the fly
//We use the prefix 'data:...' to tell that the string is not an url but is itself the content of the file
report.addAttachment('text file 1.txt', 'data:text/plain;utf8,This is the content of the text file 1.');
report.addAttachment('text file 2.txt', 'data:text/plain;utf8,This is the content of the text file 2.');
report.addAttachment('text file 3.txt', 'data:text/plain;utf8,This is the content of the text file 3.');
//Attach an image stored in the document table
//We use the prefix 'document:...'
report.addAttachment('logo.jpg', 'documents:logo');
//Add an xml element
//We just add the new created Banana.Xml.newDocument
var xmlDocument = Banana.Xml.newDocument("eCH-0217:VATDeclaration");
var rootNode = xmlDocument.addElement("eCH-0217:VATDeclaration");
rootNode.addElement("title").addTextNode("Vat Declaration 2018");
report.addAttachment('vat_declaration.xml', xmlDocument);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Print multiple reports
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialmultiplereports
// @description = Tutorial: Multiple reports
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var docs = [];
var styles = [];
// Report
for (var i = 0; i < 10; i++) {
var report = Banana.Report.newReport("Report title");
report.addParagraph("Hello World #" + i + " !!!", "styleHelloWorld");
report.setTitle("Document " + i);
report.getFooter().addFieldPageNr();
docs.push(report);
// Styles
var stylesheet = Banana.Report.newStyleSheet();
var style = stylesheet.addStyle(".styleHelloWorld");
style.setAttribute("font-size", "24pt");
style.setAttribute("text-align", "center");
style.setAttribute("margin-top", "10mm");
var style2 = stylesheet.addStyle("@page");
style2.setAttribute("size", "landscape");
styles.push(stylesheet);
}
// Print preview of 10 documents together
Banana.Report.preview("Multi documents printing example", docs, styles);
}
Add structured text
// Banana Accounting Extension Javascript
// @api = 1.2.0
// @id = ch.banana.uni.app.tutorialstructuredtext
// @description = Hello world
// @task = app.command
// @doctype = nodocument
// @publisher = Publisher name
// @pubdate = 2022-05-31
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report and the stylesheet
var report = Banana.Report.newReport('Tutorial Banana.ReportElement.addStructuredText');
var stylesheet = Banana.Report.newStyleSheet();
stylesheet.addStyle(".example", "margin-top: 2em; margin-bottom: 0.5em; font-style: italic;");
report.addParagraph("--- Markdown example ---", "example");
let mdText = "# Header 1\n";
mdText += "## Header 2\n";
mdText += "### Header 3\n";
mdText += "Markdown text with **bold** and *italic*. \n";
mdText += "[Markdown link](https://www.banana.ch) \n";
mdText += "* List row 1\n";
mdText += "* List row 2\n";
mdText += "* List row 3\n";
report.addStructuredText(mdText, "md");
report.addParagraph("--- Simple html example ---", "example");
let htmlText = "<h1 style=\"color: purple\">Header 1</h1>\n";
htmlText += "<p style=\"color: blue\">Paragraph</p>\n";
report.addStructuredText(htmlText, "html");
report.addParagraph("--- Full html example ---", "example");
htmlText = "<html>\n";
htmlText += "<head>\n";
htmlText += "<style>\n";
htmlText += "p.green {color: green;}\n";
htmlText += ".red {color: red;}\n";
htmlText += "</style>\n";
htmlText += "</head>\n";
htmlText += "<body>\n";
htmlText += "<h1 class=\"red\">Header 1</h1>\n";
htmlText += "<p class=\"green\">Paragraph</p>\n";
htmlText += "</body>\n";
htmlText += "</html>\n";
report.addStructuredText(htmlText, "html", stylesheet);
report.addParagraph("--- Plain html example ---", "example");
let plainText = "Testo riga 1\n";
plainText += "Testo riga 2\n";
plainText += "Testo riga 3\n";
report.addStructuredText(plainText, "text");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Creating Reports with table object
Create a table with one row
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialtableonerow
// @description = Tutorial: Create a table with one row
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table
var table = report.addTable('myTable');
//Add a row
tableRow = table.addRow();
tableRow.addCell('Cash');
tableRow.addCell('1200');
//Create style
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Create a table with multiple rows
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialtablemultiplerows
// @description = Tutorial: Create a table with multiple rows
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table
var table = report.addTable('myTable');
//Add row 1
tableRow = table.addRow();
tableRow.addCell('Cash');
tableRow.addCell('1200');
//Add row 2
tableRow = table.addRow();
tableRow.addCell('Bank 1');
tableRow.addCell('500');
//Add row 3
tableRow = table.addRow();
tableRow.addCell('Bank 2');
tableRow.addCell('2600');
//Print report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Create a table with multiple rows and a header
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialtablewithheader
// @description = Tutorial: Create a table with multiple rows and header
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table
var table = report.addTable('myTable');
//Create table header
var tableHeader = table.getHeader();
//Add the header of the table
tableRow = tableHeader.addRow();
tableRow.addCell('Description');
tableRow.addCell('Amount');
//Add row 1
tableRow = table.addRow();
tableRow.addCell('Cash');
tableRow.addCell('1200');
//Add row 2
tableRow = table.addRow();
tableRow.addCell('Bank 1');
tableRow.addCell('500');
//Add row 3
tableRow = table.addRow();
tableRow.addCell('Bank 2');
tableRow.addCell('2600');
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Create a table with multiple rows, header and borders
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialtablewithheaderandborders
// @description = Tutorial: Create a table with multiple rows, header and borders
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table
var table = report.addTable('myTable');
//Create table header
var tableHeader = table.getHeader();
//Add the header of the table
tableRow = tableHeader.addRow();
tableRow.addCell('Description');
tableRow.addCell('Amount');
//Add row 1
tableRow = table.addRow();
tableRow.addCell('Cash');
tableRow.addCell('1200');
//Add row 2
tableRow = table.addRow();
tableRow.addCell('Bank 1');
tableRow.addCell('500');
//Add row 3
tableRow = table.addRow();
tableRow.addCell('Bank 2');
tableRow.addCell('2600');
//Stylesheet
var stylesheet = Banana.Report.newStyleSheet();
//Create style for the table adding borders
var style = stylesheet.addStyle("table");
stylesheet.addStyle("table.myTable td", "border: thin solid black");
//Print report
Banana.Report.preview(report, stylesheet);
}
Create a table with fixed columns widths
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialtablecolumnswidth
// @description = Tutorial: Create a table with fixed columns width
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var report = Banana.Report.newReport("Javascrip Tutorial - Table with fixed column widths");
/* Create the table */
var table = report.addTable("table");
/* Add columns */
var column1 = table.addColumn("col1");
var column2 = table.addColumn("col2");
var column3 = table.addColumn("col3");
var column4 = table.addColumn("col4");
var row;
/* Add a row with cells */
row = table.addRow(); //Add a row
row.addCell("A", "", 1); //add first cell
row.addCell("B", "", 1); //add second cell
row.addCell("C", "", 1); //add third cell
row.addCell("D", "", 1); //add fourth cell
//Add more rows...
/* Print the report */
var stylesheet = createStyleSheet();
Banana.Report.preview(report, stylesheet);
}
function createStyleSheet() {
var stylesheet = Banana.Report.newStyleSheet();
var style = stylesheet.addStyle(".table");
style.setAttribute("width","100%"); //table takes all the page width
stylesheet.addStyle("table.table td", "border: thin solid black");
/* Set column widths */
stylesheet.addStyle(".col1", "width:10%");
stylesheet.addStyle(".col2", "width:55%");
stylesheet.addStyle(".col3", "width:30%");
stylesheet.addStyle(".col4", "width:5%");
return stylesheet;
}
Add multiple paragraphs in one cell
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcellmultipleparagraphs
// @description = Tutorial: Add multiple paragraphs in one cell of the table
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var report = Banana.Report.newReport("Javascrip Tutorial - Add multiple paragraphs in one cell");
/* Create the table */
var table = report.addTable("table");
var row;
/* Add a row */
row = table.addRow();
/* Add first cell with paragraphs*/
var cell1 = row.addCell("", "", 1);
cell1.addParagraph("First paragraph...", "");
cell1.addParagraph("Second paragraph...", "");
cell1.addParagraph(" "); //empty paragraph
cell1.addParagraph("Fourth paragraph...", "");
/* Add second cell without paragraphs */
var cell2 = row.addCell("Cell2...", "", 1);
/* Print the report */
var stylesheet = createStyleSheet();
Banana.Report.preview(report, stylesheet);
}
function createStyleSheet() {
var stylesheet = Banana.Report.newStyleSheet();
style = stylesheet.addStyle(".table");
style.setAttribute("width","100%");
stylesheet.addStyle("table.table td", "border: thin solid black");
return stylesheet;
}
Create a table with fixed columns and cells span
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialtablefixedcolumnscellspan
// @description = Tutorial: Create a table with fixed columns and cells span
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var report = Banana.Report.newReport("Javascrip Tutorial - Simple Table");
/* Create the table */
var table = report.addTable("table");
/* Add columns */
var column1 = table.addColumn("col1");
var column2 = table.addColumn("col2");
var column3 = table.addColumn("col3");
var column4 = table.addColumn("col4");
var row;
/* 1st row */
row = table.addRow();
row.addCell("Row 1, Cell 1: span cell over 4 columns", "", 4); //span cell over 4 columns
/* 2nd row */
row = table.addRow();
row.addCell("Row 2, Cell 1: span cell over 2 columns", "", 2); //span cell over 2 columns
row.addCell("Row 2, Cell 3: span cell over 2 columns", "", 2); //span cell over 2 columns
/* 3rd row */
row = table.addRow();
row.addCell("Row 3, Cell 1", "", 1);
row.addCell("Row 3, Cell 2: span cell over 2 columns", "", 2); //span cell over 2 columns
row.addCell("Row 3, Cell 4", "", 1);
/* 4th row*/
row = table.addRow();
row.addCell("Row 4, Cell 1", "", 1);
row.addCell("Row 4, Cell 2", "", 1);
row.addCell("Row 4, Cell 3", "", 1);
row.addCell("Row 4, Cell 4", "", 1);
/* 5th row */
row = table.addRow();
row.addCell("Row 5, Cell 1: span cell over 3 columns", "", 3); //span cell over 3 columns
row.addCell("Row 5, Cell 4", "", 1);
/* Print the report */
var stylesheet = createStyleSheet();
Banana.Report.preview(report, stylesheet);
}
function createStyleSheet() {
var stylesheet = Banana.Report.newStyleSheet();
var style = stylesheet.addStyle(".table");
style.setAttribute("width","100%"); //table takes all the page width
stylesheet.addStyle("table.table td", "border: thin solid black; padding-top:10px; padding-bottom:10px;");
/* Set columns' width */
stylesheet.addStyle(".col1", "width:25%");
stylesheet.addStyle(".col2", "width:25%");
stylesheet.addStyle(".col3", "width:25%");
stylesheet.addStyle(".col4", "width:25%");
return stylesheet;
}
Add reporting styles
Set page margins
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialstylesheetsetmargins
// @description = Tutorial: Stylesheet set margins
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text using a style
report.addParagraph('Hello world!');
//Add some styles
var stylesheet = Banana.Report.newStyleSheet();
//Create the margin for the page: [top, right, bottom, left]
var pageStyle = stylesheet.addStyle("@page");
pageStyle.setAttribute("margin", "10mm 20mm 10mm 20mm");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Set landscape page
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialstylesheetsetlandscape
// @description = Tutorial: Stylesheet set landscape
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text using a style
report.addParagraph('Hello world!');
//Add some styles
var stylesheet = Banana.Report.newStyleSheet();
//Create the margin for the page
var pageStyle = stylesheet.addStyle("@page");
pageStyle.setAttribute("size", "landscape");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Add bold style to a text
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialstylesheetboldtext
// @description = Tutorial: Stylesheet add bold style to a text
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text using a style
report.addParagraph('Hello world!', 'boldStyle');
//Add some styles
var stylesheet = Banana.Report.newStyleSheet();
//Create the "boldStyle"
style = stylesheet.addStyle(".boldStyle");
style.setAttribute("font-weight", "bold");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Add a font size to a text
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialstylesheetfontsizetext
// @description = Tutorial: Stylesheet add a specific font size to a text
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text using a style
report.addParagraph('Hello world!', 'titleStyle');
//Add some styles
var stylesheet = Banana.Report.newStyleSheet();
//Create the style for the text
style = stylesheet.addStyle(".titleStyle");
style.setAttribute("font-size", "20px");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Add a color to a text
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialstylesheetcolortext
// @description = Tutorial: Stylesheet add a color to a text
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Add a paragraph with some text using a style
report.addParagraph('Hello world!', 'colorStyle');
//Add some styles
var stylesheet = Banana.Report.newStyleSheet();
//Create the style for the text
style = stylesheet.addStyle(".colorStyle");
style.setAttribute("color", "red");
//Print the report
Banana.Report.preview(report, stylesheet);
}
First page/cover example
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcoverpageexample
// @description = Tutorial: First page/cover example
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport("Cover example");
var title = " 'This is the title' ";
var companyName = "Banana.ch SA";
var openingDate = "01.01.2015";
var closufeDate = "31.12.2015";
var year = "2015";
report.addParagraph(title, "heading1 alignCenter");
report.addParagraph(" ");
report.addParagraph(companyName, "heading2 alignCenter");
report.addParagraph(" ");
report.addParagraph(year, "heading3 alignCenter");
report.addParagraph(" ");
report.addParagraph("(" + openingDate + " - " + closufeDate + ")", "heading4 alignCenter");
//Add some styles
var stylesheet = Banana.Report.newStyleSheet();
var pageStyle = stylesheet.addStyle("@page");
pageStyle.setAttribute("margin", "10mm 10mm 10mm 20mm");
stylesheet.addStyle("body", "font-family : Helvetica");
style = stylesheet.addStyle(".heading1");
style.setAttribute("font-size", "22px");
style.setAttribute("font-weight", "bold");
style.setAttribute("border-top", "100mm");
style = stylesheet.addStyle(".heading2");
style.setAttribute("font-size", "18px");
style.setAttribute("font-weight", "bold");
style = stylesheet.addStyle(".heading3");
style.setAttribute("font-size", "14px");
style.setAttribute("font-weight", "bold");
style = stylesheet.addStyle(".heading4");
style.setAttribute("font-size", "10px");
style.setAttribute("font-weight", "bold");
style = stylesheet.addStyle(".alignCenter");
style.setAttribute("text-align", "center");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Format values
Format numbers
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialformatnumbers
// @description = Tutorial: Format numbers
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Convert the value
var convertedAmount1 = Banana.Converter.toLocaleNumberFormat("1200.65");
var convertedAmount2 = Banana.Converter.toLocaleNumberFormat("0");
var convertedAmount3 = Banana.Converter.toLocaleNumberFormat(1234.56);
var convertedAmount4 = Banana.Converter.toLocaleNumberFormat(1);
//Add the converted amount to the report's paragraph
report.addParagraph(convertedAmount1);
report.addParagraph(convertedAmount2);
report.addParagraph(convertedAmount3);
report.addParagraph(convertedAmount4);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Format dates
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialformatdates
// @description = Tutorial: Format dates
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Convert the value: parameter is a string
var date1 = Banana.Converter.toLocaleDateFormat('2015-12-31');
//Convert the value: parameter is a date object
var d = new Date();
var date2 = Banana.Converter.toLocaleDateFormat(d);
//Add the converted amount to the report's paragraph
report.addParagraph(date1);
report.addParagraph(date2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Format transactions journal data
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialformattransactionjournaldata
// @description = Tutorial: Format transactions journal data
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create the table that will be printed on the report
var table = report.addTable("myTable");
//Add column titles to the table report
var tableHeader = table.getHeader();
tableRow = tableHeader.addRow();
tableRow.addCell('Data', 'boldStyle');
tableRow.addCell('Account', 'boldStyle');
tableRow.addCell('Description', 'boldStyle');
tableRow.addCell('Amount', 'boldStyle');
//Create a table with all transactions
var journal = Banana.document.journal(Banana.document.ORIGINTYPE_CURRENT, Banana.document.ACCOUNTTYPE_NORMAL);
//Read some values of the journal table
for (var i = 0; i < journal.rowCount; i++) {
var tRow = journal.row(i);
//Add the values taken from the rows to the respective cells of the table
//For the dates and the amounts we apply the format functions
tableRow = table.addRow();
tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')));
tableRow.addCell(tRow.value('JAccount'));
tableRow.addCell(tRow.value('JAccountDescription'));
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JAmount')), 'alignRight');
}
//Create the styleSheet
var stylesheet = Banana.Report.newStyleSheet();
//Add borders to the table
var style = stylesheet.addStyle("table");
stylesheet.addStyle("table.myTable td", "border: thin solid black");
//Add the right alignment for the amount
style = stylesheet.addStyle(".alignRight");
style.setAttribute("text-align", "right");
//Add the bold style for the header
style = stylesheet.addStyle(".boldStyle");
style.setAttribute("font-weight", "bold");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Numbers operations with SDecimal() functions
Basic mathematical operations (sum, subtract, multiply, divide)
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialbasicmathematicaloperations
// @description = Tutorial: Basic mathematical operations (sum, subtract, multiply, divide)
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Do some mathematical operations and add the results to the report
//Sum
var sum = Banana.SDecimal.add('6.50', '3.50'); // return '10.00'
report.addParagraph(sum);
//Subtract
var sub = Banana.SDecimal.subtract('10', '3'); // return '7'
report.addParagraph(sub);
//Multiply
var mul = Banana.SDecimal.multiply('6', '3'); // return '18'
report.addParagraph(mul);
//Divide
var div = Banana.SDecimal.divide('6', '3'); // return '2'
report.addParagraph(div);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
abs function
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialabsfunction
// @description = Tutorial: abs function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Return the value without the sign
var absValue = Banana.SDecimal.abs('-10');
//Add a paragraph to the report
report.addParagraph(absValue);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Compare two values
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcomparevalues
// @description = Tutorial: Compare two values
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
// Compare the values:
// return 1 if value1 > value2
// return 0 if value1 = value2
// return -1 if value1 < value2
var compareValue = Banana.SDecimal.compare('5', '2');
//Add a paragraph to the report
report.addParagraph(compareValue);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Invert sign of a value
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialinvertsign
// @description = Tutorial: Invert value sign
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Invert the sign:
//if positive return a negative value
//if negative return a positive value
var invertValue = Banana.SDecimal.invert('4');
//Add a paragraph to the report
report.addParagraph(invertValue);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Check the sign of a value
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcheckvaluesign
// @description = Tutorial: Check value sign
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Check the sign of the value:
//return 1 if value > 0
//return 0 if value = 0
//return -1 if value <0
var signValue = Banana.SDecimal.sign('-6');
//Add a paragraph to the report
report.addParagraph(signValue);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Number of decimals and rounding properties
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialdecimalsrounding
// @description = Tutorial: Number of decimals and rounding properties
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Divide without properties
var result1 = Banana.SDecimal.divide('10', '3.25'); //return '3.3333333333333333333333333'
report.addParagraph(result1);
//Divide with number of decimals property
var result2 = Banana.SDecimal.divide('10', '3.25', {
'decimals': 4
});
report.addParagraph(result2);
//Divide with number of decimals and rounding properties
var result3 = Banana.SDecimal.divide('10', '3.25', {
'decimals': 2,
'mode': Banana.SDecimal.HALF_UP
});
report.addParagraph(result3);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
User interface functions
Dialog information
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialinformationdialog
// @description = Tutorial: Information dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Open a dialog window with an information
Banana.Ui.showInformation('Title', 'This is the information message!');
}
Dialog question
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialquestiondialog
// @description = Tutorial: Question dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create a report
var report = Banana.Report.newReport("Report title");
//Open a dialog window with a question
var question = Banana.Ui.showQuestion('Question title', 'Print the report?');
//If 'true' do something...
if (question) {
//...for example add some text to the paragraph
report.addParagraph('The answer was YES!');
//...then print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
}
Dialog show text
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialshowtextdialog
// @description = Tutorial: Show text dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Open a dialog window showing the text.
//In this case we want to show the table Accounts as html file
Banana.Ui.showText(Banana.document.table('Accounts').toHtml(['Account', 'Group', 'Description', 'Balance'], true));
}
Dialog input text
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialinputtextdialog
// @description = Tutorial: Input text dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create a report
var report = Banana.Report.newReport("Report title");
//Open a dialog window asking the user to insert some text
//The text inserted is saved into a variable
var textInsertedByUser = Banana.Ui.getText('This is a dialog window', 'Insert some text', '');
//Add to the paragraph the text inserted by the user
report.addParagraph(textInsertedByUser);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Dialog item selection
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialitemselectiondialog
// @description = Tutorial: Item selection dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Open a dialog window asking to select an item from the list
//The selected item is then saved into a variable
var itemSelected = Banana.Ui.getItem('Input', 'Choose a value', ['Item ONE', 'Item TWO', 'Item THREE', 'Item FOUR', 'Item FIVE'], 2, false);
//Add the selected item to the paragraph
report.addParagraph(itemSelected);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Dialog period selection
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialperiodselectiondialog
// @description = Tutorial: Period selection dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create a report
var report = Banana.Report.newReport("Report title");
//Open a dialog windows to choose a period
var date = Banana.Ui.getPeriod('Period selection', '2015-01-01', '2015-12-31');
//Add the date information to the report
report.addParagraph(date.startDate);
report.addParagraph(date.endDate);
report.addParagraph(date.hasSelection);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Dialog multiple items selection
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialmultipleitemsselectiondialog
// @description = Tutorial: Multiple items selection dialog
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Open a dialog window asking to select an item from the list
//The selected item is then saved into a variable
var itemsSelected = Banana.Ui.getItems(
'Input',
'Choose one or more values',
['Item ONE', 'Item TWO', 'Item THREE', 'Item FOUR', 'Item FIVE'],
['Item ONE', 'Item THREE']
);
if (!itemsSelected) {
// User pressed cancel
return;
}
//Add the selected item to the paragraph
report.addParagraph("Selected items:");
if (itemsSelected.length > 0) {
for (var i = 0; i < itemsSelected.length; ++i) {
report.addParagraph("- " + itemsSelected[i]);
}
} else {
report.addParagraph("No item selected");
}
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Message functions
Show messages
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialshowmessages
// @description = Tutorial: Show messages
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
Banana.document.clearMessages();
//Step 1 - table access
var transactionTable = Banana.document.table('Transactions');
//Step 2 - loop on each row of the table
for (var i = 0; i < transactionTable.rowCount; i++) {
var tRow = transactionTable.row(i);
//Check the length of the description
if (tRow.value('Description').length > 20 && tRow.value('Description').length < 30) {
Banana.document.addMessage("Warning: row " + tRow.rowNr + ", description's length is " + tRow.value('Description').length + "!");
} else if (tRow.value('Description').length >= 30) {
Banana.document.addMessage("Error: row " + tRow.rowNr + ", description's length is " + tRow.value('Description').length + "!");
}
}
}
Show messages linked to table Transaction
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialshowmessageslinkedtotable
// @description = Tutorial: Show messages linked to table Transactions
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
Banana.document.clearMessages();
//Step 1 - table access
var transactionTable = Banana.document.table('Transactions');
//Step 2 - loop on each row of the table
for (var i = 0; i < transactionTable.rowCount; i++) {
var tRow = transactionTable.row(i);
//Check the length of the description
if (tRow.value('Description').length > 20 && tRow.value('Description').length < 30) {
tRow.addMessage("Warning: description's length is " + tRow.value('Description').length + "!");
} else if (tRow.value('Description').length >= 30) {
tRow.addMessage("Error: description's length is " + tRow.value('Description').length + "!");
}
}
}
Clear all messages
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialclearmessages
// @description = Tutorial: Clear all messages
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
Banana.document.clearMessages();
}
Save settings
Save settings
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialsavesettings
// @description = Tutorial: Save settings
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Readscript settings
var readText = Banana.document.scriptReadSettings();
//If there is a saved setting we can use it
if (readText) {
//Use the JSON.parse() to convert a JSON text into a JavaScript object
var object = JSON.parse(readText);
//Add a paragraph with the saved and parsed text
report.addParagraph('Previously saved value: ' + object);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
// If it doesn't exists a saved setting yet (which it happens the very first time the script is executed),
// it is necessary to create and save it
//For example using an dialog window to insert some text
var text = Banana.Ui.getText('Save settings example', 'Insert some text', '');
//Convert a JavaScript value into a JSON string using the JSON.stringify() function
var textToSave = JSON.stringify(text);
//Save script settings
var savedText = Banana.document.scriptSaveSettings(textToSave);
}
Retrieving informations from "File and account properties..."
Retrieve basic accounting informations
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretrievebasicaccountinginformation
// @description = Tutorial: Retrieve basic accounting information
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create report
var report = Banana.Report.newReport('Report title');
//Get some value of the accounting file
var fileName = Banana.document.info("Base", "FileName");
var decimalsAmounts = Banana.document.info("Base", "DecimalsAmounts");
var headerLeft = Banana.document.info("Base", "HeaderLeft");
var headerRight = Banana.document.info("Base", "HeaderRight");
var basicCurrency = Banana.document.info("AccountingDataBase", "BasicCurrency");
//For openingDate and closureDate
var startDate = Banana.document.info('AccountingDataBase', 'OpeningDate');
var endDate = Banana.document.info('AccountingDataBase', 'ClosureDate');
//For file accounting type
var fileType = Banana.document.info("Base", "FileType");
var fileGroup = Banana.document.info("Base", "FileTypeGroup");
var fileNumber = Banana.document.info("Base", "FileTypeNumber");
//Add the informations to the report
report.addParagraph(fileName);
report.addParagraph(decimalsAmounts);
report.addParagraph(headerLeft);
report.addParagraph(headerRight);
report.addParagraph(basicCurrency);
report.addParagraph(startDate);
report.addParagraph(endDate);
report.addParagraph(fileType);
report.addParagraph(fileGroup);
report.addParagraph(fileNumber);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retrieve address informations
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretrieveaddressinformation
// @description = Tutorial: Retrieve address information
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create report
var report = Banana.Report.newReport('Report title');
//Save informations
var company = Banana.document.info('AccountingDataBase', 'Company');
var courtesy = Banana.document.info('AccountingDataBase', 'Courtesy');
var name = Banana.document.info('AccountingDataBase', 'Name');
var familyName = Banana.document.info('AccountingDataBase', 'FamilyName');
var address1 = Banana.document.info('AccountingDataBase', 'Address1');
var address2 = Banana.document.info('AccountingDataBase', 'Address2');
var zip = Banana.document.info('AccountingDataBase', 'Zip');
var city = Banana.document.info('AccountingDataBase', 'City');
var state = Banana.document.info('AccountingDataBase', 'State');
var country = Banana.document.info('AccountingDataBase', 'Country');
var web = Banana.document.info('AccountingDataBase', 'Web');
var email = Banana.document.info('AccountingDataBase', 'Email');
var phone = Banana.document.info('AccountingDataBase', 'Phone');
var mobile = Banana.document.info('AccountingDataBase', 'Mobile');
var fax = Banana.document.info('AccountingDataBase', 'Fax');
var fiscalNumber = Banana.document.info('AccountingDataBase', 'FiscalNumber');
var vatNumber = Banana.document.info('AccountingDataBase', 'VatNumber');
//Add the informations to the report
report.addParagraph(company);
report.addParagraph(courtesy);
report.addParagraph(name);
report.addParagraph(familyName);
report.addParagraph(address1);
report.addParagraph(address2);
report.addParagraph(zip);
report.addParagraph(city);
report.addParagraph(state);
report.addParagraph(country);
report.addParagraph(web);
report.addParagraph(email);
report.addParagraph(phone);
report.addParagraph(mobile);
report.addParagraph(fax);
report.addParagraph(fiscalNumber);
report.addParagraph(vatNumber);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retrieving data from Banana tables
Retrieve rows table values using findRowByValue() function
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretrieverowstablevaluesusingfindrowbyvalue
// @description = Tutorial: Retrieve rows table values using findRowByValue() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create report
var report = Banana.Report.newReport("Report title");
//Save the row extracted from the table
var rowToExtract = Banana.document.table('Accounts').findRowByValue('Account', '1000');
//Add the Account, Description and Balance informations to the report
report.addParagraph(rowToExtract.value('Account'));
report.addParagraph(rowToExtract.value('Description'));
report.addParagraph(rowToExtract.value('Balance'));
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retrieve specific rows table values
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretrievespecificrowstablevalues
// @description = Tutorial: Retrieve specific rows table values
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create report
var report = Banana.Report.newReport("Report title");
// Step 1 - table access: specify the name of the table ("Accounts", "Transactions", "VatCodes", ...)
var accountsTable = Banana.document.table("Accounts");
//Step 2 - row selection: it is important to note that the rows of the Banana table start counting from 0,
//so keep in mind to specify one number less than the desired one
//1st row = 0
//2nd row = 1
//3rd row = 2
//4th row = 3
//...
var row3 = accountsTable.row(2); // We want the third row
// Step 3 - select all the desired columns
var account = row3.value("Account");
var description = row3.value("Description");
var balance = row3.value("Balance");
//Add the informations to the report
report.addParagraph(account);
report.addParagraph(description);
report.addParagraph(balance);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retrieve rows table values
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretrieverowstablevalues
// @description = Tutorial: Retrieve rows table values
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create a report
var report = Banana.Report.newReport('Report title');
//Step 1 - table access
var accountsTable = Banana.document.table('Accounts');
//Step 2 - loop on each row of the table, instead of specifying a single row
for (var rowNumber = 0; rowNumber < accountsTable.rowCount; rowNumber++) {
var tRow = accountsTable.row(rowNumber);
//Step 3 - select the desired columns
var account = tRow.value('Account');
var description = tRow.value('Description');
var balance = tRow.value('Balance');
//Add the informations to the report
report.addParagraph(account + ', ' + description + ', ' + balance);
}
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retrieve rows table with account number
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretretrieverowstablewithaccountnumber
// @description = Tutorial: Retrieve rows table with account number
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create a report
var report = Banana.Report.newReport('Report title');
//Step 1 - table access
var accountsTable = Banana.document.table('Accounts');
//Step 2 - loop on each row of the table, instead of specifying a single row
for (var rowNumber = 0; rowNumber < accountsTable.rowCount; rowNumber++) {
var tRow = accountsTable.row(rowNumber);
//If the row has an account number we save the values and print them
if (tRow.value('Account')) {
//Step 3 - select the desired columns
var account = tRow.value('Account');
var description = tRow.value('Description');
var balance = tRow.value('Balance');
//Add the informations to the report
report.addParagraph(account + ', ' + description + ', ' + balance);
}
}
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Print all table rows in a table format
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialretprintrowstablewithaccountnumberintableformat
// @description = Tutorial: Print rows table with account number in table format
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create a report
var report = Banana.Report.newReport('Report title');
//Create the table that will be printed on the report
var table = report.addTable("myTable");
//Add column titles to the table report
var tableHeader = table.getHeader();
tableRow = tableHeader.addRow();
tableRow.addCell("Account");
tableRow.addCell("Description");
tableRow.addCell("Balance");
//Step 1 - table access
var accountsTable = Banana.document.table('Accounts');
//Step 2 - loop on each row of the table, instead of specifying a single row
for (var rowNumber = 0; rowNumber < accountsTable.rowCount; rowNumber++) {
var tRow = accountsTable.row(rowNumber);
//If the row has an account number we save the values and print them
if (tRow.value('Account')) {
//Step 3 - select the desired columns
var account = tRow.value('Account');
var description = tRow.value('Description');
var balance = tRow.value('Balance');
//Add the values taken from the rows to the respective cells of the table
tableRow = table.addRow();
tableRow.addCell(account);
tableRow.addCell(description);
tableRow.addCell(balance);
}
}
//Create the styleSheet to
var stylesheet = Banana.Report.newStyleSheet();
//Create a table style adding the border
var style = stylesheet.addStyle("table");
stylesheet.addStyle("table.myTable td", "border: thin solid black");
//Print the report
Banana.Report.preview(report, stylesheet);
}
Find all rows that match a condition using findRows
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialfindrows
// @description = Tutorial: Retrieve all rows table that match a condition using findRows
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var tableAccounts = Banana.document.table('Accounts');
var rows = tableAccounts.findRows(accountStartsWith25XX);
var accounts = [];
for(var i = 0; i < rows.length; i++) {
accounts.push(rows[i].value('Account'));
}
Banana.Ui.showInformation('Info', 'Accounts that start with 25XX: ' + accounts.join(';'));
}
function accountStartsWith25XX(rowObj,rowNr,table) {
// Return true if account start with '25XX'
return rowObj.value('Account').startsWith('25');
}
Retrieve all rows that match a condition using extractRows
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialextractrows
// @description = Tutorial: Retrieve all rows table that match a condition using extractRows
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var tableAccounts = Banana.document.table('Accounts');
tableAccounts.extractRows(accountStartsWith25XX, 'Accounts that start with 25XX');
}
function accountStartsWith25XX(rowObj,rowNr,table) {
// Return true if account start with '25XX'
return rowObj.value('Account').startsWith('25');
}
Retriveing data with currentBalance() function
Amount of opening for all transactions for an account
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentbalancesingleaccount
// @description = Tutorial: Retrieve amount of an account using currentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take opening sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.currentBalance('1000', '', '').opening;
//Take opening sum for a specific period
var amount2 = Banana.document.currentBalance('1000', '2015-01-05', '2015-02-07').opening;
//Add a paragraph with the amounts just calculated
report.addParagraph(Banana.document.accountDescription('1000'));
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Amount of opening for all transactions for multiple accounts
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentbalancemultipleaccounts
// @description = Tutorial: Retrieve amount of multiple accounts using currentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take opening sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.currentBalance('1000|1010|1011', '', '').opening;
//Take opening sum for a specific period
var amount2 = Banana.document.currentBalance('1000|1010|1011', '2015-01-05', '2015-02-07').opening;
//Add a paragraph with the amounts just calculated
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Amount of opening for all transactions for a single group
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentbalancesinglegroup
// @description = Tutorial: Retrieve amount of single group using currentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take opening sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.currentBalance('Gr=100', '', '').opening;
//Take opening sum for a specific period
var amount2 = Banana.document.currentBalance('Gr=100', '2015-01-05', '2015-02-07').opening;
//Add a paragraph with the amounts just calculated
report.addParagraph(Banana.document.accountDescription('Gr=100'));
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Amount of opening for all transactions for multiple groups
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentbalancemultiplegroups
// @description = Tutorial: Retrieve amount of multiple groups using currentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take opening sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.currentBalance('Gr=100|110|120', '', '').opening;
//Take opening sum for a specific period
var amount2 = Banana.document.currentBalance('Gr=100|110|120', '2015-01-05', '2015-02-07').opening;
//Add a paragraph with the amounts just calculated
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Amount of opening for all transactions for a BClass
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentbalancesinglebclass
// @description = Tutorial: Retrieve amount of a single BClass using currentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take opening sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.currentBalance('BClass=1', '', '').opening;
//Take opening sum for a specific period
var amount2 = Banana.document.currentBalance('BClass=1', '2015-01-05', '2015-02-07').opening;
//Add a paragraph with the amounts just calculated
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Amount of opening for all transactions for multiple BClass values
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentbalancemultiplebclass
// @description = Tutorial: Retrieve amount of multiple BClass using currentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take opening sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.currentBalance('BClass=1|2', '', '').opening;
//Take opening sum for a specific period
var amount2 = Banana.document.currentBalance('BClass=1|2', '2015-01-05', '2015-02-07').opening;
//Add a paragraph with the amounts just calculated
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retriveing data with vatCurrentBalance() function
Sum the Vat amounts for the specified vat code
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialvatcurrentbalancesinglevatcode
// @description = Tutorial: Sum the VAT amounts for single VAT code using vatCurrentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take the vatAmount sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.vatCurrentBalance('S10', '', '').vatAmount;
//Take the vatAmount sum for a specific period
var amount2 = Banana.document.vatCurrentBalance('S10', '2015-01-01', '2015-01-06').vatAmount;
//Add a paragraph with the amounts just calculated
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Sum the Vat amounts for multiple vat codes
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialvatcurrentbalancemultiplevatcodes
// @description = Tutorial: Sum the VAT amounts for multiple VAT codes using vatCurrentBalance() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Take the vatAmount sum for a non-specific period: period taken from Banana
var amount1 = Banana.document.vatCurrentBalance('S10|P10', '', '').vatAmount;
//Take the vatAmount sum for a specific period
var amount2 = Banana.document.vatCurrentBalance('S10|P10', '2015-02-01', '2015-02-28').vatAmount;
//Add a paragraph with the amounts just calculated
report.addParagraph(amount1);
report.addParagraph(amount2);
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retriveing data with currentCard() function
For a given account without specifying the period
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentcardnoperiod
// @description = Tutorial: Use currentCard() function for a given account
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table with all the transactions of the given account
var transTab = Banana.document.currentCard('1010', '', '');
//For each row of the table we save the values
for (var i = 0; i < transTab.rowCount; i++) {
var tRow = transTab.row(i);
var date = tRow.value('JDate');
var account = tRow.value('JAccount');
var description = tRow.value("JDescription");
var debit = tRow.value('JDebitAmount');
var credit = tRow.value('JCreditAmount');
var balance = tRow.value('JBalance');
//Add a paragraph with the values just calculated
report.addParagraph(date + ', ' + account + ', ' + description + ', ' + debit + ', ' + credit + ', ' + balance);
}
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
For a given account and period
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialcurrentcardwithperiod
// @description = Tutorial: Use currentCard() function for a given account and period
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table with all transactions for the given account and period
var transTab = Banana.document.currentCard('1010', '2015-01-03', '2015-02-07');
//For each row of the table we save the values
for (var i = 0; i < transTab.rowCount; i++) {
var tRow = transTab.row(i);
var date = tRow.value('JDate');
var account = tRow.value('JAccount');
var description = tRow.value("JDescription");
var debit = tRow.value('JDebitAmount');
var credit = tRow.value('JCreditAmount');
var balance = tRow.value('JBalance');
//Add a paragraph with the values just calculated
report.addParagraph(date + ', ' + account + ', ' + description + ', ' + debit + ', ' + credit + ', ' + balance);
}
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Retriveing data with journal() function
Get all transaction for normal accounts
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialjournal
// @description = Tutorial: Retrieve all transactions for normal accounts using the journal() function
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Create a table with all transactions
var journal = Banana.document.journal(Banana.document.ORIGINTYPE_CURRENT, Banana.document.ACCOUNTTYPE_NORMAL);
//Read the table and save some values
for (var i = 0; i < journal.rowCount; i++) {
var tRow = journal.row(i);
var date = tRow.value('JDate');
var account = tRow.value('JAccount');
var accDescription = tRow.value('JAccountDescription');
var description = tRow.value('JDescription');
var vatCode = tRow.value('JVatCodeWithoutSign');
var vatDescription = tRow.value('JVatCodeDescription');
var amount = tRow.value('JAmount');
//Add to the paragraph the values just saved
report.addParagraph(date + ', ' + account + ', ' + accDescription + ', ' + description + ', ' + vatCode + ', ' + vatDescription + ', ' + amount);
}
//Print the report
var stylesheet = Banana.Report.newStyleSheet();
Banana.Report.preview(report, stylesheet);
}
Reading and writing xml files
Reading an xml file
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialreadxmlfile
// @description = Tutorial: Read an XML file
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
var xml = '<Library updated="2018-09-03">' + // To simplify,
'<Book>' + // the variable
'<Title>Paths of colours</Title>' + // xml contains
'<Author>Rosa Indaco</Author>' + // the xml string
'</Book>' +
'<Book>' +
'<Title>Accounting exercises</Title>' +
'<Author>Su Zhang</Author>' +
'</Book>' +
'</Library>';
var bookList = ""; // Create string for the output
var xmlFile = Banana.Xml.parse(xml); // Create XML Parse object
var xmlRoot = xmlFile.firstChildElement('Library'); // Find the first tag "Library" in the XML
var updateDate = xmlRoot.attribute('updated'); // Take attribute assigned to the tag
bookList += "Books in the library on " + updateDate + "\n\n"; // Append to the output string
var bookNode = xmlRoot.firstChildElement('Book'); // Take the first Book
while (bookNode) { // As long as there are books, repeat
var title = bookNode.firstChildElement('Title').text; // Find the first tag "Title" in the XML
var authorNode = bookNode.firstChildElement('Author'); // Find the first tag "Author" in the XML
var author = authorNode ? authorNode.text : 'unknow'; // Check whether there is a tag "Author"
bookList += title + " - " + author + "\n"; // Append to the output string
bookNode = bookNode.nextSiblingElement('Book'); // Go to the next book
}
Banana.Ui.showText("List of books present in the xml file", bookList); // Output the results
Writing an xml file
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialwritexmlfile
// @description = Tutorial: Write an XML file
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
var xmlDocument = Banana.Xml.newDocument("Library"); // Create XML file
xmlDocument.addComment("This is the generated xml file"); // Add comment to the file
var rootNode = xmlDocument.addElement("Library"); // Set root tag "Library"
rootNode.setAttribute("updated", "2018-09-03"); // Set attribute to the tag "Library"
var bookNode = rootNode.addElement("Book"); // Create tag "Book" child of "Library"
bookNode.addElement("Title").addTextNode("Paths of colours"); // Create tag "Title" child of "Book"
bookNode.addElement("Author").addTextNode("Rosa Indaco"); // Create tag "Author" child of "Book"
var bookNode = rootNode.addElement("Book"); // Create tag "Book" child of "Library"
bookNode.addElement("Title").addTextNode("Accounting exercises"); // Create tag "Title" child of "Book"
bookNode.addElement("Author").addTextNode("Su Zhang"); // Create tag "Author" child of "Book"
var xmlString = Banana.Xml.save(xmlDocument); // Create output
// Show the xml file
Banana.Ui.showText("Xml file", xmlString); // Generate output
}
Validate an xml file via xsd
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialxmlvalidation
// @description = Tutorial: Validate an XML file via XSD
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
// The xml file
var xml = '<Library updated="2018-09-03"> \
<Book> \
<Title>Paths of colours</Title> \
<Author>Rosa Indaco</Author> \
</Book> \
<Book> \
<Title>Accounting exercises</Title> \
<Author>Su Zhang</Author> \
</Book> \
</Library>';
// Parse the xml
var xmlFile = Banana.Xml.parse(xml);
// Validate the xml
var valid = Banana.Xml.validate(xmlFile, 'documents:xsd');
if (valid) {
Banana.Ui.showInformation('Validation result', 'The xml is valid');
} else {
Banana.Ui.showInformation('Validation result', 'The xml is not valid: ' + Banana.Xml.errorString);
}
}
Filter for xml file
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialgeneralxmlFile
// @description = Tutorial: General functions on XML files
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2020-03-27
// @inputdatasource = none
// @timeout = -1
function exec(){
const xml = '<solarSystem updated="2020-03-27">' +
'<Planet>' +
'<name>Sun</name>' +
'<sizeOrder>1</sizeOrder>' +
'</Planet>' +
'<Planet>' +
'<name>Earth</name>' +
'<sizeOrder>6</sizeOrder>' +
'<satellite>Moon</satellite>' +
'</Planet>' +
'<Planet>' +
'<name>Jupiter</name>' +
'<sizeOrder>2</sizeOrder>' +
'<satellite>Callisto</satellite>' +
'</Planet>' +
'<Planet>' +
'<name>Saturn</name>' +
'<sizeOrder>3</sizeOrder>' +
'<satellite>Cronus</satellite>' +
'</Planet>' +
'<Planet>' +
'<name>Mars</name>' +
'<sizeOrder>8</sizeOrder>' +
'<satellite>Phobos</satellite>' +
'</Planet>' +
'<Planet>' +
'<name>Neptune</name>' +
'<sizeOrder>5</sizeOrder>' +
'<satellite>Triton</satellite>' +
'</Planet>' +
'<Planet>' +
'<name>Mercury</name>' +
'<sizeOrder>9</sizeOrder>' +
'</Planet>' +
'<Planet>' +
'<name>Venus</name>' +
'<sizeOrder>7</sizeOrder>' +
'</Planet>' +
'<Planet>' +
'<name>Uranus</name>' +
'<sizeOrder>4</sizeOrder>' +
'<satellite>Ariel</satellite>' +
'</Planet>' +
'</solarSystem>';
var outputString = "";
var xmlFile = Banana.Xml.parse(xml);
var xmlRoot = xmlFile.firstChildElement('solarSystem');
var updateDate = xmlRoot.attribute('updated');
outputString += "Planets in the solar System with satellites updated on " + updateDate + "\n\n";
var planet = xmlRoot.firstChildElement('Planet');
while (planet) {
if (planet.hasChildElements('satellite')) {
var planetName = planet.firstChildElement('name').text;
var planetSatellite = planet.firstChildElement('satellite').text;
outputString += "name: " + planetName + ", satellite: " + planetSatellite + "\n";
}
planet = planet.nextSiblingElement('Planet');
}
Banana.Ui.showText("Planets in the XML with satellites", outputString);
}
Export Transaction table to xml file
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.export
// @api = 1.0
// @pubdate = 2016-04-08
// @doctype = *.*
// @description = Export into an xml file (.xml)
// @task = export.file
// @exportfiletype = xml
// @timeout = -1
function exec() {
var xmlDocument = Banana.Xml.newDocument("Transactions");
xmlDocument.addComment("This is the generated xml file");
var rootNode = xmlDocument.addElement("transactions");
var tableTransactions = Banana.document.table('Transactions');
if (!tableTransactions) {
return;
}
for (i=0;i<tableTransactions.rowCount;i++) {
if (tableTransactions.row(i).value('Amount')) {
var transactionNode = rootNode.addElement("transaction");
transactionNode.addElement("date").addTextNode(tableTransactions.row(i).value('Date'));
transactionNode.addElement("description").addTextNode(tableTransactions.row(i).value('Description'));
transactionNode.addElement("amount").addTextNode(tableTransactions.row(i).value('Amount'));
}
}
var xmlString = Banana.Xml.save(xmlDocument);
Banana.Ui.showText("Xml file", xmlString);
return xmlString;
}
Import Transactions from xml file
// Banana Accounting Extension Javascript
// @api = 1.0
// @pubdate = 2020-04-02
// @id = ch.banana.uni.app.tutorialretrieverowstablevalues
// @description = Import transactions
// @task = import.transactions
// @outputformat = transactions.simple
// @doctype = *
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @inputfilefilter = Text files (*.xml);;All files (*.*)
// @publisher = Banana.ch SA
// @timeout = -1
function exec(inputFile) {
var CSV_String = "Date" + '","' + "Description" + '","' + "Income" + '","' + "Expenses" + '\n';
var xmlFile = Banana.Xml.parse(inputFile);
var xmlRoot = xmlFile.firstChildElement('transactions');
var transactionNode = xmlRoot.firstChildElement('transaction');
while (transactionNode) {
var date = transactionNode.firstChildElement('date').text;
var description = transactionNode.firstChildElement('description').text;
var income = transactionNode.firstChildElement('income').text;
var expenses = transactionNode.firstChildElement('expenses').text;
CSV_String += ('"' + date + '","' + description + '","' + income + '","' + expenses + '"' + '\n');
transactionNode = transactionNode.nextSiblingElement('transaction');
}
Banana.console.debug(CSV_String);
var csvFile = Banana.Converter.csvToArray(CSV_String, ',', '"');
var tsvFile = Banana.Converter.arrayToTsv(csvFile);
return tsvFile;
}
Debugging
Ouput messages to the debug panel
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialdebugmessages
// @description = Tutorial: Output messages to the debug panel
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
Banana.console.log("An info message");
Banana.console.debug("A debug message");
Banana.console.warn("A warning message"); // Since Banana 9.0.4 use Banana.console.warning
Banana.console.critical("A critical message");
}
Progress bar and timeout
Script timeout
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialscripttimeout
// @description = Tutorial: Progress bar
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-12-06
// @inputdatasource = none
// @timeout = 2000
function exec() {
while(true) {
}
}
Progess bar
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialprogressbar
// @description = Tutorial: Progress bar
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-12-06
// @inputdatasource = none
// @timeout = 20000
function exec() {
var nr = 100000000;
var progressBar = Banana.application.progressBar;
progressBar.start(nr);
for (var i = 0; i < nr; ++i) {
if (!progressBar.step(1)) {
break; // Operation canceled by the user
}
}
}
User interface
QTableWidget interaction
// Banana Accounting Extension Javascript
/**
* This example shows the use of a QTableWidget
* The user interface is defined in file "ch.banana.tutorial.tablewidget.ui"
* This example is also found in the tutorial file "embedded_javascript_tutorial1.ac2"
*/
// @id = ch.banana.exmaple.tablewidget
// @version = 1.0
// @date = 2019-12-06
// @publisher = Banana.ch SA
// @description = Tutorial QTableWidget
// @task = app.command
// @inputdatasource = none
// @timeout = -1
// @doctype = nodocument
var param = {};
/** Dialog's functions declaration */
var dialog = Banana.Ui.createUi("documents:740.ui");
var table = dialog.findChild("tableWidget");
var buttonBox = dialog.findChild("buttonBox");
var statusLabel = dialog.findChild("statusLabel");
/** Main function */
function exec(inData) {
//Check the version of Banana
var requiredVersion = "9.1.0.0";
if (!Banana.compareVersion || Banana.compareVersion(Banana.application.version, requiredVersion) < 0) {
Banana.Ui.showInformation("You need Banana 9.1.0.0 or newer to run this example");
return;
}
// Connect button box signals
buttonBox.accepted.connect(function() {dialog.close();});
buttonBox.rejected.connect(function() {dialog.close();});
// Set the table read
// table.setEditTriggers(0); // see flags QAbstractItemView::EditTriggers
// Set columns
table.setColumnCount(4);
table.setHorizontalHeaderLabels(["A", "B", "C", "D"])
table.setColumnWidth(1, 200);
// Set rows
table.setRowCount(5);
// Set cell text
table.item(1,0).text = "hello";
// Set cell colors
var item2 = table.item(1,1);
item2.text = "colors";
item2.background = "red";
item2.foreground = "white";
// Set cell checkable
var item3 = table.item(1,2);
item3.flags = 0x33; // See flags Qt::ItemFlags
item3.text = "check-it";
item3.checkState = 0; // See flags Qt::CheckState
// Set current cell
// table.setCurrentCell(1,0);
// Connect table's signals after the table is populated
table.cellClicked.connect(updateStatusBar);
table.cellChanged.connect(updateStatusBar);
// Show dialog
Banana.application.progressBar.pause();
dialog.exec();
Banana.application.progressBar.resume();
// Get table size
Banana.console.log("Table size: " + table.rowCount + ", " + table.columnCount);
// Get current position
Banana.console.log("Current position: " + table.currentRow + ", " + table.currentColumn);
// Get current text
var curItem = table.currentItem();
Banana.console.log("Current text: " + curItem.text);
// Get item check state
Banana.console.log("Item check state: " + (item3.checkState === 2 ? "checked" : "unchecked"));
}
// Update status bar with useful info about the current cell
function updateStatusBar() {
var curItem = table.currentItem();
if (curItem) {
var msg = "Cell " + (table.currentRow + 1) + "," + (table.currentColumn + 1) + " clicked. ";
if (curItem.text.length > 0)
msg += "Text: '" + curItem.text + "'. ";
if (curItem.checkState === 2)
msg += "Checked: true.";
statusLabel.text = msg;
} else {
statusLabel.text = "";
}
}
- User interface for QTableWidget interaction
// Banana Accounting Extension Javascript
<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>Dialog</class>
<widget class="QDialog" name="Dialog">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>584</width>
<height>411</height>
</rect>
</property>
<property name="windowTitle">
<string>QTableWidget Tutorial</string>
</property>
<layout class="QVBoxLayout" name="verticalLayout">
<item>
<widget class="QTableWidget" name="tableWidget"/>
</item>
<item>
<layout class="QHBoxLayout" name="horizontalLayout">
<item>
<widget class="QLabel" name="statusLabel">
<property name="text">
<string/>
</property>
</widget>
</item>
</layout>
</item>
<item>
<widget class="QDialogButtonBox" name="buttonBox">
<property name="standardButtons">
<set>QDialogButtonBox::Ok</set>
</property>
</widget>
</item>
</layout>
</widget>
<tabstops>
<tabstop>tableWidget</tabstop>
</tabstops>
<resources/>
<connections/>
</ui>
Find dialog
// Banana Accounting Extension Javascript
/**
* This example search a text in all the tables of the document,
* and show the matches in the messages pane.
*/
// @id = ch.banana.scripts.find
// @version = 1.3
// @date = 2019-12-06
// @publisher = Banana.ch SA
// @description = Find in whole accounting
// @description.it = Cerca in tutta la contabilità
// @description.de = Suchen in der gesamten Buchhaltung
// @description.fr = Chercher dans toute la comptabilité
// @task = app.command
// @inputdatasource = none
// @timeout = -1
/**
* param values are loaded from Banana.document, edited through dialog and saved to Banana.document
* This array object is like a map (associative array) i.e. "key":"value", see initParam()
* Examples of keys: searchText, wholeText, ...
*/
var param = {};
/** Dialog's functions declaration */
var dialog = Banana.Ui.createUi("documents:742.ui"); // ch.banana.scripts.find.ui
/** Dialog Objects declaration */
var findNextButton = dialog.findChild('findNextButton');
var helpButton = dialog.findChild('helpButton');
var closeButton = dialog.findChild('closeButton');
var searchTextLineEdit = dialog.findChild('searchTextLineEdit');
var matchCaseCheckBox = dialog.findChild('matchCaseCheckBox');
var wholeTextCheckBox = dialog.findChild('wholeTextCheckBox');
dialog.checkdata = function () {
var valid = true;
if (searchTextLineEdit.text.length <= 0) {
Banana.Ui.showInformation("Error", "Search text can't be empty");
valid = false;
}
if (valid) {
dialog.accept();
}
};
dialog.showHelp = function () {
Banana.Ui.showHelp("ch.banana.script.find");
};
dialog.closeDialog = function () {
dialog.close();
};
/** Dialog's events declaration */
findNextButton.clicked.connect(dialog, dialog.checkdata);
helpButton.clicked.connect(dialog, dialog.showHelp);
closeButton.clicked.connect(dialog, dialog.closeDialog);
/** Main function */
function exec(inData) {
//calls dialog
// var rtnDialog = true;
var rtnDialog = dialogExec();
//search text in the whole accounting
if (rtnDialog && Banana.document) {
Banana.document.clearMessages();
searchInTables();
}
}
/** Show the dialog and set the parameters */
function dialogExec() {
// Read saved script settings
initParam();
if (Banana.document) {
var data = Banana.document.getScriptSettings();
if (data.length > 0) {
param = JSON.parse(data);
}
}
// Text at cursor position
var cursor = Banana.document.cursor;
var columnName = cursor.tableName === 'Documents' && cursor.columnName == 'Attachments' ? 'Description' : cursor.columnName;
param["searchText"] = Banana.document.value(cursor.tableName,cursor.rowNr,columnName);
searchTextLineEdit.text = param["searchText"];
// Set dialog parameters
if (param["matchCase"] == "true")
matchCaseCheckBox.checked = true;
else
matchCaseCheckBox.checked = false;
if (param["wholeText"] == "true")
wholeTextCheckBox.checked = true;
else
wholeTextCheckBox.checked = false;
Banana.application.progressBar.pause();
var dlgResult = dialog.exec();
Banana.application.progressBar.resume();
if (dlgResult !== 1)
return false;
// Read dialog parameters
param["searchText"] = searchTextLineEdit.text;
if (matchCaseCheckBox.checked)
param["matchCase"] = "true";
else
param["matchCase"] = "false";
if (wholeTextCheckBox.checked)
param["wholeText"] = "true";
else
param["wholeText"] = "false";
// Save script settings
var paramString = JSON.stringify(param);
var value = Banana.document.setScriptSettings(paramString);
return true;
}
/** Initialize dialog values with default values */
function initParam() {
param = {
"searchText": "",
"matchCase": "false",
"wholeText": "false"
};
}
/** Search a text in the accounting's tables */
function searchInTables() {
var searchText = param["searchText"];
if (param["matchCase"] === "false")
searchText = searchText.toLowerCase();
var tables = Banana.document.tableNames;
// Tables
for (var t=0; t < tables.length; t++) {
var table = Banana.document.table(tables[t]);
var columns = table.columnNames;
// Rows
for (var r=0; r < table.rowCount; r++) {
// Columns
for (var c=0; c < columns.length; c++) {
var textFound = false;
var text = table.value(r, columns[c]);
if (param["matchCase"] === "false")
text = text.toLowerCase();
// Find text
if (param["wholeText"] === "true") {
if (text === searchText)
textFound = true;
} else {
if (text.indexOf(searchText) >= 0)
textFound = true;
}
// Show message
if (textFound) {
table.addMessage("Text \"" + param["searchText"] +
"\" found in \"" + table.value(r, columns[c]) + "\"", r, columns[c]);
}
}
}
}
}
- User interface for Find dialog
// Banana Accounting Extension Javascript
<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>DlgFind</class>
<widget class="QDialog" name="DlgFind">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>438</width>
<height>193</height>
</rect>
</property>
<property name="windowTitle">
<string>Find</string>
</property>
<layout class="QVBoxLayout" name="verticalLayout_2">
<item>
<layout class="QGridLayout" name="gridLayout">
<property name="horizontalSpacing">
<number>40</number>
</property>
<item row="0" column="0">
<widget class="QLabel" name="searchTextLabel">
<property name="text">
<string>Search &text</string>
</property>
</widget>
</item>
<item row="0" column="1">
<widget class="QLineEdit" name="searchTextLineEdit"/>
</item>
</layout>
</item>
<item>
<widget class="QGroupBox" name="groupBox">
<property name="title">
<string>Options</string>
</property>
<property name="flat">
<bool>false</bool>
</property>
<property name="checkable">
<bool>false</bool>
</property>
<layout class="QVBoxLayout" name="verticalLayout">
<item>
<widget class="QCheckBox" name="matchCaseCheckBox">
<property name="text">
<string>&Match case</string>
</property>
</widget>
</item>
<item>
<widget class="QCheckBox" name="wholeTextCheckBox">
<property name="text">
<string>&Whole text</string>
</property>
</widget>
</item>
</layout>
</widget>
</item>
<item>
<spacer name="verticalSpacer">
<property name="orientation">
<enum>Qt::Vertical</enum>
</property>
<property name="sizeHint" stdset="0">
<size>
<width>20</width>
<height>15</height>
</size>
</property>
</spacer>
</item>
<item>
<layout class="QHBoxLayout" name="horizontalLayout">
<item>
<spacer name="horizontalSpacer">
<property name="orientation">
<enum>Qt::Horizontal</enum>
</property>
<property name="sizeHint" stdset="0">
<size>
<width>80</width>
<height>20</height>
</size>
</property>
</spacer>
</item>
<item>
<widget class="QPushButton" name="findNextButton">
<property name="text">
<string>&Find</string>
</property>
</widget>
</item>
<item>
<widget class="QPushButton" name="helpButton">
<property name="text">
<string>Help</string>
</property>
</widget>
</item>
<item>
<widget class="QPushButton" name="closeButton">
<property name="text">
<string>Close</string>
</property>
</widget>
</item>
</layout>
</item>
</layout>
</widget>
<tabstops>
<tabstop>matchCaseCheckBox</tabstop>
<tabstop>findNextButton</tabstop>
</tabstops>
<resources/>
<connections/>
</ui>
Export
Export tutorial1 javascript codes
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exportjavascriptcodestutorial1.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes of the Tutorial 1 examples
// @task = export.file
// @doctype = *.*
// @docproperties =
// @timeout = -1
// @exportfiletype = js
//Main function
function exec() {
//Check if we are on an opened document
if (!Banana.document) {
return;
}
//Take the table Documents
var documents = Banana.document.table('Documents');
//We check if the table Documents exists, then we can take all the codes
//If the table Documents doesn't exists, then we stop the script execution
if (!documents) {
return;
} else {
//We use this variable to save all the codes
var jsCode = '';
jsCode += '// Copyright [2018] [Banana.ch SA - Lugano Switzerland]' + '\n';
jsCode += '// ' + '\n';
jsCode += '// Licensed under the Apache License, Version 2.0 (the "License");' + '\n';
jsCode += '// you may not use this file except in compliance with the License.' + '\n';
jsCode += '// You may obtain a copy of the License at' + '\n';
jsCode += '// ' + '\n';
jsCode += '// http://www.apache.org/licenses/LICENSE-2.0' + '\n';
jsCode += '// ' + '\n';
jsCode += '// Unless required by applicable law or agreed to in writing, software' + '\n';
jsCode += '// distributed under the License is distributed on an "AS IS" BASIS,' + '\n';
jsCode += '// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.' + '\n';
jsCode += '// See the License for the specific language governing permissions and' + '\n';
jsCode += '// limitations under the License.' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n';
//Function call to get all the tutorial's codes
jsCode = getJavascriptCode(jsCode, documents);
}
return jsCode;
}
//Function that, for each tutorial's example, gets the javascript code and save it into the jsCode variable.
function getJavascriptCode(jsCode, documents) {
//Read row by row the table Documents
var len = documents.rowCount;
for (var i = 0; i < len; i++) {
//Create a variable for the row
var tRow = documents.row(i);
//We get some values
var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
var id = tRow.value("RowId"); //the id of the example
var description = tRow.value("Description"); //the description of the example
var attachments = tRow.value("Attachments"); //the javascript code of the example
//We consider only the rows that contain an id, a description and an attachment
if (id && description && attachments) {
//At the beginning of each code, we insert some information
jsCode += "/** " + '\n';
jsCode += " File: " + fName + '\n';
jsCode += " Id: " + id + '\n';
jsCode += " Description: " + description + '\n';
jsCode += "*/" + '\n';
//we add the code of the attachments
jsCode += attachments;
//At the end of each code, we insert some new lines
jsCode += '\n' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n';
}
}
//Finally we return the variable containing all the codes of all the examples
return jsCode;
}
Export tutorial1 javascript codes to HTML
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exporthtmljavascriptcodestutorial1.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes of the Tutorial 1 examples in HTML
// @task = export.file
// @doctype = *.*
// @docproperties =
// @timeout = -1
// @exportfiletype = html
//Main function
function exec() {
//Check if we are on an opened document
if (!Banana.document) {
return;
}
//Take the table Documents
var documents = Banana.document.table('Documents');
//We check if the table Documents exists, then we can take all the codes
//If the table Documents doesn't exists, then we stop the script execution
if (!documents) {
return;
} else {
//We use this variable to save all the codes
var jsCode = '';
//Function call to get all the tutorial's codes
jsCode = getJavascriptCode(jsCode, documents);
}
return jsCode;
}
//Function that, for each tutorial's example, gets the javascript code and save it into the jsCode variable.
function getJavascriptCode(jsCode, documents) {
//Read row by row the table Documents
var len = documents.rowCount;
for (var i = 0; i < len; i++) {
//Create a variable for the row
var tRow = documents.row(i);
//We get some values
var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
var section = tRow.value("Section"); // the section column used to define the title type
var id = tRow.value("RowId"); //the id of the example
var description = tRow.value("Description"); //the description of the example
var attachments = tRow.value("Attachments"); //the javascript code of the example
attachments = attachments.replace(/</g,'<'); // replace the < symbol
attachments = attachments.replace(/>/g,'>'); // replace the > symbol
//For tilte h2 we conside only rows with section h2
if (section && section === "h2") {
jsCode += '<h2>' + description + '</h2>\n';
}
//For js codes we consider only rows with section h3, id, description and attachment
else if (section && section === "h3" && id && description && attachments) {
jsCode += '<h3>' + description + '</h3>\n';
jsCode += '<pre><code class="language-javascript">\n';
jsCode += '// Banana Accounting Extension Javascript' + '\n';
jsCode += attachments;
jsCode += '\n';
jsCode += '</code></pre>\n';
}
}
//Finally we return the variable containing all the codes of all the examples
return jsCode;
}
Embedded Extensions examples Tutorial 2 (Reports)
Complete examples
Account statement - account and period choice
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialaccountstatement
// @description = Tutorial: Return an account statement for the given account and period
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Function call to add the footer to the report
addFooter(report);
//Function call to take a list of all the account numbers
var accountList = getAccountsList();
//Open a dialog window asking to select an item from the list. The selected item is then saved into a variable
var accountNumber = Banana.Ui.getItem('Account selection', 'Select an account', accountList, 0, false);
//Open a dialog windows taskint the user to choose a period
//Return an object with the attributes 'startDate', 'endDate' and 'hasSelection' or undefined if the user clicked cancel
var date = Banana.Ui.getPeriod('Period selection', '2015-01-01', '2015-12-31');
//Check if user has clicked cancel, then use the default startDate/endDate (whole year)
if (date) {
var openingDate = date.startDate;
var closureDate = date.endDate;
}
//--------------//
// 1. TITLE //
//--------------//
//Take the description of the given account and add it to the paragraph
var accountDescription = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Description');
report.addParagraph("Account statement" + " - " + accountNumber + " " + accountDescription, "styleTitle styleBottomBorder");
report.addParagraph(" ");
//--------------//
// 2. TABLE //
//--------------//
//Create a table object with all transactions for the given account and period
var transTab = Banana.document.currentCard(accountNumber, openingDate, closureDate);
//Create the table that will be printed on the report
var table = report.addTable("myTable");
//Add column titles to the table report
var tableHeader = table.getHeader();
tableRow = tableHeader.addRow();
tableRow.addCell("Date", "styleTableHeader");
tableRow.addCell("Description", "styleTableHeader");
tableRow.addCell("Contra Account", "styleTableHeader");
tableRow.addCell("Debit amount", "styleTableHeader");
tableRow.addCell("Credit amount", "styleTableHeader");
tableRow.addCell("Balance", "styleTableHeader");
//Add the values taken from each row of the table (except the last one) to the respective cells of the table
for (var i = 0; i < transTab.rowCount - 1; i++) {
var tRow = transTab.row(i);
tableRow = table.addRow();
tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')));
tableRow.addCell(tRow.value("JDescription"));
tableRow.addCell(tRow.value("JContraAccount"), "styleAccount");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount");
if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount");
} else {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount");
}
}
//We add last row (totals) separately because we want to apply a special style only to this row
for (var i = transTab.rowCount - 1; i < transTab.rowCount; i++) {
var tRow = transTab.row(i);
tableRow = table.addRow();
tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')), "styleTotal");
tableRow.addCell(tRow.value("JDescription"), "styleTotal");
tableRow.addCell(tRow.value("JContraAccount"), "styleAccount styleTotal");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount styleTotal");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount styleTotal");
if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount styleTotal");
} else {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount styleTotal");
}
}
//Functin call to create all the styles
var stylesheet = createStyleSheet();
//Print the report
Banana.Report.preview(report, stylesheet);
}
//This function adds a Footer to the report
function addFooter(report) {
report.getFooter().addClass(".img");
report.getFooter().addImage("documents:logo");
}
//This function take from Banana table 'Accounts' all the account numbers and create a list
function getAccountsList() {
var arrList = [];
for (var i = 0; i < Banana.document.table('Accounts').rowCount; i++) {
var tRow = Banana.document.table('Accounts').row(i);
if (tRow.value("Account")) {
arrList.push(tRow.value("Account"));
}
}
return arrList;
}
//The main purpose of this function is to create styles for the report print
function createStyleSheet() {
var stylesheet = Banana.Report.newStyleSheet();
//Page style
var pageStyle = stylesheet.addStyle("@page");
pageStyle.setAttribute("margin", "20m 15mm 15mm 25mm");
//Footer style
style = stylesheet.addStyle(".img");
style.setAttribute("width", "50%");
style.setAttribute("height", "auto");
style.setAttribute("text-align", "center");
//Title style
style = stylesheet.addStyle(".styleTitle");
style.setAttribute("font-size", "14pt");
style.setAttribute("font-weight", "bold");
//Bottom border
style = stylesheet.addStyle(".styleBottomBorder");
style.setAttribute("border-bottom", "1px solid black");
//Create a table style adding the border
style = stylesheet.addStyle("table");
style.setAttribute("width", "100%");
stylesheet.addStyle("table.myTable td", "border: thin solid black");
//Style for the table titles
style = stylesheet.addStyle(".styleTableHeader");
style.setAttribute("font-weight", "bold");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("background-color", "#ffd100");
style.setAttribute("color", "#1b365d");
//Style for account numbers
style = stylesheet.addStyle(".styleAccount");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("text-align", "center");
//Style for amounts
style = stylesheet.addStyle(".styleAmount");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("text-align", "right");
//Style for balances
style = stylesheet.addStyle(".styleBalance");
style.setAttribute("color", "red");
//Style for the total of the table
style = stylesheet.addStyle(".styleTotal");
style.setAttribute("font-weight", "bold");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("background-color", "#b7c3e0");
style.setAttribute("border-bottom", "1px double black");
return stylesheet;
}
Account statement - more accounts print (without accounts/period choice)
// Banana Accounting Extension Javascript
// @api = 1.0
// @id = ch.banana.uni.app.tutorialaccountstatements
// @description = Tutorial: Return a multiple accounts statements
// @task = app.command
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2019-01-25
// @inputdatasource = none
// @timeout = -1
function exec() {
//Create the report
var report = Banana.Report.newReport('Report title');
//Function call to add the footer to the report
addFooter(report);
//First function call to create the page of the report
createReport(report, '1010', '2015-01-01', '2015-12-31');
//Add a page break after the first page
report.addPageBreak();
//Second function call to create the second page of the report
createReport(report, '1011', '2015-01-01', '2015-12-31');
//Functin call to create all the styles
var stylesheet = createStyleSheet();
//Print the report
Banana.Report.preview(report, stylesheet);
}
//The purpose of this function is to create a report using the given accounts number and period
function createReport(report, accountNumber, openingDate, closureDate) {
//--------------//
// 1. ADDRESS //
//--------------//
//Read from the table 'Address' some informations and save them
var namePrefix = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('NamePrefix');
var firstName = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('FirstName');
var familyName = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('FamilyName');
var organisationName = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('OrganisationName');
var address = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Street');
var countryCode = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('CountryCode');
var postalCode = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('PostalCode');
var locality = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Locality');
var telephone = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('PhoneMain');
var email = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('EmailWork');
//Add a section to the paragraph for the address
var sectionAddress = report.addSection("styleAddress");
//Check if there are the desired address informations, then add them to the paragraph
if (organisationName) {
sectionAddress.addParagraph(organisationName);
}
if (namePrefix) {
sectionAddress.addParagraph(namePrefix);
}
if (firstName && familyName) {
sectionAddress.addParagraph(firstName + ' ' + familyName);
}
if (address) {
sectionAddress.addParagraph(address);
}
if (countryCode && postalCode && locality) {
sectionAddress.addParagraph(countryCode + ' - ' + postalCode + ' ' + locality);
}
if (telephone) {
sectionAddress.addParagraph('Tel: ' + telephone);
}
if (email) {
sectionAddress.addParagraph('Email: ' + email);
}
//--------------//
// 2. TITLE //
//--------------//
//Take the description of the given account and add it to the paragraph
var accountDescription = Banana.document.table('Accounts').findRowByValue('Account', accountNumber).value('Description');
report.addParagraph(" ", "styleParagraph");
report.addParagraph("Account statement" + " - " + accountNumber + " " + accountDescription, "styleTitle styleBottomBorder");
report.addParagraph(" ");
//--------------//
// 3. TABLE //
//--------------//
//Create a table object with all transactions for the given account and period
var transTab = Banana.document.currentCard(accountNumber, openingDate, closureDate);
//Create the table that will be printed on the report
var table = report.addTable("myTable");
//Add column titles to the table report
var tableHeader = table.getHeader();
tableRow = tableHeader.addRow();
tableRow.addCell("Date", "styleTableHeader");
tableRow.addCell("Description", "styleTableHeader");
tableRow.addCell("Contra Account", "styleTableHeader");
tableRow.addCell("Debit amount", "styleTableHeader");
tableRow.addCell("Credit amount", "styleTableHeader");
tableRow.addCell("Balance", "styleTableHeader");
//Add the values taken from each row of the table (except the last one) to the respective cells of the table
for (var i = 0; i < transTab.rowCount - 1; i++) {
var tRow = transTab.row(i);
tableRow = table.addRow();
tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')));
tableRow.addCell(tRow.value("JDescription"));
tableRow.addCell(tRow.value("JContraAccount"), "styleAccount");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount");
if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount");
} else {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount");
}
}
//We add last row (totals) separately because we want to apply a special style only to this row
for (var i = transTab.rowCount - 1; i < transTab.rowCount; i++) {
var tRow = transTab.row(i);
tableRow = table.addRow();
tableRow.addCell(Banana.Converter.toLocaleDateFormat(tRow.value('JDate')), "styleTotal");
tableRow.addCell(tRow.value("JDescription"), "styleTotal");
tableRow.addCell(tRow.value("JContraAccount"), "styleAccount styleTotal");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JDebitAmountAccountCurrency')), "styleAmount styleTotal");
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JCreditAmountAccountCurrency')), "styleAmount styleTotal");
if (Banana.SDecimal.sign(tRow.value('JBalanceAccountCurrency')) >= 0) {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleAmount styleTotal");
} else {
tableRow.addCell(Banana.Converter.toLocaleNumberFormat(tRow.value('JBalanceAccountCurrency')), "styleBalance styleAmount styleTotal");
}
}
}
//This function adds a Footer to the report
function addFooter(report) {
report.getFooter().addClass(".img");
report.getFooter().addImage("documents:logo");
}
//The main purpose of this function is to create styles for the report print
function createStyleSheet() {
var stylesheet = Banana.Report.newStyleSheet();
//Page style
var pageStyle = stylesheet.addStyle("@page");
pageStyle.setAttribute("margin", "20m 15mm 15mm 25mm");
//Footer style
style = stylesheet.addStyle(".img");
style.setAttribute("width", "50%");
style.setAttribute("height", "auto");
style.setAttribute("text-align", "center");
//Title style
style = stylesheet.addStyle(".styleTitle");
style.setAttribute("font-size", "14pt");
style.setAttribute("font-weight", "bold");
//Bottom border
style = stylesheet.addStyle(".styleBottomBorder");
style.setAttribute("border-bottom", "1px solid black");
//Create a table style adding the border
style = stylesheet.addStyle("table");
style.setAttribute("width", "100%");
stylesheet.addStyle("table.myTable td", "border: thin solid black");
//Style for the table titles
style = stylesheet.addStyle(".styleTableHeader");
style.setAttribute("font-weight", "bold");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("background-color", "#ffd100");
style.setAttribute("color", "#1b365d");
//Style for account numbers
style = stylesheet.addStyle(".styleAccount");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("text-align", "center");
//Style for amounts
style = stylesheet.addStyle(".styleAmount");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("text-align", "right");
//Style for balances
style = stylesheet.addStyle(".styleBalance");
style.setAttribute("color", "red");
//Style for the total of the table
style = stylesheet.addStyle(".styleTotal");
style.setAttribute("font-weight", "bold");
style.setAttribute("padding-bottom", "5px");
style.setAttribute("background-color", "#b7c3e0");
style.setAttribute("border-bottom", "1px double black");
style = stylesheet.addStyle(".styleAddress");
style.setAttribute("position", "absolute");
style.setAttribute("left", "0mm");
style.setAttribute("top", "0mm");
style.setAttribute("width", "80mm");
style.setAttribute("height", "30mm");
style.setAttribute("overflow-shrink-max", "0.6");
style.setAttribute("overflow", "shrink");
//Add a space between the date and the title
style = stylesheet.addStyle(".styleParagraph");
style.setAttribute("margin", "112px");
return stylesheet;
}
Export
Export a value from Banana into a text file (.txt)
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.export
// @api = 1.0
// @doctype = *.*
// @description = Tutorial: Export a value from Banana into a text file (*.txt)
// @task = export.file
// @exportfiletype = txt
// @timeout = -1
// It is possible to export data only into external files.
// In this example we take an account balance and we export it into a txt file.
// A dialog window asks the user to insert the file name and choose where to save it.
function exec() {
//Take the balance (opening + debit-credit) for the given account and period
var exportResult = Banana.document.currentBalance('1000', '2015-01-05', '2015-02-07').balance;
//Specifies a value to be returned that will be exported into the txt file
return exportResult;
}
Export Tutorial2 javascript codes
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exportjavascriptcodestutorial2.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes of the tutorial 2 examples
// @task = export.file
// @doctype = *.*
// @docproperties =
// @timeout = -1
// @exportfiletype = js
//Main function
function exec() {
//Check if we are on an opened document
if (!Banana.document) {
return;
}
//Take the table Documents
var documents = Banana.document.table('Documents');
//We check if the table Documents exists, then we can take all the codes
//If the table Documents doesn't exists, then we stop the script execution
if (!documents) {
return;
} else {
//We use this variable to save all the codes
var jsCode = '';
jsCode += '// Copyright [2018] [Banana.ch SA - Lugano Switzerland]' + '\n';
jsCode += '// ' + '\n';
jsCode += '// Licensed under the Apache License, Version 2.0 (the "License");' + '\n';
jsCode += '// you may not use this file except in compliance with the License.' + '\n';
jsCode += '// You may obtain a copy of the License at' + '\n';
jsCode += '// ' + '\n';
jsCode += '// http://www.apache.org/licenses/LICENSE-2.0' + '\n';
jsCode += '// ' + '\n';
jsCode += '// Unless required by applicable law or agreed to in writing, software' + '\n';
jsCode += '// distributed under the License is distributed on an "AS IS" BASIS,' + '\n';
jsCode += '// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.' + '\n';
jsCode += '// See the License for the specific language governing permissions and' + '\n';
jsCode += '// limitations under the License.' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n';
//Function call to get all the tutorial's codes
jsCode = getJavascriptCode(jsCode, documents);
}
return jsCode;
}
//Function that, for each tutorial's example, gets the javascript code and save it into the jsCode variable.
function getJavascriptCode(jsCode, documents) {
//Read row by row the table Documents
var len = documents.rowCount;
for (var i = 0; i < len; i++) {
//Create a variable for the row
var tRow = documents.row(i);
//We get some values
var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
var id = tRow.value("RowId"); //the id of the example
var description = tRow.value("Description"); //the description of the example
var attachments = tRow.value("Attachments"); //the javascript code of the example
//We consider only the rows that contain an id, a description and an attachment
if (id && description && attachments) {
//At the beginning of each code, we insert some information
jsCode += "/** " + '\n';
jsCode += " File: " + fName + '\n';
jsCode += " Id: " + id + '\n';
jsCode += " Description: " + description + '\n';
jsCode += "*/" + '\n';
//we add the code of the attachments
jsCode += attachments;
//At the end of each code, we insert some new lines
jsCode += '\n' + '\n' + '\n' + '\n' + '\n' + '\n' + '\n';
}
}
//Finally we return the variable containing all the codes of all the examples
return jsCode;
}
Export Tutorial2 javascript codes to HTML
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exporthtmljavascriptcodestutorial2.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes of the Tutorial 2 examples in HTML
// @task = export.file
// @doctype = *.*
// @docproperties =
// @timeout = -1
// @exportfiletype = html
//Main function
function exec() {
//Check if we are on an opened document
if (!Banana.document) {
return;
}
//Take the table Documents
var documents = Banana.document.table('Documents');
//We check if the table Documents exists, then we can take all the codes
//If the table Documents doesn't exists, then we stop the script execution
if (!documents) {
return;
} else {
//We use this variable to save all the codes
var jsCode = '';
//Function call to get all the tutorial's codes
jsCode = getJavascriptCode(jsCode, documents);
}
return jsCode;
}
//Function that, for each tutorial's example, gets the javascript code and save it into the jsCode variable.
function getJavascriptCode(jsCode, documents) {
//Read row by row the table Documents
var len = documents.rowCount;
for (var i = 0; i < len; i++) {
//Create a variable for the row
var tRow = documents.row(i);
//We get some values
var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
var section = tRow.value("Section"); // the section column used to define the title type
var id = tRow.value("RowId"); //the id of the example
var description = tRow.value("Description"); //the description of the example
var attachments = tRow.value("Attachments"); //the javascript code of the example
attachments = attachments.replace(/</g,'<'); // replace the < symbol
attachments = attachments.replace(/>/g,'>'); // replace the > symbol
//For tilte h2 we conside only rows with section h2
if (section && section === "h2") {
jsCode += '<h2>' + description + '</h2>\n';
}
//For js codes we consider only rows with section h3, id, description and attachment
else if (section && section === "h3" && id && description && attachments) {
jsCode += '<h3>' + description + '</h3>\n';
jsCode += '<pre><code class="language-javascript">\n';
jsCode += '// Banana Accounting Extension Javascript' + '\n';
jsCode += attachments;
jsCode += '\n';
jsCode += '</code></pre>\n';
}
}
//Finally we return the variable containing all the codes of all the examples
return jsCode;
}
Embedded Extensions examples Tutorial 3 Import CSV
On this page we show some examples of transactions import extensions.
Each example consists of:
- The complete JavaScript code of the import extension.
- The .CSV files in different formats with the transactions data to be imported.
Some examples contains anonymized data, the description text has no meanings. - The output in .TSV format (Tabulator Separated Values) of the import extension, which contains the transformed csv data in the “transactions.simple” format used to import the transactions in Banana Accounting.
Import transactions example 1
Migros Bank (Switzerland) import transactions with multiple csv formats
// Banana Accounting Extension Javascript
// @id = ch.banana.switzerland.import.migrosbank.tutorial
// @api = 1.0
// @pubdate = 2023-10-10
// @publisher = Banana.ch SA
// @description = Migros Bank - Import account statement .csv (Banana+ Advanced)
// @description.en = Migros Bank - Import account statement .csv (Banana+ Advanced)
// @description.de = Migros Bank - Bewegungen importieren .csv (Banana+ Advanced)
// @description.fr = Migros Bank - Importer mouvements .csv (Banana+ Advanced)
// @description.it = Migros Bank - Importa movimenti .csv (Banana+ Advanced)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @timeout = -1
// @inputencoding = latin1
// @inputfilefilter = Text files (*.txt *.csv);;All files (*.*)
// @inputfilefilter.de = Text (*.txt *.csv);;Alle Dateien (*.*)
// @inputfilefilter.fr = Texte (*.txt *.csv);;Tous (*.*)
// @inputfilefilter.it = Testo (*.txt *.csv);;Tutti i files (*.*)
/**
* Parse the data and return the data to be imported as a tab separated file.
*/
function exec(string, isTest) {
var importUtilities = new ImportUtilities(Banana.document);
if (isTest !== true && !importUtilities.verifyBananaAdvancedVersion())
return "";
var fieldSeparator = findSeparator(string);
var transactions = Banana.Converter.csvToArray(string, fieldSeparator, '"');
// Format 1
var format1 = new MBFormat1();
if (format1.match(transactions)) {
transactions = format1.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
importUtilities.getUnknownFormatError();
return "";
}
/**
* Migros Bank Format 1 A):
* Kontoauszug bis: 04.09.2023 ;;;
* ;;;
* Kontonummer: 543.278.22;;;
* Bezeichnung: Privat;;;
* Saldo: CHF 38547.7;;;
* ;;;
* Ramer E. & Ramer-Zahner D.;;;
* In den Steinreben 6C;;;
* 4153 Reinach BL;;;
* ;;;
* ;;;
* Datum;Buchungstext;Betrag;Valuta
* 04.09.23;Zahlungseingang;1838.00;04.09.23
* 04.09.23;Zahlungs;-204.45;04.09.23
*
* Migros Bank Format 1 B), valutare in futuro se fare un formato differente per conti privati,
* per ora cambia solo la data e le intestazioni:
* Moristra rerva eo:;2023-09-13
* Moristra rerva lant:;2023-10-10
* ;
* Sciercipsidea:;Rerva haragine
* ;
* ;
* ;
* Data;Testo di registrazione;Importo;Valuta
* 15.09.2023;Frunt stantuisu me quaesecerinum XXX/UT/PUS, Dis Frangunattis 47h, 1782 Raraequone;-105.45;15.09.2023
* 15.09.2023;DIDUNT Humquit-Costripe EO, Dis Volluvis 1, 7888 Prescrente;-230.95;15.09.2023
* 19.09.2023;CLAVIANTO AUFERVA EO, DIS MINENT 8, 6686 COLUMEA;-150.80;19.09.2023
*/
var MBFormat1 = class MBFormat1 {
constructor() {
this.colDate = 0;
this.colDescr = 1;
this.colAmount = 2;
this.colDateValuta = 3;
this.colCount = 4;
this.decimalSeparator = ".";
this.dateFormat = "dd.mm.yy";
}
/** Return true if the transactions match this format */
match(transactions) {
if (transactions.length === 0)
return false;
for (var i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
/* array should have all columns */
if (transaction.length == this.colCount)
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate] &&
transaction[this.colDate].match(/^(0[1-9]|[12][0-9]|3[01])[-.](0[1-9]|1[0-2])[-.]\d{2}$/)) {
formatMatched = true;
} else if (formatMatched && transaction[this.colDate] &&
transaction[this.colDate].match(/^(0[1-9]|[12][0-9]|3[01])[-.](0[1-9]|1[0-2])[-.]\d{4}$/)) {
this.dateFormat = "dd.mm.yyyy";
formatMatched = true;
}
else {
formatMatched = false;
}
if (formatMatched && transaction[this.colDateValuta] &&
transaction[this.colDateValuta].match(/\b\d{2}[.-]\d{2}[.-](?:\d{2}|\d{4})\b/g)) {
formatMatched = true;
}
else {
formatMatched = false;
}
if (formatMatched) {
return true;
}
}
return false;
}
/** Convert the transaction to the format to be imported */
convert(rows) {
var transactionsToImport = [];
for (var i = 0; i < rows.length; i++) {
let transaction = rows[i];
if (transaction.length == this.colCount &&
transaction[this.colDate].match(/^(0[1-9]|[12][0-9]|3[01])[-.](0[1-9]|1[0-2])[-.](\d{4}|\d{2})$/)) {
transactionsToImport.push(this.mapTransaction(rows[i]));
}
}
// Sort rows by date
if (this.dateFormat !== "dd.mm.yyyy") // transactions in the format B are already provided in the correct order.
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
/** Return the transaction converted in the import format */
mapTransaction(transaction) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(transaction[this.colDate], this.dateFormat));
mappedLine.push(""); // Doc is empty for now
mappedLine.push(transaction[this.colDescr]);
var amount = transaction[this.colAmount];
if (amount.length > 0) {
//check decimal separator, if is comma, we replace it.
if (amount.indexOf(",") >= 0)
amount = amount.replace(',', '.');
if (amount[0] === "-") {
amount = amount.replace(/-/g, ''); //remove minus sign
mappedLine.push("");
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, "."));
} else {
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, "."));
mappedLine.push("");
}
}
return mappedLine;
}
}
/**
* The function findSeparator is used to find the field separator.
*/
function findSeparator(string) {
var commaCount = 0;
var semicolonCount = 0;
var tabCount = 0;
for (var i = 0; i < 1000 && i < string.length; i++) {
var c = string[i];
if (c === ',')
commaCount++;
else if (c === ';')
semicolonCount++;
else if (c === '\t')
tabCount++;
}
if (tabCount > commaCount && tabCount > semicolonCount) {
return '\t';
}
else if (semicolonCount > commaCount) {
return ';';
}
return ',';
}
/*
* class ImportUtilities
* Contains methods that can be shared by extensions for importing bank data
*/
var ImportUtilities = class ImportUtilities {
constructor(banDocument) {
this.banDocument = banDocument;
if (this.banDocument === undefined)
this.banDocument = Banana.document;
}
//The purpose of this function is to convert all the data into a format supported by Banana
convertToBananaFormat(intermediaryData) {
var columnTitles = [];
//Create titles only for fields not starting with "_"
for (var name in intermediaryData[0]) {
if (name.substring(0, 1) !== "_") {
columnTitles.push(name);
}
}
//Function call Banana.Converter.objectArrayToCsv() to create a CSV with new data just converted
var convertedCsv = Banana.Converter.objectArrayToCsv(columnTitles, intermediaryData, "\t");
return convertedCsv;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertCsvToIntermediaryData(inData, convertionParam) {
var form = [];
var intermediaryData = [];
//Add the header if present
if (convertionParam.header) {
inData = convertionParam.header + inData;
}
//Read the CSV file and create an array with the data
var csvFile = Banana.Converter.csvToArray(inData, convertionParam.separator, convertionParam.textDelim);
//Variables used to save the columns titles and the rows values
var columns = this.getHeaderData(csvFile, convertionParam.headerLineStart); //array
var rows = this.getRowData(csvFile, convertionParam.dataLineStart); //array of array
//Load the form with data taken from the array. Create objects
this.loadForm(form, columns, rows);
//Create the new CSV file with converted data
var convertedRow;
//For each row of the form, we call the rowConverter() function and we save the converted data
for (var i = 0; i < form.length; i++) {
convertedRow = convertionParam.rowConverter(form[i]);
intermediaryData.push(convertedRow);
}
//Return the converted CSV data into the Banana document table
return intermediaryData;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertHtmlToIntermediaryData(inData, convertionParam) {
var form = [];
var intermediaryData = [];
//Read the HTML file and create an array with the data
var htmlFile = [];
var htmlRows = inData.match(/<tr[^>]*>.*?<\/tr>/gi);
for (var rowNr = 0; rowNr < htmlRows.length; rowNr++) {
var htmlRow = [];
var htmlFields = htmlRows[rowNr].match(/<t(h|d)[^>]*>.*?<\/t(h|d)>/gi);
for (var fieldNr = 0; fieldNr < htmlFields.length; fieldNr++) {
var htmlFieldRe = />(.*)</g.exec(htmlFields[fieldNr]);
htmlRow.push(htmlFieldRe.length > 1 ? htmlFieldRe[1] : "");
}
htmlFile.push(htmlRow);
}
//Variables used to save the columns titles and the rows values
var columns = this.getHeaderData(htmlFile, convertionParam.headerLineStart); //array
var rows = this.getRowData(htmlFile, convertionParam.dataLineStart); //array of array
//Convert header names
for (var i = 0; i < columns.length; i++) {
var convertedHeader = columns[i];
convertedHeader = convertedHeader.toLowerCase();
convertedHeader = convertedHeader.replace(" ", "_");
var indexOfHeader = columns.indexOf(convertedHeader);
if (indexOfHeader >= 0 && indexOfHeader < i) { // Header alreay exist
//Avoid headers with same name adding an incremental index
var newIndex = 2;
while (columns.indexOf(convertedHeader + newIndex.toString()) !== -1 && newIndex < 99)
newIndex++;
convertedHeader = convertedHeader + newIndex.toString()
}
columns[i] = convertedHeader;
}
// Banana.console.log(JSON.stringify(columns, null, " "));
//Load the form with data taken from the array. Create objects
this.loadForm(form, columns, rows);
//Create the new CSV file with converted data
var convertedRow;
//For each row of the form, we call the rowConverter() function and we save the converted data
for (var i = 0; i < form.length; i++) {
convertedRow = convertionParam.rowConverter(form[i]);
intermediaryData.push(convertedRow);
}
//Return the converted CSV data into the Banana document table
return intermediaryData;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertToIntermediaryData(inData, convertionParam) {
if (convertionParam.format === "html") {
return this.convertHtmlToIntermediaryData(inData, convertionParam);
} else {
return this.convertCsvToIntermediaryData(inData, convertionParam);
}
}
//The purpose of this function is to return all the titles of the columns
getHeaderData(csvFile, startLineNumber) {
if (!startLineNumber) {
startLineNumber = 0;
}
var headerData = csvFile[startLineNumber];
for (var i = 0; i < headerData.length; i++) {
headerData[i] = headerData[i].trim();
if (!headerData[i]) {
headerData[i] = i;
}
//Avoid duplicate headers
var headerPos = headerData.indexOf(headerData[i]);
if (headerPos >= 0 && headerPos < i) { // Header already exist
var postfixIndex = 2;
while (headerData.indexOf(headerData[i] + postfixIndex.toString()) !== -1 && postfixIndex <= 99)
postfixIndex++; // Append an incremental index
headerData[i] = headerData[i] + postfixIndex.toString()
}
}
return headerData;
}
getLang() {
var lang = 'en';
if (this.banDocument)
lang = this.banDocument.locale;
else if (Banana.application.locale)
lang = Banana.application.locale;
if (lang.length > 2)
lang = lang.substr(0, 2);
return lang;
}
//The purpose of this function is to return all the data of the rows
getRowData(csvFile, startLineNumber) {
if (!startLineNumber) {
startLineNumber = 1;
}
var rowData = [];
for (var i = startLineNumber; i < csvFile.length; i++) {
rowData.push(csvFile[i]);
}
return rowData;
}
//The purpose of this function is to load all the data (titles of the columns and rows) and create a list of objects.
//Each object represents a row of the csv file
loadForm(form, columns, rows) {
for (var j = 0; j < rows.length; j++) {
var obj = {};
for (var i = 0; i < columns.length; i++) {
obj[columns[i]] = rows[j][i];
}
form.push(obj);
}
}
// The purpose of this function is to sort the data
sortData(intermediaryData, convertionParam) {
if (convertionParam.sortColums && convertionParam.sortColums.length) {
intermediaryData.sort(
function (row1, row2) {
for (var i = 0; i < convertionParam.sortColums.length; i++) {
var columnName = convertionParam.sortColums[i];
if (row1[columnName] > row2[columnName])
return 1;
else if (row1[columnName] < row2[columnName])
return -1;
}
return 0;
});
if (convertionParam.sortDescending)
intermediaryData.reverse();
}
return intermediaryData;
}
verifyBananaPlusVersion() {
if (!this.banDocument)
return false;
var BAN_VERSION_MIN = "10.0";
// supported Version
if (Banana.compareVersion && Banana.compareVersion(Banana.application.version, BAN_VERSION_MIN) >= 0) {
return true;
}
// not supported version
var lang = this.getLang();
var msg = "This extension requires Banana Accounting+";
this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
return false;
}
//Check if the version of Banana Accounting is compatible with this class
verifyBananaAdvancedVersion() {
if (!this.banDocument)
return false;
if (!Banana.application.license || Banana.application.license.licenseType !== "advanced") {
var lang = this.getLang();
var msg = "This extension requires Banana Accounting+ Advanced";
this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
return false;
}
return true;
}
getErrorMessage(errorId, lang) {
if (!lang)
lang = 'en';
switch (errorId) {
case "ID_ERR_FORMAT_UNKNOWN":
if (lang == 'it')
return "Formato del file *.csv non riconosciuto";
else if (lang == 'fr')
return "Format de fichier *.csv non reconnu";
else if (lang == 'de')
return "Unerkanntes *.csv-Dateiformat";
else
return "Unrecognised *.csv file format";
}
return '';
}
getLang() {
var lang = 'en';
if (Banana.application.locale)
lang = Banana.application.locale;
if (lang.length > 2)
lang = lang.substring(0, 2);
return lang;
}
getUnknownFormatError(){
let errId = "ID_ERR_FORMAT_UNKNOWN"; //error
let lang = this.getLang();
let msg = this.getErrorMessage(errId, lang);
Banana.document.addMessage(msg, errId);
}
}
Input data - csv_migrosbank_example_format1_20230906.csv
Input text passed to the exec() function.
Alevenequis xxx: 78.71.5440 ;;;
;;;
Ausplecutum: 457.211.73;;;
Agnuluviant: Neriam;;;
Oprit: BOX 54060.3;;;
;;;
Agile G. & Agile-Tumere L.;;;
Ut lum Ascungilis 1Q;;;
4153 Reinach BL;;;
;;;
;;;
Datum;Buchungstext;Betrag;Valuta
04.09.23;Partuducartudie;1838.00;04.09.23
Output data - csv_migrosbank_example_format1_20230906.tsv
Output text returned by the exec() function.
Date\tDoc\tDescription\tIncome\tExpenses
2023-09-04\t\tPartuducartudie\t1838.00\t
Input data - csv_migrosbank_example_format1_20230908.csv
Input text passed to the exec() function.
Officulo patem crem: 42.35.6581 ;;;
;;;
Office eo patem: 133.273.44;;;
Tabilliquaest: Patem plucinto;;;
Widum: SUB 30221.84;;;
;;;
Amniuntum-Reles Addunnes;;;
Flone H. Nolvo 13 O;;;
10090 Villarbasse TO;;;
;;;
;;;
Data;Testo di registrazione;Importo;Valuta
06.09.23;Anispus rentemple;975.00;06.09.23
04.09.23;Rentemple;-204.45;04.09.23
01.09.23;Anispus rentemple;2300.00;01.09.23
01.09.23;Anispus rentemple;2400.00;01.09.23
31.08.23;Anispus rentemple;2610.00;31.08.23
30.08.23;Rentemple;-166.00;30.08.23
29.08.23;Rentemple;-161.55;29.08.23
29.08.23;Anispus rentemple;140.00;29.08.23
28.08.23;Anispus rentemple;2640.00;28.08.23
18.08.23;Anispus rentemple;2190.00;18.08.23
10.08.23;Vicum eo eviore patem usa 84.60.6581 vi 80.60.6581;-3.00;10.08.23
10.08.23;Raepribulle multubiti morpus usa 84.60.6581 vi 80.60.6581 triente 67010185;-5.00;10.08.23
08.08.23;Rentemple;-216.50;08.08.23
07.08.23;Anispus rentemple;975.00;07.08.23
04.08.23;Anispus rentemple;2500.00;04.08.23
03.08.23;Anispus rentemple;2300.00;03.08.23
31.07.23;Anispus rentemple;2610.00;31.07.23
31.07.23;Anispus rentemple;2540.00;31.07.23
28.07.23;Anispus rentemple;140.00;28.07.23
28.07.23;Anispus rentemple;2640.00;28.07.23
25.07.23;Rentemple;-371.75;25.07.23
24.07.23;Rentemple;-708.25;24.07.23
20.07.23;Anispus rentemple;2190.00;20.07.23
18.07.23;Rentemple;-51.76;18.07.23
Output data - csv_migrosbank_example_format1_20230908.tsv
Output text returned by the exec() function.
Date\tDoc\tDescription\tIncome\tExpenses
2023-07-18\t\tRentemple\t\t51.76
2023-07-20\t\tAnispus rentemple\t2190.00\t
2023-07-24\t\tRentemple\t\t708.25
2023-07-25\t\tRentemple\t\t371.75
2023-07-28\t\tAnispus rentemple\t2640.00\t
2023-07-28\t\tAnispus rentemple\t140.00\t
2023-07-31\t\tAnispus rentemple\t2540.00\t
2023-07-31\t\tAnispus rentemple\t2610.00\t
2023-08-03\t\tAnispus rentemple\t2300.00\t
2023-08-04\t\tAnispus rentemple\t2500.00\t
2023-08-07\t\tAnispus rentemple\t975.00\t
2023-08-08\t\tRentemple\t\t216.50
2023-08-10\t\tRaepribulle multubiti morpus usa 84.60.6581 vi 80.60.6581 triente 67010185\t\t5.00
2023-08-10\t\tVicum eo eviore patem usa 84.60.6581 vi 80.60.6581\t\t3.00
2023-08-18\t\tAnispus rentemple\t2190.00\t
2023-08-28\t\tAnispus rentemple\t2640.00\t
2023-08-29\t\tAnispus rentemple\t140.00\t
2023-08-29\t\tRentemple\t\t161.55
2023-08-30\t\tRentemple\t\t166.00
2023-08-31\t\tAnispus rentemple\t2610.00\t
2023-09-01\t\tAnispus rentemple\t2400.00\t
2023-09-01\t\tAnispus rentemple\t2300.00\t
2023-09-04\t\tRentemple\t\t204.45
2023-09-06\t\tAnispus rentemple\t975.00\t
Input data - csv_migrosbank_example_format1_20231010.csv
Input text passed to the exec() function.
Moristra rerva eo:;2023-09-13
Moristra rerva lant:;2023-10-10
;
Sciercipsidea:;Rerva haragine
;
;
;
Data;Testo di registrazione;Importo;Valuta
15.09.2023;Frunt stantuisu me quaesecerinum XXX/UT/PUS, Dis Frangunattis 47h, 1782 Raraequone;-105.45;15.09.2023
15.09.2023;DIDUNT Humquit-Costripe EO, Dis Volluvis 1, 7888 Prescrente;-230.95;15.09.2023
19.09.2023;CLAVIANTO AUFERVA EO, DIS MINENT 8, 6686 COLUMEA;-150.80;19.09.2023
20.09.2023;Quibula Mindum Eaniertus Habit, Dis cat Brende 28, 1880 Reline, QL3478184100087423875;2190.00;20.09.2023
22.09.2023;CONDUONE PAVIONENT, DIS CAT BRENDE 48, 1880 RELINE, AG04803336078414G776D;6534.85;22.09.2023
28.09.2023;CURICA MITATE, DIS FERBO MEN 87 UT 1880 RELINE, EC143102680158501757U;2640.00;28.09.2023
29.09.2023;Perrede Deceripam, Insunt Pulus 60, 8533 Inatu Serneripe, HK3780577203472652012;140.00;29.09.2023
29.09.2023;Eorore Quam, Dis cat Brende 28, UT-1880 Reline, NW8367667075683018488;2610.00;29.09.2023
29.09.2023;Ludicita Lumilla, Dis Ferbo Men 87, 1880 Reline, JX6823625561033213063;2400.00;29.09.2023
03.10.2023;Biberi Tere, Dis cat Brende 28, 1880 Reline, YD8147137112546525622;2300.00;03.10.2023
06.10.2023;Aufere Adecro in commensectum, Horint Coluva-Exerent 56, 8367 Ireèex, WG0071635058848171552;975.00;06.10.2023
09.10.2023;Nanno Spirit Ripere, Dis cat Brende 28, 1880 Reline, XA1816106073487284374;2500.00;09.10.2023
10.10.2023;Mendisistra sustudabo difico nos 44.60.3252 at 18.60.3252 sideano 23418626;-5.00;10.10.2023
10.10.2023;Humea me punium rerva nos 44.60.3252 at 18.60.3252;-3.00;10.10.2023
Output data - csv_migrosbank_example_format1_20231010.tsv
Output text returned by the exec() function.
Date\tDoc\tDescription\tIncome\tExpenses
2023-09-15\t\tFrunt stantuisu me quaesecerinum XXX/UT/PUS, Dis Frangunattis 47h, 1782 Raraequone\t\t105.45
2023-09-15\t\tDIDUNT Humquit-Costripe EO, Dis Volluvis 1, 7888 Prescrente\t\t230.95
2023-09-19\t\tCLAVIANTO AUFERVA EO, DIS MINENT 8, 6686 COLUMEA\t\t150.80
2023-09-20\t\tQuibula Mindum Eaniertus Habit, Dis cat Brende 28, 1880 Reline, QL3478184100087423875\t2190.00\t
2023-09-22\t\tCONDUONE PAVIONENT, DIS CAT BRENDE 48, 1880 RELINE, AG04803336078414G776D\t6534.85\t
2023-09-28\t\tCURICA MITATE, DIS FERBO MEN 87 UT 1880 RELINE, EC143102680158501757U\t2640.00\t
2023-09-29\t\tPerrede Deceripam, Insunt Pulus 60, 8533 Inatu Serneripe, HK3780577203472652012\t140.00\t
2023-09-29\t\tEorore Quam, Dis cat Brende 28, UT-1880 Reline, NW8367667075683018488\t2610.00\t
2023-09-29\t\tLudicita Lumilla, Dis Ferbo Men 87, 1880 Reline, JX6823625561033213063\t2400.00\t
2023-10-03\t\tBiberi Tere, Dis cat Brende 28, 1880 Reline, YD8147137112546525622\t2300.00\t
2023-10-06\t\tAufere Adecro in commensectum, Horint Coluva-Exerent 56, 8367 Ireèex, WG0071635058848171552\t975.00\t
2023-10-09\t\tNanno Spirit Ripere, Dis cat Brende 28, 1880 Reline, XA1816106073487284374\t2500.00\t
2023-10-10\t\tMendisistra sustudabo difico nos 44.60.3252 at 18.60.3252 sideano 23418626\t\t5.00
2023-10-10\t\tHumea me punium rerva nos 44.60.3252 at 18.60.3252\t\t3.00
Import transactions example 2
Postfinance (Switzerland) import transactions with multiple csv formats
// Banana Accounting Extension Javascript
// @id = ch.banana.switzerland.import.postfinance.tutorial
// @api = 1.0
// @pubdate = 2023-09-29
// @publisher = Banana.ch SA
// @description = Postfinance - Import account statement .csv (Banana+ Advanced)
// @description.de = Postfinance - Bewegungen importieren .csv (Banana+ Advanced)
// @description.en = Postfinance - Import account statement .csv (Banana+ Advanced)
// @description.fr = Postfinance - Importer mouvements .csv (Banana+ Advanced)
// @description.it = Postfinance - Importa movimenti .csv (Banana+ Advanced)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @inputfilefilter = Text files (*.txt *.csv);;All files (*.*)
// @inputfilefilter.de = Text (*.txt *.csv);;Alle Dateien (*.*)
// @inputfilefilter.fr = Texte (*.txt *.csv);;Tous (*.*)
// @inputfilefilter.it = Testo (*.txt *.csv);;Tutti i files (*.*)
/**
* Parse the data and return the data to be imported as a tab separated file.
*/
function exec(inData, isTest) {
if (!inData) return "";
var importUtilities = new ImportUtilities(Banana.document);
if (isTest !== true && !importUtilities.verifyBananaAdvancedVersion())
return "";
if (inData.indexOf("<html") >= 0) {
var formatHtml1 = new PFHtmlFormat1();
var rows = formatHtml1.convert(inData);
var csv = Banana.Converter.objectArrayToCsv(
["Date", "DateValue", "Description", "Income", "Expenses"],
rows);
return csv;
} else {
var fieldSeparator = findSeparator(inData);
let inDataCleared = clearText(inData);
var transactions = Banana.Converter.csvToArray(inDataCleared, fieldSeparator);
// Format SBU 1
var formatSBU1 = new PFCSVFormatSBU1();
if (formatSBU1.match(transactions)) {
transactions = formatSBU1.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Credit Card format 1
var format1_CreditCard = new PFCSVFormat1_CreditCard();
if (format1_CreditCard.match(transactions)) {
transactions = format1_CreditCard.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 1
var format1 = new PFCSVFormat1();
if (format1.match(transactions)) {
transactions = format1.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 2
var format2 = new PFCSVFormat2();
if (format2.match(transactions)) {
transactions = format2.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 3
var format3 = new PFCSVFormat3();
if (format3.match(transactions)) {
transactions = format3.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 4
var format4 = new PFCSVFormat4();
if (format4.match(transactions)) {
transactions = format4.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 5
var format5 = new PFCSVFormat5();
if (format5.match(transactions)) {
transactions = format5.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 6, works with translated column headers.
var format6 = new PFCSVFormat6();
// getFormattedData () works with specifics headers and to translate them.
let transactionsData = format6.getFormattedData(transactions, importUtilities);
if (format6.match(transactionsData)) {
let convTransactions = format6.convert(transactionsData);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(convTransactions);
}
}
importUtilities.getUnknownFormatError();
return "";
}
/**
* Pulisce il testo dai doppi a capo, con la versione 6 del formato csv, per qualche motivo quando il file .csv
* viene aperto su windows vengono aggiunti degli a capo aggiuntivi (uno o più).
* Ogni riga dovrebbe contenere un "\r\n" non di più, anche quelle vuote.
*/
function clearText(text) {
// Sostituisce tutte le occorrenze multiple di "\r\r\n" con un singolo "\r\n"
return text.replace(/\r\r\n/g, "\r\n");
}
/**
* PFCSV Format 6, since february 2024.
* Date de début:;26.02.2022;;;;;
* Date de fin:;26.02.2024;;;;;
* Catégorie:;Tous;;;;;
* Compte:;CH00000000000000000000;;;;;
* Monnaie:;CHF;;;;;
* ;;;;;;
* Date;Type de transaction;Texte de notification;Crédit en CHF;Débit en CHF;Label;Catégorie
* ;;;;;;
* 26.02.2024;Enregistrement comptable;Descr;;-100;;Dépenses autres
* 26.02.2024;Enregistrement comptable;Descr;;-15;;Dépenses autres
* ;;;;;;
* Disclaimer:;;;;;;
* Le contenu du document a été généré à partir des paramètres de filtrage des clientes et des clients. PostFinance n’est pas responsable du contenu et de son exhaustivité.;;;;;;
*/
function PFCSVFormat6() {
this.getFormattedData = function (transactions, importUtilities) {
let headerLineStart = this.getHeaderLineStart(transactions);
let dataLineStart = headerLineStart == 6 ? 8 : 7;
// We do a copy as the getHeaderData modifies the content and we need to keep the original version clean.
var transactionsCopy = transactions.map(function (arr) {
return arr.slice();
});
if (transactionsCopy.length < dataLineStart)
return [];
let columns = importUtilities.getHeaderData(transactionsCopy, headerLineStart); //array
let rows = importUtilities.getRowData(transactionsCopy, dataLineStart); //array of array
let form = [];
/** We convert the original headers into a custom format to be able to work with the same
* format regardless of original's headers language or the position of the header column.
* We need to translate all the .csv fields as the loadForm() method expects the header and
* the rows to have the same length.
* */
let convertedColumns = [];
convertedColumns = this.convertHeaderDe(columns, convertedColumns);
if (convertedColumns.length > 0) {
importUtilities.loadForm(form, convertedColumns, rows);
return form;
}
// Convert headers from italian.
convertedColumns = this.convertHeaderIt(columns, convertedColumns);
if (convertedColumns.length > 0) {
importUtilities.loadForm(form, convertedColumns, rows);
return form;
}
// Convert headers from french.
convertedColumns = this.convertHeaderFr(columns, convertedColumns);
if (convertedColumns.length > 0) {
importUtilities.loadForm(form, convertedColumns, rows);
return form;
}
// Convert headers from english.
convertedColumns = this.convertHeaderEn(columns, convertedColumns);
if (convertedColumns.length > 0) {
importUtilities.loadForm(form, convertedColumns, rows);
return form;
}
return [];
}
/**
* With this format, if user does not explicitly set the End Date of the movements
* when is exporting, then the header and data start at a different row, then we have
* to figure out if the end date is present or not.
*/
this.getHeaderLineStart = function (transactions) {
let endDate = transactions[1][1];
if (endDate.match(/^\d{2}.\d{2}.\d{4}$/))
return 6; // the Header is on row 6.
else
return 5; // the Header is on row 5.
}
this.convertHeaderDe = function (columns) {
let convertedColumns = [];
for (var i = 0; i < columns.length; i++) {
switch (columns[i]) {
case "Datum":
convertedColumns[i] = "Date";
break;
case "Bewegungstyp":
convertedColumns[i] = "Type";
break;
case "Avisierungstext":
convertedColumns[i] = "Description";
break;
case "Gutschrift in CHF":
case "Gutschrift in EUR":
case "Gutschrift in USD":
convertedColumns[i] = "Income";
break;
case "Lastschrift in CHF":
case "Lastschrift in EUR":
case "Lastschrift in USD":
convertedColumns[i] = "Expenses";
break;
case "Label":
convertedColumns[i] = "Label";
break;
case "Kategorie":
convertedColumns[i] = "Category";
break;
default:
break;
}
}
if (convertedColumns.indexOf("Date") < 0
|| convertedColumns.indexOf("Description") < 0
|| convertedColumns.indexOf("Income") < 0
|| convertedColumns.indexOf("Expenses") < 0) {
return [];
}
return convertedColumns;
}
this.convertHeaderIt = function (columns, convertedColumns) {
for (var i = 0; i < columns.length; i++) {
switch (columns[i]) {
case "Data":
convertedColumns[i] = "Date";
break;
case "Tipo di movimento":
convertedColumns[i] = "Type";
break;
case "Testo di avviso":
convertedColumns[i] = "Description";
break;
case "Accredito in CHF":
case "Accredito in EUR":
case "Accredito in USD":
convertedColumns[i] = "Income";
break;
case "Addebito in CHF":
case "Addebito in EUR":
case "Addebito in USD":
convertedColumns[i] = "Expenses";
break;
case "Tag":
convertedColumns[i] = "Label";
break;
case "Categoria":
convertedColumns[i] = "Category";
break;
default:
break;
}
}
if (convertedColumns.indexOf("Date") < 0
|| convertedColumns.indexOf("Description") < 0
|| convertedColumns.indexOf("Income") < 0
|| convertedColumns.indexOf("Expenses") < 0) {
return [];
}
return convertedColumns;
}
this.convertHeaderFr = function (columns, convertedColumns) {
for (var i = 0; i < columns.length; i++) {
switch (columns[i]) {
case "Date":
convertedColumns[i] = "Date";
break;
case "Type de transaction":
convertedColumns[i] = "Type";
break;
case "Texte de notification":
convertedColumns[i] = "Description";
break;
case "Crédit en CHF":
case "Crédit en EUR":
case "Crédit en USD":
convertedColumns[i] = "Income";
break;
case "Débit en CHF":
case "Débit en EUR":
case "Débit en USD":
convertedColumns[i] = "Expenses";
break;
case "Label":
convertedColumns[i] = "Label";
break;
case "Catégorie":
convertedColumns[i] = "Category";
break;
default:
break;
}
}
if (convertedColumns.indexOf("Date") < 0
|| convertedColumns.indexOf("Description") < 0
|| convertedColumns.indexOf("Income") < 0
|| convertedColumns.indexOf("Expenses") < 0) {
return [];
}
return convertedColumns;
}
this.convertHeaderEn = function (columns, convertedColumns) {
for (var i = 0; i < columns.length; i++) {
switch (columns[i]) {
case "Date":
convertedColumns[i] = "Date";
break;
case "Type of transaction":
convertedColumns[i] = "Type";
break;
case "Notification text":
convertedColumns[i] = "Description";
break;
case "Credit in CHF":
case "Credit in EUR":
case "Credit in USD":
convertedColumns[i] = "Income";
break;
case "Debit in CHF":
case "Debit in EUR":
case "Debit in USD":
convertedColumns[i] = "Expenses";
break;
case "Tag":
convertedColumns[i] = "Label";
break;
case "Category":
convertedColumns[i] = "Category";
break;
default:
break;
}
}
if (convertedColumns.indexOf("Date") < 0
|| convertedColumns.indexOf("Description") < 0
|| convertedColumns.indexOf("Income") < 0
|| convertedColumns.indexOf("Expenses") < 0) {
return [];
}
return convertedColumns;
}
/** Return true if the transactions match this format */
this.match = function (transactionsData) {
if (transactionsData.length === 0)
return false;
for (var i = 0; i < transactionsData.length; i++) {
var transaction = transactionsData[i];
var formatMatched = true;
if (formatMatched && transaction["Date"] && transaction["Date"].length >= 10 &&
transaction["Date"].match(/^\d{2}.\d{2}.\d{4}$/))
formatMatched = true;
else
formatMatched = false;
if (formatMatched)
return true;
}
return false;
}
this.convert = function (transactionsData) {
var transactionsToImport = [];
for (var i = 0; i < transactionsData.length; i++) {
if (transactionsData[i]["Date"] && transactionsData[i]["Date"].length >= 10 &&
transactionsData[i]["Date"].match(/^\d{2}.\d{2}.\d{4}$/)) {
transactionsToImport.push(this.mapTransaction(transactionsData[i]));
}
}
// Sort rows by date
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "DateValue", "Doc", "ExternalReference", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
this.mapTransaction = function (transaction) {
let mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(transaction["Date"], "dd.mm.yyyy"));
mappedLine.push(Banana.Converter.toInternalDateFormat("", "dd.mm.yyyy"));
mappedLine.push("");
mappedLine.push("");
let trDescription = transaction["Description"] + ", " + transaction["Type"];
mappedLine.push(trDescription);
mappedLine.push(Banana.Converter.toInternalNumberFormat(transaction["Income"], '.'));
let expAmount = transaction["Expenses"].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(expAmount, '.'));
return mappedLine;
}
}
/**
* Credit Card format 1
* Kartenkonto:;0000 1234 5467 7654
* Karte:;XXXX XXXX XXXX 1111 PostFinance Visa Business Card
* Datum;Buchungsdetails;Gutschrift in CHF;Lastschrift in CHF
* 2023-08-24;"Tankstelle Marche Brugg BE";;-94.70
* 2023-08-21;"Tankstelle Marche Brugg BE";;-114.05
* 2023-08-10;"6131 STORNO JAHRESPREIS";80.00;
**/
function PFCSVFormat1_CreditCard() {
this.colDate = 0;
this.colDescr = 1;
this.colCredit = 2;
this.colDebit = 3;
this.dateFormat = 'dd-mm-yyyy';
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === this.colDebit + 1)
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\-)[0-9]{2}(\-)[0-9]{4}/g)) {
formatMatched = true;
} else if (formatMatched && transaction[this.colDate].match(/[0-9]{4}(\-)[0-9]{2}(\-)[0-9]{2}/g)) {
formatMatched = true;
this.dateFormat = 'yyyy-mm-dd';
} else {
formatMatched = false;
}
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < (this.colAmount + 1))
continue;
if (transaction[this.colDate] && transaction[this.colDate].match(/[0-9]{2,4}(\-)[0-9]{2}(\-)[0-9]{2,4}/g)
&& transaction[this.colDate].length == 10)
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date (just invert)
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
mappedLine.push(""); // Doc is empty for now
var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
var amount = element[this.colCredit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
amount = element[this.colDebit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
return mappedLine;
}
}
/**
* PFCSV Format 5
* Example: pfcsv.#20230901
**/
function PFCSVFormat5() {
this.colDate = 0;
this.colMovType = 1;
this.colDescr = 2;
this.colCredit = 3;
this.colDebit = 4;
this.dateFormat = 'dd.mm.yyyy';
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === this.colDebit + 1)
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.)[0-9]{2}(\.)[0-9]{2}/g)) {
formatMatched = true;
} else {
formatMatched = false;
}
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < (this.colAmount + 1))
continue;
if (transaction[this.colDate] && transaction[this.colDate].match(/[0-9]{2}(\.)[0-9]{2}(\.)[0-9]{4}/g) && transaction[this.colDate].length == 10)
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date (just invert)
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
mappedLine.push(""); // Doc is empty for now
var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colCredit], '.'));
amountDebit = element[this.colDebit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amountDebit, '.'));
return mappedLine;
}
}
/**
* PFCSV Format 4
* Example: pfcsv.#20230509
* Fœnum porto natio:;0000 8003 3386 9363
* Natio:;LIAM LIAM LIAM 5526 PecuLeverba Aturaequat Cocet Voluna
* Tuundit nostinsan:;06.04.2022 - 05.05.2022
* Data;Denominazione;Accredito in CHF;Addebito in CHF;Importo in CHF
* 2022-05-04;"ARTION *PRATIUNDICO 52163467544 XXX";;52.00;
* 2022-05-04;"1.7% SUPPL. CHF ALL'ESTERO";;0.88;
* 2022-05-04;"ARTION *EXPECT CUNT 1324126664 NOS";;21.93;
* 2022-05-03;"ARTION *EXPECT CUNT 1324126664 NOS";;11.11;
* 2022-05-03;"ARTION *MENTIO SET 1324126664 STO";;15.00;
* 2022-05-03;"1.7% SUPPL. CHF ALL'ESTERO";;0.26;
* 2022-05-02;"PATTINDE NATHOC FŒNUM NATIO";300.00;;
* 2022-05-01;"ARATIMOTE PATUBIT MODO CONDE MONCH NIS 0.56 Effect 8.1480 ost 37.77.6604 TER 0.62 8.52% de todivispect cor pasus fertumquobsemo TER 0.77";;8.44;
**/
function PFCSVFormat4() {
this.colDate = 0;
this.colDescr = 1;
this.colCredit = 2;
this.colDebit = 3;
this.colAmount = 4;
this.dateFormat = 'dd-mm-yyyy';
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === this.colAmount + 1)
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\-)[0-9]{2}(\-)[0-9]{4}/g)) {
formatMatched = true;
} else if (formatMatched && transaction[this.colDate].match(/[0-9]{4}(\-)[0-9]{2}(\-)[0-9]{2}/g)) {
formatMatched = true;
this.dateFormat = 'yyyy-mm-dd';
} else {
formatMatched = false;
}
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < (this.colAmount + 1))
continue;
if (transaction[this.colDate].match(/[0-9]{2,4}(\-)[0-9]{2}(\-)[0-9]{2,4}/g) && transaction[this.colDate].length == 10)
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date (just invert)
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
mappedLine.push(""); // Doc is empty for now
var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
var amount = element[this.colCredit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
amount = element[this.colDebit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
return mappedLine;
}
}
/**
* PF Html Format 1
* Html table with the followings colums:
* 0:Details; 1:Date; 2:Description; 3:Income; 4:Expenses; 5:DateValue; 6:Balance;
**/
function PFHtmlFormat1() {
/** This function defines the convertion of the single html table rows to Banana fields.
This is the only function to be adapted to the desired format. */
this.htmlRowToObject = function (htmlString) {
// Extract html fields (tags td)
var htmlTableFields = htmlString.match(/<td[^>]*>[\s\S]*?<\/td>/g); //[\s\S]*? match all chars non gready
if (!htmlTableFields)
return null;
// Verify fields count
if (htmlTableFields.length < 6)
return null;
// Verify if date field match
var date = this.htmlText(htmlTableFields[1]);
if (!date.match(/[0-9.]{8}/))
return null;
// Convert row
var rowObject = {};
rowObject.Date = Banana.Converter.toInternalDateFormat(this.htmlText(htmlTableFields[1]));
rowObject.Description = Banana.Converter.stringToCamelCase(this.htmlText(htmlTableFields[2]));
rowObject.ContraAccount = "";
rowObject.Income = Banana.Converter.toInternalNumberFormat(this.htmlText(htmlTableFields[3]));
rowObject.Expenses = Banana.Converter.toInternalNumberFormat(this.htmlText(htmlTableFields[4]));
rowObject.DateValue = Banana.Converter.toInternalDateFormat(this.htmlText(htmlTableFields[5]));
rowObject._Balance = Banana.Converter.toInternalNumberFormat(this.htmlText(htmlTableFields[6]));
return rowObject;
}
/** This function extract from the html the data to be imported in Banana Accounting.
It use the function htmlRowToObject to convert the single data rows. */
this.convert = function (htmlString) {
var rows = [];
var htmlTables = htmlString.match(/<tbody[^>]*>[\s\S]*?<\/tbody>/g); //[\s\S]*? match all chars non gready
if (htmlTables) {
for (var t = 0; t < htmlTables.length; t++) {
var htmlTableRows = htmlTables[t].match(/<tr[^>]*>[\s\S]*?<\/tr>/g); //[\s\S]*? match all chars non gready
if (htmlTableRows) {
for (var r = 0; r < htmlTableRows.length; r++) {
var row = this.htmlRowToObject(htmlTableRows[r]);
if (row) {
rows.push(row);
}
}
}
}
}
return rows;
}
/** This function extract the text inside an html element */
this.htmlText = function (htmlString) {
// Read text from html string
// The text is found between each ">...<" sequence
var retText = "";
var htmlTexts = htmlString.match(/>[^<]+</g);
if (htmlTexts) {
for (var i = 0; i < htmlTexts.length; i++) {
var htmlSubText = htmlTexts[i];
if (htmlSubText.length > 2)
retText = retText + htmlSubText.substr(1, htmlSubText.length - 2);
}
}
// Remove line feeds
retText = retText.replace(/^[ \n\r]+/, ""); // at the beginning
retText = retText.replace(/[ \n\r]+$/, ""); // at the end
retText = retText.replace(/ *[\n\r]+ */g, ", "); // in the middle
return retText;
}
}
/**
* PFCSV Format 3
* Example: pfcsv.#20101031
* BookingDate;BookingText;Details;ValutaDate;DebitAmount;CreditAmount;Balance
* 31.10.2010;FÜR DAS ONLINE-SET SEPTEMBER XXXX;;31.10.2010;;0.00;5831.73
* 29.10.2010;E-FINANCE XXX;1;29.10.2010;-45.00;;5831.73
* 29.10.2010;E-FINANCE XXX;1;29.10.2010;-131.55;;
* Example: pfcsv.#20131231
* Buchung;Buchungstext;Details;Valuta;Belastung;Gutschrift;Saldo;Kategorie;Familienmitglied;Kommentar
* "31.12.2013";"ZINSABSCHLUSS 010113 - 311213";"";"31.12.2013";"-0.15";"";"2549.30";"";"";""
* "24.12.2013";"KAUF/DIENSTLEISTUNG
* VOM 23.12.2013
* KARTEN NR. 82770597
* CUCINA PERO AG
* WƒDENSWIL";"1";"23.12.2013";"-124.00";"";"2549.45";"";"";""
**/
function PFCSVFormat3() {
this.colDate = 0;
this.colDescr = 1;
this.colDateValuta = 3;
this.colDebit = 4;
this.colCredit = 5;
this.colBalance = 6;
this.colComment = 9;
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === (this.colBalance + 1) ||
transaction.length === (this.colComment + 1))
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g) &&
transaction[this.colDate].length === 10)
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDateValuta].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g) &&
transaction[this.colDateValuta].length === 10)
formatMatched = true;
else
formatMatched = false;
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < (this.colBalance + 1))
continue;
if (transaction[this.colDate].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g) && transaction[this.colDate].length == 10 &&
transaction[this.colDateValuta].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g) && transaction[this.colDateValuta].length == 10)
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date (just invert)
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "DateValue", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], 'dd-mm-yyyy'));
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDateValuta], 'dd-mm-yyyy'));
mappedLine.push(""); // Doc is empty for now
var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
var amount = element[this.colCredit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
amount = element[this.colDebit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount));
return mappedLine;
}
}
/**
* PFCSV Format 2
* Example: pfcsv.#private20090401
* Example: pfcsv.#private20090401
* Data Testo d'avviso Accredito Addebito Data della valuta Saldo
* 20090401 /t ACQUISTO/SERVIZIO DEL XX.XX.XXXX CARTA N. XXX /t99.9 /t20090331 /t /t
* 20090331 /t ORDINE DEBIT DIRECT NUMERO CLIENTE XXX /t85.9 /t20090331 /t7881.35 /t
* 20090330 /t ACQUISTO/SERVIZIO DEL XX.XX.XXXX CARTA N. XXX /t43 /t20090328 /t7967.25 /t
*
*
**/
function PFCSVFormat2() {
this.colDate = 0;
this.colDescr = 1;
this.colCredit = 2;
this.colDebit = 3;
this.colDateValuta = 4;
this.colBalance = 5;
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === (this.colBalance + 2))
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{6}/g)
&& transaction[this.colDate].length === 8)
formatMatched = true;
if (formatMatched && transaction[this.colDateValuta].match(/[0-9]{6}/g) &&
transaction[this.colDateValuta].length == 8)
formatMatched = true;
else
formatMatched = false;
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < (this.colBalance + 1))
continue;
if (transaction[this.colDate].match(/[0-9]+/g) && transaction[this.colDate].length == 8 &&
transaction[this.colDateValuta].match(/[0-9]+/g) && transaction[this.colDateValuta].length == 8)
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date (just invert)
transactionsToImport = transactionsToImport.reverse();
// Add header and return
var header = [["Date", "DateValue", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(element[this.colDate]);
mappedLine.push(element[this.colDateValuta]);
mappedLine.push(""); // Doc is empty for now
var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ' '); //remove white spaces
mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colCredit]));
mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colDebit]));
return mappedLine;
}
}
/**
* PFCSV Format 1
* Example: pfcsv.#20030903-B
* Example: pfcsv.#20121101-B
* Example: pfcsv.#20160707
* Data;Descrizione della transazione;Accreditamento;Debito;Valuta;Saldo
* 31.08.2003;Saldo;;;;50078.40
* 01.09.2003;"YELLOWNET SAMMELAUFTRAG NR. X,YELLOWNET NUMMER XXXXXX";;-28.60;01.09.2003;50049.80
* 01.09.2003;"AUFTRAG DEBIT DIRECT,AUFTRAGSNUMMER X,KUNDENNUMMER XXXX";26.80;;01.09.2003;50076.60
**/
function PFCSVFormat1() {
this.colDate = 0;
this.colDescr = 1;
this.colCredit = 2;
this.colDebit = 3;
this.colDateValuta = 4;
this.colBalance = 5;
this.dateFormat = 'dd-mm-yyyy';
this.decimalSeparator = '.';
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === (this.colBalance + 1) || transaction.length === (this.colBalance + 2))
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.)[0-9]{2}(\.)[0-9]{2}/g)) {
this.dateFormat = 'dd.mm.yy';
formatMatched = true;
} else if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.|-)[0-9]{2}(\.|-)[0-9]{4}/g)) {
formatMatched = true;
} else if (formatMatched && transaction[this.colDate].match(/[0-9]{4}(\.|-)[0-9]{2}(\.|-)[0-9]{2}/g)) {
formatMatched = true;
this.dateFormat = 'yyyy-mm-dd';
} else {
formatMatched = false;
}
if (formatMatched && transaction[this.colDateValuta].match(/[0-9]{2,4}(\.|-)[0-9]{2}(\.|-)[0-9]{2,4}/g))
formatMatched = true;
else
formatMatched = false;
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < (this.colBalance + 1))
continue;
if (transaction[this.colDate].match(/[0-9\.]{3}/g) && transaction[this.colDateValuta].match(/[0-9\.]{3}/g))
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date
transactionsToImport = this.sort(transactionsToImport);
// Add header and return
var header = [["Date", "DateValue", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
/** Sort transactions by date */
this.sort = function (transactions) {
if (transactions.length <= 0)
return transactions;
var i = 0;
var previousDate = transactions[0][this.colDate];
while (i < transactions.length) {
var date = transactions[i][this.colDate];
if (previousDate.length > 0 && previousDate > date)
return transactions.reverse();
else if (previousDate.length > 0 && previousDate < date)
return transactions;
i++;
}
return transactions;
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDateValuta], this.dateFormat));
mappedLine.push(""); // Doc is empty for now
var tidyDescr = element[this.colDescr].replace(/ {2,}/g, ''); //remove white spaces
mappedLine.push(Banana.Converter.stringToCamelCase(tidyDescr));
var amount = element[this.colCredit].replace(/\+/g, ''); //remove plus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, this.decimalSeparator));
amount = element[this.colDebit].replace(/-/g, ''); //remove minus sign
mappedLine.push(Banana.Converter.toInternalNumberFormat(amount, this.decimalSeparator));
return mappedLine;
}
}
/**
* The function findSeparator is used to find the field separator.
*/
function findSeparator(string) {
var commaCount = 0;
var semicolonCount = 0;
var tabCount = 0;
for (var i = 0; i < 1000 && i < string.length; i++) {
var c = string[i];
if (c === ',')
commaCount++;
else if (c === ';')
semicolonCount++;
else if (c === '\t')
tabCount++;
}
if (tabCount > commaCount && tabCount > semicolonCount) {
return '\t';
}
else if (semicolonCount > commaCount) {
return ';';
}
return ',';
}
/**
* PFCSV Smart Business Format 1
* Example: pfcsv.#20180220-SBU
* "client_name";"paid_date";"paid_amount"
* "Schaub Thomas";"21.02.2018";"100.00"
* "Prins Carla";"20.02.2018";"150.00"
* "Mario Wlotzka";"15.02.2018";"960.00"
**/
function PFCSVFormatSBU1() {
this.colDate = 1;
this.colDescr = 0;
this.colCredit = 2;
this.dateFormat = 'dd.mm.yyyy';
this.decimalSeparator = '.';
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0)
return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
if (transaction.length === (this.colCredit + 1))
formatMatched = true;
else
formatMatched = false;
if (formatMatched && transaction[this.colDate].match(/[0-9]{2}(\.|-)[0-9]{2}(\.|-)[0-9]{4}/g)) {
formatMatched = true;
} else {
formatMatched = false;
}
if (formatMatched)
return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
var transactionsToImport = [];
// Filter and map rows
for (var i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < this.colCredit)
continue;
if (transaction[this.colDate].match(/[0-9\.]{3}/g))
transactionsToImport.push(this.mapTransaction(transaction));
}
// Sort rows by date
transactionsToImport = this.sort(transactionsToImport);
// Add header and return
var header = [["Date", "DateValue", "Doc", "Description", "Income", "Expenses"]];
return header.concat(transactionsToImport);
}
/** Sort transactions by date */
this.sort = function (transactions) {
if (transactions.length <= 0)
return transactions;
var i = 0;
var previousDate = transactions[0][this.colDate];
while (i < transactions.length) {
var date = transactions[i][this.colDate];
if (previousDate.length > 0 && previousDate > date)
return transactions.reverse();
else if (previousDate.length > 0 && previousDate < date)
return transactions;
i++;
}
return transactions;
}
this.mapTransaction = function (element) {
var mappedLine = [];
mappedLine.push(Banana.Converter.toInternalDateFormat(element[this.colDate], this.dateFormat));
mappedLine.push("");
mappedLine.push("");
mappedLine.push(element[this.colDescr]);
mappedLine.push(Banana.Converter.toInternalNumberFormat(element[this.colCredit], this.decimalSeparator));
mappedLine.push("");
return mappedLine;
}
}
/**
* The function findSeparator is used to find the field separator.
*/
function findSeparator(string) {
var commaCount = 0;
var semicolonCount = 0;
var tabCount = 0;
for (var i = 0; i < 1000 && i < string.length; i++) {
var c = string[i];
if (c === ',')
commaCount++;
else if (c === ';')
semicolonCount++;
else if (c === '\t')
tabCount++;
}
if (tabCount > commaCount && tabCount > semicolonCount) {
return '\t';
}
else if (semicolonCount > commaCount) {
return ';';
}
return ',';
}
/*
* class ImportUtilities
* Contains methods that can be shared by extensions for importing bank data
*/
var ImportUtilities = class ImportUtilities {
constructor(banDocument) {
this.banDocument = banDocument;
if (this.banDocument === undefined)
this.banDocument = Banana.document;
}
//The purpose of this function is to convert all the data into a format supported by Banana
convertToBananaFormat(intermediaryData) {
var columnTitles = [];
//Create titles only for fields not starting with "_"
for (var name in intermediaryData[0]) {
if (name.substring(0, 1) !== "_") {
columnTitles.push(name);
}
}
//Function call Banana.Converter.objectArrayToCsv() to create a CSV with new data just converted
var convertedCsv = Banana.Converter.objectArrayToCsv(columnTitles, intermediaryData, "\t");
return convertedCsv;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertCsvToIntermediaryData(inData, convertionParam) {
var form = [];
var intermediaryData = [];
//Add the header if present
if (convertionParam.header) {
inData = convertionParam.header + inData;
}
//Read the CSV file and create an array with the data
var csvFile = Banana.Converter.csvToArray(inData, convertionParam.separator, convertionParam.textDelim);
//Variables used to save the columns titles and the rows values
var columns = this.getHeaderData(csvFile, convertionParam.headerLineStart); //array
var rows = this.getRowData(csvFile, convertionParam.dataLineStart); //array of array
//Load the form with data taken from the array. Create objects
this.loadForm(form, columns, rows);
//Create the new CSV file with converted data
var convertedRow;
//For each row of the form, we call the rowConverter() function and we save the converted data
for (var i = 0; i < form.length; i++) {
convertedRow = convertionParam.rowConverter(form[i]);
intermediaryData.push(convertedRow);
}
//Return the converted CSV data into the Banana document table
return intermediaryData;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertHtmlToIntermediaryData(inData, convertionParam) {
var form = [];
var intermediaryData = [];
//Read the HTML file and create an array with the data
var htmlFile = [];
var htmlRows = inData.match(/<tr[^>]*>.*?<\/tr>/gi);
for (var rowNr = 0; rowNr < htmlRows.length; rowNr++) {
var htmlRow = [];
var htmlFields = htmlRows[rowNr].match(/<t(h|d)[^>]*>.*?<\/t(h|d)>/gi);
for (var fieldNr = 0; fieldNr < htmlFields.length; fieldNr++) {
var htmlFieldRe = />(.*)</g.exec(htmlFields[fieldNr]);
htmlRow.push(htmlFieldRe.length > 1 ? htmlFieldRe[1] : "");
}
htmlFile.push(htmlRow);
}
//Variables used to save the columns titles and the rows values
var columns = this.getHeaderData(htmlFile, convertionParam.headerLineStart); //array
var rows = this.getRowData(htmlFile, convertionParam.dataLineStart); //array of array
//Convert header names
for (var i = 0; i < columns.length; i++) {
var convertedHeader = columns[i];
convertedHeader = convertedHeader.toLowerCase();
convertedHeader = convertedHeader.replace(" ", "_");
var indexOfHeader = columns.indexOf(convertedHeader);
if (indexOfHeader >= 0 && indexOfHeader < i) { // Header alreay exist
//Avoid headers with same name adding an incremental index
var newIndex = 2;
while (columns.indexOf(convertedHeader + newIndex.toString()) !== -1 && newIndex < 99)
newIndex++;
convertedHeader = convertedHeader + newIndex.toString()
}
columns[i] = convertedHeader;
}
// Banana.console.log(JSON.stringify(columns, null, " "));
//Load the form with data taken from the array. Create objects
this.loadForm(form, columns, rows);
//Create the new CSV file with converted data
var convertedRow;
//For each row of the form, we call the rowConverter() function and we save the converted data
for (var i = 0; i < form.length; i++) {
convertedRow = convertionParam.rowConverter(form[i]);
intermediaryData.push(convertedRow);
}
//Return the converted CSV data into the Banana document table
return intermediaryData;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertToIntermediaryData(inData, convertionParam) {
if (convertionParam.format === "html") {
return this.convertHtmlToIntermediaryData(inData, convertionParam);
} else {
return this.convertCsvToIntermediaryData(inData, convertionParam);
}
}
//The purpose of this function is to return all the titles of the columns
getHeaderData(csvFile, startLineNumber) {
if (!startLineNumber) {
startLineNumber = 0;
}
var headerData = csvFile[startLineNumber];
for (var i = 0; i < headerData.length; i++) {
headerData[i] = headerData[i].trim();
if (!headerData[i]) {
headerData[i] = i;
}
//Avoid duplicate headers
var headerPos = headerData.indexOf(headerData[i]);
if (headerPos >= 0 && headerPos < i) { // Header already exist
var postfixIndex = 2;
while (headerData.indexOf(headerData[i] + postfixIndex.toString()) !== -1 && postfixIndex <= 99)
postfixIndex++; // Append an incremental index
headerData[i] = headerData[i] + postfixIndex.toString()
}
}
return headerData;
}
getLang() {
var lang = 'en';
if (this.banDocument)
lang = this.banDocument.locale;
else if (Banana.application.locale)
lang = Banana.application.locale;
if (lang.length > 2)
lang = lang.substr(0, 2);
return lang;
}
//The purpose of this function is to return all the data of the rows
getRowData(csvFile, startLineNumber) {
if (!startLineNumber) {
startLineNumber = 1;
}
var rowData = [];
for (var i = startLineNumber; i < csvFile.length; i++) {
rowData.push(csvFile[i]);
}
return rowData;
}
//The purpose of this function is to load all the data (titles of the columns and rows) and create a list of objects.
//Each object represents a row of the csv file
loadForm(form, columns, rows) {
for (var j = 0; j < rows.length; j++) {
var obj = {};
for (var i = 0; i < columns.length; i++) {
obj[columns[i]] = rows[j][i];
}
form.push(obj);
}
}
// The purpose of this function is to sort the data
sortData(intermediaryData, convertionParam) {
if (convertionParam.sortColums && convertionParam.sortColums.length) {
intermediaryData.sort(
function (row1, row2) {
for (var i = 0; i < convertionParam.sortColums.length; i++) {
var columnName = convertionParam.sortColums[i];
if (row1[columnName] > row2[columnName])
return 1;
else if (row1[columnName] < row2[columnName])
return -1;
}
return 0;
});
if (convertionParam.sortDescending)
intermediaryData.reverse();
}
return intermediaryData;
}
verifyBananaPlusVersion() {
if (!this.banDocument)
return false;
var BAN_VERSION_MIN = "10.0";
// supported Version
if (Banana.compareVersion && Banana.compareVersion(Banana.application.version, BAN_VERSION_MIN) >= 0) {
return true;
}
// not supported version
var lang = this.getLang();
var msg = "This extension requires Banana Accounting+";
this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
return false;
}
//Check if the version of Banana Accounting is compatible with this class
verifyBananaAdvancedVersion() {
if (!this.banDocument)
return false;
if (!Banana.application.license || Banana.application.license.licenseType !== "advanced") {
var lang = this.getLang();
var msg = "This extension requires Banana Accounting+ Advanced";
this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
return false;
}
return true;
}
getErrorMessage(errorId, lang) {
if (!lang)
lang = 'en';
switch (errorId) {
case "ID_ERR_FORMAT_UNKNOWN":
if (lang == 'it')
return "Formato del file *.csv non riconosciuto";
else if (lang == 'fr')
return "Format de fichier *.csv non reconnu";
else if (lang == 'de')
return "Unerkanntes *.csv-Dateiformat";
else
return "Unrecognised *.csv file format";
}
return '';
}
getLang() {
var lang = 'en';
if (Banana.application.locale)
lang = Banana.application.locale;
if (lang.length > 2)
lang = lang.substring(0, 2);
return lang;
}
getUnknownFormatError(){
let errId = "ID_ERR_FORMAT_UNKNOWN"; //error
let lang = this.getLang();
let msg = this.getErrorMessage(errId, lang);
Banana.document.addMessage(msg, errId);
}
}
Input data - csv_postfinance_example_format1_CreditCard_20230929.csv
Input text passed to the exec() function.
Kartenkonto:;0000 1234 5467 7654
Karte:;XXXX XXXX XXXX 1111 PostFinance Visa Business Card
Datum;Buchungsdetails;Gutschrift in CHF;Lastschrift in CHF
2023-08-24;"Tankstelle";;-94.70
2023-08-21;"Tankstelle";;-114.05
2023-08-10;"6131 STORNO JAHRESPREIS";80.00;
Disclaimer:
Der Dokumentinhalt wurde durch Filtereinstellungen der Kund:innen generiert. PostFinance ist für den Inhalt und die Vollständigkeit nicht verantwortlich.
Output data - csv_postfinance_example_format1_CreditCard_20230929.tsv
Output text returned by the exec() function.
Date\tDoc\tDescription\tIncome\tExpenses
2023-08-10\t\t6131 Storno Jahrespreis\t80.00\t
2023-08-21\t\tTankstelle\t\t114.05
2023-08-24\t\tTankstelle\t\t94.70
Input data - csv_postfinance_example_format2_20090401.csv
Input text passed to the exec() function.
Data Testo d'avviso Accredito Addebito Data della valuta Saldo
20090401 ACQUISTO/SERVIZIO DES 31.03.2009 UNDAE W. 47655099 AURSENTATREPE-8250 LENSIT C. A LENSIT 71.8 66667620
20090331 ORDINE PERIS NAVITI HORTIS PERUCUM 109631 PRIETE NABO ARITATIS OS HORTIS EO VOCTENENDIT: 000000000001107565001543539 PARTERIT EVENT PRINNAM DES 26.03.2009 W. REPEM UNDAE 1107 5650 0154 3539 85.9 20090331 7881.35
20090330 ACQUISTO/SERVIZIO DES 28.03.2009 UNDAE W. 47655099 ACUTION ASTO RODO POST 30 11025450 1423.50
20090330 ACQUISTO/SERVIZIO DES 28.03.2009 UNDAE W. 47655099 ACUTION ASTO RODO POST 47 11025450
20090330 ACQUISTO/SERVIZIO DES 29.03.2009 UNDAE W. 47655099 DICITIMPTIONSI EST POST 81 78665580
20090330 ACQUISTO/SERVIZIO DES 27.03.2009 UNDAE W. 47655099 MARTURRIUNGI EO VOLANEREM ANT LENSIT 58 55580477
20090326 OPERAZIONE VENT CHRARDUCI ALIUNDI DES 25.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS 556.5 28683443 5582.50
20090325 GIRATA EX RET MODEST 8465 DITIUNIS: /SECT SECTIA ET: WNQGR7Q513MBKEVW ACINSA OS OS NOSSUS ET: 79777957 090325CH81880950 IUSUPARTERIPUS: 690505731 4570.25 20090325 8388.85
20090322 OPERAZIONE VENT CHRARDUCI ALIUNDI DES 21.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS 1606.8 13330272 8568.5
20090319 PRELIEVO DE HABERUNT DES 19.03.2009 UNDAE W. 47655099 ANTINDIS: PUTULO PUTULO 636 68200818 2224.0
20090316 OPERAZIONE VENT CHRARDUCI ALIUNDI DES 13.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS 8233 86784617 8577.0
20090316 ACQUISTO/SERVIZIO DES 14.03.2009 UNDAE W. 47655099 IURRIDICUM PUPILODEANTE ET DIO QUA VOCARURA 32 68227047
20090316 ACQUISTO/SERVIZIO DES 12.03.2009 UNDAE W. 47655099 SOMULTUSA OS PLUCET 30 60283240
20090316 ACQUISTO/SERVIZIO DES 14.03.2009 UNDAE W. 47655099 FRUCEM B. VOCARURA B. VOCARURA 22 68227047
20090311 OPERAZIONE VENT CHRARDUCI ALIUNDI DES 10.03.2009 UNDAE W. 47655099 UNCELUM ALIUNDI REPERTUS 4018 23204372 35808.0
20090309 ACQUISTO/SERVIZIO DES 07.03.2009 UNDAE W. 47655099 PLEX-8801 B. VOCARURA QUA VOCARURA 50.34 17246434 83387.0
20090309 ACQUISTO/SERVIZIO DES 07.03.2009 UNDAE W. 47655099 FRUCEM B. VOCARURA B. VOCARURA 31.34 17246434
20090309 ACQUISTO/SERVIZIO DES 07.03.2009 UNDAE W. 47655099 AURSENTATREPE-7652 QUA VENTABO QUA VOCARURA 318 17246434
20090309 ACQUISTO/SERVIZIO DES 05.03.2009 UNDAE W. 47655099 SERE-NUNT, COCTUUNT-POSTERTE PUTULO PHRATUS 52.00 52 20090305
20090306 PRELIEVO DE HABERUNT DES 06.03.2009 UNDAE W. 47655099 ANTINDIS: LENSIT CUNGUA OBABO LENSIT 472 32032225 83803.1
20090306 ACQUISTO/SERVIZIO DES 05.03.2009 UNDAE W. 47655099 FRUCEM FORGENS FORGENS 7.1 24280353
20090304 ACQUISTO/SERVIZIO DES 03.03.2009 UNDAE W. 47655099 POSTO BONCH BONCH 50.50 84402074 84572.7
20090303 ORDINE PERIS NAVITI HORTIS PERUCUM 109631 PRIETE NABO ARITATIS OS HORTIS EO VOCTENENDIT: 000000000001107565001543539 PARTERIT EVENT PRINNAM DES 26.02.2009 W. REPEM UNDAE 1107 5650 0154 3539 92.4 20090303 16062.65
Output data - csv_postfinance_example_format2_20090401.tsv
Output text returned by the exec() function.
Date\tDateValue\tDoc\tDescription\tIncome\tExpenses
20090303\t20090303\t\tOrdine Peris Naviti Hortis Perucum 109631 Priete Nabo Aritatis Os Hortis Eo Voctenendit: 000000000001107565001543539 Parterit Event Prinnam Des 26.02.2009 W. Repem Undae 1107 5650 0154 3539\t\t92.4
20090304\t84402074\t\tAcquisto/servizio Des 03.03.2009 Undae W. 47655099 Posto Bonch Bonch\t\t50.50
20090306\t24280353\t\tAcquisto/servizio Des 05.03.2009 Undae W. 47655099 Frucem Forgens Forgens\t\t7.1
20090306\t32032225\t\tPrelievo De Haberunt Des 06.03.2009 Undae W. 47655099 Antindis: Lensit Cungua Obabo Lensit\t\t472
20090309\t20090305\t\tAcquisto/servizio Des 05.03.2009 Undae W. 47655099 Sere-Nunt, Coctuunt-Posterte Putulo Phratus 52.00\t\t52
20090309\t17246434\t\tAcquisto/servizio Des 07.03.2009 Undae W. 47655099 Aursentatrepe-7652 Qua Ventabo Qua Vocarura\t\t318
20090309\t17246434\t\tAcquisto/servizio Des 07.03.2009 Undae W. 47655099 Frucem B. Vocarura B. Vocarura\t\t31.34
20090309\t17246434\t\tAcquisto/servizio Des 07.03.2009 Undae W. 47655099 Plex-8801 B. Vocarura Qua Vocarura\t\t50.34
20090311\t23204372\t\tOperazione Vent Chrarduci Aliundi Des 10.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t4018
20090316\t68227047\t\tAcquisto/servizio Des 14.03.2009 Undae W. 47655099 Frucem B. Vocarura B. Vocarura\t\t22
20090316\t60283240\t\tAcquisto/servizio Des 12.03.2009 Undae W. 47655099 Somultusa Os Plucet\t\t30
20090316\t68227047\t\tAcquisto/servizio Des 14.03.2009 Undae W. 47655099 Iurridicum Pupilodeante Et Dio Qua Vocarura\t\t32
20090316\t86784617\t\tOperazione Vent Chrarduci Aliundi Des 13.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t8233
20090319\t68200818\t\tPrelievo De Haberunt Des 19.03.2009 Undae W. 47655099 Antindis: Putulo Putulo\t\t636
20090322\t13330272\t\tOperazione Vent Chrarduci Aliundi Des 21.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t1606.8
20090325\t20090325\t\tGirata Ex Ret Modest 8465 Ditiunis: /sect Sectia Et: WNQGR7Q513MBKEVW Acinsa Os Os Nossus Et: 79777957 090325CH81880950 Iusuparteripus: 690505731\t4570.25\t
20090326\t28683443\t\tOperazione Vent Chrarduci Aliundi Des 25.03.2009 Undae W. 47655099 Uncelum Aliundi Repertus\t\t556.5
20090330\t55580477\t\tAcquisto/servizio Des 27.03.2009 Undae W. 47655099 Marturriungi Eo Volanerem Ant Lensit\t\t58
20090330\t78665580\t\tAcquisto/servizio Des 29.03.2009 Undae W. 47655099 Dicitimptionsi Est Post\t\t81
20090330\t11025450\t\tAcquisto/servizio Des 28.03.2009 Undae W. 47655099 Acution Asto Rodo Post\t\t47
20090330\t11025450\t\tAcquisto/servizio Des 28.03.2009 Undae W. 47655099 Acution Asto Rodo Post\t\t30
20090331\t20090331\t\tOrdine Peris Naviti Hortis Perucum 109631 Priete Nabo Aritatis Os Hortis Eo Voctenendit: 000000000001107565001543539 Parterit Event Prinnam Des 26.03.2009 W. Repem Undae 1107 5650 0154 3539\t\t85.9
20090401\t66667620\t\tAcquisto/servizio Des 31.03.2009 Undae W. 47655099 Aursentatrepe-8250 Lensit C. A Lensit\t\t71.8
Input data - csv_postfinance_example_format3_20101031.csv
Input text passed to the exec() function.
BookingDate;BookingText;Details;ValutaDate;DebitAmount;CreditAmount;Balance
31.10.2010;HS IGE HYACTA-XXX ANDUXEMUM 4682 - VIVIVIOFIG DEAGNA INE PAM 3555.77;;31.10.2010;;0.00;5831.73
29.10.2010;W-GANGUNT 75-13080-1 - MAGNUNANS;2;29.10.2010;-45.00;;5831.73
29.10.2010;W-GANGUNT 75-88716-7 - DIUSQUAM OSTO;2;29.10.2010;-131.55;;
29.10.2010;W-GANGUNT 75-73547-1 - MULTIUNT DE. TABICUSTIO VI;2;29.10.2010;-110.00;;
29.10.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;29.10.2010;-119.65;;
29.10.2010;W-GANGUNT 75-88560-5 - MULTIUNT DE. TABICUSTIO VI;2;29.10.2010;-140.00;;
29.10.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;29.10.2010;-249.30;;
29.10.2010;W-GANGUNT 75-28743-1 - MATE - TRUNT UT;2;29.10.2010;-122.85;;
29.10.2010;W-GANGUNT 75-45521-6 - SUBSTO BY;2;29.10.2010;-115.50;;
29.10.2010;W-GANGUNT 75-36815-7 - VERANEST;2;29.10.2010;-300.00;;
29.10.2010;W-GANGUNT 75-2055-5 - DE.SOLONE EXACTIANAVIT UT;2;29.10.2010;-1400.00;;
25.10.2010;SOLO DEM RIDEO 28-2-2 - TEM UT;2;25.10.2010;;110.00;8565.58
19.10.2010;REGERUMERI - XXX INE. RIDEO XXX 10.63.4682;2;19.10.2010;;500.00;8455.58
13.10.2010;OVUM/SEDIVISCATIRIS - XXX 36.63.4682;2;11.10.2010;-39.95;;7955.58
06.10.2010;SOLO DEM RIDEO 28-2-2 - TEM UT;2;06.10.2010;;392.00;7995.53
06.10.2010;REGERUMERI - XXX INE. RIDEO XXX 30.63.4682;2;06.10.2010;;900.00;
04.10.2010;OVUM/SEDIVISCATIRIS - XXX 25.63.4682;2;02.10.2010;-30.15;;6703.53
30.09.2010;HS IGE HYACTA-XXX LODUCI 4682 - VIVIVIOFIG HABULAC INE PAM 3555.77;;30.09.2010;-3.00;;6733.68
30.09.2010;W-GANGUNT 75-27675-7 - TESTANES (PROXITA) UT, AGNA;2;30.09.2010;-36.25;;
30.09.2010;W-GANGUNT 75-34663-7 - SENIURA ASTIURUNGOBTIS UT;2;30.09.2010;-58.85;;
30.09.2010;W-GANGUNT 75-88560-5 - MULTIUNT DE. TABICUSTIO VI;2;30.09.2010;-140.00;;
30.09.2010;W-GANGUNT 75-7670-4 - PRIATE CHABIT;2;30.09.2010;-141.50;;
30.09.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;30.09.2010;-203.95;;
30.09.2010;W-GANGUNT 75-87515-1 - LIQUIT-PONTARE;2;30.09.2010;-249.30;;
30.09.2010;W-GANGUNT 75-36815-7 - VERANEST;2;30.09.2010;-300.00;;
30.09.2010;W-GANGUNT 75-2055-5 - DE.SOLONE EXACTIANAVIT UT;2;30.09.2010;-1400.00;;
30.09.2010;SOLO DEM HYACTA-RAL 700 - HUMELUMENTEM:;2;30.09.2010;;400.00;
28.09.2010;REGERUMERI XXX INE. RIDEO - XXX 51.28.4682;2;28.09.2010;;600.00;8866.53
28.09.2010;OVUM/SEDIVISCATIRIS - XXX 58.28.4682;2;24.09.2010;-49.00;;
27.09.2010;W-GANGUNT 24315 - PENDIRESTIFFICTRANSOLUNIUS DIO;2;27.09.2010;-282.60;;8315.53
20.09.2010;REGERUMERI XXX INE. RIDEO - XXX 58.28.4682;2;20.09.2010;;500.00;8598.13
17.09.2010;REGERUMERI XXX INE. RIDEO - XXX 75.28.4682;2;17.09.2010;;700.00;8098.13
14.09.2010;OVUM/SEDIVISCATIRIS - XXX 63.28.4682;2;10.09.2010;-44.95;;7398.13
03.09.2010;REGERUMERI XXX INE. RIDEO - XXX 63.28.4682;2;03.09.2010;;1200.00;7443.08
02.09.2010;OVUM/SEDIVISCATIRIS - XXX 28.28.4682;2;31.08.2010;-42.75;;6243.08
01.09.2010;REGERUMERI XXX INE. RIDEO - XXX 75.28.4682;2;01.09.2010;;800.00;6285.83
Output data - csv_postfinance_example_format3_20101031.tsv
Output text returned by the exec() function.
Date\tDateValue\tDoc\tDescription\tIncome\tExpenses
2010-09-01\t2010-09-01\t\tRegerumeri Xxx Ine. Rideo - Xxx 75.28.4682\t800.00\t
2010-09-02\t2010-08-31\t\tOvum/sediviscatiris - Xxx 28.28.4682\t\t42.75
2010-09-03\t2010-09-03\t\tRegerumeri Xxx Ine. Rideo - Xxx 63.28.4682\t1200.00\t
2010-09-14\t2010-09-10\t\tOvum/sediviscatiris - Xxx 63.28.4682\t\t44.95
2010-09-17\t2010-09-17\t\tRegerumeri Xxx Ine. Rideo - Xxx 75.28.4682\t700.00\t
2010-09-20\t2010-09-20\t\tRegerumeri Xxx Ine. Rideo - Xxx 58.28.4682\t500.00\t
2010-09-27\t2010-09-27\t\tW-Gangunt 24315 - Pendirestiffictransolunius Dio\t\t282.60
2010-09-28\t2010-09-24\t\tOvum/sediviscatiris - Xxx 58.28.4682\t\t49.00
2010-09-28\t2010-09-28\t\tRegerumeri Xxx Ine. Rideo - Xxx 51.28.4682\t600.00\t
2010-09-30\t2010-09-30\t\tSolo Dem Hyacta-Ral 700 - Humelumentem:\t400.00\t
2010-09-30\t2010-09-30\t\tW-Gangunt 75-2055-5 - De.Solone Exactianavit Ut\t\t1400.00
2010-09-30\t2010-09-30\t\tW-Gangunt 75-36815-7 - Veranest\t\t300.00
2010-09-30\t2010-09-30\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t249.30
2010-09-30\t2010-09-30\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t203.95
2010-09-30\t2010-09-30\t\tW-Gangunt 75-7670-4 - Priate Chabit\t\t141.50
2010-09-30\t2010-09-30\t\tW-Gangunt 75-88560-5 - Multiunt De. Tabicustio Vi\t\t140.00
2010-09-30\t2010-09-30\t\tW-Gangunt 75-34663-7 - Seniura Astiurungobtis Ut\t\t58.85
2010-09-30\t2010-09-30\t\tW-Gangunt 75-27675-7 - Testanes (Proxita) Ut, Agna\t\t36.25
2010-09-30\t2010-09-30\t\tH�s Ige Hyacta-Xxx Loduci 4682 - Vivivio�fig Habulac Ine Pam 3555.77\t\t3.00
2010-10-04\t2010-10-02\t\tOvum/sediviscatiris - Xxx 25.63.4682\t\t30.15
2010-10-06\t2010-10-06\t\tRegerumeri - Xxx Ine. Rideo Xxx 30.63.4682\t900.00\t
2010-10-06\t2010-10-06\t\tSolo Dem Rideo 28-2-2 - Tem Ut\t392.00\t
2010-10-13\t2010-10-11\t\tOvum/sediviscatiris - Xxx 36.63.4682\t\t39.95
2010-10-19\t2010-10-19\t\tRegerumeri - Xxx Ine. Rideo Xxx 10.63.4682\t500.00\t
2010-10-25\t2010-10-25\t\tSolo Dem Rideo 28-2-2 - Tem Ut\t110.00\t
2010-10-29\t2010-10-29\t\tW-Gangunt 75-2055-5 - De.Solone Exactianavit Ut\t\t1400.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-36815-7 - Veranest\t\t300.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-45521-6 - Substo By\t\t115.50
2010-10-29\t2010-10-29\t\tW-Gangunt 75-28743-1 - Mate - Trunt Ut\t\t122.85
2010-10-29\t2010-10-29\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t249.30
2010-10-29\t2010-10-29\t\tW-Gangunt 75-88560-5 - Multiunt De. Tabicustio Vi\t\t140.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-87515-1 - Liquit-Pontare\t\t119.65
2010-10-29\t2010-10-29\t\tW-Gangunt 75-73547-1 - Multiunt De. Tabicustio Vi\t\t110.00
2010-10-29\t2010-10-29\t\tW-Gangunt 75-88716-7 - Diusquam Osto\t\t131.55
2010-10-29\t2010-10-29\t\tW-Gangunt 75-13080-1 - Magnunans\t\t45.00
2010-10-31\t2010-10-31\t\tH�s Ige Hyacta-Xxx Anduxemum 4682 - Vivivio�fig De�agna Ine Pam 3555.77\t0.00\t
Import transactions example 3
Ubs (Switzerland) import transactions with multiple csv formats
// Banana Accounting Extension Javascript
// @id = ch.banana.switzerland.import.ubs.tutorial
// @api = 1.0
// @pubdate = 2024-06-24
// @publisher = Banana.ch SA
// @description = UBS - Import account statement .csv (Banana+ Advanced)
// @description.en = UBS - Import account statement .csv (Banana+ Advanced)
// @description.de = UBS - Bewegungen importieren .csv (Banana+ Advanced)
// @description.fr = UBS - Importer mouvements .csv (Banana+ Advanced)
// @description.it = UBS - Importa movimenti .csv (Banana+ Advanced)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @inputfilefilter = Text files (*.txt *.csv);;All files (*.*)
// @inputfilefilter.de = Text (*.txt *.csv);;Alle Dateien (*.*)
// @inputfilefilter.fr = Texte (*.txt *.csv);;Tous (*.*)
// @inputfilefilter.it = Testo (*.txt *.csv);;Tutti i files (*.*)
// @timeout = -1
/*
* class ImportUtilities
* Contains methods that can be shared by extensions for importing bank data
*/
var ImportUtilities = class ImportUtilities {
constructor(banDocument) {
this.banDocument = banDocument;
if (this.banDocument === undefined)
this.banDocument = Banana.document;
}
//The purpose of this function is to convert all the data into a format supported by Banana
convertToBananaFormat(intermediaryData) {
var columnTitles = [];
//Create titles only for fields not starting with "_"
for (var name in intermediaryData[0]) {
if (name.substring(0, 1) !== "_") {
columnTitles.push(name);
}
}
//Function call Banana.Converter.objectArrayToCsv() to create a CSV with new data just converted
var convertedCsv = Banana.Converter.objectArrayToCsv(columnTitles, intermediaryData, "\t");
return convertedCsv;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertCsvToIntermediaryData(inData, convertionParam) {
var form = [];
var intermediaryData = [];
//Add the header if present
if (convertionParam.header) {
inData = convertionParam.header + inData;
}
//Read the CSV file and create an array with the data
var csvFile = Banana.Converter.csvToArray(inData, convertionParam.separator, convertionParam.textDelim);
//Variables used to save the columns titles and the rows values
var columns = this.getHeaderData(csvFile, convertionParam.headerLineStart); //array
var rows = this.getRowData(csvFile, convertionParam.dataLineStart); //array of array
//Load the form with data taken from the array. Create objects
this.loadForm(form, columns, rows);
//Create the new CSV file with converted data
var convertedRow;
//For each row of the form, we call the rowConverter() function and we save the converted data
for (var i = 0; i < form.length; i++) {
convertedRow = convertionParam.rowConverter(form[i]);
intermediaryData.push(convertedRow);
}
//Return the converted CSV data into the Banana document table
return intermediaryData;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertHtmlToIntermediaryData(inData, convertionParam) {
var form = [];
var intermediaryData = [];
//Read the HTML file and create an array with the data
var htmlFile = [];
var htmlRows = inData.match(/<tr[^>]*>.*?<\/tr>/gi);
for (var rowNr = 0; rowNr < htmlRows.length; rowNr++) {
var htmlRow = [];
var htmlFields = htmlRows[rowNr].match(/<t(h|d)[^>]*>.*?<\/t(h|d)>/gi);
for (var fieldNr = 0; fieldNr < htmlFields.length; fieldNr++) {
var htmlFieldRe = />(.*)</g.exec(htmlFields[fieldNr]);
htmlRow.push(htmlFieldRe.length > 1 ? htmlFieldRe[1] : "");
}
htmlFile.push(htmlRow);
}
//Variables used to save the columns titles and the rows values
var columns = this.getHeaderData(htmlFile, convertionParam.headerLineStart); //array
var rows = this.getRowData(htmlFile, convertionParam.dataLineStart); //array of array
//Convert header names
for (var i = 0; i < columns.length; i++) {
var convertedHeader = columns[i];
convertedHeader = convertedHeader.toLowerCase();
convertedHeader = convertedHeader.replace(" ", "_");
var indexOfHeader = columns.indexOf(convertedHeader);
if (indexOfHeader >= 0 && indexOfHeader < i) { // Header alreay exist
//Avoid headers with same name adding an incremental index
var newIndex = 2;
while (columns.indexOf(convertedHeader + newIndex.toString()) !== -1 && newIndex < 99)
newIndex++;
convertedHeader = convertedHeader + newIndex.toString()
}
columns[i] = convertedHeader;
}
// Banana.console.log(JSON.stringify(columns, null, " "));
//Load the form with data taken from the array. Create objects
this.loadForm(form, columns, rows);
//Create the new CSV file with converted data
var convertedRow;
//For each row of the form, we call the rowConverter() function and we save the converted data
for (var i = 0; i < form.length; i++) {
convertedRow = convertionParam.rowConverter(form[i]);
intermediaryData.push(convertedRow);
}
//Return the converted CSV data into the Banana document table
return intermediaryData;
}
// Convert to an array of objects where each object property is the banana columnNameXml
convertToIntermediaryData(inData, convertionParam) {
if (convertionParam.format === "html") {
return this.convertHtmlToIntermediaryData(inData, convertionParam);
} else {
return this.convertCsvToIntermediaryData(inData, convertionParam);
}
}
//The purpose of this function is to return all the titles of the columns
getHeaderData(csvFile, startLineNumber) {
if (!startLineNumber) {
startLineNumber = 0;
}
var headerData = csvFile[startLineNumber];
for (var i = 0; i < headerData.length; i++) {
headerData[i] = headerData[i].trim();
if (!headerData[i]) {
headerData[i] = i;
}
//Avoid duplicate headers
var headerPos = headerData.indexOf(headerData[i]);
if (headerPos >= 0 && headerPos < i) { // Header already exist
var postfixIndex = 2;
while (headerData.indexOf(headerData[i] + postfixIndex.toString()) !== -1 && postfixIndex <= 99)
postfixIndex++; // Append an incremental index
headerData[i] = headerData[i] + postfixIndex.toString()
}
}
return headerData;
}
getLang() {
var lang = 'en';
if (this.banDocument)
lang = this.banDocument.locale;
else if (Banana.application.locale)
lang = Banana.application.locale;
if (lang.length > 2)
lang = lang.substr(0, 2);
return lang;
}
//The purpose of this function is to return all the data of the rows
getRowData(csvFile, startLineNumber) {
if (!startLineNumber) {
startLineNumber = 1;
}
var rowData = [];
for (var i = startLineNumber; i < csvFile.length; i++) {
rowData.push(csvFile[i]);
}
return rowData;
}
//The purpose of this function is to load all the data (titles of the columns and rows) and create a list of objects.
//Each object represents a row of the csv file
loadForm(form, columns, rows) {
for (var j = 0; j < rows.length; j++) {
var obj = {};
for (var i = 0; i < columns.length; i++) {
obj[columns[i]] = rows[j][i];
}
form.push(obj);
}
}
// The purpose of this function is to sort the data
sortData(intermediaryData, convertionParam) {
if (convertionParam.sortColums && convertionParam.sortColums.length) {
intermediaryData.sort(
function (row1, row2) {
for (var i = 0; i < convertionParam.sortColums.length; i++) {
var columnName = convertionParam.sortColums[i];
if (row1[columnName] > row2[columnName])
return 1;
else if (row1[columnName] < row2[columnName])
return -1;
}
return 0;
});
if (convertionParam.sortDescending)
intermediaryData.reverse();
}
return intermediaryData;
}
verifyBananaPlusVersion() {
if (!this.banDocument)
return false;
var BAN_VERSION_MIN = "10.0";
// supported Version
if (Banana.compareVersion && Banana.compareVersion(Banana.application.version, BAN_VERSION_MIN) >= 0) {
return true;
}
// not supported version
var lang = this.getLang();
var msg = "This extension requires Banana Accounting+";
this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
return false;
}
//Check if the version of Banana Accounting is compatible with this class
verifyBananaAdvancedVersion() {
if (!this.banDocument)
return false;
if (!Banana.application.license || Banana.application.license.licenseType !== "advanced") {
var lang = this.getLang();
var msg = "This extension requires Banana Accounting+ Advanced";
this.banDocument.addMessage(msg, "ID_ERR_LICENSE_NOTVALID");
return false;
}
return true;
}
getErrorMessage(errorId, lang) {
if (!lang)
lang = 'en';
switch (errorId) {
case "ID_ERR_FORMAT_UNKNOWN":
if (lang == 'it')
return "Formato del file *.csv non riconosciuto";
else if (lang == 'fr')
return "Format de fichier *.csv non reconnu";
else if (lang == 'de')
return "Unerkanntes *.csv-Dateiformat";
else
return "Unrecognised *.csv file format";
}
return '';
}
getLang() {
var lang = 'en';
if (Banana.application.locale)
lang = Banana.application.locale;
if (lang.length > 2)
lang = lang.substring(0, 2);
return lang;
}
getUnknownFormatError(){
let errId = "ID_ERR_FORMAT_UNKNOWN"; //error
let lang = this.getLang();
let msg = this.getErrorMessage(errId, lang);
Banana.document.addMessage(msg, errId);
}
}
/**
* Parse the data and return the data to be imported as a tab separated file.
*/
function exec(inData, isTest) {
if (!inData) return "";
/** in the new version of format 3 (June 2024) we could have the situation where descriptions have
* trhee pairs of quotes: """description text""". This situation cause problems
* when the API Banana.Converter.csvToArray() read the content, the text is ignored. This happen
* when the text description contains a semicolon ';'.
* For the time being, we 'clean' the text of these quotes pairs by replacing them with normal quotes pairs.
* */
inData = inData.replace(/"""/g, '"');
var importUtilities = new ImportUtilities(Banana.document);
if (isTest !== true && !importUtilities.verifyBananaAdvancedVersion())
return "";
convertionParam = defineConversionParam(inData);
//Add the header if present
if (convertionParam.header) {
inData = convertionParam.header + inData;
}
let transactions = Banana.Converter.csvToArray(
inData,
convertionParam.separator,
convertionParam.textDelim
);
// Format Credit Card
var formatCc1Ubs = new UBSFormatCc1();
if (formatCc1Ubs.match(transactions)) {
transactions = formatCc1Ubs.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 1
var format1Ubs = new UBSFormat1();
if (format1Ubs.match(transactions)) {
transactions = format1Ubs.convert(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 2
var format2Ubs = new UBSFormat2();
if (format2Ubs.match(transactions)) {
transactions = format2Ubs.convert(transactions, convertionParam);
transactions = format2Ubs.postProcessIntermediaryData(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
// Format 3
var format3Ubs = new UBSFormat3();
if (format3Ubs.match(transactions)) {
transactions = format3Ubs.convert(transactions, convertionParam);
transactions = format3Ubs.postProcessIntermediaryData(transactions);
Banana.console.log(Banana.Converter.arrayToTsv(transactions));
return Banana.Converter.arrayToTsv(transactions);
}
importUtilities.getUnknownFormatError();
return "";
}
/**
* UBS Format 1
*
* Valuation date;Banking relationship;Portfolio;Product;IBAN;Ccy.;Date from;Date to;Description;Trade date;Booking date;Value date;Description 1;Description 2;Description 3;Transaction no.;Exchange rate in the original amount in settlement currency;Individual amount;Debit;Credit;Balance
* 07.07.17;0240 00254061;;0240 00254061.01C;CH62 0024 4240 2340 6101 C;CHF;01.02.17;30.06.17;UBS Business Current Account;30.06.17;30.06.17;30.06.17;e-banking Order;BWS - CHENEVAL, BWS GERMANLINGUA, DE DE DE 80331 MUECHEN, INVOICE : M25252, STD: ALICE CHENEVAL;;ZD81181TI0690091;;;3'416.82;;206'149.34
* 07.07.17;0240 00254061;;0240 00254061.01C;CH62 0024 4240 2340 6101 C;CHF;01.02.17;30.06.17;UBS Business Current Account;30.06.17;30.06.17;30.06.17;Amount paid;;;ZD81181TI0690091;1.113334;3069;;;
*/
function UBSFormat1() {
// Index of columns in csv file
this.colCount = 21;
this.colCurrency = 5;
this.colDate = 10;
this.colDateValuta = 11;
this.colDescr1 = 12;
this.colDescr2 = 13;
this.colDescr3 = 14;
this.colDescr4 = 15;
this.colExchRate = -5;
this.colDetails = -4;
this.colDebit = -3;
this.colCredit = -2;
this.colBalance = -1;
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0) return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
/* array should have all columns */
if (transaction.length >= this.colCount) formatMatched = true;
else formatMatched = false;
if (formatMatched &&
transaction[this.colDate] &&
transaction[this.colDate].match(/^[0-9]+\.[0-9]+\.[0-9]+$/))
formatMatched = true;
else formatMatched = false;
if (
formatMatched &&
transaction[this.colDateValuta] &&
transaction[this.colDateValuta].match(/^[0-9]+\.[0-9]+\.[0-9]+$/)
)
formatMatched = true;
else formatMatched = false;
if (formatMatched) return true;
}
return false;
};
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
this.colCount =
transactions.length > 1 ? transactions[0].length : this.colCount;
transactions = this.convertTransactions(transactions);
if (transactions.length > 1) {
//Sort by date
if (transactions[0][0] > transactions[transactions.length - 1][0]) {
//Sort transactions
transactions = transactions.reverse();
}
}
var header = [
[
"Date",
"DateValue",
"Doc",
"ExternalReference",
"Description",
"Income",
"Expenses",
"IsDetail",
],
];
return header.concat(transactions);
};
/** Convert the transaction to the format to be imported */
this.convertTransactions = function (transactions) {
var transactionsToImport = [];
/** Complete, filter and map rows */
var lastCompleteTransaction = null;
for (var i = 0; i < transactions.length; i++) {
var mappedTransaction = [];
var transaction = transactions[i];
if (transaction.length <= this.colDate ||
!transaction[this.colDate].match(/[0-9\.]+/g))
continue;
if (transaction.length >= this.colCount + this.colBalance &&
(transaction[this.colCount + this.colDebit] && (transaction[this.colCount + this.colDebit].length > 0) ||
(transaction[this.colCount + this.colCredit] && transaction[this.colCount + this.colCredit].length > 0) ||
(transaction[this.colCount + this.colBalance] && transaction[this.colCount + this.colBalance].length > 0))) {
// Is a complete row
if (lastCompleteTransaction) {
mappedTransaction = this.mapTransaction(lastCompleteTransaction);
mappedTransaction.push("");
transactionsToImport.push(mappedTransaction);
lastCompleteTransaction = null;
}
lastCompleteTransaction = transaction;
} else if (transaction.length >= this.colCount + this.colDetails &&
transaction[this.colCount + this.colDetails].length > 0) {
// Is a detail row
if (transaction[this.colCount + this.colExchRate].match(/[0-9]+\.[0-9]+/g)) {
// Is a multicurrency detail row
if (lastCompleteTransaction) {
mappedTransaction = this.mapTransaction(lastCompleteTransaction);
mappedTransaction.push("");
transactionsToImport.push(mappedTransaction);
lastCompleteTransaction = null;
}
} else {
// Is a normal detail row
if (transaction[this.colDescr1] === "Cashpayment charges deducted by post" &&
lastCompleteTransaction && lastCompleteTransaction[this.colDescr1] === "Incomings UBS BESR Quick") {
// Post charges are contabilised at the end of the period, skip this row
if (lastCompleteTransaction !== null) {
mappedTransaction = this.mapTransaction(lastCompleteTransaction);
mappedTransaction.push("");
transactionsToImport.push(mappedTransaction);
lastCompleteTransaction = null;
}
} else {
if (lastCompleteTransaction !== null) {
mappedTransaction = this.mapTransaction(lastCompleteTransaction);
mappedTransaction.push("S");
transactionsToImport.push(mappedTransaction);
lastCompleteTransaction = null;
}
mappedTransaction = this.mapDetailTransaction(transaction);
mappedTransaction.push("D");
transactionsToImport.push(mappedTransaction);
}
}
}
}
if (lastCompleteTransaction !== null) {
transactionsToImport.push(this.mapTransaction(lastCompleteTransaction));
}
return transactionsToImport;
};
this.mapTransaction = function (element) {
var mappedLine = [];
if (
element[this.colDate] === null ||
element[this.colDescr1] === null ||
element[this.colDescr2] === null ||
element[this.colDescr3] === null ||
element[this.colDescr4] === null ||
element[element.length + this.colCredit] === null ||
element[element.length + this.colDebit] === null
) {
mappedLine.push("");
mappedLine.push("");
mappedLine.push("Error importing data");
mappedLine.push("");
mappedLine.push("");
return mappedLine;
}
mappedLine.push(
Banana.Converter.toInternalDateFormat(element[this.colDate], "dd.mm.yyyy")
);
mappedLine.push(
Banana.Converter.toInternalDateFormat(
element[this.colDateValuta],
"dd.mm.yyyy"
)
);
mappedLine.push("");
mappedLine.push(element[this.colDescr4]); //transaction number.
mappedLine.push(this.mapDescription(element));
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[element.length + this.colCredit],
"."
)
);
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[element.length + this.colDebit],
"."
)
);
return mappedLine;
};
this.mapDetailTransaction = function (element) {
var mappedLine = [];
if (
element[this.colDate] === null ||
element[this.colDescr1] === null ||
element[this.colDescr2] === null ||
element[this.colDescr3] === null ||
element[this.colDescr4] === null ||
element[element.length + this.colCredit] === null ||
element[element.length + this.colDebit] === null
) {
mappedLine.push("");
mappedLine.push("");
mappedLine.push("");
mappedLine.push("Error importing data");
mappedLine.push("");
mappedLine.push("");
return mappedLine;
}
mappedLine.push(
Banana.Converter.toInternalDateFormat(element[this.colDate], "dd.mm.yyyy")
);
mappedLine.push(
Banana.Converter.toInternalDateFormat(
element[this.colDateValuta],
"dd.mm.yyyy"
)
);
mappedLine.push("");
mappedLine.push(element[this.colDescr4]);
mappedLine.push(this.mapDescription(element));
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[this.colCount + this.colDetails].replace(",", ""),
"."
)
);
mappedLine.push("");
return mappedLine;
};
/**
* Return the descrition for the requested line.
*/
this.mapDescription = function (line) {
var descr = line[this.colDescr1];
if (line[this.colDescr2].length) descr += ", " + line[this.colDescr2];
if (line[this.colDescr3].length) descr += ", " + line[this.colDescr3];
descr = Banana.Converter.stringToCamelCase(descr);
descr = descr.replace(/"/g, '\\"');
return '"' + descr + '"';
};
}
/**
* UBS Format 2
*
* This new format (09.2022) use the import utilities class.
* This format has no detail rows.
*
* Numero di conto:;0234 00103914.40;
* IBAN:;CH29 0023 4234 1039 1440 G;
* Dal:;2022-09-15;
* Al:;2022-09-19;
* Saldo iniziale:;1719.34;
* Saldo finale:;631.07;
* Valutazione in:;CHF;
* Numero di transazioni in questo periodo:;13;
*
* Data dell'operazione;Ora dell'operazione;Data di registrazione;Data di valuta;Moneta;Importo della transazione;Addebito/Accredito;Saldo;N. di transazione;Descrizione1;Descrizione2;Descrizione3;Note a piè di pagina;
* 2022-09-19;;2022-09-19;2022-09-19;CHF;-150.00;Addebito;600;123456TI1234567;"Versamento";"Ordine di pagamento via e-banking";;;
* 2022-09-19;;2022-09-19;2022-09-19;CHF;-92.00;Addebito;692;2345678TI2345678;"Versamento";"Ordine di pagamento via e-banking";;;
* 2022-09-19;;2022-09-19;2022-09-19;CHF;-10.00;Addebito;702;3456789TI3456789;"Versamento";"Ordine di pagamento via e-banking";;;
* 2022-09-19;;2022-09-19;2022-09-19;CHF;-40.00;Addebito;742;4567890TI4567890;"Versamento";"Ordine di pagamento via e-banking";;;
*
*/
var UBSFormat2 = class UBSFormat2 extends ImportUtilities {
// Index of columns in csv file
constructor(banDocument) {
super(banDocument);
this.colCount = 12;
this.colDateOperation = 0;
this.colDateValuta = 3;
this.colAmount = 5;
this.colOpType = 6;
this.colTransNr = 8;
this.colDescr1 = 9;
this.colDescr2 = 10;
this.colDescr3 = 11;
//Index of columns in import format.
this.newColDate = 0;
this.newColDescription = 2;
this.newColExpenses = 4;
}
/** Return true if the transactions match this format */
match(transactions) {
if (transactions.length === 0) return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
/* array should have all columns */
if (transaction.length >= this.colCount) formatMatched = true;
else formatMatched = false;
if (
formatMatched &&
transaction[this.colDateOperation] &&
transaction[this.colDateOperation].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
)
formatMatched = true;
else formatMatched = false;
if (
formatMatched &&
transaction[this.colDateValuta] &&
transaction[this.colDateValuta].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
)
if (
formatMatched &&
transaction[this.colOpType] &&
transaction[this.colOpType].match(/[a-zA-Z]/)
)
formatMatched = true;
else formatMatched = false;
if (formatMatched) return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
convert(transactions, convertionParam) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < this.colCount + 1) continue;
if (
transaction[this.colDateOperation].match(/[0-9\.]+/g) &&
transaction[this.colDateOperation].length === 10
)
transactionsToImport.push(this.mapTransaction(transaction));
}
/**
* Sort rows by date
* SPECIFY THE COLUMN TO USE FOR SORTING
* If sortColums is empty the data are not sorted
* */
convertionParam.sortColums = [0, 2]; //0 = "Date" field position, 2 = "Description" field position.
convertionParam.sortDescending = false;
transactionsToImport = sort(transactionsToImport, convertionParam);
// Add header and return
var header = [
[
"Date",
"DateValue",
"Description",
"ExternalReference",
"Expenses",
"Income",
],
];
return header.concat(transactionsToImport);
}
mapTransaction(element) {
var mappedLine = [];
let dateText = "";
let dateValueText = "";
dateText = element[this.colDateOperation].substring(0, 10);
dateValueText = element[this.colDateValuta].substring(0, 10);
mappedLine.push(
Banana.Converter.toInternalDateFormat(dateText, "yyyy-mm-dd")
);
mappedLine.push(
Banana.Converter.toInternalDateFormat(dateValueText, "yyyy-mm-dd")
);
// wrap descr to bypass TipoFileImporta::IndovinaSeparatore problem
mappedLine.push(
element[this.colDescr1] +
" " +
element[this.colDescr2] +
" " +
element[this.colDescr3]
);
mappedLine.push(element[this.colTransNr]);
if (element[this.colAmount].indexOf("-") == -1) {
mappedLine.push("");
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[this.colAmount],
this.decimalSeparator
)
);
} else {
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[this.colAmount],
this.decimalSeparator
)
);
mappedLine.push("");
}
return mappedLine;
}
//The purpose of this function is to let the user specify how to convert the categories
postProcessIntermediaryData(transactions) {
let processesData = [];
if (transactions.length < 1) return processesData;
processesData = transactions;
/** INSERT HERE THE LIST OF ACCOUNTS NAME AND THE CONVERSION NUMBER
* If the content of "Account" is the same of the text
* it will be replaced by the account number given */
//Accounts conversion
var accounts = {
//...
};
/** INSERT HERE THE LIST OF CATEGORIES NAME AND THE CONVERSION NUMBER
* If the content of "ContraAccount" is the same of the text
* it will be replaced by the account number given */
//Categories conversion
var categories = {
//...
};
//Apply the conversions
for (var i = 1; i < processesData.length; i++) {
var convertedData = processesData[i];
//Invert values
if (convertedData[this.newColExpenses]) {
convertedData[this.newColExpenses] = Banana.SDecimal.invert(
convertedData[this.newColExpenses]
);
}
}
return processesData;
}
};
/**
* UBS Format 3
*
* This new format (08.11.2022) use the import utilities class.
* This format has no detail rows.
*
* Numero di conto:;0234 00103914.40;
* IBAN:;CH29 0023 4234 1039 1440 G;
* Dal:;2022-09-15;
* Al:;2022-09-19;
* Saldo iniziale:;1719.34;
* Saldo finale:;631.07;
* Valutazione in:;CHF;
* Numero di transazioni in questo periodo:;13;
*
* Data dell'operazione;Ora dell'operazione;Data di registrazione;Data di valuta;Moneta;Debit amount;Credit amount;Individual amount;Saldo;N. di transazione;Descrizione1;Descrizione2;Descrizione3;Note a piè di pagina;
* 2022-11-07;;2022-11-07;2022-11-07;TEM;-99.80;;;2215.89;1770311TO2672955;"Meniunis (Suractae) VI,Maxi Habyssidedertis 6, 3542 Aerna";"Cepate in consolest tam g-possica";"Decilausa vi. NUS: 35 47463 30382 81016 85544 75378, Experi in consolest: 2213 / Osit: 37.57.84 - 62.57.84 / Mendimus audio at: 75.64.4848, Sologit vi. CAPH: JA18 6457 3522 2051 7571 2, Suisi: B-Possica TEM Suractae, Offereganga vi. 6068584LN1841647";;
* 2022-11-07;;2022-11-07;2022-11-07;TEM;-52.10;;;2315.69;1670311TO2672937;"Meniunis (Suractae) VI,Maxi Habyssidedertis 6, 3542 Aerna";"Cepate in consolest tam g-possica";"Decilausa vi. NUS: 35 47463 30382 27465 62135 38521, Experi in consolest: 2213 / Osit: 37.80.84 - 16.80.84 / Mendimus audio at: 81.57.4848, Sologit vi. CAPH: JA18 6457 3522 2051 7571 2, Suisi: B-Possica TEM Suractae, Offereganga vi. 7510665VI0356053";;
* 2022-11-07;17:10:46;;2022-11-07;TEM;-4.35;;;2367.79;9999311BN1710030;"CERA SEPTEMPTO,SEPTEMPTO";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 7740420TJ8353344";;
* 2022-11-07;07:55:57;;2022-11-07;TEM;-2.70;;;2372.14;9999311BN0755924;"CERA SEPTEMPTO,SEPTEMPTO";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 3275420YO4320201";;
* 2022-11-07;17:21:52;;2022-11-07;TEM;-2.30;;;2374.84;9999311BN1721198;"POR SALL. SED. MANTUMN PARATE,PARATE";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 7518748DV2785407";;
* 2022-11-06;15:31:05;2022-11-07;2022-11-06;TEM;-6.80;;;2377.14;9930811BN5353554;"Parescro Tratiantro VI,6010 Recto";"18264075-0 07/24, Pagamento carta di debito";"Offereganga vi. 8740610YF3026752";;
*
*/
var UBSFormat3 = class UBSFormat3 extends ImportUtilities {
// Index of columns in *.csv file
constructor(banDocument) {
super(banDocument);
//original file columns
this.colCount = 13;
this.colDateOperation = 0;
this.colDateValuta = 3;
this.colDebitAmt = 5;
this.colCreditAmt = 6;
this.colTransNr = 9;
this.colDescr1 = 10;
this.colDescr2 = 11;
this.colDescr3 = 12;
this.decimalSeparator = ".";
//Index of columns in import format.
this.newColDate = 0;
this.newColDescription = 2;
this.newColExpenses = 4;
}
/** Return true if the transactions match this format */
match(transactions) {
if (transactions.length === 0) return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
/* array should have all columns */
if (transaction.length >= this.colCount) formatMatched = true;
else formatMatched = false;
if (
formatMatched &&
transaction[this.colDateOperation] &&
transaction[this.colDateOperation].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
)
formatMatched = true;
else formatMatched = false;
if (
formatMatched &&
transaction[this.colDateValuta] &&
transaction[this.colDateValuta].match(/^[0-9]+\-[0-9]+\-[0-9]+$/)
)
formatMatched = true;
else formatMatched = false;
if (
(formatMatched && transaction[this.colDebitAmt]) ||
transaction[this.colCreditAmt]
)
formatMatched = true;
else formatMatched = false;
if (formatMatched) return true;
}
return false;
}
/** Convert the transaction to the format to be imported */
convert(transactions, convertionParam) {
var transactionsToImport = [];
// Filter and map rows
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
if (transaction.length < this.colCount + 1) continue;
if (
transaction[this.colDateOperation].match(/[0-9\.]+/g) &&
transaction[this.colDateOperation].length === 10
)
transactionsToImport.push(this.mapTransaction(transaction));
}
/**
* Sort rows by date
* SPECIFY THE COLUMN TO USE FOR SORTING
* If sortColums is empty the data are not sorted
* */
convertionParam.sortColums = [this.newColDate, this.newColDescription]; //0 = "Date" field position, 2 = "Description" field position.
convertionParam.sortDescending = false;
transactionsToImport = sort(transactionsToImport, convertionParam);
// Add header and return
var header = [
[
"Date",
"DateValue",
"Description",
"ExternalReference",
"Expenses",
"Income",
],
];
return header.concat(transactionsToImport);
}
mapTransaction(element) {
var mappedLine = [];
let dateText = "";
let dateValueText = "";
dateText = element[this.colDateOperation].substring(0, 10);
dateValueText = element[this.colDateValuta].substring(0, 10);
mappedLine.push(
Banana.Converter.toInternalDateFormat(dateText, "yyyy-mm-dd")
);
mappedLine.push(
Banana.Converter.toInternalDateFormat(dateValueText, "yyyy-mm-dd")
);
// wrap descr to bypass TipoFileImporta::IndovinaSeparatore problem
mappedLine.push(element[this.colDescr1] + " " + element[this.colDescr2]);
mappedLine.push(element[this.colTransNr]);
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[this.colDebitAmt],
this.decimalSeparator
)
);
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[this.colCreditAmt],
this.decimalSeparator
)
);
return mappedLine;
}
//The purpose of this function is to let the user specify how to convert the categories
postProcessIntermediaryData(transactions) {
let processesData = [];
if (transactions.length < 1) return processesData;
processesData = transactions;
/** INSERT HERE THE LIST OF ACCOUNTS NAME AND THE CONVERSION NUMBER
* If the content of "Account" is the same of the text
* it will be replaced by the account number given */
//Accounts conversion
var accounts = {
//...
};
/** INSERT HERE THE LIST OF CATEGORIES NAME AND THE CONVERSION NUMBER
* If the content of "ContraAccount" is the same of the text
* it will be replaced by the account number given */
//Categories conversion
var categories = {
//...
};
//Apply the conversions
for (var i = 1; i < processesData.length; i++) {
var convertedData = processesData[i];
//Invert values
if (convertedData[this.newColExpenses]) {
convertedData[this.newColExpenses] = Banana.SDecimal.invert(
convertedData[this.newColExpenses]
);
}
}
return processesData;
}
};
/**
* UBS Credit Card Format 1
*
* sep=;
* Numéro de compte;Numéro de carte;Titulaire de compte/carte;Date d'achat;Texte comptable;Secteur;Montant;Monnaie originale;Cours;Monnaie;Débit;Crédit;Ecriture
* ZZZZ ZZZZ ZZZZ;;SIMON JEAN;19.10.2017;Report de solde;;;;;CHF;;0.00;
* ZZZZ ZZZZ ZZZZ;XXXX XXXX XXXX XXXX;JEAN SIMON;16.11.2017;Cafe de Paris Geneve CHE;Restaurants, Bar;119.40;CHF;;;;;
* ZZZZ ZZZZ ZZZZ;XXXX XXXX XXXX XXXX;JEAN SIMON;13.11.2017;www.banana.ch LUGANO CHE;Magasin d ordinateurs;189.00;CHF;;CHF;189.00;;15.11.2017
*/
function UBSFormatCc1() {
// Index of columns in csv file
this.colCount = 13;
this.colCardNo = 2;
this.colDateDoc = 3;
this.colDescr = 4;
this.colCurrency = -4;
this.colDebit = -3;
this.colCredit = -2;
this.colDate = -1;
/** Return true if the transactions match this format */
this.match = function (transactions) {
if (transactions.length === 0) return false;
for (i = 0; i < transactions.length; i++) {
var transaction = transactions[i];
var formatMatched = false;
/* array should have all columns */
if (transaction.length >= this.colCount) formatMatched = true;
else formatMatched = false;
if (
formatMatched &&
transaction[this.colCount + this.colDate] &&
transaction[this.colCount + this.colDate].match(
/^[0-9]+\.[0-9]+\.[0-9]+$/
)
)
formatMatched = true;
else formatMatched = false;
if (formatMatched) return true;
}
return false;
};
/** Convert the transaction to the format to be imported */
this.convert = function (transactions) {
this.colCount =
transactions.length > 1 ? transactions[0].length : this.colCount;
transactions = this.convertTransactions(transactions);
if (transactions.length > 1) {
//Sort by date
if (transactions[0][0] > transactions[transactions.length - 1][0]) {
//Sort transactions
transactions = transactions.reverse();
}
}
var header = [
["Date", "DateDocument", "Doc", "Description", "Income", "Expenses"],
];
return header.concat(transactions);
};
/** Convert the transaction to the format to be imported */
this.convertTransactions = function (transactions) {
var transactionsToImport = [];
/** Complete, filter and map rows */
var lastCompleteTransaction = null;
for (
var i = 0; i < transactions.length; i++ // First row contains the header
) {
var transaction = transactions[i];
if (
transaction.length <= this.colCount + this.colDate ||
!transaction[transaction.length + this.colDate].match(
/^[0-9]+\.[0-9]+\.[0-9]+$/
)
)
continue;
var mappedTransaction = this.mapTransaction(transaction);
transactionsToImport.push(mappedTransaction);
}
return transactionsToImport;
};
this.mapTransaction = function (element) {
var mappedLine = [];
if (
element[element.length + this.colDate] === null ||
element[this.colDescr] === null ||
element[element.length + this.colCredit] === null ||
element[element.length + this.colDebit] === null
) {
mappedLine.push("");
mappedLine.push("");
mappedLine.push("Error importing data");
mappedLine.push("");
mappedLine.push("");
return mappedLine;
}
var descr;
mappedLine.push(
Banana.Converter.toInternalDateFormat(
element[element.length + this.colDate],
"dd.mm.yyyy"
)
);
mappedLine.push("");
mappedLine.push("");
mappedLine.push(this.convertDescription(element[this.colDescr]));
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[element.length + this.colCredit],
"."
)
);
mappedLine.push(
Banana.Converter.toInternalNumberFormat(
element[element.length + this.colDebit],
"."
)
);
return mappedLine;
};
this.convertDescription = function (text) {
var convertedDescr = text.replace(/ +/g, " ");
convertedDescr = convertedDescr.replace(/"/g, '\\"');
return '"' + convertedDescr + '"'; // Banana.Converter.stringToCamelCase(convertedDescr);
};
}
function defineConversionParam(inData) {
var csvData = Banana.Converter.csvToArray(inData);
var header = String(csvData[0]);
var convertionParam = {};
/** SPECIFY THE SEPARATOR AND THE TEXT DELIMITER USED IN THE CSV FILE */
convertionParam.format = "csv"; // available formats are "csv", "html"
//get text delimiter
convertionParam.textDelim = '"';
// get separator
convertionParam.separator = findSeparator(inData);
return convertionParam;
}
/** Sort transactions by date and description */
function sort(transactions, convertionParam) {
if (
transactions.length <= 0 ||
!convertionParam.sortColums ||
convertionParam.sortColums.length <= 0
)
return transactions;
transactions.sort(function (row1, row2) {
for (var i = 0; i < convertionParam.sortColums.length; i++) {
var columnIndex = convertionParam.sortColums[i];
if (row1[columnIndex] > row2[columnIndex]) return 1;
else if (row1[columnIndex] < row2[columnIndex]) return -1;
}
return 0;
});
if (convertionParam.sortDescending) transactions.reverse();
return transactions;
}
function findSeparator(string) {
var commaCount = 0;
var semicolonCount = 0;
var tabCount = 0;
for (var i = 0; i < 1000 && i < string.length; i++) {
var c = string[i];
if (c === ',')
commaCount++;
else if (c === ';')
semicolonCount++;
else if (c === '\t')
tabCount++;
}
if (tabCount > commaCount && tabCount > semicolonCount) {
return '\t';
}
else if (semicolonCount > commaCount) {
return ';';
}
return ',';
}
Input data - csv_ubs_example_format1_20230905_03.csv
Input text passed to the exec() function.
Valuation date,Banking relationship,Portfolio,Product,IBAN,Ccy.,Date from,Date to,Description,Trade date,Booking date,Value date,Description 1,Description 2,Description 3,Transaction no.,Exchange rate in the original amount in settlement currency,Individual amount,Debit,Credit,Balance
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,Secitque dedra,4-12.2023 BVG monthly pa,"ingit, GROBAE COLO, AN DUONE 1017, 600457761231762504643213385, 1561140RG4305540",9930743LK8430013,,,557.50,,4'371.73
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,R-Audideo diambile,,,9930743LK8430013,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,Viscus lis at minitudunto congerate parto,,,9930743LK8430013,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,Coniunt secitque ellura,,,9930743LP7352170,,,1'469.95,,4'929.23
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,DORPUPRA VOLOCULUVIT,,,9930743LP7352170,,-1327.500000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,PLEGENT COR INE,AN SECIBURIA (ALIQUID) 4045,,9930743LP7352170,,-75.000000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.08.2023,31.08.2023,31.08.2023,DITA PATUMERE DE,,,9930743LP7352170,,-67.450000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,30.08.2023,30.08.2023,30.08.2023,d-audideo Volury,OPUGAVIT XXX,"8820 WAEDENSWIL, AAPARTMENT RENTAL FEE SEP 23, Credit / debit advice",9702242TO3295927,,,,3'700.00,6'399.18
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,25.08.2023,25.08.2023,25.08.2023,Creta d-audideo Dedra,TUM,"AN FICA 66 8840, 404800036845402515030151356",5730237TI4790761,,,165.00,,2'699.18
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,25.08.2023,25.08.2023,25.08.2023,R-Audideo diambile,,,5730237TI4790761,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,25.08.2023,25.08.2023,25.08.2023,Viscus lis at minitudunto congerate parto,,,5730237TI4790761,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,24.08.2023,24.08.2023,24.08.2023,Creta d-audideo Dedra,RES,"AN VIT 3534, 803023367811405547285483243",2530236TI4589949,,,1'674.15,,2'864.18
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,24.08.2023,24.08.2023,24.08.2023,R-Audideo diambile,,,2530236TI4589949,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,24.08.2023,24.08.2023,24.08.2023,Viscus lis at minitudunto congerate parto,,,2530236TI4589949,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,21.08.2023,21.08.2023,21.08.2023,Creta d-audideo Dedra,VOLONCENDE FRUM ET,"AN REGRUMEA 5037, 246531233028703100220735546",5530233TI3956784,,,705.30,,4'538.33
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,21.08.2023,21.08.2023,21.08.2023,R-Audideo diambile,,,5530233TI3956784,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,21.08.2023,21.08.2023,21.08.2023,Viscus lis at minitudunto congerate parto,,,5530233TI3956784,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,02.08.2023,02.08.2023,02.08.2023,d-audideo Dedra,"VOLUNDO MANGUN, FLUVIDENT 8,","6318 WALCHWIL, CH, COMPENSATION FROM KSG",9930212TI9569993,,,5'000.00,,5'243.63
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,02.08.2023,02.08.2023,02.08.2023,R-Audideo diambile,,,9930212TI9569993,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,02.08.2023,02.08.2023,02.08.2023,Viscus lis at minitudunto congerate parto,,,9930212TI9569993,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Coniunt secitque ellura,,,9930712LP4015960,,,1'469.95,,10'243.63
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,DORPUPRA VOLOCULUVIT,,,9930712LP4015960,,-1327.500000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,PLEGENT COR INE,AN SECIBURIA (ALIQUID) 4045,,9930712LP4015960,,-75.000000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,DITA PATUMERE DE,,,9930712LP4015960,,-67.450000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Secitque dedra,4-12.2023 BVG MONTHLY PA,"INGIT, GROBAE COLO, AN DUONE 1017, 600457761231762504643213385",9930712LK6453313,,,557.50,,11'713.58
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,R-Audideo diambile,,,9930712LK6453313,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,9930712LK6453313,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Cebitom at perange alurem,,,BJ31418NJ8204828,,,0.00,,12'271.08
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,01.07.2023 - 31.07.2023,,,BJ31418NJ8204828,,,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Centuide PER-mantumn,,,BJ31418NJ8204828,,-0.200000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,BJ31418NJ8204828,,0.200000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,R-Audideo diambile,,,BJ31418NJ8204828,,-0.300000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,BJ31418NJ8204828,,0.300000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Secitque dedra diambile,,,BJ31418NJ8204828,,-0.900000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,Viscus lis at minitudunto congerate parto,,,BJ31418NJ8204828,,0.900000000,,,
30.08.2023,1111 11111111,,1111 11111111.01X,VN16 6883 8648 7677 1877 A,LAM,31.07.2023,31.08.2023,QUI Patumere Cognumn Funtali,31.07.2023,31.07.2023,31.07.2023,d-audideo Volury,OPUGAVIT XXX,"8820 WAEDENSWIL, AAPARTMENT RENTAL FEE AUG 23, Credit / debit advice",9902211TO9260785,,,,3'700.00,12'271.08
,,,,,,,,,,,,,,,,,,,,
Flassus cebitom,Quonent cebitom,,,,,,,,,,,,,,,,,,,
1111.123333,34545.000000007,,,,,,,,,,,,,,,,,,,
Output data - csv_ubs_example_format1_20230905_03.tsv
Output text returned by the exec() function.
Date\tDateValue\tDoc\tExternalReference\tDescription\tIncome\tExpenses\tIsDetail
2023-07-31\t2023-07-31\t\t9902211TO9260785\t\"D-Audideo Volury, Opugavit Xxx, 8820 Waedenswil, Aapartment Rental Fee Aug 23, Credit / Debit Advice\"\t3700.00\t
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Viscus Lis At Minitudunto Congerate Parto\"\t0.900000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Secitque Dedra Diambile\"\t-0.900000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Viscus Lis At Minitudunto Congerate Parto\"\t0.300000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"R-Audideo Diambile\"\t-0.300000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Viscus Lis At Minitudunto Congerate Parto\"\t0.200000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Centuide Per-Mantumn\"\t-0.200000000\t\tD
2023-07-31\t2023-07-31\t\tBJ31418NJ8204828\t\"Cebitom At Perange Alurem\"\t\t0.00\tS
2023-07-31\t2023-07-31\t\t9930712LK6453313\t\"Secitque Dedra, 4-12.2023 Bvg Monthly Pa, Ingit, Grobae Colo, An Duone 1017, 600457761231762504643213385\"\t\t557.50\t
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Dita Patumere De\"\t-67.450000000\t\tD
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Plegent Cor Ine, An Seciburia (Aliquid) 4045\"\t-75.000000000\t\tD
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Dorpupra Voloculuvit\"\t-1327.500000000\t\tD
2023-07-31\t2023-07-31\t\t9930712LP4015960\t\"Coniunt Secitque Ellura\"\t\t1469.95\tS
2023-08-02\t2023-08-02\t\t9930212TI9569993\t\"D-Audideo Dedra, Volundo Mangun, Fluvident 8,, 6318 Walchwil, Ch, Compensation From Ksg\"\t\t5000.00\t
2023-08-21\t2023-08-21\t\t5530233TI3956784\t\"Creta D-Audideo Dedra, Voloncende Frum Et, An Regrumea 5037, 246531233028703100220735546\"\t\t705.30\t
2023-08-24\t2023-08-24\t\t2530236TI4589949\t\"Creta D-Audideo Dedra, Res, An Vit 3534, 803023367811405547285483243\"\t\t1674.15\t
2023-08-25\t2023-08-25\t\t5730237TI4790761\t\"Creta D-Audideo Dedra, Tum, An Fica 66 8840, 404800036845402515030151356\"\t\t165.00\t
2023-08-30\t2023-08-30\t\t9702242TO3295927\t\"D-Audideo Volury, Opugavit Xxx, 8820 Waedenswil, Aapartment Rental Fee Sep 23, Credit / Debit Advice\"\t3700.00\t\t
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Dita Patumere De\"\t-67.450000000\t\tD
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Plegent Cor Ine, An Seciburia (Aliquid) 4045\"\t-75.000000000\t\tD
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Dorpupra Voloculuvit\"\t-1327.500000000\t\tD
2023-08-31\t2023-08-31\t\t9930743LP7352170\t\"Coniunt Secitque Ellura\"\t\t1469.95\tS
2023-08-31\t2023-08-31\t\t9930743LK8430013\t\"Secitque Dedra, 4-12.2023 Bvg Monthly Pa, Ingit, Grobae Colo, An Duone 1017, 600457761231762504643213385, 1561140RG4305540\"\t\t557.50\t
Input data - csv_ubs_example_format2_de_20220928.csv
Input text passed to the exec() function.
Kontonummer:;12345678910;
IBAN:;CH12345678910;
Von:;2022-09-26;
Bis:;2022-09-28;
Anfangssaldo:;1000.00;
Schlusssaldo:;1441.21;
Bewertet in:;CHF;
Anzahl Transaktionen in diesem Zeitraum:;10;
Abschlussdatum;Abschlusszeit;Buchungsdatum;Valutadatum;Währung;Transaktionsbetrag;Belastung/Gutschrift;Saldo;Transaktions-Nr.;Beschreibung1;Beschreibung2;Beschreibung3;Fussnoten;
2022-09-28;16:08:11;;2022-09-28;CHF;100.00;Gutschrift;1100.00;12345678910;"Gutschrift";"Einzahlung Bancomat";;;
2022-09-28;12:48:24;;2022-09-28;CHF;-20.00;Belastung;1000.00;12345678910;"FILIALE";"123456-0 07/24, Bezug Bancomat";;;
2022-09-27;13:32:48;2022-09-29;2022-09-27;CHF;-5.35;Belastung;1005.35;12345678910;"Lidl Viganello";"123456-0 07/24, Zahlung Debitkarte";;;
2022-09-27;16:39:42;2022-09-28;2022-09-27;CHF;-2.30;Belastung;1007.85;12345678910;"TPL Piaz. Mol. Billett L";"123456-0 07/24, Zahlung Debitkarte";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-135.00;Belastung;1142.85;12345678910;"Zahlung ";"Zahlung e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-38.01;Belastung;1180.86;12345678910;"Zahlung ";"e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-35.00;Belastung;1215.86;12345678910;"Zahlung";"Zahlung";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-31.75;Belastung;1306.21;12345678910;"Zahlung ";"e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-58.60;Belastung;1364.81;12345678910;"Sunrise UPC Sagl";"e-banking-Vergutungsauftrag";;;
2022-09-26;;2022-09-26;2022-09-26;CHF;-76.40;Belastung;1441.21;12345678910;"Salt Mobile SA";"e-banking-Vergutungsauftrag";;;
Output data - csv_ubs_example_format2_de_20220928.tsv
Output text returned by the exec() function.
Date\tDateValue\tDescription\tExternalReference\tExpenses\tIncome
2022-09-26\t2022-09-26\tSalt Mobile SA e-banking-Vergutungsauftrag \t12345678910\t76.40\t
2022-09-26\t2022-09-26\tSunrise UPC Sagl e-banking-Vergutungsauftrag \t12345678910\t58.60\t
2022-09-26\t2022-09-26\tZahlung Zahlung e-banking-Vergutungsauftrag \t12345678910\t135.00\t
2022-09-26\t2022-09-26\tZahlung e-banking-Vergutungsauftrag \t12345678910\t31.75\t
2022-09-26\t2022-09-26\tZahlung e-banking-Vergutungsauftrag \t12345678910\t38.01\t
2022-09-26\t2022-09-26\tZahlung Zahlung \t12345678910\t35.00\t
2022-09-27\t2022-09-27\tLidl Viganello 123456-0 07/24, Zahlung Debitkarte \t12345678910\t5.35\t
2022-09-27\t2022-09-27\tTPL Piaz. Mol. Billett L 123456-0 07/24, Zahlung Debitkarte \t12345678910\t2.30\t
2022-09-28\t2022-09-28\tFILIALE 123456-0 07/24, Bezug Bancomat \t12345678910\t20.00\t
2022-09-28\t2022-09-28\tGutschrift Einzahlung Bancomat \t12345678910\t\t100.00
Input data - csv_ubs_example_format3_it_triplequotes_20240621.csv
Input text passed to the exec() function.
Numero di conto:;0000 00000800.00;;;;;;;;;;;;;
IBAN:;CH00 0000 0000 0000 0000 N;;;;;;;;;;;;;
Dal:;;;;;;;;;;;;;;
Al:;;;;;;;;;;;;;;
Saldo iniziale:;1117.58;;;;;;;;;;;;;
Saldo finale:;22.97;;;;;;;;;;;;;
Valutazione in:;CHF;;;;;;;;;;;;;
Numero di transazioni in questo periodo:;6;;;;;;;;;;;;;
Data dell'operazione;Ora dell'operazione;Data di registrazione;Data di valuta;Moneta;Addebito;Accredito;Importo singolo;Saldo;N. di transazione;Descrizione1;Descrizione2;Descrizione3;Note a piè di pagina;
2024-06-19;12:48:15;;2024-06-19;CHF;-20.00;;;22.97;11111111;"""FILIALE;VIGANELLO""";"""11111111-0 07/24; Prelevamento Bancomat""";No di transazioni: 111111;;
2024-06-19;;2024-06-19;2024-06-19;CHF;-450.00;;;42.97;11111112;"""Descrizione1""";"""Descrizione2; Descrizione2""";"""Descrizione3""";;
2024-06-17;17:19:57;2024-06-19;2024-06-17;CHF;-15.40;;;492.97;11111113;"""Descrizione1;6962 Viganello""";"""11111111-0 07/24; Pagamento carta di debito""";No di transazioni: 11111112334;;
2024-06-17;17:31:13;2024-06-18;2024-06-17;CHF;-5.20;;;508.37;11111114;"""Descrizione1;6900 Lugano""";"""11111111-0 07/24; Pagamento carta di debito""";No di transazioni: 11234534553;;
2024-06-18;;2024-06-18;2024-06-18;CHF;;200.00;;513.57;11111115;"""Descrizione1;6900 Lugano""";Accredito;No di transazioni: 12678345643;;
2024-05-31;17:23:44;2024-06-03;2024-05-31;CHF;-20.00;;;1117.58;11111116;BR Lugano;"""111111111-0 07/24; Prelevamento Bancomat""";"""Spese: Prelevamento di contanti al Bancomat di banche terze/Postomat in Svizzera; No di transazioni: 1268923683""";;
Output data - csv_ubs_example_format3_it_triplequotes_20240621.tsv
Output text returned by the exec() function.
Date\tDateValue\tDescription\tExternalReference\tExpenses\tIncome
2024-05-31\t2024-05-31\tBR Lugano 111111111-0 07/24; Prelevamento Bancomat\t11111116\t20.00\t
2024-06-17\t2024-06-17\tDescrizione1;6900 Lugano 11111111-0 07/24; Pagamento carta di debito\t11111114\t5.20\t
2024-06-17\t2024-06-17\tDescrizione1;6962 Viganello 11111111-0 07/24; Pagamento carta di debito\t11111113\t15.40\t
2024-06-18\t2024-06-18\tDescrizione1;6900 Lugano Accredito\t11111115\t\t200.00
2024-06-19\t2024-06-19\tDescrizione1 Descrizione2; Descrizione2\t11111112\t450.00\t
2024-06-19\t2024-06-19\tFILIALE;VIGANELLO 11111111-0 07/24; Prelevamento Bancomat\t11111111\t20.00\t
Export
Export Tutorial3 javascript codes, csv and tsv to HTML
// Banana Accounting Extension Javascript
// @id = ch.banana.apps.exporthtmljavascriptcodestutorial3.js
// @api = 1.0
// @publisher = Banana.ch SA
// @description = Tutorial: Export javascript codes, csv and tsv of the Tutorial 3 examples in HTML
// @task = export.file
// @doctype = *.*
// @docproperties =
// @timeout = -1
// @exportfiletype = html
// @visibility = never
//Main function
function exec() {
//Check if we are on an opened document
if (!Banana.document) {
return;
}
//Take the table Documents
var documents = Banana.document.table('Documents');
//We check if the table Documents exists, then we can take all the codes
//If the table Documents doesn't exists, then we stop the script execution
if (!documents) {
return;
} else {
//Variable to save the html text
var htmlText = '';
htmlText += '<p>On this page we show some examples of transactions import extensions.</p>\n';
htmlText += '<p>Each example consists of:</p>\n';
htmlText += '<ul>\n';
htmlText += '<li>The complete <strong>JavaScript code of the import extension</strong>.</li>\n';
htmlText += '<li>The <strong>.CSV</strong> files in different formats with the transactions data to be imported.<br>Some examples contains <strong>anonymized data</strong>, the description text has no meanings. </li>\n';
htmlText += '<li>The output in <strong>.TSV</strong> format (Tabulator Separated Values) of the import extension, which contains the transformed csv data in the “transactions.simple” format used to import the transactions in Banana Accounting.</li>\n';
htmlText += '</ul>\n';
//Get all the tutorial javascript codes
htmlText = getJavascriptCode(htmlText, documents);
}
return htmlText;
}
//Function that gets the javascript code of each tutorial example and save it into the htmlText.
function getJavascriptCode(htmlText, documents) {
//Read row by row the table Documents
var len = documents.rowCount;
for (var i = 0; i < len; i++) {
//Create a variable for the row
var tRow = documents.row(i);
//We get some values
var fName = Banana.document.info("Base", "FileName").replace(/^.*[\\\/]/, ''); //the file name (without path)
var section = tRow.value("Section"); // the section column used to define the title type
var id = tRow.value("RowId"); //the id of the example
var description = tRow.value("Description"); //the description of the example
var attachments = tRow.value("Attachments"); //the javascript code of the example
attachments = attachments.replace(/</g,'<'); // replace the < symbol
attachments = attachments.replace(/>/g,'>'); // replace the > symbol
//For tilte h2 we consider only rows with section h2
if (section && section === "h2") {
htmlText += '<h2>' + description + '</h2>\n';
}
//For js codes we consider only rows with section h3, id, description and attachment
else if (section && section === "h3" && id && description && attachments) {
htmlText += '<h3>' + description + '</h3>\n';
htmlText += '<pre><code class="language-javascript">\n';
htmlText += '// Banana Accounting Extension Javascript' + '\n';
htmlText += attachments;
htmlText += '\n';
htmlText += '</code></pre>\n';
htmlText += getCsvAndTsv(id, documents); // adds csv and tsv
}
}
//Returns the html text which contains titles, javascript codes and csv examples
return htmlText;
}
//Function that gets the Csv with the bank movements and the Tsv (tabulator separated values).
//The Tsv contains the Csv data after being processed by the import extension.
//The Tsv has a specific format used by Banana Accounting to import transactions.
function getCsvAndTsv(jsCodeId, documents) {
//Variable to save the csv and tsv (tabulator separated values)
var text = '';
//Remove '.js' from the jsCodeId string, we use the id number to get the tsv
jsCodeId = jsCodeId.replace(/\.js$/, "");
//Read row by row the table Documents
var len = documents.rowCount;
for (var i = 0; i < len; i++) {
//Create a variable for the row
var tRow = documents.row(i);
//We get some values
var section = tRow.value("Section"); // the section column used to define the title type
var id = tRow.value("RowId"); //the id of the example
var description = tRow.value("Description"); //the description of the example
var attachments = tRow.value("Attachments"); //the csv/tsv text of the example
//For the csv/tsv text we consider only rows with section h4, id that starts with the same id of the import extension, description and attachment
if (section === "h4" && id.startsWith(jsCodeId) && description && attachments) {
var strtype = '';
var strIntro = '';
if (id.indexOf(".csv") > -1) {
strtype = "Input data - ";
strIntro ="Input text passed to the exec() function.";
} else if (id.indexOf(".tsv") > -1) {
strtype = "Output data - ";
strIntro ="Output text returned by the exec() function.";
}
text += '<h4>' + strtype + description + '</h4>\n';
text += '<p>'+ strIntro + '</p>\n';
text += '<pre><code class="language-plaintext">\n';
text += attachments;
text += '\n';
text += '</code></pre>\n';
}
}
return text;
}
Working with XML
Introduction
This page contains some code examples of how to read, write and more in general how to work with XML files in Banana.
All the code reported above as example can be found in the following file: embedded_javascript_tutorial1.ac2
XML object
XML files are used to save a large amount of data in an organized way.
Using the Banana APIs is possible to retrieve data from XML files and also create new XML files.
Read XML
In order to read an XML string we have to follow the following steps:
- Create the XML parse object
- Take the root of the XML
- Find the data we want to retrieve from the file using firstChildElement([text]).
- Iterate all over the file as long as we always have something from step 3.
Read XML Code Sample: Simple XML
var xml = '<Library updated="2018-09-03">' + // To simplify,
'<Book>' + // the variable
'<Title>Paths of colours</Title>' + // xml contains
'<Author>Rosa Indaco</Author>' + // the xml string
'</Book>' +
'<Book>' +
'<Title>Accounting exercises</Title>' +
'<Author>Su Zhang</Author>' +
'</Book>' +
'</Library>';
var bookList = ""; // Create string for the output
var xmlFile = Banana.Xml.parse(xml); // Create XML Parse object
var xmlRoot = xmlFile.firstChildElement('Library'); // Find the first tag "Library" in the XML
var updateDate = xmlRoot.attribute('updated'); // Take attribute assigned to the tag
bookList += "Books in the library on " + updateDate + "\n\n"; // Append to the output string
var bookNode = xmlRoot.firstChildElement('Book'); // Take the first Book
while (bookNode) { // As long as there are books, repeat
var title = bookNode.firstChildElement('Title').text; // Find the first tag "Title" in the XML
var authorNode = bookNode.firstChildElement('Author'); // Find the first tag "Author" in the XML
var author = authorNode ? authorNode.text : 'unknow'; // Check whether there is a tag "Author"
bookList += title + " - " + author + "\n"; // Append to the output string
bookNode = bookNode.nextSiblingElement('Book'); // Go to the next book
}
Banana.Ui.showText("List of books present in the xml file", bookList); // Output the results
Output:
Write XML
In order to write and create an XML file we have to follow the following steps:
- Create the XML document object.
- Create a root tag for the document using newDocument([text]).
- Add new tag using addElement([text]).
- Add text for the tag with addTextNode([text]).
Write XML Code Sample: Simple XML
var xmlDocument = Banana.Xml.newDocument("Library"); // Create XML file
xmlDocument.addComment("This is the generated xml file"); // Add comment to the file
var rootNode = xmlDocument.addElement("Library"); // Set root tag "Library"
rootNode.setAttribute("updated", "2018-09-03"); // Set attribute to the tag "Library"
var bookNode = rootNode.addElement("Book"); // Create tag "Book" child of "Library"
bookNode.addElement("Title").addTextNode("Paths of colours"); // Create tag "Title" child of "Book"
bookNode.addElement("Author").addTextNode("Rosa Indaco"); // Create tag "Author" child of "Book"
var bookNode = rootNode.addElement("Book"); // Create tag "Book" child of "Library"
bookNode.addElement("Title").addTextNode("Accounting exercises"); // Create tag "Title" child of "Book"
bookNode.addElement("Author").addTextNode("Su Zhang"); // Create tag "Author" child of "Book"
var xmlString = Banana.Xml.save(xmlDocument); // Create output
Banana.Ui.showText("Xml file", xmlString); // Generate output
Output:
Validate
Is possible to validate if a file is in fact an XML which contains tag with the validate() function.
var xmlFile = Banana.Xml.parse(xml); // Create the XML file
var valid = Banana.Xml.validate(xmlFile, 'documents:xsd'); // Validate the XML
if (valid) {
Banana.Ui.showInformation('Validation result', 'The xml is valid');
} else {
Banana.Ui.showInformation('Validation result', 'The xml is not valid: ' + Banana.Xml.errorString);
}
Output:
General examples with XML
In this section we are going to provide some examples of how to use API functions for XML files, here below there is the XML file used for the first example:
<solarSystem updated="2020-03-27">
<Planet>
<name>Sun</name>
<sizeOrder>1</sizeOrder>
</Planet>
<Planet>
<name>Earth</name>
<sizeOrder>6</sizeOrder>
<satellite>Moon</satellite>
</Planet>
<Planet>
<name>Jupiter</name>
<sizeOrder>2</sizeOrder>
<satellite>Callisto</satellite>
</Planet>
<Planet>
<name>Saturn</name>
<sizeOrder>3</sizeOrder>
<satellite>Cronus</satellite>
</Planet>
<Planet>
<name>Mars</name>
<sizeOrder>8</sizeOrder>
<satellite>Phobos</satellite>
</Planet>
<Planet>
<name>Neptune</name>
<sizeOrder>5</sizeOrder>
<satellite>Triton</satellite>
</Planet>
<Planet>
<name>Mercury</name>
<sizeOrder>9</sizeOrder>
</Planet>
<Planet>
<name>Venus</name>
<sizeOrder>7</sizeOrder>
</Planet>
<Planet>
<name>Uranus</name>
<sizeOrder>4</sizeOrder>
<satellite>Ariel</satellite>
</Planet>
</solarSystem>
Check if a tag has a specific Child tag
the following is a code example of how to return and print only planets with a satellite, the other will be excluded, therefore will be used the function hasChildElements([text]).
var outputString = "";
var xmlFile = Banana.Xml.parse(xml);
var xmlRoot = xmlFile.firstChildElement('solarSystem');
var updateDate = xmlRoot.attribute('updated');
outputString += "Planets in the solar System with satellites updated on " + updateDate + "\n\n";
var planet = xmlRoot.firstChildElement('Planet'); // Take the first tag "Planet"
while (planet) { // As long as we have planets
if (planet.hasChildElements('satellite')) { // Check if the planet has tag "satellite"
var planetName = planet.firstChildElement('name').text; // Save the planetName
var planetSatellite = planet.firstChildElement('satellite').text; // Save the satelliteName
outputString += "name: "+planetName+", satellite: "+planetSatellite+"\n";
}
planet = planet.nextSiblingElement('Planet'); // Move to the next planet
}
Banana.Ui.showText("Planets in the XML with satellites", outputString); // Show the output
Output:
Export Transactions in XML format
Here there is an example of how to take various fields from the table Transactions in one of our .ac2 files and then print them in an xml files.
Naturally it is possible to change various parameter as for example the table we want to take the data. But also the number and type of fields we want to print in our file, in order to do so we have to follow the following steps:
- Decide which table we want to retrieve informations.
- Remember to handle the error for which the file doesn't have the desired table.
- Loop through the entire table taking only the desired fields.
- Decide the indentation for the file by appending to the strings special characters like '\t' (tab) or '\n' (new line).
// @id = ch.banana.apps.export
// @api = 1.0
// @pubdate = 2016-04-08
// @doctype = *.*
// @description = Export into an xml file (.xml)
// @task = export.file
// @exportfiletype = xml
// @timeout = -1
function exec() {
var xmlDocument = Banana.Xml.newDocument("Transactions");
xmlDocument.addComment("This is the generated xml file");
var rootNode = xmlDocument.addElement("transactions");
var tableTransactions = Banana.document.table('Transactions');
if (!tableTransactions) {
return;
}
for (i=0;i<tabletransactions.rowcount;i++) {
if (tableTransactions.row(i).value('Amount')) {
var transactionNode = rootNode.addElement("transaction");
transactionNode.addElement("date").addTextNode(tableTransactions.row(i).value('Date'));
transactionNode.addElement("description").addTextNode(tableTransactions.row(i).value('Description'));
transactionNode.addElement("amount").addTextNode(tableTransactions.row(i).value('Amount'));
}
}
var xmlString = Banana.Xml.save(xmlDocument);
Banana.Ui.showText("Xml file", xmlString);
return xmlString;
The code above and his explanation are available also here.
Import transactions from XML
The following is an example of how to add new transactions to a .ac2 file in Banana, below is possible to see the XML file used as example:
<transactions>
<transaction>
<date>2020-01-01</date>
<description>Red car sale</description>
<income>10000.50</income>
<expenses></expenses>
</transaction>
<transaction>
<date>2020-03-15</date>
<description>ATM withdrawal</description>
<income></income>
<expenses>600.00</expenses>
</transaction>
<transaction>
<date>2020-02-14</date>
<description>Bought silver ring</description>
<income></income>
<expenses>2000.00</expenses>
</transaction>
<transaction>
<date>2020-04-01</date>
<description>Yellow rubber duck sale</description>
<income>4200.00</income>
<expenses></expenses>
</transaction>
</transactions>
We can eventually modify the XML file above by adding new fields for our transactions and obviously add new transactions to the file, but in order to transfer data from the file into Banana we have to follow these instructions:
- We are going to transfer the data from XML to CSV and then from CSV to TSV which are Tab separated files.
- Create the CSV string we are going to append data from our file.
- As we did before in the Read XML example we have to iterate through the XML file we take in input and then append the values of the tags into our CSV string, this time we have to remember to format the string by adding special character like '\n' (new line), ',' (comma), ''' (apostrophe) or '"' (quotation mark).
- once this process is complete and we have our CSV String ready we have to convert it into an Array using csvToArray(string, [, separator, textdelim]) function and once again convert the resulting array into a TSV using arrayToTSV(table, [, defaultChar]) and return the TSV.
// @api = 1.0
// @pubdate = 2020-04-02
// @id = ch.banana.uni.app.tutorialretrieverowstablevalues
// @description = Import transactions
// @task = import.transactions
// @outputformat = transactions.simple
// @doctype = *
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @inputfilefilter = Text files (*.xml);;All files (*.*)
// @publisher = Banana.ch SA
// @timeout = -1
function exec(inputFile) {
var CSV_String = "Date" + '","' + "Description" + '","' + "Income" + '","' + "Expenses" + '\n';
var xmlFile = Banana.Xml.parse(inputFile);
var xmlRoot = xmlFile.firstChildElement('transactions');
var transactionNode = xmlRoot.firstChildElement('transaction');
while (transactionNode) {
var date = transactionNode.firstChildElement('date').text;
var description = transactionNode.firstChildElement('description').text;
var income = transactionNode.firstChildElement('income').text;
var expenses = transactionNode.firstChildElement('expenses').text;
CSV_String += ('"' + date + '","' + description + '","' + income + '","' + expenses + '"' + '\n');
transactionNode = transactionNode.nextSiblingElement('transaction');
}
var csvFile = Banana.Converter.csvToArray(CSV_String, ',', '"');
var tsvFile = Banana.Converter.arrayToTsv(csvFile);
return tsvFile;
}