package stockmanagiment;
import java.sql.*;
public class Connect {
public static Connection makeConnection()
{
String url = "jdbc:mysql://localhost/convenience"; // 데이터베이스 주소
String id = "root";
String password = "1234";
Connection con = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("드라이버 적재 성공");
con = DriverManager.getConnection(url, id, password);
System.out.println("데이터베이스 연결 성공");
} catch (ClassNotFoundException e) {
System.out.println("드라이버를 찾을 수 없습니다.");
}catch (SQLException e) {
System.out.println("연결에 실패하였습니다.");
}
return con;
}
}
db연결 클래스
package stockmanagiment;
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JTextField;
public class Stockmanage implements ActionListener {
private JPanel idPanel;
private JLabel idlabel;
private JTextField idInput;
private JLabel passlabel;
private JTextField passInput;
private JPanel passPanel;
private JButton loginButton;
private JFrame jframe;
private Connection con;
public Stockmanage() {
idlabel = new JLabel("ID");
idInput = new JTextField(15);
idPanel = new JPanel();
idPanel.setLayout(new BorderLayout());
idPanel.add(idlabel,BorderLayout.WEST);
idPanel.add(idInput,BorderLayout.CENTER);
passlabel = new JLabel("비밀번호");
passInput = new JTextField(15);
passPanel = new JPanel();
passPanel.setLayout(new BorderLayout());
passPanel.add(passlabel,BorderLayout.WEST);
passPanel.add(passInput,BorderLayout.CENTER);
loginButton = new JButton("로그인");
loginButton.addActionListener(this);
jframe = new JFrame("재고관리 프로그램");
jframe.setLayout(new BorderLayout());
jframe.add(idPanel,BorderLayout.NORTH);
jframe.add(passPanel,BorderLayout.CENTER);
jframe.add(loginButton,BorderLayout.SOUTH);
jframe.pack();
jframe.setLocationRelativeTo(null);
jframe.setVisible(true);
}
@Override
public void actionPerformed(ActionEvent e) {
Object obj = e.getSource();
if(obj == loginButton) {
String id = idInput.getText();
String password = passInput.getText();
if(id.equals("head") && password.equals( "0000")) {
jframe.setVisible(false);
try {
con = Connect.makeConnection();
new Head(con);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
else if((id.equals("knbranch")|| (id.equals("mrbranch")))&& password.equals("0011")) {
jframe.setVisible(false);
con = Connect.makeConnection();
new Branch(id,con);
}
else {
JOptionPane.showMessageDialog(null, "일치하는 계정정보가 없습니다. "
+ "다시 확인하세요","알림", JOptionPane.ERROR_MESSAGE);
}
}
}
public static void main(String[] args) {
new Stockmanage();
}
}
package stockmanagiment;
import java.awt.BorderLayout;
import java.awt.CardLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
public class Head implements ActionListener, ItemListener{
private JLabel label;
private JTextArea dateshow;
private JTextArea dataOut;
private JLabel sumlabel;
private JTextArea sumOut;
private JComboBox<String> branchc;
private JButton showOrder;
private JButton aprOrder;
private Container tab;
private CardLayout clayout;
private JPanel headPanel;
private JPanel buttonPanel1;
private JPanel buttonPanel2;
private JPanel functionPanel;
private JPanel sumPanel;
private JFrame jframe;
private Connection con;
private PreparedStatement pstmt;
private HashMap<String, String> brtablelist;
public Head() {}
public Head(Connection con) throws SQLException {
this.con = con;
label = new JLabel("본사");
brtablelist = new HashMap<String,String>();
// 보완필요 if문 반복을 피하기 위해서 hashmap으로 코드를 짰는데 프로그램 실행할 때마다 반복해서 put메서드를 실행해줘야 하는게...
brtablelist.put("강남점", "kangnam");
brtablelist.put("모란점", "moran");
Date now = new Date(System.currentTimeMillis());
SimpleDateFormat simple= new SimpleDateFormat("YY-MM-dd");
dateshow = new JTextArea();
dateshow.setText(simple.format(now)); // textArea에 날짜 표시
dateshow.setEditable(false);
headPanel = new JPanel();
headPanel.setLayout(new BorderLayout());
headPanel.add(label,BorderLayout.WEST);
headPanel.add(dateshow,BorderLayout.EAST);
dataOut= new JTextArea(40,30);
JScrollPane jsp = new JScrollPane(dataOut,
JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
String[]list = {"지점선택","본사","강남점","모란점"};
branchc = new JComboBox<String>(list);
branchc.addItemListener(this);
showOrder = new JButton("주문조회");
showOrder.addActionListener(this);
buttonPanel1 = new JPanel();
buttonPanel1.setLayout(new BorderLayout());
buttonPanel1.add(showOrder,BorderLayout.CENTER);
aprOrder = new JButton("승인");
aprOrder.addActionListener(this);
buttonPanel2 = new JPanel();
buttonPanel2.setLayout(new BorderLayout());
buttonPanel2.add(aprOrder,BorderLayout.CENTER);
tab = new JPanel();
clayout = new CardLayout();
tab.setLayout(clayout);
tab.add(buttonPanel1, "show");
tab.add(buttonPanel2, "approve");
sumlabel = new JLabel("매출합계");
sumOut = new JTextArea();
sumPanel = new JPanel();
sumPanel.setLayout(new BorderLayout());
sumPanel.add(sumlabel,BorderLayout.WEST);
sumPanel.add(sumOut,BorderLayout.CENTER);
functionPanel = new JPanel();
functionPanel.setLayout(new BorderLayout());
functionPanel.add(branchc,BorderLayout.NORTH);
functionPanel.add(tab,BorderLayout.CENTER);
functionPanel.add(sumPanel,BorderLayout.SOUTH);
clayout.show(tab, "show");
jframe = new JFrame("재고관리 프로그램");
jframe.setLayout(new BorderLayout());
jframe.add(headPanel,BorderLayout.NORTH);
jframe.add(jsp,BorderLayout.CENTER);
jframe.add(functionPanel,BorderLayout.EAST);
jframe.pack();
jframe.setLocationRelativeTo(null);
jframe.setVisible(true);
}
@Override
public void actionPerformed(ActionEvent e) {
Object obj = e.getSource();
if( obj== showOrder) {
String branch,code,name="";
Date aprdate,orderdate;
int idx,unitprice,quantity,totalprice=0;
try {
dataOut.setText(""); //데이터창 초기화
dataOut.append("주문번호 주문지점 주문일 코드 품명 단가 수량 금액 처리일"); //컬럼명 출력
dataOut.append("\n");
pstmt = con.prepareStatement("select * from convenience.order order by aprdate;");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
idx = rs.getInt("idx");
//서로다른 지점에서 같은 날 같은제품을 주문했을 경우에 구분이 필요해서 PK로 지정할 일련번호? 주문번호가 필요함
branch = rs.getString("branch");
orderdate = rs.getDate("orderdate");
code = rs.getString("code");
name = rs.getString("name");
unitprice = rs.getInt("unitprice");
quantity = rs.getInt("quantity");
totalprice = rs.getInt("totalprice");
aprdate = rs.getDate("aprdate");
//값을 하나씩 읽어와서 변수에 대입
dataOut.append( idx +"\t"+branch+"\t"+orderdate+"\t"+code+"\t"+ name +"\t"
+ unitprice+"\t"+quantity +"\t"+ totalprice+"\t"+aprdate);
//데이터 창에 출력
dataOut.append("\n");
}
clayout.show(tab, "approve"); //버튼 바꾸기
} catch (Exception ex) {
// TODO: handle exception
}
}
else if(obj==aprOrder) {
try {
pstmt = con.prepareStatement("select idx, aprdate from convenience.order where aprdate is null;");
//승인일란이 공란인 데이터를 가져옴
ResultSet rs = pstmt.executeQuery(); //쿼리문 실행
ArrayList<Integer>list = new ArrayList<Integer>(); //idx를 넣을 arraylist 변수 선언
while(rs.next()) {
list.add(rs.getInt("idx")); //각 데이터의 idx(주문번호)를 가져와서 list에 넣음
}
int size = list.size();
for(int i=0;i<size;i++) {
pstmt = con.prepareStatement("update convenience.order set aprdate = now() where idx = ?;");
// 내부함수를 써서 오늘 날짜로 승인일 변경
pstmt.setInt(1, list.get(i));
//list에서 값을 하나씩 읽어서 order테이블에서 일치하는 행의 데이터를 가져와서 오늘 날짜로 승인일 입력
pstmt.executeUpdate();
//본사 재고에서 빼서 지점 재고로 넣는 코드 필요
}
clayout.show(tab, "show");
} catch (Exception e2) {
// TODO: handle exception
}
}
}
@Override
public void itemStateChanged(ItemEvent ie) {
String code,name,shpoint="";
Date shipping,receiving;
int quantity,unitprice,totalprice=0;
Timestamp expiry;
String choice = branchc.getSelectedItem().toString();
try {
if(choice.equals("지점선택")){} //지점선택은 아무런 행동 x
else if(choice.equals("본사")) { //본사 테이블과 지점 테이블 컬럼이 달라서 본사만 따로 분리
pstmt = (PreparedStatement)con.prepareStatement("select * from convenience.head order by expiry;");
ResultSet rs = pstmt.executeQuery();
dataOut.setText("");
dataOut.append("코드 품명 수량 단가 금액 입고날짜 출고날짜 유통기한 출고지점");
dataOut.append("\n");
while(rs.next()) {
code = rs.getString("code");
name = rs.getString("name");
quantity = rs.getInt("quantity");
unitprice = rs.getInt("unitprice");
totalprice = rs.getInt("totalprice");
receiving = rs.getDate("receiving");
shipping = rs.getDate("shipping");
expiry = rs.getTimestamp("expiry");
shpoint = rs.getString("shpoint");
dataOut.append(code+"\t"+ name + "\t"+quantity + "\t"+unitprice + "\t"+totalprice +"\t"+receiving +"\t"+ shipping + "\t"+expiry + "\t"+shpoint);
dataOut.append("\n");
}
}
else {
Date saledate;
dataOut.setText("");
dataOut.append("코드 품명 수량 단가 금액 입고날짜 판매일 유통기한");
dataOut.append("\n");
String branch=brtablelist.get(choice); //한글지점명을 key로 value인 영어 테이블 값을 가져와서 branch에 대입
if(branch!=null) {
PreparedStatement pstmt = con.prepareStatement("select * from convenience."+branch+ " order by expiry;");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
code = rs.getString("code");
name = rs.getString("name");
quantity = rs.getInt("quantity");
unitprice = rs.getInt("unitprice");
totalprice = rs.getInt("totalprice");
receiving = rs.getDate("receiving");
saledate = rs.getDate("saledate");
expiry = rs.getTimestamp("expiry");
dataOut.append(code+ "\t"+name + "\t"+quantity +"\t"+ unitprice + "\t"+totalprice +"\t"+receiving +"\t"+ saledate +"\t"+ expiry);
dataOut.append("\n");
}
}
}
} catch (Exception e) {
// TODO: handle exception
}
}
}
package stockmanagiment;
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextArea;
public class Branch implements ActionListener{
private JLabel label;
private JTextArea dateshow;
private JTextArea dataOut;
private JLabel sumlabel;
private JTextArea sumOut;
private JButton showButton;
private JButton orderButton;
private JPanel headPanel;
private JPanel functionPanel;
private JPanel sumPanel;
private JPanel buttonPanel;
private JFrame jframe;
private Connection con;
private String id;
public Branch() {};
public Branch(String id,Connection con) {
this.id = id;
this.con = con;
label = new JLabel("지점");
Date now = new Date(System.currentTimeMillis());
SimpleDateFormat simple= new SimpleDateFormat("YY/MM-dd");
dateshow = new JTextArea();
dateshow.setText(simple.format(now));
dateshow.setEditable(false);
headPanel = new JPanel();
headPanel.setLayout(new BorderLayout());
headPanel.add(label,BorderLayout.WEST);
headPanel.add(dateshow,BorderLayout.EAST);
dataOut= new JTextArea(30,30);
sumlabel = new JLabel("매출합계");
sumOut = new JTextArea();
sumPanel = new JPanel();
sumPanel.setLayout(new BorderLayout());
sumPanel.add(sumlabel,BorderLayout.WEST);
sumPanel.add(sumOut,BorderLayout.CENTER);
showButton = new JButton("조회");
showButton.addActionListener(this);
orderButton = new JButton("주문");
orderButton.addActionListener(this);
buttonPanel = new JPanel();
buttonPanel.setLayout(new BorderLayout());
buttonPanel.add(showButton,BorderLayout.CENTER);
buttonPanel.add(orderButton,BorderLayout.AFTER_LAST_LINE);
functionPanel = new JPanel();
functionPanel.setLayout(new BorderLayout());
functionPanel.add(sumPanel,BorderLayout.NORTH);
functionPanel.add(buttonPanel,BorderLayout.CENTER);
jframe = new JFrame("재고관리 프로그램");
jframe.setLayout(new BorderLayout());
jframe.add(headPanel,BorderLayout.NORTH);
jframe.add(dataOut,BorderLayout.CENTER);
jframe.add(functionPanel,BorderLayout.EAST);
jframe.pack();
jframe.setLocationRelativeTo(null);
jframe.setVisible(true);
}
public static void main(String[] args) {
}
@Override
public void actionPerformed(ActionEvent e) {
Object obj = e.getSource();
if(obj == showButton) {
String code,name="";
Date receiving,saledate;
int quantity,unitprice,totalprice;
Timestamp expiry;
String sql="";
if(id.startsWith("kn")) sql = "select * from convenience.kangnam order by expiry;";
else if(id.startsWith("mr")) sql = "select * convenience.moran order by expiry;";
try {
PreparedStatement psmt = (PreparedStatement)con.prepareStatement(sql);
ResultSet rs = psmt.executeQuery();
dataOut.setText("");
dataOut.append("코드 품명 수량 단가 금액 입고날짜 판매일 유통기한");
while(rs.next()) {
code = rs.getString("code");
name = rs.getString("name");
quantity = rs.getInt("quantity");
unitprice = rs.getInt("unitprice");
totalprice = rs.getInt("totalprice");
receiving = rs.getDate("receiving");
saledate = rs.getDate("saledate");
expiry = rs.getTimestamp("expiry");
// 값을 한 줄씩 읽어와서 변수에 대입하고 dataOut에 출력
dataOut.append(code+ "\t"+name + "\t"+quantity + "\t"+unitprice
+ "\t"+totalprice +"\t"+receiving + "\t"+saledate + "\t"+expiry+"\n");
}
} catch (Exception e2) {
// TODO: handle exception
}
}
else if(obj == orderButton) {
new Order(con,id);
}
}
}
package stockmanagiment;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Info { //상품코드를 기반으로 상품리스트에서 정보를 가져오는 클래스
public String name;
public int unitprice;
Connection con;
java.sql.Statement stmt;
public Info serch(Connection con, String code) throws SQLException {
this.con = con;
Info info = new Info();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select name, unitprice from product where code='"+code+"'"); //code와 일치하는 행을 찾아서
rs.next();
info.name = rs.getString("name"); //제품이름과
info.unitprice = rs.getInt("unitprice"); //단가를 멤버변수에 대입해서
return info; //info객체 반환
}
}
package stockmanagiment;
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
public class Order implements ActionListener{
private JLabel codeLabel;
private JTextField codeinput;
private JPanel codePanel;
private JLabel quanLabel;
private JTextField quaninput;
private JPanel quanPanel;
private JButton insertButton;
private JFrame order;
private Connection con;
private String id;
private Info info;
private PreparedStatement pstmt;
public Order(){}
public Order(Connection con, String id){
this.con = con;
this.id = id;
codeLabel = new JLabel("코드");
codeinput = new JTextField(5);
codePanel = new JPanel();
codePanel.setLayout(new BorderLayout());
codePanel.add(codeLabel,BorderLayout.WEST);
codePanel.add(codeinput,BorderLayout.EAST);
quanLabel = new JLabel("주문량");
quaninput = new JTextField(5);
quanPanel = new JPanel();
quanPanel.setLayout(new BorderLayout());
quanPanel.add(quanLabel,BorderLayout.WEST);
quanPanel.add(quaninput,BorderLayout.EAST);
insertButton = new JButton("입력");
insertButton.addActionListener(this);
order = new JFrame("주문창");
order.setLayout(new BorderLayout());
order.add(codePanel, BorderLayout.NORTH);
order.add(quanPanel, BorderLayout.CENTER);
order.add(insertButton,BorderLayout.SOUTH);
order.pack();
order.setResizable(false);
order.setVisible(true);
}
@Override
public void actionPerformed(ActionEvent e) {
Object obj = e.getSource();
if(obj == insertButton ) {
try {
String branch="";
if(id.startsWith("kn")) branch = "강남점";
else if(id.startsWith("mr")) branch = "모란점";
String code = codeinput.getText();
info = new Info().serch(con, code);
String name = info.name;
int unitprice = info.unitprice;
int quantity = Integer.parseInt(quaninput.getText());
int totalprice = unitprice*quantity;
pstmt = con.prepareStatement("insert into convenience.order "
+ "( branch, orderdate, code, name, unitprice, quantity, totalprice) values (?,now(),?,?,?,?,?);");
pstmt.setString(1, branch);
pstmt.setString(2, code);
pstmt.setString(3,name);
pstmt.setInt(4,unitprice);
pstmt.setInt(5,quantity);
pstmt.setInt(6,totalprice);
pstmt.executeUpdate();
// 각 물음표에 넣을 값을 설정해주고 쿼리문 실행
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
'JAVA' 카테고리의 다른 글
| 채팅 프로그램 ( 욕설필터 기능 구현) (1) | 2021.03.15 |
|---|---|
| JAVA 소켓 채팅 프로그램 강퇴기능 구현 + 업데이트 (1) | 2021.03.13 |
| 소켓 기반 채팅 프로그램 귓속말 기능 구현 (0) | 2021.03.10 |
| JAVA 소켓 채팅 프로그램 (server) (0) | 2021.03.04 |
| JAVA 소켓 채팅 프로그램 (Client) (0) | 2021.03.04 |
