Click here to Skip to main content
13,596,541 members
Click here to Skip to main content

Stats

55K views
1.6K downloads
67 bookmarked
Posted 16 Apr 2012
Licenced CPOL

Populating a business logical layer from Stored Procedures

, 22 Sep 2014
A brief look at a technique mapping tables to a basic business layer.
doPersistence
doPersistence.csproj.user
doPersistence.csproj.vspscc
obj
Debug
Release
Properties
doLogicCode
bin
Debug
doLogicCode.csproj.vspscc
obj
x86
Debug
doLogicCode.frmCodeGenerator.resources
doLogicCode.Properties.Resources.resources
Properties
Test
doLogic
bin
Debug
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
Release
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Properties
doLogicCode
bin
Debug
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doPersistence.dll
doPersistence.pdb
obj
x86
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.exe
doLogicCode.frmCodeGenerator.resources
doLogicCode.pdb
doLogicCode.Properties.Resources.resources
GenerateResource.read.1.tlog
GenerateResource.write.1.tlog
ResolveAssemblyReference.cache
TempPE
Properties.Resources.Designer.cs.dll
Properties
doPersistence
bin
Debug
doPersistence.dll
doPersistence.pdb
Release
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Properties
Test.suo
TestSite
Account
App_Data
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
Release
CSAutoParameterize
original
Account
transformed
Account
Database
DesignTimeResolveAssemblyReferencesInput.cache
Package
PackageTmp
Account
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Scripts
Site.Master
Styles
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
TransformWebConfig
original
transformed
Account
Properties
Scripts
Site.Master
Styles
TestSite.csproj.user
doLogic.dll
doPersistence.dll
doPersistence.dll
doLogic.dll
doPersistence.dll
TestSite.dll
Site.Master
TestSite.csproj.user
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
Test.suo
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
Site.Master
TestSite.csproj.user
PersistenceTest_orig
Account
App_Code
App_Data
Bin
Persistence.pdb
Scripts
Styles
Persistence
bin
Debug
doPersistence.pdb
Persistence.pdb
Release
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.csprojResolveAssemblyReference.cache
doPersistence.pdb
Persistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.pdb
TempPE
Persistence.csproj.user
Properties
PersistenceTest_orig.v11.suo
CodeGenerator
bin
Debug
doLogicCode.pdb
doPersistence.pdb
Persistence.pdb
CodeGenerator.csproj.vspscc
obj
x86
Debug
CodeGenerator.csproj.GenerateResource.Cache
CodeGenerator.csprojResolveAssemblyReference.cache
CodeGenerator.frmCodeGenerator.resources
CodeGenerator.Properties.Resources.resources
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.csproj.GenerateResource.Cache
doLogicCode.csprojResolveAssemblyReference.cache
doLogicCode.pdb
TempPE
Properties
AjaxControlToolkit.dll
Persistence.dll
Persistence.pdb
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.csprojResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
Persistence.csproj.user
PersistenceTest_orig.v11.suo
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
CodeGenerator.csproj.vspscc
CodeGenerator.csproj.GenerateResource.Cache
CodeGenerator.csprojResolveAssemblyReference.cache
CodeGenerator.frmCodeGenerator.resources
CodeGenerator.Properties.Resources.resources
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.csproj.GenerateResource.Cache
doLogicCode.csprojResolveAssemblyReference.cache
doLogicCode.exe
doLogicCode.pdb
Properties.Resources.Designer.cs.dll
��USE [master]

GO



/****** Object:  Database [PersistenceTest]    Script Date: 09/18/2012 10:15:00 ******/

CREATE DATABASE [PersistenceTest] ON  PRIMARY 

( NAME = N'PersistenceTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\PersistenceTest.mdf' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'PersistenceTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\PersistenceTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [PersistenceTest] SET COMPATIBILITY_LEVEL = 100

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [PersistenceTest].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [PersistenceTest] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [PersistenceTest] SET ANSI_NULLS OFF

GO

ALTER DATABASE [PersistenceTest] SET ANSI_PADDING OFF

GO

ALTER DATABASE [PersistenceTest] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [PersistenceTest] SET ARITHABORT OFF

GO

ALTER DATABASE [PersistenceTest] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [PersistenceTest] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [PersistenceTest] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [PersistenceTest] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [PersistenceTest] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [PersistenceTest] SET CURSOR_DEFAULT  GLOBAL

GO

ALTER DATABASE [PersistenceTest] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [PersistenceTest] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [PersistenceTest] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [PersistenceTest] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [PersistenceTest] SET  DISABLE_BROKER

GO

ALTER DATABASE [PersistenceTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [PersistenceTest] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [PersistenceTest] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [PersistenceTest] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [PersistenceTest] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [PersistenceTest] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [PersistenceTest] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [PersistenceTest] SET  READ_WRITE

GO

ALTER DATABASE [PersistenceTest] SET RECOVERY FULL

GO

ALTER DATABASE [PersistenceTest] SET  MULTI_USER

GO

ALTER DATABASE [PersistenceTest] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [PersistenceTest] SET DB_CHAINING OFF

GO

EXEC sys.sp_db_vardecimal_storage_format N'PersistenceTest', N'ON'

GO

USE [PersistenceTest]

GO

/****** Object:  StoredProcedure [dbo].[sys_ParametersForSP]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[sys_ParametersForSP]

	@SPName varchar(100)

with execute as owner

as



Select

	SPName = o.Name,

	Name = SUBSTRING(c.Name, 2, LEN(c.Name)),

	Type = t.Name,

	c.IsOutParam,

	c.Length,

	c.xprec,

	c.IsNullable,

	AutoIncrement = case when t.name = 'timestamp' then 1

					when c.status = 128 then 1 else 0 end



From

	SysColumns c

	join SysObjects o on o.id = c.id and o.xtype = 'P'

	join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

Where

   o.Name = @SPName

   --and IsOutParam = 1

Order By

	c.ColOrder

GO

/****** Object:  StoredProcedure [dbo].[sys_ExtendedProperty]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec sys_ExtendedProperty 'AllowsDirectSql'

*/





CREATE procedure [dbo].[sys_ExtendedProperty]

	@PropertyName varchar(100)

with execute as owner

as



	select value from sys.extended_properties where name = @PropertyName

GO

/****** Object:  Table [dbo].[secUsrGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[secUsrGroup](

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

	[usrID] [int] NOT NULL,

	[groupID] [int] NOT NULL,

 CONSTRAINT [PK_secUsrGroup] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[secUsr]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[secUsr](

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

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

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

	[Login] [varchar](25) NOT NULL,

	[Password] [varchar](100) NOT NULL,

	[Email] [varchar](150) NULL,

	[Phone] [varchar](25) NULL,

	[Internal] [bit] NULL,

	[Active] [bit] NULL,

	[NewUser] [bit] NULL,

 CONSTRAINT [PK_secUsr] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[secRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[secRole](

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

	[Role] [varchar](1000) NOT NULL,

	[Internal] [bit] NULL,

 CONSTRAINT [PK_secRole] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[secGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[secGroup](

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

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

	[internal] [bit] NULL,

	[groupshortname] [varchar](50) NULL,

 CONSTRAINT [PK_secGroup] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  UserDefinedFunction [dbo].[fn_SqlToCSharpTypeName]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create function [dbo].[fn_SqlToCSharpTypeName] (

	@SqlType varchar(50)

) returns varchar(50) as

begin



	return

	case @SqlType

	when 'bigint' then 'Int64'

	when 'binary' then 'Byte[]'

	when 'bit' then 'bool'

	when 'char' then 'string'

	when 'date' then 'DateTime'

	when 'datetime' then 'DateTime'

	when 'datetime2' then 'DateTime'

	when 'datetimeoffset' then 'DateTimeOffset'

	when 'decimal' then 'Decimal'

	when 'float' then 'double'

	when 'geography' then 'string'

	when 'geometry' then 'string'

	when 'hierarchyid' then 'int'

	when 'image' then 'Byte[]'

	when 'int' then 'int'

	when 'money' then 'Decimal'

	when 'nchar' then 'string'

	when 'ntext' then 'string'

	when 'numeric' then 'Decimal'

	when 'nvarchar' then 'string'

	when 'real' then 'double'

	when 'smalldatetime' then 'DateTime'

	when 'smallint' then 'Int16'

	when 'smallmoney' then 'Decimal'

	when 'sql_variant' then 'object'

	when 'sysname' then 'string'

	when 'text' then 'string'

	when 'time' then 'DateTime'

	when 'timestamp' then 'Byte[]'

	when 'tinyint' then 'Byte'

	when 'uniqueidentifier' then 'Guid'

	when 'varbinary' then 'Byte[]'

	when 'varchar' then 'string'

	when 'xml' then 'string'

	end

end

GO

/****** Object:  Table [dbo].[Client]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Client](

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

	[ClientName] [varchar](50) NULL,

	[ContactFirstName] [varchar](30) NULL,

	[ContactLastName] [varchar](50) NULL,

	[BillingAddress] [varchar](50) NULL,

	[City] [varchar](50) NULL,

	[State] [char](2) NULL,

	[Zip] [char](5) NULL,

	[ContactTitle] [varchar](50) NULL,

	[PhoneNumber] [varchar](10) NULL,

	[Extension] [varchar](5) NULL,

	[FaxNumber] [varchar](10) NULL,

	[EmailAddress] [varchar](50) NULL,

	[Notes] [varchar](100) NULL,

	[Image] [varchar](150) NULL,

	[BannerImage] [varchar](150) NULL,

	[MedCCImage] [varchar](150) NULL,

	[SmallCCImage] [varchar](150) NULL,

	[Theme] [varchar](50) NULL,

	[Active] [bit] NULL,

	[URL] [varchar](50) NULL,

	[ClientFulfillmentDocFolder] [varchar](50) NULL,

	[DivID] [int] NULL,

	[WPPayType] [bit] NULL,

	[WPClass] [bit] NULL,

	[WPCategory] [bit] NULL,

	[WPUCode] [bit] NULL,

	[ClientShortName] [varchar](10) NULL,

	[CallCenterPhone] [varchar](11) NULL,

	[CallCenterEmail] [varchar](50) NULL,

 CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED 

(

	[ClientCode] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  StoredProcedure [dbo].[delete_Client]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*



select top 100 * from Client

exec hm_delete_Client

*/



create procedure [dbo].[delete_Client]

	@ClientCode int

AS



UPDATE dbo.Client

SET

	Active = 0

where

	ClientCode = @ClientCode

GO

/****** Object:  StoredProcedure [dbo].[delete_secRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_secRole]

	@ID int

as

	delete from

		secRole

	where

		ID = @ID

GO

/****** Object:  StoredProcedure [dbo].[delete_secGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_secGroup]

	@ID int

as

	delete from

		secGroup

	where

		ID = @ID

GO

/****** Object:  StoredProcedure [dbo].[delete_secUsrGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_secUsrGroup]

	@usrID int,

	@groupID int

as

	delete from

		secUsrGroup

	where

		usrID = @usrID

		and groupID = @groupID

GO

/****** Object:  StoredProcedure [dbo].[list_Client]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[list_Client]

as

	select

		ClientCode,

		ClientName,

		ContactFirstName,

		ContactLastName,

		BillingAddress,

		City,

		State,

		Zip,

		ContactTitle,

		PhoneNumber,

		Extension,

		FaxNumber,

		EmailAddress,

		Notes,

		Image,

		BannerImage,

		MedCCImage,

		SmallCCImage,

		Theme,

		Active,

		URL,

		ClientFulfillmentDocFolder,

		DivID,

		WPPayType,

		WPClass,

		WPCategory,

		WPUCode,

		ClientShortName,

		CallCenterPhone,

		CallCenterEmail

	from

		Client

	ORDER BY

		ClientName

GO

/****** Object:  Table [dbo].[Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Divisions](

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

	[ClientCode] [int] NOT NULL,

	[DivName] [varchar](200) NULL,

 CONSTRAINT [PK_Divisions] PRIMARY KEY CLUSTERED 

(

	[DivID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  StoredProcedure [dbo].[list_secRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[list_secRole]

as

	select

		ID,

		Name = Role,

		IsInternal = Internal

	from

		secRole

GO

/****** Object:  StoredProcedure [dbo].[list_secGroupForUser]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_secGroupForUser 63

*/





CREATE procedure [dbo].[list_secGroupForUser]

	@UserID int

as

	select

		g.ID,

		Name = g.GroupName,

		IsInternal = g.Internal,

		ShortName = g.GroupShortName

	from

		secGroup g

		join secUsrGroup ug on ug.groupID = g.ID

	where

		ug.usrID = @UserID

	order by

		GroupName

GO

/****** Object:  StoredProcedure [dbo].[list_secGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[list_secGroup]

as

	select

		ID,

		Name = GroupName,

		IsInternal = Internal,

		ShortName = GroupShortName

	from

		secGroup

	order by

		GroupName

GO

/****** Object:  StoredProcedure [dbo].[load_Client]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[load_Client]

	@ClientCode int

as

	select

		ClientCode,

		ClientName,

		ContactFirstName,

		ContactLastName,

		BillingAddress,

		City,

		State,

		Zip,

		ContactTitle,

		PhoneNumber,

		Extension,

		FaxNumber,

		EmailAddress,

		Notes,

		Image,

		BannerImage,

		MedCCImage,

		SmallCCImage,

		Theme,

		Active,

		URL,

		ClientFulfillmentDocFolder,

		DivID,

		WPPayType,

		WPClass,

		WPCategory,

		WPUCode,

		ClientShortName,

		CallCenterPhone,

		CallCenterEmail

	from

		Client

	where

		ClientCode = @ClientCode

GO

/****** Object:  StoredProcedure [dbo].[list_secUsr]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[list_secUsr]

as

	select

		ID,

		FirstName,

		LastName,

		Login,

		Password,

		Email,

		Phone,

		Internal,

		Active,

		NewUser

	from

		secUsr

	order by

		LastName, FirstName

GO

/****** Object:  Table [dbo].[Managers]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Managers](

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

	[ClientCode] [int] NOT NULL,

	[FN] [varchar](50) NULL,

	[LN] [varchar](50) NULL,

	[MN] [varchar](50) NULL,

	[Title] [varchar](50) NULL,

	[Phone] [varchar](10) NULL,

	[Extension] [varchar](5) NULL,

	[Email] [varchar](50) NULL,

 CONSTRAINT [PK_Managers] PRIMARY KEY CLUSTERED 

(

	[ManCode] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  StoredProcedure [dbo].[sys_StructureForTable]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[sys_StructureForTable]

	@TableName varchar(100)

with execute as owner

as



	Select

		TableName = o.Name,

		c.Name,

		Type = t.Name,

		NETType = dbo.fn_SqlToCSharpTypeName(t.Name),

		c.Length,

		c.xprec,

		c.IsNullable,

		AutoIncrement = case when t.name = 'timestamp' then 1

						when c.status = 128 then 1 else 0 end

	From

		SysColumns c

		join SysObjects o on o.id = c.id and o.xtype = 'U'

		join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

    Where

       o.Name = @TableName

       --and IsOutParam = 1

    Order By c.ColOrder

GO

/****** Object:  StoredProcedure [dbo].[sys_CodeForTable]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[sys_CodeForTable]

	@TableName varchar(100)

with execute as owner

as



	select

		CSharp = 'public ' + dbo.fn_SqlToCSharpTypeName(t.Name) + ' ' + c.Name + ' { get; set; }',

		SqlParms = '@' + c.Name + ' '

			+ case t.Name when 'varchar' then 'varchar(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				when 'nvarchar' then 'nvarchar(' + convert(varchar(5), c.Length) + ')'

				when 'char' then 'char(' + convert(varchar(5), c.Length) + ')'

				when 'nchar' then 'nchar(' + convert(varchar(5), c.Length) + ')'

				when 'datetime2' then 'datetime2(7)'

				when 'datetimeoffset' then 'datetimeoffset(7)'

				when 'varbinary' then 'varbinary(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				else t.Name end

			+ ',',

		FieldList = c.Name + ',',

		ParmList = '@' + c.Name + ',',

		UpdateList = c.Name + ' = @' + c.Name + ',',

		o.Name As TableName,

		c.Name as ColumnName,

		t.Name As Type,

		c.Length,

		c.xprec,

		c.IsNullable,

		AutoIncrement = case when t.name = 'timestamp' then 1

						when c.status = 128 then 1 else 0 end,

		*

	from

		SysColumns c

		join SysObjects o on o.id = c.id and o.xtype = 'U'

		join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

    where

       o.Name = @TableName

       --and IsOutParam = 1

    order by c.ColOrder

GO

/****** Object:  StoredProcedure [dbo].[sys_CodeForSP]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[sys_CodeForSP]

	@SPName varchar(100)

with execute as owner

as



	select

		CSharp = 'public ' + dbo.fn_SqlToCSharpTypeName(t.Name) + ' ' + substring(c.Name, 2, len(c.Name)) + ' { get; set; }',

		ParmList = c.Name + ' '

			+ case t.Name when 'varchar' then 'varchar(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				when 'nvarchar' then 'nvarchar(' + convert(varchar(5), c.Length) + ')'

				when 'char' then 'char(' + convert(varchar(5), c.Length) + ')'

				when 'nchar' then 'nchar(' + convert(varchar(5), c.Length) + ')'

				when 'datetime2' then 'datetime2(7)'

				when 'datetimeoffset' then 'datetimeoffset(7)'

				when 'varbinary' then 'varbinary(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				else t.Name end

			+ ',',

		FieldList = substring(c.Name, 2, len(c.Name)) + ',',

		ValueList = c.Name + ',',

		UpdateList = substring(c.Name, 2, len(c.Name)) + ' = ' + c.Name + ',',

		o.Name As TableName,

		c.Name as ColumnName,

		t.Name As Type,

		c.Length,

		c.xprec,

		c.IsNullable,

		AutoIncrement = case when t.name = 'timestamp' then 1

						when c.status = 128 then 1 else 0 end

	from

		SysColumns c

		join SysObjects o on o.id = c.id and o.xtype = 'P'

		join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

    where

       o.Name = @SPName

       --and IsOutParam = 1

    order by

		c.ColOrder

GO

/****** Object:  StoredProcedure [dbo].[store_Client]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

select top 100 * from Client

exec sys_CodeForTable 'Client'

*/



CREATE procedure [dbo].[store_Client]

	@ClientCode INT output,

	@ClientName varchar(50),

	@ContactFirstName varchar(30),

	@ContactLastName varchar(50),

	@BillingAddress varchar(50),

	@City varchar(50),

	@State char(2),

	@Zip char(5),

	@ContactTitle varchar(50),

	@PhoneNumber varchar(10),

	@Extension varchar(5),

	@FaxNumber varchar(10),

	@EmailAddress varchar(50),

	@Notes varchar(100),

	@Image varchar(150),

	@BannerImage varchar(150),

	@MedCCImage varchar(150),

	@SmallCCImage varchar(150),

	@Theme varchar(50),

	@Active bit,

	@URL varchar(50),

	@ClientFulfillmentDocFolder varchar(50),

	@DivID int,

	@WPPayType bit,

	@WPClass bit,

	@WPCategory bit,

	@WPUCode bit,

	@ClientShortName varchar(10),

	@CallCenterPhone VARCHAR(11),

	@CallCenterEmail VARCHAR(50)

as

	if exists(select 1 from Client where ClientCode = @ClientCode) begin

		update Client set

			ClientName = @ClientName,

			ContactFirstName = @ContactFirstName,

			ContactLastName = @ContactLastName,

			BillingAddress = @BillingAddress,

			City = @City,

			State = @State,

			Zip = @Zip,

			ContactTitle = @ContactTitle,

			PhoneNumber = @PhoneNumber,

			Extension = @Extension,

			FaxNumber = @FaxNumber,

			EmailAddress = @EmailAddress,

			Notes = @Notes,

			Image = @Image,

			BannerImage = @BannerImage,

			MedCCImage = @MedCCImage,

			SmallCCImage = @SmallCCImage,

			Theme = @Theme,

			Active = @Active,

			URL = @URL,

			ClientFulfillmentDocFolder = @ClientFulfillmentDocFolder,

			DivID = @DivID,

			WPPayType = @WPPayType,

			WPClass = @WPClass,

			WPCategory = @WPCategory,

			WPUCode = @WPUCode,

			ClientShortName = @ClientShortName,

			CallCenterPhone = @CallCenterPhone,

			CallCenterEmail = @CallCenterEmail

		where

			ClientCode = @ClientCode

	end else begin

		insert into Client (

			ClientName,

			ContactFirstName,

			ContactLastName,

			BillingAddress,

			City,

			State,

			Zip,

			ContactTitle,

			PhoneNumber,

			Extension,

			FaxNumber,

			EmailAddress,

			Notes,

			Image,

			BannerImage,

			MedCCImage,

			SmallCCImage,

			Theme,

			Active,

			URL,

			ClientFulfillmentDocFolder,

			DivID,

			WPPayType,

			WPClass,

			WPCategory,

			WPUCode,

			ClientShortName,

			CallCenterPhone,

			CallCenterEmail

		) values (

			@ClientName,

			@ContactFirstName,

			@ContactLastName,

			@BillingAddress,

			@City,

			@State,

			@Zip,

			@ContactTitle,

			@PhoneNumber,

			@Extension,

			@FaxNumber,

			@EmailAddress,

			@Notes,

			@Image,

			@BannerImage,

			@MedCCImage,

			@SmallCCImage,

			@Theme,

			@Active,

			@URL,

			@ClientFulfillmentDocFolder,

			@DivID,

			@WPPayType,

			@WPClass,

			@WPCategory,

			@WPUCode,

			@ClientShortName,

			@CallCenterPhone,

			@CallCenterEmail

		)



		set @ClientCode = scope_identity()

	end

	

return @ClientCode

GO

/****** Object:  Table [dbo].[secUsrRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[secUsrRole](

	[usrID] [int] NOT NULL,

	[roleID] [int] NOT NULL,

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

 CONSTRAINT [PK_secUsrRole] PRIMARY KEY CLUSTERED 

(

	[usrID] ASC,

	[roleID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  StoredProcedure [dbo].[store_secUsrGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[store_secUsrGroup]

	@usrID int,

	@groupID int

as

	if not exists(select 1 from secUsrGroup where usrID = @usrID and groupID = @groupID) begin

		insert into secUsrGroup (

			usrID,

			groupID

		) values (

			@usrID,

			@groupID

		)

	end

GO

/****** Object:  StoredProcedure [dbo].[store_secUsr]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_secUsr]

	@ID int output,

	@FirstName varchar(50),

	@LastName varchar(50),

	@Login varchar(25),

	@Password varchar(100),

	@Email varchar(150),

	@Phone varchar(25),

	@Internal bit,

	@Active bit,

	@NewUser bit

as

	if exists(select 1 from secUsr where ID = @ID) begin

		update secUsr set

			FirstName = @FirstName,

			LastName = @LastName,

			Login = @Login,

			Password = @Password,

			Email = @Email,

			Phone = @Phone,

			Internal = @Internal,

			Active = @Active,

			NewUser = @NewUser

		where

			ID = @ID

	end else begin

		insert into secUsr (

			FirstName,

			LastName,

			Login,

			Password,

			Email,

			Phone,

			Internal,

			Active,

			NewUser

		) values (

			@FirstName,

			@LastName,

			@Login,

			@Password,

			@Email,

			@Phone,

			@Internal,

			@Active,

			@NewUser

		)



		set @ID = scope_identity()

	end



return @ID

GO

/****** Object:  StoredProcedure [dbo].[store_secRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_secRole]

	@ID int output,

	@Name varchar(1000),

	@IsInternal bit

as

	if exists(select 1 from secRole where ID = @ID) begin

		update secRole set

			Role = @Name,

			Internal = @IsInternal

		where

			ID = @ID

	end else begin

		insert into secRole (

			Role,

			Internal

		) values (

			@Name,

			@IsInternal

		)



		set @ID = scope_identity()

	end



return @ID

GO

/****** Object:  StoredProcedure [dbo].[store_secGroup]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_secGroup]

	@ID int output,

	@Name varchar(50),

	@IsInternal bit,

	@ShortName varchar(50)

as

	if exists(select 1 from secGroup where ID = @ID) begin

		update secGroup set

			GroupName = @Name,

			Internal = @IsInternal,

			GroupShortName = @ShortName

		where

			ID = @ID

	end else begin

		insert into secGroup (

			GroupName,

			Internal,

			GroupShortName

		) values (

			@Name,

			@IsInternal,

			@ShortName

		)



		set @ID = scope_identity()

	end



return @ID

GO

/****** Object:  StoredProcedure [dbo].[store_Managers]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_Managers]

	@ManagerID int output,

	@ClientCode int,

	@FirstName varchar(50),

	@LastName varchar(50),

	@MiddleName varchar(50),

	@Title varchar(50),

	@Phone varchar(10),

	@Extension varchar(5),

	@Email varchar(50)

as

	if exists(select 1 from Managers where ManCode = @ManagerID) begin

		update Managers set

			ClientCode = @ClientCode,

			FN = @FirstName,

			LN = @LastName,

			MN = @MiddleName,

			Title = @Title,

			Phone = @Phone,

			Extension = @Extension,

			Email = @Email

		where

			ManCode = @ManagerID

	end else begin

		insert into Managers (

			ClientCode,

			FN,

			LN,

			MN,

			Title,

			Phone,

			Extension,

			Email

		) values (

			@ClientCode,

			@FirstName,

			@LastName,

			@MiddleName,

			@Title,

			@Phone,

			@Extension,

			@Email

		)



		set @ManagerID = scope_identity()

	end



--if isnull(@PrimaryLocationID, 0) = 0

--	update Manager_Locations set IsPrimary = 0 where ManCode = @ManagerID

--else if exists(select 1 from Manager_Locations where ManCode = @ManagerID and LocID = @PrimaryLocationID)

--	update Manager_Locations set IsPrimary = 1 where ManCode = @ManagerID and LocID = @PrimaryLocationID

--else

--	insert into Manager_Locations (ManCode, LocID, IsPrimary) values (@ManagerID, @PrimaryLocationID, 1)



--update Manager_Locations set IsPrimary = 0 where ManCode = @ManagerID and LocID <> isnull(@PrimaryLocationID, 0)





return @ManagerID

GO

/****** Object:  StoredProcedure [dbo].[store_secUsrRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_secUsrRole]

	@usrID int,

	@roleID int

as

	if not exists(select 1 from secUsrRole where usrID = @usrID and roleID = @roleID) begin

		insert into secUsrRole (

			usrID,

			roleID

		) values (

			@usrID,

			@roleID

		)

	end

GO

/****** Object:  StoredProcedure [dbo].[store_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[store_Divisions]

	@DivID int output,

	@ClientCode int,

	@DivName varchar(200)

as

	if exists(select 1 from Divisions where DivID = @DivID) begin

		update Divisions set

			ClientCode = @ClientCode,

			DivName = @DivName

		where

			DivID = @DivID

	end else begin

		insert into Divisions (

			ClientCode,

			DivName

		) values (

			@ClientCode,

			@DivName

		)



		set @DivID = scope_identity()

	end



return @DivID

GO

/****** Object:  Table [dbo].[Manager_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Manager_Divisions](

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

	[ManCode] [int] NOT NULL,

	[DivID] [int] NOT NULL,

	[isPrimary] [bit] NULL,

 CONSTRAINT [PK_Manager_Divisions] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Locations](

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

	[LocationName] [varchar](200) NULL,

	[DivID] [int] NOT NULL,

	[ClientCode] [int] NOT NULL,

	[EDNH] [int] NULL,

	[CODNH] [int] NULL,

	[EDRE] [int] NULL,

	[CODRE] [int] NULL,

	[EDDC] [int] NULL,

	[CODDC] [int] NULL,

	[EDCCEL] [int] NULL,

	[CODCCEL] [int] NULL,

	[EDCCM] [int] NULL,

	[CODCCM] [int] NULL,

	[EDCCIN] [int] NULL,

	[CODCCIN] [int] NULL,

 CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED 

(

	[LocID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  StoredProcedure [dbo].[load_Managers]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_load_Managers 3

*/



CREATE procedure [dbo].[load_Managers]

	@ManagerID int

as

	select

		ManagerID = ManCode,

		ClientCode,

		FirstName = FN,

		LastName = LN,

		MiddleName = MN,

		Title,

		Phone,

		Extension,

		Email

		--PrimaryLocationID = (select top 1 LocID from Manager_Locations ml where ml.ManCode = m.ManCode and ml.IsPrimary = 1),

		--PrimaryDivisionID = (select top 1 DivID from Manager_Divisions md where md.ManCode = m.ManCode and md.IsPrimary = 1)

	from

		Managers m

	where

		ManCode = @ManagerID

GO

/****** Object:  StoredProcedure [dbo].[list_secRoleForUser]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[list_secRoleForUser]

	@UserID int

as

	select

		r.ID,

		Name = r.Role,

		IsInternal = r.Internal

	from

		secRole r

		join secUsrRole ur on ur.roleID = r.ID

	where

		ur.usrID = @UserID

GO

/****** Object:  StoredProcedure [dbo].[list_ManagersForClient]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Managers 3, 0, 48

select * from Managers where ClientCode = 3

select * from Managers m left join Manager_Locations ml on m.ManCode = ml.ManCode where ClientCode = 3

select * from Managers m left join Manager_Locations ml on m.ManCode = ml.ManCode where ClientCode = 3 and LocID = 3



select * from Manager_Locations where ManCode = 115

*/



CREATE procedure [dbo].[list_ManagersForClient]

	@ClientCode int

as



	select

		ManagerID = m.ManCode,

		ClientCode,

		FirstName = FN,

		LastName = LN,

		MiddleName = MN,

		Title,

		Phone,

		Extension,

		Email

	from

		Managers m

	where

		m.ClientCode = @ClientCode

	order by

		LastName,

		FirstName

GO

/****** Object:  StoredProcedure [dbo].[load_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[load_Divisions]

	@DivID int

as

	select

		DivID,

		ClientCode,

		DivName

	from

		Divisions

	where

		DivID = @DivID

GO

/****** Object:  StoredProcedure [dbo].[delete_secUsrRole]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[delete_secUsrRole]

	@usrID int,

	@roleID int

as

	delete from

		secUsrRole

	where

		usrID = @usrID

		and roleID = @roleID

GO

/****** Object:  StoredProcedure [dbo].[list_DivisionsForClient]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[list_DivisionsForClient]

	@ClientCode int

as

	select

		DivID,

		ClientCode,

		DivName

	from

		Divisions

	where

		ClientCode = @ClientCode

	order by

		DivName

GO

/****** Object:  StoredProcedure [dbo].[delete_secUsr]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[delete_secUsr]

	@ID int

as



	delete from secUsrRole

	where usrID = @ID



	delete from secUsrGroup

	where usrID = @ID



	delete from secEventDefault

	where usrID = @ID



	delete from secEventUsr

	where usrID = @ID

	

	delete from

		secUsr

	where

		ID = @ID

GO

/****** Object:  StoredProcedure [dbo].[delete_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_Divisions]

	@DivID int

as

	delete from

		Divisions

	where

		DivID = @DivID

GO

/****** Object:  StoredProcedure [dbo].[delete_Manager_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_Manager_Divisions]

	@ID int,

	@ManagerID int,

	@DivisionID int

as

	delete from Manager_Divisions

	where

		ID = @ID

	

	delete from Manager_Divisions

	where

		ManCode = @ManagerID

		and DivID = @DivisionID

GO

/****** Object:  StoredProcedure [dbo].[list_Manager_DivisionsForManager]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Manager_DivisionsForManager 3



select * from Manager_Divisions

*/



CREATE procedure [dbo].[list_Manager_DivisionsForManager]

	@ManagerID int

as



	select

		ID,

		ManagerID = ManCode,

		DivisionID = md.DivID,

		IsPrimary

	from

		Manager_Divisions md

		join Divisions d on d.DivID = md.DivID

	where

		ManCode = @ManagerID

	order by

		DivName

GO

/****** Object:  StoredProcedure [dbo].[list_Manager_DivisionsForDivision]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Manager_DivisionsForDivision 3



select * from Manager_Divisions

*/



CREATE procedure [dbo].[list_Manager_DivisionsForDivision]

	@DivisionID int

as



	select

		ID,

		ManagerID = md.ManCode,

		DivisionID = DivID,

		IsPrimary

	from

		Manager_Divisions md

		join Managers m on m.ManCode = md.ManCode

	where

		DivID = @DivisionID

	order by

		LN, FN

GO

/****** Object:  StoredProcedure [dbo].[list_LocationsForClientOrDivision]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_LocationsForClient 3

*/



CREATE procedure [dbo].[list_LocationsForClientOrDivision]

	@ClientCode INT,

	@DivID INT = 0

AS

	IF @DivID IS NULL SET @DivID = 0

	

	select

		LocID,

		LocationName,

		DivisionName = isnull(DivName, ''),

		l.DivID,

		l.ClientCode,

		EDNH,

		CODNH,

		EDRE,

		CODRE,

		EDDC,

		CODDC,

		EDCCEL,

		CODCCEL,

		EDCCM,

		CODCCM,

		EDCCIN,

		CODCCIN

	from

		Locations l

		left join Divisions d on l.DivID = d.DivID and l.ClientCode = d.ClientCode

	where

		l.ClientCode = @ClientCode

		AND (@DivID = 0 OR l.DivID = @DivID)

	order by

		LocationName

GO

/****** Object:  StoredProcedure [dbo].[list_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[list_Locations]

as

	select

		LocID,

		LocationName,

		DivisionName = isnull(DivName, ''),

		l.DivID,

		l.ClientCode,

		EDNH,

		CODNH,

		EDRE,

		CODRE,

		EDDC,

		CODDC,

		EDCCEL,

		CODCCEL,

		EDCCM,

		CODCCM,

		EDCCIN,

		CODCCIN

	from

		Locations l

		left join Divisions d on l.DivID = d.DivID and l.ClientCode = d.ClientCode

	order by

		LocationName

GO

/****** Object:  StoredProcedure [dbo].[load_Manager_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Manager_DivisionsForManager 3



select * from Manager_Divisions

*/



create procedure [dbo].[load_Manager_Divisions]

	@ID int,

	@ManagerID int,

	@DivisionID int

as



	select

		ID,

		ManagerID = ManCode,

		DivisionID = DivID,

		IsPrimary

	from

		Manager_Divisions

	where

		(ID = @ID)

		or (ManCode = @ManagerID and DivID = @DivisionID)

GO

/****** Object:  StoredProcedure [dbo].[load_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[load_Locations]

	@LocID int

as

	select

		LocID,

		LocationName,

		DivisionName = isnull(DivName, ''),

		l.DivID,

		l.ClientCode,

		EDNH,

		CODNH,

		EDRE,

		CODRE,

		EDDC,

		CODDC,

		EDCCEL,

		CODCCEL,

		EDCCM,

		CODCCM,

		EDCCIN,

		CODCCIN

	from

		Locations l

		left join Divisions d on l.DivID = d.DivID and l.ClientCode = d.ClientCode

	where

		l.LocID = @LocID

GO

/****** Object:  StoredProcedure [dbo].[list_ManagersForDivision]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Managers 3, 0, 48

select * from Managers where ClientCode = 3

select * from Managers m left join Manager_Locations ml on m.ManCode = ml.ManCode where ClientCode = 3

select * from Managers m left join Manager_Locations ml on m.ManCode = ml.ManCode where ClientCode = 3 and LocID = 3



select * from Manager_Locations where ManCode = 115

*/



CREATE procedure [dbo].[list_ManagersForDivision]

	@ClientCode int,

	@DivisionID int

as



	select

		ManagerID = m.ManCode,

		ClientCode,

		FirstName = FN,

		LastName = LN,

		MiddleName = MN,

		Title,

		Phone,

		Extension,

		Email

	from

		Managers m

	where

		m.ClientCode = @ClientCode

		and m.ManCode in (select ManCode from Manager_Divisions where DivID = @DivisionID)

	order by

		LastName,

		FirstName

GO

/****** Object:  Table [dbo].[Manager_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Manager_Locations](

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

	[ManCode] [int] NOT NULL,

	[LocID] [int] NOT NULL,

	[isPrimary] [bit] NULL,

 CONSTRAINT [PK_Manager_Locations] PRIMARY KEY CLUSTERED 

(

	[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  StoredProcedure [dbo].[store_Manager_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_Manager_Divisions]

	@ID int = 0 output,

	@ManagerID int,

	@DivisionID int,

	@IsPrimary bit

as

	if exists(select 1 from Manager_Divisions where ID = @ID) begin

		update Manager_Divisions set

			IsPrimary = @IsPrimary

		where

			ID = @ID

	end else if exists(select 1 from Manager_Divisions where ManCode = @ManagerID and DivID = @DivisionID) begin

		update Manager_Divisions set

			IsPrimary = @IsPrimary

		where

			ManCode = @ManagerID

			and DivID = @DivisionID

		

		set @ID = (select ID from Manager_Divisions where ManCode = @ManagerID and DivID = @DivisionID)

	end else begin

		insert into Manager_Divisions (

			ManCode, DivID, IsPrimary

		) values (

			@ManagerID, @DivisionID, @IsPrimary

		)



		set @ID = scope_identity()

	end



return @ID

GO

/****** Object:  StoredProcedure [dbo].[store_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_Locations]

	@LocID int output,

	@LocationName varchar(200),

	@DivID int,

	@ClientCode int,

	@EDNH int,

	@CODNH int,

	@EDRE int,

	@CODRE int,

	@EDDC int,

	@CODDC int,

	@EDCCEL int,

	@CODCCEL int,

	@EDCCM int,

	@CODCCM int,

	@EDCCIN int,

	@CODCCIN int

as

	if exists(select 1 from Locations where LocID = @LocID) begin

		update Locations set

			LocationName = @LocationName,

			DivID = @DivID,

			ClientCode = @ClientCode,

			EDNH = @EDNH,

			CODNH = @CODNH,

			EDRE = @EDRE,

			CODRE = @CODRE,

			EDDC = @EDDC,

			CODDC = @CODDC,

			EDCCEL = @EDCCEL,

			CODCCEL = @CODCCEL,

			EDCCM = @EDCCM,

			CODCCM = @CODCCM,

			EDCCIN = @EDCCIN,

			CODCCIN = @CODCCIN

		where

			LocID = @LocID

	end else begin

		insert into Locations (

			LocationName,

			DivID,

			ClientCode,

			EDNH,

			CODNH,

			EDRE,

			CODRE,

			EDDC,

			CODDC,

			EDCCEL,

			CODCCEL,

			EDCCM,

			CODCCM,

			EDCCIN,

			CODCCIN

		) values (

			@LocationName,

			@DivID,

			@ClientCode,

			@EDNH,

			@CODNH,

			@EDRE,

			@CODRE,

			@EDDC,

			@CODDC,

			@EDCCEL,

			@CODCCEL,

			@EDCCM,

			@CODCCM,

			@EDCCIN,

			@CODCCIN

		)



		set @LocID = scope_identity()

	end



return @LocID

GO

/****** Object:  StoredProcedure [dbo].[store_Manager_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[store_Manager_Locations]

	@ID int = 0 output,

	@ManagerID int,

	@LocationID int,

	@IsPrimary bit

as

	if exists(select 1 from Manager_Locations where ID = @ID) begin

		update Manager_Locations set

			IsPrimary = @IsPrimary

		where

			ID = @ID

	end else if exists(select 1 from Manager_Locations where ManCode = @ManagerID and LocID = @LocationID) begin

		update Manager_Locations set

			IsPrimary = @IsPrimary

		where

			ManCode = @ManagerID

			and LocID = @LocationID

		set @ID = (select ID from Manager_Locations where ManCode = @ManagerID and LocID = @LocationID)

	end else begin

		insert into Manager_Locations (

			ManCode, LocID, IsPrimary

		) values (

			@ManagerID, @LocationID, @IsPrimary

		)



		set @ID = scope_identity()

	end



return @ID

GO

/****** Object:  StoredProcedure [dbo].[load_Manager_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Manager_LocationsForManager 3



select * from Manager_Locations

*/



create procedure [dbo].[load_Manager_Locations]

	@ID int,

	@ManagerID int,

	@LocationID int

as



	select

		ID,

		ManagerID = ManCode,

		LocationID = LocID,

		IsPrimary

	from

		Manager_Locations

	where

		(ID = @ID)

		or (ManCode = @ManagerID and LocID = @LocationID)

GO

/****** Object:  StoredProcedure [dbo].[list_Manager_LocationsForManager]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Manager_LocationsForManager 3



select * from Manager_Locations

*/



CREATE procedure [dbo].[list_Manager_LocationsForManager]

	@ManagerID int

as



	select

		ID,

		ManagerID = ManCode,

		LocationID = ml.LocID,

		IsPrimary

	from

		Manager_Locations ml

		join Locations l on l.LocID = ml.LocID

	where

		ManCode = @ManagerID

	order by

		LocationName

GO

/****** Object:  StoredProcedure [dbo].[list_Manager_LocationsForLocation]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Manager_LocationsForLocation 3



select * from Manager_Locations

*/



CREATE procedure [dbo].[list_Manager_LocationsForLocation]

	@LocationID int

as



	select

		ID,

		ManagerID = ml.ManCode,

		LocationID = ml.LocID,

		IsPrimary

	from

		Manager_Locations ml

		join Managers m on m.ManCode = ml.ManCode

	where

		ml.LocID = @LocationID

	order by

		LN, FN

GO

/****** Object:  StoredProcedure [dbo].[list_ManagersForLocation]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

exec hm_list_Managers 3, 0, 48

select * from Managers where ClientCode = 3

select * from Managers m left join Manager_Locations ml on m.ManCode = ml.ManCode where ClientCode = 3

select * from Managers m left join Manager_Locations ml on m.ManCode = ml.ManCode where ClientCode = 3 and LocID = 3



select * from Manager_Locations where ManCode = 115

*/



CREATE procedure [dbo].[list_ManagersForLocation]

	@ClientCode int,

	@LocationID int

as



	select

		ManagerID = m.ManCode,

		ClientCode,

		FirstName = FN,

		LastName = LN,

		MiddleName = MN,

		Title,

		Phone,

		Extension,

		Email

	from

		Managers m

	where

		m.ClientCode = @ClientCode

		and m.ManCode in (select ManCode from Manager_Locations where LocID = @LocationID)

	order by

		LastName,

		FirstName

GO

/****** Object:  StoredProcedure [dbo].[delete_Managers]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[delete_Managers]

	@ManagerID int

as



begin transaction



begin try

	delete from

		Manager_Locations

	where

		ManCode = @ManagerID

		

	delete from

		Manager_Divisions

	where

		ManCode = @ManagerID



	delete from

		Managers

	where

		ManCode = @ManagerID

end try

begin catch

	rollback

	

	declare @msg nvarchar(4000)

	declare @severity int

	set @msg = error_message()

	set @severity = error_severity()

	raiserror(@msg, @severity, 1)

	return

end catch



commit

GO

/****** Object:  StoredProcedure [dbo].[delete_Manager_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_Manager_Locations]

	@ID int,

	@ManagerID int,

	@LocationID int

as

	delete from Manager_Locations

	where

		ID = @ID

	

	delete from Manager_Locations

	where

		ManCode = @ManagerID

		and LocID = @LocationID

GO

/****** Object:  StoredProcedure [dbo].[delete_Locations]    Script Date: 09/18/2012 10:15:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[delete_Locations]

	@LocID int

as



begin transaction



begin try

	delete ml

	from

		Manager_Locations ml

		join Locations l ON l.LocID = ml.LocID

	where

		ml.LocID = @LocID



	delete from

		Locations

	where

		LocID = @LocID

end try begin catch

	rollback

	declare @msg nvarchar(4000), @severity int

	select @msg = error_message(), @severity = error_severity()

	raiserror(@msg, @severity, 1)

	return

end catch



commit

GO

/****** Object:  Default [DF_secUsr_Internal]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[secUsr] ADD  CONSTRAINT [DF_secUsr_Internal]  DEFAULT ((1)) FOR [Internal]

GO

/****** Object:  Default [DF_secUsr_Active]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[secUsr] ADD  CONSTRAINT [DF_secUsr_Active]  DEFAULT ((1)) FOR [Active]

GO

/****** Object:  Default [DF_secUsr_NewUser]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[secUsr] ADD  CONSTRAINT [DF_secUsr_NewUser]  DEFAULT ((0)) FOR [NewUser]

GO

/****** Object:  ForeignKey [FK_Divisions_Client]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Divisions]  WITH CHECK ADD  CONSTRAINT [FK_Divisions_Client] FOREIGN KEY([ClientCode])

REFERENCES [dbo].[Client] ([ClientCode])

GO

ALTER TABLE [dbo].[Divisions] CHECK CONSTRAINT [FK_Divisions_Client]

GO

/****** Object:  ForeignKey [FK_Managers_Client]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Managers]  WITH CHECK ADD  CONSTRAINT [FK_Managers_Client] FOREIGN KEY([ClientCode])

REFERENCES [dbo].[Client] ([ClientCode])

GO

ALTER TABLE [dbo].[Managers] CHECK CONSTRAINT [FK_Managers_Client]

GO

/****** Object:  ForeignKey [FK_secUsrRole_secRole]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[secUsrRole]  WITH CHECK ADD  CONSTRAINT [FK_secUsrRole_secRole] FOREIGN KEY([roleID])

REFERENCES [dbo].[secRole] ([ID])

GO

ALTER TABLE [dbo].[secUsrRole] CHECK CONSTRAINT [FK_secUsrRole_secRole]

GO

/****** Object:  ForeignKey [FK_Manager_Divisions_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Manager_Divisions]  WITH CHECK ADD  CONSTRAINT [FK_Manager_Divisions_Divisions] FOREIGN KEY([DivID])

REFERENCES [dbo].[Divisions] ([DivID])

GO

ALTER TABLE [dbo].[Manager_Divisions] CHECK CONSTRAINT [FK_Manager_Divisions_Divisions]

GO

/****** Object:  ForeignKey [FK_Manager_Divisions_Managers]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Manager_Divisions]  WITH CHECK ADD  CONSTRAINT [FK_Manager_Divisions_Managers] FOREIGN KEY([ManCode])

REFERENCES [dbo].[Managers] ([ManCode])

GO

ALTER TABLE [dbo].[Manager_Divisions] CHECK CONSTRAINT [FK_Manager_Divisions_Managers]

GO

/****** Object:  ForeignKey [FK_Locations_Divisions]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Locations]  WITH CHECK ADD  CONSTRAINT [FK_Locations_Divisions] FOREIGN KEY([DivID])

REFERENCES [dbo].[Divisions] ([DivID])

GO

ALTER TABLE [dbo].[Locations] CHECK CONSTRAINT [FK_Locations_Divisions]

GO

/****** Object:  ForeignKey [FK_Manager_Locations_Locations]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Manager_Locations]  WITH CHECK ADD  CONSTRAINT [FK_Manager_Locations_Locations] FOREIGN KEY([LocID])

REFERENCES [dbo].[Locations] ([LocID])

GO

ALTER TABLE [dbo].[Manager_Locations] CHECK CONSTRAINT [FK_Manager_Locations_Locations]

GO

/****** Object:  ForeignKey [FK_Manager_Locations_Managers]    Script Date: 09/18/2012 10:15:01 ******/

ALTER TABLE [dbo].[Manager_Locations]  WITH CHECK ADD  CONSTRAINT [FK_Manager_Locations_Managers] FOREIGN KEY([ManCode])

REFERENCES [dbo].[Managers] ([ManCode])

GO

ALTER TABLE [dbo].[Manager_Locations] CHECK CONSTRAINT [FK_Manager_Locations_Managers]

GO

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

andrewbb@gmail.com
Architect
United States United States
No Biography provided

You may also be interested in...

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.180621.3 | Last Updated 22 Sep 2014
Article Copyright 2012 by andrewbb@gmail.com
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid