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     foreach($this->info['TeamIDis'] as $id){
 226       if(!in_array($id,$tmp)){
 227         $this->LastError = sprintf("Given 'Team ID':%s is invalid.",$id);
 228        return(false);
 229       }
 230     }
 231     return(true);
 232   }
 233
 234   function REMOVE()
 235   {
 236     $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 237     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 238     if((!$ist)||(!isset($ist[0]['company_id']))||($ist[0]['company_id']<=0)){
 239       $this->LastError(sprintf("Can't get company id for login %s",$this->info['login']));
 240       return(false);
 241     }else{
 242
 243       $company_id     = $ist[0]['company_id'];
 244
 245       $qry = "UPDATE person SET login='SKY".$company_id.$this->info['login']."', is_account=0, is_intra_account=0 WHERE company_id=".$company_id.";";
 246       $this->ogo->Query($qry);
 247       /*
 248       $this->ogo->Query("DELETE FROM telephone WHERE company_id=".$company_id.";");
 249       $this->ogo->Query("DELETE FROM address  WHERE company_id=".$company_id.";");
 250       $this->ogo->Query("DELETE FROM company_info WHERE company_id=".$company_id.";");
 251       $this->ogo->Query("DELETE FROM company_value WHERE company_id=".$company_id.";");
 252       $this->ogo->Query("DELETE FROM company_assignment WHERE sub_company_id=".$company_id.";");
 253       $this->ogo->Query("DELETE FROM person WHERE company_id=".$company_id.";");
 254       $this->ogo->Query("DELETE FROM staff WHERE company_id=".$company_id.";");
 255       */
 256       return(true);
 257     }
 258   }
 259
 260   function ADD()
 261   {
 262     /*
 263        Entry settings for personm table
 264      */
 265     $arr = array( "company_id","object_version","owner_id","template_user_id",
 266                   "is_person","is_account","is_intra_account","is_extra_account",
 267                   "number","description","is_locked","login","name","name","firstname",
 268                   "salutation","degree","birthday","sex","db_status","password");
 269     $this->info['company_id']  = $this->ogo->gen_id();
 270     $this->info['userID']             = "OGo".$this->info['company_id'];
 271     foreach($arr as $attr){
 272       if($attr == "number"){
 273         $add_user[$attr]  = $this->info['userID'];
 274       }else{
 275         $add_user[$attr]  = $this->info[$attr];
 276       }
 277     }
 278     $QUERY[] = gen_syntax($add_user,"person","ADD",false);
 279
 280
 281     /*
 282        Entry for staff table
 283      */
 284     $arr = array("staff_id","company_id","description","login","is_team","is_account","db_status");
 285     $this->info['staff_id'] = $this->ogo->gen_id();
 286     foreach($arr as $attr){
 287       $add_staff[$attr] = $this->info[$attr];
 288     }
 289     $QUERY[] = gen_syntax($add_staff,"staff","ADD",false);
 290
 291
 292     /*
 293        Create entries for company nfo
 294      */
 295     $arr = array("company_info_id","company_id","db_status");
 296     $this->info['company_info_id']  = $this->ogo->gen_id();
 297     foreach($arr as $attr){
 298       $add_company_info[$attr]  = $this->info[$attr];
 299     }
 300     $QUERY[] = gen_syntax($add_company_info,"company_info","ADD",false);
 301
 302
 303     /*
 304        Create entries for company value
 305      */
 306     $arr = array("db_status","value_string","attribute","company_id","company_value_id");
 307     $this->info['attribute']        = "email1";
 308     $this->info['company_value_id'] = $this->ogo->gen_id();
 309     foreach($arr as $attr){
 310       $add_company_value[$attr]  = $this->info[$attr];
 311     }
 312     $QUERY[] = gen_syntax($add_company_value,"company_value","ADD",false);
 313
 314
 315     /*
 316        address entries
 317      */
 318     $arr = array("company_id","address_id","street","zip","country","zipcity","state","db_status","name1","type");
 319     foreach(array("private","mailing","location") as  $type){
 320
 321       $this->info['address_id'] = $this->ogo->gen_id();
 322       $this->info['type']       = $type;
 323       foreach($arr as $attr){
 324         $add_address[$attr]  = $this->info[$attr];
 325       }
 326       $QUERY[] = gen_syntax($add_address,"address","ADD",false);
 327     }
 328
 329
 330     /*
 331        telephone entries
 332      */
 333     $arr = array("telephone_id","object_version","company_id","number","type","db_status");
 334     foreach(array("01_tel","02_tel","03_tel_funk","05_tel_private","10_fax","fax_private") as $type){
 335       $this->info['type']       = $type;
 336       $this->info['telephone_id']     = $this->ogo->gen_id();
 337       foreach($arr as $attr){
 338         $add_telephone[$attr]  = $this->info[$attr];
 339       }
 340       $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
 341     }
 342
 343     /*
 344        company_assignment entries (Location Team)
 345      */
 346     $this->info['old_company_id']       = $this->info['company_id'];
 347     $this->info['sub_company_id']       = $this->info['old_company_id'];
 348
 349     $this->info['company_assignment_id']= $this->ogo->gen_id();
 350     $this->info['company_id']           = $this->info['LocationTeamID'];
 351     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 352     foreach($arr as $attr){
 353       $add_company_assignment[$attr]  = $this->info[$attr];
 354     }
 355     $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 356
 357
 358     /*
 359        company_assignment entries (Teams)
 360      */
 361     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 362     foreach($this->info['TeamIDis'] as $TeamID){
 363
 364       $this->info['company_id']           = $TeamID;
 365       $this->info['sub_company_id']       = $this->info['old_company_id'];
 366       $this->info['company_assignment_id']= $this->ogo->gen_id();
 367       foreach($arr as $attr){
 368         $add_company_assignment[$attr]  = $this->info[$attr];
 369       }
 370       $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 371     }
 372
 373     $remove_all = false;
 374     foreach($QUERY as $q ){
 375       if(!$this->ogo->Query($q)){
 376         $remove_all = true;
 377         break;
 378       }
 379     }
 380
 381     if($remove_all== true){
 382       $this->ogo->Query("DELETE FROM telephone WHERE company_id=".$this->info['company_id'].";");
 383       $this->ogo->Query("DELETE FROM address WHERE company_id=".$this->info['company_id'].";");
 384       $this->ogo->Query("DELETE FROM company_assignment WHERE company_id=".$this->info['company_id'].";");
 385       $this->ogo->Query("DELETE FROM company_info WHERE company_id=".$this->info['company_id'].";");
 386       $this->ogo->Query("DELETE FROM company_value WHERE company_id=".$this->info['company_id'].";");
 387       $this->ogo->Query("DELETE FROM staff WHERE company_id=".$this->info['company_id'].";");
 388       $this->ogo->Query("DELETE FROM person WHERE company_id=".$this->info['company_id'].";");
 389       $this->LastError="Query failed, removed all added entries";
 390       return(false);
 391     }
 392     return(true);
 393   }
 394
 395
 396   function EDIT()
 397   {
 398     $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
 399     $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
 400     /*
 401        Entry settings for personm table
 402      */
 403     $arr = array( "company_id","object_version","owner_id",
 404                   "template_user_id","is_person","is_account","is_intra_account",
 405                   "is_extra_account","number","description","is_locked","login","name",
 406                   "firstname","salutation","degree","birthday","sex","db_status","password");
 407     $this->info['company_id'] = $ist[0]['company_id'];
 408     $this->info['userID']     = "OGo".$this->info['company_id'];
 409     foreach($arr as $attr){
 410       if($attr == "number"){
 411         $add_user[$attr]  = $this->info['userID'];
 412       }else{
 413         $add_user[$attr]  = $this->info[$attr];
 414       }
 415     }
 416     $QUERY[] = gen_syntax($add_user,"person","EDIT",$ist);
 417
 418
 419     /*
 420        Entry for staff table
 421      */
 422     $arr = array("staff_id","company_id","description","login","is_team","is_account","db_status");
 423     $this->info['staff_id'] = $this->ogo->gen_id();
 424     foreach($arr as $attr){
 425       $add_staff[$attr] = $this->info[$attr];
 426     }
 427     $QUERY[] = gen_syntax($add_staff,"staff","EDIT",$ist);
 428
 429
 430     /*
 431        Create entries for company nfo
 432      */
 433     $arr = array("company_info_id","company_id","db_status");
 434     $this->info['company_info_id']    = $this->ogo->gen_id();
 435     foreach($arr as $attr){
 436       $add_company_info[$attr]  = $this->info[$attr];
 437     }
 438     $QUERY[] = gen_syntax($add_company_info,"company_info","EDIT",$ist);
 439
 440
 441     /*
 442        Create entries for company value
 443      */
 444     $QUERY[] = "DELETE FROM company_value WHERE company_id=".$ist[0]['company_id']." AND attribute='mail1';";
 445     $arr = array("db_status","value_string","attribute","company_id","company_value_id");
 446     $this->info['attribute']        = "email1";
 447     $this->info['company_value_id'] = $this->ogo->gen_id();
 448     foreach($arr as $attr){
 449       $add_company_value[$attr]  = $this->info[$attr];
 450     }
 451     $QUERY[] = gen_syntax($add_company_value,"company_value","ADD",false);
 452
 453
 454     /*
 455        address entries
 456      */
 457     $QUERY[] = "DELETE FROM address WHERE company_id=".$ist[0]['company_id'].";";
 458     $this->info['company_id'] = $ist[0]['company_id'];
 459     $arr = array("company_id","address_id","street","zip","country","zipcity","state","db_status","name1","type");
 460     foreach(array("private","mailing","location") as  $type){
 461       $this->info['type']       = $type;
 462       $this->info['address_id'] = $this->ogo->gen_id();
 463       foreach($arr as $attr){
 464         $add_address[$attr]  = $this->info[$attr];
 465       }
 466       $QUERY[] = gen_syntax($add_address,"address","ADD",false);
 467     }
 468
 469
 470     /*
 471        telephone entries
 472      */
 473     $QUERY[] = "DELETE FROM telephone WHERE company_id=".$ist[0]['company_id'].";";
 474     $this->info['company_id'] = $ist[0]['company_id'];
 475     $arr = array("telephone_id","object_version","company_id","number","type","db_status");
 476     foreach(array("01_tel","02_tel","03_tel_funk","05_tel_private","10_fax","fax_private") as $type){
 477       $this->info['type']           = $type;
 478       $this->info['telephone_id']   = $this->ogo->gen_id();
 479       foreach($arr as $attr){
 480         $add_telephone[$attr]  = $this->info[$attr];
 481       }
 482
 483       $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
 484     }
 485
 486
 487     /*
 488        company_assignment entries (Location Team)
 489      */
 490     $this->info['old_company_id'] = $this->info['company_id'];
 491
 492     /* First remove location team */
 493     $QUERY[] = "DELETE FROM company_assignment WHERE (sub_company_id=".$ist[0]['company_id'].") AND
 494       company_id in (SELECT company_id FROM team WHERE is_location_team=1);";
 495
 496     $this->info['sub_company_id']       = $ist[0]['company_id'];
 497     $this->info['company_assignment_id']= $this->ogo->gen_id();
 498     $this->info['company_id']           = $this->info['LocationTeamID'];
 499     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 500     foreach($arr as $attr){
 501       $add_company_assignment[$attr]  = $this->info[$attr];
 502     }
 503     $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 504
 505
 506     /*
 507        company_assignment entries (Teams)
 508      */
 509     /* First remove location team */
 510     $QUERY[] = "DELETE FROM company_assignment
 511                 WHERE
 512                     (sub_company_id=".$ist[0]['company_id'].")
 513                   AND
 514                     company_id IN
 515                       (SELECT company_id FROM team  WHERE  (is_team=1) AND company_id NOT IN
 516                          (SELECT company_id FROM team WHERE is_location_team=1));";
 517
 518     $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
 519
 520     if(is_array($this->info['TeamIDis'])){
 521       foreach($this->info['TeamIDis'] as $TeamID){
 522         $this->info['company_id']           = $TeamID;
 523         $this->info['sub_company_id']       = $ist[0]['company_id'];
 524         $this->info['company_assignment_id']= $this->ogo->gen_id();
 525         $add_company_assignment = array();
 526         foreach($arr as $attr){
 527           $add_company_assignment[$attr]  = $this->info[$attr];
 528         }
 529         $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
 530       }
 531     }
 532     $remove_all = false;
 533
 534     foreach($QUERY as $q ){
 535       if(!$this->ogo-> Query($q)){
 536         print $q;
 537         $remove_all = true;
 538         break;
 539       }
 540     }
 541   }
 542 }
 543
 544 // vim:tabstop=2:expandtab:shiftwidth=2:filetype=php:syntax:ruler:
 545 ?>