ILIAS Release_3_9_x_branch .rev 46835: 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 
00035 class Database
00036 {
00037         
00038 
00044         var $dlI = false;
00045 
00046         
00050         function database($DP_dlI)
00051         {
00052                 $this->dlI                      = $DP_dlI;      //Connect to database
00053                 $this->alluser_id       = ALLUSERID;
00054         }
00055 
00056 
00061         function disconnect()
00062         {
00063                 mysql_close($this->dlI);        //Closes connection to database
00064         }       
00065 
00066 
00071         function isConnected()
00072         {
00073                 if (isset($this->dlI))  //Checks if connected to database
00074                 {
00075                         return true;
00076                 }
00077                 else
00078                 {
00079                         return false;
00080                 }
00081         }//end function 
00082 
00083 
00088         function dbError()
00089         {
00090                 return mysql_error();
00091         }//end function 
00092 
00093 
00101         function applyChangedDate ($a_user_id, $a_date_id, $a_timestamp)
00102         {
00103                 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
00104                 {
00105                         $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
00106                         $status = mysql_fetch_array($result);
00107                         mysql_free_result ($result);
00108                         if ($status[0] == '0')  //Status of the date = new
00109                         {
00110                                 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
00111                                 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
00112                                 if (mysql_errno ($this->dlI) == 0) return true;
00113                         }
00114                         elseif ($status[0] == '1')      //Status of the date = changed
00115                         {
00116                                 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
00117                                 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
00118                                 if (mysql_errno ($this->dlI) == 0) return true;
00119                         }
00120                         else
00121                         {
00122                                 return true;
00123                         }
00124                 }
00125                 else
00126                 {
00127                         return false;
00128                 }
00129         }//end function
00130         
00131         
00139         function discardChangedDate ($a_user_id, $a_date_id, $a_timestamp)
00140         {
00141                 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
00142                 {
00143                         $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
00144                         $status = mysql_fetch_array($result);
00145                         mysql_free_result ($result);
00146                         if ($status[0] == '0')  //Status of the date = new
00147                         {
00148                                 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
00149                                 if (mysql_errno ($this->dlI) == 0) return true;
00150                         }
00151                         elseif ($status[0] == '1')      //Status of the date = changed
00152                         {
00153                                 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
00154                                 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
00155                                 if (mysql_errno ($this->dlI) == 0) return true;
00156                         }
00157                         elseif ($status[0] == '2')      //Status of the date = deleted
00158                         {
00159                                 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
00160                                 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
00161                                 $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
00162                                 $group_id = mysql_fetch_array($result);
00163                                 mysql_free_result ($result);
00164                                 $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
00165                                 $numOfNegDates = mysql_fetch_array($result);
00166                                 mysql_free_result ($result);
00167                                 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
00168                                 {
00169                                         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
00170                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."'", $this->dlI);        //                      /
00171                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."'", $this->dlI);            //              /
00172                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."'", $this->dlI);             //      /
00173                                 }
00174 
00175                                 return true;
00176                         }
00177                         else
00178                         {
00179                                 return false;
00180                         }
00181                 }
00182                 else
00183                 {
00184                         return false;
00185                 }
00186         }//end function
00187 
00188         
00195         function getChangedDates ($a_user_id, $a_status)
00196         {
00197                 if (isset($this->dlI) && isset($a_user_id) && isset($a_status)) //Checks if connected to database and if all parameters are set
00198                 {
00199                         $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
00200                         $dp_dates = false;
00201                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00202                         {
00203                                 $dp_dates[$i] = $row;
00204                         }
00205                         mysql_free_result ($result);
00206                         return $dp_dates;
00207                 }
00208                 else
00209                 {
00210                         return false;
00211                 }
00212         }//end function
00213 
00214 
00223         function getDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00224         {
00225                 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
00226                 {
00227                         $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
00228                         $dp_neg_dates = false;
00229                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00230                         {
00231                                 $dp_neg_dates[$i] = $row[0];
00232                         }
00233                         mysql_free_result ($result);
00234                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);//Gets all groups in which the user is a member via the interface class
00235 
00236                         if ($group_ids == false) $group_ids = array('-2');      //Adds a dummy to the array to avoid "Cannot implode" errors
00237                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00238                         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
00239                         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
00240                         $dp_dates = false;
00241                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00242                         {
00243                                 $dp_dates[$i] = $row;
00244                         }
00245                         mysql_free_result ($result);
00246                         return $dp_dates;
00247                 }
00248                 else
00249                 {
00250                         return false;
00251                 }
00252         }//end function
00253 
00254 
00263         function getFullDayDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00264         {
00265                 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
00266                 {
00267                         $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
00268                         $dp_neg_dates = false;
00269                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00270                         {
00271                                 $dp_neg_dates[$i] = $row[0];
00272                         }
00273                         mysql_free_result ($result);
00274                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);       //Gets all groups in which the user is a member via the interface class
00275                         if ($group_ids == false) $group_ids = array('-2');      //Adds a dummy to the array to avoid "Cannot implode" errors
00276                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00277                         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
00278                         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
00279                         $dp_dates = false;
00280                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00281                         {
00282                                 $dp_dates[$i] = $row;
00283                         }
00284                         mysql_free_result ($result);
00285                 return $dp_dates;
00286                 }
00287                 else
00288                 {
00289                         return false;
00290                 }
00291         }//end function
00292         
00301         function getRotationDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00302         {
00303                 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
00304                 {
00305                         $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
00306                         $dp_neg_dates = false;
00307                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00308                         {
00309                                 $dp_neg_dates[$i] = $row[0];
00310                         }
00311                         mysql_free_result ($result);
00312                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);       //Gets all groups in which the user is a member via the interface class
00313                         if ($group_ids == false) $group_ids = array('-2');              //Adds a dummy to the array to avoid "Cannot implode" errors
00314                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00315                         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
00316                         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
00317                         $dp_dates = false;
00318                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00319                         {
00320                                 $dp_dates[$i] = $row;
00321                         }
00322                         mysql_free_result ($result);
00323                         return $dp_dates;
00324                 }
00325                 else
00326                 {
00327                         return false;
00328                 }
00329         }//end function
00330 
00339         function getFullDayRotationDates ($a_user_id, $a_begin, $a_end, $a_keyword_ids)
00340         {
00341                 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
00342                 {
00343                         $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
00344                         $dp_neg_dates = false;
00345                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00346                         {
00347                                 $dp_neg_dates[$i] = $row[0];
00348                         }
00349                         mysql_free_result ($result);
00350                         $group_ids = ilCalInterface::getMemberGroups($a_user_id);       //Gets all groups in which the user is a member via the interface class
00351                         if ($group_ids == false) $group_ids = array('-2');      //Adds a dummy to the array to avoid "Cannot implode" errors
00352                         if ($dp_neg_dates == false) $dp_neg_dates = array('-2');        //Adds a dummy to the array to avoid "Cannot implode" errors
00353                         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
00354                         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
00355                         $dp_dates = false;
00356                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00357                         {
00358                                 $dp_dates[$i] = $row;
00359                         }
00360                         mysql_free_result ($result);
00361                         return $dp_dates;
00362                 }
00363                 else
00364                 {
00365                         return false;
00366                 }
00367         }//end function
00368 
00369 
00376         function getDate ($a_date_id, $a_user_id)
00377         {
00378                 if (isset($this->dlI) && isset($a_date_id) && isset($a_user_id))        //Checks if connected to database and if all parameters are set
00379                 {
00380                         $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
00381                         $date = false;
00382                         $date = mysql_fetch_array($result);
00383                         mysql_free_result ($result);
00384                         if ($date)
00385                         {
00386                                 $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
00387                                 $keyword_id = false;
00388                                 $keyword_id = mysql_fetch_array($result);
00389                                 mysql_free_result ($result);
00390                                 if ($keyword_id)
00391                                 {
00392                                         $date = array_merge ($date, $keyword_id);       //Merges the keyword_id with the other information of the date
00393                                 }
00394                                 return $date;
00395                         }
00396                         else
00397                         {
00398                                 return false;
00399                         }
00400                 }
00401                 else
00402                 {
00403                         return false;
00404                 }
00405         }//end function
00406 
00407 
00415         function getNegRotationDates ($a_user_id, $a_begin, $a_end)
00416         {
00417                 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
00418                 {
00419                         $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
00420                         $dp_neg_dates = false;
00421                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00422                         {
00423                                 $dp_neg_dates[$i] = $row;
00424                         }
00425                         mysql_free_result ($result);
00426                         return $dp_neg_dates;
00427                 }
00428                 else
00429                 {
00430                         return false;
00431                 }
00432         }//end function
00433 
00434 
00440         function getUserGroups ($a_user_id)
00441         {
00442                 if (isset($this->dlI) && isset($a_user_id))     //Checks if connected to database and if all parameters are set
00443                 {
00444                         $groups = ilCalInterface::getUserGroups($a_user_id);    //Forwards the request to the interface class
00445                         return $groups;
00446                 }
00447                 else
00448                 {
00449                         return false;
00450                 }
00451         }//end function
00452         
00453         
00460         function addKeyword ($a_user_id, $a_keyword)
00461         {
00462                 if (isset($this->dlI) && isset($a_user_id) && isset($a_keyword))        //Checks if connected to database and if all parameters are set
00463                 {
00464                         $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
00465                         if (mysql_fetch_array($result) == false)
00466                         {
00467                                 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
00468                                 if (mysql_errno ($this->dlI) == 0) return true;
00469                                 else return false;
00470                         }
00471                         else
00472                         {
00473                                 return true;
00474                         }
00475                         mysql_free_result ($result);
00476                 }
00477                 else
00478                 {
00479                         return false;
00480                 }
00481         }//end function
00482 
00483 
00489         function getKeywords ($a_user_id)
00490         {
00491                 if (isset($this->dlI) && isset($a_user_id))     //Checks if connected to database and if all parameters are set
00492                 {
00493                         $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
00494                         $dp_keywords = false;
00495                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00496                         {
00497                                    $dp_keywords[$i] = $row;
00498                         }
00499                         return $dp_keywords;
00500                         mysql_free_result ($result);
00501                 }
00502                 else
00503                 {
00504                         return false;
00505                 }
00506         }//end function
00507 
00508 
00514         function delKeyword ($a_keyword_id)
00515         {
00516                 if (isset($this->dlI) && isset($a_keyword_id))  //Checks if connected to database and if all parameters are set
00517                 {
00518                         $return = false;
00519                         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
00520                         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
00521                         if (mysql_errno ($this->dlI) == 0) $return = true;
00522                 }
00523                 else
00524                 {
00525                         $return = false;
00526                 }
00527                 return $return;
00528         }//end function
00529 
00530 
00537         function updateKeyword ($a_keyword_id, $a_keyword)
00538         {
00539                 if (isset($this->dlI) && isset($a_keyword_id) && isset($a_keyword))     //Checks if connected to database and if all parameters are set
00540                 {
00541                         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
00542                         if (mysql_errno ($this->dlI) == 0) return true;
00543                         else 
00544                         {
00545                                 return false;
00546                         }
00547                 }
00548                 else
00549                 {
00550                         return false;
00551                 }
00552         }//end function
00553 
00554 
00562         function getGroupDates ($a_group_id, $a_begin, $a_end)
00563         {       
00564                 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
00565                 {   
00566                         $user_ids = ilCalInterface::getOtherMembers($a_group_id, -1);   //Gets all members of this group via the interface class
00567                         $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
00568                         $dp_dates = false;
00569 
00570                         echo(mysql_error());
00571                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00572                         {
00573                                 $dp_dates[$i] = $row;
00574                         }
00575                         mysql_free_result ($result);
00576 
00577                         return $dp_dates;
00578                 }
00579                 else
00580                 {
00581                         return false;
00582                 }
00583         }//end function
00584 
00592         function getGroupRotationDates ($a_group_id, $a_begin, $a_end)
00593         {
00594                 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
00595                 {
00596                         $user_ids = ilCalInterface::getOtherMembers($a_group_id, -1);   //Gets all members of this group via the interface class
00597                         $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);   
00598                         //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
00599                         $dp_dates = false;
00600                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00601                         {
00602                                 $dp_dates[$i] = $row;
00603                         }
00604                         mysql_free_result ($result);
00605                         return $dp_dates;
00606                 }
00607                 else
00608                 {
00609                         return false;
00610                 }
00611         }//end function
00612 
00618         function getGroupName ($a_group_id)
00619         {
00620                 if (isset($this->dlI) && isset($a_group_id))    //Checks if connected to database and if all parameters are set
00621                 {
00622                         $groupname = ilCalInterface::getGroupName($a_group_id); //Forwards the request to the interface class
00623                         return $groupname;
00624                 }
00625                 else
00626                 {
00627                         return false;
00628                 }
00629         }//end function
00630 
00631 
00639         function addStartEnd ($a_user_id, $a_start, $a_end)
00640         {
00641                 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
00642                 {
00643                         $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
00644                         if (mysql_fetch_array($result) == false)
00645                         {
00646                                 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
00647                                 if (mysql_errno ($this->dlI) == 0) return true;
00648                                 else return false;
00649                         }
00650                         else
00651                         {
00652                                 return true;
00653                         }
00654                         mysql_free_result ($result);
00655                 }
00656                 else
00657                 {
00658                         return false;
00659                 }
00660         }//end function
00661 
00662 
00668         function getStartEnd ($a_user_id)
00669         {
00670                 if (isset($this->dlI) && isset($a_user_id))     //Checks if connected to database and if all parameters are set
00671                 {
00672                         $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
00673                         if ($times = mysql_fetch_array($result)) return $times;
00674                         else return false;
00675                         mysql_free_result ($result);
00676                 }
00677                 else
00678                 {
00679                         return false;
00680                 }
00681         }//end function
00682 
00683 
00691         function updateStartEnd ($a_properties_id, $a_start, $a_end)
00692         {
00693                 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
00694                 {
00695                         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
00696                         if (mysql_errno ($this->dlI) == 0) return true;
00697                         else 
00698                         {
00699                                 return false;
00700                         }
00701                 }
00702                 else
00703                 {
00704                         return false;
00705                 }
00706         }//end function
00707 
00708 
00722         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)
00723         {
00724                 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
00725                 {
00726                         $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
00727                         $test = mysql_fetch_array($result);
00728                         mysql_free_result ($result);
00729                         if ($a_begin != $test[0])       //Checks if date is already in table of dp_dates (see SQL-query)
00730                         {
00731                                 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
00732                                 if (mysql_errno ($this->dlI) == 0) $return = 0;
00733                                 $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
00734                                 $date_id = mysql_fetch_array($result);
00735                                 mysql_free_result ($result);
00736                                 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
00737                                 if ($a_group_id != '0') //Checks if date is a date of a group
00738                                 {
00739                                         $users = false;
00740                                         $users = ilCalInterface::getOtherMembers($a_group_id, $a_user_id);      //Gets all other members of this group from database via the interface class
00741                                         if($users) {
00742                                                 for ($i = 0; $i < count($users); $i++)
00743                                                 {
00744                                                         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
00745                                                         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
00746                                                 }
00747                                         }
00748                                 }
00749                         }
00750                         else $return = 1;       //Errorcode 1 = Date exists already
00751                 }
00752                 else $return = 2;       //No DB-connection or one or more parameters missing
00753                 return $return;
00754         }//end function
00755 
00756 
00770         function updateDate ($a_date_id, $a_begin, $a_end, $a_user_id, $a_changed, $a_rotation, $a_end_rotation, $a_shorttext, $a_text)
00771         {
00772                 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
00773                 {
00774                         $return = false;
00775                         $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
00776                         $date = mysql_fetch_row($result);
00777                         mysql_free_result ($result);
00778                         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
00779                         {
00780                                 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
00781                                 if (mysql_errno ($this->dlI) == 0) $return = true;
00782                                 if ($date[1] != '0')    //Date is a date of a group
00783                                 {
00784                                         $users = false;
00785                                         $users = ilCalInterface::getOtherMembers($date[1], $a_user_id); //Gets all other members of this group from database via the interface class
00786                                         if($users) {
00787                                         for ($i = 0; $i < count($users); $i++)
00788                                         {
00789                                                 $test = false;
00790                                                 $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
00791                                                 $test = mysql_fetch_array($result2);
00792                                                 mysql_free_result ($result2);
00793                                                 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)
00794                                                 {
00795                                                                 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
00796                                                                 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
00797                                                 }
00798                                         }
00799                                         }
00800                                 }
00801                                 if ($a_rotation != $date[4])    //Rotation has changed
00802                                 {
00803                                         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
00804                                         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
00805                                 }
00806                         }
00807                         else
00808                         {
00809                                 $return = false;
00810                         }
00811                 }
00812                 else
00813                 {
00814                         $return = false;
00815                 }
00816                 return $return;
00817         }//end function
00818 
00819 
00827         function delDate ($a_date_id, $a_user_id, $a_timestamp)
00828         {
00829                 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
00830                 {
00831                         $date = false;
00832                         $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
00833                         $date = mysql_fetch_row($result);
00834                         mysql_free_result ($result);
00835                         if ($date[0] == 0 && $a_user_id == $date[1]) //Not a date of a group / user is owner of the date
00836                         {
00837                                 if ($a_timestamp != 0 && $date[2] != 0) //Single rotating date
00838                                 {
00839                                         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
00840                                 }
00841                                 else //Whole rotating date or single date
00842                                 {
00843                                         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
00844                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."'", $this->dlI);    //              /
00845                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."'", $this->dlI);     //      /
00846                                 }
00847                                 $return = true;
00848                         }
00849                         elseif ($date[0] != 0) //Date of a group
00850                         {
00851                                 if ($a_timestamp == 0)  //Whole rotating date or single date
00852                                 {
00853                                         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
00854                                         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
00855                                         $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
00856                                         $keyword_id = mysql_fetch_array($result);
00857                                         mysql_free_result ($result);
00858                                         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
00859                                 }
00860                                 else    //Single rotating date
00861                                 {
00862                                         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
00863                                         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
00864                                 }
00865                                 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);      
00866                                 //Inserts a negative date into the table of negative dp_dates
00867                                 if ($a_user_id == $date[1])     //User is owner of date
00868                                 {
00869 
00870                                         $users = false;
00871                                         $users = ilCalInterface::getOtherMembers($date[0], $a_user_id); //Gets all other members of this group from database via the interface class
00872                                         if($users) {
00873                                                 for ($i = 0; $i < count($users); $i++)
00874                                                 {
00875                                                 //Date has been deleted by member
00876                                                 $test = false;
00877                                                 $test2 = false;
00878                                                 $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);
00879                                                 $test = mysql_fetch_array($result);
00880                                                 mysql_free_result ($result);
00881                                                 $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);
00882                                                 $test2 = mysql_fetch_row($result);
00883                                                 mysql_free_result ($result);
00884                                                         if ($a_timestamp == 0)  //Whole rotating date or single date
00885                                                         {
00886                                                                 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
00887                                                                 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
00888                                                         }
00889                                                         else    //Single rotating date
00890                                                         {
00891                                                                 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
00892                                                                 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
00893                                                         }
00894 
00895                                                         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
00896                                                         }
00897                                                 }
00898                                         }
00899                                 }
00900                                 $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
00901                                 $numOfNegDates = mysql_fetch_array($result);
00902                                 mysql_free_result ($result);
00903 
00904                                 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
00905 
00906                                 {
00907                                         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
00908                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_changed_dates WHERE date_id = '".$a_date_id."'", $this->dlI);        //                      /
00909                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_neg_dates WHERE date_id = '".$a_date_id."'", $this->dlI);            //              /
00910                                         mysql_query ("DELETE FROM ".$this->dbase_cscw.".dp_keywords WHERE date_id = '".$a_date_id."'", $this->dlI);             //      /
00911                                 }
00912                                 $return = true; 
00913                         }       
00914                         else
00915                         {
00916                                 $return = false;
00917                         }
00918                 }
00919                 else
00920                 {
00921                         $return = false;
00922                 }
00923                 return $return;
00924         }//end function
00925 
00926 
00934         function updateKeyword2Date ($a_user_id, $a_date_id, $a_keyword_id)
00935         {
00936                 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
00937                 {
00938                                 $keyword_id_old = false;
00939                                 $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
00940                                 $keyword_id_old = mysql_fetch_array($result);
00941                                 mysql_free_result ($result);
00942                                 if ($a_keyword_id != $keyword_id_old[0])        // If dp_keyword has changed
00943                                 {
00944                                         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
00945                                         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
00946                                         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
00947                                 }
00948                                 return true;
00949                 }
00950                 else
00951                 {
00952                         return false;
00953                 }
00954         }//end function
00955 
00956 
00961         function cleanDatabase ()
00962         {
00963                 if (isset($this->dlI))  //Checks if connected to database
00964                 {
00965                         $groups = ilCalInterface::getGroups();  //Gets all groups of the system
00966                         $ago = strtotime ("-6 month");  //Sets the time which date have to be ago to be deleted
00967                         $tobedeleted = false;
00968                         
00969                         $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
00970                         for ($i = 0; $row = mysql_fetch_array($result); $i++)
00971                         {
00972                                 $tobedeleted[$i] = $row[0];
00973                         }
00974                         mysql_free_result ($result);
00975                         
00976                         $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
00977                         for ($i = 0, $temp = false; $row = mysql_fetch_array($result); $i++)
00978                         {
00979                                 $temp[$i] = $row[0];
00980                         }
00981                         $tobedeleted = array_merge ($tobedeleted, $temp);
00982                         mysql_free_result ($result);
00983                         
00984                         $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
00985                         for ($i = 0, $temp = false; $row = mysql_fetch_array($result); $i++)
00986                         {
00987                                 $temp[$i] = $row[0];
00988                         }
00989                         $tobedeleted = array_merge ($tobedeleted, $temp);
00990                         mysql_free_result ($result);
00991                         
00992                         $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
00993                         for ($i = 0, $temp = false; $row = mysql_fetch_array($result); $i++)
00994                         {
00995                                 $temp[$i] = $row[0];
00996                         }
00997                         $tobedeleted = array_merge ($tobedeleted, $temp);
00998                         mysql_free_result ($result);
00999                         
01000                         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
01001                         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
01002                         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
01003                         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
01004                                                 
01005                         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
01006                         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
01007                         
01008                         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
01009                         
01010                         $result = mysql_query ("SELECT DISTINCT id FROM ".$this->dbase_cscw.".dp_keyword", $this->dlI); //Gets all keyword_ids
01011                         for ($i = 0, $tobedeleted = false; $row = mysql_fetch_array($result); $i++)
01012                         {
01013                                 $tobedeleted[$i] = $row[0];
01014                         }
01015                         mysql_free_result ($result);
01016 
01017                         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
01018                         
01019                         return true;            
01020                 }
01021                 else
01022                 {
01023                         return false;
01024                 }
01025         }//end function
01026 
01027 
01028 } //end Class
01029 ?>