package pay;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import bbs.Bbs;
import pay.Order;
import pay.Detail;

public class Pay {
	
	private Connection conn;
	private ResultSet rs;
	
	public Pay() {
		try {
			String dbURL = "jdbc:oracle:thin:@localhost:1521:ORCL";
			String dbID = "-";
			String dbPassword = "-";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(dbURL, dbID, dbPassword);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public int onum() {
		String SQL = "SELECT MAX(O_NO) FROM ORDERS";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
			return 0; //현재가 첫 번째 게시글인 경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int wd(int O_NO, int P_NO, int COUN, String TOER, int TNUM) {
		String SQL = "INSERT INTO DETAIL VALUES (?, ?, ?, ?, ?, ?)";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);;
			pstmt.setInt(1, getNest());
			pstmt.setInt(2, O_NO);
			pstmt.setInt(3, P_NO);
			pstmt.setInt(4, COUN);
			pstmt.setString(5, TOER);
			pstmt.setInt(6, TNUM);
			return pstmt.executeUpdate();
				
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int wo(int USER_NO, String ADDR, String CONDI, int PAY) {
		String SQL = "INSERT INTO ORDERS VALUES (?, ?, SYSTIMESTAMP, ?, ?, ?)";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);;
			pstmt.setInt(1, getNext());
			pstmt.setInt(2, USER_NO);
			pstmt.setString(3, ADDR);
			pstmt.setString(4, CONDI);
			pstmt.setInt(5, PAY);
			return pstmt.executeUpdate();
				
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public ArrayList<Order> getor() {
	    String SQL = "SELECT * FROM ORDERS ORDER BY O_NO DESC";
	    ArrayList<Order> list = new ArrayList<Order>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Order o = new Order();
	            o.setO_no(rs.getInt(1));
	            o.setUser_no(rs.getInt(2));
	            o.setDate(rs.getString(3));
	            o.setADDR(rs.getString(4));
	            o.setCondi(rs.getString(5));
	            o.setPay(rs.getInt(6));
	            list.add(o);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return list;
	}
	
	public ArrayList<Order> sendor() {
	    String SQL = "SELECT * FROM ORDERS WHERE CNODI = '준비중'";
	    ArrayList<Order> list = new ArrayList<Order>();
	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            Order o = new Order();
	            o.setO_no(rs.getInt(1));
	            o.setUser_no(rs.getInt(2));
	            o.setDate(rs.getString(3));
	            o.setADDR(rs.getString(4));
	            o.setCondi(rs.getString(5));
	            o.setPay(rs.getInt(6));
	            list.add(o);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return list;
	}
	
	public int sopo(int O_NO) {
	    String SQL = "UPDATE ORDERS SET CNODI = '배송완료' WHERE O_NO = ? ";
	    ArrayList<Order> list = new ArrayList<Order>();
	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        pstmt.setInt(1, O_NO);
	        rs = pstmt.executeQuery();

	        while (rs.next()) {
	            return pstmt.executeUpdate();
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return -1;
	}
	
	public ArrayList<Detail> getde(int O_NO) {
	    String SQL = "SELECT * FROM DETAIL WHERE O_NO = ? ORDER BY O_NO DESC";
	    ArrayList<Detail> list = new ArrayList<Detail>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        pstmt.setInt(1, O_NO);
	        rs = pstmt.executeQuery();
	        while (rs.next()) {
	            Detail d = new Detail();
	            d.setD_no(rs.getInt(1));
	            d.setO_no(rs.getInt(2));
	            d.setP_no(rs.getInt(3));
	            d.setCoun(rs.getInt(4));
	            d.setToer(rs.getString(5));
	            d.setTnum(rs.getInt(6));
	            list.add(d);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return list;
	}

	public int getNext() {
		String SQL = "SELECT O_NO FROM ORDERS ORDER BY O_NO DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글인 경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int getNest() {
		String SQL = "SELECT D_NO FROM DETAIL ORDER BY D_NO DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글인 경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	public ArrayList<Order> money() {
	    String SQL = "SELECT SUM(CASE WHEN TO_DATE(ntime, 'YYYY-MM-DD') = TRUNC(SYSDATE) - 1 THEN pay ELSE 0 END) AS 어제매출, SUM(CASE WHEN TO_DATE(ntime, 'YYYY-MM-DD') = TRUNC(SYSDATE) THEN pay ELSE 0 END) AS 오늘매출, SUM(CASE WHEN TO_DATE(ntime, 'YYYY-MM-DD') BETWEEN TRUNC(SYSDATE) - 7 AND TRUNC(SYSDATE) - 1 THEN pay ELSE 0 END) AS 지난주매출, SUM(CASE WHEN TO_DATE(ntime, 'YYYY-MM-DD') BETWEEN TRUNC(SYSDATE) - 1 AND TRUNC(SYSDATE) THEN pay ELSE 0 END) AS 이번주매출, SUM(CASE WHEN ADD_MONTHS(TO_DATE(ntime, 'YYYY-MM-DD'), 1) = TRUNC(SYSDATE, 'MONTH') THEN pay ELSE 0 END) AS 지난달매출, SUM(CASE WHEN TO_DATE(ntime, 'YYYY-MM-DD') >= TRUNC(SYSDATE, 'MONTH') AND TO_DATE(ntime, 'YYYY-MM-DD') < ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1) THEN pay ELSE 0 END) AS 이번달매출, SUM(CASE WHEN EXTRACT(YEAR FROM TO_DATE(ntime, 'YYYY-MM-DD')) BETWEEN EXTRACT(YEAR FROM SYSDATE) - 1 AND EXTRACT(YEAR FROM SYSDATE) - 1 THEN pay ELSE 0 END) AS 제작년매출, SUM(CASE WHEN EXTRACT(YEAR FROM TO_DATE(ntime, 'YYYY-MM-DD')) = EXTRACT(YEAR FROM SYSDATE) THEN pay ELSE 0 END) AS 작년매출 FROM ORDERS WHERE PAY IS NOT NULL";
	    ArrayList<Order> list = new ArrayList<Order>();

	    try {
	        PreparedStatement pstmt = conn.prepareStatement(SQL);
	        rs = pstmt.executeQuery();
	        while (rs.next()) {
	        	Order o = new Order();
	            o.setPay(rs.getInt(1));
	            list.add(o);
	            o.setPay(rs.getInt(2));
	            list.add(o);
	            o.setPay(rs.getInt(3));
	            list.add(o);
	            o.setPay(rs.getInt(4));
	            list.add(o);
	            o.setPay(rs.getInt(5));
	            list.add(o);
	            o.setPay(rs.getInt(6));
	            list.add(o);
	            o.setPay(rs.getInt(7));
	            list.add(o);
	            o.setPay(rs.getInt(8));
	            list.add(o);
	            list.add(o);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return list;
	}
}