function getUpdate($sqlDataArray = array())
{
if (isset($sqlDataArray['info_update_id'])) {
$this->sql = 'SELECT info_update_title,
info_update_id,
info_update_content,
info_update_date
FROM info_update
WHERE info_update_id = :info_update_id';
} else {
$this->sql = 'SELECT info_update_title,
info_update_id,
info_update_date
FROM info_update';
}
$this->results = $this->db->select($this->sql, $sqlDataArray);
$this->resultsQuantity = count($this->results);
foreach ($this->results as $key => $footerData) {
$this->results[$key]['info_update_title_url'] = Helpers::filterUrl($this->results[$key]['info_update_title']);
$this->results[$key]['info_update_date'] = Dates::convertDate($this->results[$key]['info_update_date']);
}
if ($this->resultsQuantity > 0) {
return $this->results;
} else {
FlashMessages::setFlashMessage('noresult', '<b>Not Found!</b><br />Update information you are looking for does not exist.');
}
}
function searchCache($sqlDataArray = array())
{
if (isset($_COOKIE['pirna_db_cache_search'])) {
$this->rawData = explode('@', FormElements::filterString($_COOKIE['pirna_db_cache_search']));
$quantity = count($this->rawData);
for ($num = 0; $num < $quantity; ++$num) {
$this->temporaryData[$num] = explode('|', $this->rawData[$num]);
}
if ($sqlDataArray['cache_type'] === 'pirna') {
$tempNumber = 0;
for ($num = 0; $num < $quantity; ++$num) {
if ($this->temporaryData[$num][1] === 'pirna') {
$this->results[$tempNumber]['cacheDate'] = Dates::convertDate($this->temporaryData[$num][0]);
$this->results[$tempNumber]['cacheUrl'] = 'pirna/1?pirna_code=' . $this->temporaryData[$num][3];
$this->results[$tempNumber]['cacheUrl'] .= '&pirna_organism=' . $this->temporaryData[$num][5];
$this->results[$tempNumber]['cacheQuery'] = 'piRNA Code = ' . $this->temporaryData[$num][3];
$this->results[$tempNumber]['cacheQuery'] .= ' & piRNA Organism = ' . $this->temporaryData[$num][5];
++$tempNumber;
}
}
} elseif ($sqlDataArray['cache_type'] === 'alignament') {
$tempNumber = 0;
for ($num = 0; $num < $quantity; ++$num) {
if ($this->temporaryData[$num][1] === 'alignament') {
$this->results[$tempNumber]['cacheDate'] = Dates::convertDate($this->temporaryData[$num][0]);
$this->results[$tempNumber]['cacheUrl'] = 'alignament/1?alignament_chromosome=' . $this->temporaryData[$num][3];
$this->results[$tempNumber]['cacheUrl'] .= '&alignament_start=' . $this->temporaryData[$num][5];
$this->results[$tempNumber]['cacheUrl'] .= '&alignament_end=' . $this->temporaryData[$num][7];
$this->results[$tempNumber]['cacheQuery'] = 'Chromosome = ' . $this->temporaryData[$num][3];
$this->results[$tempNumber]['cacheQuery'] .= ' & Start = ' . $this->temporaryData[$num][5];
$this->results[$tempNumber]['cacheQuery'] .= ' & End = ' . $this->temporaryData[$num][7];
++$tempNumber;
}
}
} elseif ($sqlDataArray['cache_type'] === 'cluster') {
$tempNumber = 0;
for ($num = 0; $num < $quantity; ++$num) {
if ($this->temporaryData[$num][1] === 'cluster') {
$this->results[$tempNumber]['cacheDate'] = Dates::convertDate($this->temporaryData[$num][0]);
$this->results[$tempNumber]['cacheUrl'] = 'cluster/1?cluster_chromosome=' . $this->temporaryData[$num][3] . '&cluster_start=' . $this->temporaryData[$num][5] . '&cluster_end=' . $this->temporaryData[$num][7];
$this->results[$tempNumber]['cacheQuery'] = 'Chromosome = ' . $this->temporaryData[$num][3] . ' & Start = ' . $this->temporaryData[$num][5] . ' & End = ' . $this->temporaryData[$num][7];
++$tempNumber;
}
}
}
}
$this->quantityResults = count($this->results);
if ($this->quantityResults > 0) {
return $this->results;
} else {
FlashMessages::setFlashMessage('noresult', '<b>Zero Results!</b><br />None of your cache search results was found.');
}
}
function getBrowseCluster($sqlDataArray = array())
{
$this->sqlAllResults = 'SELECT cluster_code
FROM cluster';
$this->allResults = $this->db->select($this->sqlAllResults, $sqlDataArray);
$this->results['quantityAllCluster'] = count($this->allResults);
$this->sql = 'SELECT cluster_code,
cluster_chromosome,
cluster_start,
cluster_end
FROM cluster
LIMIT :initialItem,
:maximumQuantity';
$this->results['clusterList'] = $this->db->select($this->sql, $sqlDataArray);
$this->results['totalPages'] = ceil($this->results['quantityAllCluster'] / $sqlDataArray['maximumQuantity']);
if ($this->results['quantityAllCluster'] > 0) {
return $this->results;
} else {
FlashMessages::setFlashMessage('noresult', '<b>Zero Results!</b><br />None cluster was found in database.');
}
}
public function checkFormToken($formToken = null, $redirectTo = 'index', $redirectTime = 5)
{
if ($formToken != Session::getSession('formToken')) {
Session::unsetSession('formToken');
FlashMessages::setFlashMessage('warning', '<b>Form Authentication Error!</b><br />You Will be Redirected in 5 Seconds.');
FlashMessages::redirectFlashMessage($redirectTo, $redirectTime);
}
}
function getInformation($sqlDataArray = array(), $type = null)
{
if ($type === 'pirna') {
$this->sql = 'SELECT pirna_code,
pirna_access,
pirna_organism,
pirna_sequence,
pirna_feedback_agree,
pirna_feedback_disagree
FROM pirna
WHERE pirna_code = :pirna_code';
$this->results['pirnaData'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->resultsQuantity = count($this->results);
$this->sql = 'SELECT COUNT(rel_alignament_pirna.id) AS qttAlignaments
FROM rel_alignament_pirna
INNER JOIN rel_alignament_build
ON rel_alignament_pirna.pirna_code = :pirna_code
AND rel_alignament_pirna.alignament_id = rel_alignament_build.alignament_id
AND rel_alignament_build.build_code = "hg38"';
$this->results['qttAlignamentsFirst'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->sql = 'SELECT COUNT(rel_alignament_pirna.id) AS qttAlignaments
FROM rel_alignament_pirna
INNER JOIN rel_alignament_build
ON rel_alignament_pirna.pirna_code = :pirna_code
AND rel_alignament_pirna.alignament_id = rel_alignament_build.alignament_id
AND rel_alignament_build.build_code = "hg19"';
$this->results['qttAlignamentsSecond'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->sql = 'SELECT COUNT(DISTINCT target.target_id) AS qttTargets
FROM target
INNER JOIN rel_alignament_pirna
ON rel_alignament_pirna.pirna_code = :pirna_code
INNER JOIN alignament
ON rel_alignament_pirna.alignament_id = alignament.alignament_id
AND alignament.alignament_chromosome = target.target_chromosome
AND alignament.alignament_start >= target.target_start
AND alignament.alignament_end <= target.target_end
AND alignament.alignament_strand = target.target_strand
';
$this->results['qttTargets'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->sql = 'SELECT alignament.alignament_chromosome
FROM alignament
INNER JOIN rel_alignament_pirna
ON rel_alignament_pirna.pirna_code = :pirna_code
INNER JOIN rel_alignament_build
ON rel_alignament_build.alignament_id = rel_alignament_pirna.alignament_id
AND rel_alignament_build.alignament_id = alignament.alignament_id
AND rel_alignament_build.build_code = "hg38"
GROUP BY alignament.alignament_chromosome
';
$this->results['qttChromosomesFirst'] = $this->db->select($this->sql, $sqlDataArray);
$this->sql = 'SELECT alignament.alignament_chromosome
FROM alignament
INNER JOIN rel_alignament_pirna
ON rel_alignament_pirna.pirna_code = :pirna_code
INNER JOIN rel_alignament_build
ON rel_alignament_build.alignament_id = rel_alignament_pirna.alignament_id
AND rel_alignament_build.alignament_id = alignament.alignament_id
AND rel_alignament_build.build_code = "hg19"
GROUP BY alignament.alignament_chromosome
';
$this->results['qttChromosomesSecond'] = $this->db->select($this->sql, $sqlDataArray);
$this->sql = 'SELECT COUNT(alignament.alignament_strand) AS qttAlignaments
FROM alignament
INNER JOIN rel_alignament_pirna
ON rel_alignament_pirna.pirna_code = :pirna_code
AND rel_alignament_pirna.alignament_id = alignament.alignament_id
AND alignament.alignament_strand = "p"
';
$this->results['qttAlignamentsSense'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->sql = 'SELECT COUNT(alignament.alignament_strand) AS qttAlignaments
FROM alignament
INNER JOIN rel_alignament_pirna
ON rel_alignament_pirna.pirna_code = :pirna_code
AND rel_alignament_pirna.alignament_id = alignament.alignament_id
AND alignament.alignament_strand = "m"
';
$this->results['qttAlignamentsAntisense'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->sql = 'SELECT COUNT(DISTINCT rel_alignament_cluster.cluster_code) AS qttCluster
FROM rel_alignament_cluster
INNER JOIN rel_alignament_pirna
ON rel_alignament_pirna.pirna_code = :pirna_code
AND rel_alignament_pirna.alignament_id = rel_alignament_cluster.alignament_id
';
$this->results['qttCluster'] = $this->db->select($this->sql, $sqlDataArray, false);
$this->sql = 'SELECT COUNT(DISTINCT comment_id) AS qttComments
FROM rel_comment_pirna
WHERE pirna_code = :pirna_code';
$this->results['qttComments'] = $this->db->select($this->sql, $sqlDataArray, false);
if ($this->resultsQuantity > 0) {
return $this->results;
} else {
FlashMessages::setFlashMessage('noresult', '<b>Not Found!</b><br />piRNA you are looking for does not exist.');
}
} elseif ($type === 'dataset') {
$this->sql = 'SELECT dataset_id,
dataset_sample,
dataset_serie,
dataset_organism,
dataset_development_stage,
dataset_method,
dataset_tissue
FROM dataset
WHERE dataset_id = :dataset_id';
$this->results = $this->db->select($this->sql, $sqlDataArray);
$this->resultsQuantity = count($this->results);
$this->results['geoInformation']['noResult'] = true;
$this->results['isGeo'] = false;
if (substr($this->results[0]['dataset_sample'], 0, 3) === 'GSM') {
$this->results['isGeo'] = true;
$url = 'http://www.ncbi.nlm.nih.gov/geo/query/acc.cgi?acc=' . $this->results[0]['dataset_sample'] . '&targ=self&form=xml&view=quick';
$geoHeaders = get_headers($url);
$geoHeadersStatus = substr($geoHeaders[0], 9, 3);
if ($geoHeadersStatus === '200') {
$geoInformation = file_get_contents($url);
preg_match('/<Person>([a-zA-Z<>\\/0-9 ]{1,})<\\/Person>/U', $geoInformation, $tempAuthorName);
if (!empty($tempAuthorName[0])) {
$tempAuthorName[0] = preg_replace('/(<\\/Person>)/U', '', $tempAuthorName[0]);
$tempAuthorName[0] = preg_replace('/(<[a-zA-Z]{1,}>)/U', '', $tempAuthorName[0]);
$tempAuthorName[0] = preg_replace('/(<\\/[a-zA-Z]{1,}>)/U', '; ', $tempAuthorName[0]);
$tempAuthorName[0] = rtrim($tempAuthorName[0], '; ');
}
preg_match('/<Laboratory>(.*)<\\/Laboratory>/U', $geoInformation, $tempLaboratoryName);
preg_match('/<Department>(.*)<\\/Department>/U', $geoInformation, $tempDepartamentName);
preg_match('/<Organization>(.*)<\\/Organization>/U', $geoInformation, $tempOrganizationName);
preg_match('/<Platform iid="([a-zA-Z0-9]{1,})">/U', $geoInformation, $tempPlatformId);
preg_match('/<Title>(.*)<\\/Title>/U', $geoInformation, $tempTitleDataset);
preg_match('/<Type>(.*)<\\/Type>/U', $geoInformation, $tempTypeDataset);
preg_match('/<Treatment-Protocol>(.*)<\\/Treatment-Protocol>/s', $geoInformation, $tempTreatmentProtocol);
preg_match('/<Growth-Protocol>(.*)<\\/Growth-Protocol>/s', $geoInformation, $tempGrowthProtocol);
preg_match('/<Molecule>(.*)<\\/Molecule>/U', $geoInformation, $tempMolecule);
preg_match('/<Extract-Protocol>(.*)<\\/Extract-Protocol>/s', $geoInformation, $tempExtractProtocol);
preg_match('/<Label-Protocol>(.*)<\\/Label-Protocol>/s', $geoInformation, $tempLabelProtocol);
preg_match('/<Hybridization-Protocol>(.*)<\\/Hybridization-Protocol>/s', $geoInformation, $tempHybridizationProtocol);
preg_match('/<Scan-Protocol>(.*)<\\/Scan-Protocol>/s', $geoInformation, $tempScanProtocol);
preg_match('/<Description>(.*)<\\/Description>/Us', $geoInformation, $tempDescription);
preg_match('/<Data-Processing>(.*)<\\/Data-Processing>/s', $geoInformation, $tempDataProcessing);
$this->results['geoInformation']['author'] = isset($tempAuthorName[0]) ? $tempAuthorName[0] : '';
$this->results['geoInformation']['laboratory'] = isset($tempLaboratoryName[1]) ? $tempLaboratoryName[1] : '';
$this->results['geoInformation']['departament'] = isset($tempDepartamentName[1]) ? $tempDepartamentName[1] : '';
$this->results['geoInformation']['organization'] = isset($tempOrganizationName[1]) ? $tempOrganizationName[1] : '';
$this->results['geoInformation']['platform'] = isset($tempPlatformId[1]) ? $tempPlatformId[1] : '';
$this->results['geoInformation']['title'] = isset($tempTitleDataset[1]) ? $tempTitleDataset[1] : '';
$this->results['geoInformation']['type'] = isset($tempTypeDataset[1]) ? $tempTypeDataset[1] : '';
$this->results['geoInformation']['treatmentProtocol'] = isset($tempTreatmentProtocol[1]) ? $tempTreatmentProtocol[1] : '';
$this->results['geoInformation']['growthProtocol'] = isset($tempGrowthProtocol[1]) ? $tempGrowthProtocol[1] : '';
$this->results['geoInformation']['molecule'] = isset($tempMolecule[1]) ? $tempMolecule[1] : '';
$this->results['geoInformation']['extractProtocol'] = isset($tempExtractProtocol[1]) ? $tempExtractProtocol[1] : '';
$this->results['geoInformation']['labelProtocol'] = isset($tempLabelProtocol[1]) ? $tempLabelProtocol[1] : '';
$this->results['geoInformation']['hybridizationProtocol'] = isset($tempHybridizationProtocol[1]) ? $tempHybridizationProtocol[1] : '';
$this->results['geoInformation']['scanProtocol'] = isset($tempScanProtocol[1]) ? $tempScanProtocol[1] : '';
$this->results['geoInformation']['description'] = isset($tempDescription[1]) ? $tempDescription[1] : '';
$this->results['geoInformation']['dataProcessing'] = isset($tempDataProcessing[1]) ? $tempDataProcessing[1] : '';
$this->results['geoInformation']['noResult'] = false;
}
}
if ($this->resultsQuantity > 0) {
return $this->results;
} else {
FlashMessages::setFlashMessage('noresult', '<b>Not Found!</b><br />Dataset you are looking for does not exist.');
}
} elseif ($type === 'cluster') {
$this->sql = 'SELECT cluster_code,
cluster_chromosome,
cluster_start,
cluster_end
FROM cluster
WHERE cluster_code = :cluster_code';
$this->results = $this->db->select($this->sql, $sqlDataArray);
$this->resultsQuantity = count($this->results);
if ($this->resultsQuantity > 0) {
return $this->results[0];
} else {
FlashMessages::setFlashMessage('noresult', '<b>Not Found!</b><br />Cluster you are looking for does not exist.');
}
} elseif ($type === 'alignament') {
$this->sql = 'SELECT alignament_id,
alignament_chromosome,
alignament_start,
alignament_end,
alignament_strand
FROM alignament
WHERE alignament_id = :alignament_id';
$this->results = $this->db->select($this->sql, $sqlDataArray);
$this->results[0]['alignament_strand'] = Helpers::changeStrandIcon($this->results[0]['alignament_strand']);
$this->resultsQuantity = count($this->results);
if ($this->resultsQuantity > 0) {
return $this->results[0];
} else {
FlashMessages::setFlashMessage('noresult', '<b>Not Found!</b><br />The alignament you are looking for does not exist.');
}
}
}
function searchCluster($sqlDataArray = array())
{
if (isset($_POST['search'])) {
$this->dataArray = array('cluster_chromosome' => FormElements::getPostInteger('inputSearchChromosome', true), 'cluster_start' => FormElements::getPostInteger('inputSearchStart', true), 'cluster_end' => FormElements::getPostInteger('inputSearchEnd', true));
$this->formToken = FormElements::getPostString('token', true);
parent::checkFormRequired($this->dataArray);
parent::checkFormToken($this->formToken, 'search/cluster');
} elseif (isset($_GET['cluster_chromosome']) && isset($_GET['cluster_start']) && isset($_GET['cluster_end'])) {
$this->dataArray = array('cluster_chromosome' => FormElements::filterInteger($_GET['cluster_chromosome'], true), 'cluster_start' => FormElements::filterInteger($_GET['cluster_start'], true), 'cluster_end' => FormElements::filterInteger($_GET['cluster_end'], true));
}
if (!in_array('errorRequiredField', $this->dataArray) && (isset($_GET['cluster_chromosome']) || $this->formToken === Session::getSession('formToken')) && !FlashMessages::hasFlashMessage()) {
$cookieData = date('Y-m-d') . '|cluster|' . 'cluster_chromosome|' . $this->dataArray['cluster_chromosome'] . '|cluster_start|' . $this->dataArray['cluster_start'] . '|cluster_end|' . $this->dataArray['cluster_end'];
if (isset($_COOKIE['pirna_db_cache_search'])) {
$newData = $_COOKIE['pirna_db_cache_search'] . '@' . $cookieData;
} else {
$newData = $cookieData;
}
setcookie('pirna_db_cache_search', $newData, time() + 86400 * 30 * 30, '/');
Session::unsetSession('formToken');
$this->sqlAllResults = 'SELECT cluster_code,
cluster_chromosome,
cluster_start,
cluster_end
FROM cluster
WHERE cluster_chromosome = ' . $this->dataArray['cluster_chromosome'] . ' AND
cluster_start >= ' . $this->dataArray['cluster_start'] . ' AND
cluster_end <= ' . $this->dataArray['cluster_end'];
$this->allResults = $this->db->select($this->sqlAllResults);
$this->results[0] = count($this->allResults);
$this->results[1] = ceil($this->results[0] / $sqlDataArray['maximumQuantity']);
$this->sql = 'SELECT cluster_code,
cluster_chromosome,
cluster_start,
cluster_end
FROM cluster
WHERE cluster_chromosome = ' . $this->dataArray['cluster_chromosome'] . ' AND
cluster_start >= ' . $this->dataArray['cluster_start'] . ' AND
cluster_end <= ' . $this->dataArray['cluster_end'] . '
LIMIT :initialItem, :maximumQuantity';
$this->results[2] = $this->db->select($this->sql, $sqlDataArray);
$this->results[3] = '?cluster_chromosome=' . $this->dataArray['cluster_chromosome'] . '&' . 'cluster_start=' . $this->dataArray['cluster_start'] . '&' . 'cluster_end=' . $this->dataArray['cluster_end'];
if ($this->results[0] > 0) {
return $this->results;
} else {
FlashMessages::setFlashMessage('noresult', '<b>Not Found!</b><br />None cluster was found using your query..');
}
}
}
function sendCreateInfo($type = null)
{
if ($type === 'faq') {
$this->table = 'faq';
$this->dataArray = array('faq_title' => FormElements::getPostString('createFaqTitle', true), 'faq_content' => FormElements::getPostString('createFaqContent', true), 'faq_type' => FormElements::getPostString('createFaqType', true));
} elseif ($type === 'article') {
$this->table = 'system_article';
$this->dataArray = array('article_title' => FormElements::getPostString('createArticleTitle', true), 'article_author' => FormElements::getPostString('createArticleAuthor', true), 'article_journal' => FormElements::getPostString('createArticleJournal', true), 'article_date' => FormElements::getPostString('createArticleDate', true), 'article_reference' => FormElements::getPostString('createArticleReference', true));
} elseif ($type === 'informations') {
$this->table = 'general_text';
$this->dataArray = array('text_content' => FormElements::getPostString('createInformationsContent', true), 'text_type' => FormElements::getPostString('createInformationsType', true));
} elseif ($type === 'update') {
$this->table = 'info_update';
$this->dataArray = array('info_update_title' => FormElements::getPostString('createUpdateTitle', true), 'info_update_content' => FormElements::getPostString('createUpdateContent', true));
} else {
FlashMessages::setFlashMessage('error', '<b>Error!</b><br />This Section Does not Exists.');
}
$this->formToken = FormElements::getPostString('token', true);
parent::checkFormRequired($this->dataArray);
parent::checkFormToken($this->formToken, 'administration/createinfo/' . $type);
if (!FlashMessages::hasFlashMessage()) {
Session::unsetSession('formToken');
$this->resultsDelete = $this->db->insert($this->table, $this->dataArray);
if (count($this->resultsDelete) === 1) {
FlashMessages::setFlashMessage('success', '<b>Success!</b><br />Succesfully Created.');
} else {
FlashMessages::setFlashMessage('error', '<b>Error!</b><br />Unsuccesfully Created.');
}
}
}
