jfsms_db.sql

主要功能如题

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JFoperator]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JFoperator]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RFDorig]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RFDorig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RFDconf]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RFDconf]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JFsetting]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JFsetting]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MsgJF]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MsgJF]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JFparti]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JFparti]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FLlist]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FLlist]
GO



CREATE TABLE [dbo].[JFoperator] (
	[OperID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL  
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RFDorig] (
	[UserID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RFdate] [datetime] NOT NULL ,
	[Money] [numeric] (9,2) NOT NULL DEFAULT 0,
	[OperID] [varchar] (20) NOT NULL ,
	[Status] [smallint] NOT NULL,
	[JFtype] [smallint] NOT NULL,
	[ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[OperTime] [datetime] NOT NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[FLname] [varchar] (20) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RFDconf] (
	[UserID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[RFdate] [datetime] NOT NULL ,
	[Money] [numeric] (9,2) NOT NULL DEFAULT 0,
	[JFtype] [smallint] NOT NULL,
	[ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Status] [smallint] NOT NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[FLname] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JFsetting] (
	[UserID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FLid] [int] NOT NULL DEFAULT 0,
	[StartDate] [datetime] NULL ,
	[EndDate] [datetime] NULL  
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MsgJF] (
	[UserID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SendFlag] [smallint] NOT NULL DEFAULT 1,
	[Cmoney] [numeric] (9,2) NOT NULL DEFAULT 0,
	[SendedNum] [int] NOT NULL DEFAULT 0,
	[TotalMoney] [numeric] (9,2) NOT NULL DEFAULT 0,
	[SendableNum] [int] NOT NULL DEFAULT 0
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[JFparti] (
	[UserID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[SendTime] [datetime] NOT NULL ,
	[Money] [numeric] (9,2) NOT NULL DEFAULT 0,
	[JFtype] [smallint] NOT NULL,
	[JFtime] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FLlist] (
	[FLid] [int] NOT NULL,
	[JFtype] [smallint] NOT NULL ,
	[JFunit] [int] NOT NULL DEFAULT 1,
	[JFquan] [numeric] (9,2) NOT NULL DEFAULT 0, 
	[FLname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Status] [smallint] NOT NULL DEFAULT 0
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JFoperator] WITH NOCHECK ADD 
	CONSTRAINT [PK_JFoperator] PRIMARY KEY  CLUSTERED 
	(
		[OperID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[RFDconf] WITH NOCHECK ADD
	CONSTRAINT [PK_RFDconf] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[JFsetting] WITH NOCHECK ADD
	CONSTRAINT [PK_JFsetting] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[MsgJF] WITH NOCHECK ADD
	CONSTRAINT [PK_MsgJF] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[FLlist] WITH NOCHECK ADD
	CONSTRAINT [PK_FLlist] PRIMARY KEY  CLUSTERED 
	(
		[FLid]
	)  ON [PRIMARY] 
GO


if exists(select * from sysobjects where name='sysUser_Insert' and type='TR')
	drop trigger sysUser_Insert
GO

create trigger sysUser_Insert
on sysUser for INSERT 
as
	declare @Uid varchar(20),@rate real, @Fid int,@Jtype smallint,@Mo real,@err int;
	select @Uid=userID from Inserted;
	select @rate=FLid from FLlist where Status=1;
	
	begin tran

	if @rate>0
		insert into JFsetting(UserID,FLid) values(@Uid,@rate);
	else
		insert into JFsetting(Userid) values(@Uid);

	insert into MsgJF(UserID) values(@Uid);

	commit tran

Go

if exists(select * from sysobjects where name='MsgSend_Update' and type='TR')
	drop trigger MsgSend_Update
GO

create trigger MsgSend_Update
on MsgSend for UPDATE 
as
	declare @Uid varchar(20),@Mcode varchar(20),@Stime datetime,@rate real, @Fid int,@Jtype smallint,@Mo real;
	if update(Type)
	begin
		select @Uid=userID,@Mcode=Mobile,@Stime=SendTime from Inserted;
		select @Fid=FLid from JFsetting where UserID=@Uid;
		select @Jtype=JFtype from FLlist where FLid=@Fid;
		
		begin tran
		
		if @Fid>0
		begin
			if @Jtype=0
			begin

				select @rate=FLlist.JFquan/FLlist.JFunit from FLlist where FLid=@Fid;
				update MsgJF with (rowlock) set Cmoney=Cmoney-@rate,SendedNum=SendedNum+1,TotalMoney=TotalMoney+@rate,SendableNum=FLOOR(Cmoney/@rate) where UserID=@Uid;
				update MsgJF with (rowlock) set SendableNum=FLOOR(Cmoney/@rate) where UserID=@Uid;
				select @Mo=Cmoney from MsgJF where UserID=@Uid;
				if @Mo>=@rate
					update MsgJF with (rowlock) set SendFlag=0 where UserID=@Uid;
				else
					update MsgJF with (rowlock) set SendFlag=1 where UserID=@Uid;

				insert JFparti(UserID,Mobile,SendTime,Money,JFtype,JFtime) values(@Uid,@Mcode,@Stime,@rate,@Jtype,GetDate());
					

			end

			else

			begin
					
				declare @Sdate datetime,@Edate datetime;
				select @Sdate=Startdate,@Edate=EndDate from JFsetting where UserID=@Uid;
					
				if getdate()<dateadd(day,1,@Edate) and getdate()>=@Sdate
					update MsgJF with (rowlock) set SendFlag=0,SendableNum=-1,SendedNum=SendedNum+1 where UserID=@Uid;
				else
					update MsgJF with (rowlock) set SendFlag=1,SendableNum=0,SendedNum=SendedNum+1 where UserID=@Uid;

				insert JFparti(UserID,Mobile,SendTime,JFtype,JFtime) values(@Uid,@Mcode,@Stime,@Jtype,GetDate());

			end
	
		end

		commit tran

	end
Go