﻿<?xml version="1.0"?>
<objects xmlns="http://www.springframework.net"
				 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
				 xmlns:quartz="http://www.opensymphony.com/quartz/JobSchedulingData"
				 xsi:schemaLocation="http://www.springframework.net http://www.springframework.net/xsd/spring-objects.xsd" >
  <object id="AgencyControlDeviceExport" type="Sleis.Export.ExportGroup">
    <property name="Name" value="Agency_ControlDevice">
    </property>
    <property name="Queries">
      <dictionary key-type="System.String" value-type="System.String">
        <entry key="ControlDevice" value="SELECT
	                                          cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                          cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                          cds.CTRL_DEVC_STAT_DESC AS CTRL_DEVC_STATUS,
	                                          cd.CTRL_DEVC_STAT_CD_DATE AS CTRL_DEVC_STATUS_DATE,
	                                          cm.CTRL_MEAS_DESC AS CTRL_MEAS_DESC,
	                                          cd.CTRL_DEVC_CMNT AS CTRL_DEVC_CMNT
                                          FROM
	                                          M_CTRL_DEVC cd 
	                                          INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	                                          INNER JOIN M_AGN_RPT ar ON fs.AGN_RPT_ID = ar.AGN_RPT_ID
	
	                                          LEFT JOIN A_CTRL_DEVC_STAT cds ON cd.A_CTRL_DEVC_STAT_CD = cds.A_CTRL_DEVC_STAT_CD
	                                          LEFT JOIN E_CTRL_MEAS cm ON cd.E_CTRL_MEAS_CD = cm.E_CTRL_MEAS_CD
                                          WHERE
	                                          ar.AGN_RPT_ID = :reportId --current report being reported on
                                          ORDER BY
	                                          cd.CTRL_DEVC_IDEN ASC
	                                      " />
        <entry key="ControlledPollutants" value="SELECT
	                                                cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                cdp.E_POLLUTANT_CD AS CTRL_POLLUTANT,
	                                                p.POLLUTANT_DESC AS CTRL_POLLUTANT_DESC,
	                                                cdp.PCT_CTRL_MEAS_REDC_EFCY AS PCT_REDC_EFCY
                                                FROM
	                                                M_CTRL_DEVC_POLLUTANT cdp 
	                                                INNER JOIN M_CTRL_DEVC cd ON cdp.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                                INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	                                                INNER JOIN M_AGN_RPT ar ON fs.AGN_RPT_ID = ar.AGN_RPT_ID
	
	                                                LEFT JOIN E_POLLUTANT p ON cdp.E_POLLUTANT_CD = p.E_POLLUTANT_CD
                                                WHERE
	                                                ar.AGN_RPT_ID = :reportId --current report being reported on
                                                ORDER BY
	                                                cd.CTRL_DEVC_IDEN ASC,
	                                                p.E_POLLUTANT_CD ASC
                                                " />
        <entry key="AdditionalInformation" value="SELECT
	                                                  cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                  cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                  cf.FLD_LABEL AS FLD_NAME,
	                                                  cfv.CSTM_FLD_VAL AS FLD_VALUE
                                                  FROM
	                                                  M_CTRL_DEVC_CSTM_FLD_VAL cfv 
	                                                  INNER JOIN A_CSTM_FLD cf ON cfv.A_CSTM_FLD_ID = cf.A_CSTM_FLD_ID
	                                                  INNER JOIN M_CTRL_DEVC cd ON cfv.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                                  INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	                                                  INNER JOIN M_AGN_RPT ar ON fs.AGN_RPT_ID = ar.AGN_RPT_ID
                                                  WHERE
	                                                  ar.AGN_RPT_ID = :reportId --current report being reported on
                                                  ORDER BY
	                                                  cd.CTRL_DEVC_IDEN ASC,
	                                                  cf.FLD_LABEL ASC" />
      </dictionary>
    </property>
  </object>
  <object id="MasterControlDeviceExport" type="Sleis.Export.ExportGroup">
    <property name="Name" value="Master_ControlDevice">
    </property>
    <property name="Queries">
      <dictionary key-type="System.String" value-type="System.String">
        <entry key="ControlDevices" value="SELECT
	                                                cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                cds.CTRL_DEVC_STAT_DESC AS CTRL_DEVC_STATUS,
	                                                cd.CTRL_DEVC_STAT_CD_DATE AS CTRL_DEVC_STATUS_DATE,
	                                                cm.CTRL_MEAS_DESC AS CTRL_MEAS_DESC
                                                FROM
	                                                M_CTRL_DEVC cd 
	                                                INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	
	                                                LEFT JOIN A_CTRL_DEVC_STAT cds ON cd.A_CTRL_DEVC_STAT_CD = cds.A_CTRL_DEVC_STAT_CD
	                                                LEFT JOIN E_CTRL_MEAS cm ON cd.E_CTRL_MEAS_CD = cm.E_CTRL_MEAS_CD
                                                WHERE
	                                                fs.FAC_SITE_ID = :facilityId --current facility
	                                                AND fs.FAC_SITE_MSTR_IND = 1
                                                ORDER BY
	                                                cd.CTRL_DEVC_IDEN ASC
	                                      " />
        <entry key="ControlledPollutants" value="SELECT
	                                                cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                cdp.E_POLLUTANT_CD AS CTRL_POLLUTANT,
	                                                p.POLLUTANT_DESC AS CTRL_POLLUTANT_DESC,
	                                                cdp.PCT_CTRL_MEAS_REDC_EFCY AS PCT_REDC_EFCY
                                                FROM
	                                                M_CTRL_DEVC_POLLUTANT cdp 
	                                                INNER JOIN M_CTRL_DEVC cd ON cdp.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                                INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	
	                                                LEFT JOIN E_POLLUTANT p ON cdp.E_POLLUTANT_CD = p.E_POLLUTANT_CD
                                                WHERE
	                                                fs.FAC_SITE_ID = :facilityId --current facility
	                                                AND fs.FAC_SITE_MSTR_IND = 1
                                                ORDER BY
	                                                cd.CTRL_DEVC_IDEN ASC,
	                                                p.E_POLLUTANT_CD ASC


                                                " />
        <entry key="AdditionalInformation" value="SELECT
	                                                  cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                  cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                  cf.FLD_LABEL AS FLD_NAME,
	                                                  cfv.CSTM_FLD_VAL AS FLD_VALUE
                                                  FROM
	                                                  M_CTRL_DEVC_CSTM_FLD_VAL cfv 
	                                                  INNER JOIN A_CSTM_FLD cf ON cfv.A_CSTM_FLD_ID = cf.A_CSTM_FLD_ID
	                                                  INNER JOIN M_CTRL_DEVC cd ON cfv.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                                  INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
                                                  WHERE
	                                                  fs.FAC_SITE_ID = :facilityId --current facility
	                                                  AND fs.FAC_SITE_MSTR_IND = 1
                                                  ORDER BY
	                                                  cd.CTRL_DEVC_IDEN ASC,
	                                                  cf.FLD_LABEL ASC
	                                                  " />
        <entry key="Identifiers" value="SELECT
	                                        cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                        cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                        cdi.CTRL_DEVC_IDEN AS ALT_CTRL_DEVC_IDEN,
	                                        cdi.A_PROG_SYS_CD AS ALT_CTRL_DEVC_IDEN_SYS_CD,
	                                        cdi.EFF_DATE AS ALT_CTRL_DEVC_IDEN_EFF_DATE
                                        FROM
	                                        M_CTRL_DEVC_IDEN cdi
	                                        INNER JOIN M_CTRL_DEVC cd ON cdi.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                        INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
                                        WHERE
	                                        fs.FAC_SITE_ID = :facilityId --current facility
	                                        AND fs.FAC_SITE_MSTR_IND = 1
                                        ORDER BY
	                                        cd.CTRL_DEVC_IDEN ASC
	
	                                                  " />
        <entry key="Comments" value="SELECT
	                                    cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                    cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                    cdc.CMNT_TXT,
	                                    CASE 
		                                    WHEN cdc.CMNT_BY_USER_ID IS NULL THEN 'Agency Editor'
		                                    ELSE s.USER_FULL_NAME
	                                    END AS CMNT_BY,
	                                    cdc.CMNT_DATE AS CMNT_DATE
                                    FROM
	                                    M_CTRL_DEVC_CMNT cdc
	                                    INNER JOIN M_CTRL_DEVC cd ON cdc.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                    INNER JOIN M_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	
	                                    LEFT JOIN SEC_USER s ON cdc.CMNT_BY_USER_ID = s.SEC_USER_ID
                                    WHERE
	                                    fs.FAC_SITE_ID = :facilityId --current facility
	                                    AND fs.FAC_SITE_MSTR_IND = 1
                                    ORDER BY
	                                    cd.CTRL_DEVC_IDEN ASC
	                                                  " />
      </dictionary>
    </property>
  </object>
  <object id="PublicControlDeviceExport" type="Sleis.Export.ExportGroup">
    <property name="Name" value="Public_ControlDevice">
    </property>
    <property name="Queries">
      <dictionary key-type="System.String" value-type="System.String">
        <entry key="ControlDevice" value="SELECT
	                                                cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                cds.CTRL_DEVC_STAT_DESC AS CTRL_DEVC_STATUS,
	                                                cd.CTRL_DEVC_STAT_CD_DATE AS CTRL_DEVC_STATUS_DATE,
	                                                cm.CTRL_MEAS_DESC AS CTRL_MEAS_DESC,
	                                                cd.CTRL_DEVC_CMNT AS CTRL_DEVC_CMNT
                                                FROM
	                                                P_CTRL_DEVC cd 
	                                                INNER JOIN P_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	                                                INNER JOIN P_FAC_EMIS_RPT fer ON fs.FAC_EMIS_RPT_ID = fer.FAC_EMIS_RPT_ID
	                                                INNER JOIN M_AGN_RPT ar ON fer.AGN_RPT_ID = ar.AGN_RPT_ID
	
	                                                LEFT JOIN A_CTRL_DEVC_STAT cds ON cd.A_CTRL_DEVC_STAT_CD = cds.A_CTRL_DEVC_STAT_CD
	                                                LEFT JOIN E_CTRL_MEAS cm ON cd.E_CTRL_MEAS_CD = cm.E_CTRL_MEAS_CD
                                                WHERE
	                                                ar.AGN_RPT_ID = :reportId --current report being reported on
                                                ORDER BY
	                                                cd.CTRL_DEVC_IDEN ASC
	
	                                      " />
        <entry key="ControlledPollutants" value="SELECT
	                                                cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                cdp.E_POLLUTANT_CD AS CTRL_POLLUTANT,
	                                                p.POLLUTANT_DESC AS CTRL_POLLUTANT_DESC,
	                                                cdp.PCT_CTRL_MEAS_REDC_EFCY AS PCT_REDC_EFCY
                                                FROM
	                                                P_CTRL_DEVC_POLLUTANT cdp 
	                                                INNER JOIN P_CTRL_DEVC cd ON cdp.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                                INNER JOIN P_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	                                                INNER JOIN P_FAC_EMIS_RPT fer ON fs.FAC_EMIS_RPT_ID = fer.FAC_EMIS_RPT_ID
	                                                INNER JOIN M_AGN_RPT ar ON fer.AGN_RPT_ID = ar.AGN_RPT_ID
	
	                                                LEFT JOIN E_POLLUTANT p ON cdp.E_POLLUTANT_CD = p.E_POLLUTANT_CD
                                                WHERE
	                                                ar.AGN_RPT_ID = :reportId --current report being reported on
                                                ORDER BY
	                                                cd.CTRL_DEVC_IDEN ASC,
	                                                p.E_POLLUTANT_CD ASC

                                                " />
        <entry key="AdditionalInformation" value="SELECT
	                                                  cd.CTRL_DEVC_IDEN AS CTRL_DEVC_IDEN,
	                                                  cd.CTRL_DEVC_DESC AS CTRL_DEVC_DESC,
	                                                  cf.FLD_LABEL AS FLD_NAME,
	                                                  cfv.CSTM_FLD_VAL AS FLD_VALUE
                                                  FROM
	                                                  P_CTRL_DEVC_CSTM_FLD_VAL cfv 
	                                                  INNER JOIN A_CSTM_FLD cf ON cfv.A_CSTM_FLD_ID = cf.A_CSTM_FLD_ID
	                                                  INNER JOIN P_CTRL_DEVC cd ON cfv.CTRL_DEVC_ID = cd.CTRL_DEVC_ID
	                                                  INNER JOIN P_FAC_SITE fs ON cd.FAC_SITE_ID = fs.FAC_SITE_ID
	                                                  INNER JOIN P_FAC_EMIS_RPT fer ON fs.FAC_EMIS_RPT_ID = fer.FAC_EMIS_RPT_ID
	                                                  INNER JOIN M_AGN_RPT ar ON fer.AGN_RPT_ID = ar.AGN_RPT_ID
                                                  WHERE
	                                                  ar.AGN_RPT_ID = :reportId --current report being reported on
                                                  ORDER BY
	                                                  cd.CTRL_DEVC_IDEN ASC,
	                                                  cf.FLD_LABEL ASC" />
      </dictionary>
    </property>
  </object>
</objects>
