HTML - 상품정보과 매출관리
- 상품정보를 보여주고 상품안의 매출관리하는 프로그램을 짜보자.
1. 먼저 expree -g ex04를 해서 파일을 만들어준다.Permalink
2. mysql에서 테이블과 데이터를 넣어준다.Permalink
3. app.js 파일에서Permalink
app.use('/sale', require('./routes/sale.js'));
app.use('/product', require('./routes/product.js'));
를 넣어 연결해주고 db도 연결해준다.
//데이타베이스 연결
var db=require('./db');
db.connect(function(err){
if(err){
console.log('데이터베이스연결실패...')
}
});
4. db.js 파일을 생성하여 sql을 가져온다.Permalink
var mysql = require('mysql');
var conn;
exports.connect=function() {
conn=mysql.createPool({
connectionLimit:100,
host:'localhost',
user:'web1',
password:'pass',
database:'webdb1'
});
}
exports.get=function(){
return conn;
};
5. 상품 목록 메인 페이지만들기Permalink
index.ejs
<!DOCTYPE html>
<html>
<head>
<title><%= title %></title>
<link rel='stylesheet' href='/stylesheets/common.css' />
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
</head>
<body>
<div id ="page">
<div id = "top">
<h1><%=title%></h1>
</div>
<div id ="center">
<%-include(pageName)%>
</div>
<div id = "bottom">
<h5>Copyright 2022. KangKkaem All Rights Reserved.</h5>
</div>
</div>
</body>
</html>
insert.ejs
<form name ="frm" method= "post" action="insert">
<table>
<tr>
<td width=100 class ="title">상품코드</td>
<td width =500><input type = "text" name ="code" value=<%=newCode%> readonly></td>
</tr>
<tr>
<td width=100 class ="title">상품이름</td>
<td width =500><input type = "text" name ="name" size=40></td>
</tr>
<tr>
<td width=100 class ="title">상품가격</td>
<td width =500><input type = "text" name ="price"></td>
</tr>
<tr>
<td width=100 class ="title">제조사</td>
<td width =500><input type = "text" name ="company"></td>
</tr>
</table>
<div id = "buttons">
<input type = "submit" value = "등록" class = "button">
<input type = "reset" value = "취소" class = "button">
<input type = "reset" value = "목록" class = "button" onclick="location.href='/'">
</div>
</form>
<script>
$(frm).on("submit",function(e){
var code = $(frm.code).val();
var name = $(frm.name).val();
var price = $(frm.price).val();
var company = $(frm.company).val();
if(name==""){
alert("상품이름을 입력하세요!");
$(frm.name).focus();
}else if(price ==""){
alert("상품가격을 입력하세요!");
$(frm.price).focus();
}else if(company ==""){
alert("제조사를 입력하세요!");
$(frm.company).focus();
}else {
if(!confirm(`${code}|${name}|${price}|${company} 저장?`)) return;
frm.submit();
}
});
</script>
product.js 의 상품목록
var db=require('../db');
/* 상품목록 */
router.get('/list', function(req, res, next) {
var sql= 'select * from tbl_product order by code desc';
db.get().query(sql,function(err,rows){
res.send(rows);
});
});
5. 상품 등록Permalink
<form name ="frm" method= "post" action="insert">
<table>
<tr>
<td width=100 class ="title">상품코드</td>
<td width =500><input type = "text" name ="code" value=<%=newCode%> readonly></td>
</tr>
<tr>
<td width=100 class ="title">상품이름</td>
<td width =500><input type = "text" name ="name" size=40></td>
</tr>
<tr>
<td width=100 class ="title">상품가격</td>
<td width =500><input type = "text" name ="price"></td>
</tr>
<tr>
<td width=100 class ="title">제조사</td>
<td width =500><input type = "text" name ="company"></td>
</tr>
</table>
<div id = "buttons">
<input type = "submit" value = "등록" class = "button">
<input type = "reset" value = "취소" class = "button">
<input type = "reset" value = "목록" class = "button" onclick="location.href='/'">
</div>
</form>
<script>
$(frm).on("submit",function(e){
var code = $(frm.code).val();
var name = $(frm.name).val();
var price = $(frm.price).val();
var company = $(frm.company).val();
if(name==""){
alert("상품이름을 입력하세요!");
$(frm.name).focus();
}else if(price ==""){
alert("상품가격을 입력하세요!");
$(frm.price).focus();
}else if(company ==""){
alert("제조사를 입력하세요!");
$(frm.company).focus();
}else {
if(!confirm(`${code}|${name}|${price}|${company} 저장?`)) return;
frm.submit();
}
});
</script>
코드는 자동으로 +1이되어 부여가되고 상품이름과 가격 제조사를 입력하고 등록버튼을 누르면 상품등록이된다. 입력하지않은 값이있으면 입력하라고 경고창이 뜬다.
// 상품 등록페이지
router.get('/insert', function(req,res){
var sql = "select max(code) mcode from tbl_product";
db.get().query(sql, function(err,rows){
var maxCode =rows[0].mcode;
var newCode='P' + (parseInt(maxCode.substr(1))+1);
res.render('index', {title: '상품등록', pageName: 'insert.ejs', newCode:newCode})
});
});
//db에 상품등록
router.post('/insert',function(req,res){
var code = req.body.code;
var name = req.body.name;
var price = req.body.price;
var company = req.body.company;
var sql = 'insert into tbl_product(code,name,price,company) values(?,?,?,?)';
db.get().query(sql, [code,name,price,company], function(err,rows){
res.redirect('/');
});
});
sql문 등록 을 불러오는 문이다.
6. 상품 정보 수정과 삭제Permalink
코드값을 누르면 코드값에 해당되는 정보들이 출력되는데 여기서 수정과 삭제가 가능하다.
- 상품정보읽기
<form name ="frm" method= "post" action="update">
<table>
<tr>
<td width=100 class ="title">상품코드</td>
<td width =500><input type = "text" name ="code" value="<%=vo.code%>" readonly></td>
</tr>
<tr>
<td width=100 class ="title">상품이름</td>
<td width =500><input type = "text" name ="name" size=40 value="<%=vo.name%>"></td>
</tr>
<tr>
<td width=100 class ="title">상품가격</td>
<td width =500><input type = "text" name ="price" value="<%=vo.price%>"></td>
</tr>
<tr>
<td width=100 class ="title">제조사</td>
<td width =500><input type = "text" name ="company" value="<%=vo.company%>"></td>
</tr>
</table>
<div id = "buttons">
<input type = "submit" value = "수정" class = "button">
<input type = "reset" value = "취소" class = "button">
<input type = "reset" value = "목록" class = "button" onclick="location.href='/'">
<input type = "reset" value = "삭제" class = "button" id ="btnDelete">
</div>
</form>
var moment = require('moment');
//상품정보읽기
router.get('/read', function(req,res){
var code=req.query.code;
var today= moment(new Date()).format('YYYY-MM-DD');
var sql = 'select * from tbl_product where code=?'
db.get().query(sql, [code], function(err,rows){
res.render('index', {title: '상품정보', pageName:'read.ejs',vo:rows[0], today:today});
});
});
- 상품 정부 수정
<script> $(frm).on("submit",function(e){ var code = $(frm.code).val(); var name = $(frm.name).val(); var price = $(frm.price).val(); var company = $(frm.company).val(); if(name==""){ alert("상품이름을 입력하세요!"); $(frm.name).focus(); }else if(price ==""){ alert("상품가격을 입력하세요!"); $(frm.price).focus(); }else if(company ==""){ alert("제조사를 입력하세요!"); $(frm.company).focus(); }else { if(!confirm(`${code}|${name}|${price}|${company} 수정?`)) return; frm.submit(); } }); </script>
//db에 상품정보수정
router.post('/update', function(req,res){
var code = req.body.code;
var name = req.body.name;
var price = req.body.price;
var company = req.body.company;
var sql = 'update tbl_product set name=?, price=?,company=? where code=?';
db.get().query(sql,[name,price,company,code], function(err,rows){
res.redirect('/');
});
});
- 상품 정보 삭제
```js
//삭제버튼
$(“#btnDelete”).on(“click”, function(){
if(!confirm(
${code}을(를) 삭제하실래요?)) return; frm.action= “delete”; frm.submit(); }); //DB에서 상품정보 삭제 router.post(‘/delete’,function(req,res){ var code = req.body.code; var sql = ‘delete from tbl_product where code=?’; db.get().query(sql, [code],function(err,rows){ res.redirect(‘/’); }); });
## 7. 매출 현황 정보 수정과 삭제
- 매출현황정보 출력
```js
<hr/>
<h3 id ="sale" style="font-size:30px;"><%=vo.name%>의 매출현황</h3>
<table>
<tr>
<td width =50 class ="title">판매일</td>
<td><input type = "text" id ="sdate" size =20 value="<%=today%>"></td>
<td width= 50 class ="title">판매가 </td>
<td><input type = "text" id ="price" value="<%=vo.price%>" size=3></td>
<td width = 50 class="title">판매수량</td>
<td><input type = "text" id ="quantity"placeholder="수량" size=2></td>
<td><button id ="btnInsert">매출등록</button></td>
</tr>
</table>
<table id = "tbl"></table>
<script id="temp" type="text/x-handlebars-template">
<tr class = "title" >
<td width=50>번호</td>
<td width=100>판매일</td>
<td width=100>판매가격</td>
<td width=100>판매수량</td>
<td width=50>금액</td>
<td width=50>수정</td>
<td width=50>삭제</td>
</tr>
<tr class ="row">
<td class = "id"></td>
<td></td>
<td><input type = "text" class ="price" value="" size=4></td>
<td><input type = "text" class ="quantity" value="" size=4></td>
<td></td>
<td><button class = "btnUpdate" style="background-color: rgb(218, 78, 78);">수정</button></td>
<td><button class = "btnDelete" style="background-color: rgb(218, 78, 78);">삭제</button></td>
</tr>
</script>
//매출현황목록
getSale();
function getSale(){
$.ajax({
type: "get",
dataType : "json",
url:"/sale/list",
data: {code:code},
success:function(data){
// alert(data.length);
var template = Handlebars.compile($("#temp").html());
$("#tbl").html(template(data));
}
});
}
sql문
/* 상품판매목록 */
router.get('/list', function(req, res, next) {
var code=req.query.code;
var sql ='select *, format(price*quantity,0) sum,';
sql+='date_format(sdate, "%Y-%m-%d") fdate ';
sql+='from tbl_sale ';
sql+='where code=? ';
sql+='order by id desc';
db.get().query(sql, [code], function(err, rows){
res.send(rows);
});
});
//DB에 매출등록
router.post('/insert', function(req,res){
var code = req.body.code;
var sdate = req.body.sdate;
var price= req.body.price;
var quantity = req.body.quantity;
//console.log(`%{code}\n${sdate}\n${price}\n${quantity}`);
var sql ='insert into tbl_sale(code,sdate,price,quantity) values(?,?,?,?)';
db.get().query(sql,[code,sdate,price,quantity], function(err,rows){
res.sendStatus(200);
});
});
- 매출현황 정보 수정
//매출현황 수정버튼눌럿을때 $("#tbl").on("click", ".row .btnUpdate" ,function(){ var row =$(this).parent().parent(); var id = row.find(".id").html(); var quantity = row.find(".quantity").val(); var price = row.find(".price").val(); if(!confirm(`${id}|${price}|${quantity} 수정?`)) return; $.ajax({ type: "post", url: "/sale/update", data : {id:id , price:price, quantity:quantity}, success:function(){ alert("매출수정성공!"); getSale(); } }); });
DB 매출수정
//DB에 매출수정
router.post('/update', function(req,res){
var id = req.body.id;
var price = req.body.price;
var quantity = req.body.quantity;
console.log(`${id}\n${price}\n${quantity}`);
var sql ='update tbl_sale set price=?, quantity=? where id =?';
db.get().query(sql,[price,quantity,id], function(err,rows){
res.sendStatus(200);
});
});
- 매출현황 정보 삭제
//매출현황 삭제버튼눌럿을때 $("#tbl").on("click", ".row .btnDelete" ,function(){ var row = $(this).parent().parent(); var id = row.find(".id").html(); if(!confirm(`${id} 삭제?`)) return; $.ajax({ type: "post", url: "/sale/delete", data : {id:id}, success:function(){ alert("매출삭제성공!"); getSale(); } }); });
DB삭제
//DB에 매출삭제
router.post('/delete', function(req,res){
var id = req.body.id;
var sql ='delete from tbl_sale where id =?';
db.get().query(sql,[id], function(err,rows){
res.sendStatus(200);
});
});
끝!
댓글남기기