sub calibration_load_list_vars {
	
	my ($where) = @_ ;

	our %quote_status = () ; 

	our %calibrator_users = () ; 

	# &db_min_ro('users','id,name,user_type',"",'','') ;
	# &db_min_ro('users','id,name,user_type,username',"(user_type = 'support' OR user_type = 'manager' OR user_type = 'casual_calibrator') AND name NOT LIKE 'Rory%'",'','') ;
	&db_min_ro('regions','*','','','') ;
	
	&db_min_ro('users','id,name,user_type,username,region_ids',"name NOT LIKE 'Rory%' AND name NOT LIKE 'Handre%'",'','') ;
	$opts{calibrator} .= qq~<option value="none">>>>None<<<</option>~ ;	
	$opts{head_operator}     .= qq~<option value="none">>>>None<<<</option>~ ;	
	foreach my $_uid (sort {$db{users}{$a}{name} cmp $db{users}{$b}{name}} keys %{$db{users}}) {
		next if $useropts{it}{$db{users}{$_uid}{username}} ;
		next if $db{users}{$_uid}{username} =~ m/test/i ;
		next if $db{users}{$_uid}{username} =~ m/handre/i ;
		
		my $regions = join(",",map{"$db{regions}{$_}{code}"} split(/\,/,$db{users}{$_uid}{region_ids})) ;
		
		$regions = "[" . $regions . "]" if $regions ;
		
		# if ($db{users}{$_uid}{user_type} eq 'support' || $db{users}{$_uid}{user_type} eq 'manager' || $db{users}{$_uid}{user_type} eq 'casual_calibrator' || $monitoring_report) {
		if ($db{users}{$_uid}{user_type} eq 'support' || $db{users}{$_uid}{user_type} eq 'manager' || $db{users}{$_uid}{user_type} eq 'casual_calibrator') {
			$calibrator_users{$_uid} = $db{users}{$_uid}{name} ;
			$opts{calibrator} .= qq~<option value="$_uid">$db{users}{$_uid}{name}</option>~ if lc $db{users}{$_uid}{username} eq lc $db{users}{$_uid}{name} ;					
			$opts{calibrator} .= qq~<option value="$_uid">$db{users}{$_uid}{name} [$db{users}{$_uid}{username}]</option>~ if lc $db{users}{$_uid}{username} ne lc $db{users}{$_uid}{name} ;					
			# $opts{calibrator} .= qq~<option value="$_uid">$db{users}{$_uid}{name}</option>~ if !$regions ;					
		}
		if ($db{users}{$_uid}{user_type} eq 'casual_c' || ($db{users}{$_uid}{user_type} eq 'casual_b' && $lcpage eq 'set-up-groups-report')) {
			$opts{head_operator} .= qq~<option value="$_uid">$db{users}{$_uid}{name}</option>~ if lc $db{users}{$_uid}{username} eq lc $db{users}{$_uid}{name} ;
			$opts{head_operator} .= qq~<option value="$_uid">$db{users}{$_uid}{name} [$db{users}{$_uid}{username}]</option>~ if lc $db{users}{$_uid}{username} ne lc $db{users}{$_uid}{name} ;
			# $opts{head_operator} .= qq~<option value="$_uid">$db{users}{$_uid}{name}</option>~ if !$regions ;	
		}
	}
	
	# my $event_systems_sql_where = ($monitoring_report) ? "" : "system_type = 'mobile'" ;
	 
	&db_min_ro('event_systems','*',"system_type = 'mobile' OR system_type = 'solo'",'','') if $set_up_groups;
	&db_min_ro('event_systems','*',"system_type = 'mobile'",'','') if !$set_up_groups;
	
	my @event_quote_ids = () ; 
					
	# &db_min_ro($table,"id,ref,organisation_ids,quote_accepted,event_system_id_multiple,region_id,sport_type_ids,operator_ids,date_from,date_to,if(date_from < '$now_year-$now_mm-$now_dd 00:00:00',0,1) AS 'after_start_date',event_system_calibration_ids,event_system_calibration_status","$where AND `quote_accepted` = '1'",'','') ; 
	&db_min_ro($table,"id,ref,organisation_ids,quote_accepted,event_system_id_multiple,region_id,sport_type_ids,operator_ids,date_from,date_to,event_system_calibration_ids,event_system_calibration_status,event_system_head_operator_ids,times_from,times_to,daily_operator_ids,days_active,monitor_ids,temp_calibrator_ids,set_up_groups_status_ids,set_up_groups_head_operator_ids","$where AND `quote_accepted` = '1'",'','') ; 
	
	unless ($set_up_group) {
		
		my $calibration_status_report_where = join(" OR ", map { "event_quote_id='$_'" } grep { $_ } keys %{$db{$table}});
		&db_min_ro('calibration_status_report','event_quote_id,request_sent',$calibration_status_report_where,'','') ;	

	}
	
	# calibration_status_report
	
	my $min_id = 0 ; my $max_id = 0 ; my @all_event_ids = () ; 
	
	our %event_system_calibration_ids = () ; our %event_system_calibration_status = () ; our %event_system_head_operator_id = () ; our %default_vaules = () ;  our %event_system_request_sent = () ;

	foreach my $id (keys %{$db{$table}}) {
		
		# &common_debug(">>>>>>>>> 1. [$id] $db{$table}{$id}{ref}") ;
		
		next unless $id ; my $cnt = 0 ; 
		
		push @all_event_ids,"`id` = '$id'" ; 
		
		my @ed_from = &common_split_sql_time($db{$table}{$id}{date_from}) ;
		my @ed_to   = &common_split_sql_time($db{$table}{$id}{date_to}) ; 
		
		&common_date_array("$ed_from[0]$ed_from[1]$ed_from[2]","$ed_to[0]$ed_to[1]$ed_to[2]") ;
		
		# $sort_calibration_by_time_ref{"$ed_from[0]$ed_from[1]$ed_from[2]$ed_from[3]$ed_from[4]-$db{$table}{$id}{ref}"} = $id ;
		$sort_calibration_by_time_ref{"$ed_from[0]$ed_from[1]$ed_from[2]$ed_from[3]$ed_from[4]-$id"} = $id ;
		
		# my @event_sys_calibration_status    = split(";",$db{$table}{$id}{event_system_calibration_status}) ; 
		my @event_sys_calibration_status    = ($set_up_groups) ? split(/\;/,$db{$table}{$id}{set_up_groups_status_ids}) : split(/\;/,$db{$table}{$id}{event_system_calibration_status}) ; 
		my @event_system_ids                = split(/\;/,$db{$table}{$id}{event_system_id_multiple}) ; 
		my @calibration_ids                 = split(/\;/,$db{$table}{$id}{event_system_calibration_ids}) ; 
		my @head_operator_ids				= ($set_up_groups) ? split(/\;/,$db{$table}{$id}{set_up_groups_head_operator_ids}) : split(/\;/,$db{$table}{$id}{event_system_head_operator_ids}) ; 
		my @request_sent					= ($set_up_groups) ? () : split(/\|/,$db{calibration_status_report}{$id}{request_sent}) ; 
 
		my $evntday = 0 ; my $evntsys = 0 ; my $system_id = 0 ;
		
		foreach my $_cid (@calibration_ids) {
			
			if ($_cid =~ /~/) {
				($system_id,$_cid) = split(/\~/,$_cid) ; 
			} else {
				$system_id = $event_system_ids[$evntsys] ;
			}
			$evntsys++ ;
 
			next unless $system_id ;
			 
			next unless $db{event_systems}{$system_id}{system_type}  ;
			
			my (@calibrator_ids_per_event_day) = split(/\-/,$_cid);
			$evntday=0; # set the event day back to zero for each set of calibrators
			foreach my $_cid_pd (@calibrator_ids_per_event_day) {
				$evntday++;
				next unless $_cid_pd ;
				$event_system_calibration_ids{$id}{$system_id}{$evntday} = $_cid_pd ;
				$default_vaules{calibrator}{$id}{$system_id}{$evntday} = $_cid_pd ;
				# &common_debug("1. >>> $event_system_calibration_ids{$id}{$system_id}{$evntday} [$id] [$system_id] [$evntday]") ; 
			}
			# $evntsys++;
		}
		
		$evntsys=0;	
		
		foreach my $_cstatus (@event_sys_calibration_status) {
			
			if ($_cstatus =~ /~/) {
				($system_id,$_cstatus) = split(/\~/,$_cstatus) ; 
			} else {
				$system_id = $event_system_ids[$evntsys] ;
			}
			$evntsys++;
				
			next unless $system_id ;
			next unless $db{event_systems}{$system_id}{system_type}  ;
			
			my (@calibrator_status_per_event_day) = split(/\-/,$_cstatus);
			$evntday=0; # set the event day back to zero for each set of calibrators
			foreach my $_cstatus_pd (@calibrator_status_per_event_day) {
				$evntday++;
				next unless $_cstatus_pd ;
				$event_system_calibration_status{$id}{$system_id}{$evntday} = $_cstatus_pd ;
				$default_vaules{status}{$id}{$system_id}{$evntday} = $_cstatus_pd ;
				# &common_debug("1. >>> $event_system_calibration_status{$id}{$system_id}{$evntday} [$id] [$system_id] [$evntday]") ; 
			}
			# $evntsys++;
		}
		
		$evntsys=0;	
		
		foreach my $_cstatus (@head_operator_ids) {
			
			if ($_cstatus =~ /~/) {
				($system_id,$_cstatus) = split(/\~/,$_cstatus) ; 
			} else {
				$system_id = $event_system_ids[$evntsys] ;
			}
			$evntsys++ ;
			next unless $system_id ;
			next unless $db{event_systems}{$system_id}{system_type} ;
			
			my (@head_operator_id_event_day) = split(/\-/,$_cstatus);
			$evntday=0; # set the event day back to zero for each set of calibrators
			foreach my $_cstatus_pd (@head_operator_id_event_day) {
				$evntday++;
				next unless $_cstatus_pd ; 
				$event_system_head_operator_id{$id}{$system_id}{$evntday} = $_cstatus_pd ;
				$default_vaules{head_operator}{$id}{$system_id}{$evntday} = $_cstatus_pd ;
				# &common_debug("1. >>> $event_system_calibration_status{$id}{$system_id}{$evntday} [$id] [$system_id] [$evntday]") ; 
			}
			# $evntsys++;
		}
		
		foreach my $data_per_system (@request_sent) {
			
			($system_id,$data_per_system) = split(/\~/,$data_per_system) ;
			next unless $system_id ;
			
			next unless $db{event_systems}{$system_id}{system_type} ;

			my @request_sent_event_day = split(/\;/,$data_per_system) ;
			$evntday = 0 ;
			foreach my $_cstatus_pd (@request_sent_event_day) {
				$evntday++ ;
				next unless $_cstatus_pd ;
				$event_system_request_sent{$id}{$system_id}{$evntday} = $_cstatus_pd ;
				$default_vaules{request_sent}{$id}{$system_id}{$evntday} = $_cstatus_pd ;
			}
		}
	}

	# &db_min_ro('event_systems','*',"system_type = 'mobile'",'','') ;

	# &calibration_check_for_overlapping_dates('event_systems',"event_system_id_multiple") ; 
	
	foreach my $id (sort keys %{$db{$table}}) {
		
		$min_id = $id if not $min_id or $min_id > $id ; 
		$max_id = $id if $max_id < $id ;
	}
	
	my $id_sql = qq~(`id` <= '$max_id' AND `id` >= '$min_id')~ ;
	# $id_sql = qq~ AND $id_sql~ if $where_2 or $where_3 ;
	
	
	# my $id_sql = join (" OR " ,@all_event_ids) ;
	
	# &db_min_ro('event_quotes_min','*',"$where_2$where_3$id_sql",'','') ;
	# &db_min_ro('event_quotes_min','*',"$id_sql",'','') ;
	# &db_min_ro('event_cost_items','id,name','','','')   ;
	&db_min_ro('event_cost_items','id,name',"excl_from_expenses < 1",'','')   ;
	&db_min_ro('calibration_status',"id,status",'','','') unless $calibration_status ;
	
	foreach (sort {$db{calibration_status}{$a}{status} cmp $db{calibration_status}{$b}{status}} keys %{$db{calibration_status}}) {
		$db{calibration_status}{$_}{name} = $db{calibration_status}{$_}{status} ;
		$opts{status} .= qq~<option value="$_">$db{calibration_status}{$_}{status}</option>~ ; 
	}

	&db_min_ro('sport_types','id,name','','','') ;
	&db_min_ro('organisations','id,name','','','') ;
	
} #------------------------------------------------------------------------------------------
 
sub calibration_build_table_and_or_excel {
	
	my ($report) = @_ ;
	
	# our @sql_col_display = ($monitoring_report) ? ("event_nr","event","date","day","start_date","start_time","end_time","venue","region","type","system","sport","operator") : ($temp_calibrator_report) ? ("count","event","date","day","start_date","start_time","end_time","venue","region","type","system","sport","operator","shift_1_(07:00-10:00)","shift_2_(10:00-13:00)","shift_3_(13:00-16:00)","shift_4_(16:00-19:00)") : ("count","event","calibration_date","day","start_date","start_time","venue","region","system","sport","operator","head_operator","calibrator","status","update") ;
	# our @sql_col_display = ("count","event","calibration_date","day","start_date","start_time","venue","region","system","sport","operator","head_operator","calibrator","status","update") ;
	
	our @sql_col_display = ("count","event","calib_date","day","start_date","start_time","venue","region","system","sport","operator","head_operator","request_sent","calibrator","status","frames") ;
	
	@sql_col_display = grep { $_ ne "calibrator" 	} @sql_col_display if $set_up_groups ;
	@sql_col_display = grep { $_ ne "request_sent" 	} @sql_col_display if $set_up_groups ;
	@sql_col_display = grep { $_ ne "frames" 		} @sql_col_display if $set_up_groups ;
	@sql_col_display = grep { $_ ne "head_operator" } @sql_col_display if $calibration_report ;
	
	$custom_column_styles{calibrator}				= qq~style="min-width:150px;"~ ;
	$custom_column_styles{head_operator}			= qq~style="min-width:150px;"~ ;
	$custom_column_styles{status} 					= qq~style="min-width:150px;"~ ;
	
	&report_xlsx_export_header("$xlsxreportname",$xlsxdir,'',$xlsx_title_heading) ;
	
	push @sql_col_display,"update" ;
	
	my $nr_of_cols = scalar @sql_col_display ;
	
	my @rev_sql_col_display = reverse @sql_col_display ;
	
	foreach (@rev_sql_col_display) { $lastchild++ ; $last_child{$_} = $lastchild ; } # &common_debug("last-child : $_ [$last_child{$_}]");	
	
	$xlsxrow-- ;
	
	my $cali_cnt = 0 ;  my $found_editable = 0 ; my @all_select_ids = () ; 
	
	$format84 -> set_align("center") ; 
	
	my $found_editable_status = 0 ; 
	
	our $max_frame_width = 0 ; 
	
	my %default_val_id = () ;
	
	# foreach my $id (sort {$db{$table}{$a}{date_from} cmp $db{$table}{$b}{date_from}} keys %{$db{$table}}) {
	foreach my $date_ref (sort {$sort_calibration_by_time_ref{$b} <=> $sort_calibration_by_time_ref{$a}} keys %sort_calibration_by_time_ref) {
		
		my $id = $sort_calibration_by_time_ref{$date_ref} ;
	
		next unless $id ; 
		
		my $system_count = 0 ; my $system_day_cnt = 0 ; 

		my %system_has_daily_op = () ; my %default_op_ids = () ; 
		
		foreach my $system_row (split(/\|/,$db{$table}{$id}{daily_operator_ids})) {
			$system_count++ ; 
			next unless $system_row ;
			$system_day_cnt = 0 ;
			foreach my $col (split(/\;/,$system_row)) {
				$system_day_cnt++ ;
				next unless $col ; 
				$default_op_ids{$system_count}{$system_day_cnt} = $col if $col ;
				$system_has_daily_op{$system_count} = 1 if $col ; 
			}
		}	
		$system_count = 0 ; 
		
		unless (&common_check_if_string_contains_an_integer($db{$table}{$id}{event_system_id_multiple})) {
			$db{$table}{$id}{event_system_id_multiple} = ";none;" ; 
		} 
	
		my @system_name_ids = split(/\;/,$db{$table}{$id}{event_system_id_multiple}) ;		
		my @club_ids        = split(/\;/,$db{$table}{$id}{club_ids}) ;
		my @op_ids        	= ($db{$table}{$id}{operator_ids} =~ /,/) ? split(/\,/,$db{$table}{$id}{operator_ids}) : ($db{$table}{$id}{operator_ids} =~ /;/) ? split(/\;/,$db{$table}{$id}{operator_ids}) : $db{$table}{$id}{operator_ids} ;	
		my @start_times 	= split(/\;/,";".$db{$table}{$id}{times_from}) ;	  
		my @end_times 		= split(/\;/,$db{$table}{$id}{times_to}.";") ;	  
		my @days_active		= split(/\;/,$db{$table}{$id}{days_active}) ;	  
		
		my $cnt_rows = 0 ;
		for (1 .. 50) {
			$cnt_rows = $_ if $system_name_ids[$_ - 1] or $club_ids[$_ - 1] ;  
		}

		$system_count = 0 ; 
		
		$colname_to_id{head_operator} = "head_operator" ;
		$colname_to_id{calibrator} = "calibrator_id" ;
		$colname_to_id{status} = "calibration_status" ;
		$colname_to_id{request_sent} = "request_sent" ;
		
		$preferred_title{head_operator} = "Head Operator" ; 
		$preferred_title{calibrator} = "Calibrator" ; 
		$preferred_title{status} = "Calibration Status" ;	
		
		$default_val_table{head_operator} = 'users' ;
		$default_val_table{calibrator} = 'users' ;
		$default_val_table{status} = 'calibration_status' ;
		# $default_val_table{request_sent} = 'calibration_status_report' ;
		
		&calibration_get_status_frames($id) ; 
		
		foreach my $event_sys_id (@system_name_ids) {
			
			$system_count++ ;	
					
			next unless $event_sys_id ;
			
			next unless $db{event_systems}{$event_sys_id}{system_type} ;
				
			$system_day_cnt = 0 ; 
				
			foreach my $cal_date (sort keys %day_of_week) {
				
				my $srch_date_from_comp   = $i{date_from} ; $srch_date_from_comp =~ s/\-//g ;
				my $srch_date_to_comp     = $i{date_to} ; $srch_date_to_comp =~ s/\-//g ;
				my $date_from_comp        = substr($db{$table}{$id}{date_from},0,10) ; 	$date_from_comp =~ s/\-//g ;
				my $date_to_comp          = substr($db{$table}{$id}{date_to},0,10) ; 	$date_to_comp 	=~ s/\-//g ;
				my $cal_date_comp         = $cal_date ; # $cal_date_comp =~ s/\-//g ;
				   $cal_date              = substr($cal_date,0,4) . '-' . substr($cal_date,4,2) . '-' . substr($cal_date,6,2) ; 
				my $date_from             = substr($db{$table}{$id}{date_from},0,10) ;
				$system_day_cnt++ ;
		
				next if $cal_date_comp < $date_from_comp ;
				
				next if $cal_date_comp > $date_to_comp ;
 
				$cnt{$event_sys_id}{$id}++ ;
				
				next if $db{$table}{$id}{days_active} && !$days_active[$cnt{$event_sys_id}{$id}-1] ; 

				next if $cal_date_comp > $srch_date_to_comp ;

				next if $cal_date_comp < $srch_date_from_comp ;
				
				next if $i{calibration_status} && $i{calibration_status} ne 'all' && $i{calibration_status} ne $default_vaules{status}{$id}{$event_sys_id}{$cnt{$event_sys_id}{$id}} ;

				$cali_cnt++;
					
				$xlsxcol = 0 ; 
				$print_tbody .= qq~<tr id="$id$dow">~ if $report ;
				
				my $nr_of_cols_cnt = $nr_of_cols + 1 ; 
				
				our $default_javascript = qq~~ ;
				
				my $contains_select = 0 ; 
				
				foreach (@sql_col_display) { 
					
					$nr_of_cols_cnt-- ; 
					
					next unless $_ ; # blank for the buttons column
					
					my $formatting = $format84 ;
					my $val        = $db{$table}{$id}{$_} ;
					our $val_min    = $val  ;
					my $align      = qq~ class="dt-center"~ ; 
					my $nowrap     = '' ;
			
					my $sys_day_cnt = $cnt{$event_sys_id}{$id} ;
					
					my $field = "" ; my $default_val = $default_vaules{$_}{$id}{$event_sys_id}{$sys_day_cnt} ; my $only_display_val = 0 ; my $background_color ="#424949" ; 	my $default_val_is_saved = ($default_val) ? 1 : 0 ;	

					if ($_ eq 'head_operator' || $_ eq 'calibrator' || $_ eq 'status' || $_ eq 'request_sent') {
	
						$val_min = '' ;
		
						$formatting = $format91 ;

						if ($report) {						
							
							$field = "$colname_to_id{$_}\_$id\_$event_sys_id\_$sys_day_cnt" ;
							
							if (($cal_date_comp >= $now_ccyymmdd && $event_sys_id ne "-1") || (!$default_val)) {		
								$preferred_title{$field} = $preferred_title{$_} ;
								$opts{$field}            = $opts{$_} ;
								# $useropts{'common'}{'css'} .= qq~.$_ { min-width:180px; } ~ ; ????
								$val_min = $db{$default_val_table{$_}}{$default_val}{name} if $default_val_table{$_} ;
								if ($_ eq 'status') {
									if ($default_val) {
										if ($default_val eq '1') { #calibrating
											$background_color = "#FFA500" ;
											$formatting = $format89 ;											
										} elsif ($default_val eq '2') { #cancelled
											$background_color = "#AD0502" ;		
											$formatting = $format90 ;
										} elsif ($default_val  eq '3') { #done
											$background_color = "#2ECC71" ;		
											$formatting = $format88 ;											
										} elsif ($default_val eq '4') { 
											$background_color = "#424949" ;											
										}
									} else {
										$default_val = 4 ; 			
									}											
								}
								$found_editable	= 1 ; 							
								$contains_select = 1 ; 
							} else {
								$default_val_id{$_}{$db{$default_val_table{$_}}{$default_val}{name}} = $default_val if $default_val_table{$_} ;
								$default_val = $db{$default_val_table{$_}}{$default_val}{name} if $default_val_table{$_} ;
								$default_val = "None" if $default_val eq 'none' ;
								$only_display_val = 1 ;
							}
						} else {
							$default_val_id{$_}{$db{$default_val_table{$_}}{$default_val}{name}} = $default_val if $default_val_table{$_} ;
							$default_val = $db{$default_val_table{$_}}{$default_val}{name}  if $default_val_table{$_} ;
							$default_val = "None" if $default_val eq 'none' ;
							$only_display_val = 1 ;
						}
					}

					if ($_ eq 'count') {
						$val = $cali_cnt ; 
					} elsif ($_ eq 'event') {
						$val = "$db{$table}{$id}{ref} [$id]" ;	
					} elsif ($_ eq 'calib_date' || $_ eq 'date') {			
						# $val = "$cal_date [$day_of_week{$cal_date}]" ;
						$val = "$cal_date" ;
						$nowrap = "nowrap" ;
					} elsif ($_ eq 'day') {
						$val = "$sys_day_cnt" ;
					} elsif ($_ eq 'start_date') {
						$nowrap = "nowrap" ;
						$val = $date_from ;
					} elsif ($_ eq 'venue') {
						my @oids = split(/\,/,$db{$table}{$id}{organisation_ids}) ;
						foreach $_oid (@oids) {
							$val .= $db{organisations}{$_oid}{name} . '<br>' ;
						}
						$val = substr($val,0,-4) if $val ; ;		
						# $nowrap = "nowrap" ;						
					} elsif ($_ eq 'region') {
						$val = $db{regions}{$db{$table}{$id}{region_id}}{code} ;		
					} elsif ($_ eq 'type') {
						$val = (lc $db{event_systems}{$event_sys_id}{description} =~ /cricket/) ? "C" :
								($db{event_systems}{$event_sys_id}{system_type} eq 'mobile') ? "M" :
								($db{event_systems}{$event_sys_id}{system_type} eq 'fixed') ? "F" : "";
					} elsif ($_ eq 'system') {
						$val = '' ; 
						$val = qq~$db{event_systems}{$event_sys_id}{name}~ ; 
						$val .= qq~ ($db{event_systems}{$event_sys_id}{description})~ if $db{event_systems}{$event_sys_id}{description} ; 
						# $nowrap = "nowrap" ;
					} elsif ($_ eq 'sport') {
						$val = '' ; 
						foreach my $sport_id (split(",",$db{$table}{$id}{sport_type_ids})) {
							$db{sport_types}{$sport_id}{name} = uc $db{sport_types}{$sport_id}{name} ;
							$val .= qq~$db{sport_types}{$sport_id}{name}<br>~ ; 
						}
						$val = substr($val,0,-4) if $val ;	
					} elsif ($_ eq 'operator') {
						$nowrap = "nowrap" ;
						$val = '' ; 
						# $val = $db{users}{$table_op_ids[$system_count - 1]}{name} if $table_op_ids[$system_count - 1] ;			
						$val = ($default_op_ids{$system_count}{$cnt{$event_sys_id}{$id}}) ? $db{users}{$default_op_ids{$system_count}{$cnt{$event_sys_id}{$id}}}{name} : (!$default_op_ids{$system_count}{$system_day_cnt} && $system_has_daily_op{$system_count}) ? "None" : $db{users}{$op_ids[$system_count - 1]}{name} ;			
						# $formatting = $format92 if $event_cnt == 2 ;	
						$formatting = $format92 if $cali_cnt == 2 ;	
					} elsif ($_ eq 'start_time') {
						if ($sys_day_cnt == 1 or ($sys_day_cnt > 1 and not $start_times[$sys_day_cnt-1])) {
							$val = substr($db{$table}{$id}{date_from},11,5) ;		
						} else {
							$val = substr($start_times[$sys_day_cnt-1],0,5) ;
						} 
					} elsif ($_ eq 'end_time') {
						if ($start_times[$sys_day_cnt-1] && !$end_times[$sys_day_cnt-1]) {
							$val = substr($db{$table}{$id}{date_to},11,5) ;		
						} else {
							$val = substr($end_times[$sys_day_cnt-1],0,5) ;
						} 
					}  elsif ($_ eq 'head_operator') {
						# $val = ($only_display_val && $default_val) ? qq~<input name='$field' value='$default_val_id{$_}{$default_val}' type='hidden'>~ : qq~~ ;  
						$val .= &common_min_table_select($field,$default_val,$only_display_val,$cali_cnt,$nr_of_cols_cnt,$background_color,$default_val_is_saved,$_) ; 
					} elsif ($_ eq 'request_sent') {
						$val_min = $default_val ;
						# $val = ($only_display_val && $default_val) ? qq~<input name='$field' value='$default_val' type='hidden'>~ : qq~~ ;  
						$default_val = "$now_hour:$now_min:$now_sec" unless $default_val ; 
						$val .= &common_min_table_timepicker($field,$default_val,$only_display_val,$cali_cnt,$nr_of_cols_cnt,$background_color,$default_val_is_saved,$_) ; 
					} elsif ($_ eq 'calibrator') {
						# $val = ($only_display_val && $default_val) ? qq~<input name='$field' value='$default_val_id{$_}{$default_val}' type='hidden'>~ : qq~~ ;  
						$val .= &common_min_table_select($field,$default_val,$only_display_val,$cali_cnt,$nr_of_cols_cnt,$background_color,$default_val_is_saved,$_) ; 	
					} elsif ($_ eq 'status') {
						$found_editable_status = 1 if !$only_display_val ; 
						# $val = ($only_display_val && $default_val) ? qq~<input name='$field' value='$default_val_id{$_}{$default_val}' type='hidden'>~ : qq~~ ;  
						$val .= &common_min_table_select($field,$default_val,$only_display_val,$cali_cnt,$nr_of_cols_cnt,$background_color,$default_val_is_saved,$_) ; 
						$val_min = '' if $val_min == 4 ;
					} elsif ($_ eq 'frames') {
						$nowrap = "nowrap" ;
						$val .= &calibration_get_frames($id,$event_sys_id,$sys_day_cnt) ;
					} elsif ($_ eq 'update') {
						if (($report && $cal_date_comp >= $now_ccyymmdd && $event_sys_id ne "-1") || $contains_select) {		
							$val = &common_min_table_checkbox("update_$id\_$event_sys_id\_$sys_day_cnt") ; 
							chop $default_javascript if $default_javascript ;
							if ($default_javascript) {
								push @default_javascript_by_id, qq~"$id\_$event_sys_id\_$sys_day_cnt":{$default_javascript}~ ;
								push @all_update_field_ids,"#selectHead_operator\_$id\_$event_sys_id\_$sys_day_cnt,#selectCalibrator_id\_$id\_$event_sys_id\_$sys_day_cnt,#selectCalibration_status\_$id\_$event_sys_id\_$sys_day_cnt,#timepickerRequest_sent\_$id\_$event_sys_id\_$sys_day_cnt" ;
							} 
						} else {
							$val = '' ;
						}
					}
					if ($_ ne 'frames' && $_ ne 'request_sent' && $_ ne 'head_operator' && $_ ne 'status' && $_ ne 'calibrator' && substr($_,0,5) ne 'shift' && $_ ne 'update') {
						$val_min = $val ;
						$val_min =~ s/\<br>/\;/g ;	
					}
					
					$print_tbody .= qq~<td $align $nowrap>$val</td>~ if $report ; 
					
					&xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,$val_min,$formatting) if $val_min && $_ ne 'update' ;
					&xlsxcreator_write_xlsx($ws,$xlsxrow,$xlsxcol,"",$format84) if !$val_min && ($_ eq 'head_operator' || $_ eq 'calibrator' || $_ eq 'status' || $_ eq 'request_sent') ;
					$xlsxcol++ ;
					
				}
				
				$print_tbody .= qq~</tr>~ if $report ;

				$xlsxrow++ ; 	
			}
				
		}
	}

	my $all_select_ids_string = join(",",@all_select_ids) ; 
	$trigger_jquery_raw .= qq~\$("$all_select_ids_string").chosen({allow_single_deselect:true});~ ;
	
	if (!$found_editable) {
		$trigger_jquery_raw .= qq~
			\$("#$useropts{table_id} ").find("th:last").remove() ;
			\$("#$useropts{table_id} tr").each(function() {
				\$(this).find("td:last").remove() ;
			}) ;
		~ ;		
	} elsif ($set_up_groups) {
		&common_min_table_select_jquery("#$useropts{table_id} td:nth-last-child(3),#$useropts{table_id} td:nth-last-child(2)") ;
		&common_min_table_update_checkbox_col("3") ;
	} else {
		&common_min_table_select_jquery("#$useropts{table_id} td:nth-last-child(4),#$useropts{table_id} td:nth-last-child(3)") ;
		&common_min_table_update_checkbox_col("3") ;
	}
	
	if ($found_editable) {
		$trigger_jquery .= qq~\$("#$useropts{table_id} td:nth-last-child(3)").css("width","12.5%"); ~ ;			
		$trigger_jquery .= ($set_up_groups) ? qq~\$("#$useropts{table_id} td:nth-last-child(2)").css("width","12.5%");~ : qq~\$("#$useropts{table_id} td:nth-last-child(4)").css("width","12.5%");~ ;
	}
	
	if (!$found_editable_status) {
		$trigger_jquery .= qq~\$("#$useropts{table_id} td:nth-last-child(3)").css("width","");~ if !$set_up_groups ;
	}
	
	$worksheet{$ws}->set_column(0,0,10) ;
	$worksheet{$ws}->set_column(1,1,30) ;
	$worksheet{$ws}->set_column(2,2,15) ;
	$worksheet{$ws}->set_column(3,3,5) ;	
	$worksheet{$ws}->set_column(4,5,13) ;
	$worksheet{$ws}->set_column(6,6,30) ;
	$worksheet{$ws}->set_column(7,7,8 );	
	$worksheet{$ws}->set_column(8,12,30) ;
	$worksheet{$ws}->set_column(13,13,30) unless $set_up_groups ;
	$worksheet{$ws}->set_column(14,14,$max_frame_width/7) unless $set_up_groups ;
	
	&common_min_table_timepicker_jquery("[id^='timepickerRequest_sent_']")	;
	
	&report_xlsx_export_footer('L',15,$xlsxdir) ;
	
} #-------------------------------------------------------------------------------

sub calibration_check_for_overlapping_dates {

	my ($check_table,$checked_column,$date_from_input,$date_to_input,$sys_ids_input,$get_periods) = @_ ; 
 
	my %seen_system_id = () ; my %checked_system_id = () ; my %system_start_date = () ; my %system_end_date = () ; 
	
	my $sys_ids = "" ; 
	
	if ($get_periods) {
		$sys_ids = $sys_ids_input ; 
	}
	
	foreach my $id (sort keys %{$db{$table}}) {
		
		unless ($get_periods) {
			$sys_ids = $db{$table}{$id}{$checked_column} ;
		}		
		foreach (split(";",$sys_ids)) {
			
			$system_start_date{$_}{$id}	= $db{$table}{$id}{date_from} ;
			$system_end_date{$_}{$id} = $db{$table}{$id}{date_to} ;
		}
	}
	
	if ($date_from_input and $date_to_input and $sys_ids_input) {		
		
		foreach (split(";",$sys_ids_input)) {
			
			$system_start_date{$_}{none} = $date_from_input ;
			$system_end_date{$_}{none} = $date_to_input ;
		}
	}
	
	foreach my $id (sort keys %{$db{$table}}) {

		unless ($get_periods) {
			$sys_ids = $db{$table}{$id}{$checked_column} ;
		}	
		
		foreach my $sys_id (split(";",$sys_ids)) {
			
			next unless $sys_id ;
						
			if ($seen_system_id{$sys_id} and not $checked_system_id{$sys_id}) {
				
				$checked_system_id{$sys_id} = 1 ;
				foreach my $id2 (keys %{$system_start_date{$sys_id}}) {
					
					next if $id2 eq $id ;
					my $t1 = &common_check_if_time_is_greater($system_start_date{$sys_id}{$id2},$db{$table}{$id}{date_to}) ; 	## 21<12
					my $t2 = &common_check_if_time_is_greater($system_end_date{$sys_id}{$id2},$db{$table}{$id}{date_to}) ; 		## 22<12
					my $t3 = &common_check_if_time_is_greater($system_start_date{$sys_id}{$id2},$db{$table}{$id}{date_from}) ;	## 21<11
					my $t4 = &common_check_if_time_is_greater($system_end_date{$sys_id}{$id2},$db{$table}{$id}{date_from}) ;		## 22<11
					
					if (($t1 and not $t2) or ($t3 and not $t4) or (not $t3 and $t2) or ($t3 and not $t2)) {
						my $system_name = qq~$db{$check_table}{$sys_id}{name}~ ;
						$system_name .= qq~ ($db{$check_table}{$sys_id}{description})~ if $db{$check_table}{$sys_id}{description} ; 
						
						if ($get_periods) {
							$overlap_dates{$sys_id}{$id2} = "$db{$table}{$id2}{date_from} - $db{$table}{$id2}{date_to}" ;
						} else {
							$trigger_jquery_raw .= qq~
								noty({text:'System $system_name and cannot be used in both event $db{$table}{$id}{ref} and $db{$table}{$id2}{ref} as the times ovelap!!',layout:"center",type:"error",timeout:30000}) ;						
							~ ;								
						}
					}				
				}
			}
			$seen_system_id{$sys_id} = 1 ;
		}
	}
	
} #-------------------------------------------------------------------------------

sub calibration_get_frames {
	
	my ($id,$event_sys_id,$sys_day_cnt) = @_ ; 

	$nowrap = "nowrap" ;
	my $attachments = "" ; my $attach_butt = "" ; 
	
	if ($gallery{$id}{$event_sys_id}{$sys_day_cnt}) {
		
		my $margin = 5 ; my $scaled_height = 100 ; my $total_width = 0 ; my $max_height = 350 ;  my $max_image_height = 0 ; 
		
		foreach my $img_name (split(/\;/,$gallery{$id}{$event_sys_id}{$sys_day_cnt})) {
			
			my $file_path = "$htmlpath/uploads/calibration_status_uploads/$id/$img_name";

			my ($width, $height) = imgsize($file_path);
			
			if ($height && -f $file_path) {
				
				my $final_file = $file_path;  # default: use original
				# my $scaled_width  = $width;
				# my $scaled_height = $height;

				if ($height > $max_height) {
					
					$max_image_height = $max_height if $max_image_height < $height ;
					
					# Load and resize with fixed height = 500
					my $img = Image::Magick->new ;
					$img->Read($file_path) ;

					my $scale = $max_height / $height;
					$width  = int($width * $scale);
					$height = $max_height;

					$img->Resize(geometry => "${width}x${height}");

					# Write to temporary file
					my ($fh, $tempfile) = tempfile(SUFFIX => '.jpg');
					$img->Write(filename => $tempfile, quality => 70);

					$final_file = $tempfile;
					$temp_files{$tempfile} = 1;   # track temp file for cleanup
				} else {
					$max_image_height = $height if $max_image_height < $height ;
				}
				
				($width,$height) = imgsize($final_file);
				
				$total_width += $margin;

				&xlsxcreator_insert_image($ws,$xlsxrow,$xlsxcol,$final_file,$total_width,$margin,150/$height,150/$height) if $height > 150 ;
				&xlsxcreator_insert_image($ws,$xlsxrow,$xlsxcol,$final_file,$total_width,$margin) if $height <= 150 ;

				$total_width += $width*150/$height;
			}

			$total_width += $margin;
		}

		$worksheet{$ws}->set_row($xlsxrow,(2*$margin+150)*(83/110)) if $max_image_height ;
		
		$max_frame_width = $total_width if $total_width && $max_frame_width < $total_width ;
		
		$attachments .= &calibration_get_uploads_list($db{calibration_status_report}{$id}{event_quote_id},$gallery{$id}{$event_sys_id}{$sys_day_cnt}) ;
	}
		
	if ($attachments) {
	
		$attach_butt = qq~
			&nbsp;
			<a href="#" data-toggle="popover" data-placement="left" data-content="$attachments" title="Uploads" data-html="true" data-trigger="focus" class="btn btn-round btn-default">
				<i class="glyphicon glyphicon-paperclip" style="top:4px;"></i>
			</a>
		~ ; 
		
	}

	my $val_return = qq~
		$attach_butt
		<a href="javascript:dlgMdl('$useropts{scripts}/dialog/calibration_status_report_uploads.pl?$id&$event_sys_id&$sys_day_cnt','Edit Images','','medium-dialog');" class="btn btn-round btn-info" data-toggle="tooltip" data-placement="left" title="" data-original-title="Edit Images">
			<i class="glyphicon glyphicon-edit" style="top:4px;">
			</i>
		</a>
	~ ; 	
	
	return $val_return ;
	
} #-------------------------------------------------------------------------------

sub calibration_get_status_frames {

	my ($event_id) = @_ ;	
		
	return unless $event_id ;
	
	my %frames_hash = () ; 

	foreach my $system_frames (split(/\|/,$db{calibration_status_report}{$event_id}{upload_names})) {
		
		next unless $system_frames ;
		my ($system_id,$system_frames_2) = split(/\~/,$system_frames) ; 
		my $day_cnt = 0 ; 
		
		foreach my $day_frames (split(/\:/,$system_frames_2)) {
			$day_cnt++ ;
			next unless $day_frames ;
			$gallery{$event_id}{$system_id}{$day_cnt} = $day_frames ; 
		}
	}

} #------------------------------------------------------------------------------------------

sub calibration_get_uploads_list {

	my ($id,$uploads) = @_ ;

	my $attachments = '' ;

	return '' unless $id ; 
	
	my @uploads_arr = split(/\;/,$uploads) ; 
	
	my $uploads_cnt = 0  ; 
	
	foreach (@uploads_arr) { 
		
		$uploads_cnt++ ; 
		
		next unless $_ ; 

		my $url_prefix = qq~/uploads/calibration_status_uploads/$id~ ;
		
		my ($width,$height) = imgsize("$htmlpath/$url_prefix/$_");
 
		next unless $height ;
		
		$width = 100*($width/$height) ; 
		$height = 100 ; 
		$attachments .= qq~<a target='_blank' href='$url_prefix/$_'><img src='$url_prefix/$_' style='width:$width\px;height:$height\px;border:1px solid #ccc;margin:2px;max-width:100%;display:block;'></a>~ ;
	
	}

	return $attachments ;

} #------------------------------------------------------------------------------------------


1 ;