CREATE TABLE [dbo].[Device]
(
[DeviceID] [int] NOT NULL IDENTITY(1, 1),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Device_CreateDate] DEFAULT (getdate()),
[EditedFields] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountID] [int] NULL,
[Manufacturer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Model] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SerialNr] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrinterAssetNr] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERPEquipID] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERPData] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MacAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrinterIPAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalID] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color] [bit] NULL,
[Networked] [bit] NULL,
[DeviceType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Segment] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MonthlyDutyCycle] [float] NULL,
[Vendor] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SalesRep] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PurchaseDate] [datetime] NULL,
[PurchasePrice] [float] NULL,
[PONumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InstallDate] [datetime] NULL,
[InstallMeter] [int] NULL,
[FirstAuditDate] [datetime] NULL,
[TotalServiceCopies] [int] NULL,
[TotalServiceCalls] [int] NULL,
[LastServiceDate] [datetime] NULL,
[ServiceHistory] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Contact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OnContract] [bit] NULL,
[ContractID] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractVendor] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractType] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractRate] [float] NULL,
[ContractEndDate] [datetime] NULL,
[MonthlyBaseRate] [float] NULL,
[MonthlyMaintainanceRate] [float] NULL,
[AnnualEscalation] [int] NULL,
[MonoPagesIncluded] [int] NULL,
[MonoOverage] [float] NULL,
[ColorPagesIncluded] [int] NULL,
[ColorOverage] [float] NULL,
[TotalOutputPagesIncluded] [int] NULL,
[ScannerUseIncluded] [int] NULL,
[ScannerOverage] [float] NULL,
[FaxUseIncluded] [int] NULL,
[FaxOverage] [float] NULL,
[MonoCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MonoOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColorCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColorOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScannerCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScannerOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FaxCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FaxOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserCreated] [bit] NOT NULL CONSTRAINT [DF_Device_UserCreated] DEFAULT ((0)),
[OEMPartNo] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModelID] [int] NULL,
[Owned] [bit] NULL,
[Leased] [bit] NULL,
[CppContract] [bit] NULL,
[LeaseVendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LeaseStartDate] [datetime] NULL,
[LeaseMonths] [int] NULL,
[LeaseEndDate] [datetime] NULL,
[LeaseMonthlyPayment] [float] NULL,
[LeaseRate] [float] NULL,
[LeasePayment] [float] NULL,
[LeasePoints] [float] NULL,
[LeaseDepreciation] [float] NULL,
[DepreciationMonths] [int] NULL,
[LeaseAnnualEscalation] [float] NULL,
[CovBlack] [float] NULL,
[CovCyan] [float] NULL,
[CovMagenta] [float] NULL,
[CovYellow] [float] NULL,
[DateIntroduced] [datetime] NULL,
[DateDiscontinued] [datetime] NULL,
[SpeedColor] [int] NULL,
[SpeedMono] [int] NULL,
[TimeToFirstPageColor] [float] NULL,
[TimeToFirstPageMono] [float] NULL,
[ResHoriz] [int] NULL,
[ResVert] [int] NULL,
[StreetPrice] [float] NULL,
[MSRP] [float] NULL,
[Coverage] [float] NULL,
[PrintMethod] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GraphicUrl] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OEMProdID] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LCDDisplay] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirmwareVer] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrintQuality] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrinterStatus] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PreventMaintInterval] [float] NULL,
[PagesUntilPreventMaint] [int] NULL,
[NoJobsProcessed] [int] NULL,
[InstallMeterMono] [int] NULL,
[InstallMeterColor] [int] NULL,
[LastAuditDate] [datetime] NULL,
[CalcCPPMono] [float] NULL,
[CalcCPPColor] [float] NULL,
[PrinterErrorState] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceContact] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HostName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColorCoverage] [float] NULL,
[BlackCoverageType] [smallint] NULL,
[CyanCoverageType] [smallint] NULL,
[MagentaCoverageType] [smallint] NULL,
[YellowCoverageType] [smallint] NULL,
[FirstPrinterAuditID] [int] NULL,
[LastPrinterAuditID] [int] NULL,
[OriginalManufacturer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalModel] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalSerialNr] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalMacAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalPrinterIPAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalLocalID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalHostName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalPrinterName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalValues] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LCDWarnings] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastServiceAuditID] [int] NULL,
[LastSuppliesAuditID] [int] NULL,
[RefPrinterAuditID] [int] NULL,
[RefServiceDataAuditID] [int] NULL,
[BlackCoverageDetails] [int] NULL,
[CyanCoverageDetails] [int] NULL,
[MagentaCoverageDetails] [int] NULL,
[YellowCoverageDetails] [int] NULL,
[LastServiceDetailsAuditID] [int] NULL,
[FirmwareVersion2] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirmwareVersion3] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirmwareVersion4] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsDuplex] [bit] NULL,
[PowerActive] [float] NULL,
[PowerIdle] [float] NULL,
[PowerSleep1] [float] NULL,
[PowerSleep2] [float] NULL,
[MeterGroup1] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MeterGroup2] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InternalFields] [xml] NULL,
[LastSupplyErrorStates] [int] NULL,
[InvalidAudits] [int] NOT NULL CONSTRAINT [DF_Device_InvalidAudits] DEFAULT ((0)),
[State] [tinyint] NOT NULL CONSTRAINT [DF_Device_State] DEFAULT ((0)),
[BlockedDate] [datetime] NULL,
[BlockedByContactID] [int] NULL,
[PurchaseTerm] [int] NULL,
[PurchasePriceCurrencyID] [int] NULL,
[LeasePriceCurrencyID] [int] NULL,
[AlertStatus] [tinyint] NOT NULL CONSTRAINT [DF_Device_AlertStatus] DEFAULT ((0)),
[TotalAMV] [int] NULL,
[MonoAMV] [int] NULL,
[ColorAMV] [int] NULL,
[NormalizedModel] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MeterSource] [tinyint] NOT NULL CONSTRAINT [DF_Device_MeterSource] DEFAULT ((0)),
[SupplySource] [tinyint] NOT NULL CONSTRAINT [DF_Device_SupplySource] DEFAULT ((0)),
[LastPageCountTotal] [int] NULL,
[LastPageCountColor] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [PK_Device_DeviceID] PRIMARY KEY CLUSTERED ([DeviceID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_AccountID_Account_AccountID] FOREIGN KEY ([AccountID]) REFERENCES [dbo].[Account] ([AccountID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_BlockedByContactID_Contact_ContactID] FOREIGN KEY ([BlockedByContactID]) REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_FirstPrinterAuditID_PrinterAudit_PrinterAuditID] FOREIGN KEY ([FirstPrinterAuditID]) REFERENCES [dbo].[PrinterAudit] ([PrinterAuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastPrinterAuditID_PrinterAudit_PrinterAuditID] FOREIGN KEY ([LastPrinterAuditID]) REFERENCES [dbo].[PrinterAudit] ([PrinterAuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastServiceAuditID_Audit_AuditID] FOREIGN KEY ([LastServiceAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastServiceDetailsAuditID_Audit_AuditID] FOREIGN KEY ([LastServiceDetailsAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastSuppliesAuditID_Audit_AuditID] FOREIGN KEY ([LastSuppliesAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_ModelID_VendorModel_ModelID] FOREIGN KEY ([ModelID]) REFERENCES [dbo].[VendorModel] ([ModelID]) ON DELETE SET NULL ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_RefPrinterAuditID_PrinterAudit_PrinterAuditID] FOREIGN KEY ([RefPrinterAuditID]) REFERENCES [dbo].[PrinterAudit] ([PrinterAuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_RefServiceDataAuditID_Audit_AuditID] FOREIGN KEY ([RefServiceDataAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO