통합구현 #9 - aggregate

김형우·2022년 3월 16일
0

mongoose + vue.js

목록 보기
10/15

백엔드

1. /models/item.js

  • D:\exp_20220311\models\item.js
  1. model 생성
  2. entity = Schema와 같은 역할
  3. 여기에서 컬렉션, 시퀀스 다 정함
// 라이브러리 가져오기
// npm i mongoose --save
var mongoose = require('mongoose');
// entity에 해당하는것 만들기
var Schema = mongoose.Schema;

// npm install --save mongoose-sequence
const AutoIncrement = require('mongoose-sequence')(mongoose);

// Schema = entity
// 번호, 물품코드(100-001-111, 100-002-111) => 001은 1로 바뀜.
// 물품명, 가격, 수량
var itemSchema = new Schema({
    _id      : Number,
    code1    : {type : String, default : ''},
    code2    : {type : String, default : ''},
    code3    : {type : String, default : ''},
    name     : {type : String, default : ''},
    price    : {type : Number, default : 0},
    quantity : {type : Number, default : 0},
    regdate  : {type : Date, default:Date.now}
});

// 시퀀스 사용설정
// '_id'에는 시퀀스를 써서 자동적으로 생성된 데이터가 들어갈 것이다.
itemSchema.plugin(AutoIncrement, {id : 'SEQ_ITEM8_ID', inc_field : '_id'});

// 컬렉션 book8에 bookSchema 형태로 넣겠다.
module.exports = mongoose.model('item8', itemSchema);

2. /routes/item.js

  • D:\exp_20220311\routes\item.js
    이건 잘 보면 할수있다
var express = require('express');
var router = express.Router();

// models(Schema), import
var Item = require('../models/item');

// 물품등록
// 127.0.0.1:3000/item/insert
// { "code1":"101", "code2":"011", "code3":"001", "name":"아메리카노", "price":2000, "quantity":22 }
router.post('/insert', async function(req, res, next) {
    try {       
        var item = new Item();

        item.code1 = req.body.code1;
        item.code2 = req.body.code2;
        item.code3 = req.body.code3;
        item.name = req.body.name;
        item.price = Number(req.body.price);
        item['quantity'] = Number(req.body.quantity);

        const result = await item.save();
        console.log("/insert/result ===> ",result);
        if (result._id !== '') {
            return res.send({status:200});            
        }
        return res.send({status:0});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 물품목록
// 127.0.0.1:3000/item/select
router.get('/select', async function(req, res, next) {
    try {   
        const query = {};
        const result = await Item.find(query).sort({"_id":-1});
        return res.send({status:200, result:result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 대분류별 등록물품 개수
// 127.0.0.1:3000/item/groupcode1
router.get('/groupcode1', async function(req, res, next) {
    try {       
        const result = await Item.aggregate([
            {
                $project : {
                    code1 : 1,
                    price : 1,
                    quantity : 1
                }
            },
            {
                $group : {
                    _id : '$code1', // 그룹기준
                    count : {$sum : 1},
                    pricetotal : {$sum : '$price'}, // 대분류기준 가격 합계
                    quantitytotal : {$sum : '$quantity'} // 대분류기준 수량 합계
                }
            },
            {
                $sort : {
                    _id : 1
                }
            }
        ]);
        return res.send({status:200, result:result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 대분류별 등록물품 개수 2
// 127.0.0.1:3000/item/groupcode11?code1=101
router.get('/groupcode11', async function(req, res, next) {
    try {       
        const code1 = req.query.code1;
        const result = await Item.aggregate([
            {
                $match : {
                    code1 : code1
                }
            },
            {
                $project : {
                    code1 : 1,
                    code2 : 1,
                    price : 1,
                    quantity : 1
                }
            },
            {
                $group : {
                    _id : '$code1', // 그룹기준
                    code2 :{ $push : '$code2'},
                    count : {$sum : 1},
                    pricetotal : {$sum : '$price'}, // 대분류기준 가격 합계
                    quantitytotal : {$sum : '$quantity'} // 대분류기준 수량 합계
                }
            },
            {
                $sort : {
                    _id : 1
                }
            }
        ]);
        return res.send({status:200, result:result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 중분류별 등록물품 개수 특정code2
// 127.0.0.1:3000/item/groupcode2?code2=022
router.get('/groupcode2', async function(req, res, next) {
    try {
        // query로 code2를 받아서 받은것만 aggregate함
        const code2 = req.query.code2;
        const result = await Item.aggregate([
            {
                // query로 받은 code2가 일치하는 것만 조건으로
                $match : {
                    code2 : code2
                }
            },
            {
                $project : {
                    code2 : 1,
                    code3 : 1,
                    price : 1,
                    quantity : 1
                }
            },
            {
                $group : {
                    _id : '$code2',
                    code3 : { $push : '$code3'},
                    count : {$sum: 1 },
                    pricetotal : {$sum : '$price'},
                    quantitytotal : {$sum : '$quantity'}
                }
            },
            {
                $sort : {
                    _id : 1
                }
            }
        ])
        return res.send({status:200, result : result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 중분류별 등록물품 개수 전체 2
// 127.0.0.1:3000/item/groupcode22
router.get('/groupcode22', async function(req, res, next) {
    try {
        
        const result = await Item.aggregate([
            
            {
                $project : {
                    code2 : 1,
                    code3 : 1,
                    price : 1,
                    quantity : 1
                }
            },
            {
                $group : {
                    _id : '$code2',
                    code3 : { $push : '$code3'},
                    count : {$sum: 1 },
                    pricetotal : {$sum : '$price'},
                    quantitytotal : {$sum : '$quantity'}
                }
            },
            {
                $sort : {
                    _id : 1
                }
            }
        ])
        return res.send({status:200, result : result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 소분류별 등록물품 개수
// 127.0.0.1:3000/item/groupcode3?code3=001
router.get('/groupcode3', async function(req, res, next) {
    try {
        // query로 code2를 받아서 받은것만 aggregate함        
        const code3 = req.query.code3;
        const result = await Item.aggregate([
            {
                // query로 받은 code2가 일치하는 것만 조건으로
                $match : {                    
                    code3 : code3,
                }
            },
            {
                $project : {                    
                    code3 : 1,
                    price : 1,
                    quantity : 1
                }
            },
            {
                $group : {
                    _id : '$code3',
                    count : {$sum: 1 },
                    pricetotal : {$sum : '$price'},
                    quantitytotal : {$sum : '$quantity'}
                }
            },
            {
                $sort : {
                    _id : 1
                }
            }
        ])
        return res.send({status:200, result : result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

// 소분류별 등록물품 개수 전체 33
// 127.0.0.1:3000/item/groupcode33
router.get('/groupcode33', async function(req, res, next) {
    try {
        const result = await Item.aggregate([
            
            {
                $project : {                    
                    code3 : 1,
                    price : 1,
                    quantity : 1
                }
            },
            
            {
                $group : {
                    _id : '$code3',
                    count : {$sum: 1 },
                    pricetotal : {$sum : '$price'},
                    quantitytotal : {$sum : '$quantity'}
                }
            },
            {
                $sort : {
                    _id : 1
                }
            }
        ])
        return res.send({status:200, result : result});
    } catch (e) {
        console.error(e);
        return res.send({status:-1});
    }    
});

module.exports = router;

프론트

1. /components/ItemView.vue

  • D:\vue_20220314\src\components\ItemView.vue
<template>
    <div>
        <h3>Item</h3>

        <el-form class="demo-form-inline">
            <el-form-item label="코드">
                <el-input v-model="state.code" placeholder="000-000-000" style="width:200px;" />
            </el-form-item>
            <el-form-item label="이름">
                <el-input v-model="state.name" placeholder="물품명" style="width:200px;" />
            </el-form-item>
            <el-form-item label="가격">
                <el-input v-model="state.price" placeholder="가격" style="width:200px;" />
            </el-form-item>
            <el-form-item label="수량">
                <el-input v-model="state.quantity" placeholder="수량" style="width:200px;" />
            </el-form-item>
            <el-form-item label=" ">
                <el-button type="primary" @click="handleInsert">물품등록</el-button>
            </el-form-item>
        </el-form>        

        <el-table :data="state.items" style="width: 100%">
            <el-table-column prop="_id" label="번호" width="70" />
            <el-table-column prop="code1" label="대분류" width="80" />
            <el-table-column prop="code2" label="중분류" width="80" />
            <el-table-column prop="code3" label="소분류" width="80" />
            <el-table-column prop="name" label="물품명" />
            <el-table-column prop="price" label="가격" />
            <el-table-column prop="quantity" label="수량" />
            <el-table-column prop="regdate" label="등록일" />
        </el-table>
       
    </div>
</template>

<script>
import { reactive } from '@vue/reactivity'
import { onMounted, getCurrentInstance } from '@vue/runtime-core';
import axios from 'axios';
export default {
    setup () {

        // main.js에서 만든 변수 땡겨 씀
        // insert하고 갱신 직전에 알려줌
        const app = getCurrentInstance();
        const socket = app.appContext.config.globalProperties.$socket;
        console.log('ItemView/socket ==> ', socket);

        const state = reactive({
            code : '',
            name : '',
            price : '',
            quantity : '',
        });        

        const handleData = async() => {
            const url = `/item/select`;
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            console.log('ItemView/handleData/response ===> ',response);
            if (response.data.status === 200) {
                state.items = response.data.result;
            }
        }

        const handleInsert = async() => {
            const tmp = state.code.split("-");
            console.log("handleInsert/tmp ===> ", tmp);
            const url = `/item/insert`;
            const headers = {"Content-Type":"application/json"};
            const body = {
                code1 : tmp[0],
                code2 : tmp[1],
                code3 : tmp[2],
                name : state.name,
                price : Number(state.price),
                quantity : Number(state.quantity)
            }
            const response = await axios.post(url, body, {headers});
            console.log('ItemView.vue/handleInsert/response ===> ',response);
            if(response.data.status === 200) {
                alert('등록완료');
                // 이 위치에서 등록했음을 통지
                // 등록한 신호만 전송, AdminView에서 신호를 받고 rest로 받음
                // 데이터를 직접 넘기는게 아니다!!! (중요!)
                // A -이미지-> B
                // ==> 1. A가 DB에 이미지 저장
                // 2. A가 B에게 이미지 전달했음을 통지
                // 3. B가 DB에서 이미지를 꺼냄
                socket.emit('publish', {
                    data:{
                        userid : 'aaa', 
                        username : 'insert' // 신호 명
                    }
                });
                
                // state.code = '';
                state.name = '';
                state.price = '';
                state.quantity = '';
                handleData();
            }
        }


        onMounted( () => {
            handleData()
        });

        return {
            state,
            handleInsert,
        }
    }
}
</script>

<style lang="scss" scoped>

</style>

2. /components/AdminView.vue

  • D:\vue_20220314\src\components\AdminView.vue
<template>
    <div>
        <h3>Item</h3>        
        {{state.items}}
        <el-form class="demo-form-inline">
            <el-form-item label="코드별">

                <el-select 
                v-model="state.code1" 
                class="m-2" placeholder="대분류" 
                size="large"
                @change="handleCode1">
                    <el-option
                    v-for="item in state.items1"
                    :key="item"
                    :label="item._id"
                    :value="'?code1='+item._id"                    
                    />
                </el-select>

                <el-select 
                v-model="state.code2" 
                class="m-2" placeholder="중분류" 
                size="large"
                @change="handleCode2">
                    <el-option
                    v-for="item in state.no2"
                    :key="item"
                    :label="item"
                    :value="'?code2='+item"                    
                    />
                </el-select>

                <el-select 
                v-model="state.code3" 
                class="m-2" placeholder="소분류" 
                size="large"
                @change="handleCode3">
                    <el-option
                    v-for="item in state.no3"
                    :key="item"
                    :label="item"
                    :value="'?code3='+item"                    
                    />
                </el-select>
            </el-form-item>
        </el-form>

        <el-form class="demo-form-inline">
            <el-form-item label="코드별">
                <el-button type="primary" @click="handleCate1">대분류</el-button>
                <el-button type="primary" @click="handleCate2">중분류</el-button>
                <el-button type="primary" @click="handleCate3">소분류</el-button>
            </el-form-item>
        </el-form>


        <el-table :data="state.items" style="width: 100%">
            <el-table-column prop="_id" label="대분류" width="70" />
            <el-table-column prop="count" label="전체개수" width="80" />
            <el-table-column prop="pricetotal" label="가격합계" width="80" />
            <el-table-column prop="quantitytotal" label="수량합계" width="80" />
        </el-table>
<!-- {
    "_id": "103",
    "count": 2,
    "pricetotal": 16300,
    "quantitytotal": 55
}         -->
    </div>
</template>

<script>
import { reactive } from '@vue/reactivity'
import { getCurrentInstance, onMounted } from '@vue/runtime-core';
import axios from 'axios';
export default {
    setup () {
        // main.js에서 만든 변수 땡겨 씀
        // onMounted에서 바로 받음
        const app = getCurrentInstance();
        const socket = app.appContext.config.globalProperties.$socket;
        console.log('ItemView/socket ==> ', socket);


        const state = reactive({
            code : '',
            name : '',
            price : '',
            quantity : '',
        });

        const handleCode1 = async() => {
            console.log('code1 ===> ', state.code1);
            const url = `/item/groupcode11${state.code1}`
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            // console.log('Code1/response ===> ',response);
            if(response.data.status === 200){
                state.items = response.data.result
                state.no2 = response.data.result[0].code2
                // console.log('no2 ===> ', state.no2);
            }
        }     

        const handleCode2 = async() => {
            const url = `/item/groupcode2${state.code2}`
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            // console.log('Code2/response ===> ',response);
            if(response.data.status === 200){
                state.items = response.data.result
                state.no3 = response.data.result[0].code3
                // console.log('no3 ===> ', state.no3);
            }
        }

        const handleCode3 = async() => {
            const url = `/item/groupcode3${state.code3}`
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            // console.log('Code3/response ===> ',response);
            if(response.data.status === 200){
                state.items = response.data.result
            }
        }
        
        const handleCate1 = async() => {
            const url = `/item/groupcode1`;
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            // console.log('ItemView/handleData/response ===> ',response);
            if (response.data.status === 200) {
                state.items = response.data.result;
                state.items1 = Object.create(state.items);
                // console.log(state.items1);
            }
        }

        const handleCate2 = async() => {
            const url = `/item/groupcode22`
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            if (response.data.status === 200) {
                state.items = response.data.result;
            }
        }

        const handleCate3 = async() => {
            const url = `/item/groupcode33`
            const headers = {"Content-Type":"application/json"};
            const response = await axios.get(url, {headers});
            if (response.data.status === 200) {
                state.items = response.data.result;
            }
        }

        const handleData = async() => {
                        
        }       


        onMounted( () => {
            handleCate1();
            // socket을 이용해서 받기
            socket.on('subscribe', (recv) => {
                console.log('recv ===> ',recv);
                if(recv.username === 'insert'){
                    handleCate1();
                }
            })
            handleData();
        });

        return {
            state,
            handleData,
            // 분류 코드별 조회
            handleCode1,
            handleCode2,
            handleCode3,
            // 분류별 전체
            handleCate1,
            handleCate2,
            handleCate3
        }
    }
}
</script>

<style lang="scss" scoped>

</style>
profile
The best

0개의 댓글