sql - Execute problemmer - Udvikleren.dk - Programmering, webdesign og grafik

Tags: sql
Bruger #16975 @ 30.03.15 20:16
jeg kan ikke Execute min sql fra master over i den db jeg har opretet
SQL kode
 USE [master]
 GO
 /****** Object:  Database [dbGarderobeLocalServer]    Script Date: 03/25/2015 05:40:21 ******/
 
 ALTER DATABASE [dbGarderobeLocalServer] SET COMPATIBILITY_LEVEL = 100
 GO
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 begin
 EXEC [dbGarderobeLocalServer].[dbo].[sp_fulltext_database] @action = 'enable'
 end
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET ANSI_NULL_DEFAULT OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET ANSI_NULLS OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET ANSI_PADDING OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET ANSI_WARNINGS OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET ARITHABORT OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET AUTO_CLOSE OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET AUTO_CREATE_STATISTICS ON
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET AUTO_SHRINK OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET AUTO_UPDATE_STATISTICS ON
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET CURSOR_CLOSE_ON_COMMIT OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET CURSOR_DEFAULT  GLOBAL
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET CONCAT_NULL_YIELDS_NULL OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET NUMERIC_ROUNDABORT OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET QUOTED_IDENTIFIER OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET RECURSIVE_TRIGGERS OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET  DISABLE_BROKER
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET DATE_CORRELATION_OPTIMIZATION OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET TRUSTWORTHY OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET ALLOW_SNAPSHOT_ISOLATION OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET PARAMETERIZATION SIMPLE
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET READ_COMMITTED_SNAPSHOT OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET HONOR_BROKER_PRIORITY OFF
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET  READ_WRITE
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET RECOVERY SIMPLE
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET  MULTI_USER
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET PAGE_VERIFY CHECKSUM
 GO
 ALTER DATABASE [dbGarderobeLocalServer] SET DB_CHAINING OFF
 GO
 USE [dbGarderobeLocalServer]
 GO
 /****** Object:  Table [dbo].[tblWardrobe]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblWardrobe](
     [UserInfo] [nvarchar](16) NOT NULL,
  CONSTRAINT [PK_tblWardrobe] PRIMARY KEY CLUSTERED 
 (
     [UserInfo] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblVisitors]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblVisitors](
     [SrNo] [int] NULL,
     [ThumbId] [nvarchar](4000) NULL,
     [HangerNo] [nvarchar](50) NULL,
     [CheckIn] [bit] NULL,
     [CheckInTime] [datetime] NULL,
     [CheckOutTime] [datetime] NULL,
     [Shift] [nvarchar](50) NULL,
     [OperatorSrNo] [int] NULL,
     [Price] [decimal](18, 2) NULL,
     [Dis] [decimal](18, 2) NULL,
     [ReserveStatus] [bit] NULL,
     [FreeWardrobe] [bit] NULL,
     [LuggageNo] [nvarchar](50) NULL,
     [LuggageReserveStatus] [bit] NULL,
     [LuggagePrice] [decimal](18, 2) NULL,
     [Locked] [bit] NULL,
     [LuggLocked] [bit] NULL,
     [ClubID] [nvarchar](50) NULL,
     [HSrNo] [int] NULL,
     [LsrNo] [int] NULL,
     [HL] [bit] NULL,
     [LL] [bit] NULL,
     [HReset] [bit] NULL,
     [LReset] [bit] NULL
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblOperatorsLogin]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblOperatorsLogin](
     [SrNo] [int] NOT NULL,
     [OperatorName] [nvarchar](50) NULL,
     [OperatorPassword] [nvarchar](50) NULL,
     [CreationDate] [datetime] NULL,
     [ManagerSrNo] [int] NULL,
     [IsActive] [bit] NULL,
     [ClubID] [nvarchar](50) NULL
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblOperatorsLog]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblOperatorsLog](
     [SrNo] [int] NOT NULL,
     [OperatorSrNo] [int] NULL,
     [LoginTime] [datetime] NULL,
     [LogoutTime] [datetime] NULL,
     [LogDate] [datetime] NULL,
  CONSTRAINT [PK_tblOperatorsLog] PRIMARY KEY CLUSTERED 
 (
     [SrNo] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblManagerLogin]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblManagerLogin](
     [SrNo] [int] IDENTITY(1,1) NOT NULL,
     [ManagerName] [nvarchar](50) NULL,
     [ManagerPwd] [nvarchar](50) NULL,
     [IsActive] [bit] NULL,
  CONSTRAINT [PK_tblManagerLogin] PRIMARY KEY CLUSTERED 
 (
     [SrNo] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblLuggageMaster]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblLuggageMaster](
     [ID] [int] NOT NULL,
     [LuggageTitle] [nvarchar](50) NULL,
     [LuggageDetail] [nvarchar](max) NULL,
     [Cost] [decimal](18, 2) NULL,
     [Discount] [decimal](18, 2) NULL,
     [Status] [bit] NULL,
  CONSTRAINT [PK_tblLuggageMaster] 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]
 GO
 /****** Object:  Table [dbo].[tblLuggageDetail]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblLuggageDetail](
     [ID] [int] NOT NULL,
     [HangerNo] [nvarchar](50) NULL,
     [ThumbID] [nvarchar](50) NULL,
     [Date] [datetime] NULL,
     [LuggageID] [int] NULL,
     [Price] [decimal](18, 2) NULL,
     [Discount] [decimal](18, 2) NULL,
     [Qty] [decimal](18, 2) NULL,
     [Amount] [decimal](18, 2) NULL,
     [FreeWardrobe] [bit] NULL,
     [LuggageNo] [nvarchar](50) NULL,
  CONSTRAINT [PK_tblLuggageDetail] 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]
 GO
 /****** Object:  Table [dbo].[tblLuggageCreation]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblLuggageCreation](
     [SrNo] [int] NOT NULL,
     [Luggage] [int] NULL,
     [ForDate] [datetime] NULL,
     [ManagerSrNo] [int] NULL,
     [ResetStatus] [bit] NULL,
     [Price] [decimal](18, 2) NULL,
     [ClubID] [nvarchar](50) NULL,
     [IsLooping] [bit] NULL
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblLogo]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblLogo](
     [SrNo] [int] IDENTITY(1,1) NOT NULL,
     [Logo] [image] NULL,
  CONSTRAINT [PK_tblLogo] PRIMARY KEY CLUSTERED 
 (
     [SrNo] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblLog]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblLog](
     [SrNo] [int] NOT NULL,
     [UserName] [nvarchar](max) NULL,
     [FormName] [nvarchar](max) NULL,
     [ActionTaken] [nvarchar](max) NULL,
     [ActionDate] [datetime] NULL,
     [ActionTime] [datetime] NULL,
     [ClubID] [nvarchar](50) NULL
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblHangersCreation]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblHangersCreation](
     [SrNo] [int] NOT NULL,
     [Hangers] [int] NULL,
     [Shift] [nvarchar](50) NULL,
     [ForDate] [datetime] NULL,
     [ManagerSrNo] [int] NULL,
     [ResetStatus] [bit] NULL,
     [Price] [decimal](18, 2) NULL,
     [Discount] [decimal](18, 2) NULL,
     [ClubID] [nvarchar](50) NULL,
     [IsLooping] [bit] NULL
 ) ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblAddress]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblAddress](
     [ID] [int] NOT NULL,
     [HLine1] [nvarchar](max) NULL,
     [HLine2] [nvarchar](max) NULL,
     [HLine3] [nvarchar](max) NULL,
     [HLine4] [nvarchar](max) NULL,
     [HLine5] [nvarchar](max) NULL,
     [FLIne1] [nvarchar](max) NULL,
     [FLine2] [nvarchar](max) NULL,
     [FLine3] [nvarchar](max) NULL,
     [Picture] [image] NULL,
     [Status] [bit] NULL,
     [ClubID] [nvarchar](50) NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 /****** Object:  Table [dbo].[tblActivation]    Script Date: 03/25/2015 05:40:23 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[tblActivation](
     [SrNo] [int] NULL,
     [ClubName] [nvarchar](50) NULL,
     [ClubID] [nvarchar](50) NULL,
     [ActivationDate] [datetime] NULL,
     [ExpiryDate] [datetime] NULL,
     [UserName] [nvarchar](50) NULL,
     [Password] [nvarchar](50) NULL,
     [EmailID] [nvarchar](50) NULL,
     [ActiveStatus] [bit] NULL
 ) ON [PRIMARY]
 GO
 /****** Object:  StoredProcedure [dbo].[UploadWardrobe]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 Create PROCEDURE [dbo].[UploadWardrobe](@ward nvarchar(50))
 AS
 if Not exists(Select UserInfo from tblWardrobe where UserInfo=@ward)
 insert into tblWardrobe values(@ward)
 GO
 /****** Object:  StoredProcedure [dbo].[UploadVisitor]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadVisitor](@srno int,@thid nvarchar(4000),@hno nvarchar(50),@chkin bit,@chkintime datetime,@chkouttime datetime,@shift nvarchar(50),@opsrno int,@price decimal(18,2),@dis decimal(18,2),@resstatus bit,@frward bit,@lno nvarchar(50),@lrstatus bit,@lprice decimal(18,2),@locked bit,@llocked bit,@clbid nvarchar(50),@hsrno int,@lsrno int,@hl bit,@ll bit,@hr bit,@lr bit)
 AS
 if Not exists (Select SrNo from tblVisitors where SrNo=@srno and ClubID=@clbid)
 insert into tblVisitors values(@srno,@thid,@hno,@chkin,@chkintime,@chkouttime,@shift,@opsrno,@price,@dis,@resstatus,@frward,@lno,@lrstatus,@lprice,@locked,@llocked,@clbid,@hsrno,@lsrno,@hl,@ll,@hr,@lr)
 else
 Update tblVisitors Set ThumbID=@thid,HangerNo=@hno,CheckIn=@chkin,CheckInTime=@chkintime,CheckOutTime=@chkouttime,Shift=@shift,OperatorSrNo=@opsrno,Price=@price,Dis=@dis,ReserveStatus=@resstatus,FreeWardrobe=@frward,LuggageNo=@lno,LuggageReserveStatus=@lrstatus,LuggagePrice=@lprice,Locked=@locked,LuggLocked=@llocked,HSrNo=@hsrno,LSrNo=@lsrno,HL=@hl,LL=@ll,HReset=@hr,LReset=@lr where SrNo=@srno and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[UploadOperatorLogin]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadOperatorLogin](@srno int,@opname nvarchar(50),@oppwd nvarchar(50),@date datetime,@msrno int,@isactive bit,@clbid nvarchar(50))
 AS
 if not exists (Select SrNo from tblOperatorsLogin where SrNo=@srno and ClubID=@clbid)
 Insert into tblOperatorsLogin values(@srno,@opname,@oppwd,@date,@msrno,@isactive,@clbid)
 else 
 Update tblOperatorsLogin Set OperatorName=@opname,OperatorPassword=@oppwd,CreationDate=@date,ManagerSrNo=@msrno,IsActive=@isactive where SrNo=@srno and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[UploadLuggageCreation]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadLuggageCreation](@srno int,@luggage int,@fordate datetime,@msrno int,@reset bit,@price decimal(18,2),@clbid nvarchar(50),@looping bit)
 AS
 if not exists (Select SrNo from tblLuggageCreation where ClubID=@clbid and SrNo=@srno)
 Insert into tblLuggageCreation Values(@srno,@luggage,@fordate,@msrno,@reset,@price,@clbid,@looping)
 else 
 update tblLuggageCreation set Luggage=@luggage,ForDate=@fordate,ManagerSrNo=@msrno,ResetStatus=@reset,Price=@price,IsLooping=@looping where ClubID=@clbid and SrNo=@srno
 GO
 /****** Object:  StoredProcedure [dbo].[UploadLog]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadLog](@srno int,@uname nvarchar(MAX),@formName nvarchar(MAX),@action nvarchar(MAX),@actdate datetime,@acttime nvarchar(MAX),@clbid nvarchar(50))
 AS
 if not exists (Select SrNo from tblLog where SrNo=@srno and ClubID=@clbid)
 Insert into tblLog Values(@srno,@uname,@formname,@action,@actdate,@acttime,@clbid)
 else 
 Update tblLog set UserName=@uname,FormName=@formname,ActionTaken=@action,ActionDate=@actdate,ActionTime=@acttime where ClubID=@clbid and SrNo=@Srno
 GO
 /****** Object:  StoredProcedure [dbo].[UploadHangerCreation]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadHangerCreation](@srno int,@hangers int,@shift nvarchar(20),@fordate datetime,@msrno int,@reset bit,@price decimal(18,2),@dis decimal(18,2),@clbid nvarchar(50),@looping bit)
 AS
 if not exists (Select SrNo from tblHangersCreation where ClubID=@clbid and SrNo=@srno)
 Insert into tblHangersCreation Values(@srno,@hangers,@shift,@fordate,@msrno,@reset,@price,@dis,@clbid,@looping)
 else 
 update tblHangersCreation set Hangers=@hangers,Shift=@shift,ForDate=@fordate,ManagerSrNo=@msrno,ResetStatus=@reset,Price=@price,Discount=@dis,ISLooping=@looping where ClubID=@clbid and SrNo=@srno
 GO
 /****** Object:  StoredProcedure [dbo].[UploadAddress]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadAddress](@id int,@hl1 nvarchar(MAX),@hl2 nvarchar(MAX),@hl3 nvarchar(MAX),@hl4 nvarchar(MAX),@hl5 nvarchar(MAX),@Fl1 nvarchar(MAX),@Fl2 nvarchar(MAX),@Fl3 nvarchar(MAX),@clbid nvarchar(50),@status bit,@pic image)
 As
 if not exists (Select ID from tblAddress where ClubID=@clbid and ID=@id)
 insert into tblAddress values(@id,@hl1,@hl2,@hl3,@hl4,@hl5,@Fl1,@Fl2,@Fl3,@pic,@status,@clbid)
 else 
 Update tblAddress set HLine1=@hl1,HLine2=@hl2,HLine3=@hl3,HLine4=@hl4,HLine5=@hl5,FLine1=@Fl1,FLine2=@Fl2,FLine3=@Fl3,Picture=@pic,Status=@status where ClubID=@clbid and ID=@id
 GO
 /****** Object:  StoredProcedure [dbo].[UploadActivation]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UploadActivation](@srno int,@clbname nvarchar(50),@clbid nvarchar(50),@actdate datetime,@expdate datetime,@uname nvarchar(50),@pwd nvarchar(50),@email nvarchar(50),@actstatus bit)
 As
 if not exists(Select SrNo from tblActivation where SrNo=@srno and ClubID=@clbid)
 Insert into tblActivation values(@srno,@clbname,@clbid,@actdate,@expdate,@uname,@pwd,@email,@actstatus)
 else
 Update tblActivation Set ClubName=@clbname,ClubID=@clbid,ActivationDate=@actdate,ExpiryDate=@expdate,UserName=@uname,Password=@pwd,EmailID=@email,ActiveStatus=@actstatus where SrNo=@srno and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateLuggagePrice]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateLuggagePrice](@price decimal(18,2),@fordate datetime,@clbid nvarchar(50))
 AS
     Declare @status int
 select @status=ResetStatus from tblLuggageCreation where Fordate=@fordate and ClubID=@clbid
 if @status=0    
 begin
     update tblLuggageCreation set Price=@price where ForDate=@fordate and ClubID=@clbid
     return 1
     end
 else
     return 7
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateHangersPrice]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateHangersPrice](@price decimal(18,2),@dis decimal(18,2),@fordate datetime,@clbid nvarchar(50))
 AS
     Declare @status int
 select @status=ResetStatus from tblHangersCreation where Fordate=@fordate and ClubID=@clbid
 if @status=0    
 begin
     update tblHangersCreation set Price=@price,Discount=@dis where ForDate=@fordate and ClubID=@clbid
     return 1
     end
 else
     return 7
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateFreeWardrobe]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateFreeWardrobe](@thid nvarchar(50),@hno nvarchar(50),@date datetime,@clbid nvarchar(50))
 AS
     update tblVisitors set FreeWardrobe=1 where SrNo=@thid and ClubID=@clbid and SrNo=(select Max(SrNo) from tblVisitors) and convert(char(11),CheckInTime,101)=convert(char(11),@date,101)
 
 update tblLuggageDetail set FreeWardrobe=1 where Thumbid=@thid and Hangerno=@hno and convert(char(11),Date,101)=convert(char(11),@date,101)
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateandUnlock2]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateandUnlock2](@lno nvarchar(50),@sno int)
     
 AS
     Update tblVisitors set LuggLocked=1,Locked=1 where LuggageNo=@lno and srNo=@sno and LsrNo=(Select Max(SrNo) from tblLuggageCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateandUnlock]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateandUnlock](@chkouttime datetime,@lno nvarchar(50))
 AS
 Update tblVisitors set CheckIn=0,CheckOutTime=@chkouttime,LuggageReserveStatus=0,LuggLocked=0,Locked=0 where LuggageNo=@lno
 and LsrNo=(Select Max(SrNo) from tblLuggageCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateandLockHanger]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateandLockHanger](@hno nvarchar(50),@srno int)
 AS
 Update tblVisitors set Locked=1,LuggLocked=1 where HangerNo=@hno and SrNo=@srno and HsrNo=(Select Max(SrNo) from tblHangersCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[Updateandlock]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[Updateandlock](@lno nvarchar(50),@sno int)
     
 AS
     Update tblVisitors set LuggLocked=1,Locked=1 where LuggageNo=@lno and srNo=@sno and LsrNo=(Select Max(SrNo) from tblLuggageCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[UpdateAllRecord]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UpdateAllRecord](@clbname nvarchar(50),@clbid nvarchar(50),@actdate datetime,@expdate datetime,@uname nvarchar(50),@pwd nvarchar(50),@email nvarchar(50),@actstatus bit)
 AS
 Update tblActivation  Set ClubID=@clbid,ClubName=@clbname,ActivationDate=@actdate,ExpiryDate=@expdate,UserName=@uname,Password=@pwd,EmailID=@email,ActiveStatus=@actstatus
 
 Update tblHangersCreation Set ClubID=@clbid
 
 Update tblLuggageCreation Set ClubID=@clbid
 
 Update tblVisitors Set ClubID=@clbid
 
 Update tblWardrobe Set UserInfo=@clbid
 
 Update tblOperatorsLogin Set ClubID=@clbid
 
 Update tblLog Set ClubID=@clbid
 
 Update tblAddress Set ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[UnpdateandUnLockHanger]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UnpdateandUnLockHanger](@chkouttime datetime,@hno nvarchar(50),@srno int)
 AS
 Update tblVisitors set CheckIn=0,CheckOutTime=@chkouttime,LuggageReserveStatus=0,Locked=0,LuggLocked=0 where HangerNo=@hno and Locked=1 and SrNo=@srno and HsrNo=(Select Max(SrNo) from tblHangersCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[UnpdateandLockHanger]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[UnpdateandLockHanger](@chkouttime datetime,@hno nvarchar(50),@srno int)
 AS
 Update tblVisitors set CheckIn=0,CheckOutTime=@chkouttime,LuggageReserveStatus=0,Locked=0,LuggLocked=0 where HangerNo=@hno and Locked=1 and SrNo=@srno and HsrNo=(Select Max(SrNo) from tblHangersCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[TemporaryPro]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[TemporaryPro]
 AS
 
 --select m.ID,(case when l.LuggageTitle is null then 'No Luggage' else l.LuggageTitle end) from tblLuggageDetail m join tblLuggageMaster l on m.LuggageID=l.ID
 
 --Select isnull(sum(Hangers),0),isnull(sum(Price),0),isnull(sum(Discount),0) from tblHangersCreation where ResetStatus=0
 Select 'L'+cast(Max(Substring(UserInfo,2,4))+1 as varchar(20)) from tblWardrobe
 GO
 /****** Object:  StoredProcedure [dbo].[RptProcedure]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[RptProcedure](@date datetime,@clbid nvarchar(50),@time1 datetime,@time2 datetime)
 AS
 --GetFreeWardrobeReport
 
 select v.HangerNo vHangerNo,v.CheckInTime vCheckIn,v.LuggageNo LuggageNo,isnull(v.Price,0) vBal,isnull(v.LuggagePrice,0) lPrice from tblVisitors v  where convert(datetime,convert(char(11),v.CheckInTime,101),101)=convert(datetime,convert(char(11),@date,101),101) and  v.FreeWardRobe=1 and ClubID=@clbid
 
 --DailyHangerRevenueReport
 
 Select HangerNo vHangerNo,CheckInTime vCheckIn,Price VBal,(case When v.FreeWardrobe=1 then 'Free Wardrobe' else 'Paid Wardrobe' end) FreeWard,v.LuggageNo,v.LuggagePrice lPrice from tblVisitors v where convert(datetime,convert(char(11),v.CheckInTime,101),101)=convert(datetime,convert(char(11),@date,101),101)and convert(char(8),v.CheckInTime,108) between convert(char(8),@Time1,108) and convert(char(8),@Time2,108) and v.ClubID=@clbid
 
 --CrIncomeFromLuggage
 
 Select HangerNo,CheckInTime Date,LuggageNo,LuggagePrice Price from tblvisitors v where convert(char(11),v.checkInTime,101)=convert(char(11),@date,101) and ClubID=@clbid
 
 --CrFetchLog
 
 Select * from tblLog where ActionDate=@date and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[ResetLuggage]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[ResetLuggage](@clbid nvarchar(50))
     
 AS
 declare @srno int
 
 Select @srno=isnull(Max(SrNo),0) from tblLuggageCreation Where ClubID=@clbid
 
 Update tblLuggageCreation set ResetStatus=1 where SrNo=@srno and ClubID=@clbid
 
 Update tblVisitors set LReset=1 where LSrNo=@srno and ClubID=@clbid and LuggageReserveStatus=0 and CheckIn=0
 
 update tblvisitors set LReset=0 where LuggageNo in (Select LuggageNo from tblVisitors where CheckIN=1)
 GO
 /****** Object:  StoredProcedure [dbo].[ResetHangers]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[ResetHangers](@clbid nvarchar(50))
     
 AS
 declare @srno int
 
 Select @srno=isnull(Max(SrNo),0) from tblHangersCreation Where ClubID=@clbid
 
 Update tblHangersCreation set ResetStatus=1 where SrNo=@srno and ClubID=@clbid
 
 Update tblVisitors set HReset=1 where HSrNo=@srno and ClubID=@clbid and ReserveStatus=1 and CheckIn=0
 
 update tblvisitors set HReset=0 where HangerNo in (Select HangerNo from tblVisitors where CheckIN=1)
 GO
 /****** Object:  StoredProcedure [dbo].[NextLuggage]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[NextLuggage](@clbid nvarchar(50))
 As
 declare @date datetime
 declare @tl int
 declare @nl int
 declare @srno int
 declare @lno int
 declare @i int=0
 declare @x int
 declare @maxsrno int=0
 declare @reset bit=0
 declare @looping bit=0
 Select @maxsrno=Max(SrNo) from tblLuggageCreation where ClubID=@clbid
 Select @tl=isnull((Luggage),0),@reset=ResetStatus,@looping=IsLooping from tblLuggageCreation where SrNo=@maxsrno and ClubID=@clbid--ResetStatus=0
 if @looping=0
 begin
 if exists(Select LuggageNo from tblVisitors where (right(LuggageNo,4)=@tl or @Reset=1) and ClubID=@clbid and lSrNo=@maxsrno)
 begin
 set @i=1
 While @i<=@tl
 begin
 if exists(select LuggageNo from tblVisitors where right(LuggageNo,4)=@i and LuggageReserveStatus=0 and CheckIn=0 and LReset=1 and ClubID=@clbid and lSrNo=@maxsrno)
 break
 if not exists(select LuggageNo from tblVisitors where right(LuggageNo,4)=@i and ClubID=@clbid and lSrNo=@maxsrno)
 break
 set @i=@i+1
 end
 set @nl=@i
 if @nl<=@tl
 begin
 Select @nl
 end
 else
 begin
 select @date=ForDate from tblLuggageCreation where SrNo=@maxsrno and ClubID=@clbid;
 with ct1(LuggageNo)
 As(Select substring(LuggageNo,2,4) from tblVisitors where CheckIn=0 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and LuggageNo is not null and lSrNo=@maxsrno),
 ct2(LuggageNo)
 As(Select substring(LuggageNo,2,4) from tblVisitors where CheckIn=1 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and LuggageNo is not null and lSrNo=@maxsrno)
 Select isnull(Min(LuggageNo),0) from ct1 where LuggageNo not in(Select LuggageNo from ct2)
 end
 end
 
 else
 
 begin
 set @i=1
 While @i<@tl
 begin
 if not exists(select LuggageNo from tblVisitors where right(LuggageNo,4)=@i and ClubID=@clbid and lSrNo=@maxsrno)
 break
 set @i=@i+1
 end
 set @nl=@i
 if @nl<=@tl
 begin
 Select @nl
 end
 else
 begin
 select @date=ForDate from tblLuggageCreation where ResetStatus=0 and ClubID=@clbid;
 with ct1(LuggageNo)
 As(Select substring(LuggageNo,2,4) from tblVisitors where CheckIn=0 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and LuggageNo is not null and lSrNo=@maxsrno),
 ct2(LuggageNo)
 As(Select substring(LuggageNo,2,4) from tblVisitors where CheckIn=1 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and LuggageNo is not null and lSrNo=@maxsrno)
 Select isnull(Min(LuggageNo),0) from ct1 where LuggageNo not in(Select LuggageNo from ct2)
 end
 end
 end
 else
 begin
 set @i=1
 While @i<@tl
 begin
 if not exists(select LuggageNo from tblVisitors where right(LuggageNo,4)=@i and LuggageReserveStatus=1 and CheckIn=1 and ClubID=@clbid and lSrNo=@maxsrno)
 break
 set @i=@i+1
 end
 set @nl=@i
 if @nl<=@tl
 begin
 Select @nl
 end
 end
 GO
 /****** Object:  StoredProcedure [dbo].[NextHanger]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[NextHanger](@clbid nvarchar(50))
 As
 declare @date datetime
 declare @th int
 declare @nh int
 declare @srno int
 declare @hno int
 declare @i int=0
 declare @x int
 declare @reset bit=0
 declare @looping bit=0
 declare @maxsrno int=0
 declare @resethno int=0
 Select @maxsrno=Max(SrNo) from tblHangersCreation where ClubID=@clbid
 Select @th=isnull(Hangers,0),@reset=ResetStatus,@looping=IsLooping from tblHangersCreation where SrNo=@maxsrno and ClubID=@clbid--ResetStatus=0
 if @looping=0
 begin
  
  if exists(Select HangerNo from tblVisitors where (HangerNo=@th or @reset=1) and ClubID=@clbid and HSrNo=@maxsrno)
   begin
   
   set @i=1
   
    While @i<=@th
     begin
      if  exists(select HangerNo from tblVisitors where HangerNo=@i and ReserveStatus=1 and CheckIn=0 and HReset=1 and ClubID=@clbid and HSrNo=@maxsrno)
        break
        
        if  not exists(select HangerNo from tblVisitors where HangerNo=@i and ClubID=@clbid and HSrNo=@maxsrno)
        break
        
     set @i=@i+1
     end
     
     set @nh=@i
     if @nh<=@th
     begin
     Select @nh
     end
     else
     begin
     select @date=ForDate from tblHangersCreation where SrNo=@maxsrno and ClubID=@clbid;--ResetStatus=0;
     with ct1(HangerNo)
     As(Select HangerNo from tblVisitors where CheckIn=0 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,  101)>=convert(char(11),@date,101) and HSrNo=@maxsrno),
     ct2(HangerNo)
     As(Select HangerNo from tblVisitors where CheckIn=1 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and HSrNo=@maxsrno)
     Select isnull(Min(HangerNo),0) from ct1 where HangerNo not in(Select HangerNo from ct2)
     end
 end
 
 else 
   begin
    set @i=1
    While @i<@th
     begin
      if not exists(select HangerNo from tblVisitors where cast(HangerNo as integer)=@i and ClubID=@clbid and HSrNo=@maxsrno)
      break
      set @i=@i+1
     end
     set @nh=@i
     if @nh<=@th
     begin
     Select @nh
     end
     else
     begin
     select @date=ForDate from tblHangersCreation where /*SrNo=@maxsrno and*/ ClubID=@clbid and ResetStatus=0;
     with ct1(HangerNo)
     As(Select HangerNo from tblVisitors where CheckIn=0 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and HSrNo=@maxsrno ),
     ct2(HangerNo)
 As(Select HangerNo from tblVisitors where CheckIn=1 and ClubID=@clbid and Reservestatus=1 and convert(char(11),CheckInTime,101)>=convert(char(11),@date,101) and HSrNo=@maxsrno )
 Select isnull(Min(HangerNo),0) from ct1 where HangerNo not in(Select HangerNo from ct2)
     end
   end
 end
 
 --islooping = true
 else
 begin
 set @i=1
   While @i<@th
     begin
      if not exists(select HangerNo from tblVisitors where HangerNo=@i and ReserveStatus=1 and CheckIn=1 and ClubID=@clbid and HSrNo=@maxsrno)
        break
     set @i=@i+1
     end
   set @nh=@i
   if @nh<=@th
    begin
     Select @nh
    end
 end
 GO
 /****** Object:  StoredProcedure [dbo].[LuggageLooping]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[LuggageLooping](@clbid nvarchar(50),@isloop bit)
 AS
 declare @srno int
 Select @srno=isnull(Max(SrNo),0) from tblLuggagecreation where ClubID=@clbid
 update tblLuggagecreation set IsLooping=@isloop where SrNo=@srno
 GO
 /****** Object:  StoredProcedure [dbo].[LockLuggageNo]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[LockLuggageNo](@lno nvarchar(50),@chkin bit,@chkintime datetime,@chkouttime datetime,@shift nvarchar(50),@opsrno int,@price decimal(18,2),@clbid nvarchar(50))
 AS
     declare @srno int
     declare @lsrno int
     select @srno=isnull(Max(SrNo),0)+1 from tblVisitors
     select @lsrno=isnull(Max(SrNo),0) from tblluggageCreation where ClubID=@clbid
 insert into tblVisitors values(@srno,null,Null,@chkin,@chkintime,@chkouttime,@shift,@opsrno,0,0,0,0,@lno,1,@price,0,1,@clbid,null,@lsrno,0,1,0,0)
 return @srno
 GO
 /****** Object:  StoredProcedure [dbo].[LockhangerNo]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[LockhangerNo](@hno nvarchar(50),@chkin bit,@chkintime datetime,@chkouttime datetime,@shift nvarchar(50),@opsrno int,@price decimal(18,2),@clbid nvarchar(50))
 AS
     declare @srno int
     declare @hsrno int
     select @srno=isnull(Max(SrNo),0)+1 from tblVisitors
     select @hsrno=isnull(Max(SrNo),0) from tblHangersCreation where ClubID=@clbid
 insert into tblVisitors values(@srno,null,@hno,@chkin,@chkintime,@chkouttime,@shift,@opsrno,@price,0,1,0,null,0,0,1,0,@clbid,@hsrno,null,1,0,0,0)
 return @srno
 GO
 /****** Object:  StoredProcedure [dbo].[ListHangerWithLuggage]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[ListHangerWithLuggage]
     
 AS
 Select HangerNo,isnull(LuggageNo,'NA') LuggageNo from tblVisitors where ReserveStatus=1
 GO
 /****** Object:  StoredProcedure [dbo].[IUDLuggageMaster]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[IUDLuggageMaster](@op int,@id int,@title nvarchar(50),@detail nvarchar(MAx),@cost decimal(18,2),@dis decimal(18,2),@status bit)
 AS
 if @op=1
     begin
       Select @id=isnull(max(ID),0)+1 from tblLuggageMaster
       insert into tblLuggageMaster values(@id,@title,@detail,@cost,@dis,@status)
     end
 else if @op=2
     begin
       update tblluggagemaster set LuggageTitle=@title,LuggageDetail=@detail,Cost=@cost,Discount=@dis,Status=@status where ID=@id
     end
 GO
 /****** Object:  StoredProcedure [dbo].[InsertVisitor]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[InsertVisitor](@thid nvarchar(4000),@hno nvarchar(50),@chkin bit,@chkintime datetime,@chkouttime datetime,@shift nvarchar(50),@opsrno int,@price decimal(18,2),@dis decimal(18,2),@clbid nvarchar(50),@lno nvarchar(50),@lrstatus bit,@lprice decimal(18,2),@h bit,@l bit)
 AS
     declare @srno int
     declare @hsrno int
     declare @lsrno int
     select @srno=isnull(Max(SrNo),0)+1 from tblVisitors
     select @hsrno=isnull(Max(SrNo),0) from tblHangersCreation where ClubID=@clbid
     select @lsrno=isnull(Max(SrNo),0) from tblLuggageCreation where ClubID=@clbid
 
 update tblVisitors set HReset=0 where HangerNo=@hno and HSrNo=@hsrno 
 
 update tblVisitors set LReset=0 where LuggageNo=@lno and LSrNo=@lsrno
 
 if (@h=1 and @l=1)
 insert into tblVisitors values(@srno,@thid,@hno,@chkin,@chkintime,@chkouttime,@shift,@opsrno,@price,@dis,1,0,@lno,@lrstatus,@lprice,0,0,@clbid,@hsrno,@lsrno,0,0,0,0)
 else if (@h=1 and @l=0)
 insert into tblVisitors values(@srno,@thid,@hno,@chkin,@chkintime,@chkouttime,@shift,@opsrno,@price,@dis,1,0,@lno,@lrstatus,@lprice,0,0,@clbid,@hsrno,null,0,0,0,0)
 else if (@h=0 and @l=1)
 insert into tblVisitors values(@srno,@thid,@hno,@chkin,@chkintime,@chkouttime,@shift,@opsrno,@price,@dis,1,0,@lno,@lrstatus,@lprice,0,0,@clbid,null,@lsrno,0,0,0,0)
 
 return @srno
 GO
 /****** Object:  StoredProcedure [dbo].[InsertLuggageDetail]    Script Date: 03/25/2015 05:40:35 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[InsertLuggageDetail](@hno nvarchar(50),@thid nvarchar(50),@date datetime,@lid int,@price decimal(18,2),@dis decimal(18,2),@qty decimal(18,2),@amnt decimal(18,2),@lno nvarchar(50))
 AS
 declare @id int
 if not exists(select LuggageID from tblLuggageDetail where ThumbID=@thid and Date=@date and LuggageID=@lid)
 begin
 select @id=isnull(Max(ID),0)+1 from tblLuggageDetail
 insert into tblLuggageDetail values(@id,@hno,@thid,@date,@lid,@price,@dis,@qty,@amnt,0,@lno) end
 else
 begin
 update tblLuggageDetail set Qty=@qty,Price=@price,Discount=@dis,amount=@amnt where thumbID=@thid and Date=@date and LuggageID=@lid
 end
 GO
 /****** Object:  StoredProcedure [dbo].[InsertLogo]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[InsertLogo](@logo image)
 AS
 declare @c int
 select @c=count(SrNo) from tblLogo
 if @c>0
 update tblLogo set Logo=@logo
 else 
 insert into tblLogo values(@logo)
 GO
 /****** Object:  StoredProcedure [dbo].[InsertAddress]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[InsertAddress](@hl1 nvarchar(MAX),@hl2 nvarchar(MAX),@hl3 nvarchar(MAX),@hl4 nvarchar(MAX),@hl5 nvarchar(MAX),@Fl1 nvarchar(MAX),@Fl2 nvarchar(MAX),@Fl3 nvarchar(MAX),@clbid nvarchar(50))
 AS
 declare @srno int
 select @srno=isnull(Max(ID),0)+1 from tblAddress
 insert into tblAddress values(@srno,@hl1,@hl2,@hl3,@hl4,@hl5,@Fl1,@Fl2,@Fl3,Null,1,@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[InsertActivation]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[InsertActivation](@clbname nvarchar(50),@clbid nvarchar(50),@actdate datetime,@expdate datetime,@uname nvarchar(50),@pwd nvarchar(50),@email nvarchar(50),@pic image,@actstatus bit)
 As
 declare @srno int
 declare @srno1 int
 Select @srno=isnull(Max(SrNo),0)+1 from tblActivation
 if not exists(Select * from tblActivation)
 Insert into tblActivation values(@srno,@clbname,@clbid,@actdate,@expdate,@uname,@pwd,@email,@actstatus)
 Select @srno1=isnull(Max(ID),0)+1 from tblAddress
 if not exists (Select * from tblAddress where ClubID=@clbid)
 Insert into tblAddress values(@srno1,'Header Line - 1','Header Line - 2','Header Line - 3','Header Line - 4','Header Line - 5','Footer Line - 1','Footer Line - 2','Footer Line - 3',@pic,1,@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[HangerLooping]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[HangerLooping](@clbid nvarchar(50),@isloop bit)
 AS
 declare @srno int
 Select @srno=isnull(Max(SrNo),0) from tblhangerscreation where ClubID=@clbid
 update tblhangerscreation set IsLooping=@isloop where SrNo=@srno
 GO
 /****** Object:  StoredProcedure [dbo].[GuestAnalysingReport]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GuestAnalysingReport](@dt datetime,@dt2 datetime,@clbid nvarchar(50))--,@time1 datetime,@time2 datetime)
 As
 declare @chkin int
 declare @chkout int
 Select @chkin=isnull(count(*),0)  from tblVisitors v where v.CheckInTime between @dt and @dt2 /*convert(datetime,convert(char(11),v.CheckInTime,101),101) between convert(datetime,convert(char(11),@dt,101),101) and convert(datetime,convert(char(11),@dt2,101),101) and convert(char(8),Convert(datetime,v.CheckInTime,0),108) between convert(char(8),Convert(datetime,@Time1,0),108) and convert(char(8),Convert(datetime,@Time2,0),108)*/ and v.ClubID=@clbid  and HL=0 and LL=0
 
 Select @chkout=isnull(count(*),0)  from tblVisitors v where v.CheckOutTime between @dt and @dt2 /*convert(datetime,convert(char(11),v.CheckOutTime,101),101)between convert(datetime,convert(char(11),@dt,101),101) and convert(datetime,convert(char(11),@dt2,101),101) and convert(char(8),Convert(datetime,v.CheckInTime,0),108) between convert(char(8),Convert(datetime,@Time1,0),108) and convert(char(8),Convert(datetime,@Time2,0),108)*/and v.ClubID=@clbid and HL=0 and LL=0
 
 Select @chkin CheckIn,@chkout CheckOut,@dt dates,@dt2 dates2,convert(char(8),Convert(datetime,@dt,0),108) fromTime,convert(char(8),Convert(datetime,@dt2,0),108) ToTime
 GO
 /****** Object:  StoredProcedure [dbo].[GetLuggageDetail3]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetLuggageDetail3](@thid nvarchar(50),@chkin datetime)
 AS
 with ct1(ThumID,Title,Qty,Price,Amount)
 as(select d.ThumbID,(case when m.LuggageTitle is null then 'No Luggage'else m.LuggageTitle end) Title, cast(cast((case when d.Qty is null then '0' else d.Qty end) as int)as varchar(20)) Qty,d.Price,d.Amount from tblLuggageMaster m,tblLuggageDetail d where d.LuggageID=m.ID and d.ThumbID=@thid and convert(char(11),d.Date,101)=convert(char(11),@chkin,101))
 
 select c.Title Title,c.Price Price,c.Qty Qty,c.Amount Amount from ct1 c,tblVisitors v where c.ThumID=v.SrNo
 GO
 /****** Object:  StoredProcedure [dbo].[GetLuggageDetail2]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetLuggageDetail2](@thid nvarchar(50),@chkin datetime)
 AS
 with ct1(ThumID,Luggage,Qty)
 as(select d.ThumbID,m.LuggageTitle Luggage,cast(cast(d.Qty as int)as varchar(20)) Qty from tblLuggageMaster m,tblLuggageDetail d where d.LuggageID=m.ID and d.ThumbID=@thid and convert(char(11),d.Date,101)=convert(char(11),@chkin,101))
 select Luggage,Qty from ct1
 GO
 /****** Object:  StoredProcedure [dbo].[GetLuggageDetail]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetLuggageDetail](@thid nvarchar(50),@chkin datetime,@clbid nvarchar(50))
 AS
 with ct1(ThumID,Title,LuggageNo)
 as(select d.ThumbID,(case when m.LuggageTitle is null then 'No Luggage'else m.LuggageTitle end)+' : '+cast(cast((case when d.Qty is null then '0' else d.Qty end) as int)as varchar(20)) Title,d.LuggageNo from tblLuggageMaster m,tblLuggageDetail d where d.LuggageID=m.ID and d.ThumbID=@thid /*and convert(char(11),d.Date,101)=convert(char(11),@chkin,101)*/)
 
 select c.Title,c.LuggageNo from ct1 c,tblVisitors v where c.ThumID=v.SrNo
 GO
 /****** Object:  StoredProcedure [dbo].[GetList]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetList](@clbid nvarchar(50))
 AS
 Select HangerNo,isnull(LuggageNo,'NA') LuggageNo from tblVisitors where ReserveStatus=1 and ClubiD=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[GetHangerForPrint]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetHangerForPrint](@thid nvarchar(50),@hanno nvarchar(50),@date datetime,@clbid nvarchar(50))
 AS
 select convert(char(8),v.CheckInTime,108),isnull((v.price),0)-isnull((v.Dis),0) from tblVisitors v where convert(datetime,convert(char(11),v.CheckInTime,101),101)=convert(datetime,convert(char(11),@date,101),101) and ClubID=@clbid and v.SrNo=@thid and v.HangerNo=@hanno and v.CheckOutTime is not null
 GO
 /****** Object:  StoredProcedure [dbo].[GetFreeWardrobeReport]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetFreeWardrobeReport](@dt datetime,@dt2 datetime,@clbid nvarchar(50))--,@time1 datetime,@time2 datetime
 AS
 select v.HangerNo vHangerNo,convert(char(8),Convert(datetime,v.CheckInTime,0),108) vCheckIn,v.LuggageNo LuggageNo,isnull(v.Price,0) vBal,isnull(v.LuggagePrice,0) lPrice,convert(char(8),Convert(datetime,v.CheckOutTime,0),108) CheckOutTime,(case When v.FreeWardrobe=1 then 'Free Wardrobe' else 'Paid Wardrobe' end) FreeWard from tblVisitors v  where v.CheckInTime between @dt and @dt2 /*convert(datetime,convert(char(11),v.CheckInTime,101),101) between convert(datetime,convert(char(11),@dt,101),101) and convert(datetime,convert(char(11),@dt2,101),101)*/ and  v.FreeWardRobe=1 and ClubID=@clbid /*and convert(char(8),Convert(datetime,v.CheckInTime,0),108) between convert(char(8),Convert(datetime,@Time1,0),108) and convert(char(8),Convert(datetime,@Time2,0),108)*/ and v.ClubID=@clbid and (HL=0 or LL=0)
 GO
 /****** Object:  StoredProcedure [dbo].[GetFreeorPaid]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetFreeorPaid](@thid nvarchar(50),@chkin datetime,@clbid nvarchar(50))
 AS
 with ct1(ThumID,Status)
 as(select v.ThumbID,(case when FreeWardrobe = 1 then 'Free Wardrobe' else 'Paid Wardrobe' end) from tblvisitors v where v.SrNo=@thid and ClubID=@clbid /*and convert(char(11),v.checkintime,101)=convert(char(11),@chkin,101)*/)
 
 select c.Status from ct1 c
 GO
 /****** Object:  StoredProcedure [dbo].[GetAllLuggStatus]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetAllLuggStatus](@x nvarchar(50),@clbid nvarchar(50))
 AS
 --GetLuggageStatus
     Select isnull(SrNo,0) from tblVisitors where LuggageNo=@x and LuggLocked=1 and CheckIn=1 and ClubID=@clbid and LsrNo=(Select Max(SrNo) from tblLuggageCreation where ClubID=@clbid)
     --GetLuggageStatus2
     Select isnull(SrNo,0) from tblVisitors where LuggageNo=@x and LuggLocked=0  and CheckIN=1 and ClubID=@clbid and LsrNo=(Select Max(SrNo) from tblLuggageCreation where ClubID=@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[GetAllHangerStatus]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetAllHangerStatus](@x nvarchar(50),@clbid nvarchar(50))
     
 AS
     --GetHangerStatus
     Select isnull(SrNo,0) from tblVisitors where HangerNo=@x and Locked=1 and CheckIn=1 and ClubID=@clbid and HsrNo=(Select Max(SrNo) from tblHangersCreation where ClubID=@clbid)
     --GetHangerStatus2
     Select isnull(SrNo,0) from tblVisitors where HangerNo=@x and Locked=0 and CheckIn=1 and ClubID=@clbid and HsrNo=(Select Max(SrNo) from tblHangersCreation where ClubID=@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[GetAddress]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[GetAddress]
 AS
 Select * from tblAddress where Status=1
 GO
 /****** Object:  StoredProcedure [dbo].[FreeLuggage]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FreeLuggage](@sno int)
 AS
 Update tblVisitors set LuggageReserveStatus=0 where SrNo=@sno
 GO
 /****** Object:  StoredProcedure [dbo].[FillGridLuggagePrice]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FillGridLuggagePrice](@clbid nvarchar(50))
 AS
 Select isnull(sum(Luggage),0) [Luggage Qty],ForDate [Creation Date],isnull(Price,0) Price from tblLuggageCreation where SrNo=(Select Max(SrNo) from tblLuggageCreation where ClubID=@clbid) and ClubID=@clbid group by ForDate,Price
 GO
 /****** Object:  StoredProcedure [dbo].[FillGridHangerPrice]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FillGridHangerPrice](@clbid nvarchar(50))
 AS
 Select isnull(sum(Hangers),0) [Hanger Qty],ForDate [Creation Date],isnull(Price,0) Price,isnull(Discount,0) Discount from tblHangersCreation where Srno=(Select MAx(Srno) from tblHangersCreation where ClubID=@clbid) and ClubID=@clbid group by ForDate,Price,Discount
 GO
 /****** Object:  StoredProcedure [dbo].[FetchTotalHangersForFrmAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchTotalHangersForFrmAllHangers]
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
 declare @maxsrno int=0
 Select @maxsrno=Max(SrNo) from tblHangersCreation
     Select isnull(Hangers,0) from tblHangersCreation where SrNo=@maxsrno/*forDate=@date and ResetStatus=0*/
 GO
 /****** Object:  StoredProcedure [dbo].[FetchReservedLuggageForAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchReservedLuggageForAllHangers](@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
     Select SrNo,LuggageNo,LuggLocked from tblVisitors where LuggageReserveStatus=1 and CheckIn=1 and LuggageNo is not Null and ClubID=@clbid and LSrNo=(Select Max(SrNo) from tblLuggageCreation where ClubID=@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[FetchReservedHangerForAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchReservedHangerForAllHangers](@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
     Select SrNo,HangerNo,Locked from tblVisitors where ReserveStatus=1 and CheckIn=1 and HangerNo is not Null and ClubID=@clbid and HSrNo=(Select Max(SrNo) from tblHangersCreation where ClubID=@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[FetchOperator]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchOperator](@clbid nvarchar(50))
 
 AS
     Select * from tblOperatorsLogin where ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[FetchLuggagePriceForAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchLuggagePriceForAllHangers](@clbid nvarchar(50))
 AS
     declare @maxsrno int=0
 Select @maxsrno=Max(SrNo) from tblLuggageCreation Where ClubID=@clbid
 Select isnull(sum(Price),0) from tblLuggageCreation where SrNo=@maxsrno and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[FetchLuggageLooping]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 Create PROCEDURE [dbo].[FetchLuggageLooping](@clbid nvarchar(50))
 AS
 declare @srno int
 Select @srno=isnull(Max(SrNo),0) from tblLuggagecreation where ClubID=@clbid
 Select IsLooping from tblLuggageCreation where SrNo=@srno
 GO
 /****** Object:  StoredProcedure [dbo].[FetchHangersStatus]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchHangersStatus](@date Datetime,@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
 declare @maxsrno int=0
 declare @maxsrno1 int=0
 Select @maxsrno=Max(SrNo) from tblHangersCreation where ClubID=@clbid
 Select @maxsrno1=Max(SrNo) from tblLuggageCreation where ClubID=@clbid
 /*FetchTotalHangers*/
     Select isnull(sum(Hangers),0),isnull(sum(Price),0),isnull(sum(Discount),0) from tblHangersCreation where SrNo=@maxsrno and ClubID=@clbid-- ResetStatus=0
     
     /*FetchTotalLuggage*/
     Select isnull(sum(Luggage),0),isnull(sum(Price),0) from tblLuggageCreation where SrNo=@maxsrno1 and ClubID=@clbid
     
     /*FetchResearveHanger*/
     Select isnull(Count(*),0) from tblVisitors where ReserveStatus=1 and CheckIn=1 and HangerNo is not Null and ClubID=@clbid and HsrNo=@maxsrno
     
     /*FetchResearveLuggage*/
     Select isnull(Count(*),0) from tblVisitors where LuggageReserveStatus=1 and CheckIn=1 and LuggageNo is not null and ClubID=@clbid and LsrNo=@maxsrno1
     
     /*FetchHangerPrice*/
     Select isnull(sum(Price),0) from tblHangersCreation where SrNo=@maxsrno and ClubID=@clbid--ResetStatus=0
     
     /*FetchLuggagePrice*/
     Select isnull(sum(Price),0) from tblLuggageCreation where SrNo=@maxsrno1 and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[FetchHangerPriceForAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchHangerPriceForAllHangers](@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
 declare @maxsrno int=0
 Select @maxsrno=Max(SrNo) from tblHangersCreation Where ClubID=@clbid
 Select isnull(sum(Price),0) from tblHangersCreation where SrNo=@maxsrno and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[FetchHangerLooping]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchHangerLooping](@clbid nvarchar(50))
 AS
 declare @srno int
 Select @srno=isnull(Max(SrNo),0) from tblhangerscreation where ClubID=@clbid
 Select IsLooping from tblHangersCreation where SrNo=@srno
 GO
 /****** Object:  StoredProcedure [dbo].[FetchFreeLuggagesForAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchFreeLuggagesForAllHangers](@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
     declare @maxsrno int=0
     declare @i int =1
     declare @th int
     
     Select @maxsrno=Max(SrNo) from tblLuggageCreation where ClubID=@clbid
     
     Select @th=isnull(Luggage,0)  from tblLuggageCreation where SrNo=@maxsrno and ClubID=@clbid
     
     create table #temptable(LuggageNo int)
     while @i<=@th
     begin
     insert into #temptable values(@i)
     set @i=@i+1
     end
     
     Select LuggageNo FreeLuagges from #temptable where LuggageNo not in(Select right(LuggageNo,4) from tblVisitors where LuggageReserveStatus=1 and CheckIn=1 and LuggageNo is not Null and ClubiD=@clbid and LsrNo=@maxsrno)
 GO
 /****** Object:  StoredProcedure [dbo].[FetchFreeHangersForAllHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchFreeHangersForAllHangers](@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
     declare @maxsrno int=0
     declare @i int =1
     declare @th int
     
     Select @maxsrno=Max(SrNo) from tblHangersCreation where ClubID=@clbid
     
     Select @th=isnull(Hangers,0)  from tblHangersCreation where SrNo=@maxsrno and ClubID=@clbid
     
     create table #temptable(HangerNo int)
     while @i<=@th
     begin
     insert into #temptable values(@i)
     set @i=@i+1
     end
     
     Select HangerNo FreeHangers from #temptable where HangerNo not in(Select HangerNo from tblVisitors where ReserveStatus=1 and CheckIn=1 and HangerNo is not Null and ClubiD=@clbid and HSrNo=@maxsrno) order by HangerNo
 GO
 /****** Object:  StoredProcedure [dbo].[FetchFingersForCheckOut]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchFingersForCheckOut](@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
     select SrNo,ThumbId from tblVisitors where /*convert(char(11),CheckInTime,101)=convert(char(11),@chkin,101) and*/ CheckIn=1 and ClubID=@clbid and thumbid is not null and (LsrNo=(Select Max(SrNo) from tblLuggageCreation where ClubID=@clbid) or HsrNo=(Select Max(SrNo) from tblHangersCreation where ClubID=@clbid))
 GO
 /****** Object:  StoredProcedure [dbo].[FetchAddress]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[FetchAddress](@clbid nvarchar(50))
 AS
     Select * from tblAddress where ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[DeleteData]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[DeleteData]
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
     delete from tblVisitors
 GO
 /****** Object:  StoredProcedure [dbo].[DeleteAllRecord]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[DeleteAllRecord]
 AS
 --Delete from tblHangersCreation
 
 --Delete from tblLuggageCreation
 
 Delete From tblVisitors 
 
 --Delete from tblWardrobe
 
 --Delete From tblOperatorsLogin
 
 Delete from tblLog
 
 --Delete from tblAddress
 
 --Delete from tblActivation
 GO
 /****** Object:  StoredProcedure [dbo].[DailyHangerRevenueReport]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[DailyHangerRevenueReport](@dt datetime,@dt2 datetime,@clbid nvarchar(50))--@time1 datetime,@time2 datetime,
 AS
 Select HangerNo vHangerNo,convert(char(8),Convert(datetime,v.CheckInTime,0),108) vCheckIn,isnull(Price,0) VBal,(case When v.FreeWardrobe=1 then 'Free Wardrobe' else 'Paid Wardrobe' end) FreeWard,v.LuggageNo,isnull(v.LuggagePrice,0) lPrice,convert(char(8),Convert(datetime,v.CheckOutTime,0),108) CheckOutTime from tblVisitors v where v.CheckInTime between @dt and @dt2/*convert(datetime,convert(char(11),v.CheckInTime,101),101) between convert(datetime,convert(char(11),@dt,101),101) and convert(datetime,convert(char(11),@dt2,101),101) and convert(char(8),Convert(datetime,v.CheckInTime,0),108) between convert(char(8),Convert(datetime,@Time1,0),108) and convert(char(8),Convert(datetime,@Time2,0),108)*/ and v.ClubID=@clbid and HangerNo is not null and HL=0 and v.FreeWardrobe=0
 GO
 /****** Object:  StoredProcedure [dbo].[CrIncomeFromLuggage]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CrIncomeFromLuggage](@date datetime,@date2 datetime,@clbid nvarchar(50))--,@time1 datetime,@time2 datetime
 AS
 Select HangerNo vHangerNo,convert(char(8),Convert(datetime,v.CheckInTime,0),108) vCheckIn,isnull(Price,0) VBal,(case When v.FreeWardrobe=1 then 'Free Wardrobe' else 'Paid Wardrobe' end) FreeWard,v.LuggageNo,isnull(v.LuggagePrice,0) lPrice,convert(char(8),Convert(datetime,v.CheckOutTime,0),108) CheckOutTime from tblVisitors v where v.CheckInTime between @date and @date2 /*convert(datetime,convert(char(11),v.CheckInTime,101),101) between convert(datetime,convert(char(11),@date,101),101) and convert(datetime,convert(char(11),@date2,101),101)*/ and ClubID=@clbid and LuggageNo is not null /*and convert(char(8),Convert(datetime,v.CheckInTime,0),108) between convert(char(8),Convert(datetime,@Time1,0),108) and convert(char(8),Convert(datetime,@Time2,0),108)*/ and v.ClubID=@clbid and LL=0 and v.FreeWardrobe=0
 GO
 /****** Object:  StoredProcedure [dbo].[CrFetchLog]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CrFetchLog](@date datetime,@date2 datetime,@clbid nvarchar(50))--,@time1 datetime,@time2 datetime
 AS
 
 Select SrNo,UserName,FormName,ActionTaken,ActionDate,convert(char(8),Convert(datetime,ActionTime,0),108) ActionTime,ClubID,@date FromDate,@date2 ToDate from tblLog where ActionDate between @date and @date2/*convert(datetime,convert(char(11),ActionDate,101),101) between convert(datetime,convert(char(11),@date,101),101) and convert(datetime,convert(char(11),@date2,101),101) and convert(char(8),Convert(datetime,ActionTime,0),108) between convert(char(8),Convert(datetime,@Time1,0),108) and convert(char(8),Convert(datetime,@Time2,0),108)*/ and ClubID=@clbid
 GO
 /****** Object:  StoredProcedure [dbo].[CreateOperator]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CreateOperator](@srno int,@opname nvarchar(50),@oppwd nvarchar(50),@date datetime,@msrno int,@isactive bit,@blnnew bit,@clbid nvarchar(50))
 AS
 declare @sr int
 if @blnnew=1
 begin
 if not exists (select OperatorName,OperatorPassword from tblOperatorsLogin where OperatorName=@opname and OperatorPassword=@oppwd and ClubID=@clbid)
 begin
 Select @sr=isnull(Max(SrNo),0)+1 from tblOperatorsLogin
 insert into tblOperatorsLogin values(@sr,@opname,@oppwd,@date,@msrno,@isactive,@clbid)
 return 1
 end
 else 
 return 7
 end
 else
 begin
 if exists (select OperatorName,OperatorPassword from tblOperatorsLogin where Srno=@srno and ClubID=@clbid)
 begin
 update  tblOperatorsLogin set OperatorName=@opname,OperatorPassword=@oppwd,ManagerSrNo=@msrno,IsActive=@isactive where SrNo=@srno and ClubID=@clbid
 return 2
 end
 else
 return 9
 end
 GO
 /****** Object:  StoredProcedure [dbo].[CreateLuggageNumber]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CreateLuggageNumber](@luggage int,@fordate datetime,@msrno int,@reset bit,@clbid nvarchar(50))
     
 AS
 declare @r bit
 declare @srno int
 declare @sr int
 declare @price decimal(18,2)
     
     begin
     select @sr=isnull(Max(SrNo),0)+1 from tblLuggageCreation where ClubID=@clbid
     insert into tblLuggageCreation(SrNo,Luggage,ForDate,ManagerSrNo,ResetStatus,Price,ClubID,IsLooping) values(@sr,@luggage,@fordate,@msrno,@reset,0,@clbid,0)
     update tblvisitors set LuggageReserveStatus=0 where ClubID=@clbid and CheckIn=1
     Select @srno=isnull(Max(SrNo),0) from tblLuggageCreation Where ClubID=@clbid
     Select @price=price from tblLuggageCreation where SrNo=@srno-1 and ClubID=@clbid
     update tblLuggageCreation set Price=@price where SrNo=@srno and ClubID=@clbid
     Update tblLuggageCreation set ResetStatus=1 where SrNo<>@srno and ClubID=@clbid
     end
 GO
 /****** Object:  StoredProcedure [dbo].[CreateLog]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CreateLog](@uname nvarchar(MAX),@frmname nvarchar(MAX),@action nvarchar(MAX),@actdate datetime,@acttime nvarchar(MAX),@clbid nvarchar(50))
 AS
 declare @srno int
 Select @srno=isnull(Max(SrNo),0)+1 from tblLog
 insert into tblLog values(@srno,@uname,@frmname,@action,@actdate,@acttime,@clbid)
 GO
 /****** Object:  StoredProcedure [dbo].[CreateHangers]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CreateHangers](@hangers int,@shift nvarchar(20),@fordate datetime,@msrno int,@reset bit,@clbid nvarchar(50))
     
 AS
 declare @r bit
 declare @srno int
 declare @sr int
 declare @price decimal(18,2)
     begin
     select @Sr=isnull(Max(SrNo),0)+1 from tblHangersCreation where ClubID=@clbid
     insert into tblHangersCreation(SrNo,Hangers,Shift,ForDate,ManagerSrNo,ResetStatus,Price,Discount,ClubID,IsLooping) values(@sr,@hangers,@shift,@fordate,@msrno,@reset,0,0,@clbid,0)
     Select @srno=isnull(Max(SrNo),0) from tblHangersCreation where ClubID=@clbid
     Select @price=price from tblHangersCreation where SrNo=@srno-1 and ClubID=@clbid
     update tblHangersCreation set Price=@price where SrNo=@srno and ClubID=@clbid
     Update tblHangersCreation set ResetStatus=1 where SrNo<>@srno and ClubID=@clbid
     end
 GO
 /****** Object:  StoredProcedure [dbo].[CheckOut]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckOut](@time datetime,@srno int,@clbid nvarchar(50))
     /*
     (
     @parameter1 int = 5,
     @parameter2 datatype OUTPUT
     )
     */
 AS
 declare @r int
 set @r=0
 if exists(select srno from tblvisitors where SrNo=@srno and ClubID=@clbid ) --and convert(datetime,convert(char(11),CheckInTime,101),101)=convert(datetime,convert(char(11),@time,101),101))
 begin
 select @r=Isnull(HangerNo,0) from tblvisitors where SrNo=@srno and ClubID=@clbid-- and convert(datetime,convert(char(11),CheckInTime,101),101)=convert(datetime,convert(char(11),@time,101),101)
     Update tblVisitors set CheckIn=0,CheckOutTime=@time,LuggageReserveStatus=0 where /*convert(datetime,convert(char(11),CheckInTime,101),101)=convert(datetime,convert(char(11),@time,101),101) and*/  SrNo=@srno and ClubID=@clbid
     return @r
     end
     else
     return -1
 GO
 /****** Object:  StoredProcedure [dbo].[CheckOperator2]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 Create PROCEDURE [dbo].[CheckOperator2](@pk nvarchar(16))
     
 AS
 Declare @ue int
 
 if exists(Select * from tblActivation where ClubID=@pk)
 begin
 set @ue=1
 return @ue
 end
 else
 Begin
 set @ue=0
 return @ue
 End
 GO
 /****** Object:  StoredProcedure [dbo].[CheckOperator]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckOperator](@uname nvarchar(50),@pwd nvarchar(50),@clbid nvarchar(50))
     
 AS
     Declare @ue int
     
 if Exists(select OperatorName from tblOperatorsLogin where OperatorName=@uname and OperatorPassword=@pwd and Isactive=1 and ClubiD=@clbid)
 Begin
 set @ue=1
 return @ue
 End
 else
 Begin
 set @ue=0
 return @ue
 End
 GO
 /****** Object:  StoredProcedure [dbo].[CheckManager]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckManager](@uname nvarchar(50),@pwd nvarchar(50))
     
 AS
     Declare @ue int
     
 if Exists(select ManagerName from tblManagerLogin where ManagerName=@uname and ManagerPwd=@pwd)
 Begin
 set @ue=1
 return @ue
 End
 else
 Begin
 set @ue=0
 return @ue
 End
 GO
 /****** Object:  StoredProcedure [dbo].[CheckLuggageLockStatus]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckLuggageLockStatus](@lno nvarchar(50),@date datetime,@shift nvarchar(20),@clbid nvarchar(50))
 
 AS
 declare @x int
     if exists (Select LuggageNo from tblVisitors where LuggageNo=@lno and ClubID=@clbid and Shift=@shift and LuggageNo is not Null and LuggLocked=1 and LsrNo=(Select Max(SrNo) from tblLuggageCreation))
     begin
      Select @x=SrNo from tblVisitors where LuggageNo=@lno and ClubID=@clbid and Shift=@shift and LuggageNo is not Null and LuggageReserveStatus=1 and LuggLocked=1 and LsrNo=(Select Max(SrNo) from tblLuggageCreation)
     return @x
     end
     else
     return 0
 GO
 /****** Object:  StoredProcedure [dbo].[CheckLuggageByHangerNo]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckLuggageByHangerNo](@hno nvarchar(50),@clbid nvarchar(50))
 AS
 declare @status bit
 Select @status=LuggageReserveStatus from tblVisitors where HangerNo=@hNo and CheckIn=1 and ReserveStatus=1 and ClubID=@clbid
 if @status=1
 return 0
 else
 return 1
 GO
 /****** Object:  StoredProcedure [dbo].[CheckLuggage(dbo)]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[CheckLuggage(dbo)](@lno nvarchar(50),@date datetime,@shift nvarchar(20),@clbid nvarchar(50))
 
 AS
 declare @x int
     if exists (Select LuggageNo from tblVisitors where LuggageNo=@lno and ClubID=@clbid and Shift=@shift and LuggageNo is not Null and LsrNo=(Select Max(SrNo) from tblLuggageCreation))
     begin
      Select @x=SrNo from tblVisitors where LuggageNo=@lno and ClubID=@clbid and Shift=@shift and LuggageNo is not Null and LuggageReserveStatus=1 and LsrNo=(Select Max(SrNo) from tblLuggageCreation)
     return @x
     end
     else
     return 0
 GO
 /****** Object:  StoredProcedure [dbo].[CheckLuggage]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckLuggage](@clbid nvarchar(50))
 AS
 Select isnull(count(LuggageNo),0) from tblVisitors where LuggageReserveStatus=1 and ClubID=@clbid and LsrNo=(Select Max(SrNo) from tblLuggageCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[CheckHangerLockStatus]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckHangerLockStatus](@hno nvarchar(50),@date datetime,@shift nvarchar(20),@clbid nvarchar(40))
 AS
 declare @x int
     if exists (Select HangerNo from tblVisitors where HangerNo=@hno and ClubID=@clbid and Shift=@shift and CheckIn=1 and Locked=1 and HsrNo=(Select Max(SrNo) from tblHangersCreation))
     begin
      Select @x=SrNo from tblVisitors where HangerNo=@hno and ClubID=@clbid and Shift=@shift and CheckIn=1 and Locked=1 and HsrNo=(Select Max(SrNo) from tblHangersCreation)
     return @x
     end
     else
     return 0
 GO
 /****** Object:  StoredProcedure [dbo].[CheckHanger(dbo)]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:        <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:    <Description,,>
 -- =============================================
 CREATE PROCEDURE [dbo].[CheckHanger(dbo)](@hno nvarchar(50),@date datetime,@shift nvarchar(20),@clbid nvarchar(50))
 AS
     declare @x int
     if exists (Select HangerNo from tblVisitors where HangerNo=@hno and ClubID=@clbid and Shift=@shift and CheckIn=1 and HsrNo=(Select Max(SrNo) from tblHangersCreation))
     begin
      Select @x=SrNo from tblVisitors where HangerNo=@hno and Shift=@shift and CheckIn=1 and ClubID=@clbid and HsrNo=(Select Max(SrNo) from tblHangersCreation)
     return @x
     end
     else
     return 0
 GO
 /****** Object:  StoredProcedure [dbo].[CheckHanger]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckHanger](@clbid nvarchar(50))
 AS
 Select isnull(count(HangerNo),0) from tblVisitors where CheckIn=1 and ClubID=@clbid and HsrNo=(Select Max(SrNo) from tblHangersCreation)
 GO
 /****** Object:  StoredProcedure [dbo].[CheckAdmin]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckAdmin](@uname nvarchar(50),@pwd nvarchar(50),@pk nvarchar(16))
     
 AS
 Declare @ue int
 if exists(Select UserName from tblActivation where UserName=@uname and Password=@pwd and ClubID=@pk)
 begin
 set @ue=1
 return @ue
 end
 else
 Begin
 set @ue=0
 return @ue
 End
 GO
 /****** Object:  StoredProcedure [dbo].[CheckActivation]    Script Date: 03/25/2015 05:40:36 ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[CheckActivation](@pk nvarchar(16))
 AS
 declare @expdate datetime 
 
 declare @todaydate datetime 
 
 declare @actstatus bit
 
 set @todaydate=GetDate()
 
 Select @expdate=ExpiryDate,@actstatus=ActiveStatus from tblActivation where ClubID=@pk
 
 if(@actstatus=1)
 begin
   if(convert(datetime,convert(char(11),@expDate,101),101)>convert(datetime,convert(char(11),@todaydate,101),101) or convert   (datetime,convert(char(11),@expDate,101),101)=convert(datetime,convert(char(11),@todaydate,101),101)) 
     begin
       return 1
     end
   else
       begin
       return 9
     end
 end
 else
  begin
   return 8
  end
 GO
1 svar postet i denne tråd vises herunder
0 indlæg har modtaget i alt 0 karma
Sorter efter stemmer Sorter efter dato
Bruger #17081 @ 30.03.15 21:19
1.258
Hvad får du af fejl?
Execute problemmer

Karma barometer (30 dage)

Modtaget

Givet

Favorit hos
Forum tråde
Artikler