-->
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: Producing a customer Statement

Producing a customer Statement 5 months 2 weeks ago #36763

Hi ariffidaali,
Can you share the code you used with myList to get the statement ?
The administrator has disabled public write access.

Producing a customer Statement 5 months 2 weeks ago #36773

  • ariffidaali
  • ariffidaali's Avatar
  • OFFLINE
  • Master
  • Just another Dolibarian!
  • Posts: 350
  • Thank you received: 65
  • Karma: 15
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
<?xml version='1.0' encoding='ISO-8859-1'?><mylist>
<label>Invoice Status</label>
<titlemenu>Invoice Status</titlemenu>
<mainmenu>accountancy</mainmenu>
<leftmenu></leftmenu>
<elementtab></elementtab>
<perms></perms>
<datatable>false</datatable>
<langs></langs>
<export>0</export>
<model_pdf>-1</model_pdf>
<author></author>
<querylist>
from custstat c
ORDER BY datec desc
</querylist>
<fieldinit>

</fieldinit>
<querydo>

</querydo>
<fields>
	<field >
	 	<name>Date Created</name>
	 	<field>datec</field>
	 	<alias>Date_Created</alias>
	 	<type>Date</type>
	 	<pos>1</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Invoice Date</name>
	 	<field>Inv_Date</field>
	 	<alias></alias>
	 	<type>Date</type>
	 	<pos>2</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Invoice No.</name>
	 	<field>Inv_No</field>
	 	<alias></alias>
	 	<type>Text</type>
	 	<pos>3</pos>
	 	<param>Facture:/compta/facture/class/facture.class.php:facture:facnumber</param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport></sumreport>
	 	<avgreport></avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf></widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Customer</name>
	 	<field>Customer_Name</field>
	 	<alias></alias>
	 	<type>Text</type>
	 	<pos>4</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Customer Ref </name>
	 	<field>Customer_Ref</field>
	 	<alias></alias>
	 	<type>Text</type>
	 	<pos>5</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Invoice Amount</name>
	 	<field>Inv_Amount</field>
	 	<alias></alias>
	 	<type>Number</type>
	 	<pos>6</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Paid Amount</name>
	 	<field>Paid_Amount</field>
	 	<alias></alias>
	 	<type>Number</type>
	 	<pos>7</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Balance</name>
	 	<field>Balance</field>
	 	<alias></alias>
	 	<type>Number</type>
	 	<pos>8</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Payment Status</name>
	 	<field>Payment_Status</field>
	 	<alias></alias>
	 	<type>Text</type>
	 	<pos>9</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>By</name>
	 	<field>user</field>
	 	<alias></alias>
	 	<type>Text</type>
	 	<pos>10</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport>0</sumreport>
	 	<avgreport>0</avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>100</width>
	 	<widthpdf>0</widthpdf>
	 	<filterinit></filterinit>
	</field>
	<field >
	 	<name>Warehouse</name>
	 	<field>warehouse</field>
	 	<alias>Warehouse</alias>
	 	<type>Text</type>
	 	<pos>11</pos>
	 	<param></param>
	 	<align>left</align>
	 	<enabled>1</enabled>
	 	<sumreport></sumreport>
	 	<avgreport></avgreport>
	 	<visible>1</visible>
	 	<filter>1</filter>
	 	<width>15</width>
	 	<widthpdf></widthpdf>
	 	<filterinit></filterinit>
	</field>
</fields>
</mylist>


editsettingmylist.png
Last Edit: 5 months 2 weeks ago by ariffidaali.
The administrator has disabled public write access.

Producing a customer Statement 5 months 2 weeks ago #36774

  • ariffidaali
  • ariffidaali's Avatar
  • OFFLINE
  • Master
  • Just another Dolibarian!
  • Posts: 350
  • Thank you received: 65
  • Karma: 15
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


CustomerstatementorInvoicestatusmyList.png
Last Edit: 5 months 2 weeks ago by ariffidaali.
The administrator has disabled public write access.

Producing a customer Statement 5 months 3 days ago #36951

Thank you ariffidaali,

I'll test it and get back to you.
The administrator has disabled public write access.

Producing a customer Statement 3 months 4 weeks ago #37281

  • nazeer
  • nazeer's Avatar
  • OFFLINE
  • Expert
  • admin@cynergy.solutions
  • Posts: 143
  • Thank you received: 15
  • Karma: 5
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.
Last Edit: 3 months 4 weeks ago by nazeer.
The administrator has disabled public write access.

Producing a customer Statement 3 months 4 weeks ago #37285

  • ariffidaali
  • ariffidaali's Avatar
  • OFFLINE
  • Master
  • Just another Dolibarian!
  • Posts: 350
  • Thank you received: 65
  • Karma: 15
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)
The administrator has disabled public write access.

Producing a customer Statement 3 months 4 weeks ago #37298

  • nazeer
  • nazeer's Avatar
  • OFFLINE
  • Expert
  • admin@cynergy.solutions
  • Posts: 143
  • Thank you received: 15
  • Karma: 5
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
The administrator has disabled public write access.

Producing a customer Statement 3 months 4 weeks ago #37320

  • ariffidaali
  • ariffidaali's Avatar
  • OFFLINE
  • Master
  • Just another Dolibarian!
  • Posts: 350
  • Thank you received: 65
  • Karma: 15
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:

mylist.png
Last Edit: 3 months 4 weeks ago by ariffidaali.
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Time to create page: 0.090 seconds