database.php | searchcode

/database.php

https://bitbucket.org/sspssp/akk-berlin
PHP | 578 lines | 280 code | 198 blank | 100 comment | 26 complexity | 3ad2c14b87a685c5105b832d1cb60ca0 MD5 | raw file

<?php

/*
* ----------------------------------------------------------------------------
* Hendrik Stiefel <hendrik@machmaldieaugenauf.de>, Jörg Franke <piratenpartei@newjorg.de> 
* and Wilm Schumacher <wilm.schumacher@piratenpartei.de> wrote this file. As 
* long as you retain this notice you can do whatever you want with this stuff. 
* If we meet some day, and you think this stuff is worth it, you can buy us a 
* beer in return
* 
* Hendrik, Jörg and Wilm
* ----------------------------------------------------------------------------
*/



/*
	the database class. This is the connection to the world of mysql
*/
class DataBase {

	private $db;


	// constructor
	function __construct() {

		$this->db=$this->DBConnect();
	}


	/*
	the fetching function with inbuild x-site-scripting protection
	*/
	public function mysql_fetch_row( $res ) {

		$out=mysql_fetch_row( $res );

		if( $out==false ){ return false; };

		foreach( $out as &$value ){
			$value=htmlspecialchars( $value );
		};

		return $out;

	}

	/*
	the main search function
	makes the search depending on the input
	*/
	public function GetSearch ( $lastname , $firstname , $membershipid , 
		$lastnamelike=true , $firstnamelike=true , $membershipidlike=true ) {

		$lastname=mysql_real_escape_string( $lastname , $this->db );
		$firstname=mysql_real_escape_string( $firstname , $this->db );
		$membershipid=mysql_real_escape_string( $membershipid , $this->db );

		$query="SELECT
			dbid,lastname,middlename,firstname,membershipid,birthdate,adress,city,zipcode,
			division,paid,akkreditiert,paidchanged,acomment,warning,topay
		 FROM members  ";

	
		if( $lastname!="" or $firstname!="" or $membershipid!="" ){
		
			$query=$query.'WHERE ';

			// lastname
			if( $lastname!="" ){
				if( $lastnamelike==true ){
					$query=$query.' lastname LIKE "%'.$lastname.'%" AND';
				} else {
					$query=$query.' lastname="'.$lastname.'" AND';
				};
			};
			// firstname
			if( $firstname!="" ){
				if( $firstnamelike==true ){
					$query=$query.' firstname LIKE "%'.$firstname.'%" AND';
				} else {
					$query=$query.' firstname="'.$firstname.'" AND';
				};
			};
			// lastname
			if( $membershipid!="" ){
				if( $membershipidlike==true ){
					$query=$query.' membershipid LIKE "%'.$membershipid.'%" AND';
				} else {
					$query=$query.' membershipid="'.$membershipid.'" AND';
				};
			};


			$query=substr( $query , 0 , strlen($query)-3);

		};

		$query=$query.' ORDER BY lastname,firstname,membershipid;';


		$out=mysql_query( $query , $this->db ) or die('something went wrong');

		return $out;


	}


	/*
	document
	*/
	function DocumentChange( $dbid , $type , $comment=NULL ) {

		$userid=mysql_real_escape_string( $_SESSION['id'] );

		if( is_null($comment) ){
			$query='INSERT INTO changes (dbid,userid,thechange,thetime) 
			 VALUES ('.$dbid.','.$userid.','.$type.',NOW())';
		} else {
			$comment=mysql_real_escape_string( $comment );
			$query="INSERT INTO changes (dbid,userid,thechange,acomment,thetime) 
			 VALUES (".$dbid.",".$userid.",".$type.",'".$comment."',NOW())";			
		};

		mysql_query( $query , $this->db ) or die('somethnig went wrong');
	
		return;
	
	}

	/*
	deakk a contact (with mysql injection protection)
	*/
	public function DeAkk( $number ) {


		// the work
		$number=mysql_real_escape_string( $number , $this->db );

		$query='UPDATE members SET akkreditiert=0 WHERE dbid='.$number.';';

		mysql_query( $query , $this->db ) or die('somethnig went wrong');

		// document change
		$this->DocumentChange( $number,1 );

		return 0;

	}

	/*
	akk a contact (with mysql injection protection)
	*/
	public function Akk( $number ) {


		// the work
		$number=mysql_real_escape_string( $number , $this->db );

		$query='UPDATE members SET akkreditiert=1 WHERE dbid='.$number.';';

		mysql_query( $query , $this->db ) or die('somethnig went wrong');

		// document change
		$this->DocumentChange( $number,0 );


		return 0;

	}

	/*
	pay a contact (with mysql injection protection)
	*/
	public function Pay( $number , $comment ) {


		// the work
		$number=mysql_real_escape_string( $number , $this->db );
		$comment=mysql_real_escape_string( $comment , $this->db );

		$query='UPDATE members SET paid=1,paidchanged=1,acomment=\''.$comment.'\' WHERE dbid='.$number.';';

		mysql_query( $query , $this->db ) or die('somethnig went wrong');

		// document change
		$this->DocumentChange( $number,2 , $comment=$comment );


		return 0;

	}

	/*
	unpay a contact (with mysql injection protection)
	*/
	public function UnPay( $number ) {


		// the work
		$number=mysql_real_escape_string( $number , $this->db );

		$query='UPDATE members SET paid=0,akkreditiert=0,paidchanged=0 WHERE dbid='.$number.';';

		mysql_query( $query , $this->db ) or die('somethnig went wrong');

		// document change
		$this->DocumentChange( $number,3 );


		return 0;

	}



	/*
	connecting to a database
	
	*/
	private function DbConnect() 
	{
		require_once('Constants.php');
	
		$db=mysql_connect($databhost , $databusername, $databpass);

		if (!$db) {
			die('Verbindung zur Datenbank fehlgeschlagen: '. mysql_error());
		}

		
		 $db_selected=mysql_select_db($databname, $db);
		if (!$db_selected) {
			die('Kann die Datenbank nicht benutzen: ' . mysql_error());
		}
		mysql_set_charset('utf8',$db);
		$charset = mysql_client_encoding($db);
		//printf("Benutzer Zeichensatz ist %s\n", $charset);
		return $db;
	}

	/*
	search for contacts with a specific membership id (with mysql injection protection)
	*/
	public function MemberschipIDSearch( $id ) {

		$id=mysql_real_escape_string( $id );

		$query='SELECT 
			dbid,lastname,middlename,firstname,membershipid,birthdate,adress,city,zipcode,
			division,paid,akkreditiert,paidchanged,acomment,warning,topay
		 FROM members WHERE membershipid='.$id.' ORDER BY lastname,firstname,membershipid;';

		return mysql_query( $query , $this->db );

	}

	/*
	search for contacts with a specific lastname (with mysql injection protection)
	*/
	public function LastnameSearch( $name ) {

		$name=mysql_real_escape_string( $name );

		$query='SELECT 
			dbid,lastname,middlename,firstname,membershipid,birthdate,adress,city,zipcode,
			division,paid,akkreditiert,paidchanged,acomment,warning,topay
		 FROM members WHERE lastname LIKE "%'.$name.'%" ORDER BY lastname,firstname,membershipid;';

		return mysql_query( $query , $this->db );

	}

	/*
	gets the devisions in which the contacts are divided
	*/
	public function GetDivisions() {

		$query='SELECT division FROM members GROUP BY division;';

		return mysql_query( $query );

	}

	/*
	gets the number of contacts by a division

	for $paidlive = x "paid on location" = x is checked


	for $akkreditiert = x "akkreditiert = x is checked


	(with mysql injection protection)
	*/
	public function GetTotalByDivision( $division , $paidlive=NULL , $akkreditiert=NULL ) {

		$division=mysql_real_escape_string( $division , $this->db );

		$query="SELECT COUNT(*) FROM members WHERE division='".$division."'";;

		if( is_null( $paidlive )==false ){
			$paidlive=mysql_real_escape_string( $paidlive , $this->db );

			$query=$query.' AND paidchanged='.$paidlive;
		};

		if( is_null( $akkreditiert )==false ){
			$akkreditiert=mysql_real_escape_string( $akkreditiert , $this->db );

			$query=$query.' AND akkreditiert='.$akkreditiert;
		};

		$query=$query.';';

		$res=mysql_query( $query , $this->db );

		$num=mysql_num_rows( $res );

		if( $num!=1 ){
			die('something went horrible wrong');
		};

		$row=$this->mysql_fetch_row( $res );

		return $row[0];

	}
	/*
		This function is new in rel. 1.1. It's only for get  some stats automaticly without using excel-stuff :-)

		$whichOne=1 -> returns number of already akk members for given division
		$whichOne=2 -> returns number of all members which paid for given division
		$whichOne=3 -> returns number of all members
		$whichOne=4 -> returns number of all members which paid
	*/

	public function GetStats($division, $whichOne)
	{
	        $division=mysql_real_escape_string( $division , $this->db );
		switch($whichOne)
		{
			case 1:  $query="SELECT COUNT(*) FROM members WHERE division='".$division."' and akkreditiert = 1"; break;
			case 2:  $query="SELECT COUNT(*) FROM members WHERE division='".$division."' and paid = 1"; break;

                        case 3:  $query="SELECT COUNT(*) FROM members"; break;
 	                case 4:  $query="SELECT COUNT(*) FROM members where  paid = 1"; break;
		}
		$query=$query.';';
		$res=mysql_query( $query , $this->db );
                $num=mysql_num_rows( $res );

                if( $num!=1 ){
                        die('something went horrible wrong , maybe the databaseconnection is lost.');
                };

                $row=$this->mysql_fetch_row( $res );

                return $row[0];
	}

	/*
	get all contacts which paid live, by division (with mysql injection protection)
	*/
	public function GetLivePaid( $division ) {

		$division=mysql_real_escape_string( $division , $this->db );

		$query="SELECT dbid,lastname,middlename,firstname,membershipid,birthdate,acomment FROM members
		 WHERE division='".$division."' AND paidchanged=1 AND paid=1;";

		$res=mysql_query( $query , $this->db );

		return $res;


	}

	/*
	gets the whole list with all info to print it out
	*/
	public function GetBackupListByDivision( $division ) {

		$division=mysql_real_escape_string( $division , $this->db );

		$query="SELECT lastname,middlename,firstname,membershipid,birthdate,adress,city,paid,akkreditiert,topay FROM members
		 WHERE division='".$division."' ORDER BY lastname,firstname;";


		$res=mysql_query( $query , $this->db );

		return $res;
	
	}

	/*
	get all actions on one entry
	*/
	public function GetChanges( $dbid ) {

		$dbid=mysql_real_escape_string( $dbid , $this->db );	


		$query='SELECT changeid,userid,dbid,thechange,acomment,thetime FROM changes 
		 WHERE dbid='.$dbid.' ORDER BY thetime;';

		$res=mysql_query( $query , $this->db );

		return $res;

	}

	/*
	gets a name of a user by the id
	*/
	public function GetUserById ( $id ) {

		$id=mysql_real_escape_string( $id , $this->db );	

		$query='SELECT name FROM users WHERE userid='.$id.';';

		$res=mysql_query( $query , $this->db );

		if( mysql_num_rows( $res )!=1 ){
			die('something went horrible wrong');
		};

		return $this->mysql_fetch_row($res);

	}

	/*
	gets the id of a user by the name
	*/
	public function GetIDByName ( $name ) {

		$name=mysql_real_escape_string( $name , $this->db );	

		$query='SELECT userid FROM users WHERE name="'.$name.'";';

		$res=mysql_query( $query , $this->db );

		if( mysql_num_rows( $res )>1 ){
			die('something went horrible wrong');
		};

		$out=$this->mysql_fetch_row($res);

		return $out[0];

	}


	/*
	checks wheather a user + passwd is correct (returns number of entries)
	*/
	public function CheckUserAndPw($name,$passwd)
	{

		$name=mysql_real_escape_string($name);
		$passwd=mysql_real_escape_string($passwd);

		$query="SELECT userid FROM users WHERE 
		 name='".$name."' and hash=md5('".$passwd."');";
		$result = mysql_query($query , $this->db );

		$num=mysql_num_rows($result);

		if( $num>1 ){
			die( 'something went horrible wrong' );
		};

		return  $num;
	}

	// gets all users
	public function GetAllUsers () {

		$query="SELECT userid,name FROM users ORDER BY userid;";		

		$res=mysql_query( $query , $this->db );

		return $res;

	}

	public function AddUser( $name , $passwd ) {

		$name=mysql_real_escape_string($name);
		$passwd=mysql_real_escape_string($passwd);

		$query='INSERT INTO users (name,hash) VALUES ("'.$name.'",md5("'.$passwd.'"));';		

		mysql_query( $query , $this->db );

		return true;

	}

	public function DeleteUser( $id ) {

		$id=mysql_real_escape_string($id);

		$query='DELETE FROM users WHERE userid='.$id.';';		

		mysql_query( $query , $this->db );

		return true;

	}

	public function ChangePasswd( $id , $passwd ) {

		$id=mysql_real_escape_string($id);
		$passwd=mysql_real_escape_string($passwd);
		
		$query='UPDATE users SET hash=md5("'.$passwd.'") WHERE userid='.$id.';';		

		mysql_query( $query , $this->db );

		return true;

	}

	public function GetDBTime(  ) {
		$res=mysql_query( "SELECT NOW();" );

		$row=mysql_fetch_row( $res );

		return $row[0];

	}

	public function GetMemberByID ( $id ) {

		$id=mysql_real_escape_string($id);

		$query='SELECT lastname,middlename,firstname,membershipid,birthdate,adress,city,zipcode,division 
		 FROM members WHERE dbid='.$id.';';

		$res=mysql_query( $query );

		$num=mysql_num_rows( $res );

		if( $num==0 ){

			return false;

		} else if ( $num==1 ){

			$out=$this->mysql_fetch_row($res);
			return $out;
		} else {
			die( 'something went horrible wring' );
		};


	}
	public function getWahlkreis($street, $streetNumber, $limit = 5)
	{
		$sql = 'SELECT * FROM `adressen` WHERE `Strassenname` LIKE "%'.mysql_real_escape_string(substr($street,0, $limit)).'%" AND `Nummerierungskennzeichen` IN("F", IF(MOD('.mysql_real_escape_string($streetNumber).', 2)=0, "G", "U")) AND `HausnrVon` <= '.mysql_real_escape_string($streetNumber).' AND `HausnrBis` >= '.mysql_real_escape_string($streetNumber);
echo "<br>";
		$res = mysql_query($sql);
		$ret = array(); // Return Array
		while($row = mysql_fetch_array($res))
		{
			$ret[] = $row;
		}
		return $ret;
	}

};



?>