<?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="Order">

  <typeAlias alias="order" type="com.ibatis.jpetstore.domain.Order"/>

  <statement id="getOrder" resultClass="order" parameterClass="int">
    select
      BILLADDR1 AS billAddress1,
      BILLADDR2 AS billAddress2,
      BILLCITY,
      BILLCOUNTRY,
      BILLSTATE,
      BILLTOFIRSTNAME,
      BILLTOLASTNAME,
      BILLZIP,
      SHIPADDR1 AS shipAddress1,
      SHIPADDR2 AS shipAddress2,
      SHIPCITY,
      SHIPCOUNTRY,
      SHIPSTATE,
      SHIPTOFIRSTNAME,
      SHIPTOLASTNAME,
      SHIPZIP,
      CARDTYPE,
      COURIER,
      CREDITCARD,
      EXPRDATE AS expiryDate,
      LOCALE,
      ORDERDATE,
      ORDERS.ORDERID,
      TOTALPRICE,
      USERID AS username,
      STATUS
    FROM ORDERS, ORDERSTATUS
    WHERE ORDERS.ORDERID = #orderId#
      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID
  </statement>

  <select id="getOrdersByUsername" resultClass="order" parameterClass="string">
    SELECT
      BILLADDR1 AS billAddress1,
      BILLADDR2 AS billAddress2,
      BILLCITY,
      BILLCOUNTRY,
      BILLSTATE,
      BILLTOFIRSTNAME,
      BILLTOLASTNAME,
      BILLZIP,
      SHIPADDR1 AS shipAddress1,
      SHIPADDR2 AS shipAddress2,
      SHIPCITY,
      SHIPCOUNTRY,
      SHIPSTATE,
      SHIPTOFIRSTNAME,
      SHIPTOLASTNAME,
      SHIPZIP,
      CARDTYPE,
      COURIER,
      CREDITCARD,
      EXPRDATE AS expiryDate,
      LOCALE,
      ORDERDATE,
      ORDERS.ORDERID,
      TOTALPRICE,
      USERID AS username,
      STATUS
    FROM ORDERS, ORDERSTATUS
    WHERE ORDERS.USERID = #value#
      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID
    ORDER BY ORDERDATE
  </select>

  <insert id="insertOrder" parameterClass="order">
    INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE,
      SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY,
      COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME,
      CREDITCARD, EXPRDATE, CARDTYPE, LOCALE)
    VALUES(#orderId#, #username#, #orderDate#, #shipAddress1#, #shipAddress2:VARCHAR#, #shipCity#,
      #shipState#, #shipZip#, #shipCountry#, #billAddress1#, #billAddress2:VARCHAR#, #billCity#,
      #billState#, #billZip#, #billCountry#, #courier#, #totalPrice#, #billToFirstName#, #billToLastName#,
      #shipToFirstName#, #shipToLastName#, #creditCard#, #expiryDate#, #cardType#, #locale#)
  </insert>

  <insert id="insertOrderStatus" parameterClass="order">
    INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)
    VALUES (#orderId:NUMERIC#, #orderId:NUMERIC#, #orderDate:TIMESTAMP#, #status:VARCHAR#)
  </insert>

</sqlMap>
