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_