------------------------------------------------------------------
DECLARE @RadiaLastRun  int       SET @RadiaLastRun    = 61
DECLARE @RadiaFirstRun int       SET @RadiaFirstRun   = 9999            /*  Last 11-MAY-2009 */
DECLARE @ChassisType varchar(40) SET @ChassisType     = '%%'            /*  LAPTOP, DESKTOP, TOP */
DECLARE @MachineType varchar(80) SET @MachineType     = '%BACKSTAGE%' 

/* Options for @MachineType include: BACKSTAGE_WORKSTATION, BACKSTAGE_TASK_MACHINE, BACKSTAGE_DEPOT_SERVER, BACKSTAGE_BIS_SERVER */
/* RETAIL_NET_PC, RETAIL_POS_PC, RETAIL_REST_PC, RETAIL_WIN_SERVER, RETAIL_ISP_SERVER, RETAIL_AIX_SERVER, RETAIL_COPIENT_SERVER  */
------------------------------------------------------------------

-- TEST if machines are adequate for Credant

use radia  select upper(rdc.device_id) Hostname, lower(rdc.person) UserID, 
    left(rdc.mtime,11) LastRadia, left(rdc.ctime,11) FirstRadia,
    rdc.computer_type Chassis, 
    CASE 
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('264761U')) THEN 'T20'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('26477EU')) THEN 'T22'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('26476RU','26476GU')) THEN 'T23'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('236661U','236663U','236686U','2366MU8')) THEN 'T30'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('2371GHH','2371H8U')) THEN 'X40'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('23736U6','23738RU','23738SU','2373AHU','23737FU','2374QU6')) THEN 'T41'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('TP-1R___')) THEN 'T41p'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('23747JU')) THEN 'T41GP'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('25253CH','25256NU')) THEN 'X41'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('23736ZU')) THEN 'T42'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('2373KYU')) THEN 'T42p'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('18714CU','2668CA7','1875DLU','TP-70___')) THEN 'T43'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('200705U','1951CE4','200757U','TP-79___')) THEN 'T60'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('200796U')) THEN 'T60p'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('17024EU')) THEN 'X60'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('64634YU')) THEN 'T61'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('64575KU')) THEN 'T61p'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('766634U')) THEN 'X61'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel in ('HP EliteBook 8530p')) THEN 'HP 8530p'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel like ('HP nr3610%')) THEN 'HP nr3610'
       WHEN rdc.computer_type = 'LAPTOP' and cpu_speed = '2000MHz' and memory in ('2007','999') THEN 'T61?'
       WHEN rdc.computer_type = 'LAPTOP' and cpu_speed = '1866MHz'  THEN 'T43?'
       WHEN rdc.computer_type = 'LAPTOP' and cpu_speed = '1833MHz'  THEN 'T60?'
       WHEN rdc.computer_type = 'LAPTOP' and cpu_speed = '1700MHz'  THEN 'T42?'
       WHEN rdc.computer_type = 'LAPTOP' and cpu_speed = '1600MHz'  THEN 'T41?'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wModel like ('%HP%')) THEN '*HP*'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wManufacturer like ('%IBM%')) THEN '*IBM*'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wManufacturer like ('%Lenovo%')) THEN '*Lenovo*'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wManufacturer like ('%Dell%')) THEN '*Dell*'
       WHEN rdc.device_id in (select wName from  radia.rWin32_ComputerSystem where wManufacturer like ('%Compaq%')) THEN '*Compaq*'
       WHEN rdc.computer_type = 'LAPTOP' and cpu_speed = '2266MHz' and memory in ('2011','2012') THEN '*HP*'
    ELSE ' '
    END as Model,
    REPLACE(rsc.machine_type, 'BACKSTAGE_', '') [Machine Type], 
    CASE WHEN
            ((CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1800
            or  rdc.device_id in (
               select wName from  radia.rWin32_ComputerSystem as rcs 
               where rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */))) 
            and 
            CAST(rdc.memory AS INT) > 995 
         THEN 'Pass ('+rdc.cpu_speed +' / '+ cast(rdc.memory as varchar(40))+'M)'
         WHEN
            ((CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1800
             or  rdc.device_id in (
               select wName from  radia.rWin32_ComputerSystem as rcs 
               where rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */))) 
            and 
            CAST(rdc.memory AS INT) < 995 
         THEN 'Upgradable ('+rdc.cpu_speed +' / '+ cast(rdc.memory as varchar(40))+'M)'
         ELSE 'Fail ('+rdc.cpu_speed +' / '+ cast(rdc.memory as varchar(40))+'M)' 
    END as Hardware,
 
    CASE 
        WHEN rsc.os_ver = 'XP' THEN 'Pass'
        WHEN  rsc.os_ver = 'W2K' 
                  and
              (rdc.device_id not in (select device_id from radia.DeviceAservice where zobjname in ('2K_R3PP_APPLICATION','2K_INFORMIX')) 
               and  rdc.device_id not in ('PLX1244590L'))
            THEN 'Upgradable'
        ELSE 'Fail (apps)' 
    END as Software,
    CASE WHEN
            ((CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1800
            or  rdc.device_id in (
               select wName from  radia.rWin32_ComputerSystem as rcs 
               where rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */))) 
            and CAST(rdc.memory AS INT) > 995 
            and rsc.os_ver = 'XP' 
         THEN 'Pass'
         WHEN
            ((CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1800
             or  rdc.device_id in (
               select wName from  radia.rWin32_ComputerSystem as rcs 
               where rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */))) 
            and rsc.os_ver = 'W2K' 
            and
              (rdc.device_id not in (select device_id from radia.DeviceAservice where zobjname in ('2K_RTPP_APPLICATION','2K_ADAGE_CLIENT','2K_CITRIX_ADAGE','2K_INFORMIX_ADAGE')) 
               and  rdc.device_id not in ('PBX_483569','PLX1244590L'))
         THEN 'Upgradeable'
         WHEN
            ((CAST(REPLACE(rdc.cpu_speed, 'MHz', '') AS INT) > 1800
             or  rdc.device_id in (
               select wName from  radia.rWin32_ComputerSystem as rcs 
               where rcs.wModel in ('2373KYU','18714CU','2668CA7','1875DLU','TP-70___','200705U','1951CE4','200757U','TP-79___','17024EU','766634U') /* T42p,T43,T43,T43,T43,T60,T60,T60,T60,X60,X61 */))) 
            and rsc.os_ver = 'XP'
        THEN 'Upgradeable'
        ELSE 'Fail' 
    END as CredantQualified,
    CASE WHEN 
          rdc.device_id in (select device_id from radia.DeviceAservice where zobjname =  'XP_CREDANT_GUARDIAN') 
    THEN 'YES'   ELSE 'NO'   
    END as CredantInstalled

from radia.safewayconfig as rsc, radia.deviceconfig as rdc
where rdc.device_id = rsc.device_id
and rdc.computer_type like @ChassisType
and rsc.machine_type  like @MachineType
and datediff(dd, rdc.mtime, getDate()) < @RadiaLastRun
and datediff(dd, rdc.ctime, getDate()) < @RadiaFirstRun
and person in (
'APATD00',
'SXARR09',
'SXEAV02'
)
order by CredantQualified, Software desc, Hardware desc