/**
* deletes a request with a requestID on a users DB.
*
* @param $userdb
* @param $requestId
* @return bool
*/
public function deleteRequest($userdb, $requestId)
{
$username = RequestHelper::readUsernameFromRequest();
//control variable
$all_query_ok = true;
//disable autocommit so commit/rollback is possible
$this->core->dbh->autocommit(false);
// select all intervention IDs with the specified RequestID as an array
if ($stmt = $this->core->dbh->query("SELECT IntervID FROM intervention WHERE Request_id = '" . $requestId . "'")) {
$row = $stmt->fetch_row();
$stmt->close();
} else {
$all_query_ok = false;
}
if (!$all_query_ok) {
LogHelper::write("select intervid failed", $username);
}
//skip deleting intervention work and material if no interventions are in the DB
//delete from intervention_material and intervention_work
if (!empty($row)) {
$oIntervention = new Intervention($userdb);
foreach ($row as $intervID) {
$all_query_ok &= $oIntervention->deleteInterventionMaterial($intervID);
$all_query_ok &= $oIntervention->deleteInterventionWork($intervID);
}
}
//delete from interventions
$this->core->dbh->query("DELETE FROM intervention WHERE Request_id = '" . $requestId . "'") ? false : ($all_query_ok = false);
if (!$all_query_ok) {
LogHelper::write("delete intervention failed", $username);
}
//delete from requests
$this->core->dbh->query("DELETE FROM request WHERE Request_id = '" . $requestId . "'") ? false : ($all_query_ok = false);
if (!$all_query_ok) {
LogHelper::write("delete request failed", $username);
}
// commit or rollback
if ($all_query_ok) {
$this->core->dbh->commit();
$this->core->dbh->close();
return TRUE;
} else {
$this->core->dbh->rollback();
$this->core->dbh->close();
return FALSE;
}
}
$body = $app->request()->getBody();
$request = json_decode($body, true);
$response = array();
$intervID = $request['IntervID'];
$action = $request['Action'];
$date_action = $request['Date_action'];
$time = $request['Time'];
//read user db and name from request
$userdb = R::readDbFromRequest();
$by_user = R::readUsernameFromRequest();
//add work
$oIntervention = new Intervention($userdb);
$workId = $oIntervention->addWork($intervID, $action, $date_action, $time, $by_user);
//read old total work
$oldTotalWork = $oIntervention->getTotalWork($intervID);
//update new total work as hours (not as minutes)
$newTotalWork = $oldTotalWork + $time / 60;
$InUpdated = $oIntervention->updateInterventionWork($intervID, $newTotalWork, $by_user);
if ($workId != NULL & $InUpdated) {
$response["error"] = false;
$response["message"] = "Intervention work created";
Log::write("Intervention work with ID " . $workId . " created successfully.", $by_user);
R::echoResponse(201, $response);
} else {
$response["error"] = true;
$response["message"] = "Could not create intervention work";
Log::write("Failed to create new intervention work.", $by_user);
R::echoResponse(500, $response);
}
});
});
$request = $app->request();
$public_key = $request->headers('X-PublicKey');
//get User array from sent public key
$user = $oUser->getUserByPublicKey($public_key);
$userdb = $oUser->setDefaultDatabase($user['LoginID']);
//get access level string of user
$access_level = $oUser->getAccessLevel($user['LoginID']);
//create new instance with the user specific database
$tempTool = new DbExport($userdb);
//read relevant table names with the user specific access level
$export = $tempTool->readRelevantTables($access_level);
$app->contentType('application/json;charset=utf-8');
echo json_encode($export);
});
/**
* GET route to export database meta information to JSON
*
*/
$app->get('/database/scheme', function () use($app) {
$userdb = R::readDbFromRequest();
$by_user = R::readUsernameFromRequest();
$tempTool = new DbExport($userdb);
//read metainformation of tables
$export = $tempTool->readRelevantTablesMetaInformation($userdb);
if (!isset($export)) {
Log::write("The user's DefaultDB does not exist on the server!", $by_user);
}
$app->contentType('application/json;charset=utf-8');
echo json_encode($export);
});
});
$response['public_key'] = $public_key;
$code = 200;
} else {
$response['public_key'] = "could not create or read keys";
$code = 500;
Log::write("Could not create or read keys from user", $user['username']);
}
//get access level of user (stored in employee table)
$access_level = $oUser->getAccessLevel($loginId);
$response['access_level'] = $access_level;
if ($access_level == NULL) {
Log::write("*** access_level of user is NULL", $user['username']);
}
} else {
// unknown error occurred
$response['error'] = true;
$response['message'] = "An error occurred. Possible duplicate username?";
$code = 500;
Log::write("Could not get user from database", $user['username']);
}
} else {
// user credentials are wrong
$response['error'] = true;
$response['message'] = 'Login failed. Incorrect credentials';
$code = 401;
Log::write("Login failed. Incorrect credentials");
}
//finally the response
R::echoResponse($code, $response);
});
});
/**
* Update asset
*
* @param String $assetId
* @param Int $genericAssetId
* @param Int $umdns
* @param String $assetFullName
* @param String $manufacturerId
* @param String $model
* @param String $serialNumber
* @param String $internaliventoryNumber
* @param String $locationId
* @param String $responsiblePers
* @param Int $assetStatusId
* @param Int $assetUtilizationId
* @param String $purchaseDate
* @param String $installationDate
* @param String $lifeTime
* @param Double $purchasePrice
* @param Double $currentValue
* @param Int $warrantyContractId
* @param String $agentId
* @param String $warrantyContractExp
* @param String $warrantyContractNotes
* @param String $employeeId
* @param String $supplierId
* @param String $donorId
* @param String $serviceManual
* @param String $notes
* @param String $picture
* @param String $by_user
* @param String $URL_Manual
* @param String $metrologyDocument
* @param String $metrologyDate
* @param String $metrology
* @param String $assetFullName
*
* @return Int Number of affected rows
*/
public function updateAsset($assetId, $genericAssetId, $umdns, $assetFullName, $manufacturerId, $model, $serialNumber, $internaliventoryNumber, $locationId, $responsiblePers, $assetStatusId, $assetUtilizationId, $purchaseDate, $installationDate, $lifeTime, $purchasePrice, $currentValue, $warrantyContractId, $agentId, $warrantyContractExp, $warrantyContractNotes, $employeeId, $supplierId, $donorId, $serviceManual, $notes, $picture, $by_user, $URL_Manual, $metrologyDocument, $metrologyDate, $metrology)
{
$lastmodified = RequestHelper::getTimestamp();
//cast the incoming parameters (all Strings) to its original data type(s)
$genericAssetId = (int) $genericAssetId;
$umdns = (int) $umdns;
$assetStatusId = (int) $assetStatusId;
$assetUtilizationId = (int) $assetUtilizationId;
$lifeTime = (int) $lifeTime;
$purchasePrice = (double) $purchasePrice;
$currentValue = (double) $currentValue;
$warrantyContractId = (int) $warrantyContractId;
$sql = "UPDATE assets SET GenericAssetID = ?, UMDNS = ?, AssetFullName = ?, ManufacturerID = ?, Model = ?, SerialNumber = ?, InternalIventoryNumber = ?, LocationID = ?, ResponsiblePers = ?, AssetStatusID = ?, AssetUtilizationID = ?, PurchaseDate = ?, InstallationDate = ?, Lifetime = ?, PurchasePrice = ?, CurrentValue = ?, WarrantyContractID = ?, AgentID = ?, WarrantyContractExp = ?, WarrantyContractNotes = ?, EmployeeID = ?, SupplierID = ?, DonorID = ?, ServiceManual = ?, Notes = ?, Picture = ?, lastmodified = ?, by_user = ?, URL_Manual = ?, MetrologyDocument = ?, MetrologyDate = ?, Metrology = ? WHERE AssetID = ?";
if ($stmt = $this->core->dbh->prepare($sql)) {
//i = int, s = String, d = double, b = blob
$stmt->bind_param("iisssssssiissiddissssssssssssssss", $genericAssetId, $umdns, $assetFullName, $manufacturerId, $model, $serialNumber, $internaliventoryNumber, $locationId, $responsiblePers, $assetStatusId, $assetUtilizationId, $purchaseDate, $installationDate, $lifeTime, $purchasePrice, $currentValue, $warrantyContractId, $agentId, $warrantyContractExp, $warrantyContractNotes, $employeeId, $supplierId, $donorId, $serviceManual, $notes, $picture, $lastmodified, $by_user, $URL_Manual, $metrologyDocument, $metrologyDate, $metrology, $assetId);
}
$stmt->execute();
$num_affected_rows = $stmt->affected_rows;
$stmt->close();
return $num_affected_rows > 0;
}
/**
* Read the user belonging to the incoming request and get his/her defaultDB.
*
* @return String $userdb
*/
public static function readDbFromRequest()
{
$app = \Slim\Slim::getInstance();
$oUser = new User();
$request = $app->request();
$public_key = $request->headers('X-PublicKey');
//get User array from sent public key
$user = $oUser->getUserByPublicKey($public_key);
$userdb = $oUser->setDefaultDatabase($user['LoginID']);
if ($userdb != NULL) {
return $userdb;
} else {
$response["error"] = true;
$response["defaultdb"] = "Could not select database of user.";
LogHelper::write("Could not select database, check if the user's DefaultDB is available.", $user['username']);
RequestHelper::echoResponse(500, $response);
$app->stop();
}
}