package com.jbaptech.accp.netbar.server.dao;

import com.jbaptech.accp.netbar.server.entity.Record;
import java.sql.*;
import java.sql.SQLException;
import com.jbaptech.accp.netbar.server.entity.Computer;
import java.util.ArrayList;
import com.jbaptech.accp.netbar.server.entity.Card;
import com.jbaptech.accp.netbar.client.ComsumeDisplayData;

public class RecordDAO {
  public RecordDAO() {
  }

  public void doStartUseComputerBusiness(Record record, Computer computer) {

    Connection con = null;
    PreparedStatement pStatement1 = null;
    PreparedStatement pStatement2 = null;

    try {
      con = ConnectionManager.getConnction();
      con.setAutoCommit(false);
      String strSql =
          "insert into record(Id,CardId,ComputerId,BeginTime) values(?,?,?,?)";
      pStatement1 = con.prepareStatement(strSql);
      pStatement1.setInt(1, getNewId());
      pStatement1.setString(2, record.getCardId());
      pStatement1.setString(3, record.getComputerId());
      pStatement1.setString(4, record.getBeginTime());
      pStatement1.executeUpdate();

      String strSql2 =
          "update computer set OnUse =1 where id =(?) ; ";
      pStatement2 = con.prepareStatement(strSql2);
      pStatement2.setString(1, computer.getId());
      pStatement2.executeUpdate();

      con.commit();
      con.setAutoCommit(true);

    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
      try {
        pStatement1.close();
        pStatement2.close();
        con.rollback();

      }
      catch (Exception e) {
        e.printStackTrace();
      }
    }
    finally {
      try {
        con.close();
      }
      catch (SQLException e) {
        e.printStackTrace();
      }

      //clearDao();

    } //finally
  }

  public ArrayList getNotStopComputer() {

    ArrayList list = new ArrayList();
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet res = null;

    try {
      dbConnection = ConnectionManager.getConnction();
      // 查詢數據SQL語句
      String strSql =
          "select * from record where EndTime is NULL order by ComputerID";
      if (dbConnection != null) {
        System.out.println(dbConnection != null);
      }

      //查詢操作
      pStatement = dbConnection.prepareStatement(strSql);
      res = pStatement.executeQuery();
      while (res.next()) {
        Record record = new Record();
        record.setId(res.getInt("id"));
        record.setCardId(res.getString("CardId"));
        record.setComputerId(res.getString("ComputerId"));
        record.setBeginTime(res.getString("BeginTime"));
        list.add(record);
      }
    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
    }
    finally {
      ConnectionManager.closeResultSet(res);
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(dbConnection);

    }

    return list;

  }

  public ComsumeDisplayData getStopCompouterRelationInfo(Record record) {
    ComsumeDisplayData result = new ComsumeDisplayData();
    Connection dbConnection = null;
    PreparedStatement pStatement = null;
    ResultSet res = null;

    try {
      dbConnection = ConnectionManager.getConnction();
      // 查詢數據SQL語句
      String strSql = "select r.id,r.computerid,r.cardid,r.begintime,c.balance from record r, card c where r.ComputerId =" +
          record.getComputerId() + " and r.EndTime is NULL and r.cardid = c.id";
      if (dbConnection != null) {
        System.out.println(dbConnection != null);
      }

      //查詢操作
      pStatement = dbConnection.prepareStatement(strSql);
      res = pStatement.executeQuery();
      if (res.next()) {

     record.setId(res.getInt("id"));
     record.setComputerId( res.getString("computerid"));
     record.setCardId(res.getString("cardId"));
     record.setBeginTime(res.getString("beginTime"));


     Card card = new Card();
     card.setBalance( res.getInt("balance"));

     result.setCard(card);
     result.setRecord(record);


   }


    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
    }finally{
      ConnectionManager.closeResultSet(res);
  ConnectionManager.closeStatement(pStatement);
  ConnectionManager.closeConnection(dbConnection);

    }
    return result;
  }

  public void doDatabaseChangeAboutEndPlay(Record record, Card card) {

    Connection con = null;
    PreparedStatement pStatement = null;
    try {
//從卡里扣款
      con = ConnectionManager.getConnction();
      con.setAutoCommit(false);

      String updatCardBalanceSql =
          "update Card set balance =(?) where id=(?) ; ";
      pStatement = con.prepareStatement(updatCardBalanceSql);
      pStatement.setInt(1, card.getBalance());
      pStatement.setString(2, card.getId());
      pStatement.executeUpdate();

      //修改上機記錄
      String updatRecordSql =
          "update Record set endTime =(?), fee =(?) where id=(?) ; ";
      pStatement = con.prepareStatement(updatRecordSql);
      pStatement.setString(1, record.getEndTime());
      pStatement.setInt(2, record.getFee());
      pStatement.setInt(3, record.getId());
      pStatement.executeUpdate();

      //修改機器為未使用狀態
      String updatComputerSql =
          "update Computer set OnUse = 0 where id=(?) ; ";
      pStatement = con.prepareStatement(updatComputerSql);
      pStatement.setString(1, record.getComputerId());
      pStatement.executeUpdate();

      con.commit();
      con.setAutoCommit(true);

    }
    catch (SQLException sqlE) {
      sqlE.printStackTrace();
      try {
        con.rollback();
      }
      catch (Exception e) {
        e.printStackTrace();
      }
    }
    finally {
      ConnectionManager.closeStatement(pStatement);
      ConnectionManager.closeConnection(con);

    }
  }

  private int getNewId() {
    int id = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet resSet = null;
    try {
      con = ConnectionManager.getConnction();
      // 查詢數據SQL語句
      String sqlStr = "select max(id) from record ";
      //查詢操作
      pstmt = con.prepareStatement(sqlStr);
      resSet = pstmt.executeQuery();
      if (resSet.next()) {
        id = resSet.getInt(1);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {

      ConnectionManager.closeResultSet(resSet);
      ConnectionManager.closeStatement(pstmt);
      ConnectionManager.closeConnection(con);

    }
    return id + 1;
  }

}