#!/usr/bin/perl

#############################################################
# Age Analyses Month End                                    #
# Convert from web interface extrent/a_month_end.html       #
# By Jay ,Feb 13,2020                               #########
# Email: Steven.ji@126.com                          #
# command line:  month_end.bat                      #       
#####################################################

use strict;
use DBI;

my $dbuser     = 'root';
my $dbpassword = 'fbg4ips';
my $dbhost     = '127.0.0.1';
my $database   = $ARGV[0];  #command argument is a database name

unless ($database){
  print "Usage: $0 oakdale_1\n";exit;
}

my $db   = DataBase->new();

#Debtors - account =1
Debtors();

#Creditors -account =2
Creditors();
 
sub Debtors{

    my $lastmonthend =  $db->SelectRow("select * from account_monthend where account = ? order by id desc limit 1",1);
    my $current_month;
	if($lastmonthend->{month}){
	    my $date = $lastmonthend->{year} . "-" . $lastmonthend->{month} ."-01";
	       $current_month = $db->SelectRow("SELECT MONTH(DATE_ADD(?,INTERVAL 1 MONTH)) AS month,
		                                            YEAR(DATE_ADD(?,INTERVAL 1 MONTH)) AS year,
													DATE_ADD(?,INTERVAL 1 MONTH) > current_date AS future",
													$date,$date,$date);
		    print STDERR "Debtors LAST FINALIZE DONE IN:" . $lastmonthend->{month} ."-" . $lastmonthend->{year} ."\n";
	}else{
	      $current_month  = $db->SelectRow("select month(current_date) month, year(current_date) year");
		  print STDERR "Debtors LAST FINALIZED MONTH: NEVER!\n";	
	}
	
	my $cdate = $db->SelectRow("select day(current_date) days, month(current_date) month, year(current_date) year");
	print STDERR "Debtors FINALIZE AAND OPEN NEW MONTH:". $current_month->{month} ."-" . $current_month->{year},"\n";

    my $list = $db->SelectARef("select * from tillclients");
	foreach my $entity(@$list){
	    my $b_current = $entity->{b_current};
		my $b_30      = $entity->{b_30};
		my $b_60      = $entity->{b_60};
		my $b_90      = $entity->{b_90};
		my $b_120     = $entity->{b_120};
		my $b_120plus = $entity->{b_120plus};
		
		$b_120plus    = $b_120plus + $b_120;
		$b_120        = $b_90;
        $b_90         = $b_60; 
		$b_60         = $b_30;
		$b_30         = $b_current;
		$b_current    = 0;
		
		$db->Exec("update tillclients set b_current=?,b_30=?,b_60 =?,b_90 =?,b_120 =?,b_120plus = ? 
		                 where id = ?",
						 $b_current, $b_30, $b_60, $b_90, $b_120,$b_120plus, $entity->{id});
		
	}
	unless($db->SelectOne("select id from account_monthend where month = ? and year = ? and account = 1",
	                          $current_month->{month},$current_month->{year})){
							  
		$db->Exec("insert into account_monthend SET month=?, year=?, account=?, person=?",
		                      $current_month->{month},$current_month->{year},1,'cron');
	}

}

sub Creditors{
    my $lastmonthend =  $db->SelectRow("select * from account_monthend where account = ? order by id desc limit 1",2);
    my $current_month;
	if($lastmonthend->{month}){
	    my $date = $lastmonthend->{year} . "-" . $lastmonthend->{month} ."-01";
	       $current_month = $db->SelectRow("SELECT MONTH(DATE_ADD(?,INTERVAL 1 MONTH)) AS month,
		                                            YEAR(DATE_ADD(?,INTERVAL 1 MONTH)) AS year,
													DATE_ADD(?,INTERVAL 1 MONTH) > current_date AS future",
													$date,$date,$date);
		    print STDERR "Creditors LAST FINALIZE DONE IN:" . $lastmonthend->{month} ."-" . $lastmonthend->{year} ."\n";
	}else{
	      $current_month  = $db->SelectRow("select month(current_date) month, year(current_date) year");
		  print STDERR "Creditors LAST FINALIZED MONTH: NEVER!\n";	
	}
	
	my $cdate = $db->SelectRow("select day(current_date) days, month(current_date) month, year(current_date) year");
	print STDERR "Creditors FINALIZE AND OPEN NEW MONTH:". $current_month->{month} ."-" . $current_month->{year},"\n";

      my $list = $db->SelectARef("select * from suppliers");
	  foreach my $entity(@$list){
	    my $b_current = $entity->{b_current};
		my $b_30      = $entity->{b_30};
		my $b_60      = $entity->{b_60};
		my $b_90      = $entity->{b_90};
		my $b_120     = $entity->{b_120};
		my $b_120plus = $entity->{b_120plus};
		
		$b_120plus    = $b_120plus + $b_120;
		$b_120        = $b_90;
        $b_90         = $b_60; 
		$b_60         = $b_30;
		$b_30         = $b_current;
		$b_current    = 0;
		
		$db->Exec("update suppliers set b_current=?,b_30=?,b_60 =?,b_90 =?,b_120 =?,b_120plus = ? 
		                 where id = ?",
						 $b_current, $b_30, $b_60, $b_90, $b_120,$b_120plus, $entity->{id});
		
	}
	unless($db->SelectOne("select id from account_monthend where month = ? and year = ? and account = 2",
	                          $current_month->{month},$current_month->{year})){
							  
		$db->Exec("insert into account_monthend SET month=?, year=?, account=?, person=?",
		                      $current_month->{month},$current_month->{year},2,'cron');
	}


} 



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;