BMSYSTEM.sql

--create database BMSYSTEM

USE [BMSYSTEM]
GO
/****** Object:  Table [dbo].[DEPART_INFO]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DEPART_INFO](
	[DPID] [int] IDENTITY(1,1) NOT NULL,
	[DPNAME] [varchar](50) NULL,
	[DPPLACE] [varchar](200) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[DPPCMAC] [varchar](50) NULL,
	[ADDDATE] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[DPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BIKE_BORROW_KIND]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BIKE_BORROW_KIND](
	[BKID] [int] IDENTITY(1,1) NOT NULL,
	[BBKNAME] [varchar](50) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[BKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DEPART_YEAR_FORM]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DEPART_YEAR_FORM](
	[DYID] [int] IDENTITY(1,1) NOT NULL,
	[DPID] [int] NULL,
	[VIPCOST] [money] NOT NULL,
	[VIPNOCOST] [money] NOT NULL,
	[TIME] [datetime] NOT NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[NewVipIncome] [money] NOT NULL,
	[VipCharge] [money] NOT NULL,
	[VipNoCharge] [money] NOT NULL,
	[VipBalance] [money] NOT NULL,
	[VipNoBalance] [money] NOT NULL,
	[Total] [money] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[DYID] 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].[DEPART_MONTH_FORM]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DEPART_MONTH_FORM](
	[DMID] [int] IDENTITY(1,1) NOT NULL,
	[DPID] [int] NOT NULL,
	[VIPCOST] [money] NOT NULL,
	[VIPNOCOST] [money] NOT NULL,
	[TIME] [datetime] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[NewVipIncome] [money] NOT NULL,
	[VipCharge] [money] NOT NULL,
	[VipNoCharge] [money] NOT NULL,
	[VipBalance] [money] NOT NULL,
	[VipNoBalance] [money] NOT NULL,
	[Total] [money] NOT NULL,
 CONSTRAINT [PK_DEPART_MONTH_FORM] PRIMARY KEY CLUSTERED 
(
	[DMID] 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].[EXCEPTION_INFO]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EXCEPTION_INFO](
	[EXID] [int] IDENTITY(1,1) NOT NULL,
	[EXCONTENT] [varchar](max) NULL,
	[ECFUNC] [varchar](50) NULL,
	[EXTIME] [datetime] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[EXID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[COST_RULE_INFO]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COST_RULE_INFO](
	[CRID] [int] IDENTITY(1,1) NOT NULL,
	[CRNAME] [varchar](50) NULL,
	[COSTPH] [money] NULL,
	[COSTTIME] [int] NULL,
	[CRKID] [int] NULL,
	[COSTBEGIN] [money] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[CONTIME] [int] NULL,
	[LVID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[CRID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[BIKE_RETURN_INFO]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BIKE_RETURN_INFO](
	[RBKID] [int] IDENTITY(1,1) NOT NULL,
	[BBKID] [int] NULL,
	[RBKTIME] [datetime] NULL,
	[VIPID] [int] NULL,
	[DPID] [int] NULL,
	[BUCOST] [money] NOT NULL,
	[BUTIME] [datetime] NULL,
	[BRNUMBER] [int] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[ISVIP] [bit] NULL,
	[USERID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[RBKID] 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].[LOG_INFO]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_INFO](
	[LOGID] [int] IDENTITY(1,1) NOT NULL,
	[USERID] [int] NULL,
	[CONTENT] [varchar](200) NULL,
	[LOGTIME] [datetime] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[DPID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[LOGID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MONTH_COST_RULE]    Script Date: 09/24/2015 19:14:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MONTH_COST_RULE](
	[CRID] [int] IDENTITY(1,1) NOT NULL,
	[CRNAME] [varchar](50) NOT NULL,
	[COSTPH] [money] NULL,
	[COSTTIME] [time](7) NULL,
	[COSTBEGIN] [money] NULL,
	[ISDELETE] [bit] NOT NULL,
	[MARK] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_MONTH_COST_RULE] PRIMARY KEY CLUSTERED 
(
	[CRID] 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].[MONTH_COST]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[MONTH_COST]
@BBKTIME DATETIME,
@RBKTIME DATETIME,
@COSTTIME INT,--每月扣费时间
@COSTPH INT,--每天扣费金额
@CONTIME INT,--起始金额包含时间是几天
@COSTBEGIN MONEY,--起始扣费金额
@BRNUMBER INT,--还车辆数
@USEMONEY MONEY OUTPUT,
@USETIME DATETIME OUTPUT
AS
BEGIN
DECLARE @TIMESPAN DATETIME
DECLARE @HOURSPAN INT
DECLARE @DAYSPAN INT
DECLARE @HOURUSER INT
SELECT @DAYSPAN =DATEDIFF(DAY,@BBKTIME,@RBKTIME)--后面的时间减去前面的时间只算小时
 SET @USETIME='1900-1-1 00:00:00'
 SET @HOURUSER=DATEDIFF(HOUR,@BBKTIME,@RBKTIME)
 SET @USETIME=DATEADD(DAY,@HOURUSER/24,@USETIME)--1900-1-1 00:00:00时间为起始算出租的天数加到起始时间上
 SET @USETIME=DATEADD(HOUR,@HOURUSER%24,@USETIME)--上面的时间为准，算出租的小时数加到时间上
IF(@DAYSPAN<@CONTIME)
SET @USEMONEY=@COSTBEGIN*@BRNUMBER
ELSE
SELECT @BBKTIME= DATEADD(DAY,@COSTBEGIN,@BBKTIME)--将起租日期延后使计算时间除去已经包含的天数
SELECT @DAYSPAN=DATEDIFF(DAY,@BBKTIME,@RBKTIME)--算出天数差
SELECT @BBKTIME=DATEADD(DAY,@DAYSPAN,@BBKTIME)--为了算小时差将时间加到同一天
SELECT @HOURSPAN=DATEDIFF(HOUR,@BBKTIME,@RBKTIME)--算出小时差
IF(@HOURSPAN<@COSTTIME)--看小时差是否小于规定时间
BEGIN
SET @USEMONEY=(@COSTBEGIN+(@COSTPH*@DAYSPAN))*@BRNUMBER--如果小于那么当天不算
END
ELSE
BEGIN
SET @USEMONEY=(@COSTBEGIN+(@COSTPH*(@DAYSPAN+1)))*@BRNUMBER--如果不小于那当天也算
END
SELECT @USEMONEY,@USETIME
END
GO
/****** Object:  Table [dbo].[NIGHT_COST_RULE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NIGHT_COST_RULE](
	[ID] [int] NOT NULL,
	[VIPCOST] [int] NOT NULL,
	[BEGINTIME] [int] NOT NULL,
	[ENDTIME] [int] NOT NULL,
	[ISDELET] [bit] NOT NULL,
	[MARK] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[RULE_KIND_RELATION_UPDATE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.1
-- Description:	修改租车类型与扣费规则的关系
-- =============================================
CREATE PROCEDURE [dbo].[RULE_KIND_RELATION_UPDATE]
	@RKRID INT,
	@LVID INT,
	@BRKID INT,
	@RUID INT
AS
BEGIN
	UPDATE RULE_KIND_RELATION SET LVID=@LVID,BRKID=@BRKID,RUID=@RUID WHERE BRKID=@BRKID
END
GO
/****** Object:  StoredProcedure [dbo].[RULE_KIND_RELATION_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.1
-- Description:	根据ID获取租车类型与扣费规则之间的关系
-- =============================================
CREATE PROCEDURE [dbo].[RULE_KIND_RELATION_SELECT_BY_ID]
	@RKRID INT
AS
BEGIN
	SELECT RKRID,LVID,BRKID,RUID,ISDELETE FROM RULE_KIND_RELATION WHERE RKRID=@RKRID
END
GO
/****** Object:  StoredProcedure [dbo].[RULE_KIND_RELATION_SELECT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.1
-- Description:	获取所有租车类型与扣费规则之间的关系
-- =============================================
CREATE PROCEDURE [dbo].[RULE_KIND_RELATION_SELECT]
	
AS
BEGIN
	SELECT RKRID,LVID,BRKID,RUID,ISDELETE FROM RULE_KIND_RELATION WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[RULE_KIND_RELATION_INSERT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.1
-- Description:	添加租车类型与扣费规则关系
-- =============================================
CREATE PROCEDURE [dbo].[RULE_KIND_RELATION_INSERT]
	@LVID INT,
	@BRKID INT,
	@RUID INT
AS
BEGIN
	INSERT INTO RULE_KIND_RELATION (LVID,BRKID,RUID) VALUES (@LVID,@BRKID,@RUID)
END
GO
/****** Object:  StoredProcedure [dbo].[RULE_KIND_RELATION_DELETE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.1
-- Description:	删除租车类型与扣费规则之间的关系
-- =============================================
CREATE PROCEDURE [dbo].[RULE_KIND_RELATION_DELETE]
	@RKRID INT
AS
BEGIN
	UPDATE RULE_KIND_RELATION SET ISDELETE='1' WHERE RKRID=@RKRID
END
GO
/****** Object:  Table [dbo].[USER_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[USER_INFO](
	[USERID] [int] IDENTITY(1,1) NOT NULL,
	[USERNAME] [varchar](50) NULL,
	[USERPWD] [varchar](50) NULL,
	[USERCONTROLPWD] [varchar](50) NULL,
	[USERPHONE] [varchar](50) NULL,
	[USERCARD] [varchar](200) NULL,
	[USERPHOTO] [image] NULL,
	[DPID] [int] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[USERID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_BORROW_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VIP_BORROW_INFO](
	[BBKID] [int] IDENTITY(1,1) NOT NULL,
	[VIPID] [int] NULL,
	[BBKTIME] [datetime] NULL,
	[DPID] [int] NULL,
	[BBKNUM] [int] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [varchar](1000) NULL,
	[RKRID] [int] NULL,
	[ISRETURN] [bit] NULL,
	[LEFTNUM] [int] NULL,
	[USERID] [int] NULL,
	[ISNIGHT] [bit] NULL,
 CONSTRAINT [PK__VIP_BORR__7F82637B1A14E395] PRIMARY KEY CLUSTERED 
(
	[BBKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VIP_INFO](
	[VIPID] [int] IDENTITY(1,1) NOT NULL,
	[VIPNUMBER] [varchar](200) NULL,
	[VIPNAME] [varchar](50) NULL,
	[VIPSEX] [bit] NULL,
	[VIPPHOTO] [image] NULL,
	[VIPLEVELID] [int] NULL,
	[VIPPWD] [varchar](50) NULL,
	[VIPPHONE] [varchar](50) NULL,
	[VIPCARD] [varchar](200) NULL,
	[VIPBIRTHDAY] [date] NULL,
	[VIPADDRESS] [varchar](200) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [varchar](1000) NULL,
	[DPID] [int] NULL,
	[ADDDATE] [datetime] NULL,
	[USERID] [int] NULL,
 CONSTRAINT [PK__VIP_INFO__E98EA37B1DE57479] PRIMARY KEY CLUSTERED 
(
	[VIPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[U_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[U_INFO](
	[UID] [int] IDENTITY(1,1) NOT NULL,
	[UINFO] [varchar](50) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[ADDDATE] [datetime] NULL,
	[ISADMIN] [bit] NULL,
	[DPID] [int] NULL,
	[IDENTIFY] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[UID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_LEVEL_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VIP_LEVEL_INFO](
	[LVID] [int] IDENTITY(1,1) NOT NULL,
	[LVNAME] [varchar](50) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[ISVIP] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[LVID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_MONEY_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VIP_MONEY_INFO](
	[VMID] [int] IDENTITY(1,1) NOT NULL,
	[VIPID] [int] NULL,
	[VMBALANCE] [money] NOT NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
	[ISVIP] [bit] NULL,
	[INTEGRAL] [varchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[VMID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_NO_BORROW_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VIP_NO_BORROW_INFO](
	[BBKID] [int] IDENTITY(1,1) NOT NULL,
	[VIPID] [int] NULL,
	[DPID] [int] NULL,
	[RKRID] [int] NULL,
	[VNNAME] [varchar](50) NULL,
	[VNPHONE] [varchar](50) NULL,
	[VNCARD] [varchar](200) NULL,
	[VNNUMBER] [int] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [varchar](1000) NULL,
	[BBKTIME] [datetime] NULL,
	[ISRETURN] [bit] NULL,
	[LEFTNUM] [int] NULL,
	[USERID] [int] NULL,
	[ISNIGHT] [bit] NULL,
 CONSTRAINT [PK__VIP_NO_B__7F82637B29572725] PRIMARY KEY CLUSTERED 
(
	[BBKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_NO_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VIP_NO_INFO](
	[VIPID] [int] IDENTITY(1,1) NOT NULL,
	[VIPNUMBER] [varchar](200) NULL,
	[VIPNAME] [varchar](50) NULL,
	[VIPPWD] [varchar](50) NULL,
	[VIPPHONE] [varchar](50) NULL,
	[VIPCARD] [varchar](200) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [varchar](1000) NULL,
	[DPID] [int] NULL,
	[LVID] [int] NULL,
	[ADDDATE] [datetime] NULL,
 CONSTRAINT [PK__VIP_NO_I__E98EA37B25869641] PRIMARY KEY CLUSTERED 
(
	[VIPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VIP_YEAR_FORM]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VIP_YEAR_FORM](
	[VYID] [int] IDENTITY(1,1) NOT NULL,
	[VIPID] [int] NULL,
	[VYCOST] [money] NOT NULL,
	[VYTIMES] [int] NULL,
	[TIME] [datetime] NOT NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
 CONSTRAINT [PK_VIP_YEAR_FORM] PRIMARY KEY CLUSTERED 
(
	[VYID] 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].[WHOLE_CONFIG]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WHOLE_CONFIG](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MEANING] [varchar](200) NULL,
	[VALUE] [varchar](200) NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [varchar](50) NULL,
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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[YEAR_COST_RULE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[YEAR_COST_RULE](
	[CRID] [int] IDENTITY(1,1) NOT NULL,
	[CRNAME] [varchar](50) NOT NULL,
	[COSTPH] [money] NULL,
	[COSTTIME] [time](7) NULL,
	[COSTBEGIN] [money] NULL,
	[ISDELETE] [bit] NOT NULL,
	[MARK] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_YEAR_COST_RULE] PRIMARY KEY CLUSTERED 
(
	[CRID] 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].[YEAR_COST]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[YEAR_COST]
@BBKTIME DATETIME,
@RBKTIME DATETIME,
@COSTTIME INT,--每小时起扣时间
@COSTPH MONEY,--每次扣费金额
@CONTIME INT,--起始价包含时间小时数
@COSTBEGIN MONEY,--每小时扣费金额
@BRNUMBER INT,--还车辆数
@USEMONEY MONEY OUTPUT,
@USETIME DATETIME OUTPUT
AS
BEGIN
 DECLARE @TIMESTAP DATETIME--临时时间
 DECLARE @MONTHTENP INT
 DECLARE @DAYTEMP INT 
 DECLARE @DAYUSER INT
 SELECT @MONTHTENP =DATEDIFF(MONTH,@BBKTIME,@RBKTIME)--后面的时间减去前面的时间只算YUE
 SET @USETIME='1900-1-1 00:00:00'
 SET @USETIME=DATEADD(MONTH,@MONTHTENP,@USETIME)
 SET @TIMESTAP=DATEADD(MONTH,@MONTHTENP,@BBKTIME)
 SET @DAYUSER=DATEDIFF(DAY,@TIMESTAP,@RBKTIME)
 IF(@DAYTEMP<0)
 BEGIN
 SET @TIMESTAP=DATEADD(MONTH,-1,@TIMESTAP)
 SET @USETIME=DATEADD(MONTH,-1,@USETIME)
 SET @DAYUSER=DATEDIFF(DAY,@TIMESTAP,@RBKTIME)
 SET @USETIME=DATEADD(DAY,@DAYUSER,@USETIME)
 END
 ELSE
 BEGIN
 SET @DAYUSER=DATEDIFF(DAY,@TIMESTAP,@RBKTIME)
 SET @USETIME=DATEADD(DAY,@DAYUSER,@USETIME)
 END
 IF(@MONTHTENP<@CONTIME)
 BEGIN
 SET @USEMONEY=@COSTBEGIN*@BRNUMBER 
 END
ELSE 
BEGIN
	SELECT @BBKTIME=DATEADD(MONTH,@CONTIME,@BBKTIME)--将起租时间延后起始价包含的时间然后再计算金额
	SELECT @MONTHTENP =DATEDIFF(MONTH,@BBKTIME,@RBKTIME)--后面的时间减去前面的时间只算月
	SELECT @BBKTIME=DATEADD(MONTH,@MONTHTENP,@BBKTIME)--将租车时间放到还车时间同一个月方便计算天数
	SELECT @DAYTEMP=DATEDIFF(DAY,@BBKTIME,@RBKTIME)--计算天数
IF(@DAYTEMP<@COSTTIME)
	BEGIN
	SET @USEMONEY=(@MONTHTENP*@COSTPH+@COSTBEGIN)*@BRNUMBER--算出花费
	END
 ELSE
	BEGIN
	SET @USEMONEY=((@MONTHTENP+1)*@COSTPH+@COSTBEGIN)*@BRNUMBER--算出花费
	END
	SELECT @USEMONEY,@USETIME
END
END
GO
/****** Object:  Table [dbo].[RECHARGE_INFO]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RECHARGE_INFO](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[VIPID] [int] NULL,
	[ISVIP] [bit] NULL,
	[CHARGETIME] [datetime] NULL,
	[BALANCE] [money] NULL,
	[CHARGEMONEY] [money] NULL,
	[DEPARTMENTID] [int] NULL,
	[USERID] [int] NULL,
	[ISDELETE] [bit] NULL,
	[MARK] [nvarchar](50) NULL,
 CONSTRAINT [PK__RECHARGE__3214EC271FCDBCEB] 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 = 1) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[YEAR_COST_RULE_UPDATE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	修改年租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[YEAR_COST_RULE_UPDATE] 
	@CRID int,
	@CRNAME varchar(50),
	@COSTPH money,
	@COSTTIME time,
	@COSTBEGIN money
AS
BEGIN
	UPDATE YEAR_COST_RULE SET CRNAME=@CRNAME,COSTPH=@COSTPH,COSTTIME=@COSTTIME,COSTBEGIN=@COSTBEGIN WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[YEAR_COST_RULE_SELECT_BY_CRID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	根据Id获取年租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[YEAR_COST_RULE_SELECT_BY_CRID]
	@CRID INT
AS
BEGIN
	SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN FROM YEAR_COST_RULE WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[YEAR_COST_RULE_SELECT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	获取所有年租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[YEAR_COST_RULE_SELECT]

AS
BEGIN
	SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN FROM YEAR_COST_RULE WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[YEAR_COST_RULE_INSERT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	添加年租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[YEAR_COST_RULE_INSERT]
	@CRNAME varchar(50),
	@COSTPH money,
	@COSTTIME time,
	@COSTBEGIN money
AS
BEGIN
	INSERT INTO YEAR_COST_RULE (CRNAME,COSTPH,COSTTIME,COSTBEGIN) VALUES (@CRNAME,@COSTPH,@COSTTIME,@COSTBEGIN)
END
GO
/****** Object:  StoredProcedure [dbo].[YEAR_COST_RULE_DELETE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	删除年租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[YEAR_COST_RULE_DELETE]
	@CRID int
AS
BEGIN
	UPDATE YEAR_COST_RULE SET ISDELETE='1' WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[RECHARGE_ADD]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[RECHARGE_ADD]
 @VIPID int,
 @ISVIP bit,
 @CHARGEMONEY money,
 @DEPARTMENTID int,
 @USERID int
 as

 begin
	INSERT INTO RECHARGE_INFO (VIPID,ISVIP,CHARGEMONEY,DEPARTMENTID,USERID) VALUES (@VIPID,@ISVIP,@CHARGEMONEY,@DEPARTMENTID,@USERID)
	update RECHARGE_INFO SET VIPID=1 where vipid=0
	END
GO
/****** Object:  StoredProcedure [dbo].[WHOLE_CONFIG_UPDATE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[WHOLE_CONFIG_UPDATE]
  @ID INT,
  @MEANING VARCHAR(200),
  @VALUE VARCHAR(200),
  @ISDELETE BIT
  AS
   BEGIN
   UPDATE WHOLE_CONFIG SET MEANING=@MEANING,VALUE=@VALUE,ISDELETE=@ISDELETE WHERE ID=@ID
   END
GO
/****** Object:  StoredProcedure [dbo].[WHOLE_CONFIG_SELECT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[WHOLE_CONFIG_SELECT]
  @ID INT
  AS
  BEGIN
  SELECT ID,MEANING,VALUE,ISDELETE FROM WHOLE_CONFIG WHERE ID=@ID AND ISDELETE!=1
  END
GO
/****** Object:  StoredProcedure [dbo].[VIPNOTNIGHTBORROW]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014-8-23
-- Description:	取消包夜功能
-- =============================================
CREATE PROCEDURE [dbo].[VIPNOTNIGHTBORROW]
@BBID INT,
@ISVIP BIT

AS
BEGIN
   --非会员
	IF @ISVIP =0
	BEGIN
	UPDATE VIP_NO_BORROW_INFO SET ISNIGHT=0 WHERE BBKID =@BBID
	END
	ELSE
	BEGIN
	UPDATE VIP_BORROW_INFO SET ISNIGHT =0 WHERE BBKID =@BBID 
	END
END
GO
/****** Object:  StoredProcedure [dbo].[VIPNIGHTBORROW]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	    郑传哲
-- Create date: 2014-08-23
-- Description:	vip租车转为包夜租车
-- =============================================
CREATE PROCEDURE [dbo].[VIPNIGHTBORROW]
@BBID INT,
@ISVIP BIT

AS
BEGIN
   --非会员
	IF @ISVIP =0
	BEGIN
	UPDATE VIP_NO_BORROW_INFO SET ISNIGHT=1 WHERE BBKID =@BBID
	END
	ELSE
	BEGIN
	UPDATE VIP_BORROW_INFO SET ISNIGHT =1 WHERE BBKID =@BBID 
	END
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_YEAR_FORM_SELECT_BY_TIME]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	根据时间段选出会员消费
-- =============================================
CREATE PROCEDURE [dbo].[VIP_YEAR_FORM_SELECT_BY_TIME]
	@BEGINTIME datetime,
	@ENDTIME datetime,
	@DPID int
AS
BEGIN
	--SELECT VYID,VIPID,VYCOST,VYTIMES,TIME,ISDELETE FROM VIP_YEAR_FORM WHERE TIME<@ENDTIME AND TIME>@BEGINTIME AND ISDELETE!=2
	SELECT VYID,V.VIPID,V.VIPNAME,VYCOST,VYTIMES,TIME,VYF.ISDELETE FROM VIP_YEAR_FORM VYF,DEPART_INFO D,VIP_INFO V WHERE D.DPID=V.VIPID AND V.VIPID=VYF.VIPID AND TIME<@ENDTIME AND TIME>@BEGINTIME AND VYF.ISDELETE=1 AND D.DPID=@DPID AND D.ISDELETE!=1 UNION 
	SELECT VYID,V.VIPID,V.VIPNAME,VYCOST,VYTIMES,TIME,VYF.ISDELETE FROM VIP_YEAR_FORM VYF,DEPART_INFO D,VIP_NO_INFO V WHERE  D.DPID=V.VIPID AND V.VIPID=VYF.VIPID AND TIME<@ENDTIME AND TIME>@BEGINTIME AND VYF.ISDELETE=0 AND D.DPID=@DPID AND D.ISDELETE!=1
	--SELECT vyf.VYID,vyf.VIPID,vyf.VYCOST,vyf.VYTIMES,vyf.TIME,vyf.ISDELETE FROM VIP_YEAR_FORM vyf,VIP_INFO v,DEPART_INFO d WHERE vyf.VIPID=v.VIPID AND v.DPID=d.DPID AND d.DPID=@DPID AND vyf.TIME<@ENDTIME AND vyf.TIME>@BEGINTIME AND vyf.ISDELETE!=2
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_YEAR_FORM_INSERT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_YEAR_FORM_INSERT]
--每天每个VIP一个记录
AS
 BEGIN
 --DECLARE @BEGIN DATETIME
 --DECLARE @END DATETIME
 
 declare @dayBegin datetime
 declare @dayEnd datetime
 set @dayBegin=DATEADD(HOUR ,-4,CONVERT(VARCHAR(10),GETDATE (),111))--当天开始时间
 set @dayEnd= DATEADD(HOUR ,20,CONVERT(VARCHAR(10),GETDATE (),111))--当天结束时间
 --SET @END=GETDATE()
 --SET @BEGIN=dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate()))--上个月一号
 --选择会员
 SELECT VIP_INFO.VIPID AS VIPID,ISNULL(SUM(BIKE_RETURN_INFO.BUCOST),0) AS COST,COUNT(*) AS TIMES INTO #TEMP FROM VIP_INFO LEFT JOIN BIKE_RETURN_INFO ON VIP_INFO.VIPID=BIKE_RETURN_INFO.VIPID AND BIKE_RETURN_INFO.ISVIP=1 AND VIP_INFO.ISDELETE!=1 AND BIKE_RETURN_INFO.RBKTIME>@dayBegin AND BIKE_RETURN_INFO.RBKTIME<@dayEnd GROUP BY VIP_INFO.VIPID 
 --插入报表中，会员用ISDELETE为1来表示
 INSERT INTO VIP_YEAR_FORM (VIPID,VYCOST,VYTIMES,TIME,ISDELETE) SELECT #TEMP.VIPID,#TEMP.COST,#TEMP.TIMES,getdate(),1 FROM #TEMP
 --选择非会员
 SELECT VIP_NO_INFO.VIPID AS VIPID,ISNULL(SUM(BIKE_RETURN_INFO.BUCOST),0) AS COST,COUNT(*) AS TIMES INTO #TEMPNO FROM VIP_NO_INFO LEFT JOIN BIKE_RETURN_INFO ON VIP_NO_INFO.VIPID=BIKE_RETURN_INFO.VIPID AND BIKE_RETURN_INFO.ISVIP=0 AND VIP_NO_INFO.ISDELETE!=1 AND BIKE_RETURN_INFO.RBKTIME>@dayBegin AND BIKE_RETURN_INFO.RBKTIME<@dayEnd GROUP BY VIP_NO_INFO.VIPID
 --插入报表，非会员用ISDELETE为0表示
 INSERT INTO VIP_YEAR_FORM (VIPID,VYCOST,VYTIMES,TIME,ISDELETE) SELECT #TEMPNO.VIPID,#TEMPNO.COST,#TEMPNO.TIMES,getdate(),0 FROM #TEMPNO
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_UPDATE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_UPDATE]
@VIPID INT,
@VIPNUMBER VARCHAR(200),
@VIPNAME VARCHAR(50),
@VIPSEX BIT,
@VIPPHOTO IMAGE,
@VIPLEVELID INT,
@VIPPWD VARCHAR(50),
@VIPPHONE VARCHAR(50),
@VIPCARD VARCHAR(200),
@VIPBIRTHDAY DATE,
@VIPADDRESS VARCHAR(200),
@DPID INT,
@VIPMARK VARCHAR(1000)
AS
BEGIN
UPDATE VIP_INFO SET MARK =@VIPMARK , VIPNUMBER=@VIPNUMBER,VIPNAME=@VIPNAME,VIPSEX=@VIPSEX,VIPPHOTO=@VIPPHOTO,VIPLEVELID=@VIPLEVELID,VIPPWD=@VIPPWD,VIPPHONE=@VIPPHONE,VIPBIRTHDAY=@VIPBIRTHDAY,VIPADDRESS=@VIPADDRESS,DPID=@DPID WHERE VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SUM_MONEY_BALANCE_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/8/6
-- Description:	取出会员总余额
-- =============================================
CREATE PROCEDURE [dbo].[VIP_SUM_MONEY_BALANCE_BY_DPID]
	@DPID INT
	AS
BEGIN
	 SELECT SUM(VMBALANCE) as [sum] FROM VIP_MONEY_INFO VM
	 INNER JOIN VIP_INFO VP ON VM.ISDELETE !=1 AND VM.VIPID =VP.VIPID AND VP.DPID=@DPID AND VM.ISVIP =1
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SUM_MONEY_BALANCE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/8/6
-- Description:	取出会员总余额
-- =============================================
CREATE PROCEDURE [dbo].[VIP_SUM_MONEY_BALANCE]
	
	AS
BEGIN
	 SELECT SUM(VMBALANCE) as [sum] FROM VIP_MONEY_INFO WHERE ISDELETE !=1 AND ISVIP =1
	
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT_NEW_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[VIP_SELECT_NEW_BY_DPID]
  @ADDDATE datetime,
  @DPID int,
 @beginTime datetime,
 @endTime datetime
  as
   begin
     
   SELECT VIPID,VIPNAME,VIPNUMBER,ADDDATE,USERID FROM VIP_INFO WHERE DPID=@DPID AND ADDDATE>@beginTime and ADDDATE<@endTime
   end
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT_BY_VIPNUMBER]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/7/16
-- Description:	根据会员卡号取出会员信息
-- =============================================
CREATE PROCEDURE  [dbo].[VIP_SELECT_BY_VIPNUMBER]
 @VIPNUMBER VARCHAR(50)
AS
BEGIN
	SELECT VIPID,VIPNUMBER,VIPNAME,VIPSEX,VIPLEVELID,VIPPHONE,VIPCARD,VIPBIRTHDAY,VIPADDRESS,DPID,VIPPWD,ADDDATE,MARK  FROM [VIP_INFO] WHERE VIPNUMBER =@VIPNUMBER AND ISDELETE =0
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT_BY_VIPNAME]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/8/8
-- Description:	根据会员名取出会员信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_SELECT_BY_VIPNAME]
@VIPNAME VARCHAR(100)

AS
BEGIN
	SELECT VIPID,VIPNUMBER,VIPNAME,VIPSEX,VIPLEVELID,VIPPHONE,VIPCARD,VIPBIRTHDAY,VIPADDRESS,VIPPWD,ADDDATE,MARK  FROM VIP_INFO WHERE (VIPNAME =@VIPNAME OR VIPNUMBER =@VIPNAME)  AND ISDELETE!=1 ORDER BY ADDDATE DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT_BY_ID_MAINPAGE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/10/9
-- Description:	主页根据VIPID取出会员信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_SELECT_BY_ID_MAINPAGE]
@VIPID INT

AS
BEGIN
	SELECT VIPNUMBER,VIPNAME,VIPADDRESS ,VIPPHONE   FROM VIP_INFO WHERE VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_SELECT_BY_ID]
@VIPID INT
AS
BEGIN
SELECT VIPID,VIPNUMBER,VIPNAME,VIPSEX,VIPLEVELID,VIPPHONE,VIPCARD,VIPBIRTHDAY,VIPADDRESS,DPID,VIPPWD,ADDDATE,MARK  FROM VIP_INFO WHERE VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_SELECT_BY_DPID]
 @DPID INT
 AS
 BEGIN
 SELECT VIPID,VIPNUMBER,VIPNAME,VIPSEX,VIPLEVELID,VIPPHONE,VIPCARD,VIPBIRTHDAY,VIPADDRESS,VIPPWD,ADDDATE ,MARK FROM VIP_INFO WHERE DPID=@DPID AND ISDELETE!=1 ORDER BY ADDDATE DESC
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_SELECT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_SELECT]
AS
BEGIN
SELECT VIPID,VIPNUMBER,VIPNAME,VIPSEX,VIPLEVELID,VIPPWD,VIPPHONE,VIPCARD,VIPBIRTHDAY,VIPADDRESS,DPID,ADDDATE FROM VIP_INFO WHERE ISDELETE!=1 ORDER BY ADDDATE DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_PHOTO_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/10/20
-- Description:	根据VIPID获得VIP照片
-- =============================================
CREATE PROCEDURE [dbo].[VIP_PHOTO_SELECT_BY_ID]
@VIPID INT

AS
BEGIN
	SELECT VIPPHOTO FROM VIP_INFO WHERE VIPID =@VIPID 
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NOT_SUM_MONEY_BALANCE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/8/7
-- Description:	取出非会员总余额
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NOT_SUM_MONEY_BALANCE]

AS
BEGIN
	 SELECT SUM(VMBALANCE) as [sum] FROM VIP_MONEY_INFO WHERE ISDELETE !=1 AND ISVIP =0
	
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_UPDATE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_UPDATE]
@VIPID INT,
@VIPNUMBER VARCHAR(200),
@VIPNAME VARCHAR(50),
@VIPPWD VARCHAR(50),
@VIPPHONE VARCHAR(50),
@VIPCARD VARCHAR(200),
@DPID INT,
@LVID INT,
@MARK VARCHAR(1000)
AS
BEGIN
UPDATE VIP_NO_INFO SET MARK =@MARK , VIPNUMBER=@VIPNUMBER,VIPNAME=@VIPNAME,VIPPWD=@VIPPWD,VIPPHONE=@VIPPHONE,VIPCARD=@VIPCARD,DPID=@DPID,LVID=@LVID WHERE VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_SUM_MONEY_BALANCE_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/8/6
-- Description:	取出会员总余额
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_SUM_MONEY_BALANCE_BY_DPID]
	@DPID INT
	AS
BEGIN
	 SELECT SUM(VMBALANCE ) as [sum] FROM VIP_MONEY_INFO VM
	 INNER JOIN VIP_NO_INFO VP ON VM.ISDELETE !=1 AND VM.VIPID =VP.VIPID AND VP.DPID=@DPID AND VM.ISVIP =0
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_SELECT_BY_VIPNUMBER]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.29
-- Description:	根据卡号获取非会员信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_SELECT_BY_VIPNUMBER]
	@VIPNUMBER VARCHAR(200)
AS
BEGIN
	SELECT VIPID,VIPNUMBER,VIPNAME,VIPPWD,VIPPHONE,VIPCARD,DPID,LVID,ADDDATE,MARK  FROM VIP_NO_INFO WHERE ISDELETE!='1' AND VIPNUMBER=@VIPNUMBER
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_SELECT_BY_VIPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_SELECT_BY_VIPID]
@VIPID INT
AS
BEGIN
SELECT VIPID,VIPNUMBER,VIPNAME,VIPPWD,VIPPHONE,VIPCARD,DPID,LVID,ADDDATE,MARK  FROM VIP_NO_INFO WHERE ISDELETE!='1' AND VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_SELECT_BY_NAME]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	  郑传哲
-- Create date: 2014/8/8
-- Description:	根据非会员名称取出非会员列表
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_SELECT_BY_NAME]
@VIPNAME VARCHAR(100)
AS
BEGIN
	SELECT VIPID,VIPNUMBER,VIPNAME,VIPPWD,VIPPHONE,VIPCARD,DPID,LVID,ADDDATE,MARK  FROM VIP_NO_INFO WHERE ISDELETE!='1' AND( VIPNAME =@VIPNAME OR VIPNUMBER =@VIPNAME )  ORDER BY ADDDATE DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_SELECT_BY_DPID]
@DPID INT
AS
BEGIN
SELECT VIPID,VIPNUMBER,VIPNAME,VIPPWD,VIPPHONE,VIPCARD,DPID,LVID,ADDDATE,MARK FROM VIP_NO_INFO WHERE ISDELETE!='1' AND DPID=@DPID ORDER BY ADDDATE DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_SELECT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_SELECT]
AS
BEGIN
SELECT VIPID,VIPNUMBER,VIPNAME,VIPPWD,VIPPHONE,VIPCARD,DPID,LVID,ADDDATE FROM VIP_NO_INFO WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_MARK_UPDATE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014-10-31
-- Description:	会员更改租车备注
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_MARK_UPDATE]
@BBID int,
@MARK VARCHAR(1000)

AS
BEGIN
	UPDATE VIP_NO_BORROW_INFO SET MARK =@MARK WHERE BBKID =@BBID 
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_INSERT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_INSERT]
 @VIPNUMBER VARCHAR(200),
 @VIPNAME VARCHAR(50),
 @VIPPWD VARCHAR(50),
 @VIPPHONE VARCHAR(50),
 @VIPCARD VARCHAR(200),
 @DPID INT,
 @LVID INT,
 @ADDDATE DATETIME,
@MARK VARCHAR(1000) 
 AS
 DECLARE @VIPID INT
 BEGIN
 INSERT INTO VIP_NO_INFO (VIPNUMBER,VIPNAME,VIPPWD,VIPPHONE,VIPCARD,DPID,LVID,ISDELETE,ADDDATE,MARK ) VALUES (@VIPNUMBER,@VIPNAME,@VIPPWD,@VIPPHONE,@VIPCARD,@DPID,@LVID,0,@ADDDATE,@MARK )
 SELECT @VIPID=@@IDENTITY
 INSERT INTO VIP_MONEY_INFO(VIPID,VMBALANCE,ISVIP,INTEGRAL) VALUES (@VIPID,0,0,0)
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_DELETE_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_DELETE_BY_DPID]
 @DPID INT
 AS
 BEGIN
 UPDATE VIP_NO_INFO SET ISDELETE=1 WHERE DPID=@DPID
 UPDATE BIKE_RETURN_INFO SET ISDELETE=1 WHERE BIKE_RETURN_INFO.VIPID IN (SELECT VIPID FROM VIP_NO_INFO WHERE DPID=@DPID) AND ISVIP=0
 UPDATE VIP_MONEY_INFO SET ISDELETE=1 WHERE VIP_MONEY_INFO.VIPID IN (SELECT VIPID FROM VIP_NO_INFO WHERE DPID=@DPID) AND ISVIP=0
 UPDATE VIP_NO_BORROW_INFO SET ISDELETE=1 WHERE VIP_NO_BORROW_INFO.VIPID IN (SELECT VIPID FROM VIP_NO_INFO WHERE DPID=@DPID)
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_DELETE]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	删除非会员
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_DELETE]
	@VIPID int
AS
BEGIN
	UPDATE VIP_NO_INFO SET ISDELETE='1' WHERE VIPID=@VIPID
	UPDATE VIP_MONEY_INFO SET ISDELETE=1 WHERE VIPID=@VIPID AND ISVIP=0
	UPDATE BIKE_RETURN_INFO SET ISDELETE=1 WHERE BIKE_RETURN_INFO.VIPID=@VIPID AND ISVIP=0
	UPDATE VIP_NO_BORROW_INFO SET ISDELETE=1 WHERE VIP_NO_BORROW_INFO.VIPID=@VIPID 
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_CHECK]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.31
-- Description:	非会员登录验证
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_CHECK] 
	@VIPNUMBER VARCHAR(200),
@VIPPWD VARCHAR(50)
AS
BEGIN
	SELECT VIPID FROM VIP_NO_INFO WHERE (VIPNUMBER=@VIPNUMBER	OR VIPNAME =@VIPNUMBER ) AND VIPPWD=@VIPPWD
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_BY_VIPID_AND_TIME]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.16
-- Description:	根据非会员ID和时间段获取租车信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_BORROW_SELECT_BY_VIPID_AND_TIME]
@VIPID INT,
@STARTTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
SELECT BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM FROM VIP_NO_BORROW_INFO WHERE ISDELETE!='1' AND VIPID=@VIPID AND BBKTIME BETWEEN @STARTTIME AND @ENDTIME
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_BY_VIPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_SELECT_BY_VIPID]
@VIPID INT
AS
BEGIN
SELECT BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM FROM VIP_NO_BORROW_INFO WHERE ISDELETE!=1 AND VIPID=@VIPID AND ISRETURN!=1
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_BY_DPID_AND_TIME]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.15
-- Description:	根据分店ID和时间段获取租车信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_BORROW_SELECT_BY_DPID_AND_TIME]
@DPID INT,
@STARTTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
SET  @STARTTIME='2014-1-1 0:0:0';
SELECT BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM,MARK,ISNIGHT  FROM VIP_NO_BORROW_INFO WHERE ISDELETE!=1 AND DPID=@DPID AND BBKTIME BETWEEN @STARTTIME AND @ENDTIME AND ISRETURN!=1 ORDER BY BBKID DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_SELECT_BY_DPID]
@DPID INT
AS
BEGIN
SELECT BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM FROM VIP_NO_BORROW_INFO WHERE ISDELETE!=1 AND DPID=@DPID AND ISRETURN!=1 ORDER BY BBKID DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_BY_BBKID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_SELECT_BY_BBKID]
@BBKID INT
AS
BEGIN
SELECT BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM,MARK,ISNIGHT  FROM VIP_NO_BORROW_INFO WHERE ISDELETE!='1' AND BBKID=@BBKID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_ALL_BY_DPID_AND_TIME]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[VIP_NO_BORROW_SELECT_ALL_BY_DPID_AND_TIME]
@DPID INT,
@STARTTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
SELECT  BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM FROM VIP_NO_BORROW_INFO WHERE ISDELETE!=1 AND BBKTIME BETWEEN @STARTTIME AND @ENDTIME AND DPID=@DPID ORDER BY BBKID DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT_ALL_BY_DPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_SELECT_ALL_BY_DPID]
@DPID INT
AS
 BEGIN
 SELECT  BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM FROM VIP_NO_BORROW_INFO WHERE ISDELETE!=1 AND DPID=@DPID ORDER BY BBKID DESC
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_SELECT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_SELECT]
AS
BEGIN
SELECT BBKID,VIPID,DPID,VNNAME,VNPHONE,VNCARD,VNNUMBER,RKRID,BBKTIME,ISRETURN,LEFTNUM FROM VIP_NO_BORROW_INFO WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_INSERT]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_INSERT]
@VIPID INT,--VIPid
@DPID INT,
@BKID INT,--租车种类
@VNNAME VARCHAR(50),
@VNCARD VARCHAR(200),
@VNNUMBER INT,
@VNPHONE VARCHAR(50),
@USERID INT,
@BBKTIME DATETIME,
@MARK VARCHAR(1000)
AS
BEGIN
 DECLARE @MONEYLEFT MONEY
 DECLARE @COSTBEGIN MONEY
 DECLARE @RKRID INT
 DECLARE @LVID INT
 DECLARE @RUID INT --扣费规则ID
 SELECT @LVID=LVID FROM VIP_NO_INFO WHERE VIPID=@VIPID --取出等级
 SELECT @MONEYLEFT=VMBALANCE FROM VIP_MONEY_INFO WHERE VIPID=@VIPID AND ISVIP!=1
 SELECT @MONEYLEFT=ISNULL( @MONEYLEFT,0)
 --SELECT @RKRID=RKRID,@RUID=RUID FROM RULE_KIND_RELATION WHERE LVID=@LVID AND BRKID=@BKID--根据等级和租车类型取出租车扣费种类
 --SELECT @COSTBEGIN=COSTBEGIN FROM COST_RULE_INFO WHERE CRID=@RUID
 SELECT @COSTBEGIN=COSTBEGIN FROM COST_RULE_INFO WHERE LVID=@LVID AND CRKID=@BKID
 
 IF(@MONEYLEFT<@COSTBEGIN)--如果余额为小于初始扣费金额那么提示他充值
 BEGIN
 RETURN -2
 END
 
INSERT INTO VIP_NO_BORROW_INFO (VIPID,DPID,RKRID,VNNAME,VNPHONE,VNCARD,VNNUMBER,BBKTIME,ISDELETE,LEFTNUM,ISRETURN,USERID,MARK ) VALUES (@VIPID,@DPID,@BKID,@VNNAME,@VNPHONE,@VNCARD,@VNNUMBER,@BBKTIME,0,@VNNUMBER,0,@USERID,@MARK)
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_DELETE_BY_VIPID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_DELETE_BY_VIPID]
@VIPID INT
AS
BEGIN
UPDATE VIP_NO_BORROW_INFO SET ISDELETE='1' WHERE VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_DELETE_BY_BBKID]    Script Date: 09/24/2015 19:14:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_DELETE_BY_BBKID]
@BBKID INT
AS
BEGIN
UPDATE VIP_NO_BORROW_INFO SET ISDELETE='1' WHERE BBKID=@BBKID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_NO_BORROW_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_NO_BORROW_DELETE]
@VIPID INT
AS
BEGIN
UPDATE VIP_NO_BORROW_INFO SET ISDELETE='1'
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_MONEY_UPDATE]
@VMID INT,
@VMBALANCE MONEY
AS
BEGIN
UPDATE VIP_MONEY_INFO SET VMBALANCE=@VMBALANCE WHERE VMID=@VMID 
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_SELECT_BY_VMID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_MONEY_SELECT_BY_VMID]
@VMID INT
AS
BEGIN
SELECT VMID,VIPID,VMBALANCE,INTEGRAL FROM VIP_MONEY_INFO WHERE VMID=@VMID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_SELECT_BY_VIPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_MONEY_SELECT_BY_VIPID]
@VIPID INT,
@ISVIP BIT
AS
BEGIN
SELECT VMID,VIPID,VMBALANCE,INTEGRAL FROM VIP_MONEY_INFO WHERE VIPID=@VIPID AND ISVIP=@ISVIP
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_MONEY_SELECT]
@ISVIP BIT
AS
BEGIN
SELECT VMID,VIPID,VMBALANCE,INTEGRAL FROM VIP_MONEY_INFO WHERE ISDELETE!='1' AND ISVIP=@ISVIP
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_RECHARGE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---会员充值，非会员充值，都用此方法，根据isvip来决定是给会员充值还是给非会员充值
--充值的逻辑为：先取出原来余额，用新充值的钱加上原来的余额作为新的余额

CREATE PROC [dbo].[VIP_MONEY_RECHARGE]
@VIPID INT,
@VMBALANCE MONEY,
@ISVIP BIT,
@INTEGRAL MONEY
AS
BEGIN
DECLARE @VMMONEY MONEY
SELECT @VMMONEY=VMBALANCE FROM VIP_MONEY_INFO WHERE VIPID=@VIPID AND ISVIP=@ISVIP
SET @VMMONEY=@VMMONEY+@VMBALANCE
IF @INTEGRAL >=0
BEGIN
UPDATE VIP_MONEY_INFO SET VMBALANCE=@VMMONEY ,INTEGRAL =@INTEGRAL  WHERE VIPID=@VIPID AND ISVIP=@ISVIP
END
ELSE
BEGIN
UPDATE VIP_MONEY_INFO SET VMBALANCE=@VMMONEY WHERE VIPID=@VIPID AND ISVIP=@ISVIP
END
return 1
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_MONEY_INSERT]
@VIPID INT,
@VMBALANCE MONEY,
@ISVIP BIT
AS
BEGIN
INSERT INTO VIP_MONEY_INFO (VIPID,VMBALANCE,ISVIP) VALUES (@VIPID,@VMBALANCE,@ISVIP)
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MONEY_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_MONEY_DELETE]
@VMID INT
AS
BEGIN
UPDATE VIP_MONEY_INFO SET ISDELETE='1' WHERE VMID=@VMID 
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_MARK_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014-10-31
-- Description:	会员更改租车备注
-- =============================================
CREATE PROCEDURE [dbo].[VIP_MARK_UPDATE]

@BBID int,
@MARK VARCHAR(1000)

AS
BEGIN
	UPDATE VIP_BORROW_INFO SET MARK =@MARK WHERE BBKID =@BBID 
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_LEVEL_UPDATE]
@LVID INT,
@LVNAME VARCHAR(50),
@ISDELETE BIT,
@ISVIP BIT
AS
BEGIN
UPDATE VIP_LEVEL_INFO SET LVNAME=@LVNAME,ISDELETE=@ISDELETE,ISVIP=@ISVIP WHERE LVID=@LVID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_SELECT_BY_LVNAME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.29
-- Description:	根据等级名称获取会员等级信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_LEVEL_SELECT_BY_LVNAME]
	@LVNAME VARCHAR(50)
AS
BEGIN
	SELECT LVNAME,ISVIP FROM VIP_LEVEL_INFO WHERE LVNAME=@LVNAME AND ISDELETE!=1
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_SELECT_BY_ISVIP]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_LEVEL_SELECT_BY_ISVIP]
@ISVIP BIT
AS
BEGIN
SELECT LVID,LVNAME,ISVIP FROM VIP_LEVEL_INFO WHERE ISDELETE!=1 AND ISVIP=@ISVIP ORDER BY LVID DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_LEVEL_SELECT_BY_ID]
@LVID INT
AS
BEGIN
SELECT LVNAME,ISVIP FROM VIP_LEVEL_INFO WHERE LVID=@LVID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_LEVEL_SELECT]
AS
BEGIN
SELECT LVID,LVNAME,ISVIP FROM VIP_LEVEL_INFO WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_LEVEL_INSERT]
@LVNAME VARCHAR(50),
@ISVIP BIT
AS
BEGIN
INSERT INTO VIP_LEVEL_INFO (LVNAME,ISDELETE,ISVIP) VALUES (@LVNAME,0,@ISVIP)
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_LEVEL_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_LEVEL_DELETE]
@LVID INT
AS
BEGIN
UPDATE VIP_LEVEL_INFO SET ISDELETE=1 WHERE LVID=@LVID
UPDATE COST_RULE_INFO SET ISDELETE=1 WHERE LVID=@LVID
UPDATE VIP_INFO SET VIPLEVELID=1 WHERE VIPLEVELID=@LVID
UPDATE VIP_NO_INFO SET LVID=1 WHERE LVID=@LVID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_INSERT]
@VIPNUMBER VARCHAR(200),
@VIPNAME VARCHAR(50),
@VIPSEX BIT,
@VIPPHOTO IMAGE,
@VIPLEVELID INT,
@VIPPWD VARCHAR(50),
@VIPPHONE VARCHAR(50),
@VIPCARD VARCHAR(200),
@VIPBIRTHDAY DATE,
@VIPADDRESS VARCHAR(200),
@DPID INT,
@ADDDATE DATETIME,
@USERID  INT,
 @CHARGEMONEY money,
@VIPMARK VARCHAR(1000)
AS
DECLARE @VIPID INT
BEGIN
IF((SELECT COUNT(VIPID) FROM VIP_INFO WHERE VIPNUMBER =@VIPNUMBER AND ISDELETE =0 )>0)
BEGIN
 RETURN
END
INSERT INTO VIP_INFO (VIPNUMBER,VIPNAME,VIPSEX,VIPPHOTO,VIPLEVELID,VIPPWD,VIPPHONE,VIPBIRTHDAY,VIPADDRESS,DPID,ISDELETE,VIPCARD,ADDDATE,USERID,MARK	 ) VALUES (@VIPNUMBER,@VIPNAME,@VIPSEX,@VIPPHOTO,@VIPLEVELID,@VIPPWD,@VIPPHONE,@VIPBIRTHDAY,@VIPADDRESS,@DPID,'FALSE',@VIPCARD,@ADDDATE,@USERID,@VIPMARK  )
SELECT @VIPID=@@IDENTITY
INSERT INTO VIP_MONEY_INFO (VIPID,VMBALANCE,ISVIP,INTEGRAL) VALUES (@VIPID,@CHARGEMONEY,1,0)
INSERT INTO RECHARGE_INFO (VIPID,ISVIP,CHARGEMONEY,DEPARTMENTID,USERID) VALUES (@VIPID,1,@CHARGEMONEY,@DPID,@USERID)

END
GO
/****** Object:  StoredProcedure [dbo].[U_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[U_DELETE]
@UID INT
AS
BEGIN
UPDATE U_INFO SET ISDELETE='1' WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_DELETE_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_DELETE_BY_DPID]
@DPID INT
AS
 BEGIN
 UPDATE VIP_INFO SET ISDELETE=1 WHERE DPID=@DPID
 UPDATE BIKE_RETURN_INFO SET ISDELETE=1 WHERE BIKE_RETURN_INFO.VIPID IN (SELECT VIPID FROM VIP_INFO WHERE DPID=@DPID) AND ISVIP=1
 UPDATE VIP_BORROW_INFO SET ISDELETE=1 WHERE VIP_BORROW_INFO.VIPID IN (SELECT VIPID FROM VIP_INFO WHERE DPID=@DPID)
 UPDATE VIP_MONEY_INFO SET ISDELETE=1 WHERE VIP_MONEY_INFO.VIPID IN (SELECT VIPID FROM VIP_INFO WHERE DPID=@DPID) AND ISVIP=1
 UPDATE VIP_YEAR_FORM SET ISDELETE=1 WHERE VIP_YEAR_FORM.VIPID IN (SELECT VIPID FROM VIP_INFO WHERE DPID=@DPID) 
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_DELETE]
@VIPID INT
AS
BEGIN
UPDATE VIP_INFO SET ISDELETE=1 WHERE VIPID=@VIPID
UPDATE BIKE_RETURN_INFO SET ISDELETE=1 WHERE BIKE_RETURN_INFO.VIPID=@VIPID AND ISVIP=1
UPDATE VIP_BORROW_INFO SET ISDELETE=1 WHERE VIP_BORROW_INFO.VIPID=@VIPID
UPDATE VIP_MONEY_INFO SET ISDELETE=1 WHERE VIPID =@VIPID AND ISVIP=1
UPDATE VIP_YEAR_FORM SET ISDELETE=1 WHERE VIP_YEAR_FORM.VIPID=@VIPID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_CHECK]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_CHECK]
@VIPNUMBER VARCHAR(200),
@VIPPWD VARCHAR(50)
AS
BEGIN
DECLARE @COUNT INT
SELECT  @COUNT=COUNT (VIPID)FROM VIP_INFO WHERE VIPNUMBER=@VIPNUMBER AND VIPPWD=@VIPPWD
IF @COUNT >0
BEGIN
SELECT  VIPID FROM VIP_INFO WHERE VIPNUMBER=@VIPNUMBER AND VIPPWD=@VIPPWD
END
ELSE
BEGIN
SELECT  VIPID FROM VIP_INFO WHERE VIPNAME =@VIPNUMBER AND VIPPWD=@VIPPWD
END

END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_UNRETURN_BY_VIPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_BORROW_UNRETURN_BY_VIPID]
@VIPID INT
AS
BEGIN
SELECT BBKID,MARK ,BBKTIME,DPID,BBKNUM,VIPID,ISNIGHT ,RKRID,(SELECT COUNT(*) FROM VIP_BORROW_INFO WHERE VIPID=@VIPID AND ISDELETE!='1') as TIMES,ISRETURN,LEFTNUM FROM VIP_BORROW_INFO WHERE VIPID=@VIPID AND ISDELETE!=1 AND ISRETURN!=1
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_BY_VIPID_AND_TIME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.16
-- Description:	根据会员ID和时间段获取租车信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_BORROW_SELECT_BY_VIPID_AND_TIME]
@VIPID INT,
@STARTTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
SELECT BBKID,BBKTIME,DPID,BBKNUM,VIPID,RKRID,ISRETURN,LEFTNUM FROM VIP_BORROW_INFO WHERE VIPID=@VIPID AND BBKTIME BETWEEN @STARTTIME AND @ENDTIME AND ISDELETE!='1' ORDER BY BBKID DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_BY_VIPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_BORROW_SELECT_BY_VIPID]
@VIPID INT
AS
BEGIN
SELECT BBKID,BBKTIME,DPID,BBKNUM,VIPID,RKRID,ISRETURN,LEFTNUM FROM VIP_BORROW_INFO WHERE VIPID=@VIPID AND ISDELETE!='1' ORDER BY BBKID DESC
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	根据ID获取租车信息
-- =============================================
CREATE PROCEDURE [dbo].[VIP_BORROW_SELECT_BY_ID]
	@BBKID int
AS
BEGIN
	SELECT BBKID,VIPID,BBKTIME,DPID,BBKNUM,RKRID,ISRETURN,LEFTNUM,MARK,ISNIGHT  FROM VIP_BORROW_INFO WHERE BBKID=@BBKID
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_BY_DPID_AND_TIME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.15
-- Description:	根据分店ID和时间段获取租车信息(未还)
-- =============================================
CREATE PROCEDURE [dbo].[VIP_BORROW_SELECT_BY_DPID_AND_TIME]
	@DPID INT,
	@STARTTIME DATETIME,
	@ENDTIME DATETIME
AS
BEGIN
    SET  @STARTTIME='2014-1-1 0:0:0';
	SELECT BBKID,VIPID,BBKTIME,BBKNUM,DPID,RKRID,ISRETURN,LEFTNUM,MARK,ISNIGHT   FROM VIP_BORROW_INFO WHERE DPID=@DPID AND BBKTIME BETWEEN @STARTTIME AND @ENDTIME AND ISDELETE!=1 AND ISRETURN!=1 order by BBKID desc
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_BORROW_SELECT_BY_DPID]
@DPID INT
AS
BEGIN 
SELECT BBKID,VIPID,BBKTIME,BBKNUM,DPID,RKRID,ISRETURN,LEFTNUM FROM VIP_BORROW_INFO WHERE DPID=@DPID AND ISDELETE!=1 AND ISRETURN!=1 order by BBKID desc
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_ALL_BY_DPID_AND_TIME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.15
-- Description:	根据分店ID和时间段获得租车信息(已还，未还)都有
-- =============================================
CREATE PROCEDURE [dbo].[VIP_BORROW_SELECT_ALL_BY_DPID_AND_TIME]
	@DPID INT,
	@STARTTIME DATETIME,
	@ENDTIME DATETIME
AS
BEGIN
	SELECT BBKID,VIPID,BBKTIME,BBKNUM,DPID,RKRID,ISRETURN,LEFTNUM,MARK  FROM VIP_BORROW_INFO WHERE DPID=@DPID AND BBKTIME BETWEEN @STARTTIME AND @ENDTIME AND ISDELETE!=1 order by BBKID desc
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT_ALL_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_BORROW_SELECT_ALL_BY_DPID]
@DPID INT
 AS
  BEGIN
  SELECT BBKID,VIPID,BBKTIME,BBKNUM,DPID,RKRID,ISRETURN,LEFTNUM FROM VIP_BORROW_INFO WHERE DPID=@DPID AND ISDELETE!=1 order by BBKID desc
  END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_BORROW_SELECT]
AS
BEGIN
SELECT BBKID,VIPID,BBKTIME,DPID,BBKNUM,RKRID,ISRETURN,LEFTNUM FROM VIP_BORROW_INFO WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[VIP_BORROW_INSERT]
@VIPID INT,
@BBKTIME DATETIME,
@DPID INT,
@BBKNUM INT,
@USERID INT,
@BKID INT ,
@MARK  VARCHAR(1000)
AS
 BEGIN
 DECLARE @LVID INT
 DECLARE @RKRID INT
 DECLARE @BBKNUMTEMP INT
 DECLARE @BBKIDTEMP INT
 DECLARE @MONEYLEFT MONEY
 DECLARE @COSTBEGIN MONEY
 DECLARE @RUID INT  --扣费规则ID
 SELECT @MONEYLEFT=VMBALANCE FROM VIP_MONEY_INFO WHERE VIPID=@VIPID AND ISVIP=1
 SELECT @MONEYLEFT=ISNULL(@MONEYLEFT,0)
 SELECT @LVID=VIPLEVELID FROM VIP_INFO WHERE VIPID=@VIPID --取出等级
 SELECT @COSTBEGIN=COSTBEGIN FROM COST_RULE_INFO WHERE LVID=@LVID AND CRKID=@BKID
 IF(@MONEYLEFT<@COSTBEGIN)--如果余额为小于初始扣费金额那么提示他充值
 BEGIN
 RETURN -2
 END
 
 BEGIN
 BEGIN TRY
 INSERT INTO VIP_BORROW_INFO (VIPID,BBKTIME,DPID,BBKNUM,RKRID,ISDELETE,ISRETURN,LEFTNUM,USERID,MARK) VALUES (@VIPID,@BBKTIME,@DPID,@BBKNUM,@BKID,0,0,@BBKNUM,@USERID,@MARK )
 END TRY
 BEGIN CATCH
 INSERT INTO EXCEPTION_INFO (EXCONTENT,ECFUNC,EXTIME) VALUES ('添加vip租车信息存错过程出错'+ERROR_MESSAGE(),'VIP_BORROW_INSERT',GETDATE())
 END CATCH
 END
 END
GO
/****** Object:  StoredProcedure [dbo].[VIP_BORROW_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	删除会员租车记录
-- =============================================
CREATE PROCEDURE [dbo].[VIP_BORROW_DELETE]
	@BBKID int
AS
BEGIN
	UPDATE VIP_BORROW_INFO SET ISDELETE=1 WHERE BBKID=@BBKID
END
GO
/****** Object:  StoredProcedure [dbo].[USER_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_UPDATE]
@USERID INT,
@USERNAME VARCHAR(50),
@USERPWD VARCHAR(50),
@USERCONTROLPWD VARCHAR(50),
@USERPHONE VARCHAR(50),
@USERCARD VARCHAR(200),
@USERPHOTO IMAGE,
@DPID INT
AS
BEGIN
UPDATE USER_INFO SET USERNAME=@USERNAME,USERPWD=@USERPWD,USERCONTROLPWD=@USERCONTROLPWD,USERPHONE=@USERPHONE,USERCARD=@USERCARD,USERPHOTO=@USERPHOTO,DPID=@DPID WHERE USERID=@USERID
END
GO
/****** Object:  StoredProcedure [dbo].[USER_SELECT_BY_NAME_AND_PWD]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.9
-- Description:	用户登录
-- =============================================
CREATE PROCEDURE [dbo].[USER_SELECT_BY_NAME_AND_PWD]
	@USERNAME VARCHAR(50),
	@USERPWD VARCHAR(50)
AS
BEGIN
	SELECT USERID,USERNAME,USERPHONE,USERCARD,DPID,USERPHOTO,USERPWD,USERCONTROLPWD FROM USER_INFO WHERE ISDELETE!=1 AND USERNAME=@USERNAME AND USERPWD=@USERPWD
END
GO
/****** Object:  StoredProcedure [dbo].[USER_SELECT_BY_NAME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014/7/16
-- Description:	根据用户名取出用户信息
-- =============================================
CREATE PROCEDURE [dbo].[USER_SELECT_BY_NAME]

@NAME VARCHAR(50)

AS
BEGIN
SELECT *  FROM USER_INFO WHERE [USERNAME] =@NAME 
END
GO
/****** Object:  StoredProcedure [dbo].[USER_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_SELECT_BY_ID]
@USERID INT
AS
BEGIN
SELECT USERID,USERNAME,USERPHONE,USERCARD,DPID,USERPHOTO,USERPWD,USERCONTROLPWD FROM USER_INFO WHERE USERID=@USERID
END
GO
/****** Object:  StoredProcedure [dbo].[USER_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_SELECT_BY_DPID]
@DPID INT
AS
BEGIN
SELECT USERID,USERNAME,USERPHONE,USERCARD,DPID,USERPHOTO,USERPWD,USERCONTROLPWD FROM USER_INFO WHERE ISDELETE!=1 AND DPID=@DPID
END
GO
/****** Object:  StoredProcedure [dbo].[USER_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_SELECT]
AS
BEGIN
SELECT USERID,USERNAME,USERPHONE,USERCARD,DPID,USERPHOTO,USERPWD,USERCONTROLPWD FROM USER_INFO WHERE ISDELETE!=1
END
GO
/****** Object:  StoredProcedure [dbo].[USER_DELETE_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_DELETE_BY_DPID]
@DPID INT
AS
 BEGIN
 UPDATE USER_INFO SET ISDELETE=1 WHERE DPID=@DPID
 UPDATE LOG_INFO SET ISDELETE=1 WHERE USERID IN (SELECT USERID FROM USER_INFO WHERE DPID=@DPID)
 END
GO
/****** Object:  StoredProcedure [dbo].[USER_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_DELETE]
@USERID INT
AS
BEGIN
UPDATE USER_INFO SET ISDELETE=1 WHERE USERID=@USERID
END
GO
/****** Object:  StoredProcedure [dbo].[USER_ADD]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[USER_ADD]
@USERNAME VARCHAR(50),
@USERPWD VARCHAR(50),
@USERCONTROLPWD VARCHAR(50),
@USERPHONE VARCHAR(50),
@USERCARD VARCHAR(200),
@USERPHOTO IMAGE,
@DPID INT
AS
INSERT INTO USER_INFO (USERNAME,USERPWD,USERCONTROLPWD,USERPHONE,USERCARD,USERPHOTO,DPID,ISDELETE) VALUES (@USERNAME,@USERPWD,@USERCONTROLPWD,@USERPHONE,@USERCARD,@USERPHOTO,@DPID,0)
GO
/****** Object:  StoredProcedure [dbo].[U_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[U_UPDATE]
@UINFO VARCHAR(50),
@UID INT,
@ISADMIN BIT,
@DPID INT,
@IDENTIFY VARCHAR(50)
AS
BEGIN
UPDATE U_INFO SET UINFO=@UINFO,ISADMIN=@ISADMIN,DPID=@DPID,IDENTIFY=@IDENTIFY WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[U_SELECT_BY_UINFO]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[U_SELECT_BY_UINFO]
  @IDENTIFY VARCHAR(50)
  AS
  BEGIN
  SELECT UINFO,UID,DPID,ADDDATE,ISADMIN,IDENTIFY FROM U_INFO WHERE ISDELETE!=1 AND IDENTIFY=@IDENTIFY
  END
GO
/****** Object:  StoredProcedure [dbo].[U_SELECT_BY_UID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.49
-- Description:	根据ID获取U盾
-- =============================================
CREATE PROCEDURE [dbo].[U_SELECT_BY_UID]
	@UID int
AS
BEGIN
	SELECT UID,UINFO,ISADMIN,ADDDATE,DPID,IDENTIFY FROM U_INFO WHERE UID=@UID
END
GO
/****** Object:  StoredProcedure [dbo].[U_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	获取所有U盾
-- =============================================
CREATE PROCEDURE [dbo].[U_SELECT]
AS
BEGIN
	SELECT UID,UINFO,ISADMIN,ADDDATE,DPID,IDENTIFY FROM U_INFO WHERE ISDELETE!=1
END
GO
/****** Object:  StoredProcedure [dbo].[U_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[U_INSERT]
@UINFO VARCHAR(50),
@ISADMIN BIT,
@DPID INT,
@IDENTIFY VARCHAR(50)
AS
BEGIN
INSERT INTO U_INFO (UINFO,ISADMIN,ADDDATE,DPID,ISDELETE,IDENTIFY) VALUES (@UINFO,@ISADMIN,GETDATE(),@DPID,0,@IDENTIFY)
END
GO
/****** Object:  StoredProcedure [dbo].[RECHARGE_SELECT_BY_DEPART]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[RECHARGE_SELECT_BY_DEPART]
@beginTime datetime,
@endTime datetime,
@DepartId int
as
 begin
 
 select * from dbo.RECHARGE_INFO where CHARGETIME>@beginTime and CHARGETIME<@endTime and DEPARTMENTID=@DepartId and ISDELETE!=1
 end
GO
/****** Object:  StoredProcedure [dbo].[NIGHT_BORROW_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014-8-26
-- Description:	根据id取出包夜扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[NIGHT_BORROW_SELECT_BY_ID]
@ID INT
AS
BEGIN
	SELECT VIPCOST,BEGINTIME ,ENDTIME  FROM NIGHT_COST_RULE where ID=@ID
END
GO
/****** Object:  StoredProcedure [dbo].[NIGHT_BORROW_RULE_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		郑传哲
-- Create date: 2014-8-26
-- Description:	修改包夜扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[NIGHT_BORROW_RULE_UPDATE]

@ID INT,
@COST INT,
@BEGINTIME INT,
@ENDTIME INT

AS
BEGIN
	UPDATE NIGHT_COST_RULE SET VIPCOST =@COST ,BEGINTIME =@BEGINTIME ,ENDTIME =@ENDTIME WHERE ID =@ID 
END
GO
/****** Object:  StoredProcedure [dbo].[MONTH_COST_RULE_UPADTE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	修改月租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[MONTH_COST_RULE_UPADTE] 
    @CRID INT,
	@CRNAME varchar(50),
	@COSTPH money,
	@COSTTIME time,
	@COSTBEGIN money
AS
BEGIN
	UPDATE MONTH_COST_RULE SET CRNAME=@CRNAME,COSTPH=@COSTPH,@COSTTIME=@COSTTIME,@COSTBEGIN=@COSTBEGIN WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[MONTH_COST_RULE_SELECT_BY_CRID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	根据id获取月租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[MONTH_COST_RULE_SELECT_BY_CRID] 
	@CRID INT
AS
BEGIN
	SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN FROM MONTH_COST_RULE WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[MONTH_COST_RULE_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	获取所有月租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[MONTH_COST_RULE_SELECT]

AS
BEGIN
	SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN FROM MONTH_COST_RULE WHERE ISDELETE!=1
END
GO
/****** Object:  StoredProcedure [dbo].[MONTH_COST_RULE_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	添加月租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[MONTH_COST_RULE_INSERT] 
	@CRNAME varchar(50),
	@COSTPH money,
	@COSTTIME time,
	@COSTBEGIN money
AS
BEGIN
	INSERT INTO MONTH_COST_RULE (CRNAME,COSTPH,COSTTIME,COSTBEGIN) VALUES(@COSTTIME,@COSTPH,@COSTTIME,@COSTBEGIN)
END
GO
/****** Object:  StoredProcedure [dbo].[MONTH_COST_RULE_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.30
-- Description:	删除月租扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[MONTH_COST_RULE_DELETE]
	@CRID INT
AS
BEGIN
	UPDATE MONTH_COST_RULE SET ISDELETE=1 WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[LOG_TIME_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOG_TIME_SELECT]
AS
BEGIN
SELECT LOGTIME FROM LOG_INFO WHERE ISDELETE!=1
END
GO
/****** Object:  StoredProcedure [dbo].[LOG_SELECT_BY_USERID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOG_SELECT_BY_USERID]
@USERID INT,
@STARTTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
IF(@STARTTIME='') AND (@ENDTIME='')
BEGIN
SELECT LOGID,USERID,CONTENT,LOGTIME FROM LOG_INFO WHERE ISDELETE!='1' AND USERID=@USERID 
END
ELSE IF(@STARTTIME='') AND (@ENDTIME!='')
BEGIN
SELECT LOGID,USERID,CONTENT,LOGTIME FROM LOG_INFO WHERE ISDELETE!='1' AND USERID=@USERID AND LOGTIME<@ENDTIME
END
ELSE IF (@STARTTIME!='') AND (@ENDTIME='')
BEGIN
SELECT LOGID,USERID,CONTENT,LOGTIME FROM LOG_INFO WHERE ISDELETE!='1' AND USERID=@USERID AND LOGTIME>@STARTTIME
END
ELSE
BEGIN
SELECT LOGID,USERID,CONTENT,LOGTIME FROM LOG_INFO WHERE ISDELETE!='1' AND USERID=@USERID AND LOGTIME BETWEEN @STARTTIME AND @ENDTIME

END

END
GO
/****** Object:  StoredProcedure [dbo].[LOG_SELECT_BY_LOGID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOG_SELECT_BY_LOGID]
@LOGID INT
AS
BEGIN
SELECT LOGID,USERID,CONTENT,LOGTIME FROM LOG_INFO WHERE ISDELETE!='1' AND LOGID=@LOGID
END
GO
/****** Object:  StoredProcedure [dbo].[LOG_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOG_SELECT_BY_DPID]
@DPID INT,
@BEGINTIME DATETIME,
@ENDTIME DATETIME
AS
 BEGIN
 SELECT LOGID,USERID,CONTENT,LOGTIME,DPID FROM LOG_INFO WHERE ISDELETE!=1 AND DPID=@DPID AND LOGTIME>@BEGINTIME AND LOGTIME<@ENDTIME
 END
GO
/****** Object:  StoredProcedure [dbo].[LOG_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOG_SELECT]
AS
BEGIN
SELECT LOGID,USERID,CONTENT,LOGTIME,DPID FROM LOG_INFO WHERE ISDELETE!='1'
END
GO
/****** Object:  StoredProcedure [dbo].[LOG_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOG_INSERT]
@USERID INT,
@CONTENT VARCHAR(200),
@LOGTIME DATETIME,
@DPID INT
AS
BEGIN
INSERT INTO LOG_INFO (USERID,CONTENT,LOGTIME,ISDELETE,DPID) VALUES (@USERID,@CONTENT,@LOGTIME,0,@DPID)
END
GO
/****** Object:  StoredProcedure [dbo].[EXCEPTION_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[EXCEPTION_INSERT]
@EXCONTENT VARCHAR(50),
@EXFUNC VARCHAR(50),
@EXTIME DATETIME
AS
 BEGIN 
 INSERT INTO EXCEPTION_INFO (EXCONTENT,ECFUNC,EXTIME) VALUES (@EXCONTENT,@EXFUNC,@EXTIME)
 END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_DELETE]
 @RBKID INT
 AS
 BEGIN
 UPDATE BIKE_RETURN_INFO SET ISDELETE=1 WHERE RBKID=@RBKID
 END
GO
/****** Object:  StoredProcedure [dbo].[COST_RULE_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[COST_RULE_DELETE]
@CRID INT
AS
BEGIN
UPDATE COST_RULE_INFO SET ISDELETE=1 WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[CHANGE_USER]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[CHANGE_USER]
  @USERID int
  as
  begin
  declare @dateNow datetime
  declare @newVip int
  declare @vipIncome money 
  declare @vipNoIncome money 
  set @dateNow=GETDATE()
  select  @newVip= COUNT(*) from VIP_INFO where USERID=@USERID and ADDDATE >DATEADD(day, DATEDIFF(day,0,@dateNow), 0) and ADDDATE<@dateNow
  select @vipIncome= SUM(CHARGEMONEY) from RECHARGE_INFO where USERID=@USERID and CHARGETIME>DATEADD(day, DATEDIFF(day,0,@dateNow), 0) and CHARGETIME<@dateNow
  select @vipNoIncome=SUM(BUCOST) from BIKE_RETURN_INFO where ISVIP !=1 and USERID=@USERID and RBKTIME>DATEADD(day, DATEDIFF(day,0,@dateNow), 0) and RBKTIME<@dateNow
  select @newVip newVip,ISNULL(@vipIncome,0) vipIncome,ISNULL(@vipNoIncome,0) vipNoIncome
  end
GO
/****** Object:  StoredProcedure [dbo].[BORROW_KIND_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.26
-- Description:	修改租车信息
-- =============================================
CREATE PROCEDURE [dbo].[BORROW_KIND_UPDATE] 
	@BKID int,
	@BBKNAME varchar(50)
AS
BEGIN
	UPDATE BIKE_BORROW_KIND set BBKNAME=@BBKNAME WHERE BKID=@BKID
END
GO
/****** Object:  StoredProcedure [dbo].[BORROW_KIND_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.26
-- Description:	根据ID获取租车类型
-- =============================================
CREATE PROCEDURE [dbo].[BORROW_KIND_SELECT_BY_ID]
	@BKID int
AS
BEGIN
	SELECT BKID,BBKNAME FROM BIKE_BORROW_KIND WHERE BKID=@BKID
END
GO
/****** Object:  StoredProcedure [dbo].[BORROW_KIND_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.26
-- Description:	获取所有租车类型
-- =============================================
CREATE PROCEDURE [dbo].[BORROW_KIND_SELECT]

AS
BEGIN
	SELECT BKID,BBKNAME FROM BIKE_BORROW_KIND WHERE ISDELETE!=1
END
GO
/****** Object:  StoredProcedure [dbo].[BORROW_KIND_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.26
-- Description:	添加租车类型
-- =============================================
CREATE PROCEDURE [dbo].[BORROW_KIND_INSERT] 
	@BBKNAME varchar(50)
AS
BEGIN
	INSERT INTO BIKE_BORROW_KIND (BBKNAME) values (@BBKNAME)
END
GO
/****** Object:  StoredProcedure [dbo].[BORROW_KIND_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.26
-- Description:	删除租车类型
-- =============================================
CREATE PROCEDURE [dbo].[BORROW_KIND_DELETE]
	@BKID int
AS
BEGIN
	UPDATE BIKE_BORROW_KIND SET ISDELETE=1 WHERE BKID=@BKID
END
GO
/****** Object:  StoredProcedure [dbo].[BORROW_COST_SELECT_BY_TIME_DPID_BRDPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[BORROW_COST_SELECT_BY_TIME_DPID_BRDPID]
	@ISVIP BIT,
	@DPID INT,
	@STARTTIME DATETIME,
	@ENDTIME DATETIME
	as
	 begin
	 if(@ISVIP=1)
	 begin
	 select * from BIKE_RETURN_INFO where BBKID in (SELECT BBKID from VIP_BORROW_INFO where DPID=@DPID) AND ISDELETE!=1 and RBKTIME BETWEEN DATEADD(day, DATEDIFF(day,0,@STARTTIME), 0) AND dateadd(ms,-3,DATEADD(day, DATEDIFF(day,0,@ENDTIME), 1)) AND BBKID IN (SELECT BBKID FROM VIP_BORROW_INFO WHERE (DPID=@DPID AND ISRETURN=1) OR (ISRETURN!=1 AND LEFTNUM!=BBKNUM AND DPID=@DPID)) AND ISVIP=1 ORDER BY RBKID DESC
	 end
	 else 
	 begin
	 	 select * from BIKE_RETURN_INFO where BBKID in (SELECT BBKID from VIP_NO_BORROW_INFO where DPID=@DPID) AND ISDELETE!=1 and RBKTIME BETWEEN DATEADD(day, DATEDIFF(day,0,@STARTTIME), 0) AND dateadd(ms,-3,DATEADD(day, DATEDIFF(day,0,@ENDTIME), 1)) AND BBKID IN (SELECT BBKID FROM VIP_BORROW_INFO WHERE (DPID=@DPID AND ISRETURN=1) OR (ISRETURN!=1 AND LEFTNUM!=BBKNUM AND DPID=@DPID)) AND ISVIP=0 ORDER BY RBKID DESC
	 end
	 end
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_UPDATE]
@RBKID INT,
@BBKID INT,
@RBKTIME DATETIME,
@VIPID INT,
@DPID INT,
@BUCOST MONEY,
@BUTIME DATETIME,
@BRNUMBER INT,
@ISDELETE BIT
AS
 BEGIN
 UPDATE BIKE_RETURN_INFO SET BBKID=@BBKID,RBKTIME=@RBKTIME,VIPID=@VIPID,DPID=@DPID,BUCOST=@BUCOST,BUTIME=@BUTIME,BRNUMBER=@BRNUMBER,ISDELETE=@ISDELETE WHERE RBKID=@RBKID
 END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_SELECT_BY_VIPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_SELECT_BY_VIPID]
@VIPID INT,
@ISVIP BIT,
@BEGINTIME DATETIME,
@ENDTIME DATETIME
AS
BEGIN
IF(@BEGINTIME='') AND (@ENDTIME='')
SELECT RBKID,BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND ISVIP=@ISVIP AND VIPID=@VIPID
ELSE IF(@BEGINTIME!='') AND (@ENDTIME='')
SELECT RBKID,BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND ISVIP=@ISVIP AND RBKTIME>@BEGINTIME AND VIPID=@VIPID
ELSE IF(@BEGINTIME='') AND (@ENDTIME!='')
SELECT RBKID,BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND ISVIP=@ISVIP AND RBKTIME<@ENDTIME AND VIPID=@VIPID
ELSE IF(@BEGINTIME!='') AND (@ENDTIME!='')
SELECT RBKID,BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND ISVIP=@ISVIP AND VIPID=@VIPID AND  RBKTIME BETWEEN @BEGINTIME AND @ENDTIME
END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_SELECT_BY_ID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_SELECT_BY_ID]
@RBKID INT
AS
  
  BEGIN
  SELECT BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND RBKID=@RBKID
  END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_SELECT_BY_DPID_AND_TIME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2014.1.15
-- Description:	根据分店和时间段查询该分店租出车辆还车信息
-- =============================================
CREATE PROCEDURE [dbo].[BIKE_RETURN_SELECT_BY_DPID_AND_TIME]
	@ISVIP BIT,
	@DPID INT,
	@STARTTIME DATETIME,
	@ENDTIME DATETIME
AS
BEGIN
IF(@ISVIP=1)
BEGIN
SELECT RBKID,BRI.BBKID,RBKTIME,BRI.VIPID,BRI.DPID,BUCOST,BUTIME,BRNUMBER,ISVIP,VBI.BBKTIME,VBI.USERID,BRI.MARK  FROM BIKE_RETURN_INFO BRI LEFT JOIN VIP_BORROW_INFO VBI ON (BRI.BBKID=VBI.BBKID) WHERE BRI.ISDELETE!=1 AND RBKTIME >@STARTTIME AND RBKTIME< @ENDTIME  AND BRI.BBKID IN (SELECT BBKID FROM VIP_BORROW_INFO WHERE (DPID=@DPID AND ISRETURN=1) OR (ISRETURN!=1 AND LEFTNUM!=BBKNUM AND DPID=@DPID)) AND ISVIP=1 ORDER BY RBKID DESC
END
ELSE
BEGIN
SELECT RBKID,BRI.BBKID,RBKTIME,BRI.VIPID,BRI.DPID,BUCOST,BUTIME,BRNUMBER,ISVIP,VNBI.BBKTIME,VNBI.USERID ,BRI.MARK  FROM BIKE_RETURN_INFO BRI LEFT JOIN VIP_NO_BORROW_INFO VNBI ON (BRI.BBKID=VNBI.BBKID) WHERE BRI.ISDELETE!=1 AND RBKTIME > @STARTTIME  AND RBKTIME<@ENDTIME  AND BRI.BBKID IN (SELECT BBKID FROM VIP_NO_BORROW_INFO WHERE (DPID=@DPID AND ISRETURN=1) OR (ISRETURN!=1 AND LEFTNUM!=VNNUMBER AND DPID=@DPID)) AND ISVIP!=1 ORDER BY RBKID DESC
END
END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_SELECT_BY_DPID]
@ISVIP BIT,
@DPID INT
AS
BEGIN
IF(@ISVIP=1)
BEGIN
SELECT RBKID,BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND BBKID IN (SELECT BBKID FROM VIP_BORROW_INFO WHERE DPID=@DPID AND ISRETURN=1) AND ISVIP=1 ORDER BY RBKID DESC
END
ELSE
BEGIN
SELECT RBKID,BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP FROM BIKE_RETURN_INFO WHERE ISDELETE!=1 AND BBKID IN (SELECT BBKID FROM VIP_NO_BORROW_INFO WHERE DPID=@DPID AND ISRETURN=1) AND ISVIP!=1 ORDER BY RBKID DESC
END
END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_SELECT]
@BEGINTIME DATETIME,
@ENDTIME DATETIME
 AS
  BEGIN
  IF(@BEGINTIME=' ' AND @ENDTIME = ' ')
  BEGIN
  SELECT BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER FROM BIKE_RETURN_INFO WHERE ISDELETE!='1'
  END
  ELSE IF(@BEGINTIME=' ')
  BEGIN
  SELECT BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER FROM BIKE_RETURN_INFO WHERE ISDELETE!='1' AND BUTIME<=@ENDTIME
  END
  ELSE IF(@ENDTIME=' ')
  BEGIN
   SELECT BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER FROM BIKE_RETURN_INFO WHERE ISDELETE!='1' AND BUTIME>=@BEGINTIME
  END
  ELSE 
  BEGIN
  SELECT BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER FROM BIKE_RETURN_INFO WHERE ISDELETE!='1' AND BUTIME>=@BEGINTIME AND BUTIME<=@ENDTIME
  END
  END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_YEAR_FORM_SELECT_BY_TIME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	根据时间段选出分店的年报表
-- =============================================
CREATE PROCEDURE [dbo].[DEPART_YEAR_FORM_SELECT_BY_TIME] 
	@BEGINTIME datetime,
	@ENDTIME datetime,
	@DPID int
AS
BEGIN
	SELECT DYID,DPID,VIPCOST,VIPNOCOST,TIME,ISDELETE,NewVipIncome,VipCharge,VipNoCharge FROM DEPART_YEAR_FORM WHERE DPID=@DPID AND TIME>@BEGINTIME AND TIME<@ENDTIME
END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_YEAR_FORM_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_YEAR_FORM_INSERT]
AS
BEGIN
DECLARE @BEGINTIME DATETIME
DECLARE @ENDTIME DATETIME
SET @ENDTIME=dateadd(dd,-day(getdate()),getdate())  --上个月月底
set @ENDTIME=dateadd(ms,-3,DATEADD(day, DATEDIFF(day,0,@ENDTIME), 1))--上个月月底结束时间
SET @BEGINTIME=dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate()))--上个月一号的这个时间
SET @BEGINTIME=DATEADD(day, DATEDIFF(day,0,@BEGINTIME), 0)--上个月一号零点
 INSERT INTO DEPART_YEAR_FORM (DPID,VIPCOST,VIPNOCOST,TIME,ISDELETE,NewVipIncome,VipCharge,VipNoCharge) (SELECT DPID,SUM(VIPCOST),SUM(VIPNOCOST),@ENDTIME,0,SUM(NewVipIncome),SUM(VipCharge),SUM(VipNoCharge) FROM DEPART_MONTH_FORM WHERE DEPART_MONTH_FORM.TIME>@BEGINTIME AND DEPART_MONTH_FORM.TIME<@ENDTIME GROUP BY DPID)
END



--DECLARE @BEGINTIME DATETIME
--DECLARE @ENDTIME DATETIME
--SET @ENDTIME=dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())) 
--set @ENDTIME= dateadd(ms,-3,DATEADD(day, DATEDIFF(day,0,@ENDTIME), 1))
--SET @BEGINTIME=dateadd(dd,-day(getdate())+1,getdate())
--SET @BEGINTIME=DATEADD(day, DATEDIFF(day,0,@BEGINTIME), 0)

--select @ENDTIME,@BEGINTIME
--SELECT DPID,SUM(VIPCOST),SUM(VIPNOCOST),@ENDTIME,0,SUM(NewVipIncome),SUM(VipCharge),SUM(VipNoCharge),avg(VipBalance),avg(VipNoBalance),SUM(Total) FROM DEPART_MONTH_FORM WHERE DEPART_MONTH_FORM.TIME>@BEGINTIME AND DEPART_MONTH_FORM.TIME<@ENDTIME GROUP BY DPID
GO
/****** Object:  StoredProcedure [dbo].[DEPART_YEAR_FORM_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_YEAR_FORM_DELETE]
@DYID INT
AS 
 BEGIN
 UPDATE DEPART_YEAR_FORM SET ISDELETE=1 WHERE DYID=@DYID
 END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_INSERT]
 @DPNAME VARCHAR(50),
 @DPPLACE VARCHAR(200),
 @DPPCMAC VARCHAR(50)
 AS
 BEGIN
 INSERT INTO DEPART_INFO (DPNAME,DPPLACE,DPPCMAC,ISDELETE,ADDDATE) VALUES (@DPNAME,@DPPLACE,@DPPCMAC,0,GETDATE())
 END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_UPDATE]
 @DPID INT,
 @DPNAME VARCHAR(50),
 @DPPLACE VARCHAR(200),
 @DPPCMAC VARCHAR(50)
 AS
 BEGIN
 UPDATE DEPART_INFO SET DPNAME=@DPNAME,DPPLACE=@DPPLACE,DPPCMAC=@DPPCMAC WHERE DPID=@DPID
 END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_SELECT_BY_MAC]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.13
-- Description:	根据分店物理地址获取分店信息
-- =============================================
CREATE PROCEDURE [dbo].[DEPART_SELECT_BY_MAC]
@DPMAC VARCHAR(50)
AS
BEGIN
	SELECT DPID,DPNAME,DPPLACE,DPPCMAC,ADDDATE FROM DEPART_INFO WHERE ISDELETE!=1 AND DPPCMAC=@DPMAC
END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_SELECT_BY_DPID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_SELECT_BY_DPID]
 @DPID INT
 AS
  BEGIN
  SELECT DPNAME,DPPLACE,DPPCMAC,ADDDATE FROM DEPART_INFO WHERE DPID=@DPID AND ISDELETE!=1
  END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_SELECT]
 AS
 BEGIN
 SELECT DPID,DPNAME,DPPLACE,DPPCMAC,ADDDATE FROM DEPART_INFO WHERE ISDELETE!=1
 END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_MONTH_FORM_SELECT_BY_TIME]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	根据时间选出分店月报表信息
-- =============================================
CREATE PROCEDURE [dbo].[DEPART_MONTH_FORM_SELECT_BY_TIME]
	@BEGINTIME datetime,
	@ENDTIME datetime,
	@DPID int
AS
BEGIN
	SELECT * FROM DEPART_MONTH_FORM WHERE DPID=@DPID AND TIME<@ENDTIME AND TIME>@BEGINTIME
END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_MONTH_FORM_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_MONTH_FORM_INSERT]
AS
BEGIN
--DECLARE @DPID INT
--DECLARE @VIPCOST MONEY
--DECLARE @VIPNOCOST MONEY
--DECLARE @LAMONTHB DATETIME
DECLARE @TIME DATETIME
DECLARE @TIMEEND DATETIME
	SET @TIME=DATEADD(HOUR ,-4,CONVERT(VARCHAR(10),GETDATE (),111))--当天开始时间--当天凌晨时刻
	--SET @LAMONTHB=DATEADD(DD,-1,GETDATE())--昨天凌晨时刻
	SET @TIMEEND=DATEADD(HOUR ,20,CONVERT(VARCHAR(10),GETDATE (),111))--当天结束晚上8点
	--SET @LAMONTHB=dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate()))
	--select   dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate()))     /*上个月一号*/
	--select   dateadd(dd,-day(getdate()),getdate())                                           /* 上月月底 */
	--select   dateadd(dd,-day(getdate())+1,getdate())                                         /* 本月一号 */
	--select   dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate()))         /* 本月底 */
	--select   dateadd(dd,-day(dateadd(month,1,getdate()))+1,dateadd(month,1,getdate()))       /* 下月一号 */
	--select   dateadd(dd,-day(dateadd(month,2,getdate())),dateadd(month,2,getdate()))         /* 下月月底 */
	--第一步：将分店和会员租车表关联，取出分店id和租车id
	--SELECT DEPART_INFO.DPID AS DPID,VIP_BORROW_INFO.BBKID AS BBKID INTO #VIPTEMP FROM DEPART_INFO LEFT JOIN VIP_BORROW_INFO ON DEPART_INFO.DPID=VIP_BORROW_INFO.DPID AND VIP_BORROW_INFO.BBKTIME>@LAMONTHB AND VIP_BORROW_INFO.BBKTIME<@TIME
	----第二步：将租车id与还车表中租车id关联，取出分店id和会员花费
	--SELECT #VIPTEMP.DPID AS DPID,BIKE_RETURN_INFO.BUCOST AS VIPCOST INTO #VIPCOSTTEMP FROM #VIPTEMP LEFT JOIN BIKE_RETURN_INFO ON #VIPTEMP.BBKID=BIKE_RETURN_INFO.BBKID AND BIKE_RETURN_INFO.ISVIP=1
	----第三步：将分店和非会员关联，取出分店id和非会员租车id
	--SELECT DEPART_INFO.DPID AS DPID,VIP_NO_BORROW_INFO.BBKID AS BBKID INTO #VIPNOTEMP FROM DEPART_INFO LEFT JOIN VIP_NO_BORROW_INFO ON DEPART_INFO.DPID=VIP_NO_BORROW_INFO.DPID AND VIP_NO_BORROW_INFO.BBKTIME>@LAMONTHB AND VIP_NO_BORROW_INFO.BBKTIME<@TIME
	----第四步：将租车id和还车表中id关联取出分店id和非会员花费
	--SELECT #VIPNOTEMP.DPID AS DPID,BIKE_RETURN_INFO.BUCOST AS VIPNOCOST INTO #VIPNOCOSTTEMP FROM #VIPNOTEMP LEFT JOIN BIKE_RETURN_INFO ON #VIPNOTEMP.BBKID=BIKE_RETURN_INFO.BBKID AND BIKE_RETURN_INFO.ISVIP=0
	----第五步：将两个带花费和分店id的表关联
	--SELECT #VIPCOSTTEMP.DPID,#VIPCOSTTEMP.VIPCOST,#VIPNOCOSTTEMP.VIPNOCOST INTO #A FROM #VIPCOSTTEMP LEFT JOIN #VIPNOCOSTTEMP ON #VIPCOSTTEMP.DPID=#VIPNOCOSTTEMP.DPID
	----第六步：求总和	
	----SELECT DPID,SUM(VIPCOST),SUM(VIPNOCOST) ,TIME FROM #A GROUP BY DPID	
	----SELECT @DPID=DPID, @VIPCOST=ISNULL(SUM(VIPCOST),0),@VIPNOCOST=ISNULL(SUM(VIPNOCOST),0)  FROM #A GROUP BY DPID
	----第七步：插入月报表
	--INSERT INTO DEPART_MONTH_FORM (DPID,VIPCOST,VIPNOCOST) SELECT DPID,ISNULL(SUM(VIPCOST),0),ISNULL(SUM(VIPNOCOST),0) FROM #A GROUP BY DPID
	----第八步：插入时间，因为时间无法在上一步同时插入只能这样修改了
	--UPDATE DEPART_MONTH_FORM SET TIME=@TIME,ISDELETE=0 WHERE TIME IS NULL
	--DROP TABLE #VIPTEMP
	--DROP TABLE #VIPCOSTTEMP
	--DROP TABLE #VIPNOTEMP
	--DROP TABLE #VIPNOCOSTTEMP
	--DROP TABLE #A 
	insert into DEPART_MONTH_FORM (DPID,VIPCOST,VIPNOCOST,NewVipIncome,VipCharge,VipNoCharge,vipBalance,vipNoBalance)  		
	select t.DPID,ISNULL(t3.VIPCOST,0) VIPCOST,ISNULL(t6.VIPNOCOST,0) VIPNOCOST,ISNULL(t7.NEWVIP,0)*10 newVipIncome,ISNULL(t9.vipCharge,0) vipCharge,ISNULL(t11.vipNoCharge,0) vipNoCharge,ISNULL(t14.vipBalance,0) vipBalance,ISNULL(t18.vipNoBalance,0) vipNoBalance FROM DEPART_INFO t left join 
	--下面两行逻辑比较难以理解，特别备注一下：t1是所有VIP消费记录，连接VIP_BORROW连接条件是注册编号，因为计费是算到租车地的，所以算总合的时候分组方式以t2来分组,非会员的同理可以求出来
	(select SUM(t1.BUCOST) VIPCOST,t2.DPID from((select BUCOST,DPID,BBKID FROM BIKE_RETURN_INFO where ISVIP=1 and RBKTIME between @TIME and @TIMEEND)t1 left join VIP_BORROW_INFO t2 ON t1.BBKID=t2.BBKID)group by (t2.DPID))t3 on (t.DPID=t3.DPID) left join
	(select SUM(t4.BUCOST) VIPNOCOST,t5.DPID from((select BUCOST,DPID,BBKID FROM BIKE_RETURN_INFO where ISVIP=0 and RBKTIME between @TIME and @TIMEEND)t4 left join VIP_NO_BORROW_INFO t5 ON t4.BBKID=t5.BBKID)group by (t5.DPID))t6 on (t.DPID=t6.DPID) left join
	--连接新加会员表
	(select count(*) NEWVIP,DPID from VIP_INFO where ADDDATE between @TIME and @TIMEEND group by (DPID))t7 on(t7.DPID=t.DPID) left join
	--连接会员充值
	(select SUM(t8.CHARGEMONEY) vipCharge,t8.DEPARTMENTID from(select CHARGEMONEY,DEPARTMENTID from RECHARGE_INFO where ISVIP=1 and CHARGETIME between @TIME and @TIMEEND)t8 group by t8.DEPARTMENTID)t9 ON (t9.DEPARTMENTID=t.DPID) left join
	--连接非会员充值
	(select SUM(t10.CHARGEMONEY) vipNoCharge,t10.DEPARTMENTID from(select CHARGEMONEY,DEPARTMENTID from RECHARGE_INFO where ISVIP=0 and CHARGETIME between @TIME and @TIMEEND)t10 group by t10.DEPARTMENTID)t11 ON (t11.DEPARTMENTID=t.DPID) left join
	--连接会员余额
	(select t13.DPID,SUM(t13.VMBALANCE) vipBalance from(select t11.DPID,t11.VIPID,t12.VMBALANCE FROM VIP_INFO t11 left join (select VIPID,VMBALANCE from VIP_MONEY_INFO where ISVIP=1)t12 on t11.VIPID=t12.VIPID)t13 group by t13.DPID)t14 on (t14.DPID=t.DPID) left join
	--连接非会员余额
	(select t17.DPID,SUM(t17.VMBALANCE) vipNoBalance from(select t15.DPID,t15.VIPID,t16.VMBALANCE from VIP_NO_INFO t15 left join (select VIPID,VMBALANCE from VIP_MONEY_INFO where ISVIP=0)t16 on t15.VIPID=t16.VIPID)t17 group by t17.DPID)t18 on(t18.DPID=t.DPID)
END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_MONTH_FORM_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DEPART_MONTH_FORM_DELETE]
@DMID INT
AS
BEGIN
UPDATE DEPART_MONTH_FORM SET ISDELETE=1 WHERE DMID=@DMID
END
GO
/****** Object:  StoredProcedure [dbo].[DAY_COST]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DAY_COST]
@BBKTIME DATETIME,
@RBKTIME DATETIME,
@COSTTIME INT,--每小时起扣时间
@COSTPH MONEY,--每次扣费金额
@CONTIME INT,--起始价包含时间小时数
@COSTBEGIN MONEY,--每小时扣费金额
@BRNUMBER INT,--还车辆数
@ISNIGHT BIT, --是否为包夜租车
@ISVIP BIT,
@USEMONEY MONEY OUTPUT,--扣费金额
@USETIME DATETIME OUTPUT--用车时间 
AS
BEGIN
 DECLARE @TIMESTAP DATETIME--临时时间
 DECLARE @HOURTEMP INT
 DECLARE @DAYTEMP INT
 DECLARE @MINITEMP INT 
 DECLARE @MINITUSER INT
 DECLARE @HOURTEMP1 INT
 SET @USETIME='1900-1-1 00:00:00'
 SET @MINITUSER=DATEDIFF(MINUTE,@BBKTIME,@RBKTIME)
 SET @USETIME=DATEADD(HOUR,@MINITUSER/60,@USETIME)
 SET @USETIME=DATEADD(MINUTE,@MINITUSER%60,@USETIME)
 IF(@ISNIGHT =0)--非包夜租车
 BEGIN
 SELECT @HOURTEMP =DATEDIFF(MINUTE ,@BBKTIME,@RBKTIME)/60--后面的时间减去前面的时间只算小时
 IF(@HOURTEMP<@CONTIME)
 BEGIN
 SET @USEMONEY=@COSTBEGIN*@BRNUMBER  
 END
ELSE 
BEGIN
	SELECT @BBKTIME=DATEADD(HOUR,@CONTIME,@BBKTIME)--将起租时间延后起始价包含的时间然后再计算金额
	SELECT @HOURTEMP =DATEDIFF(MINUTE ,@BBKTIME,@RBKTIME)/60--后面的时间减去前面的时间只算小时
	SELECT @TIMESTAP=DATEADD(HOUR,@HOURTEMP,@BBKTIME)--计算出来小时之后再计算分钟,为租车时间加上补过来的小时
	SELECT @MINITEMP=DATEDIFF(MINUTE,@TIMESTAP,@RBKTIME)--计算分钟数
IF(@MINITEMP<@COSTTIME)
	BEGIN
	SET @USEMONEY=((@HOURTEMP)*@COSTPH+@COSTBEGIN)*@BRNUMBER--算出花费
	END
 ELSE
	BEGIN
	SET @USEMONEY=((@HOURTEMP+1)*@COSTPH+@COSTBEGIN)*@BRNUMBER--算出花费
	END
	--包天功能
    SET @DAYTEMP =DATEDIFF(DAY,@BBKTIME,@RBKTIME)+1
	IF ((@USEMONEY >20*@BRNUMBER*@DAYTEMP)and(@ISVIP =1))
	SELECT @USEMONEY =20*@BRNUMBER*@DAYTEMP 
	SELECT @USEMONEY,@USETIME,@HOURTEMP 
	RETURN 
END
END

ELSE


--包夜租车
BEGIN
 DECLARE @NIGHTCOST INT--包夜花费
 DECLARE @BEGINTIME INT--包夜起始时间
 DECLARE @ENDTIME INT--包夜结束时间
 DECLARE @BBKTIMEBEGIN DATETIME--租车起始那天包夜开始时间
 DECLARE @RBKTIMEEND DATETIME--租车结束那天包夜结束时间
 DECLARE @RBKTIMEBEGIN DATETIME--租车结束那天包夜开始时间
 DECLARE @BBKTIMEEND DATETIME--租车开始那天包夜结束时间
 SELECT @HOURTEMP=0
 SELECT @USEMONEY=0
 IF (@ISVIP =1)
  BEGIN
   SELECT @NIGHTCOST =VIPCOST,@BEGINTIME =BEGINTIME,@ENDTIME =ENDTIME FROM NIGHT_COST_RULE WHERE ID =1
  END
 ELSE
  BEGIN
   SELECT @NIGHTCOST =VIPCOST,@BEGINTIME =BEGINTIME,@ENDTIME =ENDTIME FROM NIGHT_COST_RULE WHERE ID =2
  END
 SELECT @BBKTIMEBEGIN=DATEADD(HOUR,@BEGINTIME,CONVERT(VARCHAR(10),@BBKTIME,111))
 SELECT @RBKTIMEEND=DATEADD(HOUR,@ENDTIME,CONVERT(VARCHAR(10),@RBKTIME,111))
 SELECT @BBKTIMEEND=DATEADD(HOUR,@ENDTIME,CONVERT(VARCHAR(10),@BBKTIME,111))
  SELECT @RBKTIMEBEGIN=DATEADD(HOUR,@BEGINTIME,CONVERT(VARCHAR(10),@RBKTIME,111))
 SELECT @DAYTEMP =DATEDIFF(DAY ,@BBKTIME,@RBKTIME)--后面的时间减去前面的时间只算天
 IF(@BBKTIMEEND>@RBKTIME  and @BBKTIMEEND>@BBKTIME)
  BEGIN
    SELECT @USEMONEY =@NIGHTCOST *@BRNUMBER 
	SELECT @USEMONEY,@USETIME
	RETURN 
  END

 --当天租车当天还
 IF(@DAYTEMP =0)
  BEGIN
   IF(@RBKTIME >@BBKTIMEBEGIN)
    SELECT @DAYTEMP=1
  --未在包夜时间内
   IF((@RBKTIME  BETWEEN @BBKTIMEEND AND  @BBKTIMEBEGIN )AND(@BBKTIME BETWEEN @RBKTIMEEND AND  @RBKTIMEBEGIN))
     BEGIN
      EXEC DAY_COST @BBKTIME=@BBKTIME,
				@RBKTIME=@RBKTIME,
				@COSTTIME=@COSTTIME,
				@COSTPH=@COSTPH,
				@CONTIME=@CONTIME,
				@COSTBEGIN=@COSTBEGIN,
				@BRNUMBER=@BRNUMBER,
				@ISNIGHT =0 ,
				@ISVIP =@ISVIP ,
				@USEMONEY=@USEMONEY OUTPUT,
				@USETIME=@USETIME OUTPUT
	 return
     END
  END
  --不是当 天还
 ELSE
 BEGIN
 --实际租车时间在租车天包夜开始时间之前
 IF(@BBKTIME <@BBKTIMEEND)
 BEGIN
  select @DAYTEMP =@DAYTEMP +1
 END
 ELSE
 BEGIN
  --租车时间距离开始包夜时间的小时
  IF(@BBKTIMEBEGIN>@BBKTIME )
   BEGIN
   IF(DATEDIFF(MINUTE ,@BBKTIME,@BBKTIMEBEGIN)>=@COSTTIME)
      BEGIN
       SELECT @USEMONEY=@COSTBEGIN*@BRNUMBER
      END
   SET @HOURTEMP=DATEDIFF(MINUTE ,@BBKTIME,@BBKTIMEBEGIN)/60--后面的时间减去前面的时间只算小时
   IF(@HOURTEMP <@CONTIME)
    BEGIN
     SET @HOURTEMP=0
    END
   ELSE
    BEGIN
     IF(DATEDIFF(MINUTE ,@BBKTIME,@BBKTIMEBEGIN)%60>=@COSTTIME)
      BEGIN
       SET @HOURTEMP+=1
      END
     SET @HOURTEMP-=@CONTIME
    END
   END
 END
 
 --实际还车时间在还车天包夜开始时间之后
 IF(@RBKTIME >@RBKTIMEBEGIN )
  BEGIN
  SELECT @DAYTEMP =@DAYTEMP +1
  END
  --实际还车时间在还车天包夜开始时间之前
  else
  BEGIN
   IF(@RBKTIMEEND<@RBKTIME)
     BEGIN
     SET @HOURTEMP=@HOURTEMP+DATEDIFF(MINUTE,@RBKTIMEEND,@RBKTIME)/60--后面的时间减去前面的时间只算小时
     END 
  END
  
 END
 if(@DAYTEMP >0)
   SELECT @HOURTEMP=@HOURTEMP+(@DAYTEMP-1)*(@BEGINTIME-@ENDTIME)
    SET @USEMONEY=@USEMONEY+@NIGHTCOST*@DAYTEMP *@BRNUMBER  
    SELECT @MINITEMP =0
 --实际还车时间在还车当天包夜还车时间之后，包夜租车之前
 IF(@RBKTIME between @RBKTIMEEND and @RBKTIMEBEGIN )
  BEGIN 
	SELECT @HOURTEMP1=DATEDIFF(MINUTE ,@RBKTIMEEND,@RBKTIME)/60--后面的时间减去前面的时间只算小时
	SELECT @TIMESTAP=DATEADD(HOUR,@HOURTEMP1,@RBKTIMEEND)--计算出来小时之后再计算分钟,为租车时间加上补过来的小时
	SELECT @MINITEMP=DATEDIFF(MINUTE,@TIMESTAP,@RBKTIME)--计算分钟数
  end
IF(@MINITEMP<@COSTTIME)
	BEGIN
	SELECT @USEMONEY=@USEMONEY+(@HOURTEMP)*@COSTPH*@BRNUMBER--算出花费
	END
 ELSE
	BEGIN
	SELECT @USEMONEY=@USEMONEY+(@HOURTEMP+1)*@COSTPH*@BRNUMBER--算出花费
	END



IF(@BBKTIME >@BBKTIMEBEGIN AND (DATEDIFF(MINUTE,@RBKTIMEEND,@RBKTIME)/60>0)and( DATEDIFF(MINUTE ,@BBKTIME,@BBKTIMEBEGIN)>=@COSTTIME ))
BEGIN
 IF(@ISVIP=1)
  BEGIN
     SELECT @USEMONEY =@USEMONEY -1*@BRNUMBER 
  END
 ELSE
  BEGIN
    SELECT @USEMONEY -@USEMONEY -3*@BRNUMBER 
  END
END

	SELECT @USEMONEY,@USETIME, DATEDIFF(MINUTE ,@BBKTIME,@BBKTIMEBEGIN),@HOURTEMP,@MINITEMP
	RETURN 
END

END

--DECLARE @USEMONEY MONEY --扣费金额
--DECLARE @BUTIME DATETIME --用车
-- EXEC DAY_COST @BBKTIME='2014-9-23 17:45:0',
--				@RBKTIME='2014-9-24 11:56:0',
--				@COSTTIME='10',
--				@COSTPH=2,
--				@CONTIME=1,
--				@COSTBEGIN=3,
--				@BRNUMBER=1,
--				@ISNIGHT =1,
--				@ISVIP =1,
--				@USEMONEY=@USEMONEY OUTPUT,
--				@USETIME=@BUTIME OUTPUT

--select CONVERT(VARCHAR(10),GETDATE(),111) 

--SELECT * FROM BIKE_RETURN_INFO  WHERE BUTIME >'2014-09-24 10:0:0'AND BUTIME <'2014-09-24 11:0:0'
GO
/****** Object:  StoredProcedure [dbo].[COST_RULE_UPDATE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[COST_RULE_UPDATE]
@CRID INT,
@CRNAME VARCHAR(50),
@COSTPH MONEY,
@COSTTIME INT,
@COSTBEGIN MONEY,
@CRKID INT,
@CONTIME INT,
@LVID INT
AS
BEGIN
UPDATE COST_RULE_INFO SET CRNAME=@CRNAME,COSTPH=@COSTPH,COSTTIME=@COSTTIME,COSTBEGIN=@COSTBEGIN,CRKID=@CRKID,CONTIME=@CONTIME,LVID=@LVID WHERE CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[COST_RULE_SELECT_BY_LEVELANDKIND]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.12.29
-- Description:	根据会员等级和租车类型查找扣费规则
-- =============================================
CREATE PROCEDURE [dbo].[COST_RULE_SELECT_BY_LEVELANDKIND]
	@LVID INT,
	@KIND INT
AS
BEGIN
	SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN,CRKID,CONTIME,LVID FROM COST_RULE_INFO WHERE ISDELETE!=1 AND LVID=@LVID AND CRKID=@KIND
END
GO
/****** Object:  StoredProcedure [dbo].[COST_RULE_SELECT_BY_CRID]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[COST_RULE_SELECT_BY_CRID]
@CRID INT
AS
BEGIN
SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN,CRKID,CONTIME,LVID FROM COST_RULE_INFO WHERE ISDELETE!=1 AND CRID=@CRID
END
GO
/****** Object:  StoredProcedure [dbo].[COST_RULE_SELECT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[COST_RULE_SELECT]
AS
BEGIN
SELECT CRID,CRNAME,COSTPH,COSTTIME,COSTBEGIN,CRKID,CONTIME,LVID FROM COST_RULE_INFO WHERE ISDELETE!=1 order by CRKID asc
END
GO
/****** Object:  StoredProcedure [dbo].[COST_RULE_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[COST_RULE_INSERT]
@CRNAME VARCHAR(50),
@COSTPH MONEY,
@COSTTIME INT,
@COSTBEGIN MONEY,
@CRKID INT,
@CONTIME INT,
@LVID INT
AS
BEGIN
INSERT INTO COST_RULE_INFO (CRNAME,COSTPH,COSTTIME,COSTBEGIN,CRKID,CONTIME,ISDELETE,LVID) VALUES (@CRNAME,@COSTPH,@COSTTIME,@COSTBEGIN,@CRKID,@CONTIME,0,@LVID)
END
GO
/****** Object:  StoredProcedure [dbo].[DEPART_DELETE]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		海学权
-- Create date: 2013.11.27
-- Description:	删除分店
-- =============================================
CREATE PROCEDURE [dbo].[DEPART_DELETE]
	@DPID INT
AS
BEGIN
	UPDATE DEPART_INFO SET ISDELETE=1 WHERE DPID=@DPID
	UPDATE BIKE_RETURN_INFO SET ISDELETE=1 WHERE DPID=@DPID
	UPDATE DEPART_MONTH_FORM SET ISDELETE=1 WHERE DPID=@DPID
	UPDATE DEPART_YEAR_FORM SET ISDELETE=1 WHERE DPID=@DPID
	UPDATE LOG_INFO SET ISDELETE=1 WHERE DPID=@DPID
	UPDATE U_INFO SET ISDELETE=1 WHERE DPID=@DPID
	
	UPDATE VIP_BORROW_INFO SET ISDELETE=1 WHERE DPID=@DPID
	EXEC VIP_DELETE_BY_DPID @DPID=@DPID
	EXEC VIP_NO_DELETE_BY_DPID @DPID=@DPID
	EXEC USER_DELETE_BY_DPID @DPID=@DPID
END
GO
/****** Object:  StoredProcedure [dbo].[BIKE_RETURN_INSERT]    Script Date: 09/24/2015 19:14:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BIKE_RETURN_INSERT]
@BBKID INT,--租车信息ID
@RBKTIME DATETIME,--还车 时间      
@VIPID INT,--还车vipid
@DPID INT,--还车分店id	
@BRNUMBER INT,--还车数量
@ISVIP BIT,--是否是VIP还车
@USERID INT,--还车操作用户ID
@RBKID INT OUT
AS
 BEGIN
 BEGIN TRY
 DECLARE @BBKTIME DATETIME
 DECLARE @BBKNUM INT
 DECLARE @BUTIMETEMP DATETIME
 DECLARE @VIPLVID INT
 DECLARE @RUID INT--扣费规则ID
 DECLARE @COSTPH MONEY--每小时扣费金额
 DECLARE @COSTTIME INT--扣费起始时间
 DECLARE @COSTBEGIN MONEY--起始扣费金额
 DECLARE @CRKIND INT--规则类型
 DECLARE @CONTIME INT--起始扣费金额包含时间
 DECLARE @USEMONEY MONEY --花费金额
 DECLARE @BUTIME DATETIME--租车时间
 DECLARE @MONEYLEFT MONEY--剩余金额
 DECLARE @INTEGRAL VARCHAR(200) --剩余积分
 DECLARE @PROPOR VARCHAR(200) --兑换比例(积分/钱的比例即一元钱多少积分)
 DECLARE @ISNIGHT BIT=0 --是否为包夜租车
 DECLARE @MARK VARCHAR(1000)--备注
 IF(@ISVIP=1)
 BEGIN
 SELECT @BBKNUM=LEFTNUM FROM VIP_BORROW_INFO WHERE BBKID=@BBKID
 END
 ELSE 
 BEGIN
 SELECT @BBKNUM =LEFTNUM FROM VIP_NO_BORROW_INFO WHERE BBKID=@BBKID
 END 
 IF(@BBKNUM<@BRNUMBER)
 BEGIN
 RETURN -2
 END
 ELSE
 BEGIN
 IF(@ISVIP=1)--是VIP,1是ture
 BEGIN
    select	@MARK ='电话:'+VIPPHONE+'备注:' FROM VIP_INFO WHERE  VIPID =@VIPID
    SELECT  @MARK +=MARK FROM  VIP_BORROW_INFO WHERE BBKID=@BBKID--备注
    SELECT @MARK =SUBSTRING(@MARK ,1,50)
	SELECT @BBKTIME=BBKTIME FROM VIP_BORROW_INFO WHERE BBKID=@BBKID--取得租车时间
	SELECT @VIPLVID=VIPLEVELID  FROM VIP_INFO WHERE VIPID=@VIPID--先根据VIP的ID获得该VIP的等级
	SELECT @CRKIND =RKRID  FROM VIP_BORROW_INFO WHERE BBKID=@BBKID--根据该租车编号获得租车类型是日租月租还是年租
	SELECT @ISNIGHT =ISNIGHT FROM VIP_BORROW_INFO WHERE BBKID =@BBKID 
END
ELSE
BEGIN
    SELECT @MARK ='电话:'+VNPHONE+MARK  FROM VIP_NO_BORROW_INFO  WHERE BBKID=@BBKID --获取备注
    SELECT @MARK =SUBSTRING(@MARK ,1,50)
	SELECT @BBKTIME =BBKTIME FROM VIP_NO_BORROW_INFO WHERE BBKID=@BBKID
	SELECT @VIPLVID=LVID FROM VIP_NO_INFO WHERE VIPID=@VIPID--先根据VIP的ID获得该VIP的等级
	SELECT @CRKIND =RKRID  FROM VIP_NO_BORROW_INFO WHERE BBKID=@BBKID--根据该租车编号获得租车类型是日租月租还是年租
		SELECT @ISNIGHT =ISNIGHT FROM VIP_NO_BORROW_INFO WHERE BBKID =@BBKID 
END
 SELECT @COSTPH=COSTPH,@COSTTIME=COSTTIME,@COSTBEGIN=COSTBEGIN,@CONTIME=CONTIME FROM COST_RULE_INFO WHERE LVID=@VIPLVID AND CRKID=@CRKIND AND ISDELETE!=1
 IF(@CRKIND=1)--日租
 BEGIN
 EXEC DAY_COST @BBKTIME=@BBKTIME,
				@RBKTIME=@RBKTIME,
				@COSTTIME=@COSTTIME,
				@COSTPH=@COSTPH,
				@CONTIME=@CONTIME,
				@COSTBEGIN=@COSTBEGIN,
				@BRNUMBER=@BRNUMBER,
				@ISNIGHT =@ISNIGHT ,
				@ISVIP =@ISVIP ,
				@USEMONEY=@USEMONEY OUTPUT,
				@USETIME=@BUTIME OUTPUT
 END
ELSE IF(@CRKIND=2)--月租
 BEGIN
	EXEC MONTH_COST @BBKTIME=@BBKTIME,
				@RBKTIME=@RBKTIME,
				@COSTTIME=@COSTTIME,
				@COSTPH=@COSTPH,
				@CONTIME=@CONTIME,
				@COSTBEGIN=@COSTBEGIN,
				@BRNUMBER=@BRNUMBER,
				@USEMONEY=@USEMONEY OUTPUT,
				@USETIME=@BUTIME OUTPUT
 END
ELSE IF(@CRKIND=3)--年租
 BEGIN
 EXEC YEAR_COST @BBKTIME=@BBKTIME,
				@RBKTIME=@RBKTIME,
				@COSTTIME=@COSTTIME,
				@COSTPH=@COSTPH,
				@CONTIME=@CONTIME,
				@COSTBEGIN=@COSTBEGIN,
				@BRNUMBER=@BRNUMBER,
				@USEMONEY=@USEMONEY OUTPUT,
				@USETIME=@BUTIME OUTPUT
 END
 --这里将租车信息中的租车数量减去
 SET @BBKNUM=@BBKNUM-@BRNUMBER
  IF(@ISVIP=1)
 BEGIN
	UPDATE VIP_BORROW_INFO SET LEFTNUM=@BBKNUM WHERE BBKID=@BBKID
	IF(@BBKNUM=0)
		BEGIN
		UPDATE VIP_BORROW_INFO SET ISRETURN=1 WHERE BBKID=@BBKID
		END
 END
 ELSE 
 BEGIN
	UPDATE VIP_NO_BORROW_INFO SET LEFTNUM=@BBKNUM WHERE BBKID=@BBKID
	IF(@BBKNUM=0)
	BEGIN
	UPDATE VIP_NO_BORROW_INFO SET ISRETURN=1 WHERE BBKID=@BBKID
	END
 END
 --减去金额
 SET @USEMONEY=ISNULL(@USEMONEY,0) 
 SELECT @MONEYLEFT=VMBALANCE  FROM VIP_MONEY_INFO WHERE VIPID=@VIPID AND ISVIP=@ISVIP
 SET @MONEYLEFT=@MONEYLEFT-@USEMONEY
 UPDATE VIP_MONEY_INFO SET VMBALANCE=ISNULL(@MONEYLEFT,0) WHERE VIPID=@VIPID AND ISVIP=@ISVIP
 IF(@ISVIP=1)
 BEGIN
 --增加积分
 
 SELECT @INTEGRAL=INTEGRAL FROM VIP_MONEY_INFO WHERE VIPID=@VIPID AND ISVIP=@ISVIP
 SELECT @PROPOR=VALUE FROM WHOLE_CONFIG WHERE ID=1
 IF(@PROPOR!=0)
 BEGIN
 SET @INTEGRAL=@INTEGRAL+(@USEMONEY/@PROPOR)
 UPDATE VIP_MONEY_INFO SET INTEGRAL=@INTEGRAL WHERE VIPID=@VIPID AND ISVIP=@ISVIP
 END
 END
 --添加还车记录
 INSERT INTO BIKE_RETURN_INFO (BBKID,RBKTIME,VIPID,DPID,BUCOST,BUTIME,BRNUMBER,ISVIP,USERID,ISDELETE,MARK ) VALUES (@BBKID,@RBKTIME,@VIPID,@DPID,@USEMONEY,ISNULL(@BUTIME,'1900-01-01 00:00:00'),@BRNUMBER,@ISVIP,@USERID,0,@MARK)
 SET @RBKID=@@IDENTITY
 
 RETURN 1
END
 END TRY
 BEGIN CATCH
 INSERT INTO EXCEPTION_INFO (EXCONTENT,ECFUNC,EXTIME) VALUES ('还车出错'+ERROR_MESSAGE(),'[BIKE_RETURN_INSERT]',GETDATE())
 END CATCH
END

-- DECLARE @R INT
--exec BIKE_RETURN_INSERT
--@BBKID=9,--租车信息ID
--@RBKTIME='2014-8-25 20:25:0',--还车时间      
--@VIPID=1,--还车vipid
--@DPID =3,--还车分店id	
--@BRNUMBER=1,--还车数量
--@ISVIP=0,--是否是VIP还车
--@USERID=2,--还车操作用户ID
--@RBKID=@R  OUT
GO
/****** Object:  Default [DF_DEPART_YEAR_FORM_VIPCOST]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  CONSTRAINT [DF_DEPART_YEAR_FORM_VIPCOST]  DEFAULT ((0)) FOR [VIPCOST]
GO
/****** Object:  Default [DF_DEPART_YEAR_FORM_VIPNOCOST]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  CONSTRAINT [DF_DEPART_YEAR_FORM_VIPNOCOST]  DEFAULT ((0)) FOR [VIPNOCOST]
GO
/****** Object:  Default [DF_DEPART_YEAR_FORM_TIME]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  CONSTRAINT [DF_DEPART_YEAR_FORM_TIME]  DEFAULT (getdate()) FOR [TIME]
GO
/****** Object:  Default [DF_DEPART_YEAR_FORM_ISDELETE]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  CONSTRAINT [DF_DEPART_YEAR_FORM_ISDELETE]  DEFAULT ((0)) FOR [ISDELETE]
GO
/****** Object:  Default [DF__DEPART_YE__NewVi__4F47C5E3]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  DEFAULT ((0)) FOR [NewVipIncome]
GO
/****** Object:  Default [DF__DEPART_YE__VipCh__503BEA1C]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  DEFAULT ((0)) FOR [VipCharge]
GO
/****** Object:  Default [DF__DEPART_YE__VipNo__51300E55]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  DEFAULT ((0)) FOR [VipNoCharge]
GO
/****** Object:  Default [DF__DEPART_YE__VipBa__5224328E]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  DEFAULT ((0)) FOR [VipBalance]
GO
/****** Object:  Default [DF__DEPART_YE__VipNo__531856C7]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  DEFAULT ((0)) FOR [VipNoBalance]
GO
/****** Object:  Default [DF__DEPART_YE__Total__540C7B00]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_YEAR_FORM] ADD  DEFAULT ((0)) FOR [Total]
GO
/****** Object:  Default [DF_DEPART_MONTH_FORM_VIPCOST]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  CONSTRAINT [DF_DEPART_MONTH_FORM_VIPCOST]  DEFAULT ((0)) FOR [VIPCOST]
GO
/****** Object:  Default [DF_DEPART_MONTH_FORM_VIPNOCOST]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  CONSTRAINT [DF_DEPART_MONTH_FORM_VIPNOCOST]  DEFAULT ((0)) FOR [VIPNOCOST]
GO
/****** Object:  Default [DF_DEPART_MONTH_FORM_TIME]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  CONSTRAINT [DF_DEPART_MONTH_FORM_TIME]  DEFAULT (getdate()) FOR [TIME]
GO
/****** Object:  Default [DF_DEPART_MONTH_FORM_ISDELETE]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  CONSTRAINT [DF_DEPART_MONTH_FORM_ISDELETE]  DEFAULT ((0)) FOR [ISDELETE]
GO
/****** Object:  Default [DF__DEPART_MO__NewVi__58D1301D]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  DEFAULT ((0)) FOR [NewVipIncome]
GO
/****** Object:  Default [DF__DEPART_MO__VipCh__59C55456]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  DEFAULT ((0)) FOR [VipCharge]
GO
/****** Object:  Default [DF__DEPART_MO__VipNo__5AB9788F]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  DEFAULT ((0)) FOR [VipNoCharge]
GO
/****** Object:  Default [DF__DEPART_MO__VipBa__5BAD9CC8]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  DEFAULT ((0)) FOR [VipBalance]
GO
/****** Object:  Default [DF__DEPART_MO__VipNo__5CA1C101]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  DEFAULT ((0)) FOR [VipNoBalance]
GO
/****** Object:  Default [DF__DEPART_MO__Total__5D95E53A]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[DEPART_MONTH_FORM] ADD  DEFAULT ((0)) FOR [Total]
GO
/****** Object:  Default [DF__COST_RULE__COSTP__5E8A0973]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[COST_RULE_INFO] ADD  DEFAULT ((0)) FOR [COSTPH]
GO
/****** Object:  Default [DF__COST_RULE__COSTB__5F7E2DAC]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[COST_RULE_INFO] ADD  DEFAULT ((0)) FOR [COSTBEGIN]
GO
/****** Object:  Default [DF__BIKE_RETU__BUCOS__607251E5]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[BIKE_RETURN_INFO] ADD  DEFAULT ((0)) FOR [BUCOST]
GO
/****** Object:  Default [DF_BIKE_RETURN_INFO_BUTIME]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[BIKE_RETURN_INFO] ADD  CONSTRAINT [DF_BIKE_RETURN_INFO_BUTIME]  DEFAULT ('1900-01-01 00:00:00') FOR [BUTIME]
GO
/****** Object:  Default [DF_MONTH_COST_RULE_COSTPH]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[MONTH_COST_RULE] ADD  CONSTRAINT [DF_MONTH_COST_RULE_COSTPH]  DEFAULT ((0)) FOR [COSTPH]
GO
/****** Object:  Default [DF_MONTH_COST_RULE_COSTBEGIN]    Script Date: 09/24/2015 19:14:12 ******/
ALTER TABLE [dbo].[MONTH_COST_RULE] ADD  CONSTRAINT [DF_MONTH_COST_RULE_COSTBEGIN]  DEFAULT ((0)) FOR [COSTBEGIN]
GO
/****** Object:  Default [DF_VIP_BORROW_INFO_ISDELETE]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_BORROW_INFO] ADD  CONSTRAINT [DF_VIP_BORROW_INFO_ISDELETE]  DEFAULT ((0)) FOR [ISDELETE]
GO
/****** Object:  Default [DF_VIP_BORROW_INFO_ISRETURN]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_BORROW_INFO] ADD  CONSTRAINT [DF_VIP_BORROW_INFO_ISRETURN]  DEFAULT ((0)) FOR [ISRETURN]
GO
/****** Object:  Default [DF_VIP_BORROW_INFO_ISNIGHT]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_BORROW_INFO] ADD  CONSTRAINT [DF_VIP_BORROW_INFO_ISNIGHT]  DEFAULT ((0)) FOR [ISNIGHT]
GO
/****** Object:  Default [DF_U_INFO_ADDDATE]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[U_INFO] ADD  CONSTRAINT [DF_U_INFO_ADDDATE]  DEFAULT (getdate()) FOR [ADDDATE]
GO
/****** Object:  Default [DF__VIP_MONEY__VMBAL__4E88ABD4]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_MONEY_INFO] ADD  CONSTRAINT [DF__VIP_MONEY__VMBAL__4E88ABD4]  DEFAULT ((0)) FOR [VMBALANCE]
GO
/****** Object:  Default [DF_VIP_MONEY_INFO_ISDELETE]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_MONEY_INFO] ADD  CONSTRAINT [DF_VIP_MONEY_INFO_ISDELETE]  DEFAULT ((0)) FOR [ISDELETE]
GO
/****** Object:  Default [DF_VIP_MONEY_INFO_INTEGRAL]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_MONEY_INFO] ADD  CONSTRAINT [DF_VIP_MONEY_INFO_INTEGRAL]  DEFAULT ((0)) FOR [INTEGRAL]
GO
/****** Object:  Default [DF_VIP_NO_BORROW_INFO_ISDELETE]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_NO_BORROW_INFO] ADD  CONSTRAINT [DF_VIP_NO_BORROW_INFO_ISDELETE]  DEFAULT ((0)) FOR [ISDELETE]
GO
/****** Object:  Default [DF_VIP_NO_BORROW_INFO_ISRETURN]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_NO_BORROW_INFO] ADD  CONSTRAINT [DF_VIP_NO_BORROW_INFO_ISRETURN]  DEFAULT ((0)) FOR [ISRETURN]
GO
/****** Object:  Default [DF_VIP_NO_BORROW_INFO_ISNIGHT]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[VIP_NO_BORROW_INFO] ADD  CONSTRAINT [DF_VIP_NO_BORROW_INFO_ISNIGHT]  DEFAULT ((0)) FOR [ISNIGHT]
GO
/****** Object:  Default [DF_YEAR_COST_RULE_COSTPH]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[YEAR_COST_RULE] ADD  CONSTRAINT [DF_YEAR_COST_RULE_COSTPH]  DEFAULT ((0)) FOR [COSTPH]
GO
/****** Object:  Default [DF_YEAR_COST_RULE_COSTBEGIN]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[YEAR_COST_RULE] ADD  CONSTRAINT [DF_YEAR_COST_RULE_COSTBEGIN]  DEFAULT ((0)) FOR [COSTBEGIN]
GO
/****** Object:  Default [DF__RECHARGE___CHARG__6FB49575]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[RECHARGE_INFO] ADD  DEFAULT (getdate()) FOR [CHARGETIME]
GO
/****** Object:  Default [DF__RECHARGE___BALAN__70A8B9AE]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[RECHARGE_INFO] ADD  DEFAULT ((0)) FOR [BALANCE]
GO
/****** Object:  Default [DF__RECHARGE___CHARG__719CDDE7]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[RECHARGE_INFO] ADD  DEFAULT ((0)) FOR [CHARGEMONEY]
GO
/****** Object:  Default [DF__RECHARGE___ISDEL__72910220]    Script Date: 09/24/2015 19:14:16 ******/
ALTER TABLE [dbo].[RECHARGE_INFO] ADD  DEFAULT ((0)) FOR [ISDELETE]
GO