<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="params">

    <typeAlias alias="Integer" type="java.lang.Integer"/>
    <typeAlias alias="String" type="java.lang.String" />
    <typeAlias alias="Map" type="java.util.Map" />
    <typeAlias alias="Changes" type="data.domains.ChangedParams" />


    <resultMap id="ErrorMap" class="data.domains.Error">
        <result property="error" column="error"/>
        <result property="sent" column="sent" nullValue="0"/>
    </resultMap>

    <resultMap id="DeviceParam" class="data.domains.DeviceParam">
           <result property="deviceParamId" column="id" nullValue="0" />
           <result property="serialNumber" column="serial_number"/>
           <result property="parameter" column="param_id" select="params.selectParameter"/>
           <result property="paramValue" column="param_value"/>
           <result property="creationDate" column="creation_date"/>
    </resultMap>


    <resultMap id="ChangedParamsMap" class="Changes">
        <result property="id" column="id" nullValue="0"/>
        <result property="deviceParamId" column="device_param_id" nullValue="0"/>
        <result property="paramValue" column="param_value" />
        <result property="changedDate" column="change_time" />
        <result property="applied" column="applied"  nullValue="0"/>
    </resultMap>

    <resultMap id="ChangedParamsPartMap" class="Changes">
        <result property="id" column="id" nullValue="0"/>
        <result property="paramValue" column="param_value" />
        <result property="changedDate" column="change_time" />
        <result property="applied" column="applied"  nullValue="0"/>
    </resultMap>

    <resultMap id="AppliedForChanges" class="Changes">
        <result property="paramValue" column="param_value" />
        <result property="applied" column="applied"  nullValue="0"/>
    </resultMap>

    <resultMap id="parameter" class="data.domains.Parameter">
        <result property="id" column="id"/>
        <result property="paramName"  column="name" />
        <result property="defaultValue"  column="default_value"/>
        <result property="visualName" column="visual_name"/>
        <result property="writable" column="writable" nullValue="0"/>
        <result property="visibleForAdmin"  column="isvisible_for_admin" nullValue="0"/>
        <result property="visibleForOperator"  column="isvisible_for_operator" nullValue="0"/>
        <result property="defparam"  column="isdefparam" nullValue="0"/>
        <result property="modelId" column="model_id" nullValue="0"/>
        <result property="paramType" column="param_type"/>
        <result property="lanParam" column="is_lan" nullValue="0"/>

    </resultMap>

    <select id="getSomeParameterValue" parameterClass="Map" resultClass="String">

        Select dp.param_value from device_param dp,parameter p,device d
                where dp.serial_number=#serial# and dp.param_id=p.id and p.name like concat('%',#paramName#)  and p.model_id=d.model_id and d.serial_number=#serial#
    </select>

    <select id="getLastChangeForThisParam" parameterClass="data.domains.DeviceParam" resultMap="params.ChangedParamsPartMap">
          SELECT *
              FROM history
                  WHERE device_param_id = #DeviceParamId# AND change_time in (SELECT MAX(change_time)
                                                                                              FROM history h
                                                                                               WHERE h.device_param_id = #DeviceParamId#   GROUP BY h.device_param_id) limit 1 offset 0
    </select>

    <select id="selectParameter" parameterClass="long" resultMap="params.parameter">
          SELECT * from parameter where id = #param_id#
    </select>

    <select id="selectAllChangedParams"  resultMap="params.ChangedParamsMap" parameterClass="string" >
            SELECT *
                 FROM history h, device_param dp
                 WHERE h.device_param_id = dp.id AND dp.serial_number=#serial#
    </select>

    <select id="selectAllChangesForThisParam"  resultMap="params.ChangedParamsPartMap" parameterClass="Map" >
             SELECT *
                  FROM history h, parameter p, device_param dp
                     WHERE dp.param_id = p.id AND dp.id = h.device_param_id AND dp.serial_number=#serial# AND p.id = #paramId#
    </select>

     <select id="selectParamIdBySerialandParamName" parameterClass="Map" resultMap="params.DeviceParam">
          SELECT *
            FROM device_param dp, parameter p
                WHERE  dp.param_id = p.id AND  serial_number = #serial# AND p.visual_name =#paramName#
      </select>

     <select id="selectParamIdBySerialandParamId" parameterClass="Map" resultMap="params.DeviceParam">
          SELECT *
            FROM device_param
                WHERE  param_id = #id#  AND  serial_number = #serial#
      </select>

      <select id="getParameterListByModel" resultMap="params.parameter" parameterClass="Integer">
          SELECT * from parameter WHERE model_id = #modelId#
      </select>

      <select id="getAllParamsByModelId" parameterClass="long" resultMap="params.parameter">
             SELECT * from   parameter WHERE model_id = #selectedModel#
      </select>


       <!--<select id="getChangesByParamName" parameterClass="Map" resultClass="Changes">-->
          <!--SELECT *-->
               <!--FROM history-->
                      <!--WHERE param_name=#paramName# AND serial_number=#serial#-->
      <!--</select>-->

    <select id="getAppliedForParamChanges" parameterClass="Map" resultMap="params.ChangedParamsMap">
             SELECT h.*
                  FROM device_param d , parameter p ,history h
                     WHERE d.serial_number=#serial#
                        AND p.visual_name=#paramName#
                        AND p.id=d.param_id
                        AND h.device_param_id=d.id
                        AND h.change_time=(Select max(h1.change_time) from history h1 where h1.device_param_id=d.id) limit 1 offset 0
    </select>

    <select id="getErrorMsgByChangeId" parameterClass="long"  resultMap="params.ErrorMap">
         SELECT *
                from error where history_id=#changeId#
    </select>

    <select id="getNotAppliedChanges" parameterClass="Changes" resultMap="params.ChangedParamsMap">
        SELECT *
            FROM history
                        WHERE device_param_id=#deviceParamId# and change_time=#changedDate# AND applied=0
    </select>

        <select id="selectDeviceparamsBySerialNumberForAdmin" resultMap="params.DeviceParam" parameterClass="String">
        SELECT *
            FROM device_param dp, parameter p
                WHERE dp.serial_number = #serial# AND dp.param_id=p.id AND p.isvisible_for_admin=1
    </select>

    <select id="selectDeviceparamsBySerialNumberForOperator" resultMap="params.DeviceParam" parameterClass="String">
      SELECT *
            FROM device_param dp, parameter p
                WHERE dp.serial_number = #serial# AND dp.param_id=p.id AND p.isvisible_for_operator=1
    </select>

   <select id="getDeviceparamsBySerialNumberDefaults" resultMap="params.DeviceParam" parameterClass="String">
      SELECT *
            FROM device_param dp, parameter p
                WHERE dp.serial_number = #serial# AND dp.param_id=p.id AND p.isdefparam=1
   </select>

   <!--<select id="getDefValueOfParam" resultClass="String" parameterClass="Map">-->
            <!--SELECT p.default_value from parameter p, device_param dp-->
	                                    <!--where p.visual_name=#paramName#-->
	                                                <!--AND  p.id=dp.param_id AND dp.serial_number=#serial#-->
	                                                <!--AND p.model_id=(SELECT model_id from device where serial_number=#serial#)-->
   <!--</select>-->

  <select id="getDefValueOfParam" resultClass="String" parameterClass="Map">
            SELECT p.default_value from parameter p, device_param dp
	                                    where p.id=#id#
	                                                AND  p.id=dp.param_id AND dp.serial_number=#serial#
	                                                AND p.model_id=(SELECT model_id from device where serial_number=#serial#)
   </select>

   <select id="getIntervalBySerial"  resultClass="String" parameterClass="String">
      SELECT *
            FROM device_param dp, parameter p
                WHERE dp.serial_number = #serial# AND dp.param_id=p.id AND p.name="InternetGatewayDevice.ManagementServer.PeriodicInformInterval"

   </select>

   <select id="getParamTypeById" resultClass="String" parameterClass="long">
        SELECT param_type from parameter WHERE id=#paramId#
   </select>

  <select id="getNotAppliedChangeForParam" resultMap="params.ChangedParamsMap" parameterClass="long">
      Select * from history where device_param_id=#deviceParamId# and applied=0
  </select>

   <insert id="addSendedErrorMsg" parameterClass="data.domains.Error">
          INSERT
              INTO error(id,history_id,sent)
                  VALUES(#id#,#changeId#,#sent#)

          <selectKey resultClass="long" keyProperty="id">   <!--type="post" kam "pre"-->
                SELECT LAST_INSERT_ID() AS ID
          </selectKey>
    </insert>

    <insert id="addNewParamInTmp"  parameterClass="Changes">
          INSERT
              INTO history(id,device_param_id,param_value,change_time,applied)
                  VALUES(#id#,#deviceParamId#,#paramValue#,#changedDate#,#applied#)

          <selectKey resultClass="long" keyProperty="id">   <!--type="post" kam "pre"-->
                SELECT LAST_INSERT_ID() AS ID
          </selectKey>
    </insert>

    <update id="setParamDefaultForModel"  parameterClass="long">
          UPDATE parameter
                  SET isdefparam=1
                  WHERE id=#parameter#
    </update>

     <update id="setParamDefaultValueForModel"  parameterClass="Map">
          UPDATE parameter
                  SET isdefparam=1,
                      default_value=#paramValue#
                  WHERE id=#paramId#
    </update>



    <update id="setParamNotDefaultForModel"  parameterClass="long">
          UPDATE parameter
                  SET isdefparam=0
                  WHERE id=#parameter#
    </update>

    <update id="setVisibleForAdmin"  parameterClass="long">
          UPDATE parameter
                  SET isvisible_for_admin=1
                  WHERE id=#param_id#
    </update>
    <update id="setNotVisibleForAdmin"  parameterClass="long">
          UPDATE parameter
                  SET isvisible_for_admin=0
                  WHERE id=#param_id#
    </update>
    <update id="setVisibleForOperator"  parameterClass="long">
          UPDATE parameter
                  SET isvisible_for_operator=1
                  WHERE id=#param_id#
    </update>
      <update id="setNotVisibleForOperator"  parameterClass="long">
          UPDATE parameter
                  SET isvisible_for_operator=0
                  WHERE id=#param_id#
      </update>

    <update id="setErrorMsgSended" parameterClass="long">
        UPDATE error SET sent=1 where history_id=#changesId#
    </update>

    <update id="updateDeviceParam" parameterClass="Map">
          UPDATE device_param
                  SET param_value=#newValue#
                  WHERE id=#deviceParamId#
    </update>
</sqlMap>