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

drop view [dbo].[vu_VoterBufferDomesticCivilianOnly]

GO



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

drop view [dbo].[vu_VoterBufferToVotersRegNoDupes]

GO



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

drop table [dbo].[CompletedJobs]

GO



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

drop table [dbo].[Conversion]

GO



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

drop table [dbo].[CountyInformation]

GO



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

drop table [dbo].[CountyProcessingRules]

GO



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

drop table [dbo].[DeletedFromVoterBuffer]

GO



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

drop table [dbo].[Disposition]

GO



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

drop table [dbo].[ElectionDefinition]

GO



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

drop table [dbo].[EnvelopeLayout]

GO



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

drop table [dbo].[Envelopes]

GO



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

drop table [dbo].[EnvelopesHistory]

GO



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

drop table [dbo].[IncomingDownloads]

GO



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

drop table [dbo].[IncomingOCR]

GO



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

drop table [dbo].[IncomingTrays]

GO



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

drop table [dbo].[Rules]

GO



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

drop table [dbo].[SortScheme]

GO



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

drop table [dbo].[Trays]

GO



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

drop table [dbo].[UploadedFiles]

GO



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

drop table [dbo].[UserChangesLog]

GO



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

drop table [dbo].[VoterBallotControl]

GO



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

drop table [dbo].[VoterBuffer]

GO



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

drop table [dbo].[Voters]

GO



CREATE TABLE [dbo].[CompletedJobs] (

	[ElectionNumber] [int] NOT NULL ,

	[OutputFileTimestamp] [datetime] NOT NULL ,

	[RoadmapFilename] [varchar] (50) NULL ,

	[ProductionFilename] [varchar] (50) NULL ,

	[DesktopFilename] [varchar] (50) NULL ,

	[CASSReportFilename] [varchar] (50) NULL ,

	[EnvelopeQuantity] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Conversion] (

	[Code] [varchar] (7) NOT NULL ,

	[RegNo] [varchar] (10) NOT NULL ,

	[ElectionNumber] [int] NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[CountyInformation] (

	[CountyID] [varchar] (50) NOT NULL ,

	[CountyName] [varchar] (50) NULL ,

	[StateName] [varchar] (25) NULL ,

	[StateAbbreviation] [varchar] (2) NULL ,

	[UsesJurisdictionCode] [tinyint] NULL ,

	[UsesCorrectedAddresses] [tinyint] NULL ,

	[ScanBarcodeFormula] [varchar] (50) NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[CountyProcessingRules] (

	[ProductionType] [varchar] (5) NOT NULL ,

	[OutgoingInCounty] [varchar] (2) NULL ,

	[OutgoingOutOfCounty] [varchar] (2) NULL ,

	[Incoming] [varchar] (3) NULL ,

	[InsertName] [varchar] (50) NULL ,

	[PartySplit] [tinyint] NULL ,

	[LanguageCodeSplit] [tinyint] NULL ,

	[UniqueFilesPerSplits] [tinyint] NULL ,

	[BallotCodeSplit] [tinyint] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[DeletedFromVoterBuffer] (

	[ImportSeq] [int] NOT NULL ,

	[ElectionNumber] [int] NOT NULL ,

	[RegNo] [varchar] (50) NULL ,

	[IssueNo] [int] NULL ,

	[VoterName] [varchar] (50) NULL ,

	[SeparationCode] [varchar] (50) NULL ,

	[RegAddress1] [varchar] (100) NULL ,

	[RegAddress2] [varchar] (100) NULL ,

	[RegAddress3] [varchar] (100) NULL ,

	[RegUnit] [varchar] (100) NULL ,

	[RegCity] [varchar] (100) NULL ,

	[RegState] [varchar] (100) NULL ,

	[RegZipcode] [varchar] (50) NULL ,

	[RegZip4] [varchar] (50) NULL ,

	[MailAddress1] [varchar] (100) NULL ,

	[MailAddress2] [varchar] (100) NULL ,

	[MailAddress3] [varchar] (100) NULL ,

	[MailUnit] [varchar] (100) NULL ,

	[MailCity] [varchar] (100) NULL ,

	[MailState] [varchar] (100) NULL ,

	[MailZipcode] [varchar] (50) NULL ,

	[MailZip4] [varchar] (50) NULL ,

	[PrecinctName] [varchar] (50) NULL ,

	[SortSeq] [varchar] (50) NULL ,

	[BallotStyle] [varchar] (50) NULL ,

	[BallotType] [varchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[JurisdictionCode] [varchar] (50) NULL ,

	[Party] [varchar] (50) NULL ,

	[LanguageCode] [varchar] (50) NULL ,

	[AddressCorrected] [tinyint] NULL ,

	[MailwareAddress1] [varchar] (100) NULL ,

	[MailwareAddress2] [varchar] (100) NULL ,

	[MailwareUnit] [varchar] (100) NULL ,

	[MailwareCity] [varchar] (100) NULL ,

	[MailwareState] [varchar] (100) NULL ,

	[MailwareZipcode] [varchar] (50) NULL ,

	[MailwareZip4] [varchar] (50) NULL ,

	[MailwareDPBC] [varchar] (50) NULL ,

	[MailwareCRRT] [varchar] (50) NULL ,

	[MailwareCountyname] [varchar] (100) NULL ,

	[MailwarePostnet] [varchar] (50) NULL ,

	[ElectionDate] [datetime] NULL ,

	[ProcessCode] [int] NULL ,

	[ProcessDescription] [varchar] (100) NULL ,

	[ConversionCode] [varchar] (50) NULL ,

	[CheckDigit] [varchar] (50) NULL ,

	[ScanBarcode] [varchar] (50) NULL ,

	[RevBarcode] [varchar] (50) NULL ,

	[JobNo] [varchar] (50) NULL ,

	[MailPrecinct] [tinyint] NULL ,

	[InCounty] [tinyint] NULL ,

	[MailwareErrorCode] [int] NULL ,

	[MailwareErrorMessage] [varchar] (75) NULL ,

	[MilitaryOrForeign] [tinyint] NULL ,

	[SourceFileName] [varchar] (50) NULL ,

	[SourceFileNumber] [int] NULL ,

	[SourceFileTimestamp] [datetime] NULL ,

	[ParserLeftovers] [varchar] (400) NULL ,

	[ABSPrecinct] [varchar] (50) NULL ,

	[VotingPrecinct] [varchar] (50) NULL ,

	[RegularPrecinct] [varchar] (50) NULL ,

	[BallotID] [varchar] (50) NULL ,

	[BallotID2] [varchar] (50) NULL ,

	[AppID] [varchar] (50) NULL ,

	[AffidavitNo] [varchar] (50) NULL ,

	[FileType] [varchar] (50) NULL ,

	[ABSStatus] [varchar] (50) NULL ,

	[MCode] [varchar] (50) NULL ,

	[District] [varchar] (50) NULL ,

	[PrecinctSplit] [varchar] (50) NULL ,

	[CountyElectionNumber] [varchar] (50) NULL ,

	[OutputFilename] [varchar] (50) NULL ,

	[OutputFileTimestamp] [datetime] NULL ,

	[IssueCode] [varchar] (50) NULL ,

	[SpecialField1] [varchar] (50) NULL ,

	[SpecialField2] [varchar] (50) NULL ,

	[SpecialField3] [char] (10) NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Disposition] (

	[KeyStrike] [nvarchar] (50) NULL ,

	[Reason] [nvarchar] (100) NULL ,

	[Category] [nvarchar] (50) NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[ElectionDefinition] (

	[ElectionNumber] [int] NOT NULL ,

	[ElectionDate] [datetime] NULL ,

	[ElectionDesc] [varchar] (50) NULL ,

	[PartyRequired] [tinyint] NULL ,

	[StandardAPermitted] [tinyint] NULL ,

	[BRMAllowed] [tinyint] NULL ,

	[StandardACutoffDate] [datetime] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[EnvelopeLayout] (

	[ID] [int] IDENTITY (1, 1) NOT NULL ,

	[DataString] [varchar] (500) NULL ,

	[FontName] [varchar] (50) NULL ,

	[FontSize] [float] NULL ,

	[X] [float] NULL ,

	[Y] [float] NULL ,

	[Width] [float] NULL ,

	[Height] [float] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Envelopes] (

	[ProductionSeq] [int] NULL ,

	[ID] [int] NULL ,

	[SplitSequence] [int] NULL ,

	[SplitJobNoQty] [int] NULL ,

	[IncomingEnvelope] [varchar] (50) NULL ,

	[OutgoingEnvelope] [varchar] (50) NULL ,

	[InsertName] [varchar] (50) NULL ,

	[OutputFilename] [varchar] (50) NULL ,

	[OutputFileTimestamp] [datetime] NULL ,

	[ElectionNumber] [int] NOT NULL ,

	[RegNo] [varchar] (50) NOT NULL ,

	[IssueNo] [int] NOT NULL ,

	[VoterName] [varchar] (50) NULL ,

	[MailAddress1] [varchar] (100) NULL ,

	[MailAddress2] [varchar] (100) NULL ,

	[MailAddress3] [varchar] (100) NULL ,

	[MailUnit] [varchar] (100) NULL ,

	[MailCity] [varchar] (100) NULL ,

	[MailState] [varchar] (100) NULL ,

	[MailZipcode] [varchar] (50) NULL ,

	[MailZip4] [varchar] (50) NULL ,

	[MailwareDPBC] [varchar] (50) NULL ,

	[MailwareCRRT] [varchar] (50) NULL ,

	[MailwarePostnet] [varchar] (50) NULL ,

	[RegAddress1] [varchar] (100) NULL ,

	[RegAddress2] [varchar] (100) NULL ,

	[RegUnit] [varchar] (100) NULL ,

	[RegCity] [varchar] (100) NULL ,

	[RegState] [varchar] (100) NULL ,

	[RegZipcode] [varchar] (50) NULL ,

	[RegZip4] [varchar] (50) NULL ,

	[PrecinctName] [varchar] (50) NULL ,

	[BallotStyle] [varchar] (50) NULL ,

	[JurisdictionCode] [varchar] (50) NULL ,

	[ScanBarcode] [varchar] (50) NULL ,

	[RevBarcode] [varchar] (50) NULL ,

	[LanguageCode] [varchar] (50) NULL ,

	[Party] [varchar] (50) NULL ,

	[SourceFileNumber] [int] NULL ,

	[SourceFileName] [varchar] (50) NULL ,

	[SourceFileTimestamp] [datetime] NULL ,

	[MilitaryOrForeign] [tinyint] NULL ,

	[MailPrecinct] [tinyint] NULL ,

	[RegularPrecinct] [varchar] (50) NULL ,

	[VotingPrecinct] [varchar] (50) NULL ,

	[AppID] [varchar] (50) NULL ,

	[AffidavitNo] [varchar] (50) NULL ,

	[ABSPrecinct] [varchar] (50) NULL ,

	[CountyElectionNumber] [varchar] (50) NULL ,

	[InCounty] [tinyint] NULL ,

	[JobNo] [varchar] (50) NULL ,

	[JobNoSeqOverall] [int] NULL ,

	[ABSStatus] [varchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[BallotType] [varchar] (50) NULL ,

	[Mcode] [varchar] (50) NULL ,

	[District] [varchar] (50) NULL ,

	[PrecinctSplit] [varchar] (50) NULL ,

	[ElectionDate] [datetime] NULL ,

	[Reprint] [tinyint] NULL ,

	[IssueCode] [varchar] (50) NULL ,

	[SpecialField1] [varchar] (50) NULL ,

	[SpecialField2] [varchar] (50) NULL ,

	[SpecialField3] [varchar] (50) NULL ,

	[VoterDateOfBirth] [datetime] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[EnvelopesHistory] (

	[ProductionSeq] [int] NULL ,

	[ID] [int] NULL ,

	[SplitSequence] [int] NULL ,

	[SplitJobNoQty] [int] NULL ,

	[IncomingEnvelope] [varchar] (50) NULL ,

	[OutgoingEnvelope] [varchar] (50) NULL ,

	[InsertName] [varchar] (50) NULL ,

	[OutputFilename] [varchar] (50) NULL ,

	[OutputFileTimestamp] [datetime] NULL ,

	[ElectionNumber] [int] NULL ,

	[RegNo] [varchar] (50) NULL ,

	[IssueNo] [int] NULL ,

	[VoterName] [varchar] (50) NULL ,

	[MailAddress1] [varchar] (100) NULL ,

	[MailAddress2] [varchar] (100) NULL ,

	[MailAddress3] [varchar] (100) NULL ,

	[MailUnit] [varchar] (100) NULL ,

	[MailCity] [varchar] (100) NULL ,

	[MailState] [varchar] (100) NULL ,

	[MailZipcode] [varchar] (50) NULL ,

	[MailZip4] [varchar] (50) NULL ,

	[MailwareDPBC] [varchar] (50) NULL ,

	[MailwareCRRT] [varchar] (50) NULL ,

	[MailwarePostnet] [varchar] (50) NULL ,

	[RegAddress1] [varchar] (100) NULL ,

	[RegAddress2] [varchar] (100) NULL ,

	[RegUnit] [varchar] (100) NULL ,

	[RegCity] [varchar] (100) NULL ,

	[RegState] [varchar] (100) NULL ,

	[RegZipcode] [varchar] (50) NULL ,

	[RegZip4] [varchar] (50) NULL ,

	[PrecinctName] [varchar] (50) NULL ,

	[BallotStyle] [varchar] (50) NULL ,

	[JurisdictionCode] [varchar] (50) NULL ,

	[ScanBarcode] [varchar] (50) NULL ,

	[RevBarcode] [varchar] (50) NULL ,

	[LanguageCode] [varchar] (50) NULL ,

	[Party] [varchar] (50) NULL ,

	[SourceFileNumber] [int] NULL ,

	[SourceFileName] [varchar] (50) NULL ,

	[SourceFileTimestamp] [datetime] NULL ,

	[MilitaryOrForeign] [tinyint] NULL ,

	[MailPrecinct] [tinyint] NULL ,

	[RegularPrecinct] [varchar] (50) NULL ,

	[VotingPrecinct] [varchar] (50) NULL ,

	[AppID] [varchar] (50) NULL ,

	[AffidavitNo] [varchar] (50) NULL ,

	[ABSPrecinct] [varchar] (50) NULL ,

	[CountyElectionNumber] [varchar] (50) NULL ,

	[InCounty] [tinyint] NULL ,

	[JobNo] [varchar] (50) NULL ,

	[JobNoSeqOverall] [int] NULL ,

	[ABSStatus] [varchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[BallotType] [varchar] (50) NULL ,

	[Mcode] [varchar] (50) NULL ,

	[District] [varchar] (50) NULL ,

	[PrecinctSplit] [varchar] (50) NULL ,

	[ElectionDate] [datetime] NULL ,

	[IssueCode] [varchar] (50) NULL ,

	[SpecialField1] [varchar] (50) NULL ,

	[SpecialField2] [varchar] (50) NULL ,

	[SpecialField3] [varchar] (50) NULL ,

	[VoterDateOfBirth] [datetime] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[IncomingDownloads] (

	[ElectionNumber] [int] NOT NULL ,

	[Filename] [varchar] (50) NOT NULL ,

	[SortDate] [datetime] NULL ,

	[FirstTray] [int] NULL ,

	[LastTray] [int] NULL ,

	[EnvelopeCount] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[IncomingOCR] (

	[ReadSeq] [int] NOT NULL ,

	[TrayNo] [int] NULL ,

	[SortCode] [varchar] (50) NULL ,

	[Code] [varchar] (50) NULL ,

	[RegNo] [varchar] (50) NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[IncomingTrays] (

	[TrayNo] [int] NOT NULL ,

	[ScanningDate] [datetime] NOT NULL ,

	[EnvelopeCount] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Rules] (

	[AppliedTable] [varchar] (50) NULL ,

	[AppliedField] [varchar] (50) NULL ,

	[ErrorCriteria] [varchar] (50) NULL ,

	[ErrorCode] [int] NULL ,

	[Explanation] [varchar] (100) NULL ,

	[InUse] [tinyint] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[SortScheme] (

	[ElectionNumber] [int] NULL ,

	[PrecinctName] [nvarchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[Party] [nvarchar] (50) NULL ,

	[JurisdictionCode] [nvarchar] (50) NULL ,

	[JobNo] [nvarchar] (10) NULL ,

	[BallotStyle] [nvarchar] (10) NULL ,

	[SortSeq] [nvarchar] (4) NULL ,

	[SortInc] [int] NOT NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Trays] (

	[TrayNo] [int] NULL ,

	[StartTime] [smalldatetime] NULL ,

	[EndTime] [smalldatetime] NULL ,

	[Quantity] [int] NULL ,

	[ErrorQuantity] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[UploadedFiles] (

	[FileNumber] [int] NOT NULL ,

	[ElectionNumber] [int] NOT NULL ,

	[FileName] [varchar] (50) NOT NULL ,

	[Timestamp] [datetime] NOT NULL ,

	[RecordQty] [int] NULL ,

	[ProcessedTimestamp] [datetime] NULL ,

	[ArchiveName] [varchar] (50) NULL ,

	[FileRecordType] [varchar] (50) NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[UserChangesLog] (

	[ElectionNumber] [int] NULL ,

	[RegNo] [varchar] (50) NULL ,

	[IssueNo] [int] NULL ,

	[updatestring] [varchar] (255) NULL ,

	[ChangeTimestamp] [datetime] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[VoterBallotControl] (

	[ElectionNumber] [int] NULL ,

	[PrecinctName] [nvarchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[Party] [nvarchar] (50) NULL ,

	[JurisdictionCode] [nvarchar] (50) NULL ,

	[JobNo] [nvarchar] (10) NULL ,

	[GemsCode] [nvarchar] (25) NULL ,

	[PostscriptFileName] [nvarchar] (50) NULL ,

	[LastJobNoSeqOverall] [int] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[VoterBuffer] (

	[ImportSeq] [int] NOT NULL ,

	[ElectionNumber] [int] NOT NULL ,

	[RegNo] [varchar] (50) NULL ,

	[IssueNo] [int] NULL ,

	[VoterName] [varchar] (50) NULL ,

	[SeparationCode] [varchar] (50) NULL ,

	[RegAddress1] [varchar] (100) NULL ,

	[RegAddress2] [varchar] (100) NULL ,

	[RegAddress3] [varchar] (100) NULL ,

	[RegUnit] [varchar] (100) NULL ,

	[RegCity] [varchar] (100) NULL ,

	[RegState] [varchar] (100) NULL ,

	[RegZipcode] [varchar] (50) NULL ,

	[RegZip4] [varchar] (50) NULL ,

	[MailAddress1] [varchar] (100) NULL ,

	[MailAddress2] [varchar] (100) NULL ,

	[MailAddress3] [varchar] (100) NULL ,

	[MailUnit] [varchar] (100) NULL ,

	[MailCity] [varchar] (100) NULL ,

	[MailState] [varchar] (100) NULL ,

	[MailZipcode] [varchar] (50) NULL ,

	[MailZip4] [varchar] (50) NULL ,

	[PrecinctName] [varchar] (50) NULL ,

	[SortSeq] [varchar] (10) NULL ,

	[BallotStyle] [varchar] (50) NULL ,

	[BallotType] [varchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[JurisdictionCode] [varchar] (50) NULL ,

	[Party] [varchar] (50) NULL ,

	[LanguageCode] [varchar] (50) NULL ,

	[AddressCorrected] [tinyint] NULL ,

	[MailwareAddress1] [varchar] (100) NULL ,

	[MailwareAddress2] [varchar] (100) NULL ,

	[MailwareUnit] [varchar] (100) NULL ,

	[MailwareCity] [varchar] (100) NULL ,

	[MailwareState] [varchar] (100) NULL ,

	[MailwareZipcode] [varchar] (50) NULL ,

	[MailwareZip4] [varchar] (50) NULL ,

	[MailwareDPBC] [varchar] (50) NULL ,

	[MailwareCRRT] [varchar] (50) NULL ,

	[MailwareCountyname] [varchar] (100) NULL ,

	[MailwarePostnet] [varchar] (50) NULL ,

	[ElectionDate] [datetime] NULL ,

	[ProcessCode] [int] NULL ,

	[ProcessDescription] [varchar] (100) NULL ,

	[ConversionCode] [varchar] (50) NULL ,

	[CheckDigit] [int] NULL ,

	[ScanBarcode] [varchar] (50) NULL ,

	[RevBarcode] [varchar] (50) NULL ,

	[JobNo] [varchar] (50) NULL ,

	[MailPrecinct] [tinyint] NULL ,

	[InCounty] [tinyint] NULL ,

	[MailwareErrorCode] [int] NULL ,

	[MailwareErrorMessage] [varchar] (75) NULL ,

	[MilitaryOrForeign] [tinyint] NULL ,

	[SourceFileName] [varchar] (50) NULL ,

	[SourceFileNumber] [int] NULL ,

	[SourceFileTimestamp] [datetime] NULL ,

	[ParserLeftovers] [varchar] (400) NULL ,

	[ABSPrecinct] [varchar] (50) NULL ,

	[VotingPrecinct] [varchar] (50) NULL ,

	[RegularPrecinct] [varchar] (50) NULL ,

	[BallotID] [varchar] (50) NULL ,

	[BallotID2] [varchar] (50) NULL ,

	[AppID] [varchar] (50) NULL ,

	[AffidavitNo] [varchar] (50) NULL ,

	[FileType] [varchar] (50) NULL ,

	[ABSStatus] [varchar] (50) NULL ,

	[MCode] [varchar] (50) NULL ,

	[District] [varchar] (50) NULL ,

	[PrecinctSplit] [varchar] (50) NULL ,

	[CountyElectionNumber] [varchar] (50) NULL ,

	[OutputFilename] [varchar] (50) NULL ,

	[OutputFileTimestamp] [datetime] NULL ,

	[IssueCode] [varchar] (50) NULL ,

	[SpecialField1] [varchar] (50) NULL ,

	[SpecialField2] [varchar] (50) NULL ,

	[SpecialField3] [char] (10) NULL ,

	[VoterDateOfBirth] [datetime] NULL 

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[Voters] (

	[ImportSeq] [int] NULL ,

	[ElectionNumber] [int] NOT NULL ,

	[RegNo] [varchar] (50) NOT NULL ,

	[IssueNo] [int] NOT NULL ,

	[VoterName] [varchar] (50) NULL ,

	[SeparationCode] [varchar] (50) NULL ,

	[RegAddress1] [varchar] (100) NULL ,

	[RegAddress2] [varchar] (100) NULL ,

	[RegAddress3] [varchar] (100) NULL ,

	[RegUnit] [varchar] (100) NULL ,

	[RegCity] [varchar] (100) NULL ,

	[RegState] [varchar] (100) NULL ,

	[RegZipcode] [varchar] (50) NULL ,

	[RegZip4] [varchar] (50) NULL ,

	[MailAddress1] [varchar] (100) NULL ,

	[MailAddress2] [varchar] (100) NULL ,

	[MailAddress3] [varchar] (100) NULL ,

	[MailUnit] [varchar] (100) NULL ,

	[MailCity] [varchar] (100) NULL ,

	[MailState] [varchar] (100) NULL ,

	[MailZipcode] [varchar] (50) NULL ,

	[MailZip4] [varchar] (50) NULL ,

	[PrecinctName] [varchar] (50) NULL ,

	[SortSeq] [varchar] (10) NULL ,

	[BallotStyle] [varchar] (50) NULL ,

	[BallotType] [varchar] (50) NULL ,

	[BallotCode] [int] NULL ,

	[JurisdictionCode] [varchar] (50) NULL ,

	[Party] [varchar] (50) NULL ,

	[LanguageCode] [varchar] (50) NULL ,

	[AddressCorrected] [tinyint] NULL ,

	[MailwareAddress1] [varchar] (100) NULL ,

	[MailwareAddress2] [varchar] (100) NULL ,

	[MailwareUnit] [varchar] (100) NULL ,

	[MailwareCity] [varchar] (100) NULL ,

	[MailwareState] [varchar] (100) NULL ,

	[MailwareZipcode] [varchar] (50) NULL ,

	[MailwareZip4] [varchar] (50) NULL ,

	[MailwareDPBC] [varchar] (50) NULL ,

	[MailwareCRRT] [varchar] (50) NULL ,

	[MailwareCountyname] [varchar] (100) NULL ,

	[MailwarePostnet] [varchar] (50) NULL ,

	[ElectionDate] [datetime] NULL ,

	[ProcessCode] [int] NULL ,

	[ProcessDescription] [varchar] (100) NULL ,

	[ConversionCode] [varchar] (50) NULL ,

	[CheckDigit] [varchar] (50) NULL ,

	[ScanBarcode] [varchar] (50) NULL ,

	[RevBarcode] [varchar] (50) NULL ,

	[JobNo] [varchar] (50) NULL ,

	[MailPrecinct] [tinyint] NULL ,

	[InCounty] [tinyint] NULL ,

	[MailwareErrorCode] [int] NULL ,

	[MailwareErrorMessage] [varchar] (75) NULL ,

	[MilitaryOrForeign] [tinyint] NULL ,

	[SourceFileName] [varchar] (50) NULL ,

	[SourceFileNumber] [int] NULL ,

	[SourceFileTimestamp] [datetime] NULL ,

	[ParserLeftovers] [varchar] (400) NULL ,

	[ABSPrecinct] [varchar] (50) NULL ,

	[VotingPrecinct] [varchar] (50) NULL ,

	[RegularPrecinct] [varchar] (50) NULL ,

	[BallotID] [varchar] (50) NULL ,

	[BallotID2] [varchar] (50) NULL ,

	[AppID] [varchar] (50) NULL ,

	[AffidavitNo] [varchar] (50) NULL ,

	[FileType] [varchar] (50) NULL ,

	[ABSStatus] [varchar] (50) NULL ,

	[MCode] [varchar] (50) NULL ,

	[District] [varchar] (50) NULL ,

	[PrecinctSplit] [varchar] (50) NULL ,

	[CountyElectionNumber] [varchar] (50) NULL ,

	[OutputFilename] [varchar] (50) NULL ,

	[OutputFileTimestamp] [datetime] NULL ,

	[IssueCode] [varchar] (50) NULL ,

	[SpecialField1] [varchar] (50) NULL ,

	[SpecialField2] [varchar] (50) NULL ,

	[SpecialField3] [varchar] (50) NULL ,

	[IncomingScanDate] [datetime] NULL ,

	[IncomingTrayNo] [int] NULL ,

	[IncomingSeqNo] [int] NULL ,

	[Disposition] [varchar] (50) NULL ,

	[IncomingVerifiedDate] [datetime] NULL ,

	[IncomingVerifiedBy] [varchar] (50) NULL ,

	[VoterDateOfBirth] [datetime] NULL ,

	[IncomingICRDateOfBirth] [datetime] NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[CompletedJobs] WITH NOCHECK ADD 

	CONSTRAINT [PK_CompletedJobs] PRIMARY KEY  NONCLUSTERED 

	(

		[ElectionNumber],

		[OutputFileTimestamp]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Conversion] WITH NOCHECK ADD 

	CONSTRAINT [PK_Conversion] PRIMARY KEY  NONCLUSTERED 

	(

		[Code],

		[RegNo],

		[ElectionNumber]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[CountyInformation] WITH NOCHECK ADD 

	CONSTRAINT [PK_CountyInformation] PRIMARY KEY  NONCLUSTERED 

	(

		[CountyID]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[ElectionDefinition] WITH NOCHECK ADD 

	CONSTRAINT [PK_ElectionDefinition] PRIMARY KEY  NONCLUSTERED 

	(

		[ElectionNumber]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Envelopes] WITH NOCHECK ADD 

	CONSTRAINT [DF_Envelopes_Reprint] DEFAULT (0) FOR [Reprint],

	CONSTRAINT [PK_Envelopes] PRIMARY KEY  NONCLUSTERED 

	(

		[ElectionNumber],

		[RegNo],

		[IssueNo]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[IncomingDownloads] WITH NOCHECK ADD 

	CONSTRAINT [PK_IncomingDownloads] PRIMARY KEY  NONCLUSTERED 

	(

		[ElectionNumber],

		[Filename]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[IncomingOCR] WITH NOCHECK ADD 

	CONSTRAINT [PK_IncomingOCR] PRIMARY KEY  NONCLUSTERED 

	(

		[ReadSeq]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[IncomingTrays] WITH NOCHECK ADD 

	CONSTRAINT [PK_IncomingTrays] PRIMARY KEY  NONCLUSTERED 

	(

		[TrayNo],

		[ScanningDate]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Rules] WITH NOCHECK ADD 

	CONSTRAINT [DF_Rules_InUse] DEFAULT (1) FOR [InUse]

GO



ALTER TABLE [dbo].[UploadedFiles] WITH NOCHECK ADD 

	CONSTRAINT [PK_UploadedFiles] PRIMARY KEY  NONCLUSTERED 

	(

		[FileNumber],

		[ElectionNumber],

		[FileName],

		[Timestamp]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[VoterBuffer] WITH NOCHECK ADD 

	CONSTRAINT [DF_VoterBuffer_AddressCorrected] DEFAULT (0) FOR [AddressCorrected],

	CONSTRAINT [DF_VoterBuffer_MailPrecinct] DEFAULT (0) FOR [MailPrecinct],

	CONSTRAINT [DF_VoterBuffer_InCounty] DEFAULT (0) FOR [InCounty],

	CONSTRAINT [DF_VoterBuffer_MilitaryOrForeign] DEFAULT (0) FOR [MilitaryOrForeign],

	CONSTRAINT [PK_VoterBuffer] PRIMARY KEY  NONCLUSTERED 

	(

		[ImportSeq],

		[ElectionNumber]

	)  ON [PRIMARY] 

GO



ALTER TABLE [dbo].[Voters] WITH NOCHECK ADD 

	CONSTRAINT [PK_Voters] PRIMARY KEY  NONCLUSTERED 

	(

		[ElectionNumber],

		[RegNo],

		[IssueNo]

	)  ON [PRIMARY] 

GO



 CREATE  UNIQUE  INDEX [RegNoPerElection] ON [dbo].[Conversion]([RegNo], [ElectionNumber]) ON [PRIMARY]

GO



 CREATE  INDEX [IX_EnvelopeLayout] ON [dbo].[EnvelopeLayout]([FontName]) ON [PRIMARY]

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO







CREATE VIEW dbo.vu_VoterBufferDomesticCivilianOnly

AS

SELECT MilitaryOrForeign, RegNo, IssueNo, ElectionNumber, 

    MailAddress1, MailAddress2, MailUnit, MailCity, MailState, 

    MailZipcode, MailwareAddress1, MailwareAddress2, 

    MailwareUnit, MailwareCity, MailwareState, MailwareZipcode, 

    MailwarePostnet, MailwareCountyname, ProcessCode, 

    Len(MailwarePostnet) AS LengthOfMailwarePostnet, 

    ProcessDescription, ImportSeq, SourceFileNumber

FROM VoterBuffer

WHERE (MilitaryOrForeign = 0)







GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO







CREATE VIEW dbo.vu_VoterBufferToVotersRegNoDupes

AS

SELECT VoterBuffer.RegNo, VoterBuffer.IssueNo, 

    VoterBuffer.ProcessCode, VoterBuffer.ProcessDescription, 

    VoterBuffer.ElectionNumber, 

    VoterBuffer.SourceFileNumber

FROM VoterBuffer INNER JOIN

    Voters ON VoterBuffer.RegNo = Voters.RegNo AND 

    VoterBuffer.IssueNo = Voters.IssueNo AND 

    VoterBuffer.ElectionNumber = Voters.ElectionNumber







GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO



