---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 5 ob`AXNvgAgUNV
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 5.1 ob`
---------------------------------------------------------------------

CREATE TABLE Part(
    PartId INT IDENTITY,
    Make VARCHAR(50),
    Model VARCHAR(50),
    Type VARCHAR(50)
)


INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7010CT', 'Notebook')

INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')

INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7030CT', 'Notebook')


IF EXISTS
(
    SELECT * FROM sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[prPartList]')
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
    DROP PROCEDURE [dbo].[prPartList]
GO
CREATE PROCEDURE prPartList
AS
    SELECT * FROM Part
RETURN 0
GO


INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')
SELEC * FROM Part


INSERT INTO art (Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')
SELECT * FROM Part


INSERT INTO Part(Make, Model, Type)
VALUES('Toshiba', 'Portege 7020CT', 'Notebook')
SELECT * FROM art


SELECT PartId, Make + ' ' + Model Part FROM Part
INSERT INTO Part(PartId, Make, Model, Type)
VALUES(1, 'IBM', 'Thinkpad 390D', 'Notebook')
SELECT PartId, Make + ' ' + Model Part FROM Part


CREATE TABLE TmpPart(
    PartId INT,
    Make VARCHAR(50),
    Model VARCHAR(50)
)
GO
INSERT INTO TmpPart(PartId, Make, Model)
SELECT PartId, Make, Model FROM Part
GO
DROP TABLE Part
GO
CREATE TABLE Part(
    PartId INT,
    Make VARCHAR(50),
    Model VARCHAR(50)
)
GO
INSERT INTO Part(PartId, Make, Model)
SELECT PartId, Make, Model FROM TmpPart
GO
DROP TABLE TmpPart
GO


ALTER TABLE Part ADD Cost MONEY NULL
SELECT PartId, Cost FROM Part
GO


ALTER TABLE Part ADD Cost MONEY NULL
GO
SELECT PartId, Cost FROM Part
GO


DECLARE @Name AS VARCHAR(50)
GO
SELECT @Name = 'Dejan'
GO

---------------------------------------------------------------------
-- 5.3 gUNV
---------------------------------------------------------------------

CREATE PROCEDURE ap_LeaseShedule_Clear
    @intLeaseScheduleId INT
AS
    BEGIN TRANSACTION
    -- [XXPW[ɂȂׂĂ
    -- ĩ[X0ɐݒ肷
    UPDATE dbo.Inventory
    SET Lease = 0
    WHERE LeaseScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- [Xz0ɃZbg
    UPDATE dbo.LeaseSchedule
    SET PeriodicTotalAmount = 0
    WHERE ScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    PRINT N'f[^x[X烊[X폜ł܂'
    ROLLBACK TRANSACTION
    RETURN 1


CREATE PROCEDURE ap_Order_Complete_1
    @intOrderId INT,
    @dtsCompletionDate SMALLDATETIME
AS
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
            @i INT,
            @intCountOrderItems INT,
            @intOrderItemId INT

    SELECT @intErrorCode = @@ERROR
    IF @intErrorCode = 0
        BEGIN TRANSACTION

    -- 
    IF @intErrorCode = 0
    BEGIN
        UPDATE dbo.OrderHeader
        SET CompletionDate = @dtsCompletionDate,
            OrderStatusId = 4 -- 
        WHERE OrderId = @intOrderId
        SELECT @intErrorCode = @@ERROR
    END

    -- ׂ̎W
    IF @intErrorCode = 0
    BEGIN
        CREATE TABLE #OrderItems(
            id INT IDENTITY(1, 1),
            OrderItemId INT
        )
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0
    BEGIN
        INSERT INTO #OrderItems(OrderItemId)
            SELECT ItemId
            FROM dbo.OrderItem
            WHERE OrderId = @intOrderId
        SELECT @intErrorCode = @@ERROR
    END

    -- [vɂAג̊eׂ1Ă
    IF @intErrorCode = 0
    BEGIN
        SELECT @intCountOrderItems = MAX(id),
               @i = 1
        FROM #OrderItems
        SELECT @intErrorCode = @@ERROR
    END

    WHILE @intErrorCode = 0 AND @i <= @intCountOrderItems
    BEGIN
        IF @intErrorCode = 0
        BEGIN
            SELECT @intOrderItemId = OrderItemId
            FROM #OrderItems
            WHERE id = @i
            SELECT @intErrorCode = @@ERROR
        END

        IF @intErrorCode = 0
            EXEC @intErrorCode = dbo.ap_OrderItem_Complete_1 @intOrderItemId

        IF @intErrorCode = 0
            SET @i = @i + 1
    END

    IF @intErrorCode = 0 AND @@TRANCOUNT > 0
        COMMIT TRANSACTION
    ELSE
        ROLLBACK TRANSACTION
    RETURN @intErrorCode

GO

CREATE PROCEDURE dbo.ap_OrderItem_Complete_1
    @intOrderItemId INT
AS
    SET NOCOUNT ON
    DECLARE @intErrorCode INT
    SELECT @intErrorCode = @@ERROR

    IF @intErrorCode = 0
        BEGIN TRANSACTION

    -- ׂ̊Aׂ̒Ɗ֘AtĂ
    -- ̍Ō̓tɐݒ
    IF @intErrorCode = 0
    BEGIN
        UPDATE dbo.OrderItem
            SET CompletionDate = (SELECT MAX(ChargeDate)
                                  FROM dbo.ChargeLog
                                  WHERE ItemId = @intOrderItemId)
            WHERE ItemId = @intOrderItemId
        SELECT @intErrorCode = @@ERROR
    END

    IF @intErrorCode = 0 AND @@TRANCOUNT > 0
        COMMIT TRANSACTION
    ELSE
        ROLLBACK TRANSACTION
    RETURN @intErrorCode


PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    COMMIT TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
COMMIT TRANSACTION
PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)


PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    BEGIN TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
    ROLLBACK TRANSACTION
    PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)
ROLLBACK TRANSACTION
PRINT 'Trancount = ' + CONVERT(VARCHAR(4), @@TRANCOUNT)


BEGIN TRANSACTION t1
    BEGIN TRANSACTION t2
    ROLLBACK TRANSACTION t2
ROLLBACK TRANSACTION t1


CREATE PROCEDURE dbo.ap_ScrapOrder_Save
-- 쐬
     @intOrderId INT,
     @dtsOrderDate SMALLDATETIME,
     @intRequestedById INT,
     @dtsTargetDate SMALLDATETIME,
     @insOrderTypeId SMALLINT,
     @inyOrderStatusId TINYINT
AS
    SET NOCOUNT ON
    INSERT dbo.OrderHeader(
        OrderId,
        OrderDate,
        RequestedById,
        TargetDate,
        OrderTypeId,
        OrderStatusId
    )
    VALUES(
        @intOrderId,
        @dtsOrderDate,
        @intRequestedById,
        @dtsTargetDate,
        @insOrderTypeId,
        @inyOrderStatusId
    )
GO

CREATE PROCEDURE dbo.ap_ScrapOrderItem_Save
-- ׂǉ
-- XgAhvV[WAgUNVJnob`
-- ĂяoȂ΂ȂȂ
    @intItemId INT,
    @intOrderId INT,
    @intInventoryId INT
AS
    SET NOCOUNT ON
    DECLARE @intErrorCode INT,
            @chvInventoryId VARCHAR(10)

    -- Z[u|Cgݒ
    SET @chvInventoryId = CONVERT(VARCHAR, @intInventoryId)
    SAVE TRANSACTION @chvInventoryId

    -- ׂ}
    INSERT dbo.OrderItem(ItemId, OrderId, InventoryId)
    VALUES(@intItemId, @intOrderId, @intInventoryId)
    SELECT @intErrorCode = @@ERROR

    -- G[ꍇ̓Z[u|Cg܂Ń[obN
    IF @intErrorCode <> 0
    BEGIN
        ROLLBACK TRANSACTION @chvInventoryId
        RETURN @intErrorCode
    END

    RETURN 0


DECLARE @OrderId INT
DECLARE @ErrNum INT
SET @OrderId = 117

BEGIN TRAN
EXEC @ErrNum = dbo.ap_ScrapOrder_Save
    @intOrderId = @OrderId,
    @dtsOrderDate = '2007/2/1',
    @intRequestedById = 1,
    @dtsTargetDate = '2007/2/5',
    @insOrderTypeId = 3, -- p
    @inyOrderStatusId = 1 -- ς
SELECT @ErrNum

EXEC @ErrNum = dbo.ap_ScrapOrderItem_Save
    130,
    @OrderId,
    5
SELECT @ErrNum

EXEC @ErrNum = dbo.ap_ScrapOrderItem_Save
    131,
    @OrderId,
    6
SELECT @ErrNum

EXEC @ErrNum = dbo.ap_ScrapOrderItem_Save
    132,
    @OrderId,
    8
SELECT @ErrNum

COMMIT TRAN


SET TRANSACTION ISOLATION LEVEL REPEATABLE READ


SELECT * FROM Inventory WITH (HOLDLOCK)
WHERE InventoryId = @intInventoryId


UPDATE Inventory WITH (TABLOCKX, HOLDLOCK)
SET StatusId = 4
WHERE StatusId = @intStatusId


CREATE PROCEDURE ap_LeaseShedule_Clear_distributed
-- [XXPW[ɂȂׂĂ̔ĩ[X
-- [Xzׂ0ɐݒ
-- [XXPW[̊[XЂɒʒm
    @intLeaseScheduleId INT
AS
    DECLARE @chvLeaseNumber VARCHAR(50),
            @intError INT

    -- [XɂȂǂmF
    IF GETDATE() < (SELECT EndDate
                    FROM dbo.LeaseSchedule
                    WHERE ScheduleId = @intLeaseScheduleId)
        RAISERROR(N'w肳ꂽ[XXPW[ɂȂĂ܂', 16, 1)

    IF @@ERROR <> 0
    BEGIN
        PRINT N'f[^x[X烊[X擾ł܂'
        RETURN 50000
    END

    -- [Xԍ擾
    SELECT @chvLeaseNumber = Lease.LeaseNumber
    FROM dbo.Lease Lease INNER JOIN dbo.LeaseSchedule LeaseSchedule
    ON Lease.LeaseId = LeaseSchedule.LeaseId
    WHERE LeaseSchedule.ScheduleId = @intLeaseScheduleId

    BEGIN DISTRIBUTED TRANSACTION

    -- Yĩ[Xׂ0ɐݒ
    UPDATE dbo.Inventory
    SET Lease = 0
    WHERE LeaseScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- [Xz0ɃZbg
    UPDATE LeaseSchedule
    SET PeriodicTotalAmount = 0
    WHERE ScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- [XЂ֒ʒm
    EXEC @intError = lease_srvr.LeaseSchedules.dbo.prLeaseScheduleComplete
        @chvLeaseNumber, @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    IF @intError <> 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    PRINT N'[XXPW[XVł܂'
    ROLLBACK TRANSACTION
    RETURN 50000


BEGIN TRANSACTION
UPDATE dbo.Inventory
SET Lease = 0
WHERE LeaseScheduleId = 141
GO

UPDATE dbo.LeaseSchedule
SET PeriodicTotalAmount = 0
WHERE ScheduleId = 141
COMMIT TRANSACTION
GO


CREATE PROCEDURE dbo.ap_LeaseSchedule_Clear_1
-- [XԂ̉߂ׂĂ̔i
-- [Xƃ[Xzׂ0ɐݒ
    @intLeaseScheduleId INT
AS
    -- [XɂȂǂmF
    IF GETDATE() < (SELECT EndDate
                    FROM dbo.LeaseSchedule
                    WHERE ScheduleId = @intLeaseScheduleId)
        RAISERROR(N'w肳ꂽ[XXPW[ɂȂĂ܂', 16, 1)

    -- ŃG[NƁAgUNV̊JnOłɂ炸A
    -- [obNsAVȃG[ƂȂ
    IF @@ERROR <> 0 GOTO PROBLEM

    BEGIN TRANSACTION
    -- [XԂ̉߂ׂĂ
    -- ĩ[X0ɐݒ肷
    UPDATE dbo.Inventory
    SET Lease = 0
    WHERE LeaseScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    -- [Xz0ɐݒ肷
    UPDATE dbo.LeaseSchedule
    SET PeriodicTotalAmount = 0
    WHERE ScheduleId = @intLeaseScheduleId
    IF @@ERROR <> 0 GOTO PROBLEM

    COMMIT TRANSACTION
    RETURN 0

    PROBLEM:
    PRINT N'f[^x[X烊[X폜ł܂'
    ROLLBACK TRANSACTION
    RETURN 1

