Bank account and payment of invoices

Using my Dolibarr
1

Hi,

I just managed to have automatically imported any bank transaction inside the table llx_bank . So I don’t need any manual intervention.
What I don not understant is how may I characterize a bank movement as being done by a specific client or even for a specfic invoice.

From what I see there is the possibility to input a payment related to an invoice.

How to I do the other way around ?

2

From what I know, I don’t think you can do that although that would be a nice feature.

3

Kindly share how you managed to set up this. I would love to have this done

4

My internet banking may be programmed to send me a daily e-mail including an attachment that is a zip file having a password and all the transactions of the day.
I created a special e-mail used only for receiving those statements.

As I have a VPS server, I programmed postfix (I use webmin) to automatically execute a script on any received e-mail on that email address.
The script file that does that contains:

cd /home/bankstatements/unpack
rm -r /home/bankstatements/unpack/*
echo “start” > start.txt
munpack
unzip -P mypassword Statement.zip
echo “end” > end.txt
chmod a+r *
mysql --user root --password=mysqlpassword --host localhost --database dolibarr < …/addStatementLines.sql

The file addStatementLines.sql contains the following

LOAD DATA INFILE ‘/home/bankstatements/unpack/AccountStatement.txt’
IGNORE INTO TABLE BankTransactions
(@l)
SET
AccountNumber=mid(@l,1,13),
#InputDate=mid(@l,15,10),
InputDate=STR_TO_DATE(mid(@l,15,10),’%Y/%m/%d’),
#ValueDate=mid(@l,26,10),
ValueDate=STR_TO_DATE(mid(@l,26,10),’%Y/%m/%d’),
Amount=REPLACE(mid(@l,37,19),’,’,’.’),
Currency=mid(@l,57,8),
Code=mid(@l,66,5),
Reason=mid(@l,71,35),
Branch=mid(@l,107,6),
Type=mid(@l,114,6),
Reference=mid(@l,121,20),
nl1=mid(@l,142,35),
nl2=mid(@l,178,35),
nl3=mid(@l,214,35),
nl4=mid(@l,250,35),
UniqueId=mid(@l,286,22),
Balance=REPLACE(mid(@l,309,22),’,’,’.’)
;

delete from BankTransactions WHERE not UniqueId REGEXP ‘[0-9]+’;

INSERT IGNORE INTO llx_bank(fk_account,dateo,datev,datec,tms,label,fk_type,num_releve,amount)
SELECT
llx_bank_account.rowid AS fk_account,
InputDate as dateo,ValueDate datev, now() as datec,now() as tms,
TRIM(CONCAT_WS(’ ‘,TRIM(REPLACE(REPLACE(REPLACE(nl1,’ ‘,’ ‘),’ ‘,’ ‘),’ ‘,’ ')),TRIM(nl2),TRIM(nl3),TRIM(nl4))) as label,
code as fk_type,
uniqueid as num_releve,
amount
FROM llx_bank_account inner join BankTransactions
on llx_bank_account.number=accountnumber ;

INSERT IGNORE INTO llx_c_paiement(id,code,libelle,type)
select code,code,reason,2 from BankTransactions group by code;

I don’t know if that helps … but at least may give ideas …

Regards

1 Like
5

Guys how do I add user details to be printed automatically on customer invoices, shipper invoices and all invoices. Provide me the idea. Thank you in advance