2017年2月

[数据库课程结课设计]家具城进销存管理系统

<h2> 家具城进销存管理系统</h2><h3> 1、功能要求:</h3>    实现家具类型、供应商信息的管理(要求至少一种操作方式用存储过程实现);
    实现客户信息、家具信息的管理(要求至少一种操作方式用存储过程实现);
    实现家具入库管理(要求至少一种操作方式用存储过程实现);
    实现家具的销售管理(要求至少一种操作方式用存储过程实现);
    实现收款管理;
    创建触发器,实现家具入库和销售时自动修改库存;
    创建触发器,追踪家具的价格变化,即家具价格发生改动时,记录对应的变化信息;
    创建存储过程,统计某段时间内各种商品的入库数量和销售数量;
    建立数据库相关表之间的参照完整性约束。
<h3> 2、设计参数</h3>    至少5个表结构;4个存储过程;3个触发器;2个视图。

<h3> 存储过程</h3>    商品类型管理 -> 入库  -> WareInfo
    供应商信息管理 -> 入库  -> Supplier
    客户信息管理 -> 入库  -> UserInfo
    商品信息管理 -> 更新数据  -> WareInfo
    入库信息管理 -> 更新数据  -> WareInfo
    销售信息管理 -> 更新数据  -> OrderInfo
    收款管理 -> 入库  -> OrderInfo
    某时间段内商品入库数量和销售数量 -> 入库  -> 联合语句

<h3> 触发器</h3>    商品入库时自动修改库存(进)  -> 库存信息表变更时  -> 存储过程
    商品销售时自动修改库存(出)  -> 客户信息表变更时  -> 存储过程
    商品价格变动时自动记录  -> 商品信息表变更时

<h3> 视图</h3><h4> 家具信息视图</h4>    家具名
    家具类型
    库存
    价格
<h4> 商品销售视图</h4>    商品名
    商品类型
    出厂价
    最新价
    一天内销售量
<p>     总销售量</p><h3> 设计图</h3><p> </p><p>
</p><h3> 数据库关系图</h3><p> </p><h3> init.sql</h3><p>
</p><pre class="brush:sql; toolbar:false;">-- author: delovt HHQUSE MASTER;IF EXISTS(SELECT 1 FROM sys.databases WHERE name='SQL_HC_CLASSDESIGN_20160906181302WEEK') DROP DATABASE SQL_HC_CLASSDESIGN_20160906181302WEEK;CREATE DATABASE SQL_HC_CLASSDESIGN_20160906181302WEEK;GOUSE SQL_HC_CLASSDESIGN_20160906181302WEEK;GOSET ANSI_NULLS ON -- 严格化 NULL 值条件判断SET QUOTED_IDENTIFIER OFF -- 禁止表名为系统关键字SET ANSI_PADDING ON -- 允许键值末尾的 0 插入GOCREATE TABLE [dbo].HC_Supplier, [supplierName]varchar NOT NULL DEFAULT 0, call NOT NULL DEFAULT 0, addTime NOT NULL);CREATE TABLE [dbo].HC_WareInfo, [commName]varchar NOT NULL DEFAULT 0, [type]varchar NOT NULL DEFAULT 0, [sourExp]float NOT NULL DEFAULT 0, capac NOT NULL DEFAULT 0, SuppId NOT NULL DEFAULT 0, addTime NOT NULL DEFAULT 0, updTime NOT NULL DEFAULT 0, FOREIGN KEY(SuppId) REFERENCES HC_Supplier(ID));CREATE TABLE [dbo].HC_WareInfoTemp, [commName]varchar NOT NULL DEFAULT 0, [type]varchar NOT NULL DEFAULT 0, [sourExp]float NOT NULL DEFAULT 0, capac NOT NULL DEFAULT 0, SuppId NOT NULL DEFAULT 0, addTime NOT NULL DEFAULT 0, updTime NOT NULL DEFAULT 0, FOREIGN KEY(SuppId) REFERENCES HC_Supplier(ID));CREATE TABLE [dbo].HC_CommInfo, WareId NOT NULL DEFAULT 0, [exp]float NOT NULL DEFAULT 0, addTime NOT NULL DEFAULT 0, FOREIGN KEY(WareId) REFERENCES HC_WareInfo(ID));CREATE TABLE [dbo].HC_UserInfo, [userName]varchar NOT NULL DEFAULT 0, [userCont]varchar NOT NULL DEFAULT 0, addTime NOT NULL);CREATE TABLE [dbo].HC_OrderInfo, UserId NOT NULL DEFAULT 0, CommId NOT NULL DEFAULT 0, [exp]float NOT NULL DEFAULT 0, commNumber NOT NULL DEFAULT 0, addTime NOT NULL DEFAULT 0, updTime, FOREIGN KEY(UserId) REFERENCES HC_UserInfo(ID), FOREIGN KEY(CommId) REFERENCES HC_CommInfo(ID));CREATE TABLE [dbo].HC_SellInfo, sellData NOT NULL DEFAULT 0, sellCapac NOT NULL DEFAULT 0, sellType NOT NULL DEFAULT 0 CHECK (sellType in (0,1)), addTime NOT NULL);CREATE TABLE [dbo].HC_ExpChgInfo, CommId NOT NULL DEFAULT 0, [newExp]float NOT NULL DEFAULT 0, [oldExp]float NOT NULL DEFAULT 0, addTime NOT NULL DEFAULT 0, FOREIGN KEY(CommId) REFERENCES HC_CommInfo(ID));CREATE TABLE [dbo].HC_CapacChgInfo, CommId NOT NULL DEFAULT 0, newCapac NOT NULL DEFAULT 0, oldCapac NOT NULL DEFAULT 0, chgType NOT NULL DEFAULT 0 CHECK (chgType in (0,1)), addTime NOT NULL DEFAULT 0, FOREIGN KEY(CommId) REFERENCES HC_WareInfo(ID));GOIF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[CommTypeMgr]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC CommTypeMgr;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[SuppInfoMgr]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC SuppInfoMgr;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[UserInfoMgr]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC UserInfoMgr;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[CommInfoMgr]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC CommInfoMgr;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[CommAddUpde]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC CommAddUpde;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[SellInfoUpde]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC SellInfoUpde;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[SellAddMgr]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC SellAddMgr;IF EXISTS (SELECT FROM sysobjects WHERE id = object_id(N'[CommUnionMgr]') AND OBJECTPROPERTY(id,N'IsProcedure')=1) DROP PROC CommUnionMgr;GOCREATE PROC CommTypeMgr -- 存储过程 1. 插入商品信息 @commName varchar(50), @commType varchar(50), @commSouExp float, @commCapac int, @commSuppId int, @addTime datetime, @updTime datetimeAS -- 缓存表 WareInfoTemp 作用于触发器监听 INSERT INTO HC_WareInfoTemp( [commName], [type], [sourExp], [capac], [SuppId], [addTime], [updTime] ) VALUES( @commName, @commType, @commSouExp, @commCapac, @commSuppId, @addTime, @updTime );GOCREATE PROC SuppInfoMgr -- 存储过程 2. 插入供应商信息 @supplierName varchar(50), @call int, @addTime datetimeAS INSERT INTO HC_Supplier( [supplierName], [call], [addTime] ) VALUES( @supplierName, @call, @addTime );GOCREATE PROC UserInfoMgr -- 存储过程 3. 插入客户信息 @userName varchar(50), @userCont varchar(50), @addTime datetimeAS INSERT INTO HC_UserInfo( [userName], [userCont], [addTime] ) VALUES( @userName, @userCont, @addTime );GOCREATE PROC CommInfoMgr -- 存储过程 4. 更新商品信息 @Id int, @newExp float=0.0AS IF @newExp>0 UPDATE HC_CommInfo SET [exp]=@newExp WHERE [WareId]=@Id;GOCREATE PROC CommAddUpde -- 存储过程 5. 更新仓库中商品信息 作用于触发器调用 @Id int, @commName varchar(50), @type varchar(50), @sourExp float, @SuppId int, @updTime datetimeAS UPDATE HC_WareInfo SET [commName]=@commName, [type]=@type, [sourExp]=@sourExp, [SuppId]=@SuppId, [updTime]=@updTime WHERE [Id]=@Id;GOCREATE PROC SellInfoUpde -- 存储过程 6. 销售信息管理 更新订单数据 @Id int, @UserId int, @CommId int, @exp float, @updTime datetimeAS UPDATE HC_OrderInfo SET [UserId]=@UserId, [CommId]=@CommId, [exp]=@exp, [updTime]=@updTime WHERE [Id]=@Id;GOCREATE PROC SellAddMgr -- 存储过程 7. 添加订单 @UserId int, @CommId int, @exp float, @commNumber int, @addTime datetime, @updTime datetimeAS INSERT INTO HC_OrderInfo( [UserId], [CommId], [exp], [commNumber], [addTime], [updTime] ) VALUES ( @UserId, @CommId, @exp, @commNumber, @addTime, @updTime );GOCREATE PROC CommUnionMgr -- 存储过程 8. 联合管理 某段时间内商品入库数量 @type int, @LastdayTime datetime, @TodayTime datetimeAS BEGIN IF EXISTS(SELECT 1 FROM [dbo].[HC_SellInfo] WHERE ID BETWEEN 1 AND 1) BEGIN --DECLARE @TodayTime datetime; --DECLARE @LastdayTime datetime; DECLARE @AddTime datetime; SET @AddTime=(SELECT CONVERT(NVARCHAR,getdate(),20)); --SET @TodayTime=(SELECT CONVERT(NVARCHAR,(SELECT DATEADD(day,-1,getDate())),23)); --SET @LastdayTime=(SELECT CONVERT(NVARCHAR,(SELECT DATEADD(day,+1,getDate())),23)); SELECT SUM(sellCapac) FROM [dbo].[HC_SellInfo] WHERE [addTime] BETWEEN @LastdayTime AND @TodayTime AND sellType=@type END ELSE PRINT 'Line OFF'; ENDGOCREATE TRIGGER CommChgUpd -- 触发器 1.4 商品库存变动记录ON [dbo].[HC_WareInfo]AFTER INSERT,UPDATEAS BEGIN DECLARE @ChgId int; DECLARE @OldCapac int; DECLARE @NewCapac int; DECLARE @ChgType int; DECLARE @ChgAddTime datetime; SET @ChgAddTime=(SELECT CONVERT(NVARCHAR,getdate(),20)); DECLARE @chkI int; DECLARE @chkU int; IF EXISTS(SELECT TOP 1 1 FROM DELETED) SET @chkU = 1; IF EXISTS(SELECT TOP 1 1 FROM INSERTED) SET @chkI = 1; IF @chkI=1 AND @chkU=1 BEGIN -- Update SET @ChgId=(SELECT Id FROM INSERTED); SET @NewCapac=(SELECT capac FROM [dbo].[HC_WareInfo] WHERE ID=@ChgId); SET @OldCapac=(SELECT TOP 1 newCapac FROM [dbo].[HC_CapacChgInfo] WHERE Id=@ChgId ORDER BY addTime DESC); SET @ChgType=1 IF (@NewCapac-@OldCapac)<=0 SET @ChgType=0 INSERT INTO [dbo].[HC_CapacChgInfo] VALUES( @ChgId, @NewCapac, @OldCapac, @ChgType, @ChgAddTime ); END ELSE BEGIN -- Insert SELECT @ChgId=MAX(Id),@NewCapac=capac FROM [dbo].[HC_WareInfo] GROUP BY capac; SET @ChgType=1; INSERT INTO [dbo].[HC_CapacChgInfo] VALUES( @ChgId, @NewCapac, 0, 1, @ChgAddTime ); ENDENDGOCREATE TRIGGER CommInTrig -- 触发器 1.1 商品入库自动修改库存ON [dbo].[HC_WareInfoTemp]AFTER INSERTAS BEGIN DECLARE @CommName varchar(50); DECLARE @TempId int; SELECT @TempId=Id,@CommName=commName FROM [dbo].[HC_WareInfoTemp] WHERE 1=1; IF EXISTS(SELECT 1 FROM [dbo].[HC_WareInfo] WHERE commName=@CommName) -- 更新数据 BEGIN DECLARE @Capac float; DECLARE @chgAddTime datetime; SET @chgAddTime=(SELECT CONVERT(NVARCHAR,getdate(),20)); SET @Capac=(SELECT capac FROM [dbo].[HC_WareInfoTemp] WHERE 1=1); UPDATE [dbo].[HC_WareInfo] SET capac=capac+(@Capac), updTime=@chgAddTime; END ELSE -- 插入数据 BEGIN INSERT INTO [dbo].[HC_WareInfo] SELECT commName,type,sourExp,capac,SuppId,addTime,updTime FROM [dbo].[HC_WareInfoTemp] END DELETE FROM [dbo].[HC_WareInfoTemp] WHERE Id=@TempId;ENDGOCREATE TRIGGER CommOutTrig -- 触发器 1.2 商品销售自动修改库存ON [dbo].[HC_OrderInfo]AFTER INSERTAS BEGIN DECLARE @CommId int; DECLARE @CommNumber int; DECLARE @LastInsertId int; SET @LastInsertId=(SELECT @@IDENTITY); SELECT @CommId=CommId,@CommNumber=commNumber FROM [dbo].[HC_OrderInfo] WHERE Id=@LastInsertId; UPDATE [dbo].[HC_WareInfo] SET capac=(capac-@CommNumber) WHERE Id=@CommId;ENDGOCREATE TRIGGER CommExpChgTrig -- 触发器 1.3 商品价格变动记录ON [dbo].[HC_CommInfo]AFTER INSERT,UPDATEAS BEGIN DECLARE @chkI int; DECLARE @chkU int; IF EXISTS(SELECT TOP 1 1 FROM DELETED) SET @chkU = 1; IF EXISTS(SELECT TOP 1 1 FROM INSERTED) SET @chkI = 1; IF @chkI=1 AND @chkU=1 BEGIN DECLARE @LastInsertId int; DECLARE @NewExp float; DECLARE @OldExp float; DECLARE @addTime datetime; SET @addTime=(SELECT CONVERT(NVARCHAR,getdate(),20)); SET @LastInsertId=(SELECT Id FROM Inserted); SELECT @OldExp=newExp FROM [dbo].[HC_ExpChgInfo] WHERE CommId=( SELECT Id FROM [dbo].[HC_CommInfo] WHERE Id=@LastInsertId ); SELECT @NewExp=exp FROM [dbo].[HC_CommInfo] WHERE Id=@LastInsertId; INSERT INTO [dbo].[HC_ExpChgInfo] VALUES( @LastInsertId, @NewExp, @OldExp, @addTime ); END ELSE BEGIN DECLARE @LastInsertId1 int; DECLARE @NewExp1 float; DECLARE @addTime1 datetime; SET @addTime1=(SELECT CONVERT(NVARCHAR,getdate(),20)); SET @LastInsertId1=(SELECT Id FROM Inserted); SET @NewExp1=(SELECT exp FROM [dbo].[HC_CommInfo] WHERE Id=@LastInsertId1); INSERT INTO [dbo].[HC_ExpChgInfo] VALUES( @LastInsertId1, @NewExp1, 0, @addTime1 ); ENDENDGOCREATE VIEW CommInfoView AS (-- 视图 1.1 客户信息视图 SELECT [HC_UserInfo].[userName],[HC_UserInfo].[userCont], [HC_WareInfo].[commName], [HC_OrderInfo].[commNumber],[HC_OrderInfo].[exp] FROM [HC_WareInfo],[HC_CommInfo],[HC_OrderInfo],[HC_UserInfo] WHERE [HC_WareInfo].[Id] = [HC_CommInfo].[WareId] AND [HC_OrderInfo].[UserId] = [HC_UserInfo].[Id] AND [HC_OrderInfo].[CommId] = [HC_CommInfo].[Id]);GOCREATE VIEW CommSellInfoView AS ( -- 视图 1.2 商品销售视图 SELECT [HC_WareInfo].[commName],[HC_WareInfo].[type],[HC_WareInfo].[sourExp],[HC_CommInfo].[exp],SUM([HC_SellInfo].[sellCapac]) AS 销量,[HC_SellInfo].[addTime] FROM [HC_WareInfo],[HC_CommInfo],[HC_SellInfo] WHERE [HC_CommInfo].[WareId]=[HC_WareInfo].[Id] AND [HC_SellInfo].[sellData]=[HC_Wareinfo].[Id] AND [HC_SellInfo].[sellType]<=0 GROUP BY [HC_SellInfo].[addTime],[HC_WareInfo].[commName],[HC_WareInfo].[type],[HC_WareInfo].[sourExp],[HC_CommInfo].[exp]);GO</pre><p>
</p>

SublimeText3 配置 CodeIntel 与 NodeJs 编译环境

<h3> 安装 NodeJs</h3><p> 确保 NodeJs 安装到位. 可直接在系统控制台中调用.</p><p> </p><p>
</p><h3> 安装配置 CodeIntel</h3><p> 从 github 上把源码包 down 下来. 解压文件夹到 Packages</p><p> </p><p> 把以下配置信息根据自己的情况修改然后保存到 Packages/SulimeCodeIntel/.codeintel/config</p><p>
</p><pre class="brush:bash; toolbar:false;">{ "PHP": { "php": 'D://Program Files//phpStudy//php55//php.exe', "phpExtraPaths": [], "phpConfigFile": 'php.ini' }, "JavaScript": { "javascriptExtraPaths": [] }, "Python": { "python": 'D://Python27//python.exe', "pythonExtraPaths": [] }}</pre><p> 如下图:</p> <p>
</p><h3> 配置 SBT 的 NodeJs 编译环境</h3><p> 使用 package control 安装 packageResourceViewer 和 nodejs 插件</p><p> 使用 packageRre...Vie.. 插件打开 node 编译配置</p><p> 输入 resource 打开 PRV 的操作面板</p><p> </p><p> 输入 node 进入 node 插件操作面板</p><p> </p><p> 再次输入 node 查找并 build 文件</p><p> </p><p> 根据实际情况编辑 build 文件保存即可</p><p> </p><h3> 创建编译文件</h3><p> 在 node.exe 文件夹下创建 node.bat 文件并写入</p><p><pre class="brush:as3; toolbar:false;">@echo offfor /F "eol=; tokens=1 delims= " %%a in ('tasklist ^| findstr /c:"node"') do taskkill /f /im %%anode %~dp1%~nx1</pre></p><h3> 运行结果</h3><p> 按 ctrl+b 编译运行</p><p> </p>