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)
	__toString ()
	collect_objects ($cond='', $p_array=null)
	return an array of objects. More...
	count ($p_where="", $p_array=null)
	delete ()
	exist ()
	Count the number of record with the id ,. More...
	from_array ($p_array)
	Transform an array into object. More...
	get ($p_string)
	get the value thanks the colum name and not the alias (name). More...
	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 More...
	insert ()
	load ()
	Load the current row return false if not found. More...
	next ($ret, $i)
	get_seek return the next object, the return of the query must have all the column of the object More...
	save ()
	Insert or update : if the row already exists, update otherwise insert. More...
	seek ($cond='', $p_array=null)
	retrieve array of object thanks a condition More...
	set ($p_string, $p_value)
	set the value thanks the colum name and not the alias (name) More...
	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 More...
	to_array ($prefix="")
	Turn an object (row) into an array, and the key could be prefixed with $prefix. More...
	to_row ($p_array)
	turns a row fetched from the DB into a SQL object in updating all his attribute More...
	update ()
	verify ()

Data Fields
	$cn
	$date_format
	Type of the data. More...
	$default
	defaullt date format More...
	$name
	Database connection. More...
	$primary_key
	Array of logical and real name. More...
	$table
	$type
	Column name of the primary key. More...

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
• 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 96 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 121 of file data_sql.class.php.

    {
        $this->cn=$p_cn;
        $pk=$this->primary_key;
        $this->$pk=$p_id;
        // 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);
            }
            $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

__toString()

Data_SQL::__toString

(

)

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

                                : string
    {
        $ret="values ";
        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;
        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 362 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++)
        {
            $a_return[$i]=clone $this->next($ret, $i);
        }
        return $a_return;
    }

References $i, $max, $p_array, $ret, 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, V_Contact_SQL, V_Currency_Last_Value_SQL, and Table_Data_SQL.

Referenced by __construct().

delete()

Data_SQL::delete ( )

abstract

Reimplemented in Acc_Plan_SQL, V_Contact_SQL, V_Currency_Last_Value_SQL, and Table_Data_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, V_Contact_SQL, V_Currency_Last_Value_SQL, and Table_Data_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 280 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

(

$p_string

)

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

See also

getp

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

    {
        if (array_key_exists($p_string, $this->type)) {
            return $this->$p_string;
        }
        else
            throw new Exception(__FILE__.":".__LINE__.$p_string.'Erreur attribut inexistant '.$p_string);
    }

References type.

get_cn()

Data_SQL::get_cn

(

)

Definition at line 384 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 257 of file data_sql.class.php.

    {
        return var_export($this, true);
    }

get_name()

Data_SQL::get_name

(

)

Definition at line 389 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 349 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 222 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 394 of file data_sql.class.php.

    {
        return $this->primary_key;
    }

References $primary_key.

get_type()

Data_SQL::get_type

(

)

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

    {
        return $this->type;
    }

References $type.

getp()

Data_SQL::getp

(

$p_string

)

set the value thanks the alias name instead of the colum name

See also

get

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

    {
        if (array_key_exists($p_string, $this->name)) {
            $idx=$this->name[$p_string];
            return $this->$idx;
        }
        else
            throw new Exception(__FILE__.":".__LINE__.$p_string.'Erreur attribut inexistant '.$p_string);
    }

References $idx, and name.

Referenced by Operation_Exercice\input_row().

insert()

Data_SQL::insert ( )

abstract

Reimplemented in Acc_Plan_SQL, V_Contact_SQL, V_Currency_Last_Value_SQL, and Table_Data_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, V_Contact_SQL, V_Currency_Last_Value_SQL, and Table_Data_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

Parameters

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

See also

seek

Returns

object

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

    {
        $array=$this->cn->fetch_array($ret, $i);
        return $this->from_array($array);
    }

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

Referenced by collect_objects().

set()

Data_SQL::set	(		$p_string,
			$p_value
	)

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

See also

setp

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

    {
        if (array_key_exists($p_string, $this->type))    {
            $this->$p_string=$p_value;
            return $this;
        }        else
            throw new Exception(__FILE__.":".__LINE__.$p_string.'Erreur attribut inexistant '.$p_string);
    }

References type.

set_cn()

Data_SQL::set_cn

(

$cn

)

Definition at line 404 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 414 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 217 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 424 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 434 of file data_sql.class.php.

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

References $type, and type.

setp()

Data_SQL::setp	(		$p_string,
			$p_value
	)

set the value thanks the alias name instead of the colum name

See also

set

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

    {
        if (array_key_exists($p_string, $this->name))    {
            $idx=$this->name[$p_string];
            $this->$idx=$p_value;
            return $this;
        }        else
            throw new Exception(__FILE__.":".__LINE__.$p_string.'Erreur attribut inexistant '.$p_string);
    }

References $idx, and name.

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 301 of file data_sql.class.php.

    {
        $array=array();
        foreach ($this->name as $key=> $value)
        {
            $nkey=$prefix.$key;
            $array[$key]=$this->$key;
        }
        return $array;
    }

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

to_row()

Data_SQL::to_row

(

$p_array

)

turns a row fetched from the DB into a SQL object in updating all his attribute

Parameters

$p_array

Returns

void

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

                                     {
        foreach ($this->name as $name) {
            $this->$name=$p_array[$name];
        }
    }

References $name, $p_array, and name.

Referenced by V_Contact_SQL\load().

update()

Data_SQL::update ( )

abstract

Reimplemented in Acc_Plan_SQL, V_Contact_SQL, V_Currency_Last_Value_SQL, and Table_Data_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 265 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

$cn

Data_SQL::$cn

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

Referenced by Stock_Goods_Sql\__construct(), Stock_Change_Sql\__construct(), Stock_Sql\__construct(), Default_Menu_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 102 of file data_sql.class.php.

$default

Data_SQL::$default

defaullt date format

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

$name

Data_SQL::$name

Database connection.

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

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

$primary_key

Data_SQL::$primary_key

Array of logical and real name.

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

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

$table

Data_SQL::$table

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

$type

Data_SQL::$type

Column name of the primary key.

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