Pejman Moghadam / Scripts

Sigma tanzim-moghararat logger

Public domain

---

#!/usr/local/mssql/php/bin/php
<?php
#####################################################################
# Configuragtion

# output
date_default_timezone_set('Asia/Tehran');
$yesterday = date('Y-m-d', mktime(0, 0, 0, date("m") , date("d") - 1, date("Y")));
$yesterday = date('Y-m-d',time()-86400);
$yesterday = date('Y-m-d',strtotime("-1 day"));

$log_date=$yesterday;
#$log_date="2009-11-21";
$log_dir="/var/www/htdocs/log/dialup/";
$log_file=$log_dir.$log_date.".csv";

# dbname from freetds.conf
$dbname="SigmaSystem";
$dbuser="sa";
$dbpass="123456";

# debug 0= off, 1=on, 2=on+error
$debug="1";
$retry="400";
#####################################################################

// Trun off display errors 
if($debug == "1") 
{
  echo "Turning off display_errors ...\n";
  error_reporting(0);
  ini_set('display_errors','Off');
}

// Split date
$array=explode("-",$log_date);
$year=$array[0];
$month=$array[1];
$day=$array[2];

// repeat query if error occured
for($i=0;$i<$retry;$i++)
{
  sleep(2);
  if($debug) echo "\nTry $i :\n";

  // Connect to MSSQL
  if($debug) echo "Connecting to MSSQL ...\n";
  if(!$link = mssql_connect($dbname, $dbuser, $dbpass))
    die('Something went wrong while connecting to MSSQL');

  // Check MSSQL version
  $version = mssql_query('SELECT @@VERSION');
  $row = mssql_fetch_array($version);
  if($debug=="2") echo "$row[0]";

  // Select database
  if($debug) echo "Selecting database ... \n";
    mssql_select_db($dbname, $link);

  // Sending query
  if($debug) echo "Sending query to database ... \n";
  $query="
    SELECT
      CONVERT(varchar,AccInternet_Date,121) AS AccInternet_Date, 
      CONVERT(varchar,DATEADD(s, AccInternet_SessionTime, AccInternet_Date),121) AS stop, 
      AccInternet_IPAddress,
      AccInternet_PhoneNumber
    FROM 
      tbl_Account_Internet 
    WHERE 
      ( YEAR(AccInternet_Date)='$year' and MONTH(AccInternet_Date) ='$month' and DAY(AccInternet_Date)='$day' )
    ";
  // exit loop if query replied 
  if($array = mssql_query("$query")) break;
  // prepare for repeat query
  else {
    echo "Resetting query ... \n";
    mssql_free_result($array);
    mssql_free_statement($query);
    mssql_close($link);
  } 
}

// reply can't be empty 
if($debug) echo "Checking if there is any records selected ... \n";
if(!mssql_num_rows($array)) 
{
  if($debug) echo "No records found !!! \n";
} 
else 
{
  // open output file
  if($debug) echo "Openning output file ...\n";
  if(!($handle=fopen("$log_file", "w")))
    die("Can't open $log_file");

  // fetch reply to array
  if($debug) echo "Writing output file ...\n";
  while ($row = mssql_fetch_array($array,MSSQL_BOTH))
  {
    // putting array to variables
    $start=trim($row['AccInternet_Date']);
    $dec_ip=trim($row['AccInternet_IPAddress']);
    $tel=trim($row['AccInternet_PhoneNumber']);
    $stop=trim($row['stop']);

    // replrace - with / in date
    $start = str_replace("-", "/", $start);
    // remove milliseconds .000 
    $start = preg_replace('/\..*/', '', $start);

    // replrace - with / in date
    $stop = str_replace("-", "/", $stop);
    // remove milliseconds .000 
    $stop = preg_replace('/\..*/', '', $stop);

    // translate decimal ip to dotted quad
    $one = (int) ($dec_ip / ( 256 * 256 * 256));
    $dec_ip -= $one * 256 * 256 * 256 ;
    $two = (int) ($dec_ip / (256 * 256));
    $dec_ip -= $two * 256 * 256 ;
    $three = (int) ($dec_ip / 256);
    $dec_ip -= $three * 256 ;
    $four=$dec_ip;
    $ip="$one.$two.$three.$four";

    // wirte a record to file
    fprintf($handle,"%s,%s,%s,%s\n",$start,$stop,$ip,$tel);
  }
}

if($debug) echo "Close file and connection ...\n";

// close output file
fclose($handle);

// Close the link to MSSQL
mssql_close($link);

?>

---

BY: Pejman Moghadam
TAG: tanzim, php, sigma, mssql
DATE: 2010-01-03 21:50:05

---

Pejman Moghadam / Scripts [ TXT ]