package main;
use strict;
#Main SQL function

use strict;
use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS $VERSION
  $db
  $admin
  $CONF
  %DATA
  $OLD_DATA
  @WHERE_RULES
  $WHERE
  
  $SORT
  $DESC
  $PG
  $PAGE_ROWS
);

use Exporter;
$VERSION = 2.00;
@ISA = ('Exporter');

@EXPORT = qw(
  $db
  $admin
  $CONF
  @WHERE_RULES
  $WHERE
  %DATA
  %OLD_DATA
  
  $SORT
  $DESC
  $PG
  $PAGE_ROWS
);

@EXPORT_OK = ();
%EXPORT_TAGS = ();


$db    = undef;
$admin = undef;
$CONF  = undef;
@WHERE_RULES = ();
$WHERE = '';
%DATA  = ();
$OLD_DATA   = (); #all data

$SORT      = 1;
$DESC      = '';
$PG        = 0;
$PAGE_ROWS = 25;

my $query_count = 0;

use DBI;

#**********************************************************
# Connect to DB
#**********************************************************
sub connect {
  my $class = shift;
  my $self = { };
  my ($dbhost, $dbname, $dbuser, $dbpasswd, $attr) = @_;
  bless($self, $class);
  #$self->{debug}=1;
  $self->{db} = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost", "$dbuser", "$dbpasswd") or print 
       "Content-Type: text/html\n\nError: Unable connect to server '$dbhost:$dbname'\n";
  
  #For mysql 5 or highter
  $self->{db}->do("set names ".$attr->{CHARSET}) if ($attr->{CHARSET});
 
  $self->{query_count}=0;
  return $self;
}


sub disconnect {
  my $self = shift;


  $self->{db}->disconnect;
  return $self;
}

#**********************************************************
#
#**********************************************************
sub db_version {
	my $self = shift;
  my ($attr)	= @_;

  my $version = $db->get_info( 18 ); 
  if ($version =~ /^(\d+\.\d+)/) {
   	$version = $1;
   }

  return $version;
}

#**********************************************************
#  do
# type. do 
#       list
#**********************************************************
sub query {
	my $self = shift;
  my ($db, $query, $type, $attr)	= @_;

  $self->{errstr}=undef;
  $self->{errno}=undef;
  $self->{TOTAL} = 0;
  print "<p>$query</p>\n" if ($self->{debug});

  if (defined($attr->{test})) {
  	 return $self;
   }

my $q;

my @Array = ();
# check bind params
if ($attr->{Bind}) {
  
  foreach my $Data (@{ $attr->{Bind} }) {
    push(@Array, $Data);
    #print ref(\$Data);
#    if (ref($Data) eq 'SCALAR') {
#      push(@Array, $$Data);
#     }
#    else  {
#      $self->{errno} = 7;
#      $self->{errstr} = "No SCALAR param in Bind!";
#      return $self;
#     }

   }

 }

if (defined($type) && $type eq 'do') {
#  print $query;
  $q = $db->do($query, undef, @Array);
  if (defined($db->{'mysql_insertid'})) {
  	 $self->{INSERT_ID} = $db->{'mysql_insertid'};
   }
}
else {
  #print $query;
  $q = $db->prepare($query); # || die $db->errstr;
  if($db->err) {
     $self->{errno} = 3;
     $self->{sql_errno}=$db->err;
     $self->{sql_errstr}=$db->errstr;
     $self->{errstr}=$db->errstr;
   
     return $self->{errno};
   }
  #print $query;
  
  if ($attr->{MULTI_QUERY}) {
    foreach my $line ( @{ $attr->{MULTI_QUERY} } ) {
      $q ->execute( @$line );
      if($db->err) {
        $self->{errno} = 3;

        $self->{sql_errno}=$db->err;
        $self->{sql_errstr}=$db->errstr;
        $self->{errstr}=$db->errstr;
        return $self->{errno};
       }
     }
   }
  else {
    $q ->execute();
    if($db->err) {
      $self->{errno} = 3;

      $self->{sql_errno}=$db->err;
      $self->{sql_errstr}=$db->errstr;
      $self->{errstr}=$db->errstr;
      return $self->{errno};
     }
    $self->{TOTAL} = $q->rows;
  }

  $self->{Q}=$q;

#  $self->{NUM_OF_FIELDS} = $q->{NUM_OF_FIELDS};
}



if($db->err) {

  if ($db->err == 1062) {
    $self->{errno} = 7;
    $self->{errstr} = 'ERROR_DUBLICATE';
    return $self;
   }

  $self->{errno} = 3;
  $self->{errstr} = 'SQL_ERROR'; # . ( ($self->{db}->strerr) ? $self->{db}->strerr : '' );
  return $self;
 }

if ($self->{TOTAL} > 0) {
  my @rows;
  while(my @row = $q->fetchrow()) {
    push @rows, \@row;
  }
  $self->{list} = \@rows;
 }
else {
	delete $self->{list};
}

 $self->{query_count}++;
 return $self;
}



#**********************************************************
# get_data
#**********************************************************
sub get_data {
	my $self=shift;
	my ($params, $attr) = @_;
  my %DATA;
  
  if(defined($attr->{default})) {
  	 %DATA = %{ $attr->{default} };
   }
  
  while(my($k, $v)=each %$params) {
  	 next if (! $params->{$k} && defined($DATA{$k})) ;
  	 $v =~ s/^ +|[ \n]+$//g if ($v);
  	 $DATA{$k}=$v;
     #print "--$k, '$v'<br>\n";
   }

#  while(my($k, $v)=each %DATA) {
#  	print "$k, $v<br>\n";
#  }
  
	return %DATA;
}


#**********************************************************
# search_expr($self, $value, $type)
#
# type of fields
# IP -  IP Address
# INT - integer
# STR - string
#**********************************************************
sub search_expr {
  my $self=shift;
  my ($value, $type, $field, $attr)=@_;

 	if ($attr->{EXT_FIELD}) {
    $self->{SEARCH_FIELDS} .= "$field, ";
    $self->{SEARCH_FIELDS_COUNT}++;
 	 }	
 
  if ($value =~ s/;/,/g ) {
  	return [ "$field IN ($value)" ];
   }


  my @val_arr     = split(/,/, $value);  
  my @result_arr  = ();

  foreach my $v (@val_arr) { 
    my $expr = '=';

    if($type eq 'INT' && $v =~ s/\*//g) {
      $expr = '>';
     }
    elsif ($type eq 'STR') {
    	$expr = ' LIKE ';
    	$v =~ s/\*/\%/g;
     }
    elsif ( $v =~ s/^([<>=]{1,2})// ) {
      $expr = $1;
     }  
  
    if ($type eq 'IP') {
      $v = "INET_ATON('$v')";
     }
    else {
      $v="'$v'";
     }

    $value = $expr . $v;
    
    push @result_arr, "$field$value" if ($field);
   }

  if ($field) {
  
  	if ($type ne 'INT') {
  		return [ '('. join(' or ', @result_arr)  .')']; 
  	 }

    return \@result_arr; 
   }

#  print "$expr- $value \n";

  return $value;
}


#**********************************************************
# change_constructor($self, $uid, $attr)
# $attr 
#  CHANGE_PARAM - chenging param
#  TABLE        - changing table
#  \%FIELDS     - fields of table
#  ...          - data
#  OLD_INFO     - OLD infomation for compare
#**********************************************************
sub changes {
  my $self = shift;
  my ($admin, $attr) = @_;

  
  my $TABLE        = $attr->{TABLE};
  my $CHANGE_PARAM = $attr->{CHANGE_PARAM};
  my $FIELDS       = $attr->{FIELDS};
  my %DATA         = $self->get_data($attr->{DATA}); 

  $DATA{DISABLE} = ($DATA{DISABLE}) ? 1 : 0;

  if(defined($DATA{EMAIL}) && $DATA{EMAIL} ne '') {
    if ($DATA{EMAIL} !~ /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/) {
      $self->{errno} = 11;
      $self->{errstr} = 'ERROR_WRONG_EMAIL';
      return $self;
     }
   }

  $OLD_DATA = $attr->{OLD_INFO}; #  $self->info($uid);
  if($OLD_DATA->{errno}) {
     $self->{errno}  = $OLD_DATA->{errno};
     $self->{errstr} = $OLD_DATA->{errstr};
     return $self;
   }
 


  my $CHANGES_QUERY = "";
  my $CHANGES_LOG = "";


  while(my($k, $v)=each(%DATA)) {
#  	print "$k, $v<br>\n";
    $OLD_DATA->{$k} = '' if (! $OLD_DATA->{$k});
    if (defined($FIELDS->{$k}) && $OLD_DATA->{$k} ne $DATA{$k}){
        if ($k eq 'PASSWORD' || $k eq 'NAS_MNG_PASSWORD') {
          $CHANGES_LOG .= "$k *->*;";
          $CHANGES_QUERY .= "$FIELDS->{$k}=ENCODE('$DATA{$k}', '$CONF->{secretkey}'),";
         }
        elsif($k eq 'IP' || $k eq 'NETMASK') {
          if ($DATA{$k} !~ /\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/) {
            $DATA{$k} = '0.0.0.0' ;
           }
          
          $CHANGES_LOG .= "$k $OLD_DATA->{$k}->$DATA{$k};";
          $CHANGES_QUERY .= "$FIELDS->{$k}=INET_ATON('$DATA{$k}'),";
         }
        else {
          $CHANGES_LOG .= "$k $OLD_DATA->{$k}->$DATA{$k};";
          $CHANGES_QUERY .= "$FIELDS->{$k}='$DATA{$k}',";
         }
     }
   }




if ($CHANGES_QUERY eq '') {
  return $self->{result};	
}



# print $CHANGES_LOG;
  chop($CHANGES_QUERY);
  my $extended = ($attr->{EXTENDED}) ? $attr->{EXTENDED} : '' ;
  $self->query($db, "UPDATE $TABLE SET $CHANGES_QUERY WHERE $FIELDS->{$CHANGE_PARAM}='$DATA{$CHANGE_PARAM}'$extended", 'do');

  if($self->{errno}) {
     return $self;
   }

  if (defined($DATA{UID}) && $DATA{UID} > 0 && defined($admin)) { 
     $admin->action_add($DATA{UID}, "$CHANGES_LOG");
   }

  return $self->{result};
}



1
