G
googune
Unregistered / Unconfirmed
GUEST, unregistred user!
我在网上下了一个软件的源代码 里面只有数据库的sql的源代码 如何才能创建出这个数据库 使我能正常使用这个数据库呢
源代码如下:
第一个文件名:key.sql
--设主键--------------------------------------------------------------------------------------
Alter table czy with nocheck Add
constraint czy_code_pk primary key clustered (
源代码如下:
第一个文件名:key.sql
--设主键--------------------------------------------------------------------------------------
Alter table czy with nocheck Add
constraint czy_code_pk primary key clustered (
代码:
)
Alter table sfjg with nocheck Add
constraint sfjg_bm_pk primary key clustered ([bm])
Alter table gdcl_czda with nocheck Add
constraint gdcl_czda_tmh_pk primary key clustered ([tmh])
--外键-----------------------------------------------------------------------------------------
alter table gdcl_fkjl add
constraint gdcl_fkjl_skr_code_fk foreign key(skr_code) references czy(code)
alter table lscl_fkjl add
constraint lscl_fkjl_skr_code_fk foreign key(skr_code) references czy(code)
alter table gdcl_fkjl add
constraint gdcl_fkjl_tmh_fk foreign key(tmh) references gdcl_czda(tmh)
on update cascade
alter table czyqx add
constraint czyqx_code_fk foreign key(code) references czy(code)
---设索引--------------------------------------------------------------------------------------
create unique index sfjg_yhlx_in on sfjg(yhlx)
create unique index czy_xm_in on czy(xm)
create index Gdcl_czda_czxm_in1 on Gdcl_czda(czxm)
create index Gdcl_czda_cpmh_in2 on Gdcl_czda(cphm)
create index Gdcl_czda_kfz_in3 on Gdcl_czda(kfz)
create index Gdcl_czda_wyglfz_in4 on Gdcl_czda(wyglfz)
create index Gdcl_czda_cwfz_in5 on Gdcl_czda(cwfz)
create index Gdcl_fkjl_tmh_in1 on Gdcl_fkjl(tmh)
create index Gdcl_fkjl_skr_code_in2 on Gdcl_fkjl(skr_code)
create index Gdcl_fkjl_sksj_in3 on Gdcl_fkjl(sksj)
create index Gdcl_fkjl_wxbz_in4 on Gdcl_fkjl(wxbz)
create index Gdcl_fkjl_zfrq_in5 on Gdcl_fkjl(zfrq)
create index lscl_fkjl_tmh_in1 on lscl_fkjl(tmh)
create index lscl_fkjl_cphm_in2 on lscl_fkjl(cpmh)
create index lscl_fkjl_jrsj_in3 on lscl_fkjl(jrsj)
create index lscl_fkjl_wcsj_in4 on lscl_fkjl(wcsj)
create index lscl_fkjl_ljsj_in5 on lscl_fkjl(ljsj)
create index lscl_fkjl_skr_code_in6 on lscl_fkjl(skr_code)
create index lscl_fkjl_zfrq_in7 on lscl_fkjl(zfrq)
create index lscl_fkjl_sksj_in8 on lscl_fkjl(sksj)
create index jczt_tmh_in1 on jczt(tmh)
create index jczt_cpmh_in2 on jczt(cphm)
create index jczt_cz_in3 on jczt(cz)
create index jczt_jcsj_in4 on jczt(jcsj)
create index jczt_lb_in5 on jczt(lb)
create index jczt_zt_in6 on jczt(zt)
create index jczt_czbz_in7 on jczt(czbz)
create index czyqx_code_in1 on czyqx(code)
create index czyqx_cdsm_in2 on czyqx(cdsm)
第二个文件名:proc.sql
--初始化-------------------------------------------------------------
create proc tcxt_csh
as
truncate table sys
truncate table gdcl_fkjl
truncate table lscl_fkjl
truncate table jczt
truncate table sfjg
truncate table czyqx
delete gdcl_czda
delete czy
insert czy(code,xm,mm,qybz) values('000','管理员','','1')
insert czyqx(code,cdsm) values('000','系统应用设置')
insert czyqx(code,cdsm) values('000','更改登录用户')
insert sfjg(bm,yhlx,sffs,sdsfsj,sdsjsf,jgsfsj,jgsjsf,mftcsj,mrxe)
values ('0000','临时用户','按时间收费','120','5','120','2','','20')
insert sfjg(bm,yhlx,yxq,sfje)
values ('0001','月卡用户',1,50)
insert sys(pass,hostname,dyyl)
values ('','小区车辆管理','1')
--固定车收费---------------------------------------------------------------------
create proc tcxt_in_gdcl_fkjl
@Tmh char(9),
@Sflb char(8),
@Yhlx char(8),
@Sfje money,
@Yxrqz datetime output,
@Skr_code char(3),
@sta int output
as
declare @yxq int
if @sflb='办卡费'
begin
select @yxq=yxq from sfjg where yhlx=@yhlx
set @yxrqz=dateadd(month,@yxq,getdate())
insert gdcl_fkjl(tmh,sflb,yhlx,sfje,yxrqz,skr_code,sksj,wxbz)
values (@tmh,@sflb,@yhlx,@sfje,@yxrqz,@skr_code,getdate(),'0')
update gdcl_czda set kfz=@yxrqz where tmh=@tmh --更新卡费日期止
set @sta=0
return
end
else
begin
insert gdcl_fkjl(tmh,sflb,yhlx,sfje,yxrqz,skr_code,sksj,wxbz)
values (@tmh,@sflb,@yhlx,@sfje,@yxrqz,@skr_code,getdate(),'0')
if @sflb='物管费'
begin
update gdcl_czda set wyglfz=@yxrqz where tmh=@tmh --更新物业管理费日期止
end
else
begin
update gdcl_czda set cwfz=@yxrqz where tmh=@tmh --更新车位费日期止
end
set @sta=0
return
end
--固定车辆收费作废----------------------------------------------------------------------------
create proc tcxt_zf_gdcl_fkjl
@xh int,
@sta int output
as
declare @rq datetime,@sflb char(8),@tmh char(9)
declare @kfz datetime,@wyglfz datetime,@cwfz datetime
select @rq=yxrqz,@sflb=sflb,@tmh=tmh from gdcl_fkjl where zid=@xh
select @kfz=kfz,@wyglfz=wyglfz,@cwfz=cwfz from gdcl_czda where tmh=@tmh
if @sflb='办卡费' --作废的恰好是最后一次收费记录,则需要把车主档案中最新的止日期置空
if @rq=@kfz
update gdcl_czda set kfz=null where tmh=@tmh
if @sflb='物管费'
if @rq=@wyglfz
update gdcl_czda set wyglfz=null where tmh=@tmh
if @sflb='车位费'
if @rq=@cwfz
update gdcl_czda set cwfz=null where tmh=@tmh
update gdcl_fkjl set zfrq=getdate(),wxbz='1' where zid=@xh
set @sta=0
--临时车收费-----------------------------------------------------------------------------
create proc tcxt_lscl_fkjl_clsf
@xh int,
@skr_code char(3),
@ljsj numeric(8,2) output, --必须取浮点形,后面四舍五入要用
@je money output,
@sffs char(12) output
as
declare @nowtime datetime,@tmh char(9),@cphm char(15)
declare @mcsf char(4),@mftcsj char(4),@gysf char(4)
declare @Sdsfsj char(4),@Sdsjsf char(4),@Jgsfsj char(4),@Jgsjsf char(4),@Mrxe char(4)
declare @sjdsf char(4),@cg12xssf char(4)
select @sffs=sffs from sfjg where bm='0000'
select @nowtime=getdate()
select @ljsj=datediff(minute,jrsj,@nowtime),@tmh=tmh,@cphm=cphm from lscl_fkjl where zid=@xh
if @sffs='不收费'
begin
select @je=0
update lscl_fkjl set wcsj=@nowtime,sksj=@nowtime,skr_code=@skr_code,ljsj=@ljsj,je=@je where zid=@xh --作离开处理
update jczt set czbz='1' where tmh=@tmh and zt='开入' and lb='临时车' and czbz='0'
insert jczt (Tmh,Cphm,Jcsj,Lb,Zt) values(@tmh,@cphm,@nowtime,'临时车','离开') --写入状态表
end
if @sffs='按次收费'
begin
select @mcsf=mcsf,@mftcsj=mftcsj,@gysf=gysf from sfjg where bm='0000'
set @je=cast(@mcsf as int)
if @mftcsj<>'' --累计停车时间小于免费停车时间,不收费
if @ljsj<=cast(@mftcsj as int)
set @je=0
if @gysf<>''
if @ljsj>=12*60 --超过12小时加收过夜费
set @je=@je+cast(@gysf as int)
update lscl_fkjl set wcsj=@nowtime,sksj=@nowtime,skr_code=@skr_code,ljsj=@ljsj,je=@je where zid=@xh
update jczt set czbz='1' where tmh=@tmh and zt='开入' and lb='临时车' and czbz='0'
insert jczt (Tmh,Cphm,Jcsj,Lb,Zt) values(@tmh,@cphm,@nowtime,'临时车','离开') --写入状态表
end
if @sffs='按时间收费'
begin
select @Sdsfsj=sdsfsj,@Sdsjsf=sdsjsf,@Jgsfsj=jgsfsj,@Jgsjsf=jgsjsf,@Mrxe=mrxe,@Mftcsj=mftcsj from sfjg where bm='0000'
set @je=cast(@sdsjsf as int)
if @mftcsj<>'' --累计停车时间小于免费停车时间,不收费
if @ljsj<=cast(@mftcsj as int)
set @je=0
if @ljsj>cast(@sdsfsj as int) --累计时间大于首段收费时间,加上间隔收费;间隔收费次数,四舍五入后取整
set @je=@je+round((@ljsj-cast(@sdsfsj as int))/cast(@jgsfsj as int) ,0)*cast(@jgsjsf as int)
if @mrxe<>'' --每日限额处理
if @je>cast(@mrxe as int)
set @je=cast(@mrxe as int)
update lscl_fkjl set wcsj=@nowtime,sksj=@nowtime,skr_code=@skr_code,ljsj=@ljsj,je=@je where zid=@xh
update jczt set czbz='1' where tmh=@tmh and zt='开入' and lb='临时车' and czbz='0'
insert jczt (Tmh,Cphm,Jcsj,Lb,Zt) values(@tmh,@cphm,@nowtime,'临时车','离开') --写入状态表
end
if @sffs='按时间段收费'
begin
select @sjdsf=sjdsf,@cg12xssf=cg12xssf from sfjg where bm='0000'
set @je=cast(@sjdsf as int)
if @cg12xssf<>''
if @ljsj>=12*60 --超过12小时加收过夜费
set @je=@je+cast(@cg12xssf as int)
update lscl_fkjl set wcsj=@nowtime,sksj=@nowtime,skr_code=@skr_code,ljsj=@ljsj,je=@je where zid=@xh
update jczt set czbz='1' where tmh=@tmh and zt='开入' and lb='临时车' and czbz='0'
insert jczt (Tmh,Cphm,Jcsj,Lb,Zt) values(@tmh,@cphm,@nowtime,'临时车','离开') --写入状态表
end
----------------------------------------------------------------------------------------------------------
create proc tcxt_ztjs --区内状态
@skr_code char(3),
@cnxycs int output,
@drkrzs int output,
@drkccs int output,
@drsf int output,
@sfbc int output
as
declare @f1 money,@f2 money,@b1 int,@b2 int
select @cnxycs=count(*) from jczt where czbz='0' --场内现停有车数
select @drkrzs=count(*) from jczt where convert(char,jcsj,102)=convert(char,getdate(),102) and zt='开入' --当日开入总数
select @drkccs=count(*) from jczt where convert(char,jcsj,102)=convert(char,getdate(),102) and zt='离开' --当日开出数
select @f1=isnull(sum(sfje),0),@b1=count(*) from gdcl_fkjl where convert(char,sksj,102)=convert(char,getdate(),102) and skr_code=@skr_code and zfrq is null
select @f2=isnull(sum(je),0),@b2=count(*) from lscl_fkjl where convert(char,sksj,102)=convert(char,getdate(),102) and skr_code=@skr_code and zfrq is null
select @drsf=@f1+@f2,@sfbc=@b1+@b2
--个人交款统计--------------------------------------------------------
create procedure tcxt_calculate_grsf
@code char(3),
@qrq datetime,
@zrq datetime,
@gd_sfje money output,
@gd_sfbc int output,
@gd_zfje money output,
@gd_zfbc int output,
@gd_min int output,
@gd_max int output,
@gd_zfjlh varchar(200) output,
@ls_sfje money output,
@ls_sfbc int output,
@ls_zfje money output,
@ls_zfbc int output,
@ls_min int output,
@ls_max int output,
@ls_zfjlh varchar(200) output
as
declare @zfh int,@zfstr varchar(8)
--统计固定车收费情况----------------------
select @gd_sfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_sfbc=count(*) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_zfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_zfbc=count(*) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_min=isnull(min(zid),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_max=isnull(max(zid),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
set @gd_zfjlh=''
declare t1 cursor for
select zid from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
and zfrq is not null order by zid
open t1
fetch next from t1 into @zfh
while @@fetch_status=0
begin
set @zfstr=convert(char,@zfh)
set @gd_zfjlh=@gd_zfjlh+' '+rtrim(@zfstr)
fetch next from t1 into @zfh
end
close t1
deallocate t1
--统计临时车收费情况----------------------
select @ls_sfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_sfbc=count(*) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_zfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_zfbc=count(*) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_min=isnull(min(zid),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_max=isnull(max(zid),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
set @ls_zfjlh=''
declare t1 cursor for
select zid from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
and zfrq is not null order by zid
open t1
fetch next from t1 into @zfh
while @@fetch_status=0
begin
set @zfstr=convert(char,@zfh)
set @ls_zfjlh=@ls_zfjlh+' '+rtrim(@zfstr)
fetch next from t1 into @zfh
end
close t1
deallocate t1
--全体交款统计--------------------------------------------------------------------------
create procedure tcxt_calculate_qtsf
@qrq datetime,
@zrq datetime
as
declare @code char(3),@xm char(6)
declare @gd_sfje money, @gd_sfbc int, @gd_zfje money, @gd_zfbc int,@gd_sjje money
declare @ls_sfje money, @ls_sfbc int, @ls_zfje money, @ls_zfbc int,@ls_sjje money
create table #t1(czyxm char(6),
sfje money,
sfbc int,
zfje money,
zfbc int,
ssje money
)
create table #t2(code char(3),
xm char(6),
lj int default 0
)
--仅对此段时间内有收费记录的操作员进行统计 ,不管收费员是否启用
insert #t2(code) select distinct(skr_code) from gdcl_fkjl where sksj>=@qrq and sksj<=@zrq
insert #t2(code) select distinct(skr_code) from lscl_fkjl where sksj>=@qrq and sksj<=@zrq
update #t2 set #t2.xm=b.xm from #t2 a,czy b where a.code=b.code
select code,xm,sum(lj) lj into #t3 from #t2 group by code,xm
declare c1 cursor for
select code,xm from #t3 order by code
open c1
fetch next from c1 into @code,@xm
while @@fetch_status=0
begin
--统计固定车收费情况----------------------
select @gd_sfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_sfbc=count(*) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_zfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_zfbc=count(*) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
--统计临时车收费情况----------------------
select @ls_sfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_sfbc=count(*) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_zfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_zfbc=count(*) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_sjje=@gd_sfje-@gd_zfje,@ls_sjje=@ls_sfje-@ls_zfje
insert #t1 (czyxm,sfje,sfbc,zfje,zfbc,ssje)
values(@xm,@gd_sfje+@ls_sfje,@gd_sfbc+@ls_sfbc,@gd_zfje+@ls_zfje,@gd_zfbc+@ls_zfbc,@gd_sjje+@ls_sjje)
fetch next from c1 into @code,@xm
end
close c1
deallocate c1
insert #t1 (czyxm,sfje,sfbc,zfje,zfbc,ssje)
select '合 计',isnull(sum(sfje),0),isnull(sum(sfbc),0),isnull(sum(zfje),0),isnull(sum(zfbc),0),isnull(sum(ssje),0) from #t1
select * from #t1
第三个文件名:table.sql
--系统表------------------------------------
create table sys
(Pass Char(8),
Hostname Varchar(30),
Dyyl Char(1)
)
--操作员-------------------------------
create table czy
(code char(3) not null,
xm char(6) not null,
mm char(8) not null,
qybz char(1)
)
--收费价格表------------------------------
create table sfjg
(Bm Char(4) not null,
Yhlx Char(8),
Yxq Int,
Sffs Char(12),
Sfje char(4),
Mftcsj char(4),
Gysf char(4),
Mcsf char(4),
Sdsfsj char(4),
Sdsjsf char(4),
Jgsfsj char(4),
Jgsjsf char(4),
Mrxe char(4),
Kssj Char(6),
Jssj Char(6),
Sjdsf char(4),
Cg12xssf char(4),
)
--固定车辆车主档案-----------------------------------------
create table Gdcl_czda
(
Tmh Char(9) not null,
Czxm Char(6),
Cphm Char(15),
Cxsm Char(6),
Qcms Varchar(50),
kfz Datetime,
Wyglfz Datetime,
Cwfz Datetime,
Zcsj datetime,
Ckcw Char(10),
Zz Char(10),
Lxdh Char(15),
Bz Varchar(50),
)
--固定车辆付款记录---------------------------------------------------------
create table Gdcl_fkjl
(
Tmh Char(9),
Yhlx Char(8),
Sflb Char(8),
Sfje Money,
Yxrqz Datetime,
Skr_code Char(3),
Sksj Datetime,
Wxbz Char(1),
Zfrq Datetime,
Zid Int identity(1,1),
)
--临时车辆付款记录表--------------------------------------------------------
create table Lscl_fkjl
(
Tmh Char(9),
Yhlx Char(8),
Qcms Varchar(20),
Cpmh Char(15),
Jrsj Datetime,
Wcsj Datetime,
Ljsj Int,
Skr_code Char(3),
je money,
Zfrq Datetime,
Sksj Datetime,
Zid int identity(1,1)
)
--进出状态表---------------------------------------------------------
create table Jczt
(
Tmh Char(9),
Cphm Char (15),
Cz Char(6),
Jcsj Datetime,
Lb Char(6),
Zt Char(4),
Czbz Char(1),
Tp Image,
Zid Int identity(1,1)
)
--操作员权限表---------------------------------------------------------
create table czyqx
(
code Char(3),
cdsm Char (20),
zid int identity(1,1)
)
谢谢帮助!