CREATE    PROCEDURE    dbo.aip_postoldefupd
(
/*    Drops and rebuilds the org location table

    2005-8-11    WMM Only drop table at install - just empty it at dbextract runtime.
            Client needs to drop if they change the stored procedure like this file does.
            This ensures grants only done at install when user has sa type permissions.
    2005-8-4    per George Curran Add grants.
    2005-7-27    George Curran, Computer Associates
            Removed entries specifying column level collation sequences and revert to
            to database default to be compatible with MDB 25e
    2005-6-2    WMM collation to work with MDB23c.
    2005-5-18    WMM add user defined fields.
    2005-4-24    WMM oldep_prop is not part of MDB, add hwuse.
    2005-3-25    WMM stop logging as these procedures are not part of SP1 (log not needed for r11).
    2005-3-24    WMM add hwsource (UAM domain).
    2005-3-8    WMM add hwsn.
    2005-1-11    WMM use hwdef_login_rel for owner instead of old hwdef_login_lscandate_rel.
    2004-12-27    WMM add instdate and lscandate fields - oldef_prop is enough for viewgen and detailreport.
    2004-12-17    WMM set org owner to their own org rather than org of their manager.
    2004-11-15    Bill Merrow, Computer Associates
            Add fssize and fsfree to oldef_prop from fsdef_prop.
    2004-11-02    Bill Merrow, Computer Associates
            Initialize oldef_prop from hwdef_prop, add ospub_prop fields needed
            for detail displays so no runtime osdep_prop access needed.
    2004-09-01    Bill Merrow, Computer Associates
            Drop table 'ai_oldef_prop' if it exists and create new instance each
            time as opposed to creating only if it does not exist and deleting
            rows.
            Change data type for 'office' from int to nvarchar
*/

--    Message severity set on error or completion
--    0 - Sucessful
--    1 - Warning
--    2 - Trapped error
--    3 - Runtime error
  


--     Message ID for debugging purposes
    @intMsgID int = 0 OUTPUT,

--    Message source for debugging purposes
    @strMsgSrc nvarchar(32) = 'aip_postoldefupd' OUTPUT


)
AS
--    Set environmewnt
    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON

--     Enable NOCOUNT option to suppress unnecessary messages
    SET NOCOUNT ON
        
--    Declare and initialize local variables
    DECLARE    @intMaxSev int
    DECLARE @strProc nvarchar(128)
    DECLARE @dteStart datetime
    DECLARE @intStartMsg int
    DECLARE @intEndMsg int
    DECLARE @strMsgNote nvarchar(255)

    SET    @intStartMsg = 50014
    SET    @intEndMsg = 50015
    
     

--    Execute procedure logging completion and elasped time
--    SET    @strProc = 'aip_postoldefupd'
--    SET    @dteStart = GETDATE()

--    EXEC    aip_procmsg    NULL, @intStartMsg, @strMsgSrc, NULL, NULL, @strMsgNote = @strProc
    
--    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ai_oldef_prop]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--    drop table [dbo].[ai_oldef_prop]
    
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ai_oldef_prop]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    CREATE TABLE [dbo].[ai_oldef_prop] (
        [hwuuid] [uniqueidentifier] NOT NULL ,
        [office] [nvarchar] (255) NOT NULL ,
        [hwname] [nvarchar] (128) NOT NULL ,
        [hwmfgr] [nvarchar] (128) NULL ,
        [hwdom] [nvarchar] (128) NULL ,
        [hwsource] [nvarchar] (128) NULL ,
        [hwproctype] [nvarchar] (128) NULL ,
        [hwmodel] [nvarchar] (128) NULL ,
        [hwbiosmfgr] [nvarchar] (128) NULL ,
        [hwtype] [nvarchar] (128) NULL ,
        [hwprocspeed] [int] NULL ,
        [hwproccount] [int] NULL ,
        [hwprocmodel] [nvarchar] (128) NULL ,
        [hwsn] [nvarchar] (128) NULL ,
        [hwmem] [int] NULL ,
        [fssize] [float] NULL ,
        [fsfree] [float] NULL ,
        [osname] [nvarchar] (128) NULL ,
        [oslanguage] [nvarchar] (128) NULL ,
        [ossp] [nvarchar] (128) NULL ,
        [osver] [nvarchar] (128) NULL ,
        [ospub] [nvarchar] (128) NULL ,
        [instdate] [datetime] NOT NULL ,
        [lscandate] [datetime] NOT NULL ,
        [userid] [nvarchar] (32) NULL ,
        [region] [nvarchar] (255) NULL ,
        [orgnm] [nvarchar] (255) NULL,
        [hwuse] [nvarchar] (16) NULL,
        [user1] [nvarchar] (128) NULL ,
        [user2] [nvarchar] (128) NULL ,
        [user3] [nvarchar] (128) NULL ,
        [user4] [nvarchar] (128) NULL
    ) ON [PRIMARY]
    
    CREATE  UNIQUE  CLUSTERED  INDEX [IX_ai_oldef_prop_hwuuid] ON [dbo].[ai_oldef_prop]([hwuuid]) ON [PRIMARY]
    
    ALTER TABLE [dbo].[ai_oldef_prop] WITH NOCHECK ADD
        CONSTRAINT [PK_ai_oldef_prop] PRIMARY KEY  NONCLUSTERED
        (
            [hwuuid]
        )  ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_orgnm] ON [dbo].[ai_oldef_prop]([orgnm]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_region] ON [dbo].[ai_oldef_prop]([region]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_office] ON [dbo].[ai_oldef_prop]([office]) ON [PRIMARY]

    CREATE  INDEX [IX_ai_oldef_prop_hwsource] ON [dbo].[ai_oldef_prop]([hwsource]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_hwdom] ON [dbo].[ai_oldef_prop]([hwdom]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_hwmodel] ON [dbo].[ai_oldef_prop]([hwmodel]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_hwuse] ON [dbo].[ai_oldef_prop]([hwuse]) ON [PRIMARY]
     
    CREATE  INDEX [IX_ai_oldef_prop_hwprocmodel] ON [dbo].[ai_oldef_prop]([hwprocmodel]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_hwproctype] ON [dbo].[ai_oldef_prop]([hwproctype]) ON [PRIMARY]
    
    CREATE  INDEX [IX_ai_oldef_prop_userid] ON [dbo].[ai_oldef_prop]([userid]) ON [PRIMARY]

    GRANT INSERT ON ai_oldef_prop TO aiadmin
    GRANT DELETE ON ai_oldef_prop TO aiadmin
    GRANT UPDATE ON ai_oldef_prop TO aiadmin
    GRANT SELECT ON ai_oldef_prop TO aiadmin
    GRANT SELECT ON ai_oldef_prop TO aipublic
END

-- hwmodel,hwbiosmfgr,hwtype,hwprocspeed,hwproccount,hwprocmodel,hwmem
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ai_undef_hwdef_rel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

    DELETE FROM ai_oldef_prop

    INSERT INTO ai_oldef_prop
    SELECT hwuuid, 0, hwname, hwmfgr, hwdom, 'no domain', hwproctype, hwmodel, hwbiosmfgr, hwtype, hwprocspeed, hwproccount, hwprocmodel, hwsn, hwmem, 0, 0, 'no osname', 'no oslanguage', 'no ossp', 'no osver', 'no ospub', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 'No User', 'No Region', 'No Org', 'No Use', 'no user1', 'no user2', 'no user3', 'no user4'
    FROM ai_hwdef_prop

    UPDATE ai_oldef_prop
    SET ai_oldef_prop.fssize = (select sum(ai_fsdef_prop.fssize) as fssize
    FROM ai_fsdef_prop
    where ai_fsdef_prop.hwuuid = ai_oldef_prop.hwuuid )

    UPDATE ai_oldef_prop
    SET ai_oldef_prop.fsfree = (select sum(ai_fsdef_prop.fsfree) as fsfree
    FROM ai_fsdef_prop
    where ai_fsdef_prop.hwuuid = ai_oldef_prop.hwuuid )

-- simplistic code for UAI initial GA version to use domainid as there is no linkage to UAM Source server name
--    UPDATE ai_oldef_prop
--    SET ai_oldef_prop.hwsource = ai_undef_hwdef_rel.domainid
--    FROM ai_undef_hwdef_rel
--    where ai_undef_hwdef_rel.hwuuid = ai_oldef_prop.hwuuid

-- r11 version to link to ud_datasource
-- get uam r4 domainid
--    UPDATE ai_oldef_prop
--    SET ai_oldef_prop.hwsource = ai_undef_hwdef_rel.domainid
--    FROM ai_undef_hwdef_rel
--    where ai_undef_hwdef_rel.hwuuid = ai_oldef_prop.hwuuid

-- get uam r11 and r4 domuuid as linkage to dsserver
    UPDATE ai_oldef_prop
    SET ai_oldef_prop.hwsource = ud_datasource_list.dsserver
    FROM ai_oldef_prop INNER JOIN ai_undef_hwdef_rel
    ON ai_oldef_prop.hwuuid = ai_undef_hwdef_rel.hwuuid
    inner join ud_datasource_list on ai_undef_hwdef_rel.domuuid= ud_datasource_list.dssourceuuid

-- get uam r11 and r4 domainid as linkage to dsserver
-- Done Mar 25 2005 for GIS where wmm hardcoded domainids for the three GIS domains into ud_datasource_list
-- May 31 2006 - Replaced with version above to fully support UAM r4 and UAM r11
--    UPDATE ai_oldef_prop
--    SET ai_oldef_prop.hwsource = ud_datasource_list.dsserver
--    FROM ai_oldef_prop INNER JOIN ai_undef_hwdef_rel
--    ON ai_oldef_prop.hwuuid = ai_undef_hwdef_rel.hwuuid
--    inner join ud_datasource_list on ai_undef_hwdef_rel.domainid= ud_datasource_list.dssourceid


--  old update using now dead lscandate_rel table which is superceded by hwdef_login_rel as all info is inclusive there.
    UPDATE ai_oldef_prop
    SET ai_oldef_prop.userid = ai_hwdef_login_lscandate_rel.loginid
    FROM ai_oldef_prop INNER JOIN ai_hwdef_login_lscandate_rel
    ON ai_hwdef_login_lscandate_rel.hwuuid = ai_oldef_prop.hwuuid

-- to use max scan date instead of last scan date do the following (Jan 24 2005 for UWA)
--    UPDATE ai_oldef_prop
--    SET ai_oldef_prop.userid = ai_hwdef_login_maxscan_rel.loginid
--    FROM ai_oldef_prop INNER JOIN ai_hwdef_login_maxscan_rel
--    ON ai_hwdef_login_maxscan_rel.hwuuid = ai_oldef_prop.hwuuid

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ai_hwdef_login_rel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    UPDATE ai_oldef_prop
    SET ai_oldef_prop.userid = ai_hwdef_login_rel.loginid
    FROM ai_oldef_prop INNER JOIN ai_hwdef_login_rel
    ON ai_hwdef_login_rel.hwuuid = ai_oldef_prop.hwuuid

    UPDATE ai_oldef_prop
    SET ai_oldef_prop.region = ai_usrdef_prop.office, ai_oldef_prop.office = ai_usrdef_prop.dept
    FROM ai_oldef_prop INNER JOIN ai_usrdef_prop
    ON ai_usrdef_prop.samid = ai_oldef_prop.userid

    UPDATE ai_oldef_prop
    SET instdate = ai_hwdef_osinstdate_kpi.osinstdate
    FROM ai_hwdef_osinstdate_kpi
    WHERE ai_hwdef_osinstdate_kpi.hwuuid = ai_oldef_prop.hwuuid

    UPDATE ai_oldef_prop
    SET lscandate = ai_hwdef_lscandate_kpi.lscandate
    FROM ai_hwdef_lscandate_kpi
    WHERE ai_hwdef_lscandate_kpi.hwuuid = ai_oldef_prop.hwuuid

    UPDATE ai_oldef_prop
    SET osname = ai_osdef_prop.osname, oslanguage = ai_osdef_prop.oslanguage,
    ossp = ai_osdef_prop.ossp, osver = ai_osdef_prop.osver, ospub = ai_osdef_prop.ospub
    FROM ai_oldef_prop INNER JOIN ai_osdef_hwdef_rel
    ON ai_oldef_prop.hwuuid = ai_osdef_hwdef_rel.hwuuid
    inner join ai_osdef_prop on ai_osdef_hwdef_rel.osuuid= ai_osdef_prop.osuuid

    UPDATE ai_oldef_prop
    SET hwuse = 'Laptop'
    WHERE hwtype LIKE 'Notebook' OR hwtype = 'Laptop' OR hwtype = 'Portable'

    UPDATE ai_oldef_prop
    SET hwuse = 'Desktop'
    WHERE osname NOT LIKE '%Server%' AND osname NOT LIKE '%server%' AND osname NOT LIKE '%AIX%' AND osname NOT LIKE '%SunOS%' AND
osname NOT LIKE '%HP-UX%' AND osname NOT LIKE '%Linux%' AND (hwtype IS NULL OR NOT (hwtype LIKE 'Notebook' OR hwtype = 'Laptop' OR hwtype = 'Portable'))

    UPDATE ai_oldef_prop
    SET hwuse = 'Server'
    WHERE (osname LIKE '%Server' OR osname LIKE '%Unix%' OR osname LIKE '%Linux%' OR osname LIKE '%Server%' OR osname LIKE '%server%' OR
      osname LIKE 'HP-UX' OR osname LIKE 'SunOS' OR osname LIKE 'AIX') AND (hwtype IS NULL OR NOT (hwtype LIKE 'Notebook' OR
      hwtype = 'Laptop' OR hwtype = 'Portable'))

END

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pmftoorg]') and OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN


-- clear any orgnm values in ai_usrdef_prop
    UPDATE ai_usrdef_prop
    SET orgnm = 'No Org'
-- set top level managers to their org
    UPDATE ai_usrdef_prop
    SET orgnm = ud_orgdef_list.olorgname
    FROM    ai_usrdef_prop INNER JOIN
    ud_orgdef_list ON ai_usrdef_prop.mgr=ud_orgdef_list.oluserlist
-- set top level managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'
-- set unset managers subordinates to the org of their manager
    UPDATE ai_usrdef_prop
    SET orgnm = l1.orgnm
    FROM    ai_usrdef_prop as l1 INNER JOIN
    ai_usrdef_prop ON ai_usrdef_prop.mgr=l1.samid
    WHERE ai_usrdef_prop.orgnm='No Org'
    AND l1.orgnm!='No Org'

-- clear any orgnm values in oldef_prop
    UPDATE ai_oldef_prop
    SET orgnm = 'No Org'
-- set orgnm into oldef_prop
    UPDATE ai_oldef_prop
    SET ai_oldef_prop.orgnm = ai_usrdef_prop.orgnm
    FROM ai_oldef_prop INNER JOIN ai_usrdef_prop
    ON ai_usrdef_prop.samid = ai_oldef_prop.userid

-- set owning managers to their own org - alternative from above is they belong to their managers org
-- Dec 17 2004 patch.
    UPDATE ai_oldef_prop
    SET ai_oldef_prop.orgnm = ud_orgdef_list.olorgname
    FROM ai_oldef_prop INNER JOIN ud_orgdef_list
    ON ud_orgdef_list.oluserlist = ai_oldef_prop.userid

-- Aug 4 2005 - replace old aip_postorgrelupd which is now removed.
    DELETE FROM ai_orgdef_rel
     
    INSERT INTO ai_orgdef_rel
    SELECT hwuuid, orgnm, region
    FROM ai_oldef_prop

END

--SET    @dteStart = GETDATE()
--SET    @strMsgNote = dbo.aif_setcompletedprocnote(@strProc, @dteStart, GETDATE())
--EXEC    aip_procmsg    NULL, @intEndMsg, @strMsgSrc, NULL, NULL, @strMsgNote = @strMsgNote


GO
GRANT EXECUTE ON  [dbo].[aip_postoldefupd] TO [aiadmin]
GO