C4/Accounts.pm

   1 package C4::Accounts; #asummes C4/Accounts
   2
   3 #requires DBI.pm to be installed
   4 #uses DBD:Pg
   5
   6 use strict;
   7 require Exporter;
   8 use DBI;
   9 use C4::Database;
  10 use C4::Format;
  11 use C4::Search;
  12 use C4::Stats;
  13 use C4::InterfaceCDK;
  14 use C4::Interface::AccountsCDK;
  15 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  16
  17 # set the version for version checking
  18 $VERSION = 0.01;
  19
  20 @ISA = qw(Exporter);
  21 @EXPORT = qw(&checkaccount &reconcileaccount &getnextacctno);
  22 %EXPORT_TAGS = ( );     # eg: TAG => [ qw!name1 name2! ],
  23
  24 # your exported package globals go here,
  25 # as well as any optionally exported functions
  26
  27 @EXPORT_OK   = qw($Var1 %Hashit);
  28
  29
  30 # non-exported package globals go here
  31 use vars qw(@more $stuff);
  32
  33 # initalize package globals, first exported ones
  34
  35 my $Var1   = '';
  36 my %Hashit = ();
  37
  38 # then the others (which are still accessible as $Some::Module::stuff)
  39 my $stuff  = '';
  40 my @more   = ();
  41
  42 # all file-scoped lexicals must be created before
  43 # the functions below that use them.
  44
  45 # file-private lexicals go here
  46 my $priv_var    = '';
  47 my %secret_hash = ();
  48
  49 # here's a file-private function as a closure,
  50 # callable as &$priv_func;  it cannot be prototyped.
  51 my $priv_func = sub {
  52   # stuff goes here.
  53 };
  54
  55 # make all your functions, whether exported or not;
  56
  57 sub displayaccounts{
  58   my ($env)=@_;
  59 }
  60
  61 sub checkaccount  {
  62   #take borrower number
  63   #check accounts and list amounts owing
  64   my ($env,$bornumber,$dbh)=@_;
  65   my $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
  66   borrowernumber=$bornumber and amountoutstanding<>0");
  67   $sth->execute;
  68   my $total=0;
  69   while (my $data=$sth->fetchrow_hashref){
  70     $total=$total+$data->{'sum(amountoutstanding)'};
  71   }
  72   $sth->finish;
  73   # output(1,2,"borrower owes $total");
  74   #if ($total > 0){
  75   #  # output(1,2,"borrower owes $total");
  76   #  if ($total > 5){
  77   #    reconcileaccount($env,$dbh,$bornumber,$total);
  78   #  }
  79   #}
  80   #  pause();
  81   return($total);
  82 }
  83
  84 sub reconcileaccount {
  85   #print put money owing give person opportunity to pay it off
  86   my ($env,$dummy,$bornumber,$total)=@_;
  87   my $dbh = &C4Connect;
  88   #get borrower record
  89   my $sth=$dbh->prepare("select * from borrowers
  90     where borrowernumber=$bornumber");
  91   $sth->execute;
  92   my $borrower=$sth->fetchrow_hashref;
  93   $sth->finish();
  94   #get borrower information
  95   $sth=$dbh->prepare("Select * from accountlines where
  96   borrowernumber=$bornumber and amountoutstanding<>0 order by date");
  97   $sth->execute;
  98   #display account information
  99   &clearscreen();
 100   #&helptext('F11 quits');
 101   output(20,0,"Accounts");
 102   my @accountlines;
 103   my $row=4;
 104   my $i=0;
 105   my $text;
 106   #output (1,2,"Account Info");
 107   #output (1,3,"Item\tDate      \tAmount\tDescription");
 108   while (my $data=$sth->fetchrow_hashref){
 109     my $line=$i+1;
 110     my $amount=0+$data->{'amountoutstanding'};
 111     my $itemdata = itemnodata($env,$dbh,$data->{'itemnumber'});
 112     $line= $data->{'accountno'}." ".$data->{'date'}." ".$data->{'accounttype'}." ";
 113     my $title = $itemdata->{'title'};
 114     if (length($title) > 15 ) {$title = substr($title,0,15);}
 115     $line= $line.$itemdata->{'barcode'}." $title ".$data->{'description'};
 116     $line = fmtstr($env,$line,"L65")." ".fmtdec($env,$amount,"52");
 117     push @accountlines,$line;
 118     $i++;
 119   }
 120   #get amount paid and update database
 121   my ($data,$reason)=
 122     &accountsdialog($env,"Payment Entry",$borrower,\@accountlines,$total);
 123   if ($data>0) {
 124     &recordpayment($env,$bornumber,$dbh,$data);
 125     #Check if the borrower still owes
 126     $total=&checkaccount($env,$bornumber,$dbh);
 127   }
 128   $dbh->disconnect;
 129   return($total);
 130
 131 }
 132
 133 sub recordpayment{
 134   #here we update both the accountoffsets and the account lines
 135   my ($env,$bornumber,$dbh,$data)=@_;
 136   my $updquery = "";
 137   my $newamtos = 0;
 138   my $accdata = "";
 139   my $amountleft = $data;
 140   # begin transaction
 141 #  my $sth = $dbh->prepare("begin");
 142 #  $sth->execute;
 143   my $nextaccntno = getnextacctno($env,$bornumber,$dbh);
 144   # get lines with outstanding amounts to offset
 145   my $query = "select * from accountlines
 146   where (borrowernumber = '$bornumber') and (amountoutstanding<>0)
 147   order by date";
 148   my $sth = $dbh->prepare($query);
 149   $sth->execute;
 150   # offset transactions
 151   while (($accdata=$sth->fetchrow_hashref) and ($amountleft>0)){
 152      if ($accdata->{'amountoutstanding'} < $amountleft) {
 153         $newamtos = 0;
 154         $amountleft = $amountleft - $accdata->{'amountoutstanding'};
 155      }  else {
 156         $newamtos = $accdata->{'amountoutstanding'} - $amountleft;
 157         $amountleft = 0;
 158      }
 159      my $thisacct = $accdata->{accountno};
 160      $updquery = "update accountlines set amountoutstanding= '$newamtos'
 161      where (borrowernumber = '$bornumber') and (accountno='$thisacct')";
 162      my $usth = $dbh->prepare($updquery);
 163      $usth->execute;
 164      $usth->finish;
 165      $updquery = "insert into accountoffsets
 166      (borrowernumber, accountno, offsetaccount,  offsetamount)
 167      values ($bornumber,$accdata->{'accountno'},$nextaccntno,$newamtos)";
 168      my $usth = $dbh->prepare($updquery);
 169 #     print $updquery
 170      $usth->execute;
 171      $usth->finish;
 172   }
 173   # create new line
 174   #$updquery = "insert into accountlines (borrowernumber,
 175   #accountno,date,amount,description,accounttype,amountoutstanding) values
 176   #($bornumber,$nextaccntno,datetime('now'::abstime),0-$data,'Payment,thanks',
 177   #'Pay',0-$amountleft)";
 178   $updquery = "insert into accountlines
 179   (borrowernumber, accountno,date,amount,description,accounttype,amountoutstanding)
 180   values ($bornumber,$nextaccntno,now(),0-$data,'Payment,thanks',
 181   'Pay',0-$amountleft)";
 182   my $usth = $dbh->prepare($updquery);
 183   $usth->execute;
 184   $usth->finish;
 185   UpdateStats($env,'branch','payment',$data)
 186 #  $sth->finish;
 187 #  $query = "commit";
 188 #  $sth = $dbh->prepare;
 189 #  $sth->execute;
 190 #  $sth-finish;
 191 }
 192
 193 sub getnextacctno {
 194   my ($env,$bornumber,$dbh)=@_;
 195   my $nextaccntno = 1;
 196   my $query = "select * from accountlines
 197   where (borrowernumber = '$bornumber')
 198   order by accountno desc";
 199   my $sth = $dbh->prepare($query);
 200   $sth->execute;
 201   if (my $accdata=$sth->fetchrow_hashref){
 202     $nextaccntno = $accdata->{'accountno'} + 1;
 203   }
 204   $sth->finish;
 205   return($nextaccntno);
 206 }
 207
 208 END { }       # module clean-up code here (global destructor)