package com.publish.shop.product.dao;

import java.sql.*;
import java.util.ArrayList;
import com.publish.shop.util.dao.SerialNoDAO;
import com.publish.shop.util.db.DbManager;
import com.publish.shop.util.db.DbPool;
import com.publish.shop.util.javabeans.Pager;
import com.publish.shop.util.javabeans.Debug;
import com.publish.shop.util.javabeans.DateTimeUtility;
import com.publish.shop.util.javabeans.DateUtility;
import com.publish.shop.product.javabeans.ProductModel;
import org.apache.struts.util.LabelValueBean;
import com.publish.shop.product.javabeans.CategoryProxy;
import com.publish.shop.product.javabeans.CategoryModel;

public class ProductDAO {
  public ProductDAO() {
  }

  public void insert(ProductModel model) throws Exception{
    String sql = "insert into ProductInfo_table(catId,productName,productContent,productDesc,listPrice,unitPrice,orderDesc,productImgUrl,productCount)"
                 +" values(?,?,?,?,?,?,?,?,?)";
    Connection con = null;
    PreparedStatement stmt = null;
    try{
      con = DbPool.getConnection();
      stmt = con.prepareStatement(sql);
//        int userId = SerialNoDAO.getSerialNo(con,"UserInfo_table");
//        System.out.println("userId: "+userId);
      stmt.setString(1,model.getCatId());
      stmt.setString(2,model.getProductName());
      stmt.setString(3,model.getProductContent());
      stmt.setString(4,model.getProductDesc());
//        stmt.setString(5,model.getIsPrompt());
//        stmt.setString(6,model.getRegisterTime());
      stmt.setString(5,model.getListPrice());
      stmt.setString(6, model.getUnitPrice());
      stmt.setString(7, model.getOrderDesc());
      stmt.setString(8, model.getProductImgUrl());
      stmt.setString(9, model.getProductCount());
      System.out.println("getCurTimeStamp: "+DateTimeUtility.getCurTimeStamp()+"-");
      stmt.executeUpdate();
      con.commit();
    }catch(Exception e){
      con.rollback();
      throw e;
    }
    finally{
      DbPool.closeStatement(stmt);
      DbPool.closeConnection(con);
    }
  }

    public void update(ProductModel model) throws Exception{
      String sql = "update ProductInfo_table set catId='"+model.getCatId()+"',productName='"+model.getProductName()+"',productContent='"+model.getProductContent()+"',productDesc='"+model.getProductDesc()
          +"',isPrompt='"+model.getIsPrompt()+"',listPrice='"+model.getListPrice()+"',unitPrice='"+model.getUnitPrice()+"',orderDesc='"+model.getOrderDesc()+"',productImgUrl='"+model.getProductImgUrl()
          +"',productCount='"+model.getProductName()+"'"
          +" where productId='"+model.getProductId()+"'";
      Connection con = null;
      Statement stmt = null;
      try{
        con = DbPool.getConnection();
        stmt = con.createStatement();
        stmt.executeUpdate(sql);
        con.commit();
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public void delete(String productId) throws Exception{
        String sql = "delete from ProductInfo_table where productId='" + productId +"'";
        Connection con = null;
        PreparedStatement stmt = null;
        try
        {
          con = DbPool.getConnection();
          stmt = con.prepareStatement(sql);
          stmt.executeUpdate();
          con.commit();
        }
        catch(Exception e)
        {
          throw e;
        }
        finally
        {
          DbPool.closeStatement(stmt);
          DbPool.closeConnection(con);
        }
    }

    public ArrayList query(ProductModel model) throws Exception{
        ArrayList list = new ArrayList();
        String sql = "select pi.productId,pi.catId,pi.productName,pi.productContent,pi.productDesc,pi.isPrompt,pi.resigsterTime,pi.listPrice,pi.unitPrice,pi.orderDesc,pi.productImgUrl,pi.productCount from ProductInfo_table pi where pi.isValid='1'";
        if(model.getProductId()!=null && !model.getProductId().equals("")){
          sql += " and pi.productId like '%"+model.getProductId()+"%'";
        }
        if(model.getCatId()!=null && !model.getCatId().equals("")){
          sql += " and pi.catId like '%"+model.getCatId()+"%'";
        }
        if(model.getProductName()!=null && !model.getProductName().equals("")){
          sql += " and pi.productName like '%"+model.getProductName()+"%'";
        }
        if(model.getIsPrompt()!=null && !model.getIsPrompt().equals("")){
          sql += " and pi.isPrompt='"+model.getIsPrompt()+"'";
        }
        if(model.getRegisterTime()!=null && !model.getRegisterTime().equals("")){
          sql += " and pi.registerTime='"+model.getRegisterTime()+"'";
        }
        Debug.println(sql);
        ResultSet rs = null;
         Connection con = null;
         Statement statement = null;
         try{
            con = DbPool.getConnection();
            statement = con.createStatement();
            rs = statement.executeQuery(sql);
            while(rs.next()){
              ProductModel productmodel = new ProductModel();
              productmodel.setProductId(rs.getString(1));
              productmodel.setCatId(rs.getString(2));
              productmodel.setProductName(rs.getString(3));
              productmodel.setProductContent(rs.getString(4));
              productmodel.setProductDesc(rs.getString(5));
              productmodel.setIsPrompt(rs.getString(6));
              productmodel.setRegisterTime(rs.getString(7));
              productmodel.setListPrice(rs.getString(8));
              productmodel.setUnitPrice(rs.getString(9));
              productmodel.setOrderDesc(rs.getString(10));
              productmodel.setProductImgUrl(rs.getString(11));
              productmodel.setProductCount(rs.getString(12));

              CategoryProxy lpProxy = new CategoryProxy();
              CategoryModel model0 = lpProxy.queryCategory(productmodel.getCatId());
              if (model0 != null)
                productmodel.setCatName(model0.getCatName());
              list.add(productmodel);
            }
        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return list;
    }

    public ProductModel queryProduct(String productId) throws Exception{
      ProductModel model = null;
      String sql = "select productId,catId,productName,productContent,productDesc,isPrompt,resigsterTime,listPrice,unitPrice,orderDesc,productImgUrl,productCount,isValid from ProductInfo_table";
      sql += " where productId = '"+productId+"'";
      ResultSet rs = null;
      Connection con = null;
      Statement statement = null;
      try{
        con = DbPool.getConnection();
        statement = con.createStatement();
        rs = statement.executeQuery(sql);
        while(rs.next()){
          model = new ProductModel();
          model.clear();
          model.setProductId(productId);
          String temp = rs.getString("catId");
          if (temp != null)
            model.setCatId(temp);
          temp = rs.getString("productName");
          if (temp != null)
            model.setProductName(temp);
          temp = rs.getString("productContent");
          if (temp != null)
            model.setProductContent(temp);
          temp = rs.getString("productDesc");
          if (temp != null)
            model.setProductDesc(temp);
          temp = rs.getString("isPrompt");
          if (temp != null)
            model.setIsPrompt(temp);
          temp = rs.getString("resigsterTime");
          if (temp != null)
            model.setRegisterTime(temp);
          temp = rs.getString("listPrice");
          if (temp != null)
            model.setListPrice(temp);
          temp = rs.getString("unitPrice");
          if (temp != null)
            model.setUnitPrice(temp);
          temp = rs.getString("orderDesc");
          if (temp != null)
            model.setOrderDesc(temp);
          temp = rs.getString("productImgUrl");
          if (temp != null)
            model.setProductImgUrl(temp);
          temp = rs.getString("productCount");
          if (temp != null)
            model.setProductCount(temp);
          temp = rs.getString("isValid");
          if (temp != null)
            model.setIsValid(temp);
          CategoryProxy lpProxy = new CategoryProxy();
          CategoryModel model0 = lpProxy.queryCategory(model.getCatId());
          if (model0 != null)
            model.setCatName(model0.getCatName());

        }
      }catch(Exception e){
          throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(statement);
        DbPool.closeConnection(con);
      }
      return model;
    }
	
    public ProductModel queryProductByProductName(String productName) throws Exception{
        ProductModel model = null;
        String sql = "select productId,catId,productName,productContent,productDesc,isPrompt,resigsterTime,listPrice,unitPrice,orderDesc,productImgUrl,productCount,isValid from ProductInfo_table";
        sql += " where productName = '"+productName+"'";
        ResultSet rs = null;
        Connection con = null;
        Statement statement = null;
        try{
            con = DbPool.getConnection();
            statement = con.createStatement();
            rs = statement.executeQuery(sql);
            while(rs.next()){
              model = new ProductModel();
              model.setProductId(rs.getString("productId").trim());
              model.setCatId(rs.getString("catId").trim());
              model.setProductName(rs.getString("productName").trim());
              model.setProductContent(rs.getString("productContent"));
              model.setProductDesc(rs.getString("productDesc"));
              model.setIsPrompt(rs.getString("isPrompt").trim());
              model.setRegisterTime(rs.getString("resigsterTime").trim());
              model.setListPrice(rs.getString("listPrice").trim());
              model.setUnitPrice(rs.getString("unitPrice").trim());
              model.setOrderDesc(rs.getString("orderDesc").trim());
              model.setProductImgUrl(rs.getString("productImgUrl").trim());
              model.setProductCount(rs.getString("productCount").trim());
              model.setIsValid(rs.getString("isValid").trim());
            }
        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return model;
    }

}