db.java~71~

网吧的计费系统,通过改系统可以简单的为网吧的收费提供方便

package prj;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class DB {

    public DB() {
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private static final String Driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    private static final String url = "jdbc:odbc:java";

    public static Connection getConnection() {
        Connection dbConnection = null;
        try {
            Class.forName(Driver);
            dbConnection = DriverManager.getConnection(url);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return dbConnection;
    }

//判断
    public boolean panduan(Card dbcard) {

        String id = dbcard.getId();
        String password = dbcard.getPassword();
        String strsql = "select password from Card where Id = ?";

        try {
            Connection con = getConnection();

            PreparedStatement s = con.prepareStatement(strsql);
            s.setString(1, id);
            ResultSet rs = s.executeQuery();
            if (rs.next()) {
                if (password.equalsIgnoreCase(rs.getString("Password"))) {
                    con.close();
                    return true;
                } else {
                    JOptionPane.showMessageDialog(null, "你输入的密码不正确! 请重新输入...");
                    return false;
                }
            } else {
                JOptionPane.showMessageDialog(null, "卡号不存在!");
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return false;
    }

//上机
    public void shangji(Record record, Computer computer) {
        Connection con = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;

        try {
            con = getConnection();

            String strsql =
                    "insert into record(CardId,ComputerId,BeginTime) values(?,?,?)";

            ps1 = con.prepareStatement(strsql);
            ps1.setString(1, record.getCardId());
            ps1.setString(2, record.getComputerId());
            ps1.setString(3, record.getBeginTime());
            ps1.executeUpdate();

            String strsql2 = "update Computer set OnUse = 1 where id = ?";

            ps2 = con.prepareStatement(strsql2);
            ps2.setString(1, computer.getId());
            ps2.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                ps1.close();
                ps2.close();
                con.close();
            } catch (Exception ex) {

            }
        }

    }

//下机
    public void xiaji(Record recordl, Computer computerl, Card cardl) {
        String begintime;
        String cardid;
        String endtime = recordl.getEndTime();

        Connection con = null;
        try {
            con = getConnection();

            String strsql1 = "select * from Record where ComputerId = ?";
            PreparedStatement ps1 = con.prepareStatement(strsql1);
            ps1.setString(1, recordl.getComputerId());
            ResultSet rs = ps1.executeQuery();
            if (rs.next()) {
                recordl.setCardId(rs.getString("CardId"));
                recordl.setBeginTime(rs.getString("BeginTime"));
            }
            begintime = recordl.getBeginTime();
            cardid = recordl.getCardId();
            int fee = calFee(begintime, endtime);
            con.close();

            con = getConnection();
            String strsql2 = "insert into Record(Endtime,Fee) values(?,?)";
            PreparedStatement ps2 = con.prepareStatement(strsql2);
            ps2.setString(1, endtime);
            ps2.setInt(2, fee);
            ps2.executeUpdate();
            con.close();

            con = getConnection();
            String strsql3 =
                    "update Card set balance = balance - ? where Id = ?";
            PreparedStatement ps3 = con.prepareStatement(strsql3);
            ps3.setInt(1, fee);
            ps3.setString(2, cardid);
            ps3.executeUpdate();
            con.close();

            con = getConnection();
            String strsql4 = "select * from Record where computerid = ?";
            String strsql5 = "select balance from Card where Id = ?";

            PreparedStatement ps4 = con.prepareStatement(strsql4);
            ps4.setString(1, recordl.getComputerId());
            ResultSet rs2 = ps4.executeQuery();
            if (rs2.next()) {
                recordl.setCardId(rs2.getString("CardId"));
                recordl.setComputerId(rs2.getString("ComputerId"));
                recordl.setBeginTime(rs2.getString("BeginTime"));
                recordl.setEndTime(rs2.getString("EndTime"));
                recordl.setFee(rs2.getInt("Fee"));
            }
            con.close();

            con = getConnection();
            PreparedStatement ps5 = con.prepareStatement(strsql5);
            ps5.setString(1, cardid);
            ResultSet rs3 = ps5.executeQuery();

            if (rs3.next()) {
                cardl.setBalance(rs3.getInt("balance"));
            }
            con.close();

            con = getConnection();
            String strsql6 = "update Computer set OnUse = 0 where id = ?";
            PreparedStatement ps6 = con.prepareStatement(strsql6);
            ps6.setString(1, computerl.getId());
            ps6.executeUpdate();

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

    }

//费用计算方法
    private int calFee(String begintime, String endtime) {
        int fee = 0;
        int beginyear = Integer.parseInt(begintime.substring(0, 4));
        int beginmonth = Integer.parseInt(begintime.substring(5, 7));
        int beginday = Integer.parseInt(begintime.substring(8, 10));
        int beginhour = Integer.parseInt(begintime.substring(11, 13));
        int beginminute = Integer.parseInt(begintime.substring(14, 16));

        int endyear = Integer.parseInt(endtime.substring(0, 4));
        int endmonth = Integer.parseInt(endtime.substring(5, 7));
        int endday = Integer.parseInt(endtime.substring(8, 10));
        int endhour = Integer.parseInt(endtime.substring(11, 13));
        int endminute = Integer.parseInt(endtime.substring(14, 16));

        int playminutes = 0;
        playminutes = ((endyear - beginyear) * 365 * 24 * 60 +
                       (endmonth - beginmonth) * 30 * 24 * 60 +
                       (endday - beginday) * 24 * 60 +
                       (endhour - beginhour) * 60 +
                       (endminute - beginminute));
        int modNum = playminutes % 60;
        int playhours = 0;
        playhours = playminutes / 60;

        if (playhours == 0 || modNum > 5 && playhours > 0) {
            playhours++;
        }
        fee = playhours * 2;

        return fee;
    }

    private void jbInit() throws Exception {
    }


}