perl copy UTM5 user ip from SQL to LDAP DHCP

#!/usr/bin/perl

# export UTM users ip groups to LDAP DHCP

use strict;
use warnings;
use diagnostics;
use Time::Local;
use Time::HiRes;
use DBI;
use Net::MAC;
use Data::Dumper;
use Net::LDAP;

########################################

# no need input arg

########################################

my $log = '/netup/utm5/log/ip_rfw_ldap_reload.log'; #!!!!!
#my $log = './ip_rfw_ldap_reload.log';

my $dbbase = "base";
my $dbhost = "host";
my $dbuser = "login";
my $dbpass = "pass";

my $ldaphost = 'host';
my $ldapport = '389';
my $ldapusdn = 'cn=admin,dc=domain,dc=ru';
my $ldappass = 'password';
my $ldapbase = 'cn=DHCP Config,dc=domain,dc=ru';
my $ldapgrp = 'cn=group1,cn=DHCP Config,dc=domain,dc=ru';

###### starttime
my $time = localtime time;
my $stime = [ Time::HiRes::gettimeofday( ) ];

# my var
my %hash;
my @arr;
my ($ip, $mac);
my $count = 0;

########################################

sub chmac {
    my $mac = $_[0];
    if ($mac =~ /^(([0-9a-fA-F]{2}[:-]{1}){5}([0-9a-fA-F]{2}))$/) {
        return uc($mac);
    } else { return undef; }
}

sub checkip() {
    if ($_[0] =~ /^(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9])\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[1-9]|0)\.(25[0-5]|2[0-4][0-9]|[0-1]{1}[0-9]{2}|[1-9]{1}[0-9]{1}|[0-9])$/ ) {
        return $_[0];
    } else { print "$_[0] - not ip\n"; return undef; }
}

sub ldap_connect {
    use vars qw($ldaphost $ldapport $ldapusdn $ldappass);
    my $ldap = Net::LDAP->new($ldaphost, port    => $ldapport, version => 3, timeout => 60
        ) or die "ERROR LDAP: Can't contact ldap server for writing ($@)";
    $ldap->bind( $ldapusdn, password => $ldappass );
    return $ldap;
}

sub ldap_search {
    use vars qw($ldapbase);
    my $filter; my @dn;
    if ($_[1]) {
        if ($_[1] =~ /^(([0-9a-fA-F]{2}[:-]{1}){5}([0-9a-fA-F]{2}))$/) {$filter = "(dhcpHWAddress:=ethernet " . $_[1] . ")";}
        if ($_[1] =~ /^(([0-9]{1,3}\.){3}([0-9]{1,3}))$/) {$filter = "(dhcpStatements:=fixed-address " . $_[1] . ")";}
        my $mesg = $_[0]->search(base   => $ldapbase, filter => "(|$filter)");
        foreach my $entry ( $mesg->all_entries ) {push(@dn, $entry->dn);}
        if(@dn) { return @dn;} else { return undef; }
    } else { return undef; }
}

sub ldap_delete {
    my $mesg = $_[0]->delete( $_[1] );
    return $mesg->code;
}

sub ldap_add {
    use vars qw($ldapgrp);
    my $ip = "fixed-address " . $_[1];
    my $mac = "ethernet " . $_[2];
    my $add = $_[0]->add("cn=$_[1],$ldapgrp",
        attr => [
            'cn'          => $_[1],
            'objectclass' => [ 'top', 'dhcpHost' ],
            'dhcpHWAddress' => $mac,
            'dhcpStatements' => $ip
        ]
    );
    return $add->code;
}

sub ldap_close {
    my $mesg = $_[0]->unbind;
    return $mesg->code;
}


########################################

open(LOG, ">> $log");

###### connect to db
my $dbi = "DBI:mysql:$dbbase:$dbhost";
my $dbh = DBI->connect($dbi, $dbuser, $dbpass) || die "Could not connect to database: $DBI::errstr";
my $dbq = "SELECT inet_ntoa(4294967295 & ipg.ip), ipg.mac ";
  $dbq .= "FROM ip_groups AS ipg ";
  $dbq .= "Inner Join iptraffic_service_links AS isl ON isl.ip_group_id = ipg.ip_group_id ";
  $dbq .= "Inner Join service_links AS sl ON sl.id = isl.id ";
  $dbq .= "Inner Join accounts AS ac ON ac.id = sl.account_id ";
  $dbq .= "WHERE ipg.is_deleted = 0 ";
  $dbq .= "AND ipg.ip >  '167772160' ";
  $dbq .= "AND ipg.ip <  '184549375' ";
  $dbq .= "AND ipg.mac NOT LIKE '' ";
  $dbq .= "AND isl.is_deleted =  '0' ";
  $dbq .= "AND ac.is_deleted =  '0' ";
  $dbq .= "AND ac.is_blocked =  '0'";

### selet from table
my $sth = $dbh->prepare($dbq);
    $sth->execute();
        while (($ip, $mac) = $sth->fetchrow_array()) {
            if (&checkip($ip) ne '') {
                $hash{$ip} = chmac($mac);
#                print "$ip$mac\n";
            }
            push(@arr,$ip,$mac);
            $count = $count + 1;
            if ($count%1000 == 0) {
                print "Readed $count time: " . Time::HiRes::tv_interval( $stime ) . " seconds!\n";
            }
        }
    $sth->finish();
    $dbh->disconnect();

#print Dumper(\%hash);

    print "Get SQL in time: " . Time::HiRes::tv_interval( $stime ) . " seconds!\n";

###
@arr = keys %{{ map { $_ => 1 } (grep { defined $_ } @arr)}}; # weed undef element && sort & unique
#print @arr;


my $ldap = &ldap_connect();

if (!$ldap) {
    print LOG "$time - LDAP ERR - can't connect\n";
} else {
    my @ldapdel;
    foreach my $key (@arr) {
        push(@ldapdel, ldap_search($ldap,$key));
    }
# prepare
    @ldapdel = grep { defined $_ } @ldapdel; # weed undef element
    @ldapdel = keys %{{ map { $_ => 1 } @ldapdel }}; # sort & unique
#del in ldap
    if (scalar(@ldapdel) ge 1) {
        foreach my $e ( @ldapdel ) {
            my $mesgdel = &ldap_delete($ldap,$e);
            print LOG "$time - LDAP DEL - $e - $mesgdel\n";
        }
    }

# add to ldap
    foreach my $key (keys %hash) {
        if ($hash{$key}) {
            my $add = &ldap_add($ldap,$key,$hash{$key});
            print LOG "$time - LDAP ADD - $key,$hash{$key} - $add\n";
        }
    }

    &ldap_close($ldap);
}

print "TOTAL time: " . Time::HiRes::tv_interval( $stime ) . " seconds!\n";
print LOG "TOTAL time: " . Time::HiRes::tv_interval( $stime ) . " seconds!\n";
close LOG;
=begin