ACoManage/SQL
Note: This needs to be cleaned up and split into multiple pages.
USE [ACoManage] GO /****** Object: Table [dbo].[PropertyTypes] Script Date: 04/21/2008 18:53:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PropertyTypes]( [PropertyTypeID] [int] IDENTITY(1,1) NOT NULL, [PropertyTypeDescription] [varchar](50) NOT NULL, CONSTRAINT [PK_PropertyTypes] PRIMARY KEY CLUSTERED ( [PropertyTypeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Employees] Script Date: 04/21/2008 18:53:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Employees]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [PersonSurName] [varchar](50) NOT NULL, [PersonGivenName] [varchar](50) NOT NULL, [Password] [varchar](50) NOT NULL, CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[PaymentMethods] Script Date: 04/21/2008 18:53:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PaymentMethods]( [PaymentMethodID] [int] IDENTITY(1,1) NOT NULL, [PaymentMethodDescription] [varchar](50) NOT NULL, [PaymentMethodCreditCard] [bit] NOT NULL, CONSTRAINT [PK_PaymentMethods] PRIMARY KEY CLUSTERED ( [PaymentMethodID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: StoredProcedure [dbo].[spMaintenanceRequest] Script Date: 04/21/2008 18:53:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[spMaintenanceRequest] -- Add the parameters for the stored procedure here @PropertyID int, @Notes text --, --@return_value int OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO MaintenanceRequests (PropertyID, DateCreated, Notes) VALUES (@PropertyID, GetDate(), @Notes); --SET @return_value = @@identity; RETURN @@identity; END GO /****** Object: Table [dbo].[Tenants] Script Date: 04/21/2008 18:54:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Tenants]( [TenantID] [int] IDENTITY(1,1) NOT NULL, [PersonSurName] [varchar](50) NOT NULL, [PersonGivenName] [varchar](50) NOT NULL, [ContactPhoneNumber] [varchar](50) NULL, [ContactCellNumber] [varchar](50) NULL, [ContactEmail] [varchar](50) NULL, [PersonDOB] [datetime] NULL, [Comments] [text] NULL, CONSTRAINT [PK_Tenants] PRIMARY KEY CLUSTERED ( [TenantID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Buildings] Script Date: 04/21/2008 18:53:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Buildings]( [BuildingID] [int] IDENTITY(1,1) NOT NULL, [BuildingNumber] [varchar](50) NOT NULL, [BuildingComments] [text] NULL, CONSTRAINT [PK_Buildings] PRIMARY KEY CLUSTERED ( [BuildingID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[MaintenanceRequests] Script Date: 04/21/2008 18:53:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MaintenanceRequests]( [MaintenanceRequestID] [int] IDENTITY(1,1) NOT NULL, [PropertyID] [int] NOT NULL, [EmployeeID] [int] NULL, [DateCreated] [datetime] NOT NULL, [Notes] [text] NOT NULL, [Resolved] [bit] NOT NULL CONSTRAINT [DF_MaintenanceRequests_Resolved] DEFAULT ((0)), CONSTRAINT [PK_MaintenanceRequests] PRIMARY KEY CLUSTERED ( [MaintenanceRequestID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[Leases] Script Date: 04/21/2008 18:53:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Leases]( [LeaseID] [int] IDENTITY(1,1) NOT NULL, [TenantID] [int] NOT NULL, [PropertyID] [int] NOT NULL, [MonthlyFee] [numeric](18, 2) NOT NULL, [DateStart] [datetime] NOT NULL, [DateEnd] [datetime] NOT NULL, CONSTRAINT [PK_Leases] PRIMARY KEY CLUSTERED ( [LeaseID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Properties] Script Date: 04/21/2008 18:53:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Properties]( [PropertyID] [int] IDENTITY(1,1) NOT NULL, [PropertyTypeID] [int] NOT NULL, [BuildingID] [int] NOT NULL, [PropertyNumber] [varchar](50) NOT NULL, [HasWaterView] [bit] NULL, [HasVaultedCeilings] [bit] NULL, [Bedrooms] [int] NULL, CONSTRAINT [PK_Properties] PRIMARY KEY CLUSTERED ( [PropertyID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Invoices] Script Date: 04/21/2008 18:53:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Invoices]( [InvoiceID] [int] IDENTITY(1,1) NOT NULL, [LeaseID] [int] NOT NULL, [AmountDue] [numeric](18, 2) NOT NULL, [DateDue] [datetime] NOT NULL, CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED ( [InvoiceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Payments] Script Date: 04/21/2008 18:53:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Payments]( [PaymentID] [int] IDENTITY(1,1) NOT NULL, [InvoiceID] [int] NOT NULL, [PaymentMethodID] [int] NOT NULL, [PaymentAmount] [numeric](18, 2) NOT NULL, [DateTime] [datetime] NOT NULL, CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED ( [PaymentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: View [dbo].[CurrentTenants] Script Date: 04/21/2008 18:54:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[CurrentTenants] AS SELECT TOP (100) PERCENT dbo.Tenants.TenantID, dbo.Tenants.PersonSurName, dbo.Tenants.PersonGivenName, dbo.Leases.DateStart, dbo.Leases.DateEnd, dbo.Leases.LeaseID, dbo.Properties.PropertyID, dbo.Properties.PropertyNumber, CASE WHEN dbo.Leases.DateEnd < { fn NOW() } THEN 'Historical' WHEN dbo.Leases.DateStart > { fn NOW() } THEN 'Future' WHEN dbo.Leases.DateStart < { fn NOW() } THEN 'Current' END AS LeaseStatus, dbo.Leases.MonthlyFee FROM dbo.Leases INNER JOIN dbo.Tenants ON dbo.Leases.TenantID = dbo.Tenants.TenantID INNER JOIN dbo.Properties ON dbo.Leases.PropertyID = dbo.Properties.PropertyID ORDER BY dbo.Leases.DateStart DESC GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Leases" Begin Extent = Top = 26 Left = 432 Bottom = 170 Right = 580 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Tenants" Begin Extent = Top = 17 Left = 663 Bottom = 194 Right = 848 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 25 Left = 94 Bottom = 181 Right = 264 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 11 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CurrentTenants' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CurrentTenants' GO /****** Object: View [dbo].[PropertyWithTenants] Script Date: 04/21/2008 18:54:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[PropertyWithTenants] AS SELECT dbo.Buildings.BuildingNumber, dbo.Properties.PropertyID, dbo.Properties.PropertyNumber, dbo.Properties.HasWaterView, dbo.Properties.HasVaultedCeilings, dbo.Properties.Bedrooms, dbo.PropertyTypes.PropertyTypeDescription, dbo.Properties.BuildingID FROM dbo.Buildings INNER JOIN dbo.Properties ON dbo.Buildings.BuildingID = dbo.Properties.BuildingID INNER JOIN dbo.PropertyTypes ON dbo.Properties.PropertyTypeID = dbo.PropertyTypes.PropertyTypeID GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[43] 4[12] 2[24] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Buildings" Begin Extent = Top = 12 Left = 67 Bottom = 105 Right = 233 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "PropertyTypes" Begin Extent = Top = 187 Left = 690 Bottom = 265 Right = 889 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 3 Left = 405 Bottom = 158 Right = 575 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 18 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PropertyWithTenants' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'PropertyWithTenants' GO /****** Object: View [dbo].[FutureTenants] Script Date: 04/21/2008 18:54:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[FutureTenants] AS SELECT dbo.Tenants.TenantID, dbo.Tenants.PersonSurName, dbo.Tenants.PersonGivenName, dbo.Leases.DateStart, dbo.Leases.DateEnd, dbo.Leases.LeaseID, dbo.Properties.PropertyID, dbo.Properties.PropertyNumber, 'Future' AS LeaseStatus FROM dbo.Leases INNER JOIN dbo.Properties ON dbo.Leases.PropertyID = dbo.Properties.PropertyID INNER JOIN dbo.Tenants ON dbo.Leases.TenantID = dbo.Tenants.TenantID WHERE (dbo.Leases.DateEnd > { fn NOW() }) AND (dbo.Leases.DateStart > { fn NOW() }) GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Leases" Begin Extent = Top = 32 Left = 396 Bottom = 140 Right = 547 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Tenants" Begin Extent = Top = 13 Left = 749 Bottom = 121 Right = 934 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 33 Left = 108 Bottom = 141 Right = 278 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 10 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'FutureTenants' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'FutureTenants' GO /****** Object: View [dbo].[CurrentPropertyAssociation] Script Date: 04/21/2008 18:54:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[CurrentPropertyAssociation] AS SELECT dbo.Leases.LeaseID, dbo.Properties.PropertyID, dbo.Tenants.TenantID FROM dbo.Leases INNER JOIN dbo.Properties ON dbo.Leases.PropertyID = dbo.Properties.PropertyID INNER JOIN dbo.Tenants ON dbo.Leases.TenantID = dbo.Tenants.TenantID WHERE (dbo.Leases.DateStart < { fn NOW() }) AND (dbo.Leases.DateEnd > { fn NOW() }) GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Leases" Begin Extent = Top = 12 Left = 309 Bottom = 120 Right = 460 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 18 Left = 41 Bottom = 126 Right = 211 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Tenants" Begin Extent = Top = 36 Left = 685 Bottom = 144 Right = 870 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 9 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CurrentPropertyAssociation' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'CurrentPropertyAssociation' GO /****** Object: View [dbo].[ViewPropertiesWithStatus] Script Date: 04/21/2008 18:54:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewPropertiesWithStatus] AS SELECT TOP (100) PERCENT dbo.Properties.PropertyID, dbo.Buildings.BuildingNumber, dbo.Properties.PropertyNumber, MIN(dLeases.DateStart) AS DateStart, MIN(dLeases.DateEnd) AS DateEnd, dbo.Tenants.PersonSurName, dbo.Tenants.PersonGivenName, MIN(CASE WHEN DateStart > { fn NOW() } THEN 'Future' WHEN DateStart < { fn NOW() } THEN 'Current' END) AS LeaseStatus, dbo.PropertyTypes.PropertyTypeDescription, dbo.Properties.Bedrooms, dbo.Properties.HasWaterView, dbo.Properties.HasVaultedCeilings, dbo.Buildings.BuildingID FROM dbo.Tenants INNER JOIN (SELECT LeaseID, TenantID, PropertyID, MonthlyFee, DateStart, DateEnd FROM dbo.Leases WHERE (DateEnd > { fn NOW() })) AS dLeases ON dbo.Tenants.TenantID = dLeases.TenantID RIGHT OUTER JOIN dbo.Buildings INNER JOIN dbo.Properties ON dbo.Buildings.BuildingID = dbo.Properties.BuildingID INNER JOIN dbo.PropertyTypes ON dbo.Properties.PropertyTypeID = dbo.PropertyTypes.PropertyTypeID ON dLeases.PropertyID = dbo.Properties.PropertyID GROUP BY dbo.Properties.PropertyID, dbo.Buildings.BuildingNumber, dbo.Properties.PropertyNumber, dbo.Tenants.PersonSurName, dbo.Tenants.PersonGivenName, dbo.PropertyTypes.PropertyTypeDescription, dbo.Properties.Bedrooms, dbo.Properties.HasWaterView, dbo.Properties.HasVaultedCeilings, dbo.Buildings.BuildingID ORDER BY dbo.Buildings.BuildingNumber, dbo.Properties.PropertyNumber GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[35] 4[26] 2[8] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Buildings" Begin Extent = Top = 6 Left = 38 Bottom = 99 Right = 204 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 13 Left = 259 Bottom = 176 Right = 430 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "PropertyTypes" Begin Extent = Top = 134 Left = 619 Bottom = 212 Right = 818 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Tenants" Begin Extent = Top = 24 Left = 1069 Bottom = 132 Right = 1254 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "dLeases" Begin Extent = Top = 2 Left = 602 Bottom = 110 Right = 753 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 13 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewPropertiesWithStatus' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' CriteriaPane = Begin ColumnWidths = 12 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewPropertiesWithStatus' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewPropertiesWithStatus' GO /****** Object: View [dbo].[ViewMaintenanceRequests] Script Date: 04/21/2008 18:54:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewMaintenanceRequests] AS SELECT TOP (100) PERCENT dbo.MaintenanceRequests.MaintenanceRequestID, dbo.MaintenanceRequests.PropertyID, dbo.MaintenanceRequests.EmployeeID, dbo.MaintenanceRequests.DateCreated, dbo.MaintenanceRequests.Notes, dbo.Employees.PersonSurName AS EmployeeSurName, dbo.Employees.PersonGivenName AS EmployeeGivenName, dbo.Buildings.BuildingNumber, dbo.PropertyTypes.PropertyTypeDescription, dbo.Properties.PropertyNumber, dbo.Tenants.PersonSurName AS TenantSurName, dbo.Tenants.PersonGivenName AS TenantGivenName, dbo.Tenants.ContactPhoneNumber, dbo.Tenants.ContactCellNumber, dbo.Tenants.ContactEmail, dbo.MaintenanceRequests.Resolved FROM dbo.Employees RIGHT OUTER JOIN dbo.Buildings INNER JOIN dbo.Properties ON dbo.Buildings.BuildingID = dbo.Properties.BuildingID INNER JOIN dbo.PropertyTypes ON dbo.Properties.PropertyTypeID = dbo.PropertyTypes.PropertyTypeID INNER JOIN dbo.MaintenanceRequests ON dbo.Properties.PropertyID = dbo.MaintenanceRequests.PropertyID LEFT OUTER JOIN dbo.Tenants INNER JOIN (SELECT LeaseID, TenantID, PropertyID, MonthlyFee, DateStart, DateEnd FROM dbo.Leases AS Leases_1 WHERE (DateStart < { fn NOW() }) AND (DateEnd > { fn NOW() })) AS dtLeases ON dbo.Tenants.TenantID = dtLeases.TenantID ON dbo.Properties.PropertyID = dtLeases.PropertyID ON dbo.Employees.EmployeeID = dbo.MaintenanceRequests.EmployeeID ORDER BY dbo.MaintenanceRequests.MaintenanceRequestID DESC GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[42] 4[10] 2[17] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Buildings" Begin Extent = Top = 107 Left = 898 Bottom = 200 Right = 1064 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 21 Left = 563 Bottom = 129 Right = 733 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "PropertyTypes" Begin Extent = Top = 16 Left = 871 Bottom = 94 Right = 1070 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "MaintenanceRequests" Begin Extent = Top = 146 Left = 236 Bottom = 254 Right = 428 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Tenants" Begin Extent = Top = 30 Left = 37 Bottom = 138 Right = 222 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Employees" Begin Extent = Top = 160 Left = 634 Bottom = 269 Right = 801 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "dtLeases" Begin Extent = Top = 4 Left = 278 Bottom = 112 Right = 429 End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewMaintenanceRequests' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 17 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1815 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewMaintenanceRequests' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewMaintenanceRequests' GO /****** Object: View [dbo].[ViewAllInvoices] Script Date: 04/21/2008 18:54:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewAllInvoices] AS SELECT dbo.Invoices.InvoiceID, dbo.Invoices.AmountDue, dbo.Invoices.DateDue, SUM(dbo.Payments.PaymentAmount) AS AmountPaid, dbo.Tenants.PersonSurName, dbo.Tenants.PersonGivenName, dbo.Buildings.BuildingNumber, dbo.Properties.PropertyNumber, dbo.PropertyTypes.PropertyTypeDescription, COUNT(*) AS Expr1 FROM dbo.Invoices INNER JOIN dbo.Leases ON dbo.Invoices.LeaseID = dbo.Leases.LeaseID INNER JOIN dbo.Tenants ON dbo.Leases.TenantID = dbo.Tenants.TenantID INNER JOIN dbo.Properties ON dbo.Leases.PropertyID = dbo.Properties.PropertyID INNER JOIN dbo.Buildings ON dbo.Properties.BuildingID = dbo.Buildings.BuildingID INNER JOIN dbo.PropertyTypes ON dbo.Properties.PropertyTypeID = dbo.PropertyTypes.PropertyTypeID LEFT OUTER JOIN dbo.Payments ON dbo.Invoices.InvoiceID = dbo.Payments.InvoiceID GROUP BY dbo.Invoices.InvoiceID, dbo.Invoices.AmountDue, dbo.Invoices.DateDue, dbo.Tenants.PersonSurName, dbo.Tenants.PersonGivenName, dbo.Buildings.BuildingNumber, dbo.Properties.PropertyNumber, dbo.PropertyTypes.PropertyTypeDescription GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Invoices" Begin Extent = Top = 150 Left = 465 Bottom = 258 Right = 616 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Leases" Begin Extent = Top = 125 Left = 222 Bottom = 274 Right = 373 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Tenants" Begin Extent = Top = 35 Left = 12 Bottom = 143 Right = 197 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 1 Left = 399 Bottom = 109 Right = 569 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Buildings" Begin Extent = Top = 51 Left = 768 Bottom = 144 Right = 934 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "PropertyTypes" Begin Extent = Top = 0 Left = 635 Bottom = 78 Right = 834 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Payments" Begin Extent = Top = 148 Left = 637 Bottom = 256 Right = 806 End DisplayF' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewAllInvoices' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'lags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 10 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 12 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewAllInvoices' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewAllInvoices' GO /****** Object: View [dbo].[ViewTenantLeases] Script Date: 04/21/2008 18:54:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewTenantLeases] AS SELECT TOP (100) PERCENT dbo.Tenants.TenantID, dbo.Leases.LeaseID, dbo.Leases.DateStart, dbo.Leases.DateEnd, dbo.Leases.MonthlyFee, dbo.PropertyTypes.PropertyTypeDescription, dbo.Buildings.BuildingNumber, dbo.Properties.PropertyNumber, dbo.Properties.HasWaterView, dbo.Properties.HasVaultedCeilings, dbo.Properties.Bedrooms FROM dbo.Tenants INNER JOIN dbo.Leases ON dbo.Tenants.TenantID = dbo.Leases.TenantID INNER JOIN dbo.Properties ON dbo.Leases.PropertyID = dbo.Properties.PropertyID INNER JOIN dbo.Buildings ON dbo.Properties.BuildingID = dbo.Buildings.BuildingID INNER JOIN dbo.PropertyTypes ON dbo.Properties.PropertyTypeID = dbo.PropertyTypes.PropertyTypeID ORDER BY dbo.Leases.DateStart DESC GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[41] 4[20] 2[13] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "Tenants" Begin Extent = Top = 6 Left = 38 Bottom = 230 Right = 222 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Leases" Begin Extent = Top = 6 Left = 258 Bottom = 150 Right = 398 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Properties" Begin Extent = Top = 6 Left = 450 Bottom = 167 Right = 620 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Buildings" Begin Extent = Top = 94 Left = 672 Bottom = 187 Right = 838 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "PropertyTypes" Begin Extent = Top = 10 Left = 678 Bottom = 88 Right = 877 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 12 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Beg' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewTenantLeases' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'in ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewTenantLeases' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewTenantLeases' GO /****** Object: StoredProcedure [dbo].[spNewLease] Script Date: 04/21/2008 18:53:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: R. Matt Isenhower -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[spNewLease] -- Add the parameters for the stored procedure here @NewTenant bit, @PropertyID int, @MonthlyFee numeric(18,2), @DateStart datetime, @DateEnd datetime, @TenantID int = NULL, -- if the lease is for an existing tenant -- The following are used if this = new tenant @PersonSurName varchar(50) = NULL, @PersonGivenName varchar(50) = NULL, @ContactPhoneNumber varchar(50) = NULL, @ContactCellNumber varchar(50) = NULL, @ContactEmail varchar(50) = NULL, @PersonDOB datetime = NULL, @Comments text = NULL AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @NewTenant = 1 BEGIN -- This is a new tenant INSERT INTO Tenants (PersonSurName, PersonGivenName, ContactPhoneNumber, ContactCellNumber, ContactEmail, PersonDOB, Comments) VALUES (@PersonSurName, @PersonGivenName, @ContactPhoneNumber, @ContactCellNumber, @ContactEmail, @PersonDOB, @Comments); INSERT INTO Leases (TenantID, PropertyID, MonthlyFee, DateStart, DateEnd) VALUES (@@identity, @PropertyID, @MonthlyFee, @DateStart, @DateEnd); RETURN @@identity; END ELSE BEGIN -- This is an existing tenant INSERT INTO Leases (TenantID, PropertyID, MonthlyFee, DateStart, DateEnd) VALUES (@TenantID, @PropertyID, @MonthlyFee, @DateStart, @DateEnd); RETURN @@identity; -- return the new ident END END GO /****** Object: View [dbo].[ViewPayments] Script Date: 04/21/2008 18:54:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[ViewPayments] AS SELECT dbo.Payments.PaymentID, dbo.Payments.InvoiceID, dbo.Payments.DateTime, dbo.PaymentMethods.PaymentMethodDescription, dbo.Payments.PaymentAmount FROM dbo.PaymentMethods INNER JOIN dbo.Payments ON dbo.PaymentMethods.PaymentMethodID = dbo.Payments.PaymentMethodID GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "PaymentMethods" Begin Extent = Top = 76 Left = 486 Bottom = 169 Right = 697 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "Payments" Begin Extent = Top = 47 Left = 197 Bottom = 198 Right = 366 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 9 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewPayments' GO EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'ViewPayments' GO /****** Object: StoredProcedure [dbo].[spMakePayment] Script Date: 04/21/2008 18:53:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: R. Matt Isenhower -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[spMakePayment] -- Add the parameters for the stored procedure here @InvoiceID int, @PaymentMethodID int, @PaymentAmount numeric(18,2) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here --SELECT @InvoiceID, @PaymentTypeID INSERT INTO Payments (InvoiceID, PaymentMethodID, PaymentAmount, [DateTime]) VALUES (@InvoiceID, @PaymentMethodID, @PaymentAmount, GetDate()); END GO /****** Object: ForeignKey [FK_Invoices_Leases] Script Date: 04/21/2008 18:53:23 ******/ ALTER TABLE [dbo].[Invoices] WITH CHECK ADD CONSTRAINT [FK_Invoices_Leases] FOREIGN KEY([LeaseID]) REFERENCES [dbo].[Leases] ([LeaseID]) GO ALTER TABLE [dbo].[Invoices] CHECK CONSTRAINT [FK_Invoices_Leases] GO /****** Object: ForeignKey [FK_Leases_Properties] Script Date: 04/21/2008 18:53:30 ******/ ALTER TABLE [dbo].[Leases] WITH CHECK ADD CONSTRAINT [FK_Leases_Properties] FOREIGN KEY([PropertyID]) REFERENCES [dbo].[Properties] ([PropertyID]) GO ALTER TABLE [dbo].[Leases] CHECK CONSTRAINT [FK_Leases_Properties] GO /****** Object: ForeignKey [FK_Leases_Tenants] Script Date: 04/21/2008 18:53:30 ******/ ALTER TABLE [dbo].[Leases] WITH CHECK ADD CONSTRAINT [FK_Leases_Tenants] FOREIGN KEY([TenantID]) REFERENCES [dbo].[Tenants] ([TenantID]) GO ALTER TABLE [dbo].[Leases] CHECK CONSTRAINT [FK_Leases_Tenants] GO /****** Object: ForeignKey [FK_MaintenanceRequests_Employees] Script Date: 04/21/2008 18:53:37 ******/ ALTER TABLE [dbo].[MaintenanceRequests] WITH CHECK ADD CONSTRAINT [FK_MaintenanceRequests_Employees] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employees] ([EmployeeID]) GO ALTER TABLE [dbo].[MaintenanceRequests] CHECK CONSTRAINT [FK_MaintenanceRequests_Employees] GO /****** Object: ForeignKey [FK_MaintenanceRequests_Properties] Script Date: 04/21/2008 18:53:37 ******/ ALTER TABLE [dbo].[MaintenanceRequests] WITH CHECK ADD CONSTRAINT [FK_MaintenanceRequests_Properties] FOREIGN KEY([PropertyID]) REFERENCES [dbo].[Properties] ([PropertyID]) GO ALTER TABLE [dbo].[MaintenanceRequests] CHECK CONSTRAINT [FK_MaintenanceRequests_Properties] GO /****** Object: ForeignKey [FK_Payments_Invoices] Script Date: 04/21/2008 18:53:46 ******/ ALTER TABLE [dbo].[Payments] WITH CHECK ADD CONSTRAINT [FK_Payments_Invoices] FOREIGN KEY([InvoiceID]) REFERENCES [dbo].[Invoices] ([InvoiceID]) GO ALTER TABLE [dbo].[Payments] CHECK CONSTRAINT [FK_Payments_Invoices] GO /****** Object: ForeignKey [FK_Payments_PaymentMethods] Script Date: 04/21/2008 18:53:46 ******/ ALTER TABLE [dbo].[Payments] WITH CHECK ADD CONSTRAINT [FK_Payments_PaymentMethods] FOREIGN KEY([PaymentMethodID]) REFERENCES [dbo].[PaymentMethods] ([PaymentMethodID]) GO ALTER TABLE [dbo].[Payments] CHECK CONSTRAINT [FK_Payments_PaymentMethods] GO /****** Object: ForeignKey [FK_Properties_Buildings] Script Date: 04/21/2008 18:53:52 ******/ ALTER TABLE [dbo].[Properties] WITH CHECK ADD CONSTRAINT [FK_Properties_Buildings] FOREIGN KEY([BuildingID]) REFERENCES [dbo].[Buildings] ([BuildingID]) GO ALTER TABLE [dbo].[Properties] CHECK CONSTRAINT [FK_Properties_Buildings] GO /****** Object: ForeignKey [FK_Properties_PropertyTypes] Script Date: 04/21/2008 18:53:52 ******/ ALTER TABLE [dbo].[Properties] WITH CHECK ADD CONSTRAINT [FK_Properties_PropertyTypes] FOREIGN KEY([PropertyTypeID]) REFERENCES [dbo].[PropertyTypes] ([PropertyTypeID]) GO ALTER TABLE [dbo].[Properties] CHECK CONSTRAINT [FK_Properties_PropertyTypes] GO
