#!/usr/bin/env jython

import os.path
import glob
import sys
import math
import string

import stilts
import re
from java.lang import *
from java.sql import *

from xml.etree.ElementTree import ElementTree, Element, tostring

from batch import Batch
from gpc1db import Gpc1Db
from ipptopspsdb import IppToPspsDb
from scratchdb import ScratchDb
from sqlUtility import sqlUtility

import logging.config

'''
StackBatch class

This class, a sub-class of Batch, processes single stack skycells in the form of IPP cmf files

'''
class StackBatch(Batch):

    '''
    Constructor
    '''
    def __init__(self, 
                 logger, 
                 config,
                 skychunk,
                 gpc1Db,
                 ippToPspsDb,
                 scratchDb,
                 skyID,
                 batchID):

       super(StackBatch, self).__init__(
               logger,
               config,
               skychunk,
               gpc1Db,
               ippToPspsDb,
               scratchDb,
               skyID,
               batchID,
               "ST", 
               None)

       self.stackType = "DEEP_STACK" # XXX NOTE:this is currently hard-wired for 3pi.  Before we run MD, this needs to be updated.
       self.stackTypeID = self.getStackTypeID()
       
       self.filters = ['g', 'r', 'i', 'z', 'y']
       self.stackIDs = {}
       self.imageIDs = {}
       self.fits = {}
       self.headerSet = {}
       self.md5sum = {}
       self.stackEpochs = {}
       self.expTime = {}
       self.zpImage = {}
       self.zpError = {}
       self.magref = {}
       self.nInjected = {}

       # we have two sets of imageIDs here:

       # stackID : this is the id from the gpc1 stack table and (should) match the 
       # cmf header keyword 'IMAGEID', this is also called EXTERN_ID in dvoImagesTable
       
       # imageID : this is the DVO-internal image ID for the stack cmf, and is called
       # IMAGE_ID in dvoImagesTable

       # we just need one valid filter to get the generic metadata
       validFilter = 'none'
       validFilterCount = 0
       # get a list of stackIDs, identify the CMFs, read the headers:
       for filter in self.filters:
           filterName = filter + ".00000"

           self.logger.infoPair ("getting stackID for: ", filterName)
           stackID = gpc1Db.getStackIDFromSkyIDAndFilter(skychunk.dvoLabel, skyID, filterName)
           self.stackIDs[filter] = stackID
           if (stackID > 0):
               validFilterCount=validFilterCount + 1
           if (stackID <= 0): 
               self.stackEpochs[filter] = -999
               continue
               
           validFilter = filter

           # from each stack, get fits name from getStackStageCmf
           self.logger.infoPair ("finding fits file for: ", filterName)
           self.fits[filter] = gpc1Db.getStackStageCmf(skychunk.dvoLabel, stackID)
           if not self.fits[filter]:
               self.logger.errorPair("could not find cmf file name for ", stackID)
               raise

           header = self.fits[filter].getPrimaryHeader()
           if not header:
               self.logger.errorPair("could not read FITS PHU for ", stackID)
               raise

           self.headerSet[filter] = header

           self.logger.infoPair(filter + " stackID = ", stackID)       
           self.logger.info("got here")

           #header['IMAGEID'] = self.getWarpSkyFileIDforThisAddIDforST(stackID)
           #self.logger.infoPair("header['IMAGEID']",header['IMAGEID'])
           #if not (str(stackID) == header['IMAGEID']): 
           #    self.logger.errorPair("inconsistency: header IMAGEID does not match stackID", str(stackID) + " vs " + header['IMAGEID'])
           #    raise
           self.logger.infoPair("getImageIDFromExternID()",stackID)
           imageID = self.scratchDb.getImageIDFromExternID(stackID)
           self.logger.infoPair(filter + " imageID = ", imageID)       
           self.imageIDs[filter] = imageID

           self.stackEpochs[filter] = self.getKeyFloat(header, "%.10f", 'MJD-OBS')

           self.expTime[filter] = self.getKeyFloat(header, "%.8f", 'EXPTIME')
           self.zpImage[filter] = self.getKeyFloat(header, "%.8f", 'ZPT_OBS')
           self.zpError[filter] = self.getKeyFloat(header, "%.8f", 'ZPT_ERR')
           hasdeteff = 1           
           try: deteffHeader = self.fits[filter].findAndReadHeader("SkyChip.deteff", self.config.test)
           except:
               hasdeteff = 0
           #if not deteffHeader:
           #    if self.config.test: self.logger.errorPair("No header found for Skychip.deteff for", filter)
           #    return False
               
           if hasdeteff == 1:          
               self.magref[filter] = self.getKeyFloat(deteffHeader, "%.8f", 'DETEFF.MAGREF')
               self.nInjected[filter] = int(self.getKeyInt(deteffHeader, 0, 'DETEFF.NUM'))

	   ## MEH -- md5sum test
           self.md5sum[filter] = self.fits[filter].getMd5sum()

       # did we find any valid stacks?
       if validFilter == 'none':
           self.logger.errorPair("can't find any valid stacks...?? ","??")
           raise
       # did we find any valid stacks?
       if validFilterCount == 0:
           self.logger.errorPair("can't find any valid stacks...?? ","??")
           raise
       else:
           self.logger.infoPair("number of filters found:",validFilterCount)
    
       validStackID = self.stackIDs[validFilter]

       # using the given stack ID, get the generic information: filter, skycell, etc.
       metadata = self.gpc1Db.getStackStageMeta(validStackID)  

       if not metadata:
           self.logger.errorPair("Could not get stack metadata for", validStackID)
           raise

       validHeader = self.headerSet[validFilter]

       tessName = self.getKeyValue(header, 'TESS_ID')
       self.tessID = self.scratchDb.getTessID(tessName)

       skycellName = self.getKeyValue(header, 'SKYCELL')

       # XXX EAM 20140812 : I am hardwiring the parsing logic for RINGS vs LOCAL
       if tessName == 'RINGS.V3':
           # skycell is, eg "skycell.1133.081"
           #                 0123456789012345
           self.projectionID = skycellName[8:12]
           self.skycellID = skycellName[13:]
       else:
           # skycell is, eg "skycell.081"
           #                 01234567890
           self.projectionID = 0
           self.skycellID = skycellName[8:11]
       if self.skycellID == "":
           self.skycellID = -1

       self.analysisVer = metadata[2];

       # delete PSPS tables
       for filter in self.filters:
           tableName = filter + "StackMeta"
           self.dropTableVerbose(tableName)
           tableName = filter + "StackDetEffMeta"
           self.dropTableVerbose(tableName)

       self.dropTableVerbose("StackMeta")
       self.dropTableVerbose("StackObjectThin")
       self.dropTableVerbose("StackObjectAttributes")
       
       self.dropTableVerbose("StackModelFitExtra")
       self.dropTableVerbose("StackModelFitDeV")
       self.dropTableVerbose("StackModelFitExp")
       self.dropTableVerbose("StackModelFitSer")

       self.dropTableVerbose("StackApFlx")
       self.dropTableVerbose("StackApFlxExGalUnc")
       self.dropTableVerbose("StackApFlxExGalCon6")
       self.dropTableVerbose("StackApFlxExGalCon8")

       self.dropTableVerbose("StackPetrosian")

       self.dropTableVerbose("StackToImage")
       self.dropTableVerbose("StackToFrame")


       # delete IPP tables
       if not self.config.retry:
           self.logger.info("dropping Stack cmf tables")
           for filter in self.filters:
               tableName = filter + "SkyChip_psf"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_xrad"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_xsrc"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_xfit"
               self.scratchDb.dropTable(tableName)
               tableName = filter + "SkyChip_deteff"
               self.scratchDb.dropTable(tableName)

       # create an output filename, which is {stackID}.FITS
       self.logger.infoPair( "self.id", self.id)
       self.logger.infoPair ("self.localOutPath",self.localOutPath)
       self.outputFitsFile = "%08d.FITS" % self.id
       self.outputFitsPath = "%s/%s" % (self.localOutPath, self.outputFitsFile)
       self.logger.infoPair("self.outputFitsFile",self.outputFitsFile)
       
       # set some constants
       self.historyModNum = "0"

       # insert what we know about this stack batch into the stack table
       self.ippToPspsDb.insertStackMeta(self.batchID, "x" , self.stackType)

       # dump stuff to log
       self.logger.infoPair("Stack ID", "%d" % self.id)
       self.logger.infoPair("Stack type", "%s" % self.stackType)
       self.logger.infoPair("SkycellID", "%s" % self.skycellID)
       self.logger.infoPair("ProjectionID", "%s" % self.projectionID)

    ### *********************** main populate functions ************************************

    '''
    Populates the StackMeta table, mainly from header values found in stack cmf FITS header
    '''
    def populateStackMeta(self,filter):

        header = self.headerSet[filter]

        stackID = self.stackIDs[filter]
        filterName = filter + ".00000"

        # make a table
        filterID = self.scratchDb.getFilterID(filter)
        self.logger.infoPair("filterID is ",str(filterID))

        photoCalID = str(self.scratchDb.getPhotoCalID(stackID))

        tablename = filter + "StackMeta"

        sql = "CREATE TABLE " + tablename + " LIKE StackMeta"
        self.scratchDb.execute(sql)

        self.logger.infoPair("Populating table", tablename)

        # fwhm_maj    = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ')
        # fwhm_maj_uq = self.getKeyFloat(header, "%.8f", 'FW_MJ_UQ')

        # XXX hard-wired platescale : 0.25
        pltscale = 0.25
        pltscale2 = pltscale * pltscale

        psfFwhmMajor = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ')
        if (psfFwhmMajor != "NULL"): psfFwhmMajor *= pltscale

        psfFwhmMinor = self.getKeyFloat(header, "%.8f", 'FWHM_MIN')
        if (psfFwhmMinor != "NULL"): psfFwhmMinor *= pltscale

        psfFWHM = "NULL"
        if (psfFwhmMajor != "NULL") and (psfFwhmMinor != "NULL"):
            psfFWHM = 0.5*(psfFwhmMajor + psfFwhmMinor)

        psfmodel_name    = self.getKeyValue(header, 'PSFMODEL')
        psfmodelID  = self.scratchDb.getFitModelID(psfmodel_name)

        ast_cdx = self.getKeyFloat(header, "%.8f",'AST_CDX')
        ast_cdy = self.getKeyFloat(header, "%.8f",'AST_CDY')

        astroscat = "NULL"
        if (ast_cdx != "NULL") and (ast_cdy != "NULL"):
            astroscat = math.sqrt(ast_cdx**2 + ast_cdy**2)
        
        # Convert detectionThreshold to appropriate magnitudes
        magref  = "NULL" if (self.magref[filter]  == "NULL") else float(self.magref[filter])
        expTime = "NULL" if (self.expTime[filter] == "NULL") else float(self.expTime[filter])
        zp      = "NULL" if (self.zpImage[filter] == "NULL") else float(self.zpImage[filter])
        zpErr   = "NULL" if (self.zpError[filter] == "NULL") else float(self.zpError[filter])

        # zp correction should comes from DVO (unless image is not in DVO)
        (zpImagePSF, zpImageAPER) = self.scratchDb.getStackZeroPoint(stackID)

        # zp is the value in the header, zpImage includes the McalPSF value from DVO
        if (zpImagePSF  == "NULL"): zpImagePSF  = zp
        if (zpImageAPER == "NULL"): zpImageAPER = zpImagePSF

        detectionThreshold = "NULL"
        if (magref != "NULL") and (zpImagePSF != "NULL") and (expTime != "NULL"):
            detectionThreshold = magref + zpImagePSF + 2.5 * math.log10(expTime)

        # insert stack metadata into table
        sqlLine = sqlUtility("INSERT INTO " + tablename + " (")

        sqlLine.group("stackImageID",       str(stackID))        
        sqlLine.group("batchID",            str(self.batchID))
        sqlLine.group("surveyID",           str(self.surveyID))
        sqlLine.group("filterID",           str(filterID))           
        sqlLine.group("stackTypeID",        str(self.stackTypeID))
        sqlLine.group("tessID",             str(self.tessID))            
        sqlLine.group("projectionID",       str(self.projectionID))            
        sqlLine.group("skyCellID",          str(self.skycellID))            
        sqlLine.group("photoCalID",         photoCalID) 
        sqlLine.group("analysisVer",        str(self.analysisVer))
	sqlLine.group("md5sum",             str(self.md5sum[filter]))
        sqlLine.group("detectionThreshold", detectionThreshold)
        sqlLine.group("expTime",            expTime)
        sqlLine.group("psfModelID",         psfmodelID)           
        sqlLine.group("psfFWHM",            psfFWHM)
        sqlLine.group("psfWidMajor",        psfFwhmMajor)
        sqlLine.group("psfWidMinor",        psfFwhmMinor)
        sqlLine.group("psfTheta",           self.getKeyFloat(header, "%.8f", 'ANGLE'))
#       sqlLine.group("psfFwhm_mean",       fwhm_maj)      
#       sqlLine.group("psfFwhm_max",        fwhm_maj_uq)
        sqlLine.group("astroScat",          astroscat)
        sqlLine.group("photoZero",          zpImagePSF)
        sqlLine.group("photoZeroAperture",  zpImageAPER)
        sqlLine.group("photoScat",          zpErr)
        sqlLine.group("nAstroRef",          header['NASTRO'])
        sqlLine.group("nPhotoRef",          header['NASTRO'])
        sqlLine.group("ctype1",             header['CTYPE1'])  
        sqlLine.group("ctype2",             header['CTYPE2'])  
        sqlLine.group("crval1",             self.getKeyFloat(header, "%.8f", 'CRVAL1'))   
        sqlLine.group("crval2",             self.getKeyFloat(header, "%.8f", 'CRVAL2'))   
        sqlLine.group("crpix1",             self.getKeyFloat(header, "%.8f", 'CRPIX1'))   
        sqlLine.group("crpix2",             self.getKeyFloat(header, "%.8f", 'CRPIX2'))   
        sqlLine.group("cdelt1",             self.getKeyFloat(header, "%.8e", 'CDELT1'))   
        sqlLine.group("cdelt2",             self.getKeyFloat(header, "%.8e", 'CDELT2'))   
        sqlLine.group("pc001001",           self.getKeyFloat(header, "%.8e", 'PC001001')) 
        sqlLine.group("pc001002",           self.getKeyFloat(header, "%.8e", 'PC001002')) 
        sqlLine.group("pc002001",           self.getKeyFloat(header, "%.8e", 'PC002001')) 
        sqlLine.group("pc002002",           self.getKeyFloat(header, "%.8e", 'PC002002')) 
        sqlLine.group("processingVersion",  str(self.skychunk.processingVersion))

        sql = sqlLine.make(") VALUES ( ", ")")

        try: self.scratchDb.execute(sql)
        except:
            self.logger.error("problem with SQL: " + sql)
            raise
        
        self.scratchDb.updateRecalStats(tablename, str(stackID), str(pltscale)) 

        self.tablesToExport.append(tablename)

    '''
    Populates the StackObjectThin table
    '''
    def populateStackObjectThin(self):

        self.logger.infoPair("Procesing table", "StackObjectThin")

        # insert the per-object data (IDs, random stack ID, processingVersion, etc)
        self.selectDvoObjIDs()

        self.generateRandomIDs()

        self.logger.infoPair("Adding 'row' columns to", "StackObjectThin")
        self.scratchDb.addRowCountColumn("StackObjectThin", "row")
        self.logger.infoPair("Adding unique psps ids to", "StackObjectThin")
        self.generatePspsUniqueIDs()
        self.logger.infoPair("Dropping row column from", "StackObjectThin table")
        self.scratchDb.dropColumn("StackObjectThin", "row")


        # add indexes StackObjectThin
        self.scratchDb.createIndex("StackObjectThin", "objID")

        self.logger.infoPair("inserting filter dependent items into", "StackObjectThin")

        for filter in self.filters:
            self.logger.infoPair("filter", filter)

            stackID = self.stackIDs[filter]
            self.logger.infoPair(filter + "stackID = ", stackID)

            if stackID <= 0:  
                self.logger.infoPair("no stack data for filter" , filter) 
                continue

            imageID = self.imageIDs[filter]
            self.logger.infoPair("selecting imageID", imageID)

            stackEpoch = self.stackEpochs[filter]

            # at this point, the StackObjectThin table includes objID and ippObjID

            # insert detection information for each filter based on the DVO contents
            sqlLine = sqlUtility("UPDATE StackObjectThin as a, " + self.scratchDb.dvoDetectionTable + " as b SET")

            # NOTE: filter:ippDetectID (eg, gippDetectID) are inserted in selectDvoObjIDs()
            sqlLine.group("a."+filter+"stackDetectID", "b.detectID")    # eg, gstackDetectID (from dvo.measure.extID)
            sqlLine.group("a."+filter+"stackImageID",  str(stackID))    # eg, gstackImageID (from cmf.IMAGEID)

            sqlLine.group("a.dvoRegionID",             "b.catID")                       # this is not filter dependent, but is re-written for each filter (if data exists)
            sqlLine.group("a.primaryDetection",        "((b.flags & 0x10000) >> 16)")   # this is not filter dependent, but is re-written for each filter (if data exists)
            sqlLine.group("a.bestDetection",           "((b.objflags & 0x8000) >> 15)") # this is not filter dependent, but is re-written for each filter (if data exists)

            sqlLine.group("a."+filter+"haveData",      "'1'")

            sqlLine.group("a."+filter+"ra",            "b.ra")
            sqlLine.group("a."+filter+"dec",           "b.dec_")

            sqlLine.group("a."+filter+"Epoch",         str(stackEpoch))

            # the Mag values below are set in dvopsps/insert_detections_dvopsps_catalog.c using either zpPSF (PSFMag) or zpAPER (KronMag, ApMag)
            sqlLine.group("a."+filter+"PSFMag",        "b.Mpsf") 
            sqlLine.group("a."+filter+"PSFMagErr",     "b.dMpsf")
            sqlLine.group("a."+filter+"KronMag",       "b.Mkron")
            sqlLine.group("a."+filter+"KronMagErr",    "b.dMkron")
            sqlLine.group("a."+filter+"ApMag",         "b.Map")
            sqlLine.group("a."+filter+"ApMagErr",      "b.dMap")

            # XXX EAM : 20151002 : have we dropped this?
            # sqlLine.group("a."+filter+"telluricExt",   "b.telluricExt")
          
            sqlLine.group("a."+filter+"infoFlag3",     "b.flags")
            sqlLine.group("a."+filter+"infoFlag4",     "b.filtflags")
            sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID AND b.imageID = " + str(imageID))

            try: self.scratchDb.execute(sql)
            except:
                self.logger.errorPair('failed sql: ', sql)
                raise

            sqlLine = sqlUtility("UPDATE StackObjectThin AS a , " + filter + "SkyChip_psf AS b SET")

            sqlLine.group("a."+filter+"raErr",         "b.X_PSF_SIG * b.PLTSCALE")
            sqlLine.group("a."+filter+"decErr",        "b.Y_PSF_SIG * b.PLTSCALE")
            sqlLine.group("a."+filter+"infoFlag",      "b.FLAGS")
            sqlLine.group("a."+filter+"infoFlag2",     "b.FLAGS2")
            sqlLine.group("a."+filter+"nFrames",       "b.N_FRAMES")

            sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID=b.IPP_IDET")

            # index the (f)ippDetectIDs for the next steps?
            # for filter in self.filters:
            #     IDname = filter + "ippDetectID"
            #     self.scratchDb.createIndex("StackObjectThin", IDname)

            try:
                self.scratchDb.execute(sql)
            except:
                self.logger.errorPair('failed sql: ', sql)
                raise

            ## XX # we do not yet set bestdetection, this is a placeholder until we do
            ## XX sql = "UPDATE StackObjectThin set bestDetection = 0"
            ## XX try:
            ## XX     self.scratchDb.execute(sql)
            ## XX except:
            ## XX     self.logger.errorPair('failed sql: ', sql)
            ## XX     raise


        self.tablesToExport.append("StackObjectThin")

    '''
    Populates the StackObjectAttributes table
    '''
    def populateStackObjectAttributes(self):

        tablename = "StackObjectAttributes"

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")
        
        # if we are going to add a key, do it here so it is useful
        self.scratchDb.createIndex(tablename, "objID") 

        self.logger.infoPair("inserting filter dependent cmf items into", tablename)
        for filter in self.filters:
            self.logger.infoPair("filter", filter)

            stackID = self.stackIDs[filter]
            self.logger.infoPair(filter + "stackID = ", stackID)

            if stackID <= 0:  
                self.logger.infoPair("no stack data for filter" , filter) 
                continue

            imageID = self.imageIDs[filter]
            self.logger.infoPair("selecting imageID", imageID)

            header = self.headerSet[filter]
            exptime = self.expTime[filter]
            expTimeString = str(exptime)

            # insert all the detections
            sqlLine = sqlUtility("UPDATE " + tablename + " AS a , " + filter + "SkyChip_psf AS b SET")

            sqlLine.group("a."+filter+"haveData",      "'1'")

            sqlLine.group("a."+filter+"xPos",          "b.X_PSF")
            sqlLine.group("a."+filter+"yPos",          "b.Y_PSF")
            sqlLine.group("a."+filter+"xPosErr",       "b.X_PSF_SIG")
            sqlLine.group("a."+filter+"yPosErr",       "b.Y_PSF_SIG")
            sqlLine.group("a."+filter+"psfMajorFWHM",  "b.PSF_FWHM_MAJ * b.PLTSCALE")
            sqlLine.group("a."+filter+"psfMinorFWHM",  "b.PSF_FWHM_MIN * b.PLTSCALE")
            sqlLine.group("a."+filter+"psfTheta",      "b.PSF_THETA")
            sqlLine.group("a."+filter+"psfCore",       "b.PSF_CORE")
            sqlLine.group("a."+filter+"psfLikelihood", "psfLikelihood(b.EXT_NSIGMA)")
            sqlLine.group("a."+filter+"psfQf",         "b.PSF_QF")
            sqlLine.group("a."+filter+"psfQfPerfect",  "b.PSF_QF_PERFECT")
            sqlLine.group("a."+filter+"psfChiSq",      "b.PSF_CHISQ / b.PSF_NDOF")
            sqlLine.group("a."+filter+"momentXX",      "b.MOMENTS_XX * PLTSCALE * PLTSCALE")
            sqlLine.group("a."+filter+"momentXY",      "b.MOMENTS_XY * PLTSCALE * PLTSCALE")
            sqlLine.group("a."+filter+"momentYY",      "b.MOMENTS_YY * PLTSCALE * PLTSCALE")
            sqlLine.group("a."+filter+"momentR1",      "b.MOMENTS_R1 * b.PLTSCALE")
            sqlLine.group("a."+filter+"momentRH",      "b.MOMENTS_RH * sqrt(b.PLTSCALE)")
            sqlLine.group("a."+filter+"PSFFlux",       "b.PSF_INST_FLUX / " + expTimeString)
            sqlLine.group("a."+filter+"PSFFluxErr",    "b.PSF_INST_FLUX_SIG / " + expTimeString)
            sqlLine.group("a."+filter+"ApFlux",        "b.AP_FLUX / " + expTimeString)
            sqlLine.group("a."+filter+"ApFluxErr",     "b.AP_FLUX_SIG / " + expTimeString)
            sqlLine.group("a."+filter+"KronFlux",      "b.KRON_FLUX / " + expTimeString)
            sqlLine.group("a."+filter+"KronFluxErr",   "b.KRON_FLUX_ERR / " + expTimeString)
            sqlLine.group("a."+filter+"ApFillFac",     "b.AP_NPIX / (3.14159265359 * b.AP_MAG_RADIUS * b.AP_MAG_RADIUS)")
            sqlLine.group("a."+filter+"ApRadius",      "b.AP_MAG_RADIUS*b.PLTSCALE")
            sqlLine.group("a."+filter+"KronRad",       "b.MOMENTS_R1 * 2.5 * b.PLTSCALE")
            sqlLine.group("a."+filter+"PlateScale",    "b.PLTSCALE")
            sqlLine.group("a."+filter+"ExtNSigma",     "b.EXT_NSIGMA")
            sqlLine.group("a."+filter+"sky",           "b.SKY/b.PLTSCALE/b.PLTSCALE / " + expTimeString)
            sqlLine.group("a."+filter+"skyErr",        "b.SKY_SIGMA/b.PLTSCALE/b.PLTSCALE/ " + expTimeString)

            sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET")

            try:
                self.scratchDb.execute(sql)
            except:
                self.logger.errorPair('failed sql', sql)
                raise

            # insert detection information for each filter based on the DVO contents
            sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
            sqlLine.group("a."+filter+"expTime",       "b.expTime")
            sqlLine.group("a."+filter+"zp",            "b.zpPSF")
            sqlLine.group("a."+filter+"zpAPER",        "b.zpAPER")
            sqlLine.group("a."+filter+"PSFFlux",       "a."+filter+"PSFFlux     * b.zpFactorPSF")
            sqlLine.group("a."+filter+"PSFFluxErr",    "a."+filter+"PSFFluxErr  * b.zpFactorPSF")
            sqlLine.group("a."+filter+"ApFlux",        "a."+filter+"ApFlux      * b.zpFactorAPER")
            sqlLine.group("a."+filter+"ApFluxErr",     "a."+filter+"ApFluxErr   * b.zpFactorAPER")
            sqlLine.group("a."+filter+"KronFlux",      "a."+filter+"KronFlux    * b.zpFactorAPER")
            sqlLine.group("a."+filter+"KronFluxErr",   "a."+filter+"KronFluxErr * b.zpFactorAPER")

            sqlLine.group("a."+filter+"sky",           "a."+filter+"sky         * b.zpFactorAPER")
            sqlLine.group("a."+filter+"skyErr",        "a."+filter+"skyErr      * b.zpFactorAPER")

            # where should these go?
            # sqlLine.group("a."+filter+"airmass",       "b.airmass")

            # match to the detections from the specific image
            sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID AND b.imageID = " + str(imageID))

            try:
                self.scratchDb.execute(sql)
            except:
                self.logger.errorPair('failed sql',sql)
                raise

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Populates the StackModelFitExtra table
    '''
    def populateStackModelFitExtra(self):
        tablename = "StackModelFitExtra"

        self.logger.infoPair("Procesing table", "StackModelFitExtra")
        self.insertDvoIDs("StackModelFitExtra", "StackObjectThin")
        self.scratchDb.createIndex(tablename, "objID") 

        # if we are going to add a key, do it here so it is useful
        # self.scratchDb.createIndex(tablename, "objID") 

        # objid
        # uniquepsps id
        # ippobjid
        # randomstackobjid
        # primarydetection
        # bestdetection

        #g / r / i /z / y

        self.logger.infoPair("inserting filter dependent cmf items into", tablename)
        for filter in self.filters:
            self.logger.infoPair("filter", filter)

            stackID = self.stackIDs[filter]
            self.logger.infoPair(filter + "stackID = ", stackID)

            if stackID <= 0:  
                self.logger.infoPair("no stack data for filter" , filter) 
                continue

            cmfTable = filter + "SkyChip_xsrc"
            if not cmfTable in self.tablesLoaded:
                self.logger.infoPair("no xsrc data for filter" , filter) 
                self.logger.infoPair("(did not find table in tablesLoaded)", cmfTable) 
                for name in self.tablesLoaded:
                    print "-- table: " + name
                #os._exit(3)
                continue

            # insert all the detections
            sqlLine = sqlUtility("UPDATE " + tablename + " AS a , " + cmfTable + " AS b SET")

            sqlLine.group("a."+filter+"haveData",       "'1'")
            sqlLine.group("a."+filter+"S2",             "b.G_S2")
            sqlLine.group("a."+filter+"logRT",          "b.G_RT")
            sqlLine.group("a."+filter+"logRA",          "b.G_RA")
            sqlLine.group("a."+filter+"bumpy",          "b.G_BUMPY")
            sqlLine.group("a."+filter+"halfLightRad",   "b.HALF_LIGHT_RADIUS")

            sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET")
            self.scratchDb.execute(sql)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

        # gippdetectic
        # gstackmeta
        # gS2
        # glogRT
        # glogRA
        # glogC
        # glogA
        # gbumpy
        # ghalfLightRad

        # currently, none of the fields defined by the table are generated
        
    '''
    Populates the StackModelFitExp table
    '''
    def populateStackModelFit(self, model, ippModelType):

        tablename = "StackModelFit" + model

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        # self.scratchDb.createIndex(table, "objID") 

        for filter in self.filters:
            self.populateStackModelFitFilter(model, ippModelType, filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Updates parameters for a particular model in the StackModelFit table
    '''
    def populateStackModelFitFilter(self, model, ippModelType, filter):

        tablename = "StackModelFit" + model

        if self.stackIDs[filter] <= 0:
            self.logger.infoPair("no stack data for filter" , filter) 
            return True

        cmfTable = filter + "SkyChip_xfit"
        if not cmfTable in self.tablesLoaded:
            self.logger.infoPair("no xfit data for filter" , filter) 
            self.logger.infoPair("(did not find table in tablesLoaded)", cmfTable) 
            for name in self.tablesLoaded:
                print "-- table: " + name
            #os._exit(3)
            return True

        if self.scratchDb.getRowCount(cmfTable) <=0 :
            self.logger.infoPair("no extended source information, trying to skip",filter)
            return True

        header  = self.headerSet[filter]

        imageID = self.imageIDs[filter]
        self.logger.infoPair("selecting imageID", imageID)

        # NOTE: for model magnitudes, we use zpPSF not zpAPER since they are PSF-matched
        # (maybe that is the wrong solution?)

        # model calibrated magnitude = instrumental magnitude + 2.5*log10(exptime) + ZP (added below from dvoDetectionTable)
        magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME'])))

        # XXX hard-wired platescale : 0.25 (could get this from header, but stacks have constant plate scale
        pltscale = 0.25

        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET")

        sqlLine.group("a."+filter+"haveData",              "'1'")

        # model calibrated magnitude = instrumental magnitude + 2.5*log10(exptime) + ZP (added below from dvoDetectionTable)
        sqlLine.group("a." + filter + model + "Mag",       "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime)
        sqlLine.group("a." + filter + model + "MagErr",    "b.EXT_INST_MAG_SIG")
                      
        sqlLine.group("a." + filter + model + "Radius",    "b.EXT_WIDTH_MAJ     * " + str(pltscale))
        sqlLine.group("a." + filter + model + "RadiusErr", "b.EXT_WIDTH_MAJ_ERR * " + str(pltscale))

        sqlLine.group("a." + filter + model + "Ab",        "b.EXT_WIDTH_MIN/b.EXT_WIDTH_MAJ")
        sqlLine.group("a." + filter + model + "AbErr",     "sqrt(b.EXT_WIDTH_MIN_ERR * b.EXT_WIDTH_MIN_ERR + pow(b.EXT_WIDTH_MAJ_ERR * b.EXT_WIDTH_MIN / b.EXT_WIDTH_MAJ, 2.0)) / b.EXT_WIDTH_MAJ")

        sqlLine.group("a." + filter + model + "Phi",       "DEGREES(b.EXT_THETA)")
        sqlLine.group("a." + filter + model + "PhiErr",    "DEGREES(b.EXT_THETA_ERR)")

        sqlLine.group("a." + filter + model + "Chisq",     "b.EXT_CHISQ / b.EXT_NDOF")

        # I have raExp = X_EXT

        # EAM 20150924: PV3 output cmfs were updated to include RA_EXT, DEC_EXT
        sqlLine.group("a." + filter + model + "Ra",        "b.RA_EXT")
        sqlLine.group("a." + filter + model + "Dec",       "b.DEC_EXT")
        sqlLine.group("a." + filter + model + "RaErr",     "b.X_EXT_SIG") # apply plate-scale below
        sqlLine.group("a." + filter + model + "DecErr",    "b.Y_EXT_SIG") # apply plate-scale below

        # sersic fit has an extra parameter
        if (ippModelType == "PS_MODEL_SERSIC"):
            testSQL = 'show columns FROM rSkyChip_xfit LIKE "EXT_PAR_07"'
            have_ext_par_07 = 1
            try:
                rs = self.scratchDb.execute(sql)
            except:
                have_ext_par_07 = 0
            if have_ext_par_07 == 1:
                sqlLine.group("a." + filter + model + "Nu",    "1 / (2.0 * b.EXT_PAR_07)")
                sqlLine.group("a." + filter + model + "NuErr", "'0.1'") # this is not measured at the moment

            ### I think, if ippModelType = PS_MODEL_SERSIC, and EXT_PAR_07 does not exist, then Nu, Nerr = null
            

        # missing these two fields?
        # sqlLine.group("a." + filter + model + "CF",         "b.XX")
        # sqlLine.group("a." + filter + model + "Likelihood", "b.XX")

        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND b.MODEL_TYPE = '" + ippModelType + "'")

        try: self.scratchDb.execute(sql)
        except:
            self.logger.error("problem with SQL: " + sql)
            raise

        # insert detection information for each filter based on the
        # DVO contents.  Since I already have extracted
        # StackObjectAttributes, this is probably faster using that table:
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")

        sqlLine.group("a." + filter + model + "Mag",       "a." + filter + model + "Mag       + b.zpPSF")

        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID AND b.imageID = " + str(imageID))

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql',sql)
            raise

        ## EAM 20150924: this section is now longer needed as the cmf contains RA,DEC
        ## # we have (modulo signs):
        ## # gRaExp  = gra  + (X_EXT - gxPos)*gPlateScale / cos(gdec)
        ## # gDecExp = gdec + (Y_EXT - gyPos)*gPlateScale
        ## 
        ## rawS  = "c." + filter + "PlateScale"
        ## 
        ## extR  = "a." + filter + model + "Ra"
        ## rawR  = "b." + filter + "ra"
        ## rawX  = "c." + filter + "xPos"
        ## 
        ## extD  = "a." + filter + model + "Dec"
        ## rawD  = "b." + filter + "dec"
        ## rawY  = "c." + filter + "yPos"
        ## 
        ## # dRA / dX = -PlateScale
        ## # b.+filter+ra  - (a.+filter+model+Ra - c.+filter+xPos)*c.+filter+PlateScale / cos(+filter+dec)
        ## # extR = rawR - (extR - rawX)*rawS / 3600.0 / cos(rawD)
        ## 
        ## # dDEC / dY = +PlateScale
        ## # b.+filter+dec + (a.+filter+model+Dec - c.+filter+xPos)*c.+filter+PlateScale / cos(+filter+dec)
        ## # extD = rawD + (extD - rawY)*rawS / 3600.0
        ## 
        ## # insert detection information for each filter based on the DVO contents
        ## sqlLine = sqlUtility("UPDATE " + tablename + " AS a, StackObjectThin as b, StackObjectAttributes as c SET ")
        ## sqlLine.group(extR, rawR + " - (" + extR + " - " + rawX + ")*" + rawS + " / 3600.0 / cos(radians(" + rawD + "))")
        ## sqlLine.group(extD, rawD + " + (" + extD + " - " + rawY + ")*" + rawS + " / 3600.0")
        ## field = "a." + filter + model + "RaErr"
        ## sqlLine.group(field, field + "*" + rawS)
        ## field = "a." + filter + model + "DecErr"
        ## sqlLine.group(field, field + "*" + rawS)
        ## sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a.objID = c.objID")
        ## 
        ## try:
        ##     self.scratchDb.execute(sql)
        ## except:
        ##     self.logger.errorPair('failed sql',sql)
        ##     raise

        # EAM 20150924: update the RA and DEC Errors to be in arcsec, not pixels
        # dRA (arcesc) = dRA (pixels) * PlateScale
        # dDEC(arcsec) = dDEC(pixels) * PlateScale

        rawS  = "c." + filter + "PlateScale"
        extR  = "a." + filter + model + "RaErr"
        extD  = "a." + filter + model + "DecErr"

        # insert detection information for each filter based on the DVO contents
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, StackObjectAttributes as c SET ")
        sqlLine.group(extR, extR + "*" + rawS)
        sqlLine.group(extD, extD + "*" + rawS)
        sql = sqlLine.makeEquals("WHERE a.objID = c.objID")

        try:
            self.scratchDb.execute(sql)
        except:
            self.logger.errorPair('failed sql',sql)
            raise

    '''
    Populates the StackPetrosian table, mainly from dictionary values found in IPP FITS header
    '''
    def populateStackPetrosian(self):

        tablename = "StackPetrosian"

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        # self.scratchDb.createIndex(tablename, "objID") 

        for filter in self.filters:
            self.populateStackPetrosianFilter(filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Populates the StackPetrosian table, mainly from dictionary values found in IPP FITS header
    '''
    def populateStackPetrosianFilter(self, filter):

        tablename = "StackPetrosian"

        if self.stackIDs[filter] <= 0:
            self.logger.infoPair("no stack data for filter" , filter) 
            return True

        cmfTable = filter + "SkyChip_xsrc"
        if not cmfTable in self.tablesLoaded:
            self.logger.infoPair("no xsrc data for filter" , filter) 
            self.logger.infoPair("(did not find table in tablesLoaded)", cmfTable) 
            for name in self.tablesLoaded:
                print "-- table: " + name
            #os._exit(3)
            return True

        if self.scratchDb.getRowCount(cmfTable) <=0 :
            self.logger.infoPair("no extended source information, trying to skip", filter)
            return True

        header  = self.headerSet[filter]

        imageID = self.imageIDs[filter]
        self.logger.infoPair("selecting imageID", imageID)

        # PETRO_MAG is instrumental + 2.5log(exptime) + FPA.ZP
        # we want to apply the DVO zero point, so remove FPA.ZP:

        hdrZP   = str(self.zpImage[filter])
        # magtime = "%.5f" % (2.5*math.log10(self.expTime[filter]))

        # XXX hard-wired platescale : 0.25 (could get this from header, but stacks have constant plate scale
        pltscale = 0.25

        self.logger.infoPair("Adding", "petrosians for extended sources")
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET")

        sqlLine.group("a."+filter+"haveData",         "'1'")

        sqlLine.group("a." + filter + "petMag",       "b.PETRO_MAG - " + hdrZP) # note that this is **subtracting** the hdrZP (zpAPER is added in below)
        sqlLine.group("a." + filter + "petMagErr",    "1.0 / b.PETRO_MAG_ERR")  # still inverted? (yes)

        sqlLine.group("a." + filter + "petRadius",    "b.PETRO_RADIUS        * " + str(pltscale))
        sqlLine.group("a." + filter + "petRadiusErr", "b.PETRO_RADIUS_ERR    * " + str(pltscale))
        sqlLine.group("a." + filter + "petR50",       "b.PETRO_RADIUS_50     * " + str(pltscale))
        sqlLine.group("a." + filter + "petR50Err",    "b.PETRO_RADIUS_50_ERR * " + str(pltscale))
        sqlLine.group("a." + filter + "petR90",       "b.PETRO_RADIUS_90     * " + str(pltscale))
        sqlLine.group("a." + filter + "petR90Err",    "b.PETRO_RADIUS_90_ERR * " + str(pltscale))
        sqlLine.group("a." + filter + "petCf",        "b.PETRO_FILL")

        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET ")
        self.scratchDb.execute(sql)

        # modify petMag to apply the zero point
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
        sqlLine.group("a." + filter + "petMag",       "a." + filter + "petMag    + b.zpAPER")
        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID AND b.imageID = " + str(imageID))

        self.scratchDb.execute(sql)

        # modify petRadiusErr if it is not set correctlyu
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a SET")
        sqlLine.group("a." + filter + "petRadiusErr", "a." + filter + "petRadius * 0.1")
        sql = sqlLine.makeEquals("WHERE a." + filter + "petRadiusErr IS NULL")

        self.scratchDb.execute(sql)

        # modify petRadius/petRadiusErr if they have the very wrong values.
        # Be conservative and assume all values smaller than -1e10 are very wrong values.
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a SET")
        sqlLine.group("a." + filter + "petRadius", "-999")
        sqlLine.group("a." + filter + "petRadiusErr", "-999")
        sql = sqlLine.makeEquals("WHERE a." + filter + "petRadius <= -1e+10")
        
        self.scratchDb.execute(sql)

    '''
    Populates the StackApFlx table
    '''
    def populateStackApFlx(self):

        tablename = "StackApFlx"

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        self.scratchDb.createIndex(tablename, "objID") 

        # fluxes are set to Jy
        for filter in self.filters:
            self.populateStackApFlxFilter(tablename, "Unc", filter)
            self.populateStackApFlxFilter(tablename, "Con6", filter)
            self.populateStackApFlxFilter(tablename, "Con8", filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Populates the StackApFlxEG table
    '''
    def populateStackApFlxExGal(self, version):

        tablename = "StackApFlxExGal" + version

        self.logger.infoPair("Procesing table", tablename)
        self.insertDvoIDs(tablename, "StackObjectThin")

        # if we are going to add a key, do it here so it is useful
        self.scratchDb.createIndex(tablename, "objID") 

        # fluxes are set to Jy
        for filter in self.filters:
            self.populateStackApFlxFilter(tablename, version, filter)

        self.deleteRowsWithNoStackData(tablename)
        self.tablesToExport.append(tablename)

    '''
    Updates aperture fluxes for StackApFlx table, fluxes are multiplied by zpFactor to be in Jy
    '''
    def populateStackApFlxFilter(self, tablename, version, filter):

        prefix = "none"

        # order of the 3 convolutions is fixed (raw, C1, C2) = (1, 2, 3)
        if version == "Unc":   
            psfCondition = "(b.table_index % 3 = 1)"
            prefix = ""
        if version == "Con6": 
            psfCondition = "(b.table_index % 3 = 2)"
            prefix = "c6"
        if version == "Con8": 
            psfCondition = "(b.table_index % 3 = 0)" 
            prefix = "c8"

        if prefix == "none":
            self.logger.errorPair("invalid stack ap flux version", version)
            raise

        if self.stackIDs[filter] <= 0:
            self.logger.infoPair("no stack data for filter" , filter) 
            return True

        imageID = self.imageIDs[filter]
        self.logger.infoPair("selecting imageID", imageID)

        minRadius = 1
        maxRadius = 9
        if tablename == 'StackApFlx':
            minRadius = 3
            maxRadius = 5

        # XXX : by setting these to -999 here, the values are then
        # modified downstream to apply exposure time and zero point
        # factors (or platescale terms).  this means they will not
        # have the correct -999 values in the end.

        # properly -999 these to start with.  the default should take
        # care of this, but does not?

        # chopping this out to see if it still works

        #sqlLine = sqlUtility("UPDATE " + tablename + " SET")
        #for radius in [1, 2, 3, 4, 5, 6, 7, 8, 9]:
        #    if radius < minRadius: continue
        #    if radius > maxRadius: continue
        #    pspsRadius = radius + 2
        #    sqlLine.group(filter + prefix + "flxR" + str(pspsRadius),          "-999")
        #    sqlLine.group(filter + prefix + "flxR" + str(pspsRadius) + "Err",  "-999")
        #    sqlLine.group(filter + prefix + "flxR" + str(pspsRadius) + "Std",  "-999")
        #    sqlLine.group(filter + prefix + "flxR" + str(pspsRadius) + "Fill", "-999")#

        #sql = sqlLine.makeEquals("")
         
        #        try: self.scratchDb.execute(sql)
        #        except:
        #            self.logger.errorPair('failed sql',sql)
        #            raise

        # set the flux values from the cmf file

        # skip this table if it was not loaded from the cmf
        cmfTable = filter + "SkyChip_xrad"
        if not cmfTable in self.tablesLoaded:
            self.logger.infoPair("no xrad data for filter" , filter) 
            return True

        # we have variable numbers of these columns. find out which are in use
        sql = "DESCRIBE " + cmfTable + " 'aper_fill_%'"
        rs = self.scratchDb.executeQuery(sql)
        rs.first()
        columns = []
        column = rs.getString(1)
        columns.append(column)
        while (rs.next()):
            column = rs.getString(1)
            columns.append(column)
        rs.close()

        # use regex to select the aperture number (APER_FILL_N)
        numbers = []
        for column in columns: 
            number = re.sub("APER_FILL_","",column)
            if (int(number) >= minRadius and int(number) <= maxRadius):
                numbers.append(number)
                self.logger.infoPair("Aperture Numbers",number)

        # get the exposure time for aperture fluxes
        header = self.headerSet[filter]
        exptimeString = str(self.getKeyFloat(header, "%.5f", "EXPTIME"))

        ## this is a serious problem: not sure we have to handle it.
        if (exptimeString == "NULL"):
            print "**** serious error: EXPTIME is NULL (missing or NAN)"
            os._exit(4)

        # generate the sql to do the necessary ops on the columns    
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ")

        sqlLine.group("a."+filter+"haveData", "'1'")

        for number in numbers:
            pspsNum  = str(int(number) + 2)
            field = filter + prefix + "flxR" + pspsNum
            sqlLine.group(field,          "b.APER_FLUX_"       + number + " / " + exptimeString)
            sqlLine.group(field + "Err",  "b.APER_FLUX_ERR_"   + number + " / " + exptimeString)
            sqlLine.group(field + "Std",  "b.APER_FLUX_STDEV_" + number + " / " + exptimeString)
            sqlLine.group(field + "Fill", "b.APER_FILL_"       + number)
            
        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND  " + psfCondition)
        
        try: self.scratchDb.execute(sql)
        except:
            self.logger.infoPair("failed sql", sql)
            raise

        # instrumental flux, AB mags, Jy:
        # flux_inst = cts / sec
        # mag_AB = -2.5*log(flux_inst) + ZP [with appropriate definition of zero point]
        # mag_AB = -2.5*log(flux_cgs) - 48.6 [by construction with ~Vega flux in V-band]
        # flux_cgs = ten(-0.4*(mag_AB + 48.6))
        # flux_cgs = ten(-0.4*((-2.5*log(flux_inst) + ZP) + 48.6))
        # flux_cgs = flux_inst * ten(-0.4*(ZP + 48.6))
        # flux_Jy  = flux_cgs * 10^23
        # flux_Jy  = flux_inst * ten(-0.4*(ZP + 48.6) + 23.0)  [23.0 - 0.4*48.6 = 3.56]
        # flux_Jy  = flux_inst * ten(-0.4*ZP + 3.56)
        # flux_Jy  = flux_inst * ten(-0.4*ZP) * 3630.78

        # modify flux to apply the zero point
        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
        for number in numbers:
            pspsNum  = str(int(number) + 2)
            field = filter + prefix + "flxR" + pspsNum
            sqlLine.group(field,               field         + " * b.zpFactorAPER")
            sqlLine.group(field + "Err",       field + "err" + " * b.zpFactorAPER")
            sqlLine.group(field + "Std",       field + "Std" + " * b.zpFactorAPER")

        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID AND b.imageID = " + str(imageID))

        try: self.scratchDb.execute(sql)
        except:
            self.logger.infoPair("failed sql",sql)
            raise

    '''
    Populates the StackToImage table
    '''
    def populateStackToImage(self):

        self.logger.infoPair("Procesing table", "StackToImage")

        for filter in self.filters:
            
            stackImageID = self.stackIDs[filter]
            if stackImageID > 0:
    
                imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackImageID)

                for imageID in imageIDs:

                    sql = "INSERT INTO StackToImage (stackImageID, imageID) \
                   VALUES (\
                   " + str(stackImageID) + ", " + imageID + ")"
                    self.scratchDb.execute(sql)

                    # now update StackMeta with correct number of inputs
                    sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackImageID = "+str(stackImageID)+")"
                    self.scratchDb.execute(sql)

        self.tablesToExport.append("StackToImage")

    '''
    Populates the StackToFrame table
    '''
    def populateStackToFrame(self):

        self.logger.infoPair("Procesing table", "StackToFrame")

        for filter in self.filters:
            
            stackImageID = self.stackIDs[filter]
            if stackImageID < 1: continue
    
            header = self.headerSet[filter]

            nInputs = self.getKeyInt(header, 0, 'NINPUTS')

            self.logger.debugPair("found NINPUTS", nInputs)

            for input in range(int(nInputs)):
                
                self.logger.debugPair("trying input", str(input))

                field = "INP_%04d" % input
                inputName = self.getKeyValue(header, field)
                self.logger.infoPair("splitting",inputName)
                # split inputName of form o5745g0406o.356782.wrp.1199763.skycell.1315.090.fits
                # to get the expID (second block)
#                worsds 
                words2=re.sub(":",".",inputName)
                words = words2.split('.')
                #frameID = words[1]
                
                ogo = -1

                # search the words for the o....g....o part
                for j in range(len(words)):
                    match1 = re.search('^\d\d\d\dg\d\d\d\d\o', words[j]) #remove leading  o: then ones with borked no os and cs will work now 
                    match2 = re.search('\o\d\d\d\dg\d\d\d\d\o', words[j]) #remove leading  o: then ones with borked no os and cs will work now 
                    match3 = re.search('\c\d\d\d\dg\d\d\d\d\o', words[j]) #remove leading  o: then ones with borked no os and cs will work now 
                    if (match1 != None):
                        ogo=j
                        ogoname = 'o'+words[ogo]
                        break
                    if (match2 != None):
                        ogo=j
                        ogoname = words[ogo]
                        break
                    if (match3 != None):
                        ogo=j
                        ogoname = words[ogo]
                        break
                    # did we find it? if so, are there are least 2 more elements past the ogo?
                    # this is to make sure that the exp_id is not truncated

                if ogo > -1:
                    if (ogo +2) < len(words): #this was the mistake before
                        # there is a o....g....o element, and the next 2 exist
                        frameID = words[ogo+1]
                        
                    else:
                        #ogoname = "o"+words[ogo]
                        try: frameID = self.gpc1Db.getExpIdFromExpName(ogoname)
                        except:
                            self.logger.error("cant get expid")
                            raise
                        
#                        frameID = str(rs.getInt(1))
        
#                        self.logger.infoPair("unable to find exp_id from", inputName)
#                        raise
                        # and then raise an error and stop the program. I expect this to mostly work fine
        # for the extra long label (?) - can probably get it from just the o g o (if exp_id
        # is truncated, otherwise, there will have to be some extra cleverness with 
        # queries and matching
                self.logger.infoPair("found exp_id!", frameID)

                field = "SCL_%04d" % input
                scaleFactor = self.getKeyFloat(header, "%9.5f", field)
                
                field = "ZPT_%04d" % input
                zeroPoint = self.getKeyFloat(header, "%9.5f", field)
                
                field = "EXP_%04d" % input
                expTime = self.getKeyFloat(header, "%6.3f", field) # exptime of INPUT image, not stack
                
                field = "AIR_%04d" % input
                airMass = self.getKeyFloat(header, "%6.3f", field)
                
                self.logger.debugPair("found header fields for", str(input))

                sql = "INSERT INTO StackToFrame "
                sql += " (stackImageID, frameID, scaleFactor, zp, expTime, airMass) VALUES ( "
                sql += str(stackImageID) + ", "
                sql += str(frameID) + ", "
                sql += str(scaleFactor) + ", "
                sql += str(zeroPoint) + ", "
                sql += str(expTime) + ", "
                sql += str(airMass) + ")"
                self.scratchDb.execute(sql)

        self.tablesToExport.append("StackToFrame")
        return True

    ''' 
    Populate StackDetEffMeta
    '''
    def populateStackDetEffMeta(self):
        self.logger.infoPair("Processing table", "StackDetEffMeta")

        for filter in self.filters:
            self.logger.infoPair("filter", filter)

            stackImageID = self.stackIDs[filter]
            if stackImageID <= 0:
                self.logger.infoPair("no stack data for filter", filter)
                continue

            cmfTable = filter + "SkyChip_deteff"
            if not cmfTable in self.tablesLoaded:
                self.logger.infoPair("no deteff data for filter", filter)
                self.logger.infoPair("(did not find table in tablesLoaded)", cmfTable)
                for name in self.tablesLoaded:
                    print "-- table: " + name
                os._exit(3)
                continue


            ippTableName = filter + "StackDetEffMeta"
            sql = "CREATE TABLE " + ippTableName + " LIKE StackDetEffMeta"
            try: self.scratchDb.execute(sql)
            except: pass

            # Convert detectionThreshold to appropriate magnitudes
            magref  = self.magref[filter]
            expTime = self.expTime[filter]
            zp      = self.zpImage[filter]
            zpErr   = self.zpError[filter]

            # zp correction should comes from DVO (unless image is not in DVO)
            zpImage = self.scratchDb.getImageZeroPoint(stackImageID)
            if zpImage < 0.0:
                zpImage = zp

            detectionThreshold = magref + zpImage + 2.5 * math.log10(expTime)
            nInjected = self.nInjected[filter]

            sqlLine = sqlUtility("INSERT INTO " + ippTableName + "(")
            sqlLine.group("stackImageID", str(stackImageID))
            sqlLine.group("magref",       detectionThreshold)
            sqlLine.group("nInjected",    nInjected)
            sql = sqlLine.make(") VALUES ( ", ")")

            try: self.scratchDb.execute(sql)
            except: 
                self.logger.errorPair('failed sql: ', sql)
                raise

            for i in xrange(1, 14):
                stringint = str(i)
                if i < 10:
                    stringint = '0' + str(i)

                sql = "UPDATE " + ippTableName + " , " + cmfTable + \
                    " SET offset"+stringint + " = OFFSET, counts" \
                    +stringint + " = COUNTS, diffMean" \
                    +stringint + " = DIFF_MEAN, diffStdev" \
                    +stringint + " = DIFF_STDEV, errMean" \
                    +stringint + " = ERR_MEAN WHERE table_index = " + str(i)
                
                try: self.scratchDb.execute(sql)
                except:
                    self.logger.errorPair('filed sql: ', sql)
                    raise
            # end for
                # Append this like this?
            self.tablesToExport.append(ippTableName)
        # end filters


        return True
                


    ### *********************** Utility Functions ************************************

    '''
    Drop a table and report the drop
    '''
    def dropTableVerbose(self, table):
        self.logger.infoPair("dropping table:",table)
        self.scratchDb.dropTable(table)

    '''
    get stackTypeID corresponding to our stackType
    '''
    def getStackTypeID(self):

        sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'"
        rs = self.scratchDb.executeQuery(sql)
        if not rs.next():
            self.logger.errorPair("cannot get stack type ID from database for", self.stackType)
            raise
        
        ID = rs.getInt(1)
        return ID

    def deleteRowsWithNoStackData(self, tableName):

        self.logger.infoPair("Deleting rows with no stack data from", tableName)
        sql = "DELETE FROM " + tableName + " WHERE "

        for i in range(len(self.filters)):
            filter = self.filters[i]
            sql += filter + "haveData is NULL"

            if (i < len(self.filters) - 1):
                sql += " AND "

        self.scratchDb.execute(sql)

        for filter in self.filters:
            column = filter + "haveData"
            self.scratchDb.dropColumn(tableName, column)

    '''
    Applies indexes to the PSPS tables
    '''
    def alterPspsTables(self):

        self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)")

        return True

    '''
    Applies indexes to the IPP tables
    '''
    def indexIppTables(self):

        for filter in self.filters:
            if self.stackIDs[filter] <= 0:  
                self.logger.infoPair("no files for filter" , filter) 
                continue

            self.logger.infoPair("Creating indexes on", "IPP tables")

            cmfTable = filter + "SkyChip_psf"
            if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable, "IPP_IDET")

            cmfTable = filter + "SkyChip_xfit"
            if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable, "IPP_IDET")

            cmfTable = filter + "SkyChip_xrad"
            if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable, "IPP_IDET")

            cmfTable = filter + "SkyChip_xsrc"
            if cmfTable in self.tablesLoaded: self.scratchDb.createIndex(cmfTable, "IPP_IDET")

        return True

    def generateRandomIDs(self):
        sql = "UPDATE StackObjectThin set randomStackObjID = RAND("+str(self.batchID)+")"
        self.scratchDb.execute(sql)

    def generatePspsUniqueIDs(self):
        sql = "UPDATE StackObjectThin set uniquePspsSTid = (("+str(self.batchID)+"*1000000000 ) + row)"
        self.scratchDb.execute(sql)

    '''
    select objID (psps object ID) from dvo restricted to this set of imageIDs. also insert
    ippObjID and several constants (for a skycell): surveyID, skyCellId, processingVersion
    '''
    def selectDvoObjIDs(self):

        # we need a temporary column to select detections from a common stack object
        self.scratchDb.addColumn("StackObjectThin", "ippDetectID", "bigint")

        # we need a temporary column for each filter to identify empty rows
        for filter in self.filters:
            self.scratchDb.addColumn("StackObjectThin", filter + "haveData", "tinyint")

        whereClause = " WHERE ("
        imageIDs = self.imageIDs.values()
        for i in range(len(imageIDs)):
            imageID = imageIDs[i]
            self.logger.infoPair("selecting imageID", imageID)

            # NOTE: in this statement, imageID is the DVO imageID, not the gpc1 imageID (== DVO externID)
            whereClause += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")"

            if (i < len(imageIDs) - 1):
                whereClause += " or "

        whereClause += ")"

        sql = "insert into StackObjectThin (objID, ippDetectID, ippObjID) select distinct objID, ippDetectID, ippObjID from dvoDetectionFull " + whereClause;

        self.logger.debugPair("sql 1", sql)
        self.scratchDb.execute(sql)

        # ok, now add surveyID, tessID, projectionID, skycellID, dtaRelease

        sql = "update StackObjectThin set surveyID = "+str(self.surveyID) +"\
              , tessID = '" + str(self.tessID) + "'\
              , projectionID = '" + str(self.projectionID) + "'\
              , skycellID = '" + str(self.skycellID) + "'\
              , processingVersion = '" + str(self.skychunk.processingVersion)  +"'"
              
        self.logger.debugPair("sql 2", sql)
        self.scratchDb.execute(sql)

        # ok, now fill g/r/i/z/yippDetectID using objID and detectID and image ID

        for filter in self.filters:
            if self.stackIDs[filter] <= 0: continue # stackIDs[filter] not set if we do not have the data

            imageID = self.imageIDs[filter]

            sql = "update StackObjectThin join "+self.scratchDb.dvoDetectionTable +" using (objID, ippObjID, ippDetectID) set "+filter+"ippDetectID = ippDetectID \
                  where " + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID)

            self.logger.debugPair("sql f", sql)
            self.scratchDb.execute(sql)

        self.scratchDb.dropColumn("StackObjectThin", "ippDetectID")

    '''
    Insert objid (from dvo) into table from main table
    '''
    def insertDvoIDs(self, table, mainTable):

        # we need a temporary column for each filter to identify empty rows
        for filter in self.filters:
            self.scratchDb.addColumn(table, filter + "haveData", "tinyint")

        fields = "objID, ippObjID, randomStackObjID, primaryDetection, bestDetection, uniquePspsSTID, "

        for i in range(len(self.filters)):
            filter = self.filters[i]
            fields += filter + "ippDetectID, "
            fields += filter + "stackDetectID, "
            fields += filter + "stackImageID " # careful here: do not add comma to last entry
            if (i < len(self.filters) - 1):
                fields += ", "

        sql  = "INSERT INTO " + table + " (" + fields + ") "
        sql += "SELECT " + fields + " FROM " + mainTable

        self.scratchDb.execute(sql)
        
    '''
    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    '''
    def populatePspsTables(self):
        self.logger.infoPair("starting","populatePspsTables");
        self.skipBatch = False

        # each of the "populate*" methods below add their tables to the list:
        self.tablesToExport=[]    

        # EAM 20140730 : I have revised the StackObjectXX construction
        # process.  The challenge here is that we have dvo-generated
        # objIDs and ippObjIDs for each stack detection in seperate
        # rows, but we want to merge the 5 filter sets into a single
        # set of rows.  The old code was relying on INSERT IGNORE,
        # making it hard to catch errors, and probably not doing the
        # insert very efficiently
        
        # Now we do a first pass with SELECT DISTINCT to get the
        # complete set of objIDs which are associated with detections
        # from one of the relevant images (ie, measure.imageID matches
        # the set of image IDs).  

        # once we have determined the complete set of desired objects,
        # then we can use an update to set the per-field fields with
        # data selected for each filter.  Once we have constructed
        # this basic common information in StackObjectThin, then we
        # can uses this table to seed the same rows in the other Stack
        # tables

        for filter in self.filters:
            if self.stackIDs[filter] > 0:        
                self.populateStackMeta(filter)
                
        self.populateStackObjectThin()

        self.populateStackObjectAttributes()

        self.populateStackDetEffMeta()

        if self.stackType != "NIGHTLY_STACK":

            self.logger.infoPair("populating ","StackModelFitExtra")
            self.populateStackModelFitExtra()

            self.logger.infoPair("populating ","StackModelFit")
            self.populateStackModelFit("Exp", "PS_MODEL_EXP")
            self.populateStackModelFit("DeV", "PS_MODEL_DEV")
            self.populateStackModelFit("Ser", "PS_MODEL_SERSIC")

            self.populateStackPetrosian()

            self.logger.infoPair("populating ","StackApFlx Tables")
            self.populateStackApFlx()

            self.populateStackApFlxExGal("Unc")
            self.populateStackApFlxExGal("Con6")
            self.populateStackApFlxExGal("Con8")

        # delete rows with no content. for the other tables, this is done in 
        # the 'populate' functions, but StackObjectThin needs to retain all rows
        # until those tables are done
        self.deleteRowsWithNoStackData("StackObjectThin")

        self.logger.infoPair("populating","StackToImage") 
        self.populateStackToImage()
       
 
       
        self.logger.infoPair("populating","StackToFrame") 

#        self.populateStackToFrame()
        if not self.populateStackToFrame():
                    self.logger.errorPair("can't populate"," stack to frame")
                    return False
        self.logger.infoPair("removing frameID"," duplicates")
        self.scratchDb.execute("ALTER IGNORE TABLE StackToFrame ADD UNIQUE INDEX(frameID)")

        self.setMinMaxObjID(["StackObjectThin"])

        self.logger.infoPair("finishing","populatePspsTables"); 
        return True

    '''
    overidding this method - use regex to trimoff letter before stack
    '''
    def exportPspsTablesToFits(self, regex="(.*)"):
        return super(StackBatch, self).exportPspsTablesToFits("(Stack.*)")

    '''
    Imports IPP tables from FITS file

    Accepts a regular expression filter so not all tables need to be imported
    '''
    def importIppTables(self, columns="*", filter=""):
        if self.config.useOhanaMYSQLtoFITS:
            if not self.importIppTablesOhana():
                self.logger.errorPair("problem running", "importIppTablesOhana")
                return False
        else:
            if not self.importIppTablesStilts(columns, filter):
                self.logger.errorPair("problem running", "importIppTablesStilts")
                return False

        return True

    '''
    Imports IPP tables from FITS file
    Accepts a regular expression to match the tables so not all tables need to be imported
    '''
    def importIppTablesOhana(self, columns="*", tableRE=""):
        
        self.logger.infoPair("Importing ST tables using ", "Ohana")

        self.tablesLoaded = []

        count = 0
        for filter in self.filters:
            if self.stackIDs[filter] <= 0:  
                self.logger.infoPair("no files for filter" , filter) 
                continue

            fileName = self.fits[filter].getPath()
            
            self.logger.infoPair("using filename:",fileName)
            
            ## output table names need to have "filter" prepended.  
            fitsOptions  = "-tableprefix " + filter

            # this pair of options is equivalent to the stilts "addcol table_index $0" call
            fitsOptions += " -sequence-column table_index"
            fitsOptions += " -sequence-column-start 1"

            ## *** actually write the FITS data to mysql
            self.scratchDb.loadFITStoMYSQL(fileName, fitsOptions)

            # get the list of tables in this file:
            try:
                tables = stilts.treads(fileName)
            except:
                self.logger.errorPair("STILTS could not import from", fileName)
                return False
            
            for table in tables:
                self.logger.debugPair("Read IPP table", table.name)
                myTable = filter + table.name.replace(".", "_")
                self.tablesLoaded.append(myTable)
                count += 1

        self.logger.infoPair("Done. Imported", "%d tables" % count)
        self.indexIppTables()

        return True

    '''
    Imports IPP tables from FITS file
    Accepts a regular expression to match the tables so not all tables need to be imported
    (This regex feature is not currently used...)
    '''
    def importIppTablesStilts(self, columns="*", tableRE=""):
        
        if self.config.retry: return True

        self.logger.infoPair("Importing ST tables with table match expression: ", tableRE)

        self.tablesLoaded = []

        transtable = string.maketrans(".","_")

        count = 0
        for filter in self.filters:
            if self.stackIDs[filter] <= 0:  
                self.logger.infoPair("no files for filter" , filter) 
                continue

            fileName = self.fits[filter].getPath()
            
            self.logger.infoPair("using filename:",fileName)
            
            try:
                tables = stilts.treads(fileName)
            except:
                self.logger.errorPair("STILTS could not import from", fileName)
                return False
            
            for table in tables:
                match = re.match(tableRE, table.name)
                if not match: continue

                self.logger.infoPair("Reading IPP table", filter + table.name)
                table = stilts.tpipe(table, cmd='addcol table_index $0')
                table = stilts.tpipe(table, cmd='explodeall')
                  
                # drop any previous tables before import
                self.scratchDb.dropTable(filter + table.name)
                  
                # IPP FITS files are littered with infinities, so remove these
                self.logger.info("Removing Infinity values from all columns")
                table = stilts.tpipe(table, cmd='keepcols "' + columns + '"')
                table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
                table = stilts.tpipe(table, cmd='replaceval Infinity null *')
                  
                try:
                    table.write(self.scratchDb.url + '#' + filter + table.name)
                    count = count + 1
                except:
                    self.logger.exception("Problem writing table '" + filter + table.name + "' to the database")
                      
                # headers (and thus table.name) are like SkyChip.psf, but mysql names are SkyChip_psf (converted automatically by stilts)
                myTable = filter + table.name.replace(".", "_")
                self.tablesLoaded.append(myTable)

        self.logger.infoPair("Done. Imported", "%d tables" % count)
        self.indexIppTables()
        return True
