/database.php
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;
- }
- };
- ?>