仓储管理系统

一、创建数据库

创建用户表

use db_WH
go
create table wh_user
(
u_id  char(10) primary key, --用户账号
u_name varchar(10) not null, --用户姓名
u_pwd varchar(20) not null, --用户密码
u_age char(5) default NULL, --用户年龄
u_sex char(2) check(u_sex='男' or u_sex='女'), --用户性别，取值只能为“男”或者“女”
u_phone varchar(20) default NULL, --用户号码
u_role varchar(10) check(u_role='超级管理员' or u_role='管理员' or u_role='普通账号')
default '普通账号', --用户权限，默认“普通账号”
)

创建仓库表

use db_WH
go
create table storage
(
s_id varchar(20) primary key,  --仓库编号
s_name varchar(20) not null, --仓库名字
s_dress varchar(100) not null, --仓库地址
)

创建商品表

use db_WH
go
create table goods
(
g_id varchar(20) primary key,   --商品编号
g_name varchar(20) not null,        --商品名称
g_date datetime,           --商品生产日期
g_remark varchar(1000) default null,    --商品备注
p_id varchar(10) foreign key references provider(p_id)
)

创建库存表

use db_WH
go
create table stock
(
g_id varchar(20) references goods(g_id),   --外键，与商品表编号相连
s_id varchar(20) references storage(s_id),--外键，与仓库表仓库编号相连
s_num int not null check(s_num>0),  --库存数量
primary key(g_id,s_id)  --字段组合做主键
)

创建供应商表

use db_WH
go
create table provider
(
p_id char(5) primary key, --供应商编号
p_name varchar(30) not null, --供应商姓名
p_phone varchar(20) not null, --供应商电话
)

创建入库表

use db_WH
go
create table inbound
(
i_id varchar(20) primary key,   --入库编号，五位数自增
i_num int not null ,  --入库数量
i_time datetime,  --入库时间
s_id varchar(20) foreign key references storage(s_id), --仓库编号，外键
g_id varchar(20) foreign key references goods(g_id),  --商品编号，外键
u_id char(10) foreign key references wh_user(u_id), --操作员，外键
)

创建出库表

use db_WH
go
create table outbound
(
o_id varchar(20) primary key, --出库编号，六位数自增
o_count int default 0, --出库数量s
o_time datetime,  --入库时间
s_id varchar(20)  foreign key references storage(s_id), --仓库编号，外键
g_id varchar(20) foreign key references goods(g_id), --商品编号，外键
u_id char(10) foreign key references wh_user(u_id), --操作员，外键
)

ER图

数据库关系图

创建索引

create nonclustered index 出库_创建时间 on outbound(o_time desc);	

多表查询

select * from stock
join goods 
on stock.g_id = goods.g_id

select * from outbound
join goods 
on outbound.g_id = goods.g_id
join storage
on outbound.s_id = storage.s_id

分组查询

select u_sex 性别,count(*)人数
from wh_user
group by u_sex

select u_role 角色,count(*) 人数
from wh_user
group by u_role

排序

select * from stock
order by s_num asc

存储过程

分页存储过程

--分页
go
create procedure SelectPage_storage
	@pageIndex int,
	@pageSize int
as
begin
	select * from storage
	order by s_id
	offset ((@pageIndex-1)*@pageSize) rows
	fetch next @pageSize rows only;
	return 1
end

exec SelectPage_storage @pageIndex=4,@pageSize=3
drop proc SelectPage_storage

入库表新增存储过程

--新增
create procedure addInbound
	@i_num int,
	@i_time datetime,
	@s_id varchar(20),
	@g_id varchar(20),
	@u_id char(10)
as
begin
	if exists(select * from stock where s_id=@s_id and g_id=@g_id)
		begin
			update stock set s_num=s_num+@i_num where s_id=@s_id and g_id=@g_id
		end
	else
		begin
			insert into  inbound(i_id, i_num,i_time,s_id,g_id,u_id)
	        values ('I'+right('000000'+cast((select isnull(max(right(i_id, len(i_id)-1)), 0)+1 from  inbound) as varchar), 6), @i_num,@i_time,@s_id,@g_id,@u_id); -- 插入第一条记录
			insert into stock(s_id,g_id,s_num) values (@s_id,@g_id,@i_num);
		end
end

触发器

出库触发器

--出库触发器
create trigger outbound_stock
on outbound
instead of insert
as
begin
	declare   @count1 int
	declare   @count2 int
	declare   @g_id varchar(20)
	declare   @s_id varchar(20)
	select @g_id=g_id from inserted
	select @s_id=s_id from inserted
	select @count1=o_count from inserted
	select @count2=s_num from stock where g_id=@g_id and s_id=@s_id
	if @count1>@count2
		print '出库量大于库存量,不能出库'
	else
		begin
			begin transaction
			declare @error int = 0
            begin try
					insert into outbound select * from inserted
					update stock set s_num=s_num-@count1 where s_id=@s_id and g_id=@g_id
					print '已经成功插入记录'
			end try
			begin catch
					set @error = 1
					print '出现错误!事务已回滚!'
			end catch
			if @error = 1
				begin
					rollback transaction
				end
			else
				begin
				commit transaction
				end
		end
end

drop trigger outbound_stock

更新库存触发器

--更新库存触发器
create trigger Stock_update
on stock
after update
as
begin
	declare @num int
	declare @s_id varchar(20)
	declare @g_id varchar(20)
	set @s_id=(select s_id from inserted)
	set @g_id=(select g_id from inserted)
	set @num=(select s_num from stock where g_id=@g_id and s_id=@s_id)
	if(@num=0)
		delete stock where s_id=@s_id and g_id=@g_id
end

二、用node.js连接SQL server数据库

//示例代码

var sql = require('mssql');

// DB configuration
var dbConfig = {
    user: 'sa',
    password: '123456',
    server: 'localhost',
    database: 'db_WH',
    port: 1433,
    options: {
        "encrypt": false
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    }
};

// 查询所有的用户信息
function getAllUsers() {
    var conn = new sql.ConnectionPool(dbConfig);
    //console.log(conn);
    var req = new sql.Request(conn);
    conn.connect(function (err) {
        if (err) {
            console.log(err);
            return;
        }
        // 查询t_user表
        req.query("SELECT * FROM wh_user", function (err, recordset) {
            if (err) {
                console.log(err);
                return;
            }
            else {
                console.log(recordset);
            }
            conn.close();
        });
    });
}

// 查询所有的用户信息
getAllUsers();

解决数据库连接问题

结果如下：

二、用Vue-cli创建项目，并引入element-UI

npm install -g vue
npm install -g  @vue/cli 
vue -V

vue create ‘项目名’ 回车创建

npm i element-ui -S

在vue的main.js中引入element-UI

import Vue from 'vue'
import App from './App.vue'
import router from './router'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';


Vue.use(ElementUI);
Vue.config.productionTip = false

new Vue({
  router,
  render: h => h(App)
}).$mount('#app')

配置路由

引入很重要

在首页加入各个部件，并且在Main中加入路由

添加子路由

const routes = [
  {
    path: '/',
    name: 'index',
    component: Index,
    children:[
      {
        path:'/'
      },
      {
        path:'storage',
        component:() => import('@/views/storage.vue')
      },
      {
        path:'goods',
        component:() => import('@/views/goods.vue')
      },
      {
        path:'supplier',
        component:() => import('@/views/supplier.vue')
      },
      {
        path:'inbound',
        component:() => import('@/views/inbound.vue')
      },
      {
        path:'outbound',
        component:() => import('@/views/outbound.vue')
      },
      {
        path:'user',
        component:() => import('@/views/user.vue')
      },
    ]
  },
]

在main.js中注册axios

import axios from 'axios'
Vue.prototype.$http= axios

在main.js中注册VueCookies

1.安装
npm install vue-cookie --save 
2.引入
import VueCookies from 'vue-cookies'
Vue.use(VueCookies)

遇到的问题和解决方案

防止直接修改网址就能路由跳转

在 router.js 中添加全局前置守卫 router.beforeEach
在全局前置守卫中对路由进行判断：

import Vue from 'vue'
import VueRouter from 'vue-router'
import Index from '@/views/Index.vue'
import Login from '@/views/Login.vue'

Vue.use(VueRouter)

const routes = [
  {
    path: '/Login',
    name: 'Login',
    component: Login,
    meta: {
      allowAnonymous: true // 允许未登录用户访问
    }
  },
  {
    path: '/',
    name: 'Index',
    component: Index,
    children: [
      // ...
    ],
    meta: {
      requiresAuth: true // 需要用户登录才能访问
    }
  }
]

const router = new VueRouter({
  mode: 'history',
  base: process.env.BASE_URL,
  routes
})

// 全局前置守卫
router.beforeEach((to, from, next) => {
  const isLogin = localStorage.getItem('isLogin') === 'true'
  if (!to.meta.allowAnonymous && !isLogin) {
    next('/Login')
  } else if (to.meta.allowAnonymous && isLogin) {
    next('/')
  } else {
    next()
  }
})

export default router

前端

<script>
export default {
  // ...
  methods: {
    handleLogin() {
      // 登录成功后保存用户信息到本地
      localStorage.setItem('isLogin', 'true')
      // 跳转至首页
      this.$router.push('/')
    }
  }
}
</script>

解决element-Ui对话框遮罩层问题

加入下面的代码

:modal-append-to-body="false"

获取实时时间

        getCurrentTime() {
            //获取当前时间并打印
            var _this = this;
            let yy = new Date().getFullYear();
            let mm = new Date().getMonth() + 1;
            let dd = new Date().getDate();
            let hh = new Date().getHours();
            let mf = new Date().getMinutes() < 10 ? '0' + new Date().getMinutes() : new Date().getMinutes();
            let ss = new Date().getSeconds() < 10 ? '0' + new Date().getSeconds() : new Date().getSeconds();
            _this.gettime = yy + '-' + mm + '-' + dd + ' ' + hh + ':' + mf + ':' + ss;
            console.log(_this.gettime)
            this.form.date = _this.gettime

        },

让表单不可更改

                <el-form-item label="入库时间" prop="date">
                    <el-col :span="20">
                        <el-input v-model="form.date" :disabled="true"></el-input>
                    </el-col>
                </el-form-item>

使用属性  :disabled="true" 方式，输入框或文本域有灰色底

增加表格滑轮

        <el-table :data="tableData" :header-cell-style="{ background: '#f2f5fc', color: '#555555' }" border
            style="max-height: 750px; overflow-y: auto;">

  通过设置"max-height"和"overflow-y"属性来添加滚动条。根据需要调整"max-height"的大小

解决sql拼接问题

   const result = `('${req.body.no}','${req.body.name}','${req.body.password}','${req.body.age}','${req.body.sex}','${req.body.phone}')`

解决分页问题

select * from wh_user
order by u_no
offset 1 rows
fetch next 2 row only;

创建存储过程

go
create procedure SelectPage
	@pageIndex int,
	@pageSize int
as
begin
	select * from wh_user
	order by u_no
	offset ((@pageIndex-1)*@pageSize) rows
	fetch next @pageSize rows only;
end