# Handle IPTS 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

user = PVUtil.getString(pvs[0])
beamline = PVUtil.getString(pvs[2])
ipts = pvs[3]
title = pvs[4]
members = pvs[5]

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

class IPTSTask(Runnable):
    """Runnable for performing JDBC lookups in background
       .. then using Display.syncExec to update UI as data
          arrives
    """
    def run(self):
        data = dict()
        currentDisplay.syncExec(UpdateTable(table, []))
        try:
            connection = connect()
            statement = connection.prepareStatement(
                """SELECT person_id FROM xf_admin.person@snsxprod_dbl.sns.ornl.gov WHERE usrnm=UPPER(?)""")
            statement.setString(1, user)
            
            result = statement.executeQuery()
            if result.next():
                person_id = result.getInt(1)
                pvs[1].setValue(person_id)
            else:
                pvs[1].setValue("0")
                connection.close()
                return
            
            # Even with `DISTINCT` .., there will be duplicate IPTS rows
            # because of sub, date, ...
            statement = connection.prepareStatement(
               """SELECT DISTINCT TO_CHAR(p.prpsl_id, '00000') AS IPTS,
                     p.submiss_nbr,
                     NVL(TO_CHAR(i.sched_start_dte, 'YYYY-MM-DD'), 'Tentative') AS StartDate,
                     p.title AS Title,
                     (
                      SELECT LISTAGG (m.name || ':' || m.usrnm || ':' || m.mbr_role, ';') WITHIN GROUP (ORDER BY m.usrnm)
                      FROM prpsl.prpsl_team_mbr_v@snsxprod_dbl.sns.ornl.gov m
                      WHERE m.prpsl_id = p.prpsl_id AND m.submiss_nbr = p.submiss_nbr
                     ) AS Members
                    FROM prpsl.prpsl_submiss_v@snsxprod_dbl.sns.ornl.gov p
                    JOIN express.expt_sched@snsxprod_dbl.sns.ornl.gov i ON i.prpsl_id = p.prpsl_id AND i.submiss_nbr = p.submiss_nbr
                    JOIN prpsl.pers_can_view_prpsl_v@snsxprod_dbl.sns.ornl.gov v ON p.prpsl_id = v.prpsl_id AND p.submiss_nbr = v.submiss_nbr
                    WHERE v.person_id=?
                      AND i.instr_id=UPPER(?)
                      AND v.stat_id IN ('APPROVED', 'ALTERNATE', 'CONFIRMED', 'COMPLETED')
                      AND i.stat_id IN ('S', 'TS')
                    ORDER BY IPTS DESC, StartDate DESC""")
            statement.setInt(1, person_id)
            statement.setString(2, beamline)
            result = statement.executeQuery()
            while result.next():
                ( ipts, sub, date, title, members ) = [ strclean(result.getString(i+1)) for i in range(5) ]
                if ipts in data:
                    # Use entry with latest submission or date
                    ( ipts2, sub2, date2, title2, members2 ) = data[ipts]
                    if (sub2 > sub  or  date2 > date) and not date2 == 'Tentative':
                        ( sub, date, title, members ) = ( sub2, date2, title2, members2 )
                data[ipts] = [ ipts, sub, date, title, members ]
            connection.close()
            # currentDisplay.syncExec(ShowDialog("Found %d proposals" % len(data)))
        except Exception, e:
            currentDisplay.syncExec(ShowDialog("Error: %s" % str(e)))
        finally:
            # Convert dict into array
            array = data.values()
            # Sort by IPTS, highest first
            array = sorted(array, key = lambda items : items[0], reverse=True)
            currentDisplay.syncExec(UpdateTable(table, array))
        
        
thread = Thread(IPTSTask());
thread.start()



class SelectionListener(ITableSelectionChangedListener):
    """Place info from selected IPTS entry in other PVs"""
    def selectionChanged(self, selection):
        if len(selection) != 1:
            return
        selection = selection[0]
        ipts.setValue(selection[0])
        title.setValue(selection[3])
        members.setValue(selection[4])

table.addSelectionChangedListener(SelectionListener())