--予算推移表帳票用のプロシージャ
CREATE PROCEDURE dm_P01004S
(
@C_PRJ VARCHAR(12)
)
AS
BEGIN
DECLARE @INTINDEX INT --1～10
DECLARE @SQL AS VARCHAR(1000)
DECLARE @COLNAME VARCHAR(8) --column name
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id =object_id('tempdb.dbo.#P45_1'))
DROP TABLE tempdb.#P45_1
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id =object_id('tempdb.dbo.#P45_2'))
DROP TABLE tempdb.#P45_2
CREATE TABLE #P45_1 (
C_PRJ VARCHAR(12) NOT NULL, --プロジェクトコード
C_BUN1 VARCHAR(2) NOT NULL, --大分類
C_BUN2 VARCHAR(2) NOT NULL, --中分類
C_BUN3 VARCHAR(2) NOT NULL, --小分類
D_DATA1 MONEY NULL, --予算1
D_DATA2 MONEY NULL,
D_DATA3 MONEY NULL,
D_DATA4 MONEY NULL,
D_DATA5 MONEY NULL,
D_DATA6 MONEY NULL,
D_DATA7 MONEY NULL,
D_DATA8 MONEY NULL,
D_DATA9 MONEY NULL,
D_DATA10 MONEY NULL,
C_KAISOU VARCHAR(2) NOT NULL --費用階層
)
CREATE TABLE #P45_2 (
C_PRJ VARCHAR(12) NULL, --プロジェクトコード
D_CREATE DATETIME NULL, --作成日
N_YOSAN VARCHAR(50) NULL, --予算名称
M_TSUBOTAN MONEY NULL, --坪単価
N_TOU INT NULL, --戸数
M_TOCHIDAIKIN MONEY NULL, --土地代金
M_TATEGENKA MONEY NULL, --建物原価(税込)
M_OPTIONGENKA MONEY NULL, --オプション原価(税込)
M_TOCHIURI MONEY NULL, --土地販売価格
M_TATEURI MONEY NULL, --建物販売価格（税込み）
M_TATEURI_SUB MONEY NULL, --建物販売価格（税抜き）
M_OPTION MONEY NULL, --オプション価格（税込）
STATUS VARCHAR(6) NULL, --確認済の状態
TOTISUM MONEY NULL, --土地諸費用（税込）
TATEMONOSUM MONEY NULL, --建物諸費用（税込）
MANAGESUM MONEY NULL, --販売管理費（税込）
FLAG CHAR NULL,
intCNT INT NULL
)
DECLARE @D_CREATE DATETIME
DECLARE @N_YOSAN VARCHAR(50)
DECLARE @M_TSUBOTAN MONEY
DECLARE @N_TOU INT
DECLARE @M_TOCHIDAIKIN MONEY
DECLARE @M_TATEGENKA MONEY
DECLARE @M_OPTIONGENKA MONEY
DECLARE @M_TOCHIURI MONEY
DECLARE @M_TATEURI MONEY
DECLARE @M_TATEURI_SUB MONEY
DECLARE @M_OPTION MONEY
DECLARE @STATUS VARCHAR(6)
DECLARE @intCNT INT
SELECT @intCNT=COUNT(CNT)
FROM P014予算
WHERE C_PRJ = @C_PRJ
INSERT INTO #P45_1
SELECT @C_PRJ,CD工種大分類.Code AS C_BUN1
,CD工種中分類.Code AS C_BUN2
,CD工種.CODE AS C_BUN3
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,C_KAISOU
FROM CD工種
LEFT JOIN CD工種中分類 ON CD工種.C_BUN2 = CD工種中分類.Code
LEFT JOIN CD工種大分類 ON CD工種中分類.C_BUN1 = CD工種大分類.Code
SELECT @INTINDEX=1
BEGIN TRAN
WHILE @INTINDEX<=10
BEGIN
SELECT @COLNAME='D_DATA'+CAST(@INTINDEX AS VARCHAR(2))
SET @SQL='UPDATE #P45_1 SET ' +
@COLNAME+'='+'ISNULL(P015支払内訳.M_TANKA,0) *ISNULL(P015支払内訳.N_NUM,0)'+
' FROM #P45_1
LEFT JOIN P015支払内訳 ON
#P45_1.C_PRJ=P015支払内訳.C_PRJ
AND #P45_1.C_BUN1=P015支払内訳.C_BUN1
AND #P45_1.C_BUN2=P015支払内訳.C_BUN2
AND #P45_1.C_BUN3=P015支払内訳.C_BUN3 '
+'WHERE #P45_1.C_PRJ='+@C_PRJ+' AND P015支払内訳.CNT='' ' + CAST(@INTINDEX AS VARCHAR(2)) + ''''
EXEC(@SQL)
SELECT @INTINDEX=@INTINDEX+1
END
COMMIT
SELECT @INTINDEX=1
BEGIN TRAN
WHILE @INTINDEX<=10
BEGIN
SELECT @D_CREATE=D_CREATE, --作成日
@N_YOSAN=N_YOSAN, --予算名称
@N_TOU=N_TOU, --戸数
@M_TOCHIDAIKIN=M_TOCHIDAIKIN, --土地代金
@M_TSUBOTAN=M_TSUBOTAN, --坪単価
@M_TATEGENKA=M_TATEGENKA, --建物原価(税込)
@M_OPTIONGENKA=M_OPTIONGENKA, --オプション原価(税込)
@M_TOCHIURI=M_TOCHIURI, --土地販売価格
@M_TATEURI=M_TATEURI, --建物販売価格（税込み）
@M_TATEURI_SUB=M_TATEURI-M_TATEURI_ZEI, --建物販売価格（税抜き）
@M_OPTION= M_OPTION, --オプション価格（税込）
@STATUS = --確認済の状態
CASE
WHEN D_KAKUNIN IS NOT NULL THEN '確認済'
WHEN D_KAKUNIN IS NULL THEN ''
END
FROM P014予算
WHERE C_PRJ = @C_PRJ
AND CNT = @INTINDEX
AND D_KAKUNIN IS NOT NULL
ORDER BY CNT
IF @STATUS=''
BEGIN
SELECT @N_TOU=COUNT(C_KUKAKU), --戸数
@M_TATEURI=SUM(M_TATEURI), --建物販売価格（税込み）
@M_TATEURI_SUB=SUM(M_TATEURI - M_TATEURI_ZEI), --建物販売価格（税抜き）
@M_TOCHIURI=SUM(M_TOCHIURI) --土地販売価格
FROM P011区画
WHERE C_PRJ = @C_PRJ
END
INSERT INTO #P45_2
VALUES(@C_PRJ,@D_CREATE,@N_YOSAN,@M_TSUBOTAN,@N_TOU,@M_TOCHIDAIKIN,@M_TATEGENKA,@M_OPTIONGENKA,@M_TOCHIURI,@M_TATEURI,@M_TATEURI_SUB,@M_OPTION,@STATUS,NULL,NULL,NULL,NULL,@intCNT)
SET @SQL='UPDATE #P45_2 SET TOTISUM=(SELECT SUM(ISNULL(D_DATA'+ CAST(@INTINDEX AS VARCHAR(2))+',0)) FROM #P45_1 WHERE C_KAISOU=''01'') WHERE FLAG IS NULL'
EXEC(@SQL)
SET @SQL='UPDATE #P45_2 SET MANAGESUM=(SELECT SUM(ISNULL(D_DATA'+ CAST(@INTINDEX AS VARCHAR(2))+',0)) FROM #P45_1 WHERE C_KAISOU<>''01'' AND C_KAISOU<>''02'' ) WHERE FLAG IS NULL'
EXEC(@SQL)
SET @SQL='UPDATE #P45_2 SET TATEMONOSUM=(SELECT SUM(ISNULL(D_DATA'+ CAST(@INTINDEX AS VARCHAR(2))+',0)) FROM #P45_1 WHERE C_KAISOU=''02'') , FLAG=''1'' WHERE FLAG IS NULL'
EXEC(@SQL)
SELECT @INTINDEX=@INTINDEX+1
END
COMMIT
IF @@ERROR<>0 --操作失敗
ROLLBACK
ELSE
BEGIN
SELECT * FROM #P45_1;
SELECT * FROM #P45_2
END
END
GO
posted @ 2006-11-30 12:30
BoyLee 阅读(395)
评论(0) 编辑 收藏 网摘 所属分类:
数据库