?? 財(cái)務(wù)管理系統(tǒng).sql
字號(hào):
[內(nèi)部編號(hào)] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[科目表] (
[科目代碼] [char] (20) NOT NULL ,
[科目名稱] [char] (20) NULL ,
[助記碼] [char] (20) NULL ,
[科目類別] [char] (20) NULL ,
[是否存貨科目] [char] (10) NULL ,
[余額方向] [char] (4) NULL ,
[數(shù)量單位] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[組別清單] (
[組別編號(hào)] [char] (6) NOT NULL ,
[部門(mén)編號(hào)] [char] (6) NOT NULL ,
[組別名稱] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[職工清單] (
[職工編號(hào)] [char] (6) NOT NULL ,
[組別編號(hào)] [char] (6) NOT NULL ,
[姓名] [char] (10) NOT NULL ,
[性別] [char] (2) NOT NULL ,
[職務(wù)] [char] (10) NULL ,
[密碼] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[部門(mén)清單] (
[部門(mén)編號(hào)] [char] (6) NOT NULL ,
[部門(mén)名稱] [char] (30) NOT NULL ,
[部門(mén)類型] [int] NOT NULL ,
[遠(yuǎn)程用戶號(hào)] [int] NULL ,
[用戶名] [char] (15) NULL ,
[密碼] [char] (10) NULL ,
[域名] [char] (15) NULL ,
[撥號(hào)名稱] [char] (15) NULL ,
[數(shù)據(jù)庫(kù)別名] [char] (15) NULL ,
[聯(lián)網(wǎng)文件] [char] (30) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[項(xiàng)目科目對(duì)照表] (
[項(xiàng)目名稱] [char] (40) NULL ,
[項(xiàng)目代碼] [int] NULL ,
[科目代碼] [char] (20) NULL ,
[科目級(jí)別] [int] NULL ,
[內(nèi)部編號(hào)] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[憑證表] ADD
PRIMARY KEY CLUSTERED
(
[憑證編號(hào)]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[分錄表] ADD
PRIMARY KEY CLUSTERED
(
[內(nèi)部編號(hào)]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[科目表] ADD
PRIMARY KEY CLUSTERED
(
[科目代碼]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[分錄表] ADD
CONSTRAINT [分錄表_憑證編號(hào)_fk] FOREIGN KEY
(
[憑證編號(hào)]
) REFERENCES [dbo].[憑證表] (
[憑證編號(hào)]
)
GO
ALTER TABLE [dbo].[分錄表] ADD
CONSTRAINT [分錄表_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[分錄表temp] ADD
CONSTRAINT [分錄表temp_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[分錄表歷史] ADD
CONSTRAINT [分錄表歷史_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[歷史分類賬簿] ADD
CONSTRAINT [歷史分類賬簿_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[歷史明細(xì)賬簿] ADD
CONSTRAINT [歷史明細(xì)賬簿_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[本年分類賬簿] ADD
CONSTRAINT [本年分類賬簿_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[本年明細(xì)賬簿] ADD
CONSTRAINT [本年明細(xì)賬簿_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[本期分類賬簿] ADD
CONSTRAINT [本期分類賬簿_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[本期明細(xì)賬簿] ADD
CONSTRAINT [本期明細(xì)賬簿_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[科目余額表] ADD
CONSTRAINT [科目余額表_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
ALTER TABLE [dbo].[項(xiàng)目科目對(duì)照表] ADD
CONSTRAINT [項(xiàng)目科目對(duì)照表_科目代碼_fk] FOREIGN KEY
(
[科目代碼]
) REFERENCES [dbo].[科目表] (
[科目代碼]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sf_pz憑證刪除后
as
begin tran
delete from 憑證表temp
delete from 分錄表temp
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc sf_pz憑證錄入 @操作員 char(20)
as
begin transaction
--declare @操作員 char(20)
--select @操作員='wy'
declare @maxid int
select @maxid=max(分錄編號(hào)) from 狀態(tài)表 with (tablockx)
select @maxid = case when @maxid is null then 0 else @maxid end
declare mycursor cursor for select 分錄編號(hào) from 分錄表temp
open mycursor
declare @a int
declare @i int
select @i=@maxid
fetch next from mycursor into @a
while (@@fetch_status=0)
begin
update 分錄表temp
set 分錄編號(hào)=@i+1
where current of mycursor
select @i=@i+1
fetch next from mycursor into @a
end
close mycursor
deallocate mycursor
update 狀態(tài)表
set 分錄編號(hào)=@i --當(dāng)前最大值
insert into 分錄表(分錄編號(hào),會(huì)計(jì)期間,憑證字號(hào),憑證編號(hào),摘要,科目代碼,借方,貸方,數(shù)量,單價(jià),結(jié)算方式,結(jié)算號(hào),結(jié)算日期)
select a.分錄編號(hào),a.會(huì)計(jì)期間,a.憑證字號(hào),a.憑證編號(hào),a.摘要,a.科目代碼,a.借方,a.貸方,a.數(shù)量,a.單價(jià),a.結(jié)算方式,a.結(jié)算號(hào),a.結(jié)算日期
from 分錄表temp as a,憑證表temp as b
where a.憑證字號(hào)=b.憑證字號(hào) and a.憑證編號(hào)=b.憑證編號(hào) and a.會(huì)計(jì)期間=b.會(huì)計(jì)期間 and b.制單=@操作員
update 憑證表temp
set 借方合計(jì)=e.m,貸方合計(jì)=e.n from
憑證表temp as b,(select sum(借方) as m,sum(貸方) as n,憑證字號(hào),憑證編號(hào),會(huì)計(jì)期間 from 分錄表temp group by 憑證字號(hào),憑證編號(hào),會(huì)計(jì)期間) as e
where e.憑證字號(hào)=b.憑證字號(hào) and e.憑證編號(hào)=b.憑證編號(hào) and e.會(huì)計(jì)期間=b.會(huì)計(jì)期間
insert into 憑證表(內(nèi)部編號(hào),會(huì)計(jì)期間,憑證字號(hào),憑證編號(hào),順序號(hào),日期,附單據(jù),審核,過(guò)賬,制單,借方合計(jì),貸方合計(jì))
select 內(nèi)部編號(hào),會(huì)計(jì)期間,憑證字號(hào),憑證編號(hào),順序號(hào),日期,附單據(jù),審核,過(guò)賬,制單,借方合計(jì),貸方合計(jì) from 憑證表temp where 制單=@操作員
update 憑證表
set 憑證狀態(tài)='待審',過(guò)賬狀態(tài)='否'
declare @maxnum int
select @maxnum=max(順序號(hào)碼) from 憑證表temp
if @maxnum is not null
begin
update 狀態(tài)表
set 順序號(hào)=a.順序號(hào)+1 from --當(dāng)前最大值+1,當(dāng)前可用
狀態(tài)表 as b,(select 順序號(hào) from 憑證表temp where 順序號(hào)碼=@maxnum) as a
update 狀態(tài)表
set 編號(hào)=a.憑證編號(hào)+1 from --當(dāng)前最大值+1,當(dāng)前可用
狀態(tài)表 as b,(select 憑證編號(hào),憑證字號(hào) from 憑證表temp where 順序號(hào)碼=@maxnum) as a
where a.憑證字號(hào)=b.憑證字號(hào)
end
delete from 分錄表temp
where 分錄表temp.憑證字號(hào)+cast(分錄表temp.憑證編號(hào) as char)+cast(分錄表temp.會(huì)計(jì)期間 as char) in (select 憑證字號(hào)+cast(憑證編號(hào) as char)+cast(會(huì)計(jì)期間 as char) from 憑證表temp)
delete from 憑證表temp
where 制單=@操作員
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc sf_pz憑證過(guò)賬 @操作員 char(20)
as
begin transaction
insert into 本期明細(xì)賬簿(分錄編號(hào), 會(huì)計(jì)期間, 憑證字號(hào), 憑證編號(hào), 摘要, 科目代碼, 借方, 貸方, 數(shù)量, 單價(jià), 結(jié)算方式, 結(jié)算號(hào), 結(jié)算日期)
select 分錄編號(hào), a.會(huì)計(jì)期間, a.憑證字號(hào), a.憑證編號(hào), 摘要, 科目代碼, 借方, 貸方, 數(shù)量, 單價(jià), 結(jié)算方式, 結(jié)算號(hào), 結(jié)算日期 from 分錄表 as a,憑證表 as b
where a.會(huì)計(jì)期間=b.會(huì)計(jì)期間 and a.憑證字號(hào)= b.憑證字號(hào) and a.憑證編號(hào)=b.憑證編號(hào) and b.憑證狀態(tài)='通過(guò)' and b.過(guò)賬狀態(tài)='否'
update 憑證表
set 過(guò)賬狀態(tài)='是',過(guò)賬=@操作員
where 憑證狀態(tài)='通過(guò)' and 過(guò)賬狀態(tài)='否'
update 憑證表
set 過(guò)賬=@操作員
where 憑證狀態(tài)='不通過(guò)'
insert into 分錄表歷史(分錄編號(hào), 會(huì)計(jì)期間, 憑證字號(hào), 憑證編號(hào), 摘要, 科目代碼, 借方, 貸方, 數(shù)量, 單價(jià), 結(jié)算方式, 結(jié)算號(hào), 結(jié)算日期)
select 分錄編號(hào), a.會(huì)計(jì)期間, a.憑證字號(hào), a.憑證編號(hào), 摘要, 科目代碼, 借方, 貸方, 數(shù)量, 單價(jià), 結(jié)算方式, 結(jié)算號(hào), 結(jié)算日期 from 分錄表 as a,憑證表 as b
where a.會(huì)計(jì)期間=b.會(huì)計(jì)期間 and a.憑證字號(hào)= b.憑證字號(hào) and a.憑證編號(hào)=b.憑證編號(hào) and (b.過(guò)賬狀態(tài)='是' or b.憑證狀態(tài)='不通過(guò)')
insert into 憑證表歷史(內(nèi)部編號(hào), 憑證狀態(tài), 會(huì)計(jì)期間, 憑證字號(hào), 憑證編號(hào), 順序號(hào), 日期, 附單據(jù), 審核, 過(guò)賬, 制單, 借方合計(jì), 貸方合計(jì), 過(guò)賬狀態(tài))
select 內(nèi)部編號(hào), 憑證狀態(tài), 會(huì)計(jì)期間, 憑證字號(hào), 憑證編號(hào), 順序號(hào), 日期, 附單據(jù), 審核, 過(guò)賬, 制單, 借方合計(jì), 貸方合計(jì), 過(guò)賬狀態(tài) from 憑證表
where 過(guò)賬狀態(tài)='是' or 憑證狀態(tài)='不通過(guò)'
delete from 分錄表
from (分錄表 as a join 憑證表 as b on a.會(huì)計(jì)期間=b.會(huì)計(jì)期間 and a.憑證字號(hào) =b.憑證字號(hào) and a.憑證編號(hào)=b.憑證編號(hào))
where b.過(guò)賬狀態(tài)='是' or b.憑證狀態(tài)='不通過(guò)'
delete from 憑證表
where 過(guò)賬狀態(tài)='是' or 憑證狀態(tài)='不通過(guò)'
--計(jì)算余額
--update null to 0
update 本期明細(xì)賬簿
set 借方=(case when 借方 is null then 0 else 借方 end), 貸方=(case when 貸方 is null then 0 else 貸方 end)
where 余額標(biāo)記 is null or 余額標(biāo)記 <>'是'
--update 余額方向
update 本期明細(xì)賬簿
set 余額方向=a.余額方向
from (select 科目代碼,余額方向 from 科目余額表) as a
where 本期明細(xì)賬簿.科目代碼=a.科目代碼 and (余額標(biāo)記 is null or 余額標(biāo)記 <>'是')
--update 科目余額表;本期明細(xì)賬簿--余額
declare mycursor cursor for select 分錄編號(hào),會(huì)計(jì)期間,科目代碼,借方,貸方,余額方向,余額,余額標(biāo)記 from 本期明細(xì)賬簿 where 余額標(biāo)記 is null or 余額標(biāo)記<>'是'
open mycursor
declare @bh int,@qj int,@dm char(20),@jf money,@df money,@fx char(2),@ye money,@bj char(4)
fetch next from mycursor into @bh,@qj,@dm,@jf,@df,@fx,@ye,@bj
--select @bh,@qj,@dm,@jf,@df,@fx,@ye,@bj
while (@@fetch_status=0)
begin
update 科目余額表
set 本期借方發(fā)生額=(case when 本期借方發(fā)生額 is null then @jf else 本期借方發(fā)生額+@jf end),
本期貸方發(fā)生額=(case when 本期貸方發(fā)生額 is null then @df else 本期貸方發(fā)生額+@df end),
本年借方累計(jì)發(fā)生額=(case when 本年借方累計(jì)發(fā)生額 is null then @jf else 本年借方累計(jì)發(fā)生額+@jf end),
本年貸方累計(jì)發(fā)生額=(case when 本年貸方累計(jì)發(fā)生額 is null then @df else 本年貸方累計(jì)發(fā)生額+@df end),
本期借方余額=(case when @fx='借' then (case when 本期借方余額 is null then @jf-@df else 本期借方余額+@jf-@df end) else 0 end),
本期貸方余額=(case when @fx='貸' then (case when 本期貸方余額 is null then @df-@jf else 本期貸方余額+@df-@jf end) else 0 end),
最后結(jié)算分錄=@bh,
會(huì)計(jì)期間=@qj
where 科目代碼=@dm and 會(huì)計(jì)期間=@qj
select 科目代碼,@fx,會(huì)計(jì)期間,本期借方余額,本期貸方余額 from 科目余額表 where 科目代碼=@dm and 會(huì)計(jì)期間=@qj
update 本期明細(xì)賬簿
set 余額=case when @fx='借'
then (select 本期借方余額 from 科目余額表 where 科目代碼=@dm and 會(huì)計(jì)期間=@qj)
else (select 本期貸方余額 from 科目余額表 where 科目代碼=@dm and 會(huì)計(jì)期間=@qj)
end, --a.本期借方余額
余額標(biāo)記='是'
-- from 本期明細(xì)賬簿 as b
where current of mycursor
--(select 科目代碼,會(huì)計(jì)期間,本期借方余額,本期貸方余額 from 科目余額表 where 科目代碼=@dm and 會(huì)計(jì)期間=@qj) as a
select * from 本期明細(xì)賬簿
--select * from 科目余額表 where 本期借方發(fā)生額 is not null
fetch next from mycursor into @bh,@qj,@dm,@jf,@df,@fx,@ye,@bj
end
close mycursor
deallocate mycursor
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE proc sf_pz調(diào)整憑證表
as
begin transaction
declare @maxnum int
select @maxnum=max(順序號(hào)碼) from 憑證表temp
delete 憑證表temp
where 順序號(hào)碼>(select min(順序號(hào)碼) from 憑證表temp as a,(select 憑證字號(hào),憑證編號(hào),會(huì)計(jì)期間 from 憑證表temp where 順序號(hào)碼=@Maxnum) as b where a.憑證編號(hào)=b.憑證編號(hào) and a.憑證字號(hào)=b.憑證字號(hào) and a.會(huì)計(jì)期間=b.會(huì)計(jì)期間)
update 分錄表temp
set 順序號(hào)碼=a.順序號(hào)碼 from
(select '順序號(hào)碼'=min(順序號(hào)碼) from 憑證表temp) as a
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -