lib/SGN/Controller/DroneImageryUpload.pm

   1
   2 =head1 NAME
   3
   4 =head1 DESCRIPTION
   5
   6 =head1 AUTHOR
   7
   8 =cut
   9
  10 package SGN::Controller::DroneImagery::DroneImageryUpload;
  11
  12 use Moose;
  13 use Data::Dumper;
  14 use JSON;
  15 use SGN::Model::Cvterm;
  16 use DateTime;
  17 use Math::Round;
  18 use Time::Piece;
  19 use Time::Seconds;
  20 use SGN::Image;
  21 use CXGN::DroneImagery::ImagesSearch;
  22 use File::Basename qw | basename dirname|;
  23 use URI::Encode qw(uri_encode uri_decode);
  24 use CXGN::Calendar;
  25 use Image::Size;
  26 use CXGN::DroneImagery::ImageTypes;
  27 use LWP::UserAgent;
  28 use CXGN::ZipFile;
  29 use Text::CSV;
  30 use SGN::Controller::AJAX::DroneImagery::DroneImagery;
  31
  32 BEGIN { extends 'Catalyst::Controller'; }
  33
  34 sub upload_drone_imagery : Path("/drone_imagery/upload_drone_imagery") :Args(0) {
  35     my $self = shift;
  36     my $c = shift;
  37     $c->response->headers->header( "Access-Control-Allow-Origin" => '*' );
  38     $c->response->headers->header( "Access-Control-Allow-Methods" => "POST, GET, PUT, DELETE" );
  39     my $schema = $c->dbic_schema("Bio::Chado::Schema");
  40     my $metadata_schema = $c->dbic_schema("CXGN::Metadata::Schema");
  41     my $phenome_schema = $c->dbic_schema("CXGN::Phenome::Schema");
  42     my ($user_id, $user_name, $user_role) = _check_user_login($c);
  43     print STDERR Dumper $c->req->params();
  44
  45     my $selected_trial_id = $c->req->param('drone_run_field_trial_id');
  46     if (!$selected_trial_id) {
  47         $c->stash->{message} = "Please select a field trial!";
  48         $c->stash->{template} = 'generic_message.mas';
  49         return;
  50     }
  51     my $selected_drone_run_id = $c->req->param('drone_run_id');
  52     my $new_drone_run_name = $c->req->param('drone_run_name');
  53     my $new_drone_run_type = $c->req->param('drone_run_type');
  54     my $new_drone_run_date = $c->req->param('drone_run_date');
  55     my $new_drone_base_date = $c->req->param('drone_run_base_date');
  56     my $new_drone_run_desc = $c->req->param('drone_run_description');
  57     my $new_drone_rig_desc = $c->req->param('drone_run_camera_rig_description');
  58     my $new_drone_run_vehicle_id = $c->req->param('drone_run_imaging_vehicle_id');
  59     my $new_drone_run_battery_name = $c->req->param('drone_run_imaging_vehicle_battery_name');
  60
  61     my $dir = $c->tempfiles_subdir('/upload_drone_imagery_bulk_previous');
  62
  63     if (!$new_drone_run_vehicle_id) {
  64         $c->stash->{message} = "Please give an imaging event vehicle id!";
  65         $c->stash->{template} = 'generic_message.mas';
  66         return;
  67     }
  68
  69     if (!$selected_drone_run_id && !$new_drone_run_name) {
  70         $c->stash->{message} = "Please select an imaging event or create a new imaging event!";
  71         $c->stash->{template} = 'generic_message.mas';
  72         return;
  73     }
  74     if ($new_drone_run_name && !$new_drone_run_type){
  75         $c->stash->{message} = "Please give a new imaging event type!";
  76         $c->stash->{template} = 'generic_message.mas';
  77         return;
  78     }
  79     if ($new_drone_run_name && !$new_drone_run_date){
  80         $c->stash->{message} = "Please give a new imaging event date!";
  81         $c->stash->{template} = 'generic_message.mas';
  82         return;
  83     }
  84     if ($new_drone_run_name && $new_drone_run_date !~ /^\d{4}\/\d{2}\/\d{2}\s\d\d:\d\d:\d\d$/){
  85         $c->stash->{message} = "Please give a new imaging event date in the format YYYY/MM/DD HH:mm:ss!";
  86         $c->stash->{template} = 'generic_message.mas';
  87         return;
  88     }
  89     if ($new_drone_run_name && $new_drone_base_date && $new_drone_base_date !~ /^\d{4}\/\d{2}\/\d{2}\s\d\d:\d\d:\d\d$/){
  90         $c->stash->{message} = "Please give a new imaging event base date in the format YYYY/MM/DD HH:mm:ss!";
  91         $c->stash->{template} = 'generic_message.mas';
  92         return;
  93     }
  94
  95     if ($new_drone_run_name && !$new_drone_run_desc){
  96         $c->stash->{message} = "Please give a new imaging event description!";
  97         $c->stash->{template} = 'generic_message.mas';
  98         return;
  99     }
 100
 101     my $new_drone_run_camera_info = $c->req->param('drone_image_upload_camera_info');
 102     my $new_drone_run_band_numbers = $c->req->param('drone_run_band_number');
 103     my $new_drone_run_band_stitching = $c->req->param('drone_image_upload_drone_run_band_stitching');
 104     my $new_drone_run_band_stitching_odm_more_images = 'No';
 105     my $new_drone_run_band_stitching_odm_current_image_count = 0;
 106     my $new_drone_run_band_stitching_odm_radiocalibration = $c->req->param('drone_image_upload_drone_run_band_stitching_odm_radiocalibration') eq "Yes" ? 1 : 0;
 107
 108     if (!$new_drone_run_camera_info) {
 109         $c->stash->{message} = "Please indicate the type of camera!";
 110         $c->stash->{template} = 'generic_message.mas';
 111         return;
 112     }
 113
 114     if ($new_drone_run_band_stitching eq 'no' && !$new_drone_run_band_numbers) {
 115         $c->stash->{message} = "Please give the number of new imaging event bands!";
 116         $c->stash->{template} = 'generic_message.mas';
 117         return;
 118     }
 119     if (!$new_drone_run_band_stitching) {
 120         $c->stash->{message} = "Please indicate if the images are stitched!";
 121         $c->stash->{template} = 'generic_message.mas';
 122         return;
 123     }
 124
 125     my $odm_process_running_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_opendronemap_process_running', 'project_property')->cvterm_id();
 126     if ($new_drone_run_band_stitching eq 'yes_open_data_map_stitch') {
 127         my $upload_file = $c->req->upload('upload_drone_images_zipfile');
 128         my $upload_panel_file = $c->req->upload('upload_drone_images_panel_zipfile');
 129         if (!$upload_file) {
 130             $c->stash->{message} = "Please provide a zipfile of raw images!";
 131             $c->stash->{template} = 'generic_message.mas';
 132             return;
 133         }
 134         if (!$upload_panel_file && $new_drone_run_camera_info eq 'micasense_5') {
 135             $c->stash->{message} = "Please provide a zipfile of images of the Micasense radiometric calibration panels!";
 136             $c->stash->{template} = 'generic_message.mas';
 137             return;
 138         }
 139
 140         my $q = "SELECT count(*) FROM projectprop WHERE type_id=$odm_process_running_cvterm_id AND value='1';";
 141         my $h = $schema->storage->dbh()->prepare($q);
 142         $h->execute();
 143         my ($odm_running_count) = $h->fetchrow_array();
 144         if ($odm_running_count >= $c->config->{opendronemap_max_processes}) {
 145             $c->stash->{message} = "There are already the maximum number of OpenDroneMap processes running on this machine! Please check back later when those processes are complete.";
 146             $c->stash->{template} = 'generic_message.mas';
 147             return;
 148         }
 149     }
 150
 151     # if ($selected_drone_run_id && ($new_drone_run_band_stitching eq 'yes' || $new_drone_run_band_stitching eq 'yes_raw' || $new_drone_run_band_stitching eq 'yes_automated')) {
 152     #     $c->stash->{rest} = { error => "Please create a new drone run if you are uploading a zipfile of raw images!" };
 153     #     $c->detach();
 154     # }
 155
 156     my $log_file_path = '';
 157     if ($c->config->{error_log}){
 158         $log_file_path = "--log_file_path '".$c->config->{error_log}."'";
 159     }
 160
 161     my $drone_run_nd_experiment_id;
 162     if (!$selected_drone_run_id) {
 163         my $drone_run_field_trial_project_relationship_type_id_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_on_field_trial', 'project_relationship')->cvterm_id();
 164         my $drone_run_band_drone_run_project_relationship_type_id_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_on_drone_run', 'project_relationship')->cvterm_id();
 165         my $project_start_date_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project_start_date', 'project_property')->cvterm_id();
 166
 167         my $calendar_funcs = CXGN::Calendar->new({});
 168
 169         my %seen_field_trial_drone_run_dates;
 170         my $drone_run_date_q = "SELECT drone_run_date.value
 171             FROM project AS drone_run_band_project
 172             JOIN project_relationship AS drone_run_band_rel ON (drone_run_band_rel.subject_project_id = drone_run_band_project.project_id AND drone_run_band_rel.type_id = $drone_run_band_drone_run_project_relationship_type_id_cvterm_id)
 173             JOIN project AS drone_run_project ON (drone_run_band_rel.object_project_id = drone_run_project.project_id)
 174             JOIN projectprop AS drone_run_date ON(drone_run_project.project_id=drone_run_date.project_id AND drone_run_date.type_id=$project_start_date_type_id)
 175             JOIN project_relationship AS field_trial_rel ON (drone_run_project.project_id = field_trial_rel.subject_project_id AND field_trial_rel.type_id=$drone_run_field_trial_project_relationship_type_id_cvterm_id)
 176             JOIN project AS field_trial ON (field_trial_rel.object_project_id = field_trial.project_id)
 177             WHERE field_trial.project_id = ?;";
 178         my $drone_run_date_h = $schema->storage->dbh()->prepare($drone_run_date_q);
 179         $drone_run_date_h->execute($selected_trial_id);
 180         while( my ($drone_run_date) = $drone_run_date_h->fetchrow_array()) {
 181             my $drone_run_date_formatted = $drone_run_date ? $calendar_funcs->display_start_date($drone_run_date) : '';
 182             if ($drone_run_date_formatted) {
 183                 my $date_obj = Time::Piece->strptime($drone_run_date_formatted, "%Y-%B-%d %H:%M:%S");
 184                 my $epoch_seconds = $date_obj->epoch;
 185                 $seen_field_trial_drone_run_dates{$epoch_seconds}++;
 186             }
 187         }
 188         my $drone_run_date_obj = Time::Piece->strptime($new_drone_run_date, "%Y/%m/%d %H:%M:%S");
 189         if (exists($seen_field_trial_drone_run_dates{$drone_run_date_obj->epoch})) {
 190             $c->stash->{rest} = { error => "An imaging event has already occured on this field trial at the same date and time! Please give a unique date/time for each imaging event!" };
 191             $c->detach();
 192         }
 193
 194         my $trial = CXGN::Trial->new({ bcs_schema => $schema, trial_id => $selected_trial_id });
 195         my $trial_location_id = $trial->get_location()->[0];
 196         my $planting_date = $trial->get_planting_date();
 197         my $planting_date_time_object = Time::Piece->strptime($planting_date, "%Y-%B-%d");
 198         my $drone_run_date_time_object = Time::Piece->strptime($new_drone_run_date, "%Y/%m/%d %H:%M:%S");
 199         my $time_diff;
 200         my $base_date_event;
 201         if ($new_drone_base_date) {
 202             my $imaging_event_base_date_time_object = Time::Piece->strptime($new_drone_base_date, "%Y/%m/%d %H:%M:%S");
 203             $time_diff = $drone_run_date_time_object - $imaging_event_base_date_time_object;
 204             $base_date_event = $calendar_funcs->check_value_format($new_drone_base_date);
 205         }
 206         else {
 207             $time_diff = $drone_run_date_time_object - $planting_date_time_object;
 208         }
 209         my $time_diff_weeks = $time_diff->weeks;
 210         my $time_diff_days = $time_diff->days;
 211         my $time_diff_hours = $time_diff->hours;
 212         my $rounded_time_diff_weeks = round($time_diff_weeks);
 213         if ($rounded_time_diff_weeks == 0) {
 214             $rounded_time_diff_weeks = 1;
 215         }
 216
 217         my $week_term_string = "week $rounded_time_diff_weeks";
 218         my $q = "SELECT t.cvterm_id FROM cvterm as t JOIN cv ON(t.cv_id=cv.cv_id) WHERE t.name=? and cv.name=?;";
 219         my $h = $schema->storage->dbh()->prepare($q);
 220         $h->execute($week_term_string, 'cxgn_time_ontology');
 221         my ($week_cvterm_id) = $h->fetchrow_array();
 222
 223         if (!$week_cvterm_id) {
 224             my $new_week_term = $schema->resultset("Cv::Cvterm")->create_with({
 225                name => $week_term_string,
 226                cv => 'cxgn_time_ontology'
 227             });
 228             $week_cvterm_id = $new_week_term->cvterm_id();
 229         }
 230
 231         my $day_term_string = "day $time_diff_days";
 232         $h->execute($day_term_string, 'cxgn_time_ontology');
 233         my ($day_cvterm_id) = $h->fetchrow_array();
 234
 235         if (!$day_cvterm_id) {
 236             my $new_day_term = $schema->resultset("Cv::Cvterm")->create_with({
 237                name => $day_term_string,
 238                cv => 'cxgn_time_ontology'
 239             });
 240             $day_cvterm_id = $new_day_term->cvterm_id();
 241         }
 242
 243         my $week_term = SGN::Model::Cvterm::get_trait_from_cvterm_id($schema, $week_cvterm_id, 'extended');
 244         my $day_term = SGN::Model::Cvterm::get_trait_from_cvterm_id($schema, $day_cvterm_id, 'extended');
 245
 246         my %related_cvterms = (
 247             week => $week_term,
 248             day => $day_term
 249         );
 250
 251         my $drone_run_event = $calendar_funcs->check_value_format($new_drone_run_date);
 252         my $drone_run_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_experiment', 'experiment_type')->cvterm_id();
 253         my $design_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'design', 'project_property')->cvterm_id();
 254         my $drone_run_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_project_type', 'project_property')->cvterm_id();
 255         my $drone_run_is_raw_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_is_raw_images', 'project_property')->cvterm_id();
 256         my $drone_run_camera_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_camera_type', 'project_property')->cvterm_id();
 257         my $drone_run_base_date_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_base_date', 'project_property')->cvterm_id();
 258         my $drone_run_rig_desc_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_camera_rig_description', 'project_property')->cvterm_id();
 259         my $drone_run_related_cvterms_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_related_time_cvterms_json', 'project_property')->cvterm_id();
 260         my $project_relationship_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_on_field_trial', 'project_relationship')->cvterm_id();
 261         my $imaging_vehicle_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'imaging_event_vehicle', 'stock_type')->cvterm_id();
 262         my $imaging_vehicle_properties_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'imaging_event_vehicle_json', 'stock_property')->cvterm_id();
 263
 264         my $drone_run_projectprops = [
 265             {type_id => $drone_run_type_cvterm_id, value => $new_drone_run_type},
 266             {type_id => $project_start_date_type_id, value => $drone_run_event},
 267             {type_id => $design_cvterm_id, value => 'drone_run'},
 268             {type_id => $drone_run_camera_type_cvterm_id, value => $new_drone_run_camera_info},
 269             {type_id => $drone_run_related_cvterms_cvterm_id, value => encode_json \%related_cvterms}
 270         ];
 271
 272         if ($new_drone_run_band_stitching ne 'no') {
 273             push @$drone_run_projectprops, {type_id => $drone_run_is_raw_cvterm_id, value => 1};
 274         }
 275         if ($new_drone_base_date) {
 276             push @$drone_run_projectprops, {type_id => $drone_run_base_date_type_id, value => $base_date_event};
 277         }
 278         if ($new_drone_rig_desc) {
 279             push @$drone_run_projectprops, {type_id => $drone_run_rig_desc_type_id, value => $new_drone_rig_desc};
 280         }
 281
 282         my $nd_experiment_rs = $schema->resultset("NaturalDiversity::NdExperiment")->create({
 283             nd_geolocation_id => $trial_location_id,
 284             type_id => $drone_run_experiment_type_id,
 285             nd_experiment_stocks => [{stock_id => $new_drone_run_vehicle_id, type_id => $drone_run_experiment_type_id}]
 286         });
 287         $drone_run_nd_experiment_id = $nd_experiment_rs->nd_experiment_id();
 288
 289         my $project_rs = $schema->resultset("Project::Project")->create({
 290             name => $new_drone_run_name,
 291             description => $new_drone_run_desc,
 292             projectprops => $drone_run_projectprops,
 293             project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_trial_id}],
 294             nd_experiment_projects => [{nd_experiment_id => $drone_run_nd_experiment_id}]
 295         });
 296         $selected_drone_run_id = $project_rs->project_id();
 297
 298         my $vehicle_prop = decode_json $schema->resultset("Stock::Stockprop")->search({stock_id => $new_drone_run_vehicle_id, type_id=>$imaging_vehicle_properties_cvterm_id})->first()->value();
 299         $vehicle_prop->{batteries}->{$new_drone_run_battery_name}->{usage}++;
 300         my $vehicle_prop_update = $schema->resultset('Stock::Stockprop')->update_or_create({
 301             type_id=>$imaging_vehicle_properties_cvterm_id,
 302             stock_id=>$new_drone_run_vehicle_id,
 303             rank=>0,
 304             value=>encode_json $vehicle_prop
 305         },
 306         {
 307             key=>'stockprop_c1'
 308         });
 309     }
 310
 311     my $drone_run_band_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_project_type', 'project_property')->cvterm_id();
 312     my $design_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'design', 'project_property')->cvterm_id();
 313     my $project_relationship_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_on_drone_run', 'project_relationship')->cvterm_id();
 314     my $geoparam_coordinates_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_geoparam_coordinates_type', 'project_property')->cvterm_id();
 315     my $geoparam_coordinates_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_geoparam_coordinates', 'project_property')->cvterm_id();
 316
 317     my @return_drone_run_band_project_ids;
 318     my @return_drone_run_band_image_ids;
 319     my @return_drone_run_band_image_urls;
 320     my @raw_image_boundaries_temp_images;
 321     my %saved_image_stacks;
 322     my $output_path;
 323     my $alignment_output_path;
 324     my $cmd;
 325
 326     my $image_types_allowed = CXGN::DroneImagery::ImageTypes::get_all_drone_run_band_image_types()->{hash_ref};
 327     my %seen_image_types_upload;
 328
 329     if ($new_drone_run_band_stitching eq 'no') {
 330         my @new_drone_run_bands;
 331         if ($new_drone_run_band_numbers eq 'one_bw' || $new_drone_run_band_numbers eq 'one_rgb') {
 332             my $new_drone_run_band_name = $c->req->param('drone_run_band_name_1');
 333             my $new_drone_run_band_desc = $c->req->param('drone_run_band_description_1');
 334             my $new_drone_run_band_type = $c->req->param('drone_run_band_type_1');
 335             my $new_drone_run_band_coordinate_system = $c->req->param('drone_run_band_coordinate_system_1') || 'Pixels';
 336             if (!$new_drone_run_band_name) {
 337                 $c->stash->{message} = "Please give a new imaging event band name!";
 338                 $c->stash->{template} = 'generic_message.mas';
 339                 return;
 340             }
 341             if (!$new_drone_run_band_desc){
 342                 $c->stash->{message} = "Please give a new imaging event band description!";
 343                 $c->stash->{template} = 'generic_message.mas';
 344                 return;
 345             }
 346             if (!$new_drone_run_band_type){
 347                 $c->stash->{message} = "Please give a new imaging event band type!";
 348                 $c->stash->{template} = 'generic_message.mas';
 349                 return;
 350             }
 351             if (!exists($image_types_allowed->{$new_drone_run_band_type})) {
 352                 $c->stash->{message} = "Imaging event band type not supported: $new_drone_run_band_type!";
 353                 $c->stash->{template} = 'generic_message.mas';
 354                 return;
 355             }
 356             if (exists($seen_image_types_upload{$new_drone_run_band_type})) {
 357                 $c->stash->{message} = "Imaging event band type is repeated: $new_drone_run_band_type! Each imaging event band in an imaging event should have a unique type!";
 358                 $c->stash->{template} = 'generic_message.mas';
 359                 return;
 360             }
 361             $seen_image_types_upload{$new_drone_run_band_type}++;
 362
 363             my $upload_file = $c->req->upload('drone_run_band_stitched_ortho_image_1');
 364             if (!$upload_file) {
 365                 $c->stash->{message} = "Please provide a zipfile OR a stitched ortho image!";
 366                 $c->stash->{template} = 'generic_message.mas';
 367                 return;
 368             }
 369
 370             push @new_drone_run_bands, {
 371                 name => $new_drone_run_band_name,
 372                 description => $new_drone_run_band_desc,
 373                 type => $new_drone_run_band_type,
 374                 upload_file => $upload_file,
 375                 coordinate_system => $new_drone_run_band_coordinate_system
 376             };
 377         } else {
 378             foreach (1..$new_drone_run_band_numbers) {
 379                 my $new_drone_run_band_name = $c->req->param('drone_run_band_name_'.$_);
 380                 my $new_drone_run_band_desc = $c->req->param('drone_run_band_description_'.$_);
 381                 my $new_drone_run_band_type = $c->req->param('drone_run_band_type_'.$_);
 382                 my $new_drone_run_band_coordinate_system = $c->req->param('drone_run_band_coordinate_system_'.$_) || 'Pixels';
 383                 if (!$new_drone_run_band_name) {
 384                     $c->stash->{message} = "Please give a new imaging event band name!".$_;
 385                     $c->stash->{template} = 'generic_message.mas';
 386                     return;
 387                 }
 388                 if (!$new_drone_run_band_desc){
 389                     $c->stash->{message} = "Please give a new imaging event band description!";
 390                     $c->stash->{template} = 'generic_message.mas';
 391                     return;
 392                 }
 393                 if (!$new_drone_run_band_type){
 394                     $c->stash->{message} = "Please give a new imaging event band type!";
 395                     $c->stash->{template} = 'generic_message.mas';
 396                     return;
 397                 }
 398                 if (!exists($image_types_allowed->{$new_drone_run_band_type})) {
 399                     $c->stash->{message} = "Imaging event band type not supported: $new_drone_run_band_type!";
 400                     $c->stash->{template} = 'generic_message.mas';
 401                     return;
 402                 }
 403                 if (exists($seen_image_types_upload{$new_drone_run_band_type})) {
 404                     $c->stash->{message} = "Imaging event band type is repeated: $new_drone_run_band_type! Each imaging event band in an imaging event should have a unique type!";
 405                     $c->stash->{template} = 'generic_message.mas';
 406                     return;
 407                 }
 408                 $seen_image_types_upload{$new_drone_run_band_type}++;
 409
 410                 my $upload_file = $c->req->upload('drone_run_band_stitched_ortho_image_'.$_);
 411                 if (!$upload_file) {
 412                     $c->stash->{message} = "Please provide a zipfile OR a stitched ortho image!";
 413                     $c->stash->{template} = 'generic_message.mas';
 414                     return;
 415                 }
 416
 417                 push @new_drone_run_bands, {
 418                     name => $new_drone_run_band_name,
 419                     description => $new_drone_run_band_desc,
 420                     type => $new_drone_run_band_type,
 421                     upload_file => $upload_file,
 422                     coordinate_system => $new_drone_run_band_coordinate_system
 423                 };
 424             }
 425         }
 426         foreach (@new_drone_run_bands) {
 427             my $coordinate_system = $_->{coordinate_system};
 428             my $drone_run_band_type = $_->{type};
 429             my $upload_file = $_->{upload_file};
 430             my $new_drone_run_input_image = $upload_file->tempname;
 431
 432             my @drone_run_band_projectprops = (
 433                 {type_id => $drone_run_band_type_cvterm_id, value => $drone_run_band_type},
 434                 {type_id => $design_cvterm_id, value => 'drone_run_band'},
 435                 {type_id => $geoparam_coordinates_type_cvterm_id, value => $coordinate_system}
 436             );
 437
 438             my $time = DateTime->now();
 439             my $timestamp = $time->ymd()."_".$time->hms();
 440             my $upload_original_name = $selected_drone_run_id."_".$drone_run_band_type.".png";
 441
 442             my $ortho_file;
 443             my @geoparams_coordinates;
 444             if ($coordinate_system eq 'Pixels') {
 445                 $ortho_file = $new_drone_run_input_image;
 446             }
 447             else {
 448                 my @geoparams_coordinates;
 449                 if ($drone_run_band_type eq 'RGB Color Image') {
 450                     my $outfile_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
 451                     my $outfile_image_r = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
 452                     my $outfile_image_g = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
 453                     my $outfile_image_b = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
 454                     my $outfile_geoparams = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/fileXXXX').".csv";
 455
 456                     my $geo_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/GDALOpenImageRGBGeoTiff.py --image_path $new_drone_run_input_image --outfile_path_image $outfile_image --outfile_path_image_1 $outfile_image_r --outfile_path_image_2 $outfile_image_g --outfile_path_image_3 $outfile_image_b --outfile_path_geo_params $outfile_geoparams ";
 457                     print STDERR $geo_cmd."\n";
 458                     my $geo_cmd_status = system($geo_cmd);
 459                     $ortho_file = $outfile_image;
 460
 461                     open(my $fh_geoparams, '<', $outfile_geoparams) or die "Could not open file '".$outfile_geoparams."' $!";
 462                         print STDERR "Opened ".$outfile_geoparams."\n";
 463                         my $geoparams = <$fh_geoparams>;
 464                         chomp $geoparams;
 465                         @geoparams_coordinates = split ',', $geoparams;
 466                         print STDERR Dumper [$geoparams, \@geoparams_coordinates];
 467                     close($fh_geoparams);
 468                 }
 469                 else {
 470                     my $outfile_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
 471                     my $outfile_geoparams = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/fileXXXX').".csv";
 472
 473                     my $geo_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/GDALOpenSingleChannelImageGeoTiff.py --image_path $new_drone_run_input_image --outfile_path_image $outfile_image --outfile_path_geo_params $outfile_geoparams ";
 474                     print STDERR $geo_cmd."\n";
 475                     my $geo_cmd_status = system($geo_cmd);
 476                     $ortho_file = $outfile_image;
 477
 478                     open(my $fh_geoparams, '<', $outfile_geoparams) or die "Could not open file '".$outfile_geoparams."' $!";
 479                         print STDERR "Opened ".$outfile_geoparams."\n";
 480                         my $geoparams = <$fh_geoparams>;
 481                         chomp $geoparams;
 482                         @geoparams_coordinates = split ',', $geoparams;
 483                         print STDERR Dumper [$geoparams, \@geoparams_coordinates];
 484                     close($fh_geoparams);
 485                 }
 486
 487                 push @drone_run_band_projectprops, {type_id => $geoparam_coordinates_cvterm_id, value => encode_json \@geoparams_coordinates};
 488             }
 489
 490             my $project_rs = $schema->resultset("Project::Project")->create({
 491                 name => $_->{name},
 492                 description => $_->{description},
 493                 projectprops => \@drone_run_band_projectprops,
 494                 project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_drone_run_id}]
 495             });
 496             my $selected_drone_run_band_id = $project_rs->project_id();
 497
 498             my $uploader = CXGN::UploadFile->new({
 499                 tempfile => $ortho_file,
 500                 subdirectory => "drone_imagery_upload",
 501                 archive_path => $c->config->{archive_path},
 502                 archive_filename => $upload_original_name,
 503                 timestamp => $timestamp,
 504                 user_id => $user_id,
 505                 user_role => $user_role
 506             });
 507             my $archived_filename_with_path = $uploader->archive();
 508             my $md5 = $uploader->get_md5($archived_filename_with_path);
 509             if (!$archived_filename_with_path) {
 510                 $c->stash->{message} = "Could not save file $upload_original_name in archive.";
 511                 $c->stash->{template} = 'generic_message.mas';
 512                 return;
 513             }
 514             unlink $new_drone_run_input_image;
 515             print STDERR "Archived Drone Image File: $archived_filename_with_path\n";
 516
 517             my ($check_image_width, $check_image_height) = imgsize($archived_filename_with_path);
 518             if ($check_image_width > 16384) {
 519                 my $cmd_resize = $c->config->{python_executable}.' '.$c->config->{rootpath}.'/DroneImageScripts/ImageProcess/Resize.py --image_path \''.$archived_filename_with_path.'\' --outfile_path \''.$archived_filename_with_path.'\' --width 16384';
 520                 print STDERR Dumper $cmd_resize;
 521                 my $status_resize = system($cmd_resize);
 522             }
 523             elsif ($check_image_height > 16384) {
 524                 my $cmd_resize = $c->config->{python_executable}.' '.$c->config->{rootpath}.'/DroneImageScripts/ImageProcess/Resize.py --image_path \''.$archived_filename_with_path.'\' --outfile_path \''.$archived_filename_with_path.'\' --height 16384';
 525                 print STDERR Dumper $cmd_resize;
 526                 my $status_resize = system($cmd_resize);
 527             }
 528
 529             my $image = SGN::Image->new( $schema->storage->dbh, undef, $c );
 530             $image->set_sp_person_id($user_id);
 531             my $linking_table_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stitched_drone_imagery', 'project_md_image')->cvterm_id();
 532             my $ret = $image->process_image($archived_filename_with_path, 'project', $selected_drone_run_band_id, $linking_table_type_id);
 533             push @return_drone_run_band_image_urls, $image->get_image_url('original');
 534             push @return_drone_run_band_image_ids, $image->get_image_id();
 535             push @return_drone_run_band_project_ids, $selected_drone_run_band_id;
 536         }
 537     }
 538     elsif ($new_drone_run_band_stitching eq 'yes_open_data_map_stitch') {
 539         my $upload_file = $c->req->upload('upload_drone_images_zipfile');
 540         my $upload_panel_file = $c->req->upload('upload_drone_images_panel_zipfile');
 541
 542         my $upload_original_name = $upload_file->filename();
 543         my $upload_tempfile = $upload_file->tempname;
 544         my $time = DateTime->now();
 545         my $timestamp = $time->ymd()."_".$time->hms();
 546         print STDERR Dumper [$upload_original_name, $upload_tempfile];
 547
 548         my $uploader = CXGN::UploadFile->new({
 549             tempfile => $upload_tempfile,
 550             subdirectory => "drone_imagery_upload_odm_zips",
 551             second_subdirectory => "$selected_drone_run_id",
 552             archive_path => $c->config->{archive_path},
 553             archive_filename => $upload_original_name,
 554             timestamp => $timestamp,
 555             user_id => $user_id,
 556             user_role => $user_role
 557         });
 558         my $archived_filename_with_path = $uploader->archive();
 559         my $md5 = $uploader->get_md5($archived_filename_with_path);
 560         if (!$archived_filename_with_path) {
 561             $c->stash->{message} = "Could not save file $upload_original_name in archive.";
 562             $c->stash->{template} = 'generic_message.mas';
 563             return;
 564         }
 565         unlink $upload_tempfile;
 566         print STDERR "Archived Drone Image ODM Zip File: $archived_filename_with_path\n";
 567
 568         my $image = SGN::Image->new( $c->dbc->dbh, undef, $c );
 569         my $zipfile_return = $image->upload_drone_imagery_zipfile($archived_filename_with_path, $user_id, $selected_drone_run_id);
 570         # print STDERR Dumper $zipfile_return;
 571         if ($zipfile_return->{error}) {
 572             $c->stash->{message} = "Problem saving images!".$zipfile_return->{error};
 573             $c->stash->{template} = 'generic_message.mas';
 574             return;
 575         }
 576         my $image_paths = $zipfile_return->{image_files};
 577
 578         my $example_archived_filename_with_path_odm_img;
 579         foreach my $i (@$image_paths) {
 580             my $uploader_odm_dir = CXGN::UploadFile->new({
 581                 tempfile => $i,
 582                 subdirectory => "drone_imagery_upload_odm_dir",
 583                 second_subdirectory => "$selected_drone_run_id",
 584                 third_subdirectory => 'images',
 585                 archive_path => $c->config->{archive_path},
 586                 archive_filename => basename($i),
 587                 timestamp => $timestamp,
 588                 user_id => $user_id,
 589                 user_role => $user_role,
 590                 include_timestamp => 0
 591             });
 592             my $archived_filename_with_path_odm_img = $uploader_odm_dir->archive();
 593             my $md5 = $uploader_odm_dir->get_md5($archived_filename_with_path_odm_img);
 594             if (!$archived_filename_with_path_odm_img) {
 595                 $c->stash->{message} = "Could not save file $i in archive.";
 596                 $c->stash->{template} = 'generic_message.mas';
 597                 return;
 598             }
 599             print STDERR "Archived Drone Image ODM IMG File: $archived_filename_with_path_odm_img\n";
 600             $example_archived_filename_with_path_odm_img = $archived_filename_with_path_odm_img;
 601         }
 602
 603         my $current_odm_image_count = $new_drone_run_band_stitching_odm_current_image_count+scalar(@$image_paths);
 604         if ($current_odm_image_count < 25) {
 605             $c->stash->{message} = "Upload more than $current_odm_image_count images! Atleast 25 are required for OpenDroneMap to stitch.";
 606             $c->stash->{template} = 'generic_message.mas';
 607             return;
 608         }
 609
 610         print STDERR $example_archived_filename_with_path_odm_img."\n";
 611         my @img_path_split = split '\/', $example_archived_filename_with_path_odm_img;
 612         my $image_path_img_name = pop(@img_path_split);
 613         my $image_path_project_name = pop(@img_path_split);
 614         my $image_path_remaining = join '/', @img_path_split;
 615         # my $image_path_remaining_host = $image_path_remaining =~ s/cxgn\/sgn\/static\/documents\/tempfiles/tmp\/breedbase\-site/gr;
 616         my $hostpath = $c->config->{hostpath_archive};
 617         my $image_path_remaining_host = $image_path_remaining =~ s/\/home\/production/$hostpath/gr;
 618         print STDERR Dumper [$image_path_img_name, $image_path_project_name, $image_path_remaining, $image_path_remaining_host];
 619
 620         my $dir = $c->tempfiles_subdir('/upload_drone_imagery_raw_images');
 621         my $temp_file_docker_log = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_raw_images/fileXXXX');
 622
 623         my $odm_check_prop = $schema->resultset("Project::Projectprop")->find_or_create({
 624             project_id => $selected_drone_run_id,
 625             type_id => $odm_process_running_cvterm_id
 626         });
 627         $odm_check_prop->value('1');
 628         $odm_check_prop->update();
 629
 630         my @stitched_bands;
 631         my %raw_image_bands;
 632         eval {
 633             if ($new_drone_run_camera_info eq 'micasense_5') {
 634                 my $upload_panel_original_name = $upload_panel_file->filename();
 635                 my $upload_panel_tempfile = $upload_panel_file->tempname;
 636
 637                 my $uploader_panel = CXGN::UploadFile->new({
 638                     tempfile => $upload_panel_tempfile,
 639                     subdirectory => "drone_imagery_upload_odm_panel_zips",
 640                     second_subdirectory => "$selected_drone_run_id",
 641                     archive_path => $c->config->{archive_path},
 642                     archive_filename => $upload_panel_original_name,
 643                     timestamp => $timestamp,
 644                     user_id => $user_id,
 645                     user_role => $user_role
 646                 });
 647                 my $archived_filename_panel_with_path = $uploader_panel->archive();
 648                 my $md5_panel = $uploader_panel->get_md5($archived_filename_panel_with_path);
 649                 if (!$archived_filename_panel_with_path) {
 650                     $c->stash->{message} = "Could not save file $archived_filename_panel_with_path in archive.";
 651                     $c->stash->{template} = 'generic_message.mas';
 652                     return;
 653                 }
 654                 unlink $upload_panel_tempfile;
 655                 print STDERR "Archived Drone Image ODM Zip File: $archived_filename_panel_with_path\n";
 656
 657                 # my $dtm_string = '';
 658                 # my $ua       = LWP::UserAgent->new();
 659                 # my $response = $ua->post( $c->config->{main_production_site_url}."/RunODMDocker.php", { 'file_path' => $image_path_remaining_host, 'dtm_string' => $dtm_string } );
 660                 # my $content  = $response->decoded_content();
 661                 # print STDERR Dumper $content;
 662
 663                 my $odm_radiometric_calibration = $new_drone_run_band_stitching_odm_radiocalibration ? '--radiometric-calibration camera' : '';
 664
 665                 my $odm_command = 'docker run --rm -v /var/run/docker.sock:/var/run/docker.sock -v '.$image_path_remaining_host.':/datasets/code opendronemap/odm --project-path /datasets --rerun-all --dsm --dtm '.$odm_radiometric_calibration.' > '.$temp_file_docker_log;
 666                 print STDERR $odm_command."\n";
 667                 my $odm_status = system($odm_command);
 668
 669                 my $odm_b1 = "$image_path_remaining/odm_orthophoto/b1.png";
 670                 my $odm_b2 = "$image_path_remaining/odm_orthophoto/b2.png";
 671                 my $odm_b3 = "$image_path_remaining/odm_orthophoto/b3.png";
 672                 my $odm_b4 = "$image_path_remaining/odm_orthophoto/b4.png";
 673                 my $odm_b5 = "$image_path_remaining/odm_orthophoto/b5.png";
 674                 my $odm_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/ODMOpenImage.py --image_path $image_path_remaining/odm_orthophoto/odm_orthophoto.tif --outfile_path_b1 $odm_b1 --outfile_path_b2 $odm_b2 --outfile_path_b3 $odm_b3 --outfile_path_b4 $odm_b4 --outfile_path_b5 $odm_b5 --odm_radiocalibrated True";
 675                 my $odm_open_status = system($odm_cmd);
 676
 677                 my $odm_dsm_png = "$image_path_remaining/odm_dem/dsm.png";
 678                 my $odm_dtm_png = "$image_path_remaining/odm_dem/dtm.png";
 679                 my $odm_subtract_png = "$image_path_remaining/odm_dem/subtract.png";
 680                 my $odm_dem_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/ODMOpenImageDSM.py --image_path_dsm $image_path_remaining/odm_dem/dsm.tif --image_path_dtm $image_path_remaining/odm_dem/dtm.tif --outfile_path_dsm $odm_dsm_png --outfile_path_dtm $odm_dtm_png --outfile_path_subtract $odm_subtract_png --band_number 1";
 681                 my $odm_dem_open_status = system($odm_dem_cmd);
 682
 683                 @stitched_bands = (
 684                     ["Band 1", "OpenDroneMap Blue", "Blue (450-520nm)", $odm_b1],
 685                     ["Band 2", "OpenDroneMap Green", "Green (515-600nm)", $odm_b2],
 686                     ["Band 3", "OpenDroneMap Red", "Red (600-690nm)", $odm_b3],
 687                     ["Band 4", "OpenDroneMap NIR", "NIR (780-3000nm)", $odm_b4],
 688                     ["Band 5", "OpenDroneMap RedEdge", "Red Edge (690-750nm)", $odm_b5],
 689                     ["Band 6", "OpenDroneMap DSM", "Black and White Image", $odm_dsm_png]
 690                 );
 691             }
 692             elsif ($new_drone_run_camera_info eq 'ccd_color' || $new_drone_run_camera_info eq 'cmos_color') {
 693                 my $odm_command = 'docker run --rm -v /var/run/docker.sock:/var/run/docker.sock -v '.$image_path_remaining_host.':/datasets/code opendronemap/odm --project-path /datasets --rerun-all --dsm --dtm > '.$temp_file_docker_log;
 694                 print STDERR $odm_command."\n";
 695                 my $odm_status = system($odm_command);
 696
 697                 my $odm_dsm_png = "$image_path_remaining/odm_dem/dsm.png";
 698                 my $odm_dtm_png = "$image_path_remaining/odm_dem/dtm.png";
 699                 my $odm_subtract_png = "$image_path_remaining/odm_dem/subtract.png";
 700                 my $odm_dem_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/ODMOpenImageDSM.py --image_path_dsm $image_path_remaining/odm_dem/dsm.tif --image_path_dtm $image_path_remaining/odm_dem/dtm.tif --outfile_path_dsm $odm_dsm_png --outfile_path_dtm $odm_dtm_png --outfile_path_subtract $odm_subtract_png --band_number 1";
 701                 my $odm_dem_open_status = system($odm_dem_cmd);
 702
 703                 @stitched_bands = (
 704                     ["Color Image", "OpenDroneMap RGB Color Image", "RGB Color Image", "$image_path_remaining/odm_orthophoto/odm_orthophoto.tif"],
 705                     ["DSM", "OpenDroneMap DSM", "Black and White Image", $odm_dsm_png]
 706                 );
 707             }
 708             else {
 709                 die "Camera info not supported for raw image upload ODM stitch: $new_drone_run_camera_info\n";
 710             }
 711
 712             my $calibration_info = '';
 713             if ($new_drone_run_band_stitching_odm_radiocalibration && $new_drone_run_camera_info eq 'micasense_5') {
 714                 $calibration_info = ' with radiocalibration';
 715             }
 716             elsif (!$new_drone_run_band_stitching_odm_radiocalibration && $new_drone_run_camera_info eq 'micasense_5') {
 717                 $calibration_info = ' without radiocalibration';
 718             }
 719
 720             print STDERR Dumper \@stitched_bands;
 721
 722             foreach my $m (@stitched_bands) {
 723                 my $project_rs = $schema->resultset("Project::Project")->create({
 724                     name => $new_drone_run_name."_".$m->[1],
 725                     description => $new_drone_run_desc.". ".$m->[0]." ".$m->[1].". Orthomosaic stitched by OpenDroneMap in ImageBreed".$calibration_info.".",
 726                     projectprops => [{type_id => $drone_run_band_type_cvterm_id, value => $m->[2]}, {type_id => $design_cvterm_id, value => 'drone_run_band'}],
 727                     project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_drone_run_id}]
 728                 });
 729                 my $selected_drone_run_band_id = $project_rs->project_id();
 730
 731                 my $time = DateTime->now();
 732                 my $timestamp = $time->ymd()."_".$time->hms();
 733                 my $upload_original_name = $new_drone_run_name."_ImageBreed_stitched_".$m->[1].".png";
 734
 735                 my $uploader = CXGN::UploadFile->new({
 736                     tempfile => $m->[3],
 737                     subdirectory => "drone_imagery_upload",
 738                     archive_path => $c->config->{archive_path},
 739                     archive_filename => $upload_original_name,
 740                     timestamp => $timestamp,
 741                     user_id => $user_id,
 742                     user_role => $user_role
 743                 });
 744                 my $archived_filename_with_path = $uploader->archive();
 745                 my $md5 = $uploader->get_md5($archived_filename_with_path);
 746                 if (!$archived_filename_with_path) {
 747                     $c->stash->{message} = "Could not save file $upload_original_name in archive.";
 748                     $c->stash->{template} = 'generic_message.mas';
 749                     return;
 750                 }
 751                 unlink $upload_tempfile;
 752                 print STDERR "Archived Drone Image File: $archived_filename_with_path\n";
 753
 754                 my $image = SGN::Image->new( $schema->storage->dbh, undef, $c );
 755                 $image->set_sp_person_id($user_id);
 756                 my $linking_table_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stitched_drone_imagery', 'project_md_image')->cvterm_id();
 757                 my $ret = $image->process_image($archived_filename_with_path, 'project', $selected_drone_run_band_id, $linking_table_type_id);
 758                 push @return_drone_run_band_image_urls, $image->get_image_url('original');
 759                 push @return_drone_run_band_image_ids, $image->get_image_id();
 760                 push @return_drone_run_band_project_ids, $selected_drone_run_band_id;
 761             }
 762         };
 763
 764         $odm_check_prop->value('0');
 765         $odm_check_prop->update();
 766     }
 767
 768     $c->stash->{message} = "Successfully uploaded! Go to <a href='/breeders/drone_imagery'>Drone Imagery</a>";
 769     $c->stash->{template} = 'generic_message.mas';
 770     return;
 771 }
 772
 773 sub upload_drone_imagery_bulk : Path("/drone_imagery/upload_drone_imagery_bulk") :Args(0) {
 774     my $self = shift;
 775     my $c = shift;
 776     $c->response->headers->header( "Access-Control-Allow-Origin" => '*' );
 777     $c->response->headers->header( "Access-Control-Allow-Methods" => "POST, GET, PUT, DELETE" );
 778     my $schema = $c->dbic_schema("Bio::Chado::Schema");
 779     my $metadata_schema = $c->dbic_schema("CXGN::Metadata::Schema");
 780     my $phenome_schema = $c->dbic_schema("CXGN::Phenome::Schema");
 781     my $dbh = $c->dbc->dbh;
 782     my ($user_id, $user_name, $user_role) = _check_user_login($c);
 783     print STDERR Dumper $c->req->params();
 784
 785     my $imaging_vehicle_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'imaging_event_vehicle', 'stock_type')->cvterm_id();
 786     my $imaging_vehicle_properties_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'imaging_event_vehicle_json', 'stock_property')->cvterm_id();
 787     my $drone_run_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_experiment', 'experiment_type')->cvterm_id();
 788     my $design_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'design', 'project_property')->cvterm_id();
 789     my $drone_run_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_project_type', 'project_property')->cvterm_id();
 790     my $drone_run_is_raw_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_is_raw_images', 'project_property')->cvterm_id();
 791     my $drone_run_camera_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_camera_type', 'project_property')->cvterm_id();
 792     my $project_start_date_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project_start_date', 'project_property')->cvterm_id();
 793     my $drone_run_base_date_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_base_date', 'project_property')->cvterm_id();
 794     my $drone_run_rig_desc_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_camera_rig_description', 'project_property')->cvterm_id();
 795     my $drone_run_related_cvterms_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_related_time_cvterms_json', 'project_property')->cvterm_id();
 796     my $drone_run_band_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_project_type', 'project_property')->cvterm_id();
 797     my $project_relationship_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_on_field_trial', 'project_relationship')->cvterm_id();
 798     my $drone_run_drone_run_band_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_on_drone_run', 'project_relationship')->cvterm_id();
 799     my $geoparam_coordinates_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_geoparam_coordinates_type', 'project_property')->cvterm_id();
 800     my $geoparam_coordinates_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_geoparam_coordinates', 'project_property')->cvterm_id();
 801     my $calendar_funcs = CXGN::Calendar->new({});
 802
 803     my $upload_file = $c->req->upload('upload_drone_imagery_bulk_images_zipfile');
 804     my $imaging_events_file = $c->req->upload('upload_drone_imagery_bulk_imaging_events');
 805
 806     my $upload_original_name = $upload_file->filename();
 807     my $upload_tempfile = $upload_file->tempname;
 808     my $upload_imaging_events_file = $imaging_events_file->tempname;
 809     my $time = DateTime->now();
 810     my $timestamp = $time->ymd()."_".$time->hms();
 811
 812     my $uploader = CXGN::UploadFile->new({
 813         tempfile => $upload_tempfile,
 814         subdirectory => "drone_imagery_upload_bulk_orthophoto_zips",
 815         archive_path => $c->config->{archive_path},
 816         archive_filename => $upload_original_name,
 817         timestamp => $timestamp,
 818         user_id => $user_id,
 819         user_role => $user_role
 820     });
 821     my $archived_filename_with_path = $uploader->archive();
 822     my $md5 = $uploader->get_md5($archived_filename_with_path);
 823     if (!$archived_filename_with_path) {
 824         $c->stash->{message} = "Could not save file $upload_original_name in archive.";
 825         $c->stash->{template} = 'generic_message.mas';
 826         return;
 827     }
 828     unlink $upload_tempfile;
 829     print STDERR "Archived Drone Image Bulk Orthophoto Zip File: $archived_filename_with_path\n";
 830
 831     my $archived_zip = CXGN::ZipFile->new(archived_zipfile_path=>$archived_filename_with_path);
 832     my $file_members = $archived_zip->file_members();
 833     if (!$file_members){
 834         $c->stash->{message} = 'Could not read your orthophoto bulk zipfile. Is it .zip format?';
 835         $c->stash->{template} = 'generic_message.mas';
 836         return;
 837     }
 838
 839     my %spectral_lookup = (
 840         blue => "Blue (450-520nm)",
 841         green => "Green (515-600nm)",
 842         red => "Red (600-690nm)",
 843         rededge => "Red Edge (690-750nm)",
 844         nir => "NIR (780-3000nm)",
 845         mir => "MIR (3000-50000nm)",
 846         fir => "FIR (50000-1000000nm)",
 847         thir => "Thermal IR (9000-14000nm)",
 848         rgb => "RGB Color Image",
 849         bw => "Black and White Image"
 850     );
 851
 852     my %sensor_map = (
 853         "MicaSense 5 Channel Camera" => "micasense_5",
 854         "CCD Color Camera" => "ccd_color",
 855         "CMOS Color Camera" => "cmos_color"
 856     );
 857
 858     my %filename_imaging_event_lookup;
 859     my %filename_imaging_event_band_check;
 860     foreach (@$file_members) {
 861         my $image = SGN::Image->new( $dbh, undef, $c );
 862         my $filename = $_->fileName();
 863         my @zipfile_comp = split '\/', $filename;
 864         my $filename_wext;
 865         if (scalar(@zipfile_comp)==1) {
 866             $filename_wext = $zipfile_comp[0];
 867         }
 868         else {
 869             $filename_wext = $zipfile_comp[1];
 870         }
 871         my @filename_comps = split '\.', $filename_wext;
 872         my $filename_only = $filename_comps[0];
 873         my @image_spectra = split '\_\_', $filename_only;
 874         my $temp_file = $image->upload_zipfile_images($_);
 875         my $imaging_event_name = $image_spectra[0];
 876         my $band = $image_spectra[1];
 877
 878         if (!exists($spectral_lookup{$band})) {
 879             $c->stash->{message} = "The spectral band $band is not allowed in the provided orthophoto $filename_only. Make sure the orthophotos are saved as a concatenation of the imaging event name and the spectral band, with a double-underscore (__) as the separator (e.g. Ortho1_01012020__blue.tiff) and the allowed spectral bands are blue,green,red,rededge,nir,mir,fir,thir,rgb,bw.";
 880             $c->stash->{template} = 'generic_message.mas';
 881             return;
 882         }
 883         my $spectral_band = $spectral_lookup{$band};
 884         print STDERR Dumper [$filename_wext, $filename, $temp_file, $imaging_event_name, $spectral_band];
 885         $filename_imaging_event_lookup{$filename_wext} = {
 886             file => $temp_file,
 887             band => $spectral_band,
 888             band_short => $band
 889         };
 890         if (exists($filename_imaging_event_band_check{$imaging_event_name}->{$spectral_band})) {
 891             $c->stash->{message} = "Do not upload duplicate spectral types for the same imaging event. There is already a $band image for $imaging_event_name in the zipfile! Make sure the orthophotos are saved as a concatenation of the imaging event name and the spectral band, with a double-underscore (__) as the separator (e.g. Ortho1_01012020__blue.tiff)";
 892             $c->stash->{template} = 'generic_message.mas';
 893             return;
 894         } else {
 895             $filename_imaging_event_band_check{$imaging_event_name} = $spectral_band;
 896         }
 897     }
 898
 899     my @parse_csv_errors;
 900     my %field_trial_name_lookup;
 901     my %field_trial_ids_seen;
 902     my %vehicle_name_lookup;
 903     my %seen_field_trial_drone_run_dates;
 904
 905     my $parser = Spreadsheet::ParseExcel->new();
 906     my $excel_obj = $parser->parse($upload_imaging_events_file);
 907     if (!$excel_obj) {
 908         $c->stash->{message} = 'The Excel (.xls) file could not be opened:'.$parser->error();
 909         $c->stash->{template} = 'generic_message.mas';
 910         return;
 911     }
 912
 913     my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
 914     if (!$worksheet) {
 915         $c->stash->{message} = 'Spreadsheet must be on 1st tab in Excel (.xls) file.';
 916         $c->stash->{template} = 'generic_message.mas';
 917         return;
 918     }
 919     my ( $row_min, $row_max ) = $worksheet->row_range();
 920     my ( $col_min, $col_max ) = $worksheet->col_range();
 921     if (($col_max - $col_min)  < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of plot data
 922         $c->stash->{message} = 'Spreadsheet (.xls) is missing header or contains no rows.';
 923         $c->stash->{template} = 'generic_message.mas';
 924         return;
 925     }
 926
 927     if ($worksheet->get_cell(0,0)->value() ne 'Imaging Event Name' ||
 928         $worksheet->get_cell(0,1)->value() ne 'Type' ||
 929         $worksheet->get_cell(0,2)->value() ne 'Description' ||
 930         $worksheet->get_cell(0,3)->value() ne 'Date' ||
 931         $worksheet->get_cell(0,4)->value() ne 'Vehicle Name' ||
 932         $worksheet->get_cell(0,5)->value() ne 'Vehicle Battery Set' ||
 933         $worksheet->get_cell(0,6)->value() ne 'Sensor' ||
 934         $worksheet->get_cell(0,7)->value() ne 'Field Trial Name' ||
 935         $worksheet->get_cell(0,8)->value() ne 'Image Filenames' ||
 936         $worksheet->get_cell(0,9)->value() ne 'Coordinate System' ||
 937         $worksheet->get_cell(0,10)->value() ne 'Base Date' ||
 938         $worksheet->get_cell(0,11)->value() ne 'Camera Rig') {
 939             $c->stash->{message} = "The header row in the CSV spreadsheet must be 'Imaging Event Name,Type,Description,Date,Vehicle Name,Vehicle Battery Set,Sensor,Field Trial Name,GeoJSON Filename,Image Filenames,Coordinate System,Base Date,Camera Rig'.";
 940             $c->stash->{template} = 'generic_message.mas';
 941             return;
 942     }
 943
 944     my %seen_upload_dates;
 945     for my $row ( 1 .. $row_max ) {
 946         my $imaging_event_name;
 947         if ($worksheet->get_cell($row,0)) {
 948             $imaging_event_name = $worksheet->get_cell($row,0)->value();
 949         }
 950         my $imaging_event_type;
 951         if ($worksheet->get_cell($row,1)) {
 952             $imaging_event_type = $worksheet->get_cell($row,1)->value();
 953         }
 954         my $imaging_event_desc;
 955         if ($worksheet->get_cell($row,2)) {
 956             $imaging_event_desc = $worksheet->get_cell($row,2)->value();
 957         }
 958         my $imaging_event_date;
 959         if ($worksheet->get_cell($row,3)) {
 960             $imaging_event_date = $worksheet->get_cell($row,3)->value();
 961         }
 962         my $vehicle_name;
 963         if ($worksheet->get_cell($row,4)) {
 964             $vehicle_name = $worksheet->get_cell($row,4)->value();
 965         }
 966         my $vehicle_battery = 'default_battery';
 967         if ($worksheet->get_cell($row,5)) {
 968             $vehicle_battery = $worksheet->get_cell($row,5)->value();
 969         }
 970         my $sensor;
 971         if ($worksheet->get_cell($row,6)) {
 972             $sensor = $worksheet->get_cell($row,6)->value();
 973         }
 974         my $field_trial_name;
 975         if ($worksheet->get_cell($row,7)) {
 976             $field_trial_name = $worksheet->get_cell($row,7)->value();
 977         }
 978         my $image_filenames;
 979         if ($worksheet->get_cell($row,8)) {
 980             $image_filenames = $worksheet->get_cell($row,8)->value();
 981         }
 982         my $coordinate_system;
 983         if ($worksheet->get_cell($row,9)) {
 984             $coordinate_system = $worksheet->get_cell($row,9)->value();
 985         }
 986         my $base_date;
 987         if ($worksheet->get_cell($row,10)) {
 988             $base_date = $worksheet->get_cell($row,10)->value();
 989         }
 990         my $rig_desc;
 991         if ($worksheet->get_cell($row,11)) {
 992             $rig_desc = $worksheet->get_cell($row,11)->value();
 993         }
 994
 995         if (!$imaging_event_name){
 996             push @parse_csv_errors, "Please give a new imaging event name!";
 997         }
 998         if (!$imaging_event_type){
 999             push @parse_csv_errors, "Please give an imaging event type!";
1000         }
1001         if (!$imaging_event_desc){
1002             push @parse_csv_errors, "Please give an imaging event description!";
1003         }
1004         if (!$imaging_event_date){
1005             push @parse_csv_errors, "Please give an imaging event date!";
1006         }
1007         if (!$vehicle_name){
1008             push @parse_csv_errors, "Please give a vehicle name!";
1009         }
1010         if (!$sensor){
1011             push @parse_csv_errors, "Please give a sensor name!";
1012         }
1013         if (!$field_trial_name){
1014             push @parse_csv_errors, "Please give a field trial name!";
1015         }
1016
1017         if ($coordinate_system ne 'UTM' && $coordinate_system ne 'WGS84' && $coordinate_system ne 'Pixels') {
1018             push @parse_csv_errors, "The given coordinate system $coordinate_system is not one of: UTM, WGS84, or Pixels!";
1019         }
1020         if ($coordinate_system ne 'Pixels') {
1021             $c->stash->{message} = "Only the Pixels coordinate system is currently supported for this upload. In the future GeoTIFFs will be supported, but for now please only upload simple raster images (.png, .tiff, .jpg).";
1022             $c->stash->{template} = 'generic_message.mas';
1023             return;
1024         }
1025
1026         my $field_trial_rs = $schema->resultset("Project::Project")->search({name=>$field_trial_name});
1027         if ($field_trial_rs->count != 1) {
1028             $c->stash->{message} = "The field trial $field_trial_name does not exist in the database already! Please add it first.";
1029             $c->stash->{template} = 'generic_message.mas';
1030             return;
1031         }
1032         my $field_trial_id = $field_trial_rs->first->project_id();
1033         $field_trial_ids_seen{$field_trial_id}++;
1034         $field_trial_name_lookup{$field_trial_name} = $field_trial_id;
1035
1036         if ($imaging_event_date !~ /^\d{4}\/\d{2}\/\d{2}\s\d\d:\d\d:\d\d$/){
1037             $c->stash->{message} = "Please give a new imaging event date in the format YYYY/MM/DD HH:mm:ss! The provided $imaging_event_date is not correct!";
1038             $c->stash->{template} = 'generic_message.mas';
1039             return;
1040         }
1041         if ($imaging_event_type ne 'Aerial Medium to High Res' && $imaging_event_type ne 'Aerial Low Res'){
1042             $c->stash->{message} = "The imaging event type $imaging_event_type is not one of 'Aerial Low Res' or 'Aerial Medium to High Res'!";
1043             $c->stash->{template} = 'generic_message.mas';
1044             return;
1045         }
1046         if (!exists($sensor_map{$sensor})){
1047             $c->stash->{message} = "The sensor $sensor is not one of 'MicaSense 5 Channel Camera' or 'CCD Color Camera' or 'CMOS Color Camera'!";
1048             $c->stash->{template} = 'generic_message.mas';
1049             return;
1050         }
1051
1052         my $project_rs = $schema->resultset("Project::Project")->search({name=>$imaging_event_name});
1053         if ($project_rs->count > 0) {
1054             push @parse_csv_errors, "Please use a globally unique imaging event name! The name you specified $imaging_event_name has already been used.";
1055         }
1056         my $vehicle_prop = $schema->resultset("Stock::Stock")->search({uniquename => $vehicle_name, type_id=>$imaging_vehicle_cvterm_id});
1057         if ($vehicle_prop->count != 1) {
1058             push @parse_csv_errors, "Imaging event vehicle $vehicle_name is not already in the database! Please add it first!";
1059         }
1060         else {
1061             $vehicle_name_lookup{$vehicle_name} = $vehicle_prop->first->stock_id;
1062         }
1063
1064         my $trial = CXGN::Trial->new({ bcs_schema => $schema, trial_id => $field_trial_id });
1065         my $planting_date = $trial->get_planting_date();
1066         if (!$planting_date) {
1067             $c->stash->{message} = "The field trial $field_trial_name does not have a planting date set! Please set this first!";
1068             $c->stash->{template} = 'generic_message.mas';
1069             return;
1070         }
1071         my $planting_date_time_object = Time::Piece->strptime($planting_date, "%Y-%B-%d");
1072         my $imaging_event_date_time_object = Time::Piece->strptime($imaging_event_date, "%Y/%m/%d %H:%M:%S");
1073
1074         $seen_upload_dates{$imaging_event_name} = {
1075             date => $imaging_event_date,
1076             time => $imaging_event_date_time_object->epoch,
1077             field_trial_name => $field_trial_name,
1078             field_trial_id => $field_trial_id
1079         };
1080         $seen_field_trial_drone_run_dates{$imaging_event_date_time_object->epoch}++;
1081
1082         if ($imaging_event_date_time_object->epoch - $planting_date_time_object->epoch <= 0) {
1083             push @parse_csv_errors, "The date of the imaging event $imaging_event_date is not after the field trial planting date $planting_date!";
1084         }
1085         if ($base_date) {
1086             if ($base_date !~ /^\d{4}\/\d{2}\/\d{2}\s\d\d:\d\d:\d\d$/){
1087                 $c->stash->{message} = "Please give a new imaging event base date in the format YYYY/MM/DD HH:mm:ss! The provided $base_date is not correct! Leave empty if not relevant!";
1088                 $c->stash->{template} = 'generic_message.mas';
1089                 return;
1090             }
1091             my $imaging_event_base_time_object = Time::Piece->strptime($base_date, "%Y/%m/%d %H:%M:%S");
1092
1093             if ($imaging_event_date_time_object->epoch - $imaging_event_base_time_object->epoch < 0) {
1094                 push @parse_csv_errors, "The date of the imaging event $imaging_event_date is not after the base date $base_date!";
1095             }
1096         }
1097
1098         my @orthoimage_names = split ',', $image_filenames;
1099         foreach (@orthoimage_names) {
1100             if (!exists($filename_imaging_event_lookup{$_})) {
1101                 push @parse_csv_errors, "The orthophoto filename $_ does not exist in the uploaded orthophoto zipfile. Make sure the orthophotos are saved as a concatenation of the ortho filename defined in the spreadsheet and the spectral band, with a double-underscore (__) as the separator (e.g. Ortho1_01012020__blue.tiff)";
1102             }
1103         }
1104     }
1105
1106     # my $seen_field_trial_ids_check = join ',', keys %field_trial_ids_seen;
1107     # my $drone_run_date_q = "SELECT drone_run_date.value
1108     #     FROM project AS drone_run_band_project
1109     #     JOIN project_relationship AS drone_run_band_rel ON (drone_run_band_rel.subject_project_id = drone_run_band_project.project_id AND drone_run_band_rel.type_id = $drone_run_drone_run_band_type_id)
1110     #     JOIN project AS drone_run_project ON (drone_run_band_rel.object_project_id = drone_run_project.project_id)
1111     #     JOIN projectprop AS drone_run_date ON(drone_run_project.project_id=drone_run_date.project_id AND drone_run_date.type_id=$project_start_date_type_id)
1112     #     WHERE drone_run_project.project_id IN ($seen_field_trial_ids_check);";
1113     # my $drone_run_date_h = $schema->storage->dbh()->prepare($drone_run_date_q);
1114     # $drone_run_date_h->execute();
1115     # while( my ($drone_run_date) = $drone_run_date_h->fetchrow_array()) {
1116     #     my $drone_run_date_formatted = $drone_run_date ? $calendar_funcs->display_start_date($drone_run_date) : '';
1117     #     if ($drone_run_date_formatted) {
1118     #         my $date_obj = Time::Piece->strptime($drone_run_date_formatted, "%Y-%B-%d %H:%M:%S");
1119     #         $seen_field_trial_drone_run_dates{$date_obj->epoch}++;
1120     #     }
1121     # }
1122     #
1123     # while (my($imaging_event_name,$v) = each %seen_upload_dates) {
1124     #     my $time = $v->{time};
1125     #     my $date = $v->{date};
1126     #     my $field_trial_name = $v->{field_trial_name};
1127     #     my $field_trial_id = $v->{field_trial_id};
1128     #     if (exists($seen_field_trial_drone_run_dates{$time})) {
1129     #         $c->stash->{message} = "An imaging event has already occured on this field trial ($field_trial_name) at the same date and time ($date)! Please give a unique date/time for each imaging event!";
1130     #         $c->stash->{template} = 'generic_message.mas';
1131     #         return;
1132     #     }
1133     # }
1134
1135     if (scalar(@parse_csv_errors) > 0) {
1136         my $error_string = join "<br/>", @parse_csv_errors;
1137         $c->stash->{message} = $error_string;
1138         $c->stash->{template} = 'generic_message.mas';
1139         return;
1140     }
1141
1142     my @drone_run_project_ids;
1143     my %drone_run_band_hash;
1144     for my $row ( 1 .. $row_max ) {
1145         my $imaging_event_name = $worksheet->get_cell($row,0)->value();
1146         my $imaging_event_type = $worksheet->get_cell($row,1)->value();
1147         my $imaging_event_desc = $worksheet->get_cell($row,2)->value();
1148         my $imaging_event_date = $worksheet->get_cell($row,3)->value();
1149         my $vehicle_name = $worksheet->get_cell($row,4)->value();
1150         my $vehicle_battery = $worksheet->get_cell($row,5) ? $worksheet->get_cell($row,5)->value() : 'default_battery';
1151         my $sensor = $worksheet->get_cell($row,6)->value();
1152         my $field_trial_name = $worksheet->get_cell($row,7)->value();
1153         my $image_filenames = $worksheet->get_cell($row,8)->value();
1154         my $coordinate_system = $worksheet->get_cell($row,9)->value();
1155         my $base_date = $worksheet->get_cell($row,10) ? $worksheet->get_cell($row,10)->value() : '';
1156         my $rig_desc = $worksheet->get_cell($row,11) ? $worksheet->get_cell($row,11)->value() : '';
1157
1158         my $new_drone_run_vehicle_id = $vehicle_name_lookup{$vehicle_name};
1159         my $selected_trial_id = $field_trial_name_lookup{$field_trial_name};
1160         my $new_drone_run_camera_info = $sensor_map{$sensor};
1161         my $trial = CXGN::Trial->new({ bcs_schema => $schema, trial_id => $selected_trial_id });
1162         my $trial_location_id = $trial->get_location()->[0];
1163         my $planting_date = $trial->get_planting_date();
1164         my $planting_date_time_object = Time::Piece->strptime($planting_date, "%Y-%B-%d");
1165         my $imaging_event_date_time_object = Time::Piece->strptime($imaging_event_date, "%Y/%m/%d %H:%M:%S");
1166         my $drone_run_event = $calendar_funcs->check_value_format($imaging_event_date);
1167         my $time_diff;
1168         my $base_date_event;
1169         if ($base_date) {
1170             my $imaging_event_base_date_time_object = Time::Piece->strptime($base_date, "%Y/%m/%d %H:%M:%S");
1171             $time_diff = $imaging_event_date_time_object - $imaging_event_base_date_time_object;
1172             $base_date_event = $calendar_funcs->check_value_format($base_date);
1173         }
1174         else {
1175             $time_diff = $imaging_event_date_time_object - $planting_date_time_object;
1176         }
1177         my $time_diff_weeks = $time_diff->weeks;
1178         my $time_diff_days = $time_diff->days;
1179         my $time_diff_hours = $time_diff->hours;
1180         my $rounded_time_diff_weeks = round($time_diff_weeks);
1181         if ($rounded_time_diff_weeks == 0) {
1182             $rounded_time_diff_weeks = 1;
1183         }
1184
1185         my $week_term_string = "week $rounded_time_diff_weeks";
1186         my $q = "SELECT t.cvterm_id FROM cvterm as t JOIN cv ON(t.cv_id=cv.cv_id) WHERE t.name=? and cv.name=?;";
1187         my $h = $schema->storage->dbh()->prepare($q);
1188         $h->execute($week_term_string, 'cxgn_time_ontology');
1189         my ($week_cvterm_id) = $h->fetchrow_array();
1190
1191         if (!$week_cvterm_id) {
1192             my $new_week_term = $schema->resultset("Cv::Cvterm")->create_with({
1193                name => $week_term_string,
1194                cv => 'cxgn_time_ontology'
1195             });
1196             $week_cvterm_id = $new_week_term->cvterm_id();
1197         }
1198
1199         my $day_term_string = "day $time_diff_days";
1200         $h->execute($day_term_string, 'cxgn_time_ontology');
1201         my ($day_cvterm_id) = $h->fetchrow_array();
1202
1203         if (!$day_cvterm_id) {
1204             my $new_day_term = $schema->resultset("Cv::Cvterm")->create_with({
1205                name => $day_term_string,
1206                cv => 'cxgn_time_ontology'
1207             });
1208             $day_cvterm_id = $new_day_term->cvterm_id();
1209         }
1210
1211         my $week_term = SGN::Model::Cvterm::get_trait_from_cvterm_id($schema, $week_cvterm_id, 'extended');
1212         my $day_term = SGN::Model::Cvterm::get_trait_from_cvterm_id($schema, $day_cvterm_id, 'extended');
1213
1214         my %related_cvterms = (
1215             week => $week_term,
1216             day => $day_term
1217         );
1218
1219         my $drone_run_projectprops = [
1220             {type_id => $drone_run_type_cvterm_id, value => $imaging_event_type},
1221             {type_id => $project_start_date_type_id, value => $drone_run_event},
1222             {type_id => $design_cvterm_id, value => 'drone_run'},
1223             {type_id => $drone_run_camera_type_cvterm_id, value => $new_drone_run_camera_info},
1224             {type_id => $drone_run_related_cvterms_cvterm_id, value => encode_json \%related_cvterms}
1225         ];
1226         if ($base_date) {
1227             push @$drone_run_projectprops, {type_id => $drone_run_base_date_type_id, value => $base_date_event};
1228         }
1229         if ($rig_desc) {
1230             push @$drone_run_projectprops, {type_id => $drone_run_rig_desc_type_id, value => $rig_desc};
1231         }
1232
1233         my $nd_experiment_rs = $schema->resultset("NaturalDiversity::NdExperiment")->create({
1234             nd_geolocation_id => $trial_location_id,
1235             type_id => $drone_run_experiment_type_id,
1236             nd_experiment_stocks => [{stock_id => $new_drone_run_vehicle_id, type_id => $drone_run_experiment_type_id}]
1237         });
1238         my $drone_run_nd_experiment_id = $nd_experiment_rs->nd_experiment_id();
1239
1240         my $project_rs = $schema->resultset("Project::Project")->create({
1241             name => $imaging_event_name,
1242             description => $imaging_event_desc,
1243             projectprops => $drone_run_projectprops,
1244             project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_trial_id}],
1245             nd_experiment_projects => [{nd_experiment_id => $drone_run_nd_experiment_id}]
1246         });
1247         my $selected_drone_run_id = $project_rs->project_id();
1248         push @drone_run_project_ids, $selected_drone_run_id;
1249
1250         my $vehicle_prop = decode_json $schema->resultset("Stock::Stockprop")->search({stock_id => $new_drone_run_vehicle_id, type_id=>$imaging_vehicle_properties_cvterm_id})->first()->value();
1251         $vehicle_prop->{batteries}->{$vehicle_battery}->{usage}++;
1252         my $vehicle_prop_update = $schema->resultset('Stock::Stockprop')->update_or_create({
1253             type_id=>$imaging_vehicle_properties_cvterm_id,
1254             stock_id=>$new_drone_run_vehicle_id,
1255             rank=>0,
1256             value=>encode_json $vehicle_prop
1257         },
1258         {
1259             key=>'stockprop_c1'
1260         });
1261
1262         my @orthoimage_names = split ',', $image_filenames;
1263         my @ortho_images;
1264         foreach (@orthoimage_names) {
1265             push @ortho_images, $filename_imaging_event_lookup{$_};
1266         }
1267         foreach my $m (@ortho_images) {
1268             my $project_relationship_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_on_drone_run', 'project_relationship')->cvterm_id();
1269             my $band = $m->{band};
1270             my $band_short = $m->{band_short};
1271             my $file = $m->{file};
1272             my $project_rs = $schema->resultset("Project::Project")->create({
1273                 name => $imaging_event_name."_".$band_short,
1274                 description => $imaging_event_desc.". ".$band,
1275                 projectprops => [
1276                     {type_id => $drone_run_band_type_cvterm_id, value => $band},
1277                     {type_id => $design_cvterm_id, value => 'drone_run_band'},
1278                     {type_id => $geoparam_coordinates_type_cvterm_id, value => $coordinate_system}
1279                 ],
1280                 project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_drone_run_id}]
1281             });
1282             my $selected_drone_run_band_id = $project_rs->project_id();
1283
1284             my $time = DateTime->now();
1285             my $timestamp = $time->ymd()."_".$time->hms();
1286             my $upload_original_name = $imaging_event_name."_".$band_short.".png";
1287
1288             my $uploader = CXGN::UploadFile->new({
1289                 tempfile => $file,
1290                 subdirectory => "drone_imagery_upload",
1291                 archive_path => $c->config->{archive_path},
1292                 archive_filename => $upload_original_name,
1293                 timestamp => $timestamp,
1294                 user_id => $user_id,
1295                 user_role => $user_role
1296             });
1297             my $archived_filename_with_path = $uploader->archive();
1298             my $md5 = $uploader->get_md5($archived_filename_with_path);
1299             if (!$archived_filename_with_path) {
1300                 $c->stash->{rest} = { error => "Could not save file $upload_original_name in archive." };
1301                 $c->detach();
1302             }
1303             unlink $upload_tempfile;
1304             print STDERR "Archived Bulk Orthophoto File: $archived_filename_with_path\n";
1305
1306             my $image = SGN::Image->new( $schema->storage->dbh, undef, $c );
1307             $image->set_sp_person_id($user_id);
1308             my $linking_table_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stitched_drone_imagery', 'project_md_image')->cvterm_id();
1309             my $ret = $image->process_image($archived_filename_with_path, 'project', $selected_drone_run_band_id, $linking_table_type_id);
1310
1311             push @{$drone_run_band_hash{$selected_drone_run_id}}, {
1312                 drone_run_band_project_id => $selected_drone_run_band_id,
1313                 band => $band
1314             };
1315         }
1316     }
1317
1318     $c->stash->{message} = "Successfully uploaded! Go to <a href='/breeders/drone_imagery'>Drone Imagery</a>";
1319     $c->stash->{template} = 'generic_message.mas';
1320     return;
1321 }
1322
1323 sub upload_drone_imagery_bulk_previous : Path("/drone_imagery/upload_drone_imagery_bulk_previous") :Args(0) {
1324     my $self = shift;
1325     my $c = shift;
1326     $c->response->headers->header( "Access-Control-Allow-Origin" => '*' );
1327     $c->response->headers->header( "Access-Control-Allow-Methods" => "POST, GET, PUT, DELETE" );
1328     my $schema = $c->dbic_schema("Bio::Chado::Schema");
1329     my $metadata_schema = $c->dbic_schema("CXGN::Metadata::Schema");
1330     my $phenome_schema = $c->dbic_schema("CXGN::Phenome::Schema");
1331     my $dbh = $c->dbc->dbh;
1332     my ($user_id, $user_name, $user_role) = _check_user_login($c);
1333     print STDERR Dumper $c->req->params();
1334
1335     my $imaging_vehicle_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'imaging_event_vehicle', 'stock_type')->cvterm_id();
1336     my $imaging_vehicle_properties_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'imaging_event_vehicle_json', 'stock_property')->cvterm_id();
1337     my $drone_run_experiment_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_experiment', 'experiment_type')->cvterm_id();
1338     my $design_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'design', 'project_property')->cvterm_id();
1339     my $geoparam_coordinates_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_geoparam_coordinates_type', 'project_property')->cvterm_id();
1340     my $geoparam_coordinates_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_geoparam_coordinates', 'project_property')->cvterm_id();
1341     my $drone_run_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_project_type', 'project_property')->cvterm_id();
1342     my $drone_run_is_raw_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_is_raw_images', 'project_property')->cvterm_id();
1343     my $drone_run_camera_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_camera_type', 'project_property')->cvterm_id();
1344     my $project_start_date_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'project_start_date', 'project_property')->cvterm_id();
1345     my $drone_run_base_date_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_base_date', 'project_property')->cvterm_id();
1346     my $drone_run_rig_desc_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_camera_rig_description', 'project_property')->cvterm_id();
1347     my $drone_run_related_cvterms_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_related_time_cvterms_json', 'project_property')->cvterm_id();
1348     my $drone_run_band_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_project_type', 'project_property')->cvterm_id();
1349     my $project_relationship_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_on_field_trial', 'project_relationship')->cvterm_id();
1350     my $process_indicator_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_standard_process_in_progress', 'project_property')->cvterm_id();
1351     my $processed_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_standard_process_completed', 'project_property')->cvterm_id();
1352     my $processed_minimal_vi_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_standard_process_vi_completed', 'project_property')->cvterm_id();
1353     my $drone_run_band_type_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_project_type', 'project_property')->cvterm_id();
1354     my $rotate_angle_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_rotate_angle', 'project_property')->cvterm_id();
1355     my $cropping_polygon_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_cropped_polygon', 'project_property')->cvterm_id();
1356     my $plot_polygon_template_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_plot_polygons', 'project_property')->cvterm_id();
1357     my $project_image_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stitched_drone_imagery', 'project_md_image')->cvterm_id();
1358     my $drone_run_drone_run_band_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_on_drone_run', 'project_relationship')->cvterm_id();
1359     my $calendar_funcs = CXGN::Calendar->new({});
1360
1361     my %seen_field_trial_drone_run_dates;
1362     my $drone_run_date_q = "SELECT drone_run_date.value
1363         FROM project AS drone_run_band_project
1364         JOIN project_relationship AS drone_run_band_rel ON (drone_run_band_rel.subject_project_id = drone_run_band_project.project_id AND drone_run_band_rel.type_id = $drone_run_drone_run_band_type_id)
1365         JOIN project AS drone_run_project ON (drone_run_band_rel.object_project_id = drone_run_project.project_id)
1366         JOIN projectprop AS drone_run_date ON(drone_run_project.project_id=drone_run_date.project_id AND drone_run_date.type_id=$project_start_date_type_id);";
1367     my $drone_run_date_h = $schema->storage->dbh()->prepare($drone_run_date_q);
1368     $drone_run_date_h->execute();
1369     while( my ($drone_run_date) = $drone_run_date_h->fetchrow_array()) {
1370         my $drone_run_date_formatted = $drone_run_date ? $calendar_funcs->display_start_date($drone_run_date) : '';
1371         if ($drone_run_date_formatted) {
1372             my $date_obj = Time::Piece->strptime($drone_run_date_formatted, "%Y-%B-%d %H:%M:%S");
1373             $seen_field_trial_drone_run_dates{$date_obj->epoch}++;
1374         }
1375     }
1376
1377     my %spectral_lookup = (
1378         blue => "Blue (450-520nm)",
1379         green => "Green (515-600nm)",
1380         red => "Red (600-690nm)",
1381         rededge => "Red Edge (690-750nm)",
1382         nir => "NIR (780-3000nm)",
1383         mir => "MIR (3000-50000nm)",
1384         fir => "FIR (50000-1000000nm)",
1385         thir => "Thermal IR (9000-14000nm)",
1386         rgb => "RGB Color Image",
1387         bw => "Black and White Image"
1388     );
1389
1390     my %sensor_map = (
1391         "MicaSense 5 Channel Camera" => "micasense_5",
1392         "CCD Color Camera" => "ccd_color",
1393         "CMOS Color Camera" => "cmos_color"
1394     );
1395
1396     my $upload_file = $c->req->upload('upload_drone_imagery_bulk_images_zipfile_previous');
1397     my $upload_geojson_file = $c->req->upload('upload_drone_imagery_bulk_geojson_zipfile_previous');
1398     my $imaging_events_file = $c->req->upload('upload_drone_imagery_bulk_imaging_events_previous');
1399
1400     my $upload_original_name = $upload_file->filename();
1401     my $upload_tempfile = $upload_file->tempname;
1402     my $upload_geojson_original_name = $upload_geojson_file->filename();
1403     my $upload_geojson_tempfile = $upload_geojson_file->tempname;
1404     my $upload_imaging_events_file = $imaging_events_file->tempname;
1405     my $time = DateTime->now();
1406     my $timestamp = $time->ymd()."_".$time->hms();
1407
1408     my $uploader = CXGN::UploadFile->new({
1409         tempfile => $upload_tempfile,
1410         subdirectory => "drone_imagery_upload_bulk_previous_orthophoto_zips",
1411         archive_path => $c->config->{archive_path},
1412         archive_filename => $upload_original_name,
1413         timestamp => $timestamp,
1414         user_id => $user_id,
1415         user_role => $user_role
1416     });
1417     my $archived_filename_with_path = $uploader->archive();
1418     my $md5 = $uploader->get_md5($archived_filename_with_path);
1419     if (!$archived_filename_with_path) {
1420         $c->stash->{rest} = { error => "Could not save file $upload_original_name in archive." };
1421         $c->detach();
1422     }
1423     unlink $upload_tempfile;
1424     print STDERR "Archived Drone Image Bulk Previous Orthophoto Zip File: $archived_filename_with_path\n";
1425
1426     my $archived_zip = CXGN::ZipFile->new(archived_zipfile_path=>$archived_filename_with_path);
1427     my $file_members = $archived_zip->file_members();
1428     if (!$file_members){
1429         $c->stash->{message} = 'Could not read your orthophoto bulk zipfile. Is it .zip format';
1430         $c->stash->{template} = 'generic_message.mas';
1431         return;
1432     }
1433
1434     my %filename_imaging_event_lookup;
1435     my %filename_imaging_event_band_check;
1436     foreach (@$file_members) {
1437         my $image = SGN::Image->new( $dbh, undef, $c );
1438         my $filename = $_->fileName();
1439         my @zipfile_comp = split '\/', $filename;
1440         my $filename_wext;
1441         if (scalar(@zipfile_comp)==1) {
1442             $filename_wext = $zipfile_comp[0];
1443         }
1444         else {
1445             $filename_wext = $zipfile_comp[1];
1446         }
1447         my @filename_comps = split '\.', $filename_wext;
1448         my $filename_only = $filename_comps[0];
1449         my @image_spectra = split '\_\_', $filename_only;
1450         my $temp_file = $image->upload_zipfile_images($_);
1451         my $imaging_event_name = $image_spectra[0];
1452         my $band = $image_spectra[1];
1453
1454         if (!exists($spectral_lookup{$band})) {
1455             $c->stash->{message} = "The spectral band $band is not allowed in the provided orthophoto $filename. Make sure the orthophotos are saved as a concatenation with the spectral band, with a double-underscore (__) as the separator (e.g. Ortho1_01012020__blue.tiff) and the allowed spectral bands are blue,green,red,rededge,nir,mir,fir,thir,rgb,bw.";
1456             $c->stash->{template} = 'generic_message.mas';
1457             return;
1458         }
1459         my $spectral_band = $spectral_lookup{$band};
1460         print STDERR Dumper [$filename_wext, $filename, $temp_file, $imaging_event_name, $spectral_band];
1461         $filename_imaging_event_lookup{$filename_wext} = {
1462             file => $temp_file,
1463             band => $spectral_band,
1464             band_short => $band
1465         };
1466         if (exists($filename_imaging_event_band_check{$imaging_event_name}->{$spectral_band})) {
1467             $c->stash->{message} = "Do not upload duplicate spectral types for the same imaging event. There is already a $band image for $imaging_event_name in the zipfile! Make sure the orthophotos are saved as a concatenation of the imaging event name and the spectral band, with a double-underscore (__) as the separator (e.g. Ortho1_01012020__blue.tiff)";
1468             $c->stash->{template} = 'generic_message.mas';
1469             return;
1470         } else {
1471             $filename_imaging_event_band_check{$imaging_event_name}->{$spectral_band}++;
1472         }
1473     }
1474
1475     my $uploader_geojson = CXGN::UploadFile->new({
1476         tempfile => $upload_geojson_tempfile,
1477         subdirectory => "drone_imagery_upload_bulk_previous_geojson_zips",
1478         archive_path => $c->config->{archive_path},
1479         archive_filename => $upload_geojson_original_name,
1480         timestamp => $timestamp,
1481         user_id => $user_id,
1482         user_role => $user_role
1483     });
1484     my $geojson_archived_filename_with_path = $uploader_geojson->archive();
1485     my $md5_geojson = $uploader_geojson->get_md5($geojson_archived_filename_with_path);
1486     if (!$geojson_archived_filename_with_path) {
1487         $c->stash->{message} = "Could not save file $upload_geojson_original_name in archive.";
1488         $c->stash->{template} = 'generic_message.mas';
1489         return;
1490     }
1491     unlink $upload_geojson_tempfile;
1492     print STDERR "Archived Drone Image Bulk Previous GeoJSON Zip File: $geojson_archived_filename_with_path\n";
1493
1494     my $archived_zip_geojson = CXGN::ZipFile->new(archived_zipfile_path=>$geojson_archived_filename_with_path);
1495     my $file_members_geojson = $archived_zip_geojson->file_members();
1496     if (!$file_members_geojson){
1497         $c->stash->{message} = 'Could not read your geojson bulk zipfile. Is it .zip format?';
1498         $c->stash->{template} = 'generic_message.mas';
1499         return;
1500     }
1501
1502     my %filename_imaging_event_geojson_lookup;
1503     foreach (@$file_members_geojson) {
1504         my $image = SGN::Image->new( $dbh, undef, $c );
1505         my $filename = $_->fileName();
1506         my $temp_file = $image->upload_zipfile_images($_);
1507
1508         my @zipfile_comp = split '\/', $filename;
1509         my $filename_wext;
1510         if (scalar(@zipfile_comp) == 1) {
1511             $filename_wext = $zipfile_comp[0];
1512         }
1513         else {
1514             $filename_wext = $zipfile_comp[1];
1515         }
1516
1517         print STDERR Dumper [$filename, $temp_file, $filename_wext];
1518         $filename_imaging_event_geojson_lookup{$filename_wext} = $temp_file;
1519
1520         open(my $fh_geojson_check, '<', $temp_file) or die "Could not open file '$temp_file' $!";
1521             print STDERR "Opened $temp_file\n";
1522             my $geojson_value_check = decode_json <$fh_geojson_check>;
1523             # print STDERR Dumper $geojson_value_check;
1524             if (!$geojson_value_check->{features}) {
1525                 $c->stash->{message} = 'The GeoJSON file '.$filename.' does not have a \'features\' key in it. Make sure the GeoJSON is formatted correctly.';
1526                 $c->stash->{template} = 'generic_message.mas';
1527                 return;
1528             }
1529             foreach (@{$geojson_value_check->{features}}) {
1530                 if (!$_->{properties}) {
1531                     $c->stash->{message} = 'The GeoJSON file '.$filename.' does not have a \'properties\' key in it. Make sure the GeoJSON is formatted correctly.';
1532                     $c->stash->{template} = 'generic_message.mas';
1533                     return;
1534                 }
1535                 if (!$_->{properties}->{ID}) {
1536                     $c->stash->{message} = 'The GeoJSON file '.$filename.' does not have an \'ID\' key in the \'properties\' object. Make sure the GeoJSON is formatted correctly.';
1537                     $c->stash->{template} = 'generic_message.mas';
1538                     return;
1539                 }
1540                 if (!$_->{geometry}) {
1541                     $c->stash->{message} = 'The GeoJSON file '.$filename.' does not have a \'geometry\' key in it. Make sure the GeoJSON is formatted correctly.';
1542                     $c->stash->{template} = 'generic_message.mas';
1543                     return;
1544                 }
1545                 if (!$_->{geometry}->{coordinates}) {
1546                     $c->stash->{message} = 'The GeoJSON file '.$filename.' does not have a \'coordinates\' key in the \'geometry\' object. Make sure the GeoJSON is formatted correctly.';
1547                     $c->stash->{template} = 'generic_message.mas';
1548                     return;
1549                 }
1550                 if (scalar(@{$_->{geometry}->{coordinates}->[0]}) != 5) {
1551                     $c->stash->{message} = 'The GeoJSON file '.$filename.' \'coordinates\' first object does not have 5 objects in it. The polygons must be rectangular Make sure the GeoJSON is formatted correctly.';
1552                     $c->stash->{template} = 'generic_message.mas';
1553                     return;
1554                 }
1555             }
1556         close($fh_geojson_check);
1557     }
1558
1559     my @parse_csv_errors;
1560     my %field_trial_name_lookup;
1561     my %field_trial_layout_lookup;
1562     my %vehicle_name_lookup;
1563
1564     my $parser = Spreadsheet::ParseExcel->new();
1565     my $excel_obj = $parser->parse($upload_imaging_events_file);
1566     if (!$excel_obj) {
1567         $c->stash->{message} = 'The Excel (.xls) file could not be opened:'.$parser->error();
1568         $c->stash->{template} = 'generic_message.mas';
1569         return;
1570     }
1571
1572     my $worksheet = ( $excel_obj->worksheets() )[0]; #support only one worksheet
1573     if (!$worksheet) {
1574         $c->stash->{message} = 'Spreadsheet must be on 1st tab in Excel (.xls) file.';
1575         $c->stash->{template} = 'generic_message.mas';
1576         return;
1577     }
1578     my ( $row_min, $row_max ) = $worksheet->row_range();
1579     my ( $col_min, $col_max ) = $worksheet->col_range();
1580     if (($col_max - $col_min)  < 1 || ($row_max - $row_min) < 1 ) { #must have header and at least one row of plot data
1581         $c->stash->{message} = 'Spreadsheet (.xls) is missing header or contains no rows.';
1582         $c->stash->{template} = 'generic_message.mas';
1583         return;
1584     }
1585
1586     if ($worksheet->get_cell(0,0)->value() ne 'Imaging Event Name' ||
1587         $worksheet->get_cell(0,1)->value() ne 'Type' ||
1588         $worksheet->get_cell(0,2)->value() ne 'Description' ||
1589         $worksheet->get_cell(0,3)->value() ne 'Date' ||
1590         $worksheet->get_cell(0,4)->value() ne 'Vehicle Name' ||
1591         $worksheet->get_cell(0,5)->value() ne 'Vehicle Battery Set' ||
1592         $worksheet->get_cell(0,6)->value() ne 'Sensor' ||
1593         $worksheet->get_cell(0,7)->value() ne 'Field Trial Name' ||
1594         $worksheet->get_cell(0,8)->value() ne 'GeoJSON Filename' ||
1595         $worksheet->get_cell(0,9)->value() ne 'Image Filenames' ||
1596         $worksheet->get_cell(0,10)->value() ne 'Coordinate System' ||
1597         $worksheet->get_cell(0,11)->value() ne 'Rotation Angle' ||
1598         $worksheet->get_cell(0,12)->value() ne 'Base Date' ||
1599         $worksheet->get_cell(0,13)->value() ne 'Camera Rig') {
1600             $c->stash->{message} = "The header row in the CSV spreadsheet must be 'Imaging Event Name,Type,Description,Date,Vehicle Name,Vehicle Battery Set,Sensor,Field Trial Name,GeoJSON Filename,Image Filenames,Coordinate System,Rotation Angle,Base Date,Camera Rig'.";
1601             $c->stash->{template} = 'generic_message.mas';
1602             return;
1603     }
1604
1605     my %seen_upload_dates;
1606     for my $row ( 1 .. $row_max ) {
1607         my $imaging_event_name;
1608         if ($worksheet->get_cell($row,0)) {
1609             $imaging_event_name = $worksheet->get_cell($row,0)->value();
1610         }
1611         my $imaging_event_type;
1612         if ($worksheet->get_cell($row,1)) {
1613             $imaging_event_type = $worksheet->get_cell($row,1)->value();
1614         }
1615         my $imaging_event_desc;
1616         if ($worksheet->get_cell($row,2)) {
1617             $imaging_event_desc = $worksheet->get_cell($row,2)->value();
1618         }
1619         my $imaging_event_date;
1620         if ($worksheet->get_cell($row,3)) {
1621             $imaging_event_date = $worksheet->get_cell($row,3)->value();
1622         }
1623         my $vehicle_name;
1624         if ($worksheet->get_cell($row,4)) {
1625             $vehicle_name = $worksheet->get_cell($row,4)->value();
1626         }
1627         my $vehicle_battery = 'default_battery';
1628         if ($worksheet->get_cell($row,5)) {
1629             $vehicle_battery = $worksheet->get_cell($row,5)->value();
1630         }
1631         my $sensor;
1632         if ($worksheet->get_cell($row,6)) {
1633             $sensor = $worksheet->get_cell($row,6)->value();
1634         }
1635         my $field_trial_name;
1636         if ($worksheet->get_cell($row,7)) {
1637             $field_trial_name = $worksheet->get_cell($row,7)->value();
1638         }
1639         my $geojson_filename;
1640         if ($worksheet->get_cell($row,8)) {
1641             $geojson_filename = $worksheet->get_cell($row,8)->value();
1642         }
1643         my $image_filenames;
1644         if ($worksheet->get_cell($row,9)) {
1645             $image_filenames = $worksheet->get_cell($row,9)->value();
1646         }
1647         my $coordinate_system;
1648         if ($worksheet->get_cell($row,10)) {
1649             $coordinate_system = $worksheet->get_cell($row,10)->value();
1650         }
1651         my $rotation_angle;
1652         if ($worksheet->get_cell($row,11)) {
1653             $rotation_angle = $worksheet->get_cell($row,11)->value();
1654         }
1655         my $base_date;
1656         if ($worksheet->get_cell($row,12)) {
1657             $base_date = $worksheet->get_cell($row,12)->value();
1658         }
1659         my $rig_desc;
1660         if ($worksheet->get_cell($row,13)) {
1661             $rig_desc = $worksheet->get_cell($row,13)->value();
1662         }
1663
1664         if (!$imaging_event_name){
1665             push @parse_csv_errors, "Please give a new imaging event name!";
1666         }
1667         if (!$imaging_event_type){
1668             push @parse_csv_errors, "Please give an imaging event type!";
1669         }
1670         if (!$imaging_event_desc){
1671             push @parse_csv_errors, "Please give an imaging event description!";
1672         }
1673         if (!$imaging_event_date){
1674             push @parse_csv_errors, "Please give an imaging event date!";
1675         }
1676         if (!$vehicle_name){
1677             push @parse_csv_errors, "Please give a vehicle name!";
1678         }
1679         if (!$sensor){
1680             push @parse_csv_errors, "Please give a sensor name!";
1681         }
1682         if (!$field_trial_name){
1683             push @parse_csv_errors, "Please give a field trial name!";
1684         }
1685         if (defined($rotation_angle) && ($rotation_angle < 0 || $rotation_angle > 360) ) {
1686             push @parse_csv_errors, "Rotation angle $rotation_angle not valid! Must be clock-wise between 0 and 360!";
1687         }
1688
1689         if ($coordinate_system ne 'UTM' && $coordinate_system ne 'WGS84' && $coordinate_system ne 'Pixels') {
1690             push @parse_csv_errors, "The given coordinate system $coordinate_system is not one of: UTM, WGS84, or Pixels!";
1691         }
1692         # if ($coordinate_system ne 'Pixels') {
1693         #     $c->stash->{rest} = {error => "Only the Pixels coordinate system is currently supported. In the future GeoTIFFs will be supported, but for now please only upload simple raster images (.png, .tiff, .jpg)." };
1694         #     $c->detach;
1695         # }
1696
1697         my $field_trial_rs = $schema->resultset("Project::Project")->search({name=>$field_trial_name});
1698         if ($field_trial_rs->count != 1) {
1699             $c->stash->{message} = "The field trial $field_trial_name does not exist in the database already! Please add it first.";
1700             $c->stash->{template} = 'generic_message.mas';
1701             return;
1702         }
1703         my $field_trial_id = $field_trial_rs->first->project_id();
1704         $field_trial_name_lookup{$field_trial_name} = $field_trial_id;
1705
1706         if ($imaging_event_date !~ /^\d{4}\/\d{2}\/\d{2}\s\d\d:\d\d:\d\d$/){
1707             $c->stash->{message} = "Please give a new imaging event date in the format YYYY/MM/DD HH:mm:ss! The provided $imaging_event_date is not correct!";
1708             $c->stash->{template} = 'generic_message.mas';
1709             return;
1710         }
1711         if ($imaging_event_type ne 'Aerial Medium to High Res' && $imaging_event_type ne 'Aerial Low Res'){
1712             $c->stash->{message} = "The imaging event type $imaging_event_type is not one of 'Aerial Low Res' or 'Aerial Medium to High Res'!";
1713             $c->stash->{template} = 'generic_message.mas';
1714             return;
1715         }
1716         if (!exists($sensor_map{$sensor})){
1717             $c->stash->{message} = "The sensor $sensor is not one of 'MicaSense 5 Channel Camera' or 'CCD Color Camera' or 'CMOS Color Camera'!";
1718             $c->stash->{template} = 'generic_message.mas';
1719             return;
1720         }
1721
1722         my $project_rs = $schema->resultset("Project::Project")->search({name=>$imaging_event_name});
1723         if ($project_rs->count > 0) {
1724             push @parse_csv_errors, "Please use a globally unique imaging event name! The name you specified $imaging_event_name has already been used.";
1725         }
1726         my $vehicle_prop = $schema->resultset("Stock::Stock")->search({uniquename => $vehicle_name, type_id=>$imaging_vehicle_cvterm_id});
1727         if ($vehicle_prop->count != 1) {
1728             push @parse_csv_errors, "Imaging event vehicle $vehicle_name is not already in the database! Please add it first!";
1729         }
1730         else {
1731             $vehicle_name_lookup{$vehicle_name} = $vehicle_prop->first->stock_id;
1732         }
1733
1734         my $trial = CXGN::Trial->new({ bcs_schema => $schema, trial_id => $field_trial_id });
1735         my $trial_layout = $trial->get_layout()->get_design();
1736         $field_trial_layout_lookup{$field_trial_id} = $trial_layout;
1737
1738         my $planting_date = $trial->get_planting_date();
1739         if (!$planting_date) {
1740             $c->stash->{message} = "The field trial $field_trial_name does not have a planting date set! Please set this first!";
1741             $c->stash->{template} = 'generic_message.mas';
1742             return;
1743         }
1744         my $planting_date_time_object = Time::Piece->strptime($planting_date, "%Y-%B-%d");
1745         my $imaging_event_date_time_object = Time::Piece->strptime($imaging_event_date, "%Y/%m/%d %H:%M:%S");
1746
1747         if (exists($seen_field_trial_drone_run_dates{$imaging_event_date_time_object->epoch})) {
1748             $c->stash->{message} = "An imaging event has already occured on this field trial at the same date and time ($imaging_event_date)! Please give a unique date/time for each imaging event!";
1749             $c->stash->{template} = 'generic_message.mas';
1750             return;
1751         }
1752         $seen_field_trial_drone_run_dates{$imaging_event_date_time_object->epoch}++;
1753
1754         if ($imaging_event_date_time_object->epoch - $planting_date_time_object->epoch <= 0) {
1755             push @parse_csv_errors, "The date of the imaging event $imaging_event_date is not after the field trial planting date $planting_date!";
1756         }
1757         if ($base_date) {
1758             if ($base_date !~ /^\d{4}\/\d{2}\/\d{2}\s\d\d:\d\d:\d\d$/){
1759                 $c->stash->{message} = "Please give a new imaging event base date in the format YYYY/MM/DD HH:mm:ss! The provided $base_date is not correct! Leave empty if not relevant!";
1760                 $c->stash->{template} = 'generic_message.mas';
1761                 return;
1762             }
1763             my $imaging_event_base_time_object = Time::Piece->strptime($base_date, "%Y/%m/%d %H:%M:%S");
1764
1765             if ($imaging_event_date_time_object->epoch - $imaging_event_base_time_object->epoch < 0) {
1766                 push @parse_csv_errors, "The date of the imaging event $imaging_event_date is not after the base date $base_date!";
1767             }
1768         }
1769
1770         my @orthoimage_names = split ',', $image_filenames;
1771         foreach (@orthoimage_names) {
1772             if (!exists($filename_imaging_event_lookup{$_})) {
1773                 push @parse_csv_errors, "The orthophoto filename $_ does not exist in the uploaded orthophoto zipfile. Make sure the orthophotos are saved as a concatenation of the ortho filename defined in the spreadsheet and the spectral band, with a double-underscore (__) as the separator (e.g. Ortho1_01012020__blue.tiff)";
1774             }
1775         }
1776         if (!exists($filename_imaging_event_geojson_lookup{$geojson_filename})) {
1777             push @parse_csv_errors, "The GeoJSON filename $geojson_filename does not exist in the uploaded GeoJSON zipfile!";
1778         }
1779         open(my $fh_geojson_check, '<', $filename_imaging_event_geojson_lookup{$geojson_filename}) or die "Could not open file '".$filename_imaging_event_geojson_lookup{$geojson_filename}."' $!";
1780             print STDERR "Opened ".$filename_imaging_event_geojson_lookup{$geojson_filename}."\n";
1781             my $geojson_value_check = decode_json <$fh_geojson_check>;
1782             foreach (@{$geojson_value_check->{features}}) {
1783                 my $plot_number = $_->{properties}->{ID};
1784                 if (!exists($trial_layout->{$plot_number})) {
1785                     push @parse_csv_errors, "The ID $plot_number in the GeoJSON file $geojson_filename does not exist in the field trial $field_trial_name!";
1786                 }
1787             }
1788         close($fh_geojson_check);
1789     }
1790
1791     if (scalar(@parse_csv_errors) > 0) {
1792         my $error_string = join "<br/>", @parse_csv_errors;
1793         $c->stash->{message} = $error_string;
1794         $c->stash->{template} = 'generic_message.mas';
1795         return;
1796     }
1797
1798     my $dir = $c->tempfiles_subdir('/upload_drone_imagery_bulk_previous');
1799
1800     my @drone_run_project_ids;
1801     my @drone_run_projects;
1802     my %drone_run_project_info;
1803     for my $row ( 1 .. $row_max ) {
1804         my $imaging_event_name = $worksheet->get_cell($row,0)->value();
1805         my $imaging_event_type = $worksheet->get_cell($row,1)->value();
1806         my $imaging_event_desc = $worksheet->get_cell($row,2)->value();
1807         my $imaging_event_date = $worksheet->get_cell($row,3)->value();
1808         my $vehicle_name = $worksheet->get_cell($row,4)->value();
1809         my $vehicle_battery = $worksheet->get_cell($row,5) ? $worksheet->get_cell($row,5)->value() : 'default_battery';
1810         my $sensor = $worksheet->get_cell($row,6)->value();
1811         my $field_trial_name = $worksheet->get_cell($row,7)->value();
1812         my $geojson_filename = $worksheet->get_cell($row,8)->value();
1813         my $image_filenames = $worksheet->get_cell($row,9)->value();
1814         my $coordinate_system = $worksheet->get_cell($row,10)->value();
1815         my $rotation_angle = $worksheet->get_cell($row,11) ? $worksheet->get_cell($row,11)->value() : 0;
1816         my $base_date = $worksheet->get_cell($row,12) ? $worksheet->get_cell($row,12)->value() : '';
1817         my $rig_desc = $worksheet->get_cell($row,13) ? $worksheet->get_cell($row,13)->value() : '';
1818
1819         my $new_drone_run_vehicle_id = $vehicle_name_lookup{$vehicle_name};
1820         my $selected_trial_id = $field_trial_name_lookup{$field_trial_name};
1821         my $new_drone_run_camera_info = $sensor_map{$sensor};
1822         my $trial = CXGN::Trial->new({ bcs_schema => $schema, trial_id => $selected_trial_id });
1823         my $trial_location_id = $trial->get_location()->[0];
1824         my $planting_date = $trial->get_planting_date();
1825         my $planting_date_time_object = Time::Piece->strptime($planting_date, "%Y-%B-%d");
1826         my $imaging_event_date_time_object = Time::Piece->strptime($imaging_event_date, "%Y/%m/%d %H:%M:%S");
1827         my $drone_run_event = $calendar_funcs->check_value_format($imaging_event_date);
1828         my $time_diff;
1829         my $base_date_event;
1830         if ($base_date) {
1831             my $imaging_event_base_date_time_object = Time::Piece->strptime($base_date, "%Y/%m/%d %H:%M:%S");
1832             $time_diff = $imaging_event_date_time_object - $imaging_event_base_date_time_object;
1833             $base_date_event = $calendar_funcs->check_value_format($base_date);
1834         }
1835         else {
1836             $time_diff = $imaging_event_date_time_object - $planting_date_time_object;
1837         }
1838         my $time_diff_weeks = $time_diff->weeks;
1839         my $time_diff_days = $time_diff->days;
1840         my $time_diff_hours = $time_diff->hours;
1841         my $rounded_time_diff_weeks = round($time_diff_weeks);
1842         if ($rounded_time_diff_weeks == 0) {
1843             $rounded_time_diff_weeks = 1;
1844         }
1845
1846         my $week_term_string = "week $rounded_time_diff_weeks";
1847         my $q = "SELECT t.cvterm_id FROM cvterm as t JOIN cv ON(t.cv_id=cv.cv_id) WHERE t.name=? and cv.name=?;";
1848         my $h = $schema->storage->dbh()->prepare($q);
1849         $h->execute($week_term_string, 'cxgn_time_ontology');
1850         my ($week_cvterm_id) = $h->fetchrow_array();
1851
1852         if (!$week_cvterm_id) {
1853             my $new_week_term = $schema->resultset("Cv::Cvterm")->create_with({
1854                name => $week_term_string,
1855                cv => 'cxgn_time_ontology'
1856             });
1857             $week_cvterm_id = $new_week_term->cvterm_id();
1858         }
1859
1860         my $day_term_string = "day $time_diff_days";
1861         $h->execute($day_term_string, 'cxgn_time_ontology');
1862         my ($day_cvterm_id) = $h->fetchrow_array();
1863
1864         if (!$day_cvterm_id) {
1865             my $new_day_term = $schema->resultset("Cv::Cvterm")->create_with({
1866                name => $day_term_string,
1867                cv => 'cxgn_time_ontology'
1868             });
1869             $day_cvterm_id = $new_day_term->cvterm_id();
1870         }
1871
1872         my $week_term = SGN::Model::Cvterm::get_trait_from_cvterm_id($schema, $week_cvterm_id, 'extended');
1873         my $day_term = SGN::Model::Cvterm::get_trait_from_cvterm_id($schema, $day_cvterm_id, 'extended');
1874
1875         my %related_cvterms = (
1876             week => $week_term,
1877             day => $day_term
1878         );
1879
1880         my $drone_run_projectprops = [
1881             {type_id => $drone_run_type_cvterm_id, value => $imaging_event_type},
1882             {type_id => $project_start_date_type_id, value => $drone_run_event},
1883             {type_id => $design_cvterm_id, value => 'drone_run'},
1884             {type_id => $drone_run_camera_type_cvterm_id, value => $new_drone_run_camera_info},
1885             {type_id => $drone_run_related_cvterms_cvterm_id, value => encode_json \%related_cvterms}
1886         ];
1887         if ($base_date) {
1888             push @$drone_run_projectprops, {type_id => $drone_run_base_date_type_id, value => $base_date_event};
1889         }
1890         if ($rig_desc) {
1891             push @$drone_run_projectprops, {type_id => $drone_run_rig_desc_type_id, value => $rig_desc};
1892         }
1893
1894         my $nd_experiment_rs = $schema->resultset("NaturalDiversity::NdExperiment")->create({
1895             nd_geolocation_id => $trial_location_id,
1896             type_id => $drone_run_experiment_type_id,
1897             nd_experiment_stocks => [{stock_id => $new_drone_run_vehicle_id, type_id => $drone_run_experiment_type_id}]
1898         });
1899         my $drone_run_nd_experiment_id = $nd_experiment_rs->nd_experiment_id();
1900
1901         my $project_rs = $schema->resultset("Project::Project")->create({
1902             name => $imaging_event_name,
1903             description => $imaging_event_desc,
1904             projectprops => $drone_run_projectprops,
1905             project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_trial_id}],
1906             nd_experiment_projects => [{nd_experiment_id => $drone_run_nd_experiment_id}]
1907         });
1908         my $selected_drone_run_id = $project_rs->project_id();
1909         push @drone_run_project_ids, $selected_drone_run_id;
1910
1911         my $vehicle_prop = decode_json $schema->resultset("Stock::Stockprop")->search({stock_id => $new_drone_run_vehicle_id, type_id=>$imaging_vehicle_properties_cvterm_id})->first()->value();
1912         $vehicle_prop->{batteries}->{$vehicle_battery}->{usage}++;
1913         my $vehicle_prop_update = $schema->resultset('Stock::Stockprop')->update_or_create({
1914             type_id=>$imaging_vehicle_properties_cvterm_id,
1915             stock_id=>$new_drone_run_vehicle_id,
1916             rank=>0,
1917             value=>encode_json $vehicle_prop
1918         },
1919         {
1920             key=>'stockprop_c1'
1921         });
1922
1923         my @orthoimage_names = split ',', $image_filenames;
1924         my @ortho_images;
1925         foreach (@orthoimage_names) {
1926             push @ortho_images, $filename_imaging_event_lookup{$_};
1927         }
1928         my @drone_run_band_projects;
1929         my @drone_run_band_project_ids;
1930         my @drone_run_band_geoparams_coordinates;
1931         foreach my $m (@ortho_images) {
1932             my $project_relationship_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'drone_run_band_on_drone_run', 'project_relationship')->cvterm_id();
1933             my $band = $m->{band};
1934             my $band_short = $m->{band_short};
1935             my $file = $m->{file};
1936
1937             my $time = DateTime->now();
1938             my $timestamp = $time->ymd()."_".$time->hms();
1939             my $upload_original_name = $imaging_event_name."_".$band_short.".png";
1940
1941             my $ortho_file;
1942             my @geoparams_coordinates;
1943             if ($coordinate_system eq 'Pixels') {
1944                 $ortho_file = $file;
1945             }
1946             else {
1947                 if ($band_short eq 'rgb') {
1948                     my $outfile_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
1949                     my $outfile_image_r = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
1950                     my $outfile_image_g = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
1951                     my $outfile_image_b = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
1952                     my $outfile_geoparams = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/fileXXXX').".csv";
1953
1954                     my $geo_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/GDALOpenImageRGBGeoTiff.py --image_path $file --outfile_path_image $outfile_image --outfile_path_image_1 $outfile_image_r --outfile_path_image_2 $outfile_image_g --outfile_path_image_3 $outfile_image_b --outfile_path_geo_params $outfile_geoparams ";
1955                     print STDERR $geo_cmd."\n";
1956                     my $geo_cmd_status = system($geo_cmd);
1957                     $ortho_file = $outfile_image;
1958
1959                     open(my $fh_geoparams, '<', $outfile_geoparams) or die "Could not open file '".$outfile_geoparams."' $!";
1960                         print STDERR "Opened ".$outfile_geoparams."\n";
1961                         my $geoparams = <$fh_geoparams>;
1962                         chomp $geoparams;
1963                         @geoparams_coordinates = split ',', $geoparams;
1964                         print STDERR Dumper [$geoparams, \@geoparams_coordinates];
1965                     close($fh_geoparams);
1966                 }
1967                 else {
1968                     my $outfile_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/imageXXXX').".png";
1969                     my $outfile_geoparams = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'upload_drone_imagery_bulk_previous/fileXXXX').".csv";
1970
1971                     my $geo_cmd = $c->config->{python_executable}." ".$c->config->{rootpath}."/DroneImageScripts/ImageProcess/GDALOpenSingleChannelImageGeoTiff.py --image_path $file --outfile_path_image $outfile_image --outfile_path_geo_params $outfile_geoparams ";
1972                     print STDERR $geo_cmd."\n";
1973                     my $geo_cmd_status = system($geo_cmd);
1974                     $ortho_file = $outfile_image;
1975
1976                     open(my $fh_geoparams, '<', $outfile_geoparams) or die "Could not open file '".$outfile_geoparams."' $!";
1977                         print STDERR "Opened ".$outfile_geoparams."\n";
1978                         my $geoparams = <$fh_geoparams>;
1979                         chomp $geoparams;
1980                         @geoparams_coordinates = split ',', $geoparams;
1981                         print STDERR Dumper [$geoparams, \@geoparams_coordinates];
1982                     close($fh_geoparams);
1983                 }
1984             }
1985             push @drone_run_band_geoparams_coordinates, \@geoparams_coordinates;
1986
1987             my $project_rs = $schema->resultset("Project::Project")->create({
1988                 name => $imaging_event_name."_".$band_short,
1989                 description => $imaging_event_desc.". ".$band,
1990                 projectprops => [
1991                     {type_id => $drone_run_band_type_cvterm_id, value => $band},
1992                     {type_id => $design_cvterm_id, value => 'drone_run_band'},
1993                     {type_id => $geoparam_coordinates_type_cvterm_id, value => $coordinate_system},
1994                     {type_id => $geoparam_coordinates_cvterm_id, value => encode_json \@geoparams_coordinates}
1995                 ],
1996                 project_relationship_subject_projects => [{type_id => $project_relationship_type_id, object_project_id => $selected_drone_run_id}]
1997             });
1998             my $selected_drone_run_band_id = $project_rs->project_id();
1999
2000             my $uploader = CXGN::UploadFile->new({
2001                 tempfile => $ortho_file,
2002                 subdirectory => "drone_imagery_upload",
2003                 archive_path => $c->config->{archive_path},
2004                 archive_filename => $upload_original_name,
2005                 timestamp => $timestamp,
2006                 user_id => $user_id,
2007                 user_role => $user_role
2008             });
2009             my $archived_filename_with_path = $uploader->archive();
2010             my $md5 = $uploader->get_md5($archived_filename_with_path);
2011             if (!$archived_filename_with_path) {
2012                 $c->stash->{rest} = { error => "Could not save file $upload_original_name in archive." };
2013                 $c->detach();
2014             }
2015             unlink $upload_tempfile;
2016             print STDERR "Archived Bulk Orthophoto File: $archived_filename_with_path\n";
2017
2018             my $image = SGN::Image->new( $schema->storage->dbh, undef, $c );
2019             $image->set_sp_person_id($user_id);
2020             my $linking_table_type_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stitched_drone_imagery', 'project_md_image')->cvterm_id();
2021             my $ret = $image->process_image($archived_filename_with_path, 'project', $selected_drone_run_band_id, $linking_table_type_id);
2022
2023             push @drone_run_band_projects, {
2024                 drone_run_band_project_id => $selected_drone_run_band_id,
2025                 band => $band
2026             };
2027             push @drone_run_band_project_ids, $selected_drone_run_band_id;
2028         }
2029
2030         my $geojson_temp_filename = $filename_imaging_event_geojson_lookup{$geojson_filename};
2031         push @drone_run_projects, {
2032             drone_run_project_id => $selected_drone_run_id,
2033             drone_run_band_projects => \@drone_run_band_projects,
2034             drone_run_band_project_ids => \@drone_run_band_project_ids,
2035             geojson_temp_filename => $geojson_temp_filename,
2036             time_cvterm_id => $day_cvterm_id,
2037             field_trial_id => $selected_trial_id,
2038             coordinate_system => $coordinate_system,
2039             drone_run_band_geoparams_coordinates => \@drone_run_band_geoparams_coordinates,
2040             rotation_angle => $rotation_angle
2041         };
2042
2043         $drone_run_project_info{$selected_drone_run_id} = {
2044             name => $project_rs->name()
2045         };
2046     }
2047
2048     my $vegetative_indices = ['VARI', 'TGI', 'NDRE', 'NDVI'];
2049     my $phenotype_methods = ['zonal'];
2050     my $standard_process_type = 'minimal';
2051
2052     my $rad_conversion = 0.0174533;
2053     foreach (@drone_run_projects) {
2054         my $drone_run_project_id_in = $_->{drone_run_project_id};
2055         my $time_cvterm_id = $_->{time_cvterm_id};
2056         my $apply_drone_run_band_project_ids = $_->{drone_run_band_project_ids};
2057         my $geojson_filename = $_->{geojson_temp_filename};
2058         my $field_trial_id = $_->{field_trial_id};
2059         my $coordinate_system = $_->{coordinate_system};
2060         my $drone_run_band_geoparams_coordinates = $_->{drone_run_band_geoparams_coordinates};
2061         my $rotate_value = $_->{rotation_angle}*-1;
2062         # my $rotate_value = 0;
2063
2064         my $drone_run_process_in_progress = $schema->resultset('Project::Projectprop')->update_or_create({
2065             type_id=>$process_indicator_cvterm_id,
2066             project_id=>$drone_run_project_id_in,
2067             rank=>0,
2068             value=>1
2069         },
2070         {
2071             key=>'projectprop_c1'
2072         });
2073
2074         my $drone_run_process_completed = $schema->resultset('Project::Projectprop')->update_or_create({
2075             type_id=>$processed_cvterm_id,
2076             project_id=>$drone_run_project_id_in,
2077             rank=>0,
2078             value=>0
2079         },
2080         {
2081             key=>'projectprop_c1'
2082         });
2083
2084         my $drone_run_process_minimal_vi_completed = $schema->resultset('Project::Projectprop')->update_or_create({
2085             type_id=>$processed_minimal_vi_cvterm_id,
2086             project_id=>$drone_run_project_id_in,
2087             rank=>0,
2088             value=>0
2089         },
2090         {
2091             key=>'projectprop_c1'
2092         });
2093
2094         my %vegetative_indices_hash;
2095         foreach (@$vegetative_indices) {
2096             $vegetative_indices_hash{$_}++;
2097         }
2098
2099         my $geojson_value;
2100
2101         open(my $fh_geojson, '<', $geojson_filename) or die "Could not open file '$geojson_filename' $!";
2102             print STDERR "Opened $geojson_filename\n";
2103             $geojson_value = decode_json <$fh_geojson>;
2104         close($fh_geojson);
2105
2106         my $trial_lookup = $field_trial_layout_lookup{$field_trial_id};
2107
2108         my %selected_drone_run_band_types;
2109         my $q2 = "SELECT project_md_image.image_id, drone_run_band_type.value, drone_run_band.project_id
2110             FROM project AS drone_run_band
2111             JOIN projectprop AS drone_run_band_type ON(drone_run_band_type.project_id = drone_run_band.project_id AND drone_run_band_type.type_id = $drone_run_band_type_type_id)
2112             JOIN phenome.project_md_image AS project_md_image ON(project_md_image.project_id = drone_run_band.project_id)
2113             JOIN metadata.md_image ON(project_md_image.image_id = metadata.md_image.image_id)
2114             WHERE project_md_image.type_id = $project_image_type_id
2115             AND drone_run_band.project_id = ?
2116             AND metadata.md_image.obsolete = 'f';";
2117
2118         my $h2 = $schema->storage->dbh()->prepare($q2);
2119
2120         my $term_map = CXGN::DroneImagery::ImageTypes::get_base_imagery_observation_unit_plot_polygon_term_map();
2121
2122         my %drone_run_band_info;
2123         my $drone_run_band_counter = 0;
2124         foreach my $apply_drone_run_band_project_id (@$apply_drone_run_band_project_ids) {
2125
2126             my $h2 = $schema->storage->dbh()->prepare($q2);
2127             $h2->execute($apply_drone_run_band_project_id);
2128             my ($image_id, $drone_run_band_type, $drone_run_band_project_id) = $h2->fetchrow_array();
2129             $selected_drone_run_band_types{$drone_run_band_type} = $drone_run_band_project_id;
2130
2131             my $apply_image_width_ratio = 1;
2132             my $apply_image_height_ratio = 1;
2133
2134             my $dir = $c->tempfiles_subdir('/drone_imagery_rotate');
2135             my $archive_rotate_temp_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'drone_imagery_rotate/imageXXXX');
2136             $archive_rotate_temp_image .= '.png';
2137
2138             my $rotate_return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_image_rotate($c, $schema, $metadata_schema, $drone_run_band_project_id, $image_id, $rotate_value, 0, $user_id, $user_name, $user_role, $archive_rotate_temp_image, 0, 0, 1, 1);
2139             my $rotated_image_id = $rotate_return->{rotated_image_id};
2140
2141             my $image = SGN::Image->new( $schema->storage->dbh, $rotated_image_id, $c );
2142             my $image_fullpath = $image->get_filename('original_converted', 'full');
2143
2144             my @size = imgsize($image_fullpath);
2145             my $width = $size[0];
2146             my $length = $size[1];
2147             my $x_center = $width/2;
2148             my $y_center = $length/2;
2149
2150             my $cropping_value = encode_json [[{x=>0, y=>0}, {x=>$width, y=>0}, {x=>$width, y=>$length}, {x=>0, y=>$length}]];
2151
2152             my $plot_polygons_value;
2153             foreach (@{$geojson_value->{features}}) {
2154                 my $plot_number = $_->{properties}->{ID};
2155                 my $coordinates = $_->{geometry}->{coordinates};
2156                 my $stock_name = $trial_lookup->{$plot_number}->{plot_name};
2157                 my @coords;
2158                 foreach my $crd (@{$coordinates->[0]}) {
2159                     if ($coordinate_system eq 'Pixels') {
2160                         push @coords, {
2161                             x => $crd->[0],
2162                             y => $crd->[1],
2163                         };
2164                     }
2165                     else {
2166                         my $geocoords = $drone_run_band_geoparams_coordinates->[$drone_run_band_counter];
2167
2168                         my $xOrigin = $geocoords->[0];
2169                         my $yOrigin = $geocoords->[3];
2170                         my $pixelWidth = $geocoords->[1];
2171                         my $pixelHeight = -1*$geocoords->[5];
2172                         my $x_pos = ($crd->[0] - $xOrigin) / $pixelWidth;
2173                         my $y_pos = ($yOrigin - $crd->[1] ) / $pixelHeight;
2174
2175                         my $x_pos_rotated = ($x_pos - $x_center)*cos($rad_conversion*$rotate_value*-1) - ($y_pos - $y_center)*sin($rad_conversion*$rotate_value*-1) + $x_center;
2176                         my $y_pos_rotated = ($y_pos - $y_center)*cos($rad_conversion*$rotate_value*-1) + ($x_pos - $x_center)*sin($rad_conversion*$rotate_value*-1) + $y_center;
2177
2178                         push @coords, {
2179                             x => round($x_pos_rotated),
2180                             y => round($y_pos_rotated),
2181                         };
2182                     }
2183                 }
2184                 my $last_point = pop @coords;
2185                 $plot_polygons_value->{$stock_name} = \@coords;
2186             }
2187             $plot_polygons_value = encode_json $plot_polygons_value;
2188
2189             $dir = $c->tempfiles_subdir('/drone_imagery_cropped_image');
2190             my $archive_temp_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'drone_imagery_cropped_image/imageXXXX');
2191             $archive_temp_image .= '.png';
2192
2193             my $cropping_return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_image_cropping($c, $schema, $drone_run_band_project_id, $rotated_image_id, $cropping_value, $user_id, $user_name, $user_role, $archive_temp_image, $apply_image_width_ratio, $apply_image_height_ratio);
2194             my $cropped_image_id = $cropping_return->{cropped_image_id};
2195
2196             $dir = $c->tempfiles_subdir('/drone_imagery_denoise');
2197             my $archive_denoise_temp_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'drone_imagery_denoise/imageXXXX');
2198             $archive_denoise_temp_image .= '.png';
2199
2200             my $denoise_return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_image_denoise($c, $schema, $metadata_schema, $cropped_image_id, $drone_run_band_project_id, $user_id, $user_name, $user_role, $archive_denoise_temp_image);
2201             my $denoised_image_id = $denoise_return->{denoised_image_id};
2202
2203             $drone_run_band_info{$drone_run_band_project_id} = {
2204                 denoised_image_id => $denoised_image_id,
2205                 rotate_value => $rotate_value,
2206                 cropping_value => $cropping_value,
2207                 drone_run_band_type => $drone_run_band_type,
2208                 drone_run_project_id => $drone_run_project_id_in,
2209                 drone_run_project_name => $drone_run_project_info{$drone_run_project_id_in}->{name},
2210                 plot_polygons_value => $plot_polygons_value,
2211                 check_resize => 1,
2212                 keep_original_size_rotate => 1
2213             };
2214
2215             my @denoised_plot_polygon_type = @{$term_map->{$drone_run_band_type}->{observation_unit_plot_polygon_types}->{base}};
2216             my @denoised_background_threshold_removed_imagery_types = @{$term_map->{$drone_run_band_type}->{imagery_types}->{threshold_background}};
2217             my @denoised_background_threshold_removed_plot_polygon_types = @{$term_map->{$drone_run_band_type}->{observation_unit_plot_polygon_types}->{threshold_background}};
2218
2219             my $polygon_type = '';
2220             if ($rotate_value != 0) {
2221                 $polygon_type = 'rectangular_square';
2222             } else {
2223                 $polygon_type = 'rectangular_polygon';
2224             }
2225
2226             foreach (@denoised_plot_polygon_type) {
2227                 my $plot_polygon_original_denoised_return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_plot_polygon_assign($c, $schema, $metadata_schema, $denoised_image_id, $drone_run_band_project_id, $plot_polygons_value, $_, $user_id, $user_name, $user_role, 0, 0, $apply_image_width_ratio, $apply_image_height_ratio, $polygon_type);
2228             }
2229
2230             for my $iterator (0..(scalar(@denoised_background_threshold_removed_imagery_types)-1)) {
2231                 $dir = $c->tempfiles_subdir('/drone_imagery_remove_background');
2232                 my $archive_remove_background_temp_image = $c->config->{basepath}."/".$c->tempfile( TEMPLATE => 'drone_imagery_remove_background/imageXXXX');
2233                 $archive_remove_background_temp_image .= '.png';
2234
2235                 my $background_removed_threshold_return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_image_background_remove_threshold_percentage($c, $schema, $denoised_image_id, $drone_run_band_project_id, $denoised_background_threshold_removed_imagery_types[$iterator], '25', '25', $user_id, $user_name, $user_role, $archive_remove_background_temp_image);
2236
2237                 my $plot_polygon_return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_plot_polygon_assign($c, $schema, $metadata_schema, $background_removed_threshold_return->{removed_background_image_id}, $drone_run_band_project_id, $plot_polygons_value, $denoised_background_threshold_removed_plot_polygon_types[$iterator], $user_id, $user_name, $user_role, 0, 0, $apply_image_width_ratio, $apply_image_height_ratio, $polygon_type);
2238             }
2239
2240             $drone_run_band_counter++;
2241         }
2242
2243         print STDERR Dumper \%selected_drone_run_band_types;
2244         print STDERR Dumper \%vegetative_indices_hash;
2245
2246         SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_minimal_vi_standard_process($c, $schema, $metadata_schema, \%vegetative_indices_hash, \%selected_drone_run_band_types, \%drone_run_band_info, $user_id, $user_name, $user_role, 'rectangular_polygon');
2247
2248         $drone_run_process_in_progress = $schema->resultset('Project::Projectprop')->update_or_create({
2249             type_id=>$process_indicator_cvterm_id,
2250             project_id=>$drone_run_project_id_in,
2251             rank=>0,
2252             value=>0
2253         },
2254         {
2255             key=>'projectprop_c1'
2256         });
2257
2258         $drone_run_process_completed = $schema->resultset('Project::Projectprop')->update_or_create({
2259             type_id=>$processed_cvterm_id,
2260             project_id=>$drone_run_project_id_in,
2261             rank=>0,
2262             value=>1
2263         },
2264         {
2265             key=>'projectprop_c1'
2266         });
2267
2268         $drone_run_process_minimal_vi_completed = $schema->resultset('Project::Projectprop')->update_or_create({
2269             type_id=>$processed_minimal_vi_cvterm_id,
2270             project_id=>$drone_run_project_id_in,
2271             rank=>0,
2272             value=>1
2273         },
2274         {
2275             key=>'projectprop_c1'
2276         });
2277
2278         my $return = SGN::Controller::AJAX::DroneImagery::DroneImagery::_perform_phenotype_automated($c, $schema, $metadata_schema, $phenome_schema, $drone_run_project_id_in, $time_cvterm_id, $phenotype_methods, $standard_process_type, 1, undef, $user_id, $user_name, $user_role);
2279     }
2280
2281     $c->stash->{message} = "Successfully uploaded! Go to <a href='/breeders/drone_imagery'>Drone Imagery</a>";
2282     $c->stash->{template} = 'generic_message.mas';
2283     return;
2284 }
2285
2286 sub _check_user_login {
2287     my $c = shift;
2288     my $user_id;
2289     my $user_name;
2290     my $user_role;
2291     my $session_id = $c->req->param("sgn_session_id");
2292
2293     if ($session_id){
2294         my $dbh = $c->dbc->dbh;
2295         my @user_info = CXGN::Login->new($dbh)->query_from_cookie($session_id);
2296         if (!$user_info[0]){
2297             $c->stash->{message} = 'You must be logged in to do this!';
2298             $c->stash->{template} = 'generic_message.mas';
2299             return;
2300         }
2301         $user_id = $user_info[0];
2302         $user_role = $user_info[1];
2303         my $p = CXGN::People::Person->new($dbh, $user_id);
2304         $user_name = $p->get_username;
2305     } else{
2306         if (!$c->user){
2307             $c->stash->{message} = 'You must be logged in to do this!';
2308             $c->stash->{template} = 'generic_message.mas';
2309             return;
2310         }
2311         $user_id = $c->user()->get_object()->get_sp_person_id();
2312         $user_name = $c->user()->get_object()->get_username();
2313         $user_role = $c->user->get_object->get_user_type();
2314     }
2315     return ($user_id, $user_name, $user_role);
2316 }
2317
2318 1;