+sub mail : Local {
+ my ( $self, $c ) = @_;
+ my $dbh = $c->model;
+
+ $c->stash(ok => $c->flash->{ok});
+ $c->stash(error => $c->flash->{error});
+ $c->stash(subject => $c->flash->{subject});
+ $c->stash(message => $c->flash->{message});
+
+ my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid <> '' ORDER BY gid});
+ $groups->execute;
+ my @groups;
+ push @groups,{gid => -1, groupname => 'Pick a group'};
+ while (my $group = $groups->fetchrow_hashref){
+ push @groups,$group;
+ }
+ $c->stash(groups => \@groups);
+}
+
+sub postmail : Local {
+ my ( $self, $c ) = @_;
+ my $dbh = $c->model;
+
+ my $group = $c->req->param('group');
+ my $emails = $dbh->prepare(q{SELECT email FROM users
+ WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = $1)
+ AND email is not null});
+ $emails->execute($group);
+ my @emails;
+ while (my $email = $emails->fetchrow_hashref){
+ push @emails,$email->{email};
+ }
+
+ my $subject = $c->req->param('subject');
+ my $message = $c->req->param('message');
+ my %mail = (
+ smtp => 'localhost',
+ BCC => (join ',',@emails),
+ From => 'NewDawn Command <nd@ruin.nu>',
+ 'Content-type' => 'text/plain; charset="UTF-8"',
+ Subject => $subject,
+ Message => $message,
+ );
+
+ if (sendmail %mail) {
+ $c->flash(ok => \@emails);
+ }else {
+ $c->flash(error => $Mail::Sendmail::error);
+ $c->flash(subject => $subject);
+ $c->flash(message => $message);
+ }
+
+ $c->res->redirect($c->uri_for('mail'));
+}
+
+sub sms : Local {
+ my ( $self, $c ) = @_;
+ my $dbh = $c->model;
+
+ $c->stash(ok => $c->flash->{ok});
+ $c->stash(error => $c->flash->{error});
+
+ my $query = $dbh->prepare(q{
+SELECT uid,username FROM users
+WHERE uid > 0 AND sms SIMILAR TO '\+\d+'
+ AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
+ORDER BY username
+ });
+ $query->execute;
+
+ $c->stash(users => $query->fetchall_arrayref({}) );
+
+ my $query = $dbh->prepare(q{
+SELECT u.username AS sender, COALESCE(r.username,'unknown?') AS receiver, number,
+ message, status, cost, to_char(time, 'YYYY-MM-DD HH24:MI:SS') AS time
+FROM users u
+ JOIN sms s USING (uid)
+ LEFT JOIN users r ON r.sms = '+' || s.number
+WHERE time > now() - '2 weeks'::interval
+ORDER BY time desc
+ });
+ $query->execute;
+ $c->stash(sms => $query->fetchall_arrayref({}));
+
+ $c->stash(credits => $dbh->selectrow_array(q{SELECT credits FROM clickatell}));
+}
+
+sub postsms : Local {
+ my ( $self, $c ) = @_;
+ my $dbh = $c->model;
+
+ $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
+ unless ref $c->req->parameters->{uid} eq 'ARRAY';
+ my $message = $c->req->param('message');
+
+ my $query = $dbh->prepare(q{INSERT INTO sms (uid,message,number)
+ (SELECT $1,$2, trim(leading '+' FROM sms) FROM users u WHERE uid = ANY ($3) AND sms SIMILAR TO '\+\d+' )});
+
+ $query->execute($c->user->id,$message,$c->req->parameters->{uid});
+
+ $c->res->redirect($c->uri_for('sms'));
+}
+