Table_Data_SQL Class Reference

Interface : this wrapper is used to created easily a wrapper to a table (ORM) You must create a class extending this one, in the constructor these variables have to be defined. More...

Inherits Data_SQL.

Inherited by Acc_Other_Tax_SQL, Action_Gestion_Comment_SQL, Action_Gestion_SQL, Anc_Key_Activity_SQL, Anc_Key_Detail_SQL, Anc_Key_Ledger_SQL, Anc_Key_SQL, Attr_Def_SQL, Contact_option_ref_SQL, Currency_SQL, Currency_history_SQL, Default_Menu_SQL, Document_State_SQL, Document_type_SQL, Fiche_def_ref_SQL, Forecast_Category_SQL, Forecast_Item_SQL, Forecast_SQL, Form_Definition_SQL, Form_Detail_SQL, Jrn_Note_SQL, Jrn_Sup_Document_SQL, Jrn_Tax_SQL, Jrn_def_SQL, Jrn_periode_SQL, Menu_Ref_SQL, Noalyss_SQL, Op_Predef_SQL, Operation_Exercice_Detail_SQL, Operation_Exercice_SQL, Operation_currency_SQL, Otp_Send_Secret_SQL, Parameter_Extra_SQL, Parm_Mail_Server_SQL, Parm_periode_SQL, Payment_method_SQL, Poste_analytique_SQL, Profile_Menu_sql, Profile_Mobile_SQL, Profile_sql, Quant_Fin_SQL, Quant_Purchase_SQL, Quant_Sold_SQL, Stock_Change_Sql, Stock_Goods_Sql, Stock_Sql, Tag_SQL, Tag_group_SQL, Tmp_Pcmn_SQL, Tva_Rate_SQL, User_filter_SQL, and V_Tva_Rate_SQL.

Collaboration diagram for Table_Data_SQL:

Public Member Functions
	__construct ($p_cn, $p_id=-1)
	__toString ()
	build_query ()
	Build the SQL select statement for querying the object and returns it.
	column_update ($column_expr, $value)
	update the value of a column with an expression for $value for the current record
	count ($p_where="", $p_array=null)
	return the number of count in the table corresponding to the where condition
	delete ()
	exist ()
	Count the number of record with the id ,.
	get_all_to_array ($p_key_col, $p_cond="", $p_array=NULL)
	Get all the row and use the p_key_code are the key value of array.
	insert ()
	load ()
	load a row , corresponding to the primary key
	seek ($cond='', $p_array=null)
	retrieve array of object thanks a condition
	update ()
	update the row but not the column with a default value
Public Member Functions inherited from Data_SQL
	__construct (DatabaseCore $p_cn, $p_id=-1)
	__get ($cols)
	__set ($cols, $p_value)
	collect_objects ($cond='', $p_array=null)
	return an array of objects.
	from_array ($p_array)
	Transform an array into object.
	get ($cols)
	get the value thanks the colum name and not the alias (name).
	get_a_virtual_col ()
	return array of virtual cols (alias calculated, formatted cols)
	get_cn ()
	get_info ()
	get_name ()
	get_object ($p_ret, $idx)
	get_pk_value ()
	get_primary_key ()
	get_type ()
	getp ($cols)
	set the value thanks the alias name instead of the colum name if not found try the column name
	next ($ret, $i)
	get_seek return the next object, the return of the query must have all the column of the object including the virtual columns
	save ()
	Insert or update : if the row already exists, update otherwise insert.
	set ($cols, $p_value)
	set the value thanks the colum name and not the alias (name)
	set_cn ($cn)
	set_name ($name)
	set_pk_value ($p_value)
	set_primary_key ($primary_key)
	set_type ($type)
	set_virtual_col ($col_name, $sql_expression)
	add a virtual column (formatted column, sum of 2 col, ...)
	setp ($cols, $p_value)
	set the value thanks the alias name instead of the colum name, if not found try the column name
	to_array ($prefix="")
	Turn an object (row) into an array, and the key could be prefixed with $prefix.
	to_row ($p_array)
	update the data member of current object with the value from the array.
	verify ()

Additional Inherited Members
Data Fields inherited from Data_SQL
	$cn
	$date_format
	Type of the data.
	$default
	defaullt date format
	$name
	Database connection.
	$primary_key
	Array of logical and real name.
	$table
	$type
	Column name of the primary key.
Protected Attributes inherited from Data_SQL
	$a_virtual_col

Detailed Description

Interface : this wrapper is used to created easily a wrapper to a table (ORM) You must create a class extending this one, in the constructor these variables have to be defined.

Match a table or a view into an object, you need to add the code for each table.

• table = name of the view or empty
• select = name of the select
• name = array of column name, match between logic and actual name
• type = array , match between column and type of data
• default = array of column with a default value
• date_format = format of the date
• virtual_col After you call the parent constructor

  Note

  the view or the table must include an unique key, otherwise the load doesn't work.

  : the primary key must be an integer

  class table_name_sql extends Table_Data_SQL
  {
   
  function __construct($p_id=-1)
  {
  $this->table = "schema.table";
  $this->primary_key = "o_id";
   
  $this->name=array(
  "id"=>"o_id",
  "program"=>"o_prog",
  "date"=>"o_date",
  "qcode"=>"o_qcode",
  "fiche"=>"f_id",
   
   
  );
   
  $this->type = array(
  "o_id"=>"numeric",
  "o_prog"=>"numeric",
  "o_date"=>"date",
  "o_qcode"=>"text",
  "f_id"=>"numeric",
   
  );
   
  $this->default = array(
  "o_id" => "auto",
  );
  $this->date_format = "DD.MM.YYYY";
  global $cn;
   
  parent::__construct($cn,$p_id);
  }
   
  }

  it is also possible to use set_virtual_col to format data directly in the SQL

  See also

  Data_SQL

  $jrn->set_virtual_col("str_date", " to_char(jr_tech_date,'DD/MM/YY HH24:MI')");

Definition at line 94 of file table_data_sql.class.php.

Constructor & Destructor Documentation

__construct()

Table_Data_SQL::__construct	(		$p_cn,
			$p_id = -1 )

Reimplemented in Anc_Key_Activity_SQL, Anc_Key_SQL, Profile_Menu, Stock_Change_Sql, Stock_Goods_Sql, Stock_Sql, and Tmp_Pcmn_SQL.

Definition at line 98 of file table_data_sql.class.php.

    {
        parent::__construct($p_cn, $p_id);
       
    }

References $p_id.

Member Function Documentation

__toString()

Table_Data_SQL::__toString

(

)

Reimplemented from Data_SQL.

Definition at line 103 of file table_data_sql.class.php.

                                  : string
    {
        $ret=" members : ";
        foreach ($this->name as $name) {
            $ret.="[ $name => {$this->$name} ]";
        }
 
        $ret.="| type ".var_export($this->type,true);
        $ret.="| default ".var_export($this->default,true);
        $ret.="| primary key ".$this->primary_key;
        $ret.="| date_format ".$this->date_format;
        $ret.="| a_virtual_col".var_export($this->a_virtual_col,true);
        return $ret;
    }

References Data_SQL\$name, $ret, name, and type.

build_query()

Table_Data_SQL::build_query

(

)

Build the SQL select statement for querying the object and returns it.

Returns

string Query of the object

add virtual column

Definition at line 281 of file table_data_sql.class.php.

    {
        $sql=" select ";
        $sep="";
        foreach ($this->name as $key)       {
            switch ($this->type[$key])
            {
                case "date":
                    $sql .= $sep.'to_char('.$key.",'".$this->date_format."') as ".$key;
                    break;
                default:
                    $sql.=$sep.$key;
            }
            $sep=",";
        }
        $pk=$this->primary_key;
        /**
         * add virtual column
         */
        if ( ! empty( $this->a_virtual_col)){
            $nb_virtual_col=count($this->a_virtual_col);
            $a_col= array_keys($this->a_virtual_col);
            for ($x=0;$x<$nb_virtual_col ;$x++) {
                $col=$a_col[$x];
                $expr=sprintf("$sep %s as %s ",$this->a_virtual_col[$col], $col);
                $sql.=" $expr ";
            }
        }
        $sql.=" from ".$this->table;
        return $sql;
    }

References $a_col, Data_SQL\$primary_key, $sep, $sql, $x, count(), name, and type.

Referenced by load(), and seek().

Here is the call graph for this function:

column_update()

Table_Data_SQL::column_update	(		$column_expr,
			$value )

update the value of a column with an expression for $value for the current record

Parameters

$column_exp	string like column = $1 or column=function($1)
$value	value replacing $1

Note

you can use it for entering a date with the hour and minute like this $this->column_update("field_date = to_date($1,'DD.MM.YY HH24:MI'),date('d.m.Y H:i'))

Returns

Table_Data_SQL

Definition at line 171 of file table_data_sql.class.php.

                                                {
        $pk=$this->primary_key;
        $sql="update ".$this->table." set {$column_expr} where {$this->primary_key} = $2";
        $this->cn->exec_sql($sql,[$value,$this->$pk]);
        return $this;
 
    }

References Data_SQL\$primary_key, $sql, $value, and cn.

count()

Table_Data_SQL::count	(		$p_where = "",
			$p_array = null )

return the number of count in the table corresponding to the where condition

Parameters

string	$p_where	the condition appended to the SQL select query , where must be given
array	$p_array	variable from the $p_where condition

Returns

type

Reimplemented from Data_SQL.

Definition at line 263 of file table_data_sql.class.php.

                                                     {
        $count=$this->cn->get_value("select count(*) from $this->table ".$p_where,$p_array);
        return $count;
    }

References $count, $p_array, and cn.

Referenced by build_query(), get_all_to_array(), and Stock_Goods\take_last_inventory().

delete()

Table_Data_SQL::delete

(

)

Reimplemented from Data_SQL.

Definition at line 156 of file table_data_sql.class.php.

    {
        $pk=$this->primary_key;
        $sql=" delete from ".$this->table." where ".$this->primary_key."= $1";
        $this->cn->exec_sql($sql,array($this->$pk));
    }

References Data_SQL\$primary_key, $sql, and cn.

exist()

Table_Data_SQL::exist

(

)

Count the number of record with the id ,.

Returns

integer 0 doesn't exist , 1 exists

Reimplemented from Data_SQL.

Definition at line 271 of file table_data_sql.class.php.

                            {
        $pk=$this->primary_key;
        $count=$this->cn->get_value("select count(*) from ".$this->table." where ".$this->primary_key."=$1",array($this->$pk));
        return $count;
    }

References $count, Data_SQL\$primary_key, cn, and table.

get_all_to_array()

Table_Data_SQL::get_all_to_array	(		$p_key_col,
			$p_cond = "",
			$p_array = NULL )

Get all the row and use the p_key_code are the key value of array.

The key column is usually the primary key or any unique key. the returns looks like

[ID1]=>array( ["PRIMARYKEY"=>"ID1" 
                , "VALUE" => 2]);
[ID2]=>array( ["PRIMARYKEY"=>"ID2" 
               , "VALUE" => 2]);

Note

It should be used only for small tables: the array is build row by row

Parameters

string	$p_key_col	existing and unique key
string	$p_cond	sql cond
array	$p_array	array of value for the SQL condition

Definition at line 328 of file table_data_sql.class.php.

    {
        $ret=$this->seek($p_cond, $p_array);
        if ($ret==FALSE)
            return array();
        $a_array=Database::fetch_all($ret);
        $nb_array=count($a_array);
        $a_result=array();
        try
        {
            for ($i=0; $i<$nb_array; $i++)
            {
                if (!isset($a_array[$i][$p_key_col]))
                {
                    throw new Exception("col not found ".$p_key_col);
                }
                $key=$a_array[$i][$p_key_col];
                if ( isset ($a_result[$key]) ){
                    throw new Exception ("duplicate found col : $key");
                }
                $a_result[$key]=$a_array[$i];
            }
        }
        catch (Exception $exc)
        {
            record_log($exc);
            throw $exc;
        }
        return $a_result;
    }

References $i, $nb_array, $p_array, $ret, count(), DatabaseCore\fetch_all(), record_log(), and seek().

Here is the call graph for this function:

insert()

Table_Data_SQL::insert

(

)

Reimplemented from Data_SQL.

Definition at line 119 of file table_data_sql.class.php.

    {
        $this->verify();
        $sql="insert into ".$this->table." ( ";
        $sep="";
        $par="";
        $idx=1;
        $array=array();
        foreach ($this->name as $key=> $value)
        {
            if (isset($this->default[$value])&&$this->default[$value]=="auto"&&$this->$value==null)
                continue;
            // prob. with table when the pk is not auto.
            if ($value==$this->primary_key && $this->$value==-1 && isset($this->default[$value]) )
                continue;
            $sql.=$sep.$value;
            switch ($this->type[$value])
            {
                case "date":
                    if ($this->date_format=="")
                        throw new Exception('Format Date invalide');
                    $par .=$sep.'to_timestamp($'.$idx.",'".$this->date_format."')";
                    break;
                default:
                    $par .= $sep."$".$idx;
            }
 
            $array[]=$this->$value;
            $sep=",";
            $idx++;
        }
        $sql.=") values (".$par.") returning ".$this->primary_key;
        $pk=$this->primary_key;
        $returning=$this->cn->get_value($sql, $array);
        $this->$pk=$returning;
    }

References $array, $idx, Data_SQL\$primary_key, $sep, $sql, $value, cn, name, type, and Data_SQL\verify().

Here is the call graph for this function:

load()

Table_Data_SQL::load

(

)

load a row , corresponding to the primary key

Reimplemented from Data_SQL.

Definition at line 214 of file table_data_sql.class.php.

                          :bool
    {
        
        $pk=$this->primary_key;
        // primary cannot be null or empty
        if (trim($this->$pk??"")==="" || $this->$pk===null)  {
            $this->$pk=-1;
            return false;
        }
        $sql=$this->build_query();
        $sql.=" where ".$this->primary_key." = $1";
        $result=$this->cn->get_row($sql,array ($this->$pk));
        if ($this->cn->count()==0)
        {
            $this->$pk=-1;
            return false;
        }
 
        foreach ($result  as $key=> $value)
        {
            $this->$key=$value;
        }
        return true;
    }

References Data_SQL\$primary_key, $result, $sql, $value, build_query(), and cn.

Here is the call graph for this function:

seek()

Table_Data_SQL::seek	(		$cond = '',
			$p_array = null )

retrieve array of object thanks a condition

Parameters

$cond	condition (where clause) (optional by default all the rows are fetched) you can use this parameter for the order or subselect
$p_array	array for the SQL stmt

See also

Database\exec_sql get_object Database\num_row

Returns

the return value of exec_sql

Reimplemented from Data_SQL.

Definition at line 248 of file table_data_sql.class.php.

    {
        $sql=$this->build_query();
        $sql.="  $cond ";
        $ret=$this->cn->exec_sql($sql, $p_array);
        return $ret;
    }

References $p_array, $ret, $sql, build_query(), and cn.

Referenced by get_all_to_array().

Here is the call graph for this function:

update()

Table_Data_SQL::update

(

)

update the row but not the column with a default value

Reimplemented from Data_SQL.

Definition at line 181 of file table_data_sql.class.php.

    {
        $this->verify();
        $pk=$this->primary_key;
        $sql="update ".$this->table."  ";
        $sep="";
        $idx=1;
        $array=array();
        $set=" set ";
        foreach ($this->name as $key=> $value)        {
            if (isset($this->default[$value])&&$this->default[$value]=="auto")
                continue;
            switch ($this->type[$value])
            {
                case "date":
                    $par=$value.'=to_timestamp($'.$idx.",'".$this->date_format."')";
                    break;
                default:
                    $par=$value."= $".$idx;
            }
            $sql.=$sep." $set ".$par;
            $array[]=$this->$value;
            $sep=",";
            $set="";
            $idx++;
        }
        $array[]=$this->$pk;
        $sql.=" where ".$this->primary_key." = $".$idx;
        $this->cn->exec_sql($sql, $array);
    }

References $array, $idx, Data_SQL\$primary_key, $sep, $sql, $value, cn, name, type, and Data_SQL\verify().

Here is the call graph for this function:

---

The documentation for this class was generated from the following file:

• include/lib/table_data_sql.class.php