#coding=utf-8
#Copyright (C) 2015 IDAEA-CSIC
#
#This program is free software; you can redistribute it and/or modify it under the terms of the 
#GNU General Public License as published by the Free Software Foundation; either version 2 of the License, 
#or (at your option) any later version. This program is distributed in the hope that it will be useful, but 
#ITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR 
#PURPOSE. See the GNU General Public License for more details.
#
#You should have received a copy of the GNU General Public License along with this program; if not, write to 
#the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
'''
:copyright: Copyright (C) 2015 IDAEA-CSIC
:authors: V. Velasco Mansilla, L.M. de Vries, A. Nardi, R. Criollo, E. Vázquez Suñé
:contact: enric.vazquez@idaea.csic.es
:license: GPL General Public License, version 2 or any later version (http://opensource.org/licenses/GPL-2.0)
'''
from PyQt4 import QtGui

from AKSettings import TABLES
from AKFeatureSamples import AKFeatureSamples

class AKFeatureSamplesChemistry(AKFeatureSamples):
    '''Make sure that all the queries used by AKFeatureSamples reference to the hydrochemical tables'''
    def __init__(self, settings, iface, parent=None):
        super(AKFeatureSamplesChemistry, self).__init__(settings, iface, parent)
        self.m_settings = settings 
        self.m_icon = QtGui.QIcon(settings.getIconPath('edit-select-chem.png'))
        self.m_text = u'Hydrochemical Spatial Query'

    def defineSQLQueries(self):
        self.sqlEndQuery = ";"
        self.sqlSelPoints_1 = "SELECT P.id, P.point " \
                              "FROM " + \
                              TABLES.POINTS + " P WHERE P.id IN ("
        self.sqlSelPoints_2 = ");"
        
        self.sqlSelSavedQuery = "SELECT QueryName, StartDate, EndDate " \
                                "FROM " + TABLES.SAVED_QUERY + " SQ " \
                                "WHERE SQ.ID= ? ;"

        self.sqlSelSavedQueryPoint = "SELECT P.id, P.point " \
                                     "FROM " + \
                                       TABLES.SAVED_QUERY_POINTS + " SQP, " + \
                                       TABLES.POINTS + " P " \
                                     "WHERE SQP.SAVEDQUERYID = ?" \
                                     "AND SQP.pointId = P.id;"
        
        self.sqlSelectSavedPointsWithSamples = "SELECT " \
                                                   "DISTINCT P.id, P.point " \
                                               "FROM " + \
                                                   TABLES.SAVED_QUERY + " SQ, " + \
                                                   TABLES.SAVED_QUERY_POINTS + " SQP, " + \
                                                   TABLES.SAVED_QUERY_SAMPLES + " SQS, " + \
                                                   TABLES.POINTS + " P, " + \
                                                   TABLES.CHEMSAMPLES + " CS " + \
                                               "WHERE " \
                                                   "SQP.SavedQueryId = ?" \
                                                   "AND SQ.ID = SQP.SAVEDQUERYID " \
                                                   "AND SQP.ID = SQS.SavedQueryPointID " \
                                                   "AND SQP.pointId = P.id " \
                                                   "AND P.id = CS.pointId " \
                                                   "AND CS.samplingTime BETWEEN SQ.StartDate AND SQ.EndDate;"
           
        self.sqlSelSamples = "SELECT " \
                                "SQS.Active, SQS.ID, P.point AS Point, CS.sample AS Sample, " \
                                "CS.samplingTime AS Date, CAMPS.campaign AS Campaign " \
                             "FROM " + \
                               TABLES.POINTS + " P, " + \
                               TABLES.CHEMSAMPLES + " CS, " + \
                               TABLES.CAMPAIGNS + " CAMPS, " + \
                               TABLES.SAVED_QUERY + " SQ, " + \
                               TABLES.SAVED_QUERY_POINTS + " SQP, " + \
                               TABLES.SAVED_QUERY_SAMPLES + " SQS " \
                             "WHERE " \
                               "SQP.SavedQueryId = ? " \
                               "AND SQ.ID = SQP.SAVEDQUERYID " \
                               "AND SQP.ID = SQS.SavedQueryPointID " \
                               "AND SQP.pointId = P.id " \
                               "AND P.id = CS.pointId " \
                               "AND CS.id = SQS.sampleID " \
                               "AND CS.campaignId = CAMPS.id " \
                               "AND CS.samplingTime BETWEEN SQ.StartDate AND SQ.EndDate "
        self.sqlSelSamples_campaign = "AND CS.campaignId = ? " 
        self.sqlSelSamples_dateRange = "AND CS.samplingTime BETWEEN ? AND ? "
        self.sqlSelSamples_pointList = "AND P.id IN "
        

        self.sqlGetNewId = "SELECT DISTINCT Id "\
                           "FROM " + TABLES.SAVED_QUERY_POINTS + " " \
                           "WHERE SavedQueryId = ? AND pointId = ?;"
        
        self.sqlUpdateSavedQueries = "UPDATE " + TABLES.SAVED_QUERY + " SET StartDate=? ,EndDate= ? WHERE Id= ? ;"
        
        self.sqlUpdateSavedSamples = "UPDATE " + TABLES.SAVED_QUERY_SAMPLES + " SET Active=? WHERE Id= ? ;"

        self.sqlDeleteQueryPoints = "DELETE FROM " + TABLES.SAVED_QUERY_POINTS + " WHERE SAVEDQUERYID = ?;"
        
        self.selPointsWithSamples_1 = "SELECT " \
                                         "DISTINCT P.id, CS.pointId " + \
                                      "FROM " + \
                                      TABLES.POINTS + " P, " + \
                                      TABLES.CHEMSAMPLES + " CS " \
                                      "WHERE P.id = CS.pointId " \
                                         "AND CS.pointId IN "
        self.selPointsWithSamples_2 = " AND CS.samplingTime BETWEEN ? AND ? ;"
        
        self.insertSavedQueryPoints = "INSERT INTO " + TABLES.SAVED_QUERY_POINTS + " VALUES (NULL, ?, ?);"  
        
        self.sqlSelSampleDetails = "SELECT CS.id " \
                                   "FROM " + \
                                      TABLES.POINTS + " P, " + \
                                      TABLES.CHEMSAMPLES + " CS " \
                                   "WHERE P.id = ? " \
                                   "AND CS.pointId = P.id " \
                                   "AND samplingTime BETWEEN ?  AND ? ;"
        
        self.sqlInsertSavedQuerySamples = "INSERT INTO " + TABLES.SAVED_QUERY_SAMPLES + \
                                            " (SavedQueryPointId, sampleId, Active) VALUES (?,?,\"1\");"
        
        self.sqlSelCampaigns =    "SELECT DISTINCT CS.campaignId, CAMPS.campaign " \
                                  "FROM " + \
                                      TABLES.POINTS + " P, " + \
                                      TABLES.CHEMSAMPLES + " CS, " + \
                                      TABLES.CAMPAIGNS + " CAMPS, " + \
                                      TABLES.SAVED_QUERY + " SQ, " + \
                                      TABLES.SAVED_QUERY_POINTS + " SQP, " + \
                                      TABLES.SAVED_QUERY_SAMPLES + " SQS " \
                                  "WHERE SQP.SavedQueryId = ? " \
                                    "AND SQ.id = SQP.SAVEDQUERYID " \
                                    "AND SQP.id = SQS.SavedQueryPointID " \
                                    "AND SQP.pointId = P.id " \
                                    "AND P.id = CS.pointId " \
                                    "AND CS.campaignId = CAMPS.id " \
                                    "AND CS.samplingTime BETWEEN SQ.StartDate AND SQ.EndDate;"
                                    
        self.queryListTable = TABLES.SAVED_QUERY

