ÿþUSE [cds] GO /****** Object: ForeignKey [FK_tblAlarmHistory_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarmHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] DROP CONSTRAINT [FK_tblAlarmHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblAlarms_tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarms_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] DROP CONSTRAINT [FK_tblAlarms_tblNodes] GO /****** Object: ForeignKey [FK_tblAnalogHistory_tblAnalogs] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblAnalogs]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] DROP CONSTRAINT [FK_tblAnalogHistory_tblAnalogs] GO /****** Object: ForeignKey [FK_tblAnalogHistory_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] DROP CONSTRAINT [FK_tblAnalogHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblAnalogs_tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogs_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogs]')) ALTER TABLE [dbo].[tblAnalogs] DROP CONSTRAINT [FK_tblAnalogs_tblNodes] GO /****** Object: ForeignKey [FK_tblMeterHistory_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [FK_tblMeterHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblMeterHistory_tblMeters] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblMeters]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [FK_tblMeterHistory_tblMeters] GO /****** Object: ForeignKey [FK_tblMeter_tblNode] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeter_tblNode]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeters]')) ALTER TABLE [dbo].[tblMeters] DROP CONSTRAINT [FK_tblMeter_tblNode] GO /****** Object: ForeignKey [FK_tblNodes_tblDeviceTypes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblDeviceTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [FK_tblNodes_tblDeviceTypes] GO /****** Object: ForeignKey [FK_tblNodes_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [FK_tblNodes_tblExternalSources] GO /****** Object: ForeignKey [FK_tblRouteNodes_tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] DROP CONSTRAINT [FK_tblRouteNodes_tblNodes] GO /****** Object: ForeignKey [FK_tblRouteNodes_tblRoutes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblRoutes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] DROP CONSTRAINT [FK_tblRouteNodes_tblRoutes] GO /****** Object: ForeignKey [FK_tblStatus_tblNodes] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblStatus_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblStatus]')) ALTER TABLE [dbo].[tblStatus] DROP CONSTRAINT [FK_tblStatus_tblNodes] GO /****** Object: ForeignKey [FK_tblTankHistory_tblExternalSources] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] DROP CONSTRAINT [FK_tblTankHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblTankHistory_tblTanks] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblTanks]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] DROP CONSTRAINT [FK_tblTankHistory_tblTanks] GO /****** Object: ForeignKey [FK_tblTanks_tblNodes] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTanks_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTanks]')) ALTER TABLE [dbo].[tblTanks] DROP CONSTRAINT [FK_tblTanks_tblNodes] GO /****** Object: StoredProcedure [dbo].[up_GetAnalogValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetAnalogValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetAnalogValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetAnalogValuesByName] GO /****** Object: StoredProcedure [dbo].[up_GetAnalogValuesByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogValuesByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetAnalogValuesByRoute] GO /****** Object: StoredProcedure [dbo].[up_GetTankLevelsByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankLevelsByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetTankLevelsByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetTankLevelsByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankLevelsByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetTankLevelsByName] GO /****** Object: StoredProcedure [dbo].[up_GetTankLevelsByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankLevelsByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetTankLevelsByRoute] GO /****** Object: StoredProcedure [dbo].[up_AddTankValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddTankValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddTankValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_AddTankValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddTankValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddTankValuesByName] GO /****** Object: StoredProcedure [dbo].[up_GetMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetMeterValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetMeterValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetMeterValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetMeterValuesByName] GO /****** Object: StoredProcedure [dbo].[up_GetPreviousDayCalculatedVolumeByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetPreviousDayCalculatedVolumeByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetPreviousDayCalculatedVolumeByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetPreviousDayCalculatedVolumeByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetPreviousDayCalculatedVolumeByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetPreviousDayCalculatedVolumeByName] GO /****** Object: StoredProcedure [dbo].[up_GetPreviousDayCalculatedVolumeByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetPreviousDayCalculatedVolumeByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetPreviousDayCalculatedVolumeByRoute] GO /****** Object: StoredProcedure [dbo].[up_ImportAnalogHistory] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportAnalogHistory]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportAnalogHistory] GO /****** Object: StoredProcedure [dbo].[up_ImportETCSAMeterHistoryDaily] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportETCSAMeterHistoryDaily]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportETCSAMeterHistoryDaily] GO /****** Object: StoredProcedure [dbo].[up_ImportETCSAMeterHistoryHourly] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportETCSAMeterHistoryHourly]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportETCSAMeterHistoryHourly] GO /****** Object: StoredProcedure [dbo].[up_ImportMeterHistoryDaily] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportMeterHistoryDaily]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportMeterHistoryDaily] GO /****** Object: StoredProcedure [dbo].[up_ImportMeterHistoryHourly] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportMeterHistoryHourly]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportMeterHistoryHourly] GO /****** Object: StoredProcedure [dbo].[up_ImportRoutes] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportRoutes]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportRoutes] GO /****** Object: StoredProcedure [dbo].[up_ImportTankLevels] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportTankLevels]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportTankLevels] GO /****** Object: View [dbo].[vAnalogHistory] Script Date: 08/08/2010 10:56:12 ******/ IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vAnalogHistory]')) DROP VIEW [dbo].[vAnalogHistory] GO /****** Object: View [dbo].[vFieldDirectMeterHistoryDaily] Script Date: 08/08/2010 10:56:12 ******/ IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFieldDirectMeterHistoryDaily]')) DROP VIEW [dbo].[vFieldDirectMeterHistoryDaily] GO /****** Object: View [dbo].[vFieldDirectTankLevels] Script Date: 08/08/2010 10:56:12 ******/ IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFieldDirectTankLevels]')) DROP VIEW [dbo].[vFieldDirectTankLevels] GO /****** Object: View [dbo].[vMeterHistoryHourly] Script Date: 08/08/2010 10:56:12 ******/ IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vMeterHistoryHourly]')) DROP VIEW [dbo].[vMeterHistoryHourly] GO /****** Object: View [dbo].[vTankLevelHistory] Script Date: 08/08/2010 10:56:12 ******/ IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vTankLevelHistory]')) DROP VIEW [dbo].[vTankLevelHistory] GO /****** Object: StoredProcedure [dbo].[up_GetDailyMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetDailyMeterValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetDailyMeterValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetDailyMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetDailyMeterValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetDailyMeterValuesByName] GO /****** Object: StoredProcedure [dbo].[up_GetDailyMeterValuesByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetDailyMeterValuesByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetDailyMeterValuesByRoute] GO /****** Object: StoredProcedure [dbo].[up_GetHourlyMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetHourlyMeterValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetHourlyMeterValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetHourlyMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetHourlyMeterValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetHourlyMeterValuesByName] GO /****** Object: StoredProcedure [dbo].[up_GetHourlyMeterValuesByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetHourlyMeterValuesByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetHourlyMeterValuesByRoute] GO /****** Object: StoredProcedure [dbo].[up_AddAnalogValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddAnalogValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddAnalogValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_AddAnalogValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddAnalogValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddAnalogValuesByName] GO /****** Object: StoredProcedure [dbo].[up_AddMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddMeterValuesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddMeterValuesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_AddMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddMeterValuesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddMeterValuesByName] GO /****** Object: StoredProcedure [dbo].[up_GetRouteSummaryByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetRouteSummaryByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetRouteSummaryByRoute] GO /****** Object: StoredProcedure [dbo].[up_AddMoveRouteNodeByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddMoveRouteNodeByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddMoveRouteNodeByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetMetersByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetMetersByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetMetersByRoute] GO /****** Object: Table [dbo].[tblTankHistory] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] DROP CONSTRAINT [FK_tblTankHistory_tblExternalSources] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblTanks]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] DROP CONSTRAINT [FK_tblTankHistory_tblTanks] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblTankHistory_ExternalSourceID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblTankHistory] DROP CONSTRAINT [DF_tblTankHistory_ExternalSourceID] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTankHistory]') AND type in (N'U')) DROP TABLE [dbo].[tblTankHistory] GO /****** Object: StoredProcedure [dbo].[up_ImportStatus] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportStatus]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportStatus] GO /****** Object: StoredProcedure [dbo].[up_GetRouteNodes] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetRouteNodes]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetRouteNodes] GO /****** Object: StoredProcedure [dbo].[up_GetNodeSummaryByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetNodeSummaryByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetNodeSummaryByRoute] GO /****** Object: StoredProcedure [dbo].[up_GetAlarmHistoryByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAlarmHistoryByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetAlarmHistoryByRoute] GO /****** Object: StoredProcedure [dbo].[up_GetAnalogNamesByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogNamesByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetAnalogNamesByRoute] GO /****** Object: StoredProcedure [dbo].[up_AddRouteNodeByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddRouteNodeByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddRouteNodeByExternalName] GO /****** Object: StoredProcedure [dbo].[up_AddRouteNodeByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddRouteNodeByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddRouteNodeByName] GO /****** Object: StoredProcedure [dbo].[up_GetTankNamesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankNamesByExternalName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetTankNamesByExternalName] GO /****** Object: StoredProcedure [dbo].[up_GetTankNamesByName] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankNamesByName]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetTankNamesByName] GO /****** Object: StoredProcedure [dbo].[up_GetTankNamesByRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankNamesByRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_GetTankNamesByRoute] GO /****** Object: StoredProcedure [dbo].[up_ImportAlarmHistory] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportAlarmHistory]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportAlarmHistory] GO /****** Object: StoredProcedure [dbo].[up_ImportAlarmHistory2] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportAlarmHistory2]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_ImportAlarmHistory2] GO /****** Object: Table [dbo].[tblMeterHistory] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [FK_tblMeterHistory_tblExternalSources] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblMeters]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [FK_tblMeterHistory_tblMeters] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tblMeterH__Recor__34C8D9D1]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [DF__tblMeterH__Recor__34C8D9D1] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblMeterHistory_ExternalSourceID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [DF_tblMeterHistory_ExternalSourceID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tblMeterH__Volum__35BCFE0A]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [DF__tblMeterH__Volum__35BCFE0A] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tblMeterH__Press__36B12243]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [DF__tblMeterH__Press__36B12243] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tblMeterH__Casin__37A5467C]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [DF__tblMeterH__Casin__37A5467C] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__tblMeterH__Tubin__38996AB5]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeterHistory] DROP CONSTRAINT [DF__tblMeterH__Tubin__38996AB5] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]') AND type in (N'U')) DROP TABLE [dbo].[tblMeterHistory] GO /****** Object: Table [dbo].[tblAnalogHistory] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblAnalogs]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] DROP CONSTRAINT [FK_tblAnalogHistory_tblAnalogs] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] DROP CONSTRAINT [FK_tblAnalogHistory_tblExternalSources] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblAnalogHistory_ExternalSourceID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblAnalogHistory] DROP CONSTRAINT [DF_tblAnalogHistory_ExternalSourceID] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]') AND type in (N'U')) DROP TABLE [dbo].[tblAnalogHistory] GO /****** Object: Table [dbo].[tblAnalogs] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogs_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogs]')) ALTER TABLE [dbo].[tblAnalogs] DROP CONSTRAINT [FK_tblAnalogs_tblNodes] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblAnalogs_ID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblAnalogs] DROP CONSTRAINT [DF_tblAnalogs_ID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblAnalogs_AnalogIndex]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblAnalogs] DROP CONSTRAINT [DF_tblAnalogs_AnalogIndex] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblAnalogs]') AND type in (N'U')) DROP TABLE [dbo].[tblAnalogs] GO /****** Object: Table [dbo].[tblAlarmHistory] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarmHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] DROP CONSTRAINT [FK_tblAlarmHistory_tblExternalSources] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarms_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] DROP CONSTRAINT [FK_tblAlarms_tblNodes] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblAlarmHistory_ExternalSourceID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblAlarmHistory] DROP CONSTRAINT [DF_tblAlarmHistory_ExternalSourceID] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]') AND type in (N'U')) DROP TABLE [dbo].[tblAlarmHistory] GO /****** Object: Table [dbo].[tblStatus] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblStatus_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblStatus]')) ALTER TABLE [dbo].[tblStatus] DROP CONSTRAINT [FK_tblStatus_tblNodes] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblStatus]') AND type in (N'U')) DROP TABLE [dbo].[tblStatus] GO /****** Object: Table [dbo].[tblRouteNodes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] DROP CONSTRAINT [FK_tblRouteNodes_tblNodes] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblRoutes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] DROP CONSTRAINT [FK_tblRouteNodes_tblRoutes] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]') AND type in (N'U')) DROP TABLE [dbo].[tblRouteNodes] GO /****** Object: Table [dbo].[tblMeters] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeter_tblNode]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeters]')) ALTER TABLE [dbo].[tblMeters] DROP CONSTRAINT [FK_tblMeter_tblNode] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblMeter_ID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeters] DROP CONSTRAINT [DF_tblMeter_ID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblMeter_CreationDate]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblMeters] DROP CONSTRAINT [DF_tblMeter_CreationDate] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMeters]') AND type in (N'U')) DROP TABLE [dbo].[tblMeters] GO /****** Object: Table [dbo].[tblTanks] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTanks_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTanks]')) ALTER TABLE [dbo].[tblTanks] DROP CONSTRAINT [FK_tblTanks_tblNodes] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblTanks_ID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblTanks] DROP CONSTRAINT [DF_tblTanks_ID] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTanks]') AND type in (N'U')) DROP TABLE [dbo].[tblTanks] GO /****** Object: StoredProcedure [dbo].[up_AddRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_AddRoute] GO /****** Object: StoredProcedure [dbo].[up_DeleteRoute] Script Date: 08/08/2010 10:56:07 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_DeleteRoute]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[up_DeleteRoute] GO /****** Object: Table [dbo].[tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblDeviceTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [FK_tblNodes_tblDeviceTypes] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [FK_tblNodes_tblExternalSources] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblNode_ID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [DF_tblNode_ID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblNode_ExternalSourceID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [DF_tblNode_ExternalSourceID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblNodes_Enabled]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [DF_tblNodes_Enabled] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblNodes_DeviceTypeID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [DF_tblNodes_DeviceTypeID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblNode_CreationDate]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblNodes] DROP CONSTRAINT [DF_tblNode_CreationDate] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblNodes]') AND type in (N'U')) DROP TABLE [dbo].[tblNodes] GO /****** Object: Table [dbo].[tblRoutes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblRoutes_ID]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblRoutes] DROP CONSTRAINT [DF_tblRoutes_ID] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblRoutes_IsOilHauler]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblRoutes] DROP CONSTRAINT [DF_tblRoutes_IsOilHauler] END GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_tblRoutes_IsWaterHauler]') AND type = 'D') BEGIN ALTER TABLE [dbo].[tblRoutes] DROP CONSTRAINT [DF_tblRoutes_IsWaterHauler] END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblRoutes]') AND type in (N'U')) DROP TABLE [dbo].[tblRoutes] GO /****** Object: Table [dbo].[tblSystemStrings] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSystemStrings]') AND type in (N'U')) DROP TABLE [dbo].[tblSystemStrings] GO /****** Object: Table [dbo].[tblSystemValues] Script Date: 08/08/2010 10:56:11 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSystemValues]') AND type in (N'U')) DROP TABLE [dbo].[tblSystemValues] GO /****** Object: Table [dbo].[tblDeviceTypes] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDeviceTypes]') AND type in (N'U')) DROP TABLE [dbo].[tblDeviceTypes] GO /****** Object: Table [dbo].[tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblExternalSources]') AND type in (N'U')) DROP TABLE [dbo].[tblExternalSources] GO /****** Object: Table [dbo].[tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblExternalSources]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblExternalSources]( [ID] [tinyint] NOT NULL, [Name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_tblExternalSources] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblExternalSources] UNIQUE NONCLUSTERED ( [ID] ASC, [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblDeviceTypes] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblDeviceTypes]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblDeviceTypes]( [ID] [smallint] NOT NULL, [Name] [nvarchar](125) NOT NULL, CONSTRAINT [PK_tblDeviceTypes] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblDeviceTypes] UNIQUE NONCLUSTERED ( [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblSystemValues] Script Date: 08/08/2010 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSystemValues]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblSystemValues]( [Name] [nvarchar](50) NOT NULL, [Value] [float] NOT NULL, CONSTRAINT [PK_tblSystemValues] PRIMARY KEY CLUSTERED ( [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblSystemStrings] Script Date: 08/08/2010 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSystemStrings]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblSystemStrings]( [Name] [nvarchar](50) NOT NULL, [Value] [nvarchar](512) NOT NULL, CONSTRAINT [PK_tblSystemStrings] PRIMARY KEY CLUSTERED ( [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblRoutes] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblRoutes]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblRoutes]( [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblRoutes_ID] DEFAULT (newid()), [Name] [nvarchar](50) NOT NULL, [IsOilHauler] [bit] NOT NULL CONSTRAINT [DF_tblRoutes_IsOilHauler] DEFAULT ((0)), [IsWaterHauler] [bit] NOT NULL CONSTRAINT [DF_tblRoutes_IsWaterHauler] DEFAULT ((0)), [Operator] [nvarchar](30) NULL, [Foreman] [nvarchar](30) NULL, [Op_Contact1] [nvarchar](30) NULL, [Op_Contact2] [nvarchar](30) NULL, [Frm_Contact1] [nvarchar](30) NULL, [Frm_Contact2] [nvarchar](30) NULL, [Wh_Contact1] [nvarchar](30) NULL, [Wh_Contact2] [nvarchar](30) NULL, CONSTRAINT [PK_tblRoutes] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblRoutes] UNIQUE NONCLUSTERED ( [ID] ASC, [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblNodes] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblNodes]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblNodes]( [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblNode_ID] DEFAULT (newid()), [Name] [nvarchar](125) NOT NULL, [ExternalName] [nvarchar](125) NULL, [ExternalSourceID] [tinyint] NOT NULL CONSTRAINT [DF_tblNode_ExternalSourceID] DEFAULT ((0)), [Enabled] [bit] NOT NULL CONSTRAINT [DF_tblNodes_Enabled] DEFAULT ((0)), [DeviceTypeID] [smallint] NOT NULL CONSTRAINT [DF_tblNodes_DeviceTypeID] DEFAULT ((0)), [Address] [nvarchar](125) NULL, [API] [nvarchar](20) NULL, [WellNum] [float] NULL, [PurchaserKey] [nvarchar](15) NULL, [FieldDirectKey] [int] NULL, [GasCo] [nvarchar](10) NULL, [GasHW] [bit] NULL, [County] [nvarchar](25) NULL, [State] [nvarchar](2) NULL, [CreationDate] [datetime] NOT NULL CONSTRAINT [DF_tblNode_CreationDate] DEFAULT (getdate()), [WaterHaulerGroup] [nvarchar](10) NULL, [OilHaulerGroup] [nvarchar](10) NULL, [FRMAreaGroup] [nvarchar](10) NULL, [OrigCompanyGroup] [nvarchar](10) NULL, [PMPAreaGroup] [nvarchar](4) NULL, CONSTRAINT [PK_tblNode] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblNode] UNIQUE CLUSTERED ( [Name] ASC, [ExternalName] ASC, [ExternalSourceID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: StoredProcedure [dbo].[up_DeleteRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_DeleteRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Deletes a Route -- ============================================= CREATE PROCEDURE [dbo].[up_DeleteRoute]( @name nvarchar(50) ) AS BEGIN DELETE FROM tblRoutes WHERE Name=@name END ' END GO /****** Object: StoredProcedure [dbo].[up_AddRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Adds a Route -- ============================================= CREATE PROCEDURE [dbo].[up_AddRoute]( @name nvarchar(50), @isWaterHauler bit = 0, @isOilHauler bit = 0 ) AS BEGIN IF NOT @name IS NULL AND LEN(LTRIM(RTRIM(@name))) > 0 BEGIN IF NOT EXISTS(SELECT Name FROM tblRoutes WHERE Name=@name) BEGIN INSERT INTO tblRoutes (Name,IsOilHauler,IsWaterHauler) VALUES (@name,@isOilHauler,@isWaterHauler) END END END ' END GO /****** Object: Table [dbo].[tblTanks] Script Date: 08/08/2010 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTanks]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblTanks]( [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblTanks_ID] DEFAULT (newid()), [NodeID] [uniqueidentifier] NOT NULL, [Name] [nvarchar](50) NULL, [TankIndex] [tinyint] NULL, [FieldDirectKey] [int] NULL, [Pipeline] [bit] NULL, CONSTRAINT [PK_tblTanks] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblTanks] UNIQUE NONCLUSTERED ( [NodeID] ASC, [TankIndex] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblMeters] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMeters]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblMeters]( [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblMeter_ID] DEFAULT (newid()), [NodeID] [uniqueidentifier] NOT NULL, [MeterIndex] [tinyint] NOT NULL, [MeterKey] [nvarchar](50) NULL, [FieldDirectKey] [int] NULL, [ExternalSourceID] [int] NULL, [CreationDate] [datetime] NOT NULL CONSTRAINT [DF_tblMeter_CreationDate] DEFAULT (getdate()), CONSTRAINT [PK_tblMeter] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblMeter] UNIQUE NONCLUSTERED ( [NodeID] ASC, [MeterIndex] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblRouteNodes] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblRouteNodes]( [ID] [int] IDENTITY(1,1) NOT NULL, [RouteID] [uniqueidentifier] NOT NULL, [NodeID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_tblRouteNodes] PRIMARY KEY CLUSTERED ( [RouteID] ASC, [NodeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblStatus] Script Date: 08/08/2010 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblStatus]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblStatus]( [NodeID] [uniqueidentifier] NOT NULL, [CommStatus] [nvarchar](50) NULL, [LastComm] [datetime] NULL, [RunStatus] [nvarchar](50) NULL, [TIS] [float] NULL, [IdleTime] [float] NULL, [Runtime] [float] NULL, [RuntimeYest] [float] NULL, [Cycles] [int] NULL, CONSTRAINT [PK_tblStatus] PRIMARY KEY CLUSTERED ( [NodeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblAlarmHistory] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblAlarmHistory]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [NodeID] [uniqueidentifier] NOT NULL, [Date] [datetime] NOT NULL, [ExternalSourceID] [tinyint] NULL CONSTRAINT [DF_tblAlarmHistory_ExternalSourceID] DEFAULT ((0)), [Source] [nvarchar](25) NULL, [Text] [nvarchar](125) NOT NULL, [Value] [float] NULL, CONSTRAINT [PK_tblAlarms] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblAnalogs] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblAnalogs]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblAnalogs]( [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblAnalogs_ID] DEFAULT (newid()), [NodeID] [uniqueidentifier] NOT NULL, [Name] [nvarchar](125) NOT NULL, [AnalogIndex] [tinyint] NOT NULL CONSTRAINT [DF_tblAnalogs_AnalogIndex] DEFAULT ((0)), CONSTRAINT [PK_tblAnalogs] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblAnalogs] UNIQUE NONCLUSTERED ( [NodeID] ASC, [AnalogIndex] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblAnalogHistory] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblAnalogHistory]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [AnalogID] [uniqueidentifier] NOT NULL, [ExternalSourceID] [tinyint] NOT NULL CONSTRAINT [DF_tblAnalogHistory_ExternalSourceID] DEFAULT ((0)), [Date] [datetime] NOT NULL, [Value] [float] NOT NULL, CONSTRAINT [PK_tblAnalogHistory_1] PRIMARY KEY CLUSTERED ( [AnalogID] ASC, [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[tblMeterHistory] Script Date: 08/08/2010 10:56:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblMeterHistory]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [MeterID] [uniqueidentifier] NOT NULL, [RecordType] [tinyint] NOT NULL CONSTRAINT [DF__tblMeterH__Recor__34C8D9D1] DEFAULT ((1)), [ExternalSourceID] [tinyint] NOT NULL CONSTRAINT [DF_tblMeterHistory_ExternalSourceID] DEFAULT ((0)), [Date] [datetime] NOT NULL, [Volume] [float] NULL CONSTRAINT [DF__tblMeterH__Volum__35BCFE0A] DEFAULT ((0)), [Pressure] [float] NULL CONSTRAINT [DF__tblMeterH__Press__36B12243] DEFAULT ((0)), [CasingPressure] [float] NULL CONSTRAINT [DF__tblMeterH__Casin__37A5467C] DEFAULT ((0)), [TubingPressure] [float] NULL CONSTRAINT [DF__tblMeterH__Tubin__38996AB5] DEFAULT ((0)), [DifferentialPressure] [float] NULL, [Temperature] [float] NULL, CONSTRAINT [PK_tblMeterHistory_1] PRIMARY KEY CLUSTERED ( [MeterID] ASC, [RecordType] ASC, [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblMeterHistory] UNIQUE NONCLUSTERED ( [MeterID] ASC, [RecordType] ASC, [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: StoredProcedure [dbo].[up_ImportAlarmHistory2] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportAlarmHistory2]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports alarm history into CDS from dtsAlarmHistory -- ============================================= CREATE PROCEDURE [dbo].[up_ImportAlarmHistory2] AS BEGIN DECLARE @id uniqueidentifier DECLARE @nodeID varchar(50) DECLARE @date datetime DECLARE @source varchar(3) DECLARE @text nvarchar(50) DELETE FROM tblAlarmHistory WHERE ExternalSourceID=1 DECLARE c0 CURSOR FOR SELECT NodeID, [Date], Source, [Text] FROM dtsAlarmHistory OPEN c0 FETCH NEXT FROM c0 INTO @nodeID, @date, @source, @text WHILE @@FETCH_STATUS = 0 BEGIN PRINT @nodeID SELECT @id = (SELECT s0.ID FROM tblNodes s0 WHERE s0.ExternalName=@nodeID AND s0.ExternalSourceID=1) IF NOT @id IS NULL BEGIN IF NOT EXISTS(SELECT ID FROM tblAlarmHistory WHERE NodeID=@id AND [Date]=@date AND [Source]=@source AND [Text]=@text) BEGIN INSERT INTO tblAlarmHistory (NodeID,[Date],ExternalSourceID,[Source],[Text]) VALUES( @id, @date,1, @source, @text) END END FETCH NEXT FROM c0 INTO @nodeID, @date, @source, @text END CLOSE c0 DEALLOCATE c0 END ' END GO /****** Object: StoredProcedure [dbo].[up_ImportAlarmHistory] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportAlarmHistory]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports alarm history into CDS from dtsAlarmHistory -- ============================================= CREATE PROCEDURE [dbo].[up_ImportAlarmHistory] AS BEGIN DECLARE @id uniqueidentifier DECLARE @nodeID varchar(50) DECLARE @date datetime DECLARE @source varchar(3) DECLARE @text nvarchar(50) DELETE FROM tblAlarmHistory WHERE ExternalSourceID=1 DECLARE c0 CURSOR FOR SELECT NodeID, [Date], Source, [Text] FROM dtsAlarmHistory Where Date is Not Null OPEN c0 FETCH NEXT FROM c0 INTO @nodeID, @date, @source, @text WHILE @@FETCH_STATUS = 0 BEGIN PRINT @nodeID SELECT @id = (SELECT s0.ID FROM tblNodes s0 WHERE s0.ExternalName=@nodeID AND s0.ExternalSourceID=1) IF NOT @id IS NULL BEGIN IF NOT EXISTS(SELECT ID FROM tblAlarmHistory WHERE NodeID=@id AND [Date]=@date AND [Source]=@source AND [Text]=@text) BEGIN INSERT INTO tblAlarmHistory (NodeID,[Date],ExternalSourceID,[Source],[Text]) VALUES( @id, @date,1, @source, @text) END END FETCH NEXT FROM c0 INTO @nodeID, @date, @source, @text END CLOSE c0 DEALLOCATE c0 END ' END GO /****** Object: StoredProcedure [dbo].[up_GetTankNamesByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankNamesByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets tank names by route -- ============================================= CREATE PROCEDURE [dbo].[up_GetTankNamesByRoute]( @routeName nvarchar(50) ) AS BEGIN SELECT DISTINCT t0.NodeID, t1.Name, (SELECT Name FROM tblTanks s0 WHERE s0.NodeID=t0.NodeID AND s0.TankIndex=1) AS Tank1_Name, (SELECT Name FROM tblTanks s1 WHERE s1.NodeID=t0.NodeID AND s1.TankIndex=2) AS Tank2_Name, (SELECT Name FROM tblTanks s2 WHERE s2.NodeID=t0.NodeID AND s2.TankIndex=3) AS Tank3_Name, (SELECT Name FROM tblTanks s3 WHERE s3.NodeID=t0.NodeID AND s3.TankIndex=4) AS Tank4_Name FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE t0.NodeID IN (SELECT s4.NodeID FROM tblRouteNodes s4 WHERE s4.RouteID=(SELECT s5.ID FROM tblRoutes s5 WHERE s5.Name=@routeName)) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_GetTankNamesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankNamesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets tank names by name -- ============================================= CREATE PROCEDURE [dbo].[up_GetTankNamesByName]( @name nvarchar(125) ) AS BEGIN SELECT DISTINCT t0.NodeID, t1.Name, (SELECT Name FROM tblTanks s0 WHERE s0.NodeID=t0.NodeID AND s0.TankIndex=1) AS Tank1_Name, (SELECT Name FROM tblTanks s1 WHERE s1.NodeID=t0.NodeID AND s1.TankIndex=2) AS Tank2_Name, (SELECT Name FROM tblTanks s2 WHERE s2.NodeID=t0.NodeID AND s2.TankIndex=3) AS Tank3_Name, (SELECT Name FROM tblTanks s3 WHERE s3.NodeID=t0.NodeID AND s3.TankIndex=4) AS Tank4_Name FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE t0.NodeID = (SELECT s4.ID FROM tblNodes s4 WHERE s4.Name=@name) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_GetTankNamesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankNamesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets tank names by name -- ============================================= CREATE PROCEDURE [dbo].[up_GetTankNamesByExternalName]( @name nvarchar(125) ) AS BEGIN SELECT DISTINCT t0.NodeID, t1.Name, (SELECT Name FROM tblTanks s0 WHERE s0.NodeID=t0.NodeID AND s0.TankIndex=1) AS Tank1_Name, (SELECT Name FROM tblTanks s1 WHERE s1.NodeID=t0.NodeID AND s1.TankIndex=2) AS Tank2_Name, (SELECT Name FROM tblTanks s2 WHERE s2.NodeID=t0.NodeID AND s2.TankIndex=3) AS Tank3_Name, (SELECT Name FROM tblTanks s3 WHERE s3.NodeID=t0.NodeID AND s3.TankIndex=4) AS Tank4_Name FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE t0.NodeID = (SELECT s4.ID FROM tblNodes s4 WHERE s4.ExternalName=@name) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_AddRouteNodeByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddRouteNodeByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Adds a node to a route -- ============================================= CREATE PROCEDURE [dbo].[up_AddRouteNodeByName]( @node nvarchar(125), @route nvarchar(50) ) AS BEGIN DECLARE @nodeID uniqueidentifier DECLARE @routeID uniqueidentifier SELECT @nodeID = (SELECT ID FROM tblNodes WHERE Name=@node) SELECT @routeID = (SELECT ID FROM tblRoutes WHERE Name=@route) INSERT INTO tblRouteNodes (RouteID,NodeID) VALUES(@routeID,@nodeID) END ' END GO /****** Object: StoredProcedure [dbo].[up_AddRouteNodeByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddRouteNodeByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Adds a node to a route -- ============================================= CREATE PROCEDURE [dbo].[up_AddRouteNodeByExternalName]( @node nvarchar(125), @route nvarchar(50) ) AS BEGIN DECLARE @nodeID uniqueidentifier DECLARE @routeID uniqueidentifier SELECT @nodeID = (SELECT ID FROM tblNodes WHERE ExternalName=@node) SELECT @routeID = (SELECT ID FROM tblRoutes WHERE Name=@route) INSERT INTO tblRouteNodes (RouteID,NodeID) VALUES(@routeID,@nodeID) END ' END GO /****** Object: StoredProcedure [dbo].[up_GetAnalogNamesByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogNamesByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets analog names by route -- ============================================= CREATE PROCEDURE [dbo].[up_GetAnalogNamesByRoute]( @routeName nvarchar(50) ) AS BEGIN SELECT DISTINCT t0.NodeID, t1.Name, (SELECT Name FROM tblAnalogs s0 WHERE s0.NodeID=t0.NodeID AND s0.AnalogIndex=1) AS Analog01_Name, (SELECT Name FROM tblAnalogs s1 WHERE s1.NodeID=t0.NodeID AND s1.AnalogIndex=2) AS Analog02_Name, (SELECT Name FROM tblAnalogs s2 WHERE s2.NodeID=t0.NodeID AND s2.AnalogIndex=3) AS Analog03_Name, (SELECT Name FROM tblAnalogs s3 WHERE s3.NodeID=t0.NodeID AND s3.AnalogIndex=4) AS Analog04_Name, (SELECT Name FROM tblAnalogs s4 WHERE s4.NodeID=t0.NodeID AND s4.AnalogIndex=5) AS Analog05_Name, (SELECT Name FROM tblAnalogs s5 WHERE s5.NodeID=t0.NodeID AND s5.AnalogIndex=6) AS Analog06_Name, (SELECT Name FROM tblAnalogs s6 WHERE s6.NodeID=t0.NodeID AND s6.AnalogIndex=7) AS Analog07_Name, (SELECT Name FROM tblAnalogs s7 WHERE s7.NodeID=t0.NodeID AND s7.AnalogIndex=8) AS Analog08_Name, (SELECT Name FROM tblAnalogs s8 WHERE s8.NodeID=t0.NodeID AND s8.AnalogIndex=9) AS Analog09_Name, (SELECT Name FROM tblAnalogs s9 WHERE s9.NodeID=t0.NodeID AND s9.AnalogIndex=10) AS Analog10_Name FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE t0.NodeID IN (SELECT s4.NodeID FROM tblRouteNodes s4 WHERE s4.RouteID=(SELECT s5.ID FROM tblRoutes s5 WHERE s5.Name=@routeName)) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_GetAlarmHistoryByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAlarmHistoryByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Gets alarm history by route -- ============================================= CREATE PROCEDURE [dbo].[up_GetAlarmHistoryByRoute]( @routeName nvarchar(50) ) AS BEGIN SELECT t0.ID, t0.NodeID, t1.Name, t0.[Date],t0.ExternalSourceID,t0.[Source],t0.[Text] FROM tblAlarmHistory t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID INNER JOIN (SELECT s0.NodeID, MAX(s0.Date) AS LastAlarmDate FROM tblAlarmHistory s0 GROUP BY s0.NodeID) d0 ON d0.NodeID=t0.NodeID WHERE t0.NodeID IN (SELECT s0.NodeID FROM tblRouteNodes s0 WHERE s0.RouteID=(SELECT s1.ID FROM tblRoutes s1 WHERE s1.Name=@routeName)) ORDER BY t0.[Date] DESC END ' END GO /****** Object: StoredProcedure [dbo].[up_GetNodeSummaryByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetNodeSummaryByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_GetNodeSummaryByRoute]( @routeName nvarchar(50) ) AS SELECT t0.NodeID, t1.Name, t1.DeviceTypeID, t2.Name AS [DeviceName],t1.[Address], t0.CommStatus,t0.LastComm,t0.RunStatus,t0.TIS,t0.IdleTime,t0.Runtime,t0.RuntimeYest,t0.Cycles, (SELECT TOP 1 Text FROM tblAlarmHistory WHERE NodeID=t0.NodeID) AS Alarm FROM tblStatus t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID INNER JOIN tblDeviceTypes t2 ON t2.ID = t1.DeviceTypeID WHERE t0.NodeID IN (SELECT NodeID FROM tblRouteNodes WHERE RouteID=(SELECT ID FROM tblRoutes WHERE Name=@routeName)) ' END GO /****** Object: StoredProcedure [dbo].[up_GetRouteNodes] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetRouteNodes]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets route nodes -- ============================================= CREATE PROCEDURE [dbo].[up_GetRouteNodes]( @routeName nvarchar(50) ) AS BEGIN SELECT t0.RouteID, t0.NodeID, t2.Name FROM tblRouteNodes t0 INNER JOIN tblRoutes t1 ON t0.RouteID=t1.ID INNER JOIN tblNodes t2 ON t0.NodeID=t2.ID WHERE t0.RouteID =(SELECT s0.ID FROM tblRoutes s0 WHERE s0.Name=@routeName) ORDER BY t2.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_ImportStatus] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportStatus]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports status into CDS -- ============================================= CREATE PROCEDURE [dbo].[up_ImportStatus] AS BEGIN DELETE t0 FROM tblStatus t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID WHERE t1.ExternalSourceID =1 INSERT INTO tblStatus (NodeID,CommStatus,LastComm,RunStatus,TIS,IdleTime,Runtime,RuntimeYest,Cycles) (SELECT t1.ID, t0.CommStatus, t0.LastGoodScanTime, t0.RunStatus, t0.TimeInState, t0.IdleTime, t0.RuntimeHours,t0.YestRuntimeHours,t0.TodayCycles FROM dtsStatusChalk t0 INNER JOIN tblNodes t1 ON t1.ExternalName=t0.NodeID WHERE t1.ExternalSourceID=1) END ' END GO /****** Object: Table [dbo].[tblTankHistory] Script Date: 08/08/2010 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTankHistory]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tblTankHistory]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [TankID] [uniqueidentifier] NOT NULL, [ExternalSourceID] [tinyint] NOT NULL CONSTRAINT [DF_tblTankHistory_ExternalSourceID] DEFAULT ((0)), [Date] [datetime] NOT NULL, [TankLevel] [float] NOT NULL, [Temperature] [float] NULL, CONSTRAINT [PK_tblTankHistory_1] PRIMARY KEY CLUSTERED ( [TankID] ASC, [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_tblTankHistory] UNIQUE NONCLUSTERED ( [TankID] ASC, [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: StoredProcedure [dbo].[up_GetMetersByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetMetersByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets meter information by route -- ============================================= CREATE PROCEDURE [dbo].[up_GetMetersByRoute]( @routeName nvarchar(50) ) AS BEGIN SELECT t0.ID, t0.NodeID, t1.Name, t0.MeterIndex, t0.MeterKey,t0.ExternalSourceID, t1.GasCo,t1.GasHW FROM tblMeters t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID WHERE t0.NodeID IN (SELECT s0.NodeID FROM tblRouteNodes s0 WHERE s0.RouteID=(SELECT s1.ID FROM tblRoutes s1 WHERE s1.Name=@routeName)) END ' END GO /****** Object: StoredProcedure [dbo].[up_AddMoveRouteNodeByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddMoveRouteNodeByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Adds/Moves a node to a route -- ============================================= CREATE PROCEDURE [dbo].[up_AddMoveRouteNodeByExternalName]( @node nvarchar(125), @route nvarchar(50) ) AS BEGIN DECLARE @nodeID uniqueidentifier DECLARE @routeID uniqueidentifier DECLARE @isWaterHauler bit DECLARE @isOilHauler bit IF NOT @route IS NULL BEGIN SELECT @nodeID = (SELECT ID FROM tblNodes WHERE ExternalName=@node AND ExternalSourceID=1) SELECT @routeID=ID, @isWaterHauler=IsWaterHauler,@isOilHauler=IsOilHauler FROM tblRoutes WHERE Name=@route IF NOT @nodeID IS NULL AND NOT @routeID IS NULL BEGIN IF @isWaterHauler<>0 BEGIN PRINT ''Synchronizing water hauler route ('' + @route + '')...'' -- Remove node from other water hauler routes DELETE t0 FROM tblRouteNodes t0 INNER JOIN tblRoutes t1 ON t0.RouteID=t1.ID WHERE t0.NodeID=@nodeID AND t1.IsWaterHauler<>0 AND t0.RouteID <> @routeID END IF @isOilHauler<>0 BEGIN PRINT ''Synchronizing oil hauler route ('' + @route + '')...'' -- Remove node from other oil hauler routes DELETE t0 FROM tblRouteNodes t0 INNER JOIN tblRoutes t1 ON t0.RouteID=t1.ID WHERE t0.NodeID=@nodeID AND t1.IsOilHauler<>0 AND t0.RouteID <> @routeID END IF @isWaterHauler = 0 AND @isOilHauler = 0 BEGIN PRINT ''Synchronizing route ('' + @route + '')...'' -- Remove node from other routes DELETE t0 FROM tblRouteNodes t0 INNER JOIN tblRoutes t1 ON t0.RouteID=t1.ID WHERE t0.NodeID=@nodeID AND t0.RouteID <> @routeID AND t1.IsOilHauler=0 AND t1.IsWaterHauler=0 END IF NOT EXISTS(SELECT ID from tblRouteNodes where NodeID=@nodeID AND RouteID=@routeID) BEGIN PRINT ''Adding '' + @node + '' to route ('' + @route + '')...'' INSERT INTO tblRouteNodes (RouteID,NodeID) VALUES(@routeID,@nodeID) END END END END ' END GO /****** Object: StoredProcedure [dbo].[up_GetRouteSummaryByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetRouteSummaryByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_GetRouteSummaryByRoute](@routeName nvarchar(50)) AS BEGIN SELECT t1.ID, t1.Name, (SELECT TOP 1 Volume FROM tblMeterHistory WHERE MeterID=(SELECT ID FROM tblMeters WHERE NodeID=t1.ID AND MeterIndex=1) AND RecordType=1 ORDER BY Date DESC) AS Meter1_Gas, (SELECT TOP 1 Volume FROM tblMeterHistory WHERE MeterID=(SELECT ID FROM tblMeters WHERE NodeID=t1.ID AND MeterIndex=2) AND RecordType=1 ORDER BY Date DESC) AS Meter2_Gas, (SELECT TOP 1 TankLevel FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=1) ORDER BY Date DESC) AS Tank01_Level, (SELECT TOP 1 Temperature FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=1) ORDER BY Date DESC) AS Tank01_Temperature, (SELECT TOP 1 TankLevel FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=2) ORDER BY Date DESC) AS Tank02_Level, (SELECT TOP 1 Temperature FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=2) ORDER BY Date DESC) AS Tank02_Temperature, (SELECT TOP 1 TankLevel FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=3) ORDER BY Date DESC) AS Tank03_Level, (SELECT TOP 1 Temperature FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=3) ORDER BY Date DESC) AS Tank03_Temperature, (SELECT TOP 1 TankLevel FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=4) ORDER BY Date DESC) AS Tank04_Level, (SELECT TOP 1 Temperature FROM tblTankHistory WHERE TankID=(SELECT ID FROM tblTanks WHERE NodeID=t1.ID AND TankIndex=4) ORDER BY Date DESC) AS Tank04_Temperature FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID INNER JOIN tblRoutes t2 ON t2.ID=t0.RouteID WHERE t2.Name=@routeName ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_AddMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddMeterValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Add meter values -- ============================================= CREATE PROCEDURE [dbo].[up_AddMeterValuesByName]( @name nvarchar(125), @meterIndex tinyint, @type tinyint, @date datetime, @volume float, @pressure float, @casingPressure float, @tubingPressure float, @differentialPressure float, @temperature float, @source smallint = 0 ) AS BEGIN INSERT INTO tblMeterHistory (MeterID,RecordType,ExternalSourceID,Date,Volume,Pressure,CasingPressure, TubingPressure,DifferentialPressure,Temperature) SELECT t0.[ID], @type,@source,@date,@volume,@pressure,@casingPressure,@tubingPressure,@differentialPressure, @temperature FROM tblMeters t0 WHERE t0.NodeID=(SELECT [ID] FROM tblNodes s0 WHERE s0.Name=@name) AND MeterIndex=@meterIndex END ' END GO /****** Object: StoredProcedure [dbo].[up_AddMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddMeterValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Add meter values -- ============================================= CREATE PROCEDURE [dbo].[up_AddMeterValuesByExternalName]( @name nvarchar(125), @meterIndex tinyint, @type tinyint, @date datetime, @volume float, @pressure float, @casingPressure float, @tubingPressure float, @differentialPressure float, @temperature float, @source smallint = 0 ) AS BEGIN INSERT INTO tblMeterHistory (MeterID,RecordType,ExternalSourceID,Date,Volume,Pressure,CasingPressure, TubingPressure,DifferentialPressure,Temperature) SELECT t0.[ID], @type,@source,@date,@volume,@pressure,@casingPressure,@tubingPressure,@differentialPressure, @temperature FROM tblMeters t0 WHERE t0.NodeID=(SELECT [ID] FROM tblNodes s0 WHERE s0.ExternalName=@name) AND MeterIndex=@meterIndex END ' END GO /****** Object: StoredProcedure [dbo].[up_AddAnalogValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddAnalogValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Add a Analog Values -- ============================================= CREATE PROCEDURE [dbo].[up_AddAnalogValuesByName]( @name nvarchar(125), @analogIndex int, @date datetime, @value float, @source smallint = 0 ) AS BEGIN INSERT INTO tblAnalogHistory (AnalogID,[Date],ExternalSourceID,Value) SELECT t0.[ID], @date,@source,@value FROM tblAnalogs t0 WHERE t0.NodeID=(SELECT [ID] FROM tblNodes s0 WHERE s0.Name=@name) AND t0.AnalogIndex=@analogIndex END ' END GO /****** Object: StoredProcedure [dbo].[up_AddAnalogValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddAnalogValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Add a Analog Values -- ============================================= CREATE PROCEDURE [dbo].[up_AddAnalogValuesByExternalName]( @name nvarchar(125), @analogIndex int, @date datetime, @value float, @source smallint = 0 ) AS BEGIN INSERT INTO tblAnalogHistory (AnalogID,[Date],ExternalSourceID,Value) SELECT t0.[ID], @date,@source,@value FROM tblAnalogs t0 WHERE t0.NodeID=(SELECT [ID] FROM tblNodes s0 WHERE s0.ExternalName=@name) AND t0.AnalogIndex=@analogIndex END ' END GO /****** Object: StoredProcedure [dbo].[up_GetHourlyMeterValuesByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetHourlyMeterValuesByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets hourly meter values for specified route and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetHourlyMeterValuesByRoute]( @routeName nvarchar(50), @startDate datetime, @endDate datetime ) AS BEGIN SELECT t1.ID, t1.NodeID, t1.MeterIndex,t0.Date,t0.Volume,t0.Pressure,t0.CasingPressure, t0.TubingPressure,t0.DifferentialPressure,t0.Temperature FROM tblMeterHistory t0 INNER JOIN tblMeters t1 ON t1.ID=t0.MeterID WHERE t0.[MeterID] IN (SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID IN (SELECT s1.NodeID FROM tblRouteNodes s1 WHERE s1.RouteID=(SELECT s2.ID FROM tblRoutes s2 WHERE s2.Name=@routeName))) AND t0.RecordType=2 AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.MeterID, t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetHourlyMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetHourlyMeterValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets hourly meter values for specified node, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetHourlyMeterValuesByName]( @name nvarchar(125), @meterIndex tinyint, @startDate datetime, @endDate datetime ) AS BEGIN SELECT t0.* FROM tblMeterHistory t0 WHERE t0.[MeterID]=(SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID=(SELECT s1.ID FROM tblNodes s1 WHERE s1.Name=@name) AND s0.MeterIndex=@meterIndex) AND t0.RecordType=2 AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetHourlyMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetHourlyMeterValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets hourly meter values for specified node, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetHourlyMeterValuesByExternalName]( @name nvarchar(125), @meterIndex tinyint, @startDate datetime, @endDate datetime ) AS BEGIN SELECT t0.* FROM tblMeterHistory t0 WHERE t0.[MeterID]=(SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID=(SELECT s1.ID FROM tblNodes s1 WHERE s1.ExternalName=@name) AND s0.MeterIndex=@meterIndex) AND t0.RecordType=2 AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetDailyMeterValuesByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetDailyMeterValuesByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets daily meter values for specified route, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetDailyMeterValuesByRoute]( @routeName nvarchar(50), @startDate datetime, @endDate datetime ) AS BEGIN SELECT t1.ID, t1.NodeID, t1.MeterIndex,t0.Date,t0.Volume,t0.Pressure,t0.CasingPressure, t0.TubingPressure,t0.DifferentialPressure,t0.Temperature FROM tblMeterHistory t0 INNER JOIN tblMeters t1 ON t1.ID=t0.MeterID WHERE t0.[MeterID] IN (SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID IN (SELECT s1.NodeID FROM tblRouteNodes s1 WHERE s1.RouteID=(SELECT s2.ID FROM tblRoutes s2 WHERE s2.Name=@routeName))) AND t0.RecordType=1 AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.MeterID, t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetDailyMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetDailyMeterValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets daily meter values for specified node, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetDailyMeterValuesByName]( @name nvarchar(125), @meterIndex tinyint, @startDate datetime, @endDate datetime ) AS BEGIN SELECT t0.* FROM tblMeterHistory t0 WHERE t0.[MeterID]=(SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID=(SELECT s1.ID FROM tblNodes s1 WHERE s1.Name=@name) AND s0.MeterIndex=@meterIndex) AND t0.RecordType=1 AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetDailyMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetDailyMeterValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets daily meter values for specified node, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetDailyMeterValuesByExternalName]( @name nvarchar(125), @meterIndex tinyint, @startDate datetime, @endDate datetime ) AS BEGIN SELECT t0.* FROM tblMeterHistory t0 WHERE t0.[MeterID]=(SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID=(SELECT s1.ID FROM tblNodes s1 WHERE s1.ExternalName=@name) AND s0.MeterIndex=@meterIndex) AND t0.RecordType=1 AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.[Date] END ' END GO /****** Object: View [dbo].[vTankLevelHistory] Script Date: 08/08/2010 10:56:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vTankLevelHistory]')) EXEC dbo.sp_executesql @statement = N' CREATE VIEW [dbo].[vTankLevelHistory] --WITH SCHEMABINDING AS SELECT d0.ID AS NodeID, d0.Name, ISNULL(t1.Date,ISNULL(t2.Date,ISNULL(t4.Date,t6.Date))) AS [Date], t1.TankLevel AS Tank01_Level, t1.Temperature AS Tank01_Temperature, t2.TankLevel AS Tank02_Level, t2.Temperature AS Tank02_Temperature, t4.TankLevel AS Tank03_Level, t4.Temperature AS Tank03_Temperature, t6.TankLevel AS Tank04_Level, t6.Temperature AS Tank04_Temperature FROM (SELECT s0.ID,s0.Name, (SELECT ID FROM tblTanks WHERE NodeID=s0.ID AND TankIndex=1) AS T1_ID, (SELECT ID FROM tblTanks WHERE NodeID=s0.ID AND TankIndex=2) AS T2_ID, (SELECT ID FROM tblTanks WHERE NodeID=s0.ID AND TankIndex=3) AS T3_ID, (SELECT ID FROM tblTanks WHERE NodeID=s0.ID AND TankIndex=4) AS T4_ID FROM tblNodes s0 ) d0 LEFT OUTER JOIN tblTankHistory t1 ON t1.TankID=d0.T1_ID LEFT OUTER JOIN tblTankHistory t2 ON t2.Date=ISNULL(t1.Date,t2.Date) AND t2.TankID =d0.T2_ID LEFT OUTER JOIN tblTankHistory t4 ON t4.Date=ISNULL(t1.Date,ISNULL(t2.Date,t4.Date)) AND t4.TankID =d0.T3_ID LEFT OUTER JOIN tblTankHistory t6 ON t6.Date=ISNULL(t1.Date,ISNULL(t2.Date,ISNULL(t4.Date,t6.Date))) AND t6.TankID =d0.T4_ID ' GO /****** Object: View [dbo].[vMeterHistoryHourly] Script Date: 08/08/2010 10:56:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vMeterHistoryHourly]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vMeterHistoryHourly] AS SELECT t2.Name AS NodeID, t1.FieldDirectKey AS FieldDirectID, t4.Name AS Route, t0.Date, 24 AS HoursOn, CASE WHEN t1.MeterIndex = 2 THEN - t0.Volume ELSE t0.Volume END AS Volume, t0.DifferentialPressure, t0.Pressure AS StaticPressure, t0.Temperature AS Temp FROM dbo.tblMeterHistory AS t0 INNER JOIN dbo.tblMeters AS t1 ON t0.MeterID = t1.ID INNER JOIN dbo.tblNodes AS t2 ON t1.NodeID = t2.ID INNER JOIN dbo.tblRouteNodes AS t3 ON t2.ID = t3.NodeID INNER JOIN dbo.tblRoutes AS t4 ON t3.RouteID = t4.ID WHERE (t4.IsOilHauler = 0) AND (t4.IsWaterHauler = 0) AND (t0.RecordType = 2) AND (t0.Date > GETDATE() - 7) ' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vMeterHistoryHourly', NULL,NULL)) 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[21] 2[5] 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 = -274 Left = 0 End Begin Tables = Begin Table = "t0" Begin Extent = Top = 6 Left = 38 Bottom = 125 Right = 222 End DisplayFlags = 280 TopColumn = 2 End Begin Table = "t1" Begin Extent = Top = 6 Left = 260 Bottom = 125 Right = 433 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t2" Begin Extent = Top = 126 Left = 38 Bottom = 245 Right = 211 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t3" Begin Extent = Top = 126 Left = 249 Bottom = 230 Right = 409 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t4" Begin Extent = Top = 234 Left = 249 Bottom = 353 Right = 409 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 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vMeterHistoryHourly' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'vMeterHistoryHourly', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' 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'vMeterHistoryHourly' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vMeterHistoryHourly', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vMeterHistoryHourly' GO /****** Object: View [dbo].[vFieldDirectTankLevels] Script Date: 08/08/2010 10:56:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFieldDirectTankLevels]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFieldDirectTankLevels] AS SELECT t1.ID, t1.Name, t0.FieldDirectKey, d0.Date, d0.TankLevel FROM dbo.tblTanks AS t0 INNER JOIN (SELECT TankID, DATEADD(dd, 0, DATEDIFF(dd, 0, Date)) AS Date, (SELECT TOP (1) TankLevel FROM dbo.tblTankHistory AS s0 WHERE (TankID = t0.TankID) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, Date)) = DATEADD(dd, 0, DATEDIFF(dd, 0, t0.Date))) AND (DATEPART(hour, Date) < ISNULL ((SELECT Value FROM dbo.tblSystemValues WHERE (Name = ''FieldDirectHour'')), 7)) ORDER BY Date DESC) AS TankLevel FROM dbo.tblTankHistory AS t0 GROUP BY TankID, DATEADD(dd, 0, DATEDIFF(dd, 0, Date))) AS d0 ON d0.TankID = t0.ID INNER JOIN dbo.tblNodes AS t1 ON t0.NodeID = t1.ID ' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFieldDirectTankLevels', NULL,NULL)) 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 = "t0" Begin Extent = Top = 6 Left = 38 Bottom = 123 Right = 198 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "d0" Begin Extent = Top = 6 Left = 236 Bottom = 108 Right = 396 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t1" Begin Extent = Top = 6 Left = 434 Bottom = 123 Right = 607 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" 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'vFieldDirectTankLevels' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFieldDirectTankLevels', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFieldDirectTankLevels' GO /****** Object: View [dbo].[vFieldDirectMeterHistoryDaily] Script Date: 08/08/2010 10:56:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFieldDirectMeterHistoryDaily]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFieldDirectMeterHistoryDaily] AS SELECT t2.Name AS NodeID, t1.FieldDirectKey AS FieldDirectID, t4.Name AS Route, t0.Date, 24 AS HoursOn, CASE WHEN t1.MeterIndex = 2 THEN - t0.Volume ELSE t0.Volume END AS Volume, t0.DifferentialPressure, t0.Pressure AS StaticPressure, t0.Temperature AS Temp FROM dbo.tblMeterHistory AS t0 INNER JOIN dbo.tblMeters AS t1 ON t0.MeterID = t1.ID INNER JOIN dbo.tblNodes AS t2 ON t1.NodeID = t2.ID INNER JOIN dbo.tblRouteNodes AS t3 ON t2.ID = t3.NodeID INNER JOIN dbo.tblRoutes AS t4 ON t3.RouteID = t4.ID WHERE (t4.IsOilHauler = 0) AND (t4.IsWaterHauler = 0) AND (t0.RecordType = 1) AND (t0.Date <= GETDATE() - 1) ' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFieldDirectMeterHistoryDaily', NULL,NULL)) 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 = "t0" Begin Extent = Top = 6 Left = 38 Bottom = 114 Right = 213 End DisplayFlags = 280 TopColumn = 1 End Begin Table = "t1" Begin Extent = Top = 6 Left = 251 Bottom = 114 Right = 415 End DisplayFlags = 280 TopColumn = 1 End Begin Table = "t2" Begin Extent = Top = 114 Left = 38 Bottom = 222 Right = 212 End DisplayFlags = 280 TopColumn = 16 End Begin Table = "t3" Begin Extent = Top = 114 Left = 250 Bottom = 207 Right = 401 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t4" Begin Extent = Top = 210 Left = 250 Bottom = 318 Right = 401 End DisplayFlags = 280 TopColumn = 8 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" 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 En' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFieldDirectMeterHistoryDaily' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'vFieldDirectMeterHistoryDaily', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'd ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFieldDirectMeterHistoryDaily' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFieldDirectMeterHistoryDaily', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFieldDirectMeterHistoryDaily' GO /****** Object: View [dbo].[vAnalogHistory] Script Date: 08/08/2010 10:56:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vAnalogHistory]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vAnalogHistory] AS SELECT n.ID, n.Name, t0.Date, t0.Value AS Analog01, t2.Value AS Analog02, t3.Value AS Analog03, t4.Value AS Analog04, t5.Value AS Analog05, t6.Value AS Analog06, t7.Value AS Analog07, t8.Value AS Analog08, t9.Value AS Analog09, t10.Value AS Analog10 FROM dbo.tblAnalogHistory AS t0 INNER JOIN dbo.tblAnalogs AS t1 ON t1.ID = t0.AnalogID AND t1.AnalogIndex = 1 INNER JOIN dbo.tblNodes AS n ON n.ID = t1.NodeID LEFT OUTER JOIN dbo.tblAnalogHistory AS t2 ON t2.Date = t0.Date AND t2.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s0 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 2)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t3 ON t3.Date = t0.Date AND t3.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s1 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 3)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t4 ON t4.Date = t0.Date AND t4.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 4)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t5 ON t5.Date = t0.Date AND t5.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 5)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t6 ON t6.Date = t0.Date AND t6.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 6)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t7 ON t7.Date = t0.Date AND t7.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 7)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t8 ON t8.Date = t0.Date AND t8.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 8)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t9 ON t9.Date = t0.Date AND t9.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 9)) LEFT OUTER JOIN dbo.tblAnalogHistory AS t10 ON t10.Date = t0.Date AND t10.AnalogID = (SELECT ID FROM dbo.tblAnalogs AS s2 WHERE (NodeID = t1.NodeID) AND (AnalogIndex = 10)) ' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vAnalogHistory', NULL,NULL)) 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 = "t0" Begin Extent = Top = 6 Left = 38 Bottom = 123 Right = 198 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t1" Begin Extent = Top = 6 Left = 236 Bottom = 123 Right = 396 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "n" Begin Extent = Top = 6 Left = 434 Bottom = 123 Right = 607 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t2" Begin Extent = Top = 6 Left = 645 Bottom = 123 Right = 805 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t3" Begin Extent = Top = 6 Left = 843 Bottom = 123 Right = 1003 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t4" Begin Extent = Top = 6 Left = 1041 Bottom = 123 Right = 1201 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t5" Begin Extent = Top = 126 Left = 38 Bottom = 243 Right = 198 End DisplayFlags = 280 TopColumn = 0 End' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vAnalogHistory' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'vAnalogHistory', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N' Begin Table = "t6" Begin Extent = Top = 126 Left = 236 Bottom = 243 Right = 396 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t7" Begin Extent = Top = 126 Left = 434 Bottom = 243 Right = 594 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t8" Begin Extent = Top = 126 Left = 632 Bottom = 243 Right = 792 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t9" Begin Extent = Top = 126 Left = 830 Bottom = 243 Right = 990 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "t10" Begin Extent = Top = 126 Left = 1028 Bottom = 243 Right = 1188 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" 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'vAnalogHistory' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vAnalogHistory', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vAnalogHistory' GO /****** Object: StoredProcedure [dbo].[up_ImportTankLevels] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportTankLevels]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports tank levels into CDS from dtsTankLevelsCache -- ============================================= CREATE PROCEDURE [dbo].[up_ImportTankLevels] AS BEGIN DECLARE @tankID uniqueidentifier DECLARE @nodeID uniqueidentifier DECLARE @externalName nvarchar(125) DECLARE @tankIndex tinyint DECLARE @lastDate datetime DECLARE c0 CURSOR FOR SELECT t0.ID, t0.NodeID, t1.ExternalName, t0.TankIndex, ISNULL((SELECT MAX(Date) FROM tblTankHistory WHERE TankID=t0.ID),''1/1/1900'') AS LastDate FROM tblTanks t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID AND t1.ExternalSourceID=1 ORDER BY t1.ExternalName OPEN c0 FETCH NEXT FROM c0 INTO @tankID, @nodeID, @externalName, @tankIndex, @lastDate WHILE @@FETCH_STATUS = 0 BEGIN IF @tankIndex=1 BEGIN Print ''Importing '' + @externalName + '' Tank 1...'' INSERT INTO tblTankHistory SELECT @tankID, 1, Date, TNK1LVL, TNK1TMP FROM dtsTankLevelsCacheChalk WHERE Date>@lastDate AND NodeID=@externalName AND NOT TNK1LVL IS NULL END IF @tankIndex=2 BEGIN Print ''Importing '' + @externalName + '' Tank 2...'' INSERT INTO tblTankHistory SELECT @tankID, 1, Date, TNK2LVL, TNK2TMP FROM dtsTankLevelsCacheChalk WHERE Date>@lastDate AND NodeID=@externalName AND NOT TNK2LVL IS NULL END IF @tankIndex=3 BEGIN Print ''Importing '' + @externalName + '' Tank 3...'' INSERT INTO tblTankHistory SELECT @tankID, 1, Date, TNK3LVL, TNK3TMP FROM dtsTankLevelsCacheChalk WHERE Date>@lastDate AND NodeID=@externalName AND NOT TNK3LVL IS NULL END IF @tankIndex=4 BEGIN Print ''Importing '' + @externalName + '' Tank 4...'' INSERT INTO tblTankHistory SELECT @tankID, 1, Date, TNK4LVL, TNK4TMP FROM dtsTankLevelsCacheChalk WHERE Date>@lastDate AND NodeID=@externalName AND NOT TNK4LVL IS NULL END FETCH NEXT FROM c0 INTO @tankID, @nodeID, @externalName, @tankIndex, @lastDate END CLOSE c0 DEALLOCATE c0 END ' END GO /****** Object: StoredProcedure [dbo].[up_ImportRoutes] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportRoutes]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[up_ImportRoutes] AS BEGIN DECLARE @routeName nvarchar(50) DECLARE @routeOil nvarchar(50) DECLARE @routeWater nvarchar(50) DECLARE @nodeID nvarchar(50) -- Find new basic routes DECLARE c0 CURSOR FOR select distinct stringname from dtsRoutesChalk where not stringname in (select name from tblRoutes) OPEN c0 FETCH NEXT FROM c0 INTO @routeName WHILE @@FETCH_STATUS = 0 BEGIN -- Add route PRINT ''Adding route '' + @routeName + ''...'' EXEC dbo.up_AddRoute @routeName FETCH NEXT FROM c0 INTO @routeName END CLOSE c0 DEALLOCATE c0 -- Find new water hauler routes DECLARE c1 CURSOR FOR select distinct waterhaulergroup from dtsRoutesChalk where not waterhaulergroup in (select name from tblRoutes where IsWaterHauler<>0) OPEN c1 FETCH NEXT FROM c1 INTO @routeName WHILE @@FETCH_STATUS = 0 BEGIN -- Add route PRINT ''Adding route '' + @routeName + ''...'' EXEC dbo.up_AddRoute @routeName, 1 FETCH NEXT FROM c1 INTO @routeName END CLOSE c1 DEALLOCATE c1 -- Find new oil hauler routes DECLARE c2 CURSOR FOR select distinct oilhaulergroup from dtsRoutesChalk where not oilhaulergroup in (select name from tblRoutes where IsOilHauler<>0) OPEN c2 FETCH NEXT FROM c2 INTO @routeName WHILE @@FETCH_STATUS = 0 BEGIN -- Add route PRINT ''Adding route '' + @routeName + ''...'' EXEC dbo.up_AddRoute @routeName, 0, 1 FETCH NEXT FROM c2 INTO @routeName END CLOSE c2 DEALLOCATE c2 -- Add/Move nodes to routes DECLARE c3 CURSOR FOR select NodeID, StringName, OilHaulerGroup,WaterHaulerGroup from dtsRoutesChalk WHERE NOT POCType IN (51) ORDER BY NodeID OPEN c3 FETCH NEXT FROM c3 INTO @nodeID, @routeName,@routeOil,@routeWater WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.up_AddMoveRouteNodeByExternalName @nodeID, @routeName EXEC dbo.up_AddMoveRouteNodeByExternalName @nodeID, @routeOil EXEC dbo.up_AddMoveRouteNodeByExternalName @nodeID, @routeWater FETCH NEXT FROM c3 INTO @nodeID, @routeName,@routeOil,@routeWater END CLOSE c3 DEALLOCATE c3 END ' END GO /****** Object: StoredProcedure [dbo].[up_ImportMeterHistoryHourly] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportMeterHistoryHourly]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports meter history into CDS from dtsMeterHistory -- ============================================= CREATE PROCEDURE [dbo].[up_ImportMeterHistoryHourly] AS BEGIN DECLARE @meterID uniqueidentifier DECLARE @nodeID uniqueidentifier DECLARE @externalName nvarchar(125) DECLARE @meterIndex tinyint DECLARE @lastDate datetime DECLARE c0 CURSOR FOR SELECT t0.ID, t0.NodeID, t1.ExternalName, t0.MeterIndex, ISNULL((SELECT MAX(Date) FROM tblMeterHistory WHERE MeterID=t0.ID AND RecordType=2),''1/1/1900'') AS LastDate FROM tblMeters t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID AND t1.ExternalSourceID=1 ORDER BY t1.ExternalName OPEN c0 FETCH NEXT FROM c0 INTO @meterID, @nodeID, @externalName, @meterIndex, @lastDate WHILE @@FETCH_STATUS = 0 BEGIN PRINT @externalName INSERT INTO tblMeterHistory (MeterID,RecordType,ExternalSourceID,Date,Volume,Pressure,DifferentialPressure,Temperature) SELECT @meterID, RecordType, 1, Date, Volume, Pressure, DiffPressure, Temp FROM dtsMeterHistoryChalk WHERE Date>@lastDate AND NodeID=@externalName AND MeterSequenceID=@meterIndex AND RecordType=2 AND NOT MeterSequenceID IS NULL FETCH NEXT FROM c0 INTO @meterID, @nodeID, @externalName, @meterIndex, @lastDate END CLOSE c0 DEALLOCATE c0 END ' END GO /****** Object: StoredProcedure [dbo].[up_ImportMeterHistoryDaily] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportMeterHistoryDaily]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports meter history into CDS from dtsMeterHistory -- ============================================= CREATE PROCEDURE [dbo].[up_ImportMeterHistoryDaily] AS BEGIN DECLARE @meterID uniqueidentifier DECLARE @nodeID uniqueidentifier DECLARE @externalName nvarchar(125) DECLARE @meterIndex tinyint DECLARE @lastDate datetime BEGIN TRANSACTION DECLARE c0 CURSOR FOR SELECT t0.ID, t0.NodeID, t1.ExternalName, t0.MeterIndex, ISNULL((SELECT MAX(Date) FROM tblMeterHistory WHERE MeterID=t0.ID AND RecordType=1), ''1/1/1900'') AS LastDate FROM tblMeters t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID AND t1.ExternalSourceID=1 ORDER BY t1.ExternalName OPEN c0 FETCH NEXT FROM c0 INTO @meterID, @nodeID, @externalName, @meterIndex, @lastDate WHILE @@FETCH_STATUS = 0 BEGIN PRINT @externalName + '' [ '' + cast(@meterIndex AS nvarchar) + '' ] '' + cast(@lastDate as nvarchar) + '' {'' + convert(nvarchar(50), @meterID) + ''}'' IF @lastDate = DATEADD(DD, -1, DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) ) BEGIN DELETE FROM tblMeterHistory WHERE MeterID=@meterID AND RecordType=1 AND ExternalSourceID=1 AND [Date]=@lastDate END INSERT INTO tblMeterHistory (MeterID,RecordType,ExternalSourceID,[Date],Volume,Pressure,DifferentialPressure,Temperature) SELECT @meterID, t0.RecordType, 1, t0.[Date], t0.Volume, t0.Pressure, t0.DiffPressure, t0.Temp FROM dtsMeterHistoryChalk t0 WHERE t0.[Date]>=@lastDate AND t0.NodeID=@externalName AND t0.MeterSequenceID=@meterIndex AND t0.RecordType=1 AND NOT t0.MeterSequenceID IS NULL AND NOT EXISTS(SELECT s0.ID FROM tblMeterHistory s0 WHERE s0.MeterID=@meterID AND s0.RecordType=1 AND s0.[Date]=t0.[Date]) FETCH NEXT FROM c0 INTO @meterID, @nodeID, @externalName, @meterIndex, @lastDate END CLOSE c0 DEALLOCATE c0 COMMIT END ' END GO /****** Object: StoredProcedure [dbo].[up_ImportETCSAMeterHistoryHourly] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportETCSAMeterHistoryHourly]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports ETCSA hourly meter values into CDS -- ============================================= CREATE PROCEDURE [dbo].[up_ImportETCSAMeterHistoryHourly] AS BEGIN DECLARE @meterId UNIQUEIDENTIFIER DECLARE @meterName nvarchar(125) DECLARE @meterIndex tinyint DECLARE @date datetime DECLARE @volume REAL DECLARE @sp REAL DECLARE @dp REAL DECLARE @temp REAL DECLARE c0 INSENSITIVE CURSOR FOR -- Only INSERT or UPDATE Hourly (RecordType=2) records that are for meters with: -- GasCo = ETCSA and GasHW = 0 SELECT t2.ID, t3.Name, t2.MeterIndex, t0.[Date], t0.Volume, t0.SP, t0.DP, t0.[Temp] FROM dbo.dts_etp_to_eos_hourly t0 INNER JOIN tblMeters t2 ON t2.MeterKey = t0.MeterID INNER JOIN tblNodes t3 ON t3.ID = t2.NodeID WHERE t3.GasCo=''ETCSA'' AND t3.GasHW=0 ORDER BY t0.MeterID, t0.[Date] OPEN c0 FETCH NEXT FROM c0 INTO @meterId, @meterName, @meterIndex, @date, @volume, @sp, @dp, @temp WHILE @@FETCH_STATUS = 0 BEGIN --PRINT ''Processing '' + @meterName + '' '' + CAST(@date AS nvarchar) + ''...'' IF EXISTS(SELECT ID FROM tblMeterHistory WHERE MeterID = @meterId AND RecordType = 2 AND [Date] = @date) BEGIN UPDATE tblMeterHistory SET Volume = @volume, Pressure = @sp, DifferentialPressure = @dp, Temperature = @temp WHERE MeterID = @meterId AND RecordType = 2 AND [Date] = @date END ELSE BEGIN INSERT INTO tblMeterHistory (MeterID, RecordType, ExternalSourceID, [Date], Volume, Pressure, DifferentialPressure, Temperature) VALUES (@meterId, 2, 4, @date, @volume, @sp, @dp, @temp) END FETCH NEXT FROM c0 INTO @meterId, @meterName, @meterIndex, @date, @volume, @sp, @dp, @temp END CLOSE c0 DEALLOCATE c0 RETURN 0 END ON_ERROR: RETURN -1 ' END GO /****** Object: StoredProcedure [dbo].[up_ImportETCSAMeterHistoryDaily] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportETCSAMeterHistoryDaily]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports ETCSA daily meter values into CDS -- ============================================= CREATE PROCEDURE [dbo].[up_ImportETCSAMeterHistoryDaily] AS BEGIN DECLARE @meterId UNIQUEIDENTIFIER DECLARE @meterName nvarchar(125) DECLARE @meterIndex tinyint DECLARE @date datetime DECLARE @volume REAL DECLARE @sp REAL DECLARE @dp REAL DECLARE @temp REAL DECLARE c0 INSENSITIVE CURSOR FOR -- Only INSERT or UPDATE Daily (RecordType=1) records that are for meters with: -- GasCo = ETCSA and GasHW = 0 SELECT t2.ID, t3.Name, t2.MeterIndex, t0.[Date], t0.Volume, t0.SP, t0.DP, t0.[Temp] FROM dbo.dts_etp_to_eos_daily t0 INNER JOIN tblMeters t2 ON t2.MeterKey = t0.MeterID INNER JOIN tblNodes t3 ON t3.ID = t2.NodeID WHERE t3.GasCo=''ETCSA'' AND t3.GasHW=0 ORDER BY t0.MeterID, t0.[Date] OPEN c0 FETCH NEXT FROM c0 INTO @meterId, @meterName, @meterIndex, @date, @volume, @sp, @dp, @temp WHILE @@FETCH_STATUS = 0 BEGIN --PRINT ''Processing '' + @meterName + '' '' + CAST(@date AS nvarchar) + ''...'' IF EXISTS(SELECT ID FROM tblMeterHistory WHERE MeterID = @meterId AND RecordType = 1 AND [Date] = @date) BEGIN UPDATE tblMeterHistory SET Volume = @volume, Pressure = @sp, DifferentialPressure = @dp, Temperature = @temp WHERE MeterID = @meterId AND RecordType = 1 AND [Date] = @date END ELSE BEGIN INSERT INTO tblMeterHistory (MeterID, RecordType, ExternalSourceID, [Date], Volume, Pressure, DifferentialPressure, Temperature) VALUES (@meterId, 1, 4, @date, @volume, @sp, @dp, @temp) END FETCH NEXT FROM c0 INTO @meterId, @meterName, @meterIndex, @date, @volume, @sp, @dp, @temp END CLOSE c0 DEALLOCATE c0 RETURN 0 END ON_ERROR: RETURN -1 ' END GO /****** Object: StoredProcedure [dbo].[up_ImportAnalogHistory] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_ImportAnalogHistory]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Imports Analog History into CDS -- ============================================= CREATE PROCEDURE [dbo].[up_ImportAnalogHistory] AS BEGIN DECLARE @analogID uniqueidentifier DECLARE @nodeID uniqueidentifier DECLARE @externalName nvarchar(125) DECLARE @analogIndex tinyint DECLARE @lastDate datetime DECLARE c0 CURSOR FOR SELECT t0.ID, t0.NodeID, t1.ExternalName, t0.AnalogIndex, ISNULL((SELECT MAX(Date) FROM tblAnalogHistory WHERE AnalogID=t0.ID),''1/1/1900'') AS LastDate FROM tblAnalogs t0 INNER JOIN tblNodes t1 ON t1.ID=t0.NodeID AND t1.ExternalSourceID=1 ORDER BY t1.ExternalName,t0.AnalogIndex OPEN c0 FETCH NEXT FROM c0 INTO @analogID, @nodeID, @externalName, @analogIndex, @lastDate WHILE @@FETCH_STATUS = 0 BEGIN PRINT @externalName INSERT INTO tblAnalogHistory SELECT DISTINCT t2.ID,1, t0.Date, t0.Value FROM dtsAnalogHistoryChalk t0 INNER JOIN tblNodes t1 ON t1.ExternalName=t0.NodeID AND t1.ExternalSourceID=1 INNER JOIN tblAnalogs t2 ON t2.NodeID=t1.ID AND t2.AnalogIndex=t0.DestinationType-200 WHERE t2.ID=@analogID AND t0.Date>@lastDate FETCH NEXT FROM c0 INTO @analogID, @nodeID, @externalName, @analogIndex, @lastDate END CLOSE c0 DEALLOCATE c0 END ' END GO /****** Object: StoredProcedure [dbo].[up_GetPreviousDayCalculatedVolumeByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetPreviousDayCalculatedVolumeByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets previous day calculated meter volumes by route -- ============================================= CREATE PROCEDURE [dbo].[up_GetPreviousDayCalculatedVolumeByRoute](@routeName nvarchar(50)) AS BEGIN SELECT t0.NodeID, t1.Name, ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=1) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND s0.RecordType=1 ORDER BY s0.Date DESC),0) - ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=2) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND s0.RecordType=1 ORDER BY s0.Date DESC),0) + ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=3) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND s0.RecordType=1 ORDER BY s0.Date DESC),0) AS PrevDayVolume FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE RouteID=(SELECT [ID] FROM tblRoutes WHERE Name=@routeName) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_GetPreviousDayCalculatedVolumeByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetPreviousDayCalculatedVolumeByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets previous day calculated meter volumes by name -- ============================================= CREATE PROCEDURE [dbo].[up_GetPreviousDayCalculatedVolumeByName](@name nvarchar(125)) AS BEGIN SELECT t0.NodeID, t1.Name, ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=1) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ORDER BY s0.Date DESC),0) - ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=2) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ORDER BY s0.Date DESC),0) + ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=3) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ORDER BY s0.Date DESC),0) AS PrevDayVolume FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE NodeID=(SELECT [ID] FROM tblNodes WHERE Name=@name) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_GetPreviousDayCalculatedVolumeByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetPreviousDayCalculatedVolumeByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Description: Gets previous day calculated meter volumes by external name -- ============================================= CREATE PROCEDURE [dbo].[up_GetPreviousDayCalculatedVolumeByExternalName](@name nvarchar(125)) AS BEGIN SELECT t0.NodeID, t1.Name, ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=1) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ORDER BY s0.Date DESC),0) - ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=2) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ORDER BY s0.Date DESC),0) + ISNULL((SELECT TOP 1 s0.Volume FROM tblMeterHistory s0 WHERE s0.MeterID=(SELECT s1.ID FROM tblMeters s1 WHERE s1.NodeID=t0.NodeID AND s1.MeterIndex=3) AND s0.Date<DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) ORDER BY s0.Date DESC),0) AS PrevDayVolume FROM tblRouteNodes t0 INNER JOIN tblNodes t1 ON t0.NodeID=t1.ID WHERE NodeID=(SELECT [ID] FROM tblNodes WHERE ExternalName=@name) ORDER BY t1.Name END ' END GO /****** Object: StoredProcedure [dbo].[up_GetMeterValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetMeterValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Gets meter values for specified node, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetMeterValuesByName]( @name nvarchar(125), @meterIndex tinyint, @startDate datetime, @endDate datetime ) AS BEGIN SELECT t0.* FROM tblMeterHistory t0 WHERE t0.[MeterID]=(SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID=(SELECT s1.ID FROM tblNodes s1 WHERE s1.Name=@name) AND s0.MeterIndex=@meterIndex) AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetMeterValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetMeterValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author: Jacob Sherman -- Description: Gets meter values for specified node, meter index and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetMeterValuesByExternalName]( @name nvarchar(125), @meterIndex tinyint, @startDate datetime, @endDate datetime ) AS BEGIN SELECT t0.* FROM tblMeterHistory t0 WHERE t0.[MeterID]=(SELECT s0.ID FROM tblMeters s0 WHERE s0.NodeID=(SELECT s1.ID FROM tblNodes s1 WHERE s1.ExternalName=@name) AND s0.MeterIndex=@meterIndex) AND t0.Date BETWEEN @startDate AND @endDate ORDER BY t0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_AddTankValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddTankValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Add tank Values -- ============================================= CREATE PROCEDURE [dbo].[up_AddTankValuesByName]( @name nvarchar(125), @tankIndex int, @date datetime, @tankLevel float, @tankTemp float, @source smallint = 0 ) AS BEGIN INSERT INTO tblTankHistory (TankID,[Date],ExternalSourceID,TankLevel,Temperature) SELECT t0.[ID], @date, @source, @tankLevel, @tankTemp FROM tblTanks t0 WHERE t0.NodeID=(SELECT [ID] FROM tblNodes s0 WHERE s0.Name=@name) AND t0.TankIndex=@tankIndex END ' END GO /****** Object: StoredProcedure [dbo].[up_AddTankValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_AddTankValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman (Theta) -- Description: Add tank Values -- ============================================= CREATE PROCEDURE [dbo].[up_AddTankValuesByExternalName]( @name nvarchar(125), @tankIndex int, @date datetime, @tankLevel float, @tankTemp float, @source smallint = 0 ) AS BEGIN INSERT INTO tblTankHistory (TankID,[Date],ExternalSourceID,TankLevel,Temperature) SELECT t0.[ID], @date, @source, @tankLevel, @tankTemp FROM tblTanks t0 WHERE t0.NodeID=(SELECT [ID] FROM tblNodes s0 WHERE s0.ExternalName=@name) AND t0.TankIndex=@tankIndex END ' END GO /****** Object: StoredProcedure [dbo].[up_GetTankLevelsByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankLevelsByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets Tank Levels for specified route and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetTankLevelsByRoute]( @routeName nvarchar(50), @startDate datetime, @endDate datetime ) AS BEGIN SELECT v0.* FROM vTankLevelHistory v0 WHERE v0.[NodeID] IN (SELECT NodeID FROM tblRouteNodes WHERE RouteID=(SELECT ID FROM tblRoutes WHERE Name=@routeName)) AND v0.Date BETWEEN @startDate AND @endDate ORDER BY v0.Name, v0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetTankLevelsByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankLevelsByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets Tank Levels for specified node and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetTankLevelsByName]( @name nvarchar(125), @startDate datetime, @endDate datetime ) AS BEGIN SELECT v0.* FROM vTankLevelHistory v0 WHERE v0.[NodeID] IN (SELECT s0.ID FROM tblNodes s0 WHERE s0.Name=@name) AND v0.Date BETWEEN @startDate AND @endDate ORDER BY v0.Name, v0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetTankLevelsByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetTankLevelsByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets Tank Levels for specified route and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetTankLevelsByExternalName]( @name nvarchar(125), @startDate datetime, @endDate datetime ) AS BEGIN SELECT v0.* FROM vTankLevelHistory v0 WHERE v0.[NodeID] IN (SELECT s0.ID FROM tblNodes s0 WHERE s0.ExternalName=@name) AND v0.Date BETWEEN @startDate AND @endDate ORDER BY v0.Name, v0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetAnalogValuesByRoute] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogValuesByRoute]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets Analog values for specified route and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetAnalogValuesByRoute]( @routeName nvarchar(50), @startDate datetime, @endDate datetime ) AS BEGIN SELECT v0.* FROM vAnalogHistory v0 WHERE v0.[ID] IN (SELECT NodeID FROM tblRouteNodes WHERE RouteID=(SELECT ID FROM tblRoutes WHERE Name=@routeName)) AND v0.Date BETWEEN @startDate AND @endDate ORDER BY v0.Name, v0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetAnalogValuesByName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogValuesByName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets Analog values for specified node and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetAnalogValuesByName]( @name nvarchar(125), @startDate datetime, @endDate datetime ) AS BEGIN SELECT v0.* FROM vAnalogHistory v0 WHERE v0.[ID] IN (SELECT s0.ID FROM tblNodes s0 WHERE s0.Name=@name) AND v0.Date BETWEEN @startDate AND @endDate ORDER BY v0.Name, v0.[Date] END ' END GO /****** Object: StoredProcedure [dbo].[up_GetAnalogValuesByExternalName] Script Date: 08/08/2010 10:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[up_GetAnalogValuesByExternalName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: Jacob Sherman -- Create date: 8/10/2009 -- Description: Gets Analog values for specified route and date range -- ============================================= CREATE PROCEDURE [dbo].[up_GetAnalogValuesByExternalName]( @name nvarchar(125), @startDate datetime, @endDate datetime ) AS BEGIN SELECT v0.* FROM vAnalogHistory v0 WHERE v0.[ID] IN (SELECT s0.ID FROM tblNodes s0 WHERE s0.ExternalName=@name) AND v0.Date BETWEEN @startDate AND @endDate ORDER BY v0.Name, v0.[Date] END ' END GO /****** Object: ForeignKey [FK_tblAlarmHistory_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarmHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] WITH NOCHECK ADD CONSTRAINT [FK_tblAlarmHistory_tblExternalSources] FOREIGN KEY([ExternalSourceID]) REFERENCES [dbo].[tblExternalSources] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarmHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] CHECK CONSTRAINT [FK_tblAlarmHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblAlarms_tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarms_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] WITH CHECK ADD CONSTRAINT [FK_tblAlarms_tblNodes] FOREIGN KEY([NodeID]) REFERENCES [dbo].[tblNodes] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAlarms_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAlarmHistory]')) ALTER TABLE [dbo].[tblAlarmHistory] CHECK CONSTRAINT [FK_tblAlarms_tblNodes] GO /****** Object: ForeignKey [FK_tblAnalogHistory_tblAnalogs] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblAnalogs]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] WITH CHECK ADD CONSTRAINT [FK_tblAnalogHistory_tblAnalogs] FOREIGN KEY([AnalogID]) REFERENCES [dbo].[tblAnalogs] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblAnalogs]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] CHECK CONSTRAINT [FK_tblAnalogHistory_tblAnalogs] GO /****** Object: ForeignKey [FK_tblAnalogHistory_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] WITH CHECK ADD CONSTRAINT [FK_tblAnalogHistory_tblExternalSources] FOREIGN KEY([ExternalSourceID]) REFERENCES [dbo].[tblExternalSources] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogHistory]')) ALTER TABLE [dbo].[tblAnalogHistory] CHECK CONSTRAINT [FK_tblAnalogHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblAnalogs_tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogs_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogs]')) ALTER TABLE [dbo].[tblAnalogs] WITH CHECK ADD CONSTRAINT [FK_tblAnalogs_tblNodes] FOREIGN KEY([NodeID]) REFERENCES [dbo].[tblNodes] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblAnalogs_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblAnalogs]')) ALTER TABLE [dbo].[tblAnalogs] CHECK CONSTRAINT [FK_tblAnalogs_tblNodes] GO /****** Object: ForeignKey [FK_tblMeterHistory_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] WITH CHECK ADD CONSTRAINT [FK_tblMeterHistory_tblExternalSources] FOREIGN KEY([ExternalSourceID]) REFERENCES [dbo].[tblExternalSources] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] CHECK CONSTRAINT [FK_tblMeterHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblMeterHistory_tblMeters] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblMeters]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] WITH CHECK ADD CONSTRAINT [FK_tblMeterHistory_tblMeters] FOREIGN KEY([MeterID]) REFERENCES [dbo].[tblMeters] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeterHistory_tblMeters]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeterHistory]')) ALTER TABLE [dbo].[tblMeterHistory] CHECK CONSTRAINT [FK_tblMeterHistory_tblMeters] GO /****** Object: ForeignKey [FK_tblMeter_tblNode] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeter_tblNode]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeters]')) ALTER TABLE [dbo].[tblMeters] WITH CHECK ADD CONSTRAINT [FK_tblMeter_tblNode] FOREIGN KEY([NodeID]) REFERENCES [dbo].[tblNodes] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblMeter_tblNode]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblMeters]')) ALTER TABLE [dbo].[tblMeters] CHECK CONSTRAINT [FK_tblMeter_tblNode] GO /****** Object: ForeignKey [FK_tblNodes_tblDeviceTypes] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblDeviceTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] WITH NOCHECK ADD CONSTRAINT [FK_tblNodes_tblDeviceTypes] FOREIGN KEY([DeviceTypeID]) REFERENCES [dbo].[tblDeviceTypes] ([ID]) ON UPDATE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblDeviceTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] CHECK CONSTRAINT [FK_tblNodes_tblDeviceTypes] GO /****** Object: ForeignKey [FK_tblNodes_tblExternalSources] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] WITH CHECK ADD CONSTRAINT [FK_tblNodes_tblExternalSources] FOREIGN KEY([ExternalSourceID]) REFERENCES [dbo].[tblExternalSources] ([ID]) ON UPDATE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblNodes_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblNodes]')) ALTER TABLE [dbo].[tblNodes] CHECK CONSTRAINT [FK_tblNodes_tblExternalSources] GO /****** Object: ForeignKey [FK_tblRouteNodes_tblNodes] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] WITH CHECK ADD CONSTRAINT [FK_tblRouteNodes_tblNodes] FOREIGN KEY([NodeID]) REFERENCES [dbo].[tblNodes] ([ID]) ON UPDATE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] CHECK CONSTRAINT [FK_tblRouteNodes_tblNodes] GO /****** Object: ForeignKey [FK_tblRouteNodes_tblRoutes] Script Date: 08/08/2010 10:56:10 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblRoutes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] WITH CHECK ADD CONSTRAINT [FK_tblRouteNodes_tblRoutes] FOREIGN KEY([RouteID]) REFERENCES [dbo].[tblRoutes] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblRouteNodes_tblRoutes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblRouteNodes]')) ALTER TABLE [dbo].[tblRouteNodes] CHECK CONSTRAINT [FK_tblRouteNodes_tblRoutes] GO /****** Object: ForeignKey [FK_tblStatus_tblNodes] Script Date: 08/08/2010 10:56:11 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblStatus_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblStatus]')) ALTER TABLE [dbo].[tblStatus] WITH CHECK ADD CONSTRAINT [FK_tblStatus_tblNodes] FOREIGN KEY([NodeID]) REFERENCES [dbo].[tblNodes] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblStatus_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblStatus]')) ALTER TABLE [dbo].[tblStatus] CHECK CONSTRAINT [FK_tblStatus_tblNodes] GO /****** Object: ForeignKey [FK_tblTankHistory_tblExternalSources] Script Date: 08/08/2010 10:56:11 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] WITH CHECK ADD CONSTRAINT [FK_tblTankHistory_tblExternalSources] FOREIGN KEY([ExternalSourceID]) REFERENCES [dbo].[tblExternalSources] ([ID]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblExternalSources]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] CHECK CONSTRAINT [FK_tblTankHistory_tblExternalSources] GO /****** Object: ForeignKey [FK_tblTankHistory_tblTanks] Script Date: 08/08/2010 10:56:11 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblTanks]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] WITH CHECK ADD CONSTRAINT [FK_tblTankHistory_tblTanks] FOREIGN KEY([TankID]) REFERENCES [dbo].[tblTanks] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTankHistory_tblTanks]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTankHistory]')) ALTER TABLE [dbo].[tblTankHistory] CHECK CONSTRAINT [FK_tblTankHistory_tblTanks] GO /****** Object: ForeignKey [FK_tblTanks_tblNodes] Script Date: 08/08/2010 10:56:11 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTanks_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTanks]')) ALTER TABLE [dbo].[tblTanks] WITH CHECK ADD CONSTRAINT [FK_tblTanks_tblNodes] FOREIGN KEY([NodeID]) REFERENCES [dbo].[tblNodes] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblTanks_tblNodes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblTanks]')) ALTER TABLE [dbo].[tblTanks] CHECK CONSTRAINT [FK_tblTanks_tblNodes] GO