자바 풀 스택 2/6 하루 기록 049

9:15경 학원도착
 
 
<9:30 1교시>

 
 

 
 
<10:30 2교시>
 댓글 기능 만들어서 공유해주신댔음. 우리는 프로젝트 준비하라고 하심...!!!

 
 내가 무덤판거 수습하느라 일단 자세한건 제치고 내가 한 코드만 백업...

 

 

package test.dao;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import test.dto.Com1EmpDto;
import test.dto.Com1SaleDto;
import test.util.DbcpBean;

public class Com1SaleDao {

	private static Com1SaleDao dao;

	static {
		dao = new Com1SaleDao();
	}

	private Com1SaleDao() {
	}

	public static Com1SaleDao getInstance() {
		return dao;
	}

	// 추가
	public boolean insert(Com1SaleDto dto) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int rowCount = 0;
		try {
			conn = new DbcpBean().getConn();
			String sql = """
					insert into test_com1_sales
					(saleDate, storeNum, dailySales)
					values(?,?,?)
					""";
			pstmt = conn.prepareStatement(sql);
			// ? 에 값을 여기서 바인딩한다.
			pstmt.setString(1, dto.getSaleDate());
			pstmt.setInt(2, dto.getStoreNum());
			pstmt.setInt(3, dto.getDailySales());
			// sql 문을 실행하고 변화된 row 의 개수를 리턴받기
			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
			}
		}
		if (rowCount > 0) {
			return true;
		} else {
			return false;
		}
	}



	// 수정
	// ?호점의 ?월 매출 수정
	public boolean update(Com1SaleDto dto) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int rowCount = 0;
		try {
			conn = new DbcpBean().getConn();
			String sql = """
					update test_com1_sales
					set dailySales=?
					where storeNum=? and saleDate=?
					""";
			pstmt = conn.prepareStatement(sql);
			// ? 에 값을 여기서 바인딩한다.
			pstmt.setInt(1, dto.getDailySales());
			pstmt.setInt(2, dto.getStoreNum());
			pstmt.setString(3, dto.getSaleDate());

			// sql 문을 실행하고 변화된 row 의 개수를 리턴받기
			rowCount = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
			}
		}
		if (rowCount > 0) {
			return true;
		} else {
			return false;
		}
	}

	// 하나의 데이터 얻어오기
	// ?호점 하나의 일 매출 데이터 얻어오기
	public Com1SaleDto getData(String saleDate, int storeNum) {
		// Dto 객체 선언
		Com1SaleDto dto = null;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			// Connection Pool에서 Connection 객체 가져오기
			conn = new DbcpBean().getConn();
			// 실행할 SQL 문 작성
			String sql = """
					    SELECT saleDate, storeNum, dailySales
					    FROM test_com1_sales
					    WHERE saleDate=? and storeNum=?
					""";
			pstmt = conn.prepareStatement(sql);
			// ? 에 값 바인딩
			pstmt.setString(1, saleDate);
			pstmt.setInt(2, storeNum);

			// SQL 문 실행하고 결과를 ResultSet 객체로 받기
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// Dto 객체 생성 후 값 설정
				dto = new Com1SaleDto();
				dto.setSaleDate(rs.getString("saleDate"));
				dto.setStoreNum(rs.getInt("storeNum"));
				dto.setDailySales(rs.getInt("dailySales"));

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
			}
		}
		return dto;
	}
	
	public List<Com1SaleDto> getListAll() {
	    List<Com1SaleDto> list = new ArrayList<>();
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    
	    try {
	        conn = new DbcpBean().getConn();
	        String sql = """
				SELECT *from test_com1_sales
				order by storeNum asc
	        """;
	        pstmt = conn.prepareStatement(sql);
	      
	  
	        rs = pstmt.executeQuery();
	        while (rs.next()) {
	            Com1SaleDto dto = new Com1SaleDto();
	            dto.setSaleDate(rs.getString("salesDate"));
	            dto.setStoreNum(rs.getInt("storeNum"));
	            dto.setDailySales(rs.getInt("dailySales"));
	            
	            list.add(dto);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) rs.close();
	            if (pstmt != null) pstmt.close();
	            if (conn != null) conn.close();
	        } catch (Exception e) {
	        }
	    }
	    return list;
	}
	
	
	public List<Com1SaleDto> getListYear(int year) {
	    List<Com1SaleDto> list = new ArrayList<>();
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    
	    try {
	        conn = new DbcpBean().getConn();
	        String sql = """
				SELECT dailySales, salesDate, storeNum
				from test_com1_sales
				where extract(year from salesDate)=?
	        """;
	        pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, year);
	  
	        rs = pstmt.executeQuery();
	        while (rs.next()) {
	            Com1SaleDto dto = new Com1SaleDto();
	            dto.setSaleDate(rs.getString("salesDate"));
	            dto.setStoreNum(rs.getInt("storeNum"));
	            dto.setDailySales(rs.getInt("dailySales"));
	            
	            list.add(dto);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) rs.close();
	            if (pstmt != null) pstmt.close();
	            if (conn != null) conn.close();
	        } catch (Exception e) {
	        }
	    }
	    return list;
	}
	
	
	
	public List<Com1SaleDto> getListStore(int storeNum) {
	    List<Com1SaleDto> list = new ArrayList<>();
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    
	    try {
	        conn = new DbcpBean().getConn();
	        String sql = """
				SELECT dailySales, salesDate, storeNum
				from test_com1_sales
				where storeNum=?
	        """;
	        pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, storeNum);
	  
	        rs = pstmt.executeQuery();
	        while (rs.next()) {
	            Com1SaleDto dto = new Com1SaleDto();
	            dto.setSaleDate(rs.getString("salesDate"));
	            dto.setStoreNum(rs.getInt("storeNum"));
	            dto.setDailySales(rs.getInt("dailySales"));
	            
	            list.add(dto);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) rs.close();
	            if (pstmt != null) pstmt.close();
	            if (conn != null) conn.close();
	        } catch (Exception e) {
	        }
	    }
	    return list;
	}
	
	
	public List<Com1SaleDto> getListStoreMonthly(int storeNum, int month) {
	    List<Com1SaleDto> list = new ArrayList<>();
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    
	    try {
	        conn = new DbcpBean().getConn();
	        String sql = """
				SELECT dailySales, salesDate, storeNum
				from test_com1_sales
				where storeNum=?
				groupby extract(month from salesDate)=?
	        """;
	        pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, storeNum);
	        pstmt.setInt(2, month);
	  
	        rs = pstmt.executeQuery();
	        while (rs.next()) {
	            Com1SaleDto dto = new Com1SaleDto();
	            dto.setSaleDate(rs.getString("salesDate"));
	            dto.setStoreNum(rs.getInt("storeNum"));
	            dto.setDailySales(rs.getInt("dailySales"));
	            
	            list.add(dto);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) rs.close();
	            if (pstmt != null) pstmt.close();
	            if (conn != null) conn.close();
	        } catch (Exception e) {
	        }
	    }
	    return list;
	}
	
	
	
}

 

 

package test.dto;

public class Com1SaleDto {
	private String saleDate;
	private int storeNum;
	private int dailySales;
	private int year;
	private int month;
	private int sum;
	
	public String getSaleDate() {
		return saleDate;
	}
	public void setSaleDate(String saleDate) {
		this.saleDate = saleDate;
	}
	public int getStoreNum() {
		return storeNum;
	}
	public void setStoreNum(int storeNum) {
		this.storeNum = storeNum;
	}
	public int getDailySales() {
		return dailySales;
	}
	public void setDailySales(int dailySales) {
		this.dailySales = dailySales;
	}
	public int getYear() {
		return year;
	}
	public void setYear(int year) {
		this.year = year;
	}
	public int getMonth() {
		return month;
	}
	public void setMonth(int month) {
		this.month = month;
	}
	public int getSum() {
		return sum;
	}
	public void setSum(int sum) {
		this.sum = sum;
	}
		
}

 

<%@page import="test.dao.Com1SaleDao"%>
<%@page import="test.dao.Com1Dao"%>
<%@page import="test.dto.Com1SaleDto"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>

<%
//int comid = (int) session.getAttribute("comid");
//String comname = (String) session.getAttribute("comname");

//int empno = (int) session.getAttribute("empno");
//String role = (String) session.getAttribute("role");
//String ename = (String) session.getAttribute("ename");

	Com1SaleDao SaleDao = Com1SaleDao.getInstance();
	List<Integer> storenums = Com1Dao.getInstance().getStoreNumList();


	List<Com1SaleDto> listall = SaleDao.getListAll();
	List<Com1SaleDto> listyear=SaleDao.getListYear();
	int year = SaleDao.getListYear();
	List<Com1SaleDto> listmonth=SaleDao.getListMonth(year);
	

	Com1Dao com1Dao = Com1Dao.getInstance();
	String storenumParam = request.getParameter("storenum");
    int storenum = -1;  
    List<Com1SaleDto> storeList = null;

    if (storenumParam != null && !storenumParam.isEmpty()) {
        storenum = Integer.parseInt(storenumParam);
        storeList = SaleDao.getListStoreMonth(storeNum, year); 
    }

	for(Integer tmp: storeNumbers ){
		int storenum= storeNumbers.get(tmp);
		List<Com1SaleDto> SaleList = SaleDao.getList(storenum);
	}
	int storenum = Integer.parseInt(storenumParam);
	
	List<Com1SaleDto> liststoreyear = SaleDao.getListStoreYear(storeNum);


%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet"
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
<jsp:include page="/include/resource.jsp"></jsp:include>
<style>
.tab-button {
	padding: 10px 20px;
	cursor: pointer;
	font-weight: bold;
	background-color: #f1f1f1;
	border: 1px solid #ddd;
	transition: background-color 0.3s ease;
	display: inline-block;
}

.active-tab {
	background-color: #dcdcdc;
	border-bottom: 2px solid #999;
}

.tab-content {
	padding: 20px;
	background-color: #fff;
	border-top: 1px solid #ddd;
	display: none;
}

table {
	width: 100%;
	border-collapse: collapse;
	margin-bottom: 20px;
	table-layout: fixed;
}

th, td {
	border: 1px solid #ddd;
	padding: 12px;
	text-align: center;
	background-color: #fff;
	font-size: 16px;
	white-space: normal;
	overflow-wrap: break-word;
	text-overflow: ellipsis;
	word-wrap: break-word;
}

th {
	background-color: #f5f5f5;
	font-weight: bold;
	color: #333;
	border-bottom: 2px solid #bbb;
}

tbody tr:nth-child(even) {
	background-color: #f9f9f9;
}

tbody tr:hover {
	background-color: #eef;
}
</style>
</head>

<body class="d-flex flex-column min-vh-100">
	<jsp:include page="/include/navbar.jsp"></jsp:include>
	<div class="container">
		<p>
		<%-->	회사코드 :
			<%=comid %>
			회사번호 :
			<%=comname %>
			사원번호 :
			<%=empno %>
			역할 :
			<%=role %>
			이름 :
			<%=ename %> --%>
		</p>
	</div>
	<div class="container flex-fill" style="width: 100%; margin-top: 50px;">
		<div class="tab-button" id="allTab" onclick="switchTab('all')">전체 매출</div>
		<div class="tab-button" id="allyearTab" onclick="switchTab('allyear')">전체 연매출</div>
		<div class="tab-button" id="allmonthTab" onclick="switchTab('allmonth')">전체 월매출</div>
	
		<div id="allContent" class="tab-content"
			style="padding: 20px; background-color: #fff; border-top: 1px solid #ddd; display: block;">
			<h1>전체 매출</h1>
			<form method="post" style="display: inline-block; margin-left: 20px;">
				<label for="allyear">연매출 보기</label> 
				<input type="checkbox" id="allyear" name="allyear" value="연매출 보기" /> 
					<input type="submit" value="제출" />
			</form>
			<table style="width: 100%; border-collapse: collapse;">
				<thead>
					<tr colspan="3">
						<th>호점</th>
						<th>날짜</th>
						<th>매출</th>
					</tr>
				</thead>
				<tbody>
					<% if (listall != null && !listall.isEmpty()) { 
				            for (Com1SaleDto tmp : listall) { %>
					<tr>
						<td><%= tmp.getStoreNum() %></td>
						<td><%= tmp.getSaleDate() %></td>
						<td><%= tmp.getDailySales() %></td>
					</tr>
					<%  } 
				           } else { %>
					<tr>
						<td colspan="11">매출 정보가 없습니다.</td>
					</tr>
					<% } %>
					<tr tr style="background-color: #f8f8f8; border-top: 2px solid #000;">
						<td colspan="2" style="text-align: center; font-weight: bold;">총합</td>
						<td colspan="1" style="text-align: center; font-weight: bold;">
							<% 
						    // 합계를 저장할 변수 선언
						    int totalSales = 0;
						    
						    if (listall != null && !listall.isEmpty()) { 
						        for (Com1SaleDto tmp : listall) { 
						            // tmp.getDailySales() 값을 합계에 더함
						            totalSales += tmp.getDailySales();
						    %>
						    <%= totalSales %>
						</td>
					</tr>
				</tbody>
			</table>
		</div>

		<div id="allyearContent" class="tab-content" style="padding: 20px; background-color: #fff; border-top: 1px solid #ddd; display: block;">
			<h1>전체 연매출</h1>
			<table>
				<thead>
					<tr>
						<th>호점</th>
						<th>날짜</th>
						<th>매출</th>
					</tr>
				</thead>
				<tbody>
					<% if ( listyear != null && !listyear.isEmpty()) { 
				            for (Com1SaleDto tmp : listyear) { %>
					<tr>
						<td><%= tmp.getStoreNum() %></td>
						<td><%= tmp.getSaleDate() %></td>
						<td><%= tmp.getDailySales() %></td>

					</tr>
					<%  } 
				           } else { %>
					<tr>
						<td colspan="11">연매출 정보가 없습니다.</td>
					</tr>
					<% } %>
				</tbody>
			</table>
		</div>



		<div id="allmonthContent" class="tab-content"
			style="padding: 20px; background-color: #fff; border-top: 1px solid #ddd; display: block;">
			<h2>지점별 월매출 목록</h2>
			<form method="get" id="storeForm">
				<label for="storenum">지점 선택: </label> 
				<select name="storenum"	id="storenum" onchange="switchTab('storeNum'); document.getElementById('storeForm').submit();">
					<option value="">-- 지점을 선택하세요 --</option>
					<% for (Integer tmp : storenums) { %>
					<option value="<%= storeNum %>"
						<%= (tmp == storenum) ? "selected" : "" %>>
						<%= tmp %>호점
					</option>
					<% } %>
				</select>
			</form>

			<br />

			<% if (storenum != -1) { %>
			<h3><%= storenum %>호점 직원 목록
			</h3>
			<table border="1" cellspacing="0" cellpadding="8">
				<thead>
					<tr>
						<th>호점</th>
						<th>날짜</th>
						<th>이름</th>
					</tr>
				</thead>
				<tbody>
					<% if (storeList != null && !storeList.isEmpty()) { 
			                for (Com1SaleDto tmp: storeList) { %>
					<tr>
						<%for(int num: storenums){ %>
						<td><%=num %></td>
						<%} %>
						<td><%= tmp.getStoreNum() %></td>
						<td><%= tmp.getSaleDate() %></td>
						<td><%= tmp.getDailySales() %></td>

					</tr>

				</tbody>
			</table>
		</div>
	</div>

	<%@ include file="/include/footer.jsp"%>

	<script>
	    function switchTab(tab) {
	        const tabs = ['all', 'allyear', 'allmonth'];
	
	        tabs.forEach(t => {
	            document.getElementById(t + 'Content').style.display = 'none';
	            document.getElementById(t + 'Tab').classList.remove('active-tab');
	        });
	
	        document.getElementById(tab + 'Content').style.display = 'block';
	        document.getElementById(tab + 'Tab').classList.add('active-tab');
	    }
	
	    window.onload = function() {
	        const tabs = ['all', 'allyear', 'allmonth'];
	        tabs.forEach(t => {
	            document.getElementById(t + 'Content').style.display = 'none';
	            document.getElementById(t + 'Tab').classList.remove('active-tab');
	        });

	        const urlParams = new URLSearchParams(window.location.search);
	        if (urlParams.has('storenum')) {
	            switchTab('storeNum'); 
	        }
	    };

	</script>
</body>
</html>

 

 

 

남은 시간도 수습하다가 감 ㅠㅠㅠㅠㅠ

8시반 퇴근 예정 ㅠㅠㅠㅠ