RaffleDAO.php
<?php
/**
* gplusraffle - Google API PHP OAuth 2.0 and FusionTables client based raffle
* management system
*
* @package gplusraffle
* @copyright Gael Abadin 2014
* @license MIT Expat
* @version v0.1.4-beta
*
* Raffle Data Access Object class
*/
use synapp\info\tools\uuid\uuid;
class RaffleDAO {
private $tableIds;
private $fusionTablesService;
private $debug = false;
private function escape_mysql_string($string){
$search = array("\\", "\x00", "\n", "\r", "'", '"', "\x1a");
$replace = array("\\\\","\\0","\\n", "\\r", "\\'", '\"', "\\Z");
return str_replace($search, $replace, $string);
}
/**
* @param \Google_Service_Fusiontables|null $fusionTablesService
*/
public function setFusionTablesService($fusionTablesService){
$this->fusionTablesService = $fusionTablesService;
}
/**
* @param mixed $tableIds
*/
public function setTableIds($tableIds){
$this->tableIds = $tableIds;
}
/**
* Class constructor.
*
* If a Google_Service_Fusiontables is not specified an authenticated
* Google_Client $client with fusion tables scope must be provided.
*
* @param $tableIds
* @param null $fusionTablesService
* @param null $adminClient
* @param bool $debug
*/
public function __construct(
$tableIds,
$fusionTablesService = null,
$adminClient = null,
$debug = false
){
$this->tableIds = $tableIds;
$this->debug = $debug;
$this->fusionTablesService =
($fusionTablesService === null)?
(new Google_Service_Fusiontables($adminClient)):
$fusionTablesService;
}
/**
* Horrible method to select rows from raffles table matching their columns
* to combinations of certain criteria
*
* Column matching criteria: is equal to ('=')
* Column combinators: 'AND'
*
* @param null|string $raffleId
* @param null|string $description
* @param null|string $creatorId
* @param null|string $participantId
* @param null|string $winnerId
* @param null|string $privacy
* @param null|string $status
* @throws Exception
* @return stdClass a simple object containing numerically indexed column
* names array in 'columns' property and numerically indexed rows array
* of numerically indexed column values in 'rows' property
*/
public function getRaffles(
$raffleId = null,
$description = null,
$creatorId = null,
$participantId = null,
$winnerId = null,
$privacy = null,
$status = null
){
$tableId = $this->tableIds['raffles'];
$fusionTablesService = $this->fusionTablesService;
$sql = "SELECT * FROM {$tableId}";
$raffleIdOperator = '=';
$preOperator = '';
$where = " WHERE ";
// I hate Fusion Tables with passion right now... No JOINS on plain SELECT queries? REALLY?
if (isset ($participantId)){
$simpleResultObject = $this->getFilteredDataFromTable(
'participants',
null,
$participantId
);
}
if (isset ($winnerId)){
$simpleResultObject = $this->getFilteredDataFromTable(
'winners',
null,
$winnerId
);
}
if (isset($simpleResultObject)){
if (!isset($simpleResultObject->rows)
|| !is_array($simpleResultObject->rows)
|| count($simpleResultObject->rows) <= 0
){
throw new Exception ('Raffle not found.', 404);
}
$raffleId = '';
$columnIndex = (array_search('raffleid',$simpleResultObject->columns));
foreach ($simpleResultObject->rows as $row){
$raffleId .= "'{$row[$columnIndex]}', ";
}
$raffleId = '('.rtrim($raffleId,", ").')';
$raffleIdOperator = 'IN';
} else if (isset ($raffleId)){
$raffleId = "'".$this->escape_mysql_string($raffleId)."'";
}
// Now you do too, I hope.
if (isset ($raffleId)) {
$where .= "raffleid {$raffleIdOperator} {$raffleId}";
$preOperator = " AND ";
}
if (isset ($description)) {
$description = $this->escape_mysql_string($description);
$where .= $preOperator
. "raffledescription = '{$description}'";
$preOperator = " AND ";
}
if (isset ($creatorId)) {
$creatorId = $this->escape_mysql_string($creatorId);
$where .= $preOperator
. "creatorid = '{$creatorId}'";
$preOperator = " AND ";
}
if (isset ($privacy)) {
$privacy = $this->escape_mysql_string($privacy);
$where .= $preOperator. "privacy = '{$privacy}'";
$preOperator = " AND ";
}
if (isset ($status)) {
$status = $this->escape_mysql_string($status);
$where .= $preOperator. "status = '{$status}'";
}
$result = $fusionTablesService->query->sql($sql.$where);
return $result->toSimpleObject();
}
/**
* Horrible method to select rows from $tableIdOrName whose columns
* match combinations of certain criteria
*
* Column matching criteria: is equal to ('=')
* Column combinators: 'AND'
*
* @param string $tableIdOrName
* @param null|string $raffleId
* @param null|string $id
* @return stdClass
*/
private function getFilteredDataFromTable(
$tableIdOrName,
$raffleId = null,
$id = null
){
$idFieldName = '';
//$dateFieldName = '';
if ($tableIdOrName === 'winners') {
$tableIdOrName = $this->tableIds['winners'];
$idFieldName = 'winnerid';
//$dateFieldName = 'raffled';
}
if ($tableIdOrName === 'participants') {
$tableIdOrName = $this->tableIds['participants'];
$idFieldName = 'participantid';
//$dateFieldName = 'joined';
}
$fusionTablesService = $this->fusionTablesService;
// escaping does nothing or makes injection fail
$tableIdOrName = $this->escape_mysql_string($tableIdOrName);
$sql = "SELECT * FROM {$tableIdOrName}";
$preOperator = '';
$where = " WHERE ";
if (isset ($raffleId)) {
$raffleId = $this->escape_mysql_string($raffleId);
$where .= "raffleid = '{$raffleId}'";
$preOperator = " AND ";
}
if (isset ($id)) {
$id = $this->escape_mysql_string($id);
$where .= $preOperator
. "{$idFieldName} = '{$id}'";
//$preOperator = " AND ";
}
/*
if (isset ($date)) {
$date = $this->escape_mysql_string($date);
$where .= $preOperator. "{$dateFieldName} = '{$date}'";
}
*/
$result = $fusionTablesService->query->sql($sql.$where);
return $result->toSimpleObject();
}
/**
* @param $raffleId
* @param null $tableId
* @param null|array $userIdAndIdField array('idField'=>$idFieldName,'userId'=>$userId)
* @param null $fusionTablesService
* @throws Exception
* @return stdClass
*/
public function getResultsForRaffleId(
$raffleId,
$tableId = null,
$userIdAndIdField = null,
$fusionTablesService = null
){
if ($tableId === null || $tableId === 'raffles') {
$tableId = $this->tableIds['raffles'];
} else if ($tableId === 'participants') {
$tableId = $this->tableIds['participants'];
} else if ($tableId === 'winners') {
$tableId = $this->tableIds['winners'];
}
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
// escaping does nothing or makes injection fail
$tableId = $this->escape_mysql_string($tableId);
$raffleId = $this->escape_mysql_string($raffleId);
$sql = "SELECT * FROM {$tableId} WHERE raffleid = '{$raffleId}'"
.(
isset($userIdAndIdField)?
" AND {$userIdAndIdField['idField']} = '{$userIdAndIdField['userId']}'"
:""
);
$result = $fusionTablesService->query->sql($sql);
if (
$tableId !== $this->tableIds['raffles'] &&
isset($result->rows) &&
is_array($result->rows) &&
count ($result->rows) <= 0
){
$result = $fusionTablesService->query->sql(
"SELECT * FROM {$this->tableIds['raffles']} WHERE raffleid = '{$raffleId}'"
);
if (
isset($result->rows) &&
is_array($result->rows) &&
count ($result->rows) <= 0
){
throw new Exception(
"Raffle {$raffleId} does not exist.",
404
);
}
}
return $result->toSimpleObject();
}
/**
* @param $description
* @param $userId
* @param null $created
* @param string $privacy
* @param string $status
* @param null $raffleId
* @param null $tableId
* @param null $fusionTablesService
* @throws Exception
* @return stdClass
*/
public function addRaffle(
$description,
$userId,
$created = null,
$privacy = null,
$status = null,
$raffleId = null,
$tableId = null,
$fusionTablesService = null
){
if ($privacy === null) { $privacy = 'public'; } else {
$privacy = $this->escape_mysql_string($privacy);
}
if ($status === null) { $status = 'closed'; } else {
$status = $this->escape_mysql_string($status);
}
if ($created === null) {
$created = date("Y-m-d H:i:s");
} else {
$created = $this->escape_mysql_string($created);
}
if ($raffleId === null) {
$raffleId = uuid::v5(
uuid::v4(), 'synapp\\info\\tools\\gplusraffle'
);
} else {
$raffleId = $this->escape_mysql_string($raffleId);
}
if ($tableId === null || $tableId === 'raffles') {
$tableId = $this->escape_mysql_string(
$this->tableIds['raffles']
);
} else {
$tableId = $this->escape_mysql_string($tableId);
}
$userId = $this->escape_mysql_string($userId);
$description = $this->escape_mysql_string($description);
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
// create new raffle with creatorid = $userId
$sql = "INSERT INTO {$tableId} "
."('raffleid','raffledescription','creatorid','created','privacy','status') "
."VALUES ('{$raffleId}', '{$description}', '{$userId}', '{$created}', '{$privacy}', '{$status}')";
$result = $fusionTablesService->query->sql($sql);
if (
isset($result->columns)&&
is_array($result->columns)&&
$result->columns[0]==='rowid'&&
isset($result->rows)&&
is_array($result->rows)&&
count($result->rows) === 1
){
$resultObject = new stdClass();
$resultObject->columns = array(
'raffleid',
'raffledescription',
'creatorid',
'created',
'privacy',
'status'
);
$resultObject->rows = array(
array(
$raffleId,
$description,
$userId,
$created,
$privacy,
$status
)
);
return $resultObject;
} else {
throw new Exception(
"Couldn't create raffle with id {$raffleId}",
500
);
}
}
/**
* @param $raffleId
* @param null $tableIds
* @param null $fusionTablesService
* @return bool
*/
public function deleteRaffle(
$raffleId,
$tableIds = null,
$fusionTablesService = null
){
// only admin or creator should do this, but check is not performed here.
// deletes raffle $raffleid from raffles, winners, participants
if ($tableIds === null) { $tableIds = $this->tableIds; }
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
// escaping does nothing or makes injection fail
$rafflesTableId = $this->escape_mysql_string(
$tableIds['raffles']
);
$participantsTableId = $this->escape_mysql_string(
$tableIds['participants']
);
$winnersTableId = $this->escape_mysql_string(
$tableIds['winners']
);
$raffleId = $this->escape_mysql_string($raffleId);
$sql = "SELECT ROWID FROM {$rafflesTableId} WHERE raffleid = '{$raffleId}'";
$result = $fusionTablesService->query->sql($sql);
if (isset($result->rows)&&is_array($result->rows)&&count($result->rows)>0){
$sql = "DELETE FROM {$rafflesTableId} WHERE ROWID = '{$result->rows[0][0]}'";
$fusionTablesService->query->sql($sql);
}
$sql = "SELECT ROWID FROM {$participantsTableId} WHERE raffleid = '{$raffleId}'";
$result = $fusionTablesService->query->sql($sql);
if (isset($result->rows)&&is_array($result->rows)&&count($result->rows)>0){
$sql = "DELETE FROM {$participantsTableId} WHERE ROWID = '{$result->rows[0][0]}'";
$fusionTablesService->query->sql($sql);
}
$sql = "SELECT ROWID FROM {$winnersTableId} WHERE raffleid = '{$raffleId}'";
$result = $fusionTablesService->query->sql($sql);
if (isset($result->rows)&&is_array($result->rows)&&count($result->rows)>0){
$sql = "DELETE FROM {$winnersTableId} WHERE ROWID = '{$result->rows[0][0]}'";
$fusionTablesService->query->sql($sql);
}
return true;
}
/**
* Adds participant $userId to $raffleId participants table
*
* @param string $userId
* @param string $raffleId
* @param $comment
* @param null|string $joined
* @param null|string $tableId
* @param null|Google_Service_Fusiontables $fusionTablesService
* @return stdClass
*/
public function addParticipant(
$userId,
$raffleId,
$comment = null,
$joined = null,
$tableId = null,
$fusionTablesService = null
){
// should not add to closed or raffled, and should avoid
// ($userId,$raffleId) duplicates, but checks are not performed here.
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
if ($comment === null) { $comment = ''; }
if ($tableId === null || $tableId === 'participants') { $tableId = $this->tableIds['participants']; }
if ($joined === null) { $joined = date("Y-m-d H:i:s"); }
$userId = $this->escape_mysql_string($userId);
$raffleId = $this->escape_mysql_string($raffleId);
$joined = $this->escape_mysql_string($joined);
$tableId = $this->escape_mysql_string($tableId);
// add $userId to $raffleId participants on participants table
$sql = "INSERT INTO {$tableId} "
."('raffleid','participantid','comment','joined') "
."VALUES ('{$raffleId}', '{$userId}', '{$comment}', '{$joined}')";
$result = $fusionTablesService->query->sql($sql);
return $result->toSimpleObject();
}
/**
* Removes participant $userId to $raffleId participants table
*
* The status of $raffleId should be 'open', although that's not checked here.
*
* @param string $userId
* @param string $raffleId
* @param null|string $tableId
* @param null|Google_Service_Fusiontables $fusionTablesService
* @throws Exception
* @return stdClass
*/
public function deleteParticipant(
$userId,
$raffleId,
$tableId = null,
$fusionTablesService = null
){
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
if ($tableId === null || $tableId === 'participants') { $tableId = $this->tableIds['participants']; }
$userId = $this->escape_mysql_string($userId);
$raffleId = $this->escape_mysql_string($raffleId);
$tableId = $this->escape_mysql_string($tableId);
// delete $userId where raffleid = $raffleId on participants table
$sql = "SELECT ROWID FROM {$tableId} WHERE raffleid = '{$raffleId}' AND participantid = '{$userId}'";
$result = $fusionTablesService->query->sql($sql);
if (isset($result->rows)&&is_array($result->rows)&&count($result->rows)>0){
$sql = "DELETE FROM {$tableId} WHERE ROWID = '{$result->rows[0][0]}'";
$result = $fusionTablesService->query->sql($sql);
} else {
throw new Exception (
"Raffle or participant not found.",
404
);
}
return $result->toSimpleObject();
}
/**
* Adds the specified ($raffleId,$userId) to winners table
*
* $raffled (raffle datetime) can also be specified (NOW() by default)
*
* should not add to open or raffled, and should avoid ($userId,$raffleId)
* duplicates, but checks are not performed here.
* (also, it's the same as addParticipant with just a field name change,
* but will be keep as separate functions for decoupling)
*
* @param $userId
* @param $raffleId
* @param $raffled
* @param null $tableId
* @param null $fusionTablesService
* @return stdClass
*/
public function addWinner(
$userId,
$raffleId,
$raffled,
$tableId = null,
$fusionTablesService = null
){
if ($fusionTablesService===null) {$fusionTablesService = $this->fusionTablesService; }
if ($tableId === null) { $tableId = $this->tableIds['winners']; }
if ($raffled === null) { $raffled = date("Y-m-d H:i:s"); }
$userId = $this->escape_mysql_string($userId);
$raffleId = $this->escape_mysql_string($raffleId);
$raffled = $this->escape_mysql_string($raffled);
$tableId = $this->escape_mysql_string($tableId);
// add $userId to $raffleId participants table
$sql = "INSERT INTO {$tableId} "
."('raffleid','winnerid','raffled') "
."VALUES ('{$raffleId}', '{$userId}', '{$raffled}')";
$result = $fusionTablesService->query->sql($sql);
return $result->toSimpleObject();
}
/**
* Picks N random winner(s) for a raffle among its participants.
*
* If one or more winners are already set, it returns the current winner(s).
*
* (If you want to redo the raffle you must either create a new one or
* log in as admin to reset the winners for you using resetWinners method)
*
* @param $raffleId
* @param int $limit
* @param null|string $raffled must have a valid Fusion Tables DATETIME format
* @param null|string $tableId
* @param null|Google_Service_Fusiontables $fusionTablesService
* @throws Exception the raffle must be closed
* @return stdClass
*/
public function pickWinners(
$raffleId,
$limit = null,
$raffled = null,
$tableId = null,
$fusionTablesService = null
){
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
if ($limit === null) { $limit = 1; }
if ($raffled === null) { $raffled = date("Y-m-d H:i:s"); }
if ($tableId === null || $tableId === 'winners') {
$tableId = $this->tableIds['winners'];
}
$raffleId = $this->escape_mysql_string($raffleId);
$tableId = $this->escape_mysql_string($tableId);
$simpleResultObject = $this->getResultsForRaffleId(
$raffleId,
$this->tableIds['raffles'],
null,
$fusionTablesService
);
if (
!isset($simpleResultObject->rows)
|| !is_array($simpleResultObject->rows)
|| count($simpleResultObject->rows) <=0
){
throw new Exception ('Raffle not found.', 404);
}
$status = $simpleResultObject->rows[0][
array_search('status',$simpleResultObject->columns)
];
if ($status !== 'closed'){
throw new Exception('Raffle must be closed',400);
}
$simpleResultObject = $this->getResultsForRaffleId(
$raffleId,
$tableId,
null,
$fusionTablesService
);
if (
isset($simpleResultObject->rows)
&& is_array($simpleResultObject->rows)
&& count($simpleResultObject->rows) > 0
){
return $simpleResultObject;
}
$sql = "SELECT * FROM {$this->tableIds['participants']} WHERE raffleid = '{$raffleId}'";
$result = $fusionTablesService->query->sql($sql);
$rowCount = isset($result->rows)&&is_array($result->rows)?count($result->rows):0;
if ($rowCount<=0){
throw new Exception(
"No participants found.",
404
);
}
$offset = mt_rand(0,max(0,$rowCount-$limit));
$sql = "SELECT * FROM {$this->tableIds['participants']} OFFSET {$offset} LIMIT {$limit}";
$result = $fusionTablesService->query->sql($sql);
$this->updateRaffleStatus(
$raffleId,
'raffled',
$this->tableIds['raffles'],
$fusionTablesService
);
if (
isset($result->rows)
&& is_array($result->rows)
&& count($result->rows) > 0
){
$columnIndex = (array_search('participantid',$result->columns));
foreach ($result->rows as $row){
$this->addWinner(
$row[$columnIndex],
$raffleId,
$raffled,
null,
$fusionTablesService
);
}
return $this->getResultsForRaffleId(
$raffleId,
$tableId,
null,
$fusionTablesService
);
} else {
throw new Exception(
'No participants found.',
404
);
}
}
/**
* Sets the raffles table raffleid status to the specified status.
*
* A $raffleId and a $status must be specified.
*
* It should be a valid status and should be updated by admin or creator,
* but checks are not performed here.
*
* @param $raffleId
* @param $status
* @param null $tableId
* @param null $fusionTablesService
* @return stdClass
* @throws Exception
*/
public function updateRaffleStatus(
$raffleId,
$status,
$tableId = null,
$fusionTablesService = null
){
// should be a valid status and should be updated by admin or creator,
// but checks are not performed here.
if ($fusionTablesService===null) {
$fusionTablesService = $this->fusionTablesService;
}
if ($tableId === null || $tableId === 'raffles') {
$tableId = $this->tableIds['raffles'];
}
$raffleId = $this->escape_mysql_string($raffleId);
$status = $this->escape_mysql_string($status);
if (!isset($raffleId)){
throw new Exception('Must specify a raffle',400);
}
$sql = "SELECT ROWID FROM {$tableId} WHERE raffleid = '{$raffleId}'";
$result = $fusionTablesService->query->sql($sql);
if (isset($result->rows)&&is_array($result->rows)&&count($result->rows)>0){
$sql = "UPDATE {$tableId} SET status = '{$status}' WHERE ROWID = '{$result->rows[0][0]}'";
$result = $fusionTablesService->query->sql($sql);
} else {
throw new Exception(
"updateRaffleStatus: ROWID for raffleid = {$raffleId} not "
."found on table {$tableId}.",
404
);
}
return $result->toSimpleObject();
}
}