#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 AKFeatureMeasurements import AKFeatureMeasurements
from AKSettings import TABLES

class AKFeatureMeasurementsChem(AKFeatureMeasurements):
    '''Make sure that all the queries used by AKFeatureMeasurements reference to the hydrochemical tables'''
    def __init__(self, settings, iface, windowTitle = u'Query Measurements', parent=None):        
        super(AKFeatureMeasurementsChem, self).__init__(settings, iface, windowTitle, parent)
        self.queryTable = TABLES.SAVED_QUERY

    def defineSQLQueries(self):
        self.sqlSelAllParams_1 =  "SELECT distinct CP.nameEN, CP.parameterNumber as name " \
                                "FROM " + TABLES.CHEMPARAMS + " CP "
        self.sqlSelAllParams_2 = "WHERE CP.parameterNumber IN "
        self.sqlSelAllParams_3 = "ORDER BY CP.nameEN COLLATE NOCASE;"

        self.sqlSelMeasurements_1_1 = "SELECT " \
                                      "P.id AS PointId, " \
                                      "P.point AS Point, " \
                                      "CS.sample AS Sample, " \
                                      "CS.samplingTime AS `Sample Date` , " \
                                      "CAMPS.campaign AS Campaign, " \
                                      "CM.resultTime `Measurement Date`, " \
                                      "CM.hydrochemicalParameterCode paramId, " \
                                      "CP.parameterNumber AS baseParamId, " \
                                      "CP.nameEN AS Parameter, " \
                                      "CM.value AS value, "  \
                                      "CM.compValue as limitValue, "  \
                                      "UNITS.nameEN AS Unit, " \
                                      "UNITS.uomCode AS UnitCode, " \
                                      "UNITS.id AS UnitId " \
                                    "FROM " + \
                                      TABLES.POINTS + " P, " + \
                                      TABLES.CHEMSAMPLES + " CS, " + \
                                      TABLES.CHEMMEASUREMENTS + " CM, " + \
                                      TABLES.L_UNITOFMEASUREMENTS + " UNITS, " + \
                                      TABLES.CHEMPARAMS + " CP, " + \
                                      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 SQS.Active = 1 " \
                                      "AND CS.id = SQS.SampleID " \
                                      "AND CS.samplingTime BETWEEN SQ.StartDate AND SQ.EndDate " \
                                      "AND CS.campaignId = CAMPS.id " \
                                      "AND CM.sampleId = SQS.SampleID " \
                                      "AND CM.hydrochemicalParameterCode = CP.id " \
                                      "AND CP.uomCodeId = UNITS.id " \
                                      "AND CP.parameterNumber IN "
        
        self.sqlSelMeasurements_1_2 = "SELECT " \
                                      "P.id AS PointId, " \
                                      "P.point AS Point, " \
                                      "CS.sample AS Sample, " \
                                      "CS.samplingTime AS `Sample Date` , " \
                                      "CAMPS.campaign AS Campaign, " \
                                      "CM.resultTime `Measurement Date`, " \
                                      "CM.hydrochemicalParameterCode paramId, " \
                                      "CP.parameterNumber AS baseParamId, " \
                                      "CP.nameEN AS Parameter, " \
                                      "CM.value AS value, "  \
                                      "CM.compValue as limitValue, "  \
                                      "UNITS.nameEN AS Unit, " \
                                      "UNITS.uomCode AS UnitCode, " \
                                      "UNITS.id AS UnitId " \
                                    "FROM " + \
                                      TABLES.POINTS + " P, " + \
                                      TABLES.CHEMSAMPLES + " CS, " + \
                                      TABLES.CHEMMEASUREMENTS + " CM, " + \
                                      TABLES.L_UNITOFMEASUREMENTS + " UNITS, " + \
                                      TABLES.CHEMPARAMS + " CP, " + \
                                      TABLES.CAMPAIGNS + " CAMPS, " + \
                                      TABLES.SAVED_QUERY + " SQ, " + \
                                      TABLES.SAVED_QUERY_POINTS + " SQP, " + \
                                      TABLES.SAVED_QUERY_SAMPLES + " SQS, " + \
                                      TABLES.NORMATIVES + " NRMS, " + \
                                      TABLES.CHEMNORMPARAMS + " NRMPRMS " + \
                                    "WHERE SQP.SavedQueryId = ? " \
                                      "AND SQ.ID = SQP.SAVEDQUERYID " \
                                      "AND SQP.ID = SQS.SavedQueryPointID " \
                                      "AND SQP.pointId = P.id " \
                                      "AND P.id = CS.pointId " \
                                      "AND SQS.Active = 1 " \
                                      "AND CS.id = SQS.SampleID " \
                                      "AND CS.samplingTime BETWEEN SQ.StartDate AND SQ.EndDate " \
                                      "AND CS.campaignId = CAMPS.id " \
                                      "AND CM.sampleId = SQS.SampleID " \
                                      "AND CM.hydrochemicalParameterCode = CP.id " \
                                      "AND CP.uomCodeId = UNITS.id " \
                                      "AND NRMS.id = ? " \
                                      "AND NRMPRMS.normativeId = NRMS.id " \
                                      "AND NRMPRMS.hydroChemicalParametersCode = CP.id " \
                                      "AND CP.parameterNumber IN "
        self.sqlSelMeasurements_2 = " ORDER BY P.id, CS.samplingTime, CP.nameEN;"