---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 20 WebGŴ߂̃XgAhvV[W
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 20.1 Pȃ\[V
---------------------------------------------------------------------

SELECT Inventoryid, Make,            Model,
       EqType,      Location,        FirstName,
       LastName,    AcquisitionType, Address,
       City,        ProvinceId,      Country,
       Phone,       Fax,             Email,
       UserName,    OrgUnit
FROM dbo.Inventory Inventory
  INNER JOIN dbo.Contact Contact
  ON Inventory.OwnerId = Contact.ContactId
    INNER JOIN dbo.Location Location
    ON Inventory.LocationId = Location.LocationId
      INNER JOIN dbo.AcquisitionType AcquisitionType
      ON AcquisitionType.AcquisitionTypeId = Inventory.AcquisitionTypeId
        INNER JOIN dbo.Equipment Equipment
        ON Equipment.EqId = Inventory.EqId
          INNER JOIN dbo.EqType EqType
          ON Equipment.EqTypeId = EqType.EqTypeId
            INNER JOIN dbo.OrgUnit OrgUnit
            ON Contact.OrgUnitId = OrgUnit.OrgUnitId
WHERE Make = 'Compaq'
  AND EqType = 'Storage Array'
ORDER BY Country, ProvinceId, City, Location

---------------------------------------------------------------------
-- 20.4 Lȃ\[V
---------------------------------------------------------------------

CREATE PROC ap_InventoryByMakeModel_Quick_TempTbl
-- iw̌j𖞂R[hQԂ
-- ꎞe[ugăR[hZbg𕪊
    @Make VARCHAR(50) = '%',
    @Model VARCHAR(50) = '%',
    @FirstRec INT = 1,
    @LastRec INT = 25,
    @RowCount INT = NULL OUTPUT
/* eXg:
DECLARE @rc INT
EXEC ap_InventoryByMakeModel_Quick_TempTbl @RowCount = @rc output
SELECT @rc
EXEC ap_InventoryByMakeModel_Quick_TempTbl @FirstRec = 26,
                                           @LastRec = 50,
                                           @RowCount = @rc OUTPUT
*/
AS
SET NOCOUNT ON

CREATE TABLE #Inv(ID INT IDENTITY,
                  Inventoryid INT,
                  Make VARCHAR(50),
                  Model VARCHAR(50),
                  Location VARCHAR(50),
                  FirstName VARCHAR(30),
                  LastName VARCHAR(30),
                  AcquisitionType VARCHAR(12),
                  Address VARCHAR(50),
                  City VARCHAR(50),
                  ProvinceId CHAR(3),
                  Country VARCHAR(50),
                  EqType VARCHAR(50),
                  Phone VARCHAR(20),
                  Fax VARCHAR(20),
                  Email VARCHAR(128),
                  UserName VARCHAR(50)
)

INSERT INTO #Inv(InventoryId,     Make,      Model,
                 Location,        FirstName, LastName,
                 AcquisitionType, Address,   City,
                 ProvinceId,      Country,   EqType,
                 Phone,           Fax,       Email,
                 UserName
)
SELECT
    Inventory.Inventoryid,
    Equipment.Make,
    Equipment.Model,
    Location.Location,
    Contact.FirstName,
    Contact.LastName,
    AcquisitionType.AcquisitionType,
    Location.Address,
    Location.City,
    Location.ProvinceId,
    Location.Country,
    EqType.EqType,
    Contact.Phone,
    Contact.Fax,
    Contact.Email,
    Contact.UserName
FROM dbo.EqType EqType
RIGHT OUTER JOIN dbo.Equipment Equipment
    ON EqType.EqTypeId = Equipment.EqTypeId
RIGHT OUTER JOIN dbo.Inventory Inventory
    ON Equipment.EqId = Inventory.EqId
INNER JOIN dbo.Status Status
    ON Inventory.StatusId = Status.StatusId
LEFT OUTER JOIN dbo.AcquisitionType AcquisitionType
    ON Inventory.AcquisitionTypeID = AcquisitionType.AcquisitionTypeId
LEFT OUTER JOIN dbo.Location Location
    ON Inventory.LocationId = Location.LocationId
LEFT OUTER JOIN dbo.Contact Contact
    ON Inventory.OwnerId = Contact.ContactId
WHERE Make LIKE @Make
AND Model LIKE @Model
ORDER BY Location, LastName, FirstName

SELECT @RowCount = @@ROWCOUNT

SELECT *
FROM #Inv
WHERE ID >= @FirstRec AND ID <= @LastRec
ORDER BY ID
RETURN


DECLARE @rc INT
EXEC ap_InventoryByMakeModel_Quick_TempTbl @RowCount = @rc OUTPUT
SELECT @rc


DECLARE @rc INT
EXEC ap_InventoryByMakeModel_Quick_TempTbl @FirstRec = 26,
                                           @LastRec  = 50,
                                           @RowCount = @rc OUTPUT


CREATE PROC ap_InvSum_Generate
-- NG邽߂̔񐳋Ke[u𐶐
    @debug INT = 0
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @intTransactionCountOnEntry INT

    CREATE TABLE #Inv(
        ID INT IDENTITY(1,1),
        Inventoryid INT,
        Make VARCHAR(50),
        Model VARCHAR(50),
        Location VARCHAR(50),
        Status VARCHAR(15),
        FirstName VARCHAR(30),
        LastName VARCHAR(30),
        AcquisitionType VARCHAR(12),
        Address VARCHAR(50),
        City VARCHAR(50),
        ProvinceId CHAR(3),
        Country VARCHAR(50),
        EqType VARCHAR(50),
        Phone VARCHAR(20),
        Fax VARCHAR(20),
        Email VARCHAR(128),
        UserName VARCHAR(50),
        MakeModelSIdx INT,
        LFNameSIdx INT,
        CountrySIdx INT
    )

    -- ʃZbg擾
    INSERT INTO #Inv(
        InventoryId,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
    )
    SELECT
        Inventory.Inventoryid,
        Equipment.Make,
        Equipment.Model,
        Location.Location,
        Contact.FirstName,
        Contact.LastName,
        AcquisitionType.AcquisitionType,
        Location.Address,
        Location.City,
        Location.ProvinceId,
        Location.Country,
        EqType.EqType,
        Contact.Phone,
        Contact.Fax,
        Contact.Email,
        Contact.UserName
    FROM dbo.EqType EqType
    RIGHT OUTER JOIN dbo.Equipment Equipment
        ON EqType.EqTypeId = Equipment.EqTypeId
    RIGHT OUTER JOIN dbo.Inventory Inventory
        ON Equipment.EqId = Inventory.EqId
    LEFT OUTER JOIN dbo.AcquisitionType AcquisitionType
        ON Inventory.AcquisitionTypeID = AcquisitionType.AcquisitionTypeId
    LEFT OUTER JOIN dbo.Location Location
        ON Inventory.LocationId = Location.LocationId
    LEFT OUTER JOIN dbo.Contact Contact
        ON Inventory.OwnerId = Contact.ContactId
    ORDER BY Location, LastName, FirstName

    -- R[h̕בւs

    ---- MakeAModel -------------------
    CREATE TABLE #tmp(SID INT IDENTITY(1, 1),
                      ID INT)

    INSERT INTO #tmp(ID)
    SELECT ID
    FROM #inv
    ORDER BY Make, Model

    UPDATE #inv
    SET MakeModelSIdx = #tmp.SId
    FROM #inv INNER JOIN #tmp
        ON #inv.ID = #tmp.id

    DROP TABLE #tmp

    ----------------------------------------
    ---- CountrySIdxFCountryAProvinceACityALocation -------------------
    CREATE TABLE #tmp2(SID INT IDENTITY(1, 1),
                       ID INT)

    INSERT INTO #tmp2(ID)
    SELECT ID
    FROM #inv
    ORDER BY Country, ProvinceId, City, Location

    UPDATE #inv
    SET CountrySIdx = #tmp2.SId
    FROM #inv INNER JOIN #tmp2
    ON #inv.ID = #tmp2.id

    DROP TABLE #tmp2
    ----------------------------------------
    ---- LFNameSIdxFLNameAFName -------------------
    CREATE TABLE #tmp3(SID INT IDENTITY(1, 1),
                       ID INT)

    INSERT INTO #tmp3(ID)
    SELECT ID
    FROM #inv
    ORDER BY LastName, FirstName

    UPDATE #inv
    SET LFNameSIdx = #tmp3.SId
    FROM #inv INNER JOIN #tmp3
        ON #inv.ID = #tmp3.id

    DROP TABLE #tmp3
    ----------------------------------------

    -- gUNVgpă[U[̃ANZXubN
    SELECT @intTransactionCountOnEntry = @@TRANCOUNT
    BEGIN TRANSACTION

    -- e[uč쐬
    IF EXISTS (SELECT * FROM dbo.sysobjects
               WHERE id = OBJECT_ID(N'[InventorySum]')
               AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
       DROP TABLE dbo.[InventorySum]

    CREATE TABLE dbo.InventorySum(
        ID INT,
        InventoryId INT,
        Make VARCHAR(50),
        Model VARCHAR(50),
        Location VARCHAR(50),
        FirstName VARCHAR(30),
        LastName VARCHAR(30),
        AcquisitionType VARCHAR(12),
        Address VARCHAR(50),
        City VARCHAR(50),
        ProvinceId CHAR(3),
        Country VARCHAR(50),
        EqType VARCHAR(50),
        Phone VARCHAR(20),
        Fax VARCHAR(20),
        Email VARCHAR(128),
        UserName VARCHAR(50),
        MakeModelSIdx INT,
        LFNameSIdx INT,
        CountrySIdx INT
    )

    -- e[uɃf[^荞
    INSERT INTO dbo.InventorySum(
        ID,
        InventoryId,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName,
        MakeModelSIdx,
        LFNameSIdx,
        CountrySIdx
    )
    SELECT
        ID,
        InventoryId,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName,
        MakeModelSIdx,
        LFNameSIdx,
        CountrySIdx
    FROM #inv

    -- CfbNX쐬
    CREATE UNIQUE CLUSTERED INDEX [idx_InvSum_Id]
    ON [dbo].[InventorySum] ([ID])

    CREATE INDEX [idx_InvSum_LFName]
    ON [dbo].[InventorySum] (LastName, FirstName)

    CREATE INDEX [idx_InvSum_Location]
    ON [dbo].[InventorySum] (Location)

    CREATE INDEX [idx_InvSum_ModelMakeEqType]
    ON [dbo].[InventorySum] (Model, Make, EqType)

    -- gUNV - [U[ɃANZX
    IF @@TRANCOUNT > @intTransactionCountOnEntry
        COMMIT TRANSACTION

    RETURN


CREATE PROC ap_InventoryByMakeModel_Quick1
    @Make VARCHAR(50) = NULL,  -- 
    @Model VARCHAR(50) = NULL  -- 
AS

SELECT
    Inventoryid,
    Make,
    Model,
    Location,
    FirstName,
    LastName,
    AcquisitionType,
    Address,
    City,
    ProvinceId,
    Country,
    EqType,
    Phone,
    Fax,
    Email,
    UserName
FROM dbo.InventorySum
WHERE Make LIKE @Make
AND Model LIKE @Model


EXEC ap_InventoryByMakeModel_Quick1 'Compaq', 'D%'


CREATE PROC ap_InventoryByMakeModel_Quick2
    @Make VARCHAR(50) = NULL,  -- 
    @Model VARCHAR(50) = NULL,  -- 
    @SortOrderId SMALLINT = 0
AS

    SELECT
        Id = CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END,
        Inventoryid,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
        FROM dbo.InventorySum
        WHERE Make LIKE @Make
        AND Model LIKE @Model
        ORDER BY CASE @SortOrderId
                     WHEN 1 THEN MakeModelSIdx
                     WHEN 2 THEN CountrySIdx
                     WHEN 3 THEN LFNameSIdx
                 END
    RETURN


EXEC ap_InventoryByMakeModel_Quick2 'Compaq', 'D%', 1


CREATE PROC ap_InventoryByMakeModel_Quick3
    @Make VARCHAR(50) = NULL,   -- 
    @Model VARCHAR(50) = NULL,  -- 
    @PreviousID INT = 0,        -- Õob`̍Ō̃R[h
    @SortOrderId SMALLINT = 0
AS

    SELECT TOP 25
        Id = CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END,
        Inventoryid,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
    FROM dbo.InventorySum
    WHERE CASE @SortOrderId
              WHEN 1 THEN MakeModelSIdx
              WHEN 2 THEN CountrySIdx
              WHEN 3 THEN LFNameSIdx
          END > @PreviousID
    AND Make LIKE @Make
    AND Model LIKE @Model
    ORDER BY CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END
    RETURN


EXEC ap_InventoryByMakeModel_Quick3 'Compaq', 'D%', 444, 1


CREATE PROC ap_InventoryByMakeModel_Quick
    @Make VARCHAR(50) = NULL,   -- 
    @Model VARCHAR(50) = NULL,  -- 
    @PreviousID INT = 0,        -- Õob`̍Ō̃R[h
    @SortOrderId SMALLINT = 0,
    @SearchTypeid TINYINT = 0   -- 0Fw肵Ŏn܂
                                -- 1Fw肵ƊSɈv
                                -- 2Fw肵܂
AS

    IF @SearchTypeId = 0
    BEGIN
        SET @Make = @Make + '%'
        SET @Model = @Model + '%'
    END

    IF @SearchTypeid = 2
    BEGIN
        SET @Make  = '%' + @Make  + '%'
        SET @Model = '%' + @Model + '%'
    END

    SELECT TOP 25
        Id = CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END,
        Inventoryid,
        Make,
        Model,
        Location,
        FirstName,
        LastName,
        AcquisitionType,
        Address,
        City,
        ProvinceId,
        Country,
        EqType,
        Phone,
        Fax,
        Email,
        UserName
    FROM dbo.InventorySum
    WHERE CASE @SortOrderId
              WHEN 1 THEN MakeModelSIdx
              WHEN 2 THEN CountrySIdx
              WHEN 3 THEN LFNameSIdx
          END > @PreviousID
    AND Make LIKE @Make
    AND Model LIKE @Model
    ORDER BY CASE @SortOrderId
                 WHEN 1 THEN MakeModelSIdx
                 WHEN 2 THEN CountrySIdx
                 WHEN 3 THEN LFNameSIdx
             END
    RETURN


EXEC ap_InventoryByMakeModel_Quick 'Compaq', 'D', 50, 2, 2


CREATE PROC ap_InventoryByMakeModel_Count
    @Make VARCHAR(50) = NULL,   -- 
    @Model VARCHAR(50) = NULL,  -- 
    @SearchTypeid TINYINT = 0,  -- 0Fw肵Ŏn܂
                                -- 1Fw肵ƊSɈv
                                -- 2Fw肵܂
    @Count INT OUTPUT
AS

    IF @SearchTypeId = 0
    BEGIN
        SET @Make = @Make + '%'
        SET @Model = @Model + '%'
    END

    IF @SearchTypeid = 2
    BEGIN
        SET @Make  = '%' + @Make  + '%'
        SET @Model = '%' + @Model + '%'
    END

    SELECT @Count = COUNT(*)
    FROM dbo.InventorySum
    WHERE Make LIKE @Make
    AND Model LIKE @Model

    RETURN


DECLARE @count INT
EXEC ap_InventoryByMakeModel_Count 'Compaq', 'D', 2, @count OUTPUT
SELECT @count count


CREATE PROC ap_InventorySearchAdvFull_ListPage
-- 𖞂25̔i\

-- Iɍ\zNGƁA㗝CfbNX
-- ܂ޔ񐳋Kꂽe[ugpāAʂ25R[h
-- ob`ŕԂXgAhvV[W̗
    @Make VARCHAR(50) = NULL,
    @Model VARCHAR(50) = NULL,
    @Location VARCHAR(50) = NULL,
    @FirstName VARCHAR(30) = NULL,
    @LastName VARCHAR(30) = NULL,
    @AcquisitionType VARCHAR(20) = NULL,
    @ProvinceId CHAR(3) = NULL,
    @Country VARCHAR(50) = NULL,
    @EqType VARCHAR(30) = NULL,
    @City VARCHAR(50) = NULL,
    @UserName VARCHAR(50) = NULL,
    @email VARCHAR(50) = NULL,
    @SortOrderId SMALLINT = 0,  -- 1: Makei[J[jAModelifj
                                -- 2: CountryijAProviBjACityisjALociꏊj
                                -- 4: LNameijAFNameij
    @PreviousID INT = 0,        -- Õob`̍Ō̃R[h
    @BatchSize INT = 25,
    @debug INT = 0
AS

    SET CONCAT_NULL_YIELDS_NULL OFF
    SET NOCOUNT ON

    DECLARE @chvSelect VARCHAR(max),
            @chvFrom VARCHAR(max),
            @chvWhere VARCHAR(max),
            @chvOrderby VARCHAR(max),
            @chvSQL VARCHAR(max)

    -- R[ht
    SET @chvSelect = 'SELECT TOP ' + CONVERT(VARCHAR, @BatchSize)
                   + '    Inventoryid, Make,            Model,
                          Location,    FirstName,
                          LastName,    AcquisitionType, Address,
                          City,        ProvinceId,      Country,
                          EqType,      Phone,           Fax,
                          Email,       UserName, '
                   +      CASE @SortOrderId
                              WHEN 1 THEN ' MakeModelSIdx '
                              WHEN 2 THEN ' CountrySIdx '
                              WHEN 3 THEN ' LFNameSIdx '
                          END
                   + '    AS ID '

    SET @chvFrom = ' FROM dbo.InventorySum '

    SET @chvWhere = ' WHERE '
                  + CASE @SortOrderId
                        WHEN 1 THEN ' MakeModelSIdx '
                        WHEN 2 THEN ' CountrySIdx '
                        WHEN 3 THEN ' LFNameSIdx '
                    END + '> '
                  + CONVERT(VARCHAR, @PreviousID)

    IF @Make IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Make = ''' + @Make + ''' '
    IF @Model IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Model = ''' + @Model + ''' '
    IF @Location IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Location = ''' + @Location + ''' '
    IF @FirstName IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND FirstName = ''' + @FirstName + ''' '
    IF @LastName IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND lastName = ''' + @lastName + ''' '
    IF @AcquisitionType IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND AcquisitionType = '''
                      + @AcquisitionType + ''' '
    IF @ProvinceId  IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND ProvinceId = ''' + @ProvinceId + ''' '
    IF @Country IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND Country = ''' + @Country + ''' '
    IF @EqType IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND EqType = ''' + @EqType + ''' '
    IF @City IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND City = ''' + @City + ''' '
    IF @UserName IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND UserName = ''' + @UserName + ''' '
    IF @email IS NOT NULL
        SET @chvWhere = @chvWhere + ' AND email = ''' + @email + ''' '

    SET @chvOrderBy = ' ORDER BY '
                    + CASE @SortOrderId
                          WHEN 1 THEN ' MakeModelSIdx'
                          WHEN 2 THEN ' CountrySIdx '
                          WHEN 3 THEN ' LFNameSIdx '
                      END

    SET @chvSQL = @chvSelect + @chvFrom + @chvWhere + @chvOrderby

    IF @debug = 0
        EXEC (@chvSQL)
    ELSE
        SELECT @chvSQL


EXEC ap_InventorySearchAdvFull_ListPage
    @Make = 'Compaq',
    @Model= NULL,
    @Location = NULL,
    @FirstName = 'Michael',
    @LastName = NULL,
    @AcquisitionType = NULL,
    @ProvinceId  = NULL,
    @Country = NULL,
    @EqType = NULL,
    @City = NULL,
    @UserName = NULL,
    @email = NULL,
    @SortOrderId = 1,   -- 1: [J[ƃfŕёւ
    @PreviousID = 25,   -- Õob`̍Ō̃R[h
    @BatchSize = 25,
    @debug = 0


ALTER DATABASE Asset5
    SET ALLOW_SNAPSHOT_ISOLATION ON;


ALTER DATABASE Asset5
    SET READ_COMMITTED_SNAPSHOT ON;


SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
EXEC ap_InventoryByMakeModel_Quick 'Nec', 'V', 50, 2, 2


SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
DECLARE @EqId INT

SELECT @EqId = EqId
FROM dbo.Inventory
WHERE InventoryID = 117

BEGIN TRAN
UPDATE dbo.Eq
SET Model = 'V91'
WHERE EqId = @EqId
UPDATE dbo.Eq
SET Model = 'V90'
WHERE EqId = (SELECT EqId
              FROM Inventory
              WHERE InventoryID = 118)

COMMIT TRAN


SqlConnection conn = new (string myConnString);
...
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Snapshot);
...


SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT;
GO
EXEC ap_InventoryByMakeModel_Quick 'Nec', 'V', 50, 2, 2

