Index: trunk/MagicSoft/Mars/datacenter/macros/findcacofiles.C
===================================================================
--- trunk/MagicSoft/Mars/datacenter/macros/findcacofiles.C	(revision 8478)
+++ trunk/MagicSoft/Mars/datacenter/macros/findcacofiles.C	(revision 8482)
@@ -28,6 +28,6 @@
 // ===============
 //
-// Macro to get from the database the number of the runs, for which no caco
-// file is available.
+// Macro to find caco files for runs which don't have a dedicated caco file.
+// Called by the script filesondisk
 //
 // Sometimes the DAQ aborts a run and starts itself a new one. In this cases
@@ -37,12 +37,12 @@
 // runnumber. To be able to merpp the information into the calibrated data
 // file, the runnumber of the file containing the information has to be found.
-// This is done by a script.
-// findcacofiles.C produces the input for this script. It queries from the
-// database, for which runs no caco file with the same runnumber is available
-// and writes the runnumbers into an txt file.
+// 
+// findcacofiles.C searches in the database for runs which don't have a
+// dedicated caco file, and searches for each of these runs, if one of the 10
+// previous runs has a dedicated caco file. In case one is found, it is
+// inserted into the database.
 //
 // Usage:
-//   .x findcacofiles.C+("date", "logpath")
-// date and logpath are needed only for the output file
+//   .x findcacofiles.C+
 //
 // Make sure, that database and password are corretly set in a resource
@@ -60,5 +60,5 @@
 #include <TSystem.h>
 
-#include <MSQLServer.h>
+#include "MSQLMagic.h"
 #include <TSQLRow.h>
 #include <TSQLResult.h>
@@ -67,9 +67,9 @@
 
 
-int findcacofiles(TString date, TString logpath)
+int findcacofiles()
 {
     TEnv env("sql.rc");
 
-    MSQLServer serv(env);
+    MSQLMagic serv(env);
     if (!serv.IsConnected())
     {
@@ -83,9 +83,13 @@
     cout << endl;
 
-    //get runnumbers from database
-    TString query="SELECT RunProcessStatus.fRunNumber FROM RunProcessStatus ";
-    query+=" LEFT JOIN RunData on RunData.fRunNumber=RunProcessStatus.fRunNumber ";
-    query+=" WHERE IsNull(fCaCoFileFound) and fExcludedFDAKEY=1 ";
-    query+=" and RunProcessStatus.fRunNumber > 10000 and not IsNull(fCCFileAvail)";
+    //get runnumbers and dates from database
+    TString query="SELECT RunProcessStatus.fRunNumber, ";
+    query+=" DATE_FORMAT(ADDDATE(if(fRunStart='0000-00-00 00:00:00', fRunStop, fRunStart), INTERVAL +13 HOUR), '%Y/%m/%d') ";
+    query+=" FROM RunProcessStatus ";
+    query+=" LEFT JOIN RunData ON RunData.fRunNumber=RunProcessStatus.fRunNumber ";
+    query+=" LEFT JOIN Source ON RunData.fSourceKEY=Source.fSourceKEY ";
+    query+=" WHERE IsNull(fCaCoFileFound) AND fExcludedFDAKEY=1 ";
+    query+=" AND RunProcessStatus.fRunNumber > 10000 AND NOT IsNull(fCCFileAvail)";
+    query+=" AND fTest='no'";
 
     TSQLResult *res = serv.Query(query);
@@ -96,17 +100,53 @@
     }
 
-    //create output file
-    TString filename(Form("%s/findcacofiles-%s.txt", logpath.Data(), date.Data()));
-    ofstream fout(filename, ios::app);
-    if (!fout)
-    {
-        cout << "ERROR - Cannot open file " << filename << endl;
-        return 0;
-    }
-
-    //write runnumbers into output file
+    Int_t counter=0;
+    Int_t counter2=0;
     TSQLRow *row=0;
     while ((row = res->Next()))
-        fout << (*row)[0] << endl;
+    {
+        //search nearest previous available CaCoFile
+        Int_t run=atoi((*row)[0]);
+        if (TString((*row)[1]).IsNull())
+        {
+            cout << "For run " << (*row)[0] << " fRunStart and fRunStop are 0000-00-00 00:00:00. No CaCoFile can be determined. " << endl;
+            continue;
+        }
+        cout << "CaCoFile missing for run " << (*row)[0] << " with date " << (*row)[1] << endl;
+
+        query ="SELECT MAX(fCaCoFileFound) FROM RunProcessStatus ";
+        query+=" LEFT JOIN RunData ON RunData.fRunNumber=RunProcessStatus.fRunNumber ";
+        query+=Form("WHERE DATE_FORMAT(ADDDATE(fRunStart, INTERVAL +13 HOUR), '%%Y/%%m/%%d')='%s' ",
+                    (*row)[1]);
+        query+=Form("AND RunData.fRunNumber IN (%d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d)",
+                    run, run-1, run-2, run-3, run-4, run-5,
+                    run-6, run-7, run-8, run-9, run-10);
+
+        TSQLResult *res2 = serv.Query(query);
+        if (!res2)
+        {
+            cout << "Error." << endl;
+            return 0;
+        }
+        TSQLRow *row2=0;
+        row2 = res2->Next();
+        if ((*row2)[0])
+        {
+            cout << "Found CaCoFile at run " << (*row2)[0] << endl;
+            TString vals=Form("fCaCoFileAvail=Now(), fCaCoFileFound=%s", (*row2)[0]);
+            TString where=Form("fRunNumber=%d", run);
+
+            //insert found runnumber
+            if (!serv.Update("RunProcessStatus", vals,where))
+                return 0;
+            counter2+=1;
+        }
+        else
+            cout << " No caco file found for run " << run << endl;
+
+        delete res2;
+        counter+=1;
+    }
+    cout << endl << counter << " missing caco files. " << endl;
+    cout << counter2 << " caco files found and inserted. " << endl;
 
     delete res;