#!/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;