ILIAS Release_3_7_x_branch .rev 46817: calendar/classes/class.Database.php Source File

Go to the documentation of this file.
00001 <?php
00002 /*
00003         +-----------------------------------------------------------------------------+
00004         | ILIAS open source                                                                                                                       |
00005         |       Dateplaner Modul                                                                                                                  |                                                                                                     
00006         +-----------------------------------------------------------------------------+
00007         | Copyright (c) 2004 ILIAS open source & University of Applied Sciences Bremen|
00008         |                                                                             |
00009         | This program is free software; you can redistribute it and/or               |
00010         | modify it under the terms of the GNU General Public License                 |
00011         | as published by the Free Software Foundation; either version 2              |
00012         | of the License, or (at your option) any later version.                      |
00013         |                                                                             |
00014         | This program is distributed in the hope that it will be useful,             |
00015         | but WITHOUT ANY WARRANTY; without even the implied warranty of              |
00016         | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               |
00017         | GNU General Public License for more details.                                |
00018         |                                                                             |
00019         | You should have received a copy of the GNU General Public License           |
00020         | along with this program; if not, write to the Free Software                 |
00021         | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA. |
00022         +-----------------------------------------------------------------------------+
00023 */
00024 
00038 class Database
00039 {
00040         
00041 
00047         var $dlI = false;
00048 
00049         
00053         function database($DP_dlI)
00054         {
00055                 $this->dlI                      = $DP_dlI;      //Connect to database
00056                 $this->alluser_id       = ALLUSERID;
00057         }
00058 
00059 
00064         function disconnect()
00065         {
00066                 mysql_close($this->dlI);        //Closes connection to database
00067         }       
00068 
00069 
00074         function isConnected()
00075         {
00076                 if (isset($this->dlI))  //Checks if connected to database
00077                 {
00078                         return true;
00079                 }
00080                 else
00081                 {
00082                         return false;
00083                 }
00084         }//end function 
00085 
00086 
00091         function dbError()
00092         {
00093                 return mysql_error();
00094         }//end function 
00095 
00096 
00104         function applyChangedDate ($a_user_id, $a_date_id, $a_timestamp)
00105         {
00106                 if (isset($this->dlI) && isset($a_user_id) && isset($a_date_id) && isset($a_timestamp)) //Checks if connected to database and if all parameters are set
00107                 {
00108                         $result = mysql_query ("SELECT DISTINCT status FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);    //Gets the status of the supplied date_id from database
00109                         $status = mysql_fetch_array($result);
00110                         mysql_free_result ($result);
00111                         if ($status[0] == '0')  //Status of the date = new
00112                         {
00113                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);  //Delete all entries for this user and date from the table of negative dp_dates
00114                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete all entries for this user and date from the table of changed dp_dates
00115                                 if (mysql_errno ($this->dlI) == 0) return true;
00116                         }
00117                         elseif ($status[0] == '1')      //Status of the date = changed
00118                         {
00119                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);  //Delete all entries for this user and date from the table of negative dp_dates
00120                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete all entries for this user and date from the table of changed dp_dates
00121                                 if (mysql_errno ($this->dlI) == 0) return true;
00122                         }
00123                         else
00124                         {
00125                                 return true;
00126                         }
00127                 }
00128                 else
00129                 {
00130                         return false;
00131                 }
00132         }//end function
00133         
00134         
00142         function discardChangedDate ($a_user_id, $a_date_id, $a_timestamp)
00143         {
00144                 if (isset($this->dlI) && isset($a_user_id) && isset($a_date_id) && isset($a_timestamp)) //Checks if connected to database and if all parameters are set
00145                 {
00146                         $result = mysql_query ("SELECT DISTINCT status FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);    //Gets the status of the supplied date_id from database
00147                         $status = mysql_fetch_array($result);
00148                         mysql_free_result ($result);
00149                         if ($status[0] == '0')  //Status of the date = new
00150                         {
00151                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete all entries for this user and date from the table of changed dp_dates
00152                                 if (mysql_errno ($this->dlI) == 0) return true;
00153                         }
00154                         elseif ($status[0] == '1')      //Status of the date = changed
00155                         {
00156                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete all entries for this user and date from the table of changed dp_dates
00157                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_neg_dates (id, date_id, user_id, timestamp) VALUES ('', '".$a_date_id."', '".$a_user_id."', '".$a_timestamp."')", $this->dlI);               //Inserts a negative date for this user and date into the table of negative dp_dates
00158                                 if (mysql_errno ($this->dlI) == 0) return true;
00159                         }
00160                         elseif ($status[0] == '2')      //Status of the date = deleted
00161                         {
00162                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id = '".$a_user_id."' AND date_id = '".$a_date_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete all entries for this user and date from the table of changed dp_dates
00163                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_neg_dates (id, date_id, user_id, timestamp) VALUES ('', '".$a_date_id."', '".$a_user_id."', '".$a_timestamp."')", $this->dlI);               //Inserts a negative date for this user and date into the table of negative dp_dates
00164                                 $result = mysql_query ("SELECT DISTINCT group_id FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI); //Get the group_id of this date from database
00165                                 $group_id = mysql_fetch_array($result);
00166                                 mysql_free_result ($result);
00167                                 $result = mysql_query ("SELECT DISTINCT count(id) as numOfNegDates FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND timestamp = '0'", $this->dlI);  //Counts how many users have deleted this date
00168                                 $numOfNegDates = mysql_fetch_array($result);
00169                                 mysql_free_result ($result);
00170                                 if ($numOfNegDates[0] >= ilCalInterface::getNumOfMembers($group_id[0])) //Checks if the number of users who have deleted this date is equal to the number of members of this group
00171                                 {
00172                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI);                             //Deletes all traces of this date from all tables
00173                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."'", $this->dlI);        //                      /
00174                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."'", $this->dlI);            //              /
00175                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."'", $this->dlI);             //      /
00176                                 }
00177 
00178                                 return true;
00179                         }
00180                         else
00181                         {
00182                                 return false;
00183                         }
00184                 }
00185                 else
00186                 {
00187                         return false;
00188                 }
00189         }//end function
00190 
00191         
00198         function getChangedDates ($a_user_id, $a_status)
00199         {
00200                 if (isset($this->dlI) && isset($a_user_id) && isset($a_status)) //Checks if connected to database and if all parameters are set
00201                 {
00202                         $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.shorttext, dp_dates.group_id, dp_dates.rotation, dp_changed_dates.timestamp FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_changed_dates WHERE dp_changed_dates.user_id = '".$a_user_id."' AND dp_changed_dates.status = '".$a_status."' AND dp_changed_dates.date_id = dp_dates.id ORDER BY dp_dates.begin, dp_dates.end DESC", $this->dlI);     //Gets all information on all changed dp_dates for this user according to its status from the table of dp_dates
00203                         $dp_dates = false;
00204                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00205                         {
00206                                 $dp_dates[$i] = $row;
00207                         }
00208                         mysql_free_result ($result);
00209                         return $dp_dates;
00210                 }
00211                 else
00212                 {
00213                         return false;
00214                 }
00215         }//end function
00216 
00217 
00226         function getDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00227         {
00228                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_user_id) && isset($a_begin) && isset($a_end) && isset($a_keyword_ids))  //Checks if connected to database and if all parameters are set
00229                 {
00230                         $result = mysql_query ("SELECT DISTINCT dp_dates.id FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_neg_dates WHERE dp_neg_dates.user_id = '".$a_user_id."' AND dp_neg_dates.date_id = dp_dates.id AND dp_neg_dates.timestamp = '0'", $this->dlI);        //Gets all negative dp_dates of an user from the table of negative dp_dates
00231                         $dp_neg_dates = false;
00232                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00233                         {
00234                                 $dp_neg_dates[$i] = $row[0];
00235                         }
00236                         mysql_free_result ($result);
00237                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);//Gets all groups in which the user is a member via the interface class
00238 
00239                         if ($group_ids == false) $group_ids = array('-2');      //Adds a dummy to the array to avoid "Cannot implode" errors
00240                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00241                         if ($a_keyword_ids[0] == '*') $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text FROM ".$this->dbase_cscw.".dp_dates WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end > '".$a_end."') AND dp_dates.rotation = '0' AND (dp_dates.end - dp_dates.begin != 86399) ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);        //Gets all dp_dates by user and time period which have the following stats: date is NOT a full day date, date is NOT a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00242                         else                                              $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end > '".$a_end."') AND dp_dates.rotation = '0' AND (dp_dates.end - dp_dates.begin != 86399) AND dp_keywords.keyword_id IN ('!�$%&/=', '".implode("','",$a_keyword_ids)."') AND dp_keywords.date_id = dp_dates.id ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);        //Gets all dp_dates by user, dp_keyword and time period which have the following stats: date is NOT a full day date, date is NOT a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00243                         $dp_dates = false;
00244                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00245                         {
00246                                 $dp_dates[$i] = $row;
00247                         }
00248                         mysql_free_result ($result);
00249                         return $dp_dates;
00250                 }
00251                 else
00252                 {
00253                         return false;
00254                 }
00255         }//end function
00256 
00257 
00266         function getFullDayDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00267         {
00268                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_user_id) && isset($a_begin) && isset($a_end) && isset($a_keyword_ids))  //Checks if connected to database and if all parameters are set
00269                 {
00270                         $result = mysql_query ("SELECT DISTINCT dp_dates.id FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_neg_dates WHERE dp_neg_dates.user_id = '".$a_user_id."' AND dp_neg_dates.date_id = dp_dates.id  AND dp_neg_dates.timestamp = '0'", $this->dlI);       //Gets all negative dp_dates of an user from the table of negative dp_dates
00271                         $dp_neg_dates = false;
00272                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00273                         {
00274                                 $dp_neg_dates[$i] = $row[0];
00275                         }
00276                         mysql_free_result ($result);
00277                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);       //Gets all groups in which the user is a member via the interface class
00278                         if ($group_ids == false) $group_ids = array('-2');      //Adds a dummy to the array to avoid "Cannot implode" errors
00279                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00280                         if ($a_keyword_ids[0] == '*') $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text FROM ".$this->dbase_cscw.".dp_dates WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end > '".$a_end."') AND dp_dates.rotation = '0' AND (dp_dates.end - dp_dates.begin = 86399) ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);         //Gets all dp_dates by user and time period which have the following stats: is a full day date, date is NOT a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00281                         else                                              $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end > '".$a_end."') AND dp_dates.rotation = '0' AND (dp_dates.end - dp_dates.begin = 86399) AND dp_keywords.keyword_id IN ('!�$%&/=', '".implode("','",$a_keyword_ids)."') AND dp_keywords.date_id = dp_dates.id ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI); //Gets all dp_dates by user, dp_keyword and time period which have the following stats: date is full day date, date is NOT a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00282                         $dp_dates = false;
00283                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00284                         {
00285                                 $dp_dates[$i] = $row;
00286                         }
00287                         mysql_free_result ($result);
00288                 return $dp_dates;
00289                 }
00290                 else
00291                 {
00292                         return false;
00293                 }
00294         }//end function
00295         
00304         function getRotationDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00305         {
00306                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_user_id) && isset($a_begin) && isset($a_end) && isset($a_keyword_ids))  //Checks if connected to database and if all parameters are set
00307                 {
00308                         $result = mysql_query ("SELECT DISTINCT dp_dates.id FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_neg_dates WHERE dp_neg_dates.user_id = '".$a_user_id."' AND dp_neg_dates.timestamp = '0' AND dp_neg_dates.date_id = dp_dates.id", $this->dlI);        //Gets all negative dp_dates of an user from the table of negative dp_dates
00309                         $dp_neg_dates = false;
00310                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00311                         {
00312                                 $dp_neg_dates[$i] = $row[0];
00313                         }
00314                         mysql_free_result ($result);
00315                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);       //Gets all groups in which the user is a member via the interface class
00316                         if ($group_ids == false) $group_ids = array('-2');              //Adds a dummy to the array to avoid "Cannot implode" errors
00317                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00318                         if ($a_keyword_ids[0] == '*') $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation FROM ".$this->dbase_cscw.".dp_dates WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end_rotation >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end_rotation = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end_rotation > '".$a_end."') AND dp_dates.rotation != '0' AND (dp_dates.end - dp_dates.begin != 86399) ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);  //Gets all dp_dates by user and time period which have the following stats: date is NOT a full day date, date is a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00319                         else                                              $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end_rotation >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end_rotation = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end_rotation > '".$a_end."') AND dp_dates.rotation != '0' AND (dp_dates.end - dp_dates.begin != 86399) AND dp_keywords.keyword_id IN ('!�$%&/=', '".implode("','",$a_keyword_ids)."') AND dp_keywords.date_id = dp_dates.id ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);  //Gets all dp_dates by user, dp_keyword and time period which have the following stats: date is NOT a full day date, date is a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00320                         $dp_dates = false;
00321                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00322                         {
00323                                 $dp_dates[$i] = $row;
00324                         }
00325                         mysql_free_result ($result);
00326                         return $dp_dates;
00327                 }
00328                 else
00329                 {
00330                         return false;
00331                 }
00332         }//end function
00333 
00342         function getFullDayRotationDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00343         {
00344                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_user_id) && isset($a_begin) && isset($a_end) && isset($a_keyword_ids))  //Checks if connected to database and if all parameters are set
00345                 {
00346                         $result = mysql_query ("SELECT DISTINCT dp_dates.id FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_neg_dates WHERE dp_neg_dates.user_id = '".$a_user_id."' AND dp_neg_dates.timestamp = '0' AND dp_neg_dates.date_id = dp_dates.id", $this->dlI);        //Gets all negative dp_dates of an user from the table of negative dp_dates
00347                         $dp_neg_dates = false;
00348                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00349                         {
00350                                 $dp_neg_dates[$i] = $row[0];
00351                         }
00352                         mysql_free_result ($result);
00353                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);       //Gets all groups in which the user is a member via the interface class
00354                         if ($group_ids == false) $group_ids = array('-2');      //Adds a dummy to the array to avoid "Cannot implode" errors
00355                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00356                         if ($a_keyword_ids[0] == '*') $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation FROM ".$this->dbase_cscw.".dp_dates WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end_rotation >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end_rotation = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end_rotation > '".$a_end."') AND dp_dates.rotation != '0' AND (dp_dates.end - dp_dates.begin = 86399) ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);   //Gets all dp_dates by user and time period which have the following stats: date is NOT a full day date, date is a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00357                         else                                              $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation FROM ".$this->dbase_cscw.".dp_dates, ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE (dp_dates.group_id IN ('".$this->alluser_id."','".implode("','",$group_ids)."') OR dp_dates.user_id = '".$a_user_id."') AND dp_dates.id NOT IN ('-2','".implode("','",$dp_neg_dates)."') AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end_rotation >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end_rotation = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end_rotation > '".$a_end."') AND dp_dates.rotation != '0' AND (dp_dates.end - dp_dates.begin = 86399) AND dp_keywords.keyword_id IN ('!�$%&/=', '".implode("','",$a_keyword_ids)."') AND dp_keywords.date_id = dp_dates.id ORDER BY dp_dates.begin, dp_dates.end DESC, dp_dates.changed, dp_dates.created", $this->dlI);   //Gets all dp_dates by user, dp_keyword and time period which have the following stats: date is a full day date, date is a rotation date, user is owner of date or user is member in the group of date, NOT in $dp_neg_dates
00358                         $dp_dates = false;
00359                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00360                         {
00361                                 $dp_dates[$i] = $row;
00362                         }
00363                         mysql_free_result ($result);
00364                         return $dp_dates;
00365                 }
00366                 else
00367                 {
00368                         return false;
00369                 }
00370         }//end function
00371 
00372 
00379         function getDate ($a_date_id, $a_user_id)
00380         {
00381                 if (isset($this->dlI) && isset($a_date_id) && isset($a_user_id))        //Checks if connected to database and if all parameters are set
00382                 {
00383                         $result = mysql_query ("SELECT DISTINCT id as date_id, begin, end, group_id, user_id, created, changed, rotation, shorttext, text, end_rotation FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI);  //Gets all information on a date from the table of dp_dates
00384                         $date = false;
00385                         $date = mysql_fetch_array($result);
00386                         mysql_free_result ($result);
00387                         if ($date)
00388                         {
00389                                 $result = mysql_query ("SELECT DISTINCT dp_keyword.id as keyword_id, dp_keyword.keyword FROM ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE dp_keywords.date_id = '".$a_date_id."' AND dp_keyword.user_id = '".$a_user_id."' AND dp_keyword.id = dp_keywords.keyword_id", $this->dlI);       //Gets the dedicated keyword_id of the date from the table of dp_keywords
00390                                 $keyword_id = false;
00391                                 $keyword_id = mysql_fetch_array($result);
00392                                 mysql_free_result ($result);
00393                                 if ($keyword_id)
00394                                 {
00395                                         $date = array_merge ($date, $keyword_id);       //Merges the keyword_id with the other information of the date
00396                                 }
00397                                 return $date;
00398                         }
00399                         else
00400                         {
00401                                 return false;
00402                         }
00403                 }
00404                 else
00405                 {
00406                         return false;
00407                 }
00408         }//end function
00409 
00410 
00418         function getNegRotationDates ($a_user_id, $a_begin, $a_end)
00419         {
00420                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_user_id) && isset($a_begin) && isset($a_end))   //Checks if connected to database and if all parameters are set
00421                 {
00422                         $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation, dp_neg_dates.timestamp FROM ".$this->dbase_cscw.".dp_neg_dates, ".$this->dbase_cscw.".dp_dates WHERE dp_neg_dates.user_id = '".$a_user_id."' AND dp_neg_dates.timestamp != '0' AND (dp_dates.id = dp_neg_dates.date_id AND dp_dates.rotation != '0') AND dp_neg_dates.timestamp between '".$a_begin."' AND '".$a_end."' ORDER BY dp_neg_dates.timestamp", $this->dlI);  //Gets all negative rotation dp_dates by user and time period
00423                         $dp_neg_dates = false;
00424                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00425                         {
00426                                 $dp_neg_dates[$i] = $row;
00427                         }
00428                         mysql_free_result ($result);
00429                         return $dp_neg_dates;
00430                 }
00431                 else
00432                 {
00433                         return false;
00434                 }
00435         }//end function
00436 
00437 
00443         function getUserGroups ($a_user_id)
00444         {
00445                 if (isset($this->dlI) && isset($a_user_id))     //Checks if connected to database and if all parameters are set
00446                 {
00447                         $groups = ilCalInterface::getUserGroups($a_user_id);    //Forwards the request to the interface class
00448                         return $groups;
00449                 }
00450                 else
00451                 {
00452                         return false;
00453                 }
00454         }//end function
00455         
00456         
00463         function addKeyword ($a_user_id, $a_keyword)
00464         {
00465                 if (isset($this->dlI) && isset($a_user_id) && isset($a_keyword))        //Checks if connected to database and if all parameters are set
00466                 {
00467                         $result = mysql_query ("SELECT id FROM ".$this->dbase_cscw.".dp_keyword WHERE user_id = '".$a_user_id."' AND keyword = '".$a_keyword."'", $this->dlI);  //Verfies that this dp_keyword does not exist already
00468                         if (mysql_fetch_array($result) == false)
00469                         {
00470                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_keyword (id, user_id, keyword) VALUES ('', '".$a_user_id."', '".$a_keyword."')", $this->dlI);        //Inserts the dp_keyword into the dp_keyword table
00471                                 if (mysql_errno ($this->dlI) == 0) return true;
00472                                 else return false;
00473                         }
00474                         else
00475                         {
00476                                 return true;
00477                         }
00478                         mysql_free_result ($result);
00479                 }
00480                 else
00481                 {
00482                         return false;
00483                 }
00484         }//end function
00485 
00486 
00492         function getKeywords ($a_user_id)
00493         {
00494                 if (isset($this->dlI) && isset($a_user_id))     //Checks if connected to database and if all parameters are set
00495                 {
00496                         $result = mysql_query ("SELECT DISTINCT id as keyword_id, keyword FROM ".$this->dbase_cscw.".dp_keyword WHERE user_id = '".$a_user_id."' ORDER BY keyword", $this->dlI);        //Gets all dp_keywords by user from the dp_keyword table
00497                         $dp_keywords = false;
00498                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00499                         {
00500                                    $dp_keywords[$i] = $row;
00501                         }
00502                         return $dp_keywords;
00503                         mysql_free_result ($result);
00504                 }
00505                 else
00506                 {
00507                         return false;
00508                 }
00509         }//end function
00510 
00511 
00517         function delKeyword ($a_keyword_id)
00518         {
00519                 if (isset($this->dlI) && isset($a_keyword_id))  //Checks if connected to database and if all parameters are set
00520                 {
00521                         $return = false;
00522                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keyword WHERE id = '".$a_keyword_id."'", $this->dlI);        //Deletes the dp_keyword from the dp_keyword table
00523                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE keyword_id = '".$a_keyword_id."'", $this->dlI);       //Deletes all assigned entries in the table of dp_keywords
00524                         if (mysql_errno ($this->dlI) == 0) $return = true;
00525                 }
00526                 else
00527                 {
00528                         $return = false;
00529                 }
00530                 return $return;
00531         }//end function
00532 
00533 
00540         function updateKeyword ($a_keyword_id, $a_keyword)
00541         {
00542                 if (isset($this->dlI) && isset($a_keyword_id) && isset($a_keyword))     //Checks if connected to database and if all parameters are set
00543                 {
00544                         mysql_query ("UPDATE ".$this->dbase_cscw.".dp_keyword SET keyword = '".$a_keyword."' WHERE id = '".$a_keyword_id."'", $this->dlI);      //Changes the dp_keyword in the dp_keyword table
00545                         if (mysql_errno ($this->dlI) == 0) return true;
00546                         else 
00547                         {
00548                                 return false;
00549                         }
00550                 }
00551                 else
00552                 {
00553                         return false;
00554                 }
00555         }//end function
00556 
00557 
00565         function getGroupDates ($a_group_id, $a_begin, $a_end)
00566         {       
00567                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_group_id) && isset($a_begin) && isset($a_end))  //Checks if connected to database and if all parameters are set
00568                 {   
00569                         $user_ids = ilCalInterface::getOtherMembers($a_group_id, -1);   //Gets all members of this group via the interface class
00570                         $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation  FROM ".$this->dbase_cscw.".dp_dates WHERE (dp_dates.group_id = '".$a_group_id."' OR dp_dates.user_id IN ('".$this->alluser_id."','".implode("','",$user_ids)."')) AND rotation = '0' AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end = '".$a_begin."')  ORDER BY begin, end DESC", $this->dlI);       //Gets all dp_dates by group and time period and all dp_dates of all members of this group which have the following stats: date is NOT a full day date, date is NOT a Rotationdate
00571                         $dp_dates = false;
00572 
00573                         echo(mysql_error());
00574                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00575                         {
00576                                 $dp_dates[$i] = $row;
00577                         }
00578                         mysql_free_result ($result);
00579 
00580                         return $dp_dates;
00581                 }
00582                 else
00583                 {
00584                         return false;
00585                 }
00586         }//end function
00587 
00595         function getGroupRotationDates ($a_group_id, $a_begin, $a_end)
00596         {
00597                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_group_id) && isset($a_begin) && isset($a_end))  //Checks if connected to database and if all parameters are set
00598                 {
00599                         $user_ids = ilCalInterface::getOtherMembers($a_group_id, -1);   //Gets all members of this group via the interface class
00600                         $result = mysql_query ("SELECT DISTINCT dp_dates.id as date_id, dp_dates.begin, dp_dates.end, dp_dates.group_id, dp_dates.user_id, dp_dates.shorttext, dp_dates.text, dp_dates.rotation, dp_dates.end_rotation  FROM ".$this->dbase_cscw.".dp_dates WHERE (dp_dates.group_id = '".$a_group_id."' OR dp_dates.user_id IN ('".$this->alluser_id."','".implode("','",$user_ids)."')) AND (dp_dates.begin <= '".$a_end."' AND dp_dates.end_rotation >= '".$a_begin."') AND NOT (dp_dates.begin < '".$a_begin."' AND dp_dates.end_rotation = '".$a_begin."') AND NOT (dp_dates.begin = '".$a_end."' AND dp_dates.end_rotation > '".$a_end."') AND dp_dates.rotation != '0' ORDER BY begin, end DESC", $this->dlI);   
00601                         //Gets all dp_dates by group and time period and all dp_dates of all members of this group which have the following stats: date is NOT a full day date, date is NOT a Rotationdate
00602                         $dp_dates = false;
00603                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00604                         {
00605                                 $dp_dates[$i] = $row;
00606                         }
00607                         mysql_free_result ($result);
00608                         return $dp_dates;
00609                 }
00610                 else
00611                 {
00612                         return false;
00613                 }
00614         }//end function
00615 
00621         function getGroupName ($a_group_id)
00622         {
00623                 if (isset($this->dlI) && isset($a_group_id))    //Checks if connected to database and if all parameters are set
00624                 {
00625                         $groupname = ilCalInterface::getGroupName($a_group_id); //Forwards the request to the interface class
00626                         return $groupname;
00627                 }
00628                 else
00629                 {
00630                         return false;
00631                 }
00632         }//end function
00633 
00634 
00642         function addStartEnd ($a_user_id, $a_start, $a_end)
00643         {
00644                 if (isset($this->dlI) && isset($a_user_id) && isset($a_start) && isset($a_end)) //Checks if connected to database and if all parameters are set
00645                 {
00646                         $result = mysql_query ("SELECT id FROM ".$this->dbase_cscw.".dp_properties WHERE user_id = '".$a_user_id."' AND dv_starttime  = '".$a_start."' AND dv_endtime  = '".$a_end."'", $this->dlI);    //Verfies that this start&endtime does not exist already
00647                         if (mysql_fetch_array($result) == false)
00648                         {
00649                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_properties (id, user_id, dv_starttime, dv_endtime) VALUES ('', '".$a_user_id."', '".$a_start."', '".$a_end."')", $this->dlI);        //Inserts the start- and endtime into the table of dp_properties
00650                                 if (mysql_errno ($this->dlI) == 0) return true;
00651                                 else return false;
00652                         }
00653                         else
00654                         {
00655                                 return true;
00656                         }
00657                         mysql_free_result ($result);
00658                 }
00659                 else
00660                 {
00661                         return false;
00662                 }
00663         }//end function
00664 
00665 
00671         function getStartEnd ($a_user_id)
00672         {
00673                 if (isset($this->dlI) && isset($a_user_id))     //Checks if connected to database and if all parameters are set
00674                 {
00675                         $result = mysql_query ("SELECT DISTINCT id, dv_starttime, dv_endtime FROM ".$this->dbase_cscw.".dp_properties WHERE user_id = '".$a_user_id."'", $this->dlI);   //Gets the start- and endtime for this user from the table of dp_properties
00676                         if ($times = mysql_fetch_array($result)) return $times;
00677                         else return false;
00678                         mysql_free_result ($result);
00679                 }
00680                 else
00681                 {
00682                         return false;
00683                 }
00684         }//end function
00685 
00686 
00694         function updateStartEnd ($a_properties_id, $a_start, $a_end)
00695         {
00696                 if (isset($this->dlI) && isset($a_properties_id) && isset($a_start) && isset($a_end))   //Checks if connected to database and if all parameters are set
00697                 {
00698                         mysql_query ("UPDATE ".$this->dbase_cscw.".dp_properties SET dv_starttime = '".$a_start."', dv_endtime = '".$a_end."' WHERE id = '".$a_properties_id."'", $this->dlI);  //Changes the start- and endtime in the table of dp_properties
00699                         if (mysql_errno ($this->dlI) == 0) return true;
00700                         else 
00701                         {
00702                                 return false;
00703                         }
00704                 }
00705                 else
00706                 {
00707                         return false;
00708                 }
00709         }//end function
00710 
00711 
00725         function addDate ($a_begin, $a_end, $a_group_id, $a_user_id, $a_created, $a_rotation, $a_end_rotation, $a_shorttext, $a_text, $a_keyword_id)
00726         {
00727                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_begin) && isset($a_end) && isset($a_group_id) && isset($a_user_id) && isset($a_created) && isset($a_rotation) && isset($a_end_rotation) && isset($a_shorttext) && isset($a_keyword_id)) //Checks if connected to database and if all parameters are set except the text
00728                 {
00729                         $result = mysql_query ("SELECT begin FROM ".$this->dbase_cscw.".dp_dates WHERE begin = '".$a_begin."' AND end = '".$a_end."' AND group_id = '".$a_group_id."' AND user_id = '".$a_user_id."' AND rotation = '".$a_rotation."' AND shorttext = '".$a_shorttext."' AND text = '".$a_text."' AND end_rotation = '".$a_end_rotation."'", $this->dlI);       //Verifies that date does not exist in table of dp_dates
00730                         $test = mysql_fetch_array($result);
00731                         mysql_free_result ($result);
00732                         if ($a_begin != $test[0])       //Checks if date is already in table of dp_dates (see SQL-query)
00733                         {
00734                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_dates (id, begin, end, group_id, user_id, created, changed, rotation, shorttext, text, end_rotation) VALUES ('', '".$a_begin."', '".$a_end."' , '".$a_group_id."', '".$a_user_id."', '".$a_created."' , '".$a_created."' , '".$a_rotation."' , '".$a_shorttext."', '".$a_text."', '".$a_end_rotation."')", $this->dlI);      //Inserts date into table of dp_dates
00735                                 if (mysql_errno ($this->dlI) == 0) $return = 0;
00736                                 $result = mysql_query ("SELECT id FROM ".$this->dbase_cscw.".dp_dates WHERE begin = '".$a_begin."' AND end = '".$a_end."' AND group_id = '".$a_group_id."' AND user_id = '".$a_user_id."' AND rotation = '".$a_rotation."' AND shorttext = '".$a_shorttext."' AND text = '".$a_text."' AND end_rotation = '".$a_end_rotation."'", $this->dlI);  //Get date_id of the inserted date
00737                                 $date_id = mysql_fetch_array($result);
00738                                 mysql_free_result ($result);
00739                                 if ($a_keyword_id != '0') mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_keywords (id, date_id, keyword_id) VALUES ('', '".$date_id[0]."', '".$a_keyword_id."')", $this->dlI);      //Insert dp_keyword allocation into the table of dp_keywords if dp_keyword is set
00740                                 if ($a_group_id != '0') //Checks if date is a date of a group
00741                                 {
00742                                         $users = false;
00743                                         $users = ilCalInterface::getOtherMembers($a_group_id, $a_user_id);      //Gets all other members of this group from database via the interface class
00744                                         if($users) {
00745                                                 for ($i = 0; $i < count($users); $i++)
00746                                                 {
00747                                                         mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_changed_dates (id, user_id, date_id, status, timestamp) VALUES ('', '".$users[$i]."', '".$date_id[0]."', '0', '0')", $this->dlI);    //Inserts a "new" date into the table of changed dp_dates
00748                                                         mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_neg_dates (id, date_id, user_id, timestamp) VALUES ('', '".$date_id[0]."', '".$users[$i]."', '0')", $this->dlI);     //Inserts a negative date into the table of negative dp_dates
00749                                                 }
00750                                         }
00751                                 }
00752                         }
00753                         else $return = 1;       //Errorcode 1 = Date exists already
00754                 }
00755                 else $return = 2;       //No DB-connection or one or more parameters missing
00756                 return $return;
00757         }//end function
00758 
00759 
00773         function updateDate ($a_date_id, $a_begin, $a_end, $a_user_id, $a_changed, $a_rotation, $a_end_rotation, $a_shorttext, $a_text)
00774         {
00775                 if (isset($this->dlI) && $a_begin <= $a_end && isset($a_date_id) && isset($a_begin) && isset($a_end) && isset($a_user_id) && isset($a_changed) && isset($a_rotation) && isset($a_end_rotation) && isset($a_shorttext))  //Checks if connected to database and if all parameters are set except the text
00776                 {
00777                         $return = false;
00778                         $result = mysql_query ("SELECT user_ID, group_ID, begin, end, rotation, end_rotation, shorttext, text FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI);    //Gets the all information of this date from the table of dp_dates
00779                         $date = mysql_fetch_row($result);
00780                         mysql_free_result ($result);
00781                         if ($a_user_id == $date[0] && ($a_begin != $date[2] || $a_end != $date[3] || $a_rotation != $date[4] || $a_end_rotation != $date[5] || $a_shorttext != $date[6] || $a_text != $date[7]))        //User is owner of date
00782                         {
00783                                 mysql_query ("UPDATE ".$this->dbase_cscw.".dp_dates SET begin = '".$a_begin."', end = '".$a_end."', changed = '".$a_changed."', rotation = '".$a_rotation."', shorttext = '".$a_shorttext."', text = '".$a_text."', end_rotation = '".$a_end_rotation."' WHERE id = '".$a_date_id."'", $this->dlI);     //Update the date in the table of dp_dates
00784                                 if (mysql_errno ($this->dlI) == 0) $return = true;
00785                                 if ($date[1] != '0')    //Date is a date of a group
00786                                 {
00787                                         $users = false;
00788                                         $users = ilCalInterface::getOtherMembers($date[1], $a_user_id); //Gets all other members of this group from database via the interface class
00789                                         if($users) {
00790                                         for ($i = 0; $i < count($users); $i++)
00791                                         {
00792                                                 $test = false;
00793                                                 $result2 = mysql_query ("SELECT DISTINCT date_id FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."' AND timestamp = '0' AND status = '0' ", $this->dlI);       //Checks if the date is stated as new date for this user in the table dp_changed_dates
00794                                                 $test = mysql_fetch_array($result2);
00795                                                 mysql_free_result ($result2);
00796                                                 if ($test[0] != $a_date_id)     //Checks if the date is stated as new date for this user in the table dp_changed_dates (see SQL-query)
00797                                                 {
00798                                                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."' AND timestamp = '0' AND status = '1'", $this->dlI);    //Delete all entries in the table dp_changed_dates for this user which are stated as updated
00799                                                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_changed_dates (id, user_id, date_id, status, timestamp) VALUES ('', '".$users[$i]."', '".$a_date_id."', '1', '0')", $this->dlI);     //Inserts a "updated" date into the table of changed dp_dates
00800                                                 }
00801                                         }
00802                                         }
00803                                 }
00804                                 if ($a_rotation != $date[4])    //Rotation has changed
00805                                 {
00806                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND timestamp != '0'", $this->dlI);   //Delete all single rotating dp_dates from the table of changed dp_dates
00807                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND timestamp != '0'", $this->dlI);       //Delete all single rotating dp_dates from the table of negative dp_dates
00808                                 }
00809                         }
00810                         else
00811                         {
00812                                 $return = false;
00813                         }
00814                 }
00815                 else
00816                 {
00817                         $return = false;
00818                 }
00819                 return $return;
00820         }//end function
00821 
00822 
00830         function delDate ($a_date_id, $a_user_id, $a_timestamp)
00831         {
00832                 if (isset($this->dlI) && isset($a_date_id) && isset($a_user_id) && isset($a_timestamp)) //Checks if connected to database and if all parameters are set
00833                 {
00834                         $date = false;
00835                         $result = mysql_query ("SELECT group_id, user_id, rotation FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI);       //Gets the group_id, user_id, rotation and date_id of this date from the table of dp_dates
00836                         $date = mysql_fetch_row($result);
00837                         mysql_free_result ($result);
00838                         if ($date[0] == 0 && $a_user_id == $date[1]) //Not a date of a group / user is owner of the date
00839                         {
00840                                 if ($a_timestamp != 0 && $date[2] != 0) //Single rotating date
00841                                 {
00842                                         mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_neg_dates (id, date_id, user_id, timestamp) VALUES ('', '".$a_date_id."', '".$a_user_id."' , '".$a_timestamp."')", $this->dlI);      //Insert a negative single rotating date into the table of negative dp_dates
00843                                 }
00844                                 else //Whole rotating date or single date
00845                                 {
00846                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI);                     //Deletes all traces of this date from all tables
00847                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."'", $this->dlI);    //              /
00848                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."'", $this->dlI);     //      /
00849                                 }
00850                                 $return = true;
00851                         }
00852                         elseif ($date[0] != 0) //Date of a group
00853                         {
00854                                 if ($a_timestamp == 0)  //Whole rotating date or single date
00855                                 {
00856                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$a_user_id."'", $this->dlI); //Delete all entries of this date for this user from the table of changed dp_dates
00857                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$a_user_id."'", $this->dlI);     //Delete all entries of this date for this user from the table of negative dp_dates
00858                                         $result = mysql_query ("SELECT DISTINCT dp_keywords.keyword_id FROM ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE dp_keywords.date_id = '".$a_date_id."' AND dp_keyword.id = dp_keywords.keyword_id AND dp_keyword.user_id = '".$a_user_id."'", $this->dlI);        //Get keyword_id of this date for this date
00859                                         $keyword_id = mysql_fetch_array($result);
00860                                         mysql_free_result ($result);
00861                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."' AND keyword_id = '".$keyword_id[0]."'", $this->dlI);       //Delete association of this dp_keyword to this date from the table of dp_keywords
00862                                 }
00863                                 else    //Single rotating date
00864                                 {
00865                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$a_user_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete this single rotation date for this user from the table of changed dp_dates
00866                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$a_user_id."' AND timestamp = '".$a_timestamp."'", $this->dlI);  //Delete this single rotation date for this user from the table of negative dp_dates
00867                                 }
00868                                 mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_neg_dates (id, date_id, user_id, timestamp) VALUES ('', '".$a_date_id."', '".$a_user_id."' , '".$a_timestamp."')", $this->dlI);      
00869                                 //Inserts a negative date into the table of negative dp_dates
00870                                 if ($a_user_id == $date[1])     //User is owner of date
00871                                 {
00872 
00873                                         $users = false;
00874                                         $users = ilCalInterface::getOtherMembers($date[0], $a_user_id); //Gets all other members of this group from database via the interface class
00875                                         if($users) {
00876                                                 for ($i = 0; $i < count($users); $i++)
00877                                                 {
00878                                                 //Date has been deleted by member
00879                                                 $test = false;
00880                                                 $test2 = false;
00881                                                 $result = mysql_query ("SELECT DISTINCT date_id FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."'", $this->dlI);
00882                                                 $test = mysql_fetch_array($result);
00883                                                 mysql_free_result ($result);
00884                                                 $result = mysql_query ("SELECT DISTINCT date_id, status FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."'", $this->dlI);
00885                                                 $test2 = mysql_fetch_row($result);
00886                                                 mysql_free_result ($result);
00887                                                         if ($a_timestamp == 0)  //Whole rotating date or single date
00888                                                         {
00889                                                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."'", $this->dlI); //Delete all entries of this date for this member from the table of changed dp_dates
00890                                                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."'", $this->dlI);     //Delete all entries of this date for this member from the table of negative dp_dates
00891                                                         }
00892                                                         else    //Single rotating date
00893                                                         {
00894                                                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."' AND timestamp = '".$a_timestamp."'", $this->dlI);      //Delete this single rotation date for this member from the table of changed dp_dates
00895                                                                 mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND user_id = '".$users[$i]."' AND timestamp = '".$a_timestamp."'", $this->dlI);  //Delete this single rotation date for this member from the table of negative dp_dates
00896                                                         }
00897 
00898                                                         if ($test2[1] != "0") {mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_changed_dates (id, user_id, date_id, status, timestamp) VALUES ('', '".$users[$i]."', '".$a_date_id."' , '2', '".$a_timestamp."')", $this->dlI);      //Inserts a "deleted" date for this member into the table of changed dp_dates
00899                                                         }
00900                                                 }
00901                                         }
00902                                 }
00903                                 $result = mysql_query ("SELECT DISTINCT count(id) as numOfNegDates FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."' AND timestamp = '0'", $this->dlI);  //Counts how many users have deleted this date
00904                                 $numOfNegDates = mysql_fetch_array($result);
00905                                 mysql_free_result ($result);
00906 
00907                                 if ($numOfNegDates[0] >= ilCalInterface::getNumOfMembers($date[0]))     //Checks if the number of users who have deleted this date is equal to the number of members of this group
00908 
00909                                 {
00910                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_dates WHERE id = '".$a_date_id."'", $this->dlI);                             //Deletes all traces of this date from all tables
00911                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."'", $this->dlI);        //                      /
00912                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."'", $this->dlI);            //              /
00913                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."'", $this->dlI);             //      /
00914                                 }
00915                                 $return = true; 
00916                         }       
00917                         else
00918                         {
00919                                 $return = false;
00920                         }
00921                 }
00922                 else
00923                 {
00924                         $return = false;
00925                 }
00926                 return $return;
00927         }//end function
00928 
00929 
00937         function updateKeyword2Date ($a_user_id, $a_date_id, $a_keyword_id)
00938         {
00939                 if (isset($this->dlI) && isset($a_user_id) && isset($a_date_id) && isset($a_keyword_id))        //Checks if connected to database and if all parameters are set
00940                 {
00941                                 $keyword_id_old = false;
00942                                 $result = mysql_query ("SELECT DISTINCT dp_keywords.keyword_id FROM ".$this->dbase_cscw.".dp_keyword, ".$this->dbase_cscw.".dp_keywords WHERE dp_keywords.date_id = '".$a_date_id."' AND dp_keyword.id = dp_keywords.keyword_id AND dp_keyword.user_id = '".$a_user_id."'", $this->dlI);        //Gets the old dp_keyword associated with this date
00943                                 $keyword_id_old = mysql_fetch_array($result);
00944                                 mysql_free_result ($result);
00945                                 if ($a_keyword_id != $keyword_id_old[0])        // If dp_keyword has changed
00946                                 {
00947                                         if ($a_keyword_id == 0)                          mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."' AND keyword_id = '".$keyword_id_old[0]."'", $this->dlI);  //Delete association from table of dp_keywords
00948                                         elseif ($keyword_id_old[0] == false) mysql_query ("INSERT INTO ".$this->dbase_cscw.".dp_keywords (id, date_id, keyword_id) VALUES ('', '".$a_date_id."', '".$a_keyword_id."')", $this->dlI);            //Add association from table of dp_keywords
00949                                         else                                                             mysql_query ("UPDATE ".$this->dbase_cscw.".dp_keywords SET keyword_id = '".$a_keyword_id."' WHERE date_id = '".$a_date_id."' AND keyword_id = '".$keyword_id_old[0]."'", $this->dlI);          //Change association from table of dp_keywords
00950                                 }
00951                                 return true;
00952                 }
00953                 else
00954                 {
00955                         return false;
00956                 }
00957         }//end function
00958 
00959 
00964         function cleanDatabase ()
00965         {
00966                 if (isset($this->dlI))  //Checks if connected to database
00967                 {
00968                         $groups = ilCalInterface::getGroups();  //Gets all groups of the system
00969                         $ago = strtotime ("-6 month");  //Sets the time which date have to be ago to be deleted
00970                         $tobedeleted = false;
00971                         
00972                         $result = mysql_query ("SELECT DISTINCT id FROM ".$this->dbase_cscw.".dp_dates WHERE rotation != '0' AND end_rotation <= '".$ago."'", $this->dlI);      //Gets all single dp_dates that are older than 6 month
00973                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00974                         {
00975                                 $tobedeleted[$i] = $row[0];
00976                         }
00977                         mysql_free_result ($result);
00978                         
00979                         $result = mysql_query ("SELECT DISTINCT id FROM ".$this->dbase_cscw.".dp_dates WHERE rotation = '0' AND end <= '".$ago."'", $this->dlI);                //Gets all rotating dp_dates that are older than 6 month
00980                         for ($i = 0, $temp = false; $row = mysql_fetch_array($result); $i++)
00981                         {
00982                                 $temp[$i] = $row[0];
00983                         }
00984                         $tobedeleted = array_merge ($tobedeleted, $temp);
00985                         mysql_free_result ($result);
00986                         
00987                         $result = mysql_query ("SELECT DISTINCT id FROM ".$this->dbase_cscw.".dp_dates WHERE user_id NOT IN ('-1','".implode("','",$users)."')", $this->dlI);   //Gets all dp_dates where user has been deleted
00988                         for ($i = 0, $temp = false; $row = mysql_fetch_array($result); $i++)
00989                         {
00990                                 $temp[$i] = $row[0];
00991                         }
00992                         $tobedeleted = array_merge ($tobedeleted, $temp);
00993                         mysql_free_result ($result);
00994                         
00995                         $result = mysql_query ("SELECT DISTINCT id FROM ".$this->dbase_cscw.".dp_dates WHERE group_id NOT IN ('".$this->alluser_id."','".implode("','",$groups)."')", $this->dlI);      //Gets all dp_dates where group has been deleted
00996                         for ($i = 0, $temp = false; $row = mysql_fetch_array($result); $i++)
00997                         {
00998                                 $temp[$i] = $row[0];
00999                         }
01000                         $tobedeleted = array_merge ($tobedeleted, $temp);
01001                         mysql_free_result ($result);
01002                         
01003                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_dates WHERE id IN ('-1','".implode("','",$tobedeleted)."')", $this->dlI);    //Delete all collected dp_dates from the table of dp_dates
01004                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id IN ('-1','".implode("','",$tobedeleted)."')", $this->dlI);               //Delete all collected dp_dates from the table of changed dp_dates
01005                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id IN ('-1','".implode("','",$tobedeleted)."')", $this->dlI);   //Delete all collected dp_dates from the table of negative dp_dates
01006                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id IN ('-1','".implode("','",$tobedeleted)."')", $this->dlI);            //Delete all collected dp_dates from the table of dp_keywords
01007                                                 
01008                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE user_id NOT IN ('-1','".implode("','",$users)."')", $this->dlI); //Delete all entries of deleted users from the table of changed dp_dates
01009                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE user_id NOT IN ('-1','".implode("','",$users)."')", $this->dlI);     //Delete all entries of deleted users from the table of negative dp_dates
01010                         
01011                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keyword WHERE user_id NOT IN ('-1','".implode("','",$users)."')", $this->dlI);       //Delete all entries of deleted users from the table dp_keyword
01012                         
01013                         $result = mysql_query ("SELECT DISTINCT id FROM ".$this->dbase_cscw.".dp_keyword", $this->dlI); //Gets all keyword_ids
01014                         for ($i = 0, $tobedeleted = false; $row = mysql_fetch_array($result); $i++)
01015                         {
01016                                 $tobedeleted[$i] = $row[0];
01017                         }
01018                         mysql_free_result ($result);
01019 
01020                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE keyword_id NOT IN ('-1','".implode("','",$tobedeleted)."')", $this->dlI);     //Deletes all entries of deleted dp_keywords from the table dp_keywords
01021                         
01022                         return true;            
01023                 }
01024                 else
01025                 {
01026                         return false;
01027                 }
01028         }//end function
01029 
01030 
01031 } //end Class
01032 ?>