<?php
//****************************************************************************************
// Copyright (C) 2000 Koen de Boeve
//
// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License
// as published by the Free Software Foundation; either version 2
// of the License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
//
// Version : MyPhPim-01.05
// Author : Koen de Boeve
// Contact: hide@address.com
//****************************************************************************************
if ( file_exists ( "conf" ) ) { $conf = "conf"; }
if ( file_exists ( "../conf" ) ) { $conf = "../conf"; }
include $conf . "/config.inc";
$GLOBALMYSQL = 'yes';
class SqlDB {
var $dbh;
//###################################################################
// Open a connection to the MySQL database
Function DB ( )
{
global $config;
$this->$dbh = mysql_connect ( $config[dbhost], $config[login], $config[pass] ) or die ( "Cannot connect to database, check conf/config.inc again!" );
mysql_select_db ( $config[db] );
}
//###################################################################
// Function to handle errors
Function error ( )
{
global $HTTP_SERVER_VARS;
global $basepath;
$errno = mysql_errno ( );
if ( $errno )
{
$errmsg = mysql_errno ( ) . " : " . mysql_error ( );
} else
{
$errmsg = 0;
}
return $errmsg;
}
//###################################################################
// Get an query id
Function query ( $query )
{
global $config;
$res = mysql_db_query ( $config[db], $query );
$this->error ( );
return $res;
}
//###################################################################
// get a result id
Function result ( $result, $i, $field )
{
return mysql_result ( $result, $i, $field );
}
//###################################################################
//get the number of affected rows
Function numrows ( $result )
{
return mysql_numrows ( $result );
}
//###################################################################
// retrieve results as array
Function fetch_array ( $result )
{
return mysql_fetch_array ( $result );
}
//###################################################################
// retrieve results as object
Function fetch_object ( $result )
{
return mysql_fetch_object ( $result );
}
//###################################################################
// get the id from the record you last inserted in the database
Function LastId ( ) {
//return mysql_insert_id ( );
$result = $this->query ( "SELECT LAST_INSERT_ID() AS last" );
return $this->result ( $result, 0, "last" );
}
//###################################################################
// Get information about the email account for user $user
Function Fetch_login ( $user, $account ) {
$crypt = new MCryptCl;
$db = $this->DB();
$query = "SELECT user_id FROM users WHERE user=\"$user\"";
$result = $this->query ( $query );
$user_id = $this->result ( $result, 0, "user_id" );
if ( $account == "0" ) {
$query = "SELECT * FROM account WHERE user=$user_id AND defaultacc='Y'";
} else {
$query = "SELECT * FROM account WHERE user=$user_id AND server=\"$account\"";
}
$result = $this->query ( $query );
if ( ! $result ) { return 0; }
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$value = 0;
} else {
$value[0] = $this->result ( $result, 0, "login" );
$value[2] = $this->result ( $result,0, "proto" );
$value[3] = $this->result ( $result,0, "server" );
$value[4] = $this->result ( $result,0, "port" );
$tmp = $this->result ( $result,0, "password" );
$value[1] = $crypt->Decrypt ( $tmp );
}
return $value;
}
//###################################################################
// Get a list of all email accounts $user manages with MyPhPim
Function FetchAccountnames ( $user ) {
$db = $this->DB();
$crypt = new MCryptCl;
$query = "SELECT user_id FROM users WHERE user=\"$user\"";
$result = $this->query ( $query );
$user_id = $this->result ( $result, 0, "user_id" );
$query = "SELECT server FROM account WHERE user=$user_id ";
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$go->test = 0;
$value[0] = $go;
} else {
for ( $i = 0; $i < $numrows; $i++ ) {
$go->server = $this->result ( $result, $i, "server" );
$value[$i] = $go;
}
}
return $value;
}
//###################################################################
// same thing as above, but returns ALL information as an array of objects,
// each account is an array
Function FetchAllAccounts ( $user ) {
$db = $this->DB();
$crypt = new MCryptCl;
$query = "SELECT user_id FROM users WHERE user=\"$user\"";
$result = $this->query ( $query );
$user_id = $this->result ( $result, 0, "user_id" );
$query = "SELECT * FROM account WHERE user=$user_id";
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$go->test = 0;
$value[0] = $go;
} else {
for ( $i = 0; $i < $numrows; $i++ ) {
$go->user_id = $this->result ( $result, $i, "user" );
$go->account_id = $this->result ( $result, $i, "account_id" );
$go->loginname = $this->result ( $result, $i , "login" );
$tmp = $this->result ( $result, $i , "password" );
$go->password = $crypt->Decrypt ( $tmp );
$go->proto = $this->result ( $result, $i , "proto" );
$go->server = $this->result ( $result, $i , "server" );
$go->port = $this->result ( $result, $i , "port" );
$go->defacc = $this->result ( $result, $i , "defaultacc" );
$go->test = 1;
$value[$i] = $go;
}
}
return $value;
}
//###################################################################
// add a new email account you wish to manage via MyPhPim
Function CreateAccount ( $vars, $user ) {
$db = $this->DB();
$query = "SELECT user_id FROM users WHERE user=\"$user\"";
$result = $this->query ( $query );
$user_id = $this->result ( $result, 0, "user_id" );
$query = "SELECT user FROM account WHERE user=$user_id";
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$query = "INSERT INTO account VALUES ( NULL, $user_id, '$vars->server', $vars->port, '$vars->proto', '$vars->loginname', '$vars->encrpass', 'Y')";
} else {
$query = "INSERT INTO account VALUES ( NULL, $user_id, '$vars->server', $vars->port, '$vars->proto', '$vars->loginname', '$vars->encrpass', 'N')";
}
$result = $this->query ( $query );
if( @mysql_affected_rows ( ) > 0 ) {
return 1;
} else {
return 0;
}
}
//###################################################################
// exactly the opposite as above :-)
Function DeleteEmailAccount ( $vars ) {
$db = $this->DB();
$query = "DELETE FROM account WHERE account_id=$vars->account_id AND user='$vars->user'";
$result = $this->query ( $query );
}
//###################################################################
// submit changes to this account
Function UpdateAccount ( $vars ) {
$db = $this->DB();
if ( $vars->defacc == "Y" ) {
$query = ( "UPDATE account SET defaultacc='N' WHERE user='$vars->user'" );
$result = $this->query ( $query );
}
$query = ( "UPDATE account SET server='$vars->server', port='$vars->port', proto='$vars->proto', login='$vars->loginname', password='$vars->encrpass', defaultacc='$vars->defacc' WHERE account_id='$vars->account_id'" );
$result = $this->query ( $query );
}
//###################################################################
// return A date with a certain offset
Function ReturnDateInterval ( $type, $interval, $date ) {
$db = $this->DB();
$query = "SELECT DATE_ADD( '$date', INTERVAL $interval $type) AS newdate";
$result = $this->query ( $query );
$newdate = $this->result ( $result, 0, "newdate" );
return $newdate;
}
//###################################################################
// returns an absolute value ( daynumber since year 0 )
Function GetTO_DAYS ( $date ) {
$db = $this->DB();
$query = "SELECT TO_DAYS('$date') AS absdate";
$result = $this->query ( $query );
$absdate = $this->result ( $result, 0, "absdate" );
return $absdate;
}
//###################################################################
Function FindGroupMembers ( $groupid ) {
$db = $this->DB();
$query = "SELECT user_id FROM groupmembers WHERE group_id='$groupid'";
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
for ( $i = 0; $i < $numrows; $i++ ) {
$members[$i] = $this->result ( $result, $i, "user_id" );
}
return $members;
}
//###################################################################
// Return $user's user_id
Function UserId ( $user ) {
$db = $this->DB();
$query = "SELECT user_id FROM users WHERE user='$user'";
$result = $this->query ( $query );
$user_id = $this->result ( $result, 0, "user_id" );
return $user_id;
}
//###################################################################
// Returns username for user_id
Function UserName ( $user_id ) {
$db = $this->DB();
$query = "SELECT user FROM users WHERE user_id=$user_id";
$result = $this->query ( $query );
$user = $this->result ( $result, 0, "user" );
return $user;
}
//###################################################################
Function InsertShares ( $vars, $last_id) {
$table = $vars->table . "_shares";
$id_field = $vars->id_field;
$groups = $vars->groups;
$users = $vars->users;
$sharedtype = $vars->sharedtype;
for ( $i = 0; $i < $vars->numgroups; $i++ ) {
// check if there is already another type of share for this record
$result = $this->query ( "SELECT share_id FROM $table WHERE sharetype='public' AND $id_field=$last_id" );
if ( $result ) {
$numrows = $this->numrows ( $result );
if ( $numrows > 0 ) {
for ( $j = 0; $j < $numrows; $j++ ) {
$share_id = $this->result ( $result, $j, "share_id" );
$result = $this->query ( "DELETE FROM $table WHERE share_id=$share_id" );
}
}
}
// first check if the shared item allready exists, if so, update the existing one instead of inserting a new one
$result = $this->query ( "SELECT share_id FROM $table WHERE sharetype='$sharedtype' AND sharegroups=$groups[$i] AND $id_field=$last_id" );
if ( $result ) {
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$query = "INSERT INTO $table VALUES ( NULL, '$sharedtype', '', $groups[$i], $last_id ) ";
} else {
$query = "UPDATE $table SET sharetype='$sharedtype', sharegroups=$groups[$i] WHERE $id_field=$last_id";
}
} else {
$query = "INSERT INTO $table VALUES ( NULL, '$sharedtype', '', $groups[$i], $last_id ) ";
}
$result = $this->query ( $query );
}
for ( $i = 0; $i < $vars->numusers; $i++ ) {
// check if there is already another type of share for this record
$result = $this->query ( "SELECT share_id FROM $table WHERE sharetype='public' AND $id_field=$last_id" );
if ( $result ) {
$numrows = $this->numrows ( $result );
if ( $numrows > 0 ) {
for ( $j = 0; $j < $numrows; $j++ ) {
$share_id = $this->result ( $result, $j, "share_id" );
$result = $this->query ( "DELETE FROM $table WHERE share_id=$share_id" );
}
}
}
// first check if the shared item allready exists, if so, update the existing one instead of inserting a new one
$result = $this->query ( "SELECT share_id FROM $table WHERE sharetype='$sharedtype' AND shareusers=$users[$i] AND $id_field=$last_id" );
if ( $result ) {
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$query = "INSERT INTO $table VALUES ( NULL, '$sharedtype', $users[$i], '', $last_id ) ";
} else {
$query = "UPDATE $table SET sharetype='$sharedtype', shareusers=$users[$i] WHERE $id_field=$last_id";
}
} else {
$query = "INSERT INTO $table VALUES ( NULL, '$sharedtype', $users[$i], '', $last_id ) ";
}
$result = $this->query ( $query );
}
if ( $vars->sharedtype == "public" ) {
// check if there is already another type of share for this record
$result = $this->query ( "SELECT share_id FROM $table WHERE sharetype='distribute' AND $id_field=$last_id" );
if ( $result ) {
$numrows = $this->numrows ( $result );
if ( $numrows > 0 ) {
for ( $j = 0; $j < $numrows; $j++ ) {
$share_id = $this->result ( $result, $j, "share_id" );
$result = $this->query ( "DELETE FROM $table WHERE share_id=$share_id" );
}
}
}
$result = $this->query ( "SELECT share_id FROM $table WHERE sharetype='$sharedtype' AND $id_field=$last_id" );
if ( $result ) {
$numrows = $this->numrows ( $result );
if ( $numrows == 0 ) {
$query = "INSERT INTO $table VALUES ( NULL, '$sharedtype', '', '', $last_id ) ";
} else {
$query = "UPDATE $table SET sharetype='$sharedtype', sharegroups=0, shareusers=0 WHERE $id_field=$last_id";
}
} else {
$query = "INSERT INTO $table VALUES ( NULL, '$sharedtype', '', '', $last_id ) ";
}
$result = $this->query ( $query );
}
}
//###################################################################
// Create a new Record
Function InsertNewRecord ( $vars) {
$db = $this->DB();
switch ( $vars->table ) {
case "calendar" :
$event = $vars->event;
if ( $event->until != 'no' ) {
$query = "INSERT INTO calendar VALUES ( NULL, '$event->owner_id', '$event->title',
'$event->note', '$event->date', '$event->starttime', '$event->stoptime',
'$event->repeat', $event->interv, '$event->repeattype', '$event->until', NULL, NULL, 'N','N','N','N')";
} else {
$query = "INSERT INTO calendar VALUES ( NULL, '$event->owner_id', '$event->title',
'$event->note', '$event->date', '$event->starttime', '$event->stoptime',
'$event->repeat', '$event->interv', '$event->repeattype', NULL , NULL, NULL, 'N','N','N','N' )";
}
break;;
case "todo" :
$query = "INSERT INTO todo VALUES ( NULL, '$vars->description', '$vars->detail',
'$vars->note', '$vars->start', '$vars->stop', '$vars->due', '$vars->owner_id',
'N', 'N', 'N', 'N', '$vars->status', '$vars->date' , '', '') ";
break;;
case "notes" :
$query = "INSERT INTO notes VALUES ( NULL, '$vars->note', '$vars->owner_id', 'N', 'N', 'N', 'N', '$vars->date' , '', '') ";
break;;
case "addresses" :
$query = "INSERT INTO addresses ( address_id, owner, name, firstname, address, city, zip, state,
country, telhome, telother, telwork, faxwork, mobile, email, birthday, notify_birthday,
title, main, pager, custom1, custom2, custom3, custom4,
company, note, addrdelete, addrread, addrupdate, addrwrite )
VALUES ( NULL, '$vars->owner_id', '$vars->name', '$vars->firstname',
'$vars->address', '$vars->city', '$vars->zip', '$vars->state',
'$vars->country', '$vars->telhome', '$vars->telother', '$vars->telwork', '$vars->faxwork',
'$vars->mobile', '$vars->email', '$vars->birthday', '$vars->notify_birthday',
'$vars->title', '$vars->main', '$vars->pager', '$vars->custom1', '$vars->custom2',
'$vars->custom3', '$vars->custom4', '$vars->company', '$vars->note', 'N','N','N','N') ";
break;;
}
$result = $this->query ( $query );
$id = $this->LastId ( );
$this->InsertShares ( $vars, $id );
return $id;
}
//###################################################################
// Create a record for other users or groups
Function InsertForUser ( $vars ) {
global $user;
$db = $this->DB();
$byuser = $this->UserId ( $user );
if ( $vars->otheruser == 'Y' ) {
$table = $vars->table;
$type = $vars->type;
$comment = $vars->comment;
$forusers = $vars->forusers;
$forgroups = $vars->forgroups;
$numusers = count ( $forusers );
$numgroups = count ( $forgroups );
for ( $i = 0; $i < $numusers; $i++ ) {
$last_id = $this->InsertNewRecord ( $vars );
$query = "INSERT INTO mr VALUES ( NULL, '$forusers[$i]', '$byuser', 'P', '$type', '$comment', '$last_id')";
$this->query ( $query );
}
for ( $i = 0; $i < $numgroups; $i++ ) {
$members = $this->FindGroupMembers ( $forgroups[$i] );
$nummembers = count ( $members );
for ( $j = 0;$j < $nummembers; $j++ ) {
$last_id = $this->InsertNewRecord ( $vars);
$query = "INSERT INTO mr VALUES ( NULL, '$members[$j]', '$byuser', 'P', '$type', '$comment', '$last_id')";
$this->query ( $query );
}
}
}
}
//###################################################################
// Check if there are any records pending for user, created by another user
Function SearchPendingRecords ( $user_id , $mrid ) {
$db = $this->DB();
if ( $mrid == 0 ) {
$query = "SELECT * FROM mr WHERE foruser='$user_id' AND flag='P'";
} else {
$query = "SELECT * FROM mr WHERE mr_id='$mrid'";
}
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
for ( $i = 0; $i < $numrows; $i++ ) {
$data->mr_id = $this->result ( $result, $i, "mr_id" );
$data->foruser = $this->result ( $result, $i, "foruser" );
$data->byuser = $this->result ( $result, $i, "byuser" );
$data->flag = $this->result ( $result, $i, "flag" );
$data->type = $this->result ( $result, $i, "eventtype" );
$data->comment = $this->result ( $result, $i, "comments" );
$data->record = $this->result ( $result, $i, "record" );
$arr[$i] = $data;
}
return $arr;
}
//###################################################################
Function SearchAddedRecords ( $mrid ) {
global $user;
$db = $this->DB();
$user_id = $this->UserId ( $user );
if ( $mrid == 0 ) {
$query = "SELECT * FROM mr WHERE byuser='$user_id' AND flag='A'";
} else {
$query = "SELECT * FROM mr WHERE mr_id='$mrid'";
}
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
for ( $i = 0; $i < $numrows; $i++ ) {
$data->mr_id = $this->result ( $result, $i, "mr_id" );
$data->foruser = $this->result ( $result, $i, "foruser" );
$data->byuser = $this->result ( $result, $i, "byuser" );
$data->flag = $this->result ( $result, $i, "flag" );
$data->type = $this->result ( $result, $i, "eventtype" );
$data->comment = $this->result ( $result, $i, "comments" );
$data->record = $this->result ( $result, $i, "record" );
$arr[$i] = $data;
}
return $arr;
}
//###################################################################
Function SearchRejectedRecords ( $mrid ) {
global $user;
$db = $this->DB();
$user_id = $this->UserId ( $user );
if ( $mrid == 0 ) {
$query = "SELECT * FROM mr WHERE byuser='$user_id' AND flag='R'";
} else {
$query = "SELECT * FROM mr WHERE mr_id='$mrid'";
}
$result = $this->query ( $query );
$numrows = $this->numrows ( $result );
for ( $i = 0; $i < $numrows; $i++ ) {
$data->mr_id = $this->result ( $result, $i, "mr_id" );
$data->foruser = $this->result ( $result, $i, "foruser" );
$data->byuser = $this->result ( $result, $i, "byuser" );
$data->flag = $this->result ( $result, $i, "flag" );
$data->type = $this->result ( $result, $i, "eventtype" );
$data->comment = $this->result ( $result, $i, "comments" );
$data->record = $this->result ( $result, $i, "record" );
$arr[$i] = $data;
}
return $arr;
}
//###################################################################
Function ShowThisRecord ( $data ) {
$db = $this->DB();
switch ( $data->type ) {
case "event" : $query = "SELECT * FROM calendar WHERE cal_id=$data->record";
$result = $this->query ( $query );
$title = $this->result ( $result, 0, "title" );
$note = $this->result ( $result, 0, "note" );
$date = $this->result ( $result, 0, "date" );
$starttime = $this->result ( $result, 0, "starttime" );
$stoptime = $this->result ( $result, 0, "stoptime" );
$interv = $this->result ( $result, 0, "interv" );
$repeattype = $this->result ( $result, 0, "repeattype" );
$until = $this->result ( $result, 0, "until" );
echo "<u>Title: </u><b>$title</b><br>\n";
echo "<u>Body: </u><b>$note</b><br>\n";
echo "<u>Date: </u><b>$date</b><br>\n";
echo "<u>Start Time: </u><b>$starttime</b><br>\n";
echo "<u>Stop Time: </u><b>$stoptime</b><br>\n";
echo "Repeat every <b>$interv</b> ";
echo "<b>$repeattype</b><br>\n";
echo "<u>Until: </u><b>$until</b><br>\n";
break;;
case "note" : $query = "SELECT * FROM notes WHERE notes_id=$data->record";
$result = $this->query ( $query );
$note = $this->result ( $result, 0, "note" );
echo "<u>Body: </u><b>$note</b><br>\n";
break;;
case "todo" : $query = "SELECT * FROM todo WHERE todo_id=$data->record";
$result = $this->query ( $query );
$description = $this->result ( $result, 0, "description" );
$detail = $this->result ( $result, 0, "detail" );
$note = $this->result ( $result, 0, "note" );
$startdate = $this->result ( $result, 0, "startdate" );
$stopdate = $this->result ( $result, 0, "stopdate" );
$duedate = $this->result ( $result, 0, "duedate" );
$status = $this->result ( $result, 0, "status" );
echo "<u>Title: </u><b>$description</b><br>\n";
echo "<u>Body: </u><b>$detail</b><br>\n";
echo "<u>Start Date: </u><b>$startdate</b><br>\n";
echo "<u>Stop Date: </u><b>$stopdate</b><br>\n";
echo "<u>Due Date: </u><b>$duedate</b><br>\n";
echo "<u>Note: </u> <b>$note</b><br>\n ";
echo "<u>Status: </u><b>$status</b><br>\n";
break;;
case "address" : $query = "SELECT * FROM addresses WHERE address_id=$data->record";
$result = $this->query ( $query );
$name = $this->result ( $result, 0, "name" );
$firstname = $this->result ( $result, 0, "firstname" );
$address = $this->result ( $result, 0, "address" );
$city = $this->result ( $result, 0, "city" );
$zip = $this->result ( $result, 0, "zip" );
$state = $this->result ( $result, 0, "state" );
$country = $this->result ( $result, 0, "country" );
$telhome = $this->result ( $result, 0, "telhome" );
$telother = $this->result ( $result, 0, "telother" );
$telwork = $this->result ( $result, 0, "telwork" );
$faxwork = $this->result ( $result, 0, "faxwork" );
$main = $this->result ( $result, 0, "main" );
$pager = $this->result ( $result, 0, "pager" );
$mobile = $this->result ( $result, 0, "mobile" );
$custom1 = $this->result ( $result, 0, "custom1" );
$custom2 = $this->result ( $result, 0, "custom2" );
$custom3 = $this->result ( $result, 0, "custom3" );
$custom4 = $this->result ( $result, 0, "custom4" );
$email = $this->result ( $result, 0, "email" );
$birthday = $this->result ( $result, 0, "birthday" );
$company = $this->result ( $result, 0, "company" );
$note = $this->result ( $result, 0, "note" );
$title = $this->result ( $result, 0, "title" );
echo "<u>Name: </u><b>$name </b>\n";
echo "<u>First Name: </u><b>$firstname</b><br>\n";
echo "<u>Company: </u><b>$company </b>\n";
echo "<u>Note: </u><b>$note</b><br>\n";
echo "<u>Address: </u><b>$address</b><br>\n";
echo "<u>City: </u><b>$city $zip</b><br>\n";
echo "<u>State: </u><b>$state</b><br>\n";
echo "<u>Country: </u><b>$country</b><br>\n";
echo "<u>Tel Home: </u><b>$telhome</b><br>\n";
echo "<u>Tel Work: </u><b>$telwork</b><br>\n";
echo "<u>Fax Work: </u><b>$faxwork</b><br>\n";
echo "<u>Other: </u><b>$telother</b><br>\n";
echo "<u>Other (2): </u><b>$main</b><br>\n";
echo "<u>Pager: </u><b>$pager</b><br>\n";
echo "<u>Mobile: </u><b>$mobile</b><br>\n";
echo "<u>Custom 1: </u><b>$custom1</b><br>\n";
echo "<u>Custom 2: </u><b>$custom2</b><br>\n";
echo "<u>Custom 3: </u><b>$custom3</b><br>\n";
echo "<u>Custom 4: </u><b>$custom4</b><br>\n";
echo "<u>E-Mail: </u><b>$email</b><br>\n";
echo "<u>Birthday: </u><b>$birthday</b><br>\n";
break;;
}
}
// end of SqlDB Class
}
class DB extends SqlDB { }
?>