package bo;

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

import myjdbc.*;
import struts.actionform.*;
import util.*;

public class OrderBo {
    private static OrderBo bo = null;
    private static DatabaseOperation db = null;
    private OrderBo() {
        db = DatabaseOperation.getInstance();
    }

    public static OrderBo getInstance() {
        if (bo == null) {
            return new OrderBo();
        } else {
            return bo;
        }
    }
    /**
     * 獲的指定狀態的訂單列表
     * @param form ManagedOrderForm
     */
    public void getOrderList(ManagedOrderForm form) {
        Integer status = form.getOrderStatus();
        if (status == null) {
            status = new Integer(0);
        }
        String sql =
                "select b.name,a.ordertime,sum(d.price * c.quantity) as totalprice,a.orderid " +
                "from orders as a ,users as b,orderitem as c,products as d " +
                "where a.userid = b.userid and c.orderid = a.orderid and c.productid = d.productid " +
                "and a.orderstatus = " + status.toString() +
                " group by a.orderid,b.name,a.ordertime";
        List list = db.executeSQL(sql);
        List orderList = new ArrayList();
        for (int i = 0; i < list.size(); i++) {
            Object[] obj = (Object[]) list.get(i);
            OrderListVO vo = new OrderListVO();
            vo.setUserName((String) obj[0]);
            vo.setOrderTime(Util.date2String((Timestamp) obj[1]));
            vo.setTotalPrice((Double) obj[2]);
            vo.setOrderId((String) obj[3]);
            orderList.add(vo);
        }
        form.setOrderList(orderList);
    }
    /**
     * 更新訂單狀態位
     * @param form ManagedOrderForm
     */
    public void updateOrderStatus(ManagedOrderForm form) {
        db.executeSQL("update orders set orderstatus = " + form.getUpdateStatus() +
                      " where orderid = '" + form.getOrderId() + "'");
    }

    /**
     * 統計
     *
     * @param form StatisticForm
     */
    public void statisticOrder(StatisticForm form) {

        String sql =
                "select c.name,sum(b.quantity) as total,c.price * sum(b.quantity) as totalprice " +
                "from orders as a ,orderitem as b,products as c " +
                "where b.orderid = a.orderid and b.productid = c.productid " +
                "and a.ordertime < cast('"+form.getTimeTo()+" 23:59:59" +"' as datetime) " +
                "and a.ordertime > cast('"+form.getTimeFrom()+" 0:0:0"+"' as datetime) " +
                "and a.orderstatus = 1 group by c.name,c.price";
            System.out.println(sql);
        List list = db.executeSQL(sql);
        ArrayList statisticVOList = new ArrayList();
        for (int i = 0; i < list.size(); i++) {
            Object[] obj = (Object[]) list.get(i);
            StatisticVO vo = new StatisticVO();
            vo.setProductName((String) obj[0]);
            vo.setQuantity((Integer) obj[1]);
            vo.setTotalPrice((Double) obj[2]);
            statisticVOList.add(vo);
        }
        form.setStatisticVOList(statisticVOList);

    }

}