<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.pws.ubiquity.pr.domain" default-access="field">
   <class name="RedemptionTx" table="PR_REDM_HEDR">
      <id name="id" column="REDM_TRNS_ID">
         <generator class="assigned"/>
      </id>
        <component name="audit" class="com.pws.ubiquity.common.domain.Audit">
         <property name="createdBy"  column="CRTD_BY"/>
         <property name="createTime" column="CRTD_DTTM"/>
         <property name="modifiedBy" column="LAST_CHNG_BY"/>
         <property name="modifyTime" column="LAST_CHNG_DTTM"/>
      </component>
      <component name="address" class="com.pws.ubiquity.common.domain.Address">
         <property name="address1"    column="ADDR_1"/>
         <property name="address2"    column="ADDR_2"/>
         <property name="city"       column="CITY"/>
         <property name="zipCode"    column="ZIP_CODE"/>
      </component>       
      <property name="country"          column="CNTY_CODE"/>
      <property name="customer"          column="CUST_SQNM"/>   
        <many-to-one name="customerLink"    column="CUST_SQNM" insert="false" update="false" />

      <bag name="details" table="PR_REDM_DETL"  order-by="REDM_DETL_SQNM asc" inverse="true">
         <key column="REDM_TRNS_ID"/>
         <one-to-many class="RedemptionDetail"/>
      </bag>

      <property name="email"                column="EMAL"/>   
      <property name="requestDate"          column="REQS_DTTM"/>   
      <property name="state"                column="STTE_CODE"/>   
      <property name="telephoneNumber"       column="TELP_NUMB"/>   
      <property name="totalRedemptCharge"    column="TOTL_REDM_CHRG"/>
      <property name="totalRedemptPoint"     column="TOTL_REDM_PONT"/>
        <property name="totalRedemptQuantity"    column="TOTL_REDM_QNTY"/>
      <property name="txDate"              column="TRNS_DATE"/>      
      <property name="txType"             column="TRNS_TYPE"/>
        <many-to-one name="txTypeLink"          column="TRNS_TYPE" insert="false" update="false"/>   
   </class>
</hibernate-mapping>

   public List getRedemptionTxes(Object handle, RedemptionTxCriteria criteria, Integer maxResult) {
      if ((maxResult != null) && (maxResult.intValue() == 0)) {
         return new LinkedList();
      }
      
      Session session = (Session) handle;
      Criteria redemptionTx = session.createCriteria(RedemptionTx.class,AL_REDEMPTION);
      Criteria redemptionDetail = redemptionTx.createCriteria(RedemptionTx.PROP_DETAILS,AL_REDEMPTIONDETAIL);
      //criteria = new RedemptionTxCriteria();
      //criteria.setId("1%");
      getRedemptionTxExpression(criteria, redemptionTx, redemptionDetail);

      redemptionTx.createAlias(RedemptionTx.PROP_TX_TYPE_LINK, RedemptionTx.PROP_TX_TYPE_LINK, Criteria.LEFT_JOIN);
      redemptionTx.createAlias(RedemptionTx.PROP_CUSTOMER_LINK, RedemptionTx.PROP_CUSTOMER_LINK, Criteria.LEFT_JOIN);
      redemptionDetail.createAlias(RedemptionDetail.PROP_SUPPLIER_LINK, RedemptionDetail.PROP_SUPPLIER_LINK, Criteria.LEFT_JOIN);
      redemptionDetail.createAlias(RedemptionDetail.PROP_REWARD_LINK, RedemptionDetail.PROP_REWARD_LINK, Criteria.LEFT_JOIN);

      // Set attribute to report query.
      redemptionTx.setProjection(Projections.projectionList()
         .add(Property.forName(AL_REDEMPTION+"."+RedemptionTx.PROP_REQUEST_DATE), RedemptionDAO.REQUEST_DATE)         
         .add(Property.forName(AL_REDEMPTION+"."+RedemptionTx.PROP_ID), RedemptionDAO.ORDER_NUMBER)
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER), RedemptionDAO.CUSTOMER)
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_SALUTATION), RedemptionDAO.CUSTOMER_SALUTATION)
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_FIRST_NAME), RedemptionDAO.CUSTOMER_FIRSTNAME)
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_MIDDLE_NAME), RedemptionDAO.CUSTOMER_MIDDLENAME)
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_LAST_NAME), RedemptionDAO.CUSTOMER_LASTNAME)
         .add(Property.forName(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_REWARD), RedemptionDAO.REWARD)
         .add(Property.forName(RedemptionDetail.PROP_REWARD_LINK).getProperty(Reward.PROP_NAME), RedemptionDAO.REWARD_NAME)
         .add(Property.forName(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_SUPPLIER), RedemptionDAO.SUPPLIER)
         .add(Property.forName(RedemptionDetail.PROP_SUPPLIER_LINK).getProperty(Supplier.PROP_NAME), RedemptionDAO.SUPPLIER_NAME)
         .add(Property.forName(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_STATUS), RedemptionDAO.STATUS)

         // Add sum projection.
         .add(Projections.sum(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_REDEMPTION_QUANTITY), RedemptionDAO.QUANTITY)

         // Add grouping.   
         .add(Projections.groupProperty(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_REWARD))
         .add(Property.forName(RedemptionDetail.PROP_REWARD_LINK).getProperty(Reward.PROP_NAME).group())
         .add(Projections.groupProperty(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_SUPPLIER))
         .add(Property.forName(RedemptionDetail.PROP_SUPPLIER_LINK).getProperty(Supplier.PROP_NAME).group())
         .add(Projections.groupProperty(AL_REDEMPTION+"."+RedemptionTx.PROP_REQUEST_DATE))
         .add(Projections.groupProperty(AL_REDEMPTION+"."+RedemptionTx.PROP_ID))
         .add(Projections.groupProperty(AL_REDEMPTION+"."+RedemptionTx.PROP_CUSTOMER))
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_SALUTATION).group())
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_FIRST_NAME).group())
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_MIDDLE_NAME).group())
         .add(Property.forName(RedemptionTx.PROP_CUSTOMER_LINK).getProperty(Customer.PROP_LAST_NAME).group())
         .add(Projections.groupProperty(AL_REDEMPTIONDETAIL+"."+RedemptionDetail.PROP_STATUS))
      );
      
      redemptionTx.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
      
      // Set ascending ordering on transaction date, id and reward.
      redemptionTx.addOrder(Property.forName(RedemptionTx.PROP_REQUEST_DATE).asc());
      redemptionTx.addOrder(Property.forName(RedemptionTx.PROP_ID).asc());
      redemptionDetail.addOrder(Property.forName(RedemptionDetail.PROP_REWARD).asc());
      redemptionDetail.addOrder(Property.forName(RedemptionDetail.PROP_STATUS).asc());

      if (maxResult != null){
         redemptionTx.setMaxResults(maxResult.intValue());
      }
      
      // Execute query and return result
      List result = (List) redemptionTx.list();
      if (result == null) {
         return new LinkedList();
      }
      
      return result;
   }