#!perl
#!/usr/bin/perl

## 2023-11-14 
## Deactive clients who have no invoice in x days 
## reactive manually. by going to /extranet/index.cgi?a=account&itemid=10&search=%&letter=A&page=1
## 

use strict;
use lib '.';
 
use DBI;
use JSON; 
use Data::Dumper;

my $dbuser     = 'root';
my $dbpassword = 'fbg4ips';
my $dbhost     = '127.0.0.1';
my $database   = 'spaceusers';#'cht_1';#'dial_1';#'cht_1';  

my $db   = DataBase->new();

main();

sub main{
	
	#get configurations
	my $sets = $db->SelectARef("SELECT * FROM spaceusers.Settings WHERE  name=? ", 'deactive_client');
	
    NEXT:foreach my $set(@$sets){
       
       my $database = $set->{db};       
       my $days     = $set->{val};
	   next NEXT unless $days;
       
	   my $list = $db->SelectARef("SELECT id,active,name FROM $database.tillclients WHERE active =1");
	
	   foreach my $client(@$list){
		my $acc= $db->SelectOne("SELECT id FROM $database.accounts WHERE entityid=? AND 
		                                                       tillinvoice_id >0 AND 
															   stamp + INTERVAL ? DAY  >NOW()",
															   $client->{id},
															   $days);
        unless($acc){
		  logg("$client->{id} - $client->{name} deactived");
          $db->Exec("UPDATE $database.tillclients SET active = 0 WHERE id=?",$client->{id});
		  
		}	 
	  } 
    }
}

 
sub logg
{
	my ($msg) = @_;
	my $date = sprintf("%04d-%02d-%02d %02d:%02d:%02d", getDate());

	open LOGG, ">>cron_clients.txt";
	print LOGG "[$date] $msg\n";
	close LOGG;
}
sub getTime
{
    my ($time) = @_;
    my @t = $time ? localtime( $time ) : localtime();
    return ( sprintf("%04d",$t[5]+1900),
             sprintf("%02d",$t[4]+1),
             sprintf("%02d",$t[3]),
             sprintf("%02d",$t[2]),
             sprintf("%02d",$t[1]),
             sprintf("%02d",$t[0])
           );
}

sub getDate
{
    my ($time) = @_;
    my @tt = getTime($time);
    return splice( @tt, 0, 3);
}
############

package DataBase; 
#pain for old method. so, introduce this package.....:)
use DBI;
sub new{ 
  my ($class, %opts) = @_;
  my $self = { %opts };
   
  bless $self,$class;
  $self->InitDB;
  return $self;
}

sub inherit{
  my $class = shift;
  my $dbh   = shift;
  my $self={ dbh=>undef };
  bless $self,$class;
  $self->{dbh} = $dbh;
  return $self;
}


sub dbh{shift->{dbh}}

sub InitDB{
  
  my $self = shift; 
  $self->{dbh}=DBI->connect("DBI:mysql:database=$database;host=$dbhost;",
            $dbuser,$dbpassword) || die ("Can't connect to Mysql server.");
  $self->Exec("SET sql_mode = ''");
  $self->{'exec'}=0;
  $self->{'select'}=0;
  
}

sub DESTROY{
  shift->UnInitDB();
}

sub UnInitDB{
  my $self=shift;
  if($self->{dbh})
  {
    if($self->{locks})
    {
          $self->Unlock();
    }
    $self->{dbh}->disconnect;
  }
  $self->{dbh}=undef;
}

sub Exec
{
  my $self=shift;
  $self->{dbh}->do(shift,undef,@_) || die"Can't exec:\n".$self->{dbh}->errstr;
  $self->{'exec'}++;
}

sub SelectOne
{
  my $self=shift;
  my $res = $self->{dbh}->selectrow_arrayref(shift,undef,@_);
  die"Can't execute select:\n".$self->{dbh}->errstr if $self->{dbh}->err;
  $self->{'select'}++;
  return $res->[0];
};

sub SelectRow
{
  my $self=shift;
  my $res = $self->{dbh}->selectrow_hashref(shift,undef,@_);
  die"Can't execute select:\n".$self->{dbh}->errstr if $self->{dbh}->err;
  $self->{'select'}++;
  return $res;
}

sub Select
{
  my $self=shift;

  my $res = $self->{dbh}->selectall_arrayref( shift, { Slice=>{} }, @_ );
  die"Can't execute select:\n".$self->{dbh}->errstr if $self->{dbh}->err;
  return undef if $#$res==-1;
  my $cidxor=0;
  for(@$res)
  {
    $cidxor = $cidxor ^ 1;
    $_->{row_cid} = $cidxor;
  }
  $self->{'select'}++;
  return $res;
}

sub SelectARef
{
   my $self = shift;
   my $data = $self->Select(@_);
   return [] unless $data;
   return [$data] unless ref($data) eq 'ARRAY';
   return $data;
}

sub getLastInsertId
{
  return shift->{ dbh }->{'mysql_insertid'};
}

1;