phpcompta  Version-6
 All Data Structures Namespaces Files Functions Variables Pages
Public Member Functions | Static Public Member Functions | Private Attributes
Database Class Reference

This class allow you to connect to the postgresql database, execute sql, retrieve data. More...

Public Member Functions

 __construct ($p_database_id=0, $p_type='dos')
 constructor
 
 __toString ()
 
 alter_seq ($p_name, $min)
 alter the sequence value
 
 apply_patch ($p_name, $from_setup=1)
 loop to apply all the path to a folder or a template
 
 close ()
 wrapper for the function pg_close
 
 commit ()
 Commit the transaction.
 
 count ()
 return the number of rows found by the last query, synomym for size()
 
 count_sql ($p_sql, $p_array=null)
 Count the number of row returned by a sql statement.
 
 create_sequence ($p_name, $min=1)
 
 exec_sql ($p_string, $p_array=null)
 send a sql string to the database
 
 execute ($p_string, $p_array)
 wrapper for the function pg_execute
 
 execute_script ($script)
 Execute a sql script.
 
 exist_blob ($p_oid)
 check if the large object exists
 
 exist_column ($col, $table, $schema)
 Check if a column exists in a table.
 
 exist_database ($p_name)
 Count the database name in a system view.
 
 exist_schema ($p_name)
 
 exist_sequence ($p_name)
 test if a sequence exist
 
 exist_table ($p_name, $p_schema='public')
 test if a table exist
 
 exist_view ($p_name)
 
 fetch ($p_indice)
 fetch the $p_indice array from the last query
 
 format_name ($p_id, $p_type)
 return the name of the database with the domain name
 
 get_array ($p_sql, $p_array=null)
 purpose return the result of a sql statment in a array
 
 get_current_seq ($p_seq)
 get the current sequence value
 
 get_next_seq ($p_seq)
 get the next sequence value
 
 get_value ($p_sql, $p_array=null)
 return the value of the sql, the sql will return only one value with the value
 
 get_version ()
 Get version of a database, the content of the table version.
 
 lo_export ($p_oid, $tmp)
 wrapper for the function pg_lo_export
 
 lo_import ($p_oid)
 wrapper for the function pg_lo_export
 
 lo_unlink ($p_oid)
 wrapper for the function pg_lo_unlink
 
 make_array ($p_sql, $p_null=0)
 make a array with the sql
 
 make_list ($sql, $p_array=null)
 create a string containing the value separated by comma for use in a SQL in statement
 
 prepare ($p_string, $p_sql)
 wrapper for the function pg_prepare
 
 query_to_csv ($ret, $aheader)
 with the handle of a successull query, echo each row into CSV and send it directly
 
 rollback ()
 rollback the current transaction
 
 save_upload_document ($seq)
 Save a "piece justificative".
 
 set_encoding ($p_charset)
 
 size ()
 return the number of rows found by the last query, synomym for count()
 
 start ()
 @ brief : start a transaction
 
 status ()
 
 verify ()
 

Static Public Member Functions

static escape_string ($p_string)
 wrapper for the function pg_escape_string
 
static fetch_all ($ret)
 wrapper for the function pg_fetch_all
 
static fetch_array ($ret, $p_indice=0)
 wrapper for the function pg_fetch_array
 
static fetch_result ($ret, $p_row=0, $p_col=0)
 wrapper for the function pg_fetch_all
 
static fetch_row ($ret, $p_row)
 wrapper for the function pg_fetch_row
 
static num_row ($ret)
 wrapper for the function pg_NumRows
 
static test_me ()
 

Private Attributes

 $db
 
 $ret
 

Detailed Description

This class allow you to connect to the postgresql database, execute sql, retrieve data.

Definition at line 33 of file class_database.php.

Constructor & Destructor Documentation

Database::__construct (   $p_database_id = 0,
  $p_type = 'dos' 
)

constructor

Parameters
$p_database_idis the id of the dossier, or the modele following the p_type if = 0 then connect to the repository
$p_typeis 'DOS' (defaut) for dossier or 'MOD'

Definition at line 43 of file class_database.php.

References $a, db, DEBUG, domaine, exist_schema(), exit, phpcompta_password, phpcompta_psql_port, and phpcompta_user.

{
if ( IsNumber($p_database_id) == false || strlen($p_database_id) > 5 ) die ("-->Dossier invalide [$p_database_id]");
$phpcompta_user=phpcompta_user;
$password=phpcompta_password;
$port=phpcompta_psql_port;
$host=( ! defined ("phpcompta_psql_host"))?'127.0.0.1':phpcompta_psql_host;
if ( $p_database_id == 0 )
{ /* connect to the repository */
$l_dossier=sprintf("%saccount_repository",strtolower(domaine));
}
else if ( $p_type == 'dos')
{ /* connect to a folder (dossier) */
$l_dossier=sprintf("%sdossier%d",strtolower(domaine),$p_database_id);
}
else if ($p_type=='mod')
{ /* connect to a template (modele) */
$l_dossier=sprintf("%smod%d",strtolower(domaine),$p_database_id);
}
else if ($p_type=='template')
{
$l_dossier='template1';
}
else
{
throw new Exception ('Connection invalide');
}
ob_start();
$a=pg_connect("dbname=$l_dossier host='$host' user='$phpcompta_user'
password='$password' port=$port");
if ( $a == false )
{
if (DEBUG)
{
ob_end_clean();
echo '<h2 class="error">Impossible de se connecter &agrave; postgreSql !</h2>';
echo '<p>';
echo "Vos param&egrave;tres sont incorrectes : <br>";
echo "<br>";
echo "base de donn&eacute;e : $l_dossier<br>";
echo "Domaine : ".domaine."<br>";
echo "Port $port <br>";
echo "Utilisateur : $phpcompta_user <br>";
echo '</p>';
exit ("Connection impossible : v&eacute;rifiez vos param&egrave;tres de base
de donn&eacute;es");
}
else
{
echo '<h2 class="error">Erreur de connexion !</h2>';
}
}
$this->db=$a;
if ( $this->exist_schema('comptaproc') )
pg_exec($this->db,'set search_path to public,comptaproc;');
ob_end_clean();
}

Member Function Documentation

Database::__toString ( )
Parameters
\return
Note
See Also

Definition at line 886 of file class_database.php.

{
return "database ";
}
Database::alter_seq (   $p_name,
  $min 
)

alter the sequence value

Parameters
$p_namename of the sequence
$minthe start value of the sequence

Definition at line 230 of file class_database.php.

References $Res, and exec_sql().

Referenced by apply_patch().

{
if ($min < 1) $min=1;
$Res=$this->exec_sql("alter sequence $p_name restart $min");
}
Database::apply_patch (   $p_name,
  $from_setup = 1 
)

loop to apply all the path to a folder or a template

Parameters
$p_namedatabase name
$from_setup== 1 if called from setup.php

Definition at line 359 of file class_database.php.

References $i, $max, $Max, $res, $Res, $row, $seq, $sql, $succeed, $to, alter_seq(), DBVERSION, DEBUG, exec_sql(), execute_script(), exist_schema(), get_value(), get_version(), and size().

{
$MaxVersion=DBVERSION-1;
$succeed="<span style=\"font-size:18px;color:green\">&#x2713;</span>";
echo '<ul style="list-type-style:square">';
$add=($from_setup==0)?'admin/':'';
for ( $i = 4;$i <= $MaxVersion;$i++)
{
$to = $i + 1;
if ($this->get_version() <= $i)
{
if ($this->get_version() == 97)
{
if ($this->exist_schema("amortissement"))
{
$this->exec_sql('ALTER TABLE amortissement.amortissement_histo
ADD CONSTRAINT internal_fk FOREIGN KEY (jr_internal) REFERENCES jrn (jr_internal)
ON UPDATE CASCADE ON DELETE SET NULL');
}
}
echo "<li>Patching ".$p_name.
" from the version ".$this->get_version()." to $to ";
$this->execute_script($add.'sql/patch/upgrade'.$i.'.sql');
echo $succeed;
if ( ! DEBUG ) ob_start();
// specific for version 4
if ( $i == 4 )
{
$sql="select jrn_def_id from jrn_def ";
$Res=$this->exec_sql($sql);
$Max=$this->size();
for ($seq=0;$seq<$Max;$seq++)
{
$row=pg_fetch_array($Res,$seq);
$sql=sprintf ("create sequence s_jrn_%d",$row['jrn_def_id']);
$this->exec_sql($sql);
}
}
// specific to version 7
if ( $i == 7 )
{
// now we use sequence instead of computing a max
//
$Res2=$this->exec_sql('select coalesce(max(jr_grpt_id),1) as l from jrn');
$Max2= pg_NumRows($Res2) ;
if ( $Max2 == 1)
{
$Row=pg_fetch_array($Res2,0);
var_dump($Row);
$M=$Row['l'];
$this->exec_sql("select setval('s_grpt',$M,true)");
}
}
// specific to version 17
if ( $i == 17 )
{
$this->execute_script($add.'sql/patch/upgrade17.sql');
$max=$this->get_value('select last_value from s_jnt_fic_att_value');
$this->alter_seq($p_cn,'s_jnt_fic_att_value',$max+1);
} // version
// reset sequence in the modele
//--
if ( $i == 30 && $p_name=="mod" )
{
$a_seq=array('s_jrn','s_jrn_op','s_centralized',
's_stock_goods','c_order','s_central');
foreach ($a_seq as $seq )
{
$sql=sprintf("select setval('%s',1,false)",$seq);
$Res=$this->exec_sql($sql);
}
$sql="select jrn_def_id from jrn_def ";
$Res=$this->exec_sql($sql);
$Max=pg_NumRows($Res);
for ($seq=0;$seq<$Max;$seq++)
{
$row=pg_fetch_array($Res,$seq);
$sql=sprintf ("select setval('s_jrn_%d',1,false)",$row['jrn_def_id']);
$this->exec_sql($sql);
}
}
if ( $i == 36 )
{
/* check the country and apply the path */
$res=$this->exec_sql("select pr_value from parameter where pr_id='MY_COUNTRY'");
$country=pg_fetch_result($res,0,0);
$this->execute_script($add."sql/patch/upgrade36.".$country.".sql");
$this->exec_sql('update tmp_pcmn set pcm_type=find_pcm_type(pcm_val)');
}
if ($i == 59 )
{
$res=$this->exec_sql("select pr_value from parameter where pr_id='MY_COUNTRY'");
$country=pg_fetch_result($res,0,0);
if ( $country == 'BE') $this->exec_sql("insert into parm_code values ('SUPPLIER',440,'Poste par défaut pour les fournisseurs')");
if ($country=='FR') $this->exec_sql("insert into parm_code values ('SUPPLIER',400,'Poste par défaut pour les fournisseurs')");
}
if ( $i == 61 )
{
$country=$this->get_value ("select pr_value from parameter where pr_id='MY_COUNTRY'");
$this->execute_script($add."sql/patch/upgrade61.".$country.".sql");
}
if ( ! DEBUG ) ob_end_clean();
}
}
echo '</ul>';
}
Database::close ( )

wrapper for the function pg_close

Definition at line 876 of file class_database.php.

References db.

{
pg_close($this->db);
}
Database::commit ( )

Commit the transaction.

Definition at line 214 of file class_database.php.

References $Res, and exec_sql().

{
$Res=$this->exec_sql("commit");
}
Database::count ( )

return the number of rows found by the last query, synomym for size()

Definition at line 348 of file class_database.php.

Referenced by make_list(), and query_to_csv().

{
return pg_NumRows($this->ret);
}
Database::count_sql (   $p_sql,
  $p_array = null 
)

Count the number of row returned by a sql statement.

Parameters
$p_sqlsql string
$p_arrayif not null we use the safer pg_query_params

Definition at line 178 of file class_database.php.

References $p_array, and exec_sql().

Referenced by exist_schema(), exist_sequence(), exist_table(), and exist_view().

{
$r_sql=$this->exec_sql($p_sql,$p_array);
return pg_NumRows($r_sql);
}
Database::create_sequence (   $p_name,
  $min = 1 
)

Definition at line 510 of file class_database.php.

References $sql, and exec_sql().

{
if ($min < 1) $min=1;
$sql="create sequence ".$p_name." minvalue $min";
$this->exec_sql($sql);
}
static Database::escape_string (   $p_string)
static

wrapper for the function pg_escape_string

Parameters
$p_stringis the string to escape
Returns
escaped string

Definition at line 870 of file class_database.php.

Referenced by Anc_Plan\exist(), Anc_Group\insert(), Acc_Ledger\list_operation(), Anc_Acc_List\load_anc_account(), Anc_Acc_List\load_anc_card(), Anc_Acc_List\load_card(), Anc_Table\load_poste(), Anc_Acc_List\load_poste(), Follow_Up\myList(), and Pre_operation\save().

{
return pg_escape_string($p_string);
}
Database::exec_sql (   $p_string,
  $p_array = null 
)

send a sql string to the database

Parameters
$p_stringsql string
$p_arrayarray for the SQL string (see pg_query_params)
Returns
the result of the query, a resource or false if an error occured

Definition at line 121 of file class_database.php.

References $a, $p_array, $ret, db, and DEBUG.

Referenced by alter_seq(), apply_patch(), Extension\clean(), commit(), count_sql(), create_sequence(), execute_script(), get_array(), get_next_seq(), get_value(), make_array(), rollback(), save_upload_document(), and start().

{
try
{
$this->sql=$p_string;
$this->array=$p_array;
if ( $p_array==null )
{
if ( ! DEBUG )
$this->ret=pg_query($this->db,$p_string);
else
$this->ret=@pg_query($this->db,$p_string);
}
else
{
if ( !is_array($p_array))
{
throw new Exception("Erreur : exec_sql attend un array");
}
if ( ! DEBUG )
$this->ret=pg_query_params($this->db,$p_string,$p_array);
else
$this->ret=@pg_query_params($this->db,$p_string,$p_array);
}
if ( ! $this->ret )
{
$str_error=pg_last_error($this->db). pg_result_error($this->ret);
throw new Exception (" SQL ERROR $p_string ".$str_error,1 );
}
}
catch (Exception $a)
{
if (DEBUG)
{
print_r ($p_string);
print_r($p_array);
echo $a->getMessage();
echo $a->getTrace();
echo $a->getTraceAsString();
echo pg_last_error($this->db);
}
throw ($a);
}
return $this->ret;
}
Database::execute (   $p_string,
  $p_array 
)

wrapper for the function pg_execute

Parameters
$p_stringstring name of the stmt given in pg_prepare function
$p_arraycontains the variables
Note
set this->ret to the return of pg_execute
Returns
return the result of the operation,

Definition at line 841 of file class_database.php.

References $p_array, $ret, and db.

{
$this->ret=pg_execute($this->db,$p_string,$p_array);
return $this->ret;
}
Database::execute_script (   $script)

Execute a sql script.

Parameters
$scriptscript name

Definition at line 239 of file class_database.php.

References $script, $sql, DEBUG, exec_sql(), exit, print, and rollback().

Referenced by apply_patch().

{
if ( ! DEBUG ) ob_start();
$hf=fopen($script,'r');
if ( $hf == false )
{
echo 'Ne peut ouvrir '.$script;
exit();
}
$sql="";
$flag_function=false;
while (!feof($hf))
{
$buffer=fgets($hf);
$buffer=str_replace ("$","\$",$buffer);
print $buffer."<br>";
// comment are not execute
if ( substr($buffer,0,2) == "--" )
{
//echo "comment $buffer";
continue;
}
// Blank Lines Are Skipped
If ( Strlen($buffer)==0)
{
//echo "Blank $buffer";
Continue;
}
if ( strpos(strtolower($buffer),"create function")===0 )
{
echo "found a function";
$flag_function=true;
$sql=$buffer;
continue;
}
if ( strpos(strtolower($buffer),"create or replace function")===0 )
{
echo "found a function";
$flag_function=true;
$sql=$buffer;
continue;
}
// No semi colon -> multiline command
if ( $flag_function== false && strpos($buffer,';') == false )
{
$sql.=$buffer;
continue;
}
if ( $flag_function )
{
if ( strpos(strtolower($buffer), "language plpgsql") === false &&
strpos(strtolower($buffer), "language 'plpgsql'") === false )
{
$sql.=$buffer;
continue;
}
}
else
{
// cut the semi colon
$buffer=str_replace (';','',$buffer);
}
$sql.=$buffer;
if ( $this->exec_sql($sql) == false )
{
$this->rollback();
if ( ! DEBUG ) ob_end_clean();
print "ERROR : $sql";
exit();
}
$sql="";
$flag_function=false;
print "<hr>";
} // while (feof)
fclose($hf);
if ( ! DEBUG ) ob_end_clean();
}
Database::exist_blob (   $p_oid)

check if the large object exists

Parameters
$p_oidof the large object
Returns
return true if the large obj exist or false if not

Definition at line 590 of file class_database.php.

References $r, and get_value().

{
$r=$this->get_value('select count(loid) from pg_largeobject where loid=$1'
,array($p_oid) );
if ($r > 0) return true;
else return false;
}
Database::exist_column (   $col,
  $table,
  $schema 
)

Check if a column exists in a table.

Parameters
$col: column name
$table:table name
$schema:schema name, default public
Returns
true or false

Definition at line 545 of file class_database.php.

References $r, $table, and get_value().

{
$r=$this->get_value('select count(*) from information_schema.columns where table_name=lower($1) and column_name=lower($2) and table_schema=lower($3)',
array($col,$table,$schema));
if ( $r > 0 ) return true;
return false;
}
Database::exist_database (   $p_name)

Count the database name in a system view.

Parameters
$p_namestring database name
Returns
number of database found (normally 0 or 1)

Definition at line 579 of file class_database.php.

References get_value().

{
$database_exist=$this->get_value('select count(*)
from pg_catalog.pg_database where datname = lower($1)',array($p_name));
return $database_exist;
}
Database::exist_schema (   $p_name)

Definition at line 612 of file class_database.php.

References $r, and count_sql().

Referenced by __construct(), apply_patch(), and Extension\clean().

{
$r=$this->count_sql("select nspname from pg_namespace where nspname=lower($1)",array($p_name));
if ( $r==0)
return false;
return true;
}
Database::exist_sequence (   $p_name)

test if a sequence exist

Definition at line 519 of file class_database.php.

References $r, and count_sql().

{
$r=$this->count_sql("select relname from pg_class where relname=lower($1)",array($p_name));
if ( $r==0)
return false;
return true;
}
Database::exist_table (   $p_name,
  $p_schema = 'public' 
)

test if a table exist

Parameters
$p_nametable name
$schemaname of the schema default public
Returns
true if a table exist otherwise false

Definition at line 531 of file class_database.php.

References $r, and count_sql().

{
$r=$this->count_sql("select table_name from information_schema.tables where table_schema=$1 and table_name=lower($2)", array($p_schema,$p_name));
if ( $r==0)
return false;
return true;
}
Database::exist_view (   $p_name)

Definition at line 601 of file class_database.php.

References $r, and count_sql().

{
$r=$this->count_sql("select viewname from pg_views where viewname=lower($1)",array($p_name));
if ( $r==0)
return false;
return true;
}
Database::fetch (   $p_indice)

fetch the $p_indice array from the last query

Parameters
$p_indiceindex

Definition at line 335 of file class_database.php.

{
if ( $this->ret == false ) throw new Exception ('this->ret is empty');
return pg_fetch_array($this->ret,$p_indice ) ;
}
static Database::fetch_all (   $ret)
static

wrapper for the function pg_fetch_all

Parameters
$retis the result of pg_exec (exec_sql)
Returns
double array (row x col )

Definition at line 792 of file class_database.php.

References $ret.

Referenced by Fiche\belong_ledger(), Follow_Up\get(), Anc_Operation\get_balance(), Anc_Operation\get_by_jid(), Acc_Ledger\get_class_def(), Acc_Ledger\get_detail(), Acc_Ledger\get_fiche_def(), Anc_Operation\get_jrid(), Acc_Operation\get_jrnx_detail(), User\get_ledger(), gestion_sold\get_list(), gestion_purchase\get_list(), Anc_Plan\get_list(), Anc_Operation\get_list(), Anc_Account\get_list(), Acc_Report\get_list(), Pre_operation\get_list_ledger(), Anc_Plan\get_poste_analytique(), Fiche\get_quick_code(), Acc_Ledger\get_row(), Acc_Ledger\get_rowSimple(), Acc_Ledger\get_solde(), Acc_Ledger\GetDefLine(), Fiche\getName(), Anc_Balance_Simple\load(), Anc_Group\load(), gestion_purchase\load(), Pre_operation\load(), Pre_op_ods\load(), Pre_op_fin\load(), Pre_op_ven\load(), Pre_Op_Advanced\load(), Pre_op_ach\load(), Acc_Account\load(), Acc_Account_Ledger\load(), Acc_Report\load(), Anc_Balance_Double\load(), Acc_Parm_Code\load_all(), Acc_Ledger_Info\load_all(), Todo_List\load_all(), Fiche_Def_Ref\LoadAll(), Document_modele\myList(), Anc_Group\myList(), Follow_Up\myList(), Follow_Up\short_list(), Fiche\strAttribut(), and Customer\VatListing().

{
return pg_fetch_all($ret);
}
static Database::fetch_array (   $ret,
  $p_indice = 0 
)
static

wrapper for the function pg_fetch_array

Parameters
$retis the result of a pg_exec
$p_indiceis the index
Returns
$array of column

Definition at line 783 of file class_database.php.

References $ret.

Referenced by Fiche\blank(), User\Check(), Document_modele\Delete(), Fiche\Display(), Periode\display_form_periode(), Fiche_Def\DisplayAttribut(), Anc_Account\fetch_from_db(), Document\Generate(), Document_type\get(), Anc_Plan\get(), Fiche_Def\Get(), Fiche_Def_Ref\Get(), Acc_Reconciliation\get(), Document\get(), Fiche_Def\get_attr_min(), User\get_available_folder(), Fiche\get_bk_balance(), Supplier\get_by_account(), Customer\get_by_account(), Fiche_Def\get_by_category(), Acc_Operation\get_data(), Periode\get_date_limit(), User\get_exercice(), Acc_Operation\get_internal(), Acc_Account\get_lib(), Acc_Report\get_name(), Acc_Account_Ledger\get_name(), Acc_Ledger\get_name(), Profile_sql\get_object(), Profile_Menu_sql\get_object(), Menu_Ref_sql\get_object(), Jrn_Def_sql\get_object(), User\get_preference(), Acc_Ledger\get_propertie(), Acc_Report\get_row(), Fiche\get_row_result(), Acc_Account_Ledger\get_row_sql(), Acc_Ledger\get_rowSimple(), Acc_Account_Ledger\get_solde(), Acc_Account_Ledger\get_solde_detail(), Fiche\get_solde_detail(), Acc_Ledger\get_type(), Fiche_Def\GetAll(), Fiche_Def\getAttribut(), Fiche\getAttribut(), Fiche\GetByDef(), html_min_page_start(), html_page_start(), isValid_deprecrated(), Acc_Ledger\list_operation(), Acc_Ledger\list_operation_to_reconcile(), Acc_Ledger\listing(), User\load(), Acc_Ledger_Info\load(), Forecast\load(), Forecast_Item\load(), Forecast_Cat\load(), Acc_Tva\load(), Acc_Payment\load(), Acc_Parm_Code\load(), Todo_List\load(), Dossier\load(), Acc_Bilan\load(), User\load_global_pref(), Fiche_Def\myList(), Own\Own(), query_to_csv(), Document_modele\Save(), Document\Send(), ShowMenuFiche(), Profile_Menu\sub_menu(), Document_modele\update(), Fiche\update(), Anc_Operation\update_from_jrnx(), Customer\VatListing(), and Acc_Bilan\warning().

{
return pg_fetch_array($ret,$p_indice);
}
static Database::fetch_result (   $ret,
  $p_row = 0,
  $p_col = 0 
)
static

wrapper for the function pg_fetch_all

Parameters
$retis the result of pg_exec (exec_sql)
$p_rowis the indice of the row
$p_colis the indice of the col
Returns
a string or an integer

Definition at line 803 of file class_database.php.

References $ret.

Referenced by User\Admin(), Periode\display_form_periode(), Fiche\empty_attribute(), Periode\find_periode(), Periode\get_exercice(), Acc_Ledger_Info\insert(), Forecast\insert(), Forecast_Cat\insert(), Forecast_Item\insert(), Acc_Tva\insert(), Todo_List\insert(), Acc_Report\insert(), Periode\is_centralized(), Periode\is_closed(), Periode\is_open(), Acc_Report\load(), gestion_purchase\search_by_jid(), Acc_Ledger_Info\search_id_internal(), Acc_Operation\seek_internal(), and User\set_default_periode().

{
return pg_fetch_result($ret,$p_row,$p_col);
}
static Database::fetch_row (   $ret,
  $p_row 
)
static

wrapper for the function pg_fetch_row

Parameters
$retis the result of pg_exec (exec_sql)
$p_rowis the indice of the row
Returns
an array indexed from 0

Definition at line 812 of file class_database.php.

References $ret.

Referenced by Html_Table\sql2table().

{
return pg_fetch_row($ret,$p_row);
}
Database::format_name (   $p_id,
  $p_type 
)

return the name of the database with the domain name

Parameters
$p_idof the folder WITHOUT the domain name
$p_typedos for folder mod for template
Returns
formatted name

Definition at line 558 of file class_database.php.

References domaine, echo_error(), and exit.

{
switch ($p_type)
{
case 'dos':
$sys_name=sprintf("%sdossier%d",strtolower(domaine),$p_id);
break;
case 'mod':
$sys_name=sprintf("%smod%d",strtolower(domaine),$p_id);
break;
default:
echo_error(__FILE__." format_name invalid type ".$p_type, __LINE__);
exit();
}
return $sys_name;
}
Database::get_array (   $p_sql,
  $p_array = null 
)

purpose return the result of a sql statment in a array

Parameters
$p_sqlsql query
$p_arrayif not null we use ExecSqlParam
Returns
an empty array if nothing is found

Definition at line 501 of file class_database.php.

References $array, $Max, $p_array, $r, and exec_sql().

Referenced by make_list().

{
$r=$this->exec_sql($p_sql,$p_array);
if ( ($Max= pg_NumRows($r)) == 0 ) return array();
$array=pg_fetch_all($r);
return $array;
}
Database::get_current_seq (   $p_seq)

get the current sequence value

Definition at line 185 of file class_database.php.

References $Res, and get_value().

{
$Res=$this->get_value("select currval('$p_seq') as seq");
return $Res;
}
Database::get_next_seq (   $p_seq)

get the next sequence value

Definition at line 194 of file class_database.php.

References $Res, $seq, and exec_sql().

{
$Res=$this->exec_sql("select nextval('$p_seq') as seq");
$seq=pg_fetch_array($Res,0);
return $seq['seq'];
}
Database::get_value (   $p_sql,
  $p_array = null 
)

return the value of the sql, the sql will return only one value with the value

Parameters
$p_sqlthe sql stmt example :select s_value from document_state where s_id=2
$p_arrayif array is not null we use the ExecSqlParm (safer)
See Also
exec_sql
Note
print a warning if several value are found, if only the first value is needed consider using a LIMIT clause
Returns
only the first value or an empty string if nothing is found

Definition at line 481 of file class_database.php.

References $array, $p_array, $r, and exec_sql().

Referenced by apply_patch(), exist_blob(), exist_column(), exist_database(), get_current_seq(), and get_version().

{
$this->ret=$this->exec_sql($p_sql,$p_array);
if ( pg_NumRows($this->ret) == 0 ) return "";
if ( pg_NumRows($this->ret) > 1 )
{
$array=pg_fetch_all($this->ret);
throw new Exception( "Attention $p_sql retourne ".pg_NumRows($this->ret)." valeurs ".
var_export($p_array,true)." values=".var_export($array,true));
}
$r=pg_fetch_row($this->ret,0);
return $r[0];
}
Database::get_version ( )

Get version of a database, the content of the table version.

Returns
version number

Definition at line 324 of file class_database.php.

References $Res, and get_value().

Referenced by apply_patch().

{
$Res=$this->get_value("select val from version");
return $Res;
}
Database::lo_export (   $p_oid,
  $tmp 
)

wrapper for the function pg_lo_export

Parameters
$p_oidis the oid of the log
$tmpis the file
Returns
result of the operation

Definition at line 852 of file class_database.php.

References $tmp, and db.

{
return pg_lo_export($this->db,$p_oid,$tmp);
}
Database::lo_import (   $p_oid)

wrapper for the function pg_lo_export

Parameters
$p_oidis the oid of the log
$tmpis the file
Returns
result of the operation

Definition at line 861 of file class_database.php.

References db.

{
return pg_lo_import($this->db,$p_oid);
}
Database::lo_unlink (   $p_oid)

wrapper for the function pg_lo_unlink

Parameters
$p_oidis the of oid
Returns
return the result of the operation

Definition at line 821 of file class_database.php.

References db.

{
return pg_lo_unlink($this->db,$p_oid);
}
Database::make_array (   $p_sql,
  $p_null = 0 
)

make a array with the sql

Parameters
$p_sqlsql statement, only the first two column will be returned in an array. The first col. is the label and the second the value
$p_nullif the array start with a null value
Note
this function is used with ISelect when it is needed to have a list of options
Returns
: a double array like 
Array
(
[0] => Array
    (
        [value] => 1
        [label] => Marchandise A
    )

[1] => Array
    (
        [value] => 2
        [label] => Marchandise B
    )

[2] => Array
    (
        [value] => 3
        [label] => Marchandise C
    )
)
See Also
ISelect

Definition at line 682 of file class_database.php.

References $a, $i, $max, $r, $row, exec_sql(), and h().

{
$a=$this->exec_sql($p_sql);
$max=pg_NumRows($a);
if ( $max==0 && $p_null==0) return null;
for ($i=0;$i<$max;$i++)
{
$row=pg_fetch_row($a);
$r[$i]['value']=$row[0];
$r[$i]['label']=h($row[1]);
}
// add a blank item ?
if ( $p_null == 1 )
{
for ($i=$max;$i!=0;$i--)
{
$r[$i]['value']= $r[$i-1]['value'];
$r[$i]['label']= h($r[$i-1]['label']);
}
$r[0]['value']=-1;
$r[0]['label']=" ";
} // if ( $p_null == 1 )
return $r;
}
Database::make_list (   $sql,
  $p_array = null 
)

create a string containing the value separated by comma for use in a SQL in statement

Returns
the string or empty if nothing is found
See Also
fid_card.php

Definition at line 625 of file class_database.php.

References $f, $i, $idx, $p_array, $ret, $row, $sql, count(), get_array(), and trim().

{
if ( $p_array == null )
{
$aArray=$this->get_array($sql);
}
else
{
$aArray=$this->get_array($sql,$p_array);
}
if (empty ($aArray) ) return "";
$aIdx=array_keys($aArray[0]);
$idx=$aIdx[0];
$ret="";
$f="";
for ($i = 0; $i < count($aArray);$i++)
{
$row=$aArray[$i];
$ret.=$f.$aArray[$i][$idx];
$f=',';
}
$ret=trim($ret,',');
return $ret;
}
static Database::num_row (   $ret)
static

wrapper for the function pg_NumRows

Parameters
$retis the result of a exec_sql
Returns
number of line affected

Definition at line 772 of file class_database.php.

References $ret.

Referenced by User\Admin(), Fiche\belong_ledger(), User\Check(), User\check_action(), Fiche\count_by_modele(), Periode\display_form_periode(), Fiche_Def\DisplayAttribut(), Acc_Account_Ledger\do_exist(), Fiche\empty_attribute(), Acc_Report\exist(), Follow_Up\export_csv(), Anc_Account\fetch_from_db(), Periode\find_periode(), Anc_Plan\get(), Fiche_Def\Get(), Fiche_Def_Ref\Get(), Acc_Reconciliation\get(), Document\get(), Fiche_Def\get_attr_min(), User\get_available_folder(), Fiche\get_bk_balance(), Supplier\get_by_account(), Customer\get_by_account(), Fiche_Def\get_by_category(), Acc_Operation\get_data(), Periode\get_date_limit(), Periode\get_exercice(), User\get_exercice(), Acc_Operation\get_internal(), Anc_Operation\get_jrid(), Acc_Operation\get_jrnx_detail(), User\get_ledger(), Acc_Account\get_lib(), Acc_Report\get_list(), Acc_Report\get_name(), Acc_Account_Ledger\get_name(), Acc_Ledger\get_name(), Anc_Plan\get_poste_analytique(), User\get_preference(), Acc_Ledger\get_propertie(), Acc_Report\get_row(), Acc_Ledger\get_row(), Fiche\get_row_result(), Acc_Account_Ledger\get_row_sql(), Acc_Ledger\get_rowSimple(), Acc_Account_Ledger\get_solde(), Acc_Account_Ledger\get_solde_detail(), Fiche\get_solde_detail(), Acc_Ledger\get_type(), Fiche_Def\GetAll(), Fiche_Def\getAttribut(), Fiche\getAttribut(), Fiche\GetByDef(), Stock\history(), html_min_page_start(), html_page_start(), isValid_deprecrated(), Acc_Ledger\list_operation(), Acc_Ledger\list_operation_to_reconcile(), Acc_Ledger\listing(), Profile_Menu\listing_profile(), Anc_Balance_Simple\load(), User\load(), Acc_Ledger_Info\load(), Forecast\load(), Forecast_Item\load(), Forecast_Cat\load(), Acc_Payment\load(), Acc_Parm_Code\load(), Todo_List\load(), Dossier\load(), Acc_Bilan\load(), Acc_Report\load(), Anc_Balance_Double\load(), Acc_Ledger_Info\load_all(), User\load_global_pref(), Document_modele\myList(), Fiche_Def\myList(), Own\Own(), query_to_csv(), Document_modele\Save(), gestion_purchase\search_by_jid(), Acc_Ledger_Info\search_id_internal(), Acc_Operation\seek_internal(), Document\Send(), ShowMenuFiche(), Html_Table\sql2table(), Profile_Menu\sub_menu(), Document_modele\update(), Fiche\update(), Anc_Operation\update_from_jrnx(), Customer\VatListing(), and Acc_Bilan\warning().

{
return pg_NumRows($ret);
}
Database::prepare (   $p_string,
  $p_sql 
)

wrapper for the function pg_prepare

Parameters
$p_stringstring name for pg_prepare function
$p_sqlis the sql to prepare
Returns
return the result of the operation

Definition at line 831 of file class_database.php.

References db.

{
return pg_prepare($this->db,$p_string,$p_sql);
}
Database::query_to_csv (   $ret,
  $aheader 
)

with the handle of a successull query, echo each row into CSV and send it directly

Parameters
type$rethandle to a query
type$aheaderdouble array, each item of the array contains a key type (num) and a key title

Definition at line 903 of file class_database.php.

References $e, $i, $ret, $row, $seq, count(), fetch_array(), num_row(), and printf.

{
$seq="";
for ($i=0;$i<count($aheader);$i++)
{
echo $seq.'"'.$aheader[$i]['title'].'"';
$seq=";";
}
printf("\n\r");
// fetch all the rows
for ($i=0;$i<Database::num_row($ret);$i++)
{
$row=Database::fetch_array($ret, $i);
$sep2="";
// for each rows, for each value
for ($e=0;$e<count($row)/2;$e++)
{
switch ($aheader[$e]['type'])
{
case 'num':
echo $sep2.nb($row[$e]);
break;
default:
echo $sep2.'"'.$row[$e].'"';
}
$sep2=";";
}
printf("\n\r");
}
}
Database::rollback ( )

rollback the current transaction

Definition at line 221 of file class_database.php.

References $Res, and exec_sql().

Referenced by execute_script(), and save_upload_document().

{
$Res=$this->exec_sql("rollback");
}
Database::save_upload_document (   $seq)

Save a "piece justificative".

Parameters
$seqjr_grpt_id
Returns
$oid of the lob file if success null if a error occurs

Definition at line 715 of file class_database.php.

References $_ENV, $cn, $r, $ret, db, echo_error(), exec_sql(), and rollback().

{
/* there is
no file to
upload */
if ($_FILES["pj"]["error"] == UPLOAD_ERR_NO_FILE )
{
return;
}
$new_name=tempnam($_ENV['TMP'],'pj');
if ($_FILES["pj"]["error"] > 0)
{
print_r($_FILES);
echo_error(__FILE__.":".__LINE__."Error: " . $_FILES["pj"]["error"] );
}
if ( strlen ($_FILES['pj']['tmp_name']) != 0 )
{
if (move_uploaded_file($_FILES['pj']['tmp_name'],
$new_name))
{
// echo "Image saved";
$oid= pg_lo_import($this->db,$new_name);
if ( $oid == false )
{
echo_error('postgres.php',__LINE__,"cannot upload document");
$this->rollback();
return;
}
// Remove old document
$ret=$this->exec_sql("select jr_pj from jrn where jr_grpt_id=$seq");
if (pg_num_rows($ret) != 0)
{
$r=pg_fetch_array($ret,0);
$old_oid=$r['jr_pj'];
if (strlen($old_oid) != 0)
pg_lo_unlink($cn,$old_oid);
}
// Load new document
$this->exec_sql("update jrn set jr_pj=".$oid.", jr_pj_name='".$_FILES['pj']['name']."', ".
"jr_pj_type='".$_FILES['pj']['type']."' where jr_grpt_id=$seq");
return $oid;
}
else
{
echo "<H1>Error</H1>";
$this->rollback();
}
}
return 0;
}
Database::set_encoding (   $p_charset)

Definition at line 110 of file class_database.php.

References db.

{
pg_set_client_encoding($this->db,$p_charset);
}
Database::size ( )

return the number of rows found by the last query, synomym for count()

Definition at line 342 of file class_database.php.

Referenced by apply_patch().

{
return pg_NumRows($this->ret);
}
Database::start ( )

@ brief : start a transaction

Definition at line 205 of file class_database.php.

References $Res, and exec_sql().

{
$Res=$this->exec_sql("start transaction");
}
Database::status ( )

Definition at line 892 of file class_database.php.

References db.

{
return pg_transaction_status($this->db);
}
static Database::test_me ( )
static

Definition at line 890 of file class_database.php.

{}
Database::verify ( )

Definition at line 106 of file class_database.php.

{
// Verify that the elt we want to add is correct
}

Field Documentation

Database::$db
private

database connection

Definition at line 36 of file class_database.php.

Database::$ret
private

return value

Definition at line 37 of file class_database.php.

Referenced by exec_sql(), execute(), fetch_all(), fetch_array(), fetch_result(), fetch_row(), make_list(), num_row(), query_to_csv(), and save_upload_document().

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