<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.mig.connectivity.hibernate.Message" table="messages" schema="dbo">
         <meta attribute="implement-equals">true</meta>
      <id name="messageId" type="integer">
         <meta attribute="scope-set">protected</meta>
         <meta attribute="use-in-equals">true</meta>
        <column name="message_id" />
        <generator class="assigned" />
      </id>
      <property name="sourceAddress" type="string">
          <column name="oa" length="30" not-null="true" />
      </property>
      <property name="destinationAddress" type="string">
          <column name="da" length="30" not-null="true" />
      </property>
      <property name="timestamp" type="timestamp">
          <column name="message_timestamp" length="23" not-null="true" />
      </property>
      <property name="outgoingStatus" type="integer">
          <column name="outgoing_status" length="5" not-null="true" />
      </property>
      <property name="sentStatus" type="integer">
          <column name="sent_status" length="5" not-null="true" />
      </property>
      <property name="text" type="string">
          <column name="text" length="2048"/>
      </property>
      <many-to-one name="carrier" lazy="false" class="com.mig.provisioning.db.hibernate.Carrier">
          <column name="service_provider_id" not-null="true" />
      </many-to-one>
      <set name="notificationHistories" lazy="true" inverse="true" order-by="notified_on desc">
          <key>
              <column name="message_id" not-null="true" />
          </key>
          <one-to-many class="com.mig.connectivity.hibernate.MessageNotificationHistory" />
      </set>             
    </class>
      <sql-query name="com.mig.connectivity.hibernate.getMessagesForAddressAndProvider">
       <return alias="message" class="com.mig.connectivity.hibernate.Message">
           <return-property name="messageId" column="message_id"/>
           <return-property name="sourceAddress" column="oa"/>
           <return-property name="destinationAddress" column="da"/>
           <return-property name="timestamp" column="message_timestamp"/>
           <return-property name="outgoingStatus" column="outgoing_status"/>
           <return-property name="sentStatus" column="sent_status"/>
           <return-property name="text" column="text"/>
           <return-property name="carrier" column="service_provider_id"/>
       </return>
       <![CDATA[select message_id, oa, da, message_timestamp, outgoing_status, sent_status, text, service_provider_id
       from messages
       where
         message_timestamp >= :startFrom and
         message_timestamp < :endAt and
         ( oa = :number or da = :number ) and
         virtual_provider_id = :providerId and
        service_provider_id <> 48
       order by message_timestamp desc]]>
    </sql-query>
      <sql-query name="com.mig.connectivity.hibernate.getMessagesForAddress" callable="true">
          <!-- Parameters are:
          @start_report_datetime  datetime,
          @end_report_datetime    datetime,
          @address                varchar(30)
          -->
          { call usermessages_resolvedcarrier( ?, ?, ? ) }
    </sql-query>
      <sql-query name="com.mig.connectivity.hibernate.getHistoryStatus">
       <return alias="message" class="com.mig.connectivity.hibernate.MessageNotificationHistory">
           <return-property name="messageNotificationHistoryId" column="message_notification_historyid"/>
           <return-property name="protocolMessageId" column="protocol_message_id"/>
           <return-property name="notification" column="notification"/>
           <return-property name="notifiedOn" column="notified_on"/>
           <return-property name="additionalInfo" column="additional_info"/>
           <return-property name="doneDate" column="done_date"/>
           <return-property name="messageId" column="message_id"/>
       </return>
       <![CDATA[select message_notification_historyid, protocol_message_id, notification, notified_on, additional_info, done_date, message_id
       from message_notification_history
       where message_id = :messageId
       order by notified_on desc]]>
    </sql-query>
</hibernate-mapping>

  public static List<Message> getMessagesForAddress( Session session,
      Timestamp fromDate, Timestamp toDate, String address )
    throws Exception
  {
    Query q = session.getNamedQuery( "com.mig.connectivity.hibernate.getMessagesForAddress" );
   
    int p = 0;
    q.setTimestamp( p++, fromDate );
    q.setTimestamp( p++, toDate );
    q.setString( p++, address );
   
    List<Object[]> l = q.list();
    ArrayList<Message> _messageList = new ArrayList<Message>( );
   
    for( Object[] _o: l ) {
      Message _newMessage = new Message();
      _newMessage.setMessageId( (( BigDecimal) _o[0]).intValue() );
      _newMessage.setSourceAddress( (String) _o[1] );
      _newMessage.setDestinationAddress( (String) _o[2] );
      _newMessage.setTimestamp( (Timestamp) _o[3] );
      _newMessage.setOutgoingStatus( (( BigDecimal) _o[4]).intValue() );
      _newMessage.setSentStatus( (( BigDecimal) _o[5]).intValue() );
      _newMessage.setText( (String) _o[6] );
      Carrier carrier = CarrierDAO.getCarrier( session, (Integer) _o[7] );
      carrier.getName(); // force loading of properties
      _newMessage.setCarrier( carrier );
      _newMessage.setNotificationHistories(
          new HashSet( MessageDAO.getStatusHistory( session, _newMessage )));
     
      _messageList.add( _newMessage );
    }
   
    return _messageList;   
  }

  private static List<MessageNotificationHistory> getStatusHistory( Session session, Message message ) {
    Query q = session.getNamedQuery( "com.mig.connectivity.hibernate.getHistoryStatus" );
   
    q.setInteger( "messageId", message.getMessageId() );
    return q.list();
  }

session.load( Message, (( BigDecimal) _o[0]).intValue() )