EXCELIA_DBARR/custom/bbus/class/apiproductlisthelper.class.php

<?php
use Luracast\Restler\RestException;

require_once DOL_DOCUMENT_ROOT.'/product/class/product.class.php';
require_once DOL_DOCUMENT_ROOT.'/api/class/api.class.php';

class ApiProductListProduct extends Product
{
    public $sub_products;
}

class ApiProductListHelper
{    
    public $db;
    /**
     * 
     */
    public function __construct()
    {
        global $db;

        $this->db = $db;
    }
    /**
     * 
     */
    public function list($sortfield = "t.ref", $sortorder = 'ASC', $limit = 100, $page = 0, $mode = 0, $category = 0, $sqlfilters = '', $ids_only = false, $variant_filter = 0, $pagination_data = false, $includestockdata = 0): array
    {
		ApiBbusLog::eventLog("{$sqlfilters}");

        $obj_ret = array();

		//$socid = DolibarrApiAccess::$user->socid ? DolibarrApiAccess::$user->socid : '';

		$sql = "SELECT t.rowid, t.ref, t.ref_ext";
		$sql .= " FROM ".$this->db->prefix()."product as t";
		$sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product_extrafields AS ef ON ef.fk_object = t.rowid";	// So we will be able to filter on extrafields
		if ($category > 0) {
			$sql .= ", ".$this->db->prefix()."categorie_product as c";
		}
		$sql .= ' WHERE t.entity IN ('.getEntity('product').')';
		$sql .= ' AND ef.hotelsales IS NULL';

		if ($variant_filter == 1) {
			$sql .= ' AND t.rowid not in (select distinct fk_product_parent from '.$this->db->prefix().'product_attribute_combination)';
			$sql .= ' AND t.rowid not in (select distinct fk_product_child from '.$this->db->prefix().'product_attribute_combination)';
		}
		if ($variant_filter == 2) {
			$sql .= ' AND t.rowid in (select distinct fk_product_parent from '.$this->db->prefix().'product_attribute_combination)';
		}
		if ($variant_filter == 3) {
			$sql .= ' AND t.rowid in (select distinct fk_product_child from '.$this->db->prefix().'product_attribute_combination)';
		}

		// Select products of given category
		if ($category > 0) {
			$sql .= " AND c.fk_categorie = ".((int) $category);
			$sql .= " AND c.fk_product = t.rowid";
		}
		if ($mode == 1) {
			// Show only products
			$sql .= " AND t.fk_product_type = 0";
		} elseif ($mode == 2) {
			// Show only services
			$sql .= " AND t.fk_product_type = 1";
		}

		// Add sql filters
		if ($sqlfilters) {
			$errormessage = '';
			if (!$this->_checkFilters($sqlfilters, $errormessage)) {
				throw new RestException(503, 'Error when validating parameter sqlfilters -> '.$errormessage);
			}
			$regexstring = '\(([^:\'\(\)]+:[^:\'\(\)]+:[^\(\)]+)\)';	// We must accept datc:<:2020-01-01 10:10:10
			$sql .= " AND (".preg_replace_callback('/'.$regexstring.'/', 'ApiProductListHelper::_forge_criteria_callback', $sqlfilters).")";
		}

		//print $sql;exit;

		//this query will return total products with the filters given
		$sqlTotals =  str_replace('SELECT t.rowid, t.ref, t.ref_ext', 'SELECT count(t.rowid) as total', $sql);

		$sql .= $this->db->order($sortfield, $sortorder);
		if ($limit) {
			if ($page < 0) {
				$page = 0;
			}
			$offset = $limit * $page;

			$sql .= $this->db->plimit($limit + 1, $offset);
		}
		$result = $this->db->query($sql);
		if ($result) {
			$num = $this->db->num_rows($result);
			$min = min($num, ($limit <= 0 ? $num : $limit));
			$i = 0;
			while ($i < $min) {
				$obj = $this->db->fetch_object($result);
				if (!$ids_only) {
					$product_static = new ApiProductListProduct($this->db);
					if ($product_static->fetch($obj->rowid)) {
						if ($includestockdata && DolibarrApiAccess::$user->rights->stock->lire) {
							$product_static->load_stock();

							if (is_array($product_static->stock_warehouse)) {
								foreach ($product_static->stock_warehouse as $keytmp => $valtmp) {
									if (isset($product_static->stock_warehouse[$keytmp]->detail_batch) && is_array($product_static->stock_warehouse[$keytmp]->detail_batch)) {
										foreach ($product_static->stock_warehouse[$keytmp]->detail_batch as $keytmp2 => $valtmp2) {
											unset($product_static->stock_warehouse[$keytmp]->detail_batch[$keytmp2]->db);
										}
									}
								}
							}
						}

                        // get child details
                        $product_static->sub_products = $this->_getSubproducts($product_static);

						$obj_ret[] = $this->_cleanObjectDatas($product_static);
					}
				} else {
					$obj_ret[] = $obj->rowid;
				}
				$i++;
			}
		} else {
			throw new RestException(503, 'Error when retrieve product list : '.$this->db->lasterror());
		}
		if (!count($obj_ret)) {
			throw new RestException(404, 'No product found');
		}

		//if $pagination_data is true the response will contain element data with all values and element pagination with pagination data(total,page,limit)
		if ($pagination_data) {
			$totalsResult = $this->db->query($sqlTotals);
			$total = $this->db->fetch_object($totalsResult)->total;

			$tmp = $obj_ret;
			$obj_ret = array();

			$obj_ret['data'] = $tmp;
			$obj_ret['pagination'] = array(
				'total' => (int) $total,
				'page' => $page, //count starts from 0
				'page_count' => ceil((int) $total/$limit),
				'limit' => $limit
			);
		}

        return $obj_ret;
    }

	/**
     * 
     */
    public function listhotel($sortfield = "t.ref", $sortorder = 'ASC', $limit = 100, $page = 0, $mode = 0, $category = 0, $sqlfilters = '', $ids_only = false, $variant_filter = 0, $pagination_data = false, $includestockdata = 0): array
    {
        $obj_ret = array();

		//$socid = DolibarrApiAccess::$user->socid ? DolibarrApiAccess::$user->socid : '';

		$sql = "SELECT t.rowid, t.ref, t.ref_ext";
		$sql .= " FROM ".$this->db->prefix()."product as t";
		$sql .= " LEFT JOIN ".MAIN_DB_PREFIX."product_extrafields AS ef ON ef.fk_object = t.rowid";	// So we will be able to filter on extrafields
		if ($category > 0) {
			$sql .= ", ".$this->db->prefix()."categorie_product as c";
		}
		$sql .= ' WHERE t.entity IN ('.getEntity('product').')';

		if ($variant_filter == 1) {
			$sql .= ' AND t.rowid not in (select distinct fk_product_parent from '.$this->db->prefix().'product_attribute_combination)';
			$sql .= ' AND t.rowid not in (select distinct fk_product_child from '.$this->db->prefix().'product_attribute_combination)';
		}
		if ($variant_filter == 2) {
			$sql .= ' AND t.rowid in (select distinct fk_product_parent from '.$this->db->prefix().'product_attribute_combination)';
		}
		if ($variant_filter == 3) {
			$sql .= ' AND t.rowid in (select distinct fk_product_child from '.$this->db->prefix().'product_attribute_combination)';
		}

		// Select products of given category
		if ($category > 0) {
			$sql .= " AND c.fk_categorie = ".((int) $category);
			$sql .= " AND c.fk_product = t.rowid";
		}
		if ($mode == 1) {
			// Show only products
			$sql .= " AND t.fk_product_type = 0";
		} elseif ($mode == 2) {
			// Show only services
			$sql .= " AND t.fk_product_type = 1";
		}

		// Add sql filters
		if ($sqlfilters) {
			$errormessage = '';
			if (!$this->_checkFilters($sqlfilters, $errormessage)) {
				throw new RestException(503, 'Error when validating parameter sqlfilters -> '.$errormessage);
			}
			$regexstring = '\(([^:\'\(\)]+:[^:\'\(\)]+:[^\(\)]+)\)';	// We must accept datc:<:2020-01-01 10:10:10
			$sql .= " AND (".preg_replace_callback('/'.$regexstring.'/', 'ApiProductListHelper::_forge_criteria_callback', $sqlfilters).")";
		}

		//this query will return total products with the filters given
		$sqlTotals =  str_replace('SELECT t.rowid, t.ref, t.ref_ext', 'SELECT count(t.rowid) as total', $sql);

		$sql .= $this->db->order($sortfield, $sortorder);
		if ($limit) {
			if ($page < 0) {
				$page = 0;
			}
			$offset = $limit * $page;

			$sql .= $this->db->plimit($limit + 1, $offset);
		}

		//print $sql;exit;

		$result = $this->db->query($sql);
		if ($result) {
			$num = $this->db->num_rows($result);
			$min = min($num, ($limit <= 0 ? $num : $limit));
			$i = 0;
			while ($i < $min) {
				$obj = $this->db->fetch_object($result);
				if (!$ids_only) {
					$product_static = new ApiProductListProduct($this->db);
					if ($product_static->fetch($obj->rowid)) {
						if ($includestockdata && DolibarrApiAccess::$user->rights->stock->lire) {
							$product_static->load_stock();

							if (is_array($product_static->stock_warehouse)) {
								foreach ($product_static->stock_warehouse as $keytmp => $valtmp) {
									if (isset($product_static->stock_warehouse[$keytmp]->detail_batch) && is_array($product_static->stock_warehouse[$keytmp]->detail_batch)) {
										foreach ($product_static->stock_warehouse[$keytmp]->detail_batch as $keytmp2 => $valtmp2) {
											unset($product_static->stock_warehouse[$keytmp]->detail_batch[$keytmp2]->db);
										}
									}
								}
							}
						}

                        // get child details
                        $product_static->sub_products = $this->_getSubproducts($product_static);

						$obj_ret[] = $this->_cleanObjectDatas($product_static);
					}
				} else {
					$obj_ret[] = $obj->rowid;
				}
				$i++;
			}
		} else {
			throw new RestException(503, 'Error when retrieve product list : '.$this->db->lasterror());
		}
		if (!count($obj_ret)) {
			throw new RestException(404, 'No product found');
		}

		//if $pagination_data is true the response will contain element data with all values and element pagination with pagination data(total,page,limit)
		if ($pagination_data) {
			$totalsResult = $this->db->query($sqlTotals);
			$total = $this->db->fetch_object($totalsResult)->total;

			$tmp = $obj_ret;
			$obj_ret = array();

			$obj_ret['data'] = $tmp;
			$obj_ret['pagination'] = array(
				'total' => (int) $total,
				'page' => $page, //count starts from 0
				'page_count' => ceil((int) $total/$limit),
				'limit' => $limit
			);
		}

        return $obj_ret;
    }
    /**
     * Return subproducts
     * 
     * @param   ApiProductListProduct   $product    Product
     * 
     * @return  array
     */
    protected function _getSubproducts(ApiProductListProduct $product): array
    {
        $children = [];

        $subProducts = $product->getChildsArbo($product->id, 1);
        $keys = ['rowid', 'qty', 'fk_product_type', 'label', 'incdec', 'ref', 'fk_association', 'rang'];
		
		foreach ($subProducts as $values) {
			$subProductData = array_combine($keys, $values);
            $subProduct = new Product($this->db);
            if ($subProduct->fetch($subProductData['rowid']) > 0) {
                $children[] = $this->_cleanObjectDatas($subProduct);
            }
		}

        return $children;
    }

	/**
	 * Return if a $sqlfilters parameter is valid
	 *
	 * @param  	string   		$sqlfilters     sqlfilter string
	 * @param	string			$error			Error message
	 * @return 	boolean|string   				True if valid, False if not valid
	 */
    protected function _checkFilters($sqlfilters, &$error = '')
	{
		// phpcs:enable

		return dolCheckFilters($sqlfilters, $error);
	}
	/**
	 * Clean sensible object datas
	 *
	 * @param   Object  $object	Object to clean
	 * @return	Object			Object with cleaned properties
	 */
	protected function _cleanObjectDatas($object)
	{
		// phpcs:enable
		// Remove $db object property for object
		unset($object->db);
		unset($object->isextrafieldmanaged);
		unset($object->ismultientitymanaged);
		unset($object->restrictiononfksoc);
		unset($object->table_rowid);
		unset($object->pass);
		unset($object->pass_indatabase);

		// Remove linkedObjects. We should already have linkedObjectsIds that avoid huge responses
		unset($object->linkedObjects);

		unset($object->fields);
		unset($object->oldline);

		unset($object->error);
		unset($object->errors);
		unset($object->errorhidden);

		unset($object->ref_previous);
		unset($object->ref_next);
		unset($object->ref_int);
		unset($object->imgWidth);
		unset($object->imgHeight);
		unset($object->barcode_type_code);
		unset($object->barcode_type_label);

		unset($object->mode_reglement);		// We use mode_reglement_id now
		unset($object->cond_reglement);		// We use cond_reglement_id now
		unset($object->note);				// We use note_public or note_private now
		unset($object->contact);			// We use contact_id now
		unset($object->thirdparty);			// We use thirdparty_id or fk_soc or socid now

		unset($object->projet); // Should be fk_project
		unset($object->project); // Should be fk_project
		unset($object->author); // Should be fk_user_author
		unset($object->timespent_old_duration);
		unset($object->timespent_id);
		unset($object->timespent_duration);
		unset($object->timespent_date);
		unset($object->timespent_datehour);
		unset($object->timespent_withhour);
		unset($object->timespent_fk_user);
		unset($object->timespent_note);
		unset($object->fk_delivery_address);
		unset($object->modelpdf);
		unset($object->sendtoid);
		unset($object->name_bis);
		unset($object->newref);
		unset($object->alreadypaid);
		unset($object->openid);

		unset($object->statuts);
		unset($object->statuts_short);
		unset($object->statuts_logo);
		unset($object->statuts_long);
		unset($object->statutshorts);
		unset($object->statutshort);
		unset($object->labelStatus);
		unset($object->labelStatusShort);

		unset($object->stats_propale);
		unset($object->stats_commande);
		unset($object->stats_contrat);
		unset($object->stats_facture);
		unset($object->stats_commande_fournisseur);
		unset($object->stats_reception);
		unset($object->stats_mrptoconsume);
		unset($object->stats_mrptoproduce);

		unset($object->element);
		unset($object->element_for_permission);
		unset($object->fk_element);
		unset($object->table_element);
		unset($object->table_element_line);
		unset($object->class_element_line);
		unset($object->picto);

		unset($object->fieldsforcombobox);
		unset($object->regeximgext);

		unset($object->skip_update_total);
		unset($object->context);
		unset($object->next_prev_filter);

		unset($object->region);
		unset($object->region_code);
		unset($object->country);
		unset($object->state);
		unset($object->state_code);
		unset($object->departement);
		unset($object->departement_code);

		unset($object->libelle_statut);
		unset($object->libelle_paiement);

		unset($object->prefix_comm);

		if (!isset($object->table_element) || $object->table_element != 'ticket') {
			unset($object->comments);
		}

		// Remove the $oldcopy property because it is not supported by the JSON
		// encoder. The following error is generated when trying to serialize
		// it: "Error encoding/decoding JSON: Type is not supported"
		// Note: Event if this property was correctly handled by the JSON
		// encoder, it should be ignored because keeping it would let the API
		// have a very strange behavior: calling PUT and then GET on the same
		// resource would give different results:
		// PUT /objects/{id} -> returns object with oldcopy = previous version of the object
		// GET /objects/{id} -> returns object with oldcopy empty
		unset($object->oldcopy);

		// If object has lines, remove $db property
		if (isset($object->lines) && is_array($object->lines) && count($object->lines) > 0) {
			$nboflines = count($object->lines);
			for ($i = 0; $i < $nboflines; $i++) {
				$this->_cleanObjectDatas($object->lines[$i]);

				unset($object->lines[$i]->contact);
				unset($object->lines[$i]->contact_id);
				unset($object->lines[$i]->country);
				unset($object->lines[$i]->country_id);
				unset($object->lines[$i]->country_code);
				unset($object->lines[$i]->mode_reglement_id);
				unset($object->lines[$i]->mode_reglement_code);
				unset($object->lines[$i]->mode_reglement);
				unset($object->lines[$i]->cond_reglement_id);
				unset($object->lines[$i]->cond_reglement_code);
				unset($object->lines[$i]->cond_reglement);
				unset($object->lines[$i]->fk_delivery_address);
				unset($object->lines[$i]->fk_projet);
				unset($object->lines[$i]->fk_project);
				unset($object->lines[$i]->thirdparty);
				unset($object->lines[$i]->user);
				unset($object->lines[$i]->model_pdf);
				unset($object->lines[$i]->modelpdf);
				unset($object->lines[$i]->note_public);
				unset($object->lines[$i]->note_private);
				unset($object->lines[$i]->fk_incoterms);
				unset($object->lines[$i]->label_incoterms);
				unset($object->lines[$i]->location_incoterms);
				unset($object->lines[$i]->name);
				unset($object->lines[$i]->lastname);
				unset($object->lines[$i]->firstname);
				unset($object->lines[$i]->civility_id);
				unset($object->lines[$i]->fk_multicurrency);
				unset($object->lines[$i]->multicurrency_code);
				unset($object->lines[$i]->shipping_method_id);
			}
		}

		if (!empty($object->thirdparty) && is_object($object->thirdparty)) {
			$this->_cleanObjectDatas($object->thirdparty);
		}

		if (!empty($object->product) && is_object($object->product)) {
			$this->_cleanObjectDatas($object->product);
		}

		return $object;
	}    
	/**
	 * Function to forge a SQL criteria from a Generic filter string
	 *
	 * @param  array    $matches    Array of found string by regex search.
	 * 								Each entry is 1 and only 1 criteria.
	 * 								Example: "t.ref:like:'SO-%'", "t.date_creation:<:'20160101'", "t.date_creation:<:'2016-01-01 12:30:00'", "t.nature:is:NULL", "t.field2:isnot:NULL"
	 * @return string               Forged criteria. Example: "t.field like 'abc%'"
	 */
	protected static function _forge_criteria_callback($matches)
	{
		return dolForgeCriteriaCallback($matches);
	}    
}