function get_data_objects_count($batch_size = 100000) { $time_start = time_elapsed(); $concept_data_object_counts = array(); $concept_data_object_maps = array(); $concept_info_items = array(); $concept_references = array(); $image_id = DataType::image()->id; $map_id = DataType::map()->id; $text_id = DataType::text()->id; $video_id = DataType::video()->id; $sound_id = DataType::sound()->id; $flash_id = DataType::flash()->id; $youtube_id = DataType::youtube()->id; $iucn_id = DataType::iucn()->id; $data_type_label[$image_id] = 'image'; $data_type_label[$sound_id] = 'sound'; $data_type_label[$text_id] = 'text'; $data_type_label[$video_id] = 'video'; $data_type_label[$iucn_id] = 'iucn'; $data_type_label[$flash_id] = 'flash'; $data_type_label[$youtube_id] = 'youtube'; $trusted_id = Vetted::trusted()->id; $untrusted_id = Vetted::untrusted()->id; $unreviewed_id = Vetted::unknown()->id; for ($i = $this->min_taxon_concept_id; $i <= $this->max_taxon_concept_id; $i += $batch_size) { print "\n dataObjects, its infoItems, its references [2 of 14] {$i} \n"; $sql = "SELECT dotc.taxon_concept_id tc_id, do.data_type_id, doii.info_item_id, dor.ref_id, do.description, dohe.vetted_id, do.data_subtype_id\r\n FROM data_objects_taxon_concepts dotc \r\n JOIN data_objects do ON dotc.data_object_id = do.id \r\n LEFT JOIN data_objects_info_items doii ON do.id = doii.data_object_id \r\n LEFT JOIN data_objects_refs dor ON do.id = dor.data_object_id \r\n JOIN data_objects_hierarchy_entries dohe on do.id = dohe.data_object_id\r\n WHERE do.published=1 AND dohe.visibility_id=" . Visibility::visible()->id . " AND dohe.vetted_id != {$untrusted_id} "; //." AND do.data_type_id <> $image_id "; this has to be removed to count maps if (isset($GLOBALS['test_taxon_concept_ids'])) { $sql .= " and dotc.taxon_concept_id IN (" . implode(",", $GLOBALS['test_taxon_concept_ids']) . ")"; } else { $sql .= " AND dotc.taxon_concept_id BETWEEN {$i} AND " . ($i + $batch_size); } $sql .= "\r\n UNION\r\n SELECT dotc.taxon_concept_id tc_id, do.data_type_id, doii.info_item_id, dor.ref_id, do.description, udo.vetted_id, do.data_subtype_id\r\n FROM data_objects_taxon_concepts dotc \r\n JOIN data_objects do ON dotc.data_object_id = do.id \r\n LEFT JOIN data_objects_info_items doii ON do.id = doii.data_object_id \r\n LEFT JOIN data_objects_refs dor ON do.id = dor.data_object_id \r\n JOIN users_data_objects udo on do.id = udo.data_object_id\r\n WHERE do.published=1 AND udo.visibility_id=" . Visibility::visible()->id . "\r\n "; if (isset($GLOBALS['test_taxon_concept_ids'])) { $sql .= " and dotc.taxon_concept_id IN (" . implode(",", $GLOBALS['test_taxon_concept_ids']) . ")"; } else { $sql .= " AND dotc.taxon_concept_id BETWEEN {$i} AND " . ($i + $batch_size); } $outfile = $this->mysqli_slave->select_into_outfile($sql); $FILE = fopen($outfile, "r"); if (!$FILE) { print "!! ERROR: Could not read {$outfile}"; debug("!! ERROR: Could not read {$outfile}"); return; } $num_rows = 0; while (!feof($FILE)) { if ($line = fgets($FILE)) { $num_rows++; $line = trim($line); $fields = explode("\t", $line); $tc_id = trim($fields[0]); $data_type_id = trim($fields[1]); $info_item_id = trim($fields[2]); $ref_id = trim($fields[3]); $description = trim($fields[4]); $vetted_id = trim($fields[5]); $data_subtype_id = trim($fields[6]); $label = @$data_type_label[$data_type_id]; if ($data_subtype_id != $map_id) { $words_count = str_word_count(strip_tags($description), 0); @$concept_data_object_counts[$tc_id][$label]['total']++; @($concept_data_object_counts[$tc_id][$label]['total_w'] += $words_count); if ($vetted_id == $trusted_id) { @$concept_data_object_counts[$tc_id][$label]['t']++; @($concept_data_object_counts[$tc_id][$label]['t_w'] += $words_count); } elseif ($vetted_id == $untrusted_id) { @$concept_data_object_counts[$tc_id][$label]['ut']++; @($concept_data_object_counts[$tc_id][$label]['ut_w'] += $words_count); } elseif ($vetted_id == $unreviewed_id) { @$concept_data_object_counts[$tc_id][$label]['ur']++; @($concept_data_object_counts[$tc_id][$label]['ur_w'] += $words_count); } $concept_info_items[$tc_id][$info_item_id] = ''; $concept_references[$tc_id][$ref_id] = ''; } else { @$concept_data_object_maps[$tc_id][$label]['total']++; if ($vetted_id == $trusted_id) { @$concept_data_object_maps[$tc_id][$label]['t']++; } elseif ($vetted_id == $untrusted_id) { @$concept_data_object_maps[$tc_id][$label]['ut']++; } elseif ($vetted_id == $unreviewed_id) { @$concept_data_object_maps[$tc_id][$label]['ur']++; } } } } fclose($FILE); unlink($outfile); print "\n num_rows: {$num_rows}"; } self::save_to_json_file($concept_info_items, "concept_info_items"); unset($concept_info_items); self::save_to_json_file($concept_references, "concept_references"); unset($concept_references); //save map data to be accessed later self::save_to_json_file($concept_data_object_maps, "map_counts"); unset($concept_data_object_maps); //convert associative array to a regular array $data_type_order_in_file = array("text", "video", "sound", "flash", "youtube", "iucn"); foreach ($concept_data_object_counts as $taxon_concept_id => $taxon_object_counts) { $new_value = ""; foreach ($data_type_order_in_file as $data_type) { $new_value .= "\t" . @$taxon_object_counts[$data_type]['total']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['t']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ut']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ur']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['total_w']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['t_w']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ut_w']; $new_value .= "\t" . @$taxon_object_counts[$data_type]['ur_w']; } $concept_data_object_counts[$taxon_concept_id] = $new_value; } print "\n get_data_objects_count():" . (time_elapsed() - $time_start) / 60 . " minutes"; self::save_totals_to_cumulative_txt($concept_data_object_counts, "tpm_data_objects"); unset($concept_data_object_counts); }
public function count_data_objects($data_type_id = null) { //JOIN hierarchy_entries he ON (dohe.hierarchy_entry_id=he.id) $sql = "SELECT COUNT(distinct do.guid) count FROM data_objects do JOIN data_objects_hierarchy_entries dohe ON (do.id=dohe.data_object_id) WHERE do.published=1 AND dohe.visibility_id=" . $this->visible_id . " AND dohe.vetted_id!=" . $this->untrusted_id; if ($data_type_id[0] != DataType::map()->id) { if ($data_type_id) { $sql .= " AND do.data_type_id IN (" . implode(",", $data_type_id) . ") "; } if ($data_type_id[0] == DataType::image()->id) { $sql .= " AND do.data_subtype_id IS NULL"; } } else { $sql .= " AND do.data_subtype_id = " . DataType::map()->id; } $result = $this->mysqli_slave->query($sql); if ($result && ($row = $result->fetch_assoc())) { return $row['count']; } }