#@ File outputDB("Specify your Database (.db-file)")
#@ Boolean Normalize_To_Area(value="False")
#@ String Channel_Name("Specify the channel you want to explore", style="Text Field")
#@ String m(label="Choose threshold method", choices={'Default', 'Huang', 'Intermodes', 'IsoData', 'IJ_IsoData', 'Li', 'MaxEntropy', 'Mean', 'MinError', 'Minimum', 'Moments', 'Otsu', 'Percentile', 'RenyiEntropy', 'Shanbhag', 'Triangle', 'Yen'})
#@ String Selection("Specify the selection (mask) you want to explore", style="Text Field")
#@ String Group_by(style="Text Field")

from __future__ import with_statement
from tech.tablesaw.api import Table
from tech.tablesaw.plotly import Plot
from tech.tablesaw.plotly.api import BoxPlot, Histogram
#from tech.tablesaw.plotting import Plot
from java.sql import DriverManager, SQLException
from org.sqlite import SQLiteConfig, JDBC
from java.lang import Class
import sys
from ij.measure import ResultsTable


def getConnection(jdbc_driver, DB_URL):
        """
        Get Connection to DB and returns connection handler
        """

        config = SQLiteConfig()
        config.enforceForeignKeys(True)

        try:
            Class.forName(jdbc_driver).newInstance()
        except Exception, msg:
            print msg
            sys.exit(-1)
        try:
            dbConn = DriverManager.getConnection(DB_URL, config.toProperties())
        except SQLException, msg:
            print msg
            sys.exit(-1)
        return dbConn


##############################################################################################################
####### Start of the script
##############################################################################################################


pa = "Particle_Analysis"


if not Selection:
	Selection = "allSelected"

pathDB = outputDB.getAbsolutePath()
DB_URL = "jdbc:sqlite:" + pathDB
jdbc_driver = "org.sqlite.JDBC"

dbConn = getConnection(jdbc_driver, DB_URL)

try: 
    stmt = dbConn.createStatement()
    query = "SELECT * FROM %s_Table WHERE Channel_Name = '%s' and Selection = '%s' and Method='%s'" %(pa, Channel_Name, Selection, m)
    try:
        results = stmt.executeQuery(query)
        tab = Table.read().db(results, "Particle Analysis Table")
    except SQLException, msg:
        print msg
        sys.exit(-1)
except SQLException, msg:
    print msg
    sys.exit(-1)

if Normalize_To_Area:
	paColumn = tab.column("Number_of_Particles").asDoubleColumn()
	areaColumn = tab.column("Selection_Area")
	
	normColumn = paColumn.divide(areaColumn)
	normColumn.setName("Norm")
	tab.addColumns([normColumn])
	norm = "Norm"
else: 
	norm = "Number_of_Particles"

print tab

bx = BoxPlot.create("BoxPlot of Particle Analysis Results of channel %s, selection %s" %(Channel_Name, Selection), tab, Group_by, norm)

Plot.show(bx)

colnames = tab.columnNames()
rt = ResultsTable()
for c in colnames:
	ar = tab.column(c).asObjectArray()
	for i, a in enumerate(ar):
		rt.setValue(c,i,a)

rt.show("ResultTable of Query")