lib/SGN/Controller/AJAX/Audit.pm

   1 use strict;
   2 use warnings;
   3
   4 package SGN::Controller::AJAX::Audit;
   5
   6 use Moose;
   7 use Data::Dumper;
   8 use File::Temp qw | tempfile |;
   9 use File::Slurp;
  10 use File::Spec qw | catfile|;
  11 use File::Basename qw | basename |;
  12 use File::Copy;
  13 use CXGN::Dataset;
  14 use CXGN::Dataset::File;
  15 use CXGN::Tools::Run;
  16 use Cwd qw(cwd);
  17 use JSON;
  18
  19 BEGIN { extends 'Catalyst::Controller::REST' };
  20
  21 __PACKAGE__->config(
  22     default   => 'application/json',
  23     stash_key => 'rest',
  24     map       => { 'application/json' => 'JSON' },
  25     );
  26
  27
  28 sub retrieve_results : Path('/ajax/audit/retrieve_results'){
  29     my $self = shift;
  30     my $c = shift;
  31     my $drop_menu_option = $c->req->param('db_table_list_id');
  32     my $q = "select * from audit.".$drop_menu_option.";";
  33
  34     my $h = $c->dbc->dbh->prepare($q);
  35     $h->execute();
  36     my @all_audits;
  37     my $counter = 0;
  38
  39     while (my ($audit_ts, $operation, $username, $logged_in_user, $before, $after, $transactioncode, $primary_key, $is_undo) = $h->fetchrow_array) {
  40         $all_audits[$counter] = [$audit_ts, $operation, $username, $logged_in_user, $before, $after, $transactioncode, $primary_key, $is_undo];
  41         $counter++;
  42         };
  43
  44
  45     my $json_string;
  46     $json_string = encode_json(\@all_audits);
  47     $c->stash->{rest} = {
  48         result => $json_string,
  49         };
  50 };
  51
  52 sub retrieve_table_names : Path('/ajax/audit/retrieve_table_names'){
  53     my $self = shift;
  54     my $c = shift;
  55     my $q = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'audit'";
  56     my $h = $c->dbc->dbh->prepare($q);
  57     $h->execute();
  58     my @ids;
  59     while (my ($drop_options) = $h->fetchrow_array) {
  60         push @ids, $drop_options;
  61
  62     };
  63     my $json_string;
  64     $json_string = encode_json(\@ids);
  65     $c->stash->{rest} = {
  66         result1 => $json_string,
  67         };
  68 };
  69
  70 sub retrieve_stock_audits : Path('/ajax/audit/retrieve_stock_audits'){
  71     my $self = shift;
  72     my $c = shift;
  73     my $stock_uniquename = $c->req->param('stock_uniquename');
  74     my $q = "SELECT * FROM audit.stock_audit;";
  75     my $h = $c->dbc->dbh->prepare($q);
  76     $h->execute();
  77     my @all_audits;
  78     my @before;
  79     my @after;
  80     my $counter = 0;
  81
  82     while (my ($audit_ts, $operation, $username, $logged_in_user, $before, $after, $transactioncode, $primary_key, $is_undo) = $h->fetchrow_array) {
  83         $after[$counter] = $after;
  84         $before[$counter] = $before;
  85         $all_audits[$counter] = [$audit_ts, $operation, $username, $logged_in_user, $before, $after, $transactioncode, $primary_key, $is_undo];
  86         $counter++;
  87         }
  88
  89
  90     my @matches;
  91     for (my $i = 0; $i<$counter; $i++){
  92         my $operation = $all_audits[$i][1];
  93         my $stock_json_string;
  94         eval {
  95             if ($operation eq "DELETE"){
  96                 $stock_json_string = decode_json($before[$i]);
  97             } else {
  98                 $stock_json_string = decode_json($after[$i]);
  99             }
 100         };
 101         if ($@) {
 102             warn "Failed to decode JSON at index $i: $@";
 103             next; # Skip this iteration in case of error
 104         }
 105         my $desired_uniquename = $stock_json_string->{'uniquename'};
 106         if($stock_uniquename eq $desired_uniquename){
 107             push @matches, $all_audits[$i];
 108         }
 109     }
 110
 111     my $stock_match_json;
 112     $stock_match_json = encode_json(\@matches);
 113
 114     $c->stash->{rest} = {
 115         stock_match_after => $stock_match_json,
 116     }
 117 };
 118
 119
 120 sub retrieve_trial_audits : Path('/ajax/audit/retrieve_trial_audits'){
 121     my $self = shift;
 122     my $c = shift;
 123     my $trial_id = $c->req->param('trial_id');
 124     my $q = "SELECT * FROM audit.project_audit;";
 125     my $h = $c->dbc->dbh->prepare($q);
 126     $h->execute();
 127     my @all_audits;
 128     my @before;
 129     my @after;
 130     my $counter = 0;
 131
 132     while (my ($audit_ts, $operation, $username, $logged_in_user, $before, $after, $transactioncode, $primary_key, $is_undo) = $h->fetchrow_array) {
 133         $after[$counter] = $after;
 134         $before[$counter] = $before;
 135         $all_audits[$counter] = [$audit_ts, $operation, $username, $logged_in_user, $before, $after, $transactioncode, $primary_key, $is_undo];
 136         $counter++;
 137         }
 138
 139
 140     my @matches;
 141     my $num_matches = 0; #this is to make sure only matched audits go into the matches array
 142
 143     for (my $i = 0; $i<$counter; $i++){
 144         my $operation = $all_audits[$i][1];
 145         my $json_string;
 146         if($operation eq "DELETE"){
 147             $json_string = decode_json($before[$i]);
 148         }else{
 149             $json_string = decode_json($after[$i]);
 150         }
 151         my $desired_trial_id = $json_string->{'project_id'};
 152
 153         if($trial_id eq $desired_trial_id){
 154
 155
 156             $matches[$num_matches] = $all_audits[$i];
 157             $num_matches++;
 158
 159         }
 160     }
 161
 162     my $match_trial_json;
 163     $match_trial_json = encode_json(\@matches);
 164
 165     $c->stash->{rest} = {
 166         match_project => $match_trial_json,
 167         }
 168 };