package com.shopping.dao;

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

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

public class GoodsDao {

    public int add(GenericVO gvo) throws SQLException {
        String name = gvo.getItemStr("NAME");
        String brandId = gvo.getItemStr("BRAND_ID");
        String goodsTypeId = gvo.getItemStr("GOODSTYPE_ID");
        String goodsTypeId2 = gvo.getItemStr("GOODSTYPE_ID2");
        String photo = gvo.getItemStr("PHOTO");
        String sphoto = gvo.getItemStr("SPHOTO");
        String price1 = gvo.getItemStr("PRICE1");
        String price2 = gvo.getItemStr("PRICE2");
        String dot = gvo.getItemStr("DOT");
        String weight = gvo.getItemStr("WEIGHT");
        String kucun = gvo.getItemStr("KUCUN");
        String description = gvo.getItemStr("DESCRIPTION");
        Timestamp createtime = new Timestamp(System.currentTimeMillis());

        Vector param = new Vector();
        String sql = "INSERT INTO " +
                     "goods (name, brand_id, goodstype_id, goodstype_id2, photo, sphoto, price1, " +
                     "price2, dot, weight, kucun, description, createtime, status) " +
                     "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)";
        param.addElement(name);
        param.addElement(brandId);
        param.addElement(goodsTypeId);
        param.addElement(goodsTypeId2);
        param.addElement(photo);
        param.addElement(sphoto);
        param.addElement(price1);
        param.addElement(price2);
        param.addElement(dot);
        param.addElement(weight);
        param.addElement(kucun);
        param.addElement(description);
        param.addElement(createtime);

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

        int goodsID = -1;
        sql = "SELECT goods_id FROM goods WHERE name=? AND createtime=?";
        param.clear();
        param.add(name);
        param.add(createtime);

        Vector tv = DBFactory.getDBI().getResult(sql, param);
        if (tv.size() > 0) {
            goodsID = BaseUtil.toInt(((GenericVO) tv.elementAt(0)).getItem(
                    "GOODS_ID"));
        }
        return goodsID;
    }

    public void modify(GenericVO gvo) throws SQLException {
        String goodsId = gvo.getItemStr("GOODS_ID");
        String name = gvo.getItemStr("NAME");
        String brandId = gvo.getItemStr("BRAND_ID");
        String goodsTypeId = gvo.getItemStr("GOODSTYPE_ID");
        String goodsTypeId2 = gvo.getItemStr("GOODSTYPE_ID2");
        String photo = gvo.getItemStr("PHOTO");
        String sphoto = gvo.getItemStr("SPHOTO");
        String price1 = gvo.getItemStr("PRICE1");
        String price2 = gvo.getItemStr("PRICE2");
        String dot = gvo.getItemStr("DOT");
        String weight = gvo.getItemStr("WEIGHT");
        String kucun = gvo.getItemStr("KUCUN");
        String description = gvo.getItemStr("DESCRIPTION");

        Vector param = new Vector();
        String sql = "UPDATE goods SET " +
                     "name=?, brand_id=?, goodstype_id=?, goodstype_id2=?, price1=?, " +
                     "price2=?, dot=?, weight=?, kucun=?, description=? ";
        param.addElement(name);
        param.addElement(brandId);
        param.addElement(goodsTypeId);
        param.addElement(goodsTypeId2);
        param.addElement(price1);
        param.addElement(price2);
        param.addElement(dot);
        param.addElement(weight);
        param.addElement(kucun);
        param.addElement(description);
        if (photo.length() > 0) {
            sql += ", photo=?, sphoto=? ";
            param.addElement(photo);
            param.addElement(sphoto);
        }
        sql += "WHERE goods_id=?";
        param.addElement(goodsId);

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

    public void delete(int goodsId) throws SQLException {
        String sql = "DELETE FROM goods WHERE goods_id=" + goodsId;
        DBFactory.getDBI().execute(sql);
    }

    public GenericVO getDetail(int goodsId) throws SQLException {
        String sql = "SELECT g.*, b.logo2, b.logo1, b.name AS brandname, gt.typename, gt2.typename AS typename2 " +
                     "FROM goods g, brand b, goodstype gt, goodstype gt2 " +
                     "WHERE g.goodstype_id=gt.goodstype_id AND g.goodstype_id2=gt2.goodstype_id " +
                     "AND g.brand_id=b.brand_id AND g.goods_id=" + goodsId;
        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 goodsTypeId1 = gvo.getItemStr("GOODSTYPE_ID");
        String goodsTypeId2 = gvo.getItemStr("GOODSTYPE_ID2");
        String brandId = gvo.getItemStr("BRAND_ID");

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

        sb.append(
                "SELECT g.*, gt.typename, gt2.typename AS typename2, b.logo1 " +
                "FROM goods g, goodstype gt, goodstype gt2, brand b " +
                "WHERE g.goodstype_id=gt.goodstype_id AND g.goodstype_id2=gt2.goodstype_id " +
                "AND g.brand_id=b.brand_id ");
        if (goodsTypeId1.length() > 0) {
            sb.append(" AND g.goodstype_id = ? ");
            param.addElement(new Integer(goodsTypeId1));
        }
        if (goodsTypeId2.length() > 0) {
            sb.append(" AND g.goodstype_id2 = ? ");
            param.addElement(new Integer(goodsTypeId2));
        }
        if (brandId.length() > 0) {
            sb.append(" AND g.brand_id = ? ");
            param.addElement(brandId);
        }
        sb.append(" ORDER BY g.goods_id DESC");
        return DBFactory.getDBI().getResult(sb.toString(), param,
                                            gvo.getListVO());
    }

    //增加相關商品
    public void addOOGoods(GenericVO gvo) throws SQLException {
        String goodsId = gvo.getItemStr("GOODS_ID");
        String relateGoodsId = gvo.getItemStr("RELATEGOODS_ID");
        String sql =
                "INSERT INTO relategoods (goods_id, relategoods_id) VALUES (?, ?)";
        Vector param = new Vector();
        param.addElement(goodsId);
        param.addElement(relateGoodsId);

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

    public void deleteOOGoods(int id) throws SQLException {
        String sql = "DELETE FROM relategoods WHERE id = " + id;

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

    //得到相關商品
    public Vector getRelateGoods(int goodsId) throws SQLException {
        String sql =
                "SELECT g.*, b.logo1, b.logo2, r.id FROM relategoods r, goods g, brand b " +
                "WHERE g.goods_id=r.relategoods_id AND g.brand_id=b.brand_id AND r.goods_id=" +
                goodsId;
        return DBFactory.getDBI().getResult(sql);
    }

    //根據二級分類取得品牌
    public Vector getBrandByGoodsTypeId(int goodsTypeId) throws SQLException {
        String sql = "SELECT distinct(b.name) AS name, b.brand_id FROM goods g, brand b WHERE g.goodstype_id2=" +
                     goodsTypeId + " AND g.brand_id=b.brand_id ORDER BY name";
        return DBFactory.getDBI().getResult(sql);
    }

    //更新商品人氣
    public void updateGoodsPv(int goodsId, int pv) throws SQLException {
        String sql = "UPDATE goods SET pv=pv+" + pv + " WHERE goods_id=" +
                     goodsId;
        DBFactory.getDBI().execute(sql);
    }
}