# Handle ITEMS table
#
# kasemirk@ornl.gov

from java.lang import Exception
from java.lang import Thread, Runnable
from org.csstudio.opibuilder.scriptUtil import PVUtil
from org.eclipse.jface.dialogs import MessageDialog
from org.eclipse.swt.widgets import Display
from helpers import connect, strclean, ShowDialog, UpdateTable
from org.csstudio.swt.widgets.natives.SpreadSheetTable  import ITableSelectionChangedListener

ipts = PVUtil.getLong(pvs[0])
items = pvs[1]
name = pvs[2]
description = pvs[3]
mass = pvs[4]
container = pvs[5]
nature = pvs[6]
comments = pvs[7]


currentDisplay = Display.getCurrent()
table = display.getWidget("Items").getTable()


# When opened for the first time, leave PVs unchanged.
# When later triggered by a new proposal number,
# clear the sample by setting it no "No sample"

try:
    if last_ipts != ipts:
        items.setValue(-1)
        name.setValue("None")
        description.setValue("No sample")
        mass.setValue("N/A")
        container.setValue("N/A")
        nature.setValue("N/A")
        comments.setValue("N/A")
except:
    pass

last_ipts = ipts

class ITEMSTask(Runnable):
    """Runnable for performing JDBC lookups in background
       .. then using Display.syncExec to update UI as data
          arrives
    """
    def run(self):
        # currentDisplay.syncExec( ShowDialog("Lookup Items for IPTS-%d" % ipts) )
        data = []
        data.append([ '-1', "None", "No sample", "N/A", "N/A", "N/A", "N/A" ])
        currentDisplay.syncExec(UpdateTable(table, data))
        try:
            connection = connect()
            statement = connection.prepareStatement(
               """SELECT s.smpl_id || '' AS ID,
                  s.smpl_nm AS Name,
                  s.smpl_descr AS Description,
                  s.vol_wgt_amt || s.mass_unit_id AS Mass,
                  c.descr AS Container,
                  (SELECT LISTAGG(sf.descr, ',') WITHIN GROUP (ORDER BY sf.descr)
                   FROM smpl.smpl_form@snsxprod_dbl.sns.ornl.gov sf, smpl.smpl_smpl_form_assc@snsxprod_dbl.sns.ornl.gov ssfa
                   WHERE ssfa.smpl_id = s.smpl_id
                     AND sf.smpl_form_id = ssfa.smpl_form_id
                  ) AS Nature,
                  NVL(s.cmnt, '-') AS Comments
                  FROM smpl.sample@snsxprod_dbl.sns.ornl.gov s
                  JOIN SMPL.smpl_prpsl_assc@snsxprod_dbl.sns.ornl.gov p ON s.smpl_id = p.smpl_id
                  JOIN smpl.smpl_cont@snsxprod_dbl.sns.ornl.gov c ON s.cont_id = c.cont_id
                  WHERE p.prpsl_id = ?
                  ORDER BY s.smpl_id DESC""")
               
            statement.setInt(1, ipts)
            result = statement.executeQuery()
            while result.next():
                data.append([ strclean(result.getString(i+1)) for i in range(7) ])

            connection.close()
        except Exception, e:
            currentDisplay.syncExec(ShowDialog("Error: %s" % str(e)))
        finally:
            currentDisplay.syncExec(UpdateTable(table, data))
        
        
thread = Thread(ITEMSTask());
thread.start()


class SelectionListener(ITableSelectionChangedListener):
    """Place info from selected ITEMS entry in other PVs"""
    def selectionChanged(self, selection):
        if len(selection) != 1:
            return
        selection = selection[0]
        items.setValue(int(selection[0]))
        name.setValue(selection[1])
        description.setValue(selection[2])
        mass.setValue(selection[3])
        container.setValue(selection[4])
        nature.setValue(selection[5])
        comments.setValue(selection[6])


table.addSelectionChangedListener(SelectionListener())