sub save_timecard{

	my $self = shift;

	my $template = $self->load_tmpl('timecard.htm');
	
	my $db = $self->param('my_db');

	my $cgi_query = $self->query();

	#Setup session using CGI::Session
	my $sid = $cgi_query->param('sid') || undef;
	my $session = new CGI::Session::File($sid, { Directory => 'C:\inetpub\wwwroot\Time\stmp' });
	my $employeeID = $session->param('EmployeeID');
	my $is_supervisor = $session->param('Supervisor');

	my $cardID = $cgi_query->param('cardID');
	my $change_carddate = $cgi_query->param('carddate');
	my $cardEmployeeID = $cgi_query->param('empID');

	#This is one of the few run modes that requires transactions to be used in the DBMS
	#Because timecards save information in several tables (Employees, TimeCards, Remarks), in several distinct steps,
	#we want to make sure that everything goes or nothing goes.
	#The BEGIN happens before any of the statements.  A ROLLBACK should be executed before any errors are thrown.
	#COMMIT takes place just before returning the filled-in HTML::Template to the user at the end of the run mode.

	my $sql_transaction_statement = "BEGIN;";
	my $transaction_sth = $db->prepare($sql_transaction_statement);
	$transaction_sth->execute();

	my @day_prefixes = ('m', 't', 'w', 'r', 'f', 's', 'su');

	my $day_counter = 0;
	my $execute_query = 0;
	my $top_in_rounding = $session->param("TopInRounding");
	my $bottom_in_rounding = $session->param("BottomInRounding");
	my $top_out_rounding = $session->param("TopOutRounding");
	my $bottom_out_rounding = $session->param("BottomOutRounding");
	foreach $day_prefix (@day_prefixes){
		$execute_query = 1;
		my $MornIn = fix_time_format($cgi_query->param($day_prefix . 'MornIn') || 'NULL');
		$MornIn = round_time($MornIn, $top_in_rounding, $bottom_in_rounding);
		my $MornOut = fix_time_format($cgi_query->param($day_prefix . 'MornOut') || 'NULL');
		$MornOut = round_time($MornOut, $top_out_rounding, $bottom_out_rounding);
		if (($MornIn ne 'NULL') && ($MornOut ne 'NULL') && ($MornIn > $MornOut) && ($MornOut > 0)){
			$MornOut = add_twelve($MornOut);
		}
		my $AftIn = fix_time_format($cgi_query->param($day_prefix . 'AftIn') || 'NULL');
		$AftIn = round_time($AftIn, $top_in_rounding, $bottom_in_rounding);
		my $AftOut = fix_time_format($cgi_query->param($day_prefix . 'AftOut') || 'NULL');
		$AftOut = round_time($AftOut, $top_out_rounding, $bottom_out_rounding);
		if (($AftIn ne 'NULL') && ($AftOut ne 'NULL') && ($AftIn > $AftOut) && ($AftOut > 0)){
			$AftOut = add_twelve($AftOut);
		}
		my $NightIn = fix_time_format($cgi_query->param($day_prefix . 'NightIn') || 'NULL');
		$NightIn = round_time($NightIn, $top_in_rounding, $bottom_in_rounding);
		my $NightOut = fix_time_format($cgi_query->param($day_prefix . 'NightOut') || 'NULL');
		$NightOut = round_time($NightOut, $top_out_rounding, $bottom_out_rounding);
		if (($NightIn ne 'NULL') && ($NightOut ne 'NULL') && ($NightIn > $NightOut) && ($NightOut > 0)){
			$NightOut = add_twelve($NightOut);
		}

		my $sick = $cgi_query->param($day_prefix . 'Sick') || 'NULL';
		$sick = ($sick * 3600) unless ($sick eq 'NULL');
		my $vacation = $cgi_query->param($day_prefix . 'Vacation') || 'NULL';
		$vacation = ($vacation * 3600) unless ($vacation eq 'NULL');
		
		#Build SQL and send it to DB
		my $sql_statement = "UPDATE TimeCards SET\n";
		$sql_statement .= ", MornIn=$MornIn\n" if $MornIn;
		$sql_statement .= ", MornOut=$MornOut\n" if $MornOut;
		$sql_statement .= ", AftIn=$AftIn\n" if $AftIn;
		$sql_statement .= ", AftOut=$AftOut\n" if $AftOut;
		$sql_statement .= ", NightIn=$NightIn\n" if $NightIn;
		$sql_statement .= ", NightOut=$NightOut\n" if $NightOut;
		$sql_statement .= ", Sick=$sick\n" if $sick;
		$sql_statement .= ", Vacation=$vacation\n" if $vacation;
		$execute_query = 1 if ($sql_statement =~ s/, //);	#Strip leading ", " from query.  
		#Also set flag to run query if a leading , was stripped (meaning that the query contains meaningful SQL)
		$sql_statement .= "WHERE CardDate='$change_carddate'\n";
		$sql_statement .= "AND EmployeeID='$cardEmployeeID'\n";
		$sql_statement .= "AND DayNumber=$day_counter;";

		#return $sql_statement;

		if ($execute_query){
		#return $sql_statement;
			my $sth = $db->prepare($sql_statement);
			$sth->execute();
		}
		$day_counter++;
	}

	$day_counter = 0;
	$execute_query = 0;
	my $consecutive_day_counter = 0;
	my $regular_time_total = 0;	#If this number exceeds ($week_overtime hours * 3600 seconds/hour) then begin paying out overtime
	#Preferences that affect time calculation:
	my $week_overtime = $session->param("WeekOvertime");
	my $day_overtime = $session->param("DayOvertime");
	my $day_doubletime = $session->param("DayDoubletime");
	my $seventh_day_overtime = $session->param("SeventhDayOvertime");
	foreach $day_prefix (@day_prefixes){

		#Rather than have the DBMS do all the math (with ridiculously complicated SQL),
		#the DBMS will only do addition to get all of the seconds and the script will figure out what to
		#insert in each column (Regular, Overtime, Doubletime)

		$sql_query = "SELECT ABS(IFNULL((TIME_TO_SEC(MornOut) - TIME_TO_SEC(MornIn)), 0)\n";
		$sql_query .= "+ IFNULL((TIME_TO_SEC(AftOut) - TIME_TO_SEC(AftIn)), 0)\n";
		$sql_query .= "+ IFNULL((TIME_TO_SEC(NightOut) - TIME_TO_SEC(NightIn)), 0))\n";
		$sql_query .= "AS TotalSeconds\n";
		$sql_query .= "FROM TimeCards WHERE CardDate='$change_carddate'\n";
		$sql_query .= "AND PayPeriodID=0\n";
		$sql_query .= "AND EmployeeID=$cardEmployeeID\n";
		$sql_query .= "AND DayNumber=$day_counter;\n";

		my $sth = $db->prepare($sql_query);
		$sth->execute();
		my $overtime = 'NULL';
		my $doubletime = 'NULL';
		my $data_ref;
		if ($data_ref = $sth->fetchrow_hashref()){
			#Hours were entered for this day, so increase $consecutive_day_counter
			my %data = %$data_ref;
			$regular = $data{"TotalSeconds"};
			if ($regular > 0){
				$consecutive_day_counter++;
			}else{
				$consecutive_day_counter = 0;
			}
			if (($consecutive_day_counter == 7) && $seventh_day_overtime){
				$overtime = $regular;
				$overtime = 'NULL' if (!($overtime > 0));
				$regular = 'NULL';
				if ($overtime > ($day_overtime * 3600)){
					$overtime = ($day_overtime * 3600);
					$doubletime = ($data{"TotalSeconds"} - ($day_overtime * 3600));
				}	
			}elsif ($regular > ($day_overtime * 3600)){
				$regular = ($day_overtime * 3600);
				$overtime = ($data{"TotalSeconds"} - ($day_overtime * 3600));
				if ($overtime > (($day_doubletime - $day_overtime) * 3600)){
					$overtime = (($day_doubletime - $day_overtime) * 3600);
					$doubletime = ($data{"TotalSeconds"} - ($day_doubletime * 3600));
				}
			}
		}else{
			$consecutive_day_counter = 0;
			$regular = 'NULL';
		}
		$regular = 'NULL' if ($regular == 0);
		
		$regular_time_total = $regular_time_total + $regular unless ($regular eq 'NULL');
		if (($regular_time_total > ($week_overtime * 3600)) && ($regular > 0)){
			$overtime = $overtime + $regular;
			$regular = 'NULL';
		}
	
		$sql_statement = "UPDATE TimeCards SET\n";
		$sql_statement .= "Regular=$regular,\n";
		$sql_statement .= "Overtime=$overtime,\n";
		$sql_statement .= "Doubletime=$doubletime\n";
		$sql_statement .= "WHERE CardDate='$change_carddate'\n";
		$sql_statement .= "AND EmployeeID='$cardEmployeeID'\n";
		$sql_statement .= "AND DayNumber=$day_counter;";

		#return $sql_statement;

		$sth = $db->prepare($sql_statement);
		$sth->execute();
		$day_counter++;
	}

	#Insert remarks into Remarks table
	my $remarks = $cgi_query->param('remarks');
	if ($remarks){
		my $sql_statement3;
		my $sql_query2 = "SELECT Remarks FROM Remarks WHERE EmployeeID='$cardEmployeeID'\n";
		$sql_query2 .= "AND CardDate='$change_carddate';";

		my $sth2 = $db->prepare($sql_query2);
		$sth2->execute();

		$remarks = $db->quote($remarks);	#Escape necessary characters before passing into DBMS

		if ($sth2->fetchrow_hashref()){
			$sql_statement3 = "UPDATE Remarks SET\n";
			$sql_statement3 .= "Remarks=$remarks\n";
			$sql_statement3 .= "WHERE EmployeeID='$cardEmployeeID'\n";
			$sql_statement3 .= "AND CardDate='$change_carddate';";
		}else{
			$sql_statement3 = "INSERT INTO Remarks (EmployeeID, CardDate, Remarks)\n";
			$sql_statement3 .= "VALUES ('$cardEmployeeID', '$change_carddate', $remarks);";
		}

		my $sth3 = $db->prepare($sql_statement3);
		$sth3->execute();
	}

	#Update vacation and sick leave balances
	my $sql_query4 = "SELECT IFNULL(SUM(Vacation), 0) AS UsedVacation, IFNULL(SUM(Sick), 0) AS UsedSick\n";
	$sql_query4 .= "FROM TimeCards\n";
	$sql_query4 .= "WHERE EmployeeID='$cardEmployeeID';";

	my $sth4 = $db->prepare($sql_query4);
	$sth4->execute();

	my $data_ref = $sth4->fetchrow_hashref();
	my %data = %$data_ref;

	my $used_vacation = $data{"UsedVacation"};
	my $used_sick = $data{"UsedSick"};

	my $sql_query5 = "SELECT (AnnualVacation * 3600) AS PreVacationBalance,\n";
	$sql_query5 .= "(AnnualSick * 3600) AS PreSickLeaveBalance\n";
	$sql_query5 .= "FROM Employees\n";
	$sql_query5 .= "WHERE EmployeeID='$cardEmployeeID';";

	my $sth5 = $db->prepare($sql_query5);
	$sth5->execute();

	if (!($data_ref = $sth5->fetchrow_hashref())){
		#Error.  Rollback DB:
		$sql_transaction_statement = "ROLLBACK;";
		$transaction_sth = $db->prepare($sql_transaction_statement);
		$transaction_sth->execute();
		#And send error message:
		my $output = "Error calculating remaining vacation and sick time.  Please see administrator<BR>\n";
		return $output;
	}
	%data2 = %$data_ref;
	my $output;
	if (($data2{"PreVacationBalance"} - $used_vacation) < 0){
		$output .= "Not enough vacation time remaining to fulfill this request.  Please see administrator<BR>\n";
	}
	if (($data2{"PreSickLeaveBalance"} - $used_sick) < 0){
		$output .= "Not enough sick leave remaining to fulfill this request.  Please see administrator<BR>\n";
	}
	if ($output){
		#Error.  Rollback DB.
		$sql_transaction_statement = "ROLLBACK;";
		$transaction_sth = $db->prepare($sql_transaction_statement);
		$transaction_sth->execute();
		#And send error message
		return $self->display_error($output);
	}

	my $sql_statement6 = "UPDATE Employees SET\n";
	$sql_statement6 .= "VacationBalance=($data2{'PreVacationBalance'} - $used_vacation),\n";
	$sql_statement6 .= "SickLeaveBalance=($data2{'PreSickLeaveBalance'} - $used_sick)\n";
	$sql_statement6 .= "WHERE EmployeeID='$cardEmployeeID';";
	
	my $sth6 = $db->prepare($sql_statement6);
	$sth6->execute;

	#Everything looks good.  Commit changes to DB
	$sql_transaction_statement = "COMMIT;";
	$transaction_sth = $db->prepare($sql_transaction_statement);
	$transaction_sth->execute();

	#Return user to updated timecard
	$self->param('carddate' => $change_carddate);
	$self->param('empID' => $cardEmployeeID);
	return $self->display_timecard();

}	#End of sub save_timecard