Keep track of sms credits

diff --git a/database/functions/sms_credits.sql b/database/functions/sms_credits.sql
new file mode 100644 (file)
index 0000000..5cbc71a
--- /dev/null
+++ b/database/functions/sms_credits.sql
@@ -0,0 +1,15 @@
+CREATE OR REPLACE FUNCTION sms_credits() RETURNS trigger
+    AS $_X$
+BEGIN
+       IF NEW.cost <> OLD.cost
+       THEN
+               UPDATE clickatell SET credits = credits + OLD.cost - NEW.cost;
+       END IF;
+       RETURN NEW;
+END;
+$_X$ LANGUAGE plpgsql;
+
+/*
+ALTER TABLE clickatell ADD COLUMN credits NUMERIC NOT NULL DEFAULT 0.0;
+CREATE TRIGGER sms_credits AFTER UPDATE OF cost ON sms FOR EACH ROW WHEN (NEW.cost <> OLD.cost) EXECUTE PROCEDURE sms_credits();
+*/

diff --git a/scripts/smscredits.pl b/scripts/smscredits.pl
new file mode 100755 (executable)
index 0000000..7085438
--- /dev/null
+++ b/scripts/smscredits.pl
@@ -0,0 +1,56 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use feature ':5.10';
+
+no if $] >= 5.018, warnings => "experimental::smartmatch";
+
+use local::lib;
+
+use Encode;
+
+use LWP::UserAgent;
+use URI::Escape qw(uri_escape);
+use HTTP::Request::Common;
+
+use DBI;
+use DBD::Pg qw(:pg_types);
+
+use FindBin;
+use lib "$FindBin::Bin/../lib";
+
+use ND::DB;
+my $dbh = ND::DB::DB();
+
+my $ua = LWP::UserAgent->new(agent => 'NDWebbie' );
+
+my $click = $dbh->selectrow_hashref(q{
+SELECT api_id,username,password FROM clickatell LIMIT 1
+});
+
+my %tags = (
+       api_id => $click->{api_id},
+       user => $click->{username},
+       password => $click->{password},
+       callback => 3,
+);
+
+my $update = $dbh->prepare(q{
+UPDATE clickatell SET credits = $1
+});
+
+my $res = $ua->request(
+       POST 'http://api.clickatell.com/http/getbalance',
+       Content_Type  => 'application/x-www-form-urlencoded',
+       Content       => [ %tags ]
+);
+
+given ($res->content){
+       when(/^Credit: (\d+\.\d\d)/){
+               $update->execute($1);
+       }
+       default {
+               die $_;
+       }
+}