#!/usr/bin/perl

#############################################################
# Age Analyses Month End                                    #
# Convert from web interface extrent/a_month_end.html       #
# By Jay ,March 17,2020                               #########
# Email: Steven.ji@126.com                          #
# command line:  week_end.bat                      # 
# \perl\bin\perl week_end.pl oakdale_1             #      
#####################################################

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();

my %weekdays = (0=>"Sunday",1=>"Monday",2=>"Tuesday",3=>"Wednesday",4=>"Thursday",5=>"Friday",6=>"Saturday");
			
#Debtors - account =1
Debtors();

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

    my $lastweekend =  $db->SelectRow("select a.*,DATE(created) AS cur_date,NOW() AS acc_now  
	              from account_weekend a where a.account = ? order by id desc limit 1",1);
    my $current_week;
	if($lastweekend->{week}){	     
	       $current_week = $db->SelectRow("SELECT   MONTH(? + INTERVAL 1 WEEK) AS month,
		                                            YEAR(? + INTERVAL 1 WEEK) AS year,
													DAYOFWEEK(? + INTERVAL 1 WEEK) week ",
													$lastweekend->{acc_now},$lastweekend->{acc_now},$lastweekend->{acc_now});
		    print STDERR "Debtors LAST FINALIZE DONE IN:" . $lastweekend->{cur_date} ."\n";
	}else{
	      $current_week  = $db->SelectRow("select month(current_date) month, year(current_date) year,DAYOFWEEK(NOW()) week");
		  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_week->{month} ."-" . $current_week->{year},"\n";

    my $list = $db->SelectARef("select * from tillclients");
	foreach my $entity(@$list){
	    my $w_current = $entity->{w_current};
		my $w_1       = $entity->{w_1};
		my $w_2       = $entity->{w_2};
		my $w_3       = $entity->{w_3};
		my $w_4       = $entity->{w_4};
		my $w_4plus   = $entity->{w_4plus};
		
		$w_4plus     = $w_4plus + $w_4;
		$w_4         = $w_3;
        $w_3         = $w_2; 
		$w_2         = $w_1;
		$w_1         = $w_current;
		$w_current   = 0;
		
		$db->Exec("update tillclients set w_current=?,w_1=?,w_2 =?,w_3 =?,w_4 =?,w_4plus = ? 
		                 where id = ?",
						 $w_current, $w_1, $w_2, $w_3, $w_4,$w_4plus, $entity->{id});
		
	}
	unless($db->SelectOne("select id from account_weekend where week =? 
	                                                            AND month = ? 
																and year = ? 
																and account = 1",
	                          $current_week->{week},$current_week->{month},$current_week->{year})){
							  
		$db->Exec("insert into account_weekend SET week=?,month=?, year=?, account=?, person=?",
		                      $current_week->{week},$current_week->{month},$current_week->{year},1,'cron');
	}

}

sub Creditors{
      my $lastweekend =  $db->SelectRow("select a.*,DATE(created) AS cur_date,NOW() AS acc_now  
	           from account_weekend a where a.account = ? order by id desc limit 1",2);
  
 
	
	my $current_week;
	if($lastweekend->{week}){	     
	       $current_week = $db->SelectRow("SELECT   MONTH(? + INTERVAL 1 WEEK) AS month,
		                                            YEAR(? + INTERVAL 1 WEEK) AS year,
													DAYOFWEEK(? + INTERVAL 1 WEEK) week ",
													$lastweekend->{acc_now},$lastweekend->{acc_now},$lastweekend->{acc_now});
		    print STDERR "Creditors LAST FINALIZE DONE IN:" . $lastweekend->{cur_date} ."\n";
	}else{
	      $current_week  = $db->SelectRow("select month(current_date) month, year(current_date) year,DAYOFWEEK(NOW()) week");
		  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_week->{month} ."-" . $current_week->{year},"\n";

      my $list = $db->SelectARef("select * from suppliers");
	  foreach my $entity(@$list){
	    my $w_current = $entity->{w_current};
		my $w_1       = $entity->{w_1};
		my $w_2       = $entity->{w_2};
		my $w_3       = $entity->{w_3};
		my $w_4       = $entity->{w_4};
		my $w_4plus   = $entity->{w_4plus};
		
		$w_4plus     = $w_4plus + $w_4;
		$w_4         = $w_3;
        $w_3         = $w_2; 
		$w_2         = $w_1;
		$w_1         = $w_current;
		$w_current   = 0;
		
		$db->Exec("update suppliers set w_current=?,w_1=?,w_2 =?,w_3 =?,w_4 =?,w_4plus = ? 
		                 where id = ?",
						 $w_current, $w_1, $w_2, $w_3, $w_4,$w_4plus, $entity->{id});
		
		 
		
	}
	unless($db->SelectOne("select id from account_weekend where week =? 
	                                                            AND month = ? 
																and year = ? 
																and account = 1",
	                          $current_week->{week},$current_week->{month},$current_week->{year})){
							  
		$db->Exec("insert into account_weekend SET week=?,month=?, year=?, account=?, person=?",
		                      $current_week->{week},$current_week->{month},$current_week->{year},1,'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;