static function createFisherExactTestTable($con, $refTable, $darnedResultTable) { DatabaseManager::deleteTable($con, $darnedResultTable); self::createFilterTable($con, $refTable, $darnedResultTable); try { $v = mysqli_query($con, "alter table " . $darnedResultTable . " add level float,add pvalue float,add fdr float"); if (!$v) { throw new Exception("Can not create Fisher Exact Test Table."); } } catch (Exception $e) { REDLog::writeErrLog($e->getMessage()); } }
static function performRepeatRegionsFilter($con, $previousTable, $currentTable, $args) { TableCreator::createFilterTable($con, $previousTable, $currentTable); REDLog::writeInfoLog("Start performing Repeat Regions Filter"); $repeatTable = "repeat_masker"; try { $sqlClause1 = "insert into " . $currentTable . " select * from " . $previousTable . " where not exists (select * from " . $repeatTable . " where (" . $repeatTable . ".chrom= " . $previousTable . ".chrom and " . $repeatTable . ".begin<=" . $previousTable . ".pos and " . $repeatTable . ".end>=" . $previousTable . ".pos)) "; $a = mysqli_query($con, $sqlClause1); if (!$a) { throw new Exception("Error execute sqlA clause in performRepeatRegionsFilter"); } REDLog::writeInfoLog("Start finding sites in Alu Regions"); $tempTable = REDTools::getRandomString(10); $sqlClause2 = "create temporary table {$tempTable} like {$currentTable}"; $b = mysqli_query($con, $sqlClause2); if (!$b) { throw new Exception("Error execute sqlB clause in performRepeatRegionsFilter"); } $sqlClause3 = "insert into {$tempTable} select * from {$previousTable} where exists (select chrom from {$repeatTable}\nwhere {$repeatTable}.chrom = {$previousTable}.chrom and {$repeatTable}.begin<={$previousTable}.pos and\n{$repeatTable}.end>={$previousTable}.pos and {$repeatTable}.type='SINE/Alu')"; $c = mysqli_query($con, $sqlClause3); if (!$c) { throw new Exception("Error execute sqlC clause in performRepeatRegionsFilter"); } $sqlClause4 = "update {$tempTable} set alu='T'"; $d = mysqli_query($con, $sqlClause4); if (!$d) { throw new Exception("Error execute sqlD clause in performRepeatRegionsFilter"); } $sqlClause5 = "insert into {$currentTable} select * from {$tempTable}"; $e = mysqli_query($con, $sqlClause5); if (!$e) { throw new Exception("Error execute sqlE clause in performRepeatRegionsFilter"); } DatabaseManager::deleteTable($con, $tempTable); REDLog::writeInfoLog("End finding sites in Alu Regions"); } catch (Exception $e) { REDLog::writeErrLog($e->getMessage()); } REDLog::writeInfoLog("End performing Repeat Regions Filter"); }
static function parseMultiRNAVCFFile($con, $vcfPath, $userid) { $altColumn = 4; $infoColumn = 7; $formatColumnIndex = 8; $columnLength = 0; REDLog::writeInfoLog("Start parsing RNA VCF file"); try { $fp = fopen($vcfPath, 'r'); DatabaseManager::setAutoCommit($con, false); $lineCount = 0; $hasEstablishTable = false; while (($line1 = fgets($fp)) != null) { $line = trim($line1); if (strpos($line, "##") === 0) { continue; } if (strpos($line, "#") === 0) { $columnStrings = explode("\t", substr($line, 1)); $columnLength = count($columnStrings); $sampleNamesLength = $columnLength - $formatColumnIndex - 1; for ($j = 0; $j < $sampleNamesLength; $j++) { $sampleNames[$j] = $columnStrings[$formatColumnIndex + 1 + $j]; } $tableBuilders = "{$columnStrings['0']} varchar(30),{$columnStrings['1']} int,{$columnStrings['2']} varchar(30),\n\t\t\t\t {$columnStrings['3']} varchar(5),{$columnStrings['4']} varchar(5),{$columnStrings['5']} float(10,2),\n\t\t\t\t {$columnStrings['6']} text,{$columnStrings['7']} text,"; continue; } if ($sampleNames == null) { throw new Exception("There are no samples in this vcf file"); } $sections = explode("\t", $line); for ($i = $formatColumnIndex + 1; $i < $columnLength; $i++) { if (!strpos($sections[$i], ".")) { $contain = false; $rr = 1; } else { $contain = true; $rr = 2; } if (strcmp($sections[$altColumn], ".") == 0 || $contain) { continue; } $formatColumns = explode(":", $sections[$formatColumnIndex]); $formatLength = count($formatColumns); $dataColumns = explode(":", str_replace(",", "/", $sections[$i])); $dataColumnLength = count($dataColumns); if ($formatLength != $dataColumnLength) { continue; } if (!$hasEstablishTable) { for ($j = 0; $j < $formatLength; $j++) { $formatColumn = $formatColumns[$j]; $tableBuilders = $tableBuilders . $formatColumn . " text,"; } $tableBuilders = $tableBuilders . "alu varchar(1) default 'F',index(chrom,pos)"; for ($j = 0, $len = count($sampleNames); $j < $len; $j++) { $tableName[$j] = $userid . "_" . $sampleNames[$j] . "_rnavcf" . "_" . date("Ymdhisa"); DatabaseManager::deleteTable($con, $tableName[$j]); $createClause = "create table " . $tableName[$j] . "({$tableBuilders})"; $v = mysqli_query($con, $createClause); if (!$v) { throw new Exception("Error create RNATable."); } } DatabaseManager::commit($con); $hasEstablishTable = true; } $sqlClause = "insert into " . $tableName[$i - $formatColumnIndex - 1] . "("; for ($j = 0; $j < $formatColumnIndex; $j++) { $sqlClause = $sqlClause . $columnStrings[$j] . ","; } for ($j = 0; $j < $formatLength; $j++) { $formatColumn = $formatColumns[$j]; $sqlClause = $sqlClause . $formatColumn . ","; } $sqlClause = substr($sqlClause, 0, strlen($sqlClause) - 1); $sqlClause = $sqlClause . ") values('"; if (strpos($sections[0], "ch") === 0 && !(strpos($sections[0], "chr") === 0)) { $str = str_replace("ch", "chr", $sections[0]) . "'"; $sqlClause = $sqlClause . $str; } else { if (strlen($sections[0]) < 3) { $sqlClause = $sqlClause . "chr" . $sections[0] . "'"; } else { $sqlClause = $sqlClause . $sections[0] . "'"; } } for ($j = 1; $j < $formatColumnIndex; $j++) { $sqlClause = $sqlClause . ",'" . $sections[$j] . "'"; } for ($j = 0; $j < count($dataColumns); $j++) { $dataColumn = $dataColumns[$j]; $sqlClause = $sqlClause . ",'" . $dataColumn . "'"; } $sqlClause = $sqlClause . ")"; $v = mysqli_query($con, $sqlClause); if (!$v) { throw new Exception("Error execute sql clause: {$sqlClause}"); } if (++$lineCount % 10000 == 0) { DatabaseManager::commit($con); } } } DatabaseManager::commit($con); DatabaseManager::setAutoCommit($con, true); } catch (Exception $e) { REDLog::writeInfoLog($e->getMessage()); } REDLog::writeInfoLog("End parsing RNA VCF file..."); return $tableName; }