<?php class CDR_Table { var $source; var $destination; var $duration; var $calltime; var $unique; var $foreignkey; var $tablename; var $duration_range; // in seconds var $calltime_range; // in minutes var $db; // This basically wraps a CDR table. Despite different names, each CDR table must have // a table name, and columns for the source number (Caller ID), destination number, // the duration of the call, when the call took place. // Two other columns are one which contains some unique identifier to identify the call record, // and a column which stores another table's unique ID. function CDR_Table ($tablename, $source, $destination, $duration, $calltime, $unique, $foreignkey) { $this->source = $source; $this->destination = $destination; $this->duration = $duration; $this->calltime = $calltime; $this->unique = $unique; $this->foreignkey = $foreignkey; $this->tablename = $tablename; // Replace this with some singleton class, if necessary $this->db = mysql_connect ('localhost', 'tb2user', 't00lb0x'); mysql_select_db("soulcdrs_test", $this->db); } // This function is used to match all CDRs in the current table against another table. // It takes as arguments a CDRTable object for another table of CSVs, and some // parameters for matching. These are: // $calltime_range: dates when calls are made do not match. This will try to match between $calltime - $calltime_range // and $calltime + $calltime_range // $duration_range: as above, but for the duration of the call. // $foreign_key_prefix: Our Master table (from VoicePlus) is matched against other tables, so the foreign key will // be prefixed with (for example) soul, making soul-IS4738366 function match_against ($other_table, $duration_range, $calltime_range, $foreign_key_prefix = null) { if ($this->tablename == $other_table->tablename) return -1; $cdrs = $this->find_unmatched_calls(); while ($r = mysql_fetch_array ($cdrs)) { // Pull the current call's details from the table $unique_id = $r[ $this->unique ]; $source = $r[ $this->source ]; $dest = $r[ $this->destination ]; $duration = $r[ $this->duration ]; $calltime = strtotime ($r[ $this->calltime ]); // This is the duration window we will use to compare with calls in the other table $duration_low = $duration - $duration_range; $duration_high = $duration + $duration_range; // This is the window we will use for comparing when the call took place. $calltime_low = date("Y-m-d H:i:s", DateAdd ("n", -$calltime_range, $calltime)); $calltime_high = date("Y-m-d H:i:s", DateAdd ("n", $calltime_range, $calltime)); $this->match_call ($other_table, $unique_id, $source, $dest, $duration_low, $duration_high, $calltime_low, $calltime_high, $prefix); } } // This function takes as arguments a CDRTable object, which is linked to the database the CDRs will be matched // against, and this call's details. function match_call ($other_table, $unique_id, $source, $dest, $duration_low, $duration_high, $calltime_low, $calltime_high, $prefix=null, $iteration = 0) { $matching_result = $other_table->find_matching_cdrs ($source, $dest, $duration_low, $duration_high, $calltime_low, $calltime_high); // There are two cases here. // If there are 1 or more matches, link the current call record with the first one found. // If there are 0 matches, try expanding some of the search parameters. $matching_rows = mysql_num_rows ($matching_result); if ($matching_rows >= 1) { // Retrieve the unique ID of the first result $matching_row = mysql_fetch_array ($matching_result); $row_id = $other_table->return_unique_id ($matching_row); // Link the CDRs in both tables $other_table->link_cdrs ($row_id, $unique_id, $prefix); $this->link_cdrs ($unique_id, $row_id); // We wouldn't prefix in this direction, would we? } else { $iteration = $iteration + 1; switch ($iteration) { case 1: // The first (easier) case is where the call hasn't been matched for two reasons. // The first is that a prefix was dialled and stripped by the trunking server (eg. 991|026...) // The second is where someone has dialled a 9 digit number, and it has gone out through override. // ie. 612345678 goes out override as 0261234567. // To combat these, we'll strip the last digit, and surround the number with %s, so it matches against // patterns in the database. // Strip the last digit and surround it with %s, so 0261016900 -> %026101690% $dest = substr ($dest, 2, strlen ($dest) - 1); $dest = "%" . $dest . "%"; $this->match_call ($other_table, $unique_id, $source, $dest, $duration_low, $duration_high, $calltime_low, $calltime_high, $prefix, $iteration); break; case 2: // Now expand the duration by 30 seconds in either direction $duration_low = $duration_low - 30; $duration_high = $duration_high + 30; $this->match_call ($other_table, $unique_id, $source, $dest, $duration_low, $duration_high, $calltime_low, $calltime_high, $prefix, $iteration); break; case 3: echo "---------------\n"; } } } // Find all CDRs which have not been linked to another table's CDRs. function find_unmatched_calls () { $sql = "SELECT * FROM $this->tablename WHERE $this->foreignkey IS NULL AND $this->source != $this->destination ORDER BY $this->calltime"; return mysql_query ($sql, $this->db); } // Return the unique ID for this row function return_unique_id ($database_row) { return $database_row[ $this->unique ]; } // Query the database to find matching calls. function find_matching_cdrs ($source, $dest, $duration_low, $duration_high, $calltime_low, $calltime_high) { $sql = "SELECT * FROM $this->tablename WHERE $this->foreignkey IS NULL AND $this->source = '$source'"; if (strpos ($dest, '%') === FALSE) { $sql .= " AND $this->destination = '$dest' "; } else { $sql .= " AND $this->destination LIKE '$dest' "; } $sql .= "AND $this->duration BETWEEN '$duration_low' AND '$duration_high' AND $this->calltime BETWEEN '$calltime_low' AND '$calltime_high' ORDER BY $this->calltime"; $matching_result = mysql_query ($sql, $this->db); echo $sql . "\n"; return $matching_result; } // Find the CDR with the Unique ID $unique, and set its foreign key to $foreign_key. function link_cdrs ($unique_id, $foreign_key_to_use, $prefix=null) { // Now link the two CDRs together $sql = "UPDATE $this->tablename"; // Add the prefix in, unless it's null (we don't want all records prefixed with just a -) if ($prefix != null) { $sql .= " SET $this->foreignkey = '" . $prefix . "-" . $foreign_key_to_use . "'"; } else { $sql .= " SET $this->foreignkey = '$foreign_key_to_use'"; } $sql .= " WHERE UniqueID = '$unique_id'"; mysql_query ($sql, $this->db); } } function DateAdd($interval, $number, $date) { $date_time_array = getdate($date); $hours = $date_time_array['hours']; $minutes = $date_time_array['minutes']; $seconds = $date_time_array['seconds']; $month = $date_time_array['mon']; $day = $date_time_array['mday']; $year = $date_time_array['year']; switch ($interval) { case 'yyyy': $year+=$number; break; case 'q' : $year+=($number*3); break; case 'm': $month+=$number; break; case 'y': case 'd': case 'w': $day+=$number; break; case 'ww': $day+=($number*7); break; case 'h': $hours+=$number; break; case 'n': $minutes+=$number; break; case 's': $seconds+=$number; break; } $timestamp= mktime($hours,$minutes,$seconds,$month,$day,$year); return $timestamp; }?>