#@ File outputDB("Specify your Database (.db-file)")
#@ Boolean Normalize_To_Selection_Area(value="False")
#@ Boolean Normalize_To_Mask_Area(value="False")
#@ Boolean Inside_Mask(value="True")
#@ String Channel_Name("Specify the channel you want to explore", style="Text Field")
#@ String Second_Channel("Specify the channel you want to explore", style="Text Field")
#@ String m2(label="Choose threshold method used for the second channel", 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 = "Coloc_Analysis"

if Inside_Mask:
	inorout = "Inside"
else:
	inorout = "Outside"

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

dbConn = getConnection(jdbc_driver, DB_URL)

if not Selection:
	Selection = "allSelected"

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

if Normalize_To_Selection_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"

elif Normalize_To_Mask_Area:
	paColumn = tab.column("Number_of_Particles").asDoubleColumn()
	areaColumn = tab.column("Mask_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 Colocalisation Results between %s and %s in %s" %(Channel_Name, Second_Channel, 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")