[Astpp-commit] SF.net SVN: astpp:[2253] trunk

Revision: 2253
          http://astpp.svn.sourceforge.net/astpp/?rev=2253&view=rev
Author:   darrenkw
Date:     2009-09-19 23:53:41 +0000 (Sat, 19 Sep 2009)

Log Message:
-----------
This adds support for internal invoice generation. This upgrade will require a few manual database upgrades.  I am not committing invoice presentation yet.

Modified Paths:
--------------
    trunk/modules/ASTPP/lib/ASTPP.pm
    trunk/scripts/astpp-common.pl
    trunk/scripts/astpp-generate-invoices.pl
    trunk/sql/astpp-2009-09-19.sql

Modified: trunk/modules/ASTPP/lib/ASTPP.pm
===================================================================
--- trunk/modules/ASTPP/lib/ASTPP.pm	2009-09-19 18:53:05 UTC (rev 2252)
+++ trunk/modules/ASTPP/lib/ASTPP.pm	2009-09-19 23:53:41 UTC (rev 2253)
@@ -517,7 +517,217 @@
 	return @pricelistlist;
 }
 
+sub invoice_cdrs
+# Function 1 = count cdrs
+# Function 2 = return crds
+# Function 3 = Internal Invoices, Post CDRs.
+{
+        my ($self, %arg) = @_; #Count the cdrs billable on a specific account
+	my $tmp;
+	if ($arg{function} == 1) {
+		$tmp = "SELECT COUNT(*) FROM cdrs WHERE cardnum = ";
+	}
+	elsif ($arg{function} == 2) {
+		$tmp = "SELECT * FROM cdrs WHERE cardnum = ";
+	}
+	elsif ($arg{function} == 3) {
+		$tmp = "UPDATE cdrs SET invoiceid = "
+		. $self->{_astpp_db}->quote($arg{invoiceid})
+		. ",status = 1 "
+		. " WHERE cardnum = ";
+	}
+	if ($arg{startdate} && $arg{enddate}) {
+		$tmp .= $self->{_astpp_db}->quote($arg{cardnum})
+		. " AND status = 0"
+		. " AND callstart >= DATE(" . $self->{_astpp_db}->quote($arg{startdate}) . ")"
+		. " AND callstart <= DATE(" . $self->{_astpp_db}->quote($arg{enddate}) . ")";
+	} elsif ($arg{startdate}) {
+		$tmp .= $self->{_astpp_db}->quote($arg{cardnum})
+		. " AND status = 0"
+		. " AND callstart >= DATE(" . $self->{_astpp_db}->quote($arg{startdate}) . ")";
+	} elsif ($arg{enddate}) {
+		$tmp .= $self->{_astpp_db}->quote($arg{cardnum})
+		. " AND status = 0"
+		. " AND callstart <= DATE(" . $self->{_astpp_db}->quote($arg{enddate}) . ")";
+	} else {
+		$tmp .= $self->{_astpp_db}->quote($arg{cardnum})
+		. " AND status = 0";
+	}
+	if ($arg{function} == 2) {
+		$tmp .= " GROUP BY type ORDER BY callstart";
+	}
 
+	print STDERR "$tmp \n";
+	my $sql = $self->{_astpp_db}->prepare($tmp);
+	$sql->execute;
+
+	if ($arg{function} == 1) {
+		my $row       = $sql->fetchrow_hashref;
+		$sql->finish;
+		return(
+			$row->{"COUNT(*)"}
+		);
+	}
+	elsif ($arg{function} == 2) {
+		my @cdrs;
+                while ( my $record = $sql->fetchrow_hashref ) {
+                        push @cdrs, $record;
+                }
+		$sql->finish;
+		return(
+			@cdrs
+		);
+	}
+}
+
+sub invoice_create_internal
+{
+        my ($self, %arg) = @_; # Create invoice in ASTPP Internally and return the invoice number.
+	my $tmp = "INSERT into invoices (accountid,date) VALUES("
+		. $self->{_astpp_db}->quote($arg{accountid})
+		. ",curdate())";
+	my $sql = $self->{_astpp_db}->prepare($tmp);
+	$sql->execute;
+	my $invoice = $sql->{'mysql_insertid'};
+	$sql->finish;
+	return (
+		$invoice
+	);
+}
+
+sub invoice_cdrs_subtotal_internal 
+{
+        my ($self, %arg) = @_; # Create invoice in ASTPP Internally and return the invoice number.
+	my ($tmp,$row,$sql,$credit,$debit,$total);
+	$tmp = "SELECT SUM(debit) FROM cdrs WHERE invoiceid = "
+		. $self->{_astpp_db}->quote($arg{invoiceid});
+	$sql = $self->{_astpp_db}->prepare($tmp);
+	$sql->execute;
+	$row   = $sql->fetchrow_hashref;
+	$debit = $row->{"SUM(debit)"};
+	$sql->finish;
+	$tmp = "SELECT SUM(credit) FROM cdrs WHERE invoiceid = "
+		. $self->{_astpp_db}->quote($arg{invoiceid});
+	$sql = $self->{_astpp_db}->prepare($tmp);
+	$sql->execute;
+	$row   = $sql->fetchrow_hashref;
+	$credit = $row->{"SUM(credit)"};
+	$sql->finish;
+	if ( !$credit )         { $credit         = 0; }
+	if ( !$debit )          { $debit          = 0; }
+	$total = ( $debit - $credit );
+	return ($total/10000);
+
+#	$tmp = "INSERT into invoices_total (invoiceid,title,text,value,class,sort_order) VALUES("
+#		. $self->{_astpp_db}->quote($arg{invoiceid})
+#		. ",'Subtotal','',"
+#		. $self->{_astpp_db}->quote($total/10000)
+#		. ",1,"
+#		. $self->{_astpp_db}->quote($arg{sort_order})
+#		. ")";
+#	$sql = $ $self->{_astpp_db}->prepare($tmp);
+#	$sql->execute;
+#	return $arg{sort_order}++;
+}
+
+sub invoice_subtotal_post_internal
+{
+	my ($self, %arg) = @_; 
+        $arg{value} = sprintf( "%." . $arg{decimalpoints_total} . "f", $arg{value} );
+	my $tmp = "INSERT into invoices_total (invoices_id,title,text,value,class,sort_order) VALUES("
+		. $self->{_astpp_db}->quote($arg{invoiceid})
+		. ","
+		. $self->{_astpp_db}->quote($arg{title})
+		. ","
+		. $self->{_astpp_db}->quote($arg{text})
+		. ","
+		. $self->{_astpp_db}->quote($arg{value})
+		. ","
+		. $self->{_astpp_db}->quote($arg{class})
+		. ","
+		. $self->{_astpp_db}->quote($arg{sort_order})
+		. ")";
+	my $sql = $self->{_astpp_db}->prepare($tmp);
+	$sql->execute;
+	return $arg{sort_order}++;
+}
+
+sub invoice_subtotal_internal
+{
+	my ($self, %arg) = @_; 
+        my $tmp = "SELECT SUM(value) FROM invoices_total WHERE invoices_id = "
+                . $self->{_astpp_db}->quote($arg{invoiceid});
+        my $sql = $self->{_astpp_db}->prepare($tmp);
+        $sql->execute;
+        my $row   = $sql->fetchrow_hashref;
+        my $value = $row->{"SUM(value)"};
+	$sql->finish;
+	return $value;
+}
+
+sub invoice_taxes_internal
+# function 1 = list
+# function 2 = post
+{
+        my ($self, %arg) = @_; # Create invoice in ASTPP Internally and return the invoice number.
+	my (@taxes,$row,$tmp,$sql);
+	$tmp = "SELECT * FROM taxes_to_accounts_view WHERE accountid = "
+		. $self->{_astpp_db}->quote($arg{accountid})
+		. " ORDER BY taxes_priority ASC";
+	$sql = $self->{_astpp_db}->prepare($tmp);
+	print STDERR $tmp . "/n";
+	$sql->execute;
+	while ( $row = $sql->fetchrow_hashref ) {
+		push @taxes, $row;
+	}
+	$sql->finish;
+	if ($arg{function} == 1) {
+		return @taxes;
+	}
+	my $tax_count = 1;
+	my $sort = 1;
+	my $tax_priority = "";
+	my $subtotal = $arg{invoice_subtotal};
+        foreach my $tax (@taxes) {
+		my ($tax_amount);
+		if ($tax_priority eq "") {
+			$tax_priority = $tax->{taxes_priority};
+		} elsif($tax->{taxes_priority} > $tax_priority) {
+			$tax_priority = $tax->{taxes_priority};
+		        my $tmp = "SELECT SUM(value) FROM invoices_total WHERE invoices_id = "
+		                . $self->{_astpp_db}->quote($arg{invoiceid});
+			print STDERR $tmp . "\n";
+		        my $sql = $self->{_astpp_db}->prepare($tmp);
+		        $sql->execute;
+		        my $row   = $sql->fetchrow_hashref;
+		        $subtotal = $row->{"SUM(value)"};
+			$sql->finish;
+		}
+		print STDERR "Subtotal: $subtotal \n";
+		print STDERR "Tax_rate: $tax->{taxes_rate} \n";
+                my $tax_total = (($subtotal * ( $tax->{taxes_rate} / 100 )) + $tax->{taxes_amount} );
+		print STDERR "Tax Total: $tax_total \n";
+		print STDERR "Round to: $arg{decimalpoints_tax} \n";
+                $tax_total = sprintf( "%." . $arg{decimalpoints_tax} . "f", $tax_total );
+		print STDERR "Tax Total: $tax_total \n";
+		my $tmp = "INSERT INTO invoices_total (invoices_id,title,text,value,class,sort_order) VALUES("
+		. $self->{_astpp_db}->quote($arg{invoiceid})
+		. ",'TAX',"
+		. $self->{_astpp_db}->quote($tax->{taxes_description})
+		. ","
+		. $self->{_astpp_db}->quote($tax_total)
+		. ",2,"
+		. $self->{_astpp_db}->quote($arg{sort_order})
+		. ")";
+		print STDERR $tmp . "\n";
+		my $sql = $self->{_astpp_db}->prepare($tmp);
+		$sql->execute;
+
+                $arg{sort_order}++;
+        }
+	return $arg{sort_order};
+}
+
 # Preloaded methods go here.
 
 1;

Modified: trunk/scripts/astpp-common.pl
===================================================================
--- trunk/scripts/astpp-common.pl	2009-09-19 18:53:05 UTC (rev 2252)
+++ trunk/scripts/astpp-common.pl	2009-09-19 23:53:41 UTC (rev 2253)
@@ -999,7 +999,7 @@
         $dsn =
           "DBI:Pg:database=$config->{cdr_dbname};host=$config->{cdr_dbhost}";
     }
-    print STDERR $dsn if $config->{debug} == 1;
+    print STDERR $dsn . "\n" if $config->{debug} == 1;
     $dbh = DBI->connect( $dsn, $config->{cdr_dbuser}, $config->{cdr_dbpass} );
     if ( !$dbh ) {
         print STDERR "CDR DATABASE IS DOWN\n";
@@ -3999,6 +3999,7 @@
 	else {
 		$outfile = $config->{csv_dir} . $reseller . ".csv";
 	}
+	$outfile = "/var/log/astpp/astpp.csv" if !$outfile;
 	my $notes = "Notes: " . $cdrinfo->{accountcode};
 	open( OUTFILE, ">>$outfile" )
 	  || die "CSV Error - could not open $outfile for writing\n";

Modified: trunk/scripts/astpp-generate-invoices.pl
===================================================================
--- trunk/scripts/astpp-generate-invoices.pl	2009-09-19 18:53:05 UTC (rev 2252)
+++ trunk/scripts/astpp-generate-invoices.pl	2009-09-19 23:53:41 UTC (rev 2253)
@@ -33,6 +33,7 @@
 use Locale::Country;
 use Locale::gettext_pp qw(:locale_h);
 use Data::Dumper;
+use ASTPP;
 
 #use strict;
 use lib './lib', '../lib';
@@ -44,7 +45,10 @@
 use vars qw($config $astpp_db $osc_db $agile_db $cdr_db
   @output @cardlist $config $params);
 @output = ( "STDOUT", "LOGFILE" );
+$ASTPP     = ASTPP->new;
+$ASTPP->set_verbosity(4);    #Tell ASTPP debugging how verbose we want to be.
 
+
 sub initialize() {
     $config     = &load_config();
     $astpp_db = &connect_db( $config,     @output );
@@ -54,6 +58,7 @@
       if $config->{externalbill} eq "oscommerce";
     open( LOGFILE, ">>$config->{log_file}" )
       || die "Error - could not open $config->{log_file} for writing\n";
+    $ASTPP->set_astpp_db($astpp_db);
 }
 
 sub shutdown() {
@@ -84,6 +89,73 @@
 	}
     }
 }
+elsif ( $config->{externalbill} eq "internal" ) {
+    my @cardlist;
+    if ( $params->{sweep} ) {
+        @cardlist = &update_list_cards($astpp_db, $config, $params->{sweep} );
+    }
+    foreach (@cardlist) {
+        my $cardno = $_;
+        my $carddata = &get_account( $astpp_db, $cardno );
+        if ( $carddata->{posttoexternal} == 1 ) {
+	        foreach my $handle (@output) {
+      		      print $handle "Card: $cardno \n";
+   		}
+		my $cdr_count = $ASTPP->invoice_cdrs(
+			accountid	=> $carddata->{accountid},
+			cardnum		=> $carddata->{number},
+			function	=> 1
+			);
+		if ($cdr_count > 0) {
+		my $invoice = $ASTPP->invoice_create_internal(
+			accountid	=> $carddata->{accountid}
+			);
+		$ASTPP->invoice_cdrs(
+			accountid	=> $carddata->{accountid},
+			cardnum		=> $carddata->{number},
+			invoiceid	=> $invoice,
+			function	=> 3
+			);
+
+		my $sort_order = 1;
+
+		my $subtotal = $ASTPP->invoice_cdrs_subtotal_internal(
+			invoiceid	=> $invoice
+			);
+		$sort_order = $ASTPP->invoice_subtotal_post_internal(
+			decimalpoints_total => $config->{decimalpoints_total},
+			invoiceid	=> $invoice,
+			sort_order	=> $sort_order,
+			value 		=> $subtotal,
+			title		=> "Sub Total",
+			text		=> "Sub Total",
+			class		=> "1"
+			);
+		$sort_order = $ASTPP->invoice_taxes_internal(
+			accountid	=> $carddata->{accountid},
+			invoiceid	=> $invoice,
+			sort_order	=> $sort_order,
+			function	=> 2,
+			decimalpoints_tax => $config->{decimalpoints_tax},
+			decimalpoints_total => $config->{decimalpoints_total},
+			invoice_subtotal => $subtotal
+			);
+		$subtotal = $ASTPP->invoice_subtotal_internal(
+			invoiceid	=> $invoice
+			);
+		$sort_order = $ASTPP->invoice_subtotal_post_internal(
+			decimalpoints_total => $config->{decimalpoints_total},
+			invoiceid	=> $invoice,
+			sort_order	=> $sort_order,
+			value 		=> $subtotal,
+			title		=> "Total",
+			text		=> "Total",
+			class		=> "1"
+			);
+		}
+	}
+    }
+}
 elsif ( $config->{externalbill} eq "agile" ) {
     my @cardlist = &list_cards($astpp_db);
     foreach my $cardno (@cardlist) {

Modified: trunk/sql/astpp-2009-09-19.sql
===================================================================
--- trunk/sql/astpp-2009-09-19.sql	2009-09-19 18:53:05 UTC (rev 2252)
+++ trunk/sql/astpp-2009-09-19.sql	2009-09-19 23:53:41 UTC (rev 2253)
@@ -172,7 +172,8 @@
 dialed_modify TEXT NOT NULL DEFAULT '',
 type INTEGER DEFAULT 0,
 tz CHAR(40) NOT NULL DEFAULT '',
-PRIMARY KEY  (`number`),
+PRIMARY KEY  (`accountid`),
+  KEY `pricelist` (`number`),
   KEY `pricelist` (`pricelist`),
   KEY `reseller` (`reseller`)
 );
@@ -348,6 +349,9 @@
 
 
 INSERT INTO system (name, value, comment) VALUES (
+'log_file','/var/log/astpp/astpp.log','Where do I log to?');
+
+INSERT INTO system (name, value, comment) VALUES (
 'callout_accountcode','admin','Call Files: What accountcode should we use?');
 
 INSERT INTO system (name, value, comment) VALUES (
@@ -1369,3 +1373,16 @@
 `taxes_id` VARCHAR( 11 ) NOT NULL
 ) ENGINE = MYISAM ;
 
+
+;;;; Create Views Here
+;
+CREATE VIEW taxes_to_accounts_view AS SELECT 
+taxes_to_accounts.id, 
+taxes_to_accounts.accountid,  
+taxes.taxes_id, 
+taxes.taxes_priority, 
+taxes.taxes_amount, 
+taxes.taxes_rate, 
+taxes.taxes_description
+FROM taxes_to_accounts, taxes
+WHERE taxes.taxes_id = taxes_to_accounts.taxes_id;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.