Data_SQL Class Reference

this an abstract class , all the SQL class, like noalyss_sql (table), Acc_Plan_SQL (based on a SQL not a table) or a view. More...

Inheritance diagram for Data_SQL:

Collaboration diagram for Data_SQL:

Public Member Functions
	__construct (DatabaseCore $p_cn, $p_id=-1)
	__get ($cols)
	__set ($cols, $p_value)
	__toString ()
	collect_objects ($cond='', $p_array=null)
	return an array of objects.
	count ($p_where="", $p_array=null)
	delete ()
	exist ()
	Count the number of record with the id ,.
	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
	insert ()
	load ()
	Load the current row return false if not found.
	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.
	seek ($cond='', $p_array=null)
	retrieve array of object thanks a condition
	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.
	update ()
	verify ()

Data Fields
	$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
	$a_virtual_col

Detailed Description

this an abstract class , all the SQL class, like noalyss_sql (table), Acc_Plan_SQL (based on a SQL not a table) or a view.

You must create a class extending this one, in the constructor these variables have to be defined

• table = name of the view or empty
• sql = sql statement
• name = array of column name, match between logic and actual name, or only an array of columns
• type = array , match between column and type of data
• default = array of column with a default value
• date_format = format of the date

if you give a SQL or a View you have to give a primary key, usually , the best is to use a key composed of different PK of the tables Example : in this SQL (or view) the PK is id and it is composed with the PK f_id and sg_id , remember that the pk cannot be null and must be unique ! For SQL , the value is computed , so you need a subselect like this

select
  ssw.sg_id::text||'-'||vfp.f_id::text id,
  vfp.f_id,vfp.f_enable,vfp.person_name ,vfp.person_fname ,vfp.person_qcode ,ssw.sg_id
  from rash.vw_fiche_person vfp
  join rash.security_social_worker ssw using(f_id) ;

For SQL , the value is computed , so you need a subselect like this

  select * from (
        select
        ssw.sg_id::text||'-'||vfp.f_id::text id,
        vfp.f_id,vfp.f_enable,vfp.person_name ,vfp.person_fname ,vfp.person_qcode ,ssw.sg_id
        from rash.vw_fiche_person vfp
        join rash.security_social_worker ssw using(f_id))sub1
@encode
  
  
  
   After you call the parent constructor
   @note the view or the table must include an unique key, otherwise the load 
   doesn't work.
   
   @pnote
   Name is an array the key is the logical name and the value is the name of the column
 @code
  $this->name=array(
                "id"=>"o_id",
                "program"=>"o_prog",
                "date"=>"o_date",
                "qcode"=>"o_qcode",
                "fiche"=>"f_id",
  );

the type is an array , key = column name , value = type

$this->type = array(
              "o_id"=>"numeric",
              "o_prog"=>"numeric",
              "o_date"=>"date",
              "o_qcode"=>"text",
              "f_id"=>"numeric",
               );

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

Constructor & Destructor Documentation

__construct()

Data_SQL::__construct	(	DatabaseCore	$p_cn,
			$p_id = -1 )

Reimplemented in Acc_Other_Tax_SQL, Action_Gestion_Comment_SQL, Action_Gestion_SQL, Attr_Def_SQL, Contact_option_ref_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_def_SQL, Jrn_Note_SQL, Jrn_periode_SQL, Jrn_Tax_SQL, Op_Predef_SQL, Operation_Exercice_Detail_SQL, Operation_Exercice_SQL, Parameter_Extra_SQL, Parm_periode_SQL, Payment_method_SQL, Poste_analytique_SQL, Profile_Mobile_SQL, Quant_Fin_SQL, Quant_Purchase_SQL, Quant_Sold_SQL, Tag_group_SQL, Tva_Rate_SQL, User_filter_SQL, and V_Tva_Rate_SQL.

Definition at line 124 of file data_sql.class.php.

    {
        $this->cn=$p_cn;
        $pk=$this->primary_key;
        $this->$pk=$p_id;
         $this->a_virtual_col=array();
        // check that the definition is correct
        if (count($this->name) != count($this->type) ){
                throw new Exception (__FILE__." $this->table Cannot instantiate");
        }
        // forbid the use of a column named type , date_format,  name  or primary_key to avoid conflict
        
        /* Initialize an empty object */
        foreach ($this->name as $key)
        {
            if ( in_array($key,['name','type','format_date','cn','date_format','default'] ) ) {
                throw new Exception ('DATASQL-94 invalid column name'.$key,94);
            }
            $this->$key=null;
        }
        $this->$pk=$p_id;
        /* load it , if the pk id doesn't exist, it will be turned into -1  */
        if ($p_id != -1 )$this->load();
        if ( empty($this->date_format) )         $this->date_format="DD.MM.YYYY";
    }

References $p_id, $primary_key, cn, count(), load(), name, and type.

Here is the call graph for this function:

Member Function Documentation

__get()

Data_SQL::__get

(

$cols

)

Definition at line 265 of file data_sql.class.php.

                                 {
         if (array_key_exists($cols, $this->type)) {
            return $this->$cols;
        }
         if (! empty ($this->a_virtual_col) &&  array_key_exists($cols, $this->a_virtual_col))
        {
             return $this->$cols;
        }
        throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
    }

References EXC_DATA_SQL, and type.

__set()

Data_SQL::__set	(		$cols,
			$p_value )

Definition at line 252 of file data_sql.class.php.

                                          {
        if (array_key_exists($cols, $this->type))    {
            $this->$cols=$p_value;
            return $this;
        }        
          if ( ! empty ($this->a_virtual_col) && array_key_exists($cols, $this->a_virtual_col))
        {
             $this->$cols=$p_value;
             return $this;
        }
        
           throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
    }

References EXC_DATA_SQL, and type.

__toString()

Data_SQL::__toString

(

)

Reimplemented in Table_Data_SQL.

Definition at line 108 of file data_sql.class.php.

                                : string
    {
        $ret=" members: ";
        foreach ($this->name as $name) {
            $ret.="[ $name => {$this->$name} ]";
        }
 
        $ret.="| type ".print_r($this->type,true);
        $ret.="| default ".print_r($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 $name, $ret, name, and type.

collect_objects()

Data_SQL::collect_objects	(		$cond = '',
			$p_array = null )

return an array of objects.

Do not use this function if they are too many objects, it takes a lot of memory, and could slow down your application.

Parameters

$cond	condition, order...
$p_array	array to use for a condition

Note

this function could slow down your application.

Definition at line 454 of file data_sql.class.php.

    {
        if ($p_array != null && ! is_array($p_array) )
        {
            throw new Exception(_("Erreur : exec_sql attend un array"));
        }
        $ret=$this->seek($cond, $p_array);
        $max=Database::num_row($ret);
        $a_return=array();
        for ($i=0; $i<$max; $i++)
        {
            $x=clone $this->next($ret, $i);
           
            $a_return[$i]=$x;
        }
        return $a_return;
    }

References $i, $max, $p_array, $ret, $x, _, next(), DatabaseCore\num_row(), and seek().

Here is the call graph for this function:

count()

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

abstract

Reimplemented in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

References $p_array.

Referenced by __construct().

delete()

Data_SQL::delete ( )

abstract

Reimplemented in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

exist()

Data_SQL::exist ( )

abstract

Count the number of record with the id ,.

Returns

integer 0 doesn't exist , 1 exists

Reimplemented in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

Referenced by save().

from_array()

Data_SQL::from_array

(

$p_array

)

Transform an array into object.

Parameters

type

$p_array

Returns

object

Definition at line 344 of file data_sql.class.php.

    {
        foreach ($this->name as $key=> $value)
        {
            if (isset($p_array[$value]))
            {
                $this->$value=$p_array[$value];
            }
            else
            {
                $this->$value=null;
            }
        }
        return $this;
    }

References $p_array, $value, and name.

Referenced by next().

get()

Data_SQL::get

(

$cols

)

get the value thanks the colum name and not the alias (name).

See also

getp

Definition at line 184 of file data_sql.class.php.

    {
        if (array_key_exists($cols, $this->type)) {
            return $this->$cols;
        }
         if (array_key_exists($cols, $this->a_virtual_col))
        {
             return $this->$cols;
        }
             throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
    }

References EXC_DATA_SQL, and type.

get_a_virtual_col()

Data_SQL::get_a_virtual_col

(

)

return array of virtual cols (alias calculated, formatted cols)

Returns

array

Definition at line 165 of file data_sql.class.php.

                                        {
        return $this->a_virtual_col;
    }

References $a_virtual_col.

get_cn()

Data_SQL::get_cn

(

)

Definition at line 478 of file data_sql.class.php.

    {
        return $this->cn;
    }

References $cn.

Referenced by Operation_Exercice\input_row().

get_info()

Data_SQL::get_info

(

)

Definition at line 321 of file data_sql.class.php.

    {
        return var_export($this, true);
    }

get_name()

Data_SQL::get_name

(

)

Definition at line 483 of file data_sql.class.php.

    {
        return $this->name;
    }

References $name.

get_object()

Data_SQL::get_object	(		$p_ret,
			$idx )

See also

next

Definition at line 441 of file data_sql.class.php.

    {
        return $this->next($p_ret, $idx);
    }

References $idx, and next().

Here is the call graph for this function:

get_pk_value()

Data_SQL::get_pk_value

(

)

Reimplemented in Acc_Plan_SQL.

Definition at line 286 of file data_sql.class.php.

    {
        $pk=$this->primary_key;
          return $this->$pk;
    }

References $primary_key.

get_primary_key()

Data_SQL::get_primary_key

(

)

Definition at line 488 of file data_sql.class.php.

    {
        return $this->primary_key;
    }

References $primary_key.

get_type()

Data_SQL::get_type

(

)

Definition at line 493 of file data_sql.class.php.

    {
        return $this->type;
    }

References $type.

getp()

Data_SQL::getp

(

$cols

)

set the value thanks the alias name instead of the colum name if not found try the column name

See also

get

Definition at line 215 of file data_sql.class.php.

    {
        if (array_key_exists($cols, $this->name)) {
            $idx=$this->name[$cols];
            return $this->$idx;
        }
        if (array_key_exists($cols, $this->type)) {
            return $this->$cols;
        }
        if (array_key_exists($cols, $this->a_virtual_col))
        {
             return $this->$cols;
        }
 
        throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
    }

References $idx, EXC_DATA_SQL, name, and type.

Referenced by Operation_Exercice\input_row().

insert()

Data_SQL::insert ( )

abstract

Reimplemented in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

Referenced by save().

load()

Data_SQL::load ( )

abstract

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 in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

Referenced by __construct().

next()

Data_SQL::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

Parameters

$p_ret	is the return value of an exec_sql
$idx	is the index

See also

seek

Returns

object

Definition at line 421 of file data_sql.class.php.

    {
        $array=$this->cn->fetch_array($ret, $i);
        $this->from_array($array);
        if ( ! empty ($this->a_virtual_col )) {
                $a_column= array_keys($this->a_virtual_col);
                foreach( $a_column as $column){
                    if ( isset  ($array[$column] )) {
                        $this->$column = $array[$column];
                    } else {
                        $this->$column =   null;
                    }
                }
        }
        return $this;
    }

References $array, $i, $ret, cn, and from_array().

Referenced by collect_objects(), and get_object().

Here is the call graph for this function:

save()

Data_SQL::save

(

)

Insert or update : if the row already exists, update otherwise insert.

Definition at line 152 of file data_sql.class.php.

    {
       $count = $this->exist();
        
        if ($count == 0)
            $this->insert();
        else
            $this->update();
    }

References $count, exist(), insert(), and update().

Here is the call graph for this function:

seek()

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

abstract

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 in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

References $p_array.

Referenced by collect_objects().

set()

Data_SQL::set	(		$cols,
			$p_value )

set the value thanks the colum name and not the alias (name)

See also

setp

Definition at line 200 of file data_sql.class.php.

    {
        if (array_key_exists($cols, $this->type))    {
            $this->$cols=$p_value;
            return $this;
        }        else
           throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
            
    }

References EXC_DATA_SQL, and type.

set_cn()

Data_SQL::set_cn

(

$cn

)

Definition at line 498 of file data_sql.class.php.

    {
        $this->cn=$cn;
        return $this;
    }

References $cn, and cn.

set_name()

Data_SQL::set_name

(

$name

)

Parameters

string

$name

Returns

$this

Definition at line 508 of file data_sql.class.php.

    {
        $this->name=$name;
        return $this;
    }

References $name, and name.

set_pk_value()

Data_SQL::set_pk_value

(

$p_value

)

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

     {
         $pk=$this->primary_key;
           $this->$pk=$p_value;
     }

References $primary_key.

set_primary_key()

Data_SQL::set_primary_key

(

$primary_key

)

Parameters

string

$primary_key

Returns

$this

Definition at line 518 of file data_sql.class.php.

    {
        $this->primary_key=$primary_key;
        return $this;
    }

References $primary_key.

set_type()

Data_SQL::set_type

(

$type

)

Parameters

array

$type

Returns

$this

Definition at line 528 of file data_sql.class.php.

    {
        $this->type=$type;
        return $this;
    }

References $type, and type.

set_virtual_col()

Data_SQL::set_virtual_col	(		$col_name,
			$sql_expression )

add a virtual column (formatted column, sum of 2 col, ...)

Parameters

$col_name	(string) name of the column , will be use in get , getp
$sql_expression	(string) SQL Expression for the column like "to_char(col1,'DD.MM.YY HH24:MI:SS')", col1+col2, ...

Note

sql expression and col_name must be valid , there is no futher check

Returns

void

Definition at line 177 of file data_sql.class.php.

                                                               {
        $this->a_virtual_col[$col_name]=$sql_expression;
    }

setp()

Data_SQL::setp	(		$cols,
			$p_value )

set the value thanks the alias name instead of the colum name, if not found try the column name

See also

set

Definition at line 237 of file data_sql.class.php.

    {
        if (array_key_exists($cols, $this->name))    {
            $idx=$this->name[$cols];
            $this->$idx=$p_value;
            return $this;
        }       
        if (array_key_exists($cols, $this->type))    {
            $this->$cols=$p_value;
            return $this;
        }
        
        throw new \Exception (" unknow cols [$cols] =".$this,EXC_DATA_SQL);
    }

References $idx, EXC_DATA_SQL, name, and type.

to_array()

Data_SQL::to_array

(

$prefix = ""

)

Turn an object (row) into an array, and the key could be prefixed with $prefix.

Parameters

string

$prefix

before the key

Returns

array

Definition at line 365 of file data_sql.class.php.

    {
        $array=array();
        foreach ($this->name as $key=> $value)
        {
            $nkey=$prefix.$key;
            $array[$key]=$this->$key;
        }
        if ( ! empty ($this->a_virtual_col )) {
            $a_column= array_keys($this->a_virtual_col);
            foreach( $a_column as $column){
                $array[$column]=$this->a_virtual_col[$column];
            }
        }
        return $array;
    }

References $array, $prefix, $value, and name.

to_row()

Data_SQL::to_row

(

$p_array

)

update the data member of current object with the value from the array.

includes the virtual column, usefull if need to update several columns in once

Parameters

$p_array

(array) associative key = column_vale, value new value for this col.

Returns

void

Definition at line 388 of file data_sql.class.php.

                                     {
        foreach ($this->name as $name) {
            if (isset ($p_array[$name])) {
                $this->$name=$p_array[$name];
            }
        }
         if ( ! empty ($this->a_virtual_col )) {
                $a_column= array_keys($this->a_virtual_col);
                foreach( $a_column as $column){
                    if ( isset ($p_array[$column])) {
                    $this->$column = $p_array[$column];
                    }
                }
        }
    }

References $name, $p_array, and name.

Referenced by V_Contact_SQL\load().

update()

Data_SQL::update ( )

abstract

Reimplemented in Acc_Plan_SQL, Table_Data_SQL, V_Contact_SQL, and V_Currency_Last_Value_SQL.

Referenced by save().

verify()

Data_SQL::verify

(

)

Todo

ajout vérification type (date, text ou numeric)

Returns

int

Reimplemented in Menu_Ref.

Definition at line 329 of file data_sql.class.php.

    {
        foreach ($this->name as $key)
        {
            if (noalyss_trim($this->$key)=='')
                $this->$key=null;
        }
        return 0;
    }

References name, and noalyss_trim().

Referenced by Table_Data_SQL\insert(), and Table_Data_SQL\update().

Here is the call graph for this function:

Field Documentation

$a_virtual_col

Data_SQL::$a_virtual_col

protected

Definition at line 107 of file data_sql.class.php.

Referenced by get_a_virtual_col().

$cn

Data_SQL::$cn

Definition at line 100 of file data_sql.class.php.

Referenced by Default_Menu_SQL\__construct(), Stock_Change_Sql\__construct(), Stock_Goods_Sql\__construct(), Stock_Sql\__construct(), Stock\build_tmp_table(), Stock\create_query_histo(), get_cn(), Stock_Goods\input(), Stock_Goods\record_save(), set_cn(), Stock\summary(), and Stock_Goods\take_last_inventory().

$date_format

Data_SQL::$date_format

Type of the data.

Definition at line 104 of file data_sql.class.php.

$default

Data_SQL::$default

defaullt date format

Definition at line 105 of file data_sql.class.php.

$name

Data_SQL::$name

Database connection.

Definition at line 101 of file data_sql.class.php.

Referenced by __toString(), Table_Data_SQL\__toString(), get_name(), set_name(), and to_row().

$primary_key

Data_SQL::$primary_key

Array of logical and real name.

Definition at line 102 of file data_sql.class.php.

Referenced by __construct(), Table_Data_SQL\build_query(), Table_Data_SQL\column_update(), Table_Data_SQL\delete(), Table_Data_SQL\exist(), V_Currency_Last_Value_SQL\exist(), get_pk_value(), get_primary_key(), Table_Data_SQL\insert(), Acc_Plan_SQL\load(), Table_Data_SQL\load(), V_Currency_Last_Value_SQL\load(), set_pk_value(), set_primary_key(), and Table_Data_SQL\update().

$table

Data_SQL::$table

Definition at line 106 of file data_sql.class.php.

$type

Data_SQL::$type

Column name of the primary key.

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

Referenced by get_type(), and set_type().

---

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

• include/lib/data_sql.class.php