I also think that a customer statement is a must !
Please answer to the topics at least !
regards
I also think that a customer statement is a must !
Please answer to the topics at least !
regards
The best thing you can do is to write a detailed analysis of your needs, then maybe ask one of the numerous companies working around dolibarr to develop the functionality.
That’s the best way to see your needs fulfilled and integrated into the software strategy.
Otherwise, wait for a nice developer to take care freely of your needs, or another customer to finance its development.
Someone said “free software is free once it have been paid” or something like that.
When you use a software like dolibarr, you have it for free but other companies have already financed its evolutions, so if you do need some improvements, feel free to finance them
I have finally created a Customer statements by generating the values of all the data from the selected invoices and generating it in to one pdf instead of just merging all the pdfs
It took a long and lenghty process but finally achieved it. Will soon post the code
Anyone have a customer statement yet?
So where did you get that little button at the top that say (Reports)?
Has anyone tried this?
I installed an addon called mylist from dollistore but it’s Menu text was patastools so I changed it to reports from here:
Home->Setup–>Menu–>Menu Editor
Hi Alvin,
I tried the Reportico addon without success. I can get the module running:
But then when I run the project and clicking on Go throws the following error:
Where did you get that Reports’ module from?
Hi jamshaidalipak,
I used a module myList from Dolistore and then I changed menu to Reports after installing it. Home->Setup–>Menu–>Menu Editor
Hi ariffidaali,
Can you share the code you used with myList to get the statement ?
Hi Kalsedia,
This is the xml code I used for the customer statement list in myList:
Omit warehouse as that is a custom field in the table that I created
[code]
<?xml version='1.0' encoding='ISO-8859-1'?>Invoice Status
Invoice Status
accountancy
false
0
<model_pdf>-1</model_pdf>
from custstat c
ORDER BY datec desc
I first created this View called custat in the mysql database:
SELECT DISTINCT
f.facnumber AS Inv_No,
f.datef AS Inv_Date,
f.datec,
UCASE(s.nom) AS Customer_Name,
f.ref_client as Customer_Ref,
f.total_ttc AS Inv_Amount,
p.datep AS Payment_date,
p.ref AS Payment_Ref,
sum(coalesce(pf.amount, " ")) AS Paid_Amount,
f.total_ttc - sum(coalesce(pf.amount, " ")) AS Balance,
CASE
/* WHEN f.paye = 1 AND p.datep IS NOT NULL and cp.id = 4 then 'Paid by Cash'
WHEN f.paye = 1 AND p.datep IS NOT NULL and cp.id = 2 then 'Paid by Bank Transfer'
WHEN f.paye = 1 AND p.datep IS NOT NULL and cp.id = 3 then 'Paid by Credit Card'
WHEN f.paye = 1 AND p.datep IS NOT NULL and cp.id = 7 then 'Paid by Check'
WHEN f.paye = 0 AND p.datep IS NOT NULL and cp.id = 4 then 'Partial Payment by Cash'
WHEN f.paye = 0 AND p.datep IS NOT NULL and cp.id = 2 then 'Partial Payment by Bank Transfer'
WHEN f.paye = 0 AND p.datep IS NOT NULL and cp.id = 3 then 'Partial Payment by Credit Card'
WHEN f.paye = 0 AND p.datep IS NOT NULL and cp.id = 7 then 'Partial Payment by Check' */
WHEN f.paye = 1 AND p.datep IS NOT NULL and p.ref is not null then 'Paid'
WHEN f.paye = 1 AND p.datep IS NULL and f.total_ttc = 0 then 'Free of Cost'
WHEN f.paye = 0 AND p.datep IS NULL THEN 'Not paid'
WHEN f.paye = 0 AND p.datep IS NOT NULL THEN 'Partial Payment'
END AS 'Payment_Status',
CASE
WHEN cp.id = 4 THEN COALESCE(CONCAT(cp.libelle,' ', p.num_paiement), "-")
WHEN p.num_paiement = '' AND cp.id <> 4 THEN COALESCE(CONCAT(cp.libelle,' ', p.num_paiement), "-")
ELSE
COALESCE(CONCAT(cp.libelle,' ', 'No.', p.num_paiement), "-")
END AS 'Payment_No',
uc.login as user,
extra.warehouse as warehouse
FROM
llx_societe AS s
LEFT JOIN llx_c_country AS c ON s.fk_pays = c.rowid,
llx_facture AS f
LEFT JOIN llx_projet AS pj ON f.fk_projet = pj.rowid
LEFT JOIN llx_user AS uc ON f.fk_user_author = uc.rowid
LEFT JOIN llx_user AS uv ON f.fk_user_valid = uv.rowid
LEFT JOIN llx_facture_extrafields AS extra ON f.rowid = extra.fk_object
LEFT JOIN llx_paiement_facture AS pf ON pf.fk_facture = f.rowid
LEFT JOIN llx_paiement AS p ON pf.fk_paiement = p.rowid
LEFT JOIN llx_c_paiement AS pt ON pt.id = p.fk_paiement
LEFT JOIN llx_c_paiement AS cp ON cp.id = p.fk_paiement
LEFT JOIN llx_bank AS b ON b.rowid = p.fk_bank
LEFT JOIN llx_bank_account AS ba ON ba.rowid = b.fk_account
WHERE
f.fk_soc = s.rowid AND f.entity IN(1) AND f.facnumber NOT LIKE '%PROV%' and f.facnumber not like '%CN%'
GROUP BY f.facnumber
ORDER BY
s.nom,
f.datef DESC
Thank you ariffidaali,
I’ll test it and get back to you.
Hi, arif
Thanks for example.
Q. where did you created or inserted this table view?
Q. Can this list be exported to PDF or ODT template? as export csv is scrambled
Q. Any idea how to get totals at the end of page?
Thank.
Hi,
Q. where did you created or inserted this table view? Using an addon called mylist: myList addon
Q. Can this list be exported to PDF or ODT template? as export csv is scrambled: the CSV must be opened in MS Excel or using Libreoffice is easier. In MS Excel do not double click to open file use file open to open the file then a wizard will guide you.
Q. Any idea how to get totals at the end of page? yes there are totals at end of page (there is a procedure to do that)
I am using mylist free version is that different from addon you mentioned?
What is the procedure to have total at eop and can we compute 2 fields to result in another.
eg.
unpaid amount - paid amount = balance
Hi Nazeer,
The link I have provided is the paid version. The free version has some limitations like eg it can’t export to pdf but other than that its the same.
Yes you can have total at eop and can compute 2 fields to result in another but you need SQL knowledge:
Hello.
I am new into dolibarr, and I need exactly same statement. Is there anyone who can make it for me and sell me the file so I can upload it or … Please let me know.
THANK YOU VERY MUCH.