From: ivan <ivan>
Date: Sat, 2 Nov 2002 00:13:33 +0000 (+0000)
Subject: whew, glad i had a copy of this
X-Git-Tag: freeside_1_5_0pre1~151
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=dfaabf0c82291f2839922065aa80b2590bab25b0

whew, glad i had a copy of this
---

diff --git a/httemplate/graph/money_time-graph.cgi b/httemplate/graph/money_time-graph.cgi
new file mode 100755
index 000000000..93a80d165
--- /dev/null
+++ b/httemplate/graph/money_time-graph.cgi
@@ -0,0 +1,96 @@
+<%
+
+#find first month
+my $syear = 2001;
+my $smonth = 8;
+
+#find last month
+my $eyear = 2002;
+my $emonth = 11;
+
+my @labels;
+my %data;
+
+while ( $syear < $eyear || ( $syear == $eyear && $smonth < $emonth ) ) {
+  push @labels, "$smonth/$syear";
+
+  my $speriod = timelocal(0,0,0,1,$smonth-1,$syear);
+  if ( ++$smonth == 13 ) { $syear++; $smonth=1; }
+  my $eperiod = timelocal(0,0,0,1,$smonth-1,$syear);
+
+  my $where = "WHERE _date >= $speriod AND _date < $eperiod";
+
+  # Invoiced
+  my $charged_sql = "SELECT SUM(charged) FROM cust_bill $where";
+  my $charged_sth = dbh->prepare($charged_sql) or die dbh->errstr;
+  $charged_sth->execute or die $charged_sth->errstr;
+  my $charged = $charged_sth->fetchrow_arrayref->[0] || 0;
+
+  push @{$data{charged}}, $charged;
+
+  #accounts receivable
+#  my $ar_sql2 = "SELECT SUM(amount) FROM cust_credit $where";
+  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";
+  my $credited_sth = dbh->prepare($credited_sql) or die dbh->errstr;
+  $credited_sth->execute or die $credited_sth->errstr;
+  my $credited = $credited_sth->fetchrow_arrayref->[0] || 0;
+
+    #horrible local kludge
+    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 _%'";
+    my $expenses_sth = dbh->prepare($expenses_sql) or die dbh->errstr;
+    $expenses_sth->execute or die $expenses_sth->errstr;
+    my $expenses = $expenses_sth->fetchrow_arrayref->[0] || 0;
+
+  push @{$data{ar}}, $charged-$credited-$expenses;
+
+  #deferred revenue
+#  push @{$data{defer}}, '0';
+
+  #cashflow
+  my $paid_sql = "SELECT SUM(paid) FROM cust_pay $where";
+  my $paid_sth = dbh->prepare($paid_sql) or die dbh->errstr;
+  $paid_sth->execute or die $paid_sth->errstr;
+  my $paid = $paid_sth->fetchrow_arrayref->[0] || 0;
+
+  my $refunded_sql = "SELECT SUM(refund) FROM cust_refund $where";
+  my $refunded_sth = dbh->prepare($refunded_sql) or die dbh->errstr;
+  $refunded_sth->execute or die $refunded_sth->errstr;
+  my $refunded = $refunded_sth->fetchrow_arrayref->[0] || 0;
+
+  push @{$data{cash}}, $paid-$refunded;
+
+}
+
+#my $chart = Chart::LinesPoints->new(1024,480);
+my $chart = Chart::LinesPoints->new(768,480);
+
+$chart->set(
+  #'min_val' => 0,
+  'legend' => 'bottom',
+  'legend_labels' => [ #'Invoiced (cust_bill)',
+                       'Accounts receivable (invoices - applied credits)',
+                       #'Deferred revenue',
+                       'Actual cashflow (payments - refunds)' ],
+);
+
+my @data = ( \@labels,
+             #map $data{$_}, qw( ar defer cash )
+             #map $data{$_}, qw( charged ar cash )
+             map $data{$_}, qw( ar cash )
+           );
+
+#my $gd = $chart->plot(\@data);
+#open (IMG, ">i_r_c.png");
+#print IMG $gd->png;
+#close IMG;
+
+#$chart->png("i_r_c.png", \@data);
+
+#$chart->cgi_png(\@data);
+
+http_header('Content-Type' => 'image/png' );
+$Response->{ContentType} = 'image/png';
+
+$chart->_set_colors();
+
+%><%= $chart->scalar_png(\@data) %>
diff --git a/httemplate/graph/money_time.cgi b/httemplate/graph/money_time.cgi
new file mode 100644
index 000000000..d6c35434b
--- /dev/null
+++ b/httemplate/graph/money_time.cgi
@@ -0,0 +1,43 @@
+<HTML>
+  <HEAD>
+    <TITLE>Graphing monetary values over time</TITLE>
+  </HEAD>
+<BODY BGCOLOR="#e8e8e8">
+<IMG SRC="money_time-graph.cgi" WIDTH="768" HEIGHT="480">
+<BR>
+<FORM METHOD="POST">
+<INPUT TYPE="checkbox" NAME="ar">
+  Accounts receivable (invoices - applied credits)<BR>
+<INPUT TYPE="checkbox" NAME="charged">
+  Just Invoices<BR>
+<INPUT TYPE="checkbox" NAME="defer">
+  Accounts receivable, with deferred revenue (invoices - applied credits, with charges for annual/semi-annual/quarterly/etc. services deferred over applicable time period) (there has got to be a shorter description for this)<BR>
+<INPUT TYPE="checkbox" NAME="cash">
+  Cashflow (payments - refunds)<BR>
+<BR>
+From <SELECT NAME="smonth">
+<% my @m = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
+   foreach my $m ( 1..12 ) { %>
+<OPTION VALUE="<%= $m %>"><%= $m[$m-1] %>
+<% } %>
+</SELECT>
+<SELECT NAME="syear">
+<% foreach my $y ( 1999 .. 2010 ) { %>
+<OPTION VALUE="<%= $y %>"><%= $y %>
+<% } %>
+</SELECT>
+ to <SELECT NAME="emonth">
+<% foreach my $m ( 1..12 ) { %>
+<OPTION VALUE="<%= $m %>"><%= $m[$m-1] %>
+<% } %>
+</SELECT>
+<SELECT NAME="eyear">
+<% foreach my $y ( 1999 .. 2010 ) { %>
+<OPTION VALUE="<%= $y %>"><%= $y %>
+<% } %>
+</SELECT>
+
+<INPUT TYPE="submit" VALUE="Graph">
+</FORM>
+</BODY>
+</HTML>