?? 財務管理系統.sql
字號:
[內部編號] [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 ,
[數量單位] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[組別清單] (
[組別編號] [char] (6) NOT NULL ,
[部門編號] [char] (6) NOT NULL ,
[組別名稱] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[職工清單] (
[職工編號] [char] (6) NOT NULL ,
[組別編號] [char] (6) NOT NULL ,
[姓名] [char] (10) NOT NULL ,
[性別] [char] (2) NOT NULL ,
[職務] [char] (10) NULL ,
[密碼] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[部門清單] (
[部門編號] [char] (6) NOT NULL ,
[部門名稱] [char] (30) NOT NULL ,
[部門類型] [int] NOT NULL ,
[遠程用戶號] [int] NULL ,
[用戶名] [char] (15) NULL ,
[密碼] [char] (10) NULL ,
[域名] [char] (15) NULL ,
[撥號名稱] [char] (15) NULL ,
[數據庫別名] [char] (15) NULL ,
[聯網文件] [char] (30) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[項目科目對照表] (
[項目名稱] [char] (40) NULL ,
[項目代碼] [int] NULL ,
[科目代碼] [char] (20) NULL ,
[科目級別] [int] NULL ,
[內部編號] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[憑證表] ADD
PRIMARY KEY CLUSTERED
(
[憑證編號]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[分錄表] ADD
PRIMARY KEY CLUSTERED
(
[內部編號]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[科目表] ADD
PRIMARY KEY CLUSTERED
(
[科目代碼]
) ON [PRIMARY]
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].[分錄表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].[歷史明細帳簿] ADD
CONSTRAINT [歷史明細帳簿_科目代碼_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].[本期分類帳簿] ADD
CONSTRAINT [本期分類帳簿_科目代碼_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].[項目科目對照表] ADD
CONSTRAINT [項目科目對照表_科目代碼_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(分錄編號) from 狀態表 with (tablockx)
select @maxid = case when @maxid is null then 0 else @maxid end
declare mycursor cursor for select 分錄編號 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 分錄編號=@i+1
where current of mycursor
select @i=@i+1
fetch next from mycursor into @a
end
close mycursor
deallocate mycursor
update 狀態表
set 分錄編號=@i --當前最大值
insert into 分錄表(分錄編號,會計期間,憑證字號,憑證編號,摘要,科目代碼,借方,貸方,數量,單價,結算方式,結算號,結算日期)
select a.分錄編號,a.會計期間,a.憑證字號,a.憑證編號,a.摘要,a.科目代碼,a.借方,a.貸方,a.數量,a.單價,a.結算方式,a.結算號,a.結算日期
from 分錄表temp as a,憑證表temp as b
where a.憑證字號=b.憑證字號 and a.憑證編號=b.憑證編號 and a.會計期間=b.會計期間 and b.制單=@操作員
update 憑證表temp
set 借方合計=e.m,貸方合計=e.n from
憑證表temp as b,(select sum(借方) as m,sum(貸方) as n,憑證字號,憑證編號,會計期間 from 分錄表temp group by 憑證字號,憑證編號,會計期間) as e
where e.憑證字號=b.憑證字號 and e.憑證編號=b.憑證編號 and e.會計期間=b.會計期間
insert into 憑證表(內部編號,會計期間,憑證字號,憑證編號,順序號,日期,附單據,審核,過帳,制單,借方合計,貸方合計)
select 內部編號,會計期間,憑證字號,憑證編號,順序號,日期,附單據,審核,過帳,制單,借方合計,貸方合計 from 憑證表temp where 制單=@操作員
update 憑證表
set 憑證狀態='待審',過帳狀態='否'
declare @maxnum int
select @maxnum=max(順序號碼) from 憑證表temp
if @maxnum is not null
begin
update 狀態表
set 順序號=a.順序號+1 from --當前最大值+1,當前可用
狀態表 as b,(select 順序號 from 憑證表temp where 順序號碼=@maxnum) as a
update 狀態表
set 編號=a.憑證編號+1 from --當前最大值+1,當前可用
狀態表 as b,(select 憑證編號,憑證字號 from 憑證表temp where 順序號碼=@maxnum) as a
where a.憑證字號=b.憑證字號
end
delete from 分錄表temp
where 分錄表temp.憑證字號+cast(分錄表temp.憑證編號 as char)+cast(分錄表temp.會計期間 as char) in (select 憑證字號+cast(憑證編號 as char)+cast(會計期間 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憑證過帳 @操作員 char(20)
as
begin transaction
insert into 本期明細帳簿(分錄編號, 會計期間, 憑證字號, 憑證編號, 摘要, 科目代碼, 借方, 貸方, 數量, 單價, 結算方式, 結算號, 結算日期)
select 分錄編號, a.會計期間, a.憑證字號, a.憑證編號, 摘要, 科目代碼, 借方, 貸方, 數量, 單價, 結算方式, 結算號, 結算日期 from 分錄表 as a,憑證表 as b
where a.會計期間=b.會計期間 and a.憑證字號= b.憑證字號 and a.憑證編號=b.憑證編號 and b.憑證狀態='通過' and b.過帳狀態='否'
update 憑證表
set 過帳狀態='是',過帳=@操作員
where 憑證狀態='通過' and 過帳狀態='否'
update 憑證表
set 過帳=@操作員
where 憑證狀態='不通過'
insert into 分錄表歷史(分錄編號, 會計期間, 憑證字號, 憑證編號, 摘要, 科目代碼, 借方, 貸方, 數量, 單價, 結算方式, 結算號, 結算日期)
select 分錄編號, a.會計期間, a.憑證字號, a.憑證編號, 摘要, 科目代碼, 借方, 貸方, 數量, 單價, 結算方式, 結算號, 結算日期 from 分錄表 as a,憑證表 as b
where a.會計期間=b.會計期間 and a.憑證字號= b.憑證字號 and a.憑證編號=b.憑證編號 and (b.過帳狀態='是' or b.憑證狀態='不通過')
insert into 憑證表歷史(內部編號, 憑證狀態, 會計期間, 憑證字號, 憑證編號, 順序號, 日期, 附單據, 審核, 過帳, 制單, 借方合計, 貸方合計, 過帳狀態)
select 內部編號, 憑證狀態, 會計期間, 憑證字號, 憑證編號, 順序號, 日期, 附單據, 審核, 過帳, 制單, 借方合計, 貸方合計, 過帳狀態 from 憑證表
where 過帳狀態='是' or 憑證狀態='不通過'
delete from 分錄表
from (分錄表 as a join 憑證表 as b on a.會計期間=b.會計期間 and a.憑證字號 =b.憑證字號 and a.憑證編號=b.憑證編號)
where b.過帳狀態='是' or b.憑證狀態='不通過'
delete from 憑證表
where 過帳狀態='是' or 憑證狀態='不通過'
--計算余額
--update null to 0
update 本期明細帳簿
set 借方=(case when 借方 is null then 0 else 借方 end), 貸方=(case when 貸方 is null then 0 else 貸方 end)
where 余額標記 is null or 余額標記 <>'是'
--update 余額方向
update 本期明細帳簿
set 余額方向=a.余額方向
from (select 科目代碼,余額方向 from 科目余額表) as a
where 本期明細帳簿.科目代碼=a.科目代碼 and (余額標記 is null or 余額標記 <>'是')
--update 科目余額表;本期明細帳簿--余額
declare mycursor cursor for select 分錄編號,會計期間,科目代碼,借方,貸方,余額方向,余額,余額標記 from 本期明細帳簿 where 余額標記 is null or 余額標記<>'是'
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 本期借方發生額=(case when 本期借方發生額 is null then @jf else 本期借方發生額+@jf end),
本期貸方發生額=(case when 本期貸方發生額 is null then @df else 本期貸方發生額+@df end),
本年借方累計發生額=(case when 本年借方累計發生額 is null then @jf else 本年借方累計發生額+@jf end),
本年貸方累計發生額=(case when 本年貸方累計發生額 is null then @df else 本年貸方累計發生額+@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),
最后結算分錄=@bh,
會計期間=@qj
where 科目代碼=@dm and 會計期間=@qj
select 科目代碼,@fx,會計期間,本期借方余額,本期貸方余額 from 科目余額表 where 科目代碼=@dm and 會計期間=@qj
update 本期明細帳簿
set 余額=case when @fx='借'
then (select 本期借方余額 from 科目余額表 where 科目代碼=@dm and 會計期間=@qj)
else (select 本期貸方余額 from 科目余額表 where 科目代碼=@dm and 會計期間=@qj)
end, --a.本期借方余額
余額標記='是'
-- from 本期明細帳簿 as b
where current of mycursor
--(select 科目代碼,會計期間,本期借方余額,本期貸方余額 from 科目余額表 where 科目代碼=@dm and 會計期間=@qj) as a
select * from 本期明細帳簿
--select * from 科目余額表 where 本期借方發生額 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調整憑證表
as
begin transaction
declare @maxnum int
select @maxnum=max(順序號碼) from 憑證表temp
delete 憑證表temp
where 順序號碼>(select min(順序號碼) from 憑證表temp as a,(select 憑證字號,憑證編號,會計期間 from 憑證表temp where 順序號碼=@Maxnum) as b where a.憑證編號=b.憑證編號 and a.憑證字號=b.憑證字號 and a.會計期間=b.會計期間)
update 分錄表temp
set 順序號碼=a.順序號碼 from
(select '順序號碼'=min(順序號碼) from 憑證表temp) as a
commit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -