web-ylbtech(合同管理)-数据库设计

ylbtech-DatabaseDesgin:web-ylbtech(合同管理)-数据库设计

 

1.A，数据库关系图

 

1.B，数据库设计脚本

 /App_Data/sql-basic.sql

-- =============================================
-- DatabaseName: ContractManagementSystem
-- remark: 合同管理系统
-- author: ylb
-- date: 14:27 2012-8-29
-- =============================================
use master
GO
IF EXISTS (SELECT * 
       FROM   master..sysdatabases 
       WHERE  name = N'ContractManagementSystem')
    DROP DATABASE ContractManagementSystem
GO

CREATE DATABASE ContractManagementSystem
GO

GO
use ContractManagementSystem


go
-- =============================================
-- ylb: 1,分属地【区域】
-- =============================================
create table Area
(
areaId int primary key identity(1,1),    --编号
areaName varchar(200)            --区域名称
)

go
insert into Area(areaName) values('公司主管')

go
-- =============================================
-- ylb: 2,项目
-- remark: 软件类别
-- =============================================
create table SoftwareType
(
softwareTypeId int primary key identity(1,1),    --编号
softwareTypeName varchar(200)            --软件名称
)

go
insert into SoftwareType(softwareTypeName) values('单机版')

go
-- =============================================
-- ylb: 3,合同状态
-- =============================================
create table ContractType
(
contractTypeId int primary key identity(1,1),    --编号
contractTypeName varchar(200)            --合同名称
)

go
insert into ContractType(contractTypeName) values('服务合同')


go
-- =============================================
-- ylb: 4,公司单位
-- =============================================
create table Company
(
companyId int primary key identity(1,1),    --编号
companyName varchar(200)            --单位名称【签署单位】
)

go
insert into Company(companyName) values('清华大学')


go
-- =============================================
-- ylb: 5,用户表
-- =============================================
create table Users
(
userId int primary key identity(1,1),    --编号
username varchar(100) unique not null,            --姓名【UN】
userpass varchar(100) not null,            --密码
[role] int default(0),            --角色【权限】 0:普通用户；1：管理员；2：业务员
areaId int references Area(areaId)    --区域编号【FK】
)

go
insert into Users(username,userpass,role,areaId) values('admin','m123456',1,1)
insert into Users(username,userpass,role,areaId) values('普通用户','m123456',0,1)

go
-- =============================================
-- ylb: 7,行业类型
-- =============================================
create table IndustryType
(
IndustryTypeId int primary key identity(1,1),    --编号
IndustryTypeName varchar(200)            --行业名称
)
go
insert into IndustryType(IndustryTypeName) values('工业')

go
-- =============================================
-- ylb: 6,合同表
-- =============================================
create table Contract
(
contractId int primary key identity(1,1),    --编号
contractNumbers varchar(100) not null,        --合同编号
areaId int,        --所属区域【FK】
softwareTypeId varchar(100),            --软件类型【多选，多个之间用‘,’号隔开】
contractTypeId int,    --合同状态【FK】

signUnit varchar(200),        --签订单位
higherLeaveUnits varchar(200),    --上级单位
payment varchar(200),        --支付方式【即，付费方式】
principal varchar(100),        --合同负责人
contact varchar(100),        --客户联系人

telephone varchar(100),        --客户联系电话
address varchar(200),        --客户单位地址
amountType varchar(100),    --合同金额
signTime datetime,        --签订时间
endTime datetime,        --合同有效期，即到期时间

remark varchar(500),        --备注
companyID int,    --签署单位
softwareTypeRemark varchar(500),    -- 软件类型备注
paidAmount decimal,            --已付金额
contractAmount decimal,            --合同金额

contractLife datetime,            --合同到期时间
flagRenew varchar(50) default('执行中'),        --合同状态, 执行中、结束、意外终止
renewYears int,                --续费年限
billingDate varchar(500),        --开票日期
jingShouRenId int,                --经手人
managerId int,                    --管理人    
flag int default(0),                --合同到期提醒表示

industryTypeId int,                    --行业类型编号
amountType2 varchar(200),            --支付类型【服务】
paidAmount2 decimal,            --已付金额【服务】
contractAmount2 decimal            --合同金额【服务】
)
go
print '合同管理系统数据库创建完成！'

-- =============================================
-- DatabaseName: ContractManagementSystem
-- remark: 合同管理系统
-- author: ylb
-- date: 14:27 2012-8-29
-- =============================================
use master
GO
IF EXISTS (SELECT * 
       FROM   master..sysdatabases 
       WHERE  name = N'ContractManagementSystem')
    DROP DATABASE ContractManagementSystem
GO

CREATE DATABASE ContractManagementSystem
GO

GO
use ContractManagementSystem


go
-- =============================================
-- ylb: 1,分属地【区域】
-- =============================================
create table Area
(
areaId int primary key identity(1,1),    --编号
areaName varchar(200)            --区域名称
)

go
insert into Area(areaName) values('公司主管')

go
-- =============================================
-- ylb: 2,项目
-- remark: 软件类别
-- =============================================
create table SoftwareType
(
softwareTypeId int primary key identity(1,1),    --编号
softwareTypeName varchar(200)            --软件名称
)

go
insert into SoftwareType(softwareTypeName) values('单机版')

go
-- =============================================
-- ylb: 3,合同状态
-- =============================================
create table ContractType
(
contractTypeId int primary key identity(1,1),    --编号
contractTypeName varchar(200)            --合同名称
)

go
insert into ContractType(contractTypeName) values('服务合同')


go
-- =============================================
-- ylb: 4,公司单位
-- =============================================
create table Company
(
companyId int primary key identity(1,1),    --编号
companyName varchar(200)            --单位名称【签署单位】
)

go
insert into Company(companyName) values('清华大学')


go
-- =============================================
-- ylb: 5,用户表
-- =============================================
create table Users
(
userId int primary key identity(1,1),    --编号
username varchar(100) unique not null,            --姓名【UN】
userpass varchar(100) not null,            --密码
[role] int default(0),            --角色【权限】 0:普通用户；1：管理员
areaId int references Area(areaId)    --区域编号【FK】
)

go
insert into Users(username,userpass,role,areaId) values('admin','m123456',1,1)
insert into Users(username,userpass,role,areaId) values('普通用户','m123456',0,1)

go
-- =============================================
-- ylb: 7,行业类型
-- =============================================
create table IndustryType
(
IndustryTypeId int primary key identity(1,1),    --编号
IndustryTypeName varchar(200)            --行业名称
)
go
insert into IndustryType(IndustryTypeName) values('工业')

go
-- =============================================
-- ylb: 6,合同表
-- =============================================
create table Contract
(
contractId int primary key identity(1,1),    --编号
contractNumbers varchar(100) not null,        --合同编号
areaId int,        --所属区域【FK】
softwareTypeId varchar(100),            --软件类型【多选，多个之间用‘,’号隔开】
contractTypeId int,    --合同状态【FK】

signUnit varchar(200),        --签订单位
higherLeaveUnits varchar(200),    --上级单位
payment varchar(200),        --支付方式【即，付费方式】
principal varchar(100),        --合同负责人
contact varchar(100),        --客户联系人

telephone varchar(100),        --客户联系电话
address varchar(200),        --客户单位地址
amountType varchar(100),    --合同金额
signTime datetime,        --签订时间
endTime datetime,        --合同有效期，即到期时间

remark varchar(500),        --备注
companyID int,    --签署单位
softwareTypeRemark varchar(500),    -- 软件类型备注
paidAmount decimal,            --已付金额
contractAmount decimal,            --合同金额

contractLife datetime,            --合同到期时间
flagRenew varchar(50) default('合同正常'),        --合同状态, 0：否；1：是
renewYears int,                --续费年限
billingDate varchar(500),        --开票日期
jingShouRenId int,                --经手人
managerId int,                    --管理人    
flag int default(0),                --合同到期提醒表示

industryTypeId int,                    --行业类型编号
amountType2 varchar(200),            --支付类型【服务】
paidAmount2 decimal,            --已付金额【服务】
contractAmount2 decimal            --合同金额【服务】
)
go
print '合同管理系统数据库创建完成！'

/App_Data/update-basic.sql

-- =============================================
-- DatabaseName: ContractManagementSystem
-- remark: 对合同管理系统后期修改
-- author: ylb
-- date: 14:27 2012-8-29
-- =============================================
use ContractManagementSystem

go
alter table Contract
add contractStartTime datetime --合同开始时间

go
alter table Contract
add databaseTypes varchar(200),    --数据类型列表
customerIP varchar(200)        --客户IP地址

1.C，功能实现代码

 /App_Data/select/1,Area.sql

use ContractManagementSystem
go

--1,GetAll
select areaId,areaName from Area order by areaId desc

go
--2,Add
insert into Area(areaName) values('公司主管')

go
--3,GetModel
select areaId,areaName from Area where areaId=1

go
--4,Update
update Area set areaName='' where areaId=1

go
--5,Delete
delete Area where areaId=1

 /App_Data/select/2,SoftwareType.sql

use ContractManagementSystem
go

--1,GetAll
select softwareTypeId,softwareTypeName from SoftwareType order by softwareTypeId desc

go
--2,Add
insert into SoftwareType(softwareTypeName) values('单版软件')

go
--3,GetModel
select softwareTypeId,softwareTypeName from SoftwareType where softwareTypeId=1

go
--4,Update
update SoftwareType set softwareTypeName='dd' where softwareTypeId=1

go
--5, Delete
delete SoftwareType where softwareTypeId=1

 /App_Data/select/3,ContractType.sql

use ContractManagementSystem
go
--1,GetAll
select contractTypeId,contractTypeName from ContractType order by contractTypeId desc

go
--2,Add
insert into ContractType(contractTypeName) values('年服务合同')

go
--3,GetModel
select contractTypeId,contractTypeName from ContractType where contractTypeId=1

go
--4,Update
update ContractType set contractTypeName='' where contractTypeId=1

go
--5,Delete
delete ContractType where contractTypeId=1

 /App_Data/select/4,Company.sql

use ContractManagementSystem
go

--1,GetAll
select companyId,companyName from Company order by companyId desc

go
--2,Add
insert into Company(companyName) values('XX公司')

go
--3,GetModel
select companyId,companyName from Company where companyId=1

go
--4,Update
update Company set companyName='' where companyId=1

go
--5,Delete
delete Company where companyId=1

 /App_Data/select/5,Users.sql

use ContractManagementSystem
go

--1,GetList查询所有用户
select userId,username from Users order by userId desc

--2,login

select count(*) from Users where userId=1 and userpass='m123456'

go
--3,Add
select count(*) from Users where username=''
go
insert into Users(username,userpass,[role],areaId) values('admin','m123456',1,1)

go
--4,GetAll
select userId,username,userpass,role,a.areaId,areaName from Users u inner join Area a on u.areaId=a.areaId  order by userId desc
go

go
--5,GetModel
select userId,username,userpass,role,areaId from Users where userId=1

go
--6,Update
--remark:"admin"不允许修改。
--在修改之前
update Users set username='',userpass='',role=1,areaId=1 where userId=1

go
--7,Delete
delete Users where userId=1

go
--8, ChangePassword
update Users set userpass='' where userId=1

,6

 /App_Data/select/6,Contract.sql

use ContractManagementSystem
go 

--1,GetAll
select contractId,contractNumbers,signUnit,endTime from [Contract] order by contractId desc

go
--2,Add
select contractId,contractNumbers,areaId,softwareTypeId,contractTypeId
,signUnit,higherLeaveUnits,payment,principal,contact
,telephone ,[address],amount,signTime,endTime
,remark,companyId,softwareTypeRemark,paidAmount,contractAmount
,contractLife,flagRenew,renewYears,billingDate,jingShouRenId from [Contract]

insert into [Contract](contractNumbers,areaId,softwareTypeId,contractTypeId
,signUnit,higherLeaveUnits,payment,principal,contact
,telephone ,[address],amount,signTime,endTime
,remark,companyId,softwareTypeRemark,paidAmount,contractAmount
,contractLife,flagRenew,renewYears,billingDate,jingShouRenId) values()

go
--3,GetModel
select contractId,contractNumbers,areaId,softwareTypeId,contractTypeId
,signUnit,higherLeaveUnits,payment,principal,contact
,telephone ,[address],amount,signTime,endTime
,remark,companyId,softwareTypeRemark,paidAmount,contractAmount
,contractLife,flagRenew,renewYears,billingDate,jingShouRenId from [Contract] where contractId=1

go
--4,Update
update [Contract] set contractNumbers='' where contractId=1


go
--5,Delete
delete [Contract] where contractId=1

go
--6,GetAllBySignUnit
select contractId,contractNumbers,signUnit,endTime from [Contract] 
where signUnit like '%dd%'
order by contractId desc

go
--7,HandoverOfPower
update [Contract] set jingShouRenId=0 where jingShouRenId=0


go
--8,GetAllEndTime
select * from [Contract]
where dateAdd(MM,1,endTime)>getdate()

go
--9,EndTimePass
update [Contract] set flag=1 where contractId=0


go
--10,GetPrincinal
select distinct principal from [Contract] order by principal

 /App_Data/select/7,IndustryType.sql

use ContractManagementSystem
go
--1,GetAll
select IndustryTypeId,IndustryTypeName from IndustryType order by IndustryTypeId desc

go
--2,Add
insert into IndustryType(IndustryTypeName) values('工业')

go
--3,GetModel
select IndustryTypeId,IndustryTypeName from IndustryType where IndustryTypeId=1

go
--4,Update
update IndustryType set IndustryTypeName='' where IndustryTypeId=1

go
--5,Delete
delete IndustryType where IndustryTypeId=1

/App_Data/2,report/

 /App_Data/2,report/1,ReportByYear.sql

use ContractManagementSystem
go
--==============================================
--1,以年份统计报表
--==============================================
select * from [Contract]

--select COUNT( amountType =='True') from [Contract]

select * from Area a inner join [Contract] c on a.areaId=c.areaId

go

select areaName,COUNT(areaName) '数量',SUM(paidAmount) '购买【已付金额】',SUM(contractAmount) '购买【合同金额】',SUM(paidAmount2) '服务【已付金额】'
,SUM(contractAmount2) '服务【合同金额】',(SUM(paidAmount)+SUM(paidAmount2)) '小计【已付金额】',(SUM(contractAmount)+SUM(contractAmount2)) '小计【合同金额】'
,((SUM(contractAmount)+SUM(contractAmount2))-(SUM(paidAmount)+SUM(paidAmount2))) '小计【差金额】' from Area a 
 inner join [Contract] c on a.areaId=c.areaId

group by areaName

 /App_Data/2,report/2,NewOrOldContract.sql

use ContractManagementSystem
go

--2012年新合同
select COUNT(contractId) from [Contract]  where DATEPART(YYYY,signTime)='2012'
--2012年旧合同
select COUNT(contractId) from [Contract]  where DATEPART(YYYY,endTime)='2012'

作者：ylbtech 出处：http://ylbtech.cnblogs.com/ 本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。