include/class_opengw.inc

   1 <?php
   2
   3 require_once("class_pgsql_opengw.inc");
   4
   5 class ogw{
   6   var $info;
   7   var $ogo;
   8
   9   var $validLocationTeam;
  10   var $validTemplateUser;
  11   var $validTeams;
  12
  13
  14   var $InfoOK = false;
  15
  16   var $MUST = array("name","login","template_user_id");
  17
  18   var $MAY = array( "salutation","firstname","description","degree",
  19                     "birthday","sex","street","zip","country","zipcity",
  20                     "state","name1","value_string","number","db_status",
  21                     "object_version","is_locked","LocationTeamID","TeamIDis");
  22
  23   var $LastError  = "";
  24   var $option     = "";
  25   var $connected = false;
  26
  27   function ogw($username,$password,$host,$db)
  28   {
  29     $this->ogo  = new pgre_sql($username,$password,$host,$db);
  30
  31     if($this->ogo->is_connected){
  32       $this->validLocationTeam  = $this->ogo->GetLocationTeam();
  33       $this->validTemplateUser  = $this->ogo->GetTemplateUser();
  34       $this->validTeams         = $this->ogo->GetTeams();
  35       $this->connected = true;
  36     }else{
  37       $this->validLocationTeam  = array();//$this->ogo->GetLocationTeam();
  38       $this->validTemplateUser  = array();//$this->ogo->GetTemplateUser();
  39       $this->validTeams         = array();//$this->ogo->GetTeams();
  40       $this->connected = false;
  41     }
  42   }
  43
  44   function SetInfos($infos)
  45   {
  46     $infos['name1']              = $infos['name'];
  47     $this->info = $infos;
  48   }
  49
  50   function GetInfos($uid)
  51   {
  52     $ret = array();
  53     $qry = "SELECT  is_person,is_account,is_intra_account,is_extra_account,
  54                     number,owner_id,object_version,company_id,template_user_id,is_locked,
  55                     name,firstname,description,salutation,login,degree,birthday,sex
  56             FROM person WHERE login='".$uid."';";
  57     $res = $this->ogo->FetchAllRows($this->ogo->Query($qry));
  58     $ret = $res[0];
  59
  60     $qry = "SELECT street,zip,zipcity,country,state FROM address WHERE company_id = ".$ret['company_id']." limit 1;";
  61     $res = $this->ogo->FetchAllRows($this->ogo->Query($qry));
  62     $ret = array_merge($ret,$res[0]);
  63
  64     $qry = "SELECT company_id FROM company_assignment
  65             WHERE (sub_company_id=".$ret['company_id'].")
  66               AND company_id IN
  67                 (SELECT company_id FROM team  WHERE  (is_team=1)
  68                 AND
  69                   company_id NOT IN (SELECT company_id FROM team WHERE is_location_team=1));";
  70     $res = $this->ogo->FetchAllRows($this->ogo->Query($qry));
  71     if(is_array($res)){
  72       foreach($res as $r){
  73         $ret['TeamIDis'][]=$r['company_id'];
  74       }
  75     }else{
  76       $ret['TeamIDis']=array();
  77     }
  78
  79     $qry = "SELECT value_string from company_value WHERE company_id=".$ret['company_id'].";";
  80     $res = $this->ogo->FetchAllRows($this->ogo->Query($qry));
  81     $ret = array_merge($ret,$res[0]);
  82
  83     $qry ="SELECT company_id FROM company_assignment
  84            WHERE (sub_company_id=".$ret['company_id'].")
  85               AND company_id IN (SELECT company_id FROM team  WHERE  (is_location_team=1));";
  86     $res = $this->ogo->FetchAllRows($this->ogo->Query($qry));
  87     $ret['LocationTeamID'] = $res[0]['company_id'];
  88
  89     return($ret);
  90   }
  91
  92   function Perform($option)
  93   {
  94     if(!in_array($option,array("ADD","EDIT","REMOVE"))){
  95       $this->LastError = sprintf("Option '%s' is not allowed, possible options are 'ADD' 'EDIT' 'REMOVE'.",$option);
  96       return(false);
  97     }else{
  98       $this->option = $option;
  99       if(($this->option == "EDIT")||($this->option=="ADD")){
 100
 101         /* Static variables */
 102         $this->info['is_person']          = 1;
 103         $this->info['is_account']         = 1;
 104         $this->info['is_intra_account']   = 1;
 105         $this->info['is_extra_account']   = 0;
 106         $this->info['owner_id']           = 10000;
 107         $this->info['is_team']            = 0;
 108
 109         $this->InfoOK = $this->checkInfos();
 110
 111         if($this->InfoOK){
 112           $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 113           $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 114
 115           if($this->option == "ADD"){
 116             if($this->CheckExistence()){
 117               $this->LastError="Can't add entry already exists.";
 118               return(false);
 119             }else{
 120               $this->info['db_status']          = "inserted";
 121               $this->info['object_version']     = 1;
 122               return($this->ADD());
 123             }
 124           }else{
 125             if(!$this->CheckExistence()){
 126               $this->LastError="Can't edit entry, entry doesn't exists.";
 127               return(false);
 128             }else{
 129               $this->info['db_status']          = "edited";
 130               $this->info['object_version']     = $ist[0]['object_version']++;
 131               return($this->EDIT());
 132             }
 133           }
 134         }else{
 135           return($this->InfoOK);
 136         }
 137       }
 138       if($this->option == "REMOVE"){
 139
 140         if((!isset($this->info['login']))||(empty($this->info['login']))){
 141           $this->LastError = "Require login to detect existence";
 142           return(false);
 143         }else{
 144
 145           if($this->CheckExistence()){
 146             return($this->REMOVE());
 147           }else{
 148             $this->LastError="Can't remove non existing entry";
 149             return(false);
 150           }
 151         }
 152       }
 153     }
 154   }
 155
 156   function CheckExistence()
 157   {
 158     /* Check if thios entry already exists */
 159     $qry = "SELECT login,name FROM person WHERE login='".$this->info['login']."';";
 160     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 161     if($ist){
 162       return(true);
 163     }else{
 164       return(false);
 165     }
 166   }
 167
 168   function checkInfos()
 169   {
 170     foreach($this->MUST as $name){
 171       if((!isset($this->info[$name]))||(empty($this->info[$name]))){
 172         $this->LastError = sprintf("Must attribute '%s' wasn't set.",$name);
 173         return(false);
 174       }
 175     }
 176
 177     foreach($this->MAY as $name){
 178       if((!isset($this->info[$name]))||(empty($this->info[$name]))){
 179         $this->info[$name] = false;
 180       }
 181     }
 182
 183     $tmp = array(false,"",0);
 184     foreach($this->validLocationTeam as $id){
 185       $tmp[]= $id['company_id'];
 186     }
 187     if(!in_array($this->info['LocationTeamID'],$tmp)){
 188       $this->LastError = "Given 'Location Team' is invalid.";
 189       return(false);
 190     }
 191
 192     $tmp = array();
 193     foreach($this->validTemplateUser as $id){
 194       $tmp[]= $id['company_id'];
 195     }
 196     if(!in_array($this->info['template_user_id'],$tmp)){
 197       $this->LastError = "Given 'Template User ID' is invalid.";
 198       return(false);
 199     }
 200
 201
 202     $tmp = array();
 203     foreach($this->validTeams as $id){
 204       $tmp[]= $id['company_id'];
 205     }
 206     foreach($this->info['TeamIDis'] as $id){
 207       if(!in_array($id,$tmp)){
 208         $this->LastError = sprintf("Given 'Team ID':%s is invalid.",$id);
 209        return(false);
 210       }
 211     }
 212     return(true);
 213   }
 214
 215   function REMOVE()
 216   {
 217     $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 218     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 219     if((!$ist)||(!isset($ist[0]['company_id']))||($ist[0]['company_id']<=0)){
 220       $this->LastError(sprintf("Can't get company id for login %s",$this->info['login']));
 221       return(false);
 222     }else{
 223       $company_id     = $ist[0]['company_id'];
 224       $this->ogo->Query("DELETE FROM telephone WHERE company_id=".$company_id.";");
 225       $this->ogo->Query("DELETE FROM address  WHERE company_id=".$company_id.";");
 226       $this->ogo->Query("DELETE FROM company_info WHERE company_id=".$company_id.";");
 227       $this->ogo->Query("DELETE FROM company_value WHERE company_id=".$company_id.";");
 228       $this->ogo->Query("DELETE FROM company_assignment WHERE sub_company_id=".$company_id.";");
 229       $this->ogo->Query("DELETE FROM person WHERE company_id=".$company_id.";");
 230       $this->ogo->Query("DELETE FROM staff WHERE company_id=".$company_id.";");
 231       return(true);
 232     }
 233   }
 234
 235   function ADD()
 236   {
 237     /*
 238        Entry settings for personm table
 239      */
 240     $arr = array( "company_id","object_version","owner_id","template_user_id",
 241                   "is_person","is_account","is_intra_account","is_extra_account",
 242                   "number","description","is_locked","login","name","name","firstname",
 243                   "salutation","degree","birthday","sex","db_status");
 244     $this->info['company_id']  = $this->ogo->gen_id();
 245     $this->info['userID']             = "OGo".$this->info['company_id'];
 246     foreach($arr as $attr){
 247       if($attr == "number"){
 248         $add_user[$attr]  = $this->info['userID'];
 249       }else{
 250         $add_user[$attr]  = $this->info[$attr];
 251       }
 252     }
 253     $QUERY[] = gen_syntax($add_user,"person","ADD",false);
 254
 255
 256     /*
 257        Entry for staff table
 258      */
 259     $arr = array("staff_id","company_id","description","login","is_team","is_account","db_status");
 260     $this->info['staff_id'] = $this->ogo->gen_id();
 261     foreach($arr as $attr){
 262       $add_staff[$attr] = $this->info[$attr];
 263     }
 264     $QUERY[] = gen_syntax($add_staff,"staff","ADD",false);
 265
 266
 267     /*
 268        Create entries for company nfo
 269      */
 270     $arr = array("company_info_id","company_id","db_status");
 271     $this->info['company_info_id']  = $this->ogo->gen_id();
 272     foreach($arr as $attr){
 273       $add_company_info[$attr]  = $this->info[$attr];
 274     }
 275     $QUERY[] = gen_syntax($add_company_info,"company_info","ADD",false);
 276
 277
 278     /*
 279        Create entries for company value
 280      */
 281     $arr = array("db_status","value_string","attribute","company_id","company_value_id");
 282     $this->info['attribute']        = "email1";
 283     $this->info['company_value_id'] = $this->ogo->gen_id();
 284     foreach($arr as $attr){
 285       $add_company_value[$attr]  = $this->info[$attr];
 286     }
 287     $QUERY[] = gen_syntax($add_company_value,"company_value","ADD",false);
 288
 289
 290     /*
 291        address entries
 292      */
 293     $arr = array("company_id","address_id","street","zip","country","zipcity","state","db_status","name1","type");
 294     foreach(array("private","mailing","location") as  $type){
 295
 296       $this->info['address_id'] = $this->ogo->gen_id();
 297       $this->info['type']       = $type;
 298       foreach($arr as $attr){
 299         $add_address[$attr]  = $this->info[$attr];
 300       }
 301       $QUERY[] = gen_syntax($add_address,"address","ADD",false);
 302     }
 303
 304
 305     /*
 306        telephone entries
 307      */
 308     $arr = array("telephone_id","object_version","company_id","number","type","db_status");
 309     foreach(array("01_tel","02_tel","03_tel_funk","05_tel_private","10_fax","fax_private") as $type){
 310       $this->info['type']       = $type;
 311       $this->info['telephone_id']     = $this->ogo->gen_id();
 312       foreach($arr as $attr){
 313         $add_telephone[$attr]  = $this->info[$attr];
 314       }
 315       $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
 316     }
 317
 318     /*
 319        company_assignment entries (Location Team)
 320      */
 321     $this->info['old_company_id']       = $this->info['company_id'];
 322     $this->info['sub_company_id']       = $this->info['old_company_id'];
 323
 324     $this->info['company_assignment_id']= $this->ogo->gen_id();
 325     $this->info['company_id']           = $this->info['LocationTeamID'];
 326     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 327     foreach($arr as $attr){
 328       $add_company_assignment[$attr]  = $this->info[$attr];
 329     }
 330     $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 331
 332
 333     /*
 334        company_assignment entries (Teams)
 335      */
 336     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 337     foreach($this->info['TeamIDis'] as $TeamID){
 338
 339       $this->info['company_id']           = $TeamID;
 340       $this->info['sub_company_id']       = $this->info['old_company_id'];
 341       $this->info['company_assignment_id']= $this->ogo->gen_id();
 342       foreach($arr as $attr){
 343         $add_company_assignment[$attr]  = $this->info[$attr];
 344       }
 345       $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 346     }
 347
 348     $remove_all = false;
 349     foreach($QUERY as $q ){
 350       if(!$this->ogo->Query($q)){
 351         $remove_all = true;
 352         break;
 353       }
 354     }
 355
 356     if($remove_all== true){
 357       $this->ogo->Query("DELETE FROM telephone WHERE company_id=".$this->info['company_id'].";");
 358       $this->ogo->Query("DELETE FROM address WHERE company_id=".$this->info['company_id'].";");
 359       $this->ogo->Query("DELETE FROM company_assignment WHERE company_id=".$this->info['company_id'].";");
 360       $this->ogo->Query("DELETE FROM company_info WHERE company_id=".$this->info['company_id'].";");
 361       $this->ogo->Query("DELETE FROM company_value WHERE company_id=".$this->info['company_id'].";");
 362       $this->ogo->Query("DELETE FROM staff WHERE company_id=".$this->info['company_id'].";");
 363       $this->ogo->Query("DELETE FROM person WHERE company_id=".$this->info['company_id'].";");
 364       $this->LastError="Query failed, removed all added entries";
 365       return(false);
 366     }
 367     return(true);
 368   }
 369
 370
 371   function EDIT()
 372   {
 373     $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 374     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 375     /*
 376        Entry settings for personm table
 377      */
 378     $arr = array( "company_id","object_version","owner_id",
 379                   "template_user_id","is_person","is_account","is_intra_account",
 380                   "is_extra_account","number","description","is_locked","login","name",
 381                   "firstname","salutation","degree","birthday","sex","db_status");
 382     $this->info['company_id'] = $ist[0]['company_id'];
 383     $this->info['userID']     = "OGo".$this->info['company_id'];
 384     foreach($arr as $attr){
 385       if($attr == "number"){
 386         $add_user[$attr]  = $this->info['userID'];
 387       }else{
 388         $add_user[$attr]  = $this->info[$attr];
 389       }
 390     }
 391     $QUERY[] = gen_syntax($add_user,"person","EDIT",$ist);
 392
 393
 394     /*
 395        Entry for staff table
 396      */
 397     $arr = array("staff_id","company_id","description","login","is_team","is_account","db_status");
 398     $this->info['staff_id'] = $this->ogo->gen_id();
 399     foreach($arr as $attr){
 400       $add_staff[$attr] = $this->info[$attr];
 401     }
 402     $QUERY[] = gen_syntax($add_staff,"staff","EDIT",$ist);
 403
 404
 405     /*
 406        Create entries for company nfo
 407      */
 408     $arr = array("company_info_id","company_id","db_status");
 409     $this->info['company_info_id']    = $this->ogo->gen_id();
 410     foreach($arr as $attr){
 411       $add_company_info[$attr]  = $this->info[$attr];
 412     }
 413     $QUERY[] = gen_syntax($add_company_info,"company_info","EDIT",$ist);
 414
 415
 416     /*
 417        Create entries for company value
 418      */
 419     $QUERY[] = "DELETE FROM company_value WHERE company_id=".$ist[0]['company_id']." AND attribute='mail1';";
 420     $arr = array("db_status","value_string","attribute","company_id","company_value_id");
 421     $this->info['attribute']        = "email1";
 422     $this->info['company_value_id'] = $this->ogo->gen_id();
 423     foreach($arr as $attr){
 424       $add_company_value[$attr]  = $this->info[$attr];
 425     }
 426     $QUERY[] = gen_syntax($add_company_value,"company_value","ADD",false);
 427
 428
 429     /*
 430        address entries
 431      */
 432     $QUERY[] = "DELETE FROM address WHERE company_id=".$ist[0]['company_id'].";";
 433     $this->info['company_id'] = $ist[0]['company_id'];
 434     $arr = array("company_id","address_id","street","zip","country","zipcity","state","db_status","name1","type");
 435     foreach(array("private","mailing","location") as  $type){
 436       $this->info['type']       = $type;
 437       $this->info['address_id'] = $this->ogo->gen_id();
 438       foreach($arr as $attr){
 439         $add_address[$attr]  = $this->info[$attr];
 440       }
 441       $QUERY[] = gen_syntax($add_address,"address","ADD",false);
 442     }
 443
 444
 445     /*
 446        telephone entries
 447      */
 448     $QUERY[] = "DELETE FROM telephone WHERE company_id=".$ist[0]['company_id'].";";
 449     $this->info['company_id'] = $ist[0]['company_id'];
 450     $arr = array("telephone_id","object_version","company_id","number","type","db_status");
 451     foreach(array("01_tel","02_tel","03_tel_funk","05_tel_private","10_fax","fax_private") as $type){
 452       $this->info['type']           = $type;
 453       $this->info['telephone_id']   = $this->ogo->gen_id();
 454       foreach($arr as $attr){
 455         $add_telephone[$attr]  = $this->info[$attr];
 456       }
 457
 458       $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
 459     }
 460
 461
 462     /*
 463        company_assignment entries (Location Team)
 464      */
 465     $this->info['old_company_id'] = $this->info['company_id'];
 466
 467     /* First remove location team */
 468     $QUERY[] = "DELETE FROM company_assignment WHERE (sub_company_id=".$ist[0]['company_id'].") AND
 469       company_id in (SELECT company_id FROM team WHERE is_location_team=1);";
 470
 471     $this->info['sub_company_id']       = $ist[0]['company_id'];
 472     $this->info['company_assignment_id']= $this->ogo->gen_id();
 473     $this->info['company_id']           = $this->info['LocationTeamID'];
 474     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 475     foreach($arr as $attr){
 476       $add_company_assignment[$attr]  = $this->info[$attr];
 477     }
 478     $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 479
 480
 481     /*
 482        company_assignment entries (Teams)
 483      */
 484     /* First remove location team */
 485     $QUERY[] = "DELETE FROM company_assignment
 486                 WHERE
 487                     (sub_company_id=".$ist[0]['company_id'].")
 488                   AND
 489                     company_id IN
 490                       (SELECT company_id FROM team  WHERE  (is_team=1) AND company_id NOT IN
 491                          (SELECT company_id FROM team WHERE is_location_team=1));";
 492
 493     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 494     foreach($this->info['TeamIDis'] as $TeamID){
 495       $this->info['company_id']           = $TeamID;
 496       $this->info['sub_company_id']       = $ist[0]['company_id'];
 497       $this->info['company_assignment_id']= $this->ogo->gen_id();
 498       foreach($arr as $attr){
 499         $add_company_assignment[$attr]  = $this->info[$attr];
 500       }
 501       $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 502     }
 503
 504     $remove_all = false;
 505
 506     foreach($QUERY as $q ){
 507       if(!$this->ogo-> Query($q)){
 508         print $q;
 509         $remove_all = true;
 510         break;
 511       }
 512     }
 513   }
 514 }
 515
 516 // vim:tabstop=2:expandtab:shiftwidth=2:filetype=php:syntax:ruler:
 517 ?>