From: Michael Andreen <harv@ruin.nu>
Date: Tue, 17 Jan 2017 21:39:58 +0000 (+0100)
Subject: Keep track of sms credits
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=a5b1d55407699bd24bede5cce84c4b5d434a724d

Keep track of sms credits
---

diff --git a/database/functions/sms_credits.sql b/database/functions/sms_credits.sql
new file mode 100644
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
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 $_;
+	}
+}