X-Git-Url: https://git.nubati.net/cgi-bin/gitweb.cgi?p=e-DoKo.git;a=blobdiff_plain;f=db.php;h=646f0043a27b66c7facf5cc57d59ce9e2b896fa2;hp=a44436043228c57c132c9367425e58756aa6bfaf;hb=dfbea65d8ae9c21190a9fe5b69ea2ee098dcede7;hpb=44adf43ccb058fb76e7bdbb18917ca86a7f7ec29 diff --git a/db.php b/db.php index a444360..646f004 100644 --- a/db.php +++ b/db.php @@ -61,6 +61,16 @@ function DB_get_email_by_name($name) else return ""; } +function DB_get_passwd_by_name($name) +{ + $result = mysql_query("SELECT password FROM User WHERE fullname=".DB_quote_smart($name).""); + $r = mysql_fetch_array($result,MYSQL_NUM); + + if($r) + return $r[0]; + else + return ""; +} function DB_get_email_by_userid($id) { @@ -134,12 +144,39 @@ function DB_get_userid_by_email_and_password($email,$password) $result = mysql_query("SELECT id FROM User WHERE email=".DB_quote_smart($email)." AND password=".DB_quote_smart($password)); $r = mysql_fetch_array($result,MYSQL_NUM); + /* test if a recovery password has been set */ + if(!$r) + { + $result = mysql_query("SELECT User.id FROM User". + " LEFT JOIN Recovery ON User.id=Recovery.user_id". + " WHERE email=".DB_quote_smart($email). + " AND Recovery.password=".DB_quote_smart($password). + " AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= Recovery.create_date"); + $r = mysql_fetch_array($result,MYSQL_NUM); + } + if($r) return $r[0]; else return 0; } +function DB_check_recovery_passwords($password,$email) +{ + $result = mysql_query("SELECT User.id FROM User". + " LEFT JOIN Recovery ON User.id=Recovery.user_id". + " WHERE email=".DB_quote_smart($email). + " AND Recovery.password=".DB_quote_smart($password). + " AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= Recovery.create_date"); + $r = mysql_fetch_array($result,MYSQL_NUM); + + if($r) + return 1; + else + return 0; + +} + function DB_get_handid_by_hash($hash) { $result = mysql_query("SELECT id FROM Hand WHERE hash=".DB_quote_smart($hash)); @@ -455,7 +492,7 @@ function DB_get_current_trickid($gameid) if(!$sequence || $sequence==4) { - mysql_query("INSERT INTO Trick VALUES (NULL,NULL,NULL, ".DB_quote_smart($gameid).")"); + mysql_query("INSERT INTO Trick VALUES (NULL,NULL,NULL, ".DB_quote_smart($gameid).",NULL)"); $trickid = mysql_insert_id(); $sequence = 1; $number++; @@ -473,7 +510,6 @@ function DB_get_max_trickid($gameid) $result = mysql_query("SELECT MAX(id) FROM Trick WHERE game_id=".DB_quote_smart($gameid)); $r = mysql_fetch_array($result,MYSQL_NUM) ; - return ($r?$r[0]:NULL); } @@ -580,6 +616,7 @@ function DB_get_user_timezone($userid) function DB_insert_comment($comment,$playid,$userid) { mysql_query("INSERT INTO Comment VALUES (NULL,NULL,NULL,$userid,$playid, ".DB_quote_smart($comment).")"); + return; } @@ -694,12 +731,13 @@ function DB_get_hashes_by_session($session,$user) return $r; } -function DB_get_ruleset($dullen,$schweinchen) +function DB_get_ruleset($dullen,$schweinchen,$call) { $r = array(); $result = mysql_query("SELECT id FROM Rulesets WHERE". " dullen=".DB_quote_smart($dullen)." AND ". + " call=".DB_quote_smart($call)." AND ". " schweinchen=".DB_quote_smart($schweinchen)); if($result) $r = mysql_fetch_array($result,MYSQL_NUM); @@ -711,7 +749,8 @@ function DB_get_ruleset($dullen,$schweinchen) /* create new one */ $result = mysql_query("INSERT INTO Rulesets VALUES (NULL, NULL, ". DB_quote_smart($dullen).",". - DB_quote_smart($schweinchen). + DB_quote_smart($schweinchen).",". + DB_quote_smart($call). ", NULL)"); if($result) return mysql_insert_id(); @@ -747,30 +786,25 @@ function DB_get_PREF($myid) { if($r[0]=="germancards" && (time()-strtotime( "2009-12-31 23:59:59")<0) ) /* licence only valid until then */ $PREF["cardset"]="altenburg"; - else - $PREF["cardset"]="english"; + else + $PREF["cardset"]="english"; } else $PREF["cardset"]="english"; - $result = mysql_query("SELECT value from User_Prefs". - " WHERE user_id='$myid' AND pref_key='ccemail'" ); - $r = mysql_fetch_array($result,MYSQL_NUM); - if($r) - $PREF["ccemail"]=$r[0]; - else - $PREF["ccemail"]="no"; - return; } function DB_get_unused_randomnumbers($userstr) { - $queryresult = mysql_query("SELECT randomnumbers,Game.id FROM Game ". - " LEFT JOIN Hand ON Hand.game_id=Game.id ". - " AND user_id not in (".$userstr.")". - " GROUP BY Game.id ". - " HAVING num=4"); + $queryresult = mysql_query(" SELECT randomnumbers FROM Game". + " WHERE randomnumbers NOT IN". + " (SELECT randomnumbers FROM Game". + " LEFT JOIN Hand ON Game.id=Hand.game_id". + " WHERE user_id IN (". $userstr .")". + " GROUP BY randomnumbers". + " )"); + $r = mysql_fetch_array($queryresult,MYSQL_NUM); if($r) @@ -779,4 +813,131 @@ function DB_get_unused_randomnumbers($userstr) return ""; } +function DB_get_number_of_passwords_recovery($user) +{ + $queryresult = mysql_query("SELECT COUNT(*) FROM Recovery ". + " WHERE user_id=$user ". + " AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= create_date". + " GROUP BY user_id " ); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + else + return 0; +} + +function DB_set_recovery_password($user,$newpw) +{ + mysql_query("INSERT INTO Recovery VALUES(NULL,".DB_quote_smart($user). + ",".DB_quote_smart($newpw).",NULL)"); + + return; +} + +function DB_get_card_name($card) +{ + $queryresult = mysql_query("SELECT strength,suite FROM Card WHERE id='$card'"); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]." of ".$r[1]; + else + return "Error during get_card_name ".$card; +} + +function DB_get_current_playid($gameid) +{ + $trick = DB_get_max_trickid($gameid); + + if(!$trick) return NULL; + + $queryresult = mysql_query("SELECT id FROM Play WHERE trick_id='$trick' ORDER BY create_date DESC LIMIT 1"); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + + return ""; +} + +function DB_get_call_by_hash($hash) +{ + $queryresult = mysql_query("SELECT point_call FROM Hand WHERE hash='$hash'"); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + + return NULL; +} + +function DB_get_partner_call_by_hash($hash) +{ + $partner = DB_get_partner_hash_by_hash($hash); + + if($partner) + { + $queryresult = mysql_query("SELECT point_call FROM Hand WHERE hash='$partner'"); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + } + + return NULL; +} + +function DB_get_partner_hash_by_hash($hash) +{ + $gameid = DB_get_gameid_by_hash($hash); + $party = DB_get_party_by_hash($hash); + + $queryresult = mysql_query("SELECT hash FROM Hand WHERE game_id='$gameid' AND party='$party' AND hash<>'$hash'"); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + + return NULL; +} + +function DB_format_gameid($gameid) +{ + $session = DB_get_session_by_gameid($gameid); + + /* get number of game */ + $result = mysql_query("SELECT COUNT(*),create_date FROM Game". + " WHERE session='$session' ". + " AND TIMEDIFF(create_date, (SELECT create_date FROM Game WHERE id='$gameid'))<=0 ". + " GROUP by session"); + $r = mysql_fetch_array($result,MYSQL_NUM); + + return $session.".".$r[0]; +} + +function DB_get_reminder($user,$gameid) +{ + $queryresult = mysql_query("SELECT COUNT(*) FROM Reminder ". + " WHERE user_id=$user ". + " AND game_id=$gameid ". + " AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= create_date". + " GROUP BY user_id " ); + + $r = mysql_fetch_array($queryresult,MYSQL_NUM); + if($r) + return $r[0]; + else + return 0; +} + +function DB_set_reminder($user,$gameid) +{ + mysql_query("INSERT INTO Reminder ". + " VALUES(NULL, ".DB_quote_smart($user).", ".DB_quote_smart($gameid). + ", NULL) "); + return 0; +} + + ?> \ No newline at end of file