diff options
Diffstat (limited to 'perllib/FixMyStreet/DB')
23 files changed, 2049 insertions, 0 deletions
diff --git a/perllib/FixMyStreet/DB/Result/Abuse.pm b/perllib/FixMyStreet/DB/Result/Abuse.pm new file mode 100644 index 000000000..b1cf9c1ed --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Abuse.pm @@ -0,0 +1,21 @@ +package FixMyStreet::DB::Result::Abuse; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("abuse"); +__PACKAGE__->add_columns("email", { data_type => "text", is_nullable => 0 }); +__PACKAGE__->set_primary_key("email"); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:IuTLiJSDZGLF/WX8q3iKIQ + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/AdminLog.pm b/perllib/FixMyStreet/DB/Result/AdminLog.pm new file mode 100644 index 000000000..da97950a0 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/AdminLog.pm @@ -0,0 +1,44 @@ +package FixMyStreet::DB::Result::AdminLog; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("admin_log"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "admin_log_id_seq", + }, + "admin_user", + { data_type => "text", is_nullable => 0 }, + "object_type", + { data_type => "text", is_nullable => 0 }, + "object_id", + { data_type => "integer", is_nullable => 0 }, + "action", + { data_type => "text", is_nullable => 0 }, + "whenedited", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, +); +__PACKAGE__->set_primary_key("id"); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7427CuN3/6IL2GxiQDoWUA + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/Alert.pm b/perllib/FixMyStreet/DB/Result/Alert.pm new file mode 100644 index 000000000..eddd98f37 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Alert.pm @@ -0,0 +1,127 @@ +package FixMyStreet::DB::Result::Alert; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("alert"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "alert_id_seq", + }, + "alert_type", + { data_type => "text", is_foreign_key => 1, is_nullable => 0 }, + "parameter", + { data_type => "text", is_nullable => 1 }, + "parameter2", + { data_type => "text", is_nullable => 1 }, + "confirmed", + { data_type => "integer", default_value => 0, is_nullable => 0 }, + "lang", + { data_type => "text", default_value => "en-gb", is_nullable => 0 }, + "cobrand", + { data_type => "text", default_value => "", is_nullable => 0 }, + "cobrand_data", + { data_type => "text", default_value => "", is_nullable => 0 }, + "whensubscribed", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, + "whendisabled", + { data_type => "timestamp", is_nullable => 1 }, + "user_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->belongs_to( + "alert_type", + "FixMyStreet::DB::Result::AlertType", + { ref => "alert_type" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); +__PACKAGE__->belongs_to( + "user", + "FixMyStreet::DB::Result::User", + { id => "user_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); +__PACKAGE__->has_many( + "alert_sents", + "FixMyStreet::DB::Result::AlertSent", + { "foreign.alert_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:d2TrE9UIZdXu3eXYJH0Zmw + +# You can replace this text with custom code or comments, and it will be preserved on regeneration + +use DateTime::TimeZone; +use Moose; +use namespace::clean -except => [ 'meta' ]; + +with 'FixMyStreet::Roles::Abuser'; + +my $tz = DateTime::TimeZone->new( name => "local" ); + + +sub whensubscribed_local { + my $self = shift; + + return $self->whensubscribed + ? $self->whensubscribed->set_time_zone($tz) + : $self->whensubscribed; +} + +sub whendisabled_local { + my $self = shift; + + return $self->whendisabled + ? $self->whendisabled->set_time_zone($tz) + : $self->whendisabled; +} + +=head2 confirm + + $alert->confirm(); + +Sets the state of the alert to confirmed. + +=cut + +sub confirm { + my $self = shift; + + $self->confirmed(1); + $self->whendisabled(undef); + $self->update; + + return 1; +} + +sub disable { + my $self = shift; + + $self->whendisabled( \'ms_current_timestamp()' ); + $self->update; + + return 1; +} + +# need the inline_constuctor bit as we don't inherit from Moose +__PACKAGE__->meta->make_immutable( inline_constructor => 0 ); + +1; diff --git a/perllib/FixMyStreet/DB/Result/AlertSent.pm b/perllib/FixMyStreet/DB/Result/AlertSent.pm new file mode 100644 index 000000000..a901c2fde --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/AlertSent.pm @@ -0,0 +1,38 @@ +package FixMyStreet::DB::Result::AlertSent; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("alert_sent"); +__PACKAGE__->add_columns( + "alert_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "parameter", + { data_type => "text", is_nullable => 1 }, + "whenqueued", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, +); +__PACKAGE__->belongs_to( + "alert", + "FixMyStreet::DB::Result::Alert", + { id => "alert_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:fTiWIoriQUvHpWc9PpFLvA + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/AlertType.pm b/perllib/FixMyStreet/DB/Result/AlertType.pm new file mode 100644 index 000000000..d23a2983d --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/AlertType.pm @@ -0,0 +1,55 @@ +package FixMyStreet::DB::Result::AlertType; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("alert_type"); +__PACKAGE__->add_columns( + "ref", + { data_type => "text", is_nullable => 0 }, + "head_sql_query", + { data_type => "text", is_nullable => 0 }, + "head_table", + { data_type => "text", is_nullable => 0 }, + "head_title", + { data_type => "text", is_nullable => 0 }, + "head_link", + { data_type => "text", is_nullable => 0 }, + "head_description", + { data_type => "text", is_nullable => 0 }, + "item_table", + { data_type => "text", is_nullable => 0 }, + "item_where", + { data_type => "text", is_nullable => 0 }, + "item_order", + { data_type => "text", is_nullable => 0 }, + "item_title", + { data_type => "text", is_nullable => 0 }, + "item_link", + { data_type => "text", is_nullable => 0 }, + "item_description", + { data_type => "text", is_nullable => 0 }, + "template", + { data_type => "text", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("ref"); +__PACKAGE__->has_many( + "alerts", + "FixMyStreet::DB::Result::Alert", + { "foreign.alert_type" => "self.ref" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:+PKqo7IZ4MlM9ur4V2P9tA + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/Comment.pm b/perllib/FixMyStreet/DB/Result/Comment.pm new file mode 100644 index 000000000..ae152eb31 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Comment.pm @@ -0,0 +1,153 @@ +package FixMyStreet::DB::Result::Comment; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("comment"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "comment_id_seq", + }, + "problem_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "name", + { data_type => "text", is_nullable => 1 }, + "website", + { data_type => "text", is_nullable => 1 }, + "created", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, + "confirmed", + { data_type => "timestamp", is_nullable => 1 }, + "text", + { data_type => "text", is_nullable => 0 }, + "photo", + { data_type => "bytea", is_nullable => 1 }, + "state", + { data_type => "text", is_nullable => 0 }, + "cobrand", + { data_type => "text", default_value => "", is_nullable => 0 }, + "lang", + { data_type => "text", default_value => "en-gb", is_nullable => 0 }, + "cobrand_data", + { data_type => "text", default_value => "", is_nullable => 0 }, + "mark_fixed", + { data_type => "boolean", is_nullable => 0 }, + "mark_open", + { data_type => "boolean", default_value => \"false", is_nullable => 0 }, + "user_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "anonymous", + { data_type => "boolean", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->belongs_to( + "user", + "FixMyStreet::DB::Result::User", + { id => "user_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); +__PACKAGE__->belongs_to( + "problem", + "FixMyStreet::DB::Result::Problem", + { id => "problem_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:TYFusbxkOkAewaiZYZVJUA + +use DateTime::TimeZone; +use Image::Size; +use Moose; +use namespace::clean -except => [ 'meta' ]; + +with 'FixMyStreet::Roles::Abuser'; + +my $tz = DateTime::TimeZone->new( name => "local" ); + +sub created_local { + my $self = shift; + + return $self->created + ? $self->created->set_time_zone($tz) + : $self->created; +} + +sub confirmed_local { + my $self = shift; + + # if confirmed is null then it doesn't get inflated so don't + # try and set the timezone + return $self->confirmed + ? $self->confirmed->set_time_zone($tz) + : $self->confirmed; +} + +# You can replace this text with custom code or comments, and it will be preserved on regeneration + +sub check_for_errors { + my $self = shift; + + my %errors = (); + + $errors{name} = _('Please enter your name') + if !$self->name || $self->name !~ m/\S/; + + $errors{update} = _('Please enter a message') + unless $self->text =~ m/\S/; + + return \%errors; +} + +=head2 confirm + +Set state of comment to confirmed + +=cut + +sub confirm { + my $self = shift; + + $self->state( 'confirmed' ); + $self->confirmed( \'ms_current_timestamp()' ); +} + +=head2 get_photo_params + +Returns a hashref of details of any attached photo for use in templates. +Hashref contains height, width and url keys. + +=cut + +sub get_photo_params { + my $self = shift; + + return {} unless $self->photo; + + my $photo = {}; + ( $photo->{width}, $photo->{height} ) = + Image::Size::imgsize( \$self->photo ); + $photo->{url} = '/photo?c=' . $self->id; + + return $photo; +} + +# we need the inline_constructor bit as we don't inherit from Moose +__PACKAGE__->meta->make_immutable( inline_constructor => 0 ); + +1; diff --git a/perllib/FixMyStreet/DB/Result/Contact.pm b/perllib/FixMyStreet/DB/Result/Contact.pm new file mode 100644 index 000000000..001fb4ac6 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Contact.pm @@ -0,0 +1,45 @@ +package FixMyStreet::DB::Result::Contact; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("contacts"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "contacts_id_seq", + }, + "area_id", + { data_type => "integer", is_nullable => 0 }, + "category", + { data_type => "text", default_value => "Other", is_nullable => 0 }, + "email", + { data_type => "text", is_nullable => 0 }, + "confirmed", + { data_type => "boolean", is_nullable => 0 }, + "deleted", + { data_type => "boolean", is_nullable => 0 }, + "editor", + { data_type => "text", is_nullable => 0 }, + "whenedited", + { data_type => "timestamp", is_nullable => 0 }, + "note", + { data_type => "text", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->add_unique_constraint("contacts_area_id_category_idx", ["area_id", "category"]); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:BXGd4uk1ybC5RTKlInTr0w + +1; diff --git a/perllib/FixMyStreet/DB/Result/ContactsHistory.pm b/perllib/FixMyStreet/DB/Result/ContactsHistory.pm new file mode 100644 index 000000000..811a06b44 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/ContactsHistory.pm @@ -0,0 +1,48 @@ +package FixMyStreet::DB::Result::ContactsHistory; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("contacts_history"); +__PACKAGE__->add_columns( + "contacts_history_id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "contacts_history_contacts_history_id_seq", + }, + "contact_id", + { data_type => "integer", is_nullable => 0 }, + "area_id", + { data_type => "integer", is_nullable => 0 }, + "category", + { data_type => "text", default_value => "Other", is_nullable => 0 }, + "email", + { data_type => "text", is_nullable => 0 }, + "confirmed", + { data_type => "boolean", is_nullable => 0 }, + "deleted", + { data_type => "boolean", is_nullable => 0 }, + "editor", + { data_type => "text", is_nullable => 0 }, + "whenedited", + { data_type => "timestamp", is_nullable => 0 }, + "note", + { data_type => "text", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("contacts_history_id"); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:9APvBwAOebG5g4MGxJuVKQ + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/Nearby.pm b/perllib/FixMyStreet/DB/Result/Nearby.pm new file mode 100644 index 000000000..d3d228788 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Nearby.pm @@ -0,0 +1,33 @@ +package FixMyStreet::DB::Result::Nearby; + +# Thanks to http://www.perlmonks.org/?node_id=633800 + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; +use Moose; +use namespace::clean -except => [ 'meta' ]; + +__PACKAGE__->table( 'NONE' ); +__PACKAGE__->add_columns( + "problem_id", + { data_type => "integer", is_nullable => 0 }, + "distance", + { data_type => "double precision", is_nullable => 0 }, +); +__PACKAGE__->belongs_to( + "problem", + "FixMyStreet::DB::Result::Problem", + { id => "problem_id" }, + { is_deferrable => 1 }, +); + +# Make a new ResultSource based on the User class +__PACKAGE__->result_source_instance + ->name( \'problem_find_nearby(?,?,?)' ); + +# we need the inline_constructor bit as we don't inherit from Moose +__PACKAGE__->meta->make_immutable( inline_constructor => 0 ); + +1; diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm new file mode 100644 index 000000000..ff730958a --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Problem.pm @@ -0,0 +1,404 @@ +package FixMyStreet::DB::Result::Problem; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("problem"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "problem_id_seq", + }, + "postcode", + { data_type => "text", is_nullable => 0 }, + "latitude", + { data_type => "double precision", is_nullable => 0 }, + "longitude", + { data_type => "double precision", is_nullable => 0 }, + "council", + { data_type => "text", is_nullable => 1 }, + "areas", + { data_type => "text", is_nullable => 0 }, + "category", + { data_type => "text", default_value => "Other", is_nullable => 0 }, + "title", + { data_type => "text", is_nullable => 0 }, + "detail", + { data_type => "text", is_nullable => 0 }, + "photo", + { data_type => "bytea", is_nullable => 1 }, + "used_map", + { data_type => "boolean", is_nullable => 0 }, + "user_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "name", + { data_type => "text", is_nullable => 0 }, + "anonymous", + { data_type => "boolean", is_nullable => 0 }, + "external_id", + { data_type => "text", is_nullable => 1 }, + "external_body", + { data_type => "text", is_nullable => 1 }, + "external_team", + { data_type => "text", is_nullable => 1 }, + "created", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, + "confirmed", + { data_type => "timestamp", is_nullable => 1 }, + "state", + { data_type => "text", is_nullable => 0 }, + "lang", + { data_type => "text", default_value => "en-gb", is_nullable => 0 }, + "service", + { data_type => "text", default_value => "", is_nullable => 0 }, + "cobrand", + { data_type => "text", default_value => "", is_nullable => 0 }, + "cobrand_data", + { data_type => "text", default_value => "", is_nullable => 0 }, + "lastupdate", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, + "whensent", + { data_type => "timestamp", is_nullable => 1 }, + "send_questionnaire", + { data_type => "boolean", default_value => \"true", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->has_many( + "comments", + "FixMyStreet::DB::Result::Comment", + { "foreign.problem_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); +__PACKAGE__->belongs_to( + "user", + "FixMyStreet::DB::Result::User", + { id => "user_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); +__PACKAGE__->has_many( + "questionnaires", + "FixMyStreet::DB::Result::Questionnaire", + { "foreign.problem_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:3sw/1dqxlTvcWEI/eJTm4w + +# Add fake relationship to stored procedure table +__PACKAGE__->has_many( + "nearby", + "FixMyStreet::DB::Result::Nearby", + { "foreign.problem_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + +use DateTime::TimeZone; +use Image::Size; +use Moose; +use namespace::clean -except => [ 'meta' ]; +use Utils; + +with 'FixMyStreet::Roles::Abuser'; + +my $tz = DateTime::TimeZone->new( name => "local" ); + +sub confirmed_local { + my $self = shift; + + return $self->confirmed + ? $self->confirmed->set_time_zone($tz) + : $self->confirmed; +} + +sub created_local { + my $self = shift; + + return $self->created + ? $self->created->set_time_zone($tz) + : $self->created; +} + +sub whensent_local { + my $self = shift; + + return $self->whensent + ? $self->whensent->set_time_zone($tz) + : $self->whensent; +} + +sub lastupdate_local { + my $self = shift; + + return $self->lastupdate + ? $self->lastupdate->set_time_zone($tz) + : $self->lastupdate; +} + +around service => sub { + my ( $orig, $self ) = ( shift, shift ); + my $s = $self->$orig(@_); + $s =~ s/_/ /g; + return $s; +}; + +=head2 check_for_errors + + $error_hashref = $problem->check_for_errors(); + +Look at all the fields and return a hashref with all errors found, keyed on the +field name. This is intended to be passed back to the form to display the +errors. + +TODO - ideally we'd pass back error codes which would be humanised in the +templates (eg: 'missing','email_not_valid', etc). + +=cut + +sub check_for_errors { + my $self = shift; + + my %errors = (); + + $errors{title} = _('Please enter a subject') + unless $self->title =~ m/\S/; + + $errors{detail} = _('Please enter some details') + unless $self->detail =~ m/\S/; + + $errors{council} = _('No council selected') + unless $self->council + && $self->council =~ m/^(?:-1|[\d,]+(?:\|[\d,]+)?)$/; + + if ( !$self->name || $self->name !~ m/\S/ ) { + $errors{name} = _('Please enter your name'); + } + elsif (length( $self->name ) < 5 + || $self->name !~ m/\s/ + || $self->name =~ m/\ba\s*n+on+((y|o)mo?u?s)?(ly)?\b/i ) + { + $errors{name} = _( +'Please enter your full name, councils need this information - if you do not wish your name to be shown on the site, untick the box' + ); + } + + if ( $self->category + && $self->category eq _('-- Pick a category --') ) + { + $errors{category} = _('Please choose a category'); + $self->category(undef); + } + elsif ($self->category + && $self->category eq _('-- Pick a property type --') ) + { + $errors{category} = _('Please choose a property type'); + $self->category(undef); + } + + return \%errors; +} + +=head2 confirm + + $bool = $problem->confirm( ); + $problem->update; + + +Set the state to 'confirmed' and put current time into 'confirmed' field. This +is a no-op if the report is already confirmed. + +NOTE - does not update storage - call update or insert to do that. + +=cut + +sub confirm { + my $self = shift; + + return if $self->state eq 'confirmed'; + + $self->state('confirmed'); + $self->confirmed( \'ms_current_timestamp()' ); + return 1; +} + +=head2 councils + +Returns an arrayref of councils to which a report was sent. + +=cut + +sub councils { + my $self = shift; + return [] unless $self->council; + (my $council = $self->council) =~ s/\|.*$//; + my @council = split( /,/, $council ); + return \@council; +} + +=head2 url + +Returns a URL for this problem report. + +=cut + +sub url { + my $self = shift; + return "/report/" . $self->id; +} + +=head2 get_photo_params + +Returns a hashref of details of any attached photo for use in templates. +Hashref contains height, width and url keys. + +=cut + +sub get_photo_params { + my $self = shift; + + return {} unless $self->photo; + + my $photo = {}; + ( $photo->{width}, $photo->{height} ) = + Image::Size::imgsize( \$self->photo ); + $photo->{url} = '/photo?id=' . $self->id; + + return $photo; +} + +=head2 meta_line + +Returns a string to be used on a problem report page, describing some of the +meta data about the report. + +=cut + +sub meta_line { + my ( $problem, $c ) = @_; + + my $date_time = + Utils::prettify_epoch( $problem->confirmed_local->epoch ); + my $meta = ''; + + # FIXME Should be in cobrand + if ($c->cobrand->moniker eq 'emptyhomes') { + + my $category = _($problem->category); + utf8::decode($category); + if ($problem->anonymous) { + $meta = sprintf(_('%s, reported anonymously at %s'), $category, $date_time); + } else { + $meta = sprintf(_('%s, reported by %s at %s'), $category, $problem->name, $date_time); + } + + } else { + + if ( $problem->anonymous ) { + if ( $problem->service + and $problem->category && $problem->category ne _('Other') ) + { + $meta = + sprintf( _('Reported by %s in the %s category anonymously at %s'), + $problem->service, $problem->category, $date_time ); + } + elsif ( $problem->service ) { + $meta = sprintf( _('Reported by %s anonymously at %s'), + $problem->service, $date_time ); + } + elsif ( $problem->category and $problem->category ne _('Other') ) { + $meta = sprintf( _('Reported in the %s category anonymously at %s'), + $problem->category, $date_time ); + } + else { + $meta = sprintf( _('Reported anonymously at %s'), $date_time ); + } + } + else { + if ( $problem->service + and $problem->category && $problem->category ne _('Other') ) + { + $meta = sprintf( + _('Reported by %s in the %s category by %s at %s'), + $problem->service, $problem->category, + $problem->name, $date_time + ); + } + elsif ( $problem->service ) { + $meta = sprintf( _('Reported by %s by %s at %s'), + $problem->service, $problem->name, $date_time ); + } + elsif ( $problem->category and $problem->category ne _('Other') ) { + $meta = sprintf( _('Reported in the %s category by %s at %s'), + $problem->category, $problem->name, $date_time ); + } + else { + $meta = + sprintf( _('Reported by %s at %s'), $problem->name, $date_time ); + } + } + + } + + $meta .= $c->cobrand->extra_problem_meta_text($problem); + $meta .= '; ' . _('the map was not used so pin location may be inaccurate') + unless $problem->used_map; + + return $meta; +} + +sub body { + my ( $problem, $c ) = @_; + my $body; + if ($problem->external_body) { + $body = $problem->external_body; + } else { + (my $council = $problem->council) =~ s/\|.*//g; + my @councils = split( /,/, $council ); + my $areas_info = mySociety::MaPit::call('areas', \@councils); + $body = join( _(' and '), + map { + my $name = $areas_info->{$_}->{name}; + if (mySociety::Config::get('AREA_LINKS_FROM_PROBLEMS')) { + '<a href="' + . $c->uri_for( '/reports/' . $c->cobrand->short_name( $areas_info->{$_} ) ) + . '">' . $name . '</a>'; + } else { + $name; + } + } @councils + ); + } + return $body; +} + +# TODO Some/much of this could be moved to the template +sub duration_string { + my ( $problem, $c ) = @_; + my $body = $problem->body( $c ); + return sprintf(_('Sent to %s %s later'), $body, + Utils::prettify_duration($problem->whensent_local->epoch - $problem->confirmed_local->epoch, 'minute') + ); +} + +# we need the inline_constructor bit as we don't inherit from Moose +__PACKAGE__->meta->make_immutable( inline_constructor => 0 ); + +1; diff --git a/perllib/FixMyStreet/DB/Result/Questionnaire.pm b/perllib/FixMyStreet/DB/Result/Questionnaire.pm new file mode 100644 index 000000000..cc4ec300b --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Questionnaire.pm @@ -0,0 +1,66 @@ +package FixMyStreet::DB::Result::Questionnaire; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("questionnaire"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "questionnaire_id_seq", + }, + "problem_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, + "whensent", + { data_type => "timestamp", is_nullable => 0 }, + "whenanswered", + { data_type => "timestamp", is_nullable => 1 }, + "ever_reported", + { data_type => "boolean", is_nullable => 1 }, + "old_state", + { data_type => "text", is_nullable => 1 }, + "new_state", + { data_type => "text", is_nullable => 1 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->belongs_to( + "problem", + "FixMyStreet::DB::Result::Problem", + { id => "problem_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:QNFqqCg6J4SFlg4zwm7TWw + +use DateTime::TimeZone; + +my $tz = DateTime::TimeZone->new( name => "local" ); + +sub whensent_local { + my $self = shift; + + return $self->whensent + ? $self->whensent->set_time_zone($tz) + : $self->whensent; +} + +sub whenanswered_local { + my $self = shift; + + return $self->whenanswered + ? $self->whenanswered->set_time_zone($tz) + : $self->whenanswered; +} + +1; diff --git a/perllib/FixMyStreet/DB/Result/Secret.pm b/perllib/FixMyStreet/DB/Result/Secret.pm new file mode 100644 index 000000000..8a1fa671d --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Secret.pm @@ -0,0 +1,21 @@ +package FixMyStreet::DB::Result::Secret; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("secret"); +__PACKAGE__->add_columns("secret", { data_type => "text", is_nullable => 0 }); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:MfqW1K0aFtwpa/1c/UwHjg + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/Session.pm b/perllib/FixMyStreet/DB/Result/Session.pm new file mode 100644 index 000000000..9d5d509dc --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Session.pm @@ -0,0 +1,28 @@ +package FixMyStreet::DB::Result::Session; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("sessions"); +__PACKAGE__->add_columns( + "id", + { data_type => "char", is_nullable => 0, size => 72 }, + "session_data", + { data_type => "text", is_nullable => 1 }, + "expires", + { data_type => "integer", is_nullable => 1 }, +); +__PACKAGE__->set_primary_key("id"); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:TagSQOXnDttkwfJ7oDH8Yw + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/perllib/FixMyStreet/DB/Result/Token.pm b/perllib/FixMyStreet/DB/Result/Token.pm new file mode 100644 index 000000000..3a900858d --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/Token.pm @@ -0,0 +1,87 @@ +package FixMyStreet::DB::Result::Token; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("token"); +__PACKAGE__->add_columns( + "scope", + { data_type => "text", is_nullable => 0 }, + "token", + { data_type => "text", is_nullable => 0 }, + "data", + { data_type => "bytea", is_nullable => 0 }, + "created", + { + data_type => "timestamp", + default_value => \"ms_current_timestamp()", + is_nullable => 0, + }, +); +__PACKAGE__->set_primary_key("scope", "token"); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:frl+na3HrIzGw9D1t891nA + +# Trying not to use this +# use mySociety::DBHandle qw(dbh); + +use mySociety::AuthToken; +use IO::String; +use RABX; + +=head1 NAME + +FixMyStreet::DB::Result::Token + +=head2 DESCRIPTION + +Representation of mySociety::AuthToken in the DBIx::Class world. + +Mostly done so that we don't need to use mySociety::DBHandle. + +The 'data' value is automatically inflated and deflated in the same way that the +AuthToken would do it. 'token' is set to a new random value by default and the +'created' timestamp is achieved using the database function +ms_current_timestamp. + +=cut + +__PACKAGE__->filter_column( + data => { + filter_from_storage => sub { + my $self = shift; + my $ser = shift; + return undef unless defined $ser; + my $h = new IO::String($ser); + return RABX::wire_rd($h); + }, + filter_to_storage => sub { + my $self = shift; + my $data = shift; + my $ser = ''; + my $h = new IO::String($ser); + RABX::wire_wr( $data, $h ); + return $ser; + }, + } +); + +sub new { + my ( $class, $attrs ) = @_; + + $attrs->{token} ||= mySociety::AuthToken::random_token(); + $attrs->{created} ||= \'ms_current_timestamp()'; + + my $new = $class->next::method($attrs); + return $new; +} + +1; diff --git a/perllib/FixMyStreet/DB/Result/User.pm b/perllib/FixMyStreet/DB/Result/User.pm new file mode 100644 index 000000000..4ee413a58 --- /dev/null +++ b/perllib/FixMyStreet/DB/Result/User.pm @@ -0,0 +1,135 @@ +package FixMyStreet::DB::Result::User; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +__PACKAGE__->load_components("FilterColumn", "InflateColumn::DateTime", "EncodedColumn"); +__PACKAGE__->table("users"); +__PACKAGE__->add_columns( + "id", + { + data_type => "integer", + is_auto_increment => 1, + is_nullable => 0, + sequence => "users_id_seq", + }, + "email", + { data_type => "text", is_nullable => 0 }, + "name", + { data_type => "text", is_nullable => 1 }, + "phone", + { data_type => "text", is_nullable => 1 }, + "password", + { data_type => "text", default_value => "", is_nullable => 0 }, +); +__PACKAGE__->set_primary_key("id"); +__PACKAGE__->add_unique_constraint("users_email_key", ["email"]); +__PACKAGE__->has_many( + "alerts", + "FixMyStreet::DB::Result::Alert", + { "foreign.user_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); +__PACKAGE__->has_many( + "comments", + "FixMyStreet::DB::Result::Comment", + { "foreign.user_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); +__PACKAGE__->has_many( + "problems", + "FixMyStreet::DB::Result::Problem", + { "foreign.user_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-06-23 15:49:48 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:T2JK+KyfoE2hkCLgreq1XQ + +__PACKAGE__->add_columns( + "password" => { + encode_column => 1, + encode_class => 'Crypt::Eksblowfish::Bcrypt', + encode_args => { cost => 8 }, + encode_check_method => 'check_password', + }, +); + +use mySociety::EmailUtil; + +=head2 check_for_errors + + $error_hashref = $problem->check_for_errors(); + +Look at all the fields and return a hashref with all errors found, keyed on the +field name. This is intended to be passed back to the form to display the +errors. + +TODO - ideally we'd pass back error codes which would be humanised in the +templates (eg: 'missing','email_not_valid', etc). + +=cut + +sub check_for_errors { + my $self = shift; + + my %errors = (); + + if ( !$self->name || $self->name !~ m/\S/ ) { + $errors{name} = _('Please enter your name'); + } + + if ( $self->email !~ /\S/ ) { + $errors{email} = _('Please enter your email'); + } + elsif ( !mySociety::EmailUtil::is_valid_email( $self->email ) ) { + $errors{email} = _('Please enter a valid email'); + } + + return \%errors; +} + +=head2 answered_ever_reported + +Check if the user has ever answered a questionnaire. + +=cut + +sub answered_ever_reported { + my $self = shift; + + my $has_answered = + $self->result_source->schema->resultset('Questionnaire')->search( + { + ever_reported => { not => undef }, + problem_id => { -in => + $self->problems->get_column('id')->as_query }, + } + ); + + return $has_answered->count > 0; +} + +=head2 alert_for_problem + +Returns whether the user is already subscribed to an +alert for the problem ID provided. + +=cut + +sub alert_for_problem { + my ( $self, $id ) = @_; + + return $self->alerts->find( { + alert_type => 'new_updates', + parameter => $id, + } ); +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Alert.pm b/perllib/FixMyStreet/DB/ResultSet/Alert.pm new file mode 100644 index 000000000..5848265f1 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Alert.pm @@ -0,0 +1,50 @@ +package FixMyStreet::DB::ResultSet::Alert; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub timeline_created { + my ( $rs, $restriction ) = @_; + + my $prefetch = + FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? + [ qw/alert_type user/ ] : + [ qw/alert_type/ ]; + + return $rs->search( + { + whensubscribed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + confirmed => 1, + %{ $restriction }, + }, + { + prefetch => $prefetch, + } + ); +} + +sub timeline_disabled { + my ( $rs, $restriction ) = @_; + + return $rs->search( + { + whendisabled => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + %{ $restriction }, + }, + ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['confirmed'], + select => [ 'confirmed', { count => 'id' } ], + as => [qw/confirmed confirmed_count/] + } + ); +} +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm new file mode 100644 index 000000000..46009cb85 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm @@ -0,0 +1,210 @@ +package FixMyStreet::DB::ResultSet::AlertType; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +use File::Slurp; + +use mySociety::DBHandle qw(dbh); +use mySociety::EmailUtil; +use mySociety::Gaze; +use mySociety::Locale; +use mySociety::MaPit; + +# Child must have confirmed, id, email, state(!) columns +# If parent/child, child table must also have name and text +# and foreign key to parent must be PARENT_id +sub email_alerts ($) { + my ( $rs ) = @_; + + my $q = $rs->search( { ref => { -not_like => '%local_problems%' } } ); + while (my $alert_type = $q->next) { + my $ref = $alert_type->ref; + my $head_table = $alert_type->head_table; + my $item_table = $alert_type->item_table; + my $query = 'select alert.id as alert_id, alert.user_id as alert_user_id, alert.lang as alert_lang, alert.cobrand as alert_cobrand, + alert.cobrand_data as alert_cobrand_data, alert.parameter as alert_parameter, alert.parameter2 as alert_parameter2, '; + if ($head_table) { + $query .= " + $item_table.id as item_id, $item_table.text as item_text, + $item_table.name as item_name, $item_table.anonymous as item_anonymous, + $head_table.* + from alert + inner join $item_table on alert.parameter::integer = $item_table.${head_table}_id + inner join $head_table on alert.parameter::integer = $head_table.id + "; + } else { + $query .= " $item_table.*, + $item_table.id as item_id + from alert, $item_table"; + } + $query .= " + where alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed + and $item_table.confirmed >= ms_current_timestamp() - '7 days'::interval + and (select whenqueued from alert_sent where alert_sent.alert_id = alert.id and alert_sent.parameter::integer = $item_table.id) is null + and $item_table.user_id <> alert.user_id + and " . $alert_type->item_where . " + and alert.confirmed = 1 + order by alert.id, $item_table.confirmed"; + # XXX Ugh - needs work + $query =~ s/\?/alert.parameter/ if ($query =~ /\?/); + $query =~ s/\?/alert.parameter2/ if ($query =~ /\?/); + $query = dbh()->prepare($query); + $query->execute(); + my $last_alert_id; + my %data = ( template => $alert_type->template, data => '' ); + while (my $row = $query->fetchrow_hashref) { + + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->{alert_cobrand})->new(); + + # Cobranded and non-cobranded messages can share a database. In this case, the conf file + # should specify a vhost to send the reports for each cobrand, so that they don't get sent + # more than once if there are multiple vhosts running off the same database. The email_host + # call checks if this is the host that sends mail for this cobrand. + next unless $cobrand->email_host; + + FixMyStreet::App->model('DB::AlertSent')->create( { + alert_id => $row->{alert_id}, + parameter => $row->{item_id}, + } ); + if ($last_alert_id && $last_alert_id != $row->{alert_id}) { + _send_aggregated_alert_email(%data); + %data = ( template => $alert_type->template, data => '' ); + } + + # create problem status message for the templates + $data{state_message} = + $row->{state} eq 'fixed' + ? _("This report is currently marked as fixed.") + : _("This report is currently marked as open."); + + my $url = $cobrand->base_url_for_emails( $row->{alert_cobrand_data} ); + if ($row->{item_text}) { + $data{problem_url} = $url . "/report/" . $row->{id}; + $data{data} .= $row->{item_name} . ' : ' if $row->{item_name} && !$row->{item_anonymous}; + $data{data} .= $row->{item_text} . "\n\n------\n\n"; + } else { + $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n"; + } + if (!$data{alert_user_id}) { + %data = (%data, %$row); + if ($ref eq 'area_problems' || $ref eq 'council_problems' || $ref eq 'ward_problems') { + my $va_info = mySociety::MaPit::call('area', $row->{alert_parameter}); + $data{area_name} = $va_info->{name}; + } + if ($ref eq 'ward_problems') { + my $va_info = mySociety::MaPit::call('area', $row->{alert_parameter2}); + $data{ward_name} = $va_info->{name}; + } + } + $data{cobrand} = $row->{alert_cobrand}; + $data{cobrand_data} = $row->{alert_cobrand_data}; + $data{lang} = $row->{alert_lang}; + $last_alert_id = $row->{alert_id}; + } + if ($last_alert_id) { + _send_aggregated_alert_email(%data); + } + } + + # Nearby done separately as the table contains the parameters + my $template = $rs->find( { ref => 'local_problems' } )->template; + my $query = FixMyStreet::App->model('DB::Alert')->search( { + alert_type => 'local_problems', + whendisabled => undef, + confirmed => 1 + }, { + order_by => 'id' + } ); + while (my $alert = $query->next) { + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($alert->cobrand)->new(); + next unless $cobrand->email_host; + + my $longitude = $alert->parameter; + my $latitude = $alert->parameter2; + my $url = $cobrand->base_url_for_emails( $alert->cobrand_data ); + my ($site_restriction, $site_id) = $cobrand->site_restriction( $alert->cobrand_data ); + my $d = mySociety::Gaze::get_radius_containing_population($latitude, $longitude, 200000); + # Convert integer to GB locale string (with a ".") + $d = mySociety::Locale::in_gb_locale { + sprintf("%f", int($d*10+0.5)/10); + }; + my %data = ( template => $template, data => '', alert_id => $alert->id, alert_email => $alert->user->email, lang => $alert->lang, cobrand => $alert->cobrand, cobrand_data => $alert->cobrand_data ); + my $q = "select problem.id, problem.title from problem_find_nearby(?, ?, ?) as nearby, problem, users + where nearby.problem_id = problem.id + and problem.user_id = users.id + and problem.state in ('confirmed', 'fixed') + and problem.confirmed >= ? and problem.confirmed >= ms_current_timestamp() - '7 days'::interval + and (select whenqueued from alert_sent where alert_sent.alert_id = ? and alert_sent.parameter::integer = problem.id) is null + and users.email <> ? + $site_restriction + order by confirmed desc"; + $q = dbh()->prepare($q); + $q->execute($latitude, $longitude, $d, $alert->whensubscribed, $alert->id, $alert->user->email); + while (my $row = $q->fetchrow_hashref) { + FixMyStreet::App->model('DB::AlertSent')->create( { + alert_id => $alert->id, + parameter => $row->{id}, + } ); + $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n"; + } + _send_aggregated_alert_email(%data) if $data{data}; + } +} + +sub _send_aggregated_alert_email(%) { + my %data = @_; + + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($data{cobrand})->new(); + + $cobrand->set_lang_and_domain( $data{lang}, 1 ); + + if (!$data{alert_email}) { + my $user = FixMyStreet::App->model('DB::User')->find( { + id => $data{alert_user_id} + } ); + $data{alert_email} = $user->email; + } + + my $token = FixMyStreet::App->model("DB::Token")->new_result( { + scope => 'alert', + data => { + id => $data{alert_id}, + type => 'unsubscribe', + email => $data{alert_email}, + } + } ); + $data{unsubscribe_url} = $cobrand->base_url_for_emails( $data{cobrand_data} ) . '/A/' . $token->token; + + my $template = FixMyStreet->path_to( + "templates", "email", $cobrand->moniker, $data{lang}, "$data{template}.txt" + )->stringify; + $template = FixMyStreet->path_to( "templates", "email", $cobrand->moniker, "$data{template}.txt" )->stringify + unless -e $template; + $template = FixMyStreet->path_to( "templates", "email", "default", "$data{template}.txt" )->stringify + unless -e $template; + $template = File::Slurp::read_file($template); + + my $sender = $cobrand->contact_email; + (my $from = $sender) =~ s/team/fms-DO-NOT-REPLY/; # XXX + my $result = FixMyStreet::App->send_email_cron( + { + _template_ => $template, + _parameters_ => \%data, + From => [ $from, _($cobrand->contact_name) ], + To => $data{alert_email}, + }, + $sender, + [ $data{alert_email} ], + 0, + ); + + if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) { + $token->insert(); + } else { + print "Failed to send alert $data{alert_id}!"; + } +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm new file mode 100644 index 000000000..70f8027aa --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm @@ -0,0 +1,41 @@ +package FixMyStreet::DB::ResultSet::Comment; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub timeline { + my ( $rs, $restriction ) = @_; + + my $prefetch = + FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? + [ qw/user/ ] : + []; + + return $rs->search( + { + state => 'confirmed', + created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + %{ $restriction }, + }, + { + prefetch => $prefetch, + } + ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['me.state'], + select => [ 'me.state', { count => 'me.id' } ], + as => [qw/state state_count/], + join => 'problem' + } + ); +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Contact.pm b/perllib/FixMyStreet/DB/ResultSet/Contact.pm new file mode 100644 index 000000000..6fa6a03a0 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Contact.pm @@ -0,0 +1,33 @@ +package FixMyStreet::DB::ResultSet::Contact; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +=head2 not_deleted + + $rs = $rs->not_deleted(); + +Filter down to not deleted contacts - which have C<deleted> set to false; + +=cut + +sub not_deleted { + my $rs = shift; + return $rs->search( { deleted => 0 } ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['confirmed'], + select => [ 'confirmed', { count => 'id' } ], + as => [qw/confirmed confirmed_count/] + } + ); +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm new file mode 100644 index 000000000..3b3a3d90b --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm @@ -0,0 +1,50 @@ +package FixMyStreet::DB::ResultSet::Nearby; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub nearby { + my ( $rs, $c, $dist, $ids, $limit, $mid_lat, $mid_lon, $interval ) = @_; + + my $params = { + state => [ 'confirmed', 'fixed' ], + }; + $params->{'current_timestamp-lastupdate'} = { '<', \"'$interval'::interval" } + if $interval; + $params->{id} = { -not_in => $ids } + if $ids; + $params = { + %{ $c->cobrand->problems_clause }, + %$params + } if $c->cobrand->problems_clause; + + my $attrs = { + join => 'problem', + columns => [ + 'problem.id', 'problem.title', 'problem.latitude', + 'problem.longitude', 'distance', 'problem.state', + 'problem.confirmed' + ], + bind => [ $mid_lat, $mid_lon, $dist ], + order_by => [ 'distance', { -desc => 'created' } ], + rows => $limit, + }; + + my @problems = mySociety::Locale::in_gb_locale { $rs->search( $params, $attrs )->all }; + return \@problems; +} + +# XXX Not currently used, so not migrating at present. +#sub fixed_nearby { +# my ($dist, $mid_lat, $mid_lon) = @_; +# mySociety::Locale::in_gb_locale { select_all( +# "select id, title, latitude, longitude, distance +# from problem_find_nearby(?, ?, $dist) as nearby, problem +# where nearby.problem_id = problem.id and state='fixed' +# site_restriction +# order by lastupdate desc", $mid_lat, $mid_lon); +# } +#} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm new file mode 100644 index 000000000..ca329ab59 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -0,0 +1,203 @@ +package FixMyStreet::DB::ResultSet::Problem; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +my $site_restriction; +my $site_key; + +sub set_restriction { + my ( $rs, $sql, $key, $restriction ) = @_; + $site_key = $key; + $site_restriction = $restriction; +} + +# Front page statistics + +sub recent_fixed { + my $rs = shift; + my $key = "recent_fixed:$site_key"; + my $result = Memcached::get($key); + unless ($result) { + $result = $rs->search( { + state => 'fixed', + lastupdate => { '>', \"current_timestamp-'1 month'::interval" }, + } )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +sub number_comments { + my $rs = shift; + my $key = "number_comments:$site_key"; + my $result = Memcached::get($key); + unless ($result) { + $result = $rs->search( + { 'comments.state' => 'confirmed' }, + { join => 'comments' } + )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +sub recent_new { + my ( $rs, $interval ) = @_; + (my $key = $interval) =~ s/\s+//g; + $key = "recent_new:$site_key:$key"; + my $result = Memcached::get($key); + unless ($result) { + $result = $rs->search( { + state => [ 'confirmed', 'fixed' ], + confirmed => { '>', \"current_timestamp-'$interval'::interval" }, + } )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +# Front page recent lists + +sub recent { + my ( $rs ) = @_; + my $key = "recent:$site_key"; + my $result = Memcached::get($key); + unless ($result) { + $result = [ $rs->search( { + state => [ 'confirmed', 'fixed' ] + }, { + columns => [ 'id', 'title' ], + order_by => { -desc => 'confirmed' }, + rows => 5, + } )->all ]; + Memcached::set($key, $result, 3600); + } + return $result; +} + +sub recent_photos { + my ( $rs, $num, $lat, $lon, $dist ) = @_; + my $probs; + my $query = { + state => [ 'confirmed', 'fixed' ], + photo => { '!=', undef }, + }; + my $attrs = { + columns => [ 'id', 'title' ], + order_by => { -desc => 'confirmed' }, + rows => $num, + }; + if (defined $lat) { + my $dist2 = $dist; # Create a copy of the variable to stop it being stringified into a locale in the next line! + my $key = "recent_photos:$site_key:$num:$lat:$lon:$dist2"; + $probs = Memcached::get($key); + unless ($probs) { + $attrs->{bind} = [ $lat, $lon, $dist ]; + $attrs->{join} = 'nearby'; + $probs = [ mySociety::Locale::in_gb_locale { + $rs->search( $query, $attrs )->all; + } ]; + Memcached::set($key, $probs, 3600); + } + } else { + my $key = "recent_photos:$site_key:$num"; + $probs = Memcached::get($key); + unless ($probs) { + $probs = [ $rs->search( $query, $attrs )->all ]; + Memcached::set($key, $probs, 3600); + } + } + return $probs; +} + +# Problems around a location + +sub around_map { + my ( $rs, $min_lat, $max_lat, $min_lon, $max_lon, $interval, $limit ) = @_; + my $attr = { + order_by => { -desc => 'created' }, + columns => [ + 'id', 'title' ,'latitude', 'longitude', 'state', 'confirmed' + ], + }; + $attr->{rows} = $limit if $limit; + + my $q = { + state => [ 'confirmed', 'fixed' ], + latitude => { '>=', $min_lat, '<', $max_lat }, + longitude => { '>=', $min_lon, '<', $max_lon }, + }; + $q->{'current_timestamp - lastupdate'} = { '<', \"'$interval'::interval" } + if $interval; + + my @problems = mySociety::Locale::in_gb_locale { $rs->search( $q, $attr )->all }; + return \@problems; +} + +# Admin functions + +sub timeline { + my ( $rs ) = @_; + + my $prefetch = + FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? + [ qw/user/ ] : + []; + + return $rs->search( + { + -or => { + created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + confirmed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whensent => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + } + }, + { + prefetch => $prefetch, + } + ); +} + +sub summary_count { + my ( $rs ) = @_; + + return $rs->search( + undef, + { + group_by => ['state'], + select => [ 'state', { count => 'id' } ], + as => [qw/state state_count/] + } + ); +} + +sub unique_users { + my ( $rs ) = @_; + + return $rs->search( { + state => [ 'confirmed', 'fixed' ], + }, { + select => [ { count => { distinct => 'user_id' } } ], + as => [ 'count' ] + } )->first->get_column('count'); +} + +sub categories_summary { + my ( $rs ) = @_; + + my $categories = $rs->search( { + state => [ 'confirmed', 'fixed' ], + whensent => { '<' => \"NOW() - INTERVAL '4 weeks'" }, + }, { + select => [ 'category', { count => 'id' }, { count => \"case when state='fixed' then 1 else null end" } ], + as => [ 'category', 'c', 'fixed' ], + group_by => [ 'category' ], + result_class => 'DBIx::Class::ResultClass::HashRefInflator' + } ); + my %categories = map { $_->{category} => { total => $_->{c}, fixed => $_->{fixed} } } $categories->all; + return \%categories; +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm new file mode 100644 index 000000000..e490c77a6 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm @@ -0,0 +1,149 @@ +package FixMyStreet::DB::ResultSet::Questionnaire; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; +use File::Slurp; +use Utils; +use mySociety::EmailUtil; + +sub send_questionnaires { + my ( $rs, $params ) = @_; + $rs->send_questionnaires_period( '4 weeks', $params ); + $rs->send_questionnaires_period( '26 weeks', $params ) + if $params->{site} eq 'emptyhomes'; +} + +sub send_questionnaires_period { + my ( $rs, $period, $params ) = @_; + + # Select all problems that need a questionnaire email sending + my $q_params = { + state => [ 'confirmed', 'fixed' ], + whensent => [ + '-and', + { '!=', undef }, + { '<', \"ms_current_timestamp() - '$period'::interval" }, + ], + send_questionnaire => 1, + }; + # FIXME Do these a bit better... + if ($params->{site} eq 'emptyhomes' && $period eq '4 weeks') { + $q_params->{'(select max(whensent) from questionnaire where me.id=problem_id)'} = undef; + } elsif ($params->{site} eq 'emptyhomes' && $period eq '26 weeks') { + $q_params->{'(select max(whensent) from questionnaire where me.id=problem_id)'} = { '!=', undef }; + } else { + $q_params->{'-or'} = [ + '(select max(whensent) from questionnaire where me.id=problem_id)' => undef, + '(select max(whenanswered) from questionnaire where me.id=problem_id)' => { '<', \"ms_current_timestamp() - '$period'::interval" } + ]; + } + + my $unsent = FixMyStreet::App->model('DB::Problem')->search( $q_params, { + order_by => { -desc => 'confirmed' } + } ); + + while (my $row = $unsent->next) { + + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->cobrand)->new(); + $cobrand->set_lang_and_domain($row->lang, 1); + + # Cobranded and non-cobranded messages can share a database. In this case, the conf file + # should specify a vhost to send the reports for each cobrand, so that they don't get sent + # more than once if there are multiple vhosts running off the same database. The email_host + # call checks if this is the host that sends mail for this cobrand. + next unless $cobrand->email_host; + + my $template; + if ($params->{site} eq 'emptyhomes') { + ($template = $period) =~ s/ //; + $template = File::Slurp::read_file( FixMyStreet->path_to( "templates/email/emptyhomes/" . $row->lang . "/questionnaire-$template.txt" )->stringify ); + } else { + $template = FixMyStreet->path_to( "templates", "email", $cobrand->moniker, "questionnaire.txt" )->stringify; + $template = FixMyStreet->path_to( "templates", "email", "default", "questionnaire.txt" )->stringify + unless -e $template; + $template = File::Slurp::read_file( $template ); + } + + my %h = map { $_ => $row->$_ } qw/name title detail category/; + $h{created} = Utils::prettify_duration( time() - $row->confirmed->epoch, 'week' ); + + my $questionnaire = FixMyStreet::App->model('DB::Questionnaire')->create( { + problem_id => $row->id, + whensent => \'ms_current_timestamp()', + } ); + + # We won't send another questionnaire unless they ask for it (or it was + # the first EHA questionnaire. + $row->send_questionnaire( 0 ) + if $params->{site} ne 'emptyhomes' || $period eq '26 weeks'; + + my $token = FixMyStreet::App->model("DB::Token")->new_result( { + scope => 'questionnaire', + data => $questionnaire->id, + } ); + $h{url} = $cobrand->base_url_for_emails($row->cobrand_data) . '/Q/' . $token->token; + + my $sender = $cobrand->contact_email; + my $sender_name = _($cobrand->contact_name); + $sender =~ s/team/fms-DO-NOT-REPLY/; + + print "Sending questionnaire " . $questionnaire->id . ", problem " + . $row->id . ", token " . $token->token . " to " + . $row->user->email . "\n" + if $params->{verbose}; + + my $result = FixMyStreet::App->send_email_cron( + { + _template_ => $template, + _parameters_ => \%h, + To => [ [ $row->user->email, $row->name ] ], + From => [ $sender, $sender_name ], + }, + $sender, + [ $row->user->email ], + $params->{nomail} + ); + if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) { + print " ...success\n" if $params->{verbose}; + $row->update(); + $token->insert(); + } else { + print " ...failed\n" if $params->{verbose}; + $questionnaire->delete; + } + } +} + +sub timeline { + my ( $rs, $restriction ) = @_; + + return $rs->search( + { + -or => { + whenanswered => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + 'me.whensent' => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + }, + %{ $restriction }, + }, + { + -select => [qw/me.*/], + prefetch => [qw/problem/], + } + ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => [ \'whenanswered is not null' ], + select => [ \'(whenanswered is not null)', { count => 'me.id' } ], + as => [qw/answered questionnaire_count/], + join => 'problem' + } + ); +} +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/User.pm b/perllib/FixMyStreet/DB/ResultSet/User.pm new file mode 100644 index 000000000..7e657a936 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/User.pm @@ -0,0 +1,8 @@ +package FixMyStreet::DB::ResultSet::User; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + + +1; |