From: Michael Andreen <harv@ruin.nu>
Date: Sun, 31 May 2009 21:39:38 +0000 (+0200)
Subject: Infrastructure to handle sms relaying.
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=f85a252b56bf58c0280d5484397ac8b69dcbdc29

Infrastructure to handle sms relaying.
---

diff --git a/database/sms.sql b/database/sms.sql
new file mode 100644
index 0000000..f1d866a
--- /dev/null
+++ b/database/sms.sql
@@ -0,0 +1,23 @@
+DROP TABLE IF EXISTS sms;
+CREATE TABLE sms (
+	id SERIAL PRIMARY KEY,
+	msgid TEXT UNIQUE,
+	uid INTEGER NOT NULL REFERENCES users(uid),
+	status TEXT NOT NULL DEFAULT 'Waiting',
+	number TEXT NOT NULL,
+	message VARCHAR(140) NOT NULL,
+	cost NUMERIC(4,2) NOT NULL DEFAULT 0,
+	time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX sms_status_msgid_idx ON sms (status) WHERE msgid IS NULL;
+
+DROP TABLE IF EXISTS clickatell;
+
+CREATE TABLE clickatell (
+	api_id TEXT NOT NULL,
+	username TEXT NOT NULL,
+	password TEXT NOT NULL,
+	PRIMARY KEY (api_id, username)
+);
+
diff --git a/lib/NDWeb/Controller/Root.pm b/lib/NDWeb/Controller/Root.pm
index df700f9..a6bb56a 100644
--- a/lib/NDWeb/Controller/Root.pm
+++ b/lib/NDWeb/Controller/Root.pm
@@ -75,6 +75,41 @@ sub logout : Local {
 	$c->res->redirect($c->uri_for('index'));
 }
 
+my %clickatellstatus = (
+	"001", "Message unknown. The delivering network did not recognise the message type or content.",
+	"002", "Message queued. The message could not be delivered and has been queued for attempted redelivery.",
+	"003", "Delivered. Delivered to the network or gateway (delivered to the recipient).",
+	"004", "Received by recipient. Confirmation of receipt on the handset of the recipient.",
+	"005", "Error with message. There was an error with the message, probably caused by the content of the message itself.",
+	"006", "User cancelled message delivery. Client cancelled the message by setting the validity period, or the message was terminated by an internal mechanism.",
+	"007", "Error delivering message An error occurred delivering the message to the handset.",
+	"008", " OK. Message received by gateway.",
+	"009", "Routing error. The routing gateway or network has had an error routing the message.",
+	"010", "Message expired. Message has expired at the network due to the handset being off, or out of reach.",
+	"011", "Message queued for later delivery. Message has been queued at the Clickatell gateway for delivery at a later time (delayed delivery).",
+	"012", "Out of credit. The message cannot be delivered due to a lack of funds in your account. Please re-purchase credits."
+);
+
+
+sub smsconfirm : Local {
+	my ($self, $c) = @_;
+	my $dbh = $c->model;
+
+	my $sms = $dbh->prepare(q{
+UPDATE sms SET status = $2, cost = $3
+	,time = TIMESTAMP WITH TIME ZONE 'epoch' + $4 * INTERVAL '1 second'
+WHERE msgid = $1
+		});
+
+	$sms->execute($c->req->param('apiMsgId')
+		,$clickatellstatus{$c->req->param('status')}
+		,$c->req->param('charge')
+		,$c->req->param('timestamp'));
+
+	$c->stash(template => 'default.tt2');
+}
+
+
 sub begin : Private {
 	my ($self, $c) = @_;
 
diff --git a/scripts/sms.pl b/scripts/sms.pl
new file mode 100755
index 0000000..aef5c7d
--- /dev/null
+++ b/scripts/sms.pl
@@ -0,0 +1,81 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use feature ':5.10';
+
+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,
+);
+
+$dbh->begin_work;
+my $sms = $dbh->prepare(q{
+SELECT * FROM sms WHERE msgid IS NULL AND status = 'Waiting' FOR UPDATE
+});
+
+my $update = $dbh->prepare(q{
+UPDATE sms SET msgid = $2, status = $3, time = NOW() WHERE id = $1
+});
+
+$sms->execute;
+eval{
+	$dbh->pg_savepoint('sms') or die "Couldn't save";
+	while(my $msg = $sms->fetchrow_hashref){
+		$dbh->pg_release('sms') or die "Couldn't save";
+		$dbh->pg_savepoint('sms') or die "Couldn't save";
+
+		my %tags = (%tags,
+			to => $msg->{number},
+			text => encode("latin1",$msg->{message}),
+		);
+
+		my $res = $ua->request(
+			POST 'http://api.clickatell.com/http/sendmsg',
+			Content_Type  => 'application/x-www-form-urlencoded',
+			Content       => [ %tags ]
+		);
+
+		given ($res->content){
+			when(/^ID: (\S+)/){
+				$update->execute($msg->{id},$1,'Sent');
+			}
+			when(/^ERR: (?:302|128|114|113|105),(.*)/){
+				$update->execute($msg->{id},undef,$1);
+			}
+			default {
+				die $_;
+			}
+		}
+	}
+};
+
+if ($@){
+	warn $@;
+	$dbh->pg_rollback_to('sms') or die "rollback didn't work";
+}
+
+$dbh->commit;