httemplate/graph/money_time-graph.cgi

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