#!/usr/bin/perl -w
#	usage (make sure the permissions allow execution): update_db_run_dat_qx [period] [angle]
#							   must have the proper ".my.cnf" in your home directory
#
#							   update_db_run_dat_qx 1 6   (fails for first period b/c there are no vdc/pid flags)
#							   update_db_run_dat_qx 2 6 
#							   update_db_run_dat_qx 2 9
#
# 	by: Jaideep Singh
#
#	date: 07/28/2006 
#
#	about:	creates db_run.dat_test_[period]_[angle], it makes a copy of the old db_run file and gives a new name with a timestamp to prevent 
#		overwriting an older bkp file which are save to ./bkp_db_run_files
#
#	sample of db_run:
#		
#		# Run database for E97-110 (small angle GDH) 15 Jul - 21 Jul 03
#		--------[ 2003-07-01 00:00:00 ]
#
#		# 2.1 GeV values for checkout
#		Ebeam      = 2.134
#		L.theta    = 18.
#		L.pcentral = 2.072
#		R.theta    = -6.
#		R.pcentral = 2.136
#
#		# Use 2.13 GeV matrix elements
#		R.vdc.config = mid_momenta
#		R.ps.config = 2670
#		R.sh.config = 2670
#
#		########  run = 2421(3) ela = pol3he(0)
#		--------[ 2003-07-18 19:43:54 ]
#		  Ebeam        = 2.134170
#		  dEbeam       = 1.00E-05
#		  MA           = 2.808391
#		  R.pcentral   = 2.126753
#		  R.vdc.config = mid_momenta
#		  R.ps.config  = 2670fx
#		  R.sh.config  = 2670fx
use strict "subs";

#VERY IMPORTANT, set the following correctly:
$relative_bkp_path = "bkp_db_run_files";
$period = "oops";
$angle = "oops";
$period = $ARGV[0];
$angle  = $ARGV[1];
if ( !defined ($period) )
{ $period = "undefined"; }
if ( !defined ($angle) )
{ $angle = "undefined"; }

#just checking to make sure that the period and angle are set to the allowed values: (1,6) ; (2,6) ; (2,9)
print qx(date);
if    (($period eq "1") && ($angle eq "6"))
{ print "Period = $period ; Angle = $angle degrees\n"; }
elsif (($period eq "2") && ($angle eq "6"))
{ print "Period = $period ; Angle = $angle degrees\n"; }
elsif (($period eq "2") && ($angle eq "9"))
{ print "Period = $period ; Angle = $angle degrees\n"; }
else
{ 
	print "Period = $period AND Angle = $angle degrees are not acceptable\nonly correct usages: update_db_run_dat_qx 1 6\n";
	print "\t\t     update_db_run_dat_qx 2 6\n";
	print "\t\t     update_db_run_dat_qx 2 9\n";
	die "oops"; 
}

#getting some info about who is updating the db_run.dat file from where
$user = qx(whoami);
$hostname = qx(hostname);
chomp($user);
chomp($hostname);
$mycnf_file = qx(cat ~/\.my\.cnf);
if ($mycnf_file =~ /host=(.*)\ndatabase=(.*)\n/)
{
	$db_host = $1;
	$db_name = $2;
}
else
{
	$db_host = "unknown host";
	$db_name = "unknown database";
}


#add zero to single digits
%addzero = (
           "00" => "00",
           "01" => "01",
           "02" => "02",
           "03" => "03",
           "04" => "04",
           "05" => "05",
           "06" => "06",
           "07" => "07",
           "08" => "08",
           "09" => "09",
           "0" => "00",
           "1" => "01",
           "2" => "02",
           "3" => "03",
           "4" => "04",
           "5" => "05",
           "6" => "06",
           "7" => "07",
           "8" => "08",
           "9" => "09",
           "10" => "10",
           "11" => "11",
           "12" => "12",
           "13" => "13",
           "14" => "14",
           "15" => "15",
           "16" => "16",
           "17" => "17",
           "18" => "18",
           "19" => "19",
           "20" => "20",
           "21" => "21",
           "22" => "22",
           "23" => "23",
           "24" => "24",
           "25" => "25",
           "26" => "26",
           "27" => "27",
           "28" => "28",
           "29" => "29",
           "30" => "30",
           "31" => "31",
           "32" => "32",
           "33" => "33",
           "34" => "34",
           "35" => "35",
           "36" => "36",
           "37" => "37",
           "38" => "38",
           "39" => "39",
           "40" => "40",
           "41" => "41",
           "42" => "42",
           "43" => "43",
           "44" => "44",
           "45" => "45",
           "46" => "46",
           "47" => "47",
           "48" => "48",
           "49" => "49",
           "50" => "50",
           "51" => "51",
           "52" => "52",
           "53" => "53",
           "54" => "54",
           "55" => "55",
           "56" => "56",
           "57" => "57",
           "58" => "58",
           "59" => "59",
           "60" => "60"
           );
# converts C struct month numbers to 3 letter month abbreviations
%num2mon = (
           "00" => "Jan",
           "01" => "Feb",
           "02" => "Mar",
           "03" => "Apr",
           "04" => "May",
           "05" => "Jun",
           "06" => "Jul",
           "07" => "Aug",
           "08" => "Sep",
           "09" => "Oct",
           "10" => "Nov",
           "11" => "Dec"
	   );

#initializing variables
#$filename = "db_run\.dat";
$filename = "db_run\.dat_test"."_$period"."_$angle";
$bad_flag = 0;
$bad_beam = 0;
$bad_pmom = 0;
$bad_mass = 0;
$bad_vdc = 0;
$bad_pid = 0;
$total = 0;
$good_runs = 0;
#getting current timestamp and backing up old db_run.dat file with this timestamp
#full -> ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
($sec,$min,$hour,$mday,$mon,$year) = localtime(time);
$sec  = $addzero{$sec};
$min  = $addzero{$min};
$hour = $addzero{$hour};
$mday = $addzero{$mday};
$mon  = $addzero{$mon};
$mon  = $num2mon{"$mon"};
$year = $addzero{$year + 1900 - 2000};
$change_timestamp = "$hour:$min:$sec\_$mday$mon$year";
$filename_bkp = "\./".$relative_bkp_path."/".$filename."\.bkp\.".$change_timestamp;
print qx(cp $filename $filename_bkp);
print "Just in case Jaideep is being stupid...(sigh)...backing up $filename -> $filename_bkp\n";
print "Getting info from $db_name on $db_host defined in the \.my\.cnf file (MySQL config file) in your home directory (you have this right?)\n";

# setting up MySQL query command
$time_shift = -30;
my $columns = " run , flag , runtype , target1 , target2 , from_unixtime(start_time+$time_shift) , ebeam , ebeam_sigma , pmom , masstar_eff , vdc_flag , pid_flag";
my $tables = " summary ";
my $conditions = " period = $period and nom_angle = $angle and start_time is not NULL ORDER BY start_time";
$query = "SELECT ".$columns." FROM ".$tables." WHERE ".$conditions;

# have to send the query through the shell using the mysq command b/c JLAB doesn't have the perl DBI module?
$raw = qx(echo \"$query\" | mysql -N masterdb);

# writing some preliminary comments to the db file
open(OUTFILE,">$filename");
if ($period eq "1")
{ print OUTFILE "# Run database for E97-110 (small angle GDH) 15 Apr - 23 May 03\n"; }
elsif (($period eq "2") && ($angle eq "6"))
{ print OUTFILE "# Run database for E97-110 (small angle GDH) 01 Jul - 07 Aug 03\n"; }
elsif (($period eq "2") && ($angle eq "9"))
{ print OUTFILE "# Run database for E97-110 (small angle GDH) 08 Aug - 01 Sep 03\n"; }
$year = $year + 2000;
print OUTFILE "# generated from $db_name on $db_host at $hour:$min:$sec on $mon $mday $year by $user from $hostname\n\n";
print OUTFILE "### R\.theta      = central in plane scattering angle in degrees in the hall coordinate system, note the sign\n";
print OUTFILE "### Ebeam        = beam energy in GeV \n";
print OUTFILE "### dEbeam       = beam energy std dev in GeV \n";
print OUTFILE "### MA           = mass of target in GeV, default is neutron, unless it is an elastic/pressure curve run => 3He, or carbon nucleus if carbon run \n";
print OUTFILE "### R\.pcentral   = central or nominal spectrometer setting in GeV/c \n";
print OUTFILE "### R\.vdc\.config = configuration flag that tells the analyzer which optics elements to use from the db_R\.vdc\.dat file \n";
print OUTFILE "### R\.ps\.config  = configuration flag that tells the analyzer which preshower constants to use from the db_R\.ps\.dat file \n";
print OUTFILE "### R\.sh\.config  = configuration flag that tells the analyzer which shower constants to use from the db_R\.sh\.dat file \n\n";
print OUTFILE "# the following are some default values that I (JS) got from vcommander:\n";
if ($period eq "1")
{ print OUTFILE "--------[ 2003-04-15 00:00:00 ]

# 2\.1 GeV values for checkout
Ebeam      = 2\.134
L\.theta    = 18\.
L\.pcentral = 2\.072
R\.theta    = -6\.0
R\.pcentral = 2\.136

# Use 2\.13 GeV matrix elements
R\.vdc\.config = first
R\.ps\.config = 2670fx
R\.sh\.config = 2670fx

"; }
elsif (($period eq "2") && ($angle eq "6"))
{ print OUTFILE "--------[ 2003-07-01 00:00:00 ]

# 2\.1 GeV values for checkout
Ebeam      = 2\.134
L\.theta    = 18\.
L\.pcentral = 2\.072
R\.theta    = -6\.0
R\.pcentral = 2\.136

# Use 2\.13 GeV matrix elements
R\.vdc\.config = mid_momenta
R\.ps\.config = 2670fx
R\.sh\.config = 2670fx

"; }
elsif (($period eq "2") && ($angle eq "9"))
{ print OUTFILE "--------[ 2003-08-08 00:00:00 ]

# 3\.8 GeV values for checkout
Ebeam      = 3\.775
L\.theta    = 18\.
L\.pcentral = 2\.072
R\.theta    = -9\.0
R\.pcentral = 2\.136

# Use 2\.13 GeV matrix elements
R\.vdc\.config = mid_momenta
R\.ps\.config = 3590
R\.sh\.config = 3590

"; }

print OUTFILE "# setting these variables for all time\n";
print OUTFILE "# this might look redundant but i have no idea how the analyzer will think about it...\n";
print OUTFILE "M = 0.510998918E-3\n";
print OUTFILE "mbeam = 0.510998918E-3\n";
print OUTFILE "# i dont think that this actually gets used anywhere but whatever...\n";
print OUTFILE "qbeam = -1.0\n\n";

# processing each run one by one
@all_rows = split /\n/ , $raw;
foreach $row (@all_rows)
{	
	@data = split /\t/ , $row;
	$total++;
	$status      = "ok";
	$run         =  $data[0];
	$flag        =  $data[1];
	$runtype     =  $data[2];
	$target1     =  $data[3];
	$target2     =  $data[4];
	$start_time  =  $data[5];
	$ebeam       =  $data[6];
	$ebeam_sigma =  $data[7];
	$pmom        =  $data[8];
	$mass        =  $data[9];
	$vdc         = $data[10];
	$pid         = $data[11];

# checking to make sure that the returned values have the correct units and that NULL values are handled correctly

	if ($total == 1)
	{ $first_run = $run }
	$last_run = $run;

	if ($target2 eq "NULL")
	{ $target2 = " " }

	if ($flag < 2)
	{ $status .= " ; flag is less than 2 ; "; $bad_flag++ }

	if ($ebeam eq "NULL")   
	{ $status .= " ; beam energy is NULL ; "; $bad_beam++ }
	else
	{ $ebeam = $ebeam/1000.0 }

	if ($ebeam_sigma eq "NULL")
	{ $ebeam_sigma = 0.0 }
	else
	{ $ebeam_sigma = $ebeam_sigma/1000.0 }

	if ($pmom eq "NULL")
	{ $status .= " ; spectrometer momentum is NULL ; "; $bad_pmom++ }
	else
	{ $pmom = $pmom/1000.0; }

	if ($mass eq "NULL")   
	{ $status .= " ; mass is NULL ; "; $bad_mass++ }
	else
	{ $mass = $mass/1000.0 }
	
	if ($vdc eq "NULL")
	{ $status .= " ; vdc_flag is NULL ; "; $bad_vdc++ }

	if ($pid eq "NULL")
	{ $status .= " ; pid_flag is NULL ; "; $bad_pid++ }

# if everything is ok for this run, then adding the info to the db file, otherwise writing a comment that the run failed for some reason

	if ($status eq "ok")
	{
		$good_runs++;
		$ebeam_string = sprintf "Ebeam        = %.6f" , $ebeam;
		$ebeam_sigma_string = sprintf "dEbeam       = %.2E" , $ebeam_sigma;
		$mass_string = sprintf "MA           = %.6f" , $mass;
		$pmom_string  = sprintf "R\.pcentral   = %.6f" , $pmom;
		$vdc = "R\.vdc\.config = $vdc";
		$ps  = "R\.ps\.config  = $pid";
		$sh  = "R\.sh\.config  = $pid";
		print OUTFILE "\n########  run = $run($flag) $runtype = $target1($target2)\n";
		print OUTFILE "--------[ $start_time ]\n";
		print OUTFILE "  $ebeam_string\n  $ebeam_sigma_string\n  $mass_string\n  $pmom_string\n  $vdc\n  $ps\n  $sh\n";
# example output:
#		########  run = 2421(3) ela = pol3he(0)
#		--------[ 2003-07-18 19:43:54 ]
#		  Ebeam        = 2.134170
#		  dEbeam       = 1.00E-05
#		  MA           = 2.808391
#		  R.pcentral   = 2.126753
#		  R.vdc.config = mid_momenta
#		  R.ps.config  = 2670fx
#		  R.sh.config  = 2670fx
	}
	else
	{ print OUTFILE "\n#$run($flag) is skipped because: $status\n" };
}
close(OUTFILE);

# some final comments:
print "Processed a total of $total runs starting from $first_run and ending with $last_run\n";
$skipped = $total - $good_runs;
print "Skipped $skipped = $bad_flag (flag < 2) or $bad_beam (ebeam was NULL) or $bad_pmom (pmom was NULL) or $bad_mass (masstar_eff was NULL) or $bad_vdc (vdc_flag was NULL) or $bad_pid (pid_flag was NULL)\n";
print qx(date);