Index: /trunk/ippToPsps/scripts/ippToPsps_run.pl
===================================================================
--- /trunk/ippToPsps/scripts/ippToPsps_run.pl	(revision 28119)
+++ /trunk/ippToPsps/scripts/ippToPsps_run.pl	(revision 28120)
@@ -12,9 +12,5 @@
 
 # globals
-my ($verbose, $save_temps, $no_op, $no_update, $camera, $batchType, $output, $dvodb, $datastoreProduct, $label, $singleExpId, $no_publish);
-
-# TODO temporary until we use database to store current jobid
-my $jobIdFilePath = "./config/currentJobId.txt";
-my $logFile = "./config/logfile.txt";
+my ($verbose, $save_temps, $no_op, $no_update, $camera, $batchType, $output, $dvodb, $datastoreProduct, $label, $singleExpId, $no_publish, $force);
 
 # default values for certain globals
@@ -28,5 +24,5 @@
 $singleExpId = undef;
 $datastoreProduct = "PSPS_test";
-
+$force = 0;
 
 # get user args
@@ -43,4 +39,5 @@
         'no-op' => \$no_op,
         'no-update' => \$no_update,
+        'force|f' => \$force,
         ) or pod2usage( 2 );
 
@@ -97,25 +94,38 @@
 }
 
+
 #######################################################################################
 # 
-# Finds all exposures for the provided label and generates PSPS FITS data for them
-#
-#######################################################################################
-sub process {
-    my $lastExpId = getLastExpId();
-
-    print "*******************************************************************************\n*\n";
-    print "* Last successfully processed exposure ID was $lastExpId\n";
-
-    # Set up the database
-    my $dbname = 'gpc1';
-    my $dbserver = 'ippdb01';
-    my $dbuser = 'ippuser';
-    my $dbpass = 'ippuser';
+# Connects to a db
+# 
+########################################################################################
+sub connectToDb {
+    my ($dbname,$dbserver,$dbuser,$dbpass) = @_;
+
+
     my $db = DBI->connect("DBI:mysql:database=${dbname};host=${dbserver};" .
             "mysql_socket=" . DB_SOCKET(),
             ${dbuser},${dbpass},
             { RaiseError => 1, AutoCommit => 1}
-            ) or die "Unable to connect to database $DBI::errstr\n";
+            );
+    
+    printf("* Connection to '$dbname' on '$dbserver': %s\n", $db ? "success" : "FAILED ($DBI::errstr)");
+
+    return $db;
+}
+
+
+#######################################################################################
+# 
+# Finds all exposures for the provided label and generates PSPS FITS data for them
+#
+#######################################################################################
+sub process {
+
+    print "*******************************************************************************\n*\n";
+
+    my $gpc1Db = connectToDb("gpc1", "ippdb01", "ippuser", "ippuser") or return 0;
+    my $ippToPspsDb = connectToDb("ippToPsps", "ippdb01", "ipp", "ipp") or return 0;
+    my $lastExpId = getLastExpId($ippToPspsDb);
 
     my $query;
@@ -123,5 +133,5 @@
 
         # query to retrieve all exposures with the provided label
-        $query = $db->prepare(<<SQL);
+        $query = $gpc1Db->prepare(<<SQL);
 
         SELECT DISTINCT rawExp.exp_id, camRun.dist_group
@@ -129,8 +139,10 @@
             WHERE camRun.chip_id = chipRun.chip_id
             AND chipRun.exp_id = rawExp.exp_id
-            AND camRun.label LIKE '%$label%'
+            AND camRun.dist_group LIKE 'ThreePi'
             AND rawExp.exp_id > $lastExpId
             ORDER BY rawExp.exp_id ASC
 SQL
+            #AND rawExp.exp_id > $lastExpId
+            #AND camRun.label LIKE '%$label%'
             #AND rawExp.exp_id > 136561
             #AND camRun.dist_group LIKE 'sas'
@@ -139,5 +151,5 @@
     else {
 
-        $query = $db->prepare(<<SQL);
+        $query = $gpc1Db->prepare(<<SQL);
 
         SELECT DISTINCT rawExp.exp_id, dist_group FROM rawExp, chipRun WHERE chipRun.exp_id = rawExp.exp_id AND rawExp.exp_id = $singleExpId
@@ -148,8 +160,7 @@
     my $batchesPerJob = 1;
     my $anyBatches = 0;
+    my $published = 0;
 
     $query->execute;
-
-    open (LOGFILE, ">> $logFile");
 
     my $jobId = -1;
@@ -159,12 +170,16 @@
         my ($expId, $distGroup) = @row;
 
-        $jobId = getNewJobId();
-
-        print "* Exposure ID = $expId with dist group = '$distGroup' as job ID $jobId\n";
+        if (isExposureAlreadyProcessed($ippToPspsDb, $expId)) {
+                
+                if ($force) {print "* Forcing....\n";} 
+                else {next};
+                }
 
         my $surveyType = getSurveyTypeFromDistGroup($distGroup);
-
         if (!$surveyType) {next;}
-        print "* Using survey type of $surveyType\n";
+
+        $jobId = getNewBatchId($ippToPspsDb, $expId, $surveyType);
+
+# TODO quit here if no sensible job ID
 
         # generate batch and job paths from job and batch IDs
@@ -181,5 +196,5 @@
 
         # run IppToPsps program
-        if (runIppToPsps($db, $expId, $batchOutputPath, $batchType)) {
+        if (runIppToPsps($gpc1Db, $expId, $batchOutputPath, $batchType)) {
 
             # write batch manifest and tar and zip up
@@ -198,13 +213,13 @@
             elsif ($anyBatches) {
 
+                $published = 0;
                 if (writeJobManifest($output, $job, $surveyType)) {
 
-                    if (!$no_publish && !publishToDatastore($output, $job)) { 
-
+                    if (!$no_publish && publishToDatastore($output, $job)) { 
+                        $published = 1;
                     }
                 }
 
-                # write to the log
-                print LOGFILE "$jobId $expId\n";
+                updateDb($ippToPspsDb, $jobId, $expId, 1, $published);
 
                 $batchId = 0;
@@ -214,10 +229,6 @@
 
                 print "* No batches to publish\n";
-                print LOGFILE "$jobId $expId ERROR\n";
             }
         }
-        else {
-            print LOGFILE "$jobId $expId ERROR\n";
-        }
 
         if ($batchType eq "init" ) {print "* Wrote initialisation batch\n"; last;}
@@ -227,6 +238,6 @@
     }
 
-    $db->disconnect();
-    close (LOGFILE);
+    $gpc1Db->disconnect();
+    $ippToPspsDb->disconnect();
 
     return 1;
@@ -239,33 +250,104 @@
 ########################################################################################
 sub getLastExpId {
-
-    my $lastExpId = `tail $logFile -n 1 | awk '{print \$2}'`;
-    chomp($lastExpId);
-    return $lastExpId;
-}
-
-#######################################################################################
-#
-# Gets the current jobId (ini file for now - will be Db eventually TODO) 
+    my ($db) = @_;
+
+    my $query = $db->prepare(<<SQL);
+
+    SELECT exp_id 
+        FROM batches 
+        WHERE processed = 1
+        ORDER BY exp_id 
+        DESC LIMIT 1;
+SQL
+
+    $query->execute;
+
+    my $expId = $query->fetchrow_array();
+
+    print "* Last successfully processed exposure ID was $expId\n";
+
+    return $expId; 
+}
+
+#######################################################################################
+#
+# Updates an existing database record 
 #
 ########################################################################################
-sub getNewJobId {
-
-    my $jobId = -1;
-
-    open (MYFILE, $jobIdFilePath);
-    while (<MYFILE>) {
-        chomp;
-        $jobId = $_;
-    }
-    close (MYFILE);
-
-    $jobId++;
-
-    open (MYFILE, "> $jobIdFilePath");
-    print MYFILE "$jobId";
-    close (MYFILE);
-
-    return $jobId;
+sub updateDb {
+    my ($db, $batchId, $expId, $processed, $published) = @_;
+
+    my $query = $db->prepare(<<SQL);
+
+    UPDATE batches 
+        SET processed = $processed, on_datastore = $published 
+        WHERE batch_id = $batchId 
+        AND exp_id = $expId;
+SQL
+
+    $query->execute; # TODO check response of these
+}
+
+#######################################################################################
+#
+# Checks whether we have successfully processed this exposure 
+#
+########################################################################################
+sub isExposureAlreadyProcessed {
+    my ($db, $expId) = @_;
+
+    my $query = $db->prepare(<<SQL);
+
+SELECT COUNT(*) 
+    FROM batches 
+    WHERE exp_id = $expId 
+    AND processed = 1;
+
+SQL
+
+    $query->execute;
+
+    my $processed = $query->fetchrow_array();
+
+    if ($processed) {print "* Exposure ID '$expId' has already been processed\n";}
+
+    return $processed;
+}
+
+#######################################################################################
+#
+# Generates a new record in the database with a new jobId 
+#
+########################################################################################
+sub getNewBatchId {
+    my ($db, $expId,$surveyType) = @_;
+
+    my $query = $db->prepare(<<SQL);
+
+    SELECT batch_id 
+        FROM batches 
+        ORDER BY batch_id 
+        DESC LIMIT 1;
+SQL
+
+    $query->execute;
+
+    my $batchId = $query->fetchrow_array();
+
+    $batchId++;
+
+    $query = $db->prepare(<<SQL);
+    INSERT INTO batches
+        (batch_id, exp_id, survey_id)
+        VALUES
+        ($batchId, $expId, '$surveyType');
+
+SQL
+
+    $query->execute;
+
+    print "* New batch '$batchId' for exposure ID = $expId and survey = '$surveyType'\n";
+
+    return $batchId;
 }
 
@@ -276,5 +358,5 @@
 #######################################################################################
 sub runIppToPsps {
-    my ($db, $expId, $batchOutputPath, $batchType) = @_;
+    my ($gpc1Db, $expId, $batchOutputPath, $batchType) = @_;
 
     my $success = 0;
@@ -285,8 +367,8 @@
     }
     elsif ($batchType =~ /det/) {
-        $success = produceDetections($db, $expId, $batchOutputPath);
+        $success = produceDetections($gpc1Db, $expId, $batchOutputPath);
     }
     elsif ($batchType =~ /diff/) {
-        $success = produceDiffs($db, $expId, $batchOutputPath);
+        $success = produceDiffs($gpc1Db, $expId, $batchOutputPath);
     }
 
@@ -591,5 +673,5 @@
 #######################################################################################
 sub produceDetections {
-    my ($db,$expId,$outputPath) = @_;
+    my ($gpc1Db,$expId,$outputPath) = @_;
 
     # query to retrieve nebulous key of camera smf file for this exposure
@@ -602,5 +684,6 @@
         "JOIN chipRun USING(chip_id) ".
         "JOIN rawExp USING (exp_id) ".
-        "WHERE rawExp.exp_id = $expId AND camRun.magicked";
+        "WHERE rawExp.exp_id = $expId AND camRun.magicked ".
+        "ORDER BY camRun.cam_id DESC LIMIT 1";  # make sure we get most recently processed
 
     if ($verbose) { print"* $query\n";}
@@ -608,5 +691,5 @@
 
     # execute query and check results
-    my $results = $db->selectall_arrayref( $query );
+    my $results = $gpc1Db->selectall_arrayref( $query );
     my $size = @$results;
     if ($size < 1) {print "* No smf files found for this exposure\n"; return 0;}
@@ -637,5 +720,5 @@
 #######################################################################################
 sub produceDiffs {
-    my ($db,$expId,$outputPath) = @_;
+    my ($gpc1Db,$expId,$outputPath) = @_;
 
     my $query = 
@@ -653,5 +736,5 @@
 
     # execute query and check results
-    my $results = $db->selectall_arrayref( $query );
+    my $results = $gpc1Db->selectall_arrayref( $query );
     my $size = @$results;
     if ($size < 1) {print "* No cmf files found for this exposure\n"; return 0;}