Fetch the repository succeeded.
This action will force synchronization from 济南驰骋公司/CCFlow, which will overwrite any changes that you have made since you forked the repository, and can not be recovered!!!
Synchronous operation will process in the background and will refresh the page when finishing processing. Please be patient.
CREATE TABLE [UserRecentContact](
[UserID] [varchar](50) NOT NULL,
[ContactID] [varchar](50) NULL,
[ContactType] [int] NULL,
[ContactTime] [varchar](50) NULL,
[OrderID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [SMS](
[msgID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[smsMsgContent] [text] NOT NULL,
[sendID] [nchar](20) NOT NULL,
[datetime] [datetime] NOT NULL,
CONSTRAINT [PK_SMS] PRIMARY KEY CLUSTERED
(
[msgID] 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
CREATE TABLE [sendCount](
[MsgID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[smsMsgContent] [text] NOT NULL,
[saveDate] [datetime] NOT NULL,
CONSTRAINT [PK_sendCount] PRIMARY KEY CLUSTERED
(
[MsgID] 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
CREATE TABLE [RecordMsgUser](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[MsgId] [numeric](18, 0) NOT NULL,
[ReceiveID] [nchar](20) NOT NULL,
[isSendSuccess] [bit] NOT NULL,
[isSave] [bit] NOT NULL,
CONSTRAINT [PK_GroupRecordMsg] 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
CREATE function [Get_StrArrayStrOfIndex]
(
@str varchar(8000), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，
--跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
end
GO
CREATE function [dbo].[Get_StrArrayLength]
(
@str varchar(8000), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
GO
CREATE TABLE [CustomGroup](
[GroupID] [int] IDENTITY(1,1) NOT NULL,
[GroupName] [varchar](50) NULL,
[Notice] [nvarchar](200) NULL,
[users] [ntext] NULL,
[UserID] [varchar](50) NULL,
[CreateDateTime] [datetime] NULL,
CONSTRAINT [PK_CustomGroup] PRIMARY KEY CLUSTERED
(
[GroupID] 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
CREATE FUNCTION [GetParentDeptID](@DeptID varchar(1000))
RETURNS int
AS
BEGIN
DECLARE @ParentDeptID int
--传入的是字符型部门编号（能转换为整数），编码规则为aabbccdd
IF LEN(@DeptID)=2
SET @ParentDeptID=0 --最上一级
ELSE
BEGIN
SET @ParentDeptID=CAST(LEFT(@DeptID,LEN(@DeptID)-2) as int)
END
RETURN @ParentDeptID
END
GO
CREATE TABLE [RecordMsg](
[MsgID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[sendID] [nchar](200) NULL,
[msgDateTime] [datetime] NULL,
[msgContent] [text] NULL,
[ImageInfo] [ntext] NULL,
[fontName] [nchar](30) NULL,
[fontSize] [float] NULL,
[fontBold] [bit] NULL,
[fontItalic] [bit] NULL,
[fontStrikeout] [bit] NULL,
[fontUnderline] [bit] NULL,
[fontColor] [int] NULL,
[InfoClass] [int] NULL,
[SMSInfo] [tinyint] NULL,
[GroupID] [int] NULL,
[SendUserID] [nchar](200) NULL,
CONSTRAINT [PK_RecordMsg] PRIMARY KEY CLUSTERED
(
[MsgID] 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
CREATE FUNCTION [MD5]
(
-- 源字符串
@src nvarchar(max),
-- 加密类型(16/32)
@type int = 32
)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
-- 存放md5加密串(ox)
DECLARE @smd5 varchar(34)
-- 加密字符串
SELECT @smd5 =sys.fn_VarBinToHexStr(hashbytes('MD5', @src));
IF @type=16
SELECT @smd5 = SUBSTRING(@smd5,11,16) --16位
ELSE
SELECT @smd5 = SUBSTRING(@smd5,3,32) --32位
-- 返回加密串
RETURN @smd5
END
GO
CREATE TABLE [IMUser](
[UserID] [varchar](50) NOT NULL,
[orderID] [int] NULL,
[headPicIdx] [int] NULL,
[onlineState] [tinyint] NULL,
[lastIP] [char](30) NULL,
[lastPort] [int] NULL,
[lastNetClass] [tinyint] NULL,
[lastDate] [datetime] NULL,
[onlineDateLength] [int] NULL,
[CreateGroupMax] [tinyint] NULL,
[isSendSMS] [tinyint] NULL,
[isEditUserData] [tinyint] NULL,
[isAdmin] [bit] NULL,
[Remark] [nvarchar](200) NULL,
[isAbandan] [bit] NULL,
[DepsVersion] [char](32) NULL,
[UsersVersion] [char](32) NULL,
[DepsCount] [int] NULL,
[UsersCount] [int] NULL,
[headPicFile] [nvarchar](256) NULL,
[SID] [varchar](20) NULL,
[Sex] [varchar](50) NULL,
[Tel] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[Fax] [varchar](50) NULL,
[EMail] [varchar](50) NULL,
[Address] [varchar](200) NULL,
[PostCode] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [IMDept](
[DeptID] [int] NOT NULL,
[orderID] [int] NULL,
[Phone] [varchar](50) NULL,
[Fax] [varchar](50) NULL,
[isAbandan] [bit] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE [GroupMsgRecordInsertUsers]
-- Add the parameters for the stored procedure here
@MsgId int,
@ReceiveID varchar(8000) --消息多个接收者用分号隔开
--@isSave bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.---1
SET NOCOUNT ON;
declare @next int
set @next=1
while @next<dbo.Get_StrArrayLength(@ReceiveID,';')
begin
insert into RecordMsgUser (MsgId,ReceiveID) values(@MsgId ,dbo.Get_StrArrayStrOfIndex(@ReceiveID,';',@next) )
set @next=@next+1
end
END
GO
CREATE PROC [GetOrgInfo]
@userID varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 1 * FROM IMUser
END
GO
CREATE PROCEDURE [GetAllUsersSigns]
AS
BEGIN
SET NOCOUNT ON;
SELECT UserID,Remark from IMUser where UserID<>'admin'
END
GO
CREATE PROCEDURE [CustomGroupGetInfoFromGroupID]
-- Add the parameters for the stored procedure here
@GroupID int
AS
BEGIN
SET NOCOUNT ON;
SELECT * from customGroup where GroupID=@GroupID
END
GO
CREATE PROCEDURE [CustomGroupGetIDs]
-- Add the parameters for the stored procedure here
--@userID varchar(20)
@userID nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT GroupID,GroupName from customGroup where userID=@userID or users like '%' + @userID + ';%'
END
GO
CREATE PROCEDURE [CustomGroupCreateOrUpdate]
-- Add the parameters for the stored procedure here
@GroupID int,
@GroupName nvarchar(20),
@Notice nchar(100),
@Users text,
@userID nvarchar(20)
AS
BEGIN
declare @createMax int --标识用户用户组创建充许的最大值数
declare @createCount int --标识用户已经创建用户组的数目
declare @returnGroupID int--标识创建成功的用户组号，不成功为0；
set @createMax=1 --假设当前用户创建的最大值数
set @createCount=0 --假设当前用户已经创建的用户组数
set @returnGroupID=0--假设创建没有成功
SET NOCOUNT ON;
if @GroupID=0 --表示用户是创建新用户组
begin
--获取用户已经创建用户组的数目
select @createCount=count(*) from CustomGroup where UserID=@userID
--获取用户用户组创建充许的最大值
--select @createMax=CreateGroupMax from SystemUsers where UserID=@userID
--select @createMax=CreateGroupMax from Users where UserID=@userID
select @createMax=CreateGroupMax from IMUser where UserID=@userID
if @createCount<@createMax --如果还未达到最大值，则创建用户组，并返回创建组号
begin
insert into CustomGroup(GroupName,Notice,users,UserID) values( @GroupName,@Notice,@Users,@userID)--将创建用户组的信息存入表中
set @returnGroupID=@@IDENTITY --返回已创建或更新的用户组号码
end
end
if @GroupID<>0 --表示用户是更新其创建用户组的信息
begin
select @createCount=count(*) from CustomGroup where GroupID=@GroupID and UserID=@userID --判断要更新的用户组是否当前用户创建的
if @createCount=0 --如果不是当前用户创建的用户组，则无权更新信息
begin
set @returnGroupID=0
end
if @createCount=1 --如果是当前用户创建的用户组，则更新信息
begin
update CustomGroup set GroupName=@GroupName,Notice=@Notice,Users=@Users where GroupID=@GroupID
set @returnGroupID=@GroupID --设置返回用户组的ID
end
end
return @returnGroupID --返回已创建或更新的用户组号码
SET NOCOUNT OFF
END
GO
CREATE VIEW [V_Emp]
AS
SELECT [No] as UserID,[Name] as UserName,Pass,CAST(FK_Dept as int) as DeptID FROM Port_Emp
GO
CREATE VIEW [V_Dept]
AS
--SELECT CAST([No] as int) as DeptID,[Name] as DeptName,dbo.GetParentDeptID(No) as ParentDeptID
--FROM Port_Dept
SELECT CAST([No] as int) as DeptID,[Name] as DeptName,CAST(ParentNo as int) as ParentDeptID FROM Port_Dept
GO
CREATE PROCEDURE [UserUpdateSign]
@userID nvarchar(20),--用户ID
@Sign varchar(200) --签名
AS
BEGIN
SET NOCOUNT ON;
update IMUser set Remark=@Sign where userID=@userID
END
GO
CREATE PROCEDURE [UserUpdateOnlineState]
-- Add the parameters for the stored procedure here
--@userID varchar(20),--用户ID
@userID nvarchar(20),--用户ID
@State tinyint-- 0<用户状态值<255
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @State=0--如果用户离线，则计算并保存其在线的时间
begin
--update systemUsers set onlineState=@State,onlineDateLength=(select isnull(onlineDateLength,0)+datediff(ss,lastDate,getdate()) from systemUsers where UserID=@userID) where UserID=@userID
update IMUser set onlineState=@State,onlineDateLength=(select isnull(onlineDateLength,0)+datediff(ss,lastDate,getdate()) from IMUser where UserID=@userID) where UserID=@userID
end
else
begin--否则只更新用户在线状态
--update systemUsers set onlineState=@State where UserID=@userID
update IMUser set onlineState=@State where UserID=@userID
end
END
GO
CREATE PROCEDURE [UserUpdateOnlineInfo]
-- Add the parameters for the stored procedure here
--@userID varchar(20),--用户ID
@userID nvarchar(20),--用户ID
--@IP varchar(15) ,--用户IP
@IP nvarchar(15) ,--用户IP
@Port int,--端口
@SID varchar(20), --SID
@NetClass tinyint--网络类型
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--update systemUsers set lastIp=@IP,lastPort=@Port,lastNetClass=@NetClass,lastDate=GetDate(),
--onlineState=1 where userID=@userID
update IMUser set lastIp=@IP,lastPort=@Port,lastNetClass=@NetClass,lastDate=GetDate(),[SID]=@SID,onlineState=1 where userID=@userID
END
GO
CREATE PROCEDURE [RecordMsgInsert]
-- Add the parameters for the stored procedure here
--@userID varchar(20),
@userID nvarchar(20),
@ReceiveID varchar(20),
@msgContent ntext,
@ImageInfo ntext,
@fontName nchar(30),
@fontSize float,
@fontBold bit,
@fontItalic bit,
@fontStrikeout bit,
@fontUnderline bit,
@fontColor int,
@InfoClass int,
@SMSInfo tinyint,
@GroupID int
AS
BEGIN
SET NOCOUNT ON;
insert into RecordMsg (sendID,
msgContent,
ImageInfo,
fontName,
fontSize,
fontBold,
fontItalic,
fontStrikeout,
fontUnderline,
fontColor,
InfoClass,
SMSInfo,
GroupID
)
values(@userID,
@msgContent,
@ImageInfo,
@fontName,
@fontSize,
@fontBold,
@fontItalic,
@fontStrikeout,
@fontUnderline ,
@fontColor,
@InfoClass,
@SMSInfo,
@GroupID
)
if (@ReceiveID<>'')--如果接收者ID不为空，说明不是群发消息，则将用户记录添加到RecordMsgUser表
begin
insert into RecordMsgUser (MsgId,ReceiveID ) values(@@IDENTITY ,@ReceiveID )
end
SET NOCOUNT OFF
return @@IDENTITY
END
GO
CREATE PROCEDURE [RecordMsgGetFromId]
-- Add the parameters for the stored procedure here
@ID numeric (18,0)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from RecordMsg where MsgId= @ID
END
GO
CREATE PROCEDURE [RecordMsgGet]
-- Add the parameters for the stored procedure here
@userID nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from RecordMsg,RecordMsgUser where RecordMsgUser.isSendSuccess=0 and RecordMsgUser.ReceiveID=@userID and RecordMsg.MsgId=RecordMsgUser.MsgId
END
GO
CREATE PROCEDURE [RecordMsgDelFromID]
-- Add the parameters for the stored procedure here
@ID numeric (18,0),
@userID nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- update RecordMsgUser set isSendSuccess=1 where ReceiveID=@userID and MsgId=@ID
-- 先删除RecordMsgUser表中的用户信息
delete from RecordMsgUser where ReceiveID=@userID and MsgId=@ID
-- 获取当前消息的记录条数是否为0
DECLARE @Total INT
select @Total=count(*) from RecordMsgUser where MsgId=@ID
if (@Total=0)
begin
delete from RecordMsg where MsgId=@ID --删除当前离线消息
end
END
GO
CREATE PROCEDURE [UpdateRecentContact]
-- Add the parameters for the stored procedure here
--@userID varchar(20),--用户ID
@userID nvarchar(20),--用户ID
@receiveID nvarchar(20)-- 最近联系人ID
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @existUserID int;
DECLARE @cTime varchar(50);
SET @cTime= CONVERT(varchar(4),year(getdate()))+'-'+CONVERT(varchar(4),month(getdate()))+'-'+CONVERT(varchar(2),day(getdate()))+' '+CONVERT(varchar(2),datepart(hh,getdate
()))+':'+CONVERT(varchar(2),datepart(mi,getdate()))+':'+CONVERT(varchar(2),datepart(ss,getdate()))+'.'+CONVERT(varchar(10),datepart(ms,getdate()))
SELECT @existUserID=COUNT(*) FROM UserRecentContact WHERE UserID=@userID AND ContactID=@receiveID
IF @existUserID=0--列表中没有该用户，则添加记录
begin
INSERT INTO UserRecentContact(UserID,ContactID,ContactType,ContactTime,OrderID) VALUES(@userID,@receiveID,0,@cTime,0)
end
else --列表中已存在，则更新时间
begin
update UserRecentContact set ContactTime=@cTime where UserID=@userID AND ContactID=@receiveID
end
END
GO
CREATE PROCEDURE [SYSTEMRecordMsgGet]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from RecordMsg,RecordMsgUser where RecordMsgUser.isSendSuccess=0 and RecordMsg.SendID='SYSTEM' and RecordMsg.MsgId=RecordMsgUser.MsgId
END
GO
CREATE TABLE [smsUser](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[msgID] [numeric](18, 0) NOT NULL,
[receiveID] [nchar](20) NOT NULL,
[sendFailedCount] [tinyint] NULL,
[isSendSuccess] [bit] NULL,
[lastDate] [datetime] NULL,
CONSTRAINT [PK_smsUser] 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
--只获取前20个
CREATE PROCEDURE [UserGetRecentContactList]
@userId varchar(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 20 UserID,ContactID,ContactType,ContactTime,OrderID from UserRecentContact where UserID=@userId ORDER BY CONVERT(datetime,ContactTime) DESC
END
GO
CREATE PROCEDURE [UserGetUsersSignsByUserID]
@userID nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT Remark from IMUser where userID=@userID
END
GO
CREATE PROCEDURE [UserGetUserDataFromUserID]
-- Add the parameters for the stored procedure here
--@userID varchar(20)
@userID nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--2011.6.7修改
--SELECT * from systemUsers where userID=@userID
--由于只取版本号等和部门用户名无关的信息，所以就这么也行
SELECT a.*,b.orderID,b.Remark,b.SID from V_Emp a,IMUser b where a.UserID=b.UserID AND a.userID=@userID
END
GO
CREATE PROCEDURE [userIsPassword]
--@userID varchar(20),
@userID nvarchar(20),
--@password varchar(32)
@password nvarchar(32)
AS
BEGIN
SET NOCOUNT ON;
declare @count int
-- Insert statements for procedure here
--SELECT @count=count(*) from systemUsers where userID=@userID and password=@password
SELECT @count=count(*) from V_Emp where userID=@userID and pass=@password
return @count
END
GO
CREATE PROCEDURE [userIsExist]
-- Add the parameters for the stored procedure here
--@userID varchar(20)
@userID nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
declare @userCount tinyint
set @userCount=0
--SELECT @userCount=count(userID) from systemUsers where UserID=@userID
SELECT @userCount=count(userID) from V_Emp where UserID=@userID
return @userCount
END
GO
CREATE PROCEDURE [userGetrData]
-- Add the parameters for the stored procedure here
--@userId varchar(20)
@userId nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT * from SystemUsers where userID=@userId
SELECT a.UserID,a.UserName,a.UserName as [Name],
b.Tel as ophone,b.Mobile,b.Fax,b.EMail,b.Tel as PHS
FROM V_Emp a,IMUser b WHERE a.UserID=b.UserID AND a.UserID=@userId
END
GO
CREATE PROCEDURE [UserGetPageUsersBaseInfo]
--@UserID varchar(20),
@UserID nvarchar(20),
@PageSize int,
@PageIndex int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #PageIndex --创建临时表
(
IndexId int IDENTITY (1, 1) NOT NULL,
--UserID varchar(20)--用于保存用户ID集合
UserID nvarchar(20)--用于保存用户ID集合
)
--INSERT INTO #PageIndex(UserID) select userID from systemUsers
--where DepId<>1 order by orderID,userID asc
INSERT INTO #PageIndex(UserID) select a.userID from V_Emp a,IMUser b
where a.UserID=b.UserID order by b.orderID,a.userID asc
--select userID,userName,DepId,orderID from systemUsers
--WHERE userID in(select userID from #PageIndex where IndexId BETWEEN @PageSize*(@PageIndex-1)+1 AND @PageSize*(@PageIndex))
select a.userID,a.userName,a.DeptID as DepId,b.orderID
from V_Emp a,IMUser b WHERE a.UserID=b.UserID AND a.userID in(select userID from #PageIndex
where IndexId BETWEEN @PageSize*(@PageIndex-1)+1 AND @PageSize*(@PageIndex))
END
GO
CREATE PROCEDURE [userGetBaseData]
-- Add the parameters for the stored procedure here
--@userId varchar(20)
@userId nvarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT * from SystemUsers where userID=@userId
SELECT a.UserID,a.UserName,b.PostCode as post,
b.Tel as phone,b.Mobile as GSM,b.Fax as phs
FROM V_Emp a,IMUser b WHERE a.UserID=b.UserID AND a.UserID=@userId
END
GO
CREATE PROCEDURE [UserChangePassword]
--@userID varchar(20),
@userID nvarchar(20),
@PasswordOld varchar(32),--密码为32位MD5值
@PasswordNew varchar(32) --密码为32位MD5值
AS
BEGIN
declare @userCount int--记录用户旧密码是否正确
SET NOCOUNT ON;
--select @userCount=count(*) from systemUsers where UserID=@userID and password=@PasswordOld
select @userCount=count(*) from V_Emp where UserID=@userID and Pass=@PasswordOld
if @userCount=0
return 0--返回0表示修改密码不成功，原因旧密码不正确
--update systemUsers set Password=@PasswordNew where UserID=@userID
update V_Emp set Pass=@PasswordNew where UserID=@userID
return 1 --返回1表示修改密码成功
END
GO
CREATE PROC [UpdateSystemData]
AS
BEGIN
DECLARE @OldUsersVersion varchar(50)
DECLARE @OldDepsVersion varchar(50)
DECLARE @OldUsersCount int
DECLARE @OldDepsCount int
DECLARE @NewUsersVersion varchar(50)
DECLARE @NewDepsVersion varchar(50)
DECLARE @NewUsersCount int
DECLARE @NewDepsCount int
--部门
DELETE FROM IMDept WHERE DeptID NOT IN (SELECT DeptID FROM V_Dept)
INSERT INTO IMDept(DeptID,orderID,isAbandan) SELECT DeptID,1,'False' FROM V_Dept
WHERE DeptID NOT IN (SELECT DeptID FROM IMDept)
--用户
DELETE FROM IMUser WHERE UserID NOT IN (SELECT UserID FROM V_Emp)
INSERT INTO IMUser(UserID,orderID,headPicIdx,CreateGroupMax,isSendSMS,isEditUserData,isAdmin,isAbandan,
DepsVersion,UsersVersion) SELECT UserID,1,0,10,0,0,'False','False',0,0 FROM V_Emp
WHERE UserID NOT IN (SELECT UserID FROM IMUser)
--判断首次
SELECT @OldUsersCount=COUNT(*) FROM IMUser WHERE UsersVersion<>0
IF @OldUsersCount=0
BEGIN
UPDATE IMUser SET UsersVersion=1
END
SELECT @OldDepsCount=COUNT(*) FROM IMUser WHERE DepsVersion<>0
IF @OldDepsCount=0
BEGIN
UPDATE IMUser SET DepsVersion=1
END
SELECT TOP 1 @OldUsersVersion=UsersVersion,@OldDepsVersion=DepsVersion,@OldUsersCount=UsersCount,
@OldDepsCount=DepsCount FROM IMUser WHERE DepsVersion<>0
SELECT @NewUsersCount=count(*) FROM IMUser
SELECT @NewDepsCount=count(*) FROM IMDept
SET @NewUsersVersion=CAST(CAST(@OldUsersVersion as int)+1 as varchar)
SET @NewDepsVersion=CAST(CAST(@OldDepsVersion as int)+1 as varchar)
update IMUser set UsersCount=@NewUsersCount,UsersVersion=@NewUsersVersion,DepsCount=@NewDepsCount,DepsVersion=@NewDepsVersion
END
GO
CREATE PROCEDURE [smsSendSuccess]
-- Add the parameters for the stored procedure here
@ID numeric(18,0)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
update smsUser set isSendSuccess=1,lastDate=getdate() where id=@ID
SET NOCOUNT off;
END
GO
CREATE PROCEDURE [smsSendFailed]
-- Add the parameters for the stored procedure here
@ID numeric(18,0)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
update smsUser set sendFailedCount=sendFailedCount+1,lastDate=getdate() where ID=@ID
SET NOCOUNT off;
END
GO
CREATE PROCEDURE [smsInsert]
-- Add the parameters for the stored procedure here
@ReceiveID varchar(8000) ,
@sendID varchar(20) ,
@msgContent varchar(1000)
AS
BEGIN
declare @MsgId numeric(18,0)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
insert SMS(sendID,smsMsgContent) values(@sendID,@msgContent)
set @MsgId= @@IDENTITY
declare @next int
set @next=1
while @next<dbo.Get_StrArrayLength(@ReceiveID,';')
begin
insert into smsUser (MsgId,ReceiveID) values(@MsgId ,dbo.Get_StrArrayStrOfIndex(@ReceiveID,';',@next))
set @next=@next+1
end
SET NOCOUNT OFF
END
GO
CREATE PROCEDURE [smsGetSendSuccessfulCount]
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT count(*) from smsUser where isSendSuccess=1
END
GO
CREATE PROCEDURE [smsDelNoSendFailed]
AS
BEGIN
SET NOCOUNT ON;
delete from smsUser where smsUser.isSendSuccess=0
END
GO
CREATE PROCEDURE [DepGetDepsPageBaseInfo]
-- Add the parameters for the stored procedure here
--@UserID varchar(20),
@UserID nvarchar(20),
@PageSize int,
@PageIndex int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #PageIndex --创建临时表
(
IndexId int IDENTITY (1, 1) NOT NULL,
DepID int --用于保存部门ID集合
)
--INSERT INTO #PageIndex(DepID) select DepID from Department
--where DepId<>1 order by OrderID,DepID asc
INSERT INTO #PageIndex(DepID) select a.DeptID from V_Dept a,IMDept b WHERE a.DeptID=b.DeptID order by b.OrderID,a.DeptID asc
--select DepID,DepName,SuperiorId,OrderID from Department
--WHERE DepID in(select DepID from #PageIndex where IndexId BETWEEN @PageSize*(@PageIndex-1)+1 AND @PageSize*(@PageIndex))
select a.DeptID as DepID,a.DeptName as DepName,a.ParentDeptID as SuperiorId,b.OrderID from V_Dept a,IMDept b WHERE a.DeptID=b.DeptID AND a.DeptID in(select DepID from
#PageIndex where IndexId BETWEEN @PageSize*(@PageIndex-1)+1 AND @PageSize*(@PageIndex))
END
GO
CREATE PROCEDURE [DepGetData]
-- Add the parameters for the stored procedure here
--@DepId varchar(20),
@DepId int,
@flag bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
if @flag = 0
begin
--SELECT * from Department where DepId=@DepId
SELECT a.*,b.orderID from V_Dept a,IMDept b where a.DeptID=b.DeptID AND a.DeptID=@DepId
end
else
begin
--SELECT * from Department where SuperiorId=@DepId
SELECT a.*,b.orderID from V_Dept a,IMDept b where a.DeptID=b.DeptID AND ParentDeptID=@DepId
end
END
GO
/****** Object: Default [DF_SMS_sendfailedCount] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [SMS] ADD CONSTRAINT [DF_SMS_sendfailedCount] DEFAULT (getdate()) FOR [datetime]
GO
/****** Object: Default [DF_sendCount_saveDate] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [sendCount] ADD CONSTRAINT [DF_sendCount_saveDate] DEFAULT (getdate()) FOR [saveDate]
GO
/****** Object: Default [DF_RecordMsgUser_isSendSuccess] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsgUser] ADD CONSTRAINT [DF_RecordMsgUser_isSendSuccess] DEFAULT ((0)) FOR [isSendSuccess]
GO
/****** Object: Default [DF_RecordMsgUser_isSave] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsgUser] ADD CONSTRAINT [DF_RecordMsgUser_isSave] DEFAULT ((0)) FOR [isSave]
GO
/****** Object: Default [DF_CustomGroup_CreateDateTime] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [CustomGroup] ADD CONSTRAINT [DF_CustomGroup_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime]
GO
/****** Object: Default [DF_RecordMsg_msgDateTime] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_msgDateTime] DEFAULT (getdate()) FOR [msgDateTime]
GO
/****** Object: Default [DF_RecordMsg_fontBold] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_fontBold] DEFAULT ((0)) FOR [fontBold]
GO
/****** Object: Default [DF_RecordMsg_fontItalic] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_fontItalic] DEFAULT ((0)) FOR [fontItalic]
GO
/****** Object: Default [DF_RecordMsg_fontStrikeout] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_fontStrikeout] DEFAULT ((0)) FOR [fontStrikeout]
GO
/****** Object: Default [DF_RecordMsg_fontUnderline] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_fontUnderline] DEFAULT ((0)) FOR [fontUnderline]
GO
/****** Object: Default [DF_RecordMsg_isSendSMS] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_isSendSMS] DEFAULT ((0)) FOR [SMSInfo]
GO
/****** Object: Default [DF_RecordMsg_GroupID] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [RecordMsg] ADD CONSTRAINT [DF_RecordMsg_GroupID] DEFAULT ((-1)) FOR [GroupID]
GO
/****** Object: Default [DF_IMUser_orderID] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_orderID] DEFAULT ((1)) FOR [orderID]
GO
/****** Object: Default [DF_IMUser_headPicIdx] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_headPicIdx] DEFAULT ((0)) FOR [headPicIdx]
GO
/****** Object: Default [DF_IMUser_CreateGroupMax] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_CreateGroupMax] DEFAULT ((10)) FOR [CreateGroupMax]
GO
/****** Object: Default [DF_IMUser_isSendSMS] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_isSendSMS] DEFAULT ((0)) FOR [isSendSMS]
GO
/****** Object: Default [DF_IMUser_isEditUserData] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_isEditUserData] DEFAULT ((0)) FOR [isEditUserData]
GO
/****** Object: Default [DF_IMUser_isAdmin] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_isAdmin] DEFAULT ((0)) FOR [isAdmin]
GO
/****** Object: Default [DF_IMUser_isAbandan] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_isAbandan] DEFAULT ((0)) FOR [isAbandan]
GO
/****** Object: Default [DF_IMUser_DepsVersion] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_DepsVersion] DEFAULT ((0)) FOR [DepsVersion]
GO
/****** Object: Default [DF_IMUser_UsersVersion] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMUser] ADD CONSTRAINT [DF_IMUser_UsersVersion] DEFAULT ((0)) FOR [UsersVersion]
GO
/****** Object: Default [DF_IMDept_orderID] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMDept] ADD CONSTRAINT [DF_IMDept_orderID] DEFAULT ((1)) FOR [orderID]
GO
/****** Object: Default [DF_IMDept_isAbandan] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [IMDept] ADD CONSTRAINT [DF_IMDept_isAbandan] DEFAULT ((0)) FOR [isAbandan]
GO
/****** Object: Default [DF_smsUser_sendFailedCount] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [smsUser] ADD CONSTRAINT [DF_smsUser_sendFailedCount] DEFAULT ((0)) FOR [sendFailedCount]
GO
/****** Object: Default [DF_smsUser_isSendSuccess] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [smsUser] ADD CONSTRAINT [DF_smsUser_isSendSuccess] DEFAULT ((0)) FOR [isSendSuccess]
GO
/****** Object: ForeignKey [FK_smsUser_SMS] Script Date: 06/24/2013 14:00:01 ******/
ALTER TABLE [smsUser] WITH CHECK ADD CONSTRAINT [FK_smsUser_SMS] FOREIGN KEY([msgID])
REFERENCES [SMS] ([msgID])
GO
ALTER TABLE [smsUser] CHECK CONSTRAINT [FK_smsUser_SMS]
GO
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手