httemplate/graph/money_time-graph.cgi

   1 <%
   2
   3 #find first month
   4 my $syear = 2001;
   5 my $smonth = 8;
   6
   7 #find last month
   8 my $eyear = 2002;
   9 my $emonth = 11;
  10
  11 my @labels;
  12 my %data;
  13
  14 while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth ) ) {
  15   push @labels, "$smonth/$syear";
  16
  17   my $speriod = timelocal(0,0,0,1,$smonth-1,$syear);
  18   if ( ++$smonth == 13 ) { $syear++; $smonth=1; }
  19   my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
  20
  21   my $where = "WHERE _date >= $speriod AND _date < $eperiod";
  22
  23   # Invoiced
  24   my $charged_sql = "SELECT SUM(charged) FROM cust_bill $where";
  25   my $charged_sth = dbh->prepare($charged_sql) or die dbh->errstr;
  26   $charged_sth->execute or die $charged_sth->errstr;
  27   my $charged = $charged_sth->fetchrow_arrayref->[0] || 0;
  28
  29   push @{$data{charged}}, $charged;
  30
  31   #accounts receivable
  32 #  my $ar_sql2 = "SELECT SUM(amount) FROM cust_credit $where";
  33   my $credited_sql = "SELECT SUM(cust_credit_bill.amount) FROM cust_credit_bill, cust_bill WHERE cust_bill.invnum = cust_credit_bill.invnum AND cust_bill._date >= $speriod AND cust_bill._date < $eperiod";
  34   my $credited_sth = dbh->prepare($credited_sql) or die dbh->errstr;
  35   $credited_sth->execute or die $credited_sth->errstr;
  36   my $credited = $credited_sth->fetchrow_arrayref->[0] || 0;
  37
  38     #horrible local kludge
  39     my $expenses_sql = "SELECT SUM(cust_bill_pkg.setup) FROM cust_bill_pkg, cust_bill, cust_pkg, part_pkg WHERE cust_bill.invnum = cust_bill_pkg.invnum AND cust_bill._date >= $speriod AND cust_bill._date < $eperiod AND cust_pkg.pkgnum = cust_bill_pkg.pkgnum AND cust_pkg.pkgpart = part_pkg.pkgpart AND LOWER(part_pkg.pkg) LIKE 'expense _%'";
  40     my $expenses_sth = dbh->prepare($expenses_sql) or die dbh->errstr;
  41     $expenses_sth->execute or die $expenses_sth->errstr;
  42     my $expenses = $expenses_sth->fetchrow_arrayref->[0] || 0;
  43
  44   push @{$data{ar}}, $charged-$credited-$expenses;
  45
  46   #deferred revenue
  47 #  push @{$data{defer}}, '0';
  48
  49   #cashflow
  50   my $paid_sql = "SELECT SUM(paid) FROM cust_pay $where";
  51   my $paid_sth = dbh->prepare($paid_sql) or die dbh->errstr;
  52   $paid_sth->execute or die $paid_sth->errstr;
  53   my $paid = $paid_sth->fetchrow_arrayref->[0] || 0;
  54
  55   my $refunded_sql = "SELECT SUM(refund) FROM cust_refund $where";
  56   my $refunded_sth = dbh->prepare($refunded_sql) or die dbh->errstr;
  57   $refunded_sth->execute or die $refunded_sth->errstr;
  58   my $refunded = $refunded_sth->fetchrow_arrayref->[0] || 0;
  59
  60   push @{$data{cash}}, $paid-$refunded;
  61
  62 }
  63
  64 #my $chart = Chart::LinesPoints->new(1024,480);
  65 my $chart = Chart::LinesPoints->new(768,480);
  66
  67 $chart->set(
  68   #'min_val' => 0,
  69   'legend' => 'bottom',
  70   'legend_labels' => [ #'Invoiced (cust_bill)',
  71                        'Accounts receivable (invoices - applied credits)',
  72                        #'Deferred revenue',
  73                        'Actual cashflow (payments - refunds)' ],
  74 );
  75
  76 my @data = ( \@labels,
  77              #map $data{$_}, qw( ar defer cash )
  78              #map $data{$_}, qw( charged ar cash )
  79              map $data{$_}, qw( ar cash )
  80            );
  81
  82 #my $gd = $chart->plot(\@data);
  83 #open (IMG, ">i_r_c.png");
  84 #print IMG $gd->png;
  85 #close IMG;
  86
  87 #$chart->png("i_r_c.png", \@data);
  88
  89 #$chart->cgi_png(\@data);
  90
  91 http_header('Content-Type' => 'image/png' );
  92 $Response->{ContentType} = 'image/png';
  93
  94 $chart->_set_colors();
  95
  96 %><%= $chart->scalar_png(\@data) %>