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_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, Parameter_Extra_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)
	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 the current row return false if not found.
	seek ($cond='', $p_array=null)
	retrieve array of object thanks a condition
	update ()
Public Member Functions inherited from Data_SQL
	__construct (DatabaseCore $p_cn, $p_id=-1)
	__toString ()
	collect_objects ($cond='', $p_array=null)
	return an array of objects.
	from_array ($p_array)
	Transform an array into object.
	get ($p_string)
	get the value thanks the colum name and not the alias (name).
	get_cn ()
	get_info ()
	get_name ()
	get_object ($p_ret, $idx)
	get_pk_value ()
	get_primary_key ()
	get_type ()
	getp ($p_string)
	set the value thanks the alias name instead of the colum name
	next ($ret, $i)
	get_seek return the next object, the return of the query must have all the column of the object
	save ()
	Insert or update : if the row already exists, update otherwise insert.
	set ($p_string, $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)
	setp ($p_string, $p_value)
	set the value thanks the alias name instead of the colum name
	to_array ($prefix="")
	Turn an object (row) into an array, and the key could be prefixed with $prefix.
	to_row ($p_array)
	turns a row fetched from the DB into a SQL object in updating all his attribute
	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.

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

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);
}
 
}

Definition at line 89 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 93 of file table_data_sql.class.php.

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

References $p_id.

Member Function Documentation

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

Definition at line 257 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;
        $sql.=" from ".$this->table;
        
        $sql.=" where ".$this->primary_key." = $1";
        
        return $sql;
    }

References Data_SQL\$primary_key, $sep, $sql, name, and type.

Referenced by load().

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 152 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 239 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 get_all_to_array(), and Stock_Goods\take_last_inventory().

delete()

Table_Data_SQL::delete

(

)

Reimplemented from Data_SQL.

Definition at line 137 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 247 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 294 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)
        {
            echo $exc->getMessage();
            record_log($exc->getMessage());
            record_log($exc->getTraceAsString());
            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 100 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 the current row return false if not found.

$pk=$this->primary_key;
if ( $this->get_limit_fiche_qcode() != 0 )
{
    $sql=sprintf($this->sql," limit ".$this->get_limit_fiche_qcode());
} else
{
    $sql=sprintf($this->sql,"  ");
}
$result=$this->cn->get_array($sql. " where id=$1",array ($this->$pk));
if ($this->cn->count()==0)
{
    $this->$pk=-1;
    return false;
}
 
foreach ($result[0] as $key=> $value)
{
    $this->$key=$value;
}
return true;

Returns

bool

Reimplemented from Data_SQL.

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

                          :bool
    {
        $sql=$this->build_query();
        $pk=$this->primary_key;
        // primary cannot be null or empty
        if (trim($this->$pk??"")==="" || $this->$pk===null)  {
            $this->pk=-1;
            return false;
        }
       
        $result=$this->cn->get_array($sql,array ($this->$pk));
        if ($this->cn->count()==0)
        {
            $this->$pk=-1;
            return false;
        }
 
        foreach ($result[0] 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 225 of file table_data_sql.class.php.

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

References $p_array, $ret, $sql, and cn.

Referenced by get_all_to_array().

update()

Table_Data_SQL::update

(

)

Reimplemented from Data_SQL.

Definition at line 159 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