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