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         if(!isset($this->info['is_person'])){
 103           $this->info['is_person']          = 1;
 104         }
 105
 106         if(!isset($this->is_account)){
 107           $this->info['is_account']         = 1;
 108         }
 109
 110         if(!isset($this->info['is_intra_account'])){
 111           $this->info['is_intra_account']   = 1;
 112         }
 113
 114         if(!isset($this->info['is_extra_account'])){
 115           $this->info['is_extra_account']   = 0;
 116         }
 117
 118         if(!isset($this->info['owner_id'])){
 119           $this->info['owner_id']           = 10000;
 120         }
 121
 122         if(!isset($this->info['is_team'])){
 123           $this->info['is_team']            = 0;
 124         }
 125
 126         $this->InfoOK = $this->checkInfos();
 127
 128         if($this->InfoOK){
 129           $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 130           $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 131
 132           if($this->option == "ADD"){
 133             if($this->CheckExistence()){
 134               $this->LastError="Can't add entry already exists.";
 135               return(false);
 136             }else{
 137               $this->info['db_status']          = "inserted";
 138               $this->info['object_version']     = 1;
 139               return($this->ADD());
 140             }
 141           }else{
 142             if(!$this->CheckExistence()){
 143               $this->LastError="Can't edit entry, entry doesn't exists.";
 144               return(false);
 145             }else{
 146               $this->info['db_status']          = "updated";
 147               $this->info['object_version']     = $ist[0]['object_version']++;
 148               return($this->EDIT());
 149             }
 150           }
 151         }else{
 152           return($this->InfoOK);
 153         }
 154       }
 155       if($this->option == "REMOVE"){
 156
 157         if((!isset($this->info['login']))||(empty($this->info['login']))){
 158           $this->LastError = "Require login to detect existence";
 159           return(false);
 160         }else{
 161
 162           if($this->CheckExistence()){
 163             return($this->REMOVE());
 164           }else{
 165             $this->LastError="Can't remove non existing entry";
 166             return(false);
 167           }
 168         }
 169       }
 170     }
 171   }
 172
 173   function CheckExistence()
 174   {
 175     /* Check if thios entry already exists */
 176     $qry = "SELECT login,name FROM person WHERE login='".$this->info['login']."';";
 177     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 178     if($ist){
 179       return(true);
 180     }else{
 181       return(false);
 182     }
 183   }
 184
 185   function checkInfos()
 186   {
 187     foreach($this->MUST as $name){
 188       if((!isset($this->info[$name]))||(empty($this->info[$name]))){
 189         $this->LastError = sprintf("Must attribute '%s' wasn't set.",$name);
 190         return(false);
 191       }
 192     }
 193
 194     foreach($this->MAY as $name){
 195       if((!isset($this->info[$name]))||(empty($this->info[$name]))){
 196         $this->info[$name] = false;
 197       }
 198     }
 199
 200     $tmp = array(false,"",0);
 201     foreach($this->validLocationTeam as $id){
 202       $tmp[]= $id['company_id'];
 203     }
 204     if(!in_array($this->info['LocationTeamID'],$tmp)){
 205       $this->LastError = "Given 'Location Team' is invalid.";
 206       return(false);
 207     }
 208
 209     $tmp = array();
 210     foreach($this->validTemplateUser as $id){
 211       $tmp[]= $id['company_id'];
 212     }
 213     if(!in_array($this->info['template_user_id'],$tmp)){
 214       $this->LastError = "Given 'Template User ID' is invalid.";
 215       return(false);
 216     }
 217
 218
 219     $tmp = array();
 220     foreach($this->validTeams as $id){
 221       $tmp[]= $id['company_id'];
 222     }
 223     foreach($this->info['TeamIDis'] as $id){
 224       if(!in_array($id,$tmp)){
 225         $this->LastError = sprintf("Given 'Team ID':%s is invalid.",$id);
 226        return(false);
 227       }
 228     }
 229     return(true);
 230   }
 231
 232   function REMOVE()
 233   {
 234     $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 235     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 236     if((!$ist)||(!isset($ist[0]['company_id']))||($ist[0]['company_id']<=0)){
 237       $this->LastError(sprintf("Can't get company id for login %s",$this->info['login']));
 238       return(false);
 239     }else{
 240
 241       $company_id     = $ist[0]['company_id'];
 242
 243       $qry = "UPDATE person SET login='SKY".$company_id.$this->info['login']."', is_account=0, is_intra_account=0 WHERE company_id=".$company_id.";";
 244       $this->ogo->Query($qry);
 245       /*
 246       $this->ogo->Query("DELETE FROM telephone WHERE company_id=".$company_id.";");
 247       $this->ogo->Query("DELETE FROM address  WHERE company_id=".$company_id.";");
 248       $this->ogo->Query("DELETE FROM company_info WHERE company_id=".$company_id.";");
 249       $this->ogo->Query("DELETE FROM company_value WHERE company_id=".$company_id.";");
 250       $this->ogo->Query("DELETE FROM company_assignment WHERE sub_company_id=".$company_id.";");
 251       $this->ogo->Query("DELETE FROM person WHERE company_id=".$company_id.";");
 252       $this->ogo->Query("DELETE FROM staff WHERE company_id=".$company_id.";");
 253       */
 254       return(true);
 255     }
 256   }
 257
 258   function ADD()
 259   {
 260     /*
 261        Entry settings for personm table
 262      */
 263     $arr = array( "company_id","object_version","owner_id","template_user_id",
 264                   "is_person","is_account","is_intra_account","is_extra_account",
 265                   "number","description","is_locked","login","name","name","firstname",
 266                   "salutation","degree","birthday","sex","db_status");
 267     $this->info['company_id']  = $this->ogo->gen_id();
 268     $this->info['userID']             = "OGo".$this->info['company_id'];
 269     foreach($arr as $attr){
 270       if($attr == "number"){
 271         $add_user[$attr]  = $this->info['userID'];
 272       }else{
 273         $add_user[$attr]  = $this->info[$attr];
 274       }
 275     }
 276     $QUERY[] = gen_syntax($add_user,"person","ADD",false);
 277
 278
 279     /*
 280        Entry for staff table
 281      */
 282     $arr = array("staff_id","company_id","description","login","is_team","is_account","db_status");
 283     $this->info['staff_id'] = $this->ogo->gen_id();
 284     foreach($arr as $attr){
 285       $add_staff[$attr] = $this->info[$attr];
 286     }
 287     $QUERY[] = gen_syntax($add_staff,"staff","ADD",false);
 288
 289
 290     /*
 291        Create entries for company nfo
 292      */
 293     $arr = array("company_info_id","company_id","db_status");
 294     $this->info['company_info_id']  = $this->ogo->gen_id();
 295     foreach($arr as $attr){
 296       $add_company_info[$attr]  = $this->info[$attr];
 297     }
 298     $QUERY[] = gen_syntax($add_company_info,"company_info","ADD",false);
 299
 300
 301     /*
 302        Create entries for company value
 303      */
 304     $arr = array("db_status","value_string","attribute","company_id","company_value_id");
 305     $this->info['attribute']        = "email1";
 306     $this->info['company_value_id'] = $this->ogo->gen_id();
 307     foreach($arr as $attr){
 308       $add_company_value[$attr]  = $this->info[$attr];
 309     }
 310     $QUERY[] = gen_syntax($add_company_value,"company_value","ADD",false);
 311
 312
 313     /*
 314        address entries
 315      */
 316     $arr = array("company_id","address_id","street","zip","country","zipcity","state","db_status","name1","type");
 317     foreach(array("private","mailing","location") as  $type){
 318
 319       $this->info['address_id'] = $this->ogo->gen_id();
 320       $this->info['type']       = $type;
 321       foreach($arr as $attr){
 322         $add_address[$attr]  = $this->info[$attr];
 323       }
 324       $QUERY[] = gen_syntax($add_address,"address","ADD",false);
 325     }
 326
 327
 328     /*
 329        telephone entries
 330      */
 331     $arr = array("telephone_id","object_version","company_id","number","type","db_status");
 332     foreach(array("01_tel","02_tel","03_tel_funk","05_tel_private","10_fax","fax_private") as $type){
 333       $this->info['type']       = $type;
 334       $this->info['telephone_id']     = $this->ogo->gen_id();
 335       foreach($arr as $attr){
 336         $add_telephone[$attr]  = $this->info[$attr];
 337       }
 338       $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
 339     }
 340
 341     /*
 342        company_assignment entries (Location Team)
 343      */
 344     $this->info['old_company_id']       = $this->info['company_id'];
 345     $this->info['sub_company_id']       = $this->info['old_company_id'];
 346
 347     $this->info['company_assignment_id']= $this->ogo->gen_id();
 348     $this->info['company_id']           = $this->info['LocationTeamID'];
 349     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 350     foreach($arr as $attr){
 351       $add_company_assignment[$attr]  = $this->info[$attr];
 352     }
 353     $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 354
 355
 356     /*
 357        company_assignment entries (Teams)
 358      */
 359     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 360     foreach($this->info['TeamIDis'] as $TeamID){
 361
 362       $this->info['company_id']           = $TeamID;
 363       $this->info['sub_company_id']       = $this->info['old_company_id'];
 364       $this->info['company_assignment_id']= $this->ogo->gen_id();
 365       foreach($arr as $attr){
 366         $add_company_assignment[$attr]  = $this->info[$attr];
 367       }
 368       $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 369     }
 370
 371     $remove_all = false;
 372     foreach($QUERY as $q ){
 373       if(!$this->ogo->Query($q)){
 374         $remove_all = true;
 375         break;
 376       }
 377     }
 378
 379     if($remove_all== true){
 380       $this->ogo->Query("DELETE FROM telephone WHERE company_id=".$this->info['company_id'].";");
 381       $this->ogo->Query("DELETE FROM address WHERE company_id=".$this->info['company_id'].";");
 382       $this->ogo->Query("DELETE FROM company_assignment WHERE company_id=".$this->info['company_id'].";");
 383       $this->ogo->Query("DELETE FROM company_info WHERE company_id=".$this->info['company_id'].";");
 384       $this->ogo->Query("DELETE FROM company_value WHERE company_id=".$this->info['company_id'].";");
 385       $this->ogo->Query("DELETE FROM staff WHERE company_id=".$this->info['company_id'].";");
 386       $this->ogo->Query("DELETE FROM person WHERE company_id=".$this->info['company_id'].";");
 387       $this->LastError="Query failed, removed all added entries";
 388       return(false);
 389     }
 390     return(true);
 391   }
 392
 393
 394   function EDIT()
 395   {
 396     $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 397     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 398     /*
 399        Entry settings for personm table
 400      */
 401     $arr = array( "company_id","object_version","owner_id",
 402                   "template_user_id","is_person","is_account","is_intra_account",
 403                   "is_extra_account","number","description","is_locked","login","name",
 404                   "firstname","salutation","degree","birthday","sex","db_status");
 405     $this->info['company_id'] = $ist[0]['company_id'];
 406     $this->info['userID']     = "OGo".$this->info['company_id'];
 407     foreach($arr as $attr){
 408       if($attr == "number"){
 409         $add_user[$attr]  = $this->info['userID'];
 410       }else{
 411         $add_user[$attr]  = $this->info[$attr];
 412       }
 413     }
 414     $QUERY[] = gen_syntax($add_user,"person","EDIT",$ist);
 415
 416
 417     /*
 418        Entry for staff table
 419      */
 420     $arr = array("staff_id","company_id","description","login","is_team","is_account","db_status");
 421     $this->info['staff_id'] = $this->ogo->gen_id();
 422     foreach($arr as $attr){
 423       $add_staff[$attr] = $this->info[$attr];
 424     }
 425     $QUERY[] = gen_syntax($add_staff,"staff","EDIT",$ist);
 426
 427
 428     /*
 429        Create entries for company nfo
 430      */
 431     $arr = array("company_info_id","company_id","db_status");
 432     $this->info['company_info_id']    = $this->ogo->gen_id();
 433     foreach($arr as $attr){
 434       $add_company_info[$attr]  = $this->info[$attr];
 435     }
 436     $QUERY[] = gen_syntax($add_company_info,"company_info","EDIT",$ist);
 437
 438
 439     /*
 440        Create entries for company value
 441      */
 442     $QUERY[] = "DELETE FROM company_value WHERE company_id=".$ist[0]['company_id']." AND attribute='mail1';";
 443     $arr = array("db_status","value_string","attribute","company_id","company_value_id");
 444     $this->info['attribute']        = "email1";
 445     $this->info['company_value_id'] = $this->ogo->gen_id();
 446     foreach($arr as $attr){
 447       $add_company_value[$attr]  = $this->info[$attr];
 448     }
 449     $QUERY[] = gen_syntax($add_company_value,"company_value","ADD",false);
 450
 451
 452     /*
 453        address entries
 454      */
 455     $QUERY[] = "DELETE FROM address WHERE company_id=".$ist[0]['company_id'].";";
 456     $this->info['company_id'] = $ist[0]['company_id'];
 457     $arr = array("company_id","address_id","street","zip","country","zipcity","state","db_status","name1","type");
 458     foreach(array("private","mailing","location") as  $type){
 459       $this->info['type']       = $type;
 460       $this->info['address_id'] = $this->ogo->gen_id();
 461       foreach($arr as $attr){
 462         $add_address[$attr]  = $this->info[$attr];
 463       }
 464       $QUERY[] = gen_syntax($add_address,"address","ADD",false);
 465     }
 466
 467
 468     /*
 469        telephone entries
 470      */
 471     $QUERY[] = "DELETE FROM telephone WHERE company_id=".$ist[0]['company_id'].";";
 472     $this->info['company_id'] = $ist[0]['company_id'];
 473     $arr = array("telephone_id","object_version","company_id","number","type","db_status");
 474     foreach(array("01_tel","02_tel","03_tel_funk","05_tel_private","10_fax","fax_private") as $type){
 475       $this->info['type']           = $type;
 476       $this->info['telephone_id']   = $this->ogo->gen_id();
 477       foreach($arr as $attr){
 478         $add_telephone[$attr]  = $this->info[$attr];
 479       }
 480
 481       $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
 482     }
 483
 484
 485     /*
 486        company_assignment entries (Location Team)
 487      */
 488     $this->info['old_company_id'] = $this->info['company_id'];
 489
 490     /* First remove location team */
 491     $QUERY[] = "DELETE FROM company_assignment WHERE (sub_company_id=".$ist[0]['company_id'].") AND
 492       company_id in (SELECT company_id FROM team WHERE is_location_team=1);";
 493
 494     $this->info['sub_company_id']       = $ist[0]['company_id'];
 495     $this->info['company_assignment_id']= $this->ogo->gen_id();
 496     $this->info['company_id']           = $this->info['LocationTeamID'];
 497     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 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     /*
 505        company_assignment entries (Teams)
 506      */
 507     /* First remove location team */
 508     $QUERY[] = "DELETE FROM company_assignment
 509                 WHERE
 510                     (sub_company_id=".$ist[0]['company_id'].")
 511                   AND
 512                     company_id IN
 513                       (SELECT company_id FROM team  WHERE  (is_team=1) AND company_id NOT IN
 514                          (SELECT company_id FROM team WHERE is_location_team=1));";
 515
 516     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 517     foreach($this->info['TeamIDis'] as $TeamID){
 518       $this->info['company_id']           = $TeamID;
 519       $this->info['sub_company_id']       = $ist[0]['company_id'];
 520       $this->info['company_assignment_id']= $this->ogo->gen_id();
 521       foreach($arr as $attr){
 522         $add_company_assignment[$attr]  = $this->info[$attr];
 523       }
 524       $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 525     }
 526
 527     $remove_all = false;
 528
 529     foreach($QUERY as $q ){
 530       if(!$this->ogo-> Query($q)){
 531         print $q;
 532         $remove_all = true;
 533         break;
 534       }
 535     }
 536   }
 537 }
 538
 539 // vim:tabstop=2:expandtab:shiftwidth=2:filetype=php:syntax:ruler:
 540 ?>