ODT Template Tag for Commercial Proposal Reference in Invoice PDF

Using my Dolibarr CRM/SRM Modules
,
1

Hello everyone,

I’m trying to generate invoices using an ODT template in Dolibarr, and I need to include the reference of the commercial proposal that the invoice originated from. With the default sponge template, I see that it automatically adds a line like:

Référence de la proposition commerciale : PR2409-0001 / 20/09/2024

However, when creating my own ODT template, I can’t seem to find the correct tag to include this reference. I’ve already tried all the available tags listed on the Dolibarr Wiki for ODT templates, but none seem to produce this information in the generated invoice.

I noticed that other users have asked this question in the past, but unfortunately, there were no answers:

Has anyone managed to find the correct tag or a workaround to include the commercial proposal reference and date in the ODT invoices?

Thanks in advance!

2

Hi,

there is imho no tag for this.
But you can extend the source to get the details from the proposal.
The connection between the invoice and the proposal are stored in llx_element_element.

  • You can query like this:
SELECT e.rowid, e.fk_source, c.ref, c.date_valid FROM llx_element_element e 
LEFT JOIN llx_commande c ON e.fk_source = c.rowid
WHERE e.fk_target = "'.$object->id.'" AND e.sourcetype = 'commande' AND e.targettype = 'facture';
  • extend “doc_generic_invoice_odt.modules.php” with:
	public function fetch_ref_propal($id)
	{
		// Check parameters
		if (empty($id)) {
			return -1;
		}

		$sql = 'SELECT e.fk_source, c.ref, c.date_valid';
		$sql .= ' FROM '.MAIN_DB_PREFIX.'element_element as e';
		$sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.'commande as c ON e.fk_source = c.rowid';
		$sql .= " WHERE e.fk_target ='".((int) $id)."'";
		$sql .= " AND e.sourcetype='commande'";
		$sql .= " AND e.targettype='facture'";

		dol_syslog(get_class($this)."::fetch_ref_propal", LOG_DEBUG);
		$result = $this->db->query($sql);
		if ($result) {
			$obj = $this->db->fetch_object($result);
			if ($obj) {
				$this->propal_id = $obj->rowid;
				$this->propal_ref = $obj->ref;
				$this->propal_date_valid = $obj->date_valid;
				$this->db->free($result);
				return 1;
			} else {
				$this->error = 'Propal linked to id '.$id.' not found sql='.$sql;
				return 0;
			}
		} else {
			$this->error = $this->db->error();
			return -1;
		}
	}

  • and add in function “write_file” right before
    $tmparray = array_merge($substitutionarray, array_object_from_properties,....);
    the following code:
    $this->fetch_ref_propal($object->id,'commande','facture'); $array_objet['ref_propale'] = $this->propal_ref; $array_objet['date_valid_propale'] = $this->propal_date_valid;

  • then you can use “ref_propale” and “date_valid_propale” in your odt template

2 Likes
3

Hey DG-Rilling,

Thanks for the recommandation, I will try to test it soon and will follow-up if it worked. I’ll just fork the upstream and test it in my test install when I get time. Thanks.

Fabrice