package com.publish.shop.order.dao;

import java.sql.*;
import java.util.Date;
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.order.javabeans.LineItemModel;
import com.publish.shop.product.javabeans.ProductModel;
import com.publish.shop.product.javabeans.ProductProxy;
import org.apache.struts.util.LabelValueBean;

public class LineItemDAO {
  public LineItemDAO() {
  }

  public void insert(LineItemModel model) throws Exception{
      String sql = "insert into LineItem_table(orderId,lineIndex,itemId,productId,quantity,unitPrice,orderStatus)"
                   +" values(?,?,?,?,?,?,?)";
      Connection con = null;
      PreparedStatement stmt = null;
      try{
        con = DbPool.getConnection();
        stmt = con.prepareStatement(sql);
        stmt.setInt(1, model.getOrderId());
        stmt.setString(2, model.getLineIndex());
        stmt.setString(3, model.getItemId());
        stmt.setString(4, model.getProductId());
        stmt.setString(5, model.getQuantity());
        stmt.setString(6, model.getUnitPrice());
        stmt.setInt(7, 1);
        stmt.executeUpdate();
        con.commit();
      }catch(Exception e){
        con.rollback();
        throw e;
      }
      finally{
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public void update(LineItemModel model) throws Exception{
      String sql = "update LineItem_table set productId='"+model.getProductId()+"',quantity='"+model.getQuantity()+"',unitPrice='"+model.getUnitPrice()+"',orderStatus='"+model.getOrderStatus()+"'"
          +" where orderId='"+model.getOrderId()+"' and lineIndex='"+model.getLineIndex()+"'";
      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(int orderId, String lineIndex) throws Exception{
        String sql = "delete from LineItem_table where orderId='" + orderId +"' and lineIndex='"+lineIndex+"'";
        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 void deleteByOrderId(int orderId) throws Exception{
        try
        {
          ArrayList lList = queryById(orderId);
          for (int i=0; i<lList.size(); i++)
          {
            LineItemModel model = (LineItemModel)lList.get(i);
            delete(model.getOrderId(), model.getLineIndex());
          }
        }
        catch(Exception e)
        {
          throw e;
        }
    }


    public ArrayList query(LineItemModel model) throws Exception{
        ArrayList list = new ArrayList();
        String sql = "select orderId,lineIndex,productId,quantity,unitPrice,orderStatus from LineItem_table";
        String sql01 = "";
        if(model.getOrderId()!= 0){
          sql01 += " orderId like '%"+model.getOrderId()+"%'";
        }
        if(model.getLineIndex()!=null && !model.getLineIndex().equals("")){
          if (sql01.equals(""))
              sql01 = " lineIndex like '%"+model.getLineIndex()+"%'";
          else
              sql01 += " and lineIndex like '%"+model.getLineIndex()+"%'";
        }
        if(model.getProductId()!=null && !model.getProductId().equals("")){
          if (sql01.equals(""))
              sql01 = " productId like '%"+model.getProductId()+"%'";
          else
              sql01 += " and productId like '%"+model.getProductId()+"%'";
        }
        if(model.getOrderStatus()!=null && !model.getOrderStatus().equals("")){
          if (sql01.equals(""))
              sql01 = "orderStatus like '%"+model.getOrderStatus()+"%'";
          else
              sql01 += " and orderStatus like '%"+model.getOrderStatus()+"%'";
        }
        if (!sql01.equals(""))
          sql = sql + " where " + sql01;
        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()){
              LineItemModel lineitemmodel = new LineItemModel();
              lineitemmodel.setOrderId(rs.getInt("orderId"));
              lineitemmodel.setLineIndex(rs.getString("lineIndex"));
              lineitemmodel.setProductId(rs.getString("productId"));
              lineitemmodel.setQuantity(rs.getString("quantity"));
              lineitemmodel.setUnitPrice(rs.getString("unitPrice"));
              lineitemmodel.setOrderStatus(rs.getString("orderStatus"));
              ProductProxy lpProxy = new ProductProxy();
              ProductModel productmodel = lpProxy.queryProduct(lineitemmodel.getProductId());
              if (productmodel != null)
              {
                lineitemmodel.setProductName(productmodel.getProductName());
                lineitemmodel.setCatId(productmodel.getCatId());
                lineitemmodel.setCatName(productmodel.getCatName());
              }
              list.add(lineitemmodel);
            }
        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return list;
    }

    public ArrayList queryById(int orderId) throws Exception{
        ArrayList list = new ArrayList();
        String sql = "select orderId,lineIndex,productId,quantity,unitPrice,orderStatus from LineItem_table where order='" + orderId + "'";
        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()){
              LineItemModel lineitemmodel = new LineItemModel();
              lineitemmodel.setOrderId(rs.getInt("orderId"));
              lineitemmodel.setLineIndex(rs.getString("lineIndex"));
              lineitemmodel.setProductId(rs.getString("productId"));
              lineitemmodel.setQuantity(rs.getString("quantity"));
              lineitemmodel.setUnitPrice(rs.getString("unitPrice"));
              lineitemmodel.setOrderStatus(rs.getString("orderStatus"));
              ProductProxy lpProxy = new ProductProxy();
              ProductModel productmodel = lpProxy.queryProduct(lineitemmodel.getProductId());
              if (productmodel != null)
              {
                lineitemmodel.setProductName(productmodel.getProductName());
                lineitemmodel.setCatId(productmodel.getCatId());
                lineitemmodel.setCatName(productmodel.getCatName());
              }
              list.add(lineitemmodel);
            }
        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return list;
    }

    public LineItemModel queryLineItemByOrderIdAndLineIndex(int orderId, String lineIndex) throws Exception{
      LineItemModel model = null;
      String sql = "select orderId,lineIndex,productId,quantity,unitPrice,orderStatus from LineItem_table where orderId='" + orderId +"' and lineIndex='"+lineIndex+"'";
      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 LineItemModel();
          model.clear();
          model.setOrderId(orderId);
          model.setLineIndex(lineIndex);
          String temp = rs.getString("productId");
          if (temp != null)
            model.setProductId(temp);
          temp = rs.getString("quantity");
          if (temp != null)
            model.setQuantity(temp);
          temp = rs.getString("unitPrice");
          if (temp != null)
            model.setUnitPrice(temp);
          temp = rs.getString("orderStatus");
          if (temp != null)
            model.setOrderStatus(temp);
          ProductProxy lpProxy = new ProductProxy();
          ProductModel productmodel = lpProxy.queryProduct(model.getProductId());
          if (productmodel != null)
          {
            model.setProductName(productmodel.getProductName());
            model.setCatId(productmodel.getCatId());
            model.setCatName(productmodel.getCatName());
          }
        }
      }catch(Exception e){
          throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(statement);
        DbPool.closeConnection(con);
      }
      return model;
    }
}