1 <?php
3 require_once("class_pgsql_opengw.inc");
5 class ogw{
6 var $info;
7 var $ogo;
9 var $validLocationTeam;
10 var $validTemplateUser;
11 var $validTeams;
14 var $InfoOK = false;
16 var $MUST = array("name","login","template_user_id");
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");
23 var $LastError = "";
24 var $option = "";
25 var $connected = false;
27 function ogw($username,$password,$host,$db)
28 {
29 $this->ogo = new pgre_sql($username,$password,$host,$db);
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 }
44 function SetInfos($infos)
45 {
46 if(isset($infos['name'])) {
47 $infos['name1'] = $infos['name'];
48 }
49 $this->info = $infos;
50 }
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];
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]);
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 }
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]);
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'];
91 return($ret);
92 }
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")){
103 /* Static variables */
104 if(!isset($this->info['is_person'])){
105 $this->info['is_person'] = 1;
106 }
108 if(!isset($this->is_account)){
109 $this->info['is_account'] = 1;
110 }
112 if(!isset($this->info['is_intra_account'])){
113 $this->info['is_intra_account'] = 1;
114 }
116 if(!isset($this->info['is_extra_account'])){
117 $this->info['is_extra_account'] = 0;
118 }
120 if(!isset($this->info['owner_id'])){
121 $this->info['owner_id'] = 10000;
122 }
124 if(!isset($this->info['is_team'])){
125 $this->info['is_team'] = 0;
126 }
128 $this->InfoOK = $this->checkInfos();
130 if($this->InfoOK){
131 $qry = "SELECT * FROM person WHERE login='".$this->info['login']."';";
132 $ist = $this->ogo->FetchAllRows($this->ogo->Query($qry));
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"){
159 if((!isset($this->info['login']))||(empty($this->info['login']))){
160 $this->LastError = "Require login to detect existence";
161 return(false);
162 }else{
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 }
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 }
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 }
196 foreach($this->MAY as $name){
197 if((!isset($this->info[$name]))||(empty($this->info[$name]))){
198 $this->info[$name] = false;
199 }
200 }
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 }
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 }
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 }
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{
243 $company_id = $ist[0]['company_id'];
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 }
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);
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);
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);
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);
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){
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 }
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 }
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'];
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);
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){
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 }
373 $remove_all = false;
374 foreach($QUERY as $q ){
375 if(!$this->ogo->Query($q)){
376 $remove_all = true;
377 break;
378 }
379 }
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 }
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);
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);
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);
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);
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 }
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 }
483 $QUERY[] = gen_syntax($add_telephone,"telephone","ADD",false);
484 }
487 /*
488 company_assignment entries (Location Team)
489 */
490 $this->info['old_company_id'] = $this->info['company_id'];
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);";
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);
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));";
518 $arr = array("company_assignment_id","company_id","sub_company_id","db_status");
519 foreach($this->info['TeamIDis'] as $TeamID){
520 $this->info['company_id'] = $TeamID;
521 $this->info['sub_company_id'] = $ist[0]['company_id'];
522 $this->info['company_assignment_id']= $this->ogo->gen_id();
523 foreach($arr as $attr){
524 $add_company_assignment[$attr] = $this->info[$attr];
525 }
526 $QUERY[] = gen_syntax($add_company_assignment,"company_assignment","ADD",false);
527 }
529 $remove_all = false;
531 foreach($QUERY as $q ){
532 if(!$this->ogo-> Query($q)){
533 print $q;
534 $remove_all = true;
535 break;
536 }
537 }
538 }
539 }
541 // vim:tabstop=2:expandtab:shiftwidth=2:filetype=php:syntax:ruler:
542 ?>