package com.shopping.dao;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import com.comm.db.*;
import com.comm.util.*;
import com.comm.vo.*;

public class OrderDao {

    public String add(GenericVO gvo) throws SQLException {
        String userId = gvo.getItemStr("USER_ID");
        String no = "DD" +
                    BaseUtil.toString(new Date(System.currentTimeMillis()),
                                      "yyyyMMddHHmmss");
        Timestamp createTime = new Timestamp(System.currentTimeMillis());
        String totalPrice = gvo.getItemStr("TOTALPRICE");

        Vector param = new Vector();
        String sql =
                "INSERT INTO orders (user_id, no, status, createtime, totalPrice) " +
                "VALUES (?, ?, 0, ?, ?)";
        param.addElement(new Integer(userId));
        param.addElement(no);
        param.addElement(createTime);
        param.addElement(totalPrice);
        DBFactory.getDBI().execute(sql, param);
        return no;
    }

    public void update(String no, String field, Object value) throws
            SQLException {
        String sql = "UPDATE orders SET " + field + "=? WHERE no='" + no + "'";
        Vector param = new Vector();
        param.addElement(value);

        DBFactory.getDBI().execute(sql, param);
    }

    public void updateStatus(int orderId, int status) throws SQLException {
        String sql = "UPDATE orders SET status=" + status + " WHERE order_id=" +
                     orderId;

        DBFactory.getDBI().execute(sql);
    }

    public void addOrderGoods(GenericVO gvo) throws SQLException {
        String orderNo = gvo.getItemStr("ORDERNO");
        String goodsId = gvo.getItemStr("GOODS_ID");
        String price = gvo.getItemStr("PRICE");
        String count = gvo.getItemStr("COUNT");

        String sql =
                "INSERT INTO ordergoods (orderno, goods_id, price, count) " +
                "VALUES (?, ?, ?, ?)";
        Vector param = new Vector();
        param.addElement(orderNo);
        param.addElement(goodsId);
        param.addElement(price);
        param.addElement(count);

        DBFactory.getDBI().execute(sql, param);
    }

    public GenericVO getDetail(int orderId) throws SQLException {
        String sql = "SELECT * FROM orders WHERE order_id=" + orderId;
        Vector r = DBFactory.getDBI().getResult(sql);
        if (r.size() > 0) {
            return (GenericVO) r.elementAt(0);
        } else {
            return null;
        }
    }

    public ListVO search(GenericVO gvo) throws SQLException {
        String userId = gvo.getItemStr("USER_ID");
        String status = gvo.getItemStr("STATUS");

        Vector param = new Vector();
        StringBuffer sb = new StringBuffer();

        sb.append(
                "SELECT o.*, a.name FROM orders o, address a WHERE o.address_id=a.address_id ");
        if (userId.length() > 0) {
            sb.append(" AND o.user_id=? ");
            param.addElement(userId);
        }
        if (status.length() > 0) {
            sb.append(" AND o.status = ? ");
            param.addElement(status);
        }
        sb.append(" ORDER BY o.order_id DESC");

        return DBFactory.getDBI().getResult(sb.toString(), param,
                                            gvo.getListVO());
    }


    public GenericVO getDetailByOrderNo(String orderNo) throws SQLException {
        String sql = "SELECT * FROM orders WHERE no='" + orderNo + "'";
        Vector r = DBFactory.getDBI().getResult(sql);
        if (r.size() > 0) {
            return (GenericVO) r.elementAt(0);
        } else {
            return null;
        }
    }

    //根據(jù)訂單號獲取商品
    public Vector getGoodsByOrderNo(String orderNo) throws SQLException {
        String sql = "SELECT g.*, og.count FROM ordergoods og, goods g WHERE og.goods_id=g.goods_id AND og.orderNo='" +
                     orderNo + "'";
        return DBFactory.getDBI().getResult(sql);
    }

    public Vector getOrdersByUserId(int userId) throws SQLException {
        String sql = "SELECT o.*, a.name FROM orders o, address a WHERE o.address_id=a.address_id AND o.user_id=" +
                     userId + " ORDER BY o.order_id DESC LIMIT 0, 20"; ;
        return DBFactory.getDBI().getResult(sql);
    }

    public Vector getOrdersByUserId(int userId, int status, int num) throws
            SQLException {
        String sql = "SELECT o.*, a.name FROM orders o, address a " +
                     "WHERE o.address_id=a.address_id AND o.status=" + status +
                     " AND o.user_id=" + userId +
                     " ORDER BY o.order_id DESC LIMIT 0, " + num;
        return DBFactory.getDBI().getResult(sql);
    }

    //訂單狀態(tài)
    public void addOrderStatus(GenericVO gvo) throws SQLException {
        String orderNo = gvo.getItemStr("ORDERNO");
        String status = gvo.getItemStr("STATUS");
        String statusName = gvo.getItemStr("STATUSNAME");
        String description = gvo.getItemStr("DESCRIPTION");
        Timestamp createtime = new Timestamp(System.currentTimeMillis());

        Vector param = new Vector();
        String sql = "INSERT INTO orderstatus (orderno, status, statusname, description, createtime) " +
                     "VALUES (?, ?, ?, ?, ?)";
        param.addElement(orderNo);
        param.addElement(status);
        param.addElement(statusName);
        param.addElement(description);
        param.addElement(createtime);

        DBFactory.getDBI().execute(sql, param);
    }

    public Vector getOrderStatusByOrderNo(String orderNo) throws SQLException {
        String sql = "SELECT * FROM orderstatus WHERE orderno='" + orderNo +
                     "' ORDER BY orderstatus_id";
        return DBFactory.getDBI().getResult(sql);
    }
}