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