431 lines
12 KiB
PHP
431 lines
12 KiB
PHP
<?php
|
|
/* Copyright (C) 2026 ZUGFeRD Import Module
|
|
*
|
|
* This program is free software; you can redistribute it and/or modify
|
|
* it under the terms of the GNU General Public License as published by
|
|
* the Free Software Foundation; either version 3 of the License, or
|
|
* (at your option) any later version.
|
|
*/
|
|
|
|
/**
|
|
* \file importline.class.php
|
|
* \ingroup importzugferd
|
|
* \brief Class for import line items
|
|
*/
|
|
|
|
/**
|
|
* Class ImportLine
|
|
* Manages line items for ZUGFeRD imports
|
|
*/
|
|
class ImportLine
|
|
{
|
|
/**
|
|
* @var DoliDB Database handler
|
|
*/
|
|
public $db;
|
|
|
|
/**
|
|
* @var string Error message
|
|
*/
|
|
public $error;
|
|
|
|
/**
|
|
* @var array Error messages
|
|
*/
|
|
public $errors = array();
|
|
|
|
/**
|
|
* @var int ID
|
|
*/
|
|
public $id;
|
|
|
|
/**
|
|
* @var int ID (alias)
|
|
*/
|
|
public $rowid;
|
|
|
|
/**
|
|
* @var int Import ID reference
|
|
*/
|
|
public $fk_import;
|
|
|
|
/**
|
|
* @var string Line ID from ZUGFeRD
|
|
*/
|
|
public $line_id;
|
|
|
|
/**
|
|
* @var string Supplier article reference
|
|
*/
|
|
public $supplier_ref;
|
|
|
|
/**
|
|
* @var string Product name from ZUGFeRD
|
|
*/
|
|
public $product_name;
|
|
|
|
/**
|
|
* @var string Additional description
|
|
*/
|
|
public $description;
|
|
|
|
/**
|
|
* @var float Quantity
|
|
*/
|
|
public $quantity;
|
|
|
|
/**
|
|
* @var string UN/ECE unit code
|
|
*/
|
|
public $unit_code;
|
|
|
|
/**
|
|
* @var float Unit price (calculated)
|
|
*/
|
|
public $unit_price;
|
|
|
|
/**
|
|
* @var float Original unit price
|
|
*/
|
|
public $unit_price_raw;
|
|
|
|
/**
|
|
* @var float Basis quantity for price
|
|
*/
|
|
public $basis_quantity;
|
|
|
|
/**
|
|
* @var string Basis quantity unit
|
|
*/
|
|
public $basis_quantity_unit;
|
|
|
|
/**
|
|
* @var float Line total (net)
|
|
*/
|
|
public $line_total;
|
|
|
|
/**
|
|
* @var float Tax percentage
|
|
*/
|
|
public $tax_percent;
|
|
|
|
/**
|
|
* @var string EAN/GTIN
|
|
*/
|
|
public $ean;
|
|
|
|
/**
|
|
* @var float Copper surcharge per unit (Kupferzuschlag)
|
|
*/
|
|
public $copper_surcharge;
|
|
|
|
/**
|
|
* @var float Basis quantity for copper surcharge
|
|
*/
|
|
public $copper_surcharge_basis_qty;
|
|
|
|
/**
|
|
* @var int Assigned Dolibarr product ID
|
|
*/
|
|
public $fk_product;
|
|
|
|
/**
|
|
* @var int Assigned Datanorm article ID
|
|
*/
|
|
public $fk_datanorm;
|
|
|
|
/**
|
|
* @var string Match method description
|
|
*/
|
|
public $match_method;
|
|
|
|
/**
|
|
* @var int Creation timestamp
|
|
*/
|
|
public $date_creation;
|
|
|
|
/**
|
|
* @var string Table name
|
|
*/
|
|
public $table_element = 'importzugferd_import_line';
|
|
|
|
/**
|
|
* Constructor
|
|
*
|
|
* @param DoliDB $db Database handler
|
|
*/
|
|
public function __construct($db)
|
|
{
|
|
$this->db = $db;
|
|
}
|
|
|
|
/**
|
|
* Create line in database
|
|
*
|
|
* @param User $user User creating the line
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function create($user)
|
|
{
|
|
$this->date_creation = dol_now();
|
|
|
|
$sql = "INSERT INTO " . MAIN_DB_PREFIX . $this->table_element . " (";
|
|
$sql .= "fk_import, line_id, supplier_ref, product_name, description,";
|
|
$sql .= "quantity, unit_code, unit_price, unit_price_raw, basis_quantity, basis_quantity_unit,";
|
|
$sql .= "line_total, tax_percent, ean, copper_surcharge, copper_surcharge_basis_qty,";
|
|
$sql .= "fk_product, match_method, date_creation";
|
|
$sql .= ") VALUES (";
|
|
$sql .= ((int) $this->fk_import) . ",";
|
|
$sql .= "'" . $this->db->escape($this->line_id) . "',";
|
|
$sql .= "'" . $this->db->escape($this->supplier_ref) . "',";
|
|
$sql .= "'" . $this->db->escape($this->product_name) . "',";
|
|
$sql .= "'" . $this->db->escape($this->description) . "',";
|
|
$sql .= ((float) $this->quantity) . ",";
|
|
$sql .= "'" . $this->db->escape($this->unit_code) . "',";
|
|
$sql .= ((float) $this->unit_price) . ",";
|
|
$sql .= ((float) $this->unit_price_raw) . ",";
|
|
$sql .= ((float) $this->basis_quantity) . ",";
|
|
$sql .= "'" . $this->db->escape($this->basis_quantity_unit) . "',";
|
|
$sql .= ((float) $this->line_total) . ",";
|
|
$sql .= ((float) $this->tax_percent) . ",";
|
|
$sql .= "'" . $this->db->escape($this->ean) . "',";
|
|
$sql .= ($this->copper_surcharge !== null ? ((float) $this->copper_surcharge) : "NULL") . ",";
|
|
$sql .= ($this->copper_surcharge_basis_qty !== null ? ((float) $this->copper_surcharge_basis_qty) : "NULL") . ",";
|
|
$sql .= ($this->fk_product > 0 ? ((int) $this->fk_product) : "NULL") . ",";
|
|
$sql .= "'" . $this->db->escape($this->match_method) . "',";
|
|
$sql .= "'" . $this->db->idate($this->date_creation) . "'";
|
|
$sql .= ")";
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
$this->id = $this->db->last_insert_id(MAIN_DB_PREFIX . $this->table_element);
|
|
$this->rowid = $this->id;
|
|
return $this->id;
|
|
} else {
|
|
$this->error = $this->db->lasterror();
|
|
return -1;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Fetch line from database
|
|
*
|
|
* @param int $id Line ID
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function fetch($id)
|
|
{
|
|
// Use SELECT * to be compatible with older database versions without fk_datanorm column
|
|
$sql = "SELECT * FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE rowid = " . ((int) $id);
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
if ($this->db->num_rows($resql)) {
|
|
$obj = $this->db->fetch_object($resql);
|
|
$this->id = $obj->rowid;
|
|
$this->rowid = $obj->rowid;
|
|
$this->fk_import = $obj->fk_import;
|
|
$this->line_id = $obj->line_id;
|
|
$this->supplier_ref = $obj->supplier_ref;
|
|
$this->product_name = $obj->product_name;
|
|
$this->description = $obj->description;
|
|
$this->quantity = $obj->quantity;
|
|
$this->unit_code = $obj->unit_code;
|
|
$this->unit_price = $obj->unit_price;
|
|
$this->unit_price_raw = $obj->unit_price_raw;
|
|
$this->basis_quantity = $obj->basis_quantity;
|
|
$this->basis_quantity_unit = $obj->basis_quantity_unit;
|
|
$this->line_total = $obj->line_total;
|
|
$this->tax_percent = $obj->tax_percent;
|
|
$this->ean = $obj->ean;
|
|
$this->copper_surcharge = isset($obj->copper_surcharge) ? $obj->copper_surcharge : null;
|
|
$this->copper_surcharge_basis_qty = isset($obj->copper_surcharge_basis_qty) ? $obj->copper_surcharge_basis_qty : null;
|
|
$this->fk_product = $obj->fk_product;
|
|
$this->fk_datanorm = isset($obj->fk_datanorm) ? $obj->fk_datanorm : null;
|
|
$this->match_method = $obj->match_method;
|
|
$this->date_creation = $this->db->jdate($obj->date_creation);
|
|
return 1;
|
|
}
|
|
return 0;
|
|
}
|
|
$this->error = $this->db->lasterror();
|
|
return -1;
|
|
}
|
|
|
|
/**
|
|
* Update line in database
|
|
*
|
|
* @param User $user User making the update
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function update($user)
|
|
{
|
|
$sql = "UPDATE " . MAIN_DB_PREFIX . $this->table_element . " SET";
|
|
$sql .= " fk_product = " . ($this->fk_product > 0 ? ((int) $this->fk_product) : "NULL") . ",";
|
|
$sql .= " fk_datanorm = " . ($this->fk_datanorm > 0 ? ((int) $this->fk_datanorm) : "NULL") . ",";
|
|
$sql .= " match_method = '" . $this->db->escape($this->match_method) . "'";
|
|
$sql .= " WHERE rowid = " . ((int) $this->id);
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
return 1;
|
|
}
|
|
$this->error = $this->db->lasterror();
|
|
return -1;
|
|
}
|
|
|
|
/**
|
|
* Delete line from database
|
|
*
|
|
* @param User $user User deleting the line
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function delete($user)
|
|
{
|
|
$sql = "DELETE FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE rowid = " . ((int) $this->id);
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
return 1;
|
|
}
|
|
$this->error = $this->db->lasterror();
|
|
return -1;
|
|
}
|
|
|
|
/**
|
|
* Fetch all lines for an import
|
|
*
|
|
* @param int $fk_import Import ID
|
|
* @return array|int Array of ImportLine objects or <0 if error
|
|
*/
|
|
public function fetchAllByImport($fk_import)
|
|
{
|
|
$lines = array();
|
|
|
|
$sql = "SELECT rowid FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE fk_import = " . ((int) $fk_import);
|
|
$sql .= " ORDER BY rowid ASC";
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
while ($obj = $this->db->fetch_object($resql)) {
|
|
$line = new ImportLine($this->db);
|
|
$line->fetch($obj->rowid);
|
|
$lines[] = $line;
|
|
}
|
|
return $lines;
|
|
}
|
|
$this->error = $this->db->lasterror();
|
|
return -1;
|
|
}
|
|
|
|
/**
|
|
* Delete all lines for an import
|
|
*
|
|
* @param int $fk_import Import ID
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function deleteAllByImport($fk_import)
|
|
{
|
|
$sql = "DELETE FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE fk_import = " . ((int) $fk_import);
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
return 1;
|
|
}
|
|
$this->error = $this->db->lasterror();
|
|
return -1;
|
|
}
|
|
|
|
/**
|
|
* Check if all lines for an import have products assigned
|
|
*
|
|
* @param int $fk_import Import ID
|
|
* @return bool True if all lines have products, false otherwise
|
|
*/
|
|
public function allLinesHaveProducts($fk_import)
|
|
{
|
|
$sql = "SELECT COUNT(*) as total, SUM(CASE WHEN fk_product IS NOT NULL AND fk_product > 0 THEN 1 ELSE 0 END) as with_product";
|
|
$sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE fk_import = " . ((int) $fk_import);
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
$obj = $this->db->fetch_object($resql);
|
|
return ($obj->total > 0 && $obj->total == $obj->with_product);
|
|
}
|
|
return false;
|
|
}
|
|
|
|
/**
|
|
* Count lines without product assignment
|
|
*
|
|
* @param int $fk_import Import ID
|
|
* @return int Number of lines without product
|
|
*/
|
|
public function countLinesWithoutProduct($fk_import)
|
|
{
|
|
$sql = "SELECT COUNT(*) as cnt FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE fk_import = " . ((int) $fk_import);
|
|
$sql .= " AND (fk_product IS NULL OR fk_product = 0)";
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
$obj = $this->db->fetch_object($resql);
|
|
return (int) $obj->cnt;
|
|
}
|
|
return 0;
|
|
}
|
|
|
|
/**
|
|
* Set product for this line
|
|
*
|
|
* @param int $fk_product Product ID
|
|
* @param string $match_method How product was assigned
|
|
* @param User $user User making the change
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function setProduct($fk_product, $match_method, $user)
|
|
{
|
|
$this->fk_product = $fk_product;
|
|
$this->match_method = $match_method;
|
|
return $this->update($user);
|
|
}
|
|
|
|
/**
|
|
* Set Datanorm reference for this line
|
|
*
|
|
* @param int $fk_datanorm Datanorm article ID
|
|
* @param User $user User making the change
|
|
* @return int >0 if OK, <0 if KO
|
|
*/
|
|
public function setDatanorm($fk_datanorm, $user)
|
|
{
|
|
$this->fk_datanorm = $fk_datanorm;
|
|
$this->match_method = 'datanorm_assigned';
|
|
return $this->update($user);
|
|
}
|
|
|
|
/**
|
|
* Count lines with Datanorm assignment
|
|
*
|
|
* @param int $fk_import Import ID
|
|
* @return int Number of lines with Datanorm
|
|
*/
|
|
public function countLinesWithDatanorm($fk_import)
|
|
{
|
|
$sql = "SELECT COUNT(*) as cnt FROM " . MAIN_DB_PREFIX . $this->table_element;
|
|
$sql .= " WHERE fk_import = " . ((int) $fk_import);
|
|
$sql .= " AND fk_datanorm IS NOT NULL AND fk_datanorm > 0";
|
|
|
|
$resql = $this->db->query($sql);
|
|
if ($resql) {
|
|
$obj = $this->db->fetch_object($resql);
|
|
return (int) $obj->cnt;
|
|
}
|
|
return 0;
|
|
}
|
|
}
|